Overview

The imtdb library is a comprehensive Database Abstraction Layer (ORM-like framework) designed for managing database operations in ImtCore applications. It provides a complete ecosystem for multi-database support, object-relational mapping, transaction management, schema migrations, and metadata persistence.

Core Purpose

The library addresses the following key requirements:

Multi-Database Support: Unified interface for PostgreSQL, SQLite, and file-based databases
Object-Relational Mapping: Bidirectional mapping between C++ objects and database records
SQL Query Generation: Automatic generation of parameterized SQL queries for CRUD operations
Transaction Management: ACID-compliant transactions with multi-collection coordination
Schema Migrations: Version-controlled database schema evolution and upgrade management
Document Management: JSON-based serialization of complex objects with metadata
Revision Control: Audit trail and version history for data changes
Hierarchical Structures: Support for tree-structured data and parent-child relationships
Metadata Management: Rich metadata with cascading updates and dependent data cleanup
Backup & Restore: Automated backup scheduling and database restoration capabilities

Architectural Layers

The imtdb architecture follows a layered approach with clear separation of concerns:

┌─────────────────────────────────────────────────────────────┐
│              Application Layer                               │
│        (IObjectCollection, Business Logic)                   │
└─────────────────────────────────────────────────────────────┘
                            ↓
┌─────────────────────────────────────────────────────────────┐
│         ORM Layer (Object-Relational Mapping)               │
│    ISqlDatabaseObjectDelegate, CSqlDatabaseObjectDelegateComp│
└─────────────────────────────────────────────────────────────┘
                            ↓
┌─────────────────────────────────────────────────────────────┐
│     SQL Generation Layer (Query Builders)                   │
│  CreateInsertQuery, CreateSelectQuery, CreateUpdateQuery    │
└─────────────────────────────────────────────────────────────┘
                            ↓
┌─────────────────────────────────────────────────────────────┐
│        Database Engine Layer                                │
│    IDatabaseEngine, CDatabaseEngineComp                     │
│  (Transaction Management, Query Execution)                  │
└─────────────────────────────────────────────────────────────┘
                            ↓
┌─────────────────────────────────────────────────────────────┐
│         Qt SQL Driver Layer (QSqlDatabase)                  │
└─────────────────────────────────────────────────────────────┘
                            ↓
┌─────────────────────────────────────────────────────────────┐
│    Database Server (PostgreSQL, SQLite, Files)             │
└─────────────────────────────────────────────────────────────┘

Interface Layer

The interface layer defines the core abstractions for database management:

Interface	Purpose
imtdb::IDatabaseEngine	Low-level SQL execution: transactions, query execution (static & from files), database/table creation
imtdb::IDatabaseConnector	Database connection lifecycle management (connect, disconnect, connection state)
imtdb::IDatabaseLoginSettings	Connection parameters: host, port, database name, username, password, file path
imtdb::IDatabaseObjectDelegate	Bridge between database and C++ objects; generates SQL queries for CRUD operations
imtdb::ISqlDatabaseObjectDelegate	Extended delegate: converts SQL records to objects, creates meta-information from records
imtdb::ISqlDatabaseObjectCollection	Manages collections of objects in SQL database with transaction control and filtering
imtdb::IMigrationController	Manages database schema versioning and executes migration scripts
imtdb::IDatabaseServerConnectionChecker	Verifies database server connectivity and health status
imtdb::IDependentMetaInfoController	Manages cascading metadata updates when related objects are deleted
imtdb::IJsonBasedMetaInfoDelegate	Converts object metadata to/from JSON representation
imtdb::IMetaInfoTableDelegate	Describes database table structure and metadata column mappings
imtdb::ISqlJsonXPathExtractor	Database-specific JSON field extraction (PostgreSQL vs SQLite dialects)

Component Implementations

Database Connection & Management

Component	Purpose
imtdb::CDatabaseEngineComp	Core database engine: manages connections, transactions, query execution, automatic database/table creation
imtdb::CDatabaseConnectorComp	Manages connections to remote database servers with credential handling
imtdb::CDatabaseAccessSettings	Stores and manages database connection credentials and settings
imtdb::CDatabaseAccessSettingsComp	Component wrapper for CDatabaseAccessSettings with ACF integration
imtdb::CDatabaseAutomaticBackupComp	Schedules periodic database backups with configurable retention policies

Object Collections & Delegates

