[std.database]

[Steve Teale]

Here's a sketch of an interface. This is based on my experiments with 
MySQL, and as such it is probably mid-level, and not a top level covers-
all interface.

Hopefully it will create a number of discussion points.

// Can interfaces include template functions???
interface SQLDBConnection
{
   @property Handle handle();
   Handle connect(string host, string user, string password,
                  string database = null);
   T getProperty(T)(string name);
   T getProperty(T)(int id);
   void setProperty(T)(T property, string name);
   void setProperty(T)(T property, int id);
   Handle disconnect();
}

// There should possibly be a connection pool as well, and that
// should handle the RAII aspects of connections. Handle is an
// alias to suit the database system.

interface Raw
{
   // Delete, insert, update, createXXX, and the like - no result set
   rowcount_t exec(string sql);
   // Select and such with result set - result set buffered to the
   // client to provide a Random Access Range of Rows
   rowcount_t execResultSet(string sql);
   // Select and such with result set - prepares for sequential
   // processing of an Input Range of Rows
   void execSequence(string sql);
  
    
   // Do the range defining methods need to be in the interface?
}

enum ParamDirection
{
	ParamIn,
   ParamOut,
   ParamInOut
}

interface Prepared
{
   void createParam(T)(ref T target, ParamDirection pd);
   void createInParams(T...)(ref T args)
   void createOutParams(T...)(ref T args)
   void createVariantParam(ref Variant v, ParamDirection pd);
   void createVariantParams(T...)(ref Variant[] va, T);

   // If D type arrays are the bound type, it's likely that some
   // updating of the bindings will be required when a new value
   // is set, since a.ptr and a.length may change. Otherwise
   // these operations are no-ops.
   void updateInputParam(T)(ref T target);
   void updateInParameters(T...)(ref T args);
   void updateInArray(Variant[]);
   void updateInStruct(S)(ref S s);

   // Create a set of in parameters from an array of Variants
   void setInArray(ref Variant[] va);
   // Create a set of out parameters from an array of Variants
   void setOutArray(ref Variant[] va);

   // Initialize an array of out Variants to types appropriate for a query
   void getTypesForArray(ref MyVariant[] va);

   // Create a set of input params from a struct
   void setInStruct(S)(ref S s) if (is(S== struct));
   // Create a set of out params from a struct
   void setOutStruct(S)(ref S s) if (is(S== struct));

   prepare(string sql);
   // Delete, update, createXXX, and the like - no result set
   // returns rows affected;
   rowcount_t exec();
   // Select and such with result set - result set buffered
   // to the client to
   // provide a Random Access Range of Rows
   rowcount_t execResultSet();
   // Select and such with result set - prepares for sequential
   // processing of an Input Range of Rows
   void execSequence();

   // A composite operation prepare, bind, and execute a statement
   // to get a single column value into a D variable.
   // execScalar(T)(ref T target);
   
   // Do the range defining methods need to be in the interface?
}

interface Row	// mmm bit close to Raw
{
   // Get the values from a fetched row into a D struct
   void rowToStruct(S)(ref S s) if (is(S == struct));
   // Get the values from a fetched row into an array of Variants
   void rowToStruct(ref Variant[] va);

   // Get a column value by index into a D variable from the current row
   T getValue(T)(out T target, int index, out bool isnull);
   // Get a column value by index into a D variable from the current row
   T getValue(T)(out T target, string colName, out bool isnull)

   string toString(uint index);
   string toString(string colName);
}

interface ResultSet
{
   // Get the entire result set into an array of structs/Variants
   S[] getAllRows(S)(ref S dummy) if (is(S == struct));
   Variant[] getAllRows();

   // This should be automated where possible
   void free();
}

I can currently do most of this for MySQL, and what I haven't done
is mostly rehashing of what I have.

As an example of how level 2 interfaces may differ from the top-level one
is that in my implementation, chunking is supported for transfer and 
disposal of large objects - either auto-chunking, or chunking via a 
delegate. That stuff is not shown here.

Steve