4 ms·

The article relies on major features of Oracle in order to "bust" the myth of the unmaintainable stored procedure, which leads me to believe that these are big

by sc68cal 15y ago

The article relies on major features of Oracle in order to "bust" the myth of the unmaintainable stored procedure, which leads me to believe that these are big band-aids over what is still a difficult problem: maintaining database changes and getting developers to put database changes into version control. Unlike the author, I am not fortunate enough to have Oracle, and am instead using MSSQL for our database with 500+ stored procedures. Here's my experience: Even the first "myth" that was "busted" had me cringing, putting all the stored procedures into individual files and using a source control system to manage those. The person I replaced had done the same thing with 500+ stored procedures and still had never bothered to actually update the files. He just updated the stored procedures on the development server. Partly because he was a massive fuckup, but also because it was "out of band" management. I ended up moving the code out of SourceAnywhere (an all around terrible version control system and company) and placing the code into Github, and cleaning up the database so that it could actually be dumped to an SQL file and then loaded from that file as an NUnit test, run by the CI server that I set up. Even with these huge strides forward, it has taken me months to clean up and fix these problems, while still adding new features to the existing code and database. The experience has left me feeling that the low quality of code that crap coders can get away with in stored procedures, and the lack of testability still leads me to strongly avoid stored procedures because I ended up having to fix them with sweat, tears, and blood.

statictype 15y ago
We built a version control tool in-house to help with this problem. It would examine the database and generate files (one per object) on disk which could be committed into version control. The tool has a -merge mode that examines scripts in files against the objects in the database and spits out sql commands that modify the DB to match the versioned files. It has a few drawbacks but mostly works ok.
- jasonkester 15y ago
  That sounds like a bit of a bandaid fix for a history of bad practice. Why not, instead of forensically trying to rebuild scripts for changes, just save your change scripts as you change things? You end up with the equivalent set of scripts in source control, but it seems like they'd be a lot more readable (they won't be all lumped on top of eachother and out of order, etc.). Is there another advantage to what you're doing that makes it worth deviating from the "best practice" way of doing things? Is, for example, the -merge functionality something that you use on a regular basis to roll backward?
  - statictype 15y ago
    Why not, instead of forensically trying to rebuild scripts for changes, just save your change scripts as you change things? Well, DB objects were put into version control much later than other source files, so I wanted a way to guarantee that what's there in source control matches what we actually have in the database server. You're suggestion is that basically developers maintain the discipline of having changes in the files reflect what they run in the DB, right? I thought its significantly cleaner to have the database as the single source-of-truth and take everything from there. but it seems like they'd be a lot more readable Didn't quite get that part. The generated scripts are saved in one-per-file and reflect the name of the table/procedure/etc... that it contains. Isn't that basically what the 'best practice' way of doing it is? Merging is just to take the latest set of source control and bring your DB up to date with it. How do you handle version control? I'm certainly interested in a better way to do it.
