Multi-Database Support

Overview

Q01 Core APIs support multiple database engines through PDO (PHP Data Objects) abstraction layer. This allows the same codebase to work with MySQL, Oracle, and PostgreSQL without modification.

Supported Databases:

MySQL 5.7+ / MariaDB 10.2+
Oracle 11g+ / 12c+ / 19c+
PostgreSQL 9.6+ / 10+ / 11+

Benefits:

✅ Single codebase for all databases
✅ Database-specific optimizations
✅ Automatic query adaptation
✅ Connection pooling
✅ Transaction support across databases
✅ Easy migration between engines

PDO Abstraction Layer

PDO Wrapper

CoreQuery/CoreWrite use custom PDO wrapper:

// DbInterface/PDOWrapper.php
class PDOWrapper {
    private $pdo;
    private $driver;  // mysql, oci, pgsql

    public function __construct(array $config) {
        $this->driver = $config['driver'];
        $dsn = $this->buildDSN($config);

        $this->pdo = new PDO($dsn, $config['username'], $config['password'], [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::ATTR_EMULATE_PREPARES => false
        ]);
    }

    private function buildDSN(array $config): string {
        switch ($config['driver']) {
            case 'mysql':
                return "mysql:host={$config['host']};dbname={$config['database']};charset=utf8mb4";

            case 'oci':
                return "oci:dbname=//{$config['host']}/{$config['database']}";

            case 'pgsql':
                return "pgsql:host={$config['host']};dbname={$config['database']}";

            default:
                throw new \Exception("Unsupported driver: {$config['driver']}");
        }
    }

    public function query(string $sql, array $params = []): PDOStatement {
        // Adapt SQL for database-specific syntax
        $adaptedSQL = $this->adaptSQL($sql);

        $stmt = $this->pdo->prepare($adaptedSQL);
        $stmt->execute($params);

        return $stmt;
    }

    private function adaptSQL(string $sql): string {
        // Convert generic SQL to database-specific syntax
        if ($this->driver === 'oci') {
            // Oracle: LIMIT → ROWNUM
            $sql = preg_replace('/LIMIT (\d+) OFFSET (\d+)/', 'OFFSET \2 ROWS FETCH NEXT \1 ROWS ONLY', $sql);
        }

        if ($this->driver === 'pgsql') {
            // PostgreSQL: CONCAT → ||
            $sql = str_replace('CONCAT(', '(', $sql);
            $sql = str_replace(',', ' || ', $sql);
        }

        return $sql;
    }
}

Database Configuration

Configuration File

config/database.php:

return [
    // Metadata database (TB_DIM, TB_COST, TB_VAR, TB_MENU)
    'meta' => [
        'driver' => env('META_DB_DRIVER', 'mysql'),
        'host' => env('META_DB_HOST', 'localhost'),
        'port' => env('META_DB_PORT', 3306),
        'database' => env('META_DB_DATABASE', 'q01_meta'),
        'username' => env('META_DB_USERNAME', 'q01_user'),
        'password' => env('META_DB_PASSWORD', 'secure_password'),
        'charset' => 'utf8mb4',
        'collation' => 'utf8mb4_unicode_ci',
        'options' => [
            PDO::ATTR_PERSISTENT => true
        ]
    ],

    // Data database (TB_ANAG_{DIM}00)
    'data' => [
        'driver' => env('DATA_DB_DRIVER', 'oracle'),
        'host' => env('DATA_DB_HOST', 'oracle.internal'),
        'port' => env('DATA_DB_PORT', 1521),
        'database' => env('DATA_DB_DATABASE', 'q01_data'),
        'username' => env('DATA_DB_USERNAME', 'q01_user'),
        'password' => env('DATA_DB_PASSWORD', 'secure_password')
    ],

    // Auth database (users, JWT secrets)
    'auth' => [
        'driver' => env('AUTH_DB_DRIVER', 'postgresql'),
        'host' => env('AUTH_DB_HOST', 'postgres.internal'),
        'port' => env('AUTH_DB_PORT', 5432),
        'database' => env('AUTH_DB_DATABASE', 'q01_auth'),
        'username' => env('AUTH_DB_USERNAME', 'q01_user'),
        'password' => env('AUTH_DB_PASSWORD', 'secure_password'),
        'schema' => 'public'
    ]
];

Environment Variables

.env file:

# Metadata database (MySQL)
META_DB_DRIVER=mysql
META_DB_HOST=mysql-meta.example.com
META_DB_PORT=3306
META_DB_DATABASE=q01_meta
META_DB_USERNAME=q01_user
META_DB_PASSWORD=secure_meta_password

