InstantDBBuild

Unit: InstantDBBuildCategory: Database

Overview

The InstantDBBuild unit provides classes for building database schemas from business models. It generates and executes DDL (Data Definition Language) commands to create tables, fields, and indexes based on InstantObjects metadata.

Key Concepts:

• Database Builder - Creates database from scratch (drops existing)
• Command Sequence - Ordered list of DDL commands
• SQL Commands - Individual DDL operations (CREATE TABLE, ALTER TABLE, etc.)
• Event System - Progress monitoring and error handling
• Schema Generation - Automatic conversion from Model to Database

Build Process:

Business Model → Schema → Command Sequence → SQL Execution → Database
   (Classes)    (Tables)   (DDL Commands)    (CREATE/ALTER)   (Physical)

Class Hierarchy

TInstantCustomDBBuilder (abstract base)
└── TInstantCustomDBEvolver (abstract, with model support)
    └── TInstantDBBuilder (concrete, full rebuild)

TInstantDBBuildCommandSequence (command container)

TInstantDBBuildCommand (abstract base, in InstantPersistence)
└── TInstantDBBuildSQLCommand (SQL-based commands)
    ├── TInstantDBBuildAddTableSQLCommand
    ├── TInstantDBBuildDropTableSQLCommand
    ├── TInstantDBBuildAddFieldSQLCommand
    ├── TInstantDBBuildDropFieldSQLCommand
    ├── TInstantDBBuildAlterFieldSQLCommand
    │   └── TInstantDBBuildAlterFieldGenericSQLCommand
    ├── TInstantDBBuildAddIndexSQLCommand
    ├── TInstantDBBuildDropIndexSQLCommand
    └── TInstantDBBuildAlterIndexSQLCommand

TInstantCustomDBBuilder

Abstract base class for database builders.

Inheritance: TComponent → TInstantCustomDBBuilder

Key Properties

Property	Type	Description
Connector	TInstantConnector	Target database connector
CommandSequence	TInstantDBBuildCommandSequence	Generated command sequence

Events

Event	Type	Description
BeforeCommandSequenceExecute	TNotifyEvent	Before executing entire sequence
AfterCommandSequenceExecute	TNotifyEvent	After successful sequence execution
BeforeCommandExecute	TInstantDBBuildCommandNotifyEvent	Before each command
AfterCommandExecute	TInstantDBBuildCommandNotifyEvent	After each successful command
OnCommandExecuteError	TInstantDBBuildCommandErrorEvent	On command execution error
OnWarning	TInstantWarningEvent	Non-fatal warnings

Key Methods

constructor Create(AOwner: TComponent);
destructor Destroy;

// Build command sequence
procedure BuildCommandSequence;

// Abstract method (override in descendants)
procedure InternalBuildCommandSequence; virtual; abstract;

Event Types

type
  TInstantDBBuildCommandNotifyEvent = procedure(const Sender: TObject;
    const ACommand: TInstantDBBuildCommand) of object;

  TInstantDBBuildCommandErrorEvent = procedure(const Sender: TObject;
    const ACommand: TInstantDBBuildCommand; const Error: Exception;
    var RaiseError: Boolean) of object;

TInstantCustomDBEvolver

Abstract base with model support (parent of TInstantDBBuilder).

Inheritance: TInstantCustomDBBuilder → TInstantCustomDBEvolver

Additional Properties

Property	Type	Description
TargetModel	TInstantModel	Source business model

Protected Methods

// Append commands to sequence
procedure AppendAddTableCommand(const CommandSequence: TInstantDBBuildCommandSequence;
  const TableMetadata: TInstantTableMetadata); virtual;

procedure AppendDropTableCommand(const CommandSequence: TInstantDBBuildCommandSequence;
  const TableMetadata: TInstantTableMetadata); virtual;

procedure AppendAddFieldCommand(const CommandSequence: TInstantDBBuildCommandSequence;
  const FieldMetadata: TInstantFieldMetadata); virtual;

procedure AppendDropFieldCommand(const CommandSequence: TInstantDBBuildCommandSequence;
  const FieldMetadata: TInstantFieldMetadata); virtual;

procedure AppendAlterFieldCommand(const CommandSequence: TInstantDBBuildCommandSequence;
  const SourceFieldMetadata, TargetFieldMetadata: TInstantFieldMetadata); virtual;

