FWD » Core Development » Conversion Tools

Over time, developing 4GL code naturally requires schema updates. For existing non-trivial persistent database instances, there must be some kind of process to apply those updates without a dump and load of the entire database. This requirement exists in the converted system as well, so long as 4GL schema changes are being made.

During convertion, the schema is emitted as DDL scripts that are SQL dialect-specific. In p2j.cfg.xml, you specify the dialects to be supported and for each one a set of DDL is generated. These scripts are used to create new database instances.

Once a database instance has been created, future schema updates have traditionally been left to the customer. We have multiple customers that use some variant of the following manual process:

1. Save off the ddl/ output from the baseline conversion. The baseline conversion is the last conversion for the .df before the changes which need to be applied were made.
2. Update the code and schemata in the project and run a new conversion.
3. Compare the new ddl/ to the old ddl/ contents. You can use your favorite diffing tools.
4. Extract the diffs. With little to no edits, these are the basis for a SQL script to apply the schema changes to the database for that dialect (PostgreSQL, MariaDB...).
5. If needed edit the scripts to initialize data for new columns, edit data for changed columns.
6. Use psql to run the scripts on a specific already existing and populated database instance. This can be automated.

Steps 1 through 5 are done once per schema update. Step 6 is done once per database instance that already exists.

The idea is that these scripts should leave an upgraded database instance with the identical schema to a newly created instance. For example:

• Conversion is run on 4GL app/schema v1.0.
• Installations A and B go into production with the converted code for v1.0. Each has a database created using the DDL that came from the v1.0 conversion.
• 4GL devs write changes to create 4GL app/schema v1.1. There are schema changes, so the .df is a superset of the v1.0 version.
• Conversion is run for 4GL app/schema v1.1.
• 4GL devs use the manual process noted above to create the differential v1.0 to v1.1 upgrade DDL.
• Installations C and D go into production with the converted code for v1.1.
• Installations A and B get upgraded to use the new v1.1 code and the differential upgrade scripts are used to update the database to have the same exact schema as if they had been newly created with v1.1.

You should not be able to tell the difference between the schematas at A, B, C or D. They should be identical regardless of which installation path they took.

Note that we have one customer that has built their own automated process to create these scripts and they have their own process to apply those scripts in deployments (if I understand correctly).

This task is meant for us to create a tool that is part of conversion, which would build these scripts automatically. It will have to know the baseline version and will have to retain knowledge of the schema from that version so that the diffs can be used to emit the proper script output. There is no sense in leaving this to customers, since a manual scripting process is error prone AND it is easy to end up with different schemata for different installation paths, which must never happen.

On my customer, and in hotel_gui, I have a archive.incremental task in build.xml which attempts to collect all the artifacts that are required to perform an incremental conversion. Just a mention that these artifacts could be retrieved from an archive like this, and used to generate the delta per your specifications.

• the simplest case is when the a new table is added. The CREATE statement for it is just added to DDL 1.0;
• next: a new field is added. Similar, an ALTER TABLE ADD COLUMN is generated; In this case a default value should be provided. We cannot assume the INITIAL value or NULL are valid for already existing records;
• similar for indices;
• the cases when a table or a field is deleted is pretty simple as well;
• things get more complicated if a table/ field is renamed. Dropping and recreating might not be the correct choice since there may be data stored in them, and we do no want to loose that;
• the most complicated case, I think, is when the 4GL team chooses to change the type of a field. Usually, it can happen that an int is widened to a int64 or a date to datetime or datetime-tz, but sometime a logical get changed to int or any other type is changed to character. These are not trivial operations, a full column must be updated and for this additional parameters are necessary: case-sensitivity, time-zone, decimal part. In case a field is morphed to datetime-tz, the additional offset column must also be injected.

of course, this is not a complete list. I am sure we will encounter a lot of other cases which will require solutions as well.

things get more complicated if a table/ field is renamed. Dropping and recreating might not be the correct choice since there may be data stored in them, and we do no want to loose that;

We may not need to support this. I think it is pretty specialized and probably not easily done in OE either. If this is not easily done in OE, then we can ignore it and assume that the customer will write code to do any such transition.

the most complicated case, I think, is when the 4GL team chooses to change the type of a field. Usually, it can happen that an int is widened to a int64 or a date to datetime or datetime-tz, but sometime a logical get changed to int or any other type is changed to character. These are not trivial operations, a full column must be updated and for this additional parameters are necessary: case-sensitivity, time-zone, decimal part. In case a field is morphed to datetime-tz, the additional offset column must also be injected.

I don't think we need to support this. I'm pretty sure OE doesn't make this easy.

