PDO DSN 動態構建方法：多數據庫支持完整實現

需求背景

封裝通用數據庫類，根據配置動態構建 PDO DSN 字符串，支持 MySQL, MSSQL, Oracle, SQLite, PostgreSQL, Firebird, ODBC 等多種數據庫。

完整實現代碼

<?php
class Database {
    private $hostname, $database, $username, $password, $hostport, $datatype;
    
    public function __construct($config) {
        $this->hostname = $config['host'] ?? 'localhost';
        $this->database = $config['dbname'] ?? '';
        $this->username = $config['user'] ?? '';
        $this->password = $config['pass'] ?? '';
        $this->hostport = $config['port'] ?? '';
        $this->datatype = $config['type'] ?? 'MYSQL';
    }
    
    private function setDSN() {
        switch (strtoupper($this->datatype)) {
            case 'MYSQL':
                $_DSN = "mysql:host={$this->hostname};dbname={$this->database}";
                if ($this->hostport) $_DSN .= ";port={$this->hostport}";
                break;
                
            case 'MSSQL':
            case 'DB2':
                $_DSN = "dblib:host={$this->hostname}:{$this->hostport};dbname={$this->database}";
                break;
                
            case 'ORACLE':
                // 方式一：TNS 字符串（推薦用於無 tnsnames.ora 環境）
                $_tns = "
                    (DESCRIPTION =
                        (ADDRESS_LIST =
                            (ADDRESS = (PROTOCOL = TCP)(HOST = {$this->hostname})(PORT = {$this->hostport}))
                        )
                        (CONNECT_DATA =
                            (SERVICE_NAME = {$this->database})
                        )
                    )";
                $_DSN = 'oci:dbname=' . $_tns;
                break;
                
            case 'SQLITE':
                // SQLite 使用文件路徑，非主機名
                $_DSN = 'sqlite:' . $this->hostname; // $this->hostname 實為文件路徑
                break;
                
            case 'PGSQL':
                $_DSN = "pgsql:host={$this->hostname};port={$this->hostport};dbname={$this->database}";
                break;
                
            case 'FIREBIRD':
                $_DSN = "firebird:dbname={$this->hostname}:{$this->database}";
                break;
                
            case 'ODBC':
                $_DSN = "odbc:DSN={$this->hostname};UID={$this->username};PWD={$this->password}";
                break;
                
            default:
                throw new Exception("Unsupported database type: {$this->datatype}");
        }
        return $_DSN;
    }
    
    public function connect() {
        try {
            $dsn = $this->setDSN();
            $pdo = new PDO($dsn, $this->username, $this->password);
            $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            return $pdo;
        } catch (PDOException $e) {
            throw new Exception("Connection failed: " . $e->getMessage());
        }
    }
}

// 使用示例
$mysql = new Database([
    'type' => 'MYSQL',
    'host' => 'localhost',
    'dbname' => 'myapp',
    'user' => 'root',
    'pass' => 'secret',
    'port' => '3306'
]);
$pdo = $mysql->connect();
?>

關鍵點補充

SQLite 特殊性

• 無「主機」概念，DSN 為文件路徑：sqlite:/path/to/db.sqlite
• 內存數據庫：sqlite::memory:
• SQLite2 格式：sqlite2:/path/to/db.sqlite

Oracle 連接細節

• 需安裝 Oracle Instant Client
• Linux: 設置 LD_LIBRARY_PATH；Windows: 設置 PATH 包含 client/lib
• 設置 TNS_ADMIN 指向 tnsnames.ora 目錄
• 簡化 DSN：oci:dbname=yoursid（需 tnsnames.ora 配置）

社區建議改進

1. Oracle 集群環境下，單一 hostname 不足，建議支持完整 TNS 配置文件路徑
2. SQLite 參數語義建議改為 database_file 避免混淆
3. 考慮添加 charset 參數（MySQL: charset=utf8mb4）
4. 異常處理區分連接失敗與 DSN 構建錯誤

現代替代方案

考慮使用成熟庫如 Doctrine DBAL 或 Laravel Database 組件，已完善處理各類數據庫 DSN 差異、連接池、語法平台差異等。