
David Beavon (Software) asked a question.
What are the chances that Progress adds RI to our databases for the sake of ABL code? Is there even a 1% chance this happens in the next three years?
Most folks who use Progress OE databases are doing it for their data layer. There are many other software layers like UI, agents, services, and even business logic are not necessarily built with OE. As such it makes sense for Progress to invest in the data storage engine for OLTP considerations. In my opinion, RI constraints are one of the most fundamental features of any OLTP storage engine. They are almost the heart of any relational database engine, and they are as important to the as pessimistic record locks or indexes or transaction logging. I don't know of any major database engine that leaves them out. If I google "does database X have RI", they all have it... even SQLite and MSAccess.
The benefit of RI implemented in the native engine are analogous to the CDC change-tracking-triggers that Progress added in the past few years (see OE 11.7.x). We already had a way to use ABL/AVM triggers to accomplish similar goals. Of course the legacy ABL triggers have been available for decades. But even so, it was FAR more performant and robust for Progress to implement this in a central way using native code in the engine itself. If Progress eventually conceded that the OLTP engine is responsible for CDC, then why won't they concede that the engine is responsible for FK constraints too?
I've been waiting since the 80s to see the engine take over things that are rightly the responsibility of the engine. I'm starting to lose hope. The language from the LLM search engines aren't helping matters:
https://www.google.com/search?q=why+openedge+database+has+not+introduced+RI+for+ABL+solutions
... these LLM responses are saying some things that seem overly cautions. Eg.
"Progress OpenEdge has maintained extreme backward compatibility for decades so that older ERPs and massive ABL business applications written in the 1980s and 1990s can run on modern releases without breaking."
I can see the reason for caution. Although RI seems to be the kind of thing that can be introduced without breaking backwards compatibility, since it is defined declaratively and ABL solutions must opt into it.
Some day user forums may allow Gemini to come post an answer, but for now I'll just be the Gemini ambassador. Hard to argue with a well thought-out answer, even if it is from an AI.
Can someone let me know if anyone disagrees with the answer?
Is there even an IDEA for introducing these constraints? I am scared to look, to be honest.
Referential Integrity without relations in the (R)DBMS? I admire your optimism ;-)
At my previous job we built a layer on top of the db schema and tooling and never allowed direct manipulation of the db structure.
Using that layer, which included a code generator, we could (among others) define relations and RI (restrict/cascade/nullify) which resulted in the generation of db triggers. If you can enforce that, and never allow direct db structure manipulation and only access/modify the data through ABL, then you can ensure RI...
All that tooling and rigor is overwhelming to many ABL devs. The whole point of ABL and OE is that it was supposed to be "easy". Of course we don't want to corrupt our FK references in our data, while using this easy platform.
I see how the ABL triggers may be a possible solution. But its tiring to think of retrofitting and adding them to every table with an enforced relationship.
When Progress added their own new implementation of CDC, it was also a replacement for something that customers had already been doing via home-grown triggers. I am hoping they can follow their own example, and come up with a turnkey solution for RI, just like they did for CDC. But Gemini doesn't think it will happen, and it is probably right...
I appreciate your suggestion... but if you scale that out to every single ABL customer, it doesn't make sense. This is the type of concern that should be addressed by Progress in a central and coordinated way just like they did for CDC.
I totally agree that this should have been part of the product from the beginning, and retrofitting a home-grown solution to an existing application isn't obvious. But even if Progress would come up with something, you would still need to define the relations and RI for all tables, as they can't just be derived from the db structure (I hope you don't depend on the names of fields and the OF keyword to define relations).
I've been pondering how I could retrofit some of these concepts to the ABL application in my current job, and there might be some way by leveraging AI. These things would have been totally out of reach a couple of years ago...
Yes, the relationships would need to be defined declaratively. We would not need to implement with code (per-table triggers).
No, we never relied on the consistency of column names and "OF". Are you saying that was Progress' way of specifying relationships? That was always a very dangerous way of building solutions, and very fragile over the long run. Defining FK constraints is much more future-proof.
The problem is that Progress built ABL so long ago, that even the the simple disk and memory and CPU resources - things that were needed for validating FK constraints - was too costly and they put the FK validation out of reach. The platform is largely anchored to design decisions that were relevant in the 80s and 90s before things like SSD, 128GB servers, and 4Ghz processors. Nowadays those resources are very adequate and Progress could easily re-evaluate if they wanted to, but it would still take quite a lot of investment from a software engineering standpoint. And if I were to guess, the customers who would benefit are a small number (probably only ~10% of the total OE-ABL user community would have the appetite to start leveraging new OE database functionality like this.) Maybe Gemini is right in its opinion... although I think it is funny that Gemini seems to steer people to using the SQL92 engine as an alternative approach for their OLTP CUD workloads.
> Are you saying that was Progress' way of specifying relationships?
Yes, and as you note that is very fragile to say the least. That was the reason all fields in any table at my previous job had unique names, e.g. order.ord_id, order.ord_status, etc. That way you could never use the OF keyword... (keyword forget list might have been another option 😌 )