4 ms·

it sounds like you're making an interesting point but I'm afraid I don't follow, could you elaborate?

by cerved 5y ago

jiggawatts 5y ago
The "bad" design that keeps cropping up over and over is the second system effect: Tables, relationships, and columns defined as data in a few simple tables, instead of being defined explicitly in the SQL schema as expected. This is less than optimal for lots of reasons: duplication of metadata, inefficient query plans, no foreign keys, inability to use most kinds of indexes effectively, etc... However, the need is real: the ability to easily extend or generate table schemas without having to switch languages. You can argue that this is "not that hard", but you'd be absolutely wrong. It's obscenely difficult. I've tried, failed, and have given up. One use-case I had was automatically generating tables for importing data from PowerShell. My goal was to be able to write something like this: Get-Process | Export-Sql -ConnectionString '...' -TableName 'Processes' And have the "Export-Sql" command automatically generate the table schema on the fly based on the input columns it sees. I even wanted to be able to represent object hierarchies as sets of related tables with parent-child foreign key relationships automatically put in. I got a proof-of-concept working, but there were just too many edge-cases. Things like maximum lengths for key or index columns, maximum row size, inability to switch column types on the fly, etc... So what to do other people do? They also give up and resort to defining a single table that has the columns: "TableName, RowId, ColumnName, ColumnValue" and call it a day. I mean... what other options are there? Bang your head against the wall for months trying to deal with idiotic things like identifier length limits? Correctly escaping arbitrary input strings? Generating hundreds of distinct commands that cannot be parametrised and hope you don't have a SQL injection vulnerability lurking in there somewhere? It's nuts! Have you seen just how much code it takes to take an arbitrary table, one with dozens of foreign key references, several filtered multi-column indexes, and views that depend on it, and then insert a column in a specific position? You have to drop everything, copy the table, rename, and then recreate everything. Doing this in code would be... I dunno... a few hundred thousand lines? That's absurd. You're not doing it. I'm not doing it. Microsoft did it once for SQL Server Management Studio, and I bet they're not rewriting that code in a hurry! But go back to the "bad" schema example before: Is it really that bad? What if the database engine had the ability to store common prefixes just once, instead of repeating them for each row? What if the schema looked like this: Database, Owner, Table, Column, Value Starting to look familiar? A bit like [server].[database].[dbo].[Table] perhaps, familiar to every user of Microsoft SQL Server? There's a natural hierarchy for describing the data, that lends itself well to being represented as a B-Tree, along with the data itself! That's what the "bad" schema is doing: it's representing the data with the most natural representation! It's not wrong at all! The problem is that database engines have all sorts of features and optimisations that we want that isn't directly compatible with the naive implementation of the bad schema. Constraints, foreign keys, efficient storage, indexing, etc... However, none of these features are fundamentally incompatible with an API that merely "pretends" that the data is stored in a single flat list that can have its schema updated with a simple insert. The database engine could simply factor out the schema part and the data part into different physical storage layouts, with all the usual efficiencies such as not having to repeat column names for every row. To summarise: we could have our cake and eat it too. Database engines could be developed that use ordinary select/insert/delete/update statements to modify the schema, and have it perform just as efficiently as a database that uses clumsy statements like "alter table add column". In this world, a database schema upgrade could be as simple as a single literal "MERGE" statement!
- cerved 5y ago
  While schema creation SQL can be a be a bit unwieldy, I'm not sure I appreciate which part is the problem or what you're trying to achieve. Obviously you can SELECT * INTO FROM .. if you're just temporarily inserting data. I'm not sure I see the value in automatically importing arbitrary data into a schemad database object. I think it's too complicated to be carried out by the database and should probably be done by some other piece of software and under human supervision/guidance. Are you suggesting that INSERT/UPDATE statements also have the ability to modify objects? That sounds like it would add complexity without much gain as opposed to just running and alter table query. I'm not saying that the process of writing schema modifying queries is painless but I'm not sure I'm convinced that we can have the cake and eat it For your example I personally would've AWKED into a predefined schema or inserted the data as JSON
  - jiggawatts 5y ago
    > Obviously you can SELECT * INTO FROM .. if you're just temporarily inserting data Okay, bad example. I should have specified: Insert into an existing table, add the missing columns automatically. There are some special-cases where most database engines can automatically generate schemas, and "SELECT... INTO" is one of those few. Usually only allowed with an empty destination table. > I'm not sure I see the value in automatically importing arbitrary data into a schemad database object. There are lots of use-cases for this. You might not have them, but other people do all the time. Just about any large-scale enterprise software needs to be extensible in the field, for example. Think the likes of SAP or Siebel. I've seen similar problems crop up in CMDBs, job and ticket management software, etc... Famously, Jira is slow precisely because it is so bad at handling this kind of extensibility efficiently. > Are you suggesting that INSERT/UPDATE statements also have the ability to modify objects? Not necessarily, although that could be an option. What I mean is that changes to the table schema should be made using insert/update/delete statements, where the only "data" is things such as the column names, types, constraints, etc... > That sounds like it would add complexity without much gain as opposed to just running and alter table query. It would dramatically reduce complexity, removing an entire language from database engines, along with all the associated vendor-specific syntax, quirks, and limitations. In fact, the same "schema" that is used in the wire protocol could be directly equal to the actual schema, and its update language. So if you get back a query result (with data), and want to create a copy of that schema elsewhere (e.g.: a local cache database), then you just take the "header" from the result set and "insert" it into the destination database schema. No conversion, no escaping, nothing. Seriously: Try this as an exercise. Use Java or C#, write a select statement from a query (that has join, views, etc...), and then write the code that generates a table to cache this data in a separate, local database instance. Do it. Sit down and give it a good go. You won't appreciate how hard this is until you do! Now write the code to update the cache table dynamically if the source table changes. Assume that table has a petabyte of data. (That's why it's a table, it's a cache for something that's far too big to fit in memory!) Now write the code to do all of the above with foreign constraints. Good luck! > I'm not sure I'm convinced that we can have the cake and eat it There is a reason NoSQL databases exist and are wildly popular. It's not that "schemaless" is truly better so often, it's more that modifying the schema in most DBMS offerings is so fiddly that it's essentially impossible to do programmatically. > or inserted the data as JSON NoSQL in a nutshell! You haven't solved the problem, you've given up and resorted to schemaless tables instead, exactly the "bad" example above with all of its limitations and issues. I've seen some good attempts at solving this problem, but they barely scratch the surface.

