www.digitalmars.com         C & C++   DMDScript  

digitalmars.D - [std.database]

reply Steve Teale <steve.teale britseyeview.com> writes:
I use this title at Andrei's suggestion, and repeat his idea that it be used
as a prefix for discussions as we navigate toward a design. Unless there is
resistance to the idea, I will on the job of implementing whatever we decide
is appropriate. I am retired, and have the time to do it.

It seems that every man, and possibly his dog, has a private implementation
for at least a favorite DB, so we should have plenty of material to build on.

At this point I would like to get responses from those who feel they are
likely to contribute to the design through to completion.

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.

I have started a github account, and will put my mysqld stuff there shortly,
then you can kick me out if you don't like what you see.

Steve
Oct 07 2011
next sibling parent reply Adam Burton <adz21c gmail.com> writes:
I'm willing to try and contribute as best I can.

Steve Teale wrote:


 I use this title at Andrei's suggestion, and repeat his idea that it be
 used as a prefix for discussions as we navigate toward a design. Unless
 there is resistance to the idea, I will on the job of implementing
 whatever we decide is appropriate. I am retired, and have the time to do
 it.


I am fine with that as long as people can still dip in if they see bugs or 
potential improvements.

 
 It seems that every man, and possibly his dog, has a private
 implementation for at least a favorite DB, so we should have plenty of
 material to build on.
 
 At this point I would like to get responses from those who feel they are
 likely to contribute to the design through to completion.
 
 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 have started a github account, and will put my mysqld stuff there
 shortly, then you can kick me out if you don't like what you see.
 
 Steve
Oct 08 2011
parent reply Andrei Alexandrescu <SeeWebsiteForEmail erdani.org> writes:
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), and then define a few 
drivers for popular DBMSs in hope that users interested in supporting 
additional DBMSs will contribute additional drivers using the initial 
drivers as a template. Eventually when D and its database API become 
successful, DBMS providers themselves will write the drivers.

It would be great if we could leverage another already widespread driver 
architecture. There are two that come to mind, and I looked into both: 
JDBC and ODBC. The first requires either bridging into Java or 
translating Java driver code to D. The second is widespread on Windows 
but less so on other OSs. So probably we'll need to define our own 
drivers. (This also brings dynamic linking as a possibility.)


Andrei
Oct 08 2011
next sibling parent reply Steve Teale <steve.teale britseyeview.com> writes:
Andrei,

I had a go at odbcd at about the time I first started on mysqld. I must dig it
out and
get it up to the same state so that I understand ODBC again

But basically from what you're saying, all we need is the ODBC header files
translated into D. There appear to be driver managers and plenty of ODBC drivers
for Linux.

But that does not get us to where I was thinking of. ODBC is not much easier to
use than the native C apis for the databases. I had thought that ODBC was just
one
of the C database apis that we would have to cover.

Steve
Oct 08 2011
next sibling parent reply Andrei Alexandrescu <SeeWebsiteForEmail erdani.org> writes:
On 10/8/11 11:49 AM, Steve Teale wrote:

 Andrei,

 I had a go at odbcd at about the time I first started on mysqld. I must dig it
out and
 get it up to the same state so that I understand ODBC again

 But basically from what you're saying, all we need is the ODBC header files
 translated into D.


There's also the matter of dynamically linking with drivers I think.


 There appear to be driver managers and plenty of ODBC drivers
 for Linux.


I'm not seeing all that bonanza. First hit for ==ODBC linux== yields 
http://www.unixodbc.org/ and last update is from April 2010. Not sure 
how good it is or anything.


 But that does not get us to where I was thinking of. ODBC is not much easier to
 use than the native C apis for the databases. I had thought that ODBC was just
one
 of the C database apis that we would have to cover.


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
Oct 08 2011
next sibling parent Steve Teale <steve.teale britseyeview.com> writes:
Andrei,

It's not an easy one is it.


the Linux community.



It would be nice if the DB specific drivers could be mixed and matched
with whatever we come up with as the standard D interface.

I'm more or less finished with populating individual variables and structs
and arrays of structs with my mysqld implementation. I'm going to try to
merge variants in as seamlessly as possible tomorrow.

Steve
Oct 08 2011
prev sibling next sibling parent reply Jonathan M Davis <jmdavisProg gmx.com> writes:
On Saturday, October 08, 2011 12:00:37 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.



one that I personally work on), and I've never heard good things about it. 
Supporting it makes good sense, but I wouldn't want us to design Phobos' 
database solution around it.



However, given what I've heard about ODBC and its attempts to unify databases, 
I'm skeptical of how well we'll be able to have a unified DBMS API without 
harming performance. And from what I understand, it's pretty rare to change 
DBMSes for a project. You just pick one and use it. And then in the rare case 
where you have to change, you do the work to do it (and as long as the DB is 
appropriately modularized with regards to the rest of the program, it doesn't 
have a hugely negative affect on the rest of the program). So, I question that 

much of anything in terms of performance or usability), but I'm not a DB 


harming their performance or usability.

- Jonathan M Davis
Oct 08 2011
next sibling parent Adam Burton <adz21c gmail.com> writes:
Jonathan M Davis wrote:


 On Saturday, October 08, 2011 12:00:37 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.


 

 (though not one that I personally work on), and I've never heard good
 things about it. Supporting it makes good sense, but I wouldn't want us to
 design Phobos' database solution around it.
 

 enough, then at least we have a solid base for the API's being similar

 unify databases, I'm skeptical of how well we'll be able to have a unified
 DBMS API without harming performance. And from what I understand, it's
 pretty rare to change DBMSes for a project. You just pick one and use it.
 And then in the rare case where you have to change, you do the work to do
 it (and as long as the DB is appropriately modularized with regards to the
 rest of the program, it doesn't have a hugely negative affect on the rest
 of the program). So, I question that

 #costing
 much of anything in terms of performance or usability), but I'm not a DB


 without harming their performance or usability.
 
 - Jonathan M Davis


I agree that once a project picks its database it rarely changes but I find 
most people tend to use common functionality on the application code side 
and it's the sql which tends to get optimised a lot (there are some traps in 
the application code that potentially reduce performance like impropper use 

main db requirements so people can atleast develop database connected 
applications. The drivers would give us the flexibility to handle more 
databases at a basic level (at least an odbc driver will let us achieve this 
quickly until someone wants to provide D driver implementation of a db if it 
provides advantage of some kind).

That being said how do people tend to handle the SQL itself? Unless you are 
using a library to create the SQL for you, like hibernate, does anyone find 
you tend to run into SQL syntax variation across different databases? For 
example MySQL and MSSQL handle results pagination differently 

SQL but my understanding is most databases don't implement it well (not all 
of it or poor performance etc). Seems to me even if you used a common API 
you are going to need to abstract the different types of databases some how 
just because of the SQL itself.


A common API is likely to miss some features from the specific APIs (I've 




separately but like I said "if someone is willing".


MySql and Postgresql, trying to keep them similar where possible, then look 

project was as much about getting to grips with D as a project I intend to 
use, which is why I chose the long way round to implement it.
Oct 08 2011
prev sibling parent Jacob Carlborg <doob me.com> writes:
On 2011-10-08 23:12, Jonathan M Davis wrote:

 On Saturday, October 08, 2011 12:00:37 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.



 one that I personally work on), and I've never heard good things about it.
 Supporting it makes good sense, but I wouldn't want us to design Phobos'
 database solution around it.


We have some trouble with ODBC at work too.




 However, given what I've heard about ODBC and its attempts to unify databases,
 I'm skeptical of how well we'll be able to have a unified DBMS API without
 harming performance. And from what I understand, it's pretty rare to change
 DBMSes for a project. You just pick one and use it. And then in the rare case
 where you have to change, you do the work to do it (and as long as the DB is
 appropriately modularized with regards to the rest of the program, it doesn't
 have a hugely negative affect on the rest of the program). So, I question that

 much of anything in terms of performance or usability), but I'm not a DB


 harming their performance or usability.

 - Jonathan M Davis


I think it can be quite useful to change DBMSes for a project. For 
example, start with SQLite because it easy to set up and then move to 
MySQL or similar. It's especially easy to do with Ruby on Rails on Mac 
OS X. Rails uses SQLite as the default database and Mac OS X comes 
bundle with SQLite. There is no extra steps to set up this environment.

-- 
/Jacob Carlborg
Oct 09 2011
prev sibling parent reply Jacob Carlborg <doob me.com> writes:
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.

-- 
/Jacob Carlborg
Oct 09 2011
parent reply Hans Uhlig <hans.uhlig teamaol.com> writes:
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.
Dec 02 2011
next sibling parent "Jonathan M Davis" <jmdavisProg gmx.com> writes:
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
Dec 02 2011
prev sibling next sibling parent Somedude <lovelydear mailmetrash.com> writes:
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.
Dec 03 2011
prev sibling parent reply Dejan Lekic <dejan.lekic gmail.com> writes:
 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
Dec 03 2011
parent "Marco Leise" <Marco.Leise gmx.de> writes:
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?
Dec 03 2011
prev sibling parent Sean Kelly <sean invisibleduck.org> writes:
The database API I wrote ages ago is built on ODBC and you're welcome to a c=
opy if it would help. At the time (admittedly 15 years ago) the docs for ODB=
C were incomplete and wrong in places, so a reference can be handy.=20

Sent from my iPhone

On Oct 8, 2011, at 9:49 AM, Steve Teale <steve.teale britseyeview.com> wrote=
:


 Andrei,
=20
 I had a go at odbcd at about the time I first started on mysqld. I must di=


g it out and

 get it up to the same state so that I understand ODBC again
=20
 But basically from what you're saying, all we need is the ODBC header file=


s

 translated into D. There appear to be driver managers and plenty of ODBC d=


rivers

 for Linux.
=20
 But that does not get us to where I was thinking of. ODBC is not much easi=


er to

 use than the native C apis for the databases. I had thought that ODBC was j=


ust one

 of the C database apis that we would have to cover.
=20
 Steve
Oct 08 2011
prev sibling parent "Regan Heath" <regan netmail.co.nz> writes:
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/
Oct 10 2011
prev sibling next sibling parent reply Jonathan M Davis <jmdavisProg gmx.com> writes:
On Saturday, October 08, 2011 06:43:29 Steve Teale 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.


sqlite, postgres, and mysql are the ones that come to mind for me, though 
outside of a corporate environment, I can't ever see myself using anything 
other than sqlite, since it uses a file instead of a server, which makes way 
more sense when having a database for an individual program.

