Columnas que se auto-incrementan en PostgreSQL

11.12.09. Por ooscarr (ooscarr)

PostgreSQL

MySQL tiene la posibilidad de crear columnas que se auto-incrementan en la misma definición de la columna a travé del comando AUTO_INCREMENT, no así PostgreSQL. En Postgres es más complicado, y de esta manera lo hago yo.

1. Crear secuencia

Con pgAdmin III

pgAdmin3 es una aplicación multi-plataforma que sirve para administrar las bases de datos PostgreSQL hecha por la misma comunidad PostgreSQL. Si l quieres hacer por líneas de comandos, avanza un poco más en este artículo.

Una vez abierto el pgAdmnin3 y hecha la conexión:

  1. Expando mi base datos, mi Esquema, elijo con el botón secundario del mouse la sección Secuencias y elijo en el menú contextual la opción Nueva Secuencia...

    Menú contextual sobre las Secuencias de una base de datos PotgreSQL en pgAdmin3.

  2. En el formulario le pongo un nombre (en mi caso se llamará transferencias_id), el usuario que hará uso de esta secuencia (en mi caso, el usurio se llama ooscarr) y OK. También puedo elegir si quier que se auto-incremente a partir de un número diferente al 1 y/o si quiero que el número vaya aumentando de 2 en 2, 3 en 3, etc. en vez de 1 en 1 como se pone por defecto.

    Diálogo de secuencia nueva en pgAdminIII. Si sólo se llenan los campos Nombre y Propietario, PostgreSQL los llenará automáticamente con incremento de 1 en 1, desde el número 1, y le asignará una OID, etc, etc.

Lo mismo, desde la línea de comandos

si seleccionas la nueva secuencia que se agrega a la lista puedes ver el comando SQL que se utilizó para crearla. Lo mismo se pudo haber hecho desde la línea de comandos psql con:

CREATE SEQUENCE transferencias_id;

ó

CREATE SEQUENCE transferencias_id
  INCREMENT 1
  MINVALUE 1
  START 1
  CACHE 1;
ALTER TABLE transferencias_id OWNER TO ooscarr;

transferencias_id es el nombre que le asigné a la secuencia y ooscarr es el usuario de PostgreSQL que es dueño de esta variable auto-incrementable.

2. Usar la secuencia en una columna

Con pgAdmin3

  1. Expandemos la tabla que queremos modificar, expandemos Columnas, y seleccionamos la columna de la tabla que se deseas que se auto-incremente con esta variable, vas a las Propiedades....
  2. En el inspector de columna, le asignamos el Valor por defecto NEXTVAL('transferencias_id'). Siendo transferencias_id el nombre de la secuencia.

    Diálogo de Propiedades de columna en pgAdminIII, con el campo Valor por defecto NEXTVAL('transferencias_id').

  3. OK

Con comandos (psql)

Sería...

ALTER TABLE transferencias 
    ALTER COLUMN id 
        SET DEFAULT NEXTVAL('transferencias_id');

transferencias_id es el nombre de la secuencia, id es el nombre de la columna a asignarle la secuencia y transferencias es el nombre de mi tabla donde está la columna con al secuencia.

Actualizar filas anteriores sin la secuencia

Con comandos

Para cambiar los valores null de las filas creadas antes de la asignación de la variable auto-incrementable, consultamos:

UPDATE transferencias 
    SET id = NEXTVAL('transferencias_id');

Ese paso final es una de las cosas más lentas que puedes pedirle a Postgres que haga posiblemente. Para una tabla de tamaño mediano (alrededor de 5,000,000 tuplas, con unas cuantas columnas de pequeños números y textos), eso tomó cerca de 2.5 horas en hardware poderoso - así que querrás dejar esto por un tiempo tranquilo. Afortunadamente Postgres interpreta el UPDATE como una transacción atómica: nada se realiza hasta que el comando se complete, así será difícil que dejes los datos en un estado inconsistente.

Y ya con eso, cada vez que crees una nueva fila en esa tabla, esa columna se llenará automáticamente con un número distinto automáticamente.

