Co-workers at Obtiva meet up every Tuesday for a weekly event called "GeekFest." Lunch is ordered for us for free and we use that time to enjoy discussing the latest technologies, giving presentations, demoing programs, and having heated debates about things like mock-based testing vs state-based testing and Ruby vs Python.
So anyways, I always like to think outside the box and ask some crazy questions, and yesterday I committed a crime by asking the following question after I saw a demo of a trigger and a stored procedure written to solve a specific problem that would have been harder to solve with writing application code (rare case): "Now that Oracle allows writing stored procedures in Java and MS SQL Server allows writing stored procedures in C#, isn't that the holy grail for developers that complain about how procedural and non-OO stored procedures are and how they produce difficult to maintain code? The Oracle Java version may be behind its time, but it's light years ahead as a language than that of stored procedures, isn't it?"
I got a backlash almost by everyone, even from the most OO-centric developers. Apparently, I forgot about one very important detail that one of our new co-workers reminded me of. Stored procedure code meshes well with SQL, which is a DSL that makes writing query code much easier than in Java. This ends up with simpler code that is easier to read than embedding SQL queries within another language with a different paradigm, such as Java.
Now, if we take that to the extreme, we would write all the application's query logic in stored procedures. So obviously, there is a point of diminishing returns. Stored procedures may be simple to write for small cases, but when used to write a whole application, they end up with the same issues that prompted the invention of OO methodologies, like lack of expressiveness for domain models due to no support for inheritance and polymorphism, and difficult maintenance as a developer is required to dig many levels into a procedure's code before understanding what it does due to no support for abstraction (assuming the code is well factored in many tiny procedures calling each other.) When a procedure is a method on an object, there is a lot more context to what it does, which saves developers from having to dig to understand what it does.
So, while the stored procedure language is easier than Java for writing code intermingled with SQL for simple cases, that does not necessarily mean it scales well for big projects with complex domains.
One last point that one of my co-workers mentioned that made a lot of sense is that you don't need an Object-Oriented language to write clean code. My response to that though is while that's absolutely true, you may still need an Object-Oriented language to write clean code that is easy to maintain when dealing with a complex domain. Just because the code is clean, it does not mean it's easy to maintain if the language is not expressive enough (no polymorphism, no inheritance, etc...)
What do you think?
3 comments:
I don't think the "lack of expressiveness for domain models" via polymorphism and inheritance (do people still use that? How quaint.) represents a major issue, since in a relational database system that variation would be stored in tables.
In my experience PL/SQL scales better than Java for everything that PL/SQL is designed for, which includes complex systems like Application Express and various financial applications based on Oracle Forms. This is why Java-in-the-Oracle-DB sees almost no use, aside from the opportunistic occasions where existing Java libraries can be called from PL/SQL.
It is in fact entirely credible -- good design, even -- to write all of the application's "query logic" in stored procedures. Oracle Application Express is a good example of how to do this within a point-and-click development framework.
This is going to sound rude, but I have a feeling that your perception of the shortcomings of stored procedure languages is rooted in failing to understand them completely.
Steven Huwig, thanks for your comment.
Most of my experience with PL/SQL was in dealing with painful legacy code, and having to make a change in many places to get what I want done, yet getting surprised with bugs due to no ability (at the time) to write tests first in PL/SQL. I definitely am not as experienced with them as I am with Java, so I don't mind your comment and I appreciate it.
I am curious, are you pretty comfortable with maintaining PL/SQL code even when the code base becomes enormous? If so, do you know of any patterns that can help with that?
Well, the first line of defense is to prevent your code base from becoming enormous... :-) Make sure that you aren't spending dozens of lines in a procedural selection and iteration that could be done in a three-line SQL query. Also, don't create special types for result set records just for the sake of doing it in one procedure. You can loop through the last names and birth dates in the CUSTOMERS table without making some kind of LastNameAndBirthDate record type.
That being said, good PL/SQL code is organized using packages. This lets you program to a higher-level user-defined API if needed. Among other things, you can overload procedures and functions with different argument type.
Another language element not available in Java that can help make more general or concise code is the ability to use out parameters.
I'm not really comfortable maintaining large PL/SQL code bases that are full of bad code, but then I am not any more comfortable maintaining large Java code bases that are full of bad code. And one thing experience has taught me is that most code is bad code. :)
Post a Comment