<?php

interface DbBaseInterface {

     public function __construct($descriptor='');
     public function connect($descriptor='');
     public function query($sqlStatement);
     public function fetchArray($resultQuery='', $opt='');
     public function close();
     public function numRows($resultQuery='');
     public function fieldName($position, $resultQuery='');
     public function dataSeek($position, $resultQuery='');
     public function affectedRows($resultQuery='');
     public function error($errorInfo='', $resultQuery='');
     public function noError($resultQuery='');
     public function inQuery($sqlStatement, $type=db::DB_BOTH);
     public function inQueryAssoc($sql);
     public function inQueryNum($sql);
     public function fetchOne($sql);
     public function fetchAll($sql);
     public function insert($tableName, $values, $fields='', $automaticQuotes=true);
     public function update($tableName, $fields, $values, $whereCondition=null, $automaticQuotes=true);
     public function delete($tableName, $whereCondition='');
     public function limit($sqlStatement, $number);
     public function forUpdate($sqlQuery);
     public function sharedLock($sqlQuery);
     public function begin();
     public function rollback();
     public function commit();
     public function listTables($schemaName='');
     public function describeTable($tableName, $schemaName='');
     public function getRequiredSequence($tableName='', $identityColumn='', $sequenceName='');
     public function lastInsertId($tableName='', $identityColumn='', $sequenceName='');
     public function createTable($tableName, $definition, $index=array(), $tableOptions=array());
     public function dropTable($tableName, $ifExists=false);
     public function tableExists($tableName, $schema='');
     public function getDateUsingFormat($date, $format='YYYY-MM-DD');
     public function getHaveAutoCommit();
     public function setIsolationLevel($isolationLevel);
     public function getCurrentDate();
     public function getLastResultQuery();
     public function getConnectionId();
     public function getDatabaseName();
     public function getUsername();
     public function getHostName();
     
}

//Conexión a Oracle
$connId = oci_connect("scott", "tiger", "//192.168.0.40/XE");

//Conexión a Informix
$connId = ifx_connect("stores@ol_srv1", "informix", "pass");

//Conexión en PostgreSQL
$connId = pg_connect("host=192.62.10.1 port=5432 dbname=bankdb user=dma password=2fe051871");

//LIMIT en SQLServer ó Sybase
SELECT * FROM (SELECT TOP 10 * FROM (SELECT TOP 10 customers.categoryId FROM customers) AS itable) AS otable

//LIMIT en MySQL
SELECT customers.categoryId FROM customers LIMIT 10

[development]
database.type = mysql
database.host = localhost
database.username = root
database.password = 
database.name = development_db

[production]
database.type = mysql
database.host = localhost
database.username = root
database.password =
database.name = production_db

[test]
database.type = mysql
database.host = localhost
database.username = root
database.password =
database.name = test_db

[production]
database.type = oracle
database.host = 127.0.0.1
database.username = scott
database.password = tiger
database.instance = XE
database.port = 1521
database.territory = spain
database.sort = spanish_m
database.comp = linguistic
database.charset = AL32UTF8

lib/x86/linux/ojdbc14.jar

[production]
database.layer = jdbc
database.type = oracle
database.driver = "oracle.jdbc.driver.OracleDriver"
database.dsn = "thin:@192.168.151.12:1521:XE"
database.username = scott
database.password = tiger

[production]
database.layer = "pdo"
database.type = mssql
database.dsn = "DRIVER={SQL Server};SERVER=SQLEXPRESS;DATABASE=test"

[production]
database.layer = "pdo"
database.type = mssql
database.dsn = "bankdb"

[production]
database.layer = "pdo"
database.type = mssql
database.dsn = "bankdb;UID=sa;PWD=mypass"

[production]
database.layer = "pdo"
database.type = mssql
database.dsn = "DRIVER={SQL Server};SERVER=SQLEXPRESS;DATABASE=test;UID=sa;PWD=pass

[production]
database.type = mysqli
database.host = localhost
database.username = root
database.password = my_password
database.name = production_db

[production]
database.type = mysql
database.host = localhost
database.username = root
database.password = my_password
database.name = production_db
database.port = 3306
database.autocommit = Off
database.compression = Off
database.ssl = Off
database.interactive = Off

[production]
database.type = mysqli
database.host = localhost
database.username = root
database.password = my_password
database.name = production_db

[production]
database.type = mysqli
database.host = localhost
database.socket = /tmp/mysql.sock
database.username = root
database.password = my_password
database.name = production_db
database.port = 3306
database.autocommit = Off
database.compression = Off
database.ssl = Off
database.interactive = Off
database.charset = utf8
database.key = "/home/user/key.pem"
database.cert = "/home/user/ca.crt"
database.ca = "/home/user/ca_file"
database.capath = "/home/user/capath"
database.cipher = "aes128-cbc,3des-cbc,blowfish-cbc,cast128-cbc,arcfour,aes192-cbc,aes256-cbc"

