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

Mike McGonagle mjmogo at gmail.com
Mon Nov 12 23:18:55 CET 2007


On 11/12/07, Jamie Bullock <jamie at postlude.co.uk> wrote:
>
> On Mon, 2007-11-12 at 13:38 -0600, Mike McGonagle wrote:
> 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.

One of the assumptions that I was making is that only the DATA is
being returned, I was not thinking about inserting the keys into the
lists. I am trying to think in terms of what would be the most
efficient in regards to the amount of time spent processing each
result set.

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

Well, I didn't say crazy, I was just assuming that these messages
would be created by appending symbols and such to a message box and
then sending that to the database.

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

And this is what I have been curious about. Quite frankly, I have not
looked at psql as it is directed solely at PostGres, and I don't use
it... After looking at your stuff, and seeing your descriptions, it
makes sense.

I just wonder about the extra overhead involved in dealing with tagged
lists of data.

>
> 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.

Agreed, my assumptions were different on the tagging.

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

I will have to rethink my stuff, and maybe just use your stuff to make
a generic one that connects with any database, through libdbi or
something.

That being said, I am curious to know how the performance is that you
are getting? Having to deal with the keys doubles the amount of data
handling right from the get-go.

> > 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/

Thanks, I will rethink this, based on your code...

>
> > 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.

Well, I am thinking along the lines of not having to process, or
process as little as possible, the result sets. I would prefer to be
able to take the data straight out of the external, and use it without
having to do any routing.

Would this approach provide for faster performance?

>
> > 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.

I had thought about this, and figured that just providing a list as
the out would be easier, and then just having them unpack it would be
sufficient. I would still like to avoid having to deal with embedding
the keys for the data (I would think that a user would already know
the format of the data coming back), as this would require more
processing by the external to package it up, only to have the user
'parse' it on the way out.

I guess one of my goals here is to be able to store a score (or some
sort of squence) within the database, and then recall those things you
need, when you need them. That being said, I know that databases are
not guarenteed to work in realtime. That is one of the reasons that I
would like to use sqlite directly, and not through a universal driver
like libdbi.

Thanks again, and I will look more at your stuff as a model.

Mike


-- 
Help the Environment, Plant a Bush back in Texas!

"I place economy among the first and most important republican
virtues, and public debt as the greatest of the dangers to be feared.
To preserve our independence, we must not let our rulers load us with
perpetual debt."
-- Thomas Jefferson, third US president, architect and author (1743-1826)

"Give Peace a Chance" -- John Lennon (9 October 1940 – 8 December 1980)

Peace may sound simple—one beautiful word— but it requires everything
we have, every quality, every strength, every dream, every high ideal.
—Yehudi Menuhin (1916–1999), musician

If you think you can, or you think you can't, you are probably right.
—Mark Twain

"Art may imitate life, but life imitates TV."
Ani DiFranco




More information about the Pd-dev mailing list