Referencias

Artículos relacionados

Etiquetas: , ,

Conectar a la base de datos desde JSP

3.9.08. Por ooscarr (ooscarr)

Netbeans

Con JSTL, conectarse a una base de datos es sencillo y no requiere todo ese peligroso código Java que antes producía dolores de cabeza y frustración.

Preparación

ACTUALIZACIÓN: Netbeans 6.5 agregó al asistente para crear nuevos proyectos web, un formulario extra para ingresar los drivers y los datos de la base de datos desde el principio.

Luego de crear el proyecto JSP y antes de conectar a la base de datos, hay que instalar las librerías Java para la base de datos.

  1. Vamos a las propiedades del proyecto, en la sección Librerías presionamos el botón Add Library... (Agregar librería...)

    Panel de propiedades del proyecto en Netbeans

  2. Importar

    Importar librería en Netbeans

  3. Seleccionamos la librería de PostgreSQL (o Mysql, según...)

    PostgreSQL JDBC Driver
    Aprovechar de agregar la JSTL si no ha sido incluida.

  4. Add Library (Agregar Librería)

    Add Library

  5. OK

Conexión

Luego creamos un JSPF para la conexión

  1. Nuevo archivo > JSP

  2. Le ponemos un nombre...

    Nuevo archivo JSP(F) en Netbeans
    Nos aseguramos que el archivo quede en la carpeta WEB-INF/jspf

El árbol de archivos del proyecto indicando Web Pages > WEB-INF > jspf > conexion.jspf

conexion.jspf

El código del archivo conexion.jspf contendría sólo y nada más que:

<sql:setDataSource driver="org.postgresql.Driver"
 url="jdbc:postgresql://locahost:5432/usuario"
 user="usuario"
  password="contraseña"
 var="bdatos" />

* Si se utiliza Mysql, cambiar parte de la segunda línea por jdbc:mysql://
** Si el servidor se encuentra en otra máquina, cambiar la parte locahost:5432 por la IP y el puerto correspondiente.

La conexión se guarda en una variable var="bdatos" para poder ser usada después en el código.

Código

Ya en el código JSP, primero agregamos el tag sql

<%@taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql"%>

Y después llamamos a la parte de la conexión como siempre con:

<%@include file="/WEB-INF/jspf/conexion.jspf" %>

Por ejemplo, el código completo de una página con una consulta simple sería:

index.jsp

<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<%@taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html>
 <head>
  <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
  <title>Página JSP</title>
 </head>
 <body>
  <h1>¡Hola, mundo!</h1>

<%@include file="/WEB-INF/jspf/conexion.jspf" %>
<sql:transaction dataSource="${bdatos}">

 <c:catch var="ex">
  <sql:query var="encontrados">
   SELECT primer_nombre FROM persona LIMIT 4
  </sql:query>
 </c:catch>


 <c:choose>
  <c:when test="${not empty ex}">
   <p>Problema: <c:out value="${ex.message}"/></p>
  </c:when>
  <%-- Si no hubo ninguna excepción --%>
  <c:otherwise>


   <c:choose>

    <%-- Si encontró algo --%>
    <c:when test="${encontrados.rowCount>0}">
     <ol>
      <c:forEach items="${encontrados.rows}" var="persona">
       <li><c:out value="${persona.primer_nombre}" /></li>
      </c:forEach>
     </ol>


    </c:when>
    <c:otherwise>
     <p>No se encontraron datos.</p>
    </c:otherwise>
   </c:choose>

  </c:otherwise>
 </c:choose>
</sql:transaction>
 </body>
</html>

Ejecución

ver index.jsp.

Referencias

Artículos relacionados

Etiquetas: , , , ,

¿Respaldemos la base de datos?

25.5.08. Por ooscarr (ooscarr)
PostgreSQL

Mi nombre es Oscar, y dentro de una hora nos van a borrar/formatear las cuentas que usamos para trabajar en mi Universidad. Y si bien respaldé mis archivos mediante FTP, todavía me queda información y trabajo almacenado dentro de las bases de datos que están en los servidores. Lo mismo podría ser útil si uno se va a cambiar de empresa de hosting (si es que hubiera alguna que ofrezca PostgreSQL en sus planes más económicos). Éste es un tutorial for dummies™.