- Jonathan M Davis
Oct 08 2011
parent reply Adam D. Ruppe <destructionator gmail.com> writes:
Microsoft SQL Server is important to cover too. I'm pretty sure
ODBC works fine for that (there's ODBC bindings for D already,
it's part of the Windows headers) and I wrote a little something
for my database.d, but I haven't actually tested it yet!

(The project I work on for SQL server has a lot of legacy VB
code, so  while I want to get D in on it, haven't really gotten
into it yet.)
Oct 08 2011
next sibling parent Jacob Carlborg <doob me.com> writes:
On 2011-10-08 23:11, Adam D. Ruppe wrote:

 Microsoft SQL Server is important to cover too. I'm pretty sure
 ODBC works fine for that (there's ODBC bindings for D already,
 it's part of the Windows headers) and I wrote a little something
 for my database.d, but I haven't actually tested it yet!


I have some bad experience with ODBC, connecting to SQL Server, 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.

If we don't want to completely role our own implementation I suggest we 
use freetds directly instead (ODBC uses freetds).

I think it would be good to have an ODBC implementation but it should 
not be the only way to connect to SQL Server.

-- 
/Jacob Carlborg
Oct 09 2011
prev sibling parent Daniel Gibson <metalcaedes gmail.com> writes:
Am 08.10.2011 23:11, schrieb Adam D. Ruppe:

 Microsoft SQL Server is important to cover too.


Yeah, it's probably pretty widely used in the Windows world so it should 
be supported.
And Oracle should probably be supported as well.

But if we have a generic DB API support for these can be added later 
(based on ODBC, some product specific API or whatever).

I think support for SQLite, MySQL and PostgreSQL would be a good start.

Cheers,
- Daniel
Oct 09 2011
prev sibling next sibling parent dolive <dolive89 sina.com> writes:
Steve Teale Wrote:


 I use this title at Andrei's suggestion, and repeat his idea that it be used
 as a prefix for discussions as we navigate toward a design. Unless there is
 resistance to the idea, I will on the job of implementing whatever we decide
 is appropriate. I am retired, and have the time to do it.
 
 It seems that every man, and possibly his dog, has a private implementation
 for at least a favorite DB, so we should have plenty of material to build on.
 
 At this point I would like to get responses from those who feel they are
 likely to contribute to the design through to completion.
 
 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.
 
 I have started a github account, and will put my mysqld stuff there shortly,
 then you can kick me out if you don't like what you see.
 
 Steve



Please refer to the successful experience of the industry, such as  ejb, jdbc,
jdo, jpa, ado.net, delphi db api, ruby on rails active recorder, orm's
hibernate, absorb they the advantages of, to create a powerful and simple for
both for  enterprise applications  database api framework,  to achieve this
goal will Requires more than cooperation, or you personal proficient in all of
the above  api, Hoping to start directly from the orm(such as hibernate does
not depend on jdbc,or Low dependence
), Improve orm performance. 

Hope more database experts involved in the D database api design !


thanks all !

dolive
Oct 09 2011
prev sibling next sibling parent reply Piotr Szturmaj <bncrbme jadamspam.pl> writes:
Steve Teale wrote:

 I use this title at Andrei's suggestion, and repeat his idea that it be used
 as a prefix for discussions as we navigate toward a design. Unless there is
 resistance to the idea, I will on the job of implementing whatever we decide
 is appropriate. I am retired, and have the time to do it.

 It seems that every man, and possibly his dog, has a private implementation
 for at least a favorite DB, so we should have plenty of material to build on.

 At this point I would like to get responses from those who feel they are
 likely to contribute to the design through to completion.

 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.

 I have started a github account, and will put my mysqld stuff there shortly,
 then you can kick me out if you don't like what you see.


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.

Some PostgreSQL vs MySQL differences to note:

- the former does support arrays and composite types that may be stored 
in single column, the latter doesn't
- the former support asynchronous queries, but I guess its not that 
important
- the former support async notifications (for example, fired by a 
trigger, or when server is shutting down)

2. Compile type mapping of fields should support postgres's composites 
and arrays. For example, this is obvious:

execQuery!(int, string)("select 5, 'abc'");

but this is not:

execQuery!(int, string)("select ROW(5, 'abc')"); // valid postgres query

Does it return two columns or one composite column? I already addressed 
this ambiguities in ddb, please see documentation on github 
(http://pszturmaj.github.com/ddb/db.html). See also 'advanced example' 
on that page.

3. I think that compile type mapping of fields should be generalized to 
not only support DB API. It could also be used with CSV files or other 
tabular data. It may also be extended to support tree structures with 
XML (mapping xml to structs/tuples/arrays).
Oct 09 2011
next sibling parent Andrei Alexandrescu <SeeWebsiteForEmail erdani.org> writes:
On 10/9/11 7: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.

 Some PostgreSQL vs MySQL differences to note:

 - the former does support arrays and composite types that may be stored
 in single column, the latter doesn't


Yah, Hive also has some really interesting data types, such as 
parameterized arrays and maps and JSON data. Our API should be generic 
enough to accommodate such types seamlessly.


 - the former support asynchronous queries, but I guess its not that
 important
 - the former support async notifications (for example, fired by a
 trigger, or when server is shutting down)


Not sure how we could support such. Ideas would be welcome.


 2. Compile type mapping of fields should support postgres's composites
 and arrays. For example, this is obvious:

 execQuery!(int, string)("select 5, 'abc'");

 but this is not:

 execQuery!(int, string)("select ROW(5, 'abc')"); // valid postgres query


I think that should be

execQuery!(Tuple!(int, string))("select ROW(5, 'abc')");


 Does it return two columns or one composite column? I already addressed
 this ambiguities in ddb, please see documentation on github
 (http://pszturmaj.github.com/ddb/db.html). See also 'advanced example'
 on that page.

 3. I think that compile type mapping of fields should be generalized to
 not only support DB API. It could also be used with CSV files or other
 tabular data. It may also be extended to support tree structures with
 XML (mapping xml to structs/tuples/arrays).


Interesting.


Andrei
Oct 09 2011
prev sibling parent reply Walter Bright <newshound2 digitalmars.com> writes:
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?

Some driver models have succeeded only because a powerful entity forced the 
issue - like for device drivers for an OS.

I suspect that trying to design a common api to popular databases is an 
expensive and quixotic quest. If it weren't, wouldn't it have happened already?
Oct 09 2011
next sibling parent reply Andrei Alexandrescu <SeeWebsiteForEmail erdani.org> writes:
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.


 Some driver models have succeeded only because a powerful entity forced
 the issue - like for device drivers for an OS.

 I suspect that trying to design a common api to popular databases is an
 expensive and quixotic quest. If it weren't, wouldn't it have happened
 already?


It has. All database APIs for programming languages do exactly that.


Andrei
Oct 09 2011
next sibling parent reply Sean Kelly <sean invisibleduck.org> writes:
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.


=20
=20
 Haven't common denominator designs been more or less failures in at
 least one category - gui libraries?


=20
 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.=
Oct 09 2011
next sibling parent Andrei Alexandrescu <SeeWebsiteForEmail erdani.org> writes:
On 10/9/11 6:31 PM, Sean Kelly 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.


Good point and good idea.

Andrei
Oct 09 2011
prev sibling parent reply "Roald Ribe" <rr pogostick.net> writes:
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
Oct 10 2011
next sibling parent Sean Kelly <sean invisibleduck.org> writes:
Surprising. I read a research paper about a proposed language just a few mon=
ths ago. I wonder if this is by the same guys.=20

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> wr=


ote:

=20

 On Oct 9, 2011, at 3:56 PM, Andrei Alexandrescu wrote:
=20

 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.


=20
=20
 Haven't common denominator designs been more or less failures in at
 least one category - gui libraries?


=20
 A common database interface is not a common denominator API; more like t=






he opposite. This is not difficult because most differences across database s=
ystems lie in their SQL, which is strings from D's perspective.

=20
 Assuming that by "database" you mean SQL.  Pretty fair assumption, though=




 NoSQL databases (which cover a broad range of designs since there's no stan=
dard language yet for key-value DBs, etc) are rapidly gaining popularity.  I=
 almost wonder if the base type should be named SqlDatabase instead of Datab=
ase.

=20
 There is a standard language defined for NoSQL, namely UnQL:
 http://wwww.unqlspec.org/display/UnQL/Home
=20
 Roald
Oct 10 2011
prev sibling parent Sean Kelly <sean invisibleduck.org> writes:
Same guys.  It's great to see this moving from theory to application so =
quickly.

On Oct 10, 2011, at 7:30 AM, Sean Kelly wrote:


 Surprising. I read a research paper about a proposed language just a =


few months ago. I wonder if this is by the same guys.=20

=20
 Sent from my iPhone
=20
 On Oct 10, 2011, at 12:05 AM, "Roald Ribe" <rr pogostick.net> wrote:
=20

 On Sun, 09 Oct 2011 20:31:35 -0300, Sean Kelly =




<sean invisibleduck.org> wrote:

=20

 On Oct 9, 2011, at 3:56 PM, Andrei Alexandrescu wrote:
=20

 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.


=20
=20
 Haven't common denominator designs been more or less failures in =










at

 least one category - gui libraries?


=20
 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.

=20
 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.

=20
 There is a standard language defined for NoSQL, namely UnQL:
 http://wwww.unqlspec.org/display/UnQL/Home
=20
 Roald
Oct 10 2011
prev sibling parent Jonathan M Davis <jmdavisProg gmx.com> writes:
On Sunday, October 09, 2011 16:31:35 Sean Kelly 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.


If we were to do that, then maybe it should just be sql, since it's shorter 
and just as clear: std.sql.*.

- Jonathan M Davis
Oct 09 2011
prev sibling parent Jacob Carlborg <doob me.com> writes:
On 2011-10-10 00:31, 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?

 Some driver models have succeeded only because a powerful entity forced
 the issue - like for device drivers for an OS.

 I suspect that trying to design a common api to popular databases is an
 expensive and quixotic quest. If it weren't, wouldn't it have happened
 already?


At some level we need a common API for the databases, but still be able 
to get to database specific API's when there is need for that.

-- 
/Jacob Carlborg
Oct 09 2011
prev sibling next sibling parent reply Steve Teale <steve.teale britseyeview.com> writes:
There was some discussion prior to this thread about the relative virtues 
of binding to structs or binding to arrays of Variants. I was thinking 
about this, and have experimented with Variants in my trial MySQL 
implementation. My conclusions below - do they make sense?

Using Variant to capture the output of prepared queries or provide input, 
as opposed to a struct, gets you out of the realm of what must be 
determined at compile time, but only at the expense of extra DB server 
round trip(s).

If you want to use them in a deterministic way with a database table of 
known and stable structure you have to bind them. To bind them you must 
be able to determine their type. But with Variants you can't do that 
until they are initialized. So, with a struct you must have something 
like:

struct MyTableQuery42
{
   byte col1;
   float col2;
   char[] col3;
}

This can be bound for output without doing any explicit initialization of 
an instance, since you can write a template function to bind it that 
discovers everything you need to know about the struct at compile time.

If using variants for a set of out parameters you must have something 
equivalent to:

Variant[3] va;
va[0] = cast(byte) 0;
va[1] = 0.0F;
va[2] = cast(char[]) [];

So you have to have exactly the same information at compile time for the 
Variant array as you do for the struct - you still have to specify a set 
of types.

The difference is that if you have prepared a statement, you can go to 
the server and ask for the relevant metadata. With a struct you can use 
this to check if the struct is a match for the query. With an array of 
Variants you can make if conform to the query.

However, in a large number of cases, using the struct you won't need to 
bother with the metadata, because you 'know' the types of the query 
result. You don't have to bother with them for the Variant array either, 
but in that case you have to provide a function like

Variant[] MyTableQuery42Init() { ... }

which 'knows' the same stuff.

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.

Steve
Oct 09 2011
parent reply Andrei Alexandrescu <SeeWebsiteForEmail erdani.org> writes:
On 10/9/11 11:15 AM, Steve Teale wrote:

 If using variants for a set of out parameters you must have something
 equivalent to:

 Variant[3] va;
 va[0] = cast(byte) 0;
 va[1] = 0.0F;
 va[2] = cast(char[]) [];

 So you have to have exactly the same information at compile time for the
 Variant array as you do for the struct - you still have to specify a set
 of types.


Not at all, fortunately it's much simpler than that. Far as I can tell, 
a resultset emitted by a DBMS comes in the form of data (either untyped 
buffers or strings) plus column type information, usually in the form of 
an enumeration (e.g. 0 for NULL, 1 for int, 2 for double, 3 for string 
etc). You of course also have the total column count. So all you need to 
do is use that enum to pour the data into the Variants.

See this for example:

http://msdn.microsoft.com/en-us/library/ms131297.aspx

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
Oct 09 2011
parent reply Steve Teale <steve.teale britseyeview.com> writes:
 
 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
Oct 10 2011
parent reply Andrei Alexandrescu <SeeWebsiteForEmail erdani.org> writes:
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
Oct 10 2011
next sibling parent bls <bizprac orange.fr> writes:
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 ?
Oct 10 2011
prev sibling next sibling parent Steve Teale <steve.teale britseyeview.com> writes:
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
Oct 10 2011
prev sibling parent reply "Robert Jacques" <sandford jhu.edu> writes:
On Mon, 10 Oct 2011 11:09:34 -0400, Andrei Alexandrescu
<SeeWebsiteForEmail erdani.org> wrote:


 On 10/10/11 7:01 AM, Steve Teale wrote:


[snip]


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


For what it's worth, my improved Variant proposal, does do that. (i.e. it fixes
the bug)
Oct 10 2011
parent reply Andrei Alexandrescu <SeeWebsiteForEmail erdani.org> writes:
On 10/10/11 11:02 PM, Robert Jacques wrote:

 On Mon, 10 Oct 2011 11:09:34 -0400, Andrei Alexandrescu
 <SeeWebsiteForEmail erdani.org> wrote:


 On 10/10/11 7:01 AM, Steve Teale wrote:


 [snip]


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


 For what it's worth, my improved Variant proposal, does do that. (i.e.
 it fixes the bug)


Is it ready for review?

Andrei
Oct 11 2011
parent "Robert Jacques" <sandford jhu.edu> writes:
On Tue, 11 Oct 2011 09:29:57 -0400, Andrei Alexandrescu
<SeeWebsiteForEmail erdani.org> wrote:

 On 10/10/11 11:02 PM, Robert Jacques wrote:

 On Mon, 10 Oct 2011 11:09:34 -0400, Andrei Alexandrescu
 <SeeWebsiteForEmail erdani.org> wrote:


 On 10/10/11 7:01 AM, Steve Teale wrote:


 [snip]


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


 For what it's worth, my improved Variant proposal, does do that. (i.e.
 it fixes the bug)


 Is it ready for review?

 Andrei


Yes. However, I'm currently writing my PhD thesis so my bandwidth is limited.
Oct 11 2011
prev sibling next sibling parent Steve Teale <steve.teale britseyeview.com> writes:
Further question. Should we assume in the first instance that we should 
only attempt to accommodate those DBs that are free or have some free 
version that may be limited in some way - e.g. the developer version of 
MS SQL Server.

Presumably when D reaches the point of being all-conquering, then Oracle, 
IBM and so on will chip in.

Steve
Oct 09 2011
prev sibling next sibling parent reply Steve Teale <steve.teale britseyeview.com> writes:
Further generic question. (Yes, I am listening to the answers too)

If some underlying databases don't support the features that our chosen 
interface requires, do we attempt to synthesize them - presumably at cost 
to performance, or do we just throw a compile-time exception that 
basically tells the user to use a lower interface and code it themself?

It's important that we establish such boundaries. Otherwise we'll never 
ever have an alpha version of 0.1.

To express a personal opinion, then as a first pass we should do 
something that is at about the same level as JDBC but without the 
concessions to DBs like Postgres that have fancy SQL types.

When we have decided on an interface, we can always go forward, but going 
back is embarrassing.

Steve
Oct 09 2011
next sibling parent reply Adam Ruppe <destructionator gmail.com> writes:
The way I'd do it is:

interface Database {
  // support shared functions here, and other stuff useful enough to
  // warrant emulation
}

class Postgres : Database {
  // implement the interface, of course, but also all other postgres
  // specific stuff
}


When you go to use it, if you're  happy with the basics, declare
Databases.

If you need something special, use Postgres objects.
Oct 09 2011
next sibling parent Piotr Szturmaj <bncrbme jadamspam.pl> writes:
Adam Ruppe wrote:

 The way I'd do it is:

 interface Database {
    // support shared functions here, and other stuff useful enough to
    // warrant emulation
 }

 class Postgres : Database {
    // implement the interface, of course, but also all other postgres
    // specific stuff
 }


 When you go to use it, if you're  happy with the basics, declare
 Databases.

 If you need something special, use Postgres objects.


+1
Oct 09 2011
prev sibling next sibling parent reply Andrei Alexandrescu <SeeWebsiteForEmail erdani.org> writes:
On 10/9/11 11:54 AM, Adam Ruppe wrote:

 The way I'd do it is:

 interface Database {
    // support shared functions here, and other stuff useful enough to
    // warrant emulation
 }

 class Postgres : Database {
    // implement the interface, of course, but also all other postgres
    // specific stuff
 }


 When you go to use it, if you're  happy with the basics, declare
 Databases.

 If you need something special, use Postgres objects.


Makes sense. JDBC does that, too.

Andrei
Oct 09 2011
parent reply Johann MacDonagh <johann.macdonagh.no spam.gmail.com> writes:
On 10/9/2011 2:22 PM, Andrei Alexandrescu wrote:

 On 10/9/11 11:54 AM, Adam Ruppe wrote:

 The way I'd do it is:

 interface Database {
 // support shared functions here, and other stuff useful enough to
 // warrant emulation
 }

 class Postgres : Database {
 // implement the interface, of course, but also all other postgres
 // specific stuff
 }


 When you go to use it, if you're happy with the basics, declare
 Databases.

 If you need something special, use Postgres objects.


 Makes sense. JDBC does that, too.

 Andrei


Maybe "Database" should be an abstract class rather than an interface? 
That's how ADO.net does it:

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.aspx

This is architecturally a little "cleaner", because whatever Postgres 
database class we have is a "database" class at heart, not a class that 
implements a database interface.

Also, perhaps some actual code that would exist in the abstract class 
would be a call to Close() in the destructor. That way you can declare a 
database connection on the stack, open it, do some stuff, allow it to 
leave scope, and clean up that resource without a timeout on the server end.
Oct 10 2011
parent reply Piotr Szturmaj <bncrbme jadamspam.pl> writes:
Johann MacDonagh wrote:

 Maybe "Database" should be an abstract class rather than an interface?
 That's how ADO.net does it:

 http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.aspx


Why "Database" instead of PGConnection, MySqlConnection, 
SQLiteConnection, etc. ? And of course base SqlConnection.
Oct 10 2011
parent reply Johann MacDonagh <johann.macdonagh.no spam.gmail.com> writes:
On 10/11/2011 1:57 AM, Piotr Szturmaj wrote:

 Johann MacDonagh wrote:

 Maybe "Database" should be an abstract class rather than an interface?
 That's how ADO.net does it:

 http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.aspx


 Why "Database" instead of PGConnection, MySqlConnection,
 SQLiteConnection, etc. ? And of course base SqlConnection.


No, I agree. The base type should be DbConnection (I dunno, 
SqlConnection is a little too specific, I like DbConnection better), 
extended by PGConnection, MySqlConnection, SqliteConnection, etc...
Oct 11 2011
parent reply Andrei Alexandrescu <SeeWebsiteForEmail erdani.org> writes:
On 10/11/11 4:00 PM, Johann MacDonagh wrote:

 On 10/11/2011 1:57 AM, Piotr Szturmaj wrote:

 Johann MacDonagh wrote:

 Maybe "Database" should be an abstract class rather than an interface?
 That's how ADO.net does it:

 http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.aspx


 Why "Database" instead of PGConnection, MySqlConnection,
 SQLiteConnection, etc. ? And of course base SqlConnection.


 No, I agree. The base type should be DbConnection (I dunno,
 SqlConnection is a little too specific, I like DbConnection better),
 extended by PGConnection, MySqlConnection, SqliteConnection, etc...


The database engine should be codified in the connection string, not in 
the type name.

Andrei
Oct 11 2011
parent reply Adam Ruppe <destructionator gmail.com> writes:
Andrei Alexandrescu wrote:

 The database engine should be codified in the connection string, 
 not in the type name. 


Why?

If it's in the type, you can trivially specialize for different
engines and get static checking for functions not supported
across them all, or runtime checking if you prefer.

You can also change the constructors to make it clear what
is being created.
Oct 11 2011
parent Johann MacDonagh <johann.macdonagh.no spam.gmail.com> writes:
On 10/11/2011 5:46 PM, Adam Ruppe wrote:

 Andrei Alexandrescu wrote:

 The database engine should be codified in the connection string,
 not in the type name.


 Why?

 If it's in the type, you can trivially specialize for different
 engines and get static checking for functions not supported
 across them all, or runtime checking if you prefer.

 You can also change the constructors to make it clear what
 is being created.


Agreed. In addition, if DbConnection handled all connections based on 
the connection string (of course, we're assuming connection strings are 
all unique here), then DbConnection would have to be modified to support 
a new driver.

DbConnection should have the lowest common denominator implementation / 
abstract routines, and the specialized Connection classes would work 
from that. That's how ADO.net works at least.
Oct 11 2011
prev sibling parent Jacob Carlborg <doob me.com> writes:
On 2011-10-09 18:54, Adam Ruppe wrote:

 The way I'd do it is:

 interface Database {
    // support shared functions here, and other stuff useful enough to
    // warrant emulation
 }

 class Postgres : Database {
    // implement the interface, of course, but also all other postgres
    // specific stuff
 }


 When you go to use it, if you're  happy with the basics, declare
 Databases.

 If you need something special, use Postgres objects.


+1

-- 
/Jacob Carlborg
Oct 09 2011
prev sibling next sibling parent reply Piotr Szturmaj <bncrbme jadamspam.pl> writes:
Steve Teale wrote:

 To express a personal opinion, then as a first pass we should do
 something that is at about the same level as JDBC but without the
 concessions to DBs like Postgres that have fancy SQL types.


I disagree. Doing it this way may introduce difficulties or 
incompabilities in the future. I think we should design it from the 
ground up, keeping in mind the all databases.

We probably should write a page on a wiki describing the API, without 
actually implementing anything. Then anyone involved may contribute to 
its design, so it may evolve into somewhat more thought out API.
Oct 09 2011
parent reply Steve Teale <steve.teale britseyeview.com> writes:
 We probably should write a page on a wiki describing the API, without
 actually implementing anything. Then anyone involved may contribute to
 its design, so it may evolve into somewhat more thought out API.


I like that idea! Must find out how to put up a wiki.

Steve
Oct 09 2011
parent Piotr Szturmaj <bncrbme jadamspam.pl> writes:
Steve Teale wrote:

 We probably should write a page on a wiki describing the API, without
 actually implementing anything. Then anyone involved may contribute to
 its design, so it may evolve into somewhat more thought out API.


 I like that idea! Must find out how to put up a wiki.


Or use existing one: http://www.prowiki.org/wiki4d/wiki.cgi

:-)
Oct 09 2011
prev sibling parent reply Andrei Alexandrescu <SeeWebsiteForEmail erdani.org> writes:
On 10/9/11 11:40 AM, Steve Teale wrote:

 Further generic question. (Yes, I am listening to the answers too)

 If some underlying databases don't support the features that our chosen
 interface requires, do we attempt to synthesize them - presumably at cost
 to performance, or do we just throw a compile-time exception that
 basically tells the user to use a lower interface and code it themself?


No.

Andrei
Oct 09 2011
parent reply Andrei Alexandrescu <SeeWebsiteForEmail erdani.org> writes:
On 10/09/11 13:22, Andrei Alexandrescu wrote:

 On 10/9/11 11:40 AM, Steve Teale wrote:

 Further generic question. (Yes, I am listening to the answers too)

 If some underlying databases don't support the features that our chosen
 interface requires, do we attempt to synthesize them - presumably at cost
 to performance, or do we just throw a compile-time exception that
 basically tells the user to use a lower interface and code it themself?


 No.

 Andrei


Sorry, that was awfully unclear. I meant to say the driver shouldn't do 
little miracles in adapting support from one engine to the next. It's a 
losing race.

It should be fine if certain queries or API calls fail either statically 
or dynamically.


Andrei
Oct 09 2011
parent reply Daniel Gibson <metalcaedes gmail.com> writes:
Am 09.10.2011 23:53, schrieb Andrei Alexandrescu:

 On 10/09/11 13:22, Andrei Alexandrescu wrote:

 On 10/9/11 11:40 AM, Steve Teale wrote:

 Further generic question. (Yes, I am listening to the answers too)

 If some underlying databases don't support the features that our chosen
 interface requires, do we attempt to synthesize them - presumably at
 cost
 to performance, or do we just throw a compile-time exception that
 basically tells the user to use a lower interface and code it themself?


 No.

 Andrei


 Sorry, that was awfully unclear. I meant to say the driver shouldn't do
 little miracles in adapting support from one engine to the next. It's a
 losing race.

 It should be fine if certain queries or API calls fail either statically
 or dynamically.


 Andrei


What about things like prepared statements?
It's really convenient to have - even if the DB does not support it and 
thus no performance may be expected, it could still be used to prevent 
SQL-injections (if the userspace-emulation is implemented properly).

I don't know of any SQL-DB that does not support prepared statements, 
but maybe they exist.
If not there may be similar widely used and very convenient features not 
supported by a few DBs that could be considered to be emulated.

I do agree however that this shouldn't be done for every feature, but 
just for very few that are worth it.

Cheers,
- Daniel
Oct 12 2011
parent Andrei Alexandrescu <SeeWebsiteForEmail erdani.org> writes:
On 10/12/11 6:42 AM, Daniel Gibson wrote:

 Sorry, that was awfully unclear. I meant to say the driver shouldn't do
 little miracles in adapting support from one engine to the next. It's a
 losing race.

 It should be fine if certain queries or API calls fail either statically
 or dynamically.


 Andrei


 What about things like prepared statements?


I agree with you that prepared statements are not little miracles.

Andrei
Oct 12 2011
prev sibling next sibling parent reply Walter Bright <newshound2 digitalmars.com> writes:
On 10/7/2011 11:43 PM, Steve Teale wrote:

 I use this title at Andrei's suggestion, and repeat his idea that it be used
 as a prefix for discussions as we navigate toward a design. Unless there is
 resistance to the idea, I will on the job of implementing whatever we decide
 is appropriate. I am retired, and have the time to do it.


Thanks, Steve, for being the champion for this project.

I'd like to suggest that, as a first step, simple translations of the C header 
files for the various databases be added to etc.c. That'll at least enable 
people who need to use a database now to get started.
Oct 09 2011
parent reply Steve Teale <steve.teale britseyeview.com> writes:
 I'd like to suggest that, as a first step, simple translations of the C
 header files for the various databases be added to etc.c. That'll at
 least enable people who need to use a database now to get started.


Walter,

I'm sure they're already out there waiting. I have MySQL. Any offers for 
Postgres and SQLite?

Does MS-SQL have a C interface these days I wonder?

Steve
Oct 09 2011
parent Jacob Carlborg <doob me.com> writes:
On 2011-10-10 06:54, Steve Teale wrote:

 I'd like to suggest that, as a first step, simple translations of the C
 header files for the various databases be added to etc.c. That'll at
 least enable people who need to use a database now to get started.


 Walter,

 I'm sure they're already out there waiting. I have MySQL. Any offers for
 Postgres and SQLite?

 Does MS-SQL have a C interface these days I wonder?

 Steve


I don't know but FreeTDS has implemented the TDS protocol (which SQL 
server uses). Either we use FreeTDS (LGPL license) or role our own 
implementation.

-- 
/Jacob Carlborg
Oct 09 2011
prev sibling next sibling parent reply Steve Teale <steve.teale britseyeview.com> writes:
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
Oct 10 2011
next sibling parent reply bls <bizprac orange.fr> writes:
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
Oct 10 2011
parent Piotr Szturmaj <bncrbme jadamspam.pl> writes:
bls wrote:

 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 ?


Since D support associative arrays, I vote to use them for passing 
connection properties instead of connection string. See 
PGConnection.open in pszturmaj.github.com/ddb/postgres.html.
Oct 10 2011
prev sibling parent reply Andrei Alexandrescu <SeeWebsiteForEmail erdani.org> writes:
On 10/10/11 9:07 AM, Steve Teale wrote:

 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();
 }


[snip]

This makes sense from the perspective of a database implementor who 
needs to provide the appropriate interfaces, but I think a better angle 
would be to focus on use cases and work the API backwards from there.

Here's what I think should be a complete program:

import std.sql, std.stdio;

