Database interface design - was how to build up the library.

[Alex Rønne Petersen]

On 07-10-2011 16:11, Steve Teale wrote:
> I'm thinking that for each database that gets covered there will
> need to be two modules, like:
>
> etc.c.mysql
> etc.mysqld
>
> The etc.c.xxx modules would be completely different between databases -
> just translations of the necessary header files. The xxxd modules
> would be as similar as possible consistent with allowing the strong
> points of individual database systems to show through, and the weak
> points of others to be avoided. I don't think all should be reduced
> to some LCD.
>
> These modules should attempt to make a good range of capabilities
> available to the D programmer, but they not have to be all encompassing.
> Those users who want to do really fancy things can drop back to the
> low-level interface. They should probably have the following capabilities:
>
> 1) Establishing and closing database connections.
>
> 2) The capability to execute literal SQL statements - execSQL()
> if you like. Some of these will generate result sets, of which more below.
>
> 3) The capability to create prepared statements with in and out
> parameters and association of the parameters with a source, and
> then to execute these. This breaks down into several components/
> capabilities, which could be labeled:
>
> 3a) createPreparedStatement() - marshal parameters, associate them
> with a sourceand have the server prepare the statement.
>
> 3b) execStatement() - for those SQL statements that don't have a
> result set.
>
> 3c) execStatementIncremental()/getNext() - execute the prepared statement,
> then fetch the result rows one at a time into some kind of result set.
>
> 3d) execStatementAll() - execute the prepared statement and get all
> the resulting rows into some kind of result set.
>
> 3e) (maybe) execScalar() - do the whole sequence prepare, execute,
> and get a single value result set placed into a D variable.
>
> 3f) (maybe) execStoredProcedure() - another 'do the whole thing'
> capability TBD.
>
> It is when we come to the nature of the result sets that there is
> likely to be dissent. I favor arrays of structs, but we should
> probably do arrays of arrays of variants too for those situations
> where structures can't be sorted out at compile time. There needs
> to be some symmetry between what is used here, and what can be used
> as input to operations such as a prepared insert. It is of course
> vital that this part of each middle layer produce exactly the same
> kind of results. Otherwise the top layer could become difficult.
>
> On top of this set of two modules for each database, I envisage a
> higher-level module - etc.dbops - that provides a bunch of convenience
> templates for various common database operations, spanning the databases.
> Once the middle layer is in place, this top layer should be relatively
> easy to implement. It should be noted though that all these database
> wrappers will be monstrously difficult to test.
>
> I am at the point with MySQL where I can get the result of a plain
> old query into an array of a checked structure type. I have the
> prepared statement stuff, and know how the result will be created from
> a prepared query (the execStatementAll() case) - I just have to plow
> through a bit more binding and fetching.
>
> This is probably rather general and vague, but I would like to get
> comments so we can iterate toward a practical design.
>
> Thanks
> Steve

Just a note on module naming: mysqld is misleading - it could mean 
"mysql daemon". I would recommend just calling it mysql and calling the 
C interface etc.c.mysqlc or something like this.

- Alex