4 ms·

> Unfortunately there’s too much institutional inertia behind SQL for any of the DBMS vendors to go-it-alone, and beyond trivial queries all of the SQL implemen

> Unfortunately there’s too much institutional inertia behind SQL for any of the DBMS vendors to go-it-alone, and beyond trivial queries all of the SQL implementations are incompatible with each other anyway. I'm not sure which alternative query language is sufficiently general to match SQL. A datalog implementation on top of Postgres (via libpq?) would be really interesting though.

DaiPlusPlus 6y ago
I’m saying we should keep SQL’s fundamental model, but overhaul the syntax and change some default semantics to eliminate nasty surprises and design it to encourage best-practices (I.e. pit-of-success). For example, this: ``` SELECT u.UserId AS user_userid, u.UserName AS user_userName, u.DisplayName AD user_displayName, u.Etc... this is tedious, d.DocumentId AS doc_documentId, d.etc FROM dbo.Documents AS d INNER JOIN dbo.Users AD u ON d.CreatorUserId = u.UserId WHERE d.Created >= @today AND d.Foobar IS NOT NULL AND d.ReviewerUserId IS DISTINCT FROM u.UserId ``` Could be represented using this hypothetical syntax: ``` from dbo.Users u dbo.Documents d join 1:m on u = d auto where d.Created >= @today & d.ReviewerUserId != u.UserId select u.* as user_, d. as doc_* ``` * The “1:m” (and “0:m”, “m:m”, “1:1”, etc) syntax would be used to both declare the type of JOIN needed and to assert the multiplicity of the JOIN (e.g. “1:1” would be an INNER JOIN with a one-to-one multiplicity, so the query engine would reject the query if the join uses non-unique/key columns, and “1>m” would be a LEFT OUTER JOIN with a one-to-many multiplicity). * The “auto” keyword means the exact join comparison can be omitted if there already is a foreign-key relationship between the two tables. * The “foo.* AS foo_*” syntax allows for the prefixing of all column names without needing to manually list them. I also want to allow for saying “select all columns EXCEPT these specific columns”.
- zenhack 6y ago
  I question the sensibility of having the primary interface to a database be a query language as such; It's a pain to work with programmatically. I agree with the notion of starting from relational algebra, but rather than the interface being a human-oriented textual syntax, the protocol should be defined in terms of some widely supported serialization format (take your pick). This makes it easier to write query-builder libraries, which the humans can use. You really don't want to be doing string substitution to put queries together.
mmarx 6y ago
> I'm not sure which alternative query language is sufficiently general to match SQL. A datalog implementation on top of Postgres (via libpq?) would be really interesting though. Datalog is already more general that SQL, though; even WITH RECURSIVE only gets you linear Datalog.

DaiPlusPlus 6y ago

I’m saying we should keep SQL’s fundamental model, but overhaul the syntax and change some default semantics to eliminate nasty surprises and design it to encourage best-practices (I.e. pit-of-success). For example, this: ``` SELECT u.UserId AS user_userid, u.UserName AS user_userName, u.DisplayName AD user_displayName, u.Etc... this is tedious, d.DocumentId AS doc_documentId, d.etc FROM dbo.Documents AS d INNER JOIN dbo.Users AD u ON d.CreatorUserId = u.UserId WHERE d.Created >= @today AND d.Foobar IS NOT NULL AND d.ReviewerUserId IS DISTINCT FROM u.UserId ``` Could be represented using this hypothetical syntax: ``` from dbo.Users u dbo.Documents d join 1:m on u = d auto where d.Created >= @today & d.ReviewerUserId != u.UserId select u.* as user_, d. as doc_* ``` * The “1:m” (and “0:m”, “m:m”, “1:1”, etc) syntax would be used to both declare the type of JOIN needed and to assert the multiplicity of the JOIN (e.g. “1:1” would be an INNER JOIN with a one-to-one multiplicity, so the query engine would reject the query if the join uses non-unique/key columns, and “1>m” would be a LEFT OUTER JOIN with a one-to-many multiplicity). * The “auto” keyword means the exact join comparison can be omitted if there already is a foreign-key relationship between the two tables. * The “foo.* AS foo_*” syntax allows for the prefixing of all column names without needing to manually list them. I also want to allow for saying “select all columns EXCEPT these specific columns”.

zenhack 6y ago
I question the sensibility of having the primary interface to a database be a query language as such; It's a pain to work with programmatically. I agree with the notion of starting from relational algebra, but rather than the interface being a human-oriented textual syntax, the protocol should be defined in terms of some widely supported serialization format (take your pick). This makes it easier to write query-builder libraries, which the humans can use. You really don't want to be doing string substitution to put queries together.

mmarx 6y ago

> I'm not sure which alternative query language is sufficiently general to match SQL. A datalog implementation on top of Postgres (via libpq?) would be really interesting though. Datalog is already more general that SQL, though; even WITH RECURSIVE only gets you linear Datalog.