InstantDBEvolution

Database schema evolution system for safe incremental updates.

Overview

The InstantDBEvolution unit provides TInstantDBEvolver, which implements safe incremental database schema evolution. Unlike TInstantDBBuilder which drops and rebuilds the entire database, the evolver compares the current database schema with the target model and generates only the necessary changes to bring the database up to date, preserving existing data.

This is the recommended approach for production environments where data preservation is critical.

Key Classes

TInstantDBEvolver

Builds a command sequence that upgrades a database schema to match the current (or specified) business object model by comparing source and target schemas and generating incremental changes.

Inheritance:

TComponent → TInstantCustomDBBuilder → TInstantCustomDBEvolver → TInstantDBEvolver

Key Features:

• Data Preservation: Modifies schema without dropping tables
• Incremental Updates: Generates only necessary changes
• Schema Comparison: Automatically detects differences
• Safe for Production: Designed for live systems with data
• Broker Independence: Works with any InstantObjects broker

Core Algorithm

Schema Comparison Process

The evolver implements a comprehensive diff algorithm:

1. Table Evolution

For each table in the target schema (desired state):

• If table exists in database:
  • Compare and update fields
  • Compare and update indexes
• If table doesn't exist:
  • Generate ADD TABLE command

For each table in the source schema (current database):

• If table not in target schema:
  • Generate DROP TABLE command

2. Field Evolution (for existing tables)

For each field in target table:

• If field exists in database:
  • Compare metadata (type, size, required, etc.)
  • If different: Generate ALTER FIELD command
• If field doesn't exist:
  • Generate ADD FIELD command

For each field in source table:

• If field not in target table:
  • Generate DROP FIELD command

3. Index Evolution (for existing tables)

For each index in target table (excluding primary keys):

• If index exists in database:
  • Compare metadata (fields, options)
  • If different: Generate ALTER INDEX command
• If index doesn't exist:
  • Generate ADD INDEX command

For each index in source table (excluding primary keys):

• If index not in target table:
  • Generate DROP INDEX command

Execution Order

Commands are generated in this specific order to maintain referential integrity:

1. Add/Alter Fields - Modify table structure first
2. Add/Alter Indexes - Create/update indexes
3. Drop Indexes - Remove obsolete indexes before fields
4. Drop Fields - Remove obsolete fields
5. Add Tables - Create new tables
6. Drop Tables - Remove obsolete tables

Properties

Inherited from TInstantCustomDBEvolver and TInstantCustomDBBuilder:

Property	Type	Description
Connector	TInstantConnector	Database connector to evolve
TargetModel	TInstantModel	Target business object model (desired state)
CommandSequence	TInstantDBBuildCommandSequence	Generated evolution commands
SourceSchemeWarningHandler	TInstantWarningHandler	Handler for warnings when reading current schema

Methods

BuildCommandSequence

procedure BuildCommandSequence;

Generates the evolution command sequence by:

1. Reading current database schema (via Connector.Broker.ReadDatabaseScheme)
2. Creating target schema from model (via Connector.CreateScheme(TargetModel))
3. Comparing schemas and generating diff commands

Example:

Evolver.Connector := InstantConnector1;
Evolver.TargetModel := InstantModel1.CurrentModel;
Evolver.BuildCommandSequence; // Generates commands

InternalBuildCommandSequence (Protected)

procedure InternalBuildCommandSequence; override;

Internal implementation that:

1. Clears command sequence
2. Reads source scheme from database
3. Creates target scheme from model
4. Calls GenerateSchemeDiff to populate command sequence

GenerateSchemeDiff (Private)

procedure GenerateSchemeDiff(const CommandSequence: TInstantDBBuildCommandSequence);

Core diff algorithm that compares source and target schemas and generates appropriate commands using:

• AppendAddTableCommand
• AppendDropTableCommand
• AppendAddFieldCommand
• AppendDropFieldCommand
• AppendAlterFieldCommand
• AppendAddIndexCommand
• AppendDropIndexCommand
• AppendAlterIndexCommand

Feature Detection

The evolver checks catalog features before generating commands:

ReadTableInfoSupported

function ReadTableInfoSupported: Boolean;

Returns True if both source and target catalogs support cfReadTableInfo feature.

ReadColumnInfoSupported

function ReadColumnInfoSupported: Boolean;

Returns True if both catalogs support cfReadColumnInfo feature.

ReadIndexInfoSupported

