Stored Procedures
Noferblatz (30 September 2019 08:47:11)

Stored procedures. These are bits of code which are stored in databases that assist the programmer, supposedly. You can call them as part of your SQL, and it makes your life easier.

Early on, I used these with PostgreSQL. Let me say from the outset I'm not a fan of stored procedures, and don't use them. When I say "stored procedures" I mean code stored in the database that supposedly makes life for the programmer easier.

Stored procedures have a major disadvantage. The code is hidden from all but the most discerning observers.

One of the biggest beefs I have with products like WordPress is that they insist on storing masses of text in tables inside a database. Instead of storing postings and comments in text files, where they belong, they jam them inside "text" fields in tables. Just try to get a reasonable backup out of WordPress and you'll see what I mean. You get back an XML representation of your blog, readable by WordPress, but not very much so by you. I've examined their table structure and I still don't now how they figure out what's the latest version of a blog post to display. The table doesn't contain the type of fields I'd expect for finding this out.

But if you generalize this a bit, you can see that an awful lot of websites today, while they may not store masses of text in text fields, do store user information, order information and the like in tables in a database. And rightly so, for the most part. But here's the problem. When called upon to move a site or debug the code or somesuch, you're forced not only to look at the code itself, but at the database for the values which influence the execution of the code.

I've got git to help me view different versions of the code. And it's pretty easy to FTP code up and down and examine it locally for debugging or moving purposes. But the presence of values in a database makes the whole thing a lot less trivial. And if values stored in a database make things more complex and difficult, imagine what store procedures do. They're whole sections of code that can't be debugged by "normal" tools. And you don't necessarily even know they're there sometimes. You make a query that is supposed to scoop up data from the database, and there happens to be some sort of trigger that puts the data through a stored procedure on the way out. Or the way in. Or both. You don't know it, because your SQL statement doesn't mention it. And yet the data doesn't come out the way you'd expect. Your first stop is your own code, but after some period of time, you realize it isn't your code that's distorting the results. Ultimately, you dig into the database and realize there's code there you didn't know about. Curse the programmers who designed this thing!

I suppose it could be worse. FoxPro, before Microsoft abandoned it, had gotten to the point where almost everything you did, from menus to reports to screens and even the "glue" code that held it together was held in "memo" fields (text fields). They had "engines" that would drive each type of "object" (report, menu, screen, application, etc.) based on the approved structure of the object's file and the code contained in the "memo" fields of the tables. Trying to debug FoxPro code had become a nightmare.

Bad enough we have to deal with databases in the construction and operation of websites today. This is a necessary complication (though I wish companies like WordPress would offer something better by way of backup schemes). But this is only made worse by the presence of store procedures inside databases. Don't use them. They really aren't necessary. You can perform the same operation in actual PHP/Java/Python/Perl code. And you can debug it a helluva lot easier if you do.