procedure AppendAddIndexCommand(const CommandSequence: TInstantDBBuildCommandSequence;
  const IndexMetadata: TInstantIndexMetadata); virtual;

procedure AppendDropIndexCommand(const CommandSequence: TInstantDBBuildCommandSequence;
  const IndexMetadata: TInstantIndexMetadata); virtual;

procedure AppendAlterIndexCommand(const CommandSequence: TInstantDBBuildCommandSequence;
  const SourceIndexMetadata, TargetIndexMetadata: TInstantIndexMetadata); virtual;

TInstantDBBuilder

Concrete builder that creates database from scratch.

Inheritance: TInstantCustomDBEvolver → TInstantDBBuilder

Behavior

• Drops all existing tables (destructive!)
• Creates fresh database structure from model
• Loses all data in existing tables

Key Methods

// Build command sequence (drops all, recreates all)
procedure InternalBuildCommandSequence; override;

Example Usage

procedure BuildDatabase(Connector: TInstantConnector; Model: TInstantModel);
var
  Builder: TInstantDBBuilder;
begin
  Builder := TInstantDBBuilder.Create(nil);
  try
    Builder.Connector := Connector;
    Builder.TargetModel := Model;

    // Generate commands
    Builder.BuildCommandSequence;

    // Review commands
    ShowMessage(Format('Generated %d commands', [Builder.CommandSequence.Count]));

    // Execute
    Builder.CommandSequence.Execute;

    ShowMessage('Database created successfully');
  finally
    Builder.Free;
  end;
end;

With Event Monitoring

procedure BuildDatabaseWithMonitoring;
var
  Builder: TInstantDBBuilder;
begin
  Builder := TInstantDBBuilder.Create(nil);
  try
    Builder.Connector := FireDACConnector;
    Builder.TargetModel := InstantModel;

    // Hook events
    Builder.BeforeCommandSequenceExecute := BuilderBeforeSequence;
    Builder.BeforeCommandExecute := BuilderBeforeCommand;
    Builder.AfterCommandExecute := BuilderAfterCommand;
    Builder.AfterCommandSequenceExecute := BuilderAfterSequence;
    Builder.OnCommandExecuteError := BuilderError;

    // Build and execute
    Builder.BuildCommandSequence;
    Builder.CommandSequence.Execute;
  finally
    Builder.Free;
  end;
end;

procedure TMainForm.BuilderBeforeSequence(Sender: TObject);
begin
  ProgressBar.Max := TInstantDBBuilder(Sender).CommandSequence.Count;
  ProgressBar.Position := 0;
end;

procedure TMainForm.BuilderBeforeCommand(const Sender: TObject;
  const ACommand: TInstantDBBuildCommand);
begin
  StatusLabel.Caption := ACommand.Description;
  Application.ProcessMessages;
end;

procedure TMainForm.BuilderAfterCommand(const Sender: TObject;
  const ACommand: TInstantDBBuildCommand);
begin
  ProgressBar.Position := ProgressBar.Position + 1;
  LogMemo.Lines.Add('✓ ' + ACommand.Description);
end;

procedure TMainForm.BuilderAfterSequence(Sender: TObject);
begin
  StatusLabel.Caption := 'Database built successfully';
  ShowMessage('Database creation complete');
end;

procedure TMainForm.BuilderError(const Sender: TObject;
  const ACommand: TInstantDBBuildCommand; const Error: Exception;
  var RaiseError: Boolean);
begin
  LogMemo.Lines.Add('✗ ERROR: ' + ACommand.Description);
  LogMemo.Lines.Add('  ' + Error.Message);

  if MessageDlg('Error executing command. Continue?',
    mtError, [mbYes, mbNo], 0) = mrYes then
    RaiseError := False  // Continue
  else
    RaiseError := True;  // Stop execution
end;

TInstantDBBuildCommandSequence

Container for database build commands.

Inheritance: TComponent → TInstantDBBuildCommandSequence

Key Properties

Property	Type	Description
Connector	TInstantConnector	Execution connector
Count	Integer	Number of commands
Items[Index]	TInstantDBBuildCommand	Command by index
SourceScheme	TInstantScheme	Source database schema
TargetScheme	TInstantScheme	Target database schema