it sounds like you're making an interesting point but I'm afraid I don't follow, could you elaborate?

by cerved 5y ago

it sounds like you're making an interesting point but I'm afraid I don't follow, could you elaborate?

The "bad" design that keeps cropping up over and over is the second system effect: Tables, relationships, and columns defined as data in a few simple tables, instead of being defined explicitly in the SQL schema as expected. This is less than optimal for lots of reasons: duplication of metadata, inefficient query plans, no foreign keys, inability to use most kinds of indexes effectively, etc... However, the need is real: the ability to easily extend or generate table schemas without having to switch languages. You can argue that this is "not that hard", but you'd be absolutely wrong. It's obscenely difficult. I've tried, failed, and have given up. One use-case I had was automatically generating tables for importing data from PowerShell. My goal was to be able to write something like this: Get-Process | Export-Sql -ConnectionString '...' -TableName 'Processes' And have the "Export-Sql" command automatically generate the table schema on the fly based on the input columns it sees. I even wanted to be able to represent object hierarchies as sets of related tables with parent-child foreign key relationships automatically put in. I got a proof-of-concept working, but there were just too many edge-cases. Things like maximum lengths for key or index columns, maximum row size, inability to switch column types on the fly, etc... So what to do other people do? They also give up and resort to defining a single table that has the columns: "TableName, RowId, ColumnName, ColumnValue" and call it a day. I mean... what other options are there? Bang your head against the wall for months trying to deal with idiotic things like identifier length limits? Correctly escaping arbitrary input strings? Generating hundreds of distinct commands that cannot be parametrised and hope you don't have a SQL injection vulnerability lurking in there somewhere? It's nuts! Have you seen just how much code it takes to take an arbitrary table, one with dozens of foreign key references, several filtered multi-column indexes, and views that depend on it, and then insert a column in a specific position? You have to drop everything, copy the table, rename, and then recreate everything. Doing this in code would be... I dunno... a few hundred thousand lines? That's absurd. You're not doing it. I'm not doing it. Microsoft did it once for SQL Server Management Studio, and I bet they're not rewriting that code in a hurry! But go back to the "bad" schema example before: Is it really that bad? What if the database engine had the ability to store common prefixes just once, instead of repeating them for each row? What if the schema looked like this: Database, Owner, Table, Column, Value Starting to look familiar? A bit like [server].[database].[dbo].[Table] perhaps, familiar to every user of Microsoft SQL Server? There's a natural hierarchy for describing the data, that lends itself well to being represented as a B-Tree, along with the data itself! That's what the "bad" schema is doing: it's representing the data with the most natural representation! It's not wrong at all! The problem is that database engines have all sorts of features and optimisations that we want that isn't directly compatible with the naive implementation of the bad schema. Constraints, foreign keys, efficient storage, indexing, etc... However, none of these features are fundamentally incompatible with an API that merely "pretends" that the data is stored in a single flat list that can have its schema updated with a simple insert. The database engine could simply factor out the schema part and the data part into different physical storage layouts, with all the usual efficiencies such as not having to repeat column names for every row. To summarise: we could have our cake and eat it too. Database engines could be developed that use ordinary select/insert/delete/update statements to modify the schema, and have it perform just as efficiently as a database that uses clumsy statements like "alter table add column". In this world, a database schema upgrade could be as simple as a single literal "MERGE" statement!

