|
|
|
To highlight the factors to consider when accessing FairCom DB SQL data from ISAM applications, consider the following example. Here we create a table using FairCom DB SQL and then examine the c-tree file specifications. This section also discusses the reverse case: accessing a c-tree ISAM application file from FairCom DB SQL.
|
|
|
The following FairCom DB SQL commands define a table consisting of three fields and an index defined on one of the fields:
CREATE TABLE example (
dbl float,
int4 INTEGER,
ch CHAR(8));
CREATE INDEX example_chidx ON example (ch);
COMMIT WORK;
|
|
|
Now that the table and index have been created, let’s look at the resulting field, table, and index definitions.
Field Definitions
First, let’s examine the field definitions, stored as a DODA resource in the data file. The SQL command specified three columns, but FairCom DB SQL created three additional fields for internal use:
Field name |
Offset |
Type |
Length |
|---|---|---|---|
$DELFLD$ (a) |
0 |
CT_ARRAY |
4 |
$NULFLD$ (b) |
4 |
CT_ARRAY |
1 |
$ROWID$ (c) |
8 |
CT_INT8 |
8 |
dbl |
16 |
CT_DFLOAT |
8 |
int4 |
24 |
CT_INT4 |
4 |
ch |
28 |
CT_FSTRING |
8 |
(a) $DELFLD$ is a placeholder field at the beginning of the record for c-tree deleted record flag. If the first field in a record is not a binary field or the file is VARIABLE LENGTH, $DELFLD$ is not necessary. It can be modified, however, the first byte cannot be set to 0xFF or 0xFE for fixed-length data files, as these are reserved values.
In V9.5 and later, the $DELFLD$ in any new or altered table is either 5 or 9 (HUGE) bytes by default and the TRUNCATE clause causes a new table to be created. A configuration keyword, SQL_OPTION OLD_DELFLD_LEN, forces the use of the old size.
(b) $NULFLD$ is a bit mask indicating which fields contain NULL values to support IFNULL functionality. If this field is not present, then all fields of a record will always be considered NOT NULL, and any operation to set the null flag of a particular field will be ignored. Select queries based on NULL field expressions may not be resolved as expected.
(c) $ROWID$ is an automatic serial number to support ROWID functionality. If this field is not present, then the SQL engine will use the record’s RECBYT. Please note that RECBYT may not be a unique row identifier. As records are deleted and reused, the RECBYT of a deleted record will be used for an inserted record. Select queries based on ROWID may not be resolved as expected when the RECBYT is used.
Considerations for ISAM Specifications
The record starts with 16 bytes of internal field data. The fields accessible to applications via FairCom DB SQL begin at offset 16. FairCom DB SQL automatically maintains the contents of the internal fields using c-treeDB API functions. At the ISAM level, the internal fields can be viewed and modified. If updating records at the ISAM level, care should be taken to preserve the semantics of these fields, as described above.
The field values in a FairCom DB SQL table are set using c-treeDB API functions and can be easily retrieved in the format of the underlying data type using c-treeDB API functions. Some data types (e.g., Date, Time, etc.) will be encoded by c-treeDB API and will be difficult to interpret at the ISAM level.
Table and Index Definitions
This section examines the table and index definitions defined using FairCom DB ISAM resources: IFIL, IIDX, ISEG, and XCREblk.
For the sample file created using the FairCom DB SQL commands shown in this chapter, the resulting ISAM resources are as follows:
IFIL examplefil = { // Data file definition.
".\ctreeSQL.dbs\example", // data file name (a)
-1, // data file number
36, // data record length
0, // data extension size
ctFIXED | ctTRNLOG, // data file mode (b)
3, // number of indices (c)
0, // index extension size
ctFIXED | ctTRNLOG, // index file mode (b)
exampleidx // index pointer
};
IIDX exampleidx[] = { // Index definitions.
{ // Index #1: Internal (RECBYT) index. e
5, // key length (d)
0, // key type
1, // duplicate flag
0, // null key flag
0, // empty character
1, // number of segments
&recbytseg, // segment pointer
"$RECBYT$", // r-tree symbolic index
NULL // alternate index name (a)
},
{ // Index #2: Internal (ROWID) index. e
4, // key length
0, // key type
0, // duplicate flag
0, // null key flag
0, // empty character
1, // number of segments
&rowidseg, // segment pointer
"$ROWID$", // r-tree symbolic index
NULL // alternate index name (a)
},
{ // Index #3: User-created index on ch field. f
12, // key length
0, // key type
1, // duplicate flag
0, // null key flag
0, // empty character
1, // number of segments
&chidxseg, // segment pointer
"example_chidx", // r-tree symbolic index
".\ctreeSQL.dbs\example_chidx.idx" // alternate index name (a)
}
};
ISEG recbytseg =
{0, 1, RECBYT}; // One-byte record position segment.
ISEG rowidseg =
{8, 4, SRLSEG}; // Four-byte auto serial number segment.
ISEG chidxseg =
{5, 8, SCHSEG}; // User-created segment on ch field. (f)
XCREblk xcre = { // Extended creation information.
ctTRANDEP | ctRSTRDEL // x8mode (g)
};
Notes:
(a) The SQL CREATE TABLE and CREATE INDEX commands cause FairCom DB SQL to create three files in the current database directory, which in this example is ctreeSQL.dbs. The files are:
example.dat - contains file definition resources and data records.
example.idx - contains key values for the two internal indices.
example_chidx.idx - contains key values for the user-created index on the ch field.
If additional indices are created, they are created as additional physical files.
(b) The files are created as transaction-processed files.
(c) Three indices are created (two internal indices plus the user-created index on the ch field).
(d) The first index is an internal index known as a RECBYT index used to support enhanced space management capabilities.
(e) The second index is an internal index known as a ROWID index used to support SQL ROWID functionality.
(f) The third index is the user-created index on the ch field. The key is defined over the full length of the field, uses the SCHSEG segment mode, and allows duplicates. To create an index that does not allow duplicates use the SQL command CREATE UNIQUE INDEX.
(g) The files are created using special extended file modes. ctTRANDEP specifies a transaction-dependent file and ctRSTRDEL specifies restorable delete capabilities. These properties enable rollback of file create and delete operations (at both the SQL and ISAM level).
Note: FairCom DB ISAM functions can be used to create data and index files, and if the file definitions adhere to the special properties required of FairCom DB SQL files as detailed here, the files can be easily imported to FairCom DB SQL using the ctsqlimp utility. One reason that a developer might choose this option is to create files with special properties (such as ctADD2END, to create chronological files) that FairCom DB SQL currently does not provide a way to specify at file create time.
Note: Fields of type LVARBINARY and LVARCHAR cannot be included in a SQL index due to their size.
If PRIMARY KEY is specified, FairCom DB SQL creates an additional index file with an automatically-generated name, such as sys_001_000001078.idx, and adjusts the IFIL resource in the data file to include an additional index definition.
The same is true if FOREIGN KEY is specified: FairCom DB SQL creates an additional index file with an automatically-generated name and adjusts the IFIL resource in the data file to include an additional index definition.
Note: FairCom DB ISAM has no knowledge of FairCom DB SQL referential integrity constraints. It’s possible to violate referential integrity constraints so use caution when updating records directly using ISAM functions.
