[PD-dev] SQLite for PD v0.0

[Mike McGonagle]

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(

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.
—Yehudi Menuhin (1916–1999), musician
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.puredata.info/pipermail/pd-dev/attachments/20071130/7106a41a/attachment.htm>