Component	Purpose
imtdb::CSqlDatabaseObjectCollectionComp	Core collection implementation: manages CRUD operations on object collections via SQL
imtdb::CSqlDatabaseObjectDelegateCompBase	Base implementation for object delegates: generates SQL queries for CRUD operations, handles filtering, sorting, and pagination
imtdb::CSqlDatabaseDocumentDelegateComp	Specialized delegate for document-based objects with JSON metadata, revision tracking, and dependent metadata management
imtdb::CSqlDatabaseDocumentDelegateLegacyComp	Legacy version of document delegate for backward compatibility
imtdb::CSqlJsonDatabaseDelegateComp	Extension supporting JSON-based object serialization and deserialization
imtdb::CSqliteDatabaseDocumentDelegateComp	SQLite-specific document delegate with dialect-specific SQL (LIMIT/OFFSET, JSON functions)
imtdb::CSqliteJsonDatabaseDelegateComp	SQLite-specific JSON delegate implementation
imtdb::CSqlDatabaseObjectCollectionIterator	Iterator for traversing SQL query results and converting records to objects
imtdb::CSqlDatabaseCollectionStructureIterator	Iterator for traversing hierarchical tree structures in database

Metadata Management

Component	Purpose
imtdb::CJsonBasedMetaInfoDelegateComp	Abstract base for JSON ↔ MetaInfo conversion (subclasses implement specific representations)
imtdb::CStandardSqlMetaInfoTableDelegateComp	Defines metadata table schema and handles metadata field conversions
imtdb::CDependentTableMetaInfoControllerComp	Observes collection changes and triggers dependent metadata cleanup on deletions

SQL & JSON Handling

Component	Purpose
imtdb::CPostgresXPathExtractorComp	PostgreSQL-specific JSON extraction using operators: `->`, `->>`, `#>`
imtdb::CSqliteXPathExtractorComp	SQLite-specific JSON extraction using functions: `json_extract()`, `json_type()`
imtdb::CComplexCollectionFilterConverter	Converts filter parameters (IParamsSet) to SQL WHERE clauses with proper escaping
imtdb::CSqlStructureDelegateCompBase	Base class for hierarchical structure management (tree nodes and leaves)
imtdb::CSqlStructureDelegateComp	Concrete implementation of structure delegate for tree-based data

Migrations & Versioning

Component	Purpose
imtdb::CMigrationControllerComp	Reads SQL migration scripts from folders and executes schema upgrades sequentially
imtdb::CMigrationControllerCompBase	Base migration logic with version range tracking and script execution
imtdb::CCompositeMigrationControllerComp	Combines multiple migration controllers with coordinated sequential execution
imtdb::CObjectCollectionMigrationControllerComp	Migration controller specialized for object collection schema changes

Advanced Features

Component	Purpose
imtdb::CSqlDatabaseTransactionManagerComp	Coordinates ACID transactions across multiple collections with rollback support
imtdb::CRestoringDatabaseControllerComp	Database backup and restore using command-line tools (pg_dump, pg_restore)
imtdb::TMessageDatabaseDelegateComp	Template-based delegate for message/notification persistence

Architectural Patterns

Delegate Pattern

The imtdb library extensively uses the Delegate pattern to separate collection logic from database-specific operations:

IDatabaseObjectDelegate abstracts SQL generation from collection management
Subclasses provide type-specific and database-specific query generation
Enables swapping database backends (PostgreSQL ↔ SQLite) without changing collection code
Allows specialized delegates for documents, messages, and hierarchical structures

Template Method

Base classes define algorithmic structure while subclasses provide specific implementations:

CSqlDatabaseObjectDelegateCompBase provides query generation template
CMigrationControllerCompBase defines migration execution flow
Subclasses override specific methods for database-specific SQL dialects

Component-Based Architecture

Uses ACF (Application Component Framework) for dependency injection:

Components declared with I_BEGIN_COMPONENT / I_END_COMPONENT
Dependencies injected via I_ASSIGN (references and attributes)
Configuration-driven composition for flexible system assembly
Hot-swappable implementations through component substitution

Observer Pattern

Components observe changes and trigger cascading operations:

Database access settings notify components of connection parameter changes
CDependentTableMetaInfoControllerComp observes collection updates
Enables automatic cleanup of dependent metadata on object deletion

Adapter Pattern

Adapters bridge between different abstraction layers:

CSqlDatabaseDocumentDelegateComp adapts documents to SQL tables
CJsonBasedMetaInfoDelegate adapts metadata to JSON representation
CSqlJsonXPathExtractor adapts JSON queries to database-specific syntax

