[GSOC] Database API draft proposal

[Piotr Szturmaj]

Christian Manning wrote:
> On 03/04/2011 14:42, Piotr Szturmaj wrote:
>> Fawzi Mohamed wrote:
>>> Well the comments in there are what is important, and will need to be
>>> specified better IMHO.
>>>
>>> The most important part in my opinion is how one chooses to represent a
>>> record.
>>> A big design choice is if the various fields are defined at compile time
>>> or at runtime.
>>> Also how does one add special behavior to a record? Do you use a
>>> subclasses of the generic record type (as ruby does for example)?
>>
>> I'm working on DB API for few months in my spare time. I'm delayed that
>> much by my other projects. Please take a look at my ideas:
>>
>> http://github.com/pszturmaj/ddb
>>
>> Documentation:
>> http://pszturmaj.github.com/ddb/db.html
>> http://pszturmaj.github.com/ddb/postgres.html
>>
>> In my code, row is represented using struct DBRow!(Specs...). Fields may
>> be known at compile time or not. DBRow besides base types, may be
>> instantiated using structs, tuples or arrays. Untyped row (no compile
>> time information) is DBRow!(Variant[]).
>>
>> Typed rows are very useful, for example you don't need to manually cast
>> columns to your types, it's done automatically, e.g.:
>>
>> auto cmd = new PGCommand(conn, "SELECT typname, typlen FROM pg_type");
>> auto result = cmd.executeQuery!(string, "typName", int, "len");
>>
>> foreach (row; result)
>> {
>> // here, row DBRow subtypes
>> // a Tuple!(string, "typName", int, "len")
>> writeln(row.typName, ", ", row.len);
>> }
>>
>> What do you think? :)
>
> I was going to reply with a link to your work but you beat me to it.
> I think it's a great design and incorporating it or something similar
> into the API may be the way to go.

Thanks. At this time, you can write an interface for MySQL, SQLite or 
other relational databases, using the same DBRow struct. Naming of 
course may be changed to DataRow, Row or other, depending on the choice 
of community.

In regards of base interfaces like IConnection or (semi-)abstract class 
DBConnection, I think we should have common API for all clients, but 
only to some extent. There are many features available in some database 
servers, while not available in others, for example OIDs (object 
identifiers) are fundamental thing in PostgreSQL, but they simply don't 
exist in MySQL. So, PGCommand would give you information on 
lastInsertedOID, while MySQLCommand would not.
This is also proven in ADO.NET, where each client is rarely used with 
common base interface, because it blocks many of its useful features.

I think base interface should be defined only after some of the most 
popular RDBMS clients are finished. Also interface should be choosen to 
cover the most featured/advanced database client. This is why I started 
with PostgreSQL, as its the most powerful open-source RDBMS. If base 
interface will cover it, it will also cover some less powerful RDBMSes.