Events

Same as TInstantCustomDBBuilder (BeforeExecute, AfterExecute, etc.)

Key Methods

constructor Create(AOwner: TComponent);
destructor Destroy;

// Sequence management
procedure Clear;
procedure Append(const ACommand: TInstantDBBuildCommand);
procedure MoveItem(const AItem: TInstantDBBuildCommand; const Extent: Integer);

// Execution
procedure Execute;

Example: Manual Sequence Building

var
  Sequence: TInstantDBBuildCommandSequence;
  Cmd: TInstantDBBuildSQLCommand;
  Table: TInstantTableMetadata;
begin
  Sequence := TInstantDBBuildCommandSequence.Create(nil);
  try
    Sequence.Connector := MyConnector;

    // Create Contact table
    Table := TInstantTableMetadata.Create(nil);
    Table.Name := 'CONTACTS';
    Table.FieldMetadatas.AddFieldMetadata('Class', dtString, 32, [foRequired, foPrimaryKey]);
    Table.FieldMetadatas.AddFieldMetadata('Id', dtString, 32, [foRequired, foPrimaryKey]);
    Table.FieldMetadatas.AddFieldMetadata('Name', dtString, 100, [foRequired]);
    Table.FieldMetadatas.AddFieldMetadata('Email', dtString, 100, []);

    Cmd := TInstantDBBuildAddTableSQLCommand.Create(ctAddTable, MyConnector);
    Cmd.NewMetadata := Table;
    Sequence.Append(Cmd);

    // Execute
    Sequence.Execute;
  finally
    Sequence.Free;
  end;
end;

Example: Reviewing Before Execution

procedure ReviewAndExecute(Builder: TInstantDBBuilder);
var
  I: Integer;
  Sequence: TInstantDBBuildCommandSequence;
  Memo: TMemo;
begin
  // Build sequence
  Builder.BuildCommandSequence;

  Sequence := Builder.CommandSequence;

  // Display commands
  Memo := TMemo.Create(nil);
  try
    for I := 0 to Sequence.Count - 1 do
    begin
      Memo.Lines.Add(Format('[%d] %s',
        [I + 1, Sequence[I].Description]));
      if Sequence[I] is TInstantDBBuildSQLCommand then
        Memo.Lines.Add('    SQL: ' +
          TInstantDBBuildSQLCommand(Sequence[I]).GetSQLStatement(0));
    end;

    // Show to user
    if MessageDlg('Execute ' + IntToStr(Sequence.Count) + ' commands?',
      mtConfirmation, [mbYes, mbNo], 0) = mrYes then
      Sequence.Execute;
  finally
    Memo.Free;
  end;
end;

TInstantDBBuildCommand

Abstract base for all build commands.

Location: Defined in InstantPersistence.pasInheritance: TObject → TInstantDBBuildCommand

Key Properties

Property	Type	Description
CommandType	TInstantDBBuildCommandType	Type of command
Connector	TInstantConnector	Database connector
Enabled	Boolean	Enable/disable execution
OldMetadata	TInstantMetadata	Source metadata (for alter/drop)
NewMetadata	TInstantMetadata	Target metadata (for add/alter)
Description	string	Human-readable description

TInstantDBBuildCommandType

type
  TInstantDBBuildCommandType = (
    ctAddTable,      // CREATE TABLE
    ctDropTable,     // DROP TABLE
    ctAddField,      // ALTER TABLE ADD COLUMN
    ctAlterField,    // ALTER TABLE ALTER COLUMN
    ctDropField,     // ALTER TABLE DROP COLUMN
    ctAddIndex,      // CREATE INDEX
    ctAlterIndex,    // DROP INDEX + CREATE INDEX
    ctDropIndex      // DROP INDEX
  );

Key Methods

constructor Create(const ACommandType: TInstantDBBuildCommandType;
  const AConnector: TInstantConnector = nil);

// Execute command
procedure Execute;

// Override in descendants
procedure InternalExecute; virtual; abstract;
function GetDescription: string; virtual;
function InternalExecuteHandleError(const E: Exception): Boolean; virtual;

SQL Command Classes

TInstantDBBuildSQLCommand

Base class for SQL-based commands.

Inheritance: TInstantDBBuildCommand → TInstantDBBuildSQLCommand

Key Properties

