3 ms·

We've used this pattern in an warehouse management system in a company I worked for years ago. Old version had almost all the logic in PL/SQL, some Qt forms fo

by ajuc 8y ago

We've used this pattern in an warehouse management system in a company I worked for years ago. Old version had almost all the logic in PL/SQL, some Qt forms for high-level management, and C++ console apps (warehouse processes) running on portable terminals through telnet (so in reality these were running on the server, and portable clients were telneting to it to control it). First we introduced XML-based protocol between C++ app and portable terminals, and a .net client running on these terminals to replace telnet. This allowed for a simple graphical interface instead of text-only, so there was a good motivation for customers to upgrade. Also it separated the parts clearly and allowed to mix and match new and old parts in the system. Then we introduced J2EE application server, exposed the database through hibernate, and new processes were written in java (jbpm to be specific). They still used the same XML protocol and .net client, so old C++ processes could call java processes and vice-versa. New processes required the possibility to call PL/SQL logic so the features that were needed were exposed through J2EE services to the java processes. Finally we added a way to write new management forms in Eclipse RCP. We also planned on moving the logic from PL/SQL to J2EE completely, and becoming database independent, but we never got to that. The rewrite was never completed, there was a merger in the meantime, and we switched tech again, at which point most of the team left :) But what we finished was working reliably, no features were lost, and as far as I know some customers still use the old system, some use both, some use the new system. The thing that made it easy to do, but also hard to finish - was the logic in PL/SQL. As long as we left that be it made moving everything else easy. But at the same time it was a constant temptation to just call the old PL/SQL function instead of writing a new J2EE service, and finish the task at hand faster.

dcbadacd 8y ago
I've seen huge things being built in/with PL/SQL, running stable, fast, nearly bug-free and containing all the features needed, for decades, sometimes upgraded to a new database major version. I've also seen multiple attempts at replacing those PL/SQL systems, reimplementing the same functionality with Hibernate/Java (and I recall one instance of PHP being used) and they've all been slow (to use and develop), buggy and always lacking in functionality compared to the original. Basically all three of the previous problems or a disgusting combination of them. In all of those replacement cases, there has been no objective reason to replace the entire system, maybe give the UI a facelift. So here's my question, why rewrite it all, what's the reason these "rewrites" and "stanglings" are done to nicely working systems just not complying with what the newest fad is? Is PL/SQL or DBs such an arcane knowledge your devs did not understand it enough to give it a facelift? I'm genuinely just curious why these things happen, if I knew the reason maybe I could stop another service I have to use being turned to excrement.
- agarv 8y ago
  Not the parent, but I assume people do it so they don't have to use Oracle anymore (for either cost or ethical reasons).
  - dcbadacd 8y ago
    But there are other DBs that could be migrated to, rewriting SQL to another dialect is usually much easier than a full rewrite.
- ajuc 8y ago
  The ultimate goal was to become database-independent as far as I understand (I was just a junior dev, this was my first job, so I'm guessing, I wasn't making decisions). To avoid Oracle tax I assume. It was very funny when mid-rewrite we learnt Oracle bought Java :) But there were lots of other improvements - using jbpm for designing warehousing processes was a natural fit, much better than making persistent long-running processes with C++ using nested ifs in a while loop and serializing the state of the process with manual inserts and updates on each state change. With jbpm you could see the whole state machine as a graph, move nodes around, insert new ones easily, and the persistence was automatic, including all the variables you use, which saved a lot of time and hard-to-track bugs (some combination of steps breaks the persistence the next time you enter this process - good luck fixing that and tracking what really happened on the warehouse before the process broke the persistence). Regarding the speed we were actually slightly faster with the jbpm (mostly thanks to the hibernate 2nd level cache and optimistic locking). We measured the time on the portable devices between pressing a key and seeing the next screen, and because the bottleneck was PL/SQL procedures running selects to decide what to show and waiting on locks - the whole overhead of application server and jbpm and .net client was hidden in the savings thanks to the cache (and optimistic locking). Also jbpm processes had versioning. Old versions were continuing to run and new instances were started with new version. Upgrading the processes with the C++ code was basically stop-the-world event. We could have skipped the Eclipse RCP thing, though. The subteam that worked on that went a little over the top with architecture astronomy, there were like 4 sub-layers with 4 levels of configuration xmls :). And the framework built on qt we used previously for forms was quite nice already, arguably better. And no - PL/SQL isn't arcane, and the whole team had lots of experience with it running the system for years.

by ajuc 8y ago

dcbadacd 8y ago

I've seen huge things being built in/with PL/SQL, running stable, fast, nearly bug-free and containing all the features needed, for decades, sometimes upgraded to a new database major version. I've also seen multiple attempts at replacing those PL/SQL systems, reimplementing the same functionality with Hibernate/Java (and I recall one instance of PHP being used) and they've all been slow (to use and develop), buggy and always lacking in functionality compared to the original. Basically all three of the previous problems or a disgusting combination of them. In all of those replacement cases, there has been no objective reason to replace the entire system, maybe give the UI a facelift. So here's my question, why rewrite it all, what's the reason these "rewrites" and "stanglings" are done to nicely working systems just not complying with what the newest fad is? Is PL/SQL or DBs such an arcane knowledge your devs did not understand it enough to give it a facelift? I'm genuinely just curious why these things happen, if I knew the reason maybe I could stop another service I have to use being turned to excrement.

agarv 8y ago
Not the parent, but I assume people do it so they don't have to use Oracle anymore (for either cost or ethical reasons).
- dcbadacd 8y ago
  But there are other DBs that could be migrated to, rewriting SQL to another dialect is usually much easier than a full rewrite.
ajuc 8y ago
The ultimate goal was to become database-independent as far as I understand (I was just a junior dev, this was my first job, so I'm guessing, I wasn't making decisions). To avoid Oracle tax I assume. It was very funny when mid-rewrite we learnt Oracle bought Java :) But there were lots of other improvements - using jbpm for designing warehousing processes was a natural fit, much better than making persistent long-running processes with C++ using nested ifs in a while loop and serializing the state of the process with manual inserts and updates on each state change. With jbpm you could see the whole state machine as a graph, move nodes around, insert new ones easily, and the persistence was automatic, including all the variables you use, which saved a lot of time and hard-to-track bugs (some combination of steps breaks the persistence the next time you enter this process - good luck fixing that and tracking what really happened on the warehouse before the process broke the persistence). Regarding the speed we were actually slightly faster with the jbpm (mostly thanks to the hibernate 2nd level cache and optimistic locking). We measured the time on the portable devices between pressing a key and seeing the next screen, and because the bottleneck was PL/SQL procedures running selects to decide what to show and waiting on locks - the whole overhead of application server and jbpm and .net client was hidden in the savings thanks to the cache (and optimistic locking). Also jbpm processes had versioning. Old versions were continuing to run and new instances were started with new version. Upgrading the processes with the C++ code was basically stop-the-world event. We could have skipped the Eclipse RCP thing, though. The subteam that worked on that went a little over the top with architecture astronomy, there were like 4 sub-layers with 4 levels of configuration xmls :). And the framework built on qt we used previously for forms was quite nice already, arguably better. And no - PL/SQL isn't arcane, and the whole team had lots of experience with it running the system for years.