[PD-dev] Comparing Max/MySQL with SQLdb

[Mike McGonagle]

Hello all,
I finally got around to installing Max, and looking at the MySQL external
for it... While I have not tried it out yet (I don't have access to a MySQL
database at the moment), from looking at the 2 windows of the example patch,
it is obvious that we have two different interfaces. Hans had commented that
we should follow the model set up with the Max MySQL stuff, as it is already
in use. I would be curious to find out how many people use it, but not being
a Max user, I am probably not the person to find out.

So, the obvious things that are different:

1. All input to the Max/MySQL external goes into the left inlet, while SQLdb
uses the left inlet for command input (banging for results, or other buffer
commands), and the right inlet handles all input SQL statements. This was
done to get around PD's use of comma's, so that they can be "reinserted"
into the SQL statement.

2. The outlets for Max/MySQL appear to be:

Left: Result Sets as a list
Middle: Number of Rows effected or Error indicator
Left: Bangs when done with results

SQLdb is as follows:

Left: Result Sets as a list (either tagged with the field name, or not [not
sure about Max/MySQL])
Middle: ID of last insert
Right: Error outlet and Bang when done with results

Note the differences between the Middle outlet. Max/MySQL outputs a
different piece of data that SQLdb does. Should this be changed? Which piece
of info is more important?

3. Max/MySQL supports Blobs, while SQLdb does not (at the current time).

4. There appears to be some functionality offered by Max/MySQL. In looking
at the SQL statements, there appears to be a function (named "LOAD_FILE")
that would read a file from disk. One of my goals for this external was that
it didn't provide any additional functionality, except to submit a query to
the database, and return the result sets or any error messages. While I am
not against adding functions to this, it would require that the SQLdb
external now parse the input SQL statement, and process it in some way. OR,
I could be off the track on this, and the function I am talking about is
actually provided by MySQL

5. I could be wrong on this one, too, but it would appear that Max/MySQL
supports more than one statement being inserted into the buffer before it
gets submitted to the database. I can see this being useful in that it would
allow for reading a disk file with SQL in it. I am making this assumption
based on the "sprintf" object in the main window.

6. The most important design difference between Max/MySQL and SQLdb is that
SQLdb uses a two part implementation. One that encapsulates all SQL
statements, and another that encapsulates the connection to the server. This
was done to allow for many statements to go through a single connection,
without having to send some sort of "connect" message to every instance of
the database objects.

One thing I can't quite figure out, without being able to run the thing, is
how it handles the result sets. Are they returned as one huge list? If so, I
don't think that is practical, and each result set should be returned as a
separate list, with a bang on the left inlet for each result.

So, I guess the question is, should we try to make SQLdb more like
Max/MySQL? Or should we continue with what we have? Also, I am not positive
if Max/MySQL supports Placeholders or not, but that is another aspect that
we need to think about.

Just a few thoughts...


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/20080219/08dbb0c5/attachment.htm>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: max_mysql_main.jpg
Type: image/jpeg
Size: 163691 bytes
Desc: not available
URL: <http://lists.puredata.info/pipermail/pd-dev/attachments/20080219/08dbb0c5/attachment.jpg>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: max_mysql_binary_text.jpg
Type: image/jpeg
Size: 188827 bytes
Desc: not available
URL: <http://lists.puredata.info/pipermail/pd-dev/attachments/20080219/08dbb0c5/attachment-0001.jpg>