???
---------- Forwarded message ---------- From: Mike McGonagle mjmogo@gmail.com Date: Nov 13, 2007 11:57 AM Subject: Re: [PD-dev] Fwd: Fwd: Fwd: Connecting up an SQL Database to PD To: jamie@postlude.co.uk
Jamie,
After looking at you code for passing messages into the external, instead of on the creation args, I agree, this is a little more elegant of a design.
That being said, I did notice a couple of things that might result is very subtle bugs.
1. Suppose we have a statement like this...
sql select onset, pitch, velocity, 1, duration from myscore endsql
Not having been able to compile your external, I am not quite certain, but I think in this case there would be 5 different messages sent to the external as follows:
a. sql select onset b. pitch c. velocity d. 1 e. duration from myscore endsql
as this stands, the first a, b, and c would be passed through the 'anything' method. But as d is NOT a list, it would pass through the 'float' method, and the remainder would pass through the 'anything'. As your code is right now, I think the 4th thing would get dropped. Now I know this is probably not all that common, but consider the case where a sequence is stored in the database, and the user wants to create a variation of how it gets rendered, and wants one of those parameters to be a CONSTANT. As it is legal SQL to pass in a literal number on the 'select' columns list, this CONSTANT can be substituted for the parameter in the database.
2. Class methods such as 'close'. As you are depending on the 'anything' method to gather up things that are not actually recognized by the external and assume they are SQL, if by chance, there was a field that the user wanted to extract from the database that is named 'close', it would fail.
sql select id, name, close from table endsql
a. sql select id b. name c. close
this would only process things upto and including the close. Also, any other class messages that you create would have the same effect.
To get around this, I would think that there should be a single symbol, maybe using the class name (or something), which would then be followed by the actual option.
I wrote a test external last night to test these things, and if you are interested, I could send this to you. I will have to do that tomorrow, as I don't have my computer with me at work.
Mike
On 11/13/07, Jamie Bullock jamie@postlude.co.uk wrote:
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
-- www.postlude.co.uk
-- 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
Mike McGonagle wrote:
After looking at you code for passing messages into the external, instead of on the creation args, I agree, this is a little more elegant of a design.
That being said, I did notice a couple of things that might result is very subtle bugs.
- Suppose we have a statement like this...
sql select onset, pitch, velocity, 1, duration from myscore endsql
Not having been able to compile your external, I am not quite certain, but I think in this case there would be 5 different messages sent to the external as follows:
a. sql select onset b. pitch c. velocity d. 1 e. duration from myscore endsql
You are correct in that 5 separate messages are sent to the external. However, the variable x->sqlStringStore is used to accumulate the query if it is split between several Pd messages. psql_anything() also takes care of adding the string ", " to sqlStringStore to compensate for the missing commas. It can do this because data stored in the object struct persists between method calls. The object stops accumulating the string, sends the query to the database and clears the sqlStringStore array when a message with the symbolic selector 'sqlend' is received.
Jamie
-
jamie＠postlude.co.uk -
Mike McGonagle