Icon Creating and Altering Tables

Introduction
Creating tables and altering the structure of existing tables is accomplished through the CreateTable and AlterTable methods of the TDBISAMTable component. The properties used by the CreateTable and AlterTable methods include the FieldDefs, IndexDefs, DatabaseName, TableName, and Exists properties.

Basic Steps
There are four basic steps that need to be completed when creating a table or altering the structure of an existing table. They are as follows:

1) Define the field definitions using the FieldDefs property, which is a TDBISAMFieldDefs object.

2) Define the index definitions, if any, using the IndexDefs property, which is a TDBISAMIndexDefs object.

3) Set the database and table information using the DatabaseName and TableName properties.

4) Call the CreateTable method if creating a table or the AlterTable method if altering the structure of an existing table.

Defining the field definitions
The FieldDefs property is used to specify which fields to define for the new or existing table. The FieldDefs property is a list of TDBISAMFieldDef objects, each of which contains information about the fields that make up the table. You may add new TDBISAMFieldDef objects using the Add method. There are two different versions of the Add method. One is for use when creating a table and does not accept a FieldNo parameter as the first parameter, and the other is for use when altering the structure of an existing table and requires that you specify the FieldNo parameter as the first parameter. The reason for this difference is that DBISAM uses field numbers (1-based) to distinguish between existing fields in a table and new fields being added. It also uses field numbers in addition to the index position (0-based) of a field definition in the FieldDefs property to determine if a field has been moved in the structure, but still exists. The use of field numbers also allows for the renaming of existing fields in a table without losing data when altering the structure of an existing table.

Information You may use the FieldDefs property's Update method to automatically populate the field definitions for the table from table itself specified by the TDBISAMTable TableName property.

The following summarizes how field numbers and the index position of field definitions are used when creating a table or altering the structure of a table:

ValueRules
Field NumberA field number is 1-based, meaning that it starts at 1 for the first field definition in a table. A field number is automatically assigned for all field definitions when creating a table so it need not be specified and will be ignored if specified.

When altering the structure of an existing table, a field number is required for each field definition. As indicated above, using the FieldDefs property's Update method will automatically populate the correct field numbers from an existing table. If adding a new field, the field number should be set to the next largest field number based upon the existing field numbers in the FieldDefs property. For example, if you have 5 field definitions in the FieldDefs property and wish to add another, the new field definition should be specified with 6 as its field number.

Information The field definitions represented by the FieldDefs property can have gaps in the field numbers when altering the structure of an existing table. The is because it is possible that a given field definition has been deleted, which means that its field number would not be present anywhere in the field definitions. This type of condition is exactly what indicates to DBISAM that the field should be removed from the table structure.
Index PositionAn index position is 0-based, meaning that the first field definition is at index position 0, the second field definition at index position 1, etc. When creating a table or altering the structure of an existing table, the index position represents the desired physical position of the field definition in the table after the table creation or alteration takes place.

When altering the structure of an existing table, you can move field definitions around to different index positions and leave their field numbers intact. This will indicate to DBISAM that the field has simply moved its position in the structure of the table. You can also use the Insert method to insert a field definition at a specific index position. Like the Add method, there are two versions of the Insert method, one with a FieldNo parameter for use when altering the structure of an existing table and one without for use when creating a table.

Defining the index definitions
The IndexDefs property is used to specify which indexes to define for the new or existing table. The IndexDefs property is a list of TDBISAMIndexDef objects, each of which contains information about the indexes defined for the table. You may add new TDBISAMIndexDef objects using the Add method. Unlike field definitions, DBISAM uses the index name to distinguish between different index definitions, and their index position in the list of index definitions is irrelevant.

Information You may use the IndexDefs property's Update method to automatically populate the index definitions for the table from table itself specified by the TDBISAMTable TableName property.

Please see the Index Compression topic for more information on the options for index compression in DBISAM.

Setting the Database and Table Information
The DatabaseName and TableName properties are used to specify the name and location of the table to create or the name of the table whose structure you wish to alter. The DatabaseName property can be set to a value that matches the DatabaseName property of an existing TDBISAMDatabase component, or it may directly specify the path to the new or existing table. The TableName property specifies the name of the new or existing table.

