4 ms·

One thing I particularly dislike about SQL is DDL and all the system-specific extensions (e.g. for access control). I don't really get why there is a separate t

One thing I particularly dislike about SQL is DDL and all the system-specific extensions (e.g. for access control). I don't really get why there is a separate thing like "ALTER TABLE foo DROP COLUMN bar;" if you could have "DELETE FROM schema.foo WHERE name = 'bar';". 90 % of DDL is just duplicating basic DML functionality, Except For That Special Kind Of Object Which Totally Smells Like It's Relational But It's Not. Especially for access control - why is that just not a table? I'm sure it literally is one. This makes SQL needlessly complex and tools to work with schemas far more complicated than they actually have to be. In Lisp, most things are a list/pair. In SQL, most things should have been a table.

rhacker 5y ago
That would be pretty nice, insert into schema.users (type, name, columns) values ('btree_index', 'index_dob', 'dob DESC, name ASC')
inopinatus 5y ago
In many RDBMS this is in fact the case. Some take it up a notch, for example in postgresql even types are represented as a relation in the system catalog. However, a system schema is necessarily very close to the database’s architecture and capabilities, making it implementation specific; commonality of underlying structure doesn’t really emerge from relational algebra because it is a) primarily a query paradigm and b) SQL is only a very loose adherent. In practice, woe betide the DBA that writes “insert into pg_enum” or “insert into pg_database” without knowing all the extra stuff that CREATE <anything> actually does. The DDL is also implementation specific but there’s a much higher degree of practical commonality. When writing application db migrations, and especially when supporting multiple backend databases, I’d definitely rather compose/generate DDL than engage with the minutiae of each.
talaketu 5y ago
You might find some eminent agreement[1], though to be fair he was talking about relational DBMS, not SQL DBMS. See rule 0 and 1: Even table names, column names and domain names are represented as character strings in some tables. Tables containing such names are normally part of the built-in system catalog. The catalog is accordingly a relational data base itself — one that is dynamic and active and represents the metadata (data describing the rest of the data in the system) [1] The Twelve Rules, E.F.Codd https://reldb.org/c/index.php/twelve-rules/ https://reldb.org/c/index.php/twelve-rules/
CRConrad 5y ago
Is that you, Bryce?

rhacker 5y ago

That would be pretty nice, insert into schema.users (type, name, columns) values ('btree_index', 'index_dob', 'dob DESC, name ASC')

inopinatus 5y ago

In many RDBMS this is in fact the case. Some take it up a notch, for example in postgresql even types are represented as a relation in the system catalog. However, a system schema is necessarily very close to the database’s architecture and capabilities, making it implementation specific; commonality of underlying structure doesn’t really emerge from relational algebra because it is a) primarily a query paradigm and b) SQL is only a very loose adherent. In practice, woe betide the DBA that writes “insert into pg_enum” or “insert into pg_database” without knowing all the extra stuff that CREATE <anything> actually does. The DDL is also implementation specific but there’s a much higher degree of practical commonality. When writing application db migrations, and especially when supporting multiple backend databases, I’d definitely rather compose/generate DDL than engage with the minutiae of each.

talaketu 5y ago

You might find some eminent agreement[1], though to be fair he was talking about relational DBMS, not SQL DBMS. See rule 0 and 1: Even table names, column names and domain names are represented as character strings in some tables. Tables containing such names are normally part of the built-in system catalog. The catalog is accordingly a relational data base itself — one that is dynamic and active and represents the metadata (data describing the rest of the data in the system) [1] The Twelve Rules, E.F.Codd https://reldb.org/c/index.php/twelve-rules/ https://reldb.org/c/index.php/twelve-rules/

CRConrad 5y ago

Is that you, Bryce?