ConnectionManager Class

Overview

The Kotchasan\Connection\ConnectionManager class manages multiple database connections. It supports multi-database configuration, driver management, query caching, and centralized table management.

Installation and Setup

Requirements

• PHP 7.4 or higher
• Kotchasan Framework
• PDO drivers for desired databases

Creating ConnectionManager

use Kotchasan\Connection\ConnectionManager;

$config = [
    'default' => [
        'driver' => 'mysql',
        'host' => 'localhost',
        'database' => 'myapp',
        'username' => 'user',
        'password' => 'password',
        'charset' => 'utf8mb4',
        'prefix' => 'app_'
    ]
];

$manager = new ConnectionManager($config);

Database Configuration

MySQL Configuration

$config = [
    'mysql_main' => [
        'driver' => 'mysql',
        'host' => 'localhost',
        'port' => 3306,
        'database' => 'main_db',
        'username' => 'mysql_user',
        'password' => 'mysql_pass',
        'charset' => 'utf8mb4',
        'prefix' => 'main_',
        'unix_socket' => null, // Use when connecting via Unix socket
        'tables' => [
            'user' => 'users',
            'category' => 'categories'
        ]
    ]
];

PostgreSQL Configuration

$config = [
    'pgsql_main' => [
        'driver' => 'pgsql',
        'host' => 'localhost',
        'port' => 5432,
        'database' => 'main_db',
        'username' => 'pgsql_user',
        'password' => 'pgsql_pass',
        'schema' => 'public',
        'application_name' => 'MyApp',
        'sslmode' => 'prefer',
        'prefix' => 'main_',
        'tables' => [
            'user' => 'users',
            'post' => 'posts'
        ]
    ]
];

SQL Server Configuration

$config = [
    'mssql_main' => [
        'driver' => 'sqlsrv',
        'host' => 'localhost',
        'port' => 1433,
        'database' => 'main_db',
        'username' => 'sa',
        'password' => 'mssql_pass',
        'charset' => 'utf8',
        'appname' => 'MyApp',
        'timeout' => 30,
        'encrypt' => true,
        'trust_server_certificate' => true,
        'prefix' => 'main_'
    ]
];

SQLite Configuration

$config = [
    'sqlite_main' => [
        'driver' => 'sqlite',
        'database' => '/path/to/database.sqlite',
        'prefix' => 'main_'
    ]
];

Basic Usage

Getting Connection

try {
    $connection = $manager->getConnection('mysql_main');

    if ($connection->isConnected()) {
        echo "Connected successfully";
    }
} catch (\Exception $e) {
    echo "Connection failed: " . $e->getMessage();
}

Closing Connections

// Close specific connection
$manager->closeConnection('mysql_main');

// Close all connections
$manager->closeAllConnections();

Multi-Database Management

Multi-Database Configuration

$config = [
    'main_db' => [
        'driver' => 'mysql',
        'host' => 'localhost',
        'database' => 'main_database',
        'username' => 'main_user',
        'password' => 'main_pass',
        'prefix' => 'main_'
    ],
    'analytics_db' => [
        'driver' => 'pgsql',
        'host' => 'analytics.example.com',
        'database' => 'analytics',
        'username' => 'analytics_user',
        'password' => 'analytics_pass',
        'prefix' => 'analytics_'
    ],
    'cache_db' => [
        'driver' => 'sqlite',
        'database' => '/tmp/cache.sqlite'
    ]
];

$manager = new ConnectionManager($config);

Using Multiple Databases

// Use main database
$mainConnection = $manager->getConnection('main_db');

// Use analytics database
$analyticsConnection = $manager->getConnection('analytics_db');

// Use cache database
$cacheConnection = $manager->getConnection('cache_db');

Driver Management

Registering Custom Drivers

use MyApp\Kotchasan\Database\CustomDriver;

// Register new driver
$manager->registerDriver('custom', CustomDriver::class);

// Use new driver in configuration
$config = [
    'custom_db' => [
        'driver' => 'custom',
        'host' => 'custom.example.com',
        'database' => 'custom_db'
    ]
];

Logger Management

Setting up Logger

use Kotchasan\Logger\FileLogger;

$logger = new FileLogger('/var/log/database');
$manager->setLogger($logger);

// Logger will be used by all connections
$connection = $manager->getConnection('main_db');

Getting Logger

$currentLogger = $manager->getLogger();

if ($currentLogger !== null) {
    echo "Logger is available";
}

Cache Management

Configuring Cache with Configuration

$cacheConfig = [
    'driver' => 'file',
    'path' => '/var/cache/queries'
];

$queryCache = $manager->configureCache($cacheConfig, 3600); // TTL 1 hour;

