[PD-dev] Fwd: Fwd: Fwd: Connecting up an SQL Database to PD

[Jamie Bullock]

On Mon, 2007-11-12 at 13:38 -0600, Mike McGonagle wrote:
> God, why do I do this?
I often ask myself that question!

> > > > > 2. I think having the SQL query provided as an object creation argument
> > > > > is a really bad idea.  Wouldn't it be better to just pass the query in
> > > > > as a list
> >
> > It seems mean to leave all formatting to the user, but that's most flexible
> > in practice.
> 
> At the same time, the process of building the message to create the
> SQL that then gets submitted could be cumbersome. 
> If it is just a
> simple thing like loading up a textfile, and then forwarding this on
> that is one thing (assuming that each line in the textfile is a
> complete SQL statement), but to construct dynamic SQL on the fly, that
> is a whole different thing.

I don't really understand what you mean. AFAICT we're just talking about
the difference between passing the query to the object via its
arguments, or passing the query as a message to the first inlet. The
latter just seems easier because you already have variable substitution
'for free' in Pd's message passing, and you don't need to reinstantiate
the object to change the query.

What's so crazy about constructing dynamic SQL? Already with [psql] (or
[sqlsingle]) It's straightforward to do something like:

[metro 500]
|
[random 100]
|
[moses 60]
|	|
|	|sql SELECT * FROM chords WHERE dynamic=$1 sqlend(
|	/
|sql INSERT INTO notes VALUES (2) sqlend(
|     /
|    /
|   /
|  /
[psql mydb]  

Or whatever...

>  And this doesn't even address the
> different types of SQL statements, each returning a different result
> set, or error code.

With [psql] the result of each query is returned as a set of 'tagged'
tuples. In Pd terms this means each row in the result set comprises a
discrete message prefixed with the row 'id', and with each column
corresponding to a single atom. This means that you can distinguish the
results of a single query from the results of multiple queries. Because
the object uses a single connection to the database, and no threads,
results are received in the order that queries are sent. 

I have found this to work well, and I think it would also work well for
sqlite4pd. 

> And then there is still the issue of having to parse through the
> result sets as they comeback...
> 
> > > > list would basically be UNFORMATTED data, and it would require the PD
> > > > code to interpret the result sets.
> >
> > KISS. Object should know as little about databases as it can, it's all down
> > to the object user and the service behind the object, object == gateway,
> > nothing much more.
> 
> Ok, can anyone create a PD patch that might illustrate some of the
> ways you envision sending SQL to the database? Nothing "workable",
> just a prototype of what you see...

I'm sure you've already looked at it, but there's a (maybe) useful
README and help file here:

http://pure-data.cvs.sourceforge.net/pure-data/externals/postlude/psql/

> While I would tend to agree in keeping it simple. There are a couple
> of hurdles that must be crossed. On the one hand, I get the impression
> that some are thinking that it is as simple as saying "select * from
> my-table;" and then you are done, each SQL statement is potentially
> VASTLY different from the next, and to expect to create a single
> INSTANCE of a connection, and pipe all your requests through that one
> object, I think is a little naive.

What's naive about that? Isn't that how most database clients work:
connect to the server, send queries through the connection. I think what
you are doing with your approach is something like having one connection
per query 'class' (type), but I don't think this approach is very
common. 

> On the other hand, I would think that expecting a single object to
> handle this would result in a 'route'ing nightmare in the PD code in
> interpreting the result sets. While dealing with a single instance of
> an SQL statement that has known results, would be very manageable and
> not require a lot of routing.

That's true. Your solution could require less routing/unpacking on the
results, but OTOH, it could make more if what the user wants is actually
a list that they can route/unpack in pd. I also think you are making
more work for yourself reinventing route, unpack etc inside your extern.

Jamie

-- 
www.postlude.co.uk