Property	Type	Description
Connector	TInstantRelationalConnector	SQL connector
Broker	TInstantSQLBroker	SQL broker

Key Methods

// Override to provide SQL
function GetSQLStatement(const Index: Integer): string; virtual;
function GetSQLStatementCount: Integer; virtual;  // Default: 1

// Execute SQL statements
procedure InternalExecute; override;
procedure ExecuteSQLStatement(const Index: Integer);

TInstantDBBuildAddTableSQLCommand

Creates a table.

Properties

Property	Type	Description
TableMetadata	TInstantTableMetadata	Table to create (NewMetadata)

Generated SQL Example

CREATE TABLE CONTACTS (
  Class VARCHAR(32) NOT NULL,
  Id VARCHAR(32) NOT NULL,
  UpdateCount INTEGER NOT NULL,
  Name VARCHAR(100) NOT NULL,
  Email VARCHAR(100),
  PRIMARY KEY (Class, Id)
)

TInstantDBBuildDropTableSQLCommand

Drops a table.

Properties

Property	Type	Description
TableMetadata	TInstantTableMetadata	Table to drop (OldMetadata)

Generated SQL Example

DROP TABLE CONTACTS

TInstantDBBuildAddFieldSQLCommand

Adds a field to existing table.

Properties

Property	Type	Description
FieldMetadata	TInstantFieldMetadata	Field to add (NewMetadata)

Generated SQL Example

ALTER TABLE CONTACTS ADD COLUMN Phone VARCHAR(20)

TInstantDBBuildDropFieldSQLCommand

Drops a field from table.

Properties

Property	Type	Description
FieldMetadata	TInstantFieldMetadata	Field to drop (OldMetadata)

Generated SQL Example

ALTER TABLE CONTACTS DROP COLUMN OldField

TInstantDBBuildAlterFieldSQLCommand

Changes field definition.

Properties

Property	Type	Description
OldFieldMetadata	TInstantFieldMetadata	Original field
NewFieldMetadata	TInstantFieldMetadata	New field definition

Generated SQL Example

ALTER TABLE CONTACTS ALTER COLUMN Name VARCHAR(200)

TInstantDBBuildAlterFieldGenericSQLCommand

Changes field using 6-step process (for databases without ALTER COLUMN support).

Inheritance: TInstantDBBuildAlterFieldSQLCommand → TInstantDBBuildAlterFieldGenericSQLCommand

Process

1. Add temporary field with new type
2. Copy data from old field to temp field
3. Drop old field
4. Add field with old name and new type
5. Copy data back from temp field
6. Drop temp field

Generated SQL Example

-- 1. Add temp field
ALTER TABLE CONTACTS ADD COLUMN Name_Temp VARCHAR(200)

-- 2. Copy data
UPDATE CONTACTS SET Name_Temp = Name

-- 3. Drop old field
ALTER TABLE CONTACTS DROP COLUMN Name

-- 4. Add new field
ALTER TABLE CONTACTS ADD COLUMN Name VARCHAR(200)

-- 5. Copy back
UPDATE CONTACTS SET Name = Name_Temp

-- 6. Drop temp
ALTER TABLE CONTACTS DROP COLUMN Name_Temp

TInstantDBBuildAddIndexSQLCommand

Creates an index.

Properties

Property	Type	Description
IndexMetadata	TInstantIndexMetadata	Index to create (NewMetadata)

Generated SQL Example

CREATE INDEX IX_CONTACTS_EMAIL ON CONTACTS (Email)

-- Or unique index:
CREATE UNIQUE INDEX UQ_CONTACTS_EMAIL ON CONTACTS (Email)

TInstantDBBuildDropIndexSQLCommand

Drops an index.

Properties

Property	Type	Description
IndexMetadata	TInstantIndexMetadata	Index to drop (OldMetadata)

Generated SQL Example

DROP INDEX IX_CONTACTS_EMAIL

TInstantDBBuildAlterIndexSQLCommand

Changes index (drops and recreates).

Properties

Property	Type	Description
OldIndexMetadata	TInstantIndexMetadata	Original index
NewIndexMetadata	TInstantIndexMetadata	New index definition

Statement Count

Returns 2 (DROP + CREATE)

Generated SQL Example

-- Statement 0: DROP
DROP INDEX IX_CONTACTS_NAME