We keep changing our database schema. We basically copied the mechanism we used in OpenEdge. It works like this:

• We use schema version numbers and we have incremental schema change files for these version. So schema version 300 has an OpenEdge schema change file 300.df and a FWD schema change file DB00300.sql. These schema change files contain the schema code to get from schema version 299 to 300.
• We record the current schema version number in a record in the database.
• At every startup we check if there are schema change files with a higher number than the recorded version number in the database and we apply them to the database.
• After every applied schema change file we record the schema version in the database. So if something goes wrong in the next one for some reason, the database contains the correct schema version number.
• We create the schema change files by hand. We do not create them automatically. That's was pretty complicated.
• We do have tools that can be run on a developers machine to compare the schema of a full OE conversion with the schema of FWD application with all the current incremental schema files applied. Both resulting schema files should be exactly the same! Even the slightest difference should be solved.
• The downside of this is: it is a manual process. Sometimes people forget to do the comparison. However most of the time when something goes wrong they are in a hurry, get overconfident and think they can skip the comparison. This why we have Jenkins jobs running doing an extra check at least once a day. If these jobs find something is wrong, it is published on Slack.

If you need more information like where to find all this in our code, please let me know.

On the discussion above about what to support and what not: developers do anything the (OE) code let's them do, just because they can. I am a developer.