[development]
database.layer = "pdo"
database.type = informix
database.dsn = "host=127.0.0.1;service=9800;database=bankdb;server=ids_server; protocol=onsoctcp;EnableScrollableCursors=1"
database.username = informix
database.password = bypass

User informix
Group informix

export INFORMIXDIR=/opt/IBM/informix
export INFORMIXTMP=/opt/IBM/informix/tmp
export INFORMIXSERVER=ol_server
export INFORMIXSQLHOSTS=$INFORMIXDIR/etc/sqlhosts
export ONCONFIG=onconfig
export TERMCAP=/home/informix/etc/termcap
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/opt/IBM/informix/lib:/opt/IBM/informix/etc/lib:/opt/IBM/informix/lib/esql
export DBDATE=y4md
export DB_LOCALE=en_US.819
export SERVER_LOCALE=en_US.819
export CLIENT_LOCALE=en_US.819
export TERM=ansi

export PATH=$PATH:$HOME/bin:$INFORMIXDIR/bin

ol_server onsoctcp        localhost       informixserver

informixserver  9800/tcp # Informix Dynamic Server

[development]
database.layer = "pdo"
database.dsn = "data/company.db"
database.type = sqlite

<?php

$db = DbLoader::factory('MySQL', array(
     "host" => "localhost",
     "username" => "root",
     "password" => "mypass",
     "name" => "bankdb"
));

print_r($db->fetchAll("SELECT * FROM accounts"));

<?php

$db = DbLoader::factory('MySQL', array(
     "pdo" => true,               
     "host" => "localhost",
     "username" => "root",
     "password" => "mypass",
     "name" => "bankdb"
));

$db->fetchAll("SELECT * FROM account");

$descriptor = "mysql:host=localhost;username=root;password=mypass;name=bankdb";
$db = DbLoader::factoryFromDescriptor($descriptor);

<?php

$db = DbBase::rawConnect();
$db->setTracing(true);
$results1 = $db->fetchAll("SELECT * FROM customers");
$results2 = $db->fetchAll("SELECT * FROM customers WHERE status = 'Active'");
foreach($db->getTracedSQL() as $sqlStatement){
     echo $sqlStatement."\n";
}

<?php

$db = DbBase::rawConnect();
$db->setProfiling(true);

<?php

$db = DbBase::rawConnect();
$db->setProfiling(new MyProfiler());

interface DbProfileInterface {

     public function startProfile($sqlStatement);
     public function stopProfile();
     public function getNumberTotalStatements();
     public function getTotalElapsedSeconds();
     public function getProfiles();
     public function reset();
     public function getLastProfile();

}

<?php

try {
$db = DbLoader::factory('MySQL', array(
          "pdo" => true,
          "host" => "localhost",
          "username" => "root",
          "password" => "hea101",
          "name" => "bankdb"
     ));
               
}
catch(DbException $e){
     //No se pudo cargar el adaptador
}

[development]
database.host = 127.0.0.1
database.username = my_user
database.password = my_password
database.name = bankdb
database.type = mysql
database.tracing = true

<?php

$db = DbLoader::factory('Oracle', array(
     "host" => "192.168.2.140",
     "username" => "scott",
     "password" => "tiger",
     "instance" => "XE"
));
$result = $db->query("SELECT id, name FROM customer WHERE category_id = 1");
while($row = $db->fetchArray($result)){
     echo $row['name']."\n";
}

<?php

//Mostrar todos los clientes activos
$db = DbBase::rawConnect();
$resultset = $db->find("customers", "status = 'Active'");
foreach($resulset as $row){
     echo $row['name']."\n";
} 

<?php

$db = db::rawConnect();
$customers = $db->inQueryAssoc("SELECT id, name FROM customers ORDER BY id");
foreach($customers as $customer) {
     echo $customer['name']."\n";
} 

<?php

$db = db::rawConnect();
$customers = $db->inQueryNum("SELECT id, name FROM customers ORDER BY id");
foreach($customers as $customer) {
     echo $customer[1]."\n"; //Imprime el nombre
} 

<?php
$db = DbBase::rawConnect();
$customer = $db->fetchOne("SELECT * FROM customers WHERE id = 124");

<?php

$db = Db::rawConnect();
$result = $db->query("SELECT id, name FROM customer WHERE status = 'Active'");
echo "Hay ".$result->numRows($result)." clientes activos";

<?php

$db = DbLoader::factory('MySQL', array(
     "host" => "127.0.0.1",
     "username" => "root",
     "password" => "hea101",
     "name" => "bankdb"
));
$db->query("SELECT id, name FROM customer WHERE category_id = 1");
if($db->numRows()>10){
     //Empezar desde el 10 registro
     $db->dataSeek(10);
     while($row = $db->fetchArray()){
          echo $row['name']."\n";
     }          
}

<?php

