Introducción al uso de las bases de datos desde Perl

Introducción: bases de datos

Una base de datos es una forma de almacenar y acceder a información de forma estructurada. Las bases de datos se usan a través de los llamados sistemas de gestión de bases de datos, o SGBD, de los cuales buenos ejemplos son Oracle o Sybase entre las bases de datos de pago, y PostgreSQL, MySQL o FireBird entre las libres y gratuitas.

Habitualmente los SGBD se dividen en varias partes: un servidor, que se ejecuta en un ordenador determinado, y da acceso al espacio estructurado como una BD usando una variedad de interfaces diferentes, aparte de otra serie de servicios, como autentificación y autorización, y un cliente, que permiten al usuario o a los programas acceder a esos datos. A veces por medio hay otras capas, habitualmente llamadas middleware, de las que no nos vamos a ocupar.

Como alternativa a una base de datos se puede usar algún formato de fichero, sea plano o separado por comas (o cualquier otro tipo de estructura regular) o algún otro tipo de documento estructurado, tal como el XML; sin embargo, todos estos formatos tienen diferentes problemas: el acceso a la información es secuencial, tienen que estar completos en memoria (y, lo que es peor, cargarse cada vez que se usen) antes de acceder al dato que se desee, y, finalmente, no tienen nigún tipo de información sobre la semántica o la sintaxis de los datos. Todos estos problemas se resuelven, parcialmente, con los sistemas de bases de datos XML modernos. Las bases de datos, por otro lado, permiten acceder a la información de forma eficiente, hacer búsquedas rápidas, y reunir información procedente de diferentes fuentes de la mejor forma posible. Asimismo, se encargan de la gestión del acceso múltiple a los datos, a base de un sistema de bloqueos que impide acceder a los datos si alguien los está modificando.

Algunas bases de datos usan las denominadas transacciones: conjunto de operaciones que tienen éxito o no de forma atómica; si alguna operación del conjunto falla, falla la transacción completa, y se invalidan las operaciones realizadas con anterioridad. Por ejemplo, en una base de datos que represente un sistema bancario, dar de alta una cuenta corriente y a un usuario pueden ser una operación atómica: si el usuario falla, la cuenta corriente no puede crearse, puesto que habitualmente va asociada al usuario.

Entre las bases de datos, las más habituales son las bases de datos relacionales, que permiten asociar datos de un tipo a otro, de forma que se indique, a modo de enlace, que una parte de un dato se refiere a otro almacenado en otro sitio: por ejemplo, una cuenta va asociada a una persona o personas, y se puede hacer que el registro de esa cuenta en la base de datos apunte a esas personas (y viceversa). A estos enlaces se les suele denominar claves externas. La mayor parte de las bases de datos populares hoy en día son relacionales (salvo MySQL, que hasta hace pocas versiones, no lo fue).

Aparte de acceder a ellos mediante interfaces más o menos amigables o mediante un interfaz de programación o API (que hace peticiones directamente al servidor), la mayor parte de las bases de datos usan un lenguaje de programación propio, el Structured Query Language o SQL, un lenguaje creado por IBM para que se pareciera al lenguaje natural, lo que hace que, como el malhadado COBOL, haya que escribir un montón de cosas para hacer cualquier chorrada. El SQL permite hacer todas las actividades necesarias para gestionar y trabajar con una base de datos y almacenar sus contenidos. Aún así, la mayor parte de los SGBD usan dialectos, con extensiones que no tienen porqué funcionar en los otros sistemas. Es conveniente conocer estos dialectos, porque a veces facilitan la vida, pero no usarlas si se pretende usar el programa escrito en varios entornos diferentes (no necesariamente de forma simultánea, quién sabe qué BD o versión de la BD se va a usar en el futuro).

En este tutorial veremos cuáles son los componentes básicos de una BD, cómo usarlos, y, finalmente, como acceder a ellos usando el lenguaje SQL.

Creando tablas

La base de de datos es el componente fundamental de un SGBD. Una base de datos es la unidad fundamental para almacenamiento de la información: tablas (que es donde se almacenan los datos), usuarios y privilegios de los mismos. Cada SGBD, según ha sido instalado, suele incluir con la instalación una BD por defecto (por ejemplo, la template1 en PostgreSQL); el usuario, si tiene privilegios, puede crear todas las que quiera, o usar las que incluya la cuenta de su proveedor de servicio.

Dentro de cada base de datos, la información se almacena en tablas. Se puede pensar en una tabla como tal, donde cada fila corresponde a un dato unitario, con datos de diferente tipo almacenados en sendas columnas. Por ejemplo, una tabla para personas podría incluir datos de cuatro tipos diferentes: un número de identificación único (puede ser el NIF), el nombre, los apellidos y otro dato, el puesto, pongamos por caso. Todos los datos serán alfanuméricos, y se pueden crear de la forma siguiente (fichero create-table.sql): CREATE TABLE `prueba` ( `dni` VARCHAR( 12 ) NOT NULL , `nombre` VARCHAR( 40 ) NOT NULL , `apellidos` VARCHAR( 50 ) NOT NULL , `puesto` VARCHAR( 40 ) NOT NULL , PRIMARY KEY ( `dni` ) );

