V2.0 PostgreSQL ISAM emulating tables and SQL query processing

Background​

The Xbase++ PostgreSQL DatabaseEngine provides two different access method to data. First, the ISAM emulating approach which works with dedicated SQL tables only. These SQL tables need to be created using ISAM commands like DbCreate/INDEX ON/OrdCreate or have been upsized from exisiting DBF/CDX/NTX files using the DbfUpsize Tool from Xbase++. ISAM emulating tables are appearing inside Xbase++ as a ISAM table like it was in Clipper days, however they are in fact tables on the PostgreSQL server.

Second, there is the SQL approach using DacSqlStatement(), using that way developers have full access to all features of the PostgreSQL server w/o any restrictions but need to use SQL for that.

The good thing with the ISAM emulating tables is that they can be reached using regular SQL statements via the DacSqlStatement() class. In other words it is totally legal in Xbase++ to mix SQL and ISAM code regarding the same table on the PostgreSQL server.

An example for that would be the ALTER TABLE statement which can be used with ISAM emulating tables without harm as long as the ISAM specific meta columns are not touched.

Also accessing the ISAM emulating SQL tables using plain SQL from Xbase++ or any other langauge is transparently supported, this is even true for data manipulation.

However due to the nature of a ISAM table some factors need to be considered. In the following we look into query processing with SQL.

Query processing using SQL​

When performing query processing using SQL against a ISAM emulating table there are no restrictions, you can apply any SQL SELECT. However a few things need to be kept in mind

1. Always be aware that a result set is a complete copy of data transported over the wire from the server to the client. Consequently it is a good idea to restrict your SQL SELECT clause using a WHERE clause to request only those you really need. Or using a LIMIT clause to restrict the shere amunt.
2. In the event your table has BLOBs it is a good idea to request blobs explicit, In other words, do not use the SELECT * FROM ..., instead qualify the columns you are really interested in.
3. When comparing character data keep in mind that the resulting order may be different to the order of ISAM Emulating index. Always use a ORDER BY clause to ensure at least consistent ordering
4. In the event ISAM emulating states such as Deleted() or RecNo() need to be access the ISAM emulating table columns shall be used. The table below list the ISAM Feature and the related SQL column name

Examples:​

To filter all non deleted customer with coming from germany the following would work
SELECT * FROM customers WHERE countrycode='DE' AND !__deleted

Get all records with recno() >10 and <100 from the customer table
SELECT * FROM customers WHERE __record>10 AND __record<100

Manipulating data using SQL​

We recommend to do that if INDEX columns are affected only when necessary. The reason for this is that SQL commands can not maintain the ISAM index columns, instead the PostgreSQL engine will detect the resultung inconsistency and will repair it. But this operation is time consuming and may affect USE command performance. However manipulating data which does not affect index columns is safe and can be done w/o any restrictions.