As in the initial discussions on database interfaces, I am still of the view that such support should be provided at three levels. I also suggest that we adopt a proposal that was hinted at in the initial discussions, and think in the longer term of having components that are devoted to SQL, and those that will apply to a broader view of what comprises a database. In other words we should have std.sql and std.database. (I use std purely for illustration.)

It's possibly worth mentioning that Microsoft had similar intentions years ago when they introduced OLE DB - a generalized interface to data sources. OLE DB is now being dumped. I should make it clear that what I'm working on at present are modules for xxx.sql. It has been pointed out in several postings that I don't seem to have any clear idea of the design requirements for std.database, and I admit that this is the case, though I don't think I'm alone in this. The initial discussions did not reach any well defined conclusions.

If there is anyone who wants to leap into the std.database role, please volunteer. It could take me some time to get the SQL stuff in some sort of order. ODBC is a set of dialects, and the wire protocol definitions are not easy to get up-to-speed on.

It seems that given licensing considerations, D support for SQL will probably need to be distributed. Some C header translations will have to go in Deimos - e.g. MySQL, some would probably be OK in Phobos, e.g. SQLite, and _maybe_ ODBC, (Would you believe that some driver and driver manager implementations have attached GPL wording to the ODBC header files!) and PostgreSQL.

Having done quite a bit of work on implementations for MySQL (C API and wire protocol), and SQL Server (ODBC from Linux and Windows), I'm beginning to have some fairly clear ideas about what D components for dealing with SQL databases, using SQL directly, could look like. More detail on that separately.

An immediate point that arises is that because some of the D 'header files' are derivative, and the associated D components need to link with external libraries, we're missing a place to put such components. They can't go in Phobos because of the licensing and linking considerations, and they can't go in Deimos because that is for translations of C header files only.

At the same time, It seems to me that they should follow the same quite tight implementation pattern as those that can potentially go in Phobos. Someone going from say the C API version of the MySQL interface should be able to use the same, or closely similar code as when using the wire-protocol version.

So one question is where should such implementations go?

Another questions relates to the definition of interfaces at module level. We have interfaces that go hand-in-hand with classes built in to the language. But if I wanted to say that two sql interface modules had the same 'interface', how could this be formalized. Could it be done using contracts? I'd welcome suggestions on this.

Steve

Steve Teale Wrote:

> So one question is where should such implementations go?

github?

> Another questions relates to the definition of interfaces at module level. We have interfaces that go hand-in-hand with classes built in to the language. But if I wanted to say that two sql interface modules had the same 'interface', how could this be formalized. Could it be done using contracts? I'd welcome suggestions on this.

It can be done using concepts: a template which instantiates to a set of static asserts about what you want.

On Fri, 25 Nov 2011 12:53:36 -0500, Kagamin wrote:

> Steve Teale Wrote:
> 
>> So one question is where should such implementations go?
> 
> github?

Well, probably yes, but that sounds a bit like "if you build it they will come", which doesn't always work.
> 
>> Another questions relates to the definition of interfaces at module level. We have interfaces that go hand-in-hand with classes built in to the language. But if I wanted to say that two sql interface modules had the same 'interface', how could this be formalized. Could it be done using contracts? I'd welcome suggestions on this.
> 
> It can be done using concepts: a template which instantiates to a set of static asserts about what you want.

Is that what we do with Ranges?

Steve Teale Wrote:

> > It can be done using concepts: a template which instantiates to a set of static asserts about what you want.
> 
> Is that what we do with Ranges?

Range concepts are boolean. There was a discussion on how to get detailed diagnostic if concept is not met, so that one would know what exactly is wrong. This is done with a sequence of static asserts.