void main() {
     auto db = connect("
         engine = mysql;
         user = john;
         password = doe;
         port = 6900;
     ");
     auto rows = db.execute("SELECT score, a.user FROM users a
       JOIN scores b on a.user = b.user");
     foreach (row; rows) {
         writeln("%10s\t%s", row[0], row[1]);
         // or
         // writeln("%10s\t%s", row["score"], row["user"]);
     }
}

This should cleanly close everything upon exiting from main, provide 
proper error reporting if anything bad happens (and a lot could go 
wrong: malformed conn string, missing driver, wrong username/password, 
connection errors, malformed query, wrong column name...).

Using the interfaces you propose directly would allow one to implement 
the same program, but with significant extra chaff. That's why suggest 
we focus on user-level APIs first because in many ways those are the 
most important thing. Then we will distill the proper interfaces.

So the question is - what should a typical database task look like? That 
includes prepared statements, binding to tuples and structs, and a lot 
more.


Andrei
Oct 11 2011
parent reply Jacob Carlborg <doob me.com> writes:
On 2011-10-11 19:40, Andrei Alexandrescu wrote:

 This makes sense from the perspective of a database implementor who
 needs to provide the appropriate interfaces, but I think a better angle
 would be to focus on use cases and work the API backwards from there.

 Here's what I think should be a complete program:

 import std.sql, std.stdio;

 void main() {
 auto db = connect("
 engine = mysql;
 user = john;
 password = doe;
 port = 6900;
 ");
 auto rows = db.execute("SELECT score, a.user FROM users a
 JOIN scores b on a.user = b.user");
 foreach (row; rows) {
 writeln("%10s\t%s", row[0], row[1]);
 // or
 // writeln("%10s\t%s", row["score"], row["user"]);
 }
 }

 This should cleanly close everything upon exiting from main, provide
 proper error reporting if anything bad happens (and a lot could go
 wrong: malformed conn string, missing driver, wrong username/password,
 connection errors, malformed query, wrong column name...).

 Using the interfaces you propose directly would allow one to implement
 the same program, but with significant extra chaff. That's why suggest
 we focus on user-level APIs first because in many ways those are the
 most important thing. Then we will distill the proper interfaces.

 So the question is - what should a typical database task look like? That
 includes prepared statements, binding to tuples and structs, and a lot
 more.


 Andrei


If we're talking use cases and high level interfaces I would go with 
something like:

class Post : Model
{
     mixin hasMany!("comments");
}

class Comment : Model
{
     mixin belongsTo!("post");
}

The above mixins are very ugly. This would be perfect for user definable 
annotations/attributes

void main ()
{
     Connection connection = new MySqlConnection("localhost", "john", 
"doe", "6900");

     // or using field syntax
     connection.host = "localhost";
     connection.username = "john";
     connection.password = "doe";
     connection.port = "6900";

     auto posts = Post.all; // lazily get all rows from the table "posts"
     posts = posts.where("title = ?", "foobar"); // add where condition

      // or if this is possible, using new-style lambda syntax
     posts = posts.where(post => post.title == "foobar");

     foreach (post ; posts) // execute SQL here
     {
         // get the title and content via opDispatch
         writeln("title: %s\n\n%s", post.title, post.content);
         writeln("Comments:\n");

         foreach (comment ; post.comments) // lazily loaded comments
             writeln(comments.content);
     }

     auto post = new Post;
     post.title = "foobar"; // this is not currently possible with 
opDispatch
     post.content = "content of post";

     // create a new row in the "posts" table
     post.save;
}

I haven't figured out yet how to connect a connection to the models. 
Perhaps assigning a connection object to the base class "Model":

// using field syntax
Model.connection.host = "localhost";
Model.connection.username = "john";
Model.connection.password = "doe";
Model.connection.port = "6900";

// or by assigning a new connection object
Model.connection = new MySqlConnection("localhost", "john", "doe", "6900");

Anyway, this is what I think the highest level of the interfaces could 
look like.

I recommend that everyone take a good look at ActiveRecord in Ruby on Rails:

http://guides.rubyonrails.org/active_record_querying.html
http://guides.rubyonrails.org/association_basics.html
http://guides.rubyonrails.org/active_record_validations_callbacks.html

-- 
/Jacob Carlborg
Oct 11 2011
parent reply Andrei Alexandrescu <SeeWebsiteForEmail erdani.org> writes:
On 10/11/11 3:05 PM, Jacob Carlborg wrote:

 If we're talking use cases and high level interfaces I would go with
 something like:


[snip]

 I recommend that everyone take a good look at ActiveRecord in Ruby on
 Rails:

 http://guides.rubyonrails.org/active_record_querying.html
 http://guides.rubyonrails.org/association_basics.html
 http://guides.rubyonrails.org/active_record_validations_callbacks.html


I confess the example you gave looks very foreign to me. From consulting 
http://guides.rubyonrails.org/active_record_querying.html, I see Ruby's 
active records esentially recode relational algebra in Ruby (as for the 
constructs the equivalent SQL is shown).

For a variety of reasons, this would be tenuous in D. One simple reason 
is that e.g. lambdas don't offer access to textual representation, which 
would be necessary to translate lambda-based conditions into SQL text.

I might be narrow-minded, but I thought we're still looking at writing 
and executing good old SQL code.


Andrei
Oct 11 2011
next sibling parent reply Johann MacDonagh <johann.macdonagh.no spam.gmail.com> writes:
On 10/11/2011 5:31 PM, Andrei Alexandrescu wrote:

 On 10/11/11 3:05 PM, Jacob Carlborg wrote:

 If we're talking use cases and high level interfaces I would go with
 something like:


 [snip]

 I recommend that everyone take a good look at ActiveRecord in Ruby on
 Rails:

 http://guides.rubyonrails.org/active_record_querying.html
 http://guides.rubyonrails.org/association_basics.html
 http://guides.rubyonrails.org/active_record_validations_callbacks.html


 I confess the example you gave looks very foreign to me. From consulting
 http://guides.rubyonrails.org/active_record_querying.html, I see Ruby's
 active records esentially recode relational algebra in Ruby (as for the
 constructs the equivalent SQL is shown).

 For a variety of reasons, this would be tenuous in D. One simple reason
 is that e.g. lambdas don't offer access to textual representation, which
 would be necessary to translate lambda-based conditions into SQL text.

 I might be narrow-minded, but I thought we're still looking at writing
 and executing good old SQL code.


 Andrei


We should always allow the user to get down to the nitty-gritty and 
write good ol' SQL statements and execute them. However, writing all 
that plumbing code gets old very quickly, so if we can provide some 
clean and simple frameworks on top of that, users would appreciate it.
Oct 11 2011
next sibling parent Andrei Alexandrescu <SeeWebsiteForEmail erdani.org> writes:
On 10/11/11 7:43 PM, Johann MacDonagh wrote:

 We should always allow the user to get down to the nitty-gritty and
 write good ol' SQL statements and execute them. However, writing all
 that plumbing code gets old very quickly, so if we can provide some
 clean and simple frameworks on top of that, users would appreciate it.


The way I see it, I should either learn SQL plus some alternate syntax 
for it, or SQL alone.

Andrei
Oct 11 2011
prev sibling next sibling parent Steve Teale <steve.teale britseyeview.com> writes:
 I might be narrow-minded, but I thought we're still looking at writing
 and executing good old SQL code.





 Andrei


 We should always allow the user to get down to the nitty-gritty and
 write good ol' SQL statements and execute them. However, writing all
 that plumbing code gets old very quickly, so if we can provide some
 clean and simple frameworks on top of that, users would appreciate it.


I'm mostly with Andei. I'm all for sophisticated techniques, but what the 
user has to do should not look like a primer for them. Also if the user 
has to change down a cog the lower-level interfaces should not look like 
they're written in another language.

From my experiments with MySQL I believe we can provide clean and simple 
frameworks. But, you get what you pay for - simple interfaces will be 
suitable for simple cases.

Steve
Oct 11 2011
prev sibling parent reply Jacob Carlborg <doob me.com> writes:
On 2011-10-12 02:43, Johann MacDonagh wrote:

 On 10/11/2011 5:31 PM, Andrei Alexandrescu wrote:

 On 10/11/11 3:05 PM, Jacob Carlborg wrote:

 If we're talking use cases and high level interfaces I would go with
 something like:


 [snip]

 I recommend that everyone take a good look at ActiveRecord in Ruby on
 Rails:

 http://guides.rubyonrails.org/active_record_querying.html
 http://guides.rubyonrails.org/association_basics.html
 http://guides.rubyonrails.org/active_record_validations_callbacks.html


 I confess the example you gave looks very foreign to me. From consulting
 http://guides.rubyonrails.org/active_record_querying.html, I see Ruby's
 active records esentially recode relational algebra in Ruby (as for the
 constructs the equivalent SQL is shown).

 For a variety of reasons, this would be tenuous in D. One simple reason
 is that e.g. lambdas don't offer access to textual representation, which
 would be necessary to translate lambda-based conditions into SQL text.

 I might be narrow-minded, but I thought we're still looking at writing
 and executing good old SQL code.


 Andrei


 We should always allow the user to get down to the nitty-gritty and
 write good ol' SQL statements and execute them. However, writing all
 that plumbing code gets old very quickly, so if we can provide some
 clean and simple frameworks on top of that, users would appreciate it.


Yes, exactly. The point of having several layers built on top of each 
other is that everyone is free to choose on which layer they want to 
interact with the database.

If someone is comfortable with interacting with the database in an 
object oriented API they're free to do that. If they're instead more 
comfortable in executing raw SQL, then that's possible as well.

-- 
/Jacob Carlborg
Oct 12 2011
parent reply "Marco Leise" <Marco.Leise gmx.de> writes:
Am 12.10.2011, 09:02 Uhr, schrieb Jacob Carlborg <doob me.com>:


 On 2011-10-12 02:43, Johann MacDonagh wrote:

 On 10/11/2011 5:31 PM, Andrei Alexandrescu wrote:

 On 10/11/11 3:05 PM, Jacob Carlborg wrote:

 If we're talking use cases and high level interfaces I would go with
 something like:


 [snip]

 I recommend that everyone take a good look at ActiveRecord in Ruby on
 Rails:

 http://guides.rubyonrails.org/active_record_querying.html
 http://guides.rubyonrails.org/association_basics.html
 http://guides.rubyonrails.org/active_record_validations_callbacks.html


 I confess the example you gave looks very foreign to me. From  
 consulting
 http://guides.rubyonrails.org/active_record_querying.html, I see Ruby's
 active records esentially recode relational algebra in Ruby (as for the
 constructs the equivalent SQL is shown).

 For a variety of reasons, this would be tenuous in D. One simple reason
 is that e.g. lambdas don't offer access to textual representation,  
 which
 would be necessary to translate lambda-based conditions into SQL text.

 I might be narrow-minded, but I thought we're still looking at writing
 and executing good old SQL code.


 Andrei


 We should always allow the user to get down to the nitty-gritty and
 write good ol' SQL statements and execute them. However, writing all
 that plumbing code gets old very quickly, so if we can provide some
 clean and simple frameworks on top of that, users would appreciate it.


 Yes, exactly. The point of having several layers built on top of each  
 other is that everyone is free to choose on which layer they want to  
 interact with the database.

 If someone is comfortable with interacting with the database in an  
 object oriented API they're free to do that. If they're instead more  
 comfortable in executing raw SQL, then that's possible as well.


It's not like there really is a choice. You just cannot do everything with  
an interface built around objects/structs. But I would use that interface  
most of the time. Ranges for result sets and structs for rows are a very  
natural representation of a SQL query.

Under that aspect, table associations would also be naturally turned into  
pointers. So when a "thread" table refers to the starter through its user  
id, then the thread object would have a pointer to a user object instead  
of just an integer. This makes it easy for example to just fetch a thread  
 from the DB and write "thread.starter.name".
http://docs.jboss.org/hibernate/core/3.3/reference/en/html/performance.html#performance-fetching
Oct 12 2011
parent Jacob Carlborg <doob me.com> writes:
On 2011-10-12 09:34, Marco Leise wrote:

 Am 12.10.2011, 09:02 Uhr, schrieb Jacob Carlborg <doob me.com>:


 On 2011-10-12 02:43, Johann MacDonagh wrote:

 On 10/11/2011 5:31 PM, Andrei Alexandrescu wrote:

 On 10/11/11 3:05 PM, Jacob Carlborg wrote:

 If we're talking use cases and high level interfaces I would go with
 something like:


 [snip]

 I recommend that everyone take a good look at ActiveRecord in Ruby on
 Rails:

 http://guides.rubyonrails.org/active_record_querying.html
 http://guides.rubyonrails.org/association_basics.html
 http://guides.rubyonrails.org/active_record_validations_callbacks.html


 I confess the example you gave looks very foreign to me. From
 consulting
 http://guides.rubyonrails.org/active_record_querying.html, I see Ruby's
 active records esentially recode relational algebra in Ruby (as for the
 constructs the equivalent SQL is shown).

 For a variety of reasons, this would be tenuous in D. One simple reason
 is that e.g. lambdas don't offer access to textual representation,
 which
 would be necessary to translate lambda-based conditions into SQL text.

 I might be narrow-minded, but I thought we're still looking at writing
 and executing good old SQL code.


 Andrei


 We should always allow the user to get down to the nitty-gritty and
 write good ol' SQL statements and execute them. However, writing all
 that plumbing code gets old very quickly, so if we can provide some
 clean and simple frameworks on top of that, users would appreciate it.


 Yes, exactly. The point of having several layers built on top of each
 other is that everyone is free to choose on which layer they want to
 interact with the database.

 If someone is comfortable with interacting with the database in an
 object oriented API they're free to do that. If they're instead more
 comfortable in executing raw SQL, then that's possible as well.


 It's not like there really is a choice. You just cannot do everything
 with an interface built around objects/structs. But I would use that
 interface most of the time. Ranges for result sets and structs for rows
 are a very natural representation of a SQL query.

 Under that aspect, table associations would also be naturally turned
 into pointers. So when a "thread" table refers to the starter through
 its user id, then the thread object would have a pointer to a user
 object instead of just an integer. This makes it easy for example to
 just fetch a thread from the DB and write "thread.starter.name".
 http://docs.jboss.org/hibernate/core/3.3/reference/en/html/performance.html#performance-fetching


Yes, this is what ActiveRecord does as well:

http://guides.rubyonrails.org/association_basics.html
http://guides.rubyonrails.org/active_record_querying.html

-- 
/Jacob Carlborg
Oct 12 2011
prev sibling next sibling parent reply Jacob Carlborg <doob me.com> writes:
On 2011-10-11 23:31, Andrei Alexandrescu wrote:

 On 10/11/11 3:05 PM, Jacob Carlborg wrote:

 If we're talking use cases and high level interfaces I would go with
 something like:


 [snip]

 I recommend that everyone take a good look at ActiveRecord in Ruby on
 Rails:

 http://guides.rubyonrails.org/active_record_querying.html
 http://guides.rubyonrails.org/association_basics.html
 http://guides.rubyonrails.org/active_record_validations_callbacks.html


 I confess the example you gave looks very foreign to me. From consulting
 http://guides.rubyonrails.org/active_record_querying.html, I see Ruby's
 active records esentially recode relational algebra in Ruby (as for the
 constructs the equivalent SQL is shown).


Yes, exactly. The point is to have as much as possible in functions 
instead of string literals and have a more Ruby like API than an SQL 
looking API.

connection.select("*").from("users");

instead of

connection.execute("select * from users")

Then they wrap everything in an object oriented API.


 For a variety of reasons, this would be tenuous in D. One simple reason
 is that e.g. lambdas don't offer access to textual representation, which
 would be necessary to translate lambda-based conditions into SQL text.


ActiveRecord doesn't support these lambda-based conditions out of the 
box. It is possible with the help of plugins, which uses a Ruby parser 
to get things done.

I though that it might be possible to do in D, without the use of a 
parser. Take this for example:

Post.where(p => p.title == "asd")

"p" would be some kind of object/struct that overloads opDispatch. The 
opDispatch method would return an object/struct that overloads opCmp and 
opEquals. opCmp/opEquals would then return an object/struct that have 
recorded the comparison. The "where" method can then translate it in to 
raw SQL.

To this to work opCmp/opEquals need to be able to return a struct or an 
object, I don't know if this is possible.


 I might be narrow-minded, but I thought we're still looking at writing
 and executing good old SQL code.


 Andrei


That would of course still be needed. I would consider that interface 
sit in the middle layer, above the lower driver level and below a higher 
ORM like level.

Everyone is of course free to choose at which layer they want to 
interface with the database.

-- 
/Jacob Carlborg
Oct 11 2011
next sibling parent reply Jonathan M Davis <jmdavisProg gmx.com> writes:
On Wednesday, October 12, 2011 08:57:54 Jacob Carlborg wrote:

 On 2011-10-11 23:31, Andrei Alexandrescu wrote:

 On 10/11/11 3:05 PM, Jacob Carlborg wrote:

 If we're talking use cases and high level interfaces I would go with


 

 something like:


 [snip]
 

 I recommend that everyone take a good look at ActiveRecord in Ruby on
 Rails:
 
 http://guides.rubyonrails.org/active_record_querying.html
 http://guides.rubyonrails.org/association_basics.html
 http://guides.rubyonrails.org/active_record_validations_callbacks.html


 
 I confess the example you gave looks very foreign to me. From consulting
 http://guides.rubyonrails.org/active_record_querying.html, I see Ruby's
 active records esentially recode relational algebra in Ruby (as for the
 constructs the equivalent SQL is shown).


 
 Yes, exactly. The point is to have as much as possible in functions
 instead of string literals and have a more Ruby like API than an SQL
 looking API.
 
 connection.select("*").from("users");
 
 instead of
 
 connection.execute("select * from users")


I don't know what all of the pros and cons are, since I'm not all that 
experienced with DB stuff, but on the surface at least, from the perspective of 
usability, I don't see anything better about the first one than the second one.

- Jonathan M Davis
Oct 12 2011
parent reply Jacob Carlborg <doob me.com> writes:
On 2011-10-12 09:05, Jonathan M Davis wrote:

 On Wednesday, October 12, 2011 08:57:54 Jacob Carlborg wrote:

 On 2011-10-11 23:31, Andrei Alexandrescu wrote:

 On 10/11/11 3:05 PM, Jacob Carlborg wrote:

 If we're talking use cases and high level interfaces I would go with



 something like:


 [snip]


 I recommend that everyone take a good look at ActiveRecord in Ruby on
 Rails:

 http://guides.rubyonrails.org/active_record_querying.html
 http://guides.rubyonrails.org/association_basics.html
 http://guides.rubyonrails.org/active_record_validations_callbacks.html


 I confess the example you gave looks very foreign to me. From consulting
 http://guides.rubyonrails.org/active_record_querying.html, I see Ruby's
 active records esentially recode relational algebra in Ruby (as for the
 constructs the equivalent SQL is shown).


 Yes, exactly. The point is to have as much as possible in functions
 instead of string literals and have a more Ruby like API than an SQL
 looking API.

 connection.select("*").from("users");

 instead of

 connection.execute("select * from users")


 I don't know what all of the pros and cons are, since I'm not all that
 experienced with DB stuff, but on the surface at least, from the perspective of
 usability, I don't see anything better about the first one than the second one.

 - Jonathan M Davis


I usually prefer calling methods and functions instead of writing 
everything in a string literal.

These forms might not make a big difference but when you use an object 
oriented API I think it does.

Take this Ruby example:

Post.joins(:comments => :guest)

Produces the following SQL:

SELECT posts.* FROM posts
   INNER JOIN comments ON comments.post_id = posts.id
   INNER JOIN guests ON guests.comment_id = comments.id

-- 
/Jacob Carlborg
Oct 12 2011
next sibling parent reply Andrei Alexandrescu <SeeWebsiteForEmail erdani.org> writes:
On 10/12/11 2:36 AM, Jacob Carlborg wrote:

 I usually prefer calling methods and functions instead of writing
 everything in a string literal.


I guess reasonable people may prefer differently. Someone who knows SQL 
may prefer it instead of learning another API, with its own inevitable 
quirks and limitations.


 These forms might not make a big difference but when you use an object
 oriented API I think it does.

 Take this Ruby example:

 Post.joins(:comments => :guest)

 Produces the following SQL:

 SELECT posts.* FROM posts
 INNER JOIN comments ON comments.post_id = posts.id
 INNER JOIN guests ON guests.comment_id = comments.id


That's a good illustrative example. My understanding is that some setup 
is needed in Ruby (defining the classes and associations) so the 
one-liner doesn't tell the full story. One needs to learn a fair amount 
of specifics to do that, whereas I know what your SQL query does today. 
I'd write it in a terser manner like this:

SELECT a.* FROM posts a
JOIN comments b ON a.post_id = b.id
JOIN guests c ON b.id = c.comment_id

I read through http://guides.rubyonrails.org/association_basics.html 
and, well, one way or another one needs to learn relational algebra to 
work with it, be it in an SQL form or a Ruby form. One possible issue 
is, what happens with parts of it that aren't properly covered. For 
example, to take the difference between two sets, I'd use:

SELECT a.* FROM table1 a
LEFT OUTER JOIN table2 b ON a.id = b.id
WHERE b.id IS NULL

(There are a couple other alternatives depending on DBMS.) This is an 
SQL classic, but I wouldn't know how to express that with the Ruby API. 
And that doesn't stop here. Many interesting queries are just as terse 
as they need in SQL and I fear would look convoluted when forced through 
another API.


Andrei
Oct 12 2011
next sibling parent reply "Regan Heath" <regan netmail.co.nz> writes:
On Wed, 12 Oct 2011 14:45:29 +0100, Andrei Alexandrescu  
<SeeWebsiteForEmail erdani.org> wrote:


 On 10/12/11 2:36 AM, Jacob Carlborg wrote:

 I usually prefer calling methods and functions instead of writing
 everything in a string literal.


 I guess reasonable people may prefer differently. Someone who knows SQL  
 may prefer it instead of learning another API, with its own inevitable  
 quirks and limitations.


 These forms might not make a big difference but when you use an object
 oriented API I think it does.

 Take this Ruby example:

 Post.joins(:comments => :guest)

 Produces the following SQL:

 SELECT posts.* FROM posts
 INNER JOIN comments ON comments.post_id = posts.id
 INNER JOIN guests ON guests.comment_id = comments.id


 That's a good illustrative example. My understanding is that some setup  
 is needed in Ruby (defining the classes and associations) so the  
 one-liner doesn't tell the full story. One needs to learn a fair amount  
 of specifics to do that, whereas I know what your SQL query does today.  
 I'd write it in a terser manner like this:

 SELECT a.* FROM posts a
 JOIN comments b ON a.post_id = b.id
 JOIN guests c ON b.id = c.comment_id

 I read through http://guides.rubyonrails.org/association_basics.html  
 and, well, one way or another one needs to learn relational algebra to  
 work with it, be it in an SQL form or a Ruby form. One possible issue  
 is, what happens with parts of it that aren't properly covered. For  
 example, to take the difference between two sets, I'd use:

 SELECT a.* FROM table1 a
 LEFT OUTER JOIN table2 b ON a.id = b.id
 WHERE b.id IS NULL

 (There are a couple other alternatives depending on DBMS.) This is an  
 SQL classic, but I wouldn't know how to express that with the Ruby API.  
 And that doesn't stop here. Many interesting queries are just as terse  
 as they need in SQL and I fear would look convoluted when forced through  
 another API.


FWIW I'm with Andrei on this one.  At the very least one of the proposed  
layers should allow direct/explicit SQL.  That said, in my experience  
there are differences between SQL server 2000, 2005, and 2008, and also  
differences when you go via ODBC which mean some queries work and others  
fail.  In which case it would be enormously convenient to have a layer  
where "someone smart" has already figured out how to do thing X with each  
type of database, and the rest of us can just call methodX() to make use  
of it.  But, I reckon that layer can easily be built on top of a direct  
SQL layer, or a direct MYSQL layer, etc.

-- 
Using Opera's revolutionary email client: http://www.opera.com/mail/
Oct 12 2011
parent Jacob Carlborg <doob me.com> writes:
On 2011-10-12 16:33, Regan Heath wrote:

 FWIW I'm with Andrei on this one. At the very least one of the proposed
 layers should allow direct/explicit SQL. That said, in my experience
 there are differences between SQL server 2000, 2005, and 2008, and also
 differences when you go via ODBC which mean some queries work and others
 fail. In which case it would be enormously convenient to have a layer
 where "someone smart" has already figured out how to do thing X with
 each type of database, and the rest of us can just call methodX() to
 make use of it. But, I reckon that layer can easily be built on top of a
 direct SQL layer, or a direct MYSQL layer, etc.


As I've said in other posts, the ORM API would be the highest level. It 
would still be possible to access the middle layer which executes raw 
SQL and the lower driver level which is specific to each database. The 
ORM layer would be built on top of the middle layer. The middle level 
would be built on top of the lower driver level.

With ActiveRecord you can execute raw SQL using:

User.connection.execute("sql")

Yeah, it's really nice to have an API that can figure out these little 
differences between the different SQL implementation and make it work 
across many databases.

-- 
/Jacob Carlborg
Oct 12 2011
prev sibling parent Jacob Carlborg <doob me.com> writes:
On 2011-10-12 15:45, Andrei Alexandrescu wrote:

 On 10/12/11 2:36 AM, Jacob Carlborg wrote:

 I usually prefer calling methods and functions instead of writing
 everything in a string literal.


 I guess reasonable people may prefer differently. Someone who knows SQL
 may prefer it instead of learning another API, with its own inevitable
 quirks and limitations.


 These forms might not make a big difference but when you use an object
 oriented API I think it does.

 Take this Ruby example:

 Post.joins(:comments => :guest)

 Produces the following SQL:

 SELECT posts.* FROM posts
 INNER JOIN comments ON comments.post_id = posts.id
 INNER JOIN guests ON guests.comment_id = comments.id


 That's a good illustrative example. My understanding is that some setup
 is needed in Ruby (defining the classes and associations) so the
 one-liner doesn't tell the full story. One needs to learn a fair amount
 of specifics to do that, whereas I know what your SQL query does today.
 I'd write it in a terser manner like this:


Yes, exactly. But everything depends on what you want to do and what 
your application does. The thing is that your application doesn't have 
to do much before you want to start using class and objects to implement 
it. And if a framework can load a SQL result into a set of objects, 
what's wrong with that.


 SELECT a.* FROM posts a
 JOIN comments b ON a.post_id = b.id
 JOIN guests c ON b.id = c.comment_id

 I read through http://guides.rubyonrails.org/association_basics.html
 and, well, one way or another one needs to learn relational algebra to
 work with it, be it in an SQL form or a Ruby form. One possible issue
 is, what happens with parts of it that aren't properly covered. For
 example, to take the difference between two sets, I'd use:

 SELECT a.* FROM table1 a
 LEFT OUTER JOIN table2 b ON a.id = b.id
 WHERE b.id IS NULL


I think you would have to put the join in a string, like this:

Table1.joins("LEFT OUTER JOIN table2 b ON a.id = b.id").where("b.id IS 
NULL")

As the link says:

Using Array/Hash of Named Associations
This method only works with INNER JOIN.

But I don't see why it wouldn't be possible to to something like this:

Table1.left_outer_join(:table2).where("b.id IS NULL")

At least for this example.


 (There are a couple other alternatives depending on DBMS.) This is an
 SQL classic, but I wouldn't know how to express that with the Ruby API.
 And that doesn't stop here. Many interesting queries are just as terse
 as they need in SQL and I fear would look convoluted when forced through
 another API.


 Andrei


Yes, you would need to execute raw SQL, or parts of a query in raw SQL, 
as the join example above shows.

An ORM API is not limited to an SQL database, it can be used for other 
things as well. ActiveResouce provides similar API to ActiveRecord but 
connects to a RESTful API instead of a database.

http://api.rubyonrails.org/classes/ActiveResource/Base.html

It's probably possible to adapt an ORM API to have a NoSQL backend as well.

I don't know how you code your applications but I usually code mine in 
an object oriented way. SQL and object orientation don't fit really good 
together. Because of that it's nice to have an ORM layer that hides the 
ugliness of converting SQL results to objects and I can use the objects 
just as they where created like "regular" objects with no database 
connection.

-- 
/Jacob Carlborg
Oct 12 2011
prev sibling parent Steve Teale <steve.teale britseyeview.com> writes:
On Wed, 12 Oct 2011 09:36:47 +0200, Jacob Carlborg wrote:


 On 2011-10-12 09:05, Jonathan M Davis wrote:

 On Wednesday, October 12, 2011 08:57:54 Jacob Carlborg wrote:

 On 2011-10-11 23:31, Andrei Alexandrescu wrote:

 On 10/11/11 3:05 PM, Jacob Carlborg wrote:

 If we're talking use cases and high level interfaces I would go with



 something like:


 [snip]


 I recommend that everyone take a good look at ActiveRecord in Ruby
 on Rails:

 http://guides.rubyonrails.org/active_record_querying.html
 http://guides.rubyonrails.org/association_basics.html
 http://guides.rubyonrails.org/










active_record_validations_callbacks.html

 I confess the example you gave looks very foreign to me. From
 consulting http://guides.rubyonrails.org/active_record_querying.html,
 I see Ruby's active records esentially recode relational algebra in
 Ruby (as for the constructs the equivalent SQL is shown).


 Yes, exactly. The point is to have as much as possible in functions
 instead of string literals and have a more Ruby like API than an SQL
 looking API.

 connection.select("*").from("users");

 instead of

 connection.execute("select * from users")


 I don't know what all of the pros and cons are, since I'm not all that
 experienced with DB stuff, but on the surface at least, from the
 perspective of usability, I don't see anything better about the first
 one than the second one.

 - Jonathan M Davis


 
 I usually prefer calling methods and functions instead of writing
 everything in a string literal.
 
 These forms might not make a big difference but when you use an object
 oriented API I think it does.
 
 Take this Ruby example:
 
 Post.joins(:comments => :guest)
 
 Produces the following SQL:
 
 SELECT posts.* FROM posts
    INNER JOIN comments ON comments.post_id = posts.id INNER JOIN guests
    ON guests.comment_id = comments.id


But you'd never guess!
Oct 12 2011
prev sibling parent reply Kapps <Kapps NotValidEmail.com> writes:
On 12/10/2011 12:57 AM, Jacob Carlborg wrote:

 On 2011-10-11 23:31, Andrei Alexandrescu wrote:

 On 10/11/11 3:05 PM, Jacob Carlborg wrote:

 If we're talking use cases and high level interfaces I would go with
 something like:


 [snip]

 I recommend that everyone take a good look at ActiveRecord in Ruby on
 Rails:

 http://guides.rubyonrails.org/active_record_querying.html
 http://guides.rubyonrails.org/association_basics.html
 http://guides.rubyonrails.org/active_record_validations_callbacks.html


 I confess the example you gave looks very foreign to me. From consulting
 http://guides.rubyonrails.org/active_record_querying.html, I see Ruby's
 active records esentially recode relational algebra in Ruby (as for the
 constructs the equivalent SQL is shown).


 Yes, exactly. The point is to have as much as possible in functions
 instead of string literals and have a more Ruby like API than an SQL
 looking API.

 connection.select("*").from("users");

 instead of

 connection.execute("select * from users")

 Then they wrap everything in an object oriented API.


 For a variety of reasons, this would be tenuous in D. One simple reason
 is that e.g. lambdas don't offer access to textual representation, which
 would be necessary to translate lambda-based conditions into SQL text.


 ActiveRecord doesn't support these lambda-based conditions out of the
 box. It is possible with the help of plugins, which uses a Ruby parser
 to get things done.

 I though that it might be possible to do in D, without the use of a
 parser. Take this for example:

 Post.where(p => p.title == "asd")

 "p" would be some kind of object/struct that overloads opDispatch. The
 opDispatch method would return an object/struct that overloads opCmp and
 opEquals. opCmp/opEquals would then return an object/struct that have
 recorded the comparison. The "where" method can then translate it in to
 raw SQL.

 To this to work opCmp/opEquals need to be able to return a struct or an
 object, I don't know if this is possible.


 I might be narrow-minded, but I thought we're still looking at writing
 and executing good old SQL code.


 Andrei


 That would of course still be needed. I would consider that interface
 sit in the middle layer, above the lower driver level and below a higher
 ORM like level.

 Everyone is of course free to choose at which layer they want to
 interface with the database.


Agreed, I don't think it's unreasonable to have an API for easier 
interoperability between databases that use slightly different syntax. 
The implementation isn't exactly difficult, and it makes for a very nice 
benefit and alternative to writing raw SQL (plus, prevents you from 
doing things like making a typo on a WHERE clause or missing a comma or 
semi-colon).

My approach to this was to just have a Query structure, and internally 
each database parses it as is. The query structure basically just has an 
array of where clauses, selects, etc, all of which are strings, with 
methods such as where or select that simply append to the appropriate 
clause and return the same query for chaining. The db then generates SQL 
for it, and returns a prepared statement. I rather like this approach, 
as (in my opinion) it looks cleaner, is more verifiable and, for my 
projects, I use it instead of actually writing my sql queries when possible.

Example:
query q = query("Customers").select("LastName","MiddleName")
.where("FirstName = ?")
.limit(10)
.select("CustomerID")
.order("LastName", Ascending)

The above would be passed in to a prepared statement, and for MySql 
would generate the statement "SELECT LastName, MiddleName, CustomerID 
FROM Customers WHERE FirstName = ? LIMIT 0, 10;". For something like SQL 
Server though, it could use TOP 10 instead of LIMIT 0, 10.

One of the other nice things is that it makes it simpler to make even 
higher level APIs for it. For example, you could generate a statement 
that populates a struct by just using
'query("Customers").select!(MyCustomerStruct)().where("Name=?")',
which would just call Select on all the fields without having to worry 
about how to generate the SQL for it.
Oct 13 2011
parent Jacob Carlborg <doob me.com> writes:
On 2011-10-13 09:51, Kapps wrote:

 On 12/10/2011 12:57 AM, Jacob Carlborg wrote:

 On 2011-10-11 23:31, Andrei Alexandrescu wrote:

 On 10/11/11 3:05 PM, Jacob Carlborg wrote:

 If we're talking use cases and high level interfaces I would go with
 something like:


 [snip]

 I recommend that everyone take a good look at ActiveRecord in Ruby on
 Rails:

 http://guides.rubyonrails.org/active_record_querying.html
 http://guides.rubyonrails.org/association_basics.html
 http://guides.rubyonrails.org/active_record_validations_callbacks.html


 I confess the example you gave looks very foreign to me. From consulting
 http://guides.rubyonrails.org/active_record_querying.html, I see Ruby's
 active records esentially recode relational algebra in Ruby (as for the
 constructs the equivalent SQL is shown).


 Yes, exactly. The point is to have as much as possible in functions
 instead of string literals and have a more Ruby like API than an SQL
 looking API.

 connection.select("*").from("users");

 instead of

 connection.execute("select * from users")

 Then they wrap everything in an object oriented API.


 For a variety of reasons, this would be tenuous in D. One simple reason
 is that e.g. lambdas don't offer access to textual representation, which
 would be necessary to translate lambda-based conditions into SQL text.


 ActiveRecord doesn't support these lambda-based conditions out of the
 box. It is possible with the help of plugins, which uses a Ruby parser
 to get things done.

 I though that it might be possible to do in D, without the use of a
 parser. Take this for example:

 Post.where(p => p.title == "asd")

 "p" would be some kind of object/struct that overloads opDispatch. The
 opDispatch method would return an object/struct that overloads opCmp and
 opEquals. opCmp/opEquals would then return an object/struct that have
 recorded the comparison. The "where" method can then translate it in to
 raw SQL.

 To this to work opCmp/opEquals need to be able to return a struct or an
 object, I don't know if this is possible.


 I might be narrow-minded, but I thought we're still looking at writing
 and executing good old SQL code.


 Andrei


 That would of course still be needed. I would consider that interface
 sit in the middle layer, above the lower driver level and below a higher
 ORM like level.

 Everyone is of course free to choose at which layer they want to
 interface with the database.


 Agreed, I don't think it's unreasonable to have an API for easier
 interoperability between databases that use slightly different syntax.
 The implementation isn't exactly difficult, and it makes for a very nice
 benefit and alternative to writing raw SQL (plus, prevents you from
 doing things like making a typo on a WHERE clause or missing a comma or
 semi-colon).

 My approach to this was to just have a Query structure, and internally
 each database parses it as is. The query structure basically just has an
 array of where clauses, selects, etc, all of which are strings, with
 methods such as where or select that simply append to the appropriate
 clause and return the same query for chaining. The db then generates SQL
 for it, and returns a prepared statement. I rather like this approach,
 as (in my opinion) it looks cleaner, is more verifiable and, for my
 projects, I use it instead of actually writing my sql queries when
 possible.

 Example:
 query q = query("Customers").select("LastName","MiddleName")
 .where("FirstName = ?")
 .limit(10)
 .select("CustomerID")
 .order("LastName", Ascending)

 The above would be passed in to a prepared statement, and for MySql
 would generate the statement "SELECT LastName, MiddleName, CustomerID
 FROM Customers WHERE FirstName = ? LIMIT 0, 10;". For something like SQL
 Server though, it could use TOP 10 instead of LIMIT 0, 10.

 One of the other nice things is that it makes it simpler to make even
 higher level APIs for it. For example, you could generate a statement
 that populates a struct by just using
 'query("Customers").select!(MyCustomerStruct)().where("Name=?")',
 which would just call Select on all the fields without having to worry
 about how to generate the SQL for it.



That looks nice.

-- 
/Jacob Carlborg
Oct 13 2011
prev sibling parent Jacob Carlborg <doob me.com> writes:
On 2011-10-11 23:31, Andrei Alexandrescu wrote:

 On 10/11/11 3:05 PM, Jacob Carlborg wrote:

 If we're talking use cases and high level interfaces I would go with
 something like:


 [snip]

 I recommend that everyone take a good look at ActiveRecord in Ruby on
 Rails:

 http://guides.rubyonrails.org/active_record_querying.html
 http://guides.rubyonrails.org/association_basics.html
 http://guides.rubyonrails.org/active_record_validations_callbacks.html


 I confess the example you gave looks very foreign to me. From consulting
 http://guides.rubyonrails.org/active_record_querying.html, I see Ruby's
 active records esentially recode relational algebra in Ruby (as for the
 constructs the equivalent SQL is shown).

 For a variety of reasons, this would be tenuous in D. One simple reason
 is that e.g. lambdas don't offer access to textual representation, which
 would be necessary to translate lambda-based conditions into SQL text.


This is an example of how a lambda-based condition can be translated 
into SQL:

module test;

import std.stdio;
import std.conv;

struct Result
{
     string str;
}

struct Compare
{
     string str;

     Result eq (T) (T t)
     {
         return Result(str ~ " = '" ~ s ~ "'");
     }
}

struct Table
{
     Compare opDispatch (string name) ()
     {
         return Compare(name);
     }
}

void where (Result delegate (Table) dg)
{
     auto result = dg(Table());
     writeln("where ", result.str);
}

void main ()
{
     where((Table post){
         return post.name.eq("foobar");
     });
}

The above code will print "where name = 'foobar'".

Here I'm using the "eq" method instead of opEquals since opEquals 
requires a specific signature.

-- 
/Jacob Carlborg
Oct 12 2011
prev sibling next sibling parent reply Steve Teale <steve.teale britseyeview.com> writes:
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
Oct 10 2011
parent reply Piotr Szturmaj <bncrbme jadamspam.pl> writes:
Steve Teale wrote:

 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


PostgreSQL's C lib is not needed if we handle postgres protocol directly 
(already done).
Oct 10 2011
parent Steve Teale <steve.teale britseyeview.com> writes:
On Mon, 10 Oct 2011 22:09:49 +0200, Piotr Szturmaj wrote:


 Steve Teale wrote:

 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


 
 PostgreSQL's C lib is not needed if we handle postgres protocol directly
 (already done).


Is that something that the user is going to be comfortable with if she 
has to step out of the bounds of our implementation. I would feel better 
using Posgres' excellent api documentation.
Oct 11 2011
prev sibling next sibling parent reply Johann MacDonagh <johann.macdonagh.no spam.gmail.com> writes:
On 10/8/2011 2:43 AM, Steve Teale wrote:

 I use this title at Andrei's suggestion, and repeat his idea that it be used
 as a prefix for discussions as we navigate toward a design. Unless there is
 resistance to the idea, I will on the job of implementing whatever we decide
 is appropriate. I am retired, and have the time to do it.

 It seems that every man, and possibly his dog, has a private implementation
 for at least a favorite DB, so we should have plenty of material to build on.

 At this point I would like to get responses from those who feel they are
 likely to contribute to the design through to completion.

 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.

 I have started a github account, and will put my mysqld stuff there shortly,
 then you can kick me out if you don't like what you see.

 Steve


I've written up a prototype for a "LINQ" style database querying 
mechanism in D (read about "LINQ to SQL" if you've never heard of it). 
Legally speaking, it has very little to do with LINQ, but the concept is 
similar.

Basically, it allows you to write code like this:

auto x = new SqliteConnection("mydata.db");

foreach(y; x.MyTable.where("someField > 10"))
{
   // y is a wrapper around Variant[string] with some opDispatch magic
   writeln(to!string(y.MyField));
   writeln(to!int(y.SomeOtherField));
}

Of course, "MyTable" is handled via opDispatch. The SqliteConnection 
doesn't care what tables are available in "mydata.db". You can also do 
much more. Such as:

x.MyTable.startAt(20).limit(10).where("blah").select("somefield",
"sometingElse");

In addition, you should be able to do something like this (don't think 
I've implemented this yet):

x.MyTable.select!MyStruct();

Doing that would return a range of MyStruct structs, rather than the 
wrapper around Variant[string] like above. This would allow you to do:

auto x = new SqliteConnection("mydata.db");

foreach(y; x.MyTable.where("someField > 10").select!MyStruct())
{
   // y is a wrapper around Variant[string] with some opDispatch magic
   writeln(y.MyField); // No more needing the to! template
   writeln(y.SomeOtherField);
}

Of course, this would allow you to find typos in field names at compile 
time (provided your struct is kept in sync with the database), and means 
you don't have to go through the Variant[string] for all your database 
accesses.

To implement this, a database "driver" would have to have a shared 
opDispatch implementation (perhaps done with a mixin or maybe with an 
abstract class), and it would have to be able to translate the "query" 
into a SQL query that works with their underlying database system.

I have a working prototype somewhere that works with Sqlite, and it 
seems to work very nicely. Clearly a system like this shows off what D 
can do out of the box (opDispatch), and makes writing scripts very easy.

Let me know if this is something you think should be part of 
std.database (or whatever we end up calling it).
Oct 10 2011
next sibling parent "Robert Jacques" <sandford jhu.edu> writes:
On Mon, 10 Oct 2011 23:08:30 -0400, Johann MacDonagh
<johann.macdonagh.no spam.gmail.com> wrote:

 On 10/8/2011 2:43 AM, Steve Teale wrote:

 I use this title at Andrei's suggestion, and repeat his idea that it be used
 as a prefix for discussions as we navigate toward a design. Unless there is
 resistance to the idea, I will on the job of implementing whatever we decide
 is appropriate. I am retired, and have the time to do it.

 It seems that every man, and possibly his dog, has a private implementation
 for at least a favorite DB, so we should have plenty of material to build on.

 At this point I would like to get responses from those who feel they are
 likely to contribute to the design through to completion.

 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.

 I have started a github account, and will put my mysqld stuff there shortly,
 then you can kick me out if you don't like what you see.

 Steve


 I've written up a prototype for a "LINQ" style database querying
 mechanism in D (read about "LINQ to SQL" if you've never heard of it).
 Legally speaking, it has very little to do with LINQ, but the concept is
 similar.

 Basically, it allows you to write code like this:

 auto x = new SqliteConnection("mydata.db");

 foreach(y; x.MyTable.where("someField > 10"))
 {
    // y is a wrapper around Variant[string] with some opDispatch magic
    writeln(to!string(y.MyField));
    writeln(to!int(y.SomeOtherField));
 }


For what it's worth, my Variant proposal has this kind of opDispatch magic
built-in.
Oct 10 2011
prev sibling next sibling parent Steve Teale <steve.teale britseyeview.com> writes:
On Mon, 10 Oct 2011 23:08:30 -0400, Johann MacDonagh wrote:


 I've written up a prototype for a "LINQ" style database querying
 mechanism in D (read about "LINQ to SQL" if you've never heard of it).
 Legally speaking, it has very little to do with LINQ, but the concept is
 similar.
 
 Basically, it allows you to write code like this:
 
 auto x = new SqliteConnection("mydata.db");
 
 foreach(y; x.MyTable.where("someField > 10")) {
    // y is a wrapper around Variant[string] with some opDispatch magic
    writeln(to!string(y.MyField));
    writeln(to!int(y.SomeOtherField));
 }
 
 Of course, "MyTable" is handled via opDispatch. The SqliteConnection
 doesn't care what tables are available in "mydata.db". You can also do
 much more. Such as:
 
 x.MyTable.startAt(20).limit(10).where("blah").select("somefield",
 "sometingElse");
 
 In addition, you should be able to do something like this (don't think
 I've implemented this yet):
 
 x.MyTable.select!MyStruct();
 
 Doing that would return a range of MyStruct structs, rather than the
 wrapper around Variant[string] like above. This would allow you to do:
 
 auto x = new SqliteConnection("mydata.db");
 
 foreach(y; x.MyTable.where("someField > 10").select!MyStruct()) {
    // y is a wrapper around Variant[string] with some opDispatch magic
    writeln(y.MyField); // No more needing the to! template
    writeln(y.SomeOtherField);
 }
 
 Of course, this would allow you to find typos in field names at compile
 time (provided your struct is kept in sync with the database), and means
 you don't have to go through the Variant[string] for all your database
 accesses.
 
 To implement this, a database "driver" would have to have a shared
 opDispatch implementation (perhaps done with a mixin or maybe with an
 abstract class), and it would have to be able to translate the "query"
 into a SQL query that works with their underlying database system.
 
 I have a working prototype somewhere that works with Sqlite, and it
 seems to work very nicely. Clearly a system like this shows off what D
 can do out of the box (opDispatch), and makes writing scripts very easy.
 
 Let me know if this is something you think should be part of
 std.database (or whatever we end up calling it).


I was lying in bed last night and realized that Variant[string] was 
attractive for various purposes. It's kind of like a Javascript Object.

That and the possibilities with strictly name structs creates some 
interesting possibilities.
Oct 10 2011
prev sibling parent reply Jacob Carlborg <doob me.com> writes:
On 2011-10-11 05:08, Johann MacDonagh wrote:

 On 10/8/2011 2:43 AM, Steve Teale wrote:

 I use this title at Andrei's suggestion, and repeat his idea that it
 be used
 as a prefix for discussions as we navigate toward a design. Unless
 there is
 resistance to the idea, I will on the job of implementing whatever we
 decide
 is appropriate. I am retired, and have the time to do it.

 It seems that every man, and possibly his dog, has a private
 implementation
 for at least a favorite DB, so we should have plenty of material to
 build on.

 At this point I would like to get responses from those who feel they are
 likely to contribute to the design through to completion.

 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.

 I have started a github account, and will put my mysqld stuff there
 shortly,
 then you can kick me out if you don't like what you see.

 Steve


 I've written up a prototype for a "LINQ" style database querying
 mechanism in D (read about "LINQ to SQL" if you've never heard of it).
 Legally speaking, it has very little to do with LINQ, but the concept is
 similar.

 Basically, it allows you to write code like this:

 auto x = new SqliteConnection("mydata.db");

 foreach(y; x.MyTable.where("someField > 10"))
 {
 // y is a wrapper around Variant[string] with some opDispatch magic
 writeln(to!string(y.MyField));
 writeln(to!int(y.SomeOtherField));
 }

 Of course, "MyTable" is handled via opDispatch. The SqliteConnection
 doesn't care what tables are available in "mydata.db". You can also do
 much more. Such as:

 x.MyTable.startAt(20).limit(10).where("blah").select("somefield",
 "sometingElse");

 In addition, you should be able to do something like this (don't think
 I've implemented this yet):

 x.MyTable.select!MyStruct();

 Doing that would return a range of MyStruct structs, rather than the
 wrapper around Variant[string] like above. This would allow you to do:

 auto x = new SqliteConnection("mydata.db");

 foreach(y; x.MyTable.where("someField > 10").select!MyStruct())
 {
 // y is a wrapper around Variant[string] with some opDispatch magic
 writeln(y.MyField); // No more needing the to! template
 writeln(y.SomeOtherField);
 }

 Of course, this would allow you to find typos in field names at compile
 time (provided your struct is kept in sync with the database), and means
 you don't have to go through the Variant[string] for all your database
 accesses.

 To implement this, a database "driver" would have to have a shared
 opDispatch implementation (perhaps done with a mixin or maybe with an
 abstract class), and it would have to be able to translate the "query"
 into a SQL query that works with their underlying database system.

 I have a working prototype somewhere that works with Sqlite, and it
 seems to work very nicely. Clearly a system like this shows off what D
 can do out of the box (opDispatch), and makes writing scripts very easy.

 Let me know if this is something you think should be part of
 std.database (or whatever we end up calling it).


I think that the use of opDispatch and selecting with a struct should be 
available at the higher level interfaces and not in the low level interface.

-- 
/Jacob Carlborg
Oct 11 2011
parent reply Johann MacDonagh <johann.macdonagh.no spam.gmail.com> writes:
On 10/11/2011 4:07 AM, Jacob Carlborg wrote:

 I think that the use of opDispatch and selecting with a struct should be
 available at the higher level interfaces and not in the low level
 interface.


What do you mean by this? Do you mean instead of having the opDispatch 
in the Connection class, we'd have it in a separate struct / template / 
other magic? This would allow us to utilize this method for other 
sources of data, such as XML.

On the other hand though, this doesn't add a whole lot. Let's say we had 
this code:

auto rows = db.execute("SELECT score, a.user FROM users a JOIN scores b 
on a.user = b.user");
foreach (row; rows) {
   writeln(to!int(row["score"]));
}

We could also add in a generic range operation that converts a range of 
Variant[string] (a row) to a struct specified by the user. The code 
would become:


auto rows = extractStructs!(MyStruct, db.execute("SELECT score, a.user 
FROM users a JOIN scores b on a.user = b.user"));
foreach (row; rows) {
   writeln(row.score);
}

I suppose I'm just remembering how much plumbing code had to be done 

much easier, even without my silly "LINQ" concept.
Oct 11 2011
parent Jacob Carlborg <doob me.com> writes:
On 2011-10-11 23:09, Johann MacDonagh wrote:

 On 10/11/2011 4:07 AM, Jacob Carlborg wrote:

 I think that the use of opDispatch and selecting with a struct should be
 available at the higher level interfaces and not in the low level
 interface.


 What do you mean by this? Do you mean instead of having the opDispatch
 in the Connection class, we'd have it in a separate struct / template /
 other magic? This would allow us to utilize this method for other
 sources of data, such as XML.


Something like that. Take advantage of opDispatch in a more ORM like 
interface. Which also hides the connection and executing of raw SQL.


 On the other hand though, this doesn't add a whole lot. Let's say we had
 this code:

 auto rows = db.execute("SELECT score, a.user FROM users a JOIN scores b
 on a.user = b.user");
 foreach (row; rows) {
 writeln(to!int(row["score"]));
 }


I think that the above code would sit in a middle layer between the 
lower level of the database driver and a higher level of an ORM like 
interface. See my reply to one of Andrei's posts.

-- 
/Jacob Carlborg
Oct 11 2011
prev sibling next sibling parent Steve Teale <steve.teale britseyeview.com> writes:
Another dumb idea.

I was thinking about Johathan's idea about std.sql. What if we did
something like the following - let the compiler do the work.

enum Disposition { ... }


Disposition Insert(string database, S)(out ulong ra, S s, string table)
   if (is(S == struct))
{ ... }
Disposition Insert(string database)
   (out ulong ra, Variant[string] vaa, string table) { ... }
Disposition PrepareInsert(string database, S)(S s) if (is(S == struct)) 
{ ... }
Disposition PrepareInsert(string database)(Variant[string] vaa)
{ ... }
Disposition ExecInsert(string database, S)
   (out ulong ra, S s) if (is(S == struct))
Disposition ExecInsert(string database)
   (out ulong ra, Variant[string] vaa)
{ ... }

Disposition Update(string database, string whereClause, S, T...)
  (out ulong ra, S s, string table, T args) if (is(S == struct)) { ... }
Disposition Update(string database, string whereClause, T...)
  (out ulong ra, Variant[string] vaa, string table, T args) 
{ ... }
...

Disposition Delete(string database, string whereClause, S)
                  (out ulong ra, S s) if (is(S == struct)) { ... }
Disposition Delete(string database, string whereClause)
                  (Variant[string] vaa) { ... }
...

Disposition SelectSequence(string database, SIN, SOUT)
   (SIN sin, SOUT sout, string table)
   if (is(SIN == struct) && is(SOUT == struct)) { ... }
Disposition SelectSequence(string database)
   (Variant[string] vaain, Variant[string] vaaout, string table)
{ ... }
Disposition SelectResultSet(string database, SIN, SOUT)
   (out ulong rc, SIN sin, SOUT sout, string table)
   if (is(SIN == struct) && is(SOUT == struct))
{ ... }
Disposition SelectResultSet(string database)
   (out ulong rc, Variant[string] vaain, Variant[string] vaaout,
               string table)
{ ... }
...
...

This would sidestep the need for a lowest common denominator approach.

The compiler would generate code for a specified database, and could
generate SQL with appropriate parameter markers and escaping. and the
code to execute it.

If some operation wasn't supported it could do a static assert.

This would be on top of a set of modules that provided nitty-gritty
operations for each database - database oriented modules as opposed to
SQL oriented. If the compile time SQL option didn't work for your app,
you'd fall back for specifics on these.

Could it be done?

Steve
Oct 10 2011
prev sibling next sibling parent reply Steve Teale <steve.teale britseyeview.com> writes:
The way this discussion is going we're going to have four layers, with 
the top one written by Jacob.

I'm lost with this ORM stuff, and I suspect that the vast majority of D 
users would feel the same.

My MySQL experiment is coming along quite well and would probably cope 
with most of the other suggestions I've seen for the current top layer.
Oct 12 2011
next sibling parent reply Jacob Carlborg <doob me.com> writes:
On 2011-10-12 20:13, Steve Teale wrote:

 The way this discussion is going we're going to have four layers, with
 the top one written by Jacob.


Hehe. As long as there are database connections available in Phobos and 
a fairly good API with different levels available it should be possible 
to create an ORM API as a separate project.


 I'm lost with this ORM stuff, and I suspect that the vast majority of D
 users would feel the same.

 My MySQL experiment is coming along quite well and would probably cope
 with most of the other suggestions I've seen for the current top layer.



-- 
/Jacob Carlborg
Oct 12 2011
parent Lutger Blijdestijn <lutger.blijdestijn gmail.com> writes:
Jacob Carlborg wrote:


 On 2011-10-12 20:13, Steve Teale wrote:

 The way this discussion is going we're going to have four layers, with
 the top one written by Jacob.


 
 Hehe. As long as there are database connections available in Phobos and
 a fairly good API with different levels available it should be possible
 to create an ORM API as a separate project.
 


That should be the way to go, an ORM is a huge and difficult project.
Oct 14 2011
prev sibling parent reply bls <bizprac orange.fr> writes:
Am 12.10.2011 20:13, schrieb Steve Teale:

 The way this discussion is going we're going to have four layers, with
 the top one written by Jacob.


1 = etc.c.
2 = Database classes
3 = ??
4 = ORM

Please explain ?


 I'm lost with this ORM stuff, and I suspect that the vast majority of D
 users would feel the same.



IMO, SQLAlchemy (Python) is a very clean and powerful ORM. Easy to use 
and provides the _Data Mapper_ pattern (not everybody is enthusiastic 
about the Active Record pattern) as well as the _Unit Of Work_ pattern.

The UOW pattern organizes pending insert/update/delete operations into 
queues and flushes them all in one batch. (All or nothing way, in other 
words transactional.

just in case : http://www.sqlalchemy.org/docs/orm/tutorial.html
Oct 13 2011
parent reply Steve Teale <steve.teale britseyeview.com> writes:
On Thu, 13 Oct 2011 11:22:20 +0200, bls wrote:


 Am 12.10.2011 20:13, schrieb Steve Teale:

 The way this discussion is going we're going to have four layers, with
 the top one written by Jacob.


 
 1 = etc.c.
 2 = Database classes
 3 = ??
 4 = ORM
 
 Please explain ?


What I meant was that there might be a layer here that would provide a 
common interface to the supported databases in terms of literal SQL 
commands, as embodied in Andrei's example


 

 I'm lost with this ORM stuff, and I suspect that the vast majority of D
 users would feel the same.


 
 
 IMO, SQLAlchemy (Python) is a very clean and powerful ORM. Easy to use
 and provides the _Data Mapper_ pattern (not everybody is enthusiastic
 about the Active Record pattern) as well as the _Unit Of Work_ pattern.
 
 The UOW pattern organizes pending insert/update/delete operations into
 queues and flushes them all in one batch. (All or nothing way, in other
 words transactional.
 
 just in case : http://www.sqlalchemy.org/docs/orm/tutorial.html
Oct 13 2011
parent reply simendsjo <simendsjo gmail.com> writes:
On 13.10.2011 11:53, Steve Teale wrote:

 On Thu, 13 Oct 2011 11:22:20 +0200, bls wrote:


  Am 12.10.2011 20:13, schrieb Steve Teale:

  The way this discussion is going we're going to have four layers, with
  the top one written by Jacob.




  1 = etc.c.
  2 = Database classes
  3 = ??
  4 = ORM

  Please explain ?




 What I meant was that there might be a layer here that would provide a
 common interface to the supported databases in terms of literal SQL
 commands, as embodied in Andrei's example


I was thinking more like
1) etc.c.mysql/pg - c interface
2) std.sql.mysql/pg - d interface using templates, ranges etc
3) Database classes - common interface
4) ORM
Oct 13 2011
parent reply bls <bizprac orange.fr> writes:
Am 13.10.2011 18:44, schrieb simendsjo:

 On 13.10.2011 11:53, Steve Teale wrote:

 On Thu, 13 Oct 2011 11:22:20 +0200, bls wrote:


 Am 12.10.2011 20:13, schrieb Steve Teale:

 The way this discussion is going we're going to have four layers,




 with

 the top one written by Jacob.




 1 = etc.c.
 2 = Database classes
 3 = ??
 4 = ORM

 Please explain ?




 What I meant was that there might be a layer here that would provide a
 common interface to the supported databases in terms of literal SQL
 commands, as embodied in Andrei's example


 I was thinking more like
 1) etc.c.mysql/pg - c interface
 2) std.sql.mysql/pg - d interface using templates, ranges etc
 3) Database classes - common interface
 4) ORM


Sorry, I don't get it. IMHO 2) and 3) are at the same level.. F.I.
a draft :

http://pastebin.com/n8H0QF8X
Oct 13 2011
parent reply simendsjo <simendsjo gmail.com> writes:
On 13.10.2011 19:49, bls wrote:

 Am 13.10.2011 18:44, schrieb simendsjo:

 On 13.10.2011 11:53, Steve Teale wrote:

 On Thu, 13 Oct 2011 11:22:20 +0200, bls wrote:


 Am 12.10.2011 20:13, schrieb Steve Teale:

 The way this discussion is going we're going to have four layers,




 with

 the top one written by Jacob.




 1 = etc.c.
 2 = Database classes
 3 = ??
 4 = ORM

 Please explain ?




 What I meant was that there might be a layer here that would provide a
 common interface to the supported databases in terms of literal SQL
 commands, as embodied in Andrei's example


 I was thinking more like
 1) etc.c.mysql/pg - c interface
 2) std.sql.mysql/pg - d interface using templates, ranges etc
 3) Database classes - common interface
 4) ORM


 Sorry, I don't get it. IMHO 2) and 3) are at the same level.. F.I.
 a draft :

 http://pastebin.com/n8H0QF8X


2) doesn't hide any features of the database. If the database supports a 
feature, it's here.
3) is a common interface - will hide the specific database, including 
features unique for this db.

At any level, you should be able to drop down to the lower levels if 
necessary.

Of course... 4) could be built directly on 1), 2) or 3). Step 2) isn't 
really needed as long as you can get internal handles and drop down to 
1), but having a clean D interface instead would be nice.
Oct 13 2011
next sibling parent reply bls <bizprac orange.fr> writes:
Am 13.10.2011 20:16, schrieb simendsjo:

 On 13.10.2011 19:49, bls wrote:

 Am 13.10.2011 18:44, schrieb simendsjo:

 On 13.10.2011 11:53, Steve Teale wrote:

 On Thu, 13 Oct 2011 11:22:20 +0200, bls wrote:


 Am 12.10.2011 20:13, schrieb Steve Teale:

 The way this discussion is going we're going to have four layers,




 with

 the top one written by Jacob.




 1 = etc.c.
 2 = Database classes
 3 = ??
 4 = ORM

 Please explain ?




 What I meant was that there might be a layer here that would provide a
 common interface to the supported databases in terms of literal SQL
 commands, as embodied in Andrei's example


 I was thinking more like
 1) etc.c.mysql/pg - c interface
 2) std.sql.mysql/pg - d interface using templates, ranges etc
 3) Database classes - common interface
 4) ORM


 Sorry, I don't get it. IMHO 2) and 3) are at the same level.. F.I.
 a draft :

 http://pastebin.com/n8H0QF8X


 2) doesn't hide any features of the database. If the database supports a
 feature, it's here.
 3) is a common interface - will hide the specific database, including
 features unique for this db.

 At any level, you should be able to drop down to the lower levels if
 necessary.

 Of course... 4) could be built directly on 1), 2) or 3). Step 2) isn't
 really needed as long as you can get internal handles and drop down to
 1), but having a clean D interface instead would be nice.