ZoFreX 15y ago
You could use the same thing to argue that business logic shouldn't be in PHP, or that it is hard to version control, because my predecessor put all of his functions in one giant PHP file and frequently updated websites live on the server without committing those changes to SVN. Anything can be mis-used. Whether or not we should use it should depend less on potential for mis-use, and whether it can be used correctly (and in a multi-developer environment, whether or not you can enforce correct usage in some way, either through technical or social means).
F_J_H 15y ago
I think one needs to be careful of the reasoning that goes something like "X makes it easy for people to do things poorly, which makes my job harder, and therefore X is bad." I've heard the same argument against PHP. "PHP is so easy to use that anyone can hack together crap, which is difficult to maintain. Therefore, PHP is bad." I love this quote: UNIX was not designed to stop people from doing stupid things, because that would also stop them from doing clever things. - D. Gwyn
- sc68cal 15y ago
  "PHP is so easy to use that anyone can hack together crap, which is difficult to maintain. Therefore, PHP is bad." I know you are attempting to highlight a weakness in my argument by highlighting how my reservations are fairly generic, but by the same token, I have never been lucky enough to see high quality PHP code. I don't think PHP encourages best practices, just like Stored Procedures. Either that, or PHP is just such a large community that the number of bad coders, even though it might be a constant percentage across all languages, but because PHP is so large I've run into a lot of them. Who knows? While I have no doubt that there are some real superstars that can make beautiful PHP code and write wonderful stored procedures, there is still the 99% of programmers who can't and end up making unmaintainable nightmares.
  - SoftwareMaven 15y ago
    I think that is the wrong argument, though. Trying to develop a language that tries to not let you do bad things gives you something like Java instead, where doing anything is cumbersome (and still doesn't succeed in preventing bad things from being written). There is a lot of bad PHP code because there are a lot of people who start hacking using PHP. However, any language somebody first starts hacking in will result in ugly code (you should have seen my Scheme code when I first started with that in college! :). Unfortunately, the idea of "teaching languages" doesn't really work, because people want to continue using what they've already learned. (BTW, I don't think you deserved downvotes. You are continuing the conversation, after all, not trolling. I really think we need to make downvoting more painful.)
jasonkester 15y ago
Hang on a second... You use MS SQL Server and don't follow the practices he describes in this article? Why on earth not? SQL Server is just plain awesome at this stuff. Schema is all scriptable (it even prompts you to let it script itself if you mess with things in the modeling tools). That means that you get automagic change scripts that tuck nicely into source control and are just waiting to be sucked in by your automated build and deploy system of choice. Procs and everything else are all just scripts, which get their own files. So just like you have individual files for all 500 of your classes, you'll have 500 .sql files for those procedures. It's not difficult in any way. It sounds like you ran into problems because you inherited a system from people who went out of their way to mess things up. Messed up things are hard to work with, regardless of what technology they live in. Try not to blame the database for the fact that you can build ugly things with it.
Duff 15y ago
Great points. If you exercise solid controls around your database operations, you "can" have a the same level of version control as you do on code outside of the RDBMS. But "can" isn't the same as "will" -- in practice, there are practical issues that make this painful. The other point is that using advanced Oracle/DB2 features to accomplish things means that you need to keep some RDBMS high priests around. That means lots of money.
jules 15y ago
That's like committing code to your application server and not putting it into version control. This should be attributed fully to the crappy developer and not to the software running on the application server or in this case database server.
smiler 15y ago
If you use SQL Server, then you absolutely needs to be using the RedGate SQL tools, which also includes SQL Source Control, which will make versioning your 500 SPs very very very easy. Don't delay - www.redgate.com

by sc68cal 15y ago

statictype 15y ago

We built a version control tool in-house to help with this problem. It would examine the database and generate files (one per object) on disk which could be committed into version control. The tool has a -merge mode that examines scripts in files against the objects in the database and spits out sql commands that modify the DB to match the versioned files. It has a few drawbacks but mostly works ok.

jasonkester 15y ago
That sounds like a bit of a bandaid fix for a history of bad practice. Why not, instead of forensically trying to rebuild scripts for changes, just save your change scripts as you change things? You end up with the equivalent set of scripts in source control, but it seems like they'd be a lot more readable (they won't be all lumped on top of eachother and out of order, etc.). Is there another advantage to what you're doing that makes it worth deviating from the "best practice" way of doing things? Is, for example, the -merge functionality something that you use on a regular basis to roll backward?
- statictype 15y ago
  Why not, instead of forensically trying to rebuild scripts for changes, just save your change scripts as you change things? Well, DB objects were put into version control much later than other source files, so I wanted a way to guarantee that what's there in source control matches what we actually have in the database server. You're suggestion is that basically developers maintain the discipline of having changes in the files reflect what they run in the DB, right? I thought its significantly cleaner to have the database as the single source-of-truth and take everything from there. but it seems like they'd be a lot more readable Didn't quite get that part. The generated scripts are saved in one-per-file and reflect the name of the table/procedure/etc... that it contains. Isn't that basically what the 'best practice' way of doing it is? Merging is just to take the latest set of source control and bring your DB up to date with it. How do you handle version control? I'm certainly interested in a better way to do it.

