5 ms·

Honestly, I've never worked in a shop what would have let anyone design an application where clients were allowed to write stored procedures or views back to th

by _jal 7y ago

Honestly, I've never worked in a shop what would have let anyone design an application where clients were allowed to write stored procedures or views back to the DB. Similarly, I'd never let a client modify the application logic running on my app servers. I mean, I can imagine it, but it sounds like painting "root me and do what you will with the corpse" on your front door.

derefr 7y ago
I can understand your point of view as a “database supremacist”—i.e. someone who thinks DBAs are priests who determine the canonical schema, where development teams (even within the same org) are supplicants to the DBA to get the schema changed (whereupon the schema is then changed for everyone else, who have to all just suck it up.) It’s a common viewpoint, and even a sensible one when managing DBMSes that aren’t “strong” enough to support the features required for virtualizing the schema (mostly: sprocs, schema-namespacing and search paths, low-overhead writable views that can “symlink” objects from one namespace into another, and user ACLs upon database objects where granting write access to a writable view means granting write access to the underlying object, while protecting the object itself.) The alternative viewpoint, is one where a DBMS isn’t its own Data Warehouse, but is rather a “relational transformation workspace” of a Data Lake. Consider BigQuery as a DBMS. Who determines BigQuery’s schema? Nobody. Each user has their own stuff in their own namespace, and users mount stuff from one-another’s namespaces into their own namespace. The job of the people who run BigQuery (or any other multi-tenant data-warehouse infrastructure of this type) is just to enable users to collaborate with one-another through the DBMS, where Data Definition “APIs” are contracts established between pairs of clients, rather than between a developer and the DBMS’s DBA. That was more the stance I was talking about with my parent post. But even under a Database-supremacist viewpoint, what I was talking about can still happen. Whether clients are pushing objects into the DB or not, as long as there are requirements to not break old clients, but also requirements to evolve the schema for new clients, the DBA will arrive at a set of constraints that can only be solved by doing exactly what these clients would do themselves, if given DDL privileges: to define separate “virtual namespaces” where the same object names refer to mostly-common but some slightly-different versions of the same database objects, in order to allow LegacyClient to see PolyfilledLegacySchema while NewClients see the regular, “true” schema (or vice-versa.) In any organization with a sufficiently-large team structure that must share data (picture e.g. Amazon with their store), there will be a number of teams (clients) with different conflicting constraints upon the same data set. This makes the job of the DBA managing their shared data, very similar to the job of an upstream library software developer: you must keep the library’s API stable so that existing downstream clients won’t break; while also making changes to those APIs such that new downstream clients can get the features they’re demanding. Sure, there are alternate ways of solving this stable+evolving constraint problem for shared data, just like there are alternate ways of solving the stable+evolving constraint problem for a software library. You can just build a database that’s “downstream” of your own, that decorates the data with the new features (or tell the client that needs it to do it themselves!), and which syncs changes back to the upstream DB in the upstream’s canonical format. This is like building one library to wrap another. But this is, in the end, a lot of extra complexity that introduces the chance of data corruption along the way. Much safer to warehouse your data in one system with one MVCC world-clock. It might be a requirement if your upstream DBMS is “weak” (e.g. some legacy mainframe DB2 instance) but modern DBMSes give you every feature you need to provide multiple interfaces from a single instance. Why not take advantage? ——— And, if you can accept that argument—and you also accept the idea that clients should be able to use the DBMS as a scratch workspace for their queries (i.e. that creating temporary tables isn’t a perversion of what DBMSes stand for)—then combining the two results in the idea that each client could, in theory, just tell the DBMS of its particular needs for an API to access a shared dataset, and that this would result in the automatic creation of a virtual namespace perfectly suited to that client’s needs. Sure, let’s pretend the client is specifying this virtual namespace using some sort of abstract specification language, where the DBMS takes it, parses it, and then has the power to satisfy those requirements however it wants, maybe just by directing the client to the nearest matching existing virtual namespace, etc. But think about what exactly clients would need to send in these requirements specifications for a long time, you’ll realize that the specs will still turn out to need exactly the level power of regular SQL DDL statements. You don’t need a shim layer; you just need a sufficiently-granular ACL system, such that the client can create “virtual stuff” for their own use, but can’t create or interfere with “real stuff”, or with other clients. (And then it turns out that, since the client would just be communicating its needs via SQL DDL anyway, it makes perfect sense to let dev-teams avoid Not-Invented-Here by just reusing the perfectly good SQL-DDL-emitter that is “database migration frameworks.”)

