I'm meeting quite a challenge. We have an old grown software with mostly Delphi applications, and an underlying ISAM database server (ADS)1, used with a lot of free tables and manually programmed referencial integrity (if so at all).
Our deadline to move that mess to a modern RDBMS is 2020.
Some of the preferable DB Server systems would be
- MariaDB
- Postgres
Anyways which RDBMS chosen finally isn't really the problem I'm asking about. Within 2 years is quite sportive though, let me explain why:
We have ~17000 customer installations with quite different (and customized for their workflow processes) DB structures in the field.
ADS allows to (mis-)use directory structures and those free tables, where the applications often create tables like
<db_root>/application_dir/DATA/<KeyX>/<KeyY>/<Year>/TableXYZ.adt
or
<db_root>/application_dir/DATA/<Year>/<KeyX>/<KeyY>/TableXYZ.adt
While this technique allows fast access to the data using a Delphi TTable
or SQL query based TDataSet
component, the overall referential integrity of that data might contain pitfalls, which nobody in the company is aware anymore.
I'm a bit in a doubt what would be the key points/methodologies to approach that problem.
I believe that
- Collecting usage statistics from customer installations2
- Analyzing the present structures from particularly selected more complex customer installations
could be helpful to support more founded decisions what could be the "least painful" ways to go for migration.
Writing the tools to do so would cost some developer efforts a priori though.
Also I am (and I am not the only one among my colleagues) not very confident in Embarcadero's promoted FireDAC technology.
TBH if it's the same humble, and full of flaws quality I see in the Delphi System and any WinAPI related, I'm not so sure we should follow that path (there are even drivers for MySQL
, which might fit for MariaDB
).
ODBC won't be really an option, it would be apparently too slow, too generic and produce too much network ping-pong, with the current structure and behavior of our applications.
So writing the mentioned TTable
, TDataSet
components based on a native driver ourselves, also comes into consideration.
What are your recommended methodologies to approach that gordian knot, and to even specify the essential requirements?
1)Worth mentioning that the system already was migrated from PARADOX to ADS, as a replacement for an ISAM file based DB Server, a "few" years ago.
2)We already have an app analytics service that could be used for that maybe.