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