Conectando al servidor ACInfo (acinfo.unap.cl:5432)

PgAdmin III

Con las últimas versiones del software PgAdmin III, respaldar una base de datos es muy sencillo. Si nuestra base de datos no es muy complicada en cuanto a usuarios, grupos y esquemas; o su volumen no es demasiado grande (por ejemplo, 6 millones de datos).

El respaldado

Para respaldar con el programa, como primer paso lo único que hay que hacer es hacer click con el botón derecho sobre algún objeto de tu dominio y elegir la opción Resguardo... del menú contextual.

También accesible desde el menú “Herramientas” > “Resguardo...”

Luego aparecerá una cuadro de diálogo con distintas opciones dependiendo de dónde vas a hacer la restauración.

Si no tienes permiso de super usuario en el servidor destino (donde vas a hacer la restauración), debes elegir la opción PLAIN:

Imagen del cuadro de diálogo “Resguardar”
En el caso del tipo PLAIN, lo recomendable es que el archivo termine con la extensión .sql
En Windows, es necesario escribir la ruta completa del nombre de archivo a mano (C:\...)

Este tipo de respaldos no permite blobs; para eso, se utilizan los otros formatos y se hace mediante líneas de comandos.

Si tienes permiso de super usuario en el servidor destino lo recomendado es utilizar cualquiera de las opciones COMPRESS o TAR por igual.

Imagen del cuadro de diálogo Resguardar
En el caso del tipo COMPRESS o TAR, lo recomendable es que el archivo termine con la extensión .backup

Luego de eso, es cosa de esperar. Según el sistema operativo, puede o no quedarse el computador paralizado hasta que termine. Ten paciencia o cambia de pantalla con las teclas control + tab (o command + tab en los especiales teclados mac).

Por mientras te cuento que si tienes los puertos bloqueados y no puedes conectarte con pgAdmin III, también existe un sistema para manejar la base de datos desde la web (como phpmyadmin de MySQL) llamado phpPgAdmin, que permiten exportar los datos además de los formatos SQL y pg_dump, en los formatos método COPY (binario) , XML, XHTML, CSV o datos separados por tabs y saltos de líneas. Opciones también disponibles desde las líneas de comandos.

/Applications/pgAdmin3.app/Contents/SharedSupport/pg_dump -i -h acinfo.unap.cl -p 5432 -U ooscarr -F p -O -D -v -f "/Users/ooscarr/Desktop/respaldo.sql" -n 'public' ooscarr

El proceso retornó el código de salida 0.

Para una mejor descripción de cada una de las opciones de exportación con el comando pg_dump (en inglés), visita la documentación oficial de PostgreSQL

La restauración

Hay dos formas de hacer la restauración con el programa pgAdmin III, y dependerá del formato del archivo de respaldo o los permisos de usuario que tengas en el servidor donde deseas recuperar la información.

Con el archivo .sql
SQL

Si no tienes permiso de super usuario e hiciste el respaldo en modo PLAIN, lo único que hay que hacer es ir al editor SQL (Herramientas > Herramienta para Consultas), abrir el archivo desde ahí y presionar el botón Play (Consulta > Ejecutar). Hecho.

Con el archivo .backup

Si tenemos un archivo .backup, necesitaremos los permisos necesarios en la base de datos para seleccionarla con el botón derecho y elegir la opción Restaurar...

También accesible desde el menú “Herramientas” > “Restaurar...”

Cuadro de opciones de restauración

Y con eso estaría respaldado y restaurado todo. :-) Por supuesto que podrían haber riesgos y complicaciones y habría que entrar a picar por el SSH, pero para qué nos vamos a poner tan negativos. Yo asumo que las bases de datos destino estaban vacías y la configuración de las tablas no era tan especial.

Fin