function ReadIndexInfoSupported: Boolean;

Returns True if both catalogs support cfReadIndexInfo feature.

Usage Patterns

Basic Schema Evolution

Evolve database to match current model:

procedure EvolveDatabase(Connector: TInstantConnector; Model: TInstantModel);
var
  Evolver: TInstantDBEvolver;
begin
  Evolver := TInstantDBEvolver.Create(nil);
  try
    Evolver.Connector := Connector;
    Evolver.TargetModel := Model;

    // Generate evolution commands
    Evolver.BuildCommandSequence;

    // Execute evolution
    if Evolver.CommandSequence.Count > 0 then
    begin
      Evolver.CommandSequence.Execute;
      ShowMessage('Database evolved successfully');
    end
    else
      ShowMessage('Database schema is already up to date');
  finally
    Evolver.Free;
  end;
end;

Evolution with Preview

Preview changes before applying:

procedure EvolveWithPreview(Connector: TInstantConnector; Model: TInstantModel);
var
  Evolver: TInstantDBEvolver;
  Script: TStringList;
begin
  Evolver := TInstantDBEvolver.Create(nil);
  Script := TStringList.Create;
  try
    Evolver.Connector := Connector;
    Evolver.TargetModel := Model;
    Evolver.BuildCommandSequence;

    if Evolver.CommandSequence.Count > 0 then
    begin
      // Generate SQL script
      Evolver.CommandSequence.GenerateScript(Script);

      // Show preview
      if MessageDlg('Evolution Script:' + sLineBreak + sLineBreak +
                    Script.Text + sLineBreak +
                    'Execute these changes?',
                    mtConfirmation, [mbYes, mbNo], 0) = mrYes then
      begin
        Evolver.CommandSequence.Execute;
        ShowMessage('Database evolved successfully');
      end;
    end
    else
      ShowMessage('No changes needed');
  finally
    Script.Free;
    Evolver.Free;
  end;
end;

Evolution with Progress Monitoring

Track evolution progress with event handlers:

type
  TDatabaseEvolutionForm = class(TForm)
    ProgressBar: TProgressBar;
    StatusLabel: TLabel;
  private
    procedure CommandExecuted(Sender: TObject; Command: TInstantDBBuildCommand);
  public
    procedure EvolveDatabase(Connector: TInstantConnector; Model: TInstantModel);
  end;

procedure TDatabaseEvolutionForm.CommandExecuted(Sender: TObject;
  Command: TInstantDBBuildCommand);
var
  Sequence: TInstantDBBuildCommandSequence;
begin
  Sequence := Sender as TInstantDBBuildCommandSequence;
  ProgressBar.Position := Sequence.ExecutedCount;
  StatusLabel.Caption := Format('Executed %d of %d commands',
    [Sequence.ExecutedCount, Sequence.Count]);
  Application.ProcessMessages;
end;

procedure TDatabaseEvolutionForm.EvolveDatabase(Connector: TInstantConnector;
  Model: TInstantModel);
var
  Evolver: TInstantDBEvolver;
begin
  Evolver := TInstantDBEvolver.Create(nil);
  try
    Evolver.Connector := Connector;
    Evolver.TargetModel := Model;
    Evolver.BuildCommandSequence;

    if Evolver.CommandSequence.Count > 0 then
    begin
      ProgressBar.Max := Evolver.CommandSequence.Count;
      ProgressBar.Position := 0;

      // Attach event handler
      Evolver.CommandSequence.OnCommandExecuted := CommandExecuted;

      try
        Evolver.CommandSequence.Execute;
        ShowMessage('Database evolved successfully');
      except
        on E: Exception do
        begin
          ShowMessage('Evolution failed: ' + E.Message + sLineBreak +
                     Format('Executed %d of %d commands',
                       [Evolver.CommandSequence.ExecutedCount,
                        Evolver.CommandSequence.Count]));
          raise;
        end;
      end;
    end;
  finally
    Evolver.Free;
  end;
end;

Evolution with Error Handling

Robust error handling with rollback support:

procedure SafeEvolveDatabase(Connector: TInstantConnector; Model: TInstantModel);
var
  Evolver: TInstantDBEvolver;
  Script: TStringList;