cerved 5y ago
While schema creation SQL can be a be a bit unwieldy, I'm not sure I appreciate which part is the problem or what you're trying to achieve. Obviously you can SELECT * INTO FROM .. if you're just temporarily inserting data. I'm not sure I see the value in automatically importing arbitrary data into a schemad database object. I think it's too complicated to be carried out by the database and should probably be done by some other piece of software and under human supervision/guidance. Are you suggesting that INSERT/UPDATE statements also have the ability to modify objects? That sounds like it would add complexity without much gain as opposed to just running and alter table query. I'm not saying that the process of writing schema modifying queries is painless but I'm not sure I'm convinced that we can have the cake and eat it For your example I personally would've AWKED into a predefined schema or inserted the data as JSON
- jiggawatts 5y ago
  > Obviously you can SELECT * INTO FROM .. if you're just temporarily inserting data Okay, bad example. I should have specified: Insert into an existing table, add the missing columns automatically. There are some special-cases where most database engines can automatically generate schemas, and "SELECT... INTO" is one of those few. Usually only allowed with an empty destination table. > I'm not sure I see the value in automatically importing arbitrary data into a schemad database object. There are lots of use-cases for this. You might not have them, but other people do all the time. Just about any large-scale enterprise software needs to be extensible in the field, for example. Think the likes of SAP or Siebel. I've seen similar problems crop up in CMDBs, job and ticket management software, etc... Famously, Jira is slow precisely because it is so bad at handling this kind of extensibility efficiently. > Are you suggesting that INSERT/UPDATE statements also have the ability to modify objects? Not necessarily, although that could be an option. What I mean is that changes to the table schema should be made using insert/update/delete statements, where the only "data" is things such as the column names, types, constraints, etc... > That sounds like it would add complexity without much gain as opposed to just running and alter table query. It would dramatically reduce complexity, removing an entire language from database engines, along with all the associated vendor-specific syntax, quirks, and limitations. In fact, the same "schema" that is used in the wire protocol could be directly equal to the actual schema, and its update language. So if you get back a query result (with data), and want to create a copy of that schema elsewhere (e.g.: a local cache database), then you just take the "header" from the result set and "insert" it into the destination database schema. No conversion, no escaping, nothing. Seriously: Try this as an exercise. Use Java or C#, write a select statement from a query (that has join, views, etc...), and then write the code that generates a table to cache this data in a separate, local database instance. Do it. Sit down and give it a good go. You won't appreciate how hard this is until you do! Now write the code to update the cache table dynamically if the source table changes. Assume that table has a petabyte of data. (That's why it's a table, it's a cache for something that's far too big to fit in memory!) Now write the code to do all of the above with foreign constraints. Good luck! > I'm not sure I'm convinced that we can have the cake and eat it There is a reason NoSQL databases exist and are wildly popular. It's not that "schemaless" is truly better so often, it's more that modifying the schema in most DBMS offerings is so fiddly that it's essentially impossible to do programmatically. > or inserted the data as JSON NoSQL in a nutshell! You haven't solved the problem, you've given up and resorted to schemaless tables instead, exactly the "bad" example above with all of its limitations and issues. I've seen some good attempts at solving this problem, but they barely scratch the surface.