Background​

One of the largest differences in how data is managed between SQL and ISAM is how deleted records are handled. In short:
  • ISAM: does mark a record as deleted. This way it is out of scope. To really get rid of the record you need to do a time consuming PACK operation.
  • SQL: does really delete the record/row.
But why is this? The reason for that difference is very simple. ISAM has a natural order with an inherent constraint. The record# is a unique, constant identifier for that record until a PACK happens. Where the PACK still preserves the order in time the record was added. While in SQL there is no natural order, that's why you need an order by clause to ensure consistent results between the same SELECT/query over time.

The PostgreSQL DatabaseEngine and its ISAM emulation can deal very well with all that differences if it comes to ISAM emulation of that behavior. But what if the SQL developers want to delete a record? The answer here is very simple. The SQL developer needs to deal with the ISAM semantics. If not he would delete a record leading to:
  • Inconsistent data, record# are missing leading to inconsistent navigational behavior on behalf of the ISAM emulating side
  • Possible corrupted orders, typical pattern 1:N relationship established via implicit ordering by record# in detail items
  • Broken business logic on the Xbase++ side because record re-cycling is malfunctioning
Those are just the major issues we identified in our earlier case studies when designing the ISAM emulation. For now, deleting a record via SQL is a "don't do it"! Of course, we have worked out concepts to support SQL record deletion but this also leads to behavior that violates the ISAM semantics and requires very careful adaption of your existing Xbase++ business logic aka. PRG code.

But how to do it right from the SQL side if you really want to use more of the power of an SQL DBMS, just think about getting rid of deleted records or using constraints on the tables and database. In the following, we are starting a collection of concepts for problems you may run into if you want to have full interoperability of ISAM emulating tables and your SQL code.

// getting rid of deleted record on the SQL side​

That's a simple no-brainer, PostgreSQL comes with updateable views (non-materialized). So, with the following view, you can hide all the deleted records from your SQL code.

SQL: 
CREATE VIEW artikel4sql AS (SELECT sn,ver, __record FROM artikel WHERE __deleted=false);

The previous code does a few things:
  1. Use "4sql" as a generic postfix for all SQL views derived from an ISAM emulating table. So artikel becomes artikel4sql
  2. Use only the real table column plus the record#. The reason for the record# is that some ISAM tables have not been well designed. They use record# as an order, or there is no primary key in the ISAM table so you can not identify the row in SQL.
  3. We only use the "public" fields in the SQL view, we do not use __order columns. This way we avoid dependencies between order columns and SQL views.
  4. We use a WHERE clause to filter out the deleted record

// establish a unique constraint on a SQL table​

Sometimes it is helpful to establish a constraint on behalf of the SQL server to avoid writing new code related to data model-specific rules outside of Xbase++.
To do so we establish in the following a unique constraint for our artikel table.

SQL: 
DROP TABLE IF EXISTS artikel CASCADE;
CREATE TABLE artikel ( id CHAR(5), ver CHAR(4), __deleted BOOLEAN, __record INT);

CREATE VIEW artikel4sql AS (SELECT id,ver, __record FROM artikel WHERE __deleted=false);

CREATE UNIQUE INDEX i_artikel ON artikel (id) WHERE __deleted=false;

-- added three records for testing
--
INSERT INTO artikel VALUES( 'T1', 'T1v1', false, 1 );
INSERT INTO artikel VALUES( 'T2', 'T2v1', false, 2 );
INSERT INTO artikel VALUES( 'T3', 'T3v1', false, 3 );

-- delete a record #2 and add one with the same key value, does not violate constraint,
-- W/O deletion the insert will violate the unique constraint
--
UPDATE artikel SET __deleted=true WHERE __record=2;
INSERT INTO artikel VALUES( 'T2', 'T2v2', false, 4 );

SELECT * FROM artikel4sql ORDER BY __record;

Of course, the approach above requires that you do not RECALL a deleted record as this may lead to a unique constraint violation. Also, since CDX or NTX base storage drives store duplicate keys in a table and just do not add the record to the index existing business logic may now fail with runtime errors due to a constraint violation even though your code worked very well in the past.

The bottom line here is that you can do a lot with the SQL DBMS, but any constraint/rule applied to that level (database/table) also affects how your or if your existing business logic, which is based on ISAM semantics, works.