On 10/9/2011 2:50 AM, Jacob Carlborg wrote:
> On 2011-10-08 19:00, Andrei Alexandrescu wrote:
>> 1. If we build a D wrapper for ODBC, then we allow people to write code
>> for any database that has an ODBC driver. This, assuming we commit to
>> ODBC as D's standard database interface, would complete the project.
>>
>> 2. If we want to go the route of "one std.database API with drivers for
>> each DBMS" and consider ODBC one of several DBMSs, then we need to
>> define our own driver architecture, write a few drivers ourselves
>> (including probably ODBC), and hope that people will add more drivers.
>> That's a larger project but it unties us from ODBC.
>>
>> 3. If we want to go the route of "similar but not identical specialized
>> APIs per database system" and consider ODBC again only one of the
>> database systems, then we need to define one specialized API per DBMS
>> and force users to essentially choose upfront what DBMS they'll use, and
>> code for it. It's an even larger project and I don't see obvious
>> advantages for it than less of a need for upfront design.
>>
>>
>> Andrei
>
> I would say that we declare a high level interface for database drivers.
> std.database can use this interface to connect to all databases there
> are drivers for.
>
> We then provide driver implementations for this interface for the
> databases we choose to support. It should also be possible for a user to
> create his/her own driver implementation for a database we haven't yet
> implemented or choose not to implement.
>
> Driver implementations could be:
>
> * MySQL/MariaDB
> * PostgreSQL
> * SQLite
> * ODBC
> * Oracle
> * SQL Server
>
> The smart thing would probably be to implement ODBC as the first
> database driver since it would allow most of the common databases to be
> used.
>
> But I don't want ODBC to be the only driver. I have some bad experience
> with ODBC from work. It can't handle multiple result sets and it's not
> very good at handle invalid Unicode characters. This might not be true
> for ODBC in general but it's a problem we have with the implementation
> we currently use. Also, ODBC adds an extra layer between the application
> and the database.
>

One thing I notice is everyone seems to only be Targeting Relational Databases. Any plans to support Flat, Object, Key Value, Hierarchical, or Network Model systems? It would be nice to have at least specification support for systems like membase(memcache), hbase, vertica, csv files, and similar systems.

On Friday, December 02, 2011 16:02:59 Hans Uhlig wrote:
> On 10/9/2011 2:50 AM, Jacob Carlborg wrote:
> > On 2011-10-08 19:00, Andrei Alexandrescu wrote:
> >> 1. If we build a D wrapper for ODBC, then we allow people to write
> >> code
> >> for any database that has an ODBC driver. This, assuming we commit to
> >> ODBC as D's standard database interface, would complete the project.
> >> 
> >> 2. If we want to go the route of "one std.database API with drivers
> >> for
> >> each DBMS" and consider ODBC one of several DBMSs, then we need to
> >> define our own driver architecture, write a few drivers ourselves
> >> (including probably ODBC), and hope that people will add more drivers.
> >> That's a larger project but it unties us from ODBC.
> >> 
> >> 3. If we want to go the route of "similar but not identical
> >> specialized
> >> APIs per database system" and consider ODBC again only one of the
> >> database systems, then we need to define one specialized API per DBMS
> >> and force users to essentially choose upfront what DBMS they'll use,
> >> and
> >> code for it. It's an even larger project and I don't see obvious
> >> advantages for it than less of a need for upfront design.
> >> 
> >> 
> >> Andrei
> > 
> > I would say that we declare a high level interface for database drivers. std.database can use this interface to connect to all databases there are drivers for.
> > 
> > We then provide driver implementations for this interface for the databases we choose to support. It should also be possible for a user to create his/her own driver implementation for a database we haven't yet implemented or choose not to implement.
> > 
> > Driver implementations could be:
> > 
> > * MySQL/MariaDB
> > * PostgreSQL
> > * SQLite
> > * ODBC
> > * Oracle
> > * SQL Server
> > 
> > The smart thing would probably be to implement ODBC as the first database driver since it would allow most of the common databases to be used.
> > 
> > But I don't want ODBC to be the only driver. I have some bad experience with ODBC from work. It can't handle multiple result sets and it's not very good at handle invalid Unicode characters. This might not be true for ODBC in general but it's a problem we have with the implementation we currently use. Also, ODBC adds an extra layer between the application and the database.
> 
> One thing I notice is everyone seems to only be Targeting Relational Databases. Any plans to support Flat, Object, Key Value, Hierarchical, or Network Model systems? It would be nice to have at least specification support for systems like membase(memcache), hbase, vertica, csv files, and similar systems.

Well, I wouldn't expect them to use the same API, but it's stuff like that why it's been suggested suggested that we make it std.sql instead of std.database.

- Jonathan M Davis