Factory Pattern

Factory methods create objects based on runtime type information:

Document factories create objects by TypeId
Collection factories create sub-collections for hierarchical data
Enables polymorphic object creation from database records

Main Workflows

Object Insertion Flow

InsertNewObject(object)
  ↓
1. Validate object data
  ↓
2. CreateNewObjectQuery()
   - Generate SQL INSERT statement
   - Serialize object data (binary/JSON)
   - Create metadata JSON
  ↓
3. Add operation context
   - User ID, timestamp
   - Operation description
  ↓
4. ExecuteTransaction()
   - Begin transaction
   - Execute INSERT
   - Commit or rollback
  ↓
5. Return generated object ID

Object Retrieval Flow

GetObjectData(filters, sort, pagination)
  ↓
1. Build selection query
   - CreateFilterQuery() → WHERE clause
   - CreateSortQuery() → ORDER BY clause
   - CreatePaginationQuery() → LIMIT/OFFSET
  ↓
2. ExecuteQuery(sql)
  ↓
3. For each record in result set:
   a. CreateObjectFromRecord()
      - Deserialize object data
   b. CreateObjectInfoFromRecord()
      - Extract metadata
      - Parse JSON fields
  ↓
4. Return populated object collection

Object Update Flow

UpdateObject(objectId, newData)
  ↓
1. Backup current revision (if enabled)
   - Serialize current state
   - Calculate checksum
   - Store in revision table
  ↓
2. CreateUpdateQuery()
   - Generate SQL UPDATE
   - Update modified fields
   - Update LastModified timestamp
  ↓
3. Update metadata
   - Increment revision number
   - Record operation context
  ↓
4. ExecuteTransaction()
  ↓
5. Notify observers (dependent metadata updates)

Dependent Metadata Cleanup Flow

RemoveElements(objectIds)
  ↓
1. For each object ID:
   ClearDependentMetaInfo(metaInfo)
  ↓
2. Find dependent tables
   - Parse DataMetaInfo JSON
   - Identify referenced objects
   Example: Order objects referencing deleted Customer
  ↓
3. Extract referencing object IDs
   - Query dependent tables
   - Find objects with references to deleted items
  ↓
4. Update metadata JSON fields
   - Remove stale references
   - Update metadata timestamps
  ↓
5. Execute batch updates
   - NULL out or remove dependent references
   - Preserve data integrity

Database Migration Flow

DoMigration(targetVersion)
  ↓
1. GetMigrationRange()
   - Determine current database version
   - Calculate required migration steps
  ↓
2. For each version in range:
   a. Load migration SQL script
      - Read from configured folder
      - Format: "migration_v001_to_v002.sql"
   b. Validate script syntax
   c. Begin transaction
   d. Execute migration script
   e. Update schema version table
   f. Commit transaction
  ↓
3. Handle errors
   - Rollback on failure
   - Log migration errors
  ↓
4. Return final database version

Transaction Management Flow

CSqlDatabaseTransactionManagerComp::StartTransaction()
  ↓
1. Disable internal transactions
   - Suspend auto-commit on all collections
  ↓
2. DatabaseEngine.BeginTransaction()
  ↓
3. Application performs operations
   - Multiple inserts/updates/deletes
   - Across multiple collections
  ↓
4. EndTransaction(commit=true/false)
  ↓
5. If commit:
   a. Execute accumulated SQL statements
   b. DatabaseEngine.FinishTransaction()
   c. Re-enable collection auto-transactions
   Else:
   a. DatabaseEngine.RollbackTransaction()
   b. Discard pending operations
   c. Re-enable collection auto-transactions

Major Subsystems

