You are looking at historical revision 9077 of this page. It may differ significantly from its current revision.

Database Interface Discussion

Proposed Interface

<procedure>(dbi

connect TYPE #!key FILE PORT USER PASS etc)</procedure> : Connect to a database. TYPE is a symbol, like mysql, postgres, sqlite, etc. Returns a connection object.

Possibly put the type, file, and port info into a URI-type string as JDBC does? That would make the whole thing a lot more extensible; it would be nice to do it both ways, having the separate keyword parameters also. -- vincent

<procedure>(dbi

query CONNECTION SQL)</procedure> : Query the database (see the mysql egg)

<procedure>(dbi

num-rows CONNECTION)</procedure> : Return the number of rows produced by the last query (see mysql)

<procedure>(dbi

fetch-row CONNECTION)</procedure> : Return the next row (see mysql).

<procedure>(dbi

query-fold CONNECTION PROC SQL SEED)</procedure> : fold

<procedure>(dbi

query-map CONNECTION PROC SQL)</procedure> : map

<procedure>(dbi

query-for-each CONNECTION PROC SQL SEED)</procedure> : for-each

<procedure>(dbi

insert-id CONNECTION)</procedure> : Returns the ID generated by the last insert statement.

Instead of the query-fold, query-map and query-for-each procedures I'd prefer just returning a SRFI-40/SRFI-41 stream using promises that advance the cursor when forced. This way you don't restrict the user to these three procedures, but he can use the full stream library to iterate over the result set. -- Peter Bex

I'd like to see support for prepared statements. Especially in transactional environments, on some DBMSs they can result in big performance wins. And a DBMS that doesn't support them can easily implement stubs (e.g., prepare creating a record that holds the original string). Also, another vote for streams. -- vincent

Open issues

Row Representation

How are rows returned from a query represented?

Ozzi proposes either an association list or a plain list.

Plain list or vector would be my choice. There should be a separate way of getting the field names. Also, there's the issue of how field values are represented. For those data types that correspond to Scheme values, the obvious conversion would be fine; for other datatypes it may not be so obvious.There also needs to be a blob API, so that one can read a piece of a blob without having to ship the whole thing over the network. -- vincent

Null Representation

How are null values represented? nil sounds fine to me. And yes, I recognize that SQL null and Scheme nil have different semantics. -- vincent

Return values

What should be returned from query functions. For example, could we return the insert id of a row instead of having a separate function for that? How about the number of rows in a result?