Classe PDO multidriver

[Renato]

Estou desenvolvendo uma classe PDO que suporta multiplos drivers. Por enquanto ela suporta SQLSRV (mssql) e MYSQL.

Gostaria que me ajudassem a desenvolver para termos uma boa classe a disposição (:
Seria de grande ajuda se algum dono de hospedagem me informasse quais os ".so" (por exemplo pdo.so) dos drivers PDO para linux dos diferentes bancos de dados.

Segue os códigos existentes até o momento:

configs.php

<?php
$page = strtolower(basename( $_SERVER['REQUEST_URI'] ));
$current_page = strtolower(basename( __FILE__ ));
if($page == $current_page) {
    die("Access denied! You do not have permission to access this URL directly.");
}
/*
|--------------------------------------------------------------------------
| PDO configs for driver SQLSRV
|--------------------------------------------------------------------------
| This driver is used to provide a connection to MS-SQL database.
| This extension requires the Microsoft SQL Server 2012 Native Client ODBC 
| Driver to communicate with SQL Server. Access the following URL to 
| download the Microsoft SQL Server 2012 Native Client ODBC driver for x86:
| http://go.microsoft.com/fwlink/?LinkId=163712
*/
$pdo['configs']['sqlsrv']['host'] = 'localhost';
$pdo['configs']['sqlsrv']['port'] = '1433';
$pdo['configs']['sqlsrv']['user'] = 'sa';
$pdo['configs']['sqlsrv']['pass'] = '123';
$pdo['configs']['sqlsrv']['database'] = 'muonline';
$pdo['configs']['sqlsrv']['persistent'] = false;
$pdo['configs']['sqlsrv']['debug'] = false;

/*
|--------------------------------------------------------------------------
| PDO configs for driver MYSQL
|--------------------------------------------------------------------------
| This driver is used to provide a connection to MYSQL database.
*/
$pdo['configs']['mysql']['host'] = 'localhost';
$pdo['configs']['mysql']['port'] = '51741';
$pdo['configs']['mysql']['user'] = 'root';
$pdo['configs']['mysql']['pass'] = '';
$pdo['configs']['mysql']['database'] = 'test';
$pdo['configs']['mysql']['persistent'] = false;
$pdo['configs']['mysql']['debug'] = false;

PDODriver.php

<?php
$page = strtolower(basename( $_SERVER['REQUEST_URI'] ));
$current_page = strtolower(basename( __FILE__ ));
if($page == $current_page) {
    die("Access denied! You do not have permission to access this URL directly.");
}

$configs = 'configs/configs.php';
file_exists($configs) ? require_once $configs : die ($configs . ' not found!');

class PDODriver {

    protected $drivers = array();
    protected $driver;
    protected $host = 'localhost';
    protected $database;
    protected $user;
    protected $password = '';
    protected $port = '';
    protected $persistent = false;
    protected $dsn;
    protected $connection;
    private $query = null;
    private static $_instance;

    public static function getInstance() {
        if(!isset($_instance)) {
            self::$_instance = new self();
        }
        return self::$_instance;
    }
    
    public function __construct() {}
    
    public function __clone() {}
    
    public function start($driver) {
        global $pdo;
        try {                
            $this->drivers = array('sqlsrv'/*,'mssql'*/,'mysql');                
            $this->driver = strtolower($driver);
            $this->host = $pdo['configs'][$driver]['host'];
            $this->database = $pdo['configs'][$driver]['database'];
            $this->user = $pdo['configs'][$driver]['user'];
            $this->password = $pdo['configs'][$driver]['pass'];
            $this->port = $pdo['configs'][$driver]['port'];
            $this->persistent = $pdo['configs'][$driver]['persistent'];
            if(in_array($this->driver, $this->drivers)){
                switch($this->driver) {
                    /*
                    case 'mssql':
                        $this->dsn = 'mssql:host=' . $this->host . '';
                        if(!empty($this->port)) {
                            $this->dsn .= ',' . $this->port . '';
                        }
                        $this->dsn .= ';dbname=' . $this->database . '';
                    break; 
                     */
                    case 'sqlsrv':
                        $this->dsn = 'sqlsrv:database=' . $this->database . ';server=' . $this->host . '';
                        if(!empty($this->port)) {
                            $this->dsn .= ',' . $this->port . '';
                        }
                        $this->dsn .= ';';
                    break;
                    case 'mysql':
                        $this->dsn = 'mysql:dbname=' . $this->database . ';host=' . $this->host . ';';
                        if(!empty($this->port)) {
                            $this->dsn .= '' . $this->port . ';';
                        }
                    break;
                    default :
                        throw new \Exception('The connection driver entered is invalid..');
                    break;
                }
                if(self::checkExtension($driver)) {
                    self::connect();
                }
                else {
                    throw new \Exception('The extension for the specified driver does not exist on your server. The connection can not be established.');
                }
            }
        } 
        catch(\PDOException $e) {
            die("Error: ". $e->getMessage());
        }        
    }
    private function checkAvaliableDriverPDO() {
        $drivers = null;
        foreach(PDO::getAvailableDrivers() as $avaliable){
            $drivers .= $avaliable;
        }
        return $drivers;
    }
    
    private function checkExtension($driver) {
        if(!extension_loaded('pdo') and !strpos(self::checkAvaliableDriverPDO(),$driver)) {
            return false;
        }
        else {
            return true;
        }             
    }

    /*
    |--------------------------------------------------------------------------
    | PDODriver::connect
    |--------------------------------------------------------------------------
    | Description:
    | 
    | Creates a PDO instance to represent a connection to the requested 
    | database.
    | 
    | Parameters:
    | 
    | dsn
    | The Data Source Name, or DSN, contains the information required to connect
    | to the database.
    | 
    | username
    | The user name for the DSN string. This parameter is optional for some PDO 
    | drivers.
    | 
    | password
    | 
    | The password for the DSN string. This parameter is optional for some PDO 
    | drivers.
    | 
    | driver_options
    | 
    | A key=>value array of driver-specific connection options.
    | 
    | Return Values:
    | 
    | Returns a PDO object on success or FALSE on failure.
    */ 
    private function connect() {
        if(!isset($this->connection)) {
            $this->connection = new \PDO($this->dsn, $this->user, $this->password, array(PDO::ATTR_PERSISTENT => $this->persistent));
            $this->connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            //$this->connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);
            //$this->connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT);
            //$this->connection->setAttribute(PDO::SQLSRV_ATTR_DIRECT_QUERY => true);
            if(!$this->connection) {
                throw new \Exception('The connection can not be established due to an unknown error.'); 
            }
            else {
                return $this->connection;
            }
        }
        else {
            return $this->connection;
        }
    }
 
    /*
    |--------------------------------------------------------------------------
    | PDODriver::__destruct
    |--------------------------------------------------------------------------
    | Description:
    | 
    | Destroys the existing connection to the database and all variables related
    | to it.
    | 
    | Return Values:
    | 
    | Return TRUE.
    */       
    public function __destruct() {
        self::disconnect();
        foreach ($this as $key => $value) {
            unset($this->$key);
        }
        return true;
    }

    /*
    |--------------------------------------------------------------------------
    | PDODriver::disconnect
    |--------------------------------------------------------------------------
    | Description:
    | 
    | Destroys the existing connection to the database.
    | 
    | Return Values:
    | 
    | Return TRUE.
    */   
    private function disconnect() {
        $this->connection = null;
        unset($this->connection);
        return true;
    }
    
    /*
    |--------------------------------------------------------------------------
    | PDO::beginTransaction
    |--------------------------------------------------------------------------
    | Description:
    |
    | public bool PDO::beginTransaction ( void )
    |
    | Turns off autocommit mode. While autocommit mode is turned off, changes 
    | made to the database via the PDO object instance are not committed until 
    | you end the transaction by calling PDO::commit(). Calling PDO::rollBack() 
    | will roll back all changes to the database and return the connection to 
    | autocommit mode.
    | Some databases, including MySQL, automatically issue an implicit COMMIT 
    | when a database definition language (DDL) statement such as DROP TABLE 
    | or CREATE TABLE is issued within a transaction. The implicit COMMIT will 
    | prevent you from rolling back any other changes within the transaction 
    | boundary.
    |
    | Return Values:
    | 
    | Returns TRUE on success or FALSE on failure.
    */    
    public function beginTransaction() {
        return $this->connection->beginTransaction();
    }

    /*
    |--------------------------------------------------------------------------
    | PDO::commit
    |--------------------------------------------------------------------------
    | Description:
    | 
    | public bool PDO::commit ( void ) 
    | 
    | Commits a transaction, returning the database connection to autocommit 
    | mode until the next call to PDO::beginTransaction() starts a new transaction.
    | 
    | Return Values:
    | 
    | Returns TRUE on success or FALSE on failure.
    */
    public function commit() {
        return $this->connection->commit();
    }

    /*
    |--------------------------------------------------------------------------
    | PDO::rollBack
    |--------------------------------------------------------------------------
    | Description:
    |
    | public bool PDO::rollBack ( void )
    | 
    | Rolls back the current transaction, as initiated by 
    | PDO::beginTransaction(). A PDOException will be thrown if no transaction 
    | is active.
    | If the database was set to autocommit mode, this function will restore 
    | autocommit mode after it has rolled back the transaction. Some databases, 
    | including MySQL, automatically issue an implicit COMMIT when a database 
    | definition language (DDL) statement such as DROP TABLE or CREATE TABLE is
    | issued within a transaction. The implicit COMMIT will prevent you from 
    | rolling back any other changes within the transaction boundary.
    |
    | Return Values:
    |
    | Returns TRUE on success or FALSE on failure.
    */
    public function rollBack() {
        return $this->connection->rollBack();
    }
    
    /*
    |--------------------------------------------------------------------------
    | PDO::errorInfo
    |--------------------------------------------------------------------------
    | Description:
    | 
    | public array PDO::errorInfo ( void )
    |  
    | Fetch extended error information associated with the last operation on the
    | database handle
    | 
    | Return Values:
    |
    | returns an array of error information about the last operation performed 
    | by this database handle. The array consists of the following fields:
    | 
    | Element    Information
    | 0         SQLSTATE error code (a five characters alphanumeric identifier 
    |           defined in the ANSI SQL standard).
    | 1         Driver-specific error code.
    | 2         Driver-specific error message.
    |  
    | Note:
    | If the SQLSTATE error code is not set or there is no driver-specific 
    | error, the elements following element 0 will be set to NULL.
    |  
    | PDO::errorInfo() only retrieves error information for operations performed
    | directly on the database handle. If you create a PDOStatement object 
    | through PDO::prepare() or PDO::query() and invoke an error on the 
    | statement handle, PDO::errorInfo() will not reflect the error from the 
    | statement handle. You must call PDOStatement::errorInfo() to return the 
    | error information for an operation performed on a particular statement 
    | handle.
    */
    public function errorInfo() {
        return $this->connection->errorInfo();
    }
    
    /*
    |--------------------------------------------------------------------------
    | PDO::errorCode
    |--------------------------------------------------------------------------
    | Description:
    | 
    | public mixed PDO::errorCode ( void )
    | 
    | Fetch the SQLSTATE associated with the last operation on the database 
    | handle.
    | 
    | Return Values:
    | 
    | Returns an SQLSTATE, a five characters alphanumeric identifier defined in 
    | the ANSI SQL-92 standard. Briefly, an SQLSTATE consists of a two 
    | characters class value followed by a three characters subclass value. A 
    | class value of 01 indicates a warning and is accompanied by a return code
    | of SQL_SUCCESS_WITH_INFO. Class values other than '01', except for the 
    | class 'IM', indicate an error. The class 'IM' is specific to warnings and
    | errors that derive from the implementation of PDO (or perhaps ODBC, if 
    | you're using the ODBC driver) itself. The subclass value '000' in any 
    | class indicates that there is no subclass for that SQLSTATE.
    | PDO::errorCode() only retrieves error codes for operations performed 
    | directly on the database handle. If you create a PDOStatement object 
    | through PDO::prepare() or PDO::query() and invoke an error on the 
    | statement handle, PDO::errorCode() will not reflect that error. You must 
    | call PDOStatement::errorCode() to return the error code for an operation 
    | performed on a particular statement handle.
    | Returns NULL if no operation has been run on the database handle.
    */
    public function errorCode() {
        return $this->connection->errorCode();
    }
    
    /*
    |--------------------------------------------------------------------------
    | PDO::inTransaction
    |--------------------------------------------------------------------------
    | Description:
    | 
    | Checks if a transaction is currently active within the driver. 
    | This method only works for database drivers that support transactions.
    |
    | Return Values:
    |
    | Returns TRUE if a transaction is currently active, and FALSE if not.
    */
    public function inTransaction() {
        return $this->connection->inTransaction();
    }

    private function getPDOConstantType($var) {
        if(is_int($var)) {
            return PDO::PARAM_INT;
        }
        if(is_bool($var)) {
            return PDO::PARAM_BOOL;
        }
        if(is_null($var)) {
            return PDO::PARAM_NULL;
        }
        else {
            return PDO::PARAM_STR;
        }
    }
    
    /*Example usage
     * $db = new PDODriver();
     * $db->start('mysql');
     * $db->update()
     * ->table('character')
     * ->set(array('resets' => 'resets+10','mr' => ':mr'))
     * ->where('name', '=');
     * $bind = array(':mr' => '18', ':name' => 'admin');
     * $db->execute($bind);
     */  
    public function execute($bind) {
        $statement = $this->connection->prepare($this->query);
        $field = array_keys($bind);
        $value = array_values($bind);
        for($i = 0; $i < count($field); $i++) {
            $statement->bindValue($field[$i], $value[$i], self::getPDOConstantType($value[$i]));
        }
        return $statement->execute();
    }
    
    /*Example usage
     * $db = new PDODriver();
     * $db->start('mysql');
     * $db->update()
     */   
    public function update() {
        $this->query = 'UPDATE ';
        return $this;
    }
    
    /*Example usage
     * $db = new PDODriver();
     * $db->start('mysql');
     * $db->update()
     * ->table('character')
     */   
    public function table($tb = '') {
        if($this->driver == 'mysql') {
            $this->query .= '`'.$tb.'`';
        }
        else {
            $this->query .= $tb;
        }
        return $this;
    }
    
    /*Example usage
     * $db = new PDODriver();
     * $db->start('mysql');
     * $db->update()
     * ->table('character')
     * ->set(array('resets' => 'resets+10','mr' => ':mr'))
     */
    public function set($iCol='') {
        $Colluns = null;
        $quote = null;
        if($iCol != null and is_array($iCol)) {
            if($this->driver == 'mysql') {
                foreach($iCol AS $key => $value){
                    $Colluns .= $quote . "`" . $key . "` = " . $value;
                    $quote = ", ";                     
                } 
                               
                $this->query .= " SET ".$Colluns." ";   
                //die(var_dump($this->query));           
            }
            else {
                foreach($iCol AS $key){                    
                    $Colluns .= $quote . $key . " = :" . $key;
                    $quote = ", ";                     
                }
                $this->query .= " SET ".$Colluns." ";
            }
            return $this;  
        }
        else {
            $this->query .= null;
            return $this;
        }
    }
    
    /*Example usage
     * $db = new PDODriver();
     * $db->start('mysql');
     * $db->update()
     * ->table('character')
     * ->set(array('resets' => 'resets+10','mr' => ':mr'))
     * ->where('name', '=');
     */   
    public function where($key, $condition_type) {
        if($this->driver == 'mysql') {
            $this->query .= "WHERE `". $key ."` " . $condition_type . " :". $key ."";
        }
        else {
            $this->query .= "WHERE ". $key . $condition_type ." :". $key ."";
        }
        return $this;
    }

    /*Example usage
     * $db = new PDODriver();
     * $db->start('mysql');
     * $db->update()
     * ->table('character')
     * ->set(array('resets' => 'resets+10','mr' => ':mr'))
     * ->where('name', '=')
     * ->andWhere('resets','>');
     * $bind = array(':mr' => '18', ':name' => 'admin', ':resets'=>'50');
     * $db->execute($bind);
     */   
    public function andWhere($key, $condition_type) {
        if($this->driver == 'mysql') {
            $this->query .= "AND `". $key ."` " . $condition_type . " :". $key ."";
        }
        else {
            $this->query .= "AND ". $key . $condition_type ." :". $key ."";
        }
        return $this;
    }

    /*Example usage
     * $db = new PDODriver();
     * $db->start('mysql');
     * $db->update()
     * ->table('character')
     * ->set(array('resets' => 'resets+10','mr' => ':mr'))
     * ->where('name', '=')
     * ->orWhere('resets','>');
     * $bind = array(':mr' => '18', ':name' => 'admin', ':resets'=>'50');
     * $db->execute($bind);
     */   
    public function orWhere($key, $condition_type) {
        if($this->driver == 'mysql') {
            $this->query .= "OR `". $key ."` " . $condition_type . " :". $key ."";
        }
        else {
            $this->query .= "OR ". $key . $condition_type ." :". $key ."";
        }
        return $this;
    }
 
}

index.php:

<?php
$PDODriver = 'library/database/PDODriver.php';
file_exists($PDODriver) ? require_once($PDODriver) : die($PDODriver . 'not found!');

$db = new PDODriver();
$db->start('mysql');
$db->update()
->table('character')
->set(array('resets' => 'resets+10','mr' => ':mr'))
->where('name', '=');
$bind = array(':mr' => '18', ':name' => 'admin');
$execute = $db->execute($bind);
if($execute) {
    die('tudo ok.');
}
else {
    die('erro.');
}

[SHEIK]

Biba se isso ajuda....

[Somente usuários registrados podem vem os links. ]

[Renato]

Eu já vi isso ai leo, mas ai só tem o nome do driver. Eu preciso do nome da DLL (windows) ou SO (LINUX) pra tentar dar load nela pela função dl (se o apache tiver com Thread Safety ativo).

[SHEIK]

São essas ai meu querido, adicione no final de cada drive o .so.

Para Windows, este aqui ja explica a instalação : [Somente usuários registrados podem vem os links. ]

Para Linux, você pode usar o FreeTDS com o uso da PDO_DBLIB.so.

[Renato]

Não é tão simples assim não Leo. Por exemplo o driver sqlsrv, pra windows tem vários tipos de dll:

PHP 5.4 com Thread Safety on: php_pdo_sqlsrv_54_ts.dll
PHP 5.4 com Thread Safety off: php_pdo_sqlsrv_54_nts.dll

PHP 5.3 com Thread Safety on e DLL: php_pdo_sqlsrv_53_ts_vc6.dll ou php_pdo_sqlsrv_53_ts_vc9.dll ou php_pdo_sqlsrv_53_ts.dll
PHP 5.3 com Thread Safety off: php_pdo_sqlsrv_53_nts_vc6.dll ou php_pdo_sqlsrv_53_nts_vc9.dll ou php_pdo_sqlsrv_53_nts.dll

PHP 5.2 com Thread Safety on: php_pdo_sqlsrv_52_ts_vc6.dll ou php_pdo_sqlsrv_52_ts_vc9.dll
PHP 5.2 com Thread Safety off: php_pdo_sqlsrv_52_nts_vc6.dll ou php_pdo_sqlsrv_52_nts_vc9.dll

depende até da versão do Visual Studio usado pra compilar a DLL e também da DLL usada pro PHP: php5.dll (sem Thread Safety ou php5ts.dll com Thread Safety).

Lembrando que a classe vai suportar vários tipos de driver: mysql, sqlsrv, mssql, pgsql, sqlite, odbc, dblib...

[Ayrton Ricardo]

Dica 1:

//Esta condicional:

if(isset($this->port)) {
           $this->dsn .= ',' . $this->port . '';
}

// deveria ser assim:

if(!empty($this->port)) {
           $this->dsn .= ',' . $this->port . '';
}

//Motivo, quando você declara lá: " protected $port = ''; "
//Isto já esta setado, então o isset já dará true mesmo que port esteja em branco. 

Extensões(não entendi o motivo de ter as extensões, mas de boa :v )

Informação

Linux: extension=pdo.so extension=pdo_mysql.so extension=pdo_pgsql.so extension=pdo_sqlite.so Windows: extension=php_pdo.dll extension=php_pdo_mysql.dll extension=php_pdo_pgsql.dll extension=php_pdo_sqlite.dll Há ainda o PDO_DBLIB que é driver MSSQL SERVER experimental para php que já funciona em determinadas circunstâncias(se habilitado). [Somente usuários registrados podem vem os links. ] E ainda: [Somente usuários registrados podem vem os links. ]

Verifica quais drivers estão avaliados para serem usados:

<?php

foreach (PDO::getAvailableDrivers() as $driver) {
    $this->driversAvailable[] = $driver;//Armazena em algum array pra comparar depois sei la :v, só fiz de exemplo mesmo...
    echo $driver.'<br />';
}

?>

Para quem não sabe o que é um transaction:

Transaction em sql é uma transação(avá), mas o porque de existir? O porque é que quando você abre uma transação para determinado campo de um banco/tabela, ninguém além daquela transaction pode editar aquele campo, e todas as modificações que você fizer naquele campo, só serão de fato feitas, quando você disser "Pronto cara, terminei, pode concluir a transação" (commit).
Ex:
Eu dou um BeginTransaction();
ai um outro cara resolve dar update no campo em questão, não vai conseguir até que você de o commit, caso seu servidor desligue e não seja dado nem rollback, nem commit, por algum motivo, a transação vai ficar lá, e será preciso fechar o processo que está com a transação aberta.

Mas pra que isso existe?
Pra garantir o que você vai editar seja exatamente o que você queira que seja.

E só pra completa, se o outro cara do exemplo acima der um update, será pego o valor que foi editado por você ao dar o commit, mas caso o outro cara faça select antes da sua transação acabar, os dados ainda serão os anteriores a transação. Espero que tenha conseguido me fazer entender.



At.te
Ayrton Ricardo.

[Renato]

Com relação a dica, é verdade, não tinha me atentado pra esse detalhe.
Já com relação aos drivers, eu fiz uma função pra em retornar quais drivers já estão ativos Ayrton. Dá uma sacada lá: checkAvaliableDriverPDO()

Tipo, eu quero saber quais as dll's e ".so" pra, caso as extensões não estejam ativadas (;extension=...) no apache, mas que seja um apache com Thread Safety on, eu possa tentar dar um load nela pelo metodo "dl()" pra tentar fazer a conexão...