Subsystem	Key Components	Purpose
Connection Management	IDatabaseEngine, CDatabaseEngineComp, CDatabaseConnectorComp	Connect/disconnect, manage database connections, execute raw SQL
Object Collections	CSqlDatabaseObjectCollectionComp, ISqlDatabaseObjectCollection	CRUD operations on object collections with filtering and pagination
Delegation & ORM	IDatabaseObjectDelegate, CSqlDatabaseObjectDelegateCompBase	SQL generation strategies, object-relational mapping
Document Management	CSqlDatabaseDocumentDelegateComp, CSqlJsonDatabaseDelegateComp	Serialization of complex objects, JSON-based document storage
Metadata System	IJsonBasedMetaInfoDelegate, CStandardSqlMetaInfoTableDelegateComp	Object metadata, attributes, custom fields
Revision Control	CSqlDatabaseDocumentDelegateComp (IRevisionController)	Audit trail, version history, object change tracking
Hierarchical Data	CSqlStructureDelegateCompBase, CSqlDatabaseCollectionStructureIterator	Tree-structured data, parent-child relationships
Schema Migrations	IMigrationController, CMigrationControllerComp	Database versioning, schema evolution, upgrade scripts
Transaction Management	CSqlDatabaseTransactionManagerComp	Multi-collection ACID transactions, coordinated commits
Backup & Restore	CRestoringDatabaseControllerComp, CDatabaseAutomaticBackupComp	Data persistence, disaster recovery, automated backups
JSON Operations	CPostgresXPathExtractorComp, CSqliteXPathExtractorComp	Database-agnostic JSON queries, field extraction
Dependent Data Management	CDependentTableMetaInfoControllerComp	Cascading updates, reference cleanup, referential integrity

Database Schema Conventions

The imtdb library assumes specific database table structures for proper operation:

Core Object Tables

Each object collection typically has a table with these columns:

Id (PRIMARY KEY): Unique object identifier (UUID or auto-increment integer)
TypeId: Object type identifier for polymorphic collections
Name: Human-readable object name
Description: Object description or summary
Document or Data: Serialized object data (binary or JSON)
State: Object state (active, deleted, archived)

Metadata Columns

Extended metadata support (optional):

DataMetaInfo (JSON/JSONB): Custom metadata fields, dependent references
RevisionInfo (JSON/JSONB): Revision history, audit information
TimeStamp: Object creation timestamp
LastModified: Last modification timestamp
Added: Date added to collection
OwnerID: User/owner identifier
OperationDescription: Last operation performed

Revision Tables

For audit trail functionality:

RevisionNumber: Sequential revision identifier
ObjectId: Reference to original object
RevisionData: Serialized object state at revision
Checksum: Data integrity verification
UserId: User who made the change
OperationDescription: Change description
RevisionTimestamp: When revision was created

Hierarchical Tables

For tree structures:

ParentId: Reference to parent object
ChildId: Reference to child object
Level: Depth level in hierarchy
Path: Materialized path (e.g., "/root/parent/child")

Key Design Features

Multi-Database Support

✓ Pluggable database backends via delegate pattern

PostgreSQL support with advanced features (JSONB, full-text search)
SQLite support for embedded/offline scenarios
File-based storage for simple use cases
Easy addition of new database backends

Type Safety & Security

✓ Parameterized queries prevent SQL injection attacks

All user input properly escaped
Bound parameters used for values
No string concatenation for SQL generation

Performance Optimizations

✓ Lazy loading for large datasets ✓ Pagination support (LIMIT/OFFSET) ✓ Indexed queries via metadata ✓ Batch operations for bulk inserts/updates ✓ Connection pooling (via Qt SQL layer) ✓ Transaction batching reduces round-trips

Advanced Filtering

✓ Complex filters via IParamsSet without raw SQL:

Equality, inequality comparisons
Range queries (BETWEEN)
Pattern matching (LIKE, regex)
JSON field queries
Composite filters (AND/OR)
NULL checks

Audit Trail & Compliance

✓ Complete revision history for objects ✓ User tracking for all modifications ✓ Timestamp tracking (created, modified) ✓ Checksum verification for data integrity ✓ Operation descriptions for audit logs ✓ Soft delete support (state column)

Hierarchical Data Support

✓ Tree structures with parent-child relationships ✓ Recursive queries for descendants/ancestors ✓ Materialized paths for efficient traversal ✓ Level tracking for depth calculations ✓ Subtree operations (move, copy, delete)

Rich Metadata

✓ JSON-based metadata fields ✓ Custom attributes per object ✓ Dependent references tracking ✓ Cascading updates on deletions ✓ Schema-less extensions via JSON

Schema Evolution

✓ Version-controlled migrations from SQL scripts ✓ Sequential execution ensures consistency ✓ Rollback on failure maintains database integrity ✓ Multi-step migrations for complex changes ✓ Composite controllers for distributed schemas

Usage Examples

Connecting to Database

// Create database engine
auto engine = acf::CreateComponent<imtdb::CDatabaseEngineComp>();
 