by _jal 7y ago

derefr 7y ago

I can understand your point of view as a “database supremacist”—i.e. someone who thinks DBAs are priests who determine the canonical schema, where development teams (even within the same org) are supplicants to the DBA to get the schema changed (whereupon the schema is then changed for everyone else, who have to all just suck it up.) It’s a common viewpoint, and even a sensible one when managing DBMSes that aren’t “strong” enough to support the features required for virtualizing the schema (mostly: sprocs, schema-namespacing and search paths, low-overhead writable views that can “symlink” objects from one namespace into another, and user ACLs upon database objects where granting write access to a writable view means granting write access to the underlying object, while protecting the object itself.) The alternative viewpoint, is one where a DBMS isn’t its own Data Warehouse, but is rather a “relational transformation workspace” of a Data Lake. Consider BigQuery as a DBMS. Who determines BigQuery’s schema? Nobody. Each user has their own stuff in their own namespace, and users mount stuff from one-another’s namespaces into their own namespace. The job of the people who run BigQuery (or any other multi-tenant data-warehouse infrastructure of this type) is just to enable users to collaborate with one-another through the DBMS, where Data Definition “APIs” are contracts established between pairs of clients, rather than between a developer and the DBMS’s DBA. That was more the stance I was talking about with my parent post. But even under a Database-supremacist viewpoint, what I was talking about can still happen. Whether clients are pushing objects into the DB or not, as long as there are requirements to not break old clients, but also requirements to evolve the schema for new clients, the DBA will arrive at a set of constraints that can only be solved by doing exactly what these clients would do themselves, if given DDL privileges: to define separate “virtual namespaces” where the same object names refer to mostly-common but some slightly-different versions of the same database objects, in order to allow LegacyClient to see PolyfilledLegacySchema while NewClients see the regular, “true” schema (or vice-versa.) In any organization with a sufficiently-large team structure that must share data (picture e.g. Amazon with their store), there will be a number of teams (clients) with different conflicting constraints upon the same data set. This makes the job of the DBA managing their shared data, very similar to the job of an upstream library software developer: you must keep the library’s API stable so that existing downstream clients won’t break; while also making changes to those APIs such that new downstream clients can get the features they’re demanding. Sure, there are alternate ways of solving this stable+evolving constraint problem for shared data, just like there are alternate ways of solving the stable+evolving constraint problem for a software library. You can just build a database that’s “downstream” of your own, that decorates the data with the new features (or tell the client that needs it to do it themselves!), and which syncs changes back to the upstream DB in the upstream’s canonical format. This is like building one library to wrap another. But this is, in the end, a lot of extra complexity that introduces the chance of data corruption along the way. Much safer to warehouse your data in one system with one MVCC world-clock. It might be a requirement if your upstream DBMS is “weak” (e.g. some legacy mainframe DB2 instance) but modern DBMSes give you every feature you need to provide multiple interfaces from a single instance. Why not take advantage? ——— And, if you can accept that argument—and you also accept the idea that clients should be able to use the DBMS as a scratch workspace for their queries (i.e. that creating temporary tables isn’t a perversion of what DBMSes stand for)—then combining the two results in the idea that each client could, in theory, just tell the DBMS of its particular needs for an API to access a shared dataset, and that this would result in the automatic creation of a virtual namespace perfectly suited to that client’s needs. Sure, let’s pretend the client is specifying this virtual namespace using some sort of abstract specification language, where the DBMS takes it, parses it, and then has the power to satisfy those requirements however it wants, maybe just by directing the client to the nearest matching existing virtual namespace, etc. But think about what exactly clients would need to send in these requirements specifications for a long time, you’ll realize that the specs will still turn out to need exactly the level power of regular SQL DDL statements. You don’t need a shim layer; you just need a sufficiently-granular ACL system, such that the client can create “virtual stuff” for their own use, but can’t create or interfere with “real stuff”, or with other clients. (And then it turns out that, since the client would just be communicating its needs via SQL DDL anyway, it makes perfect sense to let dev-teams avoid Not-Invented-Here by just reusing the perfectly good SQL-DDL-emitter that is “database migration frameworks.”)