-- Statement 1: CREATE
CREATE INDEX IX_CONTACTS_NAME ON CONTACTS (Name, City)

Common Usage Patterns

Simple Database Build

procedure QuickBuild(Connector: TInstantConnector);
var
  Builder: TInstantDBBuilder;
begin
  Builder := TInstantDBBuilder.Create(nil);
  try
    Builder.Connector := Connector;
    Builder.BuildCommandSequence;
    Builder.CommandSequence.Execute;
    ShowMessage('Database built');
  finally
    Builder.Free;
  end;
end;

Build with Progress Bar

procedure BuildWithProgress(Connector: TInstantConnector;
  ProgressBar: TProgressBar; StatusLabel: TLabel);
var
  Builder: TInstantDBBuilder;
  CurrentStep: Integer;
begin
  Builder := TInstantDBBuilder.Create(nil);
  try
    Builder.Connector := Connector;

    Builder.BeforeCommandSequenceExecute := procedure(Sender: TObject)
      begin
        CurrentStep := 0;
        ProgressBar.Max := TInstantDBBuilder(Sender).CommandSequence.Count;
        ProgressBar.Position := 0;
      end;

    Builder.BeforeCommandExecute := procedure(const Sender: TObject;
      const ACommand: TInstantDBBuildCommand)
      begin
        Inc(CurrentStep);
        StatusLabel.Caption := Format('Step %d/%d: %s',
          [CurrentStep, ProgressBar.Max, ACommand.Description]);
        Application.ProcessMessages;
      end;

    Builder.AfterCommandExecute := procedure(const Sender: TObject;
      const ACommand: TInstantDBBuildCommand)
      begin
        ProgressBar.Position := CurrentStep;
      end;

    Builder.BuildCommandSequence;
    Builder.CommandSequence.Execute;
  finally
    Builder.Free;
  end;
end;

Build with Error Handling

procedure BuildWithErrorHandling(Connector: TInstantConnector);
var
  Builder: TInstantDBBuilder;
  Errors: TStringList;
begin
  Errors := TStringList.Create;
  try
    Builder := TInstantDBBuilder.Create(nil);
    try
      Builder.Connector := Connector;

      Builder.OnCommandExecuteError := procedure(const Sender: TObject;
        const ACommand: TInstantDBBuildCommand; const Error: Exception;
        var RaiseError: Boolean)
        begin
          // Log error
          Errors.Add(Format('[%s] %s',
            [ACommand.Description, Error.Message]));

          // Continue with next command
          RaiseError := False;
        end;

      Builder.BuildCommandSequence;
      Builder.CommandSequence.Execute;

      if Errors.Count > 0 then
      begin
        ShowMessage(Format('Build completed with %d errors', [Errors.Count]));
        // Display error log
        ShowMessage(Errors.Text);
      end
      else
        ShowMessage('Build successful');
    finally
      Builder.Free;
    end;
  finally
    Errors.Free;
  end;
end;

Conditional Command Execution

procedure BuildWithSelectiveExecution(Connector: TInstantConnector);
var
  Builder: TInstantDBBuilder;
begin
  Builder := TInstantDBBuilder.Create(nil);
  try
    Builder.Connector := Connector;

    Builder.BeforeCommandExecute := procedure(const Sender: TObject;
      const ACommand: TInstantDBBuildCommand)
      begin
        // Skip DROP TABLE commands (preserve data in existing tables)
        if ACommand.CommandType = ctDropTable then
          ACommand.Enabled := False;

        // Confirm ALTER FIELD commands
        if ACommand.CommandType = ctAlterField then
        begin
          if MessageDlg('Execute: ' + ACommand.Description + '?',
            mtConfirmation, [mbYes, mbNo], 0) = mrNo then
            ACommand.Enabled := False;
        end;
      end;

    Builder.BuildCommandSequence;
    Builder.CommandSequence.Execute;
  finally
    Builder.Free;
  end;
end;

Generate SQL Script Without Execution

procedure GenerateSQLScript(Connector: TInstantConnector;
  const OutputFile: string);
var
  Builder: TInstantDBBuilder;
  Script: TStringList;
  I, J: Integer;
  Cmd: TInstantDBBuildSQLCommand;