Ya tengo mis archivos respaldados, mi base de datos está respaldada... Existen otros métodos para replicar los datos sin necesidad de crear un archivo sino que directamente y otras formas de respaldo automático (Slony-I). Ahora me iré a ver cómo respaldar mi base de datos MySQL, nos vemos.

Referencias

Etiquetas: , ,

Cómo agregar el lenguaje PL/pgSQL a PostgreSQL

19.5.08. Por ooscarr (ooscarr)
PostgreSQL

Cuando se inicializa la base de datos postgres manualmente por primera vez, no se instala el lenguaje de programación procedural de funciones PL/pgSQL automáticamente. Así que tendremos que hacerlo nosotros mismos.

En nuestra base de datos

Por pgAdmin III

La manera más sencilla de instalar los lenguajes procedurales, como PL/pgSQL, es a través de la interfaz gráfica del software pgAdmin III.

  1. Una vez que el servicio PostgreSQL está iniciado y funcionando...
  2. Abrimos el software pgAdmin III y vamos al panel de preferencias
    Captura de pantalla de cómo llegar al panel de preferencias en Mac OS X
  3. Nos aseguramos que en la ficha Visualizar esté marcada la casilla Lenguajes y presionamos OK.
    Captura de pantalla del panel de preferencias
  4. Nos conectamos al servidor o refrescamos la base de datos si es que ya estábamos conectados.
    Para refrescar la base de datos, sobre el nombre del objeto, con el botón derecho del ratón, elegir Refrescar
  5. Seleccionamos el objeto Lenguajes y le damos click a la opción Nuevo Lenguaje....
    Captura de pantalla del menú contextual Nuevo Lenguaje... en Mac OS X
  6. Elegimos nuestro lenguaje favorito en el campo Nombre y presionamos el botón OK.
    Captura de pantalla de las opciones de lenguajes procedurales predefinidos
  7. Ya podemos disponer del lenguaje seleccionado para crear nuevas funciones en PostgreSQL.
    En el panel de nueva función ya aparece el lenguaje plpgsql

¿Fácil, no?

* Si lo anteriormente mencionado no funciona, pruebe conectándose al servidor de la base de datos con un super usuario de postgreSQL.

Con comandos

Lo mismo se podía hacer con una simple sentencia SQL:

CREATE LANGUAGE plpgsql

Para agregar el lenguaje por defecto

Se puede hacer que un lenguaje procedural se instale automáticamente en cada nueva base de datos que creamos, para esto...

Desde pgAdmin III

  1. Abrimos el software pgAdmin III y vamos al panel de preferencias
    Captura de pantalla de cómo llegar al panel de preferencias de pgAdmin3 en Mac OS X
  2. Nos aseguramos que en la ficha Visualizar esté marcada la casilla Mostrar Objetos del Sistema en la vista de árbol? (...) y presionamos OK.
    Captura de pantalla del panel de preferencias
  3. Antes de conectar a la base de datos, cambiamos el nombre de usuario...
    Para elegir el nombre de usuario a conectar, sobre el nombre del servidor, con el botón derecho del ratón, elegir Propiedades
    ...por uno que tenga permisos de super usuario.
    Captura de pantalla del panel de servidor
  4. Una vez conectados, le agregamos el lenguaje a la base de datos llamada template1.
    Captura de pantalla del menú contextual Agregar Lenguaje sobre la base de datos template1 en Mac OS X
  5. No hay paso 5, excepto dejar las conexiones como estaban.

Con comandos

Hacer lo mismo por comandos es más complicado porque hay que hacerlo no por SQL sino que por shell:

createlang plpgsql template1

Palabras finales

Ahora que instalamos PL/pgSQL y otros lenguajes predefinidos, tenemos un entorno PostgreSQL listo para trabajar.

También es posible utilizar otros lenguajes personalizados, cuya instalación tendrán que estudiarla en la documentación oficial que está en las fuentes.

