Delirios de un Informático

Cómo instalar SQL::Translator en Mac OS X

Casi todas mis entradas que hablan sobre desarrollo asumen un funcionamiento sobre Linux, como esta sobre SQL::Translator. Al desarrollar ahora en Mac OS X he tenido que recurrir a este software, cuya instalación en Mac OS X es sencilla pero no tan rápida como tirar del repositorio de turno. Los comandos para realizar la instalación son:

sudo cpan
install SQL::Translator

La primera vez que se ejecuta CPAN tardará, ya que se configura pidiendo unos datos básicos y actualiza. Luego, una vez dentro del intérprete de Perl, el comando de instalación del módulo hará que se descargue e instale todo automáticamente, pero tardará un buen rato debido a las dependencias.

Cómo conectar PHP a SQL Server en Mac OS X

Siguiendo con mi serie de anotaciones sobre el desarrollo con PHP en Mac OS X, hoy toca hablar de algo que es más sencillo de lo que parece a primera vista: conectar PHP con SQL Server (virtualizado en una máquina Windows Server 2003 en mi caso) utilizando iODBC en Mac OS X Lion.

Con la versión de PHP que trae Lion de serie viene incluído el soporte para iODBC (una implementación de código abierto de ODBC y alternativa a unixODBC), al que le basta un controlador compatible para acceder a cualquier servidor de bases de datos. En este caso se recurre a FreeTDS, la implementeación de código abierto del controlador para SQL Server.

Como para muchas otras aplicaciones que se necesitan compilar en Mac OS X, he recurrido a Homebrew, que tras instalarlo sólo hay que ejecutar lo siguiente:

brew install freetds

La configuración también resulta sencilla: el archivo /usr/local/etc/freetds.conf debe contener lo siguiente al final:

[sqlserver]
	host = ip_o_host_del_servidor
	port = 1433
	tds version = 7.1

Luego, hay que crear el archivo /etc/odbcinst.ini con el siguiente contenido:

[FreeTDS]
Description = FreeTDS
Driver = /usr/local/lib/libtdsodbc.so
Setup = /usr/local/lib/libtdsodbc.so
UsageCount = 1

Por último, debe definirse el DSN creando el archivo /etc/odbc.ini:

[ejemplo]
Driver = FreeTDS
Database = ejemplo
Description = Base de datos de ejemplo
ServerName = sqlserver

Tras estos pasos, PHP debería poder conectarse al servidor SQL Server sin ningún problema. Pueden usarse las funciones ODBC de PHP para realizar la conexión:

$db = odbc_connect('ejemplo', 'usuario', 'contraseña');
$result = odbc_exec($db, "SELECT campo1, campo2 FROM tabla");
while($row = odbc_fetch_object($result)) var_dump($row);
odbc_close($db);

Si existe cualquier problema con la conexión, puede instalarse unixODBC con Homebrew (brew install unixodbc) y testear la conexión y obtener mensajes de error con el siguiente comando:

tsql -S ip_o_host_del_servidor -U sa

Por último es importante no añadir espacios o tabulaciones en los archivos .ini o iODBC no podrá obtener correctamente los valores causando un error en la conexión.

Cómo convertir una base de datos MySQL a SQLite

Hace tiempo hablé de SQLFairy, una serie de scripts que permiten convertir bases de datos entre sí, realizando las modificaciones oportunas a los dumps. De lo que no hablé es de cómo convertir una base de datos MySQL a SQLite sin dolor, ya que muchos scripts que circulan por la red no funcionan o están obsoletos. El proceso es sencillo:

sqlt -f DBI --dsn dbi:mysql:mydb --db-user root --db-pass ******** -t SQLite > schema.sql
mysqldump -u root -p --compatible=ansi --skip-opt mydb | grep "INSERT" > data.sql
cat schema.sql | sqlite3 mydb.db && cat data.sql | sqlite3 mydb.db

Lo único a tener en cuenta es revisar el volcado de datos en busca de comillas simples () escapadas, ya que SQLite parece no aceptarlas bien. Basta sustituirlas por alguna cadena de caracteres aleatoria que luego se re-sustituye con una consulta UPDATE.

Este script automatiza el proceso:

#!/bin/bash
sqlt -f DBI --dsn dbi:mysql:$1 --db-user $2 --db-pass $3 -t SQLite > schema.sql
mysqldump -u$2 -p$3 --compatible=ansi --skip-opt $1 | grep "INSERT" > data.sql
iconv -f ISO-8859-1 -t UTF-8 data.sql > data-iso.sql
perl -pe "s/\\\'/''/g" data-iso.sql > data.sql
cat schema.sql | sqlite3 "$1.db" && cat data.sql | sqlite3 "$1.db"
rm -f schema.sql data.sql data-iso.sql

Debe ejecutarse del siguiente modo: ./mysql2sqlite.sh database user password.

Cómo “tracear” consultas SQL

Localizar y optimizar consultas SQL problemáticas es muy laborioso y complejo a veces. Uno de los mayores problemas con los que me he encontrado, es localizar exactamente desde dónde se ha ejecutado esa consulta para así reproducir el problema lo más fielmente posible en un entorno de desarrollo.