begin
  Builder := TInstantDBBuilder.Create(nil);
  try
    Builder.Connector := Connector;
    Builder.BuildCommandSequence;

    Script := TStringList.Create;
    try
      Script.Add('-- InstantObjects Database Build Script');
      Script.Add('-- Generated: ' + DateTimeToStr(Now));
      Script.Add('');

      for I := 0 to Builder.CommandSequence.Count - 1 do
      begin
        if Builder.CommandSequence[I] is TInstantDBBuildSQLCommand then
        begin
          Cmd := TInstantDBBuildSQLCommand(Builder.CommandSequence[I]);
          Script.Add('-- ' + Cmd.Description);

          for J := 0 to Cmd.GetSQLStatementCount - 1 do
          begin
            Script.Add(Cmd.GetSQLStatement(J) + ';');
          end;

          Script.Add('');
        end;
      end;

      Script.SaveToFile(OutputFile);
      ShowMessage('Script saved to: ' + OutputFile);
    finally
      Script.Free;
    end;
  finally
    Builder.Free;
  end;
end;

Best Practices

1. Backup Before Build

   • Always backup database before running TInstantDBBuilder
   • Builder drops all tables (destructive!)
   • Use TInstantDBEvolver for safe schema updates

2. Review Before Execution

   • Call BuildCommandSequence first
   • Review CommandSequence.Count and command descriptions
   • Generate SQL script for review
   • Test on development database first

3. Error Handling

   • Always use OnCommandExecuteError event
   • Log errors for troubleshooting
   • Decide per-error: continue or abort
   • Consider database transaction support

4. Progress Feedback

   • Use BeforeCommandExecute for UI updates
   • Update progress bar in AfterCommandExecute
   • Allow user cancellation if possible
   • Call Application.ProcessMessages for responsiveness

5. Command Customization

   • Use BeforeCommandExecute to disable specific commands
   • Modify Enabled property to skip commands
   • Reorder commands with MoveItem if needed

6. Database-Specific Considerations

   • Some databases don't support transactional DDL
   • ALTER COLUMN may not be supported (use GenericSQLCommand)
   • Index syntax varies by database
   • Test with target database

7. Development vs Production

   • Use TInstantDBBuilder for development only
   • Use TInstantDBEvolver for production updates
   • Generate scripts for manual review in production
   • Never run automated build on production!

Limitations

Unsupported Operations

❌ Data Preservation - TInstantDBBuilder loses all data ❌ Incremental Updates - Use TInstantDBEvolver instead ❌ Custom DDL - Only standard CREATE/ALTER/DROP supported ❌ Stored Procedures - Not managed by builder ❌ Triggers - Not managed by builder ❌ Views - Not managed by builder ❌ Permissions - Not managed by builder

Database Support

Different databases have different DDL capabilities:

• Some don't support ALTER COLUMN (use TInstantDBBuildAlterFieldGenericSQLCommand)
• Some don't support transactional DDL (can't rollback on error)
• Index syntax varies (broker handles this)
• Primary key constraints may require special handling

Troubleshooting

Problem: "Table already exists"

Cause: Database not empty, tables from previous build Solution: TInstantDBBuilder should drop tables first; check DropTable commands are enabled

Problem: "Cannot drop table, foreign key constraint"

Cause: Foreign key references prevent dropping Solution: Drop tables in correct order, or disable foreign key checks temporarily

Problem: "ALTER COLUMN not supported"

Cause: Database doesn't support ALTER COLUMN Solution: Broker should use TInstantDBBuildAlterFieldGenericSQLCommand automatically

Problem: Build fails partway, database in inconsistent state

Cause: Error in command execution, no transaction rollback Solution:

• Check database supports transactional DDL
• Use error handling to continue or abort
• Restore from backup

Problem: Generated SQL doesn't match expectations

Cause: Broker-specific SQL generation Solution: Review generated script, customize broker's SQL generator if needed

See Also

• InstantDBEvolution - Safe schema evolution (recommended)
• InstantMetadata - Metadata system
• InstantBrokers - SQL generation
• Building/Evolving the Business Model - User guide
• Primer Demo - Working examples

Version History

• Version 3.5 - Initial database builder
• Version 4.0 - Enhanced error handling and events
• Version 4.2 - 64-bit support
• Version 4.3 - Improved SQL generation