Thanks for the feedback.. I am still not there :)
from the snippet..

final class MySQL : IDatabase {
         // common _Interface implementation stuff_
         public void open(string host,string db,string user,string pw,
                 const string[string] params) {

         }
         public void open(string host,string db,string user,string pw) {
         }
         // _MySQL specific_
         public void funkyMySQLstuff() {}
}

what keeps you away to use .. auto db = new MySQL() ?? the MySQL class 
can be as close to the metal as you want it to be ?

well, guess we have to wait for the first stuff from Steve. It is just 
that I think : design first,  implement later.
Oct 13 2011
next sibling parent Steve Teale <steve.teale britseyeview.com> writes:
 
 well, guess we have to wait for the first stuff from Steve. It is just
 that I think : design first,  implement later.


Steve is working on a level 2 piece for MySQL to get the feel for what 
can be done and what can't. It is quite close to a viewable state, then 
I'll be able to hear the groans!

This thread is an information gathering exercise to try to find out what 
we should do at level 3. It's not going terribly well since everyone has 
a different point of view. So there is top-down, and bottom-up going on, 
which I think is perfectly valid.

Steve
Oct 13 2011
prev sibling parent simendsjo <simendsjo gmail.com> writes:
On 13.10.2011 20:30, bls wrote:

 Am 13.10.2011 20:16, schrieb simendsjo:

 On 13.10.2011 19:49, bls wrote:

 Am 13.10.2011 18:44, schrieb simendsjo:

 On 13.10.2011 11:53, Steve Teale wrote:

 On Thu, 13 Oct 2011 11:22:20 +0200, bls wrote:


 Am 12.10.2011 20:13, schrieb Steve Teale:

 The way this discussion is going we're going to have four layers,




 with

 the top one written by Jacob.




 1 = etc.c.
 2 = Database classes
 3 = ??
 4 = ORM

 Please explain ?




 What I meant was that there might be a layer here that would provide a
 common interface to the supported databases in terms of literal SQL
 commands, as embodied in Andrei's example


 I was thinking more like
 1) etc.c.mysql/pg - c interface
 2) std.sql.mysql/pg - d interface using templates, ranges etc
 3) Database classes - common interface
 4) ORM


 Sorry, I don't get it. IMHO 2) and 3) are at the same level.. F.I.
 a draft :

 http://pastebin.com/n8H0QF8X


 2) doesn't hide any features of the database. If the database supports a
 feature, it's here.
 3) is a common interface - will hide the specific database, including
 features unique for this db.

 At any level, you should be able to drop down to the lower levels if
 necessary.

 Of course... 4) could be built directly on 1), 2) or 3). Step 2) isn't
 really needed as long as you can get internal handles and drop down to
 1), but having a clean D interface instead would be nice.


 Thanks for the feedback.. I am still not there :)
 from the snippet..

 final class MySQL : IDatabase {
 // common _Interface implementation stuff_
 public void open(string host,string db,string user,string pw,
 const string[string] params) {

 }
 public void open(string host,string db,string user,string pw) {
 }
 // _MySQL specific_
 public void funkyMySQLstuff() {}
 }

 what keeps you away to use .. auto db = new MySQL() ?? the MySQL class
 can be as close to the metal as you want it to be ?

 well, guess we have to wait for the first stuff from Steve. It is just
 that I think : design first, implement later.