# Data database (Oracle)
DATA_DB_DRIVER=oci
DATA_DB_HOST=oracle-data.example.com
DATA_DB_PORT=1521
DATA_DB_DATABASE=q01_data
DATA_DB_USERNAME=q01_user
DATA_DB_PASSWORD=secure_data_password

# Auth database (PostgreSQL)
AUTH_DB_DRIVER=pgsql
AUTH_DB_HOST=postgres-auth.example.com
AUTH_DB_PORT=5432
AUTH_DB_DATABASE=q01_auth
AUTH_DB_USERNAME=q01_user
AUTH_DB_PASSWORD=secure_auth_password

Database-Specific Syntax

Pagination

Generic SQL:

SELECT * FROM TB_ANAG_PRD00
WHERE PRD_SOURCE = 'tenant'
LIMIT 100 OFFSET 50;

MySQL:

SELECT * FROM TB_ANAG_PRD00
WHERE PRD_SOURCE = 'tenant'
LIMIT 100 OFFSET 50;

Oracle:

SELECT * FROM TB_ANAG_PRD00
WHERE PRD_SOURCE = 'tenant'
OFFSET 50 ROWS FETCH NEXT 100 ROWS ONLY;

PostgreSQL:

SELECT * FROM TB_ANAG_PRD00
WHERE PRD_SOURCE = 'tenant'
LIMIT 100 OFFSET 50;

String Concatenation

MySQL:

SELECT CONCAT(XPRD01, ' - ', XPRD03) as full_name
FROM TB_ANAG_PRD00;

Oracle:

SELECT XPRD01 || ' - ' || XPRD03 as full_name
FROM TB_ANAG_PRD00;

PostgreSQL:

SELECT XPRD01 || ' - ' || XPRD03 as full_name
FROM TB_ANAG_PRD00;

Date Functions

MySQL:

SELECT DATE_FORMAT(CREATED_AT, '%Y-%m-%d') as created_date
FROM TB_ANAG_PRD00;

Oracle:

SELECT TO_CHAR(TO_DATE(CREATED_AT, 'YYYYMMDDHH24MISS'), 'YYYY-MM-DD') as created_date
FROM TB_ANAG_PRD00;

PostgreSQL:

SELECT TO_CHAR(TO_TIMESTAMP(CREATED_AT, 'YYYYMMDDHH24MISS'), 'YYYY-MM-DD') as created_date
FROM TB_ANAG_PRD00;

Auto-Increment

MySQL:

CREATE TABLE TB_COUNTER (
    COUNTER_ID INT PRIMARY KEY AUTO_INCREMENT,
    COUNTER_VALUE INT
);

Oracle:

CREATE SEQUENCE seq_counter START WITH 1 INCREMENT BY 1;

CREATE TABLE TB_COUNTER (
    COUNTER_ID NUMBER PRIMARY KEY,
    COUNTER_VALUE NUMBER
);

CREATE TRIGGER trg_counter_id
BEFORE INSERT ON TB_COUNTER
FOR EACH ROW
BEGIN
    SELECT seq_counter.NEXTVAL INTO :NEW.COUNTER_ID FROM DUAL;
END;

PostgreSQL:

CREATE TABLE TB_COUNTER (
    COUNTER_ID SERIAL PRIMARY KEY,
    COUNTER_VALUE INT
);

Connection Management

Connection Pooling

PHP-FPM configuration:

; php-fpm.conf
pm = dynamic
pm.max_children = 50
pm.start_servers = 10
pm.min_spare_servers = 5
pm.max_spare_servers = 20
pm.max_requests = 500

; Persistent connections
pdo_mysql.persistent = 1
pdo_oci.persistent = 1
pdo_pgsql.persistent = 1

Connection Manager

Multiple database connections:

class ConnectionManager {
    private $connections = [];

    public function getConnection(string $database): PDOWrapper {
        if (!isset($this->connections[$database])) {
            $config = config("database.{$database}");
            $this->connections[$database] = new PDOWrapper($config);
        }

        return $this->connections[$database];
    }

    public function getMeta(): PDOWrapper {
        return $this->getConnection('meta');
    }

    public function getData(): PDOWrapper {
        return $this->getConnection('data');
    }

    public function getAuth(): PDOWrapper {
        return $this->getConnection('auth');
    }
}

// Usage
$cm = new ConnectionManager();

// Query metadata
$dimensions = $cm->getMeta()->query("SELECT * FROM TB_DIM")->fetchAll();