Le 03/12/2011 01:02, Hans Uhlig a écrit :
> 
> One thing I notice is everyone seems to only be Targeting Relational Databases. Any plans to support Flat, Object, Key Value, Hierarchical, or Network Model systems? It would be nice to have at least specification support for systems like membase(memcache), hbase, vertica, csv files, and similar systems.
> 

I don't think it's a good idea to put everything in the same bag. It
doesn't help at all and in the end, and it usually makes things more
complicated because there really is nothing in common between a flat
file and a relational database, or even a key-value store. I think it's
better to leave specific products like memcache or HBase to their own,
specific APIs, at least until there is an established standard.
It's not the role of a standard library to implement every product API
out there, especially in the NoSQL domain, where there is no established
standard at all.

> One thing I notice is everyone seems to only be Targeting Relational Databases. Any plans to support Flat, Object, Key Value, Hierarchical, or Network Model systems? It would be nice to have at least specification support for systems like membase(memcache), hbase, vertica, csv files, and similar systems.

That is the reason why I originally suggested std.database is perhaps a wrong place to cover all use-cases.

Second, there will be native (pure D) interfaces to various data sources, and also there will be bindings to C libraries with the same purpose.

Third, handling of relational databases will not be the same as with non- relational.

Fourth, SQL - there are many people who like mister C.J.Date think SQL is not good for working with relational databases. So I would strongly recommend separation of SQL from the rest because there are other query languages around, including C.J.Date's "Tutorial D".

IMHO, the center building block should be the DataSource. DataSource (or DateSet perhaps is a better name) should be ANYTHING that can be represented as collection of tuples.

Examples:
1) directory list - it is a data source representing the content of a file-
system directory.
2) result of an SQL query
3) *ANY tuple* is a DataSet with a single tuple as an element.
4) *ANY collection* can also be seen as DataSet (if the key is part of the
tuple)

With this kind of abstraction std.data (that is my preferred name for this package) would probably be the most powerful data abstraction layer I have seen so far. With std.loader we can even have pluggable drivers for various types of DataSources ...

Regards

Am 03.12.2011, 13:07 Uhr, schrieb Dejan Lekic <dejan.lekic@gmail.com>:

>
>> One thing I notice is everyone seems to only be Targeting Relational
>> Databases. Any plans to support Flat, Object, Key Value, Hierarchical,
>> or Network Model systems? It would be nice to have at least
>> specification support for systems like membase(memcache), hbase,
>> vertica, csv files, and similar systems.
>
> That is the reason why I originally suggested std.database is perhaps a
> wrong place to cover all use-cases.
>
> Second, there will be native (pure D) interfaces to various data sources,
> and also there will be bindings to C libraries with the same purpose.
>
> Third, handling of relational databases will not be the same as with non-
> relational.
>
> Fourth, SQL - there are many people who like mister C.J.Date think SQL is
> not good for working with relational databases. So I would strongly
> recommend separation of SQL from the rest because there are other query
> languages around, including C.J.Date's "Tutorial D".
>
> IMHO, the center building block should be the DataSource. DataSource (or
> DateSet perhaps is a better name) should be ANYTHING that can be represented
> as collection of tuples.
>
> Examples:
> 1) directory list - it is a data source representing the content of a file-
> system directory.
> 2) result of an SQL query
> 3) *ANY tuple* is a DataSet with a single tuple as an element.
> 4) *ANY collection* can also be seen as DataSet (if the key is part of the
> tuple)
>
> With this kind of abstraction std.data (that is my preferred name for this
> package) would probably be the most powerful data abstraction layer I have
> seen so far. With std.loader we can even have pluggable drivers for various
> types of DataSources ...
>
> Regards
>

Sounds interesting until you talk about collections. This puts more interfaces on every collection (Range + DataSet), is that good? On the other hand I remember how nice the latest GUI toolkits work with data sources for table views, edit boxes, labels and so on. So I also see a long term benefit of this in terms of software architecture. Maybe you should do a sample implementation we can evaluate. Some CSV, two different container types, the directory listing and how it works with the data set. Also what effect would this have on how foreach iterates different collection types?

   sclytrack

Here is an ORM that works with MySQL
https://www.kellermansoftware.com/p-47-net-data-access-layer.aspx