Para empezar, ¿dónde diablos metemos esto?. Hay varias formas de hacerlo. Si estás trabajando en un proveedor de servicio internet, muchos de los cuales incluyen también el paquete de servicio acceso y administración de bases de datos, lo más normal es que incluya un interfaz gráfico para hacer este tipo de cosas, tal como PHPMyAdmin (del cual se puede ver un ejemplo más arriba). Si el interfaz no es el mismo, es muy parecido: pregunta el nombre de la tabla y el número de campos, y presenta un interfaz que te permite introducir el nombre, tipo y características adicionales de cada una de las columnas de la tabla.

Otra opción es hacer lo ismo desde un interfaz gráfico, tal como pgAdmin o bien MySQLAdmin para MySQL. Supongo que otros tendrán interfaces similares. pgAdmin3 se muestra en la siguiente imagen:

El interfaz gráfico permite llevar a cabo todas las tareas de mantenimiento de la base de datos: creación, destrucción y alteración de tablas, inserción de nuevos elementos. Se aconseja, sin embargo, que no se confíe totalmente en estos elementos gráficos, y se use siempre una copia de seguridad o un fichero.

La mayoría de las suites de oficina suelen incluir también un interfaz para bases de datos; bien para manejar bases de datos en ficheros, o para conectar a bases de datos remotas. Por ejemplo, OpenOffice contiene OpenOffice Base, que puede conectar a través de ODBC (un interfaz genérico de acceso a bases de datos) a bases de datos MySQL y otros. Hay también otros interfaces en KDE e independientes, que permiten trabajar con cualquier tipo de SGBD.

Si no tenemos la suerte de contar con ese interfaz gráfico (o desgracia, porque los verdaderos programadores^tm teclean el código a pelo), deberemos introducir esos comandos desde el cliente de la BD. Se arranca el cliente:

se puede usar, por ejemplo, psql template1, la base de datos por defecto, y tecleas lo de arriba para crear la tabla; si se escribe simplemente psql se entrará en una base con el mismo nombre del usuario (la que en realidad vamos a usar en estas prácticas). En realidad, no exactamente, porque, a pesar de que las sentencias SQL parece que sí son estándar, los nombres de las cosas no lo son necesariamente, y lo de arriba, hecho para MySQL, no va para PostgreSQL: hay que quitar las comillas. Una vez hecho eso, funciona sin ningún problema, y responderá algo así:

NOTICE: CREATE TABLE / PRIMARY KEY creará el índice implícito "prueba_pkey" para la tabla "prueba" CREATE TABLE

También lo puedes crear directamente desde el fichero, sin necesidad de meterte en el interfaz:

Esta forma de actuar es la más aconsejable, sobre todo si tienes que crear diversas tablas; así puedes reproducirlo fácilmente. En un entorno de producción, lo normal es que haya que crear y destruir las tablas con cierta asiduidad, según van cambiando las definiciones de los diversos elementos. También lo puedes leer directamente desde el interfaz usando una de las órdenes del mismo:

Vamos a analizar la sentencia anterior de creación de una tabla: contiene una orden, unos datos, y una marca de final de sentencia, el ;; absolutamente imprescindible; si te lo olvidas, estás jodido. Que no se te olvide nunca meterlo, porque si no, no le hacen a la sentencia ni caso. Create table crea la tabla, y le asigna un nombre. Es una sentencia estándar SQL, que funcionará aproximadamente de la misma forma en todos los SGBD; el nombre que le hemos dado es Prueba. CREATE se usa para crear los diferentes elementos de la base de datos, y la base de datos en si. Si la base de datos no ha sido creada previamente, habrá que crearla con la sentencia

Y que no se te olvide el ;, poramordediossss.

El nombre de la tabla no puede ser arbitrario. En general, debe comenzar por una letra (no un número); puede estar compuesto de letras, números y caracteres de subrayado (_). No se suelen distinguir entre mayúsculas y minúsculas, salvo que el identificador esté entre comillas dobles. Es decir, prueba es lo mismo que PrUeBa, pero no lo mismo que "PrUeBa"; de la misma forma, se pueden usar las comillas para usar identificadores que comiencen por un número o que tengan el mismo nombre que las palabras clave de SQL. El tamaño máximo es de 31 caracteres. Todos los identificadores en PostgreSQL siguen las mismas reglas: los nombres de columna, secuencia y restricciones, por ejemplo.

A continuación se describen en cada línea una de las columnas, con el formato nombre tipo opciones y una coma (,) para finalizar la descripción del dato. Por ejemplo, la primera columna se llamará dni y será una cadena de tamaño variable (varchar, abreviatura de character varying), pero que tendrá un máximo de 12 caracteres. Tratándose del DNI debería haber sido de tamaño fijo, ¿no? Pues no, porque las tarjetas de residente, y, sobre todo, los pasaportes de la EU tienen cada uno un número de caracteres diferente. Hay muchos tipos estándar SQL; este es el más eficiente en consumo de memoria para almacenar cadenas, aunque también se pueden usar cadenas de tamaño fijo char (que es abreviatura de character), que permiten acceder a los datos más rápidamente, pero que desperdician más memoria. La diferencia de velocidad tampoco es bestial, pero si se tienen muchos registros puede ser considerable la diferencia de memoria. El problema es que los campos varchar tienen un tamaño máximo (255 caracteres), y si se quiere almacenar cualquier texto, es mejor usar el tipo text, que permite cadenas de tamaño arbitrario.

