Pagina principal ADODB en español

Consejos para escribir enunciados SQL portables

Actualizado al 18 Sep 2003. Se agrego la seccion de SQL Nativo Portable.

Si estas escribiendo una aplicacion que se usara en varios medios ambientes y sistemas operativos, necesitas planificar el soporte a multiples bases de datos. Este articulo se base en mi experiencia con multiples sistemas de bases de datos, abarca desde la 4a. dimension en mis dias de Mac, a las bases de datos que uso actualmente: Oracle, FoxPro, Access, MS SQL Server y MySQL. A pesar de que la mayoria de los consejos tambien sirven al uso de SQL con Perl, Python y otros lenguajes de programacion, me enfocare a PHP y como la libreria ADOdb de abstraccion de bases de datos ofrece algunas soluciones.

La mayoria de los vendedores de bases de datos le ponen un seguro a sus productos. La mejor o mas rapida manera de hacer las cosas es casi siempre implementado con extensionnes al SQL propietarias. Estos hace extremadamente dificil escribir codigo SQL portable que se ejecute bien bajo todas las condiciones. Cuando el primer comite ANSI se reunio en 1984 para estandarizar el SQL, los vendedores de bases de datos SQL tenian tan variadas implementaciones que solo se pudieron poner de acuerdo en la funcionanlidad medular de SQL. Muchos requerimientos especificos de la aplicacion no fueron estandarizados, y varios años despues de que empezo el esfuerzo ANSI, parece que muchas de las funciones de las bases de datos nunca van a ser estandarizadas. Con todo y que el SQL ANSI-92 ha estandarizado mucho, todavia tenemos que implementar la portabilidad en el nivel de aplicacion.

Selects

El enunciado SELECT ha sido en gran parte estandarizado. Casi cada base de datos soporta lo siguiente:

SELECT [cols] FROM [tablas]
  [WHERE condiciones]
  [GROUP BY cols]
  [HAVING condiciones]
  [ORDER BY cols]

Pero muchas tecnicas utiles solo pueden ser implementadas usando extensiones propietarias. Por ejemplo, para escribir un SQL para obtener los primeros 10 renglones, se puede escribir ...

Base de Datos Sintaxis del SQL
DB2 select * from table fetch first 10 rows only
Informix select first 10 * from table
Microsoft SQL Server y Access select top 10 * from table
MySQL y PostgreSQL select * from table limit 10
Oracle 8i select * from (select * from table) where rownum <= 10

Esta caracteristica de obtener un subconjunto de datos es tan util que en la libreria ADOdb tenemos la funcion SelectLimit( ) que nos permite esconder los detalles de implementacion dentro de una funcion que modificara el SQL:

$connection->SelectLimit('select * from table', 10);

Selects: Modos de recuperacion (Fetch Modes)

PHP nos permite recuperar los registros de la base de datos como un arreglo. Puedes escoger que los arreglos esten indexados por nombre de campo o por numero de columna. Sin embargo, los drivers de bajo de nivel de las bases de datos en PHP no son consistentes en sus esfuerzos de indexacion. ADOdb nos permite determinar el modo preferido. Eso se configura modificado la variable $ADODB_FETCH_MODE ya sea a la constante ADODB_FETCH_NUM (para indices numericos) o ADODB_FETCH_ASSOC (usando el nombre de los campos en un indice asociativo).

El comportamiento por omision de ADOdb varia dependiendo de la base de datos que se utilice. Por consistencia, asigne el valor de recuperacion a ADODB_FETCH_NUM (por velocidad) o ADODB_FETCH_ASSOC (por conveniencia) al principio del codigo.

Selects: Contando registros

Otro problema con los SELECTs es que algunas bases de datos no proporcionan el numero de renglones recuperados por el enunciado select. Esto es porque las bases de datos con el mas alto desempeño empezaran a regresar registros aun antes de que el ultimo registro sea encontrado.

En ADOdb, RecordCount( ) regresa el numero de renglones regresados, o lo emulara almacenando los renglones y regresando la cuenta despues de que lea todos los registros. Por rendimiento esto se puede desahabilitar al recuperar recordsets muy grandes modificando la variable global $ADODB_COUNTRECS = false. Esta variable es verificada cada vez que se ejecuta un SQL, por lo que se puede escoger selectivamente cuales recordsets contar.

Si prefieres poner $ADODB_COUNTRECS = false, ADOdb tambien tiene la funcion PO_RecordCount( ). Esta regresara el numero de renglones, o si no tiene el dato, regresara un estimado usando SELECT COUNT(*):

$rs = $db->Execute("select * from table where state=$state");
$numrows = $rs->PO_RecordCount('table', "state=$state");

Selects: Aseguramiento de registros (Locking)

