Background​

The SQL command ALTER TABLE can be used to modify the structure of an ISAM emulated SQL table on the PostgreSQL server. However, in order to preserve the ISAM emulation characteristic, some details have to be taken into account which are described in this article. This article does not claim to be complete. It rather has the character of a best-practice guideline and shall be supplemented over time.

A discussion of ISAM emulated tables and indexes can be found in the Xbase++ documentation Tables/Indexes on the SQL Server.

Columns​

In the following, we outline some thoughts related to changing column/field names or types.
  • Changing the name of a column breaks existing Xbase++ code.
  • The length of the column name should not exceed 10 characters. Longer names are supported but may break existing Xbase++ code.
  • Changing the type of a column may break existing Xbase++ code.
  • If columns are accessed using ISAM semantics, their types must be mappable to standard xBase types as outlined in the DBFDBE / FOXFBE documentation.
  • Adding columns is fine
  • Removing columns which are not referenced in some index key or for expressions is fine.

Indices​

For SQL indexes, no further action needs to be taken when modifying fields. The server rebuilds the index in the background. For indexes of the ISAM emulation, however, there are some points to consider.
  • ISAM emulated indexes may only be created and deleted with the Xbase++ functions OrdCreate() and OrdDestroy().
  • If fields are to be altered that are referenced by an index or for condition, destroy the ISAM index, alter the column/field and finally create the ISAM index. This is outlined in the example below.

Data types​

If a new column is added with the command ALTER TABLE, then restrictions apply to the selection of the data type. In the following table you find the types as they are chosen for the columns during the upsize process. See also the section Choosing the proper data type in the Xbase++ documentation.
SQL typeXbase++ type
booleanL
byteaV
character varyingR
characterC
dateD
double precisionF
integerI
moneyY
numericN
textM
time with time zoneH

Executing the ALTER TABLE command​

Any PostgreSql client is suitable for executing the ALTER TABLE command (e.g. pgadmin4 or psql.exe). Within an Xbase++ program, the command can be sent to the server using the function DacSqlStatement().

Example​

This example demonstrates the manipulation of an ISAM table via the ALTER TABLE command. Prerequisite for the example is the upsizing of the mdidemo example as described in the Xbase++ documentation.
Xbase++: 
#include "pgdbe.ch"

PROCEDURE Main

   LOCAL oSession
   LOCAL cConnStr
   LOCAL cOrdKey

   DbeLoad("pgdbe")
   DbeSetDefault("pgdbe")

   cConnStr := "DBE=pgdbe;server=localhost;"
   cConnStr += "db=mdidemo;uid=postgres;pwd=postgres"

   oSession := DacSession():New(cConnStr)
   IF(!oSession:IsConnected())
     ? "Connection failed ("+Var2Char(oSession:GetLastMessage())+")"
     RETURN
   ENDIF

   // Since we change length of column involved with custb we need first destroy the index
   USE CUSTOMER INDEX custa, custb VIA (oSession)
   OrdDestroy( "custb" )
   USE

   // Add the new column "email"
   oStmt := DacSqlStatement():fromChar( "ALTER TABLE customer ADD COLUMN IF NOT EXISTS email character(50);" )
   oStmt:build()
   oStmt:execute()

   // Change the type of column "lastname" to hold 50 characters (it was 30 characters before).
   oStmt := DacSqlStatement():fromChar( "ALTER TABLE customer ALTER COLUMN lastname TYPE character(50);" )
   oStmt:build()
   oStmt:execute()

   // re-create custb due to field length change
   USE CUSTOMER VIA (oSession)
   INDEX ON Upper(LastName+Firstname) TO CustB
   USE

   USE CUSTOMER INDEX custa,custb VIA (oSession)
   OrdSetFocus( "custb" )
   DbGoTop()
   BROWSE()

   USE

   oSession:disconnect()

RETURN