[PD-dev] Comparing Max/MySQL with SQLdb

Jamie Bullock jamie at postlude.co.uk
Wed Feb 20 13:41:05 CET 2008


Hi,

On Tue, 2008-02-19 at 17:49 -0600, Mike McGonagle wrote:

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

Why didn't you just install MySQL? it takes about 5 minutes if you have
a decent internet connection.

> 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 don't really understand the reasoning here. There are Pd solutions
already in use also, but we are choosing not to follow them because we
think we can do better. However, I do think [mysql] has a few things we
can learn from (see below).

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

As I mentioned in my off-list email, I think this workaround is
something we can and should avoid. I.e. I believe it is possible to just
have one inlet, and I think we should try to implement this.

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

The Max [mysql] results aren't tagged.

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

Personally don't think either piece of information is particularly
useful, and I would prefer to not have a middle outlet.


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

LOAD_FILE() is a MySQL function. It gets executed by the server, not the
[mysql] object. 

> 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

Correct! [mysql] just passes the query through to the server.

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

[mysql] is multithreaded, and buffers any incoming messages if the
database thread is busy. IOW, it does buffer the queries if required,
but there is no 'store and execute' mechanism e.g. 'execute on bang'.
IMO, this is a good design.

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

IMO, the most important feature of the separation between SQLdb and
SQLquery is nothing to do with needing to send "connect" messages or
whatever for each object, but rather to do with the advantages of
decoupling the connection and query 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? 

No, the result from each query contains 0 or more lists.

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

We get a bang to the right outlet per result set just like [psql], and
(I believe) [SQLquery]...

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

I think the only aspect of it that we should be concerned about copying
is the fact that no workaround is required to delimit queries - i.e. no
'addsemi' or 'sql'/'sqlend' or anything like that. This is worth
copying!


best,

Jamie

-- 
www.postlude.co.uk





More information about the Pd-dev mailing list