One customer (see #9288) uses Liquibase to implement their incremental schema management. I'm not sure how well that could be used generally but we should consider it. The tool does require the creation of the databases as real instances in order to do the diffing, which means the implementation is more complicated. Also, it may not integrate well with all of our IDE support (they have legacy Eclipse and not Theia or IntelliJ) nor our support for gradle.

Greg,
I tried applying SDD to this task since it's a new feature and it seemed appropriate. The design was built on a combination of information from this ticket and the related ones, OE and pgsql tests and some personal input, here are the full details.

Conversion now writes, next to the create DDL, the numbered upgrade steps that bring an existing PostgreSQL database from the previous release to the current schema. A runner applies them in place, and a checker proves that every upgraded database equals a fresh one.

1. Design from the ticket analysis¶

The tickets set the goal, the constraints and most of the positions the design follows.

• The invariant (#8786-2). A database created at v1.0 and upgraded to v1.1 must have exactly the schema of a database created fresh at v1.1. Two installation paths that end in different schemas must never happen.
• The manual process is the problem (#8786-2, #8786-7). Customers diff the old and new ddl/, hand-edit the result and run it per database. That is error prone, and the drift is real data damage.
• Initial values (#9173). A field added without a value leaves existing rows NULL, which later cannot be told from a user-entered ?. OpenEdge fills every existing row with INITIAL, TODAY and NOW included. A new field is therefore added with ADD COLUMN ... DEFAULT <literal> and the default is dropped right after, because FWD's DDL standard keeps no SQL defaults (Greg, #9173).
• Column placement (#11956). PostgreSQL only appends columns. The tsvector__ columns of FTS stay at the end of the table, so a field added later must not land after them.
• Index form (#11956-49). normalizeUniqueIndexes() depended on declaration order, and import and DDL produced different unique markings. Without a fix, a fresh database and an imported one can never be identical.
• Renames and type changes (#8786-5, #8786-6). A rename done as drop + add loses data; type changes need conversion rules OpenEdge itself refuses.
• Liquibase (#8786-11). Considered and not used: plain numbered SQL is enough, and the third-party dependency does not fit the build.
• Sharded schemata (#6320). DMOs may move into per-profile packages; the readers handle that and fail loudly on ambiguity.

From this analysis came the architecture:

• One physical model, read from pg_catalog. The previous release's create DDL and the new one are each loaded into a scratch schema in a transaction that is always rolled back, and read back. The live database is read the same way. 4GL names and INITIAL values come from the baseline jar's DMO annotations and from the new .p2o.
• A differ and a planner. The differ matches tables, fields and indexes by their 4GL names, so a SQL-only rename becomes a RENAME. The planner classifies every change as Auto, Auto costly, Guarded, Refused or No-op, and orders the statements by stage.
• Steps shipped in the application jar. Each release adds one step under upgrade/<db>/, with a plan.txt. The jar also carries the create SQL of every earlier release, so one jar can check and upgrade a database at any recorded version.
• A version table and a runner. fwd_schema_version records each applied step. The runner applies pending steps in order, one transaction per step, exactly once, and resumes after a failure.
• A checker. It compares a live database with the expected schema and names every difference. A catalog fingerprint lets the FWD server check the database at startup.

2. Design from the testcases and probes¶

Every behaviour question was answered by running it, on OpenEdge 12.8 (the reference VM), PostgreSQL 16 and H2, and by replaying the real schema history of the testcases project.

• OpenEdge never rewrites existing rows on a schema change, except to fill a newly added field. INITIAL changes leave existing rows alone, so they need no SQL.
• OpenEdge refuses an extent change, every type change except integer to int64, and a unique index over duplicates (the whole load is backed out). The tool therefore applies other type and extent changes only as a Guarded drop + add, and checks for duplicates before any DDL.
• MANDATORY keeps ? in existing rows in OpenEdge, while FWD emits NOT NULL. Making a field mandatory is Guarded and needs a fill step for those rows.
• DECIMALS changes keep stored values in OpenEdge, while PostgreSQL rounds on a scale cut. A decrease is Guarded.
• INITIAL TODAY / NOW are stored once at load time with the loading session's offset. The runner computes them once, in the FWD server's time zone, and never uses now() or a volatile default.
• PostgreSQL: an added column with a default and then DROP DEFAULT equals a fresh column except for two internal catalog columns the checker ignores. int4 to int8 and a scale increase rewrite the table; CREATE INDEX CONCURRENTLY cannot run in a transaction, so an upgrade is an offline step.
• Name normalization: index and trigger definitions always carry the schema name; other definitions do when their schema is off the search path. The reader empties the search path and normalizes schema, table and object names, so tenant schemas compare equal.
• Rebuilding a table by rename and copy collides with the old table's index and primary-key names (t_pkey1). The rebuild renames them first.
• Testcases replay: 12 real upgrade steps of data/tstcasesdb.df (bzr revisions 1508 to 1920) gave 59 changes, all classifiable.

Three findings changed the original assumptions:

• Column order: FWD orders a table's columns by ORDER, not by file order. A .df change that gives fields new ORDER values reorders the columns, so a table rebuild is needed far more often than first assumed (the replay step 1704 to 1761 needs one).
• Decimals: FWD now emits decimals as numeric(50 + DECIMALS, DECIMALS) and ignores MAX-WIDTH. A DECIMALS increase is therefore Auto, costly, and a MAX-WIDTH change needs no SQL.
• Case sensitivity: OpenEdge's own incremental dump drops and re-adds an index around a case-sensitivity change of an indexed field, so that change is not limited to hand-edited .df files.

3. Testing¶

The implementation was tested at four levels, all on PostgreSQL 16.

• Unit and PostgreSQL tests: 397 JUnit tests in com.goldencode.p2j.persist.upgrade, the dialect and the index normalization, 0 failures. Most apply real generated steps to populated tables and compare the result with a fresh load through the checker.
• Equivalence and data gates over the testcases schema history:
  • an upgraded database equals a database created from the DDL and one created by import.sh, in both directions, with equal fingerprints;
  • a database imported with the pre-fix FWD build is repaired with --seed --repair-indexes, upgraded, and then equals a fresh one;
  • every new field of a populated table holds its INITIAL value, TODAY and NOW included (a synthetic revision adds fields to populated tables); untouched columns keep their checksums and sequences keep their current values.
• Change-catalogue battery: 38 hand-changed versions of tstcasesdb.df, one per catalogue row (tables, fields, types, extents, mandatory, decimals, indexes, word indexes, renames, sequences). Each was loaded into OpenEdge 12.8 to confirm the expected behaviour, then upgraded on PostgreSQL and compared with a fresh database. All 38 pass; two rows exposed the decimals and case-sensitivity findings above.
• Live runs: the FWD server's startup check was run in a real server in both modes; a 4GL data step ran in a real batch FWD session; the runner and the wrapper were run by hand against imported testcases databases.

Probe sets behind the design: 24 OpenEdge scenarios, 17 PostgreSQL checks and one H2 check.

Not tested yet: PostgreSQL 12 and 17, a real change of FWD version between two releases, Windows, and a reader who did not work on this following the user guide.

4. Decisions taken¶

Design decisions¶

Decisions taken during implementation¶

• Unique indexes. Any index change whose target is unique is Guarded with a duplicate pre-check, because moving recid from a key column into INCLUDE can expose duplicates.
• Index repair needs no allow-list entry: --repair-indexes --backup-confirmed and the duplicate pre-check are the operator's consent.
• Read-only modes. --check, --dry-run and --sql-out run while the application is connected; applying, seeding, repairing and the hook refuse.
• Permissions. A step that adds tables only grants on its new objects, following the schema's default privileges. It never changes an owner. A database without FWD's permission model must apply it once with fwd_pg_schema.sh --apply.
• Table rebuilds copy grants, comments, column grants, storage parameters, statistics targets, replica identity and customer indexes. A table carrying objects they cannot reproduce (customer triggers, row-level security, rules, foreign keys) is refused before any DDL and needs a hand-written step.
• Large rebuilds. The row count is unknown at conversion, so a rebuild stays inside its step's transaction. --dry-run warns about every rebuilt table over about 1,000,000 rows.
• Normalized extents. A resized normalized extent fills the new child table with INITIAL; values are not copied, as in OpenEdge.
• Data steps. Java steps run on the runner's own connection inside the sub-step's transaction and cannot commit or roll it back. 4GL steps run in a batch FWD session of the matching release; the runner checks that the session really used the target database. 4GL steps are refused on tenant schemas.
• Word indexes. Dropping a word index is Auto, costly, so the pre-start hook never applies it unattended.
• Pre-start hook. All or nothing: with any unsafe step pending it applies none and stops the launch. It is enabled with FWD_SCHEMA_PRESTART=enabled.
• Test engine. build.gradle adds the JUnit Jupiter engine and runs only that engine in ./gradlew test.

5. Remaining decisions¶

• Index output change. The index normalization fix in P2JIndex changes every project's fresh DDL, .p2o and DMO unique flags, whether or not the project uses the tool. It needs a sign-off and coordination with branch 11956a (Stefanel), which touches the same code.
• Large rebuilds. Whether a rebuild of a very large table should become its own sub-step, which needs a per-table size hint at conversion.
• Normalized extents. Confirm that a resized normalized extent loses its values; the design first assumed they are copied.
• PostgreSQL versions. Run the gates on PostgreSQL 12 and 17, and fix FWD's minimum supported version for this tool.
• Other upgrade pipelines: how projects with their own upgrade pipelines move over (seed after their last own step is the proposal).
• schema_check.sh from 11956a: it is to become a wrapper around schema_upgrade --check.
• Refuse mode. With schema-check-mode=refuse, a mismatch on a database loaded at startup stops the whole server. Decide whether that is wanted.
• One recommended fix before merge: an ignore-list entry for a word-indexed field can also hide the word triggers of sibling fields whose names share its prefix.

6. Usage¶

Configuration¶

In cfg/p2j.cfg.xml, per database namespace:

The project's build must copy upgrade/ into the application jar next to ddl/.

Stage A: adopt the tool (once per project)¶

1. Point schema-baseline at the release jar running in production, and set schema-baseline-version to its label.
2. Build the first release with the tool (stage B).
3. Record every production database once. If it checks clean, use --seed; if it was imported with import.sh and differs only in its indexes, use --seed --repair-indexes --backup-confirmed. New databases created by import.sh record their version themselves.

./p2j/tools/scripts/schema_upgrade.sh -f ./p2j/build/lib -a deploy/lib/myapp.jar \
    --db_names=mydb --db_adminid=fwd_admin --seed=1.0

Stage B: build a release (once per release)¶

1. Change the .df, set schema-version to the new label and schema-baseline to the previous release jar.
2. Convert. Conversion writes upgrade/<db>/<NNNN>_<from>_to_<to>_postgresql.sql and <NNNN>_plan.txt; when the FWD version changed, also an FWD-only step before it.
3. Read plan.txt. A blocked plan stops the conversion: approve Guarded changes in schema-upgrade-allow, and resolve Refused ones with a hand-written step.
4. Rehearse on a copy of production, build the release jar and commit upgrade/<db>/.

Stage C: upgrade a database (once per database)¶

1. Back up the database and check that the backup restores.
2. Run the pre-flight while the application still runs: --dry-run checks the database, lists the pending statements, row counts, lock estimates and the TODAY / NOW values.
3. Stop the FWD server and every other client, deploy the new jars, and apply:

./p2j/tools/scripts/schema_upgrade.sh -f ./p2j/build/lib -a deploy/lib/myapp.jar \
    --db_names=mydb --db_adminid=fwd_admin --backup-confirmed \
    --server-config=deploy/server/server.sh

4. The runner applies each step in its own transaction, records it in fwd_schema_version, checks the result against the new jar's create SQL and records the fingerprint. On failure the step is rolled back and a new run resumes there.

5. Start the server. Its startup check warns (or, in refuse mode, refuses) when the database and the jar do not match.

Other modes¶

Exit codes: 0 clean, 1 drift (for the hook: an unsafe step is pending), 2 error.

The code is committed to 8786a. I am yet to go through it, or at least a detailed summary of the changes since there are 80 new and modified files.

Very interesting! Nice work!

One quick comment: the solution can't be specific to PostgreSQL. Anything dialect-specific must be hidden in our dialect layer.