This post is a follow up to an item from a different thread. Please refer back to the other post for the background on this issue: (Willing experts Idea)

GU_EMPTY is a utility that we wrote that reconciles the file definitions within our application and those that actually exist at the OS / database level. It is able to do this for Oracle, PostgreSQL, ProISAM, and SQL Server

Within our applications, we have a marker file (OR_FILES) that contains ProIV filenames and the actual SQL tables that they should map to. Under our SQL implementation, not all ProISAM files are converted to SQL tables. Work files and counter files are especially left in ProISAM.

The GU_EMPTY utility can be run against a single FILE definition, or all files.

When we send file definitions to clients in a prx, they are always sent in a function beginning with P_ or S_. Our import routine looks for these function names and calls GU_EMPTY if they are found.

Conversely, if we overwrite the entire source code, (which is detected by an internal flag containing the version number), GU_EMPTY runs across all files.

GU_EMPTY methodology – ProISAM

With ProISAM files, GU_EMPTY simply uses the appropriate SYSF commands to see if a file exists and if not creates it. Based on our application, certain files are created with large paging sizes.

GU_EMPTY methodology – SQL Server, Oracle

The SQL Server and Oracle implementation of GU_EMPTY is a touch more complicated. Both SQL Server and Oracle have tables of tables and tables of columns. These are USER_TABLES, USER_TAB_COLUMNS, SYSOBJECTS, and SYSCOLUMNS.

Our GU_EMPTY routine reads FILEHDR and then the appropriate table table. If no entry is found, the SQL to create the entire table is spontaneously generated. If the table does exist, we read FILEDEF and the column table to see if all columns exist. If any don’t exist, alter table SQL is generated.

Since global functions do not automatically regen, we have to create the SQL as an external script and do a system to call to execute it. Ideally, this will be replaced with dynamically writing a global function and calling it (having it regen) and execute the SQL. The only problem with the external script is that it can get complicated knowing if you’re in a test region and want to update the test database.

GU_EMPTY methodology – PostgreSQL

Unfortunately, PostgreSQL is even more complicated. Like Oracle and SQL Server, it has a table of tables and a table of columns. Unfortunately, these tables include blobs (oid columns) that cannot be read reliable from ProIV.

Our solution for this was to create tables without varchar columns instead of oid column and read the PostgreSQL system tables into our tables. Then the same reconciliation process takes place… with one exception.

In Oracle and SQL Server, you can add columns with both a default value and a not null constraint. However, PostgreSQL does not allow this. Instead, adding columns is a three-step process

Alter table – add column
Update – set column to zero or ‘ ‘ (zero for numeric, ‘ ‘ for alpha)
Update – set column constraints – default value and NOT NULL

Final notes

Due to some really funky ProIV error messages, GU_EMPTY is broken into 4 parts. The ProIV errors were non-existent on some platforms and made other platforms bomb out entirely. The same function worked fine in one application and failed in the other. Since breaking it apart, we have had no problems.