En entradas anteriores explicaba cómo Comparar la estructura de dos Bases De Datos con DbWrench. En esta oportunidad voy a mostrar cómo hacerlo para bases de datos PostgreSQL sin necesidad de instalar ninguna herramienta extra.

Versiones más recientes de pgAdmin 4 incorporan una opción para comparar esquemas de bases de datos PostgreSQL. Esta utilidad lleva como nombre Schema Diff.

Inicialmente les comento que la versión que estoy usando de pgAdmin 4 es la 4.21 para Windows:

He creado dos bases de datos diferentes para demostrar la funcionalidad, una llamada movies y otra movies_copy.

La base de datos movies contiene solo dos tablas genres y series:

/* Tabla de Géneros */
CREATE TABLE public.genres
(
    id serial NOT NULL,
    name character varying NOT NULL,
    CONSTRAINT genres_pkey PRIMARY KEY (id)
);

/* Tabla de Series */
CREATE TABLE public.series
(
    id serial NOT NULL,
    name character varying NOT NULL,
    year character(4),
    id_genre integer,
    CONSTRAINT series_pkey PRIMARY KEY (id),
    CONSTRAINT series_genres_fk FOREIGN KEY (id_genre)
        REFERENCES public.genres (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        NOT VALID
);

Mientras que la base de datos movies_copy contiene tres tablas genres, series (con un cambio en el year) y movies:

/* Tabla de Géneros */
CREATE TABLE public.genres
(
    id serial NOT NULL,
    name character varying NOT NULL,
    CONSTRAINT genres_pkey PRIMARY KEY (id)
);

/* Tabla de Series */
CREATE TABLE public.series
(
    id serial NOT NULL,
    name character varying NOT NULL,
    year character(5),
    id_genre integer,
    CONSTRAINT series_pkey PRIMARY KEY (id),
    CONSTRAINT series_genres_fk FOREIGN KEY (id_genre)
        REFERENCES public.genres (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        NOT VALID
);

/* Tabla de Películas */
CREATE TABLE public.movies
(
    id integer serial NOT NULL,
    name character varying NOT NULL,
    year character(4),
    id_genre integer,
    CONSTRAINT movies_pkey PRIMARY KEY (id),
    CONSTRAINT movies_genres_fk FOREIGN KEY (id_genre)
        REFERENCES public.genres (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
);

Para hacer la comparación solo tenemos que ir al menú Tools -> Schema Diff:

Luego seleccionar las opciones que correspondan en Select Source y Select Target, y hacer click en Compare. Podremos ir explorando las diferencias encontradas.

1. Schema Diff - Nueva Secuencia de Tabla movies encontrada:
   
2. Schema Diff - Nueva Tabla movies encontrada:
   
3. Schema Diff - Diferencia en estructura de Tabla series:
   



Finalmente podemos generar un Script con los cambios encontrados seleccionando todos los items que queremos que sean incluidos y haciendo click en Generate Script:





El Script generado quedaría de la siguiente manera:

-- This script was generated by a beta version of the Schema Diff utility in pgAdmin 4. 
-- This version does not include dependency resolution, and may require manual changes 
-- to the script to ensure changes are applied in the correct order.
-- Please report an issue for any failure with the reproduction steps. 
BEGIN;
CREATE SEQUENCE public.movies_id_seq
    INCREMENT 1
    START 1
    MINVALUE 1
    MAXVALUE 2147483647
    CACHE 1;

ALTER SEQUENCE public.movies_id_seq
    OWNER TO postgres;

CREATE TABLE public.movies
(
    id integer NOT NULL DEFAULT nextval('movies_id_seq'::regclass),
    name character varying COLLATE pg_catalog."default" NOT NULL,
    year character(4) COLLATE pg_catalog."default",
    id_genre integer,
    CONSTRAINT movies_pkey PRIMARY KEY (id),
    CONSTRAINT movies_genres_fk FOREIGN KEY (id_genre)
        REFERENCES public.genres (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
)

TABLESPACE pg_default;

ALTER TABLE public.movies
    OWNER to postgres;

-- WARNING:
-- The SQL statement below would normally be used to alter the datatype for the id column, however,
-- the current datatype cannot be cast to the target datatype so this conversion cannot be made automatically.

 -- ALTER TABLE public.series
 --     ALTER COLUMN id TYPE integer;

ALTER TABLE public.series
    ALTER COLUMN year TYPE character(5) COLLATE pg_catalog."default";
CREATE INDEX "None"
    ON public.series(id_genre);

END;




De esta manera logramos comparar la estructura de dos Bases de Datos PostgreSQL únicamente usando la herramienta que nos brinda pgAdmin 4. Espero pueda ser de utilidad y comenta ¿qué te pareció esta funcionalidad? ¿la conocías?.

Cuando se trata de modificar el orden de las columnas de una tabla en PostgreSQL, nos encontramos con que no hay una forma directa de hacerlo, como sí existe en MySQL y te lo muestro aquí. Respecto a esto no existe una estrategia única, por lo que voy a estar mostrando 3 formas diferentes para lograrlo.

Para todo cambio que vayamos a hacer en la estructura de nuestras tablas, es sumamente importante tener un respaldo actualizado de la misma.

Para cambiar el orden de las columnas de una tabla te voy a mostrar las siguientes formas:

1. Reconstruir la tabla con ayuda de una tabla temporal.
2. Alterar la estructura de la tabla con ayuda de columnas temporales.
3. Usar una vista.

Imaginemos que tenemos la siguiente tabla con algunos datos:

CREATE TABLE personas
(
    id integer,
    apellido varchar(50),
    nombre varchar(50),
    apodo varchar(50),
    CONSTRAINT personas_pkey PRIMARY KEY (id)
);

INSERT INTO personas (id, apellido, nombre, apodo) VALUES
(1, 'Stark', 'Tony', 'Ironman'),
(2, 'Romanoff', 'Natasha', 'Viuda Negra'),
(3, 'Parker', 'Peter', 'Spiderman'),
(4, 'Danvers', 'Carol', 'Capitana Marvel'),
(5, 'Rogers', 'Steven', 'Capitán América'),
(6, 'Banner', 'Bruce', 'Hulk');

 

Se vería de esta manera:

 

Si queremos alterar el orden de esta tabla y llevar la columna apellido después de la columna nombre, podemos usar estas alternativas:

 

Opción 1. Reconstruir la tabla con ayuda de una tabla temporal.

Lo primero que hacemos será crear una tabla temporal para copiar nuestros datos:

//Creamos una tabla temporal
CREATE TABLE personas_copia
(
    id integer,
    apellido varchar(50),
    nombre varchar(50),
    apodo varchar(50),
    CONSTRAINT personas_pkey PRIMARY KEY (id)
);

//Copiamos los datos existentes en la tabla personas
INSERT INTO personas_copia
SELECT * FROM personas;

Luego que aseguramos nuestros datos, podemos borrar la tabla que tiene la estructura que queremos cambiar (si tenemos referencias de otra tabla a la tabla que queremos borrar, primero debemos borrar estas referencias y posteriormente volver a crearlas al finalizar el proceso):

//Borramos la tabla original
DROP TABLE personas;

Seguidamente podemos crear la tabla con las columnas en el orden deseado:

//Crear la tabla ordenada
CREATE TABLE personas
(
    id integer,
    nombre varchar(50),
    apellido varchar(50),
    apodo varchar(50),
    CONSTRAINT personas_pkey PRIMARY KEY (id)
);

Volvemos a copiar nuestros datos:

//Copiamos los datos guardados
INSERT INTO personas
SELECT id, nombre, apellido, apodo FROM personas_copia;

Nos quedaría nuestra tabla ordenada:

 

Ahora ya podemos borrar la tabla temporal:

//Borramos la tabla temporal
DROP TABLE personas_copia;

 

Opción 2. Alterar la estructura de la tabla con ayuda de columnas temporales.

El primer paso en esta opción será renombrar todas las columnas que se encuentran desde de la posición donde queremos ordenar la columna (en este caso queremos que ahora nombre este donde esta apellido):

//Renombramos la columna apellido
ALTER TABLE personas
RENAME COLUMN apellido TO apellido_ant;

//Renombramos la columna nombre
ALTER TABLE personas
RENAME COLUMN nombre TO nombre_ant;

//Renombramos la columna apodo
ALTER TABLE personas
RENAME COLUMN apodo TO apodo_ant;

Ahora vamos a crear las columnas en el orden deseado:

//Creamos primero la columna nombre
ALTER TABLE personas
ADD nombre character varying;

//Luego creamos la columna apellido
ALTER TABLE personas
ADD apellido character varying;

//Finalmente creamos la columna apodo
ALTER TABLE personas
ADD apodo character varying;

Luego procedemos a copiar los datos de las columnas originales que cambiamos el nombre, a las nuevas columnas que se encuentran en la nueva posición deseada:

//Copiamos los datos
UPDATE personas SET
nombre = nombre_ant,
apellido = apellido_ant,
apodo = apodo_ant;

 

Nos quedaría hasta el momento de la siguiente manera:

 

Finalmente nos queda borrar las columnas viejas:

//Borramos la columna
ALTER TABLE personas
DROP COLUMN apellido_ant;

//Borramos la columna
ALTER TABLE personas
DROP COLUMN nombre_ant;

//Borramos la columna
ALTER TABLE personas
DROP COLUMN apodo_ant;

 

De esta forma nos quedaría nuestra tabla ordenada:

 

Opción 3. Usar una vista.

Aunque esta alternativa no considera modificar la estructura de la tabla en cuestión, en algunos casos será la solución más fácil para obtener una entidad con el orden requerido de forma rápida y segura:

//Creamos una vista con el orden deseado
CREATE VIEW personas_vista AS
SELECT id, nombre, apellido, apodo
FROM personas;

 

Cualquiera de las estrategias mostradas es válida para obtener el objetivo, todo va a depender de la capacidad de maniobra que tengamos con nuestras tablas. ¿Conoces alguna otra manera? Déjamela en los comentarios.

Hay momentos en los cuales debemos modificar el orden de una columna de una tabla en nuestra base de datos. Cuando ya tenemos datos guardados quizás no sea una alternativa crearla nuevamente. Afortunadamente MySQL nos brinda una forma sencilla de lograrlo, a diferencia de otras bases de datos como PostgreSQL donde debemos aplicar otras estrategias como las que muestro acá.

Imaginando que tenemos la siguiente estructura en nuestra tabla con algunos datos:

//Creamos la tabla
CREATE TABLE personas
(
    id integer,
    apellido varchar(50),
    nombre varchar(50),
    apodo varchar(50),
    CONSTRAINT personas_pkey PRIMARY KEY (id)
);

//Insertamos algunos datos
INSERT INTO personas (id, apellido, nombre, apodo) VALUES
(1, 'Stark', 'Tony', 'Ironman'),
(2, 'Romanoff', 'Natasha', 'Viuda Negra'),
(3, 'Parker', 'Peter', 'Spiderman'),
(4, 'Danvers', 'Carol', 'Capitana Marvel'),
(5, 'Rogers', 'Steven', 'Capitán América'),
(6, 'Banner', 'Bruce', 'Hulk');

 

Nos encontraremos con lo siguiente:

 

Suponiendo que queremos mover la columna apellido, después de la columna nombre, tendremos que ejecutar:

//Ordenamos la columna apellido después de la columna nombre
ALTER table personas
MODIFY COLUMN apellido varchar(50)
AFTER nombre;

 

Y nos quedaría de esta manera:

 

De esta forma podemos alterar el orden de las columnas que nos haga falta en una tabla en nuestra base de datos en MySQL.

PostgreSQL es un sistema de gestión de bases de datos relacionales gratuito y de código abierto ampliamente reconocido en la actualidad. Aprovechando el reciente lanzamiento PostgreSQL 10, hoy veremos cómo de manera rápida y sencilla podemos descargar PostgreSQL e instalarlo en Windows.

A través de ésta instalación que te muestro también podrás instalar pgAdmin 4 para administrar tus bases de datos de PostgreSQL.

Descargar PostgreSQL para Windows

Lo primero que debemos hacer es descargar el instalador de PostgreSQL. Su página oficial nos brinda varias opciones para descargar PostgreSQL, en éste caso vamos a usar la versión que ofrecen de EDB (Enterprise DB) que pueden encontrar aquí.

Tenemos que elegir la versión a descargar y nuestro sistema operativo como sigue:

Una vez descargado el instalador de PostgreSQL podremos proceder a instalarlo.

Instalar PostgreSQL en Windows

A continuación detallo los pasos para la instalación de PostgreSQL:

1. Lo primero que debemos hacer es buscar el archivo descargado y ejecutarlo.



2. Si les aparece la siguiente ventana, solamente deben esperar a que se configure Microsoft Visual C++ para poder ejecutar correctamente la instalación.



3. Ya podremos ver el instalador de PostgreSQL.



4. Lo primero que nos solicita es proporcionar el directorio de la instalación.



5. Luego podemos indicar cuales son los elementos que queremos instalar entre los siguientes: PostgreSQL Server, pgAdmin 4, Stack Builder y Command Line Tools. Con el primero ya estaremos instalando PostgreSQL, pero recomiendo al menos tildar el segundo para poder gestionar nuestras bases de datos.



6. Ahora nos pide proporcionar el directorio para guardar los datos.



7. Luego muy importante es indicar una clave para nuestro usuario postgres que es con el cual vamos a acceder al servidor de base de datos.



8. Después nos solicita el puerto que por defecto es el 5432, pero como ya yo tengo otra instancia de PostgreSQL en mi equipo, me da el siguiente puerto que es el 5433.



9. Lo siguiente que debemos indicar es la configuración regional de nuestras bases de datos.

10. Podremos ver todo lo configurado antes de proceder a instalar PostgreSQL.



11. Ahora el instalador ya está listo para comenzar la instalación de PostgreSQL.



12. Veremos entonces el progreso de la instalación.



13. Ya con ésto ha finalizado la instalación de PostgreSQL.



14. Por último buscaremos el programa instalado pgAdmin 4 para verificar la instalación. Como les comente yo tengo actualmente dos versiones de PostgreSQL en éste equipo.

Listo!

Ya podemos disfrutar de las ventajas que ofrece PostgreSQL para manejar nuestras bases de datos. En éste blog les muestro diversas entradas relacionadas con PostgreSQL, no dejes de visitarlas. Y cualquier duda sobre ésta instalación déjala en los comentarios.

Existe una manera muy sencilla de generar un diccionario de datos de una base de datos en MySQL a través de simples consultas. Ésto gracias a la información que nos da su INFORMATION SCHEMA.

Al consultar el INFORMATION_SCHEMA de MySQL (que no es más que una base de datos pero que almacena la estructura de nuestras bases de datos) nos encontraremos toda la información referente tablas, vistas y campos contenidos dentro de las mismas.

Las siguientes tablas son las que usaremos para generar nuestro diccionario de datos:

• TABLES.
• COLUMNS.
• TABLE_CONSTRAINTS.
• KEY_COLUMN_USAGE.

Obtener las tablas y vistas de una base de datos en MySQL

Para obtener las tablas y vistas contenidas dentro de una base de datos en MySQL, vamos a acceder a la tabla INFORMATION_SCHEMA.TABLES y listaremos los campos TABLE_NAME (nombre de la tabla) y TABLE_COMMENT (comentario de la tabla), filtrando por el campo TABLE_SCHEMA (nombre de base de datos), todo ello ordenado por TABLE_NAME (nombre de la tabla):

SELECT 
    t1.TABLE_NAME AS tabla_nombre,
    t1.TABLE_COMMENT AS tabla_descripcion
FROM 
    INFORMATION_SCHEMA.TABLES AS t1
WHERE 
    t1.TABLE_SCHEMA='[NOMBRE_BASE_DATOS]'
ORDER BY
    t1.TABLE_NAME;

Obtener las columnas de una tabla o vista de una base de datos en MySQL

Para obtener las columnas de una tabla en MySQL, vamos a acceder a la tabla INFORMATION_SCHEMA.COLUMNS y listaremos los campos COLUMN_NAME (nombre de la columna), COLUMN_DEFAULT (valor por defecto de la columna), IS_NULLABLE (si la columna acepta o no valores nulos), DATA_TYPE (tipo de dato de la columna), NUMERIC_PRECISION ó CHARACTER_MAXIMUM_LENGTH (precisión del tipo de dato de la columna) y COLUMN_COMMENT (comentario de la columna), esta vez filtrando por el campo TABLE_SCHEMA (nombre de base de datos) y TABLE_NAME (nombre de la tabla), todo ello ordenado por ORDINAL_POSITION (orden de nuestra columna en la tabla):

SELECT
    t1.COLUMN_NAME AS columna_nombre,
    t1.COLUMN_DEFAULT AS columna_defecto,
    t1.IS_NULLABLE AS columna_nulo,
    t1.DATA_TYPE AS columna_tipo_dato,
    IFNULL(t1.NUMERIC_PRECISION,
    t1.CHARACTER_MAXIMUM_LENGTH) AS columna_longitud,
    t1.COLUMN_COMMENT AS columna_descripcion
FROM 
    INFORMATION_SCHEMA.COLUMNS t1
WHERE 
    t1.TABLE_SCHEMA = '[NOMBRE_ESQUEMA]' AND
    t1.TABLE_NAME = '[NOMBRE_TABLA]'
ORDER BY
    t1.ORDINAL_POSITION;

Obtener las restricciones de las columnas de una tabla de una base de datos en MySQL

Para obtener restricciones primarias y foráneas de las tablas de una base de datos en MySQL, vamos a acceder a las tablas INFORMATION_SCHEMA.TABLE_CONSTRAINTS y INFORMATION_SCHEMA.KEY_COLUMN_USAGE, y listaremos los campos CONSTRAINT_TYPE (tipo de restricción de la columna), COLUMN_NAME (nombre de la columna), REFERENCED_TABLE_NAME (nombre de tabla referenciada) y REFERENCED_COLUMN_NAME (nombre de columna de tabla referenciada), filtrando por el campo TABLE_SCHEMA (nombre de base de datos), TABLE_NAME (nombre de la tabla) Y COLUMN_NAME (nombre de la columna), todo ello ordenado por CONSTRAINT_TYPE (tipo de restricción):

SELECT
    t1.CONSTRAINT_TYPE AS tipo_restriccion,
    t2.COLUMN_NAME columna_nombre,
    t2.REFERENCED_TABLE_NAME tabla_referencia,
    t2.REFERENCED_COLUMN_NAME columna_referencia
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS t1
    LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE t2
    ON t1.CONSTRAINT_CATALOG = t2.CONSTRAINT_CATALOG
    AND t1.CONSTRAINT_SCHEMA = t2.CONSTRAINT_SCHEMA
    AND t1.CONSTRAINT_NAME = t2.CONSTRAINT_NAME
WHERE
    t1.TABLE_SCHEMA = '[NOMBRE_BASE_DATOS]' AND
    t1.TABLE_NAME = '[NOMBRE_TABLA]' AND
    t2.COLUMN_NAME = '[NOMBRE_COLUMNA]'
ORDER BY
    t1.CONSTRAINT_TYPE DESC;

Pueden explorar muchas más posibilidades creando nuevas consultas sobre la base de datos INFORMATION_SCHEMA. Si ésta entrada les ha sido de utilidad también les muestro una forma de generar un diagrama entidad relación de manera automatizada.

También pueden ver en mi blog cómo generar un diccionario de datos de una base de datos en PostgreSQL.

Sólo una geek (para no decir nerd porque suena menos cool) diría que una función de base de datos es épica XD, pero así soy yo, deje así, ya no hay nada que hacer por mí. Te presento hoy 7 funciones épicas disponibles en PostgreSQL que me han hecho la vida más fácil.

PostgreSQL proporciona de manera sencilla toda la información de los objetos que se encuentran en una base de datos a través de su INFORMATION SCHEMA.

Al consultar el INFORMATION_SCHEMA de PostgreSQL (que no es más que una serie de vistas que nos muestran la estructura de nuestras bases de datos) nos encontraremos toda la información referente tablas, vistas y campos contenidos dentro de las mismas.

Las siguientes tablas son las que usaremos para generar nuestro diccionario de datos:

• PG_CLASS.
• TABLES.
• COLUMNS.
• TABLE_CONSTRAINTS.
• KEY_COLUMN_USAGE.
• REFERENTIAL_CONSTRAINTS.
• CONSTRAINT_COLUMN_USAGE.

Obtener las tablas y vistas de una base de datos en PostgreSQL

Para obtener las tablas y vistas contenidas dentro de una base de datos en PostgreSQL, vamos a acceder a la tabla INFORMATION_SCHEMA.TABLES y PG_CLASS (para obtener el OID de la tabla), y listaremos los campos TABLE_NAME (nombre de la tabla) y la función PG_CATALOG.OBJ_DESCRIPTION (para obtener comentario de la tabla), filtrando por el campo TABLE_SCHEMA (nombre del esquema de la base de datos), todo ello ordenado por TABLE_NAME (nombre de la tabla):

SELECT 
    t1.TABLE_NAME AS tabla_nombre,
    PG_CATALOG.OBJ_DESCRIPTION(t2.OID, 'pg_class') AS tabla_descripcion
FROM 
    INFORMATION_SCHEMA.TABLES t1
    INNER JOIN PG_CLASS t2 ON (t2.relname = t1.table_name)
WHERE 
    t1.TABLE_SCHEMA='[NOMBRE_ESQUEMA]'
ORDER BY
    t1.TABLE_NAME;

Obtener las columnas de una tabla o vista de una base de datos en PostgreSQL

Para obtener las columnas de una tabla en PostgreSQL, vamos a acceder a la tabla INFORMATION_SCHEMA.COLUMNS y PG_CLASS (para obtener el OID de la tabla), y listaremos los campos COLUMN_NAME (nombre de la columna), COLUMN_DEFAULT (valor por defecto de la columna), IS_NULLABLE (si la columna acepta o no valores nulos), DATA_TYPE (tipo de dato de la columna), NUMERIC_PRECISION ó CHARACTER_MAXIMUM_LENGTH (precisión del tipo de dato de la columna), la función PG_CATALOG.OBJ_DESCRIPTION (para obtener comentario de la tabla) y DOMAIN_NAME (nombre del dominio de la columna), esta vez filtrando por el campo TABLE_SCHEMA (nombre del esquema de la base de datos) y TABLE_NAME (nombre de la tabla), todo ello ordenado por ORDINAL_POSITION (orden de nuestra columna en la tabla):

SELECT
    t1.COLUMN_NAME AS columna_nombre,
    t1.COLUMN_DEFAULT AS columna_defecto,
    t1.IS_NULLABLE AS columna_nulo,
    t1.DATA_TYPE AS columna_tipo_dato,
    COALESCE(t1.NUMERIC_PRECISION,
    t1.CHARACTER_MAXIMUM_LENGTH) AS columna_longitud,
    PG_CATALOG.COL_DESCRIPTION(t2.OID,
    t1.DTD_IDENTIFIER::int) AS columna_descripcion,
    t1.DOMAIN_NAME AS columna_dominio
FROM 
    INFORMATION_SCHEMA.COLUMNS t1
    INNER JOIN PG_CLASS t2 ON (t2.RELNAME = t1.TABLE_NAME)
WHERE 
    t1.TABLE_SCHEMA = '[NOMBRE_ESQUEMA]' AND
    t1.TABLE_NAME = '[NOMBRE_TABLA]'
ORDER BY
    t1.ORDINAL_POSITION;

Obtener las restricciones de las columnas de una tabla de una base de datos en PostgreSQL

Para obtener restricciones primarias y foráneas de las tablas de una base de datos en PostgreSQL, vamos a acceder a las tablas INFORMATION_SCHEMA.TABLE_CONSTRAINTS, INFORMATION_SCHEMA.KEY_COLUMN_USAGE, INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS y INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE, y listaremos los campos CONSTRAINT_TYPE (tipo de restricción de la columna), COLUMN_NAME (nombre de la columna), CONSTRAINT_NAME (nombre de la restricción de la tabla y nombre de la restricción de la tabla referenciada), TABLE_NAME (nombre de tabla referenciada) y COLUMN_NAME (nombre de columna de tabla referenciada), filtrando por el campo TABLE_SCHEMA (nombre de base de datos), TABLE_NAME (nombre de la tabla) y COLUMN_NAME (nombre de la columna), todo ello ordenado por CONSTRAINT_TYPE (tipo de restricción):

SELECT
    t1.CONSTRAINT_TYPE AS tipo_restriccion,
    t2.COLUMN_NAME AS columna_nombre,
    t1.CONSTRAINT_NAME AS restriccion_nombre,
    t4.CONSTRAINT_NAME AS restriccion_referencia_nombre,
    t4.TABLE_NAME AS tabla_referencia,
    t4.COLUMN_NAME AS columna_referencia
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS t1
    LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE t2
    ON t1.CONSTRAINT_CATALOG = t2.CONSTRAINT_CATALOG
    AND t1.CONSTRAINT_SCHEMA = t2.CONSTRAINT_SCHEMA
    AND t1.CONSTRAINT_NAME = t2.CONSTRAINT_NAME
    LEFT JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS t3
    ON t1.CONSTRAINT_CATALOG = t3.CONSTRAINT_CATALOG
    AND t1.CONSTRAINT_SCHEMA = t3.CONSTRAINT_SCHEMA
    AND t1.CONSTRAINT_NAME = t3.CONSTRAINT_NAME
    LEFT JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE t4
    ON t3.UNIQUE_CONSTRAINT_CATALOG = t4.CONSTRAINT_CATALOG
    AND t3.UNIQUE_CONSTRAINT_SCHEMA = t4.CONSTRAINT_SCHEMA
    AND t3.UNIQUE_CONSTRAINT_NAME = t4.CONSTRAINT_NAME
WHERE
    t1.TABLE_SCHEMA = '[NOMBRE_ESQUEMA]' AND
    t1.TABLE_NAME = '[NOMBRE_TABLA]' AND
    t2.COLUMN_NAME = '[NOMBRE_COLUMNA]'
ORDER BY
    t1.CONSTRAINT_TYPE DESC;

Pueden explorar muchas más posibilidades creando nuevas consultas sobre la base de datos INFORMATION_SCHEMA. Si ésta entrada les ha sido de utilidad también les muestro una forma de generar un diagrama entidad relación de manera automatizada.

También pueden ver en mi blog cómo generar un diccionario de datos de una base de datos en MySQL.

Anteriormente les mostraba cómo podemos comparar dos bases de datos con el software DbWrench, en esa entrada podemos ver cómo instalar este software y además configurar la conexión con la base de datos.

En esta oportunidad les traigo cómo podemos generar un diagrama entidad-relación con este programa, usando su función de Reverse Engineer (Ingenieria Invensa) en unos simples pasos.

Una vez conectados a la base de datos como explico acá, procedemos a ir al menú Tools->Reverse Engineer:

Luego hacemos clic en el botón Reverse Engineer y esperamos a que construya el diagrama. El resultado será algo como esto:

Luego podemos editar este diagrama para tenerlo a nuestro gusto y posteriormente podemos exportarlo en un archivo (SVG, PNG ó JPG) y guardarlo en el menú File->Export y ¡Listo!:

En esta oportunidad les traigo una herramienta que me ha sacado de apuros en algunas ocasiones y se llama DbWrench. Aunque es de pago, la versión de pruebas permite usar todas las opciones disponibles.

Para esta entrada voy a enfocarme en la función de comparación de estructuras de base de datos que tiene la herramienta, al final del proceso vamos a obtener un script SQL con las diferencias encontradas.

La aplicación funciona con diferentes motores de bases de datos: MS Access, MS SQL Server, MySql, Oracle y PostgreSQL. En mi caso solo he probado con PostgreSQL.

Lo primero que tenemos es que descargarla de la página oficial. El software es una especie de aplicación portable ya que no requiere instalación y está disponibles para Linux, Windows y MAC. Solo tenemos que descargarlo, descomprimirlo y listo. En su pagina oficial explica donde esta el archivo ejecutable según tu sistema operativo. En mi caso la estoy ejecutando en Debian por lo que al descomprimirlo solo tengo que ir dentro de la carpeta descomprimida a /bin y ejecutar el archivo dbwrench3.

Veamos la demostración con un ejemplo. Primero creamos dos bases de datos, una llamada bd_produccion y otra bd_desarrollo. Lo que se busca en ese caso es saber que cambios se han hecho en la bd_desarrollo para poder aplicarlos en la bd_produccion.

A continuación las tablas para crear en cada base de datos:

- Tablas de la base de datos bd_produccion.

--Tabla de personas

CREATE TABLE persona
(
 id_persona CHARACTER VARYING(9) PRIMARY KEY,
 nombres CHARACTER VARYING(100),
 apellidos CHARACTER VARYING(100)
);

--Tabla de cargos

CREATE TABLE cargo
(
 id_cargo serial PRIMARY KEY,
 descripcion CHARACTER VARYING(100)
);

- Tablas de la base de datos bd_desarrollo.

--Tabla de personas

CREATE TABLE persona
(
 id_persona CHARACTER VARYING(9) PRIMARY KEY,
 nombres CHARACTER VARYING(100),
 apellidos CHARACTER VARYING(100),
 direccion TEXT,
 telefonos TEXT
);

--Tabla de cargos

CREATE TABLE cargo
(
 id_cargo serial PRIMARY KEY,
 descripcion CHARACTER VARYING(100)
);

--Tablas de personas / cargos

CREATE TABLE persona_cargo
(
 id_persona CHARACTER VARYING(9) REFERENCES persona(id_persona),
 id_cargo INTEGER REFERENCES cargo(id_cargo)
);

Una vez que tengamos nuestras bases de datos listas para hacer la comparación abrimos el programa DbWrench.

Lo primero que debemos hacer es crear las conexiones a las bases de datos. Esto lo hacemos en el menú Tools->Add Connection:

Luego configuramos ambas bases de datos:

Ahora buscamos el menú Tools->Database Compare y teniendo seleccionada la base de datos bd_desarrollo hacemos clic en Refresh para traer los esquemas disponibles en la base de datos.

Posteriormente hacemos clic en Sync Design para sincronizar la estructura de la base de datos en el Database Explorer en la parte izquierda del programa:

Ahora seleccionamos la base de datos bd_produccion y hacemos clic nuevamente en Refresh. Ya podremos ver las diferencias entre ambas bases de datos y solo nos restaría ir al menú Tools->Forward Engineer:

Finalmente hacemos clic en Recalculate Script:

Obtendremos el siguiente código SQL y podemos guardarlo con la opción Save To File:

/************ Update: Tables ***************/

/************ Update Table: persona ***************/

ALTER TABLE persona ADD direccion TEXT NULL;

ALTER TABLE persona ADD telefonos TEXT NULL;


/************ Add Table: persona_cargo ***************/

/* Build Table Structure */
CREATE TABLE persona_cargo
(
 id_persona VARCHAR(9) NULL,
 id_cargo INTEGER NULL
) WITHOUT OIDS;

/************ Add Foreign Keys ***************/

/* Add Foreign Key: persona_cargo_id_cargo_fkey */
ALTER TABLE persona_cargo ADD CONSTRAINT persona_cargo_id_cargo_fkey
 FOREIGN KEY (id_cargo) REFERENCES cargo (id_cargo)
 ON UPDATE NO ACTION ON DELETE NO ACTION;

/* Add Foreign Key: persona_cargo_id_persona_fkey */
ALTER TABLE persona_cargo ADD CONSTRAINT persona_cargo_id_persona_fkey
 FOREIGN KEY (id_persona) REFERENCES persona (id_persona)
 ON UPDATE NO ACTION ON DELETE NO ACTION;

Otra de las funcionalidades que me gusta de esta herramienta, es la posibilidad de hacer Reverse Engineer (Ingeniería Inversa), es decir, de la estructura de la base de datos, crear automáticamente el Diagrama Entidad-Relación. Puedes ver cómo funciona aquí.

Es posible enviar correos electrónicos desde PostgreSQL de diferentes maneras (aquí les muestro otra forma), en este caso voy a explicarlo con el uso de Python, que es la manera que hasta ahora me ha resultado más práctico.

Características del servidor de pruebas:

• Debian Squeeze
• PostgreSQL 8.4

Pasos de instalación:

dpkg - i libpython2.6.deb

dpkg - i postgresql-plpython-8.4.deb

CREATE LANGUAGE plpythonu;

CREATE OR REPLACE FUNCTION pypgmail 
(
 IN iservidor character varying,      -- Servidor de envío de correo
 IN ipuerto integer,                  -- Puerto de envío de correo
 IN iemisor character varying,        -- Correo del emisor
 IN idestinatario character varying,  -- Correo del destinatario
 IN iclave character varying,         -- Clave de autenticación del emisor
 IN iasunto character varying,        -- Asunto del mensaje
 IN imensaje text                     -- Mensaje a enviar
)
RETURNS integer AS
$BODY$

# Importamos smtplib
import smtplib
 
# Importamos los módulos necesarios
from email.mime.text import MIMEText
 
# Creamos el mensaje
msg = MIMEText(imensaje,'html')
 
# Conexión con el server
msg['Subject'] = iasunto
msg['From'] = iemisor
msg['To'] = idestinatario
 
# Autenticamos
mailServer = smtplib.SMTP(iservidor,587)
mailServer.ehlo()
mailServer.starttls()
mailServer.ehlo()
mailServer.login(iemisor,iclave)
 
# Enviamos
mailServer.sendmail(iemisor, idestinatario, msg.as_string())
 
# Cerramos conexión
mailServer.close()

return (1)

$BODY$ LANGUAGE plpythonu;

SELECT * FROM pypgmail('servidor@tudominio.com',587,'correo_emisor@tudominio.com',
'correo_destino@sudominio.com','clave','Asunto del mensaje','Cuerpo del correo.');

Es posible enviar correos electrónicos desde PostgreSQL de diferentes maneras (aquí les muestro otra forma que recomiendo más), en este caso voy a explicarlo con el procedimiento almacenado pgMail. Su funcionamiento es sencillo pero existe muy poca documentación al respecto y su principal limitante es que solo funciona con servidores de correos sin autenticación requerida.

Características del servidor de pruebas:

• Debian Squeeze
• PostgreSQL 8.4

Pasos de instalación:

1. Descargar los archivos de pgMail de su pagina oficial: pgMail. El paquete incluye:

• pgMail-x.x/CHANGELOG (notas de la versión)
• pgMail-x.x/example.cleanup.sql (ejemplo)
• pgMail-x.x/example.execute.sql (ejemplo)
• pgMail-x.x/example.setup.sql (ejemplo)
• pgMail-x.x/pgMail/pgMail.sql (procedimiento almacenado pgMail)
• pgMail-x.x/pgMail/README (archivo que indica la configuración básica del procedimiento)

2. Descargar e instalar el paquete tcl8.5:

dpkg - i tcl8.5.deb

dpkg - i postgresql-pltcl-8.4.deb

CREATE LANGUAGE pltclu;

SELECT pgmail('correoenvio@tudominio.com','correodestino@tudominio.com',
'Asunto', 'Cuerpo');

Cuando no se puede contar con un herramientas sofisticadas para el manejo de las bases de datos en PostgreSQL, se puede recurrir a pequeños trucos para lograr descubrir porque nuestra función en PL/SQL no hace lo que le pedimos.

Es tan sencillo como mandar a imprimir pequeños mensajes (algo como el famoso "pase por aquí") con datos variables que nos indiquen de que manera se comportan las variables y las estructuras de control en nuestra función.

El código para imprimir los mensajes es el siguiente:

RAISE NOTICE 'El valor de la variable es: (%)', nombre_variable;