Aplicativos em PHP/Abstrações de Bancos de Dados/AdoDB
Introdução
ADOdb (Active Data Objects DataBase) é uma abstração de bancos de dados para PHP. Também oferece uma versão para Python.
Atualmente suporta diversos SGBDs: MySQL, Oracle, Microsoft SQL Server, Sybase, Sybase SQL Anywhere, Informix, PostgreSQL, FrontBase, Interbase (Firebird and Borland variants), Foxpro, Access, ADO e ODBC.
Via ODBC podemos também conectar com diversos SGBDs: Progress, SQLite, DB2 e outros.
É uma das mais ricas e eficientes abstrações existentes para PHP atualmente.
Caso não se utilize uma abstração no código, ao migrar para outro SGBD o código sofrerá diversas alterações e além disso exigirá o conhecimento das funções de ambos os SGBDs, mas utilizando algo como ADOdb a alteração será apenas nos dados da conexão sem necessidade de conhecer as funções do PHP com os SGBDs.
Exemplo prático para mostrar alguns recursos da ADOdb
Site oficial - http://php.weblogs.com/ADODB
Download - http://adodb.sourceforge.net/#download
Documentações - http://adodb.sourceforge.net/#docs
Este tutorial teve como base os tutoriais do site oficial e também o ótimo tutorial:
PHP Application Development With ADODB
http://www.devshed.com/c/a/PHP/PHP-Application-Development-With-ADODB-part-1/
Banco de Dados para o exemplo: dbbiblioteca
create database dbbiblioteca (com o postgresql mas para usar outro SGBD basta trocar os dados)
Banco - testes
Tabela - biblioteca
CREATE TABLE biblioteca ( id serial NOT NULL PRIMARY KEY, titulo character(100), autor character(45), data date ); INSERT INTO biblioteca (id, titulo, autor, data) VALUES (1, 'Os Sertões', 'Euclides da Cunha','2005-12-25'); INSERT INTO biblioteca (id, titulo, autor, data) VALUES (2, 'Os Lusíadas', 'Camões','2005-12-25'); INSERT INTO biblioteca (id, titulo, autor, data) VALUES (3, 'A Divina Comédia', 'Dante','2005-12-25'); INSERT INTO biblioteca (id, titulo, autor, data) VALUES (4, 'Contos', 'Voltaire','2005-12-25'); INSERT INTO biblioteca (id, titulo, autor, data) VALUES (5, 'O Conde de Monte Cristo', 'Alexandre Dumas Pai','2005-12-25');
Fazer o download e descompactar numa pasta do seu DocumentRoot. Neste exemplo descompactei no raiz.
Alguns aliases para a conexão
MySQL - mysql
PostgreSQL - postgres
Interbase/Firebird - ibase
SQLite - sqlite
Vamos agora criar alguns scripts PHP para acessar o banco usando ADOdb.
Esta parte inicial se repetirá em todos os scripts, portanto apenas farei a citação de "inicial" nos demais scripts
inicial
<?php // Descomentar a linha abaixo para visualizar como plaintext no browser // header("Content-Type: text/plain"); // include the ADODB library include("includes\adodb5\adodb.inc.php"); // create an object instance // Configurar para uma conexão tipo PostgreSQL $db = NewADOConnection("mysql"); // MySQL seria "mysql" // Abrir uma conexão com o banco de dados // $db->Connect("servidor", "usuario", "senha", "banco") $db->Connect("localhost", "root", "", "testes") or die("Falha na conexão!"); //final do inicial echo "<h2>executar a consulta</h2>"; $query = "SELECT * FROM biblioteca"; $result = $db->Execute($query) or die("Erro na consulta: $query. " . $db->ErrorMsg()); // Iteração através do resultset // imprimir dados em colunas no formato TÍTULO - AUTOR while (!$result->EOF){ echo $result->fields[1] . " - " . $result->fields[2] . "<br>"; $result->MoveNext(); // Veja que função útil, como também o EOF // (End Of File, enquanto não chegar ao final) } echo "<h2>receber e imprimir o número de registros do resultset com muita simplicidade</h2>"; echo "<br>[" . $result->RecordCount() . " registros retornados]<br>"; echo "<h2>Fechar a conexão com o banco</h2>"; $db->Close(); ?>
ADODB também oferece um número de métodos alternativos para processar um resultset. Por exemplo, você pode receber o resultset como um array associativo indexado de string, onde as chaves são nomes de campos e os valores os correspondentes valores dos campos.
Um exemplo:
inicial - (aqui cole o trecho inicial) referido acima
// get resultset as associative array $ADODB_FETCH_MODE = ADODB_FETCH_ASSOC; // execute query $query = "SELECT * FROM biblioteca"; $result = $db->Execute($query) or die("Error in query: $query. " . $db->ErrorMsg()); // iterate through resultset // print column data in format TITLE - AUTHOR while (!$result->EOF) { echo $result->fields['titulo'] . " - " . $result->fields['autor'] . "<br>"; $result->MoveNext(); } // get and print number of rows in resultset echo "<br>[" . $result->RecordCount() . " registros retornados]<br>"; // close database connection $db->Close(); ?>
Você pode usar o método GetAll() no lugar do Execute() que retorna o resultset completo com um array bidimensional de pares campo-valor. Este array pode então ser processado com um simples "foreach" ou um loop "for".
Um exemplo:
inicial
// execute query $query = "SELECT * FROM biblioteca"; $result = $db->GetAll($query) or die("Error in query: $query. " . $db->ErrorMsg()); // clean up $db->Close(); // uncomment the following line to see the returned array. // print_r($result); // iterate through resultset // print column data in format TITLE - AUTHOR foreach ($result as $row) { echo $row[1] . " - " . $row[2] . "<br>"; } // get and print number of rows in resultset echo "<br>[" . sizeof($result) . " registros retornados]<br>"; ?>
Exemplo com as funções RecordCount() e FieldCount()
inicial
// execute query $query = "SELECT * FROM biblioteca"; $result = $db->Execute($query) or die("Error in query: $query. " . $db->ErrorMsg()); // get and print number of rows in resultset echo $result->RecordCount() . " registros retornados<br>"; // get and print number of fields in resultset echo $result->FieldCount() . " campos retornados<br>"; // clean up $db->Close(); ?>
Podemos obter informações sobre cada campo com o método FetchField(), que retorna um objeto contendo informações detalhadas sobre as propriedades dos campos, incluindo seus nomes e tipos. Um exemplo:
inicial
// execute query $query = "SELECT * FROM biblioteca"; $result = $db->Execute($query) or die("Error in query: $query. " . $db->ErrorMsg()); // get field information for($x=0; $x<$result->FieldCount(); $x++) { print_r($result->FetchField($x)); } // clean up $db->Close(); ?>
Para a execução de consultas INSERT em tabela contendo chave primária com auto-incremento podemos obter o último ID gerado do auto-incremento usando o método Insert_ID(). Exemplo:
inicial
// execute query $titulo = $db->qstr("It's Not Me, It's You!"); $autor = $db->qstr("J. Luser"); $query = "INSERT INTO biblioteca (titulo, autor) VALUES ($titulo, $autor)"; $result = $db->Execute($query) or die("Erro na consulta: $query. " . $db->ErrorMsg()); // print auto-generated ID if ($result) { echo "O último ID inserido foi " . $db->Insert_ID(); } // clean up $db->Close(); ?>
Obs.: o método qstr() é usado para "escapar" caracteres especiais em consultas com strings.
Quando estamos utilizando consultas que afetam registros de tabelas, como insert, delete ou update o método Affected_Rows() retorna o número de registros afetados.
inicial
// execute query $query = "DELETE FROM biblioteca WHERE author = 'Euclides da Cunha'"; $result = $db->Execute($query) or die("Erro na consulta: $query. " . $db->ErrorMsg()); // return number of affected rows if ($result) { echo $db->Affected_Rows() . " registros excluídos"; } // clean up $db->Close(); ?>
Restringir o número de registros recebidos podemos usar o método SelectLimit()
inicial
// execute query // receber 2 registros, iniciando do terceiro, ou seja, o terceiro e o quarto registros $query = "SELECT * FROM biblioteca"; $result = $db->SelectLimit($query, 2, 3) or die("Erro na consulta: $query. " . $db->ErrorMsg()); // iterate through resultset while (!$result->EOF) { echo $result->fields[1] . " - " . $result->fields[2] . "<br>"; $result->MoveNext(); } // clean up $db->Close(); ?>
Alerta: Cuidado com o copiar e colar. Este exemplo acima e vários outros acusaram erro ao executar.
Normalmente o erro era na linha (echo $result->fields[1] . " - " . $result->fields[2] . "
";).
O original estava no Write do OpenOffice. Normalmente apenas excluo os espaços antes do echo e tá resolvido ou então redigito apenas a linha do erro.
Algumas vezes precisei remover alguns espaços após o ponto e vírgula.
Obter uma lista dos bancos e tabelas do SGBD
Através dos métodos MetaDatabases() e MetaTables().
inicial
// get database list echo "Bancos:<br>"; foreach($db->MetaDatabases() as $d){ echo "=> $d<br>"; } // get table list echo "<br>Tabelas no banco atual:<br>"; foreach($db->MetaTables() as $table) { echo "=> $table<br>"; } // clean up $db->Close(); ?>
Quando precisamos executar uma consulta várias vezes
Como por exemplo diversos INSERTs. O ADOdb conta com diversos recursos úteis. Vejamos:
inicial
// prepara a consulta e a deixa em "banho maria", sem a executar ainda $query = $db->Prepare("INSERT INTO biblioteca (id, titulo, autor) VALUES (?, ?, ?)"); // ler lista titulo-autor do arquivo CSV $data = file("lista.txt"); // iteração através de cada linha do arquivo foreach ($data as $l){ // separa com vírgulas $arr = explode(",", $l); // insere os valores na consulta preparada anteriormente $result = $db->Execute($query, array($arr[0], '$arr[1]', '$arr[2]')) or die("Erro na consulta: $query. " . $db->ErrorMsg()); } // clean up $db->Close; ?>
Atentar para o fato de que o prepare apenas deixa a consulta pronta e o execute finaliza a execução.
Isto melhora muito o desempenho quando temos muitas consultas a realizar.
Uso de transações
Caso o SGBD tenha suporte:
inicial
// turn off auto-commit // begin transaction block $db->BeginTrans(); // first query $query = "INSERT INTO biblioteca (titulo, autor) VALUES ('Titulo A', 'Autor B')"; $result = $db->Execute($query) or die("Erro na consulta: $query. " . $db->ErrorMsg()); // use ID from first query in second query if ($result) { $id = $db->Insert_ID(); $query = "INSERT INTO purchase_info (id, price) VALUES ($id, 'USD 39.99')"; $result = $db->Execute($query) or die("Error in query: $query. " . $db->ErrorMsg()); } // if no failures if ($result) { // commit $db->CommitTrans(); } // else rollback else { $db->RollbackTrans(); } // clean up $db->Close; ?>
Cache de Consultas
ADOdb tembém oferece suporte para cache de consultas. Que oferece um excelente ganho de desempenho, principalmente nos casos em que precisamos executar uma mesma consulta diversas vezes.
Para ver a diferença abaixo temos uma consulta normal:
inicial
// execute query $query = "SELECT * FROM biblioteca"; $result = $db->Execute($query) or die("Erroo na consulta: $query. " . $db->ErrorMsg()); // iterate through resultset // print column data in format TITLE - AUTHOR while (!$result->EOF) { echo $result->fields[1] . " - " . $result->fields[2] . "<br>"; $result->MoveNext(); } // get and print number of rows in resultset echo "<br>[" . $result->RecordCount() . " registros retornados]<br>"; // close database connection $db->Close(); ?>
Agora usando o cache de consulta:
inicial
// execute query $query = "SELECT * FROM biblioteca"; $result = $db->CacheExecute(300,$query) or die("Erro na consulta: $query. " . $db->ErrorMsg()); // iterate through resultset // print column data in format TITLE - AUTHOR while (!$result->EOF) { echo $result->fields[1] . " - " . $result->fields[2] . "<br>"; $result->MoveNext(); } // get and print number of rows in resultset echo "<br>[" . $result->RecordCount() . " registros retornados]<br>"; // close database connection $db->Close(); ?>
Algo que dá um bom trabalho é criar um select que seja populado de uma tabela. O menu drop-down. O ADOdb tem um método especificamente para esta finalidade.
<html> <head></head> <body> <?php // include the ADODB library include("adodb/adodb.inc.php"); // create an object instance // configure it for a PostgreSQL connection $db = NewADOConnection("postgres"); // open connection to database $db->Connect("localhost", "postgres", "postabir", "dbbiblioteca") or die("Unable to connect!"); // execute query $query = "SELECT titulo, id FROM biblioteca"; // O primeiro será exibido e o segundo armazenará o resultado $result = $db->Execute($query) or die("Erro na consulta: $query. " . $db->ErrorMsg()); // print HTML menu print $result->GetMenu("biblioteca", '', false); // primeiro parâmetro é o nome do select // close database connection $db->Close(); ?> </body> </html>
Exportar um resultset para diversos formatos
- texto separado por vírgula
- texto separado por tabulação
- tabela HTML
Estes recursos não fazem parte da classe ADODB, portanto precisaremos importar outras classes.
<?php // uncomment this to see plaintext output in your browser // header("Content-Type: text/plain"); // include the ADODB library include("adodb/adodb.inc.php"); // include conversion functions include("adodb/toexport.inc.php"); // create an object instance // configure library for a PostgreSQL connection $db = NewADOConnection("postgres"); // open connection to database $db->Connect("localhost", "postgres", "postabir", "dbbiblioteca") or die("Unable to connect!"); // execute query $query = "SELECT title, id FROM library"; $result = $db->Execute($query) or die("Error in query: $query. " . $db->ErrorMsg()); // return a CSV string echo rs2csv($result); // close database connection $db->Close(); ?>
Podemos suprimir a primeira linha (nomes dos campos), adicionando um parâmetro para a chamada de rs2csv():
// return a CSV string
echo rs2csv($result, false);
Formatando com separador tab: Apenas troque a função (método) de rs2csv() para rs2tab().
Formatando a saída para tabela HTML: Para esta precisamos de outro importe, confira:
<html> <head></head> <body> <?php // uncomment this to see plaintext output in your browser // header("Content-Type: text/plain"); // include the ADODB library include("adodb/adodb.inc.php"); // include conversion functions include("adodb/tohtml.inc.php"); // create an object instance // configure it for a PostgreSQL connection $db = NewADOConnection("postgres"); // open connection to database $db->Connect("localhost", "postgres", "postabir", "dbbiblioteca") or die("Unable to connect!"); // execute query $query = "SELECT titulo, id FROM biblioteca"; $result = $db->Execute($query) or die("Erro na consulta: $query. " . $db->ErrorMsg()); // return a table echo rs2html($result); // close database connection $db->Close(); ?> </body> </html>
Formatando Data de Saída
<?php // Select a table, display the first two columns. // If the second column is a date or timestamp, reformat the date to Brazilian d/m/Y. // uncomment this to see plaintext output in your browser // header("Content-Type: text/plain"); // include the ADODB library include("adodb/adodb.inc.php"); // create an object instance // configure library for a PostgreSQL connection $db = NewADOConnection("postgres"); // Antes alterar a tabela adicionando o campo data: // \c dbbiblioteca // ALTER TABLE biblioteca ADD COLUMN data date; // open connection to database $db->Connect("localhost", "postgres", "postabir", "dbbiblioteca") or die("Unable to connect!"); $recordSet = &$db->Execute('select id,data from biblioteca'); if (!$recordSet) print $db->ErrorMsg(); else while (!$recordSet->EOF) { $fld = $recordSet->FetchField(1); $type = $recordSet->MetaType($fld->type); if ( $type == 'D' || $type == 'T') print $recordSet->fields[0].' '. $recordSet->UserDate($recordSet->fields[1],'d/m/Y').'<BR>'; else print $recordSet->fields[0].' '.$recordSet->fields[1].'<BR>'; $recordSet->MoveNext(); } $recordSet->Close(); # optional $db->Close(); # optional ?>
Exporting in CSV or Tab-Delimited Format
<?php // Exporting in CSV or Tab-Delimited Format // uncomment this to see plaintext output in your browser // header("Content-Type: text/plain"); include_once('adodb/toexport.inc.php'); // include the ADODB library include("adodb/adodb.inc.php"); // create an object instance // configure library for a PostgreSQL connection $db = NewADOConnection("postgres"); // open connection to database $db->Connect("localhost", "postgres", "postabir", "dbbiblioteca") or die("Unable to connect!"); $rs = $db->Execute('select titulo as "Título", autor as "Autor" from biblioteca'); print "<pre>"; print rs2csv($rs); # return a string, CSV format print '<hr>'; $rs->MoveFirst(); # note que alguns databases não suportam MoveFirst print rs2tab($rs,false); # return a string, tab-delimited # false == suppress field names in first line print '<hr>'; $rs->MoveFirst(); rs2tabout($rs); # send to stdout directly (there is also an rs2csvout function) print "
";
$rs->MoveFirst(); $fp = fopen($path, "w"); if ($fp) {
rs2csvfile($rs, $fp); # write to file (there is also an rs2tabfile function) fclose($fp);
}
?>
INSERT com Arquivo SCV
<?php // uncomment this to see plaintext output in your browser // header("Content-Type: text/plain"); // include the ADODB library include("adodb/adodb.inc.php"); // create an object instance // configure library for a PostgreSQL connection $db = NewADOConnection("postgres"); // open connection to database $db->Connect("localhost", "postgres", "postabir", "dbbiblioteca") or die("Unable to connect!"); // prepare query $query = $db->Prepare("INSERT INTO biblioteca (id, titulo, autor) VALUES (?, ?)"); // read title-author list in from CSV file $data = file("lista.txt"); // iterate through each line in file foreach ($data as $l){ // split on comma $arr = explode(",", $l); // insert values into prepared query $result = $db->Execute($query, array($arr[0], $arr[1])) or die("Error in query: $query. " . $db->ErrorMsg()); } // clean up $db->Close; echo "Arquivo CSV inserido com sucesso!"; ?>
Arquivo lista.txt
Sandálias do Pescador, Morris West Ana Karenina, Leon Tolstoi Terras do Sem Fim, Jorge Amado Helena, Machado de Assis Paginação com muita Simplicidade
<?php // Paginação com muita simplicidade // uncomment this to see plaintext output in your browser // header("Content-Type: text/plain"); // include the ADODB library include("adodb/adodb.inc.php"); include_once('adodb/adodb-pager.inc.php'); session_start(); // create an object instance // configure library for a PostgreSQL connection $db = NewADOConnection("postgres"); // open connection to database $db->Connect("localhost", "postgres", "postabir", "dbbiblioteca") or die("Unable to connect!"); $sql = "select * from biblioteca "; $pager = new ADODB_Pager($db,$sql); $pager->Render($rows_per_page=5); ?> <pre> <h2>Tratamento de Strings</h2> <pre> <?php // Insert a row to the Orders table containing dates and strings that need to be // quoted before they can be accepted by the database, eg: the single-quote in the word John's. // uncomment this to see plaintext output in your browser // header("Content-Type: text/plain"); // include the ADODB library include("adodb/adodb.inc.php"); // create an object instance // configure library for a PostgreSQL connection $db = NewADOConnection("postgres"); // open connection to database $db->Connect("localhost", "postgres", "postabir", "dbbiblioteca") or die("Unable to connect!"); $autor = $db->qstr("John's Old Shoppe"); // Tratamento de string com qstr antes de inserir no banco $sql = "insert into biblioteca (titulo,id,data,autor) "; $sql .= "values ('Teste2',36,'2006-10-10',$autor)"; // Veja o original para detalhes if ($db->Execute($sql) === false) { // Só insere se não ocorrer erro print 'error inserting: '.$db->ErrorMsg().'<BR>'; } echo "Inserido com sucesso!"; ?>
Debugando
<? include('adodb.inc.php'); # load code common to ADOdb $conn = &ADONewConnection('access'); # create a connection $conn->PConnect('northwind'); # connect to MS-Access, northwind dsn $shipto = $conn->qstr("John's Old Shoppe"); $sql = "insert into orders (customerID,EmployeeID,OrderDate,ShipName) "; $sql .= "values ('ANATR',2,".$conn->FormatDate(time()).",$shipto)"; $conn->debug = true; if ($conn->Execute($sql) === false) print 'error inserting'; ?>
Conectando-se a dois bancos de dados de SGBDs diferentes ao mesmo tempo
<? include('adodb.inc.php'); # load code common to ADOdb $conn1 = &ADONewConnection('mysql'); # create a mysql connection $conn2 = &ADONewConnection('oracle'); # create a oracle connection $conn1->PConnect($server, $userid, $password, $database); $conn2->PConnect(false, $ora_userid, $ora_pwd, $oraname); $conn1->Execute('insert ...'); $conn2->Execute('update ...'); ?>
Recordset Filters
Sometimes we want to pre-process all rows in a recordset before we use it. For example, we want to ucwords all text in recordset.
include_once('adodb/rsfilter.inc.php'); include_once('adodb/adodb.inc.php'); // ucwords() every element in the recordset function do_ucwords(&$arr,$rs) { foreach($arr as $k => $v) { $arr[$k] = ucwords($v); } } $db = NewADOConnection('mysql'); $db->PConnect('server','user','pwd','db'); $rs = $db->Execute('select ... from table'); $rs = RSFilter($rs,'do_ucwords');
Executing the SQL
$result = $db->Execute("SELECT * FROM employees"); if ($result === false) die("failed");
Inserts and Updates
Let's say you want to insert the following data into a database.
ID = 3 TheDate=mktime(0,0,0,8,31,2001) /* 31st August 2001 */ Note= sugar why don't we call it off $sql = "INSERT INTO table (id, thedate,note) values (" . $ID . ',' . $db->DBDate($TheDate) .',' . $db->qstr($Note).")"; $db->Execute($sql);
MetaTypes
You can find out more information about each of the fields (I use the words fields and columns interchangebly) you are selecting by calling the recordset method FetchField($fieldoffset). This will return an object with 3 properties: name, type and max_length.
For example:
$recordset = $conn->Execute("select adate from table");
$f0 = $recordset->FetchField(0);
Then $f0->name will hold 'adata', $f0->type will be set to 'date'. If the max_length is unknown, it will be set to -1.
One problem with handling different databases is that each database often calls the same type by a different name. For example a timestamp type is called datetime in one database and time in another. So ADODB has a special MetaType($type, $max_length) function that standardises the types to the following:
C: character and varchar types X: text or long character (eg. more than 255 bytes wide). B: blob or binary image D: date T: timestamp L: logical (boolean) I: integer N: numeric (float, double, money)
In the above date example,
$recordset = $conn->Execute("select adate from table"); $f0 = $recordset->FetchField(0); $type = $recordset->MetaType($f0->type, $f0->max_length); print $type; /* should print 'D' */
Apresentando a adobDB
Bom artigo do Silas Alves Júnior