Setting Cache Directly

use Kotchasan\Cache\FileCache;

$cache = new FileCache('/var/cache/queries');
$queryCache = $manager->setCache($cache, 1800); // TTL 30 minutes;

Getting Query Cache

$queryCache = $manager->getQueryCache();

if ($queryCache !== null) {
    echo "Query Cache is available";
}

Table Management

Global Table Configuration

$config = [
    'main_db' => [
        'driver' => 'mysql',
        'host' => 'localhost',
        'database' => 'main_db',
        'username' => 'user',
        'password' => 'pass',
        'prefix' => 'app_'
    ],
    'tables' => [
        'user' => 'users',
        'category' => 'categories',
        'post' => 'posts',
        'comment' => 'comments'
    ]
];

$manager = new ConnectionManager($config);

Getting Table Configuration

$tableConfig = $manager->getTableConfiguration('main_db');

if ($tableConfig !== null) {
    $prefix = $tableConfig->getPrefix(); // 'app_'
    $tables = $tableConfig->getTables(); // ['user' => 'users', ...]
}

Advanced Usage Examples

Comprehensive Configuration

use Kotchasan\Connection\ConnectionManager;
use Kotchasan\Logger\FileLogger;
use Kotchasan\Cache\FileCache;

// Multi-database configuration
$config = [
    'main' => [
        'driver' => 'mysql',
        'host' => 'localhost',
        'database' => 'main_app',
        'username' => 'main_user',
        'password' => 'main_pass',
        'charset' => 'utf8mb4',
        'prefix' => 'main_'
    ],
    'analytics' => [
        'driver' => 'pgsql',
        'host' => 'analytics.server.com',
        'database' => 'analytics_db',
        'username' => 'analytics_user',
        'password' => 'analytics_pass',
        'schema' => 'public',
        'prefix' => 'analytics_'
    ],
    'cache' => [
        'driver' => 'sqlite',
        'database' => '/tmp/app_cache.sqlite'
    ],
    'tables' => [
        'user' => 'users',
        'category' => 'categories',
        'post' => 'posts',
        'log' => 'logs'
    ]
];

// Create ConnectionManager
$logger = new FileLogger('/var/log/app');
$manager = new ConnectionManager($config, $logger);

// Setup Cache
$cache = new FileCache('/var/cache/app');
$manager->setCache($cache, 3600);

// Use different connections
try {
    $mainDb = $manager->getConnection('main');
    $analyticsDb = $manager->getConnection('analytics');
    $cacheDb = $manager->getConnection('cache');

    echo "All databases connected successfully";

} catch (\Exception $e) {
    echo "Error occurred: " . $e->getMessage();
}

Connection Pool Management

class Kotchasan\DatabasePool
{
    private ConnectionManager $manager;
    private array $activeConnections = [];

    public function __construct(array $config)
    {
        $this->manager = new ConnectionManager($config);
    }

    public function getConnection(string $name): Connection
    {
        if (!isset($this->activeConnections[$name])) {
            $this->activeConnections[$name] = $this->manager->getConnection($name);
        }

        return $this->activeConnections[$name];
    }

    public function closeAll(): void
    {
        $this->manager->closeAllConnections();
        $this->activeConnections = [];
    }
}

Error Handling

Possible Exceptions

use Kotchasan\Exception\ConfigurationException;
use Kotchasan\Exception\DatabaseException;

try {
    $connection = $manager->getConnection('nonexistent');
} catch (ConfigurationException $e) {
    echo "Configuration error: " . $e->getMessage();
} catch (DatabaseException $e) {
    echo "Database error: " . $e->getMessage();
}

Configuration Validation

// Check for missing configuration
$config = [
    'main' => [
        'driver' => 'mysql',
        // Missing host, database, etc.
    ]
];

try {
    $manager = new ConnectionManager($config);
    $connection = $manager->getConnection('main');
} catch (ConfigurationException $e) {
    echo "Incomplete configuration: " . $e->getMessage();
}

Best Practices and Considerations

Best Practices

1. Use Logger: Set up logger to track issues
2. Manage Connection Pool: Reuse connections to save resources
3. Configure Cache: Use Query Cache for better performance
4. Close Connections: Close connections when not in use

Considerations

1. Credentials: Store credentials in secure configuration files
2. Memory Management: Connections use memory, close when not needed
3. Driver Configuration: Verify required drivers are available

Common Issues

1. Unsupported Driver: Check if PDO extension is installed
2. Wrong Configuration: Verify database configuration is correct
3. Connection Timeout: Set appropriate timeout values

Related Classes

• Connection - Basic connection class
• DriverInterface - Driver interface
• TableConfiguration - Table management
• QueryCache - Query caching