The FairCom DB SQL import utility, ctsqlimp, utility “registers” or links existing c-tree files with a FairCom DB SQL database without modifying the table structure such that the files remain accessible from the original application and also accessible from FairCom DB SQL.
ctsqlimp Usage
ctsqlimp is used as follows (after creating the database file):
ctsqlimp.exe <filename> [options]
Valid [options] values:
- <filename> - data file name/path (relative paths to FairCom DB directory)
- -h - display usage help
- -d database - database name (default: ctreeSQL)
- -s server - FairCom DB SQL Server name (default: FAIRCOMS)
- -u userid - userid for logging into FairCom DB SQL
- -a password - user password for authorization
- -n symbolic - set SQL table name to symbolic
- -o userid - set owner of table to userid
- -i - non-interactive mode: ignore errors and continue
- -r - remove table from dictionaries (file is not deleted)
- -k - skip fields that don't comply with conventional identifiers rules
- -c - allow table names not complying with conventional identifiers rules
- -x - skip indexes
- -p - promote unsigned integer to greater signed type
- -P - promote unsigned types to greater signed type and set check for fitting value
- -t - do not promote unsigned types to greater signed type
- -m idxname - set index idxname as primary key
- -z - allow indexes with missing string terminator in key segments
- -l <size> - specify LONGVAR* field size threshold
- -g - ignore existing index name in IFIL resource
- -b - grant public access permissions to linked table
- -B - grant read-only permissions on the table to the public
Note: If both -B and -b are specified, the read-only setting takes precedence. Notice that the owner of the table and the DBA have all the permissions.
This introduces a change in behavior for existing applications because this switch is now-case sensitive.
- -j - non-interactive relink of existing table
- -w script - write CREATE statements into script file script instead of importing the table
- -e xmlfile - get DODA definitions from external XML file xmlfile
- -f s(f) | z(f) | sz(f) - force string padding to (s)paces (z)eroes or (sz)spaces zero terminated. (f)orce presence of field delimiter
- -l <size> - specify LONGVAR* field size threshold
- -q prefix - prefix SQL table name with prefix (when the -q option is combined with the symbolic table name option, -n, the prefix is prepended to the symbolic name instead to the table name)
- -y - keep existing permissions and synonym
- --rowid_fld fldname - expose the ROWID value as field with name (table must have $ROWID$ field)
- --rowid_idx idxname - expose index on the ROWID value with name (--rowid_fld option must be specified)
- --tls - use a TLS connection with no certificate check (mutually exclusive with --tls_cert)
- --tls_cert <cert> - use a TLS connection with certificate 'cert' (mutually exclusive with --tls)
- --ignore_index_case - ignore case in segment modes when linking the index (this may cause incorrect query result)
Note: The parameters are case-sensitive. By default they are lowercase unless otherwise stated.
Example
To make existing c-tree files mydata.dat (containing proper IFIL and DODA resources) and mydata.idx accessible via FairCom DB SQL, follow these steps:
- Create a database named ctreeSQL.
- Copy your ISAM custmast.dat and custmast.idx files into the ctreeSQL.dbs subdirectory of the server's working directory.
- Ensure FairCom DB SQL is running, as ctsqlimp is a client application.
- Run the ctsqlimp utility found in the /tools/cmdline/admin/client/ directory of your FairCom DB installation:
ctsqlimp custmast.dat -u ADMIN -a ADMIN -s FAIRCOMS
- Run the Interactive SQL, isql, utility and issue the command:
SELECT * FROM custmast;
Specifying Primary Keys from Imported Indexes
A command-line option is available with the FairCom DB SQL Table Import Utility, ctsqlimp to specify a primary key from imported indexes. Use the -m option to specify an imported index of the table to be the primary key. The -m option takes an index name as a parameter. The index name is defined in the ridxnam member of the IFIL resource IIDX structure. Please notice that if ridxnam is not defined, an index name is automatically assigned when the table is added to the FairCom DB SQL system tables. The format of the automatically assigned index name is: tablename_indexnbr where indexnbr is zero for the first index.
Example
ctsqlimp my_table -u ADMIN -p ADMIN -m my_index
ctsqlimp Check for Max Number of Indexes/Segments
In V10.3 and later, logic in ctsqlimp checks for the maximum number of indexes or segments. When importing tables, this logic checks if the number of indexes defined is less than MAX_DAT_KEY value and the number of segments per key is less than MAX_KEY_SEG value. The new logic prevents the import unless you are using interactive ctsqlimp, which asks if you want to continue with the import because it may be useful to import the table and adjust the server setting afterwards.
Allow Linking SQL Indexes with Rightmost Segment(s) on Hidden Field(s)
Prior to V11, the ctsqlimp logic did not allow importing indexes if at least one of the segments points to a field not exposed in SQL. This is encountered frequently in the FairCom RTG products.
In V11 and later, the ctsqlimp logic optionally (OFF by default) allows importing indexes having segments pointing to hidden fields if at least one segment is valid and all segments pointing to hidden fields are the rightmost with no intermixing of exposed and hidden fields.
This logic is controlled by a new sqlimport callback CTSQLCB_CONFIRM_INDEXTRUNC.
Mapping Unsigned Integers with FairCom DB SQL
A command line option is available to ctsqlimp, -p (note lower case), to map an unsigned integer (CT_CHARU, CT_INT2U and CT_INT4U) not to a corresponding FairCom DB SQL type but to the next larger integer type, thus “promoting” it.
The related -P option (note upper case) also promotes unsigned types to a greater signed type, however it includes a CHECK clause on the column to avoid SQL values rolling over due to the smaller range of available signed values.
While the FairCom DB SQL engine will use the larger integer type, nothing at the c-tree ISAM storage level changes. This allows proper unsigned (positive) values for those values that when mapped into a signed field would become negative.
Without the -p option the mapping from c-tree to FairCom DB SQL is the following:
- CT_CHARU - TINYINT
- CT_INT2U - SMALLINT
- CT_INT4U - INTEGER
With the -p option the mapping becomes:
- CT_CHARU - SMALLINT
- CT_INT2U - INTEGER
- CT_INT4U - BIGINT
When using the -p switch, a SELECT * from table shows the proper values for unsigned integer fields instead of a negative value.
When doing an insert, as long as the inserted value fits in the original type range, the value seen at the ISAM level is the proper one. If the value inserted is outside the original type range, only the lower significant part is stored.
Check for Invalid Characters in Field Names
The -k switch allows one to skip importing fields that do not comply with the conventional identifier rules without being prompted every time an incompatible field is encountered.
ctsqlimp also has a “non-interactive” option (-i) which avoids prompting the user by having a list of pre-defined default answers. The actions taken by default are the most conservative and safe. In case an unconventional field is encountered, the default action is to import the field and inform the user that in FairCom DB SQL statements, the resulting column name must be enclosed in double quotation marks.
The option -k can be used in conjunction with the -i option to change the default behavior in case an unconventional field is encountered and automatically skip it.
Support for Importing Table with an Alternative Name
The FairCom DB SQL import utility derives the table name to be stored in the FairCom DB SQL system tables from the physical file name, for instance the table custmast.dat is imported as custmast. It is possible to specify a FairCom DB SQL table name on the command line by using the -n option.
Null Fields and Field Default Values Considerations
The import utility checks if a table being imported was created with NULL FIELD support (this is true for tables created with c-treeDB API APIs). If the table being imported was created with NULL FIELD support, the import utility updates the SQL dictionaries accordingly, by setting the NULL FIELD flag to “Y”. If a table being imported has no NULL FIELD support, the NULL FIELD flag of the FairCom DB SQL dictionary will be set to “N” and the FairCom DB SQL dictionary default value for that field will be set to 0 for numeric fields, or “ ” for string fields.
Default values are not available for SQL_LONGVARCHAR and SQL_LONGVARBINARY field types and the NULL FIELD flag for those fields are always set to “Y”, even though null values are not supported for these types of fields. A side effect would be, for example, inserting a NULL value in a LONG VAR CHAR field and reading back an empty string when the field is retrieved from a record. Please note that this only affects tables created without NULL FIELD support.
ctsqlimp Import Indexes with Segment on String Field Missing Last Byte
A fairly common situation C programmers may encounter is that the last byte from an index segment is usually assumed to be ‘\0’ (nul), when in fact it is not. The index segment then does not match the entire field (many times based on a CT_FSTRING).
There have been inquiries about importing into FairCom DB SQL indexes where a segment is on a string field, however, it is missing the last byte. While this is atypical usage, with c-tree technology there is no actual problem in allowing these indexes.
The FairCom DB SQL can map an index segment into a field even if the segment does not match the entire field and to properly perform the segment low-level handling. No check is made on the “nature” of the partial segment. It is up to the import utility to verify the index is proper for FairCom DB SQL handling.
The feature itself is off by default and is activated with the SQL_OPTION PARTIAL_SEG server configuration keyword.
Note: The only way FairCom DB SQL may be aware of an index using a partial segment is because this index has been imported by ctsqlimp. Using an index with partial segment imported with ctsqlimp without the SQL_OPTION PARTIAL_SEG turned on results in a CTDBRET_NOTFIELD error.
Details
Logic was added to the ctsqlimp utility to import indexes with segment on string field missing the last byte.
Note: This feature is turned on with the -z command line switch and requires a server with SQL_OPTION PARTIAL_SEG turned on.
Import Duplicate Filenames into FairCom DB SQL
The FairCom DB SQL import utility, ctsqlimp, has been updated to take advantage of the ability to import multiple files with the same physical name into an SQL database. Simply use the ‑n (symbolic) name option on import to uniquely identify each table in FairCom DB SQL:
>ctsqlimp ./data/A/myfile.dat -u ADMIN -a ADMIN -n myfileA
>ctsqlimp ./data/B/myfile.dat -u ADMIN -a ADMIN -n myfileB
SQL Tip
To create a consolidated view over these two files, create individual views to present the tables as one. A "mapping" table can be created to provide table source identifying information to the view. Consider that myfile.dat contained the fields name, id, and moddate:
CREATE TABLE map (consolidated_name CHAR(32), symbolic_name CHAR(32);
INSERT INTO map VALUES ('myfile', 'myfileA');
INSERT INTO map VALUES ('myfile', 'myfileB');
CREATE VIEW myfileA_vmap AS (
SELECT myfileA.name, myfileA.id, myfileA.moddate, map.consolidated_name, map.symbolic_name
FROM myfileA CROSS JOIN map
WHERE map.symbolic_name = 'myfileA'
);
CREATE VIEW myfileB_vmap AS (
SELECT myfileB.name, myfileB.id, myfileB.moddate, map.consolidated_name, map.symbolic_name
FROM myfileB CROSS JOIN map
WHERE map.symbolic_name = 'myfileB'
);
CREATE VIEW myfile AS (
SELECT * from myfileA_vmap
UNION
SELECT * from myfileB_vmap
);
SELECT * from myfile;
Auto Import
FairCom DB has the ability to "Auto Import" a table into SQL at the time it is accessed. To use this feature, you will need to supply a SQL callback library providing the information needed to import the database, such as the database name, the table owner, and the table name. At runtime, when the SQL parser encounters an object not present as a table in the system tables, it calls the registered function and imports the database.
Auto Import Callback
FairCom has implemented the ability to "auto import" a table into SQL at the time it is accessed. This feature requires the user to provide a SQL callback library (ctsqlcbk) implementing the following:
- The ctsqlCallbackLoaded function handling a call with type == 2 setting *ptr to point to the function in 2. below.
- A thread-safe function in ctsqlcbk that, given the database name, the table owner, and the table name, provides the necessary CTSQLIMPOPTS setting to be used to import the table, in particular the filename on disk.
The function prototype is:
CTDBRET ctsqlImpSetOptsCallbackFunc (pCTDBSESSION pSession, pTEXT dbname, pTEXT tblowner, pTEXT tblname, CTSQLIMPOPTS **opts);
where:
- pSession [IN] - the session handle
- dbname [IN] - the name of the database in use
- tblowner [IN] - the table owner of the table
- tblname [IN] - the name in SQL of the table
- opts [OUT] - pointer to a CTSQLIMPOPTS structure holding the information to internally call ctSQLImport function. The CTSQLIMPOPTS dbsnam, srvnam, usrnam, usrpwd, nonint members settings are ignored.
The SQL server engine at startup loads the ctsqlcbk and makes a call to ctsqlCallbackLoaded passing type == 2 and registering the function returned in ptr.
At runtime, when the SQL parser encounters an object not present as a table in the system tables, if the registered function is not NULL and the user has resource permission or DBA permission when the table owner is someone else, the code calls the registered function and if the function returns CTDBRET_OK and sets the *opts != NULL attempts an automatic sqlimport using the opts returned overwriting the ignored members listed above as necessary for an internal server automatic sqlimport.
The "import" occurs in its own independent transaction (it cannot be reverted by rolling back the transaction). If the import fails for any reason, SQL statement execution continues as if the table simply does not exist.
After running the SQL callback function, a function is called to signal the memory allocated for opts should be released as follows:
ctsqlImpSetOptsCallbackFunc (pSession, NULL, NULL, NULL, opts).
See also Auto Import: Tables that are missing on disk will now be Auto Purged.
Auto Import: Tables that are missing on disk will now be Auto Purged
The FairCom DB SQL Auto Import now supports an "Auto Import" callback. When this new callback has been registered, the server should adjust the dictionary in the system tables to remove info for tables that are no longer on disk. The idea being we will constantly update our dictionary to match the files present on disk that have been opened. This means c-tree will automatically purge the information out of the system tables for tables that were previously auto imported but are now missing on disk, and add information for tables (Auto Import) that are opened but were not in the dictionary.
When a table is not found on disk where it was when added to the system table, it is purged and the error -20005 is sent back to the application. No attempt is made to try to see if the table can be imported again.