ZoFreX 15y ago

You could use the same thing to argue that business logic shouldn't be in PHP, or that it is hard to version control, because my predecessor put all of his functions in one giant PHP file and frequently updated websites live on the server without committing those changes to SVN. Anything can be mis-used. Whether or not we should use it should depend less on potential for mis-use, and whether it can be used correctly (and in a multi-developer environment, whether or not you can enforce correct usage in some way, either through technical or social means).

F_J_H 15y ago

I think one needs to be careful of the reasoning that goes something like "X makes it easy for people to do things poorly, which makes my job harder, and therefore X is bad." I've heard the same argument against PHP. "PHP is so easy to use that anyone can hack together crap, which is difficult to maintain. Therefore, PHP is bad." I love this quote: UNIX was not designed to stop people from doing stupid things, because that would also stop them from doing clever things. - D. Gwyn

sc68cal 15y ago
"PHP is so easy to use that anyone can hack together crap, which is difficult to maintain. Therefore, PHP is bad." I know you are attempting to highlight a weakness in my argument by highlighting how my reservations are fairly generic, but by the same token, I have never been lucky enough to see high quality PHP code. I don't think PHP encourages best practices, just like Stored Procedures. Either that, or PHP is just such a large community that the number of bad coders, even though it might be a constant percentage across all languages, but because PHP is so large I've run into a lot of them. Who knows? While I have no doubt that there are some real superstars that can make beautiful PHP code and write wonderful stored procedures, there is still the 99% of programmers who can't and end up making unmaintainable nightmares.
- SoftwareMaven 15y ago
  I think that is the wrong argument, though. Trying to develop a language that tries to not let you do bad things gives you something like Java instead, where doing anything is cumbersome (and still doesn't succeed in preventing bad things from being written). There is a lot of bad PHP code because there are a lot of people who start hacking using PHP. However, any language somebody first starts hacking in will result in ugly code (you should have seen my Scheme code when I first started with that in college! :). Unfortunately, the idea of "teaching languages" doesn't really work, because people want to continue using what they've already learned. (BTW, I don't think you deserved downvotes. You are continuing the conversation, after all, not trolling. I really think we need to make downvoting more painful.)

jasonkester 15y ago

Hang on a second... You use MS SQL Server and don't follow the practices he describes in this article? Why on earth not? SQL Server is just plain awesome at this stuff. Schema is all scriptable (it even prompts you to let it script itself if you mess with things in the modeling tools). That means that you get automagic change scripts that tuck nicely into source control and are just waiting to be sucked in by your automated build and deploy system of choice. Procs and everything else are all just scripts, which get their own files. So just like you have individual files for all 500 of your classes, you'll have 500 .sql files for those procedures. It's not difficult in any way. It sounds like you ran into problems because you inherited a system from people who went out of their way to mess things up. Messed up things are hard to work with, regardless of what technology they live in. Try not to blame the database for the fact that you can build ugly things with it.

Duff 15y ago

Great points. If you exercise solid controls around your database operations, you "can" have a the same level of version control as you do on code outside of the RDBMS. But "can" isn't the same as "will" -- in practice, there are practical issues that make this painful. The other point is that using advanced Oracle/DB2 features to accomplish things means that you need to keep some RDBMS high priests around. That means lots of money.

jules 15y ago

That's like committing code to your application server and not putting it into version control. This should be attributed fully to the crappy developer and not to the software running on the application server or in this case database server.

smiler 15y ago

If you use SQL Server, then you absolutely needs to be using the RedGate SQL tools, which also includes SQL Source Control, which will make versioning your 500 SPs very very very easy. Don't delay - www.redgate.com