As long as nothing is missed in the translation, what "layer" exposes 
the db specifics is irrelevant. I'm just afraid that higher levels would 
have stuff like "void funkyMySQLstuff()", while the actual 
implementation would be "ComplexStruct 
funkyMySQLstuff(ParamFromOtherFunkyMethod a, SeveralOtherOptions b)" and 
be missing important options and possible optimizations.
Oct 13 2011
prev sibling parent reply "Marco Leise" <Marco.Leise gmx.de> writes:
Am 13.10.2011, 20:16 Uhr, schrieb simendsjo <simendsjo gmail.com>:


 On 13.10.2011 19:49, bls wrote:

 Am 13.10.2011 18:44, schrieb simendsjo:

 I was thinking more like
 1) etc.c.mysql/pg - c interface
 2) std.sql.mysql/pg - d interface using templates, ranges etc
 3) Database classes - common interface
 4) ORM


 Sorry, I don't get it. IMHO 2) and 3) are at the same level.. F.I.
 a draft :

 http://pastebin.com/n8H0QF8X


 2) doesn't hide any features of the database. If the database supports a  
 feature, it's here.
 3) is a common interface - will hide the specific database, including  
 features unique for this db.

 At any level, you should be able to drop down to the lower levels if  
 necessary.

 Of course... 4) could be built directly on 1), 2) or 3). Step 2) isn't  
 really needed as long as you can get internal handles and drop down to  
 1), but having a clean D interface instead would be nice.