Un SHOW PROCESSLIST permite visualizar la lista de consultas que se están ejecutando, con su tiempo y el host desde donde se ha realizado la conexión pero sin más información, cuando lo realmente interesante es ¿desde dónde se ha ejecutado esta consulta?. La solución es muy sencilla: utilizar comentarios SQL dentro de las consultas.

Hoy en día, cualquier aplicación mínimamente grande utiliza una capa de abstracción (bien integrada en un framework, bien propia) por lo que no suele resultar difícil interceptar las consultas para añadir los comentarios. Si combinamos esto con funciones como debug_backtrace() de PHP (o su equivalente en Ruby o Python), tendremos una valiosísima información, ya que es posible determinar desde qué función, clase+metodo e incluso la línea concreta:

SELECT /* controlador test, metodo database, linea 17 */ field1, field2 FROM table

Este pequeño truco faciltará mucho el trabajo de detectar consultas conflictivas especialmente en casos en los que se ejecutan desde muchos lugares en diferentes archivos, y a efectos prácticos el rendimiento no se ve mermado.

Consejos para portabilidad de bases de datos

Hay proyectos en los que es necesario pensar en la portabilidad de la base de datos, permitiendo que funcione perfectamente en diferentes servidores. Por ejemplo, el famoso phpBB soporta 7 sistemas. Esto no es recomendable en proyectos en los que el rendimiento es algo vital como sitios web y sólo debe aplicarse a proyectos donde prima la portabilidad.

A nivel de aplicación, esto requiere el uso de una capa de abstracción que hoy en día integran la mayoría de frameworks o que puede lograrse con con la extensión PDO de PHP o clases como ADOdb. Y para convertir las bases de datos entre sí, ya he hablado de SQL::Translator, un potente script programado en Perl.

Conoce el estándar SQL-92

En la especificación de SQL-92 se definen los puntos comunes del lenguaje. Tenerlos claros los puntos es primordial como por ejemplo el uso de comillas simples para literales.

Comprueba todos los datos antes de enviarlos a la base de datos

Esto debe ser básico en cualquier desarrollo, pero nunca sobra recordarlo. Todos los datos que se almacenen deben estar comprobados, ya que un sistema puede aceptar un valor no válido para un tipo de dato y convertirlo mientras que otro sistema lo rechazará.

Usa correctamente los valores NULL y asigna valores por defecto

Todos los campos que sean omitibles en un INSERT o un UPDATE deben tener un valor por defecto o bien ser NULL. En bases de datos como SQLite, el no especificar un valor por defecto provoca un error, mientras que en MySQL no.

Usa transacciones cuando se requiera

Cuando se realizan varias consultas seguidas de inserción, actualización o borrado, es recomendable el uso de transacciones. Es mejor no realizar ningún cambio a que se realice a medias, perdiendo datos.

Usa consultas INSERT y UPDATE completas

Nunca deben omitirse partes de las sentencias de inserción y actualización. Por ejemplo: INSERT INTO tabla (campo1, campo2) VALUES (‘a’, ‘b’).

Usa siempre ORDER BY

MySQL permite establecer el orden por defecto de una tabla y en otros sistemas el orden puede variar por cada consulta realizada, ya que se puede ordenar por fecha de actualización o inserción del registro. Por eso nunca debe omitirse ORDER BY.

Evita el uso de la integridad referencial

La integridad referencial no está soportada en todos los sistemas (MySQL con tablas InnoDB sí la soporta pero no con tablas MyISAM, SQLite permite asignar claves foráneas pero no hace nada con ellas), por lo que la lógica debe trasladarse a la programación, borrando o actualizando los datos relacionados de ser necesario.

Evita el uso de funciones propias

No deben usarse funciones propias de cada sistema, dificultan la portabilidad. Lo que se puede hacer con una función en una consulta puede hacerse con otra función en la programación. Eso sí, ciertas funciones de tratamiento de cadenas y fechas pueden ser comunes o muy similares, por lo que en caso de ser necesario, debe tenerse en cuenta a la hora de programar para usar la adecuada según el sistema. Algunas capas de abastracción como ADOdb integran ayudas para estos casos.

Evita el uso triggers y procedures

Todo lo que puede incluirse en disparadores y procedimientos almacenables puede incluirse en la lógica de la aplicación. Se pierde comodidad pero evitan problemas, ya que la implementación de estas funciones varía y ciertos sistemas no las implementan.

Evita el uso de vistas

Las vistas funcionan de diferente forma entre los diferentes RDBMS existentes, sobre todo en la actualización de los datos que consulta, lo que puede provocar resultados inesperados. Hay que recordar que sólo algunos sistemas permiten la actualización de datos en vistas, por lo que es importante no hacer uso de esa funcionalidad.

Evita el uso de subconsultas

Del mismo modo que las vistas, las subconsultas funcionan de diferente forma entre los diferentes RDBMS existentes y no todos las implementan. Cada sistema tiene sus restricciones, por lo que una subconsulta que funciona perfectamente en

Evita el uso tipos de datos y atributos no estándar

Los tipos de datos específicos de ciertos motores deben evitarse. Por ejemplo, una fecha timestamp puede almacenarse en un campo INT de 32 bits, pero en MySQL puede almacenarse en un campo tipo TIMESTAMP con el atributo ON UPDATE CURRENT_TIMESTAMP que no es extensible a otros sistemas. Otro ejemplo pueden ser los tipos BOOL, que pueden sustituirse por enteros (0 – 1).