On Nov 30, 2007 2:13 PM, Hans-Christoph Steiner <
hans@eds.org> wrote:
Works for me after I compiled it as a universal binary. I attached the makefile to compile it as universal. I have one question about the interface, the [sqlite -open( style messages seem strange. Since you are sending them directly to the [sqlite] object, the "sqlite" part seems redundant, and the preceding "-" isn't very Pd-like. How about:
[open $1(
[close(
[tagged 1(
[tagged 0(
Hans, Thanks for taking the time to look at this.
I guess something needs to be explained here as the whole SQL parsing thing is to get around how PD treats comma's. The external tries to parse the SQL as closely as possible to what REAL SQL would be (mind you, this is not real parsing, so much as reading the SQL as characters, and then reassembling the characters in the buffer). As PD uses comma's to separate messages that get sent to the same object, we are taking advantage of that by assuming that a message like
"sql select mine, this, that from mytable submitsql"
would get reassembled from the following PD messages.
"sql select mine"
"this"
"that from mytable submitsql"
Because of this, it is necessary to delimit an SQL statement, with both the beginning and the ending of the statement.
So, because of this, we also need a way to send control messages to the external itself. Hence the reason for the 'sqlite' messages.
As Jamie has pointed out, the biggest benefit to using the messages in this way is that we get to use the '$1' replacement mechinism already in PD to put the data into the SQL statements.
And as far as the '-' goes, it is immaterial and can go away... I just thought it made it easier to read, especially when you can send multiple control commands within a single message...
[sqlite -buffer 1000 -results 100 -tagged 0(
This would set up the size of the SQL buffer, the size of the expected result set buffer, and then turn off the tagging. I only put the hyphens there to make this readable.
I don't quite see the need for the "endsql" and "submitsql" tags at the end of a message. Is there ever a time that you would write stuff after "endsql" that has a specific meaning? Otherwise, it seems like there should just be the selector "sql" at the front of the message.
We need to know where the REAL end to the message is because of the comma issue. And, no, putting something at the end of the message would not work, as the "parser" looks at the last token in the message for a delimiter.
Basically, the difference between the two tags is that one will terminate the SQL statement, and then return (that is 'endsql'). The other one would terminate the statement, and then submit the SQL to the database. Each line then is processed. IF, for instance, you chained a bunch of 'select' statements, all the results from each statement would be returned as one large result set. This also allows for the ability to send a buffer of 'insert' statements as one large group. Such as...
[sql begin endsql(
[sqla insert into mytable (name, id, data) values ('$1', $2, $3) endsql(
[sqla commit submitsql(
The first message would start a transaction, the second would be iterated over some loop, replacing the $1, $2, and $3 with whatever data is to be stored, and the last message would submit the SQL to the database, and commit the data.
Also, this character buffer will grow as needed, and as far as I know, it is not restricted to the size of MAXPDSTRING.
My first thought on this feature was in storing data into a database (in the case where you are connecting over a network, not with SQLite), and you wanted to send an entire buffer at once, instead of as a serise of buffers.
Also, I am assuming that "sqla" means something like "append". How about emulating the [textfile] syntax so it feels more Pd-ish. Something like:
[add insert into mine (name) values ('jacob') (
[add insert into mine (name) values ('fred') (
[submit(
This is doable, as a matter of fact, it is set up in such a way that the user can change these tags to be whatever they want. Not very portable, but hey...
I still think there would need to be a trailing tag to delimit the SQL, due to the comma issue.
and
[submit insert into mine (name) values ('the_text') (
Looks good overall, keep up the good work! I think this will be quite useful, and should be easy to build on all platforms and include in Pd-extended.
Thanks, Hans. I do have one question about the SQLite code. While the intent of using SQLite in the first place was to make it as painless as possible to work with SQL from PD. I had started with the idea of using 'libdbi', and they say that you need Fink on the Mac to install it. I went through the whole install, only to get to the last stage for the drivers, and it tells me that one of the programs it expected from Fink was NOT there.
I do see it to be very possible that this could be a prototype for working with SQL, and the same message processing things could work with other databases.
Another issue that I need to find out about is how to include the SQLite code. As that is under PUBLIC DOMAIN and holds no copyright, we can freely include the latest version with the external. OR, would it be better to include it as a library. They do claim that running it compiled directly into your code, it runs faster. They claim it is about %5 faster.
At the same time, compiling as a library is an extra step require to use this.
Mike
--
Peace may sound simple—one beautiful word— but it requires everything we have, every quality, every strength, every dream, every high ideal.