2) would be a thin wrapper around the C API. I think that runs against  
Phobos' philosophy especially with eventually four layers of abstraction.  
I'm just saying. Wouldn't mind personally.
Oct 13 2011
parent reply Steve Teale <steve.teale britseyeview.com> writes:
 
 2) would be a thin wrapper around the C API. I think that runs against
 Phobos' philosophy especially with eventually four layers of
 abstraction. I'm just saying. Wouldn't mind personally.


Marco,

Not if it's anything like the piece I'm working on, which if I judge by 
the effort I've put into it, is not thin. Perhaps I'm at the wrong level.

I hope to put an infant version on github before the weekend is out, then 
you can judge.

Steve
Oct 14 2011
next sibling parent "Marco Leise" <Marco.Leise gmx.de> writes:
Am 14.10.2011, 09:27 Uhr, schrieb Steve Teale  
<steve.teale britseyeview.com>:


 2) would be a thin wrapper around the C API. I think that runs against
 Phobos' philosophy especially with eventually four layers of
 abstraction. I'm just saying. Wouldn't mind personally.


 Marco,

 Not if it's anything like the piece I'm working on, which if I judge by
 the effort I've put into it, is not thin. Perhaps I'm at the wrong level.

 I hope to put an infant version on github before the weekend is out, then
 you can judge.

 Steve


I wanted to say "don't put a lot of work into D wrappers of C APIs unless  
you know it has a good chance of getting accepted". I didn't realize you  
already have something more elaborate and I certainly don't want to judge  
over what piece of good work gets included. There is just probably one  
layer too much in the mix to make them all obviously distinctive. There  
seem to be a lot of database bindings from different people and now is the  
time that they get a chance to become official standard in Phobos. And  
with so many different approaches and opinions we might need a few votes  
at some point.
For example I like the layer that hides away the dirty bits (LIMIT vs.  
TOP, mass INSERTs vs. single row INSERTS, SQL keywords). But since I don't  
currently use any databases I have no strong opinion.
Oct 14 2011
prev sibling parent reply sclytrack <sclytrack fake.com> writes:
On 10/14/2011 09:27 AM, Steve Teale wrote:

 2) would be a thin wrapper around the C API. I think that runs against
 Phobos' philosophy especially with eventually four layers of
 abstraction. I'm just saying. Wouldn't mind personally.


 Marco,

 Not if it's anything like the piece I'm working on, which if I judge by
 the effort I've put into it, is not thin. Perhaps I'm at the wrong level.

 I hope to put an infant version on github before the weekend is out, then
 you can judge.

 Steve


nice :-)



---

http://www.mysql.com/about/legal/licensing/foss-exception/

I'm not sure how to interpret this.
Oct 15 2011
next sibling parent Steve Teale <steve.teale britseyeview.com> writes:
On Sat, 15 Oct 2011 20:08:28 +0200, sclytrack wrote:


 On 10/14/2011 09:27 AM, Steve Teale wrote:

 2) would be a thin wrapper around the C API. I think that runs against
 Phobos' philosophy especially with eventually four layers of
 abstraction. I'm just saying. Wouldn't mind personally.


 Marco,

 Not if it's anything like the piece I'm working on, which if I judge by
 the effort I've put into it, is not thin. Perhaps I'm at the wrong
 level.

 I hope to put an infant version on github before the weekend is out,
 then you can judge.

 Steve


 
 nice :-)
 
 
 
 ---
 
 http://www.mysql.com/about/legal/licensing/foss-exception/


It's Greek to me. But we're not considering distributing their library - 
the closest we come is the translation to D of three of their header 
files.

And the source code will be available, and there's a long list of 
exceptions, but whether we pass or not I can't tell at this time of day.

Steve

 
 I'm not sure how to interpret this.
Oct 15 2011
prev sibling parent "asava samuel" <kwete87 yahoo.com> writes:
    sclytrack

Here is an ORM that works with MySQL
https://www.kellermansoftware.com/p-47-net-data-access-layer.aspx
Mar 07 2013
prev sibling next sibling parent reply Steve Teale <steve.teale britseyeview.com> writes:
There's been a fair amount of discussion along the lines of:

auto thingie = getThingie("AC/DC", "user", "pwd", "schema");
Row[] rows = thingie.exec(meets(Subject("Peter), Object("Roberta")));
// Print out some rows (if you have the energy left)

The focus points have been how to specify the environment - AC/DC, or 
MySQL, or ODBC, or whatever, and the nature of meets(...), and whether it 
might generate SQL, or something completely different.

Are we really working on std.database without conceptual limits, or can I 
assume that we can use an incremental approach, and that for the moment 
we are really talking about std.sql?

Also, while we're on the use-case track, can some of you please think 
about prepared statements, and what binding variables to them might look 
like?

I'm working along the multiple lines of:

setParam(T)(ref T t, enumDirection d);
setParams(T...)(enumDirection d, T args);
setParams(S)(S s, enumDirection d) if (is(S == struct));
setParams(Variant[] va, enumDirection d);

Do we have to assume that parameters that are IN, OUT, or INOUT will be 
required in some cases for stored procedure support?

There's also the question of dealing with large column data. JDBC, I 
think, uses streams to interface to such things, but I wonder if 
providing delegates as part of the binding process might be better. Any 
thoughts?

Thanks
Steve
Oct 12 2011
parent "Regan Heath" <regan netmail.co.nz> writes:
On Thu, 13 Oct 2011 05:42:04 +0100, Steve Teale  
<steve.teale britseyeview.com> wrote:

 Also, while we're on the use-case track, can some of you please think
 about prepared statements, and what binding variables to them might look
 like?

 I'm working along the multiple lines of:

 setParam(T)(ref T t, enumDirection d);
 setParams(T...)(enumDirection d, T args);
 setParams(S)(S s, enumDirection d) if (is(S == struct));
 setParams(Variant[] va, enumDirection d);

 Do we have to assume that parameters that are IN, OUT, or INOUT will be
 required in some cases for stored procedure support?


I've used JDBC and some custom C++ code.  I think the JDBC approach is  
convenient/easy to understand and the C++ approach we used was very  
similar.  Both had the concept of specifying the parameter by index, so  
the code looked a bit like:

int index = 0;

setParam(index++, ..);
setParam(index++, ..);
setParam(index++, ..);
setParam(index++, ..);

It *might* be useful to retain the index idea to allow the setting of  
parameters in any order, but that might simply be flexibility that no-one  
really *needs* as I can't think of a reason why you would be forced to  
specify one before another.  The underlying API might require it however,  
but that wouldn't be a problem as internally we could just keep a  
parameter index and assign them in the order given.

As for IN, OUT, INOUT.  I've never had cause to use INOUT.  I bind input  
using SQLBindParameter (specifying INPUT and not INPUT_OUTPUT or OUTPUT)  
and obtain results using SQLBindCol (as a select effectively),  
occasionally binding a column for a COUNT or similar 'result'.  I believe  
the use case for INOUT will be store procedures as you've mentioned, all  
mine are handled by IN params and colum/select output (a single result in  
my case).  I think we're going to need to handle all 3 types for maximum  
flexibility.


 There's also the question of dealing with large column data. JDBC, I
 think, uses streams to interface to such things, but I wonder if
 providing delegates as part of the binding process might be better. Any
 thoughts?


I've never used streams, but then I've never used large columns where it  
might be appropriate - or I've had the memory/a reason to load the  
complete column up front.  But, if I was going to I would probably use a  
stream, it makes sense to me.

What about ranges, could they be used?

R

-- 
Using Opera's revolutionary email client: http://www.opera.com/mail/
Oct 13 2011
prev sibling next sibling parent reply Steve Teale <steve.teale britseyeview.com> writes:
OK, for what it's worth, the compiler generated documentation (well, more 
or less) for my mysqlD effort is now on my web site. The same directory 
also has the source files and a Code::Blocks project for anyone who uses 
the latter.

I added links to these files at the bottom of the doc.

I'll put the stuff on github as soon as I figure out how. The page for 
now is:

http://britseyeview.com/software/mysqld/
Oct 14 2011
next sibling parent reply bls <bizprac orange.fr> writes:
Am 14.10.2011 16:23, schrieb Steve Teale:

 OK, for what it's worth, the compiler generated documentation (well, more
 or less) for my mysqlD effort is now on my web site. The same directory
 also has the source files and a Code::Blocks project for anyone who uses
 the latter.

 I added links to these files at the bottom of the doc.

 I'll put the stuff on github as soon as I figure out how. The page for
 now is:

 http://britseyeview.com/software/mysqld/


Hi Steve,
don't want to be the first one,  but heck, after waiting a day ...

LIKE : RowSet
The RowSet implementation is pretty cool. Especially the convenience 
implementation. Guess this RowSet implementation will ease D ORM 
development.

MISS : MetaData
Without thinking too much about Object Relational Mappers and GUI 
Adaptors...
Having Meta information is not a bad thing. In other words NEEDED.

A draft :
string[] GetDataBases()
string[] GetSchemas()
IColumns GetColumns()
string [] GetStoredPredures()
etc...

Why ? - Enable creating a Visual DB Toolkit, ER Designer, D ORM   source 
code generator....

MISS : Mapping Functionality
Map D types to database types and vice versa

Why ? - See ORM

MISS/WANT LATER : Transaction and stored procedure support
Makes IMHO not too much sense to implement MySQL TTS and  Stored proc. 
specific code right now.

Why ? Seems to be hard to create a common Interface. (nested 
Transactions are not avail. on all systems, just to face one thing) 
Nevertheless. A DB without TTS is not worth to be included into std.database

MISS/EXOTIC : Database Events
CallBack Implementation for DB Server triggered events


-----------------------------------------------------------------------
FWIW

Since creating an universal Database Interface has much to do with 
abstraction and ergo with OOP patterns...

 From an OOP view .. What is needed at Level _Two_  ? (DB classes)

_GoF pattern_ :

The Factory Design pattern -> In order to enable database independent 
development.
OR
The Prototype pattern -> smarter than Factory, but this pattern requires 
the implementation of ICloneable respective MemberwiseClone

The Observer Pattern ->
Get and transfer DB Server-side Events to interested parties.

What is needed at Level _Three_ ? (Object Relation Mapper)
Enterprise patterns

- The Data Mapper pattern
- The Active Record Pattern (optional)
- The Unit Of Work Pattern
------ UOW Pattern is using the (GoF) Memento Pattern

Random thoughts..
I guess that we will need std.uid ( getGUID() getOID() etc ) and also 
std.serialisation -> ( orange library) to make std.database work.

Finally .. as I see the Levels
1 etc.c
2 Database classes - D query language
3 ORM classes
4 GUI Adaptor classes

I hope this comments are useful. will try your MySQL stuff on WIN 7 
asap,  and (maybe) create a more detailed ORM design within the 
following days.. Thanks, Bjoern
Oct 15 2011
next sibling parent Steve Teale <steve.teale britseyeview.com> writes:
On Sun, 16 Oct 2011 00:36:52 +0200, bls wrote:


 LIKE : RowSet
 The RowSet implementation is pretty cool. Especially the convenience
 implementation. Guess this RowSet implementation will ease D ORM
 development.
 
 MISS : MetaData
 Without thinking too much about Object Relational Mappers and GUI
 Adaptors...
 Having Meta information is not a bad thing. In other words NEEDED.
 
 A draft :
 string[] GetDataBases()
 string[] GetSchemas()
 IColumns GetColumns()
 string [] GetStoredPredures()
 etc...
 
 Why ? - Enable creating a Visual DB Toolkit, ER Designer, D ORM   source
 code generator....
 
 MISS : Mapping Functionality
 Map D types to database types and vice versa
 
 Why ? - See ORM
 
 MISS/WANT LATER : Transaction and stored procedure support Makes IMHO
 not too much sense to implement MySQL TTS and  Stored proc. specific
 code right now.
 
 Why ? Seems to be hard to create a common Interface. (nested
 Transactions are not avail. on all systems, just to face one thing)
 Nevertheless. A DB without TTS is not worth to be included into
 std.database
 
 MISS/EXOTIC : Database Events
 CallBack Implementation for DB Server triggered events
 
 
 -----------------------------------------------------------------------
 FWIW
 
 Since creating an universal Database Interface has much to do with
 abstraction and ergo with OOP patterns...
 
  From an OOP view .. What is needed at Level _Two_  ? (DB classes)
 
 _GoF pattern_ :
 
 The Factory Design pattern -> In order to enable database independent
 development.
 OR
 The Prototype pattern -> smarter than Factory, but this pattern requires
 the implementation of ICloneable respective MemberwiseClone
 
 The Observer Pattern ->
 Get and transfer DB Server-side Events to interested parties.
 
 What is needed at Level _Three_ ? (Object Relation Mapper) Enterprise
 patterns
 
 - The Data Mapper pattern
 - The Active Record Pattern (optional) - The Unit Of Work Pattern
 ------ UOW Pattern is using the (GoF) Memento Pattern
 
 Random thoughts..
 I guess that we will need std.uid ( getGUID() getOID() etc ) and also
 std.serialisation -> ( orange library) to make std.database work.
 
 Finally .. as I see the Levels
 1 etc.c
 2 Database classes - D query language 3 ORM classes
 4 GUI Adaptor classes
 
 I hope this comments are useful. will try your MySQL stuff on WIN 7
 asap,  and (maybe) create a more detailed ORM design within the
 following days.. Thanks, Bjoern


Bjoern,

Lots of people have visited the page, but first you were. However I could 
have a lot worse fist comment. Thank you for your considered constructive 
criticism. The metadata sounds like a good task for a quiet Sunday.

Also I'll add that the mapping functionality is pretty much all there, 
just not explicitly exposed. A job for Monday perhaps ;=)

Steve
Oct 15 2011
prev sibling parent Steve Teale <steve.teale britseyeview.com> writes:
 I hope this comments are useful. will try your MySQL stuff on WIN 7
 asap,  and (maybe) create a more detailed ORM design within the
 following days.. Thanks, Bjoern


Just thought. On Windows you'll have to do something about Connection.open
(). I have not yet resolved the issue of the default Unix socket path.