Las funciones de PostgreSQL no sólo pueden ser definidas en el lenguaje estándar SQL, sino que en cualquier otro lenguaje que a usted se le ocurra y pueda implementar, como por ejemplo:

  • PL/pgSQL (similar al PL/SQL de Oracle)
  • C
  • C++
  • PL/Java
  • PL/Perl
  • plPHP
  • PL/Python
  • PL/Ruby
  • PL/sh
  • PL/Tcl
  • PL/scheme
  • PL/R
  • PL/Lua
  • PL/LOLCODE !
  • PL/Parrot !

Fuentes

Artículos relacionados

Etiquetas: , , ,

Cómo instalé PostgreSQL en Mac OS X

5.5.08. Por ooscarr (ooscarr)
PostgreSQL

El último sistema operativo de Apple viene con MySQL pero no incluye el poderoso sistema de bases de datos OpenSource PostgreSQL. Y para los sistemas UNIX no proveen un archivo binario de fácil instalación suponiendo que es sencillo para todos los realmente interesados (comentan que sí). Aquí entrego una guía típica de instalación de este tipo de programas que sirve tanto para Mac OS X, Linux, BSD y UNIX en general. Es para todos casi lo mismo.

Primero

  1. Se recomienda a todos descargar la última versión más conocida por estos días como iPhone SDK desde la página de Apple. O instalar las Developer Tools (que vienen en el disco de instalación) si usas Tiger.
  2. Descargar la última versión de PostgreSQL desde postgresql.org (los .md5 son las firmas digitales, no los archivos que queremos). De preferencia, en el escritorio.
  3. Hacer doble click en el archivo recién descargado para que se descomprima y ya tenemos la carpeta para compilar.

Compilar PostgreSQL

  1. Abrimos el Terminal (Aplicaciones > Utilidades > Terminal)
  2. Escribimos el comando
    $ cd
    luego espacio, arrastramos y soltamos la carpeta recién descomprimida en la ventana del Terminal para que quede la ruta escrita en la misma línea. Enter (o return). Debería quedar algo como:
    $ cd /Users/ooscarr/Desktop/postgresql-8.3.1 

Configure

Antes de comenzar a compilar, hay que generar los Makefiles. Esto se hace ejecutando el script incluido configure con parámetros opcionales. El comando y los parámetros que elegí fueron los siguientes:

./configure --enable-thread-safety \
                    --without-docdir \
                    --with-perl \
                    --with-gssapi \
                    --with-pam \
                    --with-bonjour \
                    --with-openssl

No usé --prefix= porque no tengo problemas en que Postgres se instale en /usr/local. Tampoco --exec-prefix= porque no necesito que sea independiente de la plataforma, es para instalarlo aquí mismo.

En general no recomiendo personalizar las carpetas donde Postgres va a instalar sus archivos, para tener una instalación de lo más normal y conocida que permitan pedir ayuda para arreglar problemas sin complicaciones. Leopard, como UNIX, viene con readline instalado, así que no hay muchas complicaciones. El --without-docdir es para que no instale la documentación en inglés, pero lo puedes obviar sin problemas.

El --enable-dtrace suena tentador, pero no es necesario ya que no vamos a analizar el proceso de compilación y, según el archivo README, ahora sólo tiene soporte para Solaris (un muy buen sistema operativo para desarrolladores).

Al presionar return se demora un poco porque está revisando las librerías, el sistema, etc. Debería aparecer una lista de checking y unas config.status. Todo normal.

Make

Estamos listos con el código fuente, ahora falta compilar. Comando:

$ make

Éste se demora más, ocupa más el procesador, pero en unos minutitos debería haber terminado con la frase:

All of PostgreSQL successfully made. Ready to install.

Ya. Está listo para instalar.

Instalación

La instalación es igual de fácil. Lo único que hay que hacer (por ahora) es escribir:

$ sudo make install

Lo típico. Si pide la password, la escribimos y listo. La contraseña no aparecerá en pantalla.

PostgreSQL installation complete.

Eso es todo. PostgreSQL está instalado en /usr/local/pgsql/

pgAdmin III

Antes de seguir, sería bueno instalar la última versión del software pgAdmin III cuya instalación es tan fácil como la mayoría de las instalaciones de Mac OS X: arrastrar un archivo a la carpeta de Aplicaciones.

