SQL working [ was Re: The sorry state of the D stack? ]

[Paulo Pinto]

On Monday, 8 October 2012 at 10:26:35 UTC, denizzzka wrote:
> On Monday, 8 October 2012 at 07:35:13 UTC, Paulo Pinto wrote:
>> On Sunday, 7 October 2012 at 20:05:22 UTC, denizzzka wrote:
>>> On Sunday, 7 October 2012 at 17:06:31 UTC, Joseph Rushton 
>>> Wakeling wrote:
>>>> On 10/07/2012 10:55 AM, Russel Winder wrote:
>>>>> Why only PostgreSQL. Shouldn't it also work with MySQL, 
>>>>> Oracle, DB2,
>>>>> PervasiveSQL, SQLite3, etc.?
>>>>
>>>> I don't have sufficient experience with SQL to be able to 
>>>> really make a judgement here, but is there a case for a 
>>>> std.sql or std.db that would provide a uniform D interface 
>>>> to the arbitrary DB of choice?
>>>
>>> Each database engine has a unique distinguishing features 
>>> that make this engine interesting. (for example, different 
>>> implementations of transactions - SQL standard does not 
>>> describe the SQL transactions precisely enough)
>>
>> There are plenty of existing interfaces to base D's design on, 
>> just a few of them:
>>
>> Perl - DBI
>> Python - DB API
>> C, C++ - ODBC (there is an UNIX variant of it)
>> C++ - OLE DB (Although Windows specific)
>> Java - JDBC
>> .NET - Data Providers
>> Ruby - DBI
>> TCL - TDBC
>> Go - database package
>> Delphi - Data Access
>> Haskell - HaskellDB (HDBC)
>>
>>>
>>> So, I do not know is it possible to make a universal 
>>> interface. And why it may need in real life?
>>
>>
>> At least in the enterprise world, we tend to write 
>> applications in a DB independent way.
>>
>> One reason is to be able to deploy the applications without 
>> forcing the customers to invest in new DB engines, thus 
>> reaching a broader client base.
>>
>> Sometimes inside the same organization different business 
>> units have different DB engines running (even different 
>> versions of the same DB).
>>
>> Finally, to minimize costs when management decides for 
>> whatever reason, to change the DB licenses being used.
>>
>> --
>> Paulo
>
> For this to work you need to implement an independent way to 
> create queries that would work on all database engines the same 
> way. I believe that this problem is in principle much more 
> complicated than it would have been implemented interfaces to 
> databases in separate libs.

Sure. That is why on top of a DB driver layer, usually you have 
some kind of SQL adaptation layer.

On the TCL/C abstraction layer we implemented for a product 
during the 1999-2001 timeframe, we used standard SQL '92 for all 
data queries, regardless of hand-written or generated from our 
TCL ORM.

Then there was a translation layer that transformed SQL '92 into 
DB specific SQL, before giving it to the corresponding driver.

The only two parts of the application that had DB specific code 
were the SQL transformation layer, and the .so/.dll with the DB 
specific driver.

With the added benefit that any DB fully SQL '92 compliant did 
not need any adaptations in the transformation layer.


--
Paulo