// Configure connection settings
auto settings = acf::CreateComponent<imtdb::CDatabaseAccessSettingsComp>();
settings->SetHost("localhost");
settings->SetPort(5432);
settings->SetDatabaseName("myapp_db");
settings->SetUserName("dbuser");
settings->SetPassword("secure_password");
 
// Assign settings to engine
engine->SetDatabaseSettings(settings);
 
// Connect to database
if (engine->ConnectToDatabase()) {
    // Database ready for operations
}

Creating Object Collection

// Create collection with delegate
auto collection = acf::CreateComponent<imtdb::CSqlDatabaseObjectCollectionComp>();
auto delegate = acf::CreateComponent<imtdb::CSqlDatabaseDocumentDelegateComp>();
 
// Configure delegate
delegate->SetTableName("users");
delegate->SetDatabaseEngine(engine);
 
// Assign delegate to collection
collection->SetDelegate(delegate);
 
// Collection is ready for CRUD operations

Inserting Objects

// Create new object
auto user = std::make_shared<User>();
user->SetName("John Doe");
user->SetEmail("john@example.com");
 
// Insert into collection
QString objectId;
if (collection->InsertNewObject(user, objectId)) {
    // Object inserted successfully, objectId contains new ID
}

Querying with Filters

// Create filter parameters
auto filters = acf::CreateComponent<iprm::CParamsSetComp>();
filters->SetValue("Name", "John%");  // LIKE query
filters->SetValue("Age", 25, iprm::CT_GREATER_THAN);
 
// Create sort parameters
auto sortParams = acf::CreateComponent<iprm::CParamsSetComp>();
sortParams->SetValue("Name", true);  // Ascending
 
// Query collection
auto results = collection->CreateSubCollection(filters, sortParams);
for (int i = 0; i < results->GetCount(); ++i) {
    auto user = results->GetObject(i);
    // Process user object
}

Using Transactions

// Create transaction manager
auto txManager = acf::CreateComponent<imtdb::CSqlDatabaseTransactionManagerComp>();
txManager->AddCollection(usersCollection);
txManager->AddCollection(ordersCollection);
 
// Start transaction
if (txManager->StartTransaction()) {
    try {
        // Multiple operations across collections
        usersCollection->UpdateObject(userId, userData);
        ordersCollection->InsertNewObject(order, orderId);
        
        // Commit transaction
        txManager->EndTransaction(true);
    } catch (...) {
        // Rollback on error
        txManager->EndTransaction(false);
    }
}

Running Migrations

// Create migration controller
auto migrationCtrl = acf::CreateComponent<imtdb::CMigrationControllerComp>();
migrationCtrl->SetDatabaseEngine(engine);
migrationCtrl->SetMigrationFolder("./migrations/");
 
// Check current version
int currentVersion = migrationCtrl->GetCurrentDatabaseVersion();
 
// Migrate to latest version
int targetVersion = migrationCtrl->GetLatestMigrationVersion();
if (migrationCtrl->DoMigration(targetVersion)) {
    // Migration successful
}

Best Practices

Transaction Usage

Keep transactions short: Minimize transaction duration to reduce lock contention
Batch operations: Group related operations in single transaction
Handle errors: Always rollback on errors, commit on success
Avoid nested transactions: Use transaction manager for coordinated access

Migration Guidelines

Sequential numbering: Use consistent versioning (v001, v002, ...)
Idempotent scripts: Make migrations repeatable (IF NOT EXISTS)
Test migrations: Validate on copy before production
Backup first: Always backup before major schema changes
Document changes: Include comments in migration scripts

Performance Tips

Use indexes: Create indexes on frequently queried columns
Limit result sets: Use pagination for large collections
Batch operations: Use bulk inserts instead of individual operations
Connection pooling: Reuse database connections
Lazy loading: Load related objects only when needed

Security Considerations

Parameterized queries: Never concatenate user input into SQL
Least privilege: Use minimal database permissions
Encrypt credentials: Don't store passwords in plain text
Validate input: Check data before database operations
Audit trail: Enable revision tracking for sensitive data

Conclusion

The imtdb library provides a robust, flexible, and feature-rich database abstraction layer that:

Simplifies database operations through high-level abstractions
Supports multiple database backends with minimal code changes
Ensures data integrity through transaction management
Enables schema evolution via migration system
Provides audit trail and revision control
Optimizes performance through lazy loading and pagination
Maintains security through parameterized queries

The component-based architecture allows easy customization and extension while maintaining clean separation of concerns through well-defined interfaces and design patterns.

Table of Contents