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
|