begin
  // Verify connection
  if not Connector.Connected then
    raise Exception.Create('Connector must be connected');

  Evolver := TInstantDBEvolver.Create(nil);
  Script := TStringList.Create;
  try
    Evolver.Connector := Connector;
    Evolver.TargetModel := Model;

    // Build command sequence
    Evolver.BuildCommandSequence;

    if Evolver.CommandSequence.Count = 0 then
    begin
      ShowMessage('Database schema is already up to date');
      Exit;
    end;

    // Save script for audit trail
    Evolver.CommandSequence.GenerateScript(Script);
    Script.SaveToFile('evolution_' + FormatDateTime('yyyymmdd_hhnnss', Now) + '.sql');

    // Start transaction
    Connector.StartTransaction;
    try
      // Execute evolution
      Evolver.CommandSequence.Execute;

      // Commit if successful
      Connector.CommitTransaction;
      ShowMessage('Database evolved successfully');
    except
      on E: Exception do
      begin
        // Rollback on error
        Connector.RollbackTransaction;

        ShowMessage(Format('Evolution failed at command %d of %d: %s',
          [Evolver.CommandSequence.ExecutedCount + 1,
           Evolver.CommandSequence.Count,
           E.Message]));
        raise;
      end;
    end;
  finally
    Script.Free;
    Evolver.Free;
  end;
end;

Model-Based Evolution

Evolve database when model changes:

procedure EvolveFromModelChange(Connector: TInstantConnector;
  ModelFileName: string);
var
  Evolver: TInstantDBEvolver;
  Model: TInstantModel;
begin
  Model := TInstantModel.Create(nil);
  try
    // Load new model
    Model.LoadFromFile(ModelFileName);

    Evolver := TInstantDBEvolver.Create(nil);
    try
      Evolver.Connector := Connector;
      Evolver.TargetModel := Model;
      Evolver.BuildCommandSequence;

      if Evolver.CommandSequence.Count > 0 then
      begin
        WriteLn(Format('Evolution requires %d commands:',
          [Evolver.CommandSequence.Count]));
        WriteLn('- Add Tables: ', Evolver.CommandSequence.AddTableCommandCount);
        WriteLn('- Drop Tables: ', Evolver.CommandSequence.DropTableCommandCount);
        WriteLn('- Add Fields: ', Evolver.CommandSequence.AddFieldCommandCount);
        WriteLn('- Drop Fields: ', Evolver.CommandSequence.DropFieldCommandCount);
        WriteLn('- Alter Fields: ', Evolver.CommandSequence.AlterFieldCommandCount);
        WriteLn('- Add Indexes: ', Evolver.CommandSequence.AddIndexCommandCount);
        WriteLn('- Drop Indexes: ', Evolver.CommandSequence.DropIndexCommandCount);

        Write('Proceed? (Y/N): ');
        if UpCase(ReadKey) = 'Y' then
        begin
          Evolver.CommandSequence.Execute;
          WriteLn('Evolution completed successfully');
        end;
      end;
    finally
      Evolver.Free;
    end;
  finally
    Model.Free;
  end;
end;

Evolution with Backup Verification

procedure EvolveWithBackupCheck(Connector: TInstantConnector;
  Model: TInstantModel; const BackupPath: string);
begin
  // Verify backup exists and is recent
  if not FileExists(BackupPath) then
    raise Exception.Create('Backup file not found: ' + BackupPath);

  if Now - FileDateToDateTime(FileAge(BackupPath)) > 1.0 then  // 1 day
    if MessageDlg('Backup is more than 1 day old. Continue?',
                  mtWarning, [mbYes, mbNo], 0) <> mrYes then
      Exit;

  // Proceed with evolution
  EvolveDatabase(Connector, Model);
end;

Metadata Comparison

Field Metadata Comparison

The evolver uses TInstantFieldMetadata.Equals() to detect changes:

// Compared properties:
- Name: Field name
- DataType: Data type (dtString, dtInteger, etc.)
- Size: Field size
- Required: Whether field is NOT NULL
- AlternateDataTypes: Alternative data types for compatibility

Field changes that trigger ALTER FIELD:

• Data type change (e.g., String → Memo)
• Size change (e.g., VARCHAR(50) → VARCHAR(100))
• Required constraint change (NULL → NOT NULL or vice versa)

Index Metadata Comparison

The evolver uses TInstantIndexMetadata.Equals() to detect changes:

// Compared properties:
- Name: Index name
- Fields: Indexed field names (semicolon-separated)
- Options: Index options (ixUnique, ixDescending, etc.)

Index changes that trigger ALTER INDEX:

• Field list change
• Uniqueness change
• Sort order change

