5 ms·

Actually I think the motivation is that the schema exists in code rather than in the storage engine. That gives the amazing productivity boost of not having to

by meaty 14y ago

Actually I think the motivation is that the schema exists in code rather than in the storage engine. That gives the amazing productivity boost of not having to load the schema into the database engine - something you appreciate when you hit a big 2000 table / 45000 stored proc sized mess. It's another step towards statelessness. Basically schema in code scales development-wise better than schema in storage engine.

ucee054 14y ago
And is wonderful for consistency and security! </sarcasm>
- meaty 14y ago
  What if the application doesn't require consistency or enforces it itself? Don't write consistency off straight away as a requirement. Look at how banks work for an example. As for security, that belongs in your application layer/domain model or facade if you have one, not inside your storage engine. It won't scale in there.
  - ucee054 14y ago
    You don't care about your security scaling. You care about your security being secure. Maybe after you have that, then you start caring about it scaling. (If you really need storage to scale, buy a TeraData RDBMS. It will scale beyond your wildest dreams.) If your security is in your storage engine, it is easier to secure because you only have to get it right in that one place. If your security is in the apps that uses the data stored in your engine, you have to get every single such app bulletproof. The attack surface is much larger and much worse in the second case. As for security, that belongs in your application layer/domain model -Unforgivable.
    - meaty 14y ago
      We're talking web apps here and our security had to be at application level because of complexity. Name one DBMS which handles multitenant security with acls and can handle 22,000 requests a second.
      - jeltz 14y ago
        It obviously all depends on what the 22 000 requests do. But PostgreSQL can scale to ten times that on a single server in synthetic benchmarks. http://rhaas.blogspot.se/2012/04/did-i-say-32-cores-how-about-64.html http://rhaas.blogspot.se/2012/04/did-i-say-32-cores-how-abou...
j-kidd 14y ago
Just because you got yourself into the mess, doesn't mean that database schema is suddenly bad. Data definition language (DDL) is a declarative and well thought-out DSL that can guarantee the business logic to a certain extent, at a very very low cost. On the other hand, stored proc is a relic of the integration database [1], and is definitely less flexible compared to using your programming language of choice. Instead of jumping from 2000 tables / 45000 stored procs straight to "schema in code", perhaps a saner tables design with no stored proc is the better way to go? [1] http://www.martinfowler.com/bliki/IntegrationDatabase.html http://www.martinfowler.com/bliki/IntegrationDatabase.html
- meaty 14y ago
  Yes were replacing stored procedures. First (as the app is partioned into logical components), we pick a chunk and move it to schema generation with hibernate. Then we port the stored procedures to code with tests, then we delete all the stored procedures. We've gone from 0 test cases to 82,000 test cases in 6 years (we have 80 people in our developer pool so this is not some one man effort). For ref, I inherited this mess (mainly vb6/com) and am responsible for replacing it all.

by meaty 14y ago

ucee054 14y ago

And is wonderful for consistency and security! </sarcasm>

meaty 14y ago
What if the application doesn't require consistency or enforces it itself? Don't write consistency off straight away as a requirement. Look at how banks work for an example. As for security, that belongs in your application layer/domain model or facade if you have one, not inside your storage engine. It won't scale in there.
- ucee054 14y ago
  You don't care about your security scaling. You care about your security being secure. Maybe after you have that, then you start caring about it scaling. (If you really need storage to scale, buy a TeraData RDBMS. It will scale beyond your wildest dreams.) If your security is in your storage engine, it is easier to secure because you only have to get it right in that one place. If your security is in the apps that uses the data stored in your engine, you have to get every single such app bulletproof. The attack surface is much larger and much worse in the second case. As for security, that belongs in your application layer/domain model -Unforgivable.
  - meaty 14y ago
    We're talking web apps here and our security had to be at application level because of complexity. Name one DBMS which handles multitenant security with acls and can handle 22,000 requests a second.
    - jeltz 14y ago
      It obviously all depends on what the 22 000 requests do. But PostgreSQL can scale to ten times that on a single server in synthetic benchmarks. http://rhaas.blogspot.se/2012/04/did-i-say-32-cores-how-about-64.html http://rhaas.blogspot.se/2012/04/did-i-say-32-cores-how-abou...

j-kidd 14y ago

Just because you got yourself into the mess, doesn't mean that database schema is suddenly bad. Data definition language (DDL) is a declarative and well thought-out DSL that can guarantee the business logic to a certain extent, at a very very low cost. On the other hand, stored proc is a relic of the integration database [1], and is definitely less flexible compared to using your programming language of choice. Instead of jumping from 2000 tables / 45000 stored procs straight to "schema in code", perhaps a saner tables design with no stored proc is the better way to go? [1] http://www.martinfowler.com/bliki/IntegrationDatabase.html http://www.martinfowler.com/bliki/IntegrationDatabase.html

meaty 14y ago
Yes were replacing stored procedures. First (as the app is partioned into logical components), we pick a chunk and move it to schema generation with hibernate. Then we port the stored procedures to code with tests, then we delete all the stored procedures. We've gone from 0 test cases to 82,000 test cases in 6 years (we have 80 people in our developer pool so this is not some one man effort). For ref, I inherited this mess (mainly vb6/com) and am responsible for replacing it all.