[PD] SQL Object Model was: [psql] object hand-holding

[Hans-Christoph Steiner]

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


-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.puredata.info/pipermail/pd-list/attachments/20071212/a166a652/attachment.htm>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: sql-interface.pd
Type: application/octet-stream
Size: 9919 bytes
Desc: not available
URL: <http://lists.puredata.info/pipermail/pd-list/attachments/20071212/a166a652/attachment.obj>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.puredata.info/pipermail/pd-list/attachments/20071212/a166a652/attachment-0001.htm>