God, why do I do this?
---------- Forwarded message ---------- From: Mike McGonagle mjmogo@gmail.com Date: Nov 12, 2007 1:35 PM Subject: Re: [PD-dev] Fwd: Fwd: Connecting up an SQL Database to PD To: Andy Farnell padawan12@obiwannabe.co.uk
On 11/13/07, Andy Farnell padawan12@obiwannabe.co.uk wrote:
On Mon, 12 Nov 2007 12:05:47 -0600 "Mike McGonagle" mjmogo@gmail.com wrote:
Apologies for the shallow input, just scanning and speaking my thoughts...
- I would strongly suggest not including the sqlite sources with the
sqlite4pd sources.
Yes, modularity, maintainability.
This really isn't a big issue. I will look at using a library for sqlite.
- 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. And this doesn't even address the different types of SQL statements, each returning a different result set, or error code.
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...
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. If you are familiar with other Database programming enviornments, they assume that you are working with a single SQL statement (or a simple variable statement) that serves a single purpose. They don't (AFAIK) try to pipe all their requests through the same object. Working with SQL not only requires knowing the programming, but also the format of the data expected in the database.
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.
Mike
-- Use the source
PD-dev mailing list PD-dev@iem.at http://lists.puredata.info/listinfo/pd-dev
-- 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
On Mon, 2007-11-12 at 13:38 -0600, Mike McGonagle wrote:
God, why do I do this?
I often ask myself that question!
- 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
On 11/12/07, Jamie Bullock jamie@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
On Mon, 2007-11-12 at 16:18 -0600, Mike McGonagle wrote:
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.
Well it amounts to calling SETFLOAT() per iteration over the results or not, so I think any difference in execution time would be negligible. However, I like your idea of making the tuple indices optional. I will add this to [psql]: |keys 0(, |keys 1( to turn the keys on and off.
I just wonder about the extra overhead involved in dealing with tagged lists of data.
I just did a benchmark, and for a billion iterations, with a compiler optimisation level of O2, both a loop with the SETFLOAT statement, and an empty loop completed in the order of 1-2 microseconds!
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.
See above, but what makes you think it doubles the data handling?
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?
Maybe slightly, but personally I would think about getting the design right, rather than optimisation at the moment. Unless you know that you are going to be dealing with queries that return MASSIVE result sets!
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.
I still think that a sqlite external is a nice idea in addition to other solutions because of the self-contained nature of it. I definitely think it's worth pursuing.
Jamie
On Tue, 13 Nov 2007, Jamie Bullock wrote:
I just wonder about the extra overhead involved in dealing with tagged lists of data.
I just did a benchmark, and for a billion iterations, with a compiler optimisation level of O2, both a loop with the SETFLOAT statement, and an empty loop completed in the order of 1-2 microseconds!
a really good optimiser can make an *infinite* loop run in that long! ;)
seriously, with optimisers it's becoming harder to make some measurements. C has a type modifier called "volatile" while forces true read/write on a variable, which can be useful when handling signals, threads, mmaped hardware, and... benchmarks.
Using that keyword, you can pretend that the compiler doesn't already know that it doesn't need to change the contents of the atom because you always write the same thing over and over and never use those values. Thus you can simulate a real situation but still isolate several components when you measure them.
Anyway... in the context of remote procedure calls and massive disk accesses, SETFLOAT should be the least of your concerns.
_ _ __ ___ _____ ________ _____________ _____________________ ... | Mathieu Bouchard - tél:+1.514.383.3801, Montréal QC Canada
Mathieu Bouchard wrote:
seriously, with optimisers it's becoming harder to make some measurements. C has a type modifier called "volatile" while forces true read/write on a variable, which can be useful when handling signals, threads, mmaped hardware, and... benchmarks.
That's very useful! Now I get a more sane result:
For SETFLOAT(a, i)
1e6 iterations: 5161 usec 1e9 iterations: 2.457 sec
Anyway... in the context of remote procedure calls and massive disk accesses, SETFLOAT should be the least of your concerns.
Agreed.
Jamie
On 11/12/07, Jamie Bullock jamie@postlude.co.uk wrote:
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/
Jamie,
I was looking at your source code for psql, and was wondering if it expects there to be only a single result set returned? Or does it merge all the results into one? I noticed the the comment...
/* up to 10 fields may be returned. returns floats or symbols */
I would think that making it such that it acted like 'textfile', that with each bang sent to the sql object, that it would return each subsequent row, and once the end of that set is reached, it would output a bang on a second outlet, similar to what 'textfile' does.
Thanks,
Mike M
On Mon, 2007-11-12 at 16:51 -0600, Mike McGonagle wrote:
On 11/12/07, Jamie Bullock jamie@postlude.co.uk wrote:
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/
Jamie,
I was looking at your source code for psql,
I know I said this already, and it's in the sources, but just for the record, the majority of the code in [psql] is by Iain Mott. I just forked the code added a little functionality, and took over maintaining it in CVS.
and was wondering if it expects there to be only a single result set returned? Or does it merge all the results into one? I noticed the the comment...
It gathers the results from each query and prefixes them with an index starting from 0, outputting each row as a list.
/* up to 10 fields may be returned. returns floats or symbols */
This is referring to the number of columns (not rows) that may be returned (corresponding to atoms in each Pd list from the outlet). There's no reasoon why this figure can't be higher, so I might change this in the source.
I would think that making it such that it acted like 'textfile', that with each bang sent to the sql object, that it would return each subsequent row, and once the end of that set is reached, it would output a bang on a second outlet, similar to what 'textfile' does.
Good idea! I'll add that too.
Jamie
Hallo, Jamie Bullock hat gesagt: // Jamie Bullock wrote:
I would think that making it such that it acted like 'textfile', that with each bang sent to the sql object, that it would return each subsequent row, and once the end of that set is reached, it would output a bang on a second outlet, similar to what 'textfile' does.
Good idea! I'll add that too.
Actually I thought about this, too, but I'm not sure if it's really useful. One of the strengths of SQL is, that you can limit the result set manually. So if I'd want one row at a time, I'd design my SQL query as such using LIMIT, and if I want more rows, I'd just use a different LIMIT clause. Hardcoding this into the SQL class in my view doesn't make much sense. (Using the last outlet to signal the end of results like in [textfile] does make sense, though.)
Ciao
Frank Barknecht wrote:
Jamie Bullock hat gesagt: // Jamie Bullock wrote:
I would think that making it such that it acted like 'textfile', that with each bang sent to the sql object, that it would return each subsequent row, and once the end of that set is reached, it would output a bang on a second outlet, similar to what 'textfile' does.
Good idea! I'll add that too.
Actually I thought about this, too, but I'm not sure if it's really useful. One of the strengths of SQL is, that you can limit the result set manually. So if I'd want one row at a time, I'd design my SQL query as such using LIMIT, and if I want more rows, I'd just use a different LIMIT clause. Hardcoding this into the SQL class in my view doesn't make much sense. (Using the last outlet to signal the end of results like in [textfile] does make sense, though.)
I agree. When I said "I'll add that too", I actually meant the 'bang on end of results from each query' functionality, not the 'output one line as list' a la [textfile]. If people want that, they can just do:
[psql] | [list prepend add] | [textfile]
...and then take advantage of the wonderful facilities offered by [textfile]!
Jamie
On 11/13/07, Frank Barknecht fbar@footils.org wrote:
Hallo, Jamie Bullock hat gesagt: // Jamie Bullock wrote:
I would think that making it such that it acted like 'textfile', that with each bang sent to the sql object, that it would return each subsequent row, and once the end of that set is reached, it would output a bang on a second outlet, similar to what 'textfile' does.
Good idea! I'll add that too.
Actually I thought about this, too, but I'm not sure if it's really useful. One of the strengths of SQL is, that you can limit the result set manually. So if I'd want one row at a time, I'd design my SQL query as such using LIMIT, and if I want more rows, I'd just use a different LIMIT clause. Hardcoding this into the SQL class in my view doesn't make much sense. (Using the last outlet to signal the end of results like in [textfile] does make sense, though.)
Frank, this isn't hardcoding anything into the external. Any select query on a database has the potential of returning a single row, or a billion. The point of this is that each row contains a different set of data, and the PD program would process them sequentially. Getting all the rows at once would be very cumbersome programming in sorting out the data. Being able to process each row by itself is a little bit less daunting. Also, if you only get one row at a time from the database, that would be a LOT of querying, and the amount of time for each query might slow the system quite a bit.
Mike
Ciao
Frank Barknecht _ ______footils.org__
PD-dev mailing list PD-dev@iem.at http://lists.puredata.info/listinfo/pd-dev
Hallo, Mike McGonagle hat gesagt: // Mike McGonagle wrote:
Any select query on a database has the potential of returning a single row, or a billion. The point of this is that each row contains a different set of data, and the PD program would process them sequentially. Getting all the rows at once would be very cumbersome programming in sorting out the data. Being able to process each row by itself is a little bit less daunting. Also, if you only get one row at a time from the database, that would be a LOT of querying, and the amount of time for each query might slow the system quite a bit.
Working with smaller result sets is pretty standard with SQL databases. If some query is returning billions of data, the query is wrong. A very common idiom with SQL programming is to first do a "SELECT COUNT(*) FROM x WHERE y", and then go through the result set in smaller steps using LIMIT. A Google search result page is an example for this in action.
Ciao
On 11/13/07, Frank Barknecht fbar@footils.org wrote:
Hallo, Mike McGonagle hat gesagt: // Mike McGonagle wrote:
Working with smaller result sets is pretty standard with SQL databases. If some query is returning billions of data, the query is wrong. A very common idiom with SQL programming is to first do a "SELECT COUNT(*) FROM x WHERE y", and then go through the result set in smaller steps using LIMIT. A Google search result page is an example for this in action.
I guess I thought you were trying to say that when you make a query, you were only expecting ONE result from every query.
Would it make sense to you to expect a result set to return 10 rows (or more), and then the SQL object (whatever the name) would then be sent a bang to get the next row set? I am assuming that the first result would be sent upon the initial return of the data from the call. This was my point of comparing this with a [textfile] object.
Mike
Ciao
Frank Barknecht _ ______footils.org__
PD-dev mailing list PD-dev@iem.at http://lists.puredata.info/listinfo/pd-dev
-
Frank Barknecht -
Jamie Bullock -
jamie＠postlude.co.uk
-
Mathieu Bouchard -
Mike McGonagle