// Query data
$products = $cm->getData()->query(
    "SELECT * FROM TB_ANAG_PRD00 WHERE PRD_SOURCE = ?",
    ['tenant']
)->fetchAll();

// Query auth
$user = $cm->getAuth()->query(
    "SELECT * FROM USERS WHERE EMAIL = ?",
    ['user@example.com']
)->fetch();

Database-Specific Optimizations

MySQL Optimizations

InnoDB Configuration:

[mysqld]
# InnoDB buffer pool (70-80% of RAM)
innodb_buffer_pool_size = 8G
innodb_buffer_pool_instances = 8

# InnoDB logs
innodb_log_file_size = 512M
innodb_log_buffer_size = 16M

# Connection limits
max_connections = 500
thread_cache_size = 100

# Query cache
query_cache_type = 1
query_cache_size = 256M

Indexes:

-- Composite index for common queries
CREATE INDEX idx_prd_tenant_active ON TB_ANAG_PRD00(
    PRD_SOURCE,
    TREC,
    XPRD06,
    CREATED_AT
) USING BTREE;

-- Full-text search
CREATE FULLTEXT INDEX idx_prd_fulltext ON TB_ANAG_PRD00(XPRD01, XPRD_DESCRIPTION);

Oracle Optimizations

Tablespace Configuration:

-- Create tablespace for Q01 data
CREATE TABLESPACE q01_data
    DATAFILE '/u01/app/oracle/oradata/q01/q01_data01.dbf'
    SIZE 1G
    AUTOEXTEND ON NEXT 100M MAXSIZE 10G;

-- Create table in tablespace
CREATE TABLE TB_ANAG_PRD00 (
    PRD_ID VARCHAR2(36) PRIMARY KEY,
    XPRD01 VARCHAR2(255),
    ...
) TABLESPACE q01_data;

Partitioning:

-- Partition by source (tenant)
CREATE TABLE TB_ANAG_PRD00 (
    PRD_ID VARCHAR2(36),
    PRD_SOURCE VARCHAR2(50),
    ...
) PARTITION BY LIST (PRD_SOURCE) (
    PARTITION p_tenant_a VALUES ('tenantA'),
    PARTITION p_tenant_b VALUES ('tenantB'),
    PARTITION p_default VALUES (DEFAULT)
);

Indexes:

-- Bitmap index for low-cardinality columns
CREATE BITMAP INDEX idx_prd_trec ON TB_ANAG_PRD00(TREC);

-- B-tree index for high-cardinality
CREATE INDEX idx_prd_source ON TB_ANAG_PRD00(PRD_SOURCE);

PostgreSQL Optimizations

Configuration:

# postgresql.conf

# Memory
shared_buffers = 2GB
effective_cache_size = 6GB
work_mem = 50MB
maintenance_work_mem = 512MB

# Connections
max_connections = 200

# Query planning
random_page_cost = 1.1
effective_io_concurrency = 200

# WAL
wal_buffers = 16MB
min_wal_size = 1GB
max_wal_size = 4GB

Indexes:

-- GIN index for JSONB columns
CREATE INDEX idx_prd_metadata ON TB_ANAG_PRD00 USING GIN(metadata);

-- Partial index for active products
CREATE INDEX idx_prd_active ON TB_ANAG_PRD00(XPRD06)
WHERE TREC != 'C';

-- Expression index
CREATE INDEX idx_prd_name_lower ON TB_ANAG_PRD00(LOWER(XPRD01));

Multi-Database Transactions

Distributed Transactions

Two-phase commit (2PC):

class DistributedTransaction {
    private $connections = [];

    public function begin() {
        foreach ($this->connections as $conn) {
            $conn->query('BEGIN');
        }
    }

    public function commit() {
        // Phase 1: Prepare
        foreach ($this->connections as $conn) {
            $conn->query('PREPARE TRANSACTION \'xid\'');
        }

        // Phase 2: Commit
        foreach ($this->connections as $conn) {
            $conn->query('COMMIT PREPARED \'xid\'');
        }
    }

    public function rollback() {
        foreach ($this->connections as $conn) {
            $conn->query('ROLLBACK');
        }
    }
}

// Usage
$dt = new DistributedTransaction();
$dt->addConnection($metaDB);
$dt->addConnection($dataDB);

try {
    $dt->begin();

    // Update metadata
    $metaDB->query("UPDATE TB_DIM SET ...");

    // Update data
    $dataDB->query("UPDATE TB_ANAG_PRD00 SET ...");

    $dt->commit();
} catch (\Exception $e) {
    $dt->rollback();
    throw $e;
}