I did the earliest version of this on Win32, and if I remember rightly, 
all I had to pass then were the basics - host, user, password, database.

Steve
Oct 15 2011
prev sibling parent Steve Teale <steve.teale britseyeview.com> writes:
On Fri, 14 Oct 2011 14:23:32 +0000, Steve Teale wrote:


 OK, for what it's worth, the compiler generated documentation (well,
 more or less) for my mysqlD effort is now on my web site.
 
 http://britseyeview.com/software/mysqld/


Updated this so it now also has database and table listings, column 
metadata, and details of stored functions and procedures.

Added Command methods specialized on stored functions and procedures.

Only one comment so far, so it's either absolute crap or perfect.

Steve
Oct 17 2011
prev sibling next sibling parent reply Steve Teale <steve.teale britseyeview.com> writes:
There's a discussion going on about Windows header files that has 
discussed whether header files can be copyright.

Header files may be an issue with the database implementations. For 
example my mysql.d is a straight translation of mysql.h (and a couple of 
others). Does that mean it is tainted by GPL and I can't make it Boost?

Do we have a license specialist in our group?

Steve
Oct 15 2011
next sibling parent simendsjo <simendsjo gmail.com> writes:
On 16.10.2011 08:13, Steve Teale wrote:

 There's a discussion going on about Windows header files that has
 discussed whether header files can be copyright.

 Header files may be an issue with the database implementations. For
 example my mysql.d is a straight translation of mysql.h (and a couple of
 others). Does that mean it is tainted by GPL and I can't make it Boost?

 Do we have a license specialist in our group?

 Steve


I cannot find any information regarding this now, but I seem to remember 
this has been tried and that header files is legal to translate and 
change license. I think it has been the case with ReactOS and MinGW.
Oct 16 2011
prev sibling next sibling parent reply "Steven Schveighoffer" <schveiguy yahoo.com> writes:
On Sun, 16 Oct 2011 02:13:05 -0400, Steve Teale  
<steve.teale britseyeview.com> wrote:


 There's a discussion going on about Windows header files that has
 discussed whether header files can be copyright.

 Header files may be an issue with the database implementations. For
 example my mysql.d is a straight translation of mysql.h (and a couple of
 others). Does that mean it is tainted by GPL and I can't make it Boost?


A direct translation is a derivative work.  So yes, it must be GPL.

However, there must be ways around this.  I believe headers have certain  
rules in most licenses.

You will definitely need some sort of non-translated header though.  I'm  
not a license expert, so I don't know to what lengths you need to go to  
re-license the header.

However, what about mysql itself?  If the header is GPL, so is the  
library, no?  I'm assuming you are not reimplementing the mysql client  
lib?  Linking against a GPL library is definitely not going to be  
acceptable in a phobos module.

-Steve
Oct 17 2011
next sibling parent reply Steve Teale <steve.teale britseyeview.com> writes:
On Mon, 17 Oct 2011 09:42:13 -0400, Steven Schveighoffer wrote:


 On Sun, 16 Oct 2011 02:13:05 -0400, Steve Teale
 <steve.teale britseyeview.com> wrote:
 

 There's a discussion going on about Windows header files that has
 discussed whether header files can be copyright.

 Header files may be an issue with the database implementations. For
 example my mysql.d is a straight translation of mysql.h (and a couple
 of others). Does that mean it is tainted by GPL and I can't make it
 Boost?


 
 A direct translation is a derivative work.  So yes, it must be GPL.
 
 However, there must be ways around this.  I believe headers have certain
 rules in most licenses.
 
 You will definitely need some sort of non-translated header though.  I'm
 not a license expert, so I don't know to what lengths you need to go to
 re-license the header.
 
 However, what about mysql itself?  If the header is GPL, so is the
 library, no?  I'm assuming you are not reimplementing the mysql client
 lib?  Linking against a GPL library is definitely not going to be
 acceptable in a phobos module.
 
 -Steve


Hmm, I just did a quick check, and the MySQL client/server protocol is 
GPL also, so there's nowhere to go.

How do Python and PHP communicate with MySQL. Is it just that they have 
the clout to get a dispensation from MySQL AB?

Does this stuff have to go in some repository like the proposed Deimos (a 
figure representing dread in Greek Mythology) where you will presumably 
often encounter dread licensing gotchas?

Steve
Oct 17 2011
next sibling parent reply Kagamin <spam here.lot> writes:
Steve Teale Wrote:


 Hmm, I just did a quick check, and the MySQL client/server protocol is 
 GPL also, so there's nowhere to go.


That was fixed.


 How do Python and PHP communicate with MySQL. Is it just that they have 
 the clout to get a dispensation from MySQL AB?


MySQL license has FLOSS exception: opensource software can use MySQL, but not
*relicense* its code.
http://mysql.com/about/legal/licensing/foss-exception/
Though boost is not in the list.
Oct 17 2011
parent reply "Steven Schveighoffer" <schveiguy yahoo.com> writes:
On Mon, 17 Oct 2011 10:56:45 -0400, Kagamin <spam here.lot> wrote:


 Steve Teale Wrote:


 Hmm, I just did a quick check, and the MySQL client/server protocol is
 GPL also, so there's nowhere to go.


 That was fixed.


That is good news!  do you have a supporting link?  Or is it something  
that quietly went away?

-Steve
Oct 17 2011
parent reply Kagamin <spam here.lot> writes:
Steven Schveighoffer Wrote:


 That is good news!  do you have a supporting link?  Or is it something  
 that quietly went away?


http://forge.mysql.com/w/index.php?title=MySQL_Internals_ClientServer_Protocol&diff=5078&oldid=4374

I have shown this to a guy who was going to reimplement a mysql client based on
those docs.
Oct 17 2011
next sibling parent reply "Steven Schveighoffer" <schveiguy yahoo.com> writes:
On Mon, 17 Oct 2011 11:11:37 -0400, Kagamin <spam here.lot> wrote:


 Steven Schveighoffer Wrote:


 That is good news!  do you have a supporting link?  Or is it something
 that quietly went away?


 http://forge.mysql.com/w/index.php?title=MySQL_Internals_ClientServer_Protocol&diff=5078&oldid=4374

 I have shown this to a guy who was going to reimplement a mysql client  
 based on those docs.



That is good!  Since 2007, huh....  I'm surprised I could find no  
discussion on this, it seems like it would be a big deal for those who  
wanted to reimplement mysql clients.

-Steve
Oct 17 2011
parent reply Kagamin <spam here.lot> writes:
Steven Schveighoffer Wrote:


 That is good!  Since 2007, huh....  I'm surprised I could find no  
 discussion on this


http://krow.livejournal.com/684068.html?thread=2674468#t2674468
Oct 17 2011
parent "Steven Schveighoffer" <schveiguy yahoo.com> writes:
On Mon, 17 Oct 2011 11:37:43 -0400, Kagamin <spam here.lot> wrote:


 Steven Schveighoffer Wrote:


 That is good!  Since 2007, huh....  I'm surprised I could find no
 discussion on this


 http://krow.livejournal.com/684068.html?thread=2674468#t2674468


Yes, I saw that.  But that is hardly "discussion in the community." :)

In any case, it's good that it's gone.  I'm all for a d-based mysql  
protocol implementation, and it looks like the only legal option anyways  
(if you want it in phobos, that is).

-Steve
Oct 17 2011
prev sibling parent reply Piotr Szturmaj <bncrbme jadamspam.pl> writes:
Kagamin wrote:

 Steven Schveighoffer Wrote:


 That is good news!  do you have a supporting link?  Or is it something
 that quietly went away?


 http://forge.mysql.com/w/index.php?title=MySQL_Internals_ClientServer_Protocol&diff=5078&oldid=4374

 I have shown this to a guy who was going to reimplement a mysql client based
on those docs.


You probably meant me. If we create MySQL client without using C 
bindings then we would have one of the fastest bindings at all. It may 
attract some people who write web apps to D. The same applies to 
PostgreSQL for which I wrote client without using a C binding.

I know it adds some maintaining effort but we may choose opportunistic 
approach. Use direct access where possible and fallback to C lib otherwise.

Another argument for using direct access is easy deployment of 
applications, especially when client resides in the std library.
Oct 17 2011
parent reply simendsjo <simendsjo gmail.com> writes:
On 17.10.2011 17:26, Piotr Szturmaj wrote:

 You probably meant me. If we create MySQL client without using C
 bindings then we would have one of the fastest bindings at all. It may
 attract some people who write web apps to D. The same applies to
 PostgreSQL for which I wrote client without using a C binding.


Why would a reimplementation be much faster? Is the C library "slow"? Or 
is there any overhead I'm not thinking of?
Oct 17 2011
parent reply Piotr Szturmaj <bncrbme jadamspam.pl> writes:
simendsjo wrote:

 On 17.10.2011 17:26, Piotr Szturmaj wrote:

 You probably meant me. If we create MySQL client without using C
 bindings then we would have one of the fastest bindings at all. It may
 attract some people who write web apps to D. The same applies to
 PostgreSQL for which I wrote client without using a C binding.


 Why would a reimplementation be much faster? Is the C library "slow"? Or
 is there any overhead I'm not thinking of?


There is always a function call overhead and also most of the fields are 
returned as strings, whereas in underlying protocol they are encoded 
binary, i.e. int is not sent as its decimal string representation but as 
4 bytes. This saves some time taken by string to int conversion (parsing).
Oct 17 2011
parent reply simendsjo <simendsjo gmail.com> writes:
On 17.10.2011 17:55, Piotr Szturmaj wrote:

 simendsjo wrote:

 On 17.10.2011 17:26, Piotr Szturmaj wrote:

 You probably meant me. If we create MySQL client without using C
 bindings then we would have one of the fastest bindings at all. It may
 attract some people who write web apps to D. The same applies to
 PostgreSQL for which I wrote client without using a C binding.


 Why would a reimplementation be much faster? Is the C library "slow"? Or
 is there any overhead I'm not thinking of?


 There is always a function call overhead and also most of the fields are
 returned as strings, whereas in underlying protocol they are encoded
 binary, i.e. int is not sent as its decimal string representation but as
 4 bytes. This saves some time taken by string to int conversion (parsing).


I see. I've looked a bit at the C library, and it seems the protocol 
isn't very stable. Trying to support older MySQL versions and keep up 
with protocol changes might be (too) tedious.
Oct 17 2011
parent reply Piotr Szturmaj <bncrbme jadamspam.pl> writes:
simendsjo wrote:

 On 17.10.2011 17:55, Piotr Szturmaj wrote:

 simendsjo wrote:

 On 17.10.2011 17:26, Piotr Szturmaj wrote:

 You probably meant me. If we create MySQL client without using C
 bindings then we would have one of the fastest bindings at all. It may
 attract some people who write web apps to D. The same applies to
 PostgreSQL for which I wrote client without using a C binding.


 Why would a reimplementation be much faster? Is the C library "slow"? Or
 is there any overhead I'm not thinking of?


 There is always a function call overhead and also most of the fields are
 returned as strings, whereas in underlying protocol they are encoded
 binary, i.e. int is not sent as its decimal string representation but as
 4 bytes. This saves some time taken by string to int conversion
 (parsing).


 I see. I've looked a bit at the C library, and it seems the protocol
 isn't very stable. Trying to support older MySQL versions and keep up
 with protocol changes might be (too) tedious.


PostgreSQL's protocol is stable since 2003, but MySQL's is not very 
friendly indeed. Phobos might follow opportunistic path and support 
direct access with recent MySQL versions and C wrapper for older ones.
Oct 17 2011
next sibling parent simendsjo <simendsjo gmail.com> writes:
On 17.10.2011 18:16, Piotr Szturmaj wrote:

 simendsjo wrote:

 On 17.10.2011 17:55, Piotr Szturmaj wrote:

 simendsjo wrote:

 On 17.10.2011 17:26, Piotr Szturmaj wrote:

 You probably meant me. If we create MySQL client without using C
 bindings then we would have one of the fastest bindings at all. It may
 attract some people who write web apps to D. The same applies to
 PostgreSQL for which I wrote client without using a C binding.


 Why would a reimplementation be much faster? Is the C library
 "slow"? Or
 is there any overhead I'm not thinking of?


 There is always a function call overhead and also most of the fields are
 returned as strings, whereas in underlying protocol they are encoded
 binary, i.e. int is not sent as its decimal string representation but as
 4 bytes. This saves some time taken by string to int conversion
 (parsing).


 I see. I've looked a bit at the C library, and it seems the protocol
 isn't very stable. Trying to support older MySQL versions and keep up
 with protocol changes might be (too) tedious.


 PostgreSQL's protocol is stable since 2003, but MySQL's is not very
 friendly indeed. Phobos might follow opportunistic path and support
 direct access with recent MySQL versions and C wrapper for older ones.


Since 2003? That's pretty impressive!
About MySql: The reason D is missing a lot of wrappers and libraries is 
the lack of manpower. This "duplicated" effort sounds like too much job 
at the current time. I think more people will flock to D once modules 
such as database and web-programming exists, and then more focus can go 
into optimizing.
Oct 17 2011
prev sibling parent reply Steve Teale <steve.teale britseyeview.com> writes:
 PostgreSQL's protocol is stable since 2003, but MySQL's is not very
 friendly indeed. Phobos might follow opportunistic path and support
 direct access with recent MySQL versions and C wrapper for older ones.


But it looks like the C wrapper approach for MySQL won't fly for Phobos 
because of the GPL taint. MySQL support might have to be consigned to the 
Deimos 'derived works' directory. Either that or Phobos only supports 
versions > 5.xx.

Steve
Oct 17 2011
next sibling parent reply "Marco Leise" <Marco.Leise gmx.de> writes:
Am 17.10.2011, 18:38 Uhr, schrieb Steve Teale  
<steve.teale britseyeview.com>:


 PostgreSQL's protocol is stable since 2003, but MySQL's is not very
 friendly indeed. Phobos might follow opportunistic path and support
 direct access with recent MySQL versions and C wrapper for older ones.


 But it looks like the C wrapper approach for MySQL won't fly for Phobos
 because of the GPL taint. MySQL support might have to be consigned to the
 Deimos 'derived works' directory. Either that or Phobos only supports
 versions > 5.xx.

 Steve


Do people not upgrade their database to MySQL 5? I never had to deal with  
that and especially large complicated databases. If it is easy to upgrade  
to MySQL 5 and it is faster and more secure there are probably few  
*actively developed* projects accessing MySQL 4 DBs. (MySQL 5 is pretty  
much exactly 6 years old now.)
Oct 17 2011
next sibling parent "Marco Leise" <Marco.Leise gmx.de> writes:
Am 17.10.2011, 19:46 Uhr, schrieb Marco Leise <Marco.Leise gmx.de>:


 Am 17.10.2011, 18:38 Uhr, schrieb Steve Teale  
 <steve.teale britseyeview.com>:


 PostgreSQL's protocol is stable since 2003, but MySQL's is not very
 friendly indeed. Phobos might follow opportunistic path and support
 direct access with recent MySQL versions and C wrapper for older ones.


 But it looks like the C wrapper approach for MySQL won't fly for Phobos
 because of the GPL taint. MySQL support might have to be consigned to  
 the
 Deimos 'derived works' directory. Either that or Phobos only supports
 versions > 5.xx.

 Steve


 Do people not upgrade their database to MySQL 5? I never had to deal  
 with that and especially large complicated databases. If it is easy to  
 upgrade to MySQL 5 and it is faster and more secure there are probably  
 few *actively developed* projects accessing MySQL 4 DBs. (MySQL 5 is  
 pretty much exactly 6 years old now.)


I found this: http://www.gentoo.org/doc/en/mysql-upgrading.xml#doc_chap1
Leaving away the safety net it looks like a call to mysql_upgrade_shell is  
all that's needed in the general case.
Oct 17 2011
prev sibling parent reply simendsjo <simendsjo gmail.com> writes:
On 17.10.2011 19:46, Marco Leise wrote:

 Am 17.10.2011, 18:38 Uhr, schrieb Steve Teale
 <steve.teale britseyeview.com>:


 PostgreSQL's protocol is stable since 2003, but MySQL's is not very
 friendly indeed. Phobos might follow opportunistic path and support
 direct access with recent MySQL versions and C wrapper for older ones.


 But it looks like the C wrapper approach for MySQL won't fly for Phobos
 because of the GPL taint. MySQL support might have to be consigned to the
 Deimos 'derived works' directory. Either that or Phobos only supports
 versions > 5.xx.

 Steve


 Do people not upgrade their database to MySQL 5? I never had to deal
 with that and especially large complicated databases. If it is easy to
 upgrade to MySQL 5 and it is faster and more secure there are probably
 few *actively developed* projects accessing MySQL 4 DBs. (MySQL 5 is
 pretty much exactly 6 years old now.)


You'll always find people not upgrading technology for various reasons. 
We continue to use MySQL 4 at work for an internal legacy application 
because it just works and we _know_ upgrading will be a lot of work.
And see how IE6 is still in use.

I'm not advocating that D should strive to support legacy technologies 
though. I don't think Win98 or earlier are supported either.
Oct 17 2011
next sibling parent Jacob Carlborg <doob me.com> writes:
On 2011-10-17 20:07, simendsjo wrote:

 On 17.10.2011 19:46, Marco Leise wrote:

 Am 17.10.2011, 18:38 Uhr, schrieb Steve Teale
 <steve.teale britseyeview.com>:


 PostgreSQL's protocol is stable since 2003, but MySQL's is not very
 friendly indeed. Phobos might follow opportunistic path and support
 direct access with recent MySQL versions and C wrapper for older ones.


 But it looks like the C wrapper approach for MySQL won't fly for Phobos
 because of the GPL taint. MySQL support might have to be consigned to
 the
 Deimos 'derived works' directory. Either that or Phobos only supports
 versions > 5.xx.

 Steve


 Do people not upgrade their database to MySQL 5? I never had to deal
 with that and especially large complicated databases. If it is easy to
 upgrade to MySQL 5 and it is faster and more secure there are probably
 few *actively developed* projects accessing MySQL 4 DBs. (MySQL 5 is
 pretty much exactly 6 years old now.)


 You'll always find people not upgrading technology for various reasons.
 We continue to use MySQL 4 at work for an internal legacy application
 because it just works and we _know_ upgrading will be a lot of work.
 And see how IE6 is still in use.

 I'm not advocating that D should strive to support legacy technologies
 though. I don't think Win98 or earlier are supported either.


We can start with supporting the latest version and if there is a demand 
for earlier versions those can be provided by third party or similar if 
they can't be included in Phobos.

-- 
/Jacob Carlborg
Oct 17 2011
prev sibling parent reply "Jonathan M Davis" <jmdavisProg gmx.com> writes:
On Monday, October 17, 2011 11:07 simendsjo wrote:

 On 17.10.2011 19:46, Marco Leise wrote:

 Am 17.10.2011, 18:38 Uhr, schrieb Steve Teale
 
 <steve.teale britseyeview.com>:

 PostgreSQL's protocol is stable since 2003, but MySQL's is not very
 friendly indeed. Phobos might follow opportunistic path and support
 direct access with recent MySQL versions and C wrapper for older ones.


 
 But it looks like the C wrapper approach for MySQL won't fly for Phobos
 because of the GPL taint. MySQL support might have to be consigned to
 the Deimos 'derived works' directory. Either that or Phobos only
 supports versions > 5.xx.
 
 Steve


 
 Do people not upgrade their database to MySQL 5? I never had to deal
 with that and especially large complicated databases. If it is easy to
 upgrade to MySQL 5 and it is faster and more secure there are probably
 few *actively developed* projects accessing MySQL 4 DBs. (MySQL 5 is
 pretty much exactly 6 years old now.)


 
 You'll always find people not upgrading technology for various reasons.
 We continue to use MySQL 4 at work for an internal legacy application
 because it just works and we _know_ upgrading will be a lot of work.
 And see how IE6 is still in use.
 
 I'm not advocating that D should strive to support legacy technologies
 though. I don't think Win98 or earlier are supported either.


