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(

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.

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(

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.

.hc



On Nov 29, 2007, at 1:35 PM, Mike McGonagle wrote:

http://puredata.info/Members/mjmogo/

Done, check it out at the above link.

Mike

On Nov 27, 2007 11:50 PM, Hans-Christoph Steiner hans@eds.org wrote:

What's the address for the ftp server? You could also post it on puredata.info, just make an account.

.hc

On Nov 27, 2007, at 1:14 PM, Mike McGonagle wrote:

...

Hello all,

Ok, I got some preliminary things to test. As far as I can tell, everything works using the message input method. There are still some issues to resolve for the 'text' widget entry method.

As it still contains the Amagamated version of SQLite, the archive that I have is about 800K. I posted the stuff on an FTP server. The login info is...

User: fsr Password: puredata

There is a darwin external for PPC in the archive. You don't need to have a database, as the test program will allow you to create one, and add a table, and some data.

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 _______________________________________________ PD-dev mailing list PD-dev@iem.at http://lists.puredata.info/listinfo/pd-dev

---

---

There is no way to peace, peace is the way. - A.J. Muste

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

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

Terrorism is not an enemy. It cannot be defeated. It's a tactic. It's about as sensible to say we declare war on night attacks and expect we're going to win that war. We're not going to win the war on terrorism. - retired U.S. Army general, William Odom

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

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(

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

On Dec 4, 2007, at 12:49 PM, Mike McGonagle wrote:

On Nov 30, 2007 11:39 PM, Hans-Christoph Steiner hans@eds.org wrote: 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 is doable...

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.

At the same time, doing it in the fashion we are now would make it more SQL-like. While I understand the idea of doing things in a PD- like way, taking it too far, and mangling the SQL might make the object almost unfriendly, if not downright unusable. Trying to keep the SQL as close to the real syntax, I think is very important.

It would be nice to hear from others on this topic. Anyone?

Ah, I get it, you are re-inserting the commas that Pd is interpreting based on their being a message that does not start with "sql" or "endsql"? In that case, I think it makes sense to have a separate "sql" inlet. I just have a strong aversion to having an atom at the end of the list cause the execution of something, in effect to act like the selector. Pd's whole list handling is built around the idea that the selector is always the first element, and the rest are data.

So the first, hot inlet could accept the "open", "close", "export", "clear", "add", and "submit" messages. Then the right, cold inlet would just accept anything and append it onto the end of what's there. This is also something of a violation of Pd's standards for interfaces, but I think a much less egregious one.

Another possibility would be to have the submitsql message only cause a reaction if it was selector of the current message, but I think since this interpretation of the commas is non-standard, it would be best if it had its own inlet.

.hc

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

There is no way to peace, peace is the way. -A.J. Muste