2

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.

19
  • I think the first thing you should do is evaluate what it's going to take to convert your homegrown referential integrity into first-class constraints in your chosen RDBMS, and then determine what your new constraints (not existing yet) are going to be. A well-designed database schema will go a long way towards getting you to where you want to go. Feb 16, 2018 at 21:31
  • @RobertHarvey I'm afraid we'll need to provide some kind of "relaxed hybrid" data model, that finally leaves control and being in charge of, over referential integrity to application developers who well know what they're doing (not that this is the default met unfortunately). Feb 16, 2018 at 21:41
  • Well, wouldn't your ISAM tables just naturally map over to MariaDB or PostGres tables, assuming you get the indexes right? Feb 16, 2018 at 21:42
  • 1
    Well; I don't know enough about ADS to give you a reliable advice, but I was already in a situation where we needed to replace an older foundation technology by a newer one for a bigger system. As a general strategy, I can give you just one advice: it pays off for seeking solutions where you need to change as few things as possible in the old codebase. Maybe you can create some intermediate layer which "emulates" as much as possible from ADS, maybe you can decouple the application from the database by some adapter. But avoid trying to replace ADS and to clean up messy parts both at once....
    – Doc Brown
    Feb 16, 2018 at 22:46
  • 2
    ... better solve one problem after another - solving one of those two problems at a time is hard enough on its own.
    – Doc Brown
    Feb 16, 2018 at 22:49

1 Answer 1

0

As there seem no standard methodologies to approach that problem, I believe it's best to follow @Doc's comments:

... it pays off for seeking solutions where you need to change as few things as possible in the old codebase.


... better solve one problem after another - solving one of those two problems at a time is hard enough on its own

I think it's best to 1st migrate the application's workflow to the RDBMS as close as possible to the current implementation.

The "DB schema" currently is maintained with empty table templates and a versioning mechanism that allows migration of the existing tables to newer versions.

I've been experimenting with MariaDB a bit, and these mechanisms can be well done using appropriate table structure templates, and stored procedures to manage these.

The "inappropriately" used directory paths containing key values can be changed to table names containing the same information.

The problems with missing referential integrity may be relieved a bit using generated triggers.

After that step was done, and all applications still work as intended, we can approach a "real" refactoring of the overall DB structure and schema.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.