On Sun, 09 Oct 2011 20:31:35 -0300, Sean Kelly <sean@invisibleduck.org> wrote:

> On Oct 9, 2011, at 3:56 PM, Andrei Alexandrescu wrote:
>
>> On 10/9/11 5:31 PM, Walter Bright wrote:
>>> On 10/9/2011 5:28 AM, Piotr Szturmaj wrote:
>>>> 1. I think that we should not design this API using the least common
>>>> denominator
>>>> approach. This is to not limit some databases. For example PostgreSQL
>>>> has many
>>>> great features not available in MySQL. That's why I started with
>>>> postgres in my
>>>> ddb project. I think DB API should be designed to support the most
>>>> featureful
>>>> databases and those that have less features may be easily adapted to
>>>> that API.
>>>
>>>
>>> Haven't common denominator designs been more or less failures in at
>>> least one category - gui libraries?
>>
>> A common database interface is not a common denominator API; more like the opposite. This is not difficult because most differences across database systems lie in their SQL, which is strings from D's perspective.
>
> Assuming that by "database" you mean SQL.  Pretty fair assumption, though NoSQL databases (which cover a broad range of designs since there's no standard language yet for key-value DBs, etc) are rapidly gaining popularity.  I almost wonder if the base type should be named SqlDatabase instead of Database.

There is a standard language defined for NoSQL, namely UnQL:
http://wwww.unqlspec.org/display/UnQL/Home

Roald

On Sat, 08 Oct 2011 17:19:02 +0100, Andrei Alexandrescu <SeeWebsiteForEmail@erdani.org> wrote:

> On 10/8/11 8:36 AM, Adam Burton wrote:
>>> I'd also like to get a feel for the magnitude of the task, so I'd like to
>>> ask what database systems you think should be supported.
>> mysql, postgresql, sqllite are the 3 I am aiming at in my personal
>> implementation.
>
> I had lunch yesterday with a database expert and discussed the matter with him. He advised that we take a driver-oriented approach in which we define a common API for all databases (modeled at high level after e.g. JDBC to reuse that accumulated experience)

Just a small note, because it's on my mind currently, with regards to JDBC.  This is slightly OT, but pertains to the comment about how we should model std.database.

JDBC has Statement and PreparedStatement objects, the latter allows you to 'bind' parameters, which is great.  But, it requires you actually 'prepare' them as well.. I am not 100% certain, but I believe similar C/C++ code can bind parameters without actually 'preparing' the statement for reuse.  We use this in cases where we do not re-use the statement, and want to avoid the excess work of preparing it for re-use.  In addition we bind parameters to avoid getting unique query strings, which (I believe) get cached by SQL server.. resulting in thousands of unique queries in the cache, slowing things down.

So, my comment is simply to say, assuming I am not talking rubbish, make sure the design allows for binding/not binding parameters with prepared/not-prepared statements.

This came up recently because some of our JDBC code was taking 12 minutes to do a select, due to using a PreparedStatement, and changing to a Statement reduced this to <10 sec.  But, it means the select is now unique, and we will be introducing more cached queries.. anyone know how to avoid this using Statement?

R

-- 
Using Opera's revolutionary email client: http://www.opera.com/mail/

> 
> You can glean all needed information from the resultset after having issued the query.
> 
>> It's probably true to say that the syntax/semantics of the interface will suck slightly more in the Variant case than in the struct case.
> 
> That's a given. The suckiness won't come, however, in the form of additional trips to the database.
> 
> 
> Andrei

Maybe in some cases. But at least with MySQL you have to bind before you fetch, and you don't have the meta-data about the columns until after the fetch, so you have to insert a call to mysql_stmt_result_metadata() to set up the Variant types before you bind.

I also discovered after some time wasted this morning that the MySQL struct used for date/time is too big for Variant. I had to define a MyVariant taking that struct into consideration. Is Variant supposed to throw if you initialize an instance with a struct that is bigger than maxSize? It didn't - I just got mysterious segfaults when fetch tried to store the result via a null buffer pointer.

Anyway, that's working now.

From the comments we're getting, it looks like we need to head toward a generic std.sql (or std.database) that provides basic functionality for most databases. This would be implemented over more capable modules for individual databases that covered the differentiating features as well as what would be needed for the generic case. Kind of between your options 2 and 3.

Steve

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

Surprising. I read a research paper about a proposed language just a few months ago. I wonder if this is by the same guys.

Sent from my iPhone

On Oct 10, 2011, at 12:05 AM, "Roald Ribe" <rr@pogostick.net> wrote:

> On Sun, 09 Oct 2011 20:31:35 -0300, Sean Kelly <sean@invisibleduck.org> wrote:
> 
>> On Oct 9, 2011, at 3:56 PM, Andrei Alexandrescu wrote:
>> 
>>> On 10/9/11 5:31 PM, Walter Bright wrote:
>>>> On 10/9/2011 5:28 AM, Piotr Szturmaj wrote:
>>>>> 1. I think that we should not design this API using the least common
>>>>> denominator
>>>>> approach. This is to not limit some databases. For example PostgreSQL
>>>>> has many
>>>>> great features not available in MySQL. That's why I started with
>>>>> postgres in my
>>>>> ddb project. I think DB API should be designed to support the most
>>>>> featureful
>>>>> databases and those that have less features may be easily adapted to
>>>>> that API.
>>>> 
>>>> 
>>>> Haven't common denominator designs been more or less failures in at least one category - gui libraries?
>>> 
>>> A common database interface is not a common denominator API; more like the opposite. This is not difficult because most differences across database systems lie in their SQL, which is strings from D's perspective.
>> 
>> Assuming that by "database" you mean SQL.  Pretty fair assumption, though NoSQL databases (which cover a broad range of designs since there's no standard language yet for key-value DBs, etc) are rapidly gaining popularity.  I almost wonder if the base type should be named SqlDatabase instead of Database.
> 
> There is a standard language defined for NoSQL, namely UnQL: http://wwww.unqlspec.org/display/UnQL/Home
> 
> Roald

I've just been looking at the documentation for the PostgreSQL C api. Wow!

It is so different from MySQL, and so clean. No out parameters from queries. That one is not going to be a problem.

Steve

On 10/10/11 7:01 AM, Steve Teale wrote:
>>
>> You can glean all needed information from the resultset after having
>> issued the query.
>>
>>> It's probably true to say that the syntax/semantics of the interface
>>> will suck slightly more in the Variant case than in the struct case.
>>
>> That's a given. The suckiness won't come, however, in the form of
>> additional trips to the database.
>>
>>
>> Andrei
>
> Maybe in some cases. But at least with MySQL you have to bind before you
> fetch, and you don't have the meta-data about the columns until after the
> fetch, so you have to insert a call to mysql_stmt_result_metadata() to
> set up the Variant types before you bind.

I'm confused. Isn't binding setting values to parameters prior to executing a query? That's a different thing from getting the row of a resultset.

So, you first prepare, bind, and execute a query. Then you call mysql_stmt_result_metadata() to get the number of columns in the resultset and their individual types. At that point you get to allocate the Variant[] row appropriately. After that you're ready to iterate rows.

> I also discovered after some time wasted this morning that the MySQL
> struct used for date/time is too big for Variant.

That's a bug in std.variant. Large structs should be supported automatically by using an indirection and dynamic allocation.


Andrei

Am 10.10.2011 16:07, schrieb Steve Teale:

> interface SQLDBConnection

How do you support different database connection requirements. f.i.
a non default port number
What about special parameters only available on db system xxx ?
MySQL  : protocol etc.
PostGreSQL : loglevel, charSet etc.


// JDBC enables us to use..
String url = "jdbc:postgresql://localhost/test";
Properties props = new Properties();
props.setProperty("user","steve");
props.setProperty("password","teale");
props.setProperty("ssl","true");
Connection conn = DriverManager.getConnection(url, props);

atm I am not sure about the implementation of DriverManager, but I can imagine that DriverManager follows the prototype respective the  factory pattern.

If you like, have a look at my msg..  std.database design suggestion.
my 2 cents

Am 10.10.2011 17:09, schrieb Andrei Alexandrescu:
> That's a bug in std.variant. Large structs should be supported
> automatically by using an indirection and dynamic allocation.

I am curious, what about BLOBs ?

On Mon, 10 Oct 2011 10:09:34 -0500, Andrei Alexandrescu wrote:

> On 10/10/11 7:01 AM, Steve Teale wrote:
>>>
>>> You can glean all needed information from the resultset after having issued the query.
>>>
>>>> It's probably true to say that the syntax/semantics of the interface will suck slightly more in the Variant case than in the struct case.
>>>
>>> That's a given. The suckiness won't come, however, in the form of additional trips to the database.
>>>
>>>
>>> Andrei
>>
>> Maybe in some cases. But at least with MySQL you have to bind before you fetch, and you don't have the meta-data about the columns until after the fetch, so you have to insert a call to mysql_stmt_result_metadata() to set up the Variant types before you bind.
> 
> I'm confused. Isn't binding setting values to parameters prior to executing a query? That's a different thing from getting the row of a resultset.
> 
> So, you first prepare, bind, and execute a query. Then you call
> mysql_stmt_result_metadata() to get the number of columns in the
> result set and their individual types. At that point you get to allocate
> the Variant[] row appropriately. After that you're ready to iterate
> rows.

I was not making a big deal. I was just comparing operations with a struct to operations with an array of Variants. With a struct I can automate the construction of the OUT binding parameters before executing the query. If the user, with knowledge of the expected results cares to assign appropriate values to each array element in the Variant array, that can be done prior to execution also, and I guess it is not much more effort than defining the struct. But if the initialization of the array is to be automated, I first have to get the metadata. I presume that information comes from the server - perhaps it doesn't. But if it does, then in the Variant array case, there's an extra server call.

>> I also discovered after some time wasted this morning that the MySQL struct used for date/time is too big for Variant.
> 
> That's a bug in std.variant. Large structs should be supported automatically by using an indirection and dynamic allocation.

OK, I can work around it for now, since in a sense they are supported now. All I had to do was:

alias VariantN!(maxSize!(creal, char[], void delegate(),
                MYSQL_DATETIME ...)) MyVariant;

Steve