[PD-dev] SQLite for PD v0.0

Sat Dec 1 06:39:12 CET 2007

On Nov 30, 2007, at 5:04 PM, Mike McGonagle wrote:

>
>
> On Nov 30, 2007 2:13 PM, Hans-Christoph Steiner <hans at 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(

If you use a selector for every message into this object, then you  
can get rid of the "sqlite" prefix, and it would be very natural for  
people used to Pd.  For example:

[open $1(
[close(
[export $1(   - this could be used to export the database to a .sql file

This would build a message chunk-by-chunk:

[clear (
[add insert into mytable (name) values ('$1') (
[add insert into mytable (id) values ($2) (
[add insert into mytable (data) values ($3) (
[submit (

And this would be a one liner:

[submit insert into mine (name) values ('the_text') (

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

Instead of commas, you could reuse the same message used for message  
boxes [addcomma(.  Plus [tkwidgets/text]  (its in Pd-extended now)  
will allow you to type commas and have them sent in messages, but  
they'll be escaped.  Here's an example combined the syntax of  
[textfile] and message boxes:

[clear (
[add insert into mytable (name (
[addcomma (
[add id (
[addcomma (
[add  data) values ('$1' (
[addcomma (
[add $2 (
[addcomma (
[add $3)  (
[submit (

It's not pretty, but it follows Pd-style messages and it would work.

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

I think it should just be compiled into the external. It'll make it  
really easy to use and distribute.  I think this will be an very  
useful object.  Since it's all straight C, it should be easy to  
incorporate into Pd-extended.  Then we'll have a cross-platform  
database object included in the distro.  (For whatever reason, no one  
has ever gotten around to adding flext and therefore [pool] to Pd- 
extended...)

.hc

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

------------------------------------------------------------------------ 
----

Man has survived hitherto because he was too ignorant to know how to  
realize his wishes.  Now that he can realize them, he must either  
change them, or perish.    -William Carlos Williams

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.puredata.info/pipermail/pd-dev/attachments/20071201/e070ce17/attachment.htm>