I attached an updated Pd sketch of all this stuff. Hopefully it's
clear.

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

.hc

Access to computers should be unlimited and total. - the hacker ethic