There is code in druntime and Phobos which special-cases for Windows 98 and 
earlier (e.g. std.file using the A functions instead of the W functions if the 
version of Windows that you're running on is too old to have the W functions). 
Now, personally, I would love it if we just said that you have to have at 
least Win2k if not WinXP. It would be _great_ to be able to assume at least 
Vista, since it added a number of useful functions, but XP is still too 
prevalent for that to be reasonable. Regardless, supporting older versions of 
Windows is just plain irritating, since it restricts what you can do, and D is 
new enough and Win2k old enough that I find it perfectly reasonable to insist 
that you have WinXP or newer, but that's not what we're doing at this point.

- Jonathan M Davis
Oct 17 2011
parent simendsjo <simendsjo gmail.com> writes:
On 17.10.2011 20:24, Jonathan M Davis wrote:

 There is code in druntime and Phobos which special-cases for Windows 98 and
 earlier (e.g. std.file using the A functions instead of the W functions if the
 version of Windows that you're running on is too old to have the W functions).
 Now, personally, I would love it if we just said that you have to have at
 least Win2k if not WinXP. It would be_great_  to be able to assume at least
 Vista, since it added a number of useful functions, but XP is still too
 prevalent for that to be reasonable. Regardless, supporting older versions of
 Windows is just plain irritating, since it restricts what you can do, and D is
 new enough and Win2k old enough that I find it perfectly reasonable to insist
 that you have WinXP or newer, but that's not what we're doing at this point.


I'm not even sure W2K support is in great demand. Even XP is on a strong 
downwards slope, and Win7 has a greater share of the market now. XP is 
still needed for several years, but Win98...? When was the last time 
anyone encountered Win98? Does anyone even make software with Win98 
support anymore?
I doubt D looses any market share by ditching support for operating 
systems that doesn't even have vendor support. If it restricts usage on 
newer operating systems I'm in favor of dropping it, but I guess there's 
a good reason why Win98 is supported (or just legacy from 10 years back..?)
Oct 17 2011
prev sibling parent reply "Jonathan M Davis" <jmdavisProg gmx.com> writes:
On Monday, October 17, 2011 09:38 Steve Teale wrote:

 PostgreSQL's protocol is stable since 2003, but MySQL's is not very
 friendly indeed. Phobos might follow opportunistic path and support
 direct access with recent MySQL versions and C wrapper for older ones.


 
 But it looks like the C wrapper approach for MySQL won't fly for Phobos
 because of the GPL taint. MySQL support might have to be consigned to the
 Deimos 'derived works' directory. Either that or Phobos only supports
 versions > 5.xx.


However, if we go with an appropriately pluggable approach with the DB 
engines, then it should be perfectly possible to have a 3rd party library 
which provides a DB engine for mysql which you can then use with Phobos if 
your program is GPL or GPL-compatible. So, no it won't be in Phobos if we have 
to use the C headers, but that doesn't necssarily mean that it couldn't be 
used with Phobos' DB solution.

- Jonathan M Davis
Oct 17 2011
parent Jacob Carlborg <doob me.com> writes:
On 2011-10-17 19:55, Jonathan M Davis wrote:

 On Monday, October 17, 2011 09:38 Steve Teale wrote:

 PostgreSQL's protocol is stable since 2003, but MySQL's is not very
 friendly indeed. Phobos might follow opportunistic path and support
 direct access with recent MySQL versions and C wrapper for older ones.


 But it looks like the C wrapper approach for MySQL won't fly for Phobos
 because of the GPL taint. MySQL support might have to be consigned to the
 Deimos 'derived works' directory. Either that or Phobos only supports
 versions>  5.xx.


 However, if we go with an appropriately pluggable approach with the DB
 engines, then it should be perfectly possible to have a 3rd party library
 which provides a DB engine for mysql which you can then use with Phobos if
 your program is GPL or GPL-compatible. So, no it won't be in Phobos if we have
 to use the C headers, but that doesn't necssarily mean that it couldn't be
 used with Phobos' DB solution.

 - Jonathan M Davis


I think that the Phobos database API needs to support database drivers 
written by third part users, that can be easily plugged in, for exactly 
the above mentioned reason.

-- 
/Jacob Carlborg
Oct 17 2011
prev sibling parent "Steven Schveighoffer" <schveiguy yahoo.com> writes:
On Mon, 17 Oct 2011 10:25:13 -0400, Steve Teale  
<steve.teale britseyeview.com> wrote:


 On Mon, 17 Oct 2011 09:42:13 -0400, Steven Schveighoffer wrote:


 On Sun, 16 Oct 2011 02:13:05 -0400, Steve Teale
 <steve.teale britseyeview.com> wrote:


 There's a discussion going on about Windows header files that has
 discussed whether header files can be copyright.

 Header files may be an issue with the database implementations. For
 example my mysql.d is a straight translation of mysql.h (and a couple
 of others). Does that mean it is tainted by GPL and I can't make it
 Boost?


 A direct translation is a derivative work.  So yes, it must be GPL.

 However, there must be ways around this.  I believe headers have certain
 rules in most licenses.

 You will definitely need some sort of non-translated header though.  I'm
 not a license expert, so I don't know to what lengths you need to go to
 re-license the header.

 However, what about mysql itself?  If the header is GPL, so is the
 library, no?  I'm assuming you are not reimplementing the mysql client
 lib?  Linking against a GPL library is definitely not going to be
 acceptable in a phobos module.

 -Steve


 Hmm, I just did a quick check, and the MySQL client/server protocol is
 GPL also, so there's nowhere to go.


Protocol cannot be copyrighted.  A protocol is carefully formatted data,  
but not *MySQL's* data, it's *your* data.

If they want to attempt to say my passing "select name, bar, gobbledegook  
 from myPrivateDatabase" to a server makes my code GPL, be my guest, I  
don't even think I'd need a lawyer to defend that :)  Here is a good post  
discussing it: http://krow.livejournal.com/684068.html?thread=2670116

But the library can be copyrighted (and the protocol description).  Given  
mysql's sales model (and the company behind it), you would need a very  
meticulously documented process to clean-room implement it in a way that  
could be defended, and even then Walter may not go for inclusion in  
phobos, he is allergic to even the *notion* that something might be  
challenged in court being in D/phobos.


 How do Python and PHP communicate with MySQL. Is it just that they have
 the clout to get a dispensation from MySQL AB?


little searching reveals:

http://www.mysql.com/about/legal/licensing/foss-exception/

Note PHP and Python are both in the license list, whereas Boost is not :(


 Does this stuff have to go in some repository like the proposed Deimos (a
 figure representing dread in Greek Mythology) where you will presumably
 often encounter dread licensing gotchas?


I'd imagine so.  Another option is to reimplement libmysql.

Sorry :(

-Steve
Oct 17 2011
prev sibling parent reply Steve Teale <steve.teale britseyeview.com> writes:
 A direct translation is a derivative work.  So yes, it must be GPL.
 
 However, there must be ways around this.  I believe headers have certain
 rules in most licenses.
 


Steve, do you think this provides any relief>

http://www.mysql.com/about/legal/licensing/foss-exception/

Steve
Oct 17 2011
parent "Steven Schveighoffer" <schveiguy yahoo.com> writes:
On Mon, 17 Oct 2011 11:00:13 -0400, Steve Teale  
<steve.teale britseyeview.com> wrote:


 A direct translation is a derivative work.  So yes, it must be GPL.

 However, there must be ways around this.  I believe headers have certain
 rules in most licenses.


 Steve, do you think this provides any relief>

 http://www.mysql.com/about/legal/licensing/foss-exception/


I know our messages crossed paths probably, but for completeness, no, I  
don't think this allows relief.  Boost is not listed as an allowed  
exception.

But this doesn't matter anyways.  The *end product*, not phobos, must be  
licensed free and open source under the boost license.  This is an  
important distinction.

This means, effectively, even if boost was included on the list, and we  
put mysql client bindings in phobos, in order to ship with mysql client  
library the user of phobos would have to license *their* product under  
boost!  Even if they didn't ship the source for their application, anyone  
who obtained the application source through any means would be free to  
copy it at will.  This limitation is too severe for inclusion in the core  
library of a language such as D, which might be used in closed-source  
settings.

Note that php and python are somewhat open-source anyways since they are  
interpreted, and commonly you do not need to distribute the source of your  
server-side code for it to be used.

-Steve
Oct 17 2011
prev sibling next sibling parent Kagamin <spam here.lot> writes:
Steve Teale Wrote:


 There's a discussion going on about Windows header files that has 
 discussed whether header files can be copyright.
 
 Header files may be an issue with the database implementations. For 
 example my mysql.d is a straight translation of mysql.h (and a couple of 
 others). Does that mean it is tainted by GPL and I can't make it Boost?


You can't license mysql.d under terms of boost, you can ask Oracle to include
boost into the foss exception list and license mysql.d under terms of mysql
foss exception, but first you should ask Walter whether he wants code under
terms of foss exception in phobos.
Oct 17 2011
prev sibling parent reply Kagamin <spam here.lot> writes:
Steve Teale Wrote:


 Header files may be an issue with the database implementations. For 
 example my mysql.d is a straight translation of mysql.h (and a couple of 
 others). Does that mean it is tainted by GPL and I can't make it Boost?


As an alternative why not make ODBC bindings? This way you'll be able to use
virtually any database. ODBC is the most important database binding.
Oct 17 2011
next sibling parent Steve Teale <steve.teale britseyeview.com> writes:
On Mon, 17 Oct 2011 11:40:44 -0400, Kagamin wrote:


 Steve Teale Wrote:
 

 Header files may be an issue with the database implementations. For
 example my mysql.d is a straight translation of mysql.h (and a couple
 of others). Does that mean it is tainted by GPL and I can't make it
 Boost?


 
 As an alternative why not make ODBC bindings? This way you'll be able to
 use virtually any database. ODBC is the most important database binding.


We are/were heading down the road of having ODBC as one of the options, 
but what's the status of its header files I wonder?

But it sounds to me as if Walter's CAPI concept should be expanded to 
have three directories - C headers, equivalent D interfaces, and derived 
works.

Steve
Oct 17 2011
prev sibling parent Steve Teale <steve.teale britseyeview.com> writes:
 
 As an alternative why not make ODBC bindings? This way you'll be able to
 use virtually any database. ODBC is the most important database binding.


I can't find any definitive source for the ODBC header files. I've seen 
various versions that seem to make conflicting copyright claims or to 
have conflicting license statements. Does anyone have what might be 
considered to be a definitive version of these?

Steve
Oct 17 2011
prev sibling next sibling parent reply Steve Teale <steve.teale britseyeview.com> writes:
It looks as if it is not a big deal to use the MySQL protocol rather than 
the client library. I can now log in that way, so the rest should follow, 
and I am working on the changeover. The current MySQL protocol has been 
around since version 4.1.

Given that Piotr has, I think, already done the same work at protocol 
level for Postgres, that SQLite is public domain, and that a similar API 
can be done with ODBC, we should be able to cover a fair range of systems 
without falling foul of GPL.

Steve
Oct 19 2011
next sibling parent reply Piotr Szturmaj <bncrbme jadamspam.pl> writes:
Steve Teale wrote:

 It looks as if it is not a big deal to use the MySQL protocol rather than
 the client library. I can now log in that way, so the rest should follow,
 and I am working on the changeover. The current MySQL protocol has been
 around since version 4.1.


Good to hear that! One note though. MySQL protocol has two row encoding 
modes, text or binary. Please consider using the latter for better 
peformance.


 Given that Piotr has, I think, already done the same work at protocol
 level for Postgres, that SQLite is public domain, and that a similar API
 can be done with ODBC, we should be able to cover a fair range of systems
 without falling foul of GPL.


Yes, that would be great.
Oct 20 2011
next sibling parent Steve Teale <steve.teale britseyeview.com> writes:
 
 Good to hear that! One note though. MySQL protocol has two row encoding
 modes, text or binary. Please consider using the latter for better
 performance.
 
 


Certainly - it would be pretty inefficient given the implementation 
intended to use text.

Steve
Oct 20 2011
prev sibling parent reply Steve Teale <steve.teale britseyeview.com> writes:
 Steve Teale wrote:

 It looks as if it is not a big deal to use the MySQL protocol rather
 than the client library. I can now log in that way, so the rest should
 follow, and I am working on the changeover. The current MySQL protocol
 has been around since version 4.1.


 


Unfortunately I am now reasonably sure that such documentation as there 
is about the protocol tells lies. So getting it right is going to be like 
trench warfare. I was hoping to do it without resorting to dissection of 
the MySQL source code, but the latter may be required.
Oct 20 2011
parent reply Piotr Szturmaj <bncrbme jadamspam.pl> writes:
Steve Teale wrote:

 Steve Teale wrote:

 It looks as if it is not a big deal to use the MySQL protocol rather
 than the client library. I can now log in that way, so the rest should
 follow, and I am working on the changeover. The current MySQL protocol
 has been around since version 4.1.




 Unfortunately I am now reasonably sure that such documentation as there
 is about the protocol tells lies. So getting it right is going to be like
 trench warfare. I was hoping to do it without resorting to dissection of
 the MySQL source code, but the latter may be required.


I guess you're right about that requirement. I also did read some 
Postgres source code to see how data types are encoded. Don't give up! :-)
Oct 20 2011
parent reply "Steven Schveighoffer" <schveiguy yahoo.com> writes:
On Thu, 20 Oct 2011 13:41:05 -0400, Piotr Szturmaj <bncrbme jadamspam.pl>  
wrote:


 Steve Teale wrote:

 Steve Teale wrote:

 It looks as if it is not a big deal to use the MySQL protocol rather
 than the client library. I can now log in that way, so the rest should
 follow, and I am working on the changeover. The current MySQL protocol
 has been around since version 4.1.




 Unfortunately I am now reasonably sure that such documentation as there
 is about the protocol tells lies. So getting it right is going to be  
 like
 trench warfare. I was hoping to do it without resorting to dissection of
 the MySQL source code, but the latter may be required.


 I guess you're right about that requirement. I also did read some  
 Postgres source code to see how data types are encoded. Don't give up!  
 :-)


Please be cautious about reading GPL'd source code to understand the  
protocol.  It's possible to be in violation of the license based on this.

It generally takes two people to do this correctly, one to read and  
understand the original code, and one to implement the new version based  
on information conveyed by the first person.

I'd recommend someone taking the existing protocol document and updating  
it with the corrections, then you using that document to fix the protocol  
implementation in your code.

-Steve
Oct 20 2011
next sibling parent Steve Teale <steve.teale britseyeview.com> writes:
 
 Please be cautious about reading GPL'd source code to understand the
 protocol.  It's possible to be in violation of the license based on
 this.
 
 It generally takes two people to do this correctly, one to read and
 understand the original code, and one to implement the new version based
 on information conveyed by the first person.
 
 I'd recommend someone taking the existing protocol document and updating
 it with the corrections, then you using that document to fix the
 protocol implementation in your code.


Will do. I shall attempt to do it on the basis of the network packets I 
get from the server in the first instance.

Steve

 -Steve
Oct 20 2011
prev sibling parent reply Kagamin <spam here.lot> writes:
Steven Schveighoffer Wrote:


 Please be cautious about reading GPL'd source code to understand the  
 protocol.  It's possible to be in violation of the license based on this.


As long as he doesn't copy the code, there's no violation. He can even organize
code better (or worse), e.g. use OOP, templates and/or ranges.
Oct 20 2011
parent reply "Steven Schveighoffer" <schveiguy yahoo.com> writes:
On Thu, 20 Oct 2011 15:56:24 -0400, Kagamin <spam here.lot> wrote:


 Steven Schveighoffer Wrote:


 Please be cautious about reading GPL'd source code to understand the
 protocol.  It's possible to be in violation of the license based on  
 this.


 As long as he doesn't copy the code, there's no violation. He can even  
 organize code better (or worse), e.g. use OOP, templates and/or ranges.


Isn't it copying if I retype exactly what I'm reading?  If so, wouldn't it  
be copying if I read code, then the code I write looks similar?

It may feel like not copying, and it may not be copying, but it's  
sometimes (and I stress sometimes) difficult to prove that it's not  
copying.

The easiest way to prove is not to read the code.  Then any coincidental  
similarities are provable coincidences.  It shouldn't be too difficult.   
The protocol spec should be nearly accurate (it technically should be 100%  
accurate, seems like mysql doesn't keep it up well enough), so you just  
have to fix any discrepancies.  It sounds like Steve is already on the  
right track.

If it comes down to it, someone can volunteer to help debug the code by  
comparing it to the GPL'd library in areas where the spec seems to be  
incorrect and completing the spec.  I can help with this if you really  
need it, I'd love to see native D support for MySQL, as it's my DB of  
choice ;)

-Steve
Oct 20 2011
parent Steve Teale <steve.teale britseyeview.com> writes:
 If it comes down to it, someone can volunteer to help debug the code by
 comparing it to the GPL'd library in areas where the spec seems to be
 incorrect and completing the spec.  I can help with this if you really
 need it, I'd love to see native D support for MySQL, as it's my DB of
 choice ;)


Steve,

It's coming along quite well. I can log in and do simple queries, and 
clean up after them (how MySQL does that is horrifying), and now I'm 
attacking prepared statements - so far so good. I'd say in another 3-4 
days I'll be back to where I was with the library dependent version.

I agree with you 100% that getting the library out of the way is a top 
priority. What's more, now I'm getting my head round the protocol I'm 
thinking the binding for prepared statements can be simplified from what 
I had before.

Kagamin,

I'll get that header file. I already have a good chunk of the ODBC stuff 
done from a Windows header file a couple of years ago, so I should be 
able to get a quick start.

Steve
Oct 21 2011
prev sibling parent reply Kagamin <spam here.lot> writes:
Steve Teale Wrote:


 Given that Piotr has, I think, already done the same work at protocol 
 level for Postgres, that SQLite is public domain, and that a similar API 
 can be done with ODBC, we should be able to cover a fair range of systems 
 without falling foul of GPL.


It is said ODBC is compatible with SQL/CLI
https://www2.opengroup.org/ogsys/jsp/publications/PublicationDetail
.jsp?catalogno=c451 and applications written for SQL/CLI will work when
compiled with ODBC headers.
The description says the spec gives C binding, so should be viable to write D
translation. I took a look at iODBC and MS ODBC headers they look similar,
probably copied from SQL/CLI specification.
Oct 20 2011
parent Kagamin <spam here.lot> writes:
Kagamin Wrote:


 Steve Teale Wrote:
 

 Given that Piotr has, I think, already done the same work at protocol 
 level for Postgres, that SQLite is public domain, and that a similar API 
 can be done with ODBC, we should be able to cover a fair range of systems 
 without falling foul of GPL.


 
 It is said ODBC is compatible with SQL/CLI
https://www2.opengroup.org/ogsys/jsp/publications/PublicationDetail
.jsp?catalogno=c451 and applications written for SQL/CLI will work when
compiled with ODBC headers.
 The description says the spec gives C binding, so should be viable to write D
translation. I took a look at iODBC and MS ODBC headers they look similar,
probably copied from SQL/CLI specification.


yes, ISO 9075-3 provides whole sqlcli.h C header which looks like a copy
to/from ms odbc sql.h header.
Oct 20 2011
prev sibling next sibling parent Steve Teale <steve.teale britseyeview.com> writes:
Just a quick progress report.

Since it was clear that my original ideas for a MySQL D interface were 
not going to make it into Phobos at least because of license issues, I 
have been investigating the use of the published MySQL client/server 
protocol (this was expressly removed from GPL, if it could ever have 
belonged there in the first place, some time ago.)

The results are encouraging, and I am pretty well back to the point I had 
reached using the C API, but now without files derived from GPL material, 
linking to a library, and mimicry of the C API methods.

In the course of going over the whole thing again, and with a knowledge 
now of how the MySQL client/server relationship works, I think I may have 
improved the interface, even in its present experimental form.

Steve
Oct 30 2011
prev sibling parent reply Steve Teale <steve.teale britseyeview.com> writes:
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
Nov 25 2011
parent reply Kagamin <spam here.lot> writes:
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.
Nov 25 2011
parent reply Steve Teale <steve.teale britseyeview.com> writes:
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?
Nov 26 2011
parent Kagamin <spam here.lot> writes:
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.
Nov 26 2011