$db = Db::rawConnect();
$db->query("SELECT id, name FROM customer WHERE status = 'A'");
$result = $db->getLastResultQuery();
while($row = $db->fetchArray()){
     echo $row['name']."\n";
}

<?php

$db = Db::rawConnect();
$sqlQuery = $db->limit("SELECT id, name FROM customer WHERE status = 'A'", 10);
$db->query($sqlQuery);
$result = $db->getLastResult();
while($row = $db->fetchArray()){
     echo $row['name']."\n";
}

<?php

$db = DbBase::rawConnect();

//Usando Quotes Manualmente
$values = array("'0044'", "'England'");
$fields = array("code", "name);
if($db->insert("countries", $values, $fields)){
     Flash::success("Se insertó correctamente el registro");
}

//Usando Quotes en forma automática
$values = array("0044", "England");
$fields = array("code", "name);
if($db->insert("countries", $values, $fields, true)){
     Flash::success("Se insertó correctamente el registro");
}

<?php
$db = DbBase::rawConnect();
$values = array("John Smith", new DbRawValue("current_date()"));
$fields = array('name', 'created_at');
if($db->insert("employees", $values, $fields, true)){
     Flash::success("Se insertó correctamente el registro");
}

<?php
$db = DbBase::rawConnect();

$fields = array("code", "name);
$values = array("'0044'", "'England'");
if($db->update("countries", $fields, $values)){
     Flash::success("Se actualizó correctamente el registro");
}

$fields = array("code", "name);
$values = array("0044", "England");
if($db->update("countries", $fields, $values, true)){
     Flash::success("Se actualizó correctamente el registro");
}

<?php

$db = DbBase::rawConnect();

//Eliminar todos los registros de la tabla customer
if($db->delete("customer")==true){
     Flash::success("Se eliminaron correctamente todos los registros");
}

//Eliminar usando condiciones
if($db->delete("customer", "status = 'Active'")==true){
     Flash::success("Se eliminaron correctamente los registros");
}

<?php

$db = Db::rawConnect();
$db->query("DELETE FROM customer WHERE status = 'I'");
echo "Filas borradas = ".$db->affectedRows();

<?php

$db = DbBase::rawConnect();

$db->begin();
$fields = array("name", "createdAt");

$values = array("John Smith", "2007-10-21");
if($db->insert("customer", $values, $fields, true)==false){
     $values = array("Darren Davison", "2007-12-02");
     if($db->insert("customer", $values, $fields, true)==false){
          $db->commit();
     } else {
          $db->rollback();
     }
} else {
     $db->rollback();
}

<?php

//Crear o obtener la última conexión creada
$db = DbBase::rawConnect();

//Crear una nueva conexión sin cambiar la conexión del Singleton
$db = DbBase::rawConnect(true);

//Crear una nueva conexión renovando la conexión del Singleton
$db = DbBase::rawConnect(true, true);

<?php

$db = new Db();
$db->connect("192.168.2.140", "scott", "tiger", "bankdb");
$result = $db->query("SELECT * FROM customer");
while($row = $db->fetchArray($result)){
     echo $row['name']."\n";
}

<?php

$db = DbBase::rawConnect();
$fields = $db->getFieldsFromTable("customer");

<?php

$db = DbLoader::factory('MySQL', array(
     'hostname' => 'localhost',
     'username' => 'root',
     'password' => '',
     'name' => 'test'
));
$db->createTable("example", array(
     "id" => array(
          "type" => DbMySQL::TYPE_INTEGER,
          "notNull" => true,
          "primary" => true,
          "auto" => true
),
     "nombre" => array(
          "type" => DbMySQL::TYPE_VARCHAR,
          "notNull" => true,
          "size" => 120
     ),
     "texto" => array(
          "type" => DbMySQL::TYPE_TEXT,
          "notNull" => true
     ),
     "cantidad" => array(
          "type" => DbMySQL::TYPE_INTEGER,
          "notNull" => true,
          "size" => 11
     ),
     "fecha" => array(
          "type" => DbMySQL::TYPE_DATETIME,
          "notNull" => true
     ),
     "fecha_at" => array(
          "type" => DbMySQL::TYPE_DATE
     ),
     "fecha_in" => array(
          "type" => DbMySQL::TYPE_DATE
     ),
     "estado" => array(
          "type" => DbMySQL::TYPE_CHAR ,
          "notNull" => true,
          "size" => 1
     )
), array(
     "ix1" => "nombre",
     "ix2" => array("fecha", "estado")
));

<?php

$db = Db::rawConnect();
$db->createTable("example", array(
     "id" => array(
          "type" => Db::TYPE_INTEGER,
          "notNull" => true,
          "primary" => true,
          "auto" => true
),
     "nombre" => array(
          "type" => Db::TYPE_VARCHAR,
          "notNull" => true,
          "size" => 120
     )
));

<?php

$db = DbBase::rawConnect();
$db->dropTable("customer");