Después del tipo hemos especificado que debe ser NOT NULL, es decir, que no puede estar vacío al crearse el objeto; el SGBD dará un error en caso contrario. Otra opción es por ejemplo, indicar que el dato incluido en una columna sea único: UNIQUE; si se trata de insertar dos veces un dato con el mismo valor en ese campo, dará también un error.

Además, se indica otro dato sobre esta columna, en una línea aparte:

indica que es una clave primaria, es decir, una cadena que es única, bien por ser asignada por la BD, o bien porque su estructura o tipo garantice que lo sea. Servirá para identificar de forma única los datos dentro de la tabla, y para que datos de otras tablas se refieran a él. Por tanto, no se pueden insertar dos datos con la misma clave primaria, y si se intenta, el SGBD dará un error; en este caso, evidentemente, lleva implícita la característica UNIQUE.

Hay otros tipos de datos, claro. Por ejemplo, podemos intentar dejar al sistema que asigne automáticamente números de identificación correlativos a cada uno de los usuarios (para garantizar que son únicos):

El tipo serial (que es específico de PostgreSQL; en MySQL se usa auto-increment) se autoincrementa cada vez que se añade un nuevo elemento a la base de datos; y el almacenar un entero es más eficiente que el almacenar una cadena. En realidad, esto se traduce en la base de datos en la creación de otra serie de elementos. Cuando lo introducimos desde la línea de comandos, se responde:

Es decir, que se crea una secuencia, una tabla especial que contiene el último índice asignado (y que, en un momento dado, se puede consultar para saber cuál es el último índice). Que, en realidad, sería un objeto de este tipo, tal como aparece desde el interfaz de administración (del pgadmin3, por ejemplo):

Es decir, una columna de tipo entero (primera línea), que además no puede ser nula (tercera línea) y que tiene como valor por defecto (SET DEFAULT) el siguiente valor (nextval) de la secuencia que la BD ha creado implícitamente.

Poblar una tabla tampoco es una cuestión baladí. Si no se tienen los datos en un fichero, se pueden meter a través del interfaz gráfico que ofrecen los programas de administración o los interfaces web. Por ejemplo, en la imagen se muestra el interfaz, similar a una hoja de cálculo, para la tabla creada anteriormente.

Otra opción es importar el fichero que esté en otro formato. Si tenemos la suerte de que esté en formato SQL no hay que hacer nada especial, simplemente ejecutarlo como la sentencia SQL que es (pero hay que tener cuidadín de que esté en un formato estándar, si no, lo más probable es que tengas problemas). Si está en otro formato, por ejemplo CSV (valores separados por comas), en PostgreSQL se puede usar la orden COPY. Por ejemplo, a partir de este fichero: Juan,González Martínez,coleguilla Pepe,Poo Fo,baranda usando la orden: template1=# copy prueba2 (nombre,apellidos,puesto) from '/camino/completo/al/dir/gente.txt' with delimiter ','; COPY cuyo resultado es: template1=# select * from prueba2; id | nombre | apellidos | puesto ----+--------+-------------------+------------ 1 | Juan | González Martínez | coleguilla 2 | Pepe | Poo Fo | baranda (2 rows) La orden indicada copy necesita especificar las columnas de la tabla que están en el fichero, en caso de que sólo tenga algunas columnas. La primera no la especificamos porque, al ser de tipo serial, se crea automáticamente por parte de PostgreSQL. A continuación, se pone from y el nombre del fichero a leer. Con este nombre de fichero hay que tener cuidado. Para empezar, se debe especificar el camino completo, no el camino relativo. Hay que tener en cuenta que estamos dando esta orden desde un cliente que se conecta a un servidor, el daemon de la base de datos, que es el que efectivamente ejecuta esa orden; por lo cual, para empezar, el fichero debe estar en el servidor, no en el cliente, y, para seguir, el camino debe ser el camino en el que se encuentre en ese servidor. Si queremos insertar un fichero que se encuentre en el cliente, no podemos usar esta orden. En cuanto a la humilde coma, veis que se ha especificado en la misma orden mediante use delimiter.

El problema con la orden COPY es que sólo puede usarla el superusuario, o el que tenga privilegios similares; si la usa uno cualquiera, dará el error correspondiente. Pero no hay que preocuparse: se usa la orden equivalente del interfaz psql, \copy, que tiene exactamente la misma sintaxis, salvo por el ; al final: template1=# \copy prueba2 (nombre,apellidos,puesto) from '/camino/completo/al/dir/gente.txt' with delimiter ','

Con esto, ya está creada la base de datos y tiene algún contenido. Todavía no conocemos ninguna orden para verlo, pero usando órdenes del cliente psql se puede consultar, al menos, qué tablas hay creadas y cuál es su formato. \d lista las tablas creadas, junto con su propietario, template1=> \d Listado de relaciones Schema | Nombre | Tipo | Dueño --------+------------------+-----------+---------- public | agenda | tabla | cpwb0009 public | coches | tabla | cpwb0006 public | contactos_id_seq | secuencia | cpwb0002 public | mia | tabla | cpwb0005 public | piezas | tabla | cpwb22 public | prueba | tabla | cpwb21 (6 filas) y \d nombretabla nos da información sobre la estructura de una tabla: template1=> \d prueba Tabla "public.prueba" Columna | Tipo | Modificadores -----------+-----------------------+--------------- dni | character varying(12) | not null nombre | character varying(40) | not null apellidos | character varying(50) | not null puesto | character varying(40) | not null Ãndices: "prueba_pkey" llave primaria, btree (dni)