Note: Primary key indexes (ixPrimary) are skipped during evolution.

Catalog Features

Required catalog features for schema evolution:

Feature	Description	Required For
cfReadTableInfo	Read table metadata	Table comparison
cfReadColumnInfo	Read column metadata	Field comparison
cfReadIndexInfo	Read index metadata	Index comparison

Check feature support:

if cfReadTableInfo in Connector.Broker.Catalog.Features then
  // Table evolution supported

Limitations and Considerations

Case Sensitivity

The current implementation uses AnsiUpperCase() for object name comparison, which only works for case-insensitive object names:

{ TODO : This only works for case-insensitive object names! }
SourceTableMetadata := CommandSequence.SourceScheme.FindTableMetadata(
  AnsiUpperCase(TargetTableMetadata.Name));

Workaround for case-sensitive databases:

• Use consistent casing in all metadata
• Verify catalog implementation handles case correctly

Primary Key Modifications

Primary key indexes are not modified during evolution:

if not (ixPrimary in TargetIndexMetadata.Options) then
  // Only process non-primary indexes

Reason: Modifying primary keys requires special handling due to referential integrity constraints.

Foreign Key Constraints

The current algorithm does not explicitly handle foreign key dependencies.

Planned enhancement (from source comments):

// When we have foreign keys, we'll have to sort the generated list
// according to references, or we could use a strategy of
// disabling/dropping all the constraints at the beginning and
// recreate them later.

Current workaround:

• Drop and recreate foreign keys manually if needed
• Use database-specific constraint management

Data Type Compatibility

Some data type changes may not be supported by all databases:

// Safe conversions:
VARCHAR(50) → VARCHAR(100)  // Increasing size
INTEGER → BIGINT           // Widening type

// Potentially unsafe conversions:
VARCHAR(100) → VARCHAR(50)  // Decreasing size (may truncate data)
VARCHAR → INTEGER          // Type change (may fail if data not numeric)
NOT NULL → NULL            // Safe
NULL → NOT NULL            // May fail if existing NULLs present

Best practice: Test evolution on a copy of the database first.

Comparison: Evolver vs Builder

Aspect	TInstantDBEvolver	TInstantDBBuilder
Data Preservation	Preserves all data	Destroys all data
Operation	Incremental changes	Drop and rebuild
Speed	Slower (comparison + execution)	Faster (no comparison)
Use Case	Production databases	Development, initial deployment
Risk	Low (data preserved)	High (data loss)
Schema Reading	Required	Not required
Complexity	Higher (diff algorithm)	Lower (straightforward rebuild)
Transactions	Recommended	Recommended
Backup Required	Yes (best practice)	Mandatory

Best Practices

1. Always Backup Before Evolution

// Backup database before evolution
BackupDatabase(Connector, 'backup_' + FormatDateTime('yyyymmdd_hhnnss', Now) + '.bak');

// Then evolve
EvolveDatabase(Connector, Model);

2. Preview Changes Before Execution

Evolver.BuildCommandSequence;
Evolver.CommandSequence.GenerateScript(PreviewScript);
// Review PreviewScript before executing
if UserApproves(PreviewScript.Text) then
  Evolver.CommandSequence.Execute;

3. Use Transactions for Atomicity

Connector.StartTransaction;
try
  Evolver.CommandSequence.Execute;
  Connector.CommitTransaction;
except
  Connector.RollbackTransaction;
  raise;
end;

4. Test on Development Database First

// 1. Test on dev database
EvolveDatabase(DevConnector, Model);
VerifyDataIntegrity(DevConnector);

// 2. If successful, apply to production
EvolveDatabase(ProdConnector, Model);

5. Save Evolution Scripts for Audit Trail

Evolver.BuildCommandSequence;
Evolver.CommandSequence.GenerateScript(Script);
Script.SaveToFile('evolution_' + DateTimeToStr(Now) + '.sql');
Evolver.CommandSequence.Execute;

6. Monitor Evolution Progress

// Attach event handlers for long-running evolutions
Evolver.CommandSequence.OnCommandExecuted := HandleCommandExecuted;
Evolver.CommandSequence.OnCommandError := HandleCommandError;

7. Verify Catalog Feature Support

if not (cfReadTableInfo in Connector.Broker.Catalog.Features) then
  raise Exception.Create('Broker does not support schema reading');

8. Handle Data Type Conversions Carefully

