3 ms·

I am doing this at work, I have an application with all the business logic in the database. It is a huge hassle though... I am versioning a bunch of scripts wit

I am doing this at work, I have an application with all the business logic in the database. It is a huge hassle though... I am versioning a bunch of scripts with git but it really isn't convenient. The thing is that a database already stores data but all the schematics and ddl stuff would be much more conveniently kept in descriptive plaintext.

formerly_proven 5y ago
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?
programmarchy 5y ago
Curious how far you’ve gone with this. You could have your CI/CD sync your stored procedures. But you would probably want to go further than that and manage them with a tool like db-migrate so you can keep your stored procedures tracking with your schema changes rather than just versioning them. At the end of the day, can you really separate your stored procedures from the data they operate on? I would say no you can’t.

formerly_proven 5y ago

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?

programmarchy 5y ago

Curious how far you’ve gone with this. You could have your CI/CD sync your stored procedures. But you would probably want to go further than that and manage them with a tool like db-migrate so you can keep your stored procedures tracking with your schema changes rather than just versioning them. At the end of the day, can you really separate your stored procedures from the data they operate on? I would say no you can’t.