Postgres and other database interfaces

[Erik Smith]

On Saturday, 24 February 2018 at 18:56:23 UTC, Denis F wrote:
> On Saturday, 24 February 2018 at 05:45:45 UTC, rikki cattermole 
> wrote:
>> There is plenty of desire to build a generalized SQL interface 
>> for Phobos.
>>
>> But somebody needs to do it and it won't be all that much fun 
>> to do.
>
> I want to dwell on this.
>
> It seems to me that this is a common misconception that it is 
> possible to create a good universal tool for accessing the 
> database in place of the many existing ones.
>
> SQL is something like BASIC from the 70-80s: there is no single 
> standard, there are many dialects. And the differences are both 
> syntactic and deep, for example, Postgres and Oracle 
> differently interpret the ANSI standard concerning 
> transactions. The same applies to the connection layer: some of 
> the databases allow you to organize a gradual download of the 
> results, some - feedback in the form of something like 
> "events", etc. Also, Postgres quarantiees that result of query 
> will be immutable and this is very useful, but some one another 
> DB maybe not.
>
> At best, such a tool will be suitable for simple trivial things.
>
> On the other hand, needing  for this is also not obvious - 
> databases in real projects isn't switched usually every 
> week/month because they are not interchangeable, except for 
> some very simple cases. Usually RDBMS engine should be elected 
> based on your requiriments before you start to use it.


I agree that the differences in both the client interface and the 
underlying SQL support are complicated issues.  However, it seems 
pretty clear that languages have some level of standardized db 
interface have benefited enormously in terms of adoption for 
building real word applications (JDBC for instance).  I think it 
is true people don't switch databases that much, but having a 
standard interface (especially one that is easy to use) that 
covers the most common uses cases does make the use of the 
platform itself that much more approachable.

Some of the differences can be hidden internally for the basic 
use case (such as gradual downloads) and optionally available as 
a introspective capability when the underlying driver supports it 
for more advanced usage.   The goal is to expose as much 
capability without overly complicating the interface.  This is 
easier to do now with modern abstractions then it was with ODBC 
in the 90's.

It would be good, however, to keep track of the specific problem 
areas that you mentioned to ensure that they are addressed well.

If one thing is clear from this thread, documentation is 
critical. I'm also aware that vibed support, for the databases 
that can most readily support it, is also a high priority.

erik