[plt-scheme] Working with database using scheme language

On Nov 24, 2008, at 3:33 PM, Rohan Golwala wrote:

> Hi,
>
> I have set up a connection to the database using postgreSQL but I  
> have a few questions while running the following queries:
>
> > (send a-connection map a-statement proc)
>   map : Statement (field ... -> 'a) -> (list-of 'a)
>
>   Executes a SQL query and applies the given function to the contents
>   of each row, returning a list of results.
>
> > (send a-connection for-each a-statement proc)
>   for-each : Statement (field ... -> void) -> void
>
>   Executes a SQL query and applies the given function to the contents
>   of each row, discarding the results.
>
> > (send a-connection mapfilter a-statement map-proc filter-proc)
>   mapfilter : Statement (field ... -> 'a) (field ... -> boolean) ->  
> (list-of 'a)
>
>   Like 'map', but applies the map procedure (given first) to only
>   those rows which satisfy the given predicate (given second).
>
> > (send a-connection fold a-statement proc init)
>   fold : Statement ('a field ... -> 'a) 'a -> 'a
>
> What should be the structure of the proc...? Can you send me one  
> entire example which explains the use of "proc" as well as "map- 
> proc" and "filter-proc" and their structure or the body of these  
> functions....?

spgsql comes with a sample script that has a few examples of using the  
higher-order query methods (map, mapfilter, fold) about halfway  
through. You can look at it online at this address:

   http://planet.plt-scheme.org/package-source/schematics/spgsql.plt/2/2/samples/sample1.ss

--

Here's some more explanation (with untested examples):

For the 'map' method, the procedure should take as many arguments as  
there are fields in the query result. For example, suppose that you  
have the following table:

     CREATE TABLE Person (name TEXT, city TEXT, age INTEGER)
     INSERT INTO Person VALUES ('Bill', 'Boston', 7)
     INSERT INTO Person VALUES('Mary', 'Seattle', 8)

and suppose you want to generate a report that says how old everyone  
is. You might write this query using the 'map' method:

     > (send my-connection map "SELECT name, age FROM Person"
           (lambda (name age) (format "~a is ~a years old" name age)))

and you might get a result like this:

     (list "Bill is 7 years old" "Mary is 8 years old")

The 'for-each' method is similar to 'map', but it executes the  
procedure for effect rather than gathering the resulting values in a  
list. Here's how you might use it:

     > (send my-connection for-each "SELECT name, age FROM Person"
           (lambda (name age) (printf "~a is ~a years old" name age)))

and it would print out these lines:

     Bill is 7 years old
     Mary is 8 years old

The 'fold' method, instead of accumulating the results into a list,  
lets you control the accumulator yourself. Unlike the other methods,  
the procedure takes one more argument than the number of fields  
returned by the query: the first argument is the previous accumulator  
value, and the first field is the second argument. For example, to  
compute the total age of people in the table, you could do this:

     > (send my-connection fold "SELECT name, age FROM Person"
           (lambda (total-age-so-far name age) (+ total-age-so-far age))
           0)
     15

Of course, that particular task would be better accomplished thus:

     > (send my-connection query-value "SELECT SUM(age) FROM Person")
     15

But you might have cases where you need to do the processing in Scheme.

Ryan