Crear la base de datos

Al igual que en Windows, se estila crear un usuario dedicado para que PostgreSQL administre los datos. Más bien es un requisito. En mi caso voy a guardar los datos en la carpeta /usr/local/pgsql/data como viene por defecto, pero esto cambia según la necesidad y configuración personal de cada usuario. Para crear la carpeta ahí, utilizo el famoso comando:

$ mkdir /usr/local/pgsql/data

Después hay que crear el usuario. Y como no me molesta tener un usuario más en la pantalla de inicio de sesión (si tienes el inicio de sesión automático, ni lo vas a ver), lo creo normalmente desde Preferencias del Sistema > Cuentas > +

Pantalla de Agregar nuevo usaurio de Mac OS X

Si te molesta el usuario en la ventana de inicio de sesión, lo puedes esconder con la plist correspondiente.

Bueno, ahora que existe el usuario postgres, hacemos que la carpeta /usr/local/pgsql/data pertenezcan a este nuevo usuario.

$ sudo chown postgres /usr/local/pgsql/data

Para inicializar la base de datos, debemos entrar a la cuenta de postgres con

$ su -l postgres

Y la inicializamos por primera vez con el script:

/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data

Al finalizar, aparecerán dos líneas muy importantes, ya que estos comandos se usarán para iniciar el servidor de postgresql para que funcione. En mi caso iniciaré el servidor de PostgreSQL con:

/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start

Y lo terminaré con:

/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile stop

Cabe recordar que estos comandos sólo funcionan desde el usuario postgres así que siempre habrá que iniciar su sesión escribiendo antes:

$ su -l postgres
$ /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start

Ahora que está el servidor corriendo, podemos cerrar todas las ventanas.

Crear los usuarios

La forma más fácil de crear los usuarios es abrir el software pgAdmin3, presionar el ENCHUFE para crear un nuevo servidor dentro del programa con los datos conocidos:

Agregando servidor localhost en pgAdmin III para Mac OS X

Una vez conectado, creamos un nuevo usuario desde Editar > Nuevo Objeto > Nueva rol de Login...

Editar > Nuevo Objeto > Nueva rol de Login...

Aquí se pueden usar nuestros propios datos que se usarán para conectarnos desde los códigos que podamos estar escribiendo.

Agregando datos de usuario de PostgreSQL en pgAdmin III para Mac OS X

Ya podemos crear una nueva base de datos

Menú contextual Crear nueva base de datos

Para el nuevo usuario recién creado

Asignando usuario de nueva base de datos
Recomiendo utilizar la codificación de caracteres UTF-8 como estándar si vamos a usar caracteres que no están en el idioma inglés, como la é con tilde.

Si nos desconectamos (con el mismo ENCHUFE), por seguridad deberíamos ir a Propiedades

Menú contextual Propiedades de la base de datos

... Y cambiar el nombre de la base de datos (BD de Mantenimiento) y el usuario dueño de esa base datos que recién configuramos.

Cambiando datos de base de dato PostgreSQL en PgAdmin III para Mac OS X

Cuando nos volvamos a conectar, nos va a volver a pedir la contraseña del usuario nuevo.

Por favor ingrese la contraseña

Y una vez conectados a la base de datos local, deberíamos tener un PostgreSQL completo funcionando en nuestro computador.

Tablas, triggers y vistas de PostgreSQL en PgAdmin III en español

[ACTUALIZACIÓN] Para que la instalación quede completa, faltó mencionar cómo agregar el lenguaje PL/pgSQL a PostgreSQL.

Palabras finales

Aprendimos a instalar PostgreSQL de manera sencilla en la máquina local desde el código fuente. Ahora sólo me falta aprender a hacer que se inicie automáticamente cada vez que se enciende el computador o crear un alias que inicie la base de datos con un simple doble click y sin escribir los complicados comandos:

su -l postgres
/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start

También es conveniente agregar el directorio /usr/local/pgsql/bin/ al PATH

Referencias

Artículos relacionados

Etiquetas: , , ,

Publicidad