Los enunciados SELECT son normalemte usados para implementar el aseguramiento de tablas a nivel registro. Otras bases de datos como Oracle, Interbase, PostgreSQL y MySQL con InnoDB no requieren asegurar los registros porque usan un control de versiones para mostrar datos consistentes en un punto en el tiempo.

Normalmente, recomiendo encapsular el aseguramiento en una funcion separada tal como RowLock($table, $where):

$connection->BeginTrans( );
$connection->RowLock($table, $where); 
# alguna operacion
if ($ok) $connection->CommitTrans( );
else $connection->RollbackTrans( );

Selects: Conecciones externas (Outer Joins)

No todas las bases de datos manejan 'outer join'. Ademas de esto la sintaxis para 'outer join' difiere drasticamente entre vendedores de bases de datos. Un metodo portable (y posiblemente mas lento) de implementar 'outer joins' es usando UNION.

Por ejemplo, un 'left outer join' de la norma ANSI-92 entre dos tablas t1 y t2 pudiera ser:

SELECT t1.col1, t1.col2, t2.cola 
FROM t1 LEFT JOIN t2 ON t1.col = t2.col

Esto puede ser emulado usando:

SELECT t1.col1, t1.col2, t2.cola FROM t1, t2 
WHERE t1.col = t2.col UNION ALL SELECT col1, col2, null FROM t1
WHERE t1.col not in (select distinct col from t2)

Desde ADOdb 2.13, se proporcionan indicios en el objeto conexion de las diferentes variantes de 'joins' legales. Es todavia incompleto y algunas veces dependiente de la version de la base de datos que se usa, pero sirve como una guia general:

$conn->leftOuter: contiene el operador usado para 'left outer joins' (ej. '*='), o falso si se desconce o no esta disponible.
$conn->rightOuter: contiene el operador usado para 'right outer joins' (ej '=*'), o falso si se desconce o no esta disponible.
$conn->ansiOuter: valor boleano que es verdadero si se manejan los 'outer joins' con la sintaxis ANSI-92, o falso si no se sabe.

Inserts

Cuando se crean los registros, se necesita generar un identificador unico para cada registro. Hay dos tecnicas usuales: (1)columnas auto incrementales y (2) secuencias.

Las columnas auto incrementales estan soportadas por MySQL, Sybase, Microsoft Access y SQL Server. Sin embargo otras bases de datos no manejan esta caracteristica. Por lo que por portabilidad, hay pocas opciones mas que usar secuencias. Las secuencias son funciones especiales que regresan un numero unico ascendente cada vez que se llama, es util para las llaves de bases de datos. En ADOdb, se usa la funcion GenID( ). Toma como parametro el nombre de la secuencia. Diferentes tablas pueden tener secuencias diferentes.