Veremos a continuación qué más se puede hacer con una tabla. Por ejemplo, ver el contenido...

Trabajando con la base de datos

Así, a la chita callando, ya hemos visto anteriormente cómo se pueden consultar los contenidos de la base de datos:

La orden select permite hacer consultas sobre una o varias tablas simultáneamente, y, de hecho, seleccionar parte de los contenidos de la misma atendiendo a varios criterios. En el caso general, select va seguido del nombre de las columnas a seleccionar, más from y el nombre de la tabla. Por ejemplo: template1=# select apellidos,puesto from prueba2 ; apellidos | puesto -------------------+------------ González Martínez | coleguilla Poo Fo | baranda (2 rows) sólo seleccionaría dos de las cuatro columnas de las que consta la tabla. En este caso hemos usado *, que selecciona todas las columnas. Y se le pueden añadir una serie de opciones: where hace que sólo se listen las columnas que cumplan un determinado criterio: template1=# select apellidos,puesto from prueba2 where puesto='coleguilla'; apellidos | puesto -------------------+------------ González Martínez | coleguilla (1 row) ; detrás de where hay una condición, que sigue más o menos la sintaxis habitual en este tipo de cosas: mayor, menor, en fin, todo tipo de expresiones que sería largo enumerar aquí. La condición se refiere al valor de una columna, a la cual nos referimos simplemente por su nombre; en este caso estamos seleccionando todas las filas que tengan el valor "coleguilla" en la columna puesto. Puede haber expresiones más complicadas (combinando los valores de varias columnas, o aplicándole una función). Por otro lado, también se puede ordenar el listado: template1=# select apellidos,puesto from prueba2 order by apellidos desc; apellidos | puesto -------------------+------------ Poo Fo | baranda González Martínez | coleguilla (2 rows) lo pone por orden alfabético descendente de los apellidos; order by indica que el orden no es el natural (habitualmente el de inserción en la base de datos), sino otro; y el desc de después del nombre de la columna indica que es orden descendente; asc sería orden ascendente.

Poco más se puede hacer con este material, así que habrá que meter más información en la base de datos. La orden insert es la que se usa para ese cometido.

Tras la orden insert, inseparable del into bajo pena de error de sintaxis de interpretación ambigua, nos encontramos el nombre de la tabla (prueba) seguido de los nombres de columnas (separados por comas, y entre paréntesis, que no falte eso) que vamos a insertar; la primera columna se supone que es insertada automáticamente por el SGBD, así que no hay que ponerla. Posteriormente, la palabra values y los valores que se van a insertar en las columnas. Y aquí hay que hacer un pequeño aparte para hablar de los tipos de datos constantes que se usan en el cliente psql.

El tipo de datos más simple es el número, tal como 1, o 7325. Aparte de tener en cuenta el locale (es decir, las diferentes convenciones locales, definidas en el sistema operativo, para cosas como fechas, números y códigos de caracteres) para interpretar los puntos y las comas (1.500 es mil quinientos en España, pero 1,5 en paises sajones), no tiene mucha más historia. Se pone tal cual. Las cadenas van entre comillas simples, repito, simples y se usan en todos los tipos cadena, es decir, char, con varying o no, y text. Otro tipo que puede aparecer en este tutorial es el lógico o booleano, que usa los valores true o false (sin necesidad de comillas, aunque también lo admite con ellas) o simplemente t y f. Por último, la fecha tiene en cuenta también el valor del locale en cuanto a los nombres de los meses y de los días de la semana y la forma de introducirla numéricamente : 7/01/2005 sería el 7 de enero de 2005 para los españoles, pero el 1 de julio de 2005 para los americanos; para introducirlo se usa una cadena con el año, mes y día. La hora usa el formato sexagesimal: cantidades separadas por :. Por ejemplo, si sacamos la fecha del sistema con la siguiente orden (current_timestamp es la función del sistema que devuelve la hora actual en el formato timestamp): template1=# select current_timestamp; timestamptz ------------------------------- 2005-02-26 19:28:07.050239-05 (1 row) podemos ver el formato actual de la fecha y la hora. El estilo de fecha se puede cambiar, por cierto.

Vamos a definir otra tabla:

Donde la última columna tiene un valor por defecto (lo que se indica con la opción default, que es current_timestamp, el sello temporal actual, una función del SGBD). Ya que una de las columnas tiene automáticamente un valor, no hace falta especificar qué columnas son las que se van a usar, así que para insertar datos en esta tabla, podemos usar otro formato:

Lo que aparece iluminado es lo que el usuario introduce. Basta con usar value seguido por los valores que se van a insertar en la BD, que el SGBD va insertándolos por orden en su columna correspondiente. Además, como se ve tras el select, la fecha se ha introducido también automáticamente, como mandan los cánones.

Nota: lo normal es que la columna división hiciera referencia a otra tabla en la BD, pero aquí lo hemos obviado para no enfollonar más el asunto.

Cambiando las tablas