Migration Between Databases

Database Migration Tools

Export from MySQL to Oracle:

# Export MySQL data
mysqldump -u user -p q01_data > q01_data_mysql.sql

# Convert MySQL → Oracle syntax
sed -i 's/AUTO_INCREMENT/GENERATED ALWAYS AS IDENTITY/g' q01_data_mysql.sql
sed -i 's/VARCHAR(255)/VARCHAR2(255)/g' q01_data_mysql.sql
sed -i 's/DATETIME/TIMESTAMP/g' q01_data_mysql.sql

# Import to Oracle
sqlplus user/password@oracle < q01_data_oracle.sql

Schema Compatibility:

// Modules.php - Database-agnostic schema
public function getTableSchema(string $dimension): array {
    $driver = $this->pdo->getDriver();

    $columns = match($driver) {
        'mysql' => $this->getTableSchemaMySQL($dimension),
        'oci' => $this->getTableSchemaOracle($dimension),
        'pgsql' => $this->getTableSchemaPostgreSQL($dimension),
    };

    return $columns;
}

Best Practices

✅ DO:

Use parameterized queries:

// ✅ Good - prevents SQL injection across all databases
$stmt = $pdo->query(
    "SELECT * FROM TB_ANAG_PRD00 WHERE PRD_SOURCE = ?",
    [$source]
);

Test on all target databases:

# ✅ Good - CI/CD pipeline tests
docker-compose up -d mysql oracle postgres
./vendor/bin/phpunit --group database

Use database-agnostic SQL:

// ✅ Good - works on all databases
$sql = "SELECT * FROM TB_ANAG_PRD00 WHERE PRD_SOURCE = ? LIMIT ? OFFSET ?";

// PDOWrapper adapts to database-specific syntax

❌ DON'T:

Don't use database-specific features directly:

-- ❌ Bad - MySQL specific
SELECT * FROM TB_ANAG_PRD00 FORCE INDEX (idx_prd_source);

-- ✅ Good - database agnostic
SELECT * FROM TB_ANAG_PRD00 WHERE PRD_SOURCE = ?;

Don't hardcode database types:

// ❌ Bad
if ($database === 'mysql') {
    // MySQL logic
}

// ✅ Good - use driver detection
if ($pdo->getDriver() === 'mysql') {
    // Adapt query
}

Summary

✅ PDO abstraction supports MySQL, Oracle, PostgreSQL
✅ Single codebase works with all databases
✅ Automatic SQL adaptation for database-specific syntax
✅ Connection pooling for performance
✅ Database-specific optimizations (indexes, partitioning)
✅ Distributed transactions with two-phase commit
✅ Easy migration between database engines

Key Takeaways:

PDO abstraction layer enables multi-database support
Use database-agnostic SQL when possible
Adapt syntax automatically for database-specific features
Configure connection pooling for performance
Apply database-specific optimizations (indexes, partitioning)
Test on all target databases
Use parameterized queries for security

Database Separation:

Database	Purpose	Tables
Meta	Metadata	TB_DIM, TB_COST, TB_VAR, TB_MENU
Data	Business data	`TB_ANAG_{DIM}00`
Auth	Authentication	USERS, JWT_SECRETS

Performance Optimization - Query tuning and indexing
Internal Architecture - CoreService/CoreQuery/CoreWrite
Advanced Topics - Overview

Multi-Database Support

Overview​

PDO Abstraction Layer​

PDO Wrapper​

Database Configuration​

Configuration File​

Environment Variables​

Database-Specific Syntax​

Pagination​

String Concatenation​

Date Functions​

Auto-Increment​

Connection Management​

Connection Pooling​

Connection Manager​

Database-Specific Optimizations​

MySQL Optimizations​

Oracle Optimizations​

PostgreSQL Optimizations​

Multi-Database Transactions​

Distributed Transactions​

Migration Between Databases​

Database Migration Tools​

Best Practices​

✅ DO:​

❌ DON'T:​

Summary​

Related Concepts​

Overview

PDO Abstraction Layer

PDO Wrapper

Database Configuration

Configuration File

Environment Variables

Database-Specific Syntax

Pagination

String Concatenation

Date Functions

Auto-Increment

Connection Management

Connection Pooling

Connection Manager

Database-Specific Optimizations

MySQL Optimizations

Oracle Optimizations

PostgreSQL Optimizations

Multi-Database Transactions

Distributed Transactions

Migration Between Databases

Database Migration Tools

Best Practices

✅ DO:

❌ DON'T:

Summary

Related Concepts