On Dec 11, 2007, at 4:20 PM, Mike McGonagle wrote:
(As the other thread seemed to diverge greatly in topic, I figured this might be a time to branch off and discuss what we are trying to implement)
So, can we take a step back and figure out exactly what we are doing? It seems that even within the thread itself, we have changed the Object Model a LOT... From what I have read, this is what I now understand the model to be...
[mySQL-Connection id-symbol <connection info>] <-- a PD object representing connection
[SQL-Query id-symbol <sql statement>] <-- an SQL query object
The first object, the Connection, takes a symbol that identifies itself to PD, and optionally the connection info. The second takes a symbol linking it to a Connection object (this linking can also be changed to point to another connection using a control message), and an optional SQL statement for Placeholder Inlet creation. This method will "bind" those inlets into the SQL statement. If there is no SQL statement, no binding is allowed using that object, and all SQL come to a cold inlet, using PD messages (variable stuff can be put into the statement via PD's $ mechanism).
Sounds good, but I don't think we need the 'id-symbol' in the query, the results would just be feed from the query's outlet to the connection's inlets.
The connection object responds to the following
open/connect - to open a file (in an embedded database) or connects to server
close/disconnect - close the file or breaks the connection
As the connection object can represent any database, its named <dbname-connect>. A MySQL database would be [MySQL-connect ...], sqlite would be [sqlite-connect ...], etc. A connection object provides a single outlet to provide status about the connection (similar to a [netsend] object). There is a single inlet used to control the connection object.
I think the original names for the connection/database objects are good: psql, sqlite, mysql. Then for queries, there could be a generic object. That generic object wouldn't need to understand the SQL, so it should be able to handle the variants. The only thing it needs to understand is the placeholder stuff. Then for the various databases, we do placeholder translation where necessary (this is how things like Perl:DBI handle it, AFAIK).
[sql_query] - (generic SQL constructs)
Then if need be:
[psql_query]
[mysql_query]
[sqlite_query]
The query object responds to the following
buffer - to change the size of the SQL input buffer
results - to change the size of the results buffer
As long as the "buffer" and "results" messages are optional, then it's fine by me. If possible, things should work without having to set these, then these would be for performance tweaks.
bang - submits the initial query, and outputs a result set for each bang
clear - clears the SQL buffer
Sounds good.
addsemi - appends a semicolon to the SQL buffer
addcomma - appends a comma to the SQL buffer
adddollar - appends a dollar sign to the buffer (do we need this?)
If you want to be able to add the string $1 to a database, then we need the adddollar message.
If the query is created with an embedded statement, any placeholders will generate an inlet. No cold inlet for SQL will be created.
Sounds good.
If the query does not have an embedded statement, it will provide an arbitrary inlet that accepts SQL to process.
If the connection object has the arbitrary SQL cold inlet, I am not sure we need the same functionality in the query object, but I could be wrong.
The query object has 3 outlets, the first outputs a list for each result set (on each subsequent bang, very much like a [textfile] object), the second will outlet a float representing the ROWID of the last insert statement, and the third outlet indicates the status of the query, with a bang indicating the end of the result sets, a symbol or a list is a status message from the database.
query objects would just be a handy way to generate queries to send to the connection object. So the outlets would be designed to plug straight into the inlets of the connection object. Then I think the above outlets you describe would be on the connection object, which returns the results.
*************************
Does this sound about right? I would like to have a clear outline before I start coding anything
Indeed, it's time for some coding! I am thinking of coding up a version of the [sql_query] object if no one has any objections. Ultimately, I think we should decide on a name for a common library, then start building it out as a unified library of database things. First, let's get a working interface that we all like :).