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