3 ms·
> Tracking changes is trivial, since they're a part of the schema -- use version control. It's not quite that simple. You can't just "install" the new schema o
by ars 15y ago
> Tracking changes is trivial, since they're a part of the schema -- use version control.
It's not quite that simple. You can't just "install" the new schema onto an existing database with data like you can by just copying files. You have to write your own set of alter statements to keep things synchronized (sometimes with complicated data migrations to new tables).
It works ok for 1 or 2 databases since you just do it manually - but then you lose many of the benefits of automatic source control.
And if you have a large number of database servers - or many unrelated (client) installations - you need a much more complicated system, and it's far from trivial.
Putting the SQL commands in the code is a lot simpler. There are no security benefits to stored procedures vs bound SQL statements. Perhaps it's a bit faster, but I'm not so convinced, you are trading parsing time for execution time since the stored procedure is now a program instead of a data update command.
- wiredfool 15y agoStored procedures have different security semantics than bound sql statements. See the security definer attributes. Briefly, a stored procedure can run with the calling user's permissions, or the definer's permissions. If you set up a function as a security definer, you can do things with data that protect it from disclosure in a way that you can't in a sql statement. You can do it similarly with views, but they're more of a read only case.
- matwood 15y ago(sometimes with complicated data migrations to new tables). To be fair this is an issue with or without sprocs.