---------- Forwarded message ---------
From: John Cowan <xxxxxx@ccil.org>
Date: Fri, Sep 20, 2019 at 7:01 AM
Subject: Re: Should the DBI layer be query language agnostic, even completely "ignorant"?
To: <xxxxxx@ancell-ent.com>


I think as long as the output is rectangular and the columns have names, that's all I need.  I don't care what the input language looks like.  

On Thu, Sep 19, 2019 at 5:05 PM <xxxxxx@ancell-ent.com> wrote:
From: John Cowan <xxxxxx@ccil.org>
Date: Thursday, September 19, 2019 3:34 PM

On Wed, Sep 18, 2019 at 3:10 PM <xxxxxx@ancell-ent.com> wrote:

I'm not sure myself, but I see the following advantages if it can be  pulled off without significantly increasing complexity for the user:

- Could provide a common interface for some fraction of non-SQL databases more sophisticated than KVMs that have similar interaction styles, that is, it would be a true DBI, vs. a RDBI.

I think that if what's returned from the database is not (broadly construed) a relation with a (normally fixed) number of columns and a variable number of rows, it's going to need a very different API, which of course will share some parts with this API.  For example, if you are returning a portion of a graph, the notion of a result-set makes no sense.  Of course, there are relations outside relational databases, like single sheets of spreadsheets and CSV files.

Turns out your concern has been addressed.  For graphs, start here: https://neo4j.com/docs/cypher-manual/current/introduction/

That's the Cypher query language for the Neo4j graphs database, per those surveys a/the most popular graph database, and from their web site they really seem to have their act together.

For Apache Cassandra's type of database, per Wikipedia: https://en.wikipedia.org/wiki/Wide_column_store

A wide column store is a type of NoSQL database. It uses tables, rows, and columns, but unlike a relational database, the names and format of the columns can vary from row to row in the same table. A wide column store can be interpreted as a two-dimensional key-value store.

Again from Wikipedia (I know, I know): https://en.wikipedia.org/wiki/Apache_Cassandra

CREATE KEYSPACE MyKeySpace
  WITH REPLICATION = { 'class' : 'SimpleStrategy', 'replication_factor' : 3 };

USE MyKeySpace;

CREATE COLUMNFAMILY MyColumns (id text, Last text, First text, PRIMARY KEY(id));

INSERT INTO MyColumns (id, Last, First) VALUES ('1', 'Doe', 'John');

SELECT * FROM MyColumns;

Which gives:

 id | Last | First
----+------+------
  1 | Doe  | John

(1 rows)

Both look sane, I'm tentatively going to try mixing these two in with PostgreSQL and SQLite.  For that matter, PostgreSQL's query language gets a bit funky when it looks into JSON objects: http://www.postgresqltutorial.com/postgresql-json/

- Harold