Information Keep in mind that Linux has case-sensitive file names when specifying the TableName property in Kylix. Since the logical table name is used in DBISAM as part of the physical files that make up the table (.dat for data, .idx for indexes, and .blb for BLOBs), the character casing of the table name matters greatly with Kylix.

Please see the DBISAM Architecture and Opening Tables topics for more information.

Creating the Table
After defining the field and index definitions and setting the database and table information, you can call the CreateTable method to create the actual table. It is usually good practice to also examine the Exists property of the TDBISAMTable component first to make sure that you don't attempt to overwrite an existing table. If you do attempt to overwrite an existing table an EDBISAMEngineError exception will be raised. The error code given when a table create fails due to the table already existing is 13060 and is defined as DBISAM_TABLEEXISTS in the dbisamcn unit (Delphi and Kylix) or dbisamcn header file (C++Builder).

The CreateTable method can be called without any parameters or you may specify many different parameters that set table-wide information for the table such as its description, locale, etc. The following example shows how to create the local "customer" table using the CreateTable method without any additional parameters:

begin
   with MyTable do
      begin
      DatabaseName:='d:\temp';
      TableName:='customer';
      with FieldDefs do
         begin
         Clear;
         Add('CustNo',ftFloat,0,True);
         Add('Company',ftString,30,False);
         Add('Addr1',ftString,30,False);
         Add('Addr2',ftString,30,False);
         Add('City',ftString,15,False);
         Add('State',ftString,20,False);
         Add('Zip',ftString,10,False);
         Add('Country',ftString,20,False);
         Add('Phone',ftString,15,False);
         Add('FAX',ftString,15,False);
         Add('Contact',ftString,20,False);
         end;
      with IndexDefs do
         begin
         Clear;
         Add('','CustNo',[ixPrimary]);
         Add('ByCompany','Company',[ixCaseInsensitive],
             '',icDuplicateByte);
         end;
      if not Exists then
         CreateTable;
      end;
end;

Altering the Structure of the Table
After defining the field and index definitions and setting the database and table information, you can call the AlterTable method to alter the structure of the existing table. It is usually good practice to also examine the Exists property of the TDBISAMTable component first to make sure that you don't attempt to alter the structure of a non-existent table. If you do attempt to alter the structure of a non-existent table an EDBISAMEngineError exception will be raised. The error code given when a table open fails due to the table not being present is 11010 and is defined as DBISAM_OSENOENT in the dbisamcn unit (Delphi and Kylix) or dbisamcn header file (C++Builder). Also, DBISAM requires exclusive access to the table during the process of altering the table's structure and an EDBISAMEngineError exception will be raised if the table cannot be opened exclusively. The error code given when a table open fails due to access problems is 11013 and is defined as DBISAM_OSEACCES in the dbisamcn unit (Delphi and Kylix) or dbisamcn header file (C++Builder).

The AlterTable method can be called without any parameters or you may specify many different parameters that set table-wide information for the table such as its description, locale, etc. If you wish to leave all of the table-wide information as it currently exists in the table, then you should pass the following TDBISAMTable properties to the AlterTable method (in this order):

LocaleID
UserMajorVersion
UserMinorVersion
Encrypted
Password
Description
IndexPageSize
BlobBlockSize
LastAutoIncValue
TextIndexFields
TextIndexStopWords
TextIndexSpaceChars
TextIndexIncludeChars

These properties can be read from the exising table without requiring the table to be opened first. However, in order for DBISAM to read the Password property of an encrypted DBISAM table or alter the structure of an encrypted DBISAM table in general, the password for the encrypted table must already be defined for the current session or else it must be provided via an event handler assigned to the TDBISAMSession OnPassword event or by the user via the dialog that will be displayed by DBISAM if an event handler is not assigned to this event for the current session. Please see the Opening Tables topic for more information.

Information Calling the basic version of the AlterTable method without any parameters is not the same as calling the AlterTable method with the above properties as parameters. Calling the AlterTable method with no parameters instructs DBISAM to use the default parameters for all table-wide information.

