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
PDODriver.phpCódigo 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;
index.php:Código 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;
}
}
Código 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.');
}
