SDI-Squeak database interface

Pennell, David DPennell at quallaby.com
Sun May 21 13:15:35 UTC 2000


Stefan wrote:

> Speaking about commerical use of Squeak, it needs to access 
> relational 
> databases.  Currently there's no real way to do this.  I 
> know, there's 
> something to access mysql, but the code, well, looks 
> complicate and very 
> mysql specific and the license prohibits commercial use.
> 
> So I started to do my own driver, at the moment for mysql but 
> with other 
> relational databases in mind.  I'll use the normal Squeak 
> license for my 
> code and I'd like to discuss the best interface here.  
> Currently, I used a 
> combination of Java's JDBC (which is very similar to ODBC) 
> and VisualWork's 
> EXDI.
> 
> These are the public methods and classes
> 
> Connection
>    close
>    isConnected
>    executeCommand: sqlStatementString
>    executeQuery: sqlStatementString
> 
> Connection class
>    getConnection: connectString user: userString password: 
> passwordString
> 
> ResultSet
>    close
>    next
>    atEnd
>    upToEnd
>    template:
>    template:mapping:
> 
> You'll ask the Connection class for a connection instance.  The 
> connectString is similar to what JDBC needs and must start 
> with a database 
> identifier, like "mysql:" or "oracle:".  Each driver, 
> MySqlConnection for 
> example, registers for an identifier and gets then passed the 
> strings with 
> that prefix stripped, expecting whatever format is needed.  
> MySQL needs 
> "host:port/database" for example.

Aren't these URL's in JDBC?  Should Connection be DBConnection
or SQLConnection?
 
> You can then use #executeCommand: and #executeQuery: which 
> answer either a 
> number or a resultset instance.  The first is for simple 
> statements like 
> INSERT, the latter for SELECT-like things.
> 
> The result set allows one to retrieve rows from the database. 
>  Normally, 
> arrays with ints and strings are returned.  You can use

Perhaps an extensible factory framework to support other types,
like dates, floats, your own types in blobs, etc.
 
> #template:  to 
> provide a prototype object whose instance variables are then filled, 
> similar to VisualWorks' way of doing it.  However, instead of 
> that stupid 
> idea, to use "Object new" to omit variables, I use a mapping.
> 
> I'll probably rename the ResultSet to ResultStream and 
> inherit it from 
> Stream, actually it's a PeekableStream but not a 
> PositionableStream.  I'll 
> also add Meta classes which can be used to query the 
> connection for tables 
> and tables for columns and columns for types and other information.

How about DBStream? (ala FileStream, and our future network streams,
this sounds like a good candidate for using correspondents with its
decoupling of stream protocol from external sources/sinks)
 
> The connection filters its sql commands through a method 
> (nativeSQL:) which 
> will allow me to introduce platform independent escapes like JDBC.
>
> Then, I'd like to add support for prepared statements, but 
> I'm uncertain 
> how to do this best.  One could either create a 
> PreparedStatement object as 
> JDBC does or to define new methods for Connection
> 
> prepareCommand: sqlStatementString
> prepareQuery: sqlStatementString
> executePrepareCommand: data
> executePreparedQuery: data
> 
> I'd then use the :1 or :name notation for arguments instead of the 
> "official" ? notation.  The first is - I think - reserved for stored 
> procedures, but it was really nice to use with VisualWorks.

I would rather see an SQLStatement base object.  Then SQLConnection
could have one method to execute the statement (#do: anSQLStatement
or #execute: anSQLStatement).  SQLStatement might store the string 
as a simple string and subclasses could use more elaborate structures 
for separating platform independent abstractions and/or bind variables.
This keeps you from having to parse every command passed to the 
connection.  If you want to provide support for a string substitution 
style of statement, you can always add a conversion method to SQLStatement.

I have been thinking that it would also be useful to provide a separate
SQL layer from a DB interface (provider?) layer.  I haven't had much
time to ponder this though.  I started tinkering with using Oracle Call
Interface via FFI a couple of weeks ago.  I got far enough to figure out 
that that route will be painful - especially since I've never coded OCI 
before.

-david





More information about the Squeak-dev mailing list