El equivocarse pasa hasta en las mejores familias, y la obsolescencia también, así que en toda base de datos que se precie surge un momento en que hay que quitar cosas de enmedio. La orden delete sirve para eso precisamente, pero siempre tendrá que ir cualificada, bajo riesgo de cargarse la tabla entera alegremente. Por ejemplo, vamos a cargarnos a los curritos, que son los primeros que siempre sufren:

El currito ha sido eliminado irremisiblemente. La cláusula where tiene la misma sintaxis en todos los casos. Una vez más, hay que tener cuidado de usar siempre delete cualificado, pero en el caso de que nos querramos, de todas formas, cargarnos la tabla entera, se usa drop table:

se ventila la tabla dato, para nunca más volver del limbo de los bits.

Borrar no tiene mucha más historia; pero muchas veces, lo que se requiere es actualizar los datos de una tabla, alterando alguna de las columnas de alguna de las filas. Por ejemplo, si queremos ascender a baranda a los coleguillas:

Esta orden actualizaría todos los coleguillas a barandas. Tras la orden en si va el nombre de la tabla, lo que queremos hacer (cambiar el valor de la columna puesto), y a quien queremos hacérselo ( where puesto='coleguilla'); una vez más se usa where para cualificar la orden; y, una vez más, hay que usarlo con mucho cuidado, no vayamos a actualizar más gente de la cuenta. Por ejemplo, si ahora queremos actualizar los barandas a archimandritas:

aunque se puede calificar un poco más, y si los coleguillas no se comportan como dios manda, se les rebaja otra vez a barandas:

En este caso estamos usando una nueva forma de la sentencia where: like, que permite comparar con cadenas incompletas y usar comodines. '%oo%' coincidirá con cualquier cadena que tenga dos 'o's juntas, en este caso, nuestro amiguete Pepe Poo; es decir, % es un comodín que se sustituye por 0 o más letras. Con la orden update se pueden actualizar varias columnas simultáneamente, aunque como nuestra tabla es pequeñita, no da para más.

PostgreSQL y Perl: un matrimonio hecho en el cielo

A diferencia de otros lenguajes, Perl tiene un interfaz unificado o orientado a objetos para acceso a bases de datos; independientemente del SGBD que sea, se usa el mismo interfaz y las mismas sentencias. Este interfaz se denomina DBI, DataBase Interface, y actúa como clase base para todas las clases derivadas, que usan las características específicas de cada SGBD; estas clases derivadas se denominan DBD, o DataBase Drivers. Específicamente, la de PostgreSQL es DBD::Pg.

En general, a continuación hablaremos del interfaz genérico, DBI; se aconseja, además, tratar de programar las aplicaciones basándose en él, de forma que el cambio necesario para que funcione en otro SGBD sea mínimo.

Los programas que usan estos módulos actúan como clientes de la base de datos, conectándose a través de la librería de funciones al programa servidor y transformando las filas y columnas de la BD a estructuras propias de Perl, que es de lo que se trata. En todo caso, trabajar desde Perl (u otro lenguaje de programación) añade una serie de estructuras de datos propias del acceso a la base de datos.

La primera de ellas es el asa, manejador o handle de la base de datos. Es la representación de la conexión a la base de datos dentro del programa, un concepto equivalente al del puntero a un fichero o a un socket abierto (en realidad, más parecido a este último). Para trabajar con una BD, lo primero que hay que hacer es abrir esa conexión, que quedará empaquetada en el handle. Por ejemplo:

Lo único que hace este programa es conectar a la base de datos, desconectarse e imprimir el resultado (que debería ser 1 si todo ha ido bien). Veámoslo en detalle: primero declara los dos módulos que se van a usar, el driver general DBI para BDs, y el particular para PostgreSQL (denominado Pg). En realidad, la segunda declaración no es necesaria en este programa, pero la dejamos ahí por si las moscas. La primera sentencia después de las declaraciones es la que abre la conexión a la BD, creando un handle para referirnos a ella. Lo que va entre comillas es lo que se denomina un DSN, Data Source Name. Tal como está tiene el formato siguiente: primero indica el driver que se va a usar, 'dbi:Pg', y luego dbname= y el nombre de la base de datos que vamos a usar en la conexión. En cuanto al driver de la BD, se carga automáticamente cuando se menciona aquí (lo que puede dar lugar a error si no está instalado). A partir de ahí, un DSN se puede complicar bastante más, añadiendo el puerto al que hay que conectarse, el servidor, y alguna cosa más, de esta forma "dbi:Pg:dbname=$dbname;host=$host;port=$port;". Por lo pronto lo dejamos así; sin embargo, no estamos declarando ni el nombre de usuario ni la clave; el nombre de usuario es el usuario desde el que se ejecuta (y hay que tener cuidado con esto: si se ejecuta un programa desde un CGI, el usuario será el que tenga asignado el servidor web; habitualmente, www-user; esto suele dar lugar a errores en el momento que ese usuario no tenga los mismos privilegios que el usuario desde el que se ha desarrollado y probado el programa), y no se usa ninguna clave. En caso de que se hiciera, habría que añadirle más argumentos:

