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.


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:


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.


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.

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) or dbisamcn header file (C++).

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) or dbisamcn header file (C++). 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) or dbisamcn header file (C++).

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.


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:




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.