On Nov 30, 2007, at 5:04 PM, Mike McGonagle wrote:
On Nov 30, 2007 2:13 PM, Hans-Christoph Steiner <[EMAIL PROTECTED]> 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
_______________________________________________
PD-dev mailing list
[email protected]
http://lists.puredata.info/listinfo/pd-dev