En un mismo programa puede haber tantos handles como aguante la base de datos. Eso si, cuando se acabe con ellos hay que cerrarlos, aunque habitualmente el terminar el programa de forma normal hace que se cierren automáticamente todos los handles abiertos. La segunda sentencia del programa, disconnect, hace precisamente eso; cerrar la base de datos, que no estará disponible para el programa a partir de ese momento.

Lo que ocurre es que conectándose y desconectándose nos podemos pasar la tarde entera. Lo que interesa es sacar algo de la base de datos; por ejemplo, listar una tabla. Para ello, podemos pasar directamente la orden SQL a la base de datos usando las funciones selectall_*, por ejemplo, selectall_arrayref, que es la que se usa en el siguiente ejemplo (seleccionando.pl): use DBI; my $dbh = DBI->connect("dbi:Pg:dbname=template1"); my $ary = $dbh->selectall_arrayref('select * from prueba2'); print "Id\tnombre\tapellidos\t\tpuesto\n"; print "="x50,"\n"; for ( @$ary ) { my ( $id, $nombre, $apellidos, $puesto )= @$_; print "$id\t$nombre\t$apellidos\t$puesto\n"; } my $rc = $dbh->disconnect();

que da como resultado:

que no es nada bonito, por cierto. Como siempre, en Perl hay más de una forma de hacerlo, incluso en una sola línea:

que da un resultado bastante más bonito (si es que se ve en un navegador, claro)

lo que demuestra que cuanto más ofuscado es el código en Perl, más bonita es la salida. Así que nos vamos a dedicar a analizar el primer ejemplo, que es un tanto más fácil. Vamosavé: $dbh->selectall_arrayref('select * from prueba2') devuelve una referencia a un array en Perl, y cada uno de los elementos de ese array es a su vez una referencia a un array que contiene una fila de la base de datos, es decir, una matriz bidimensional con los datos de la tabla en filae y las diferentes columnas precisamente en columnas. Las referencias en Perl son similares a los punteros en otros lenguajes, salvo que son punteros a objetos ya existentes. En este caso, es un puntero a un array o vector, cada uno de cuyos elementos es a la vez un array o vector. Llamamos $ary a esa referencia, y para desreferenciarla, usamos el símbolo del array, la arroba @: @$ary. El bucle for ( @$ary ) recorre, uno por uno, los elementos del array, asignándolos a la variable perlera por defecto, $_. Por eso, lo que se hace en la primera línea del bucle:

es desreferenciar la susodicha referencia, y asignarlo, de una sola vez, a una serie de variables, que posteriormente se imprimen en la línea siguiente. Esas dos líneas también se resumen en una:

que hace exactamente lo mismo usando la orden join, que convierte un vector en una cadena, y separa los elementos del vector por la cadena indicada, en este caso el tabulador, \t.

También se pueden hacer de otras formas o maneras, usando otra sentencia diferente que extrae los resultados en forma de matrices asociativas; viene sobre todo bien cuando cada columna necesita un tratamiento diferenciado, como vamos a hacer en el siguiente programa.

En este caso, estamos usando selectall_hashref, que, en vez de dar el resultado en una referencia a un vector, lo da en una referencia a un hash o matriz asociativa, una de mis estructuras de datos favoritas; como primer argumento se le pasa la sentencia SQL (que en este caso no selecciona todas las columnas, sino sólo el id, los nombres y los apellidos), y como segundo argumento la columna que se desea usar como clave o key del hash resultante; como es lógico y natural, se trata de que esa columna seleccionada sea, si no una clave primaria (de las que hemos explicado más arriba), al menos UNIQUE; lo mejor, claro está, es que sea de las primeras, como en este caso. El resultado de esa petición será esta estructura de datos (el aspecto con el que aparece debajo se obtiene con la función Dumper del módulo Data::Dumper):

u séase, lo que viene siendo un hash de hashes; el primero usa como clave la columna indicada, y el segundo usa como claves los nombres de las columnas. Y ese metasuperhash es mismamente el que imprimimos en las líneas siguientes, que necesitan un tanto de explicación: al principio del programa, se usa CGI qw(:standard *table *Tr);, que importa en el espacio de nombres actual no sólo las funciones estándar del módulo CGI, sino que también genera automágicamente otras funciones que no están en ese módulo, start_ y end_ para la etiqueta table y lo mismo para la etiqueta HTML Tr. Eso es lo que usamos precisamente en las líneas 8 y 9 del programa. En la línea 10 es donde se inicia el bucle que recorre el hash: precisamente recorre las claves del hash ordenadas alfabéticamente, para lo cual previamente desreferenciamos (%$href) lo referenciado.

