SQLMemTable: Embedded BDE Alternative
In-Memory SQL Database for Delphi / C++Builder
Introduction
Restructuring tables is executed by means of the RestructureTable method of the TSQLMemTable component. The properties used by the RestructureTable method include the RestructureFieldDefs and RestructureIndexDefs properties.
Specifying the New Fields Structure
The RestructureFieldDefs property is used to specify which fields to define for the restructured table. The RestructureFieldDefs property is an array of TFieldDef objects, each of which contains information about the field to create. When table is open RestructureFieldDefs property contains field definitions of all existing fields. So you should not define all fields. You may add, modify or delete some fields definitions only. You may add new TFieldDef objects using the Add method of the TFieldDefs object stored in the FieldDefs property. The Add method accepts the following parameters for the field being defined:
Field Name (String)
|
Field Name parameter indicates the name to be given to the field.
|
Data Type (TFieldType)
|
DataType parameter indicates the data type of the field Available TFieldType data types:
ftInteger, ftSmallInt, ftFloat, ftDateTime, ftBLOB, ftString (any fixed length string)
|
Size (Word)
|
Size parameter indicates the size of the field. This should be specified for the String type only. For all other data types this parameter should be set as 0. For the String type this parameter indicates the length of the field.
|
Required (Boolean)
|
Required parameter indicates whether or not the new field should be required (not Null) while adding or modifying records.
|
Specifying the Indexes in a Restructured Table
The RestructureIndexDefs property is used to specify which indexes to be defined for the restructured table. The RestructureIndexDefs property is an array of TIndexDef objects, each of them containing information about the index to create. When table is open RestructureIndexDefs property contains index definitions for current table. So you should not define all indexes. You may add, modify or delete some index definitions only. You may add new TIndexDef objects using the Add method of the TIndexDefs object contained in the IndexDefs property. The Add method accepts the following parameters for the index being defined:
Index Name (String)
|
Index Name parameter contains the name to be given to the index.
|
Fields List (String)
|
Fields List parameter contains the list of fields to be included into the index. Multiple field names specified in this parameter should be separated with a semicolon (;).
|
Index Options (TIndexOptions)
|
Index Options parameter provides information about the type of index being created (please see the component reference supplied with SQLMemTable for more information on the available TIndexOption options).
|
Restructuring the Table
The final step in restructuring a table is to call the RestructureTable method. The following example shows how to restructure the CUSTOMER.DAT table included with the Delphi demo data using the RestructureTable method:
(Structure of this table is described in example from Creating a table topic)
MySQLMemTable.Active := true;
with MySQLMemTable do
begin
modify fields structure
with RestructureFieldDefs do
begin
// add new field
Add('Customer Name',ftString,300,False);
// rename 'CustNo' field
Find('CustNo').Name := 'Customer ID';
// set new length for Company field
Find('Company').Size := 100;
// delete 'Addr2' field
Delete(Find('Addr2').Index);
end;
modify index definitions
with RestructureIndexDefs do
begin
// add new index for Customer Name field
Add('CustomerName_Index','Customer Name',[ixCaseInsensitive]);
// update primary index
Find('PrimaryKey').Fields := 'Customer ID';
end;
// change only fields structure, don't modify other parameters such as encryption
RestructureTable;
end;
|