// For risky conversions, handle in separate steps:
// 1. Add new field with new type
// 2. Copy/convert data
// 3. Drop old field
// 4. Rename new field

Common Scenarios

Adding a New Field

Model change:

// In TCustomer class:
property Email: string;  // New field added

Generated evolution:

ALTER TABLE Customer ADD Email VARCHAR(100)

Modifying Field Size

Model change:

// Changed from:
property Name: string index 50;
// To:
property Name: string index 100;

Generated evolution:

ALTER TABLE Customer ALTER COLUMN Name VARCHAR(100)

Adding an Index

Model change:

// In model:
TCustomer.Email marked with ixUnique option

Generated evolution:

CREATE UNIQUE INDEX IX_Customer_Email ON Customer (Email)

Removing a Field

Model change:

// Removed from TCustomer class:
// property OldField: string;

Generated evolution:

ALTER TABLE Customer DROP COLUMN OldField

Adding a New Class (Table)

Model change:

// New class added:
TProduct = class(TInstantObject)
  property Name: string index 100;
  property Price: Currency;
end;

Generated evolution:

CREATE TABLE Product (
  Id VARCHAR(32) NOT NULL PRIMARY KEY,
  Class VARCHAR(32),
  UpdateCount INTEGER,
  Name VARCHAR(100),
  Price DECIMAL(19,4)
)

Removing a Class (Table)

Model change:

// TObsoleteClass removed from model

Generated evolution:

DROP TABLE ObsoleteClass

Troubleshooting

"Broker does not support schema reading"

Problem: Broker's catalog lacks required features.

Solution:

// Check catalog features:
with Connector.Broker.Catalog do
  if not (cfReadTableInfo in Features) then
    ShowMessage('Catalog does not support reading table metadata');

Ensure broker implementation supports catalog features.

Evolution Generates No Commands

Problem: Database already matches model, but you expected changes.

Diagnosis:

Evolver.BuildCommandSequence;
if Evolver.CommandSequence.Count = 0 then
begin
  // Compare manually
  SourceScheme := Connector.Broker.ReadDatabaseScheme(nil);
  TargetScheme := Connector.CreateScheme(Model);
  // Inspect differences
end;

Possible causes:

• Model not loaded correctly
• Metadata already synchronized
• Catalog not reading schema correctly

Case-Sensitivity Issues

Problem: Tables/fields not found due to case differences.

Solution: Ensure consistent casing in:

• Model metadata
• Database object names
• Catalog implementation

Data Loss During Field Type Change

Problem: Converting VARCHAR to INTEGER fails.

Solution:

// Manual migration:
// 1. Add new field
ALTER TABLE Customer ADD EmailNew VARCHAR(100)
// 2. Copy/convert data
UPDATE Customer SET EmailNew = Email WHERE Email IS NOT NULL
// 3. Drop old field
ALTER TABLE Customer DROP COLUMN Email
// 4. Rename (if needed via SQL or manually)

Transaction Isolation Issues

Problem: Evolution fails due to locked tables.

Solution:

// Ensure exclusive access during evolution:
// 1. Disconnect all users
// 2. Run evolution in maintenance window
// 3. Use appropriate transaction isolation level

Primary Key Changes Not Applied

Problem: Primary key modifications ignored.

Explanation: Primary keys are skipped during evolution to avoid referential integrity issues.

Solution: Modify primary keys manually with careful constraint management.

Events

Inherited from TInstantDBBuildCommandSequence:

Event	Description
OnCommandExecuted	Fired after each command executes
OnCommandError	Fired when a command fails

See InstantDBBuild for event details.

See Also

• InstantDBBuild - Database builder (drop and rebuild)
• InstantMetadata - Metadata system
• InstantPersistence - Persistence infrastructure
• InstantBrokers - Broker architecture

Source Code

File: InstantDBEvolution.pasLocation: Source/Core/

Summary

TInstantDBEvolver provides safe, incremental database schema evolution by:

• Comparing current database schema with target business model
• Generating only necessary changes
• Preserving all existing data
• Supporting rollback via transactions

When to use:

• ✓ Production databases with valuable data
• ✓ Incremental model updates
• ✓ Safe schema migrations

When NOT to use:

• ✗ Initial database creation (use TInstantDBBuilder)
• ✗ Complete schema redesign (backup, drop, rebuild)
• ✗ Development databases without data (TInstantDBBuilder faster)

Remember: Always backup before evolution, preview changes, test on development first, and use transactions for atomicity.