Wednesday, September 19, 2012

I recently completed reading India Calling: An Intimate Portrait of a Nation's Remaking by Anand Giridharadas. Giridharadas writes the "Currents" column for the International Herald Tribune and The New York Times online. A child of immigrant parents, he worked in Mumbai as a management consultant until 2005, when he began reporting from that city for the Herald Tribune and the Times. The book traces the new found freedom and ambition among the people of India. Giridharadas contrasts the current Indian society with the time when his parents moved from India to US for better prospects. At that time Giridharadas notes, the move from India to the US was a necessity unlike now. He provides a bewilderedforeigner's perspective on the rapidly developing, fast paced, increasingly westernized India and he finds it difficult to reconcile the Indian story that he heard from his parents when he was young.Watch his interview with Jon Stewart about his book here :

Saturday, September 15, 2012

SQL*Plus is an Oracle command-line utility program that can run SQL and PL/SQL commands interactively or from a script. Programmers and DBAs commonly use it as the default available fundamental interface in almost any Oracle software installationOracle does not provide .deb packages which are recognized by Ubuntu. Instead they provide .rpm packages. This tutorial tells how to install SQL*Plus without installing Oracle and using the .rpm packages in Ubuntu.Follow these steps : 1. Download the following .rpm packages from Oracle's website. a. Choose either 32 bit or 64 bit versions b. Download Instant Client Package - Basic:All files required to run OCI, OCCI, and JDBC-OCI applications c. Download Instant Client Package - SQL*Plus:Additional libraries and executable for running SQL*Plus with Instant Client Remember to download the .rpm versions and NOT the .zip versions2. Install the application alien to convert the .rpm packages to .deb. Install it by executing:

@cegme and I recently tackled this interesting problem of finding the shortest route between two nodes in a graph from inside a database.

We have a list of research papers and their citations which we call "links". The idea is to start from a given paper and follow its links recursively till reach the specified destination paper. This is a typical graph problem and can be solved using well known shortest-path finding algorithms. However our data was in the database consequently we wanted to solve the problem using SQL queries which was a challenge.

WITH provides a way to write subqueries for use in a larger SELECT query. The subqueries, which are often referred to as Common Table Expressions or CTEs, can be thought of as defining temporary tables that exist just for this query. One use of this feature is to break down complicated queries into simpler parts.

3. We use the WITH RECURSIVE keyword available in Postgres and form a table containing the starting paper (id), the next paper in the path (link), the length of the path (depth), the list of traversed paper (route), and a boolean cycle variable to keep a note of whether we are forming a cycle or not.

WITH RECURSIVE search_paper(id, link, data, depth, route, cycle)

4. We start off with the starting paper id. This is the base case and gets executed only once.

SELECT r.pid, r.citation, 'data', 1,

ARRAY[r.pid], false FROM reference r WHERE r.pid=start

5. We recursively add the next link in the path, check if a cycle is formed or not, increase the depth. If a cycle is formed, we immediately exit the loop. This is controlled by the cycle variable. This piece of code gets executed multiple times until cycle != true.

6. Finally if we hit our destination paper id in the search, we exit the loop. We only select the depth and the route (as an array of integers) and display it. Note that we order the result with increasing depth since we may obtain more than one route.

SELECT sp.depth, (sp.route || sp.link) AS route FROM search_paper AS spWHERE link = destination AND NOT cycle ORDER BY depth ASC;