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.

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.

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
next sibling 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.

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

I think it has to be #2. If we just did #1 we would probably alienate
the Linux community.

#3 is a recipe for maintenance problems.

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

I definitely vote for #2 or #3. One of our projects at work uses it (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. We should probably explore #2 first, and then if it doesn't work well enough, then at least we have a solid base for the API's being similar with #3. 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 #2 gains us a whole lot over #3 ultimately (_especially_ if it ends up #costing much of anything in terms of performance or usability), but I'm not a DB expert, and I do think that it's at least worth exploring #2 first - if nothing else because it could lead to the APIs for #3 being better unified without harming their performance or usability. - Jonathan M Davis

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 of mysql prepared statements). I think with #2 we should be able to hit the 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 (http://forums.asp.net/p/1427310/3186427.aspx#3186427). I know there is ANSI 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. Even if #2 is completed #3 could still be worked on, if someone is willing. A common API is likely to miss some features from the specific APIs (I've only looked into mysql so this is an assumption). The implmentations from #3 could then be used to implement drivers for #2 allowing those who want flexibility to use #2 but those with more specific requirements, and bothered about performance, can use #3. That is more work than #2 or #3 separately but like I said "if someone is willing". My plan for my personal implementation was to develop specific APIs (#3) for MySql and Postgresql, trying to keep them similar where possible, then look into providing a wrapper api for common/basic functionality (#2). That 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 next 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
next sibling 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 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 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
prev 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 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.

I definitely vote for #2 or #3. One of our projects at work uses it (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.

We have some trouble with ODBC at work too.
 We should probably explore #2 first, and then if it doesn't work well enough,
 then at least we have a solid base for the API's being similar with #3.
 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
 #2 gains us a whole lot over #3 ultimately (_especially_ if it ends up costing
 much of anything in terms of performance or usability), but I'm not a DB
 expert, and I do think that it's at least worth exploring #2 first - if nothing
 else because it could lead to the APIs for #3 being better unified without
 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 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.

I definitely vote for #2 or #3. One of our projects at work uses it (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. We should probably explore #2 first, and then if it doesn't work well enough, then at least we have a solid base for the API's being similar with #3. 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 #2 gains us a whole lot over #3 ultimately (_especially_ if it ends up costing much of anything in terms of performance or usability), but I'm not a DB expert, and I do think that it's at least worth exploring #2 first - if nothing else because it could lead to the APIs for #3 being better unified without harming their performance or usability. - Jonathan M Davis
Oct 08 2011
prev sibling next 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=

 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=

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

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

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

 of the C database apis that we would have to cover.
=20
 Steve

Oct 08 2011
prev sibling next 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.

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 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 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 next 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 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.

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



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

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
prev sibling next sibling parent "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
prev sibling 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=

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



ystems lie in their SQL, which is strings from D's perspective.
=20
 Assuming that by "database" you mean SQL.  Pretty fair assumption, though=


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 =

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


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






 denominator
 approach. This is to not limit some databases. For example =






 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 =






 featureful
 databases and those that have less features may be easily adapted =






 that API.

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





 least one category - gui libraries?

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




database systems lie in their SQL, which is strings from D's = perspective.
=20
 Assuming that by "database" you mean SQL.  Pretty fair assumption, =



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 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
next sibling 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 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 parent 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
prev sibling next sibling parent 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
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 next sibling parent "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
prev sibling 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 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 next sibling parent 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
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 next 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();
 }

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:

 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:

 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 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:

 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 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:

 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:

 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 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:

 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
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 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 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 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:

 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:

 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 next sibling parent 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:

 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
prev sibling next 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:

 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 "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
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:

 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/





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

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 "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:

 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
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
next sibling parent 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
prev sibling 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 when accessing databases in C#. It sounds like doing this from D will be 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 next 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 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 ?


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,


 the top one written by Jacob.


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


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,


 the top one written by Jacob.


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


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,


 the top one written by Jacob.


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


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
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,


 the top one written by Jacob.


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


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 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
prev sibling next sibling parent 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
prev sibling 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 next sibling parent "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
prev sibling next sibling parent 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
prev sibling 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 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 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
prev sibling 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 next 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 "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
prev sibling 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
parent 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
prev sibling next 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 next sibling parent 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.
 

http://www.mysql.com/about/legal/licensing/foss-exception/ Steve
Oct 17 2011
prev sibling next sibling 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
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 next 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 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 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 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 next sibling 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.

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 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 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
prev sibling next sibling parent 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
prev sibling next sibling 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 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 next sibling parent 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
prev sibling next 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 "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
prev 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 next sibling parent "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
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
parent 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.


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
prev sibling 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.
 
 

intended to use text. Steve
Oct 20 2011
prev sibling next 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:
 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.


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
prev sibling next sibling 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.


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
parent 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
prev sibling 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 next sibling parent "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
prev sibling 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 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
next sibling parent 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
prev sibling 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