Dentro del bucle, precisamente, se imprime la primera columna de la tabla, y luego se usa otro bucle (con variable de bucle explícita $k que recorre las columnas restantes. El resultado es tal que así:

Guardando el sitio: repitiendo ejecución de sentencias.

Aunque la forma anterior de usar la BD era bastante similar a usarlo desde un cliente tal como psql, en realidad es bastante ineficiente, porque hay que enviar la sentencia SQL a través de la conexión, e interpretarla, y eso se hace cada vez que se ejecuta una sentencia. Es mucho más eficiente enviar la sentencia, que se interpreta por parte del SGBD, y posteriormente enviarle los parámetros que se tienen que atar a tal sentencia. Eso es lo que se hace en el programa siguiente.

Esta es la forma más habitual, y más eficiente, de acceder a la base de datos a bajo nivel: un ciclo de preparación, ejecución y extracción de resultados (fetch); por cada preparación de sentencia se pueden hacer varias ejecuciones, y por cada ejecución, varias búsquedas o extracciones (aunque se pueden hacer todas de una vez, usando la sentencia que hemos visto anteriormente).

En la primera línea es donde se hace la preparación, con una sentencia SQL en la que se sustituyen las variables con símbolos de interrogación (que ejercen la función de placeholders, es decir, de indicar qué partes de la sentencia SQL van a ser sustituidas por diferentes valores); lo que indicamos con esa sentencia es que vamos a listar los registros contenidos entre dos números de serie, que no conocemos de antemano. No hace falta que se usen comillas aunque vayamos a sustituirla por cadenas o fechas o lo que sea, el driver lo hace automáticamente (que pa eso está, que se lo curre, joé). La sentencia prepare devuelve un handle de sentencia, statement handle, que precisamente por eso representamos por $sth; una agarradera de sentencia de este estilo representa precisamente a la sentencia, y, por decirlo así, es como una sentencia compilada.

En la segunda línea es cuando se le asignan valores a esas variables, por orden. Si se quiere ligar cada parámetro por separado, se usa bind_param. La sentencia anterior sería equivalente a estas tres:

Esta opción se puede usar, por ejemplo, en caso de que uno de las variables vaya a quedar fija y vaya a variarse sólo la segunda, o cualquier otra. bind_param toma como argumentos el número de parámetro y el valor del mismo; la primera sentencia liga el primer parámetro (de ahí el 1), y la segunda, lo averiguásteis, el segundo (de ahí el 2).

Una vez la sentencia se ha ejecutado, hay que extraer el resultado; para eso se hace un bucle sobre el handle de sentencia, cuyo resultado será verdadero (es decir, continuará la ejecución) mientras haya más columnas que sacar de la base de datos, y falso cuando se acaben (el símil del fichero funciona aquí también). El equivalente a la lectura de una línea es en este caso la función fetchrow_array, que devuelve cada registro en forma de array (no de referencia); esa es la que se imprime en la línea siguiente, en forma de fila de una tabla. El resultado de todo esto es el esperable.

Más útil resulta toda esta movida en caso de insertar registros procedentes de un fichero. Por ejemplo, el fichero de gente que usamos anteriormente podíamos insertarlo usando el siguiente programa:

En este caso la preparación ha sido de una sentencia de inserción, que va seguida simplemente por varias sentencias de ejecución, ya que no se recuperan los resultados de la BD. El bucle posterior abre el fichero y comienza a leerlo línea a línea, incluyendo cada línea en la variable por defecto $_; esa variable se parte usando split y una expresión regular, en la que no tienes que fijarte mucho, salvo que lo que significa es que usa como divisor entre un contenido y el siguiente una coma precedida o seguida por 0 o más espacios; de esta forma, la primera palabra va a parar a la variable $nombre y el resto donde deben.

La siguiente sentencia ejecuta el código SQL que se ha preparado anteriormente ligando los placeholders a los valores de las variables que se han leido. Todo esto resulta sumamente eficiente, que conste. Por cierto, que se puede usar la misma técnica para implementar valores por defecto en el cliente :

En este caso, lo que hacemos es asignar un valor por defecto al puesto mediante el ligado del tercer parámetro a un valor determinado; el resto de los valores losligamos dentro del bucle. En este caso, el fichero contendrá los valores siguientes: nombre y apellidos, sin poner el puesto. ¿Hemos ahorrado mucho? Pues no, pero ¿y lo que hemos aprendido?

También podemos usar el propio módulo DBI para leer ficheros que tengan una estructura determinada. DBD::Anydata sirve precisamente para eso, y lo usamos en el siguiente programa:

#!/usr/bin/perl

use strict;
use warnings;
use DBI;

my $dbh = DBI->connect("dbi:Pg:dbname=test");
my $sth = $dbh->prepare('insert into prueba (dni,nombre,apellidos,puesto) values (?,?,?,?)');
my $any_dbh = DBI->connect('dbi:AnyData(RaiseError=>1):');
$any_dbh->func( 'gente', 'CSV', 'gente2.csv', 'ad_catalog');
my $csv_sth = $any_dbh->prepare("SELECT * FROM gente");
$csv_sth->execute();
my $dni = 1;
while ( my ($nombre, $apellidos, $puesto ) = $csv_sth->fetchrow_array() ) {
  $sth->execute($dni++,$nombre,$apellidos,$puesto);
}
my $rc = $dbh->disconnect();
my $rc2 = $any_dbh->disconnect();

      

Módulos a otro nivel: DBIx::Abstract

Lo de saber SQL y acceder a la BD a bajo nivel está bien, pero una vez conocido, es mejor ahorrarse lo máximo posible toda esa historia. Especialmente, usar realmente las estructuras de datos del lenguaje de programación en el que se está trabajando en vez de tanto SQL y tanta gaita. Para ello, hay una serie de módulos en Perl llamados DBIx, que ofrecen un tipo de acceso a las bases de datos de más alto nivel. Uno de ellos es DBIx::Abstract, que convierte cada una de las posibles sentencias de SQL en funciones. Lo vamos a usar en el programa siguiente:

Este programa viene a ser un resumen de los anteriores, usando el nuevo módulo. La principal diferencia es que no hay ni una sola línea de SQL; el SQL, como la procesión, van por dentro. Vamos a verlo poco a poco.

Para empezar, la conexión es más o menos similar a la habitual; salvo que, en este caso, hay que pasarle un hash. Lo más parecido a la forma anterior es como se ha hecho; sin embargo, también se puede hacer de la forma siguiente:

De todas formas, ese tipo de cosas a veces funciona y a veces no. Por eso, lo más fácil es pasar el DSN, tal como se hace en los drivers DBI.

Lo que hemos hecho a continuación es insertar elementos en la BD. En vez de usar una sentencia SQL con insert, usamos la función insert del módulo, a la que hay que pasarle como argumento la tabla donde se va a insertar, y un hash con los valores a insertar. Si se van a insertar todos los valores en el mismo orden de declaración, se pasa simplemente una cadena. Por si las moscas, es mejor así. Internamente, DBIx::Abstract traslada esa sentencia a una sentencia SQL, que ejecuta usando el driver que se le ha indicado en la conexión.

A continuación se tratan de listar los contenidos de la base de datos, usando la sentencia más simple: dos columnas (nombre, apellidos) de una tabla (prueba2) que tienen el puesto de currito: ese es el formato general se la orden select: columnas, tabla, expresión where; todo esto, como en el caso anterior, se puede hacer mediante un hash, sobre todo si hay que añadir más opciones: ordenación, por ejemplo:

En este caso, le pasamos un hash con los campos, la tabla y el orden en el que queremos que nos lo ordene. El resultado es el esperado. Si hubiera un campo where no habría sido necesario usar el hash: podríamos haberlo usado directamente como en el ejemplo anterior.

Pero claro, una sentencia select es solamente eso, una sentencia, y lo que devuelve es un handle de sentencia, tal como hemos visto en secciones anteriores. De ese handle de sentencia se tienen que extraer los resultados tal como lo hemos hecho también anteriormente; pero nos podemos saltar un paso llamando directamente a fetchall_arrayref en la misma sentencia, encadenando una llamada con otra.

Aún se puede simplificar más la expresión, suprimiendo este último paso:

select_all_to_hashref permite ejecutar las dos operaciones a la vez: creación de un handle de sentencia, y recuperación de los resultados. Devuelve una referencia a un hash, que usa como clave el primero de los campos que se le pasa en fields, en este caso, id. El contenido del hash será un vector con el resto de los elementos que se hayan recuperado de la base de datos, colocados por orden. Para imprimirlos, se usa el bucle siguiente:

que recorre las claves del hash en orden arbitrario, e imprime el nombre y el apellido.

Por último, y ya parecía que ibamos a olvidarnos, está la sentencia update, que sigue más o menos la tónica anterior:

Se le pasan los argumentos más o menos en el mismo orden que la sentencia SQL que va a generar: tabla, la actualización, y lo que queremos actualizar en forma de una sentencia where. En este caso estamos cambiando a daboss todos lo que sean colegas o coleguillas (por eso se pone like '%coleg%', que incluye esos dos puestos, y además, supercolega). Funciona de la forma esperada, y devuelve el número de columnas actualizadas, aunque en este caso no estamos utilizando ese resultado.

Aún hay más módulos que permiten un acceso de más alto nivel: DBIx::ContextualFetch, que simplifica el ciclo de búsqueda y recuperación de tuplas de la base de datos, Ima::DBI, que permite ponerle nombre a las sentencias y usarlas como funciones, y, sobre todo, Class::DBI, que mapea las tablas de la base de datos directamente a objetos que se pueden usar de forma nativa. Esta última clase es la más potente, pero explicarla queda fuera del rango de este tutorial.

Transacciones

PG admite transacciones, pero para hacer compatible el código con otras BD, por defecto las transacciones están desactivadas: cada transacción es atómica, y se actualia la BD al final de cada petición SQL. Pero se pueden activar desde el interfaz de texto usando BEGIN y END TRANSACTION; hasta que no se finalice la transacción los cambios no son definitivos, y si no se finaliza (falla por alguna razón), toda la transacción completa se puede anular.

Desde el interfaz de programación, hay que inicializar el handle de otra forma:

$dbh = DBI->connect("dbi:Pg:dbname=$dbname", "", "", {AutoCommit => 0});

activa las transacciones, y para enviar las transacciones a la BD habrá que indicar un $dbh->commit().

Bibliografía y enlaces relacionados con bases de datos, SQL y su uso desde Perl.

En el sitio web de PostgreSQL se puede encontrar todo tipo de información relativa al PostgreSQL, incluyendo el manual completo. La mayor parte de las instalaciones de PostgreSQL contienen también un manual bastante completo de uso, programación y administración. Es cuestión de buscar en /usr/share/doc o sitios similares.

Todos los módulos de los que he hablado, y muchos más, están en CPAN, el repositorio de módulos de Perl más dicharachero.

Sobre PostgreSQL, el libro del Caballo o el del elefante de O'Reilly es una buena referencia. Lo puedes conseguir en Amazon o, si tienes acceso, en Safari, el servicio de libros online de O'Reilly, donde no lo he encontrado, pero tienen este otro. También hay un libro de acceso a BD usando Perl: el libro del leopardo, (que también está disponible online en Safari).