The following example shows how to alter the local "customer" table's structure using the AlterTable method without any additional parameters. In this example we want to add a LastSaleAmount (a BCD field) to this table's structure in front of the LastSaleDate field and then add a secondary index on this new LastSaleAmount field to speed up filtering in SQL queries:

Customer Table Structure Before Alteration

Field #    Name              DataType     Size
----------------------------------------------
1          CustomerID        ftString     10
2          CustomerName      ftString     30
3          ContactName       ftString     30
4          Phone             ftString     10
5          Fax               ftString     10
6          EMail             ftString     30
7          LastSaleDate      ftDate       0       
    
Index Name      Fields In Index      Options
----------------------------------------------
(none)          CustomerID           ixPrimary

begin
   with MyTable do
      begin
      DatabaseName:='c:\temp';
      TableName:='customer';
      { Always make sure the table is closed first }
      Active:=False;
      { Update the field definitions using the
        existing field definitions from the table }
      FieldDefs.Update;
      { Same for the index definitions }
      IndexDefs.Update;
      { Now insert the new field definition.  Notice
        the index position of 6 which is 0-based and
        the field number of 8 which is 1-based and
        equal to the next available field number since
        there are currently 7 field definitions for this
        table }
      FieldDefs.Insert(6,8,'LastSaleAmount',ftBCD,2,False);
      IndexDefs.Add('LastSaleAmount','LastSaleAmount',[]);
      { Now alter the table's structure }
      AlterTable;
      end;
end;

Customer Table Structure After Alteration

Field #    Name              DataType     Size
----------------------------------------------
1          CustomerID        ftString     10
2          CustomerName      ftString     30
3          ContactName       ftString     30
4          Phone             ftString     10
5          Fax               ftString     10
6          EMail             ftString     30
7          LastSaleAmount    ftBCD        2
8          LastSaleDate      ftDate       0       
    
Index Name      Fields In Index      Options
----------------------------------------------
(none)          CustomerID           ixPrimary
LastSaleDate    LastSaleDate         (none)

In addition to using the TDBISAMTable CreateTable and AlterTable methods for creating and altering the structure of existing tables, DBISAM also allows the use of the CREATE TABLE and ALTER TABLE SQL statements.

Backup Files
Unless the SuppressBackups parameter to the AlterTable method is set to True (default is False), DBISAM will make backups of a table's physical files before altering the structure of a table, except when the following four conditions exist:

1) The only alteration of the structure that has taken place has been a change in the table description or the user-defined major or minor version numbers.

2) The only alteration of the structure that has taken place has been a change in the name of a field or its description.

3) The only alteration of the structure that has taken place has been a change in the name of an index.

4) Any combination of these three conditions.

In all other cases DBISAM will make a backup of each physical file associated with the table whose structure is being altered. Each physical file will have the same root table name but with a different extension. These extensions are as follows:

Original ExtensionBackup Extension
.dat (data).dbk
.idx (indexes).ibk
.blb (BLOBs).bbk

Information There is one exception - if the alteration of the table structure has only changed one of the primary or secondary indexes or the full text index (by changing the full text indexing parameters), then only the index file will be backed up. This is designed in this fashion to speed up the process of altering a table's structure when the only change has been to the index definitions.


To restore these files in case of a mistake, simply rename them to the proper extension or copy them to the original file names. Also, these backup files will get overwritten without warning for each structure alteration that occurs on the table. If you need the backup files for future use it's best to copy them to a separate directory where they will be safe.

The file extensions described above are the default extensions and can be changed. Please see the DBISAM Architecture and Customizing the Engine topics for more information.

Tracking the Progress of the Table Structure Alteration
To take care of tracking the progress of the table structure alteration, we have provided the TDBISAMTable and TDBISAMQuery OnAlterProgress events.

Dealing with Data Loss in the Table Structure Alteration
To take care of dealing with data loss during the alteration of a table's structure, we have provided the TDBISAMTable and TDBISAMQuery OnDataLost events. The OnDataLost event is used to track when data is lost due to field conversions between incompatible types, field constraint failures, field deletions, or key violations resulting from changes in the primary index definition or unique secondary index definitions.
Image