$id = $connection->GenID('sequence_name');
$connection->Execute("insert into table (id, firstname, lastname)
values ($id, $firstname, $lastname)");

Para las bases de datos que no manejan secuencias nativamente, ADOdb las emula creando una tabla para cada secuencua.

Ligando variables (Binding)

Ligar variables en un enunciado SQL es otra caracteristica artificiosa. El uso de 'bind' es util porque nos permite la pre-compilacion de enunciados SQL. Cuando se insertan multiples registros a una base de datos en un ciclo, el uso de 'bind' puede ofrecer un aumento de velocidad del 50% (o mayor). Sin embargo muchas bases de datos como Access y MySQL no manejan el 'bind' nativamente y hay un costo por emularlo. Ademas, diferentes bases de datos (¡especificamente Oracle!) implementan el 'bind' de manera diferente. Mi recomendacion es el uso de 'bind' Si los querys a tu base de datos estan lentos, pero asegurate de que empleas una base de datos que lo soporte como Oracle.

ADOdb maneja Prepare y Execute portables con:

$stmt = $db->Prepare('select * from customers where custid=? and state=?');
$rs = $db->Execute($stmt, array($id,'New York'));

Oracle emplea parametros de 'bind' con nombre, en lugar de "?", por lo que para manejar el 'bind' portable tenemos la funcion Param() que genera el marcador correcto, (disponible desde ADOdb 3.92):

$sql = 'insert into table (col1,col2) values ('.$DB->Param('a').','.$DB->Param('b').')';
# genera 'insert into table (col1,col2) values (?,?)'
# o      'insert into table (col1,col2) values (:a,:b)'
$stmt = $DB->Prepare($sql);
$stmt = $DB->Execute($stmt,array('one','two'));

SQL Nativo Portable

ADOdb proporciona las siguientes funciones de portabilidad, genera funciones SQL como cadenas de caracteres para ser incorporadas a tus enunciados SQL (algunas estan disponible solo desde la version ADOdb 3.92):

Funcion Descripcion
DBDate($date) Recibe un timestamp de Unix o una fecha en formato ISO y la convierte en una cadena de texto con la fecha para ser usada en INSERT/UPDATE
DBTimeStamp($date) Recibe un timestamp de Unix o una fecha en formato ISO y la convierte en una cadena de texto con un timestamp para ser usada en INSERT/UPDATE
SQLDate($date, $fmt) Genera una fecha formateada con las mascara $fmt, para uso en enunciados SELECT.
OffsetDate($date, $ndays) Genera una fecha calculada en base a $date mas/menos $ndays.
Concat($s1, $s2, ...) Concatena cadenas de caracteres. Para mssql emplee como alternativa el driver mssqlpo que permite el operador ||.
IfNull($fld, $replaceNull) Genera la cadena de caracteres que es equivalente al IFNULL de MySQL o al NVL de Oracle.
Param($name) Genera la cadena para identificar un parametro de 'bind', usa ? o parametro con nombre segun sea el caso.
$db->sysDatePropiedad que contiene la funcion SQL que regresa el dia actual.
$db->sysTimeStampPropiedad que contiene la funcion que regresa el punto en el tiempo actual (timestamp) (fecha y hora).
$db->concat_operatorPropiedad que contiene el operador para concatenar.
$db->lengthPropiedad que contiene el nombre de la funcion SQL equivalente a 'strlen'.
$db->upperCasePropiedad que contiene el nombre de la funcion SQL equivalente a 'strtoupper'.
$db->randomPropiedad que contiene el enunciado SQL para generar un numero aleatorio entre 0.00 y 1.00.
$db->substrPropiedad que contiene el nombre de la funcion SQL para tomar parte de un cadena (substring).

 

DDL y puesta a punto

Existen herramientas de diseño de bases de datos tales como ERWin o Dezign que nos permiten generar comandos del lenguaje de definicion de datos (DDL) tales como ALTER TABLE o CREATE INDEX directamente de los diagramas Entidad-Relacion (ERD).

Si embargo si prefieres emplear un esquema de creacion de tablas basado en PHP, ADOdb te lo proporciona. A continuacion esta el codigo para generar el enunciado SQL para crear un tabla con:

  1. Llave primaria autoincremental 'CLAVE',
  2. El 'NOMBRE' de la persona, tipo VARCHAR(32) NOT NULL y con valor por omision '',
  3. La fecha y hora de la creacion del registro 'CREADO',
  4. La 'EDAD' de la persona, por omision 0, tipo NUMERIC(16).

Tambien crea un indice compuesto con los campos 'NOMBRE' y 'EDAD':

$datadict = NewDataDictionary($connection);
$flds = " 
  CLAVE I AUTOINCREMENT PRIMARY,
  NOMBRE C(32) DEFAULT '' NOTNULL,
  CREADO T DEFTIMESTAMP,
  EDAD N(16) DEFAULT 0
";
$sql1 = $datadict->CreateTableSQL('tabname', $flds);
$sql2 = $datadict->CreateIndexSQL('idx_name_age', 'tabname', 'NOMBRE,EDAD');

Tipos de Datos

Limitese a los pocos tipos de datos que estan disponibles en la mayoria de las bases de datos. Char, varchar y numeric/numero estan soportados por la mayoria de las bases de datos. NO se puede confiar en que la mayoria de los demas tipos (incluyendo integer, boolean y float) esten disponibles. Recomiendo usar char(1) o number(1) para almacenar boleanos.

Cada base de datos tiene maneras diferentes de representar fechas y timestamps/datetime. ADOdb intenta desplegar todas las fecha en formato ISO (YYYY-MM-DD). ADOdb tambien proporciona DBDate( ) y DBTimeStamp( ) para convertir fechas a formatos que sean aceptablas para la base de datos. Ambas funciones reciben timestamps de Unix o cadenas de carcteres en formato ISO de fecha.

$date1 = $connection->DBDate(time( ));
$date2 = $connection->DBTimeStamp('2002-02-23 13:03:33');

Tambien se proporcionan funciones para convertir fechas de bases de datos al formato timestamp de UnixUnix:

$unixts = $recordset->UnixDate('#2002-02-30#'); # Fecha de MS Access => unix timestamp

La longitud maxima de un campo char/varchar tambien es especifica a cada base de datos. Solo podemos asumir que esta soportado hasta una longitud de campos de 250 caracteres. Esto casi siempre es impractivo para foros basados en Web o sistemas de control de contenidos (CMA). Necesitaras familiarizarte en la manera en que las bases de datos manejan objetos largos (LOBs). ADOdb implementa dos funciones, UpdateBlob( )y UpdateClob( ) que te permiten actualizar campos conteniendo Objectos Binarios Largos (BLOB, ej. imagenes) y Objetos Caracter Largos (CLOB, ej. articulos HTML):

# para oracle 
$conn->Execute('INSERT INTO blobtable (id, blobcol) VALUES (1,empty_blob())'); 
$conn->UpdateBlob('blobtable','blobcol',$blobvalue,'id=1'); 
   
# otras bases de datos excepto oracle
$conn->Execute('INSERT INTO blobtable (id, blobcol) VALUES (1, null)'); 
$conn->UpdateBlob('blobtable','blobcol',$blobvalue,'id=1');

El manejo de nulos es otra area donde puede haber diferencias. Este es un campo trampa, debido a que la logica de 3 valores es engañosa.

En general, Yo evite usar nulos excepto para fechas y les doy valor por omision 0 o de cadena vacia a mis campos numericos y caracter . Esto mantiene la consistencia con PHP, donde las cadenas vacias y el cero se tratan como equivalentes, y evita la ambiguedades con SQL cuando se usan los operadores ANY o EXISTS. Sin embargo si tu base de datos tiene una cantidad significativa tiene una cantidad significativa de datos faltantes o desconocidos, el uso de nulos puede ser una buena idea.

ADOdb tambien maneja una funcion portable para IfNull, para que puedas definir lo que hay que mostrar si el campo contiene nulo.

Procedimientos Almacenecados (Stored Procedures, SP)

Los Procedimientos Almacenados son otra area problematica. Algunas bases de datos permiten que los procedimientos almacenados regresen un recordset (Microsoft SQL Server y Sybase), y otras solo permiten parametros de salida. Los procedimientos almacenados algunas veces tienen que ser encerrados en una sintaxis especial. Por ejemplo, Oracle requiere que el codigo se encierre en un bloque anonimo con BEGIN y END. Tambien los operadores y funciones SQL tales como +, ||, TRIM( ), SUBSTR( ) o INSTR( ) varian entre vendedores.

A continuacion un ejemplo de como llamar procedimientos almacenados con dos parametros y un valor de salida:

	switch ($db->databaseType) {
	case 'mssql':
	  $sql = 'SP_RUNSOMETHING'; break;
	case 'oci8':
	  $sql = 
	  "declare RETVAL integer;begin :RETVAL := SP_RUNSOMETHING(:myid,:group);end;";
	  break;
	default:
	  die('Caracteristica no implementada');
	}
	# @RETVAL = SP_RUNSOMETHING @myid,@group
	$stmt = $db->PrepareSP($sql);	
$db->Parameter($stmt,$id,'myid'); $db->Parameter($stmt,$group,'group'); # true indica parametro de salida
$db->Parameter($stmt,$ret,'RETVAL',true); $db->Execute($stmt);

Como puedes ver, la API de ADOdb es la misma para ambas bases de datos. Pero la sintaxis de los procedimientos almacenados es bastante diferente entre las bases de datos y es no portable, ¡Estas advertido! Sin embargo algunas veces tienes pocas opciones ya que algunos sistemas solo permiten accesar datos via procedimientos almacenados. Aqui es donde solo resta la ultima solucion de portabilidad:creando SQL portable como un ejercicio de tropicalizacion...

SQL como un ejercicio de tropicalizacion

En general para proporcionar una portabilidad real, tienes que tratar el codigo SQL como un ejercicio de tropicalizacion. En PHP, se ha hecho comun definir archivos de lenguaje diferente para: Ingles, Ruso, Coreano, etc. Similarmente, Yo te sugiero que tengas archivos separados para Sybase, Intebase, MySQL, etc., e incluir condiconalmente el SQL segun la base de datos. Por ejamplo, cada enunciado SQL de MySQL se almacenaria en una variable separada, en un archivo llamado 'mysql-lang.inc.php'.

$sqlGetPassword = 'select password from users where userid=%s';
$sqlSearchKeyword = "SELECT * FROM articles WHERE match (title,body) against (%s)";

En el archivo PHP principal:

# definir cual base de datos a cargar...
$database = 'mysql';
include_once("$database-lang.inc.php");

$db = &NewADOConnection($database);
$db->PConnect(...) or die('Fallo la conexion a la base de datos');

# buscar por la clave $word
$rs = $db->Execute(sprintf($sqlSearchKeyWord,$db->qstr($word)));

Observe que se encomilla la variable $word usando la funcion qstr( ). Esto debido a que cada base de datos encomilla las cadenas de caracteres usando diferentes convenciones.

Ideas Finales

La mejor manera para asegurar que tienes codigo SQL portable es tener las tablas de datos diseñadas usando buenos principios. Aprende la teroria de normalizacion y los diagramas entidad-relacion y modela tus datos cuidadosamente. Entiende como funcionan los 'joins' e indices y como pueden ser usados para ajustar el rendimiento.

Visita la siguiente pagina para mas informacion de teoria de bases de datos y vendedores: http://adodb.sourceforge.net/#docs. Tambien lee este articulo de Optimizacion de PHP.

(c) 2002-2003 John Lim.