On Sunday, 25 February 2018 at 15:34:00 UTC, Joe wrote:
> On Sunday, 25 February 2018 at 09:07:34 UTC, Denis F wrote:
>> Libraries already are similar automatically because RDBMSes uses similar principles. But it is impossible to make libraries absolutely identical due to the presence of important significant nuances in each engine.
>
> I don't see how making libraries compatible with each other, i.e., complying with some standard, will result in losing functionality.  What I'm talking about is standardizing the names of certain classes and their methods, exceptions, and perhaps some datatypes and the expected order of certain arguments.  For example, instead of having classes named Database (sqlite3), PGConnection (ddb) and Connection (dpq2, mysql-native) there's just one name, and that class is expected to have certain methods.

I guess this is problem too: sqlite isn't connects at all, and its struct represents RDBMS itself. But other should represent only connections because they manipulate the connection and not controls its server, and connection is a potential source of connection-related exceptions. This is a subtle but important difference too.

> The library implementor can add others. For example, I see mysql-native has a connection pool and that doesn't need to be part of the standard. Also, some methods can be specified as being optional by the standard, e.g., something like callproc, because not all DBMSs support procedures.

This is another confirmation is that there are more differences than similarities  between databases. :-)

>
>> Ok, I propose to be consistent and ask for compliance with the Standard from the RDBMSes. For example, arguments substitution:
>>
>> MySQL uses the '?'
>> PostgreSQL uses $1
>> SQLite accepts both
>> Oracle uses a :name
>>
>> (Really, it is very important to come to an agreement here because, for sure, the next obvious step is writing an ORM generator on top of the idea what you are proposing.)
>
> In PEP 249, this was left up to the implementors (see the paramstyle global), but a footnote suggests that some styles are supposed to be preferred over others.
>
>> For example, I would not change unobvious at first sight system of classes "Result" and inherited from it class "Answer" for the sake of similarity to other libraries. Simply because this is the way what Postgres client library works, and if this classes will be removed some significant functionality will be lost.
>> I'm sure that in other libraries there is something similar.
>
> I'm not sure I understand your example, but perhaps this is getting too specific and close to the discussions that would need to take place betweeen existing implementors.  There has to be a negotiation process for a standard to be developed.  In my example above, suppose the sqlite3 library developer says "I don't want to have a class named Connection, because my users don't connect to a server".  It will be up to the other developers to convince him or to arrive at some compromise

What are these sacrifices for? I do not like idea that I should drop some functionality of Postgres for conpatibility with another RDBMSes. Personally, I like specific databases because it have nuances of functionality that they provide to me.

And those who wish to "crossbreeding a hedgehog with a snake", at first, can try to write a wrapper around existing SQL libraries. This is faster and more humane in relation to the developers.

On Sunday, 25 February 2018 at 11:46:26 UTC, Denis F wrote:
> But it is impossible to convert text :names or '?' into Postgres's "$1": Postgres isn't knows fields names at start of a query processing and you can't replace '?' to "$<num>" by simple 'replace' call - it will need full syntax parsing of Posgres SQL query because queries can contain EXECUTE statement (whose purpose is executing dynamic commands).

You don't have to parse the full syntax. Quote from another language (which also supports underscore in integer literals):

'Placeholders

There are three types of placeholders that can be used in DBD::Pg. The first is the "question mark" type, in which each placeholder is represented by a single question mark character. This is the method recommended by the DBI specs and is the most portable. Each question mark is internally replaced by a "dollar sign number" in the order in which they appear in the query (important when using "bind_param").

The second type of placeholder is "dollar sign numbers". This is the method that Postgres uses internally and is overall probably the best method to use if you do not need compatibility with other database systems. DBD::Pg, like PostgreSQL, allows the same number to be used more than once in the query. Numbers must start with "1" and increment by one value (but can appear in any order within the query). If the same number appears more than once in a query, it is treated as a single parameter and all instances are replaced at once. [...]

The final placeholder type is "named parameters" in the format ":foo". While this syntax is supported by DBD::Pg, its use is discouraged in favor of dollar-sign numbers.

The different types of placeholders cannot be mixed within a statement, but you may use different ones for each statement handle you have. This is confusing at best, so stick to one style within your program.

If your queries use operators that contain question marks (e.g. some of the native Postgres geometric operators and JSON operators) or array slices (e.g. data[100:300]), there are methods to instruct DBD::Pg to not treat some symbols as placeholders. First, you may simply add a backslash before the start of a placeholder, and DBD::Pg will strip the backslash and not treat the character as a placeholder.

You can also tell DBD::Pg to ignore any non-dollar sign placeholders by setting the pg_placeholder_dollaronly attribute at either the database handle or the statement handle level. [...] ' [1]

[1] http://search.cpan.org/~turnstep/DBD-Pg-3.7.4/Pg.pm#Placeholders

On Sunday, 25 February 2018 at 16:31:45 UTC, Denis F wrote:
> What are these sacrifices for? I do not like idea that I should drop some functionality of Postgres for conpatibility with another RDBMSes. Personally, I like specific databases because it have nuances of functionality that they provide to me.

Nobody said or implied anything about dropping Postgres functionality. If you can, please take a look at the contents of the Psycopg docs (http://initd.org/psycopg/docs/), the most popular Python Postgres adapter: after the entries for the connection and cursor classes, you'll see pyscopg2.extensions, pyscopg2.extras, psycopg2.pool and more. It even includes support for the recently added logical replication feature.

The "sacrifice" benefits the library implementors because it expands their potential market.  Even though you decry the differences between RDBMS, when it comes to the basics, I would argue that 80% of the code written by a SQL DB developer is very similar: connect to the DBMS (or to a file for sqlite), issue a query, retrieve results, update some rows.  If they're writing in C, D, Python, Ruby or whatever, most of their time is spent writing or tweaking SQL statements.  We're not talking about MongoDB, Cassandra, OODBMS or other creatures that *do* have significant differences (it's telling that several have implemented SQL layers on top).

> And those who wish to "crossbreeding a hedgehog with a snake", at first, can try to write a wrapper around existing SQL libraries. This is faster and more humane in relation to the developers.

Please take a look at the list of RDBMSs supported by SQLAlchemy (http://docs.sqlalchemy.org/en/latest/core/engines.html#supported-databases).  I'm *not* in favor of ORMs, but do you think that Mike Bayer (the implementor) would have had any incentive to write an ORM if a DB-API hadn't existed about 10 years before (v1 I believe is circa 1996, v2 from 1999, SQLA came out in 2006 and it already supported four DBMSs).  Even if he had had the incentive it definitely would've been much slower and error-prone, because he would have had to not only design and implement an ORM but also have to deal with idiosyncracies of each library, whereas now he can support multiple libraries for each DBMS, e.g., psycopg2 and pg8000 for Postgres, because they both conform to the same API.

You're obviously reluctant of having to rewrite dpq2 just to "play along with the other kids".  I can understand that, but who knows, maybe your design is 90% of what the others want/have/like and you may only have to make some trivial changes here and there.

In any case, as I mentioned earlier, I will delve into dpq2 (and vibe-d-postgresql) for my own project.  So I'll keep in touch.

Joe

On Sunday, 25 February 2018 at 19:29:59 UTC, kdevel wrote:
> On Sunday, 25 February 2018 at 11:46:26 UTC, Denis F wrote:
>> But it is impossible to convert text :names or '?' into Postgres's "$1": Postgres isn't knows fields names at start of a query processing and you can't replace '?' to "$<num>" by simple 'replace' call - it will need full syntax parsing of Posgres SQL query because queries can contain EXECUTE statement (whose purpose is executing dynamic commands).
>
> You don't have to parse the full syntax.

You must if you want to make a replacement correctly for all types of statements.

> Quote from another language (which also supports underscore in integer literals):

"This language" just drops off supporting of dynamic commands by using prepared statements for requests with arguments:

"WARNING: DBD::Pg now (as of version 1.40) uses true prepared statements by sending them to the backend to be prepared by the Postgres server. Statements that were legal before may no longer work."

It is trivial to replace any placeholders if you use only prepared statements, yes. But if you after that try to call procedure which calls EXECUTE IMMEDIATE command inside it will fail! How many of your users will understand why this happens?

Also I see too high level of abstraction in DBD::Pg. I am isn't add prepared statement wrappers into dpq2 because its implementation depends from async queries handling implementation.

dpq2 is "middle-level" - C calls and classes wrappers over some of it.

On Sunday, 25 February 2018 at 20:56:16 UTC, Joe wrote:
> On Sunday, 25 February 2018 at 16:31:45 UTC, Denis F wrote:
>> What are these sacrifices for? I do not like idea that I should drop some functionality of Postgres for conpatibility with another RDBMSes. Personally, I like specific databases because it have nuances of functionality that they provide to me.
>
> Nobody said or implied anything about dropping Postgres functionality. If you can, please take a look at the contents of the Psycopg docs (http://initd.org/psycopg/docs/), the most popular Python Postgres adapter: after the entries for the connection and cursor classes, you'll see pyscopg2.extensions, pyscopg2.extras, psycopg2.pool and more. It even includes support for the recently added logical replication feature.

pyscopg2 passes request arguments as strings inside of it! This degrades type safety because in D we have types.

Looks like it is uses C format:

cur.execute("SELECT '%s', %s", (123,456,)) -- first arg expected as "%s" string
cur.fetchone()
('123', 456)

> You're obviously reluctant of having to rewrite dpq2 just to "play along with the other kids".  I can understand that, but who knows, maybe your design is 90% of what the others want/have/like

No, sure. dpq2 is too lowlevel for this. Users need transactions, prepared statements wrappers, etc. But I can't implement it in dpq2 because these things is depedend from other more high level implementations.

At least, for dpk2, I clearly see that the wrapper is the best choice.

On 26/02/2018 5:06 AM, Joe wrote:
> On Sunday, 25 February 2018 at 09:23:18 UTC, rikki cattermole wrote:
>> On IRC earlier today we discussed database libs a bit, we agreed that both "?" and "@name" needed to be supported. No other suggestions came up. We don't really need a third or fourth form I think.
> 
> "@name"? Which DBMS uses that? Although Denis said Postgres uses $1, PG also supports :name, in either the command line tool (psql) and the procedural language PL/pgSQL.  Personally, I prefer what Python's PEP 249 calls 'format' (%s) or 'pyformat' (%(name)s).  Note that although the %s appears in the SQL string, the actual arguments are passed separately at invocation time, as either tuples or dicts (associative arrays).
> 
> Joe

ADO.net uses it, and since we're trying to copy some of its features, that is what came up.