Memorias PostgreSQL Guia Practica

1

Memorias

PostgreSQL Guía Práctica

ING. SERGIO ARBEY MONSALVE BETANCUR

Medellín-Colombia, 1 de Junio del 2013 [email protected]

2

Indice Índice 2 Que es PostgreSQL 5 Historia y Antecedentes 6 Ventajas de PostgreSQL 7 Características de PostgreSQL 8 Herramientas de PostgreSQL 13 Comparación de sistemas administradores de bases de datos relacionales

Información general Soporte del sistema operativo Características fundamentales Tablas y vistas Índices Otros objetos Particionamiento

15 15 15 16 17 17 18 19

Arquitectura Conceptual de PostgreSQL Front End (Cliente - Servidor) Postmaster Back End - Arquitectura General del Servidor de Postgres

Intérprete Policía de tráfico Planificador / Optimizador Ejecutor Administración de Datos Almacenamiento de Datos

Conclusión

20 21 22 22 23 23 23 23 23 25 27

Instalar y Configurar el PostgreSQL 9.x Instalación del Servidor de Bases de Datos PostgreSQL 9.x Configurar el PostgreSQL 9.2 Habilitar Conexiones Remotas al Servidor PostgreSQL 9.2 Optimización Instalar el PostgreSQL en un Directorio Diferente Instalar el PostgreSQL en un Directorio Diferente usando tablespace

28 28 32 38 38 41 42

Directorios y Archivos de PostgreSQL Ubicaciones de los directorios y archivos de PostgrSQL en Linux Ubicaciones de los directorios y archivos de Datos PostgrSQL en Linux PostgreSQL Límites de espacio en disco ¿Dónde están nuestros datos en el disco? Bloques de datos en el disco PostgreSQL: Espacio en Disco Tipos de Tablas Tipos de Datos

43 43 43 45 46 48 50 50 51

3

Indice Gestión de Bases de Datos PostgreSQL

PSQL: La pequeña gran herramienta de PostgreSQL Ejecutando psql desde el sistema operativo Ejecutando psql en modo interactivo

Controlando una Sesión en psql en modo interactivo Query multi-línea en modo interactivo Usando valores NULL y BLANK Valores DEFAULT

pg_dump y pg_dumpall pg_restore createdb y dropdb Postmaster y pg_ctl PgAdmin

54 54 55 58 61 62 63 64 64 66 67 68 69

Administración de sesiones Inicio y Tipos de sesiones Control de sesiones

71 71 71

Caso de Estudio 75 Sentencias DDL

CREATE ALTER ….. ADD ALTER ….. DROP DROP TRUNCATE

76 76 85 89 92 95

Sentencias DML SELECT INSERT UPDATE DELETE

96 96

104 105 106

Sentencias TCL COMMIT SAVEPOINT RELEASE SAVEPOINT ROLLBAK

109 109 109 110 111

Instalar Script 114 Caso de Estudio - Continuación 117 Sentencias DCL

GRANT REVOKE

119 119 120

Sentencias XML 128

4

Indice PLpgSQL

Introducción Estructura Utilización mediante SELECT DECLARE Insertar Datos en una Tabla Actualizar Datos en una Tabla Utilizar ROW TYPES Cursores Cursores Implícitos con FOR <VAR> IN <QUERY> Triggers

Validación Valores Calculados

Manejo de Errores Ejercicio Red Social

131 131 131 132 132 132 133 135 136 138 138 140 140 141 142

Consultas Enumeradas 147 Consultas, Funciones, Procedimientos y Vistas

Consultas Funciones Procedimientos Vistas

149 149 150 151 151

Funciones Ventana (Windows Functions) 153 Automatización de Backups 156 Bibliografia 158

5

Que es PostgreSQL PostgreSQL es un Sistema de Gestión de Bases de Datos Objeto-Relacionales (ORDBMS) que ha sido desarrollado de varias formas desde la década de 1980 y es un proyecto de software libre distribuido bajo licencia BSD (Berkeley Software Distribution) y creado con el aporte de varios colaboradores y auspiciantes a nivel mundial bajo los estándares de ANSI-SQL 92/99. Con más de dos década de desarrollo, PostgreSQL se ha convertido en la base de datos de código abierto y con orientación a objetos más avanzada disponible en el momento, ofreciendo las características propias de los más potentes motores de bases de datos comerciales como Oracle, DB2 o SQLServer. PostgreSQL es el último resultado de una larga evolución comenzada con el proyecto de bases de datos relacionales Ingres en la Universidad de Berkeley. Luego se inició el proyecto Post-Ingres para resolver los problemas con el modelo de base de datos relacional que se habían presentado. El proyecto resultante llamado Postgres completó el soporte de tipos de datos y la base de datos comprendía también las relaciones entre tablas o clases. El proyecto PostgreSQL sigue actualmente un activo proceso de desarrollo a nivel mundial gracias a un equipo de desarrolladores y contribuidores de código abierto. PostgreSQL es ampliamente considerado como una de las alternativas de sistema de bases de datos de código abierto. El sitio web oficial de PostgreSQL es;

http://www.postgresql.org

6

Historia y Antecedentes

La implementación del DBMS (Sistema Manejador de Bases de Datos) Postgres comenzó a desarrollarse en 1986 con la coordinación del profesor Michael Stonebraker, y fue patrocinado por algunas fundaciones estatales y militares de investigación. Los conceptos iníciales para el sistema fueron presentados con la definición del modelo de datos inicial junto con la lógica y arquitectura del gestor de almacenamiento; desde entonces, Postgres ha pasado por varias versiones. El primer sistema de pruebas fue operacional en el año 1987 y la Versión 1 fue lanzada a unos pocos usuarios en Junio de 1989; después de revisar el sistema de reglas de la primera versión, éste fue rediseñado y la Versión 2 se lanzó en Junio de 1990. La Versión 3 apareció en 1991 y añadió una implementación para múltiples gestores de almacenamiento, un ejecutor de consultas mejorado junto con un mejor sistema de reglas. En su mayor parte, las siguientes versiones hasta el lanzamiento de Postgres95 se centraron en los temas de portabilidad y fiabilidad. El mantenimiento del código y las tareas de soporte ocupaban demasiado tiempo que debía dedicarse a la investigación, así que el proyecto terminó oficialmente con el lanzamiento de la Versión 4.2. En 1994, Andrew Yu y Jolly Chen añadieron un intérprete de lenguage SQL (Lenguaje Estructurado de Consultas) a Postgres y el proyecto se denominó Postgres95, el mismo que fue lanzado a continuación en la Web para que encontrara su sitio en el mundo de los gestores de bases de datos como un descendiente de dominio público y código abierto del código original Postgres de Berkeley. El código de Postgres95 fue optimizado y reducido en tamaño en un 25% respecto a sus predecesores; muchos cambios internos mejoraron el rendimiento y la facilidad de mantenimiento. Postgres95 en su versión v1.0 se ejecutaba en un 30 a 50% más rápido que Postgres v4.2 y además de su corrección de errores, el lenguaje de consultas Postquel fue reemplazado con SQL (implementado en el servidor). También se incluyó un nuevo programa (psql) para realizar consultas SQL interactivas. En 1996 nace el proyecto PostgreSQL, siendo una nueva versión de Postgres95, tratando de reflejar la relación entre el Postgres original y las versiones más recientes con capacidades de SQL. Los números de versión parten de la 6.0, volviendo a la secuencia seguida originalmente por el proyecto Postgres de Berkeley.

El énfasis durante el desarrollo de Postgres95 estaba orientado a identificar, entender y mejorar los problemas existentes en el código del servidor. Con PostgreSQL, además de estas mejoras se puso énfasis para aumentar las

7

características y capacidades del servidor de bases de datos utilizando los estándares SQL92/SQL99. PostgreSQL se distribuye bajo la licencia BSD. La licencia BSD al contrario que la GPL permite el uso del código fuente en software no libre. El autor, bajo este tipo de licencia, mantiene la protección de copyright únicamente para la renuncia de garantía y para requerir la adecuada atribución de la autoría en los trabajos derivados, pero permite la libre redistribución y modificación, por lo que pienso que esta licencia asegura un verdadero “software libre”, en el sentido que el usuario tiene libertad ilimitada con respecto al software, y que puede decidir incluso si redistribuirlo como software no libre. Actualmente la última versión de PostgreSQL disponible para descargar desde su sitio web:

http://www.postgresql.org/download/

8

Ventajas de PostgreSQL

Instalación Ilimitada

Con PostgreSQL, nadie puede demandarlo por violar acuerdos de licencia, puesto que no hay costo asociado a la licencia del software.

Soporte

Además de las ofertas de soporte comercial, hay una importante comunidad de profesionales y entusiastas de PostgreSQL de los que su compañía puede obtener beneficios y contribuir.

Ahorros considerables en costos de operación

Ha sido diseñado y creado para tener un mantenimiento y ajuste mucho menor que otros productos, conservando todas las características, estabilidad y rendimiento.

Estabilidad y Confiabilidad Legendarias

Es extremadamente común que compañías reporten que PostgreSQL nunca ha presentado caídas en varios años de operación de alta actividad. Ni una sola vez. Simplemente funciona.

Multiplataforma

PostgreSQL está disponible en casi cualquier Unix (34 plataformas en la última versión estable - Linux, UNIX, AIX, BSD, HP-UX, Mac OS X, Solaris) y ahora en versión nativa para Windows.

Extensible

El código fuente está disponible para todos sin costo. Si su equipo necesita extender o personalizar PostgreSQL de alguna manera, pueden hacerlo con un mínimo esfuerzo, sin costos adicionales.

Esto es complementado por la comunidad de profesionales y entusiastas de PostgreSQL alrededor del mundo que también extienden PostgreSQL todos los días.

Diseñado para ambientes de alto volumen

PostgreSQL usa una estrategia de almacenamiento de filas llamada MVCC para conseguir una mejor respuesta en ambientes de grandes volúmenes. Los

9

principales proveedores de sistemas de bases de datos comerciales usan también esta tecnología, por las mismas razones.

Herramientas gráficas de diseño y administración de BD

Existen varias herramientas gráficas de alta calidad para administrar las bases de datos (pgAdmin , pgAccess) y para hacer diseño de bases de datos (Tora , Data Architect).

10

Características de PostgreSQL

A continuación se presentan las más importantes características de este sistema manejador de bases de datos por la cuales es considerado uno de los más potentes gestores de bases de datos en el mundo del software libre: Soporte SQL92/SQL99

Implementa los estándares SQL92/SQL99 con sus operadores, funciones, cláusulas y comandos (DDL y DML), junto con comandos extendidos de PostgreSQL.

Transacciones

Posee un completo soporte para control de transacciones asegurando la integridad y consistencia de los datos Permiten el paso entre dos estados consistentes manteniendo la integridad de los datos. Un bloque de transacciones comienza con una sentencia BEGIN WORK y si la transacción fue válida se cierra con COMMIT WORK o END WORK. Si la transacción falla, se cierra con ABORT o ROLLBACK WORK. BEGIN WORK; ....... Sentencias SQL; ....... COMMIT WORK;

Integridad referencial

PostgreSQL soporta integridad referencial, la cual es utilizada para garantizar la validez de los datos de la base de datos.

Soporte completo de ACID (Atomicity Consistency Isolation Durability)

Operaciones Atómicas, formadas por comandos que se ejecutan todos o ninguno de ellos.

Consistencia, que garantiza que la base de datos nunca se quede en un estado intermedio de una transacción (con parte de los comandos ejecutados y otra parte que no).

Aislamiento, que mantiene separadas las transacciones de usuarios distintos hasta que éstas han terminado, es decir controlando la concurrencia de usuarios.

11

Durabilidad, garantizando que el servidor de bases de datos guarde en un registro o log de transacciones las actualizaciones realizadas y pendientes de forma tal que pueda recuperarse de una terminación brusca como un corte de energía en la máquina.

MVCC (Control de Concurrencia Multi-Versión)

Tecnología que PostgreSQL usa para mantener la concurrencia de usuarios y evitar bloqueos innecesarios de la base de datos.

Bloqueos de tabla y filas

Hay varios modos de bloqueo para controlar el acceso concurrente a los datos en tablas Algunos de estos modos de bloqueo los adquiere PostgreSQL automáticamente antes de la ejecución de una declaración, mientras que otros son proporcionados para ser usados por las aplicaciones.

Procedimientos almacenados

Permiten optimizar y acelerar las aplicaciones y evitan transferencias innecesarias a través de la red.

Constraints y triggers

Tienen la función de mantener la integridad y consistencia en la BD. Ejecución de acciones antes o después de un evento de BD.

Multiples tipos de datos predefinidos

Como todos los manejadores de bases de datos, PostgreSQL implementa los tipos de datos definidos para el estándar SQL3 y aumenta algunos otros.

Soporte de tipos y funciones de usuario

PostgreSQL soporta operadores, funciones métodos de acceso y tipos de datos definidos por el usuario. Incorpora una estructura de datos Array.

Conectividad TCP/IP, JDBC y ODBC Interfaz con diversos lenguajes

C, C++, Java, Delphi, Python, Perl, PHP, Bash, ..... Savepoints

12

Permite hacer un rolled back sin tener que repetir la transacción entera. Point in Time Recovery

Permite salvar el estado de la DB en momentos concretos, para su posterior recuperación.

Tablespaces

Permite destinar discos físicos a un índice o a una tabla concreta. Improved Memory and I/O

Optimizaciones en la velocidad de ejecución y en el consumo de memoria de la aplicación.

Añadidos o ampliados

Lenguajes de procedimientos almacenados PL/Java, PL/J, PL/PHP y PL/Perl. Seguridad

Gestión correcta de usuarios, grupos de usuarios y contraseñas, así como también los permisos asignados a cada uno de ellos.

Respaldos (Backups) y Recuperación caliente

Respaldos y Recuperación completa de las bases de datos mientras trabaja el servidor PostgreSQL

Replicación

Con servidores PostgreSQL funcionando como maestros y otros como esclavos. Todas las transacciones se las realiza primero en el servidor maestro para que se puedan actualizar en los esclavos. La replicación es un proceso asíncrono en PostgreSQL, y se la realiza gracias a un archivo llamado binary Log que contiene la información de las modificaciones y actualizaciones entre un nodo maestro y uno o múltiples esclavos; la replicación con servidores PostgreSQL es un proceso independiente del servidor.

Herencia entre tablas

Por lo que a este gestor de bases de datos se le incluye entre los gestores objeto-relacionales.

13

Herramientas de PostgreSQL

Existen herramientas libres y gratuitas con interfaces gráficas e intuitivas y fáciles de utilizar para la administración completa de bases de datos PostgreSQL como son PhpPgAdmin o el PgAdmin que son auspiciados por los mismos creadores del sistema PostgreSQL. También existen algunas herramientas comerciales entre las que destaco por su funcionalidad a PostgreSQL Manager. Así también hay una variedad de herramientas para la administración de bases de datos PostgreSQL en modo consola, fabricadas por los mismos creadores de PostgreSQL (http://www.postgresql.org) como son por ejemplo el terminal de cliente interactivo de postgreSQL psql, o las aplicaciones para realizar copias de seguridad y restauración de base de datos pg_dump y pg_restore, o herramientas cliente para crear y eliminar bases de datos como cretedb, dropdb entre otras que son incluidas en la descarga completa del servidor PostgreSQL. PgAdmin3

Es una interfaz comprensible para el diseño y administración de una base de datos PostgreSQL, diseñada para ejecutarse en la mayoría de los Sistemas Operativos. La aplicación corre bajo GNU/Linux, FreeBSD y Windows. La interfaz gráfica soporta todas las características de PostgreSQL y facilita la administración. También incluye un Diseñador de Consultas Gráfico.

PHPPgAdmin

PHPPgAdmin es una poderosa herramienta de administración basada en un interfaz Web para bases de datos PostgreSQL. Además de la funcionalidad básica, dispone de soporte para procedimientos almacenados, triggers y vistas. Las versiones de punta van mano a mano con el desarrollo del servidor PostgreSQL. Esta versión es una de las más famosas de los administradores GUI para PostgreSQL.

14

PSQL

Es la herramienta canónica para la ejecución de sentencias SQL a través del shell del SO. Es una herramienta de tipo frontend que permite describir sentencias SQL, ejecutarlas y visualizar sus resultados. El método de ingreso puede ser mediante la inserción directa del código en la consola, o la ejecución de sentencias dentro de un archivo de texto. Provee diversos meta-comandos para la ejecución de las sentencias, así como diversas opciones tipo shell propias de la herramienta.

PgExplorer PgExplorer es una herramienta de desarrollo para Postgres con una amplia interfaz gráfica. Entre sus características se incluye una vista en árbol de las bases de datos y sus respectivos objetos. Se puede realizar ingeniería inversa a través de sentencias SQL o scripts personalizados. Hay una amplia gama de asistentes que guían a través del proceso necesario para generar comandos SQL para varios objetos y sentencias. También incluye un Diseñador de Consultas Gráfico.

15

Comparación de sistemas administradores de bases de datos relacionales Las siguientes tablas comparan información general y técnica de diferentes RDBMS. Para más información, vea los enlaces de cada producto. No se incluyen todas.

Información general

Creador Fecha de la primera versión pública

Última versión estable

Licencia de software

Adaptive Server Anywhere Sybase/iAnywhere 1992 10.0 Propietario

Adaptive Server Enterprise Sybase Inc 1987 15.0 Propietario

ANTs Data Server ANTs Software 1999 3.6 Propietario DB2 IBM 1982 9 Propietario

[Firebird ] Firebird Foundation 25 de julio de 2000 2.1 Licencia Pública InterBase

Informix Informix Software 1985 10.0 Propietario HSQLDB Hsqldb.Org 2001 1.9 Licencia BSD

Ingres Berkeley University, Computer Associates

1980 2006 CA-TOSL

InterBase Borland 1985 7.5.1 Propietario

SapDB SAP AG ? 7.4 GPL con drivers LGPL

MaxDB MySQL AB, SAP AG ? 7.7 GPL o propietario Microsoft SQL Server Microsoft 1989 2008 Propietario MySQL MySQL AB Noviembre de 1996 5.0 GPL o propietario Oracle Oracle Corporation 1977 11g Release 2 Propietario

PostgreSQL PostgreSQL Global Development Group Junio de 1989 9.0 Licencia BSD

Small SQL SmallSQL 16 de abril de 2005 0.12 LGPL SQLite D. Richard Hipp 17 de agosto de 2000 3.6.16 Dominio público

Creador Fecha de la primera versión pública

Última versión estable

Licencia de software

Soporte del sistema operativo Windows Mac OS X Linux BSD Unix z/OS Adaptive Server Enterprise Sí Sí Sí Sí Sí No

ANTs Data Server Sí Sí Sí Sí Sí ?

DB2 Sí Sí Sí Sí Sí Sí

[[Firebird ]] Sí Sí Sí Sí Sí Quizá

16

HSQLDB Sí Sí Sí Sí Sí Sí

Informix Sí Sí Sí Sí Sí No

Ingres Sí ? Sí ? Sí Quizá

InterBase Sí No Sí No Sí (Solaris) No

SapDB Sí No Sí No Sí ?

MaxDB Sí No Sí No Sí ?

Microsoft SQL Server Sí No No No No No

MySQL Sí Sí Sí Sí Sí Quizá

Oracle Sí Sí Sí Sí Sí Sí

PostgreSQL Sí Sí Sí Sí Sí No

SmallSQL Sí Sí Sí Sí Sí Sí

SQLite Sí Sí Sí Sí Sí Quizá

Windows Mac OS X Linux BSD Unix z/OS

Características fundamentales Información acerca de que características fundamentales de las RDBMS son implementados nativamente.

ACID Integridad referencial Transacciones Unicode Adaptive Server Enterprise Sí Sí Sí Sí

ANTs Data Server Sí Sí Sí Sí

DB2 Sí Sí Sí Sí

Firebird Sí Sí Sí Sí

HSQLDB Sí Sí Sí Sí

Informix Sí Sí Sí Sí

Ingres Sí Sí Sí Sí

InterBase Sí Sí Sí Sí

SapDB Sí Sí Sí Sí

MaxDB Sí Sí Sí Sí

Microsoft SQL Server Sí Sí Sí Sí

MySQL Depende 1 Depende 1 Depende 1 Sí

Oracle Sí Sí Sí Sí

PostgreSQL Sí Sí Sí Sí

SQLite Sí No 2 Básico 2 Sí

ACID Integridad referencial Transacciones Unicode

Nota (1): Para las transacciones y la integridad referencial, el tipo de tabla InnoDB debe ser usado; el tipo de tabla por defecto, MyISAM, No soporta estas características. Sin embargo, inclusive el tipo de tabla InnoDB permite el almacenamiento de valores que excedan el rango de datos; algunas vistas violan la limitación de ACID. Nota (2): Las limitaciones de CHECK y FOREIGN KEY son analizadas pero No forzadas. Transacciones anidadas No son soportadas.[1]

17

Tablas y vistas Información acerca de que tablas y vistas 3 (unas más básicos que otras) son soportados nativamente.

Tabla temporal Vista materializada Adaptive Server Enterprise Sí 5 Sí

ANTs Data Server Sí Sí

DB2 Sí Sí

[[Firebird ]] Sí No

HSQLDB Sí No

Informix Sí Sí

Ingres Sí No

InterBase Sí No

SapDB Sí No

MaxDB Sí No

Microsoft SQL Server Sí Similar 6

MySQL Sí No

Oracle Sí Sí

PostgreSQL Sí No 4

SQLite Sí No

Tabla temporal Vista materializada

Nota (4): La vista materializada puede ser emulada con PL/PgSQL [2]. Nota (5): El servidor provee tempdb, que puede ser usado para tablas temporales públicas y privadas (para la sesión). [3] Nota (6): El servidor MS SQL provee vistas indexadas. [4]

Índices Información acerca de que índices (otros como los índices básicos B-/B+) son soportados nativamente.

Árbol R-/R+ Hash Expresión Parcial Reversa Mapa de bits

Adaptive Server Enterprise No No Sí No Sí No

ANTs Data Server Sí Sí Sí Sí Sí Sí

DB2 No ? No No Sí Sí

Firebird No No No No No No HSQLDB ? ? ? ? ? ?

Informix Sí Sí Sí No No No

Ingres Sí Sí No No No No

18

InterBase ? ? No No No No

SapDB ? ? No No No No

MaxDB ? ? No No No No

Microsoft SQL Server ? ? No No No No

MySQL Tablas MyISAM solamente Tablas HEAP solamente No No No No

Oracle Edición EE solamente ? Sí No Sí Sí

PostgreSQL Sí Sí Sí Sí No No

SQLite No No No No No No

Árbol R-/R+ Hash Expresión Parcial Reversa Mapa de

bits

Otros objetos Información acerca de que otros objetos son soportados nativamente.

Dominio Cursor Trigger Funciones 5 Procedimiento 5 Rutina externa 5

Adaptive Server Enterprise Sí Sí Sí Sí Sí Sí

ANTs Data Server Sí Sí Sí Sí Sí Sí

DB2 No Sí Sí Sí Sí Sí

Firebird Sí Sí Sí Sí Sí Sí

HSQLDB ? No Sí Sí Sí Sí

Informix ? Sí Sí Sí Sí Sí

Ingres Sí Sí Sí Sí Sí ?

InterBase Sí Sí Sí Sí Sí Sí

SapDB Sí Sí Sí Sí Sí ?

MaxDB Sí Sí Sí Sí Sí ?

Microsoft SQL Server No Sí Sí Sí Sí Sí

MySQL No Sí 3 Sí 3 Sí 3 Sí 3 Sí

Oracle Sí Sí Sí Sí Sí Sí

PostgreSQL Sí Sí Sí Sí Sí Sí

SQLite No No Sí No No Sí

Dominio Cursor Trigger Funciones Procedimiento Rutina externa

Nota (3): Estos objetos de base de datos son disponibles a partir de MySQL 5.0 disponible desde 24/12/2005. Nota (5): Función y procedimiento se refieren a las rutinas internas escritas en SQL o lenguajes procedurales como PL/SQL. Rutina externa se refiere a la escritura en los lenguajes anfitriones como C, Java, Cobol, etc. "Procedimiento almacenado" es un término comúnmente usado para ese tipo de rutinas. Sin embargo, su definición varía entre diferentes vendedores de bases de datos.

19

Particionamiento Información acerca de que métodos de particionamiento son soportados nativamente.

Rango Hash Compuesto (Rango+Hash) Lista Adaptive Server Enterprise

AA AA AA AA

ANTs Data Server Sí Sí Sí Sí

DB2 Sí Sí Sí Sí

Firebird No No No No HSQLDB ? ? ? ?

Informix ? ? ? ?

Ingres Sí Sí Sí Sí

InterBase No No No No SapDB ? ? ? ?

MaxDB ? ? ? ?

Microsoft SQL Server Sí No No No

MySQL Sí Sí Sí Sí

Oracle Sí Sí Sí Sí

PostgreSQL Sí No No Sí

SQLite Sí Sí Sí Sí

Rango Hash Compuesto (Rango+Hash) Listas

20

Arquitectura Conceptual de PostgreSQL PostgreSQL usa un modelo de arquitectura cliente/servidor conocido como “proceso por usuario”. Hay un proceso maestro que se ramifica para proporcionar conexiones adicionales para cada cliente que se intente conectar a PostgreSQL. PostgreSQL es una arquitectura orientada a objetos dividida en tres grandes subsistemas:

Front End Procesos en el Cliente La aplicación sobre la que trabaja el usuario

Postmaster Procesos en el Servidor Un proceso demonio supervisor

Back End Control de la Base de datos

Uno o más servidores de bases de datos en segundo plano (servidor PostgreSQL)

Un único proceso postmaster controla una colección de bases de datos almacenadas en un host; las aplicaciones de frontend o clientes que quieren acceder a una determinada base de datos hacen llamadas y envían peticiones de usuario a través de la red al proceso postmaster, el cual en respuesta inicia un proceso en el servidor y conecta el proceso de frontend al nuevo servidor. A partir de este punto, el proceso cliente (frontend) y el servidor (backend) se comunican sin la intervención del postmaster, aunque este proceso siempre se está ejecutando, esperando peticiones de otros clientes (procesos frontend).

Soporte del sistema operativo

21

En esta arquitectura el proceso postmaster y el proceso backend siempre se ejecutan en la misma máquina (el servidor de base de datos), mientras que la aplicación frontend o cliente se puede ejecutar desde cualquier equipo.

Dentro de estos subsistemas, otras arquitecturas tales como túnel híbrido y filtro (en los procesos del servidor Postgres), la invocación implícita (en el Postmaster), cliente-servidor (con el Postmaster como servidor), y orientado a objetos (en el control de Base de Datos).

Arquitectura general conceptual de PostgreSQL

Front End (Cliente - Servidor) Consta de dos partes principales: La aplicación del cliente y la librería de interface del cliente.

Las Aplicaciones de cliente, algunas de las cuales se ejecutan en diferentes sistemas operativos, como: PgAdmin, PhpPgAdmin, EMS SQL Manager, Mergeant, PGInhaler, SQirreL y otras. La Librería de interface del Cliente es la forma en que cada una de estas aplicaciones pueden comunicarse con el Servidor porque la Librería de interface del Cliente convertirá a la apropiada consulta SQL que el Servidor puede entender e interpretar.

Esto maximiza la cohesión porque el servidor no tiene que interpretar diferentes lenguajes, sino que únicamente entiende consultas SQL, lo que hace el sistema más rápido.

22

Postmaster Es un proceso demonio supervisor que se ejecuta constantemente. Utiliza una arquitectura de invocación implícita para escuchar alguna o todas las llamadas a la base de datos. Cuando recibe una llamada desde un cliente, crea un proceso “back end” (servidor postgres) para hacerlo coincidir, utilizando una correspondencia 1-1. Una vez que el proceso se crea, se enlaza el cliente y el proceso postgres para que ya no tengan que comunicarse a través del Postmaster.

Back End - Arquitectura General del Servidor de Postgres Arquitectura de túnel híbrido y filtro. Cada componente referencia un repositorio compartido de catálogos, reglas y tablas. Se pasa una consulta SQL al Servidor Postgres que se transforma gradualmente en datos de resultados.

Arquitectura conceptual del servidor Postgres

23

Intérprete Acepta una consulta SQL en forma de texto ASCII. El analizador léxico busca coincidencia de patrones en la consulta para reconocer los identificadores y palabras clave. El Intérprete entonces ensambla estos en un árbol de traducción. El Intérprete comprueba que la consulta SQL tenga una sintaxis válida, pero no entiende la semántica.

Policía de tráfico Envía comandos simples para el ejecutor y los complejos se envían al planificador / optimizador.

Planificador / Optimizador Las Consultas SQL se pueden ejecutar en diferente orden y producen los mismos resultados. El Planificador / Optimizador elegirá la mejor ruta o la más eficiente si existen varias posibilidades. A continuación, se pasa hacia el Ejecutor.

Ejecutor Recibe el plan de Planificador / Optimizador en la forma de un árbol. Extrae las tablas de datos necesarias. Recursivamente pasa por el plan, y lleva a cabo las acciones necesarias en cada nodo. Envía al túnel y filtra, sin procesamiento por lotes. Devuelve una salida al cliente.

Administración de Datos La base de datos es mantenida por varios subsistemas independientes (y algunas veces opcional) iniciadas por el Postmaster en la construcción, incluyendo:

24

• El colector de Estadísticas. • El Auto-Vacuum. • El escritor Background. • El Sistema de Administración de la memoria.

Control de las dependencias de la Base de Datos

Colector de Estadísticas Registros de la tabla / índice de accesos de la base de datos, el uso de funciones definidas por el usuario y los comandos que se ejecutan por los procesos del servidor. La información se transmite desde el colector a través de los archivos temporales en los procesos solicitantes. Procesos que envían información relevante al colector periódicamente para mantenerlo al día.

25

Auto-Vacuum El Auto-Vaccum es una colección de procesos para escanear las tablas de la base(s) de datos con el fin de liberar la memoria no utilizada, actualizar las estadísticas, y prevenir la pérdida de datos. El Auto-Vaccum se basa en los datos recibidos desde el recolector de estadísticas para el análisis de la tabla adecuada.

Background Writer El Background Writer mantiene la información de los archivos logs (registro de la actividad) y backups (copia de seguridad) actualizados. El Background Writer mantiene actualizados los archivos logs con todos los cambios realizados a la base de datos desde su último backup a fin de que todos los datos estén seguros. La salida estándar de cada subsistema se pasa al Background Writer para mantener estos archivos logs.

Almacenamiento de Datos La base de datos es mantenida por varios subsistemas independientes (y algunas veces opcional) iniciadas por el Postmaster en la construcción, incluyendo:

• El Acceso. • El Almacenamiento. • La Rutina de Carga (Bootstrap). • La grabación en Disco.

Acceso El Subsistema de Acceso está a cargo de:

• indexación • exploración • búsqueda • recopilación y devolución de datos

El Servidor de Procesos PostgreSQL Recupera los datos utilizando el Subsistema de Acceso.

26

El Subsistema de Acceso utiliza diferentes métodos de indexación.

Almacenamiento Los datos almacenados son accesados a través del Subsistema de Almacenamiento: Encargada del mantenimiento de una serie de buffers compartidos. Permite múltiples accesos a las mismas tablas utilizando un modelo de control de concurrencia multiversión (MVCC - multiversion concurrency control model). Mantiene bloqueos de tabla y asegura la concurrencia de datos.

Bootstrap (Rutina de Carga) El subsistema Bootstrap permite a los usuarios iniciar la base de datos en modo de arranque. El Modo Bootstrap no permite consultas SQL. Bootstrap permite a los catálogos del sistema ser creados y llenados desde cero, mientras comandos normales SQL requieren que los catálogos ya existan. Bootstrap es usado por el Instalador para crear la plantilla inicial de la Base de Datos.

Caso de uso de creación de una Base de Datos

27

Caso de uso de login y consulta compleja

Conclusión La Arquitectura general de PostgreSQL es Orientada a Objetos / Repositorio. EL Front-end es una arquitectura cliente-servidor desde la Biblioteca de Cliente a los Procesos del Servidor Postgres y el Postmaster que utiliza invocación implícita. El Servidor Postgres emplea arquitectura de Túnel Híbrido y Filtro / Repositorio. El Control de Bases de Datos utiliza una arquitectura Orientada a Objetos. Adelante veremos el código fuente para determinar las dependencias a nivel de código, lo que nos permite formar una arquitectura concreta.

28

Instalar y Configurar el PostgreSQL 9.x

1 Instalación del Servidor de Bases de Datos PostgreSQL 9.x en Centos 6.2/6.3 Fedora 18/17/16, Red Hat (RHEL), Scientific Linux (SL) 6.4/6.3/6.2/6.1/6/5.9 Cubriremos la instalación y configuración básica de PostgreSQL 9.x en CentOS. Instalaremos PostgreSQL 9 utilizando el repositorio PostgreSQL y yum. El mismo procedimiento se puede utilizar para instalar PostgreSQL 9 en Red Hat, Fedora y Scientific Linux utilizando el rpm apropiado. Como la estructura de directorios de PostgreSQL ha cambiado con el lanzamiento de PostgreSQL 9, también veremos cómo podemos crear enlaces simbólicos para hacer la vida más fácil cuando se instala software o módulos que aún esperan la vieja estructura de directorios. También vamos a crear enlaces simbólicos (si es necesario) de las nuevas PostgreSQL 9 ubicaciones de los archivos de las PostgreSQL 8 ubicaciones de los archivos anteriores. Usaremos el método más sencillo de instalar, que es el repositorio rpms postrgres.

1.1 Cambie al usuario root

su - ## OR ## sudo -i

1.2 Descargar e Instalar el Repositorio PostgreSQL

Descargue la última versión de producción de su distribución aquí:

http://yum.pgrpms.org/repopackages.php

Los repositorios rpms son específicamente de 32 and 64 bit. Si instalamos en CentOS 6 x64, necesitamos: http://yum.pgrpms.org/9.2/redhat/rhel-6-x86_64/pgdg-centos92-9.2-6.noarch.rpm Si instalamos en CentOS 6 x32, necesitamos:

29

http://yum.pgrpms.org/9.2/redhat/rhel-6-i386/pgdg-centos92-9.2-6.noarch.rpm Si instala en un Linux diferente utilice el rpm apropiado. Usando wget:

[root@server1 ~]# wget http://yum.pgrpms.org/9.2/redhat/rhel-6-x86_64/pgdg-centos92-9.2-6.noarch.rpm

1. [root@server1 ~]# wget http://yum.pgrpms.org/9.2/redhat/rhel-6-x86_64/pgdg-

centos92-9.2-6.noarch.rpm 2. --2011-11-01 00:11:50-- http://yum.pgrpms.org/9.2/redhat/rhel-6-x86_64/pgdg-

centos92-9.2-6.noarch.rpm 3. Resolving yum.pgrpms.org... 98.129.198.114 4. Connecting to yum.pgrpms.org|98.129.198.114|:80... connected. 5. HTTP request sent, awaiting response... 200 OK 6. Length: 5124 (5.0K) [application/x-redhat-package-manager] 7. Saving to: pgdg-centos92-9.2-6.noarch.rpm 8. 9. 100%[======================================>] 5,124 --.-K/s in 0s 10. 11. 2011-11-01 00:11:51 (310 MB/s) - pgdg-centos92-9.2-6.noarch.rpm 12. 13. [root@server1 ~]#

1.3 Excluir del CentOS, Fedora, Red Hat y Scientifi c Linux sus propios

paquetes PostgreSQL.

Este paso es importante para conseguir que el repositorio de PostgreSQL funcione correctamente. Hay que excluir los paquetes PostgreSQL desde el repositorio de la distribución. CentOS Ahora tenemos que editar el archivo /etc/yum.repos.d/CentOS-Base.repo y adicionarle la clausula 'exclude = postgresql*' en las secciones [base] y [updates] para excluir postgreql.

1. [root@server1 ~]# cd /etc/yum.repos.d 2. [root@server1 ~]# vi CentOS-Base.repo

1. [base] 2. .... 3. .... 4. exclude=postgresql* 5. 6. # released updates 7. [updates] 8. .... 9. .... 10. exclude=postgresql*

30

Fedora Adicionamos la clausula 'exclude = postgresql*' en la sección [fedora] del archivo /etc/yum.repos.d/fedora.repo: 1. [root@server1 ~]# cd /etc/yum.repos.d 2. [root@server1 yum.repos.d]# vi fedora.repo

1. [fedora] 2. .... 3. .... 4. exclude=postgresql*

Adicionamos la clausula 'exclude = postgresql*' en la sección [updates] del archivo /etc/yum.repos.d/fedora-updates.repo: 1. [root@server1 yum.repos.d]# vi fedora-updates.repo

1. [updates] 2. .... 3. .... 4. exclude=postgresql*

Red Hat (RHEL) Adicionamos la clausula 'exclude = postgresql*' en la sección [main] del archivo /etc/yum/pluginconf.d/rhnplugin.conf: 1. [root@server1 ~]# cd /etc/yum/pluginconf.d 2. [root@server1 yum.repos.d]# vi rhnplugin.conf

1. [main] 2. .... 3. .... 4. exclude=postgresql*

Scientific Linux (SL) Adicionamos la clausula 'exclude = postgresql*' en las secciones [sl] y [sl-security] del archivo /etc/yum.repos.d/sl.repo: 1. [root@server1 ~]# cd /etc/yum.repos.d 2. [root@server1 yum.repos.d]# vi sl.repo 1. [sl] 2. .... 3. .... 4. exclude=postgresql* 5. 6. # released sl-security 7. [sl-security] 8. .... 9. ....

31

10. exclude=postgresql*

1.4 Instalar el Repositorio PostgreSQL 9.2.

Ahora, instalamos el Repositorio CentOS 1. [root@server1 ~]# rpm -i pgdg-centos92-9.2-6.noarch.rpm

Fedora 1. [root@server1 ~]# rpm -i pgdg-fedora92-9.2-6.noarch.rpm

Red Hat (RHEL) 1. [root@server1 ~]# rpm -i pgdg-redhat92-9.2-7.noarch.rpm

Scientific Linux (SL) 1. [root@server1 ~]# rpm -i pgdg-sl92-9.2-8.noarch.rpm

1.5 Chequear los paquetes que están instalados con YUM.

Este paso es opcional, pero nos permite verificar que los paquetes instalados estén ahora disponibles: 1. [root@server1 yum.repos.d]# rpm yum list postgres* 2. .... 3. .... 4. postgresql92.x86_64 5. ....

1.6 Instalar el PostgreSQL 9.2 con YUM.

Ahora podemos instalar el PostgreSQL usando yum: 1. [root@server1 ~]# yum install postgresql92 postgresql92-devel postgresql92-

server postgresql92-libs postgresql92-contrib 2. 3. .... 4. 5. Installed: 6. postgresql92.x86_64 0:9.2.6-1PGDG.rhel6 7. postgresql92-devel.x86_64 0:9.2.6-1PGDG.rhel6 8. postgresql92-libs.x86_64 0:9.2.6-1PGDG.rhel6 9. postgresql92-server.x86_64 0:9.2.6-1PGDG.rhel6 10. 11. Complete! 12. [root@server1 ~]#

32

2 Configurar el PostgreSQL 9.2

2.1 Inicializar el Cluster con el comando initdb Hay múltiples alternativas de cómo hacer esto:

• service postgresql-9.x initdb • /etc/init.d/postgresql-9.x initdb • postgresql-setup initdb • service postgresql-9.x initdb

Así, que usaremos aquí el método universal de Postgres initdb, que deberá trabajar con CentOS, Red Hat (RHEL), Fedora 18/17/16 y Scientific Linux (SL) 6.4/5.9: 1. [root@server1 ~]# su - postgres -c /usr/pgsql-9.2/bin/initdb

Otra forma, Inicializar el Postgres: 1. [root@server1 ~]# service postgresql-9.2 initdb 2. Initializing database: [ OK ] 3. [root@server1 ~]#

Arrancar el Servidor Postgres 1. [root@server1 ~]# service postgresql-9.2 start 2. Starting postgresql-9.1 service: [ OK ] 3. [root@server1 ~]#

Si encuentra errores de inicio, chequee el archivo /var/lib/pgsql/9.2/data/pg_log en busca de pistas.

2.2 Establecer el entorno del PostgreSQL 9.x El directorio home predeterminado para el usuario postgres es /var/lib/pgsql. El bash_profile para el usuario postgres se parecerá a: 1. [ -f /etc/profile ] && source /etc/profile 2. PGDATA=/var/lib/pgsql/9.2/data 3. export PGDATA

Contiene el ruta para el directorio data, pero no la ruta para el directorio de los binarios ejecutable. Para modificar esto, agregue la ruta de la siguiente manera: 1. [ -f /etc/profile ] && source /etc/profile 2. PGDATA=/var/lib/pgsql/9.2/data 3. export PGDATA 4. PATH=$PATH:$HOME/bin:/usr/pgsql-9.2/bin 5. export PATH

33

Colocando el directorio de los ejecutables binarios en la ruta del usuario postgres le permitirá invocar todos comandos.

2.3 Establecer la contraseña PostgreSQL El superusuario postgres no tiene contraseña predeterminada. Para establecer la contraseña, cambie al usuario postgres: 1. [root@server1 ~]# su - postgres

Conectar como postgres a la base de datos postgres y configurar la contraseña para el usuario postgres utilizando alter usuario de la siguiente manera: 1. –bash-4.1$ psql postgres postgres 2. Psql (9.2.6) 3. Type "help" for help. 4. 5. postgres=# alter user postgres with password 'postgres'; 6. ALTER ROLE 7. postgres=#

2.4 Configurar los Permisos para PostgreSQL ( Archivo pg_hba.conf)

Editar el archivo pg_hba.conf, donde se especifican las direcciones ip desde las cuales aceptara conexiones entrantes. Localizar el archivo pg_hba.conf bajo el directorio /var/lib/pgsql/9.2/data. En la instalación, el archivo pg_hba.conf se verá así: 1. [root@server1 ~]# vi /var/lib/pgsql/9.2/data/pg_hba.conf 2. .... 3. # TYPE DATABASE USER ADDRESS METHOD 4. local all all peer 5. # IPv4 local connections: 6. host all all 127.0.0.1/32 ident 7. # IPv6 local connections: 8. host all all ::1/128 ident 9. ....

Para que acepte todas las conexiones, se agrega en la parte de IPv4 local connections la línea:

host all all 0.0.0.0/0 md5 Cambiar METHOD a md5 para que solicite contraseña, ver cambios abajo: 1. .... 2. # TYPE DATABASE USER ADDRESS METHOD 3. local all all md5 4. # IPv4 local connections:

34

5. host all all 127.0.0.1/32 md5 6. host all all 0.0.0.0/0 md5 7. # IPv6 local connections: 8. host all all ::1/128 md5 9. ....

Se puede restringir el acceso al servidor postgres como se desee, así: host all all 10.20.4.0/24 md5 host test testuser 127.0.0.1/32 md5 Se pueden encontrar más ejemplos en la guía completa del manual en:

http://www.postgresql.org/docs/9.2/static/auth-pg-hba-conf.html Para que los cambios tomen efecto, se recarga el archivo pg_hba.conf. Hay varias formas de hacerlo: 1. [root@server1 ~]# su - postgres 2. -bash-4.1$ pg_ctl reload 3. server signaled 4. -bash-4.1$

1. -bash-4.1$ psql postgres postgres 2. psql (9.2.6) 3. Type "help" for help. 4. 5. postgres=# select pg_reload_conf(); 6. pg_reload_conf 7. ---------------- 8. t 9. (1 row) 10. 11. postgres=#

1. -bash-4.1$ psql postgres postgres -c "select pg_reload_conf();" 2. Password for user postgres: 3. pg_reload_conf 4. ---------------- 5. t 6. (1 row) 7. 8. -bash-4.1$

2.5 Configurar el Acceso Remoto para PostgreSQL ( Archivo postgresql.conf)

Localizar el archivo postgresql.conf bajo el directorio /var/lib/pgsql/9.2/data. Busque CONNECTIONS AND AUTHENTICATION. Se verá de la siguiente manera: 1. #------------------------------------------------------------------------------ 2. # CONNECTIONS AND AUTHENTICATION

35

3. #------------------------------------------------------------------------------ 4. 5. # - Connection Settings - 6. 7. #listen_addresses = 'localhost' # what IP address(es) to listen on; 8. # comma-separated list of addresses; 9. # defaults to 'localhost', '*' = all 10. # (change requires restart) 11. #port = 5432 # (change requires restart)

De forma predeterminada, el acceso está limitado a la máquina local (localhost). Para habilitar las conexiones remotas, descomentar listen_addresses y cambiar a "*" como se muestra a continuación. 1. #------------------------------------------------------------------------------ 2. # CONNECTIONS AND AUTHENTICATION 3. #------------------------------------------------------------------------------ 4. 5. # - Connection Settings - 6. 7. listen_addresses = '*' # what IP address(es) to listen on; 8. # comma-separated list of addresses; 9. # defaults to 'localhost', '*' = all 10. # (change requires restart) 11. port = 5432 # (change requires restart)

También puede establecer el límite a una IP específica (o direcciones IP utilizando una lista separada por comas). Nota: Para mayor seguridad, es una buena idea cambiar el puerto predeterminado. Para ello, cambie a un nuevo valor de puerto. 1. #------------------------------------------------------------------------------ 2. # CONNECTIONS AND AUTHENTICATION 3. #------------------------------------------------------------------------------ 4. 5. # - Connection Settings - 6. 7. listen_addresses = '192.1.2.33' # what IP address(es) to listen on; 8. # comma-separated list of addresses; 9. # defaults to 'localhost', '*' = all 10. # (change requires restart) 11. port = 5432 # (change requires restart)

Si cambia el puerto, reinicie el servicio postgresql. 1. service postgresql-9.2 restart 2. Stopping postgresql-9.2 service: [ OK ] 3. Starting postgresql-9.2 service: [ OK ] 4. [root@server1 yum.repos.d]#

Si encuentra errores de inicio, chequee el archivo /var/lib/pgsql/9.2/data/pg_log en busca de pistas.

36

Verifique si ha realizado cambios en listen_address o port: 1. -bash-4.1$ psql 2. Password: 3. psql (9.2.6) 4. Type "help" for help. 5. 6. postgres=# show listen_addresses; 7. listen_addresses 8. ------------------ 9. * 10. (1 row) 11. 12. postgres=# show port; 13. port 14. ------ 15. 5432 16. (1 row) 17. 18. postgres=#

2.6 Crear Usuario y Base de Datos para PostgreSQL 9 .x

Para chequear la funcionalidad, conéctese a la Base de Datos postgres como usuario postgres. 1. [root@server1 yum.repos.d]# psql postgres postgres 2. Password for user postgres: 3. psql (9.2.6) 4. Type "help" for help. 5. 6. postgres=#

Crear un usuario: 1. postgres=# create user testuser with password 'secreto'; 2. CREATE ROLE

Crear una Base de datos y asignarle el usuario propietario: 1. postgres=# create database test owner=testuser; 2. CREATE DATABASE

Conectar a la Base de Datos como usuario: 1. postgres=# \c test testuser 2. Password for user testuser: 3. You are now connected to database "test" as user "testuser".

Crear una Tabla e Insertar Filas: 1. test=> create table testtable (col1 varchar); 2. CREATE TABLE

37

3. test=> insert into testtable values('hello'); 4. INSERT 0 1

Consultas en la Tabla 1. test=> select * from testtable; 2. col1 3. ------- 4. hello 5. (1 row) 6. 7. test=>

Listar las Tablas de la Base de Datos test: 1. test=> \dt 2. List of relations 3. Schema | Name | Type | Owner 4. --------+-----------+-------+-------- 5. public | testtable | table | myuser 6. (1 row)

Note que public es el esquema predeterminado, deberá crear un esquema especifico para sus usuarios.

2.7 Iniciar / Auto-Iniciar (Arranque del Equipo) el servidor Postgres Ahora, Iniciamos el Servidor: CentOS, Red Hat (RHEL), Scientific Linux (SL) 6.4/5 .9 De forma predeterminada, se agrega el servicio postgresql-9.2 a chkconifg, pero todos los niveles de ejecución se establecen en off. Adicione los niveles de ejecución 2,3 y 5 para el servicio postgresql-9.2 1. ## Iniciar PostgreSQL 9.2 ##

2. [root@server1 ~]# service postgresql-9.2 start 3. ## OR ## 4. [root@server1 ~]# /etc/init.d/postgresql-9.2 start 5. 6. ## Iniciar PostgreSQL 9.2 en cada arranque del equipo ## 7. [root@server1 ~]# chkconfig --levels 235 postgresql-9.2 on

Fedora 18/17/16 1. ## Iniciar PostgreSQL 9.2 ##

2. [root@server1 ~]# systemctl start postgresql-9.2.service 3. 4. ## Iniciar PostgreSQL 9.2 en cada arranque del equipo ## 5. [root@server1 ~]# systemctl enable postgresql-9.2.service

38

3 Habilitar Conexiones Remotas al Servidor PostgreSQL 9.2 Abrir el Puerto 5432 del PostgreSQL en el Firewall Iptables.

3.1 Editar el archivo etc/sysconfig/iptables Adicionar a iptables la excepción para que acepte paquetes por el puerto 5432:

1. [root@server1 ~]# vi /etc/sysconfig/iptables 2. 3. -A INPUT -m state --state NEW -m tcp -p tcp --dport 5432 -j ACCEPT

3.2 Reiniciar el Firewall iptables

CentOS, Red Hat (RHEL), Scientific Linux (SL) 6.4/5 .9 1. [root@server1 ~]# service iptables restart 2. ## OR ## 3. [root@server1 ~]# /etc/init.d/iptables restart

Fedora 18/17/16 1. [root@server1 ~]# systemctl restart iptables.service

4 Optimización 4.1 Crear enlaces simbólicos para compatibilidad co n versiones de

anteriores a PostgreSQL 9.x Mucho, si no la mayoría de los módulos y software de terceros buscan los archivos de configuración del Postgres y el directorio de datos en sus antiguas ubicaciones (antes de la versión 9). Se puede solucionar este inconveniente y hacer la vida más fácil, creando algunos enlaces simbólicos de los nuevos lugares a los anteriores. Enlace 1: Enlace simbólico para el directorio binario. Esto es particularmente

útil ya que es la ubicación del archivo de pg_config. 1. [root@server1 ~]# ln -s /usr/pgsql-9.2/bin/pg_config /usr/bin

Enlace 2: Enlace simbólico de la anterior ubicación del directorio de datos

/var/lib/pgsql 1. [root@server1 ~]# ln -s /var/lib/pgsql/9.2/data /var/lib/pgsql 2. [root@server1 ~]# ln -s /var/lib/pgsql/9.2/backups /var/lib/pgsql

39

4.2 Optimizar el rendimiento del Postgres usando el archivo postgresql.conf Y cambiamos algunas opciones básicas del archivo postgresql.conf: 1. shared_buffers = 256MB

‘shared_buffers’: Es la memoria de trabajo compartida para todo el servidor postgreSQL, fíjese que por defecto en Debian GNU/Linux la opción es 24MB (y el valor por defecto si comentamos es 32MB), sin embargo, como esta es la memoria utilizada para trabajo de postgreSQL, es recomendable “al menos” el 25% de la RAM disponible (y jamás > 40%). 2. temp_buffers = 16MB

‘temp_buffers’: La memoria temporal utilizada por cada sesión para las tablas temporarias y para apertura de tablas en cada sesión de cada base de datos, tome en cuenta que este valor dependerá obviamente de la cantidad de datos que carga cada sesión y dependerá muchísimo del sistema que se utiliza. 3. work_mem = 16MB

‘work_mem’: uno de los valores más importantes y más despreciados, “work_mem” se refiere a la memoria temporal utilizada por cada sesión, para las operaciones de ordenamiento (ORDER BY) para las sesiones de diferenciación (GROUP … HAVING y DISTINCT) y para la gestión de hash (uniones HASH, indices HASH, hash_aggregations), si en nuestro sistema realizamos muchísimas consultas ordenadas, agrupadas, diferenciadas por cadenas, etc se crearán mucho de estos buffers de manera paralela, mientras más memoria asignemos, menos probabilidades hay que los ordenamientos y otras operaciones se hagan con archivos temporales en disco (más lentos que la memoria RAM). 4. max_stack_depth = 8MB

‘max_stack_depth’: define el tamaño del espacio utilizado para cómputo de operaciones complejas, su valor está asociado al límite máximo que un usuario (en este caso, “postgres”) tiene derecho a reservar un stack, el valor soportado por nuestra distribución se determina con “ulimit -s”. 5. shared_preload_libraries = '$libdir/plpython2.so'

‘shared_preload_libraries’: Permite cargar una librería específica cuando arranca el sistema, si utilizamos muchos procedimientos almacenados en un lenguaje específico (ej: pgsql, python, perl, tcl, java, etc), es bueno pre-cargarla para que esté disponible cuando se utilice por primera vez. Nota: esta opción ralentiza un poco el reinicio del sistema.

40

6. bgwriter_delay = 500ms

‘bgwriter_delay’: El background-writer es un proceso del servidor que se encarga de escribir a disco todos los “shared_buffers” modificados, este proceso conlleva una carga de I/O sobre el disco, su modificación permite o reducir el valor para evitar en lo más posible pérdidas de datos en equipos que pueden fallar, o su incremento permite reducir el I/O al disco duro en sistemas perfectamente protegidos.

4.2 Optimizar el Linux para Postgres. Una de las cosas que olvidamos “optimizar” (tunning) es nuestro sistema operativo GNU/Linux, con grupo de valores en el sysctl ya que podemos ayudar “mucho” a nuestro postgreSQL. Cada vez que el sistema arranque, el programa init ejecuta el script /etc/rc.d/rc.sysinit. Este script contiene un comando para ejecutar sysctl mediante el uso de /etc/sysctl.conf para determinar los valores pasados al kernel. Cualquier valor añadido a /etc/sysctl.conf surtirá efecto cada vez que el sistema arranque. Agregamos al archivo /etc/sysctl.conf 1. kernel.sem = 100 32000 100 128 2. kernel.shmall = 3279547 3. kernel.shmmax = 289128448 4. kernel.shmmni = 8192 5. fs.file-max = 287573 6. vm.dirty_bytes = 67108864 7. vm.dirty_background_bytes = 134217728

Nota: observe el valor de shmmax, la cantidad de “memoria máxima reservada para un shared_buffer” que puede crear una aplicación debe ser igual o mayor al valor del shared_buffer de postgreSQL, este valor está en bytes y es ~ 275MB. La cantidad máxima de archivos que pueden abrirse en un sistema, dependerá obviamente del nivel de trabajo de la DB, durante una operación regular, la gente puede ejecutar “lsof | wc” para obtener la cantidad de archivos abiertos. Y luego, las aplicamos: 1. [root@server1 ~]# sysctl -p 2. 3. -- 4. kernel.sem = 100 32000 100 128 5. kernel.shmall = 3279547 6. kernel.shmmax = 289128448 7. kernel.shmmni = 8192 8. fs.file-max = 287573

41

9. vm.dirty_bytes = 67108864 10. vm.dirty_background_bytes = 134217728

Ya, con estos sencillos cambios, podemos reiniciar el postresql: 1. [root@server1 ~]# /etc/init.d/postgresql restart 2. Restarting PostgreSQL 9.1 database server: main.

5 Instalar el PostgreSQL en un Directorio Diferente

Para instalar el PostgreSQL en un directorio distinto al por defecto, la diferencia con el proceso anterior consiste en crear el directorio asignarle los permisos correspondientes y ejecutar el comando initdb con los parámetros correctos para inicializar el Cluster en otra posición. El directorio puede ser un disco entero que se debe montar previamente desde el sistema operativo. Vamos a crear un directorio data: 1. [root@server1 ~]# mkdir /srv/postgresql

Asignamos el directorio a sus propietarios. Debe ser un superusuario postgres: 2. [root@server1 ~]# chown postgres /srv/postgresql

Ahora entramos al usuario postgres 3. [root@server1 ~]# su - postgres

Al instalar la BD, cambiamos los parámetros del comando initdb para Inicializar el Cluster en el directorio creado anteriormente: 4. -bash-4.1$ /usr/pgsql-9.2/bin/initdb -D /srv/postgresql

Ahora iniciamos la BD: 5. -bash-4.1$ /usr/pgsql-9.2/bin/postmaster -D /srv/postgresql

6 Instalar el PostgreSQL en un Directorio Diferente usando

tablespace

42

Se utiliza para ampliar el espacio que contiene las Bases de Datos o para tener los índices y las tablas en discos separados para mejorar los procesos de acceso i/o. Entramos al usuario postgres 1. [root@server1 ~]# su - postgres

Entramos al psql 2. -bash-4.1$ psql

En la consola, ejecutamos el comando para crear un espacio de tablas: 3. postgres=# CREATE TABLESPACE te_sistema OWNER postgres LOCATION '/srv/postgresql';

Y listo!, ya tenemos un espacio de tablas disponible para crear bases de datos: Para crear una DB que no esté asociada al espacio “por defecto” (pg_default) ejecutamos: 4. postgres=# CREATE DATABASE db_sistema WITH ENCODING='UTF8' OWNER=postgres

TEMPLATE=template0 TABLESPACE=te_sistema;

Y como verán, le pasamos el tablespace “te_sistema” que hemos creado anteriormente

43

Directorios y Archivos de PostgreSQL

Ubicaciones de los directorios y archivos de PostgreSQL en Linux El siguiente paso, después de instalar PostgreSQL es revisar lo que contiene los ficheros generados con la instalación. En la mayoría de distribuciones de Linux, los archivos de PostgreSQL se guardan en las ubicaciones mostradas en la siguiente tabla:

Ítem Ubicación

Ejecutables /usr/bin

Librerías /usr/lib

Documentación /usr/share/doc/postgresql-x.y. z /usr/share/doc/postgresql-x.y . z/contrib

Contrib /usr/share/pgsql/Contrib

Área de Datos /var/lib/pgsql/data

Área de Backup /var/lib/pgsql/backup

Templates /usr/share/pgsql

Lenguajes Procedurales /usr/lib/pgsql

Development Headers /usr/include/pqsql

Otros datos compartidos /usr/share/ pqsql

Pruebas de Regresión /usr/lib/pgsql/test/regress (en el paquete -test)

Documentación SGML /usr/share/doc/postgresql-docs-x.y.z

Ubicaciones de los directorios y archivos de Datos de PostgreSQL en Linux Es necesario ver los contenidos del directorio data, y entender como están organizados y que es lo que contiene cada uno de ellos. En esta sección se describe el formato de almacenamiento a nivel de archivos y directorios del directorio de datos de PostgreSQL. Todos los datos necesarios para que un clúster de base de datos se almacenan dentro del directorio de datos conocida como PGDATA (el nombre de la variable

44

de entorno que se puede utilizar para definir la misma). Un lugar común para PGDATA es /var/lib/pgsql/9.2/data. Diversos clústeres, gestionados por diferentes instancias del servidor, pueden existir en la misma máquina. El directorio contiene varios subdirectorios y archivos de control, como se muestra en la Tabla . Además de estos elementos necesarios, la configuración de cluster archivos postgresql.conf, pg_hba.conf , y pg_ident.conf se almacenan tradicionalmente en DATA (aunque a partir de PostgreSQL 8.0 es posible mantenerlos en otro lugar). Cada tabla o índice está almacenado en un archivo independiente, los nombres de archivos se pueden consultar en el catálogo.

Ítem Descripción

PG_VERSION Archivo que contiene el número de versión de PostgreSQL.

base Directorio con subdirectorios por cada base de datos.

global Directorio con subdirectorios para todo el cluster, como pg_database.

pg_clog Directorio con datos de transacciones.

pg_multixact Directorio que contiene la multitransacción de los Datos (utilizado para los bloqueos de fila compartidas).

pg_notify Directorio que contiene el estado de los datos LISTEN/NOTIFY.

pg_serial Directorio que contiene información sobre las transacciones serializables committed

pg_snapshots Directorio que contiene las instantáneas (snapshots) exportadas

pg_stat_tmp Directorio con archivos temporales para el subsistema de estadísticas

pg_subtrans Directorio con datos de subtransacciones.

pg_tblspc Directorio con enlaces simbólicos a tablespaces.

pg_twophase Directorio con archivos de estado para transacciones preparadas.

pg_xlog Directorio con archivos WAL (Write Ahead Log).

postmaster.opts Fichero con opciones de arranque de postmaster utilizadas.

postmaster.pid

Fichero de bloqueo con el PID actual del postmaster, La ruta del directorio del cluster de datos, fecha y hora de inicio del postmaster, número de puerto, ruta del directorio del dominio Unix (vacío en Windows), primera dirección valida de listen_address (dirección IP o * o vacia si no escuchando en TCP), y el ID de memoria compartida (shared mem) ID segmento

45

Ítem Descripción

(este archivo no está presente después que el servidor se detiene).

Para cada base de datos en el clúster hay un subdirectorio dentro PGDATA/base , el nombre de OID de la base de datos en pg_database . Este subdirectorio es la ubicación predeterminada para los archivos de la base de datos, en particular, sus catálogos del sistema se almacenan allí. Cada tabla y el índice se almacenan en un archivo independiente. Para las relaciones normales, estos archivos tienen el nombre de la tabla o de índice.

PostgreSQL Límites de espacio en disco En la tabla de abajo se describen algunos límites en el espacio de disco que maneja PostgreSQL:

Objeto Limite

base de datos Sin límites (existen bases de datos de 32 TB) .

tabla 32 TB.

fila 400 GB.

campo 1 GB.

filas en una tabla Sin límites.

columnas en una tabla 250-1600 dependiendo el tipo de columna.

índices en una tabla Sin límites.

Tabla: Límites de espacio en disco que maneja PostgreSQL Por supuesto, esto realmente no es ilimitado, pero está limitado por el espacio de disco disponible y el espacio de memoria/swap. El rendimiento puede sufrir cuando estos valores son inusualmente grandes. El máximo tamaño de una tabla de 32 TB no requiere el soporte de archivos de gran tamaño desde el sistema operativo. Las grandes tablas se almacenan como múltiples archivos de 1GB, para el sistema de archivos los límites de tamaño no son importantes. El tamaño máximo de una tabla, el tamaño de la fila, y el número máximo de columnas puede ser cuadruplicado por el aumento del tamaño de bloque por defecto a 32k. El tamaño máximo de la tabla también puede incrementarse

46

utilizando la tabla de particionado. Una limitación es que los índices no pueden ser creados en columnas más largas que alrededor de 2.000 caracteres. Afortunadamente, esos índices son raramente necesarios. La Singularidad es la mejor garantía por una función de un índice hash MD5 el largo de la columna, y la indexación de texto completo permite la búsqueda de las palabras dentro de la columna.

¿Dónde están nuestros datos en el disco?

PostgreSQL tiene la información necesaria para funcionar grabada en el disco duro y organiza los ficheros con nuestros datos ubicándolos en el directorio que hayamos definido como directorio de datos (data_directory). Tener las cosas claras en lo que respecta a este tema nos puede

ayudar en momentos difíciles como administradores de bases de datos, en el caso que nuestros datos se corrompan por alguna causa.

1. [root@server1 ~]# su - postgres 2. -bash-4.1$ psql 3. postgres=# SHOW data_directory; 4. data_directory 5. --------------------- 6. /var/lib/pgsql/9.2/data 7. (1 row)

Dentro del directorio de datos encontramos el directorio base donde se graban todos los datos contenidos en nuestras bases de datos.

1. -bash-4.1$ cd /var/lib/pgsql/9.2/data/base 2. -bash-4.1$ ls -l 3. -bash-4.1$ ls –l 4. total 28 5. drwx------ 2 postgres nogroup 12288 2011-10-04 17:53 1 6. drwx------ 2 postgres nogroup 4096 2011-10-04 17:53 11939 7. drwx------ 2 postgres nogroup 4096 2011-10-04 17:53 11947

En el directorio base existen otros con nombres numéricos y cada uno es una base de datos diferente. Para saber a qué base de datos corresponden se ejecuta:

1. postgres=# SELECT datid,datname from pg_stat_database; 2. datid | datname 3. -------+-------------- 4. 1 | template1 5. 11939 | template0 6. 11947 | postgres 7. (3 rows)

47

Los valores de datid se corresponden a los valores listados en el directorio base y la columna datname es el nombre de la base de datos asociada al identificador numérico. Crear una base de datos y ver como esto afecta nuestro sistema:

1. postgres=# CREATE DATABASE test_interno; 2. CREATE DATABASE

Se crea un directorio con el nombre 16407 que corresponde a la base de datos creada.

1. -bash-4.1$ ls -l /var/lib/pgsql/9.2/data/base/ 2. total 24 3. drwx------ 2 postgres nogroup 12288 2011-10-04 17:53 1 4. drwx------ 2 postgres nogroup 4096 2011-10-04 17:53 11939 5. drwx------ 2 postgres nogroup 4096 2011-10-04 17:53 11947 6. drwx------ 2 postgres nogroup 4096 2011-10-05 11:31 16407

1. postgres=# SELECT datid,datname from pg_stat_database; 2. datid | datname 3. -------+----------------------- 4. 1 | template1 5. 11939 | template0 6. 11947 | postgres 7. 16407 | test_interno 8. (4 rows)

Si se lista el nuevo directorio, se ve que ya tiene una serie de ficheros aunque no se haya creado ninguna tabla todavía. Estos ficheros pertenecen al sistema y son necesarios para que la base de datos funcione. Crear una tabla en la base de datos con un par de columnas de tipo integer:

1. postgres=# \c test_interno 2. You are now connected to database "test_interno" as user "postgres". 3. 4. test_interno=# CREATE TABLE test001 (id INTEGER, code INTEGER, primary key(id)); 5. NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index "test001_pkey“ 6. CREATE TABLE 7. 8. test_interno=# \d test001 9. Table "public.test001" 10. Column | Type | Modifiers 11. --------+---------+----------- 12. id | integer | not null 13. code | integer | 14. Indexes: 15. "test001_pkey" PRIMARY KEY, btree (id)

El identificador de la tabla test001 y el fichero correspondiente lo podemos obtener así:

1. test_interno=# SELECT

48

2. pg_relation_filenode('test001'),pg_relation_filepath('test001'); 3. 4. pg_relation_filenode | pg_relation_filepath 5. ----------------------+-------------------------- 6. 16465 | base/16407/16465 7. (1 row)

Sí la tabla no está en el esquema public se debe preceder del nombre del esquema. Y el del índice test001_pkey creado para clave primaria de esta tabla con:

1. test_interno=# SELECT 2. pg_relation_filenode('test001_pkey'),pg_relation_filepath('test001_pkey'); 3. 4. pg_relation_filenode | pg_relation_filepath 5. ----------------------+--------------------------- 6. 16468 | base/16407/16468 7. (1 row)

Listando del contenido del directorio de la base de datos (base/16407) se observa que se han creado dos ficheros con los nombres 16465 y 16468.

1. -bash-4.1$ ls -l /var/lib/pgsql/9.2/data/base/16407/16465 2. -rw------- 1 postgres nogroup 0 2011-10-06 12:09 base/16407/16465

1. -bash-4.1$ ls -l /var/lib/pgsql/9.2/data/base/16407/16468 2. -rw------- 1 postgres nogroup 8192 2011-10-06 12:09 base/16407/16468

Si la tabla o índice llegan a ser mayores que 1GB, se dividirán a nivel del sistema de ficheros en ficheros con un máximo de 1GB cada uno. Si por ejemplo, la tabla llega a ser de 3,5GB, se verá algo similar a esto:

1. -bash-4.1$ ls -l /var/lib/pgsql/9.2/data/base/16407/16465* 2. -rw------- 1 postgres pgdba 1073741824 Jul 5 15:11 base/16407/16465 3. -rw------- 1 postgres pgdba 1073741824 Jul 6 15:11 base/16407/16465.1 4. -rw------- 1 postgres pgdba 1073741824 Jul 7 15:11 base/16407/16465.2 5. -rw------- 1 postgres pgdba 536870912 Jul 8 15:11 base/16407/16465.3

Más información sobre las funciones de Administración del Sistema en: http://www.postgresql.org/docs/9.2/static/functions-admin.html

Bloques de datos en el disco Una vez visto como encontrar en el sistema de ficheros las bases de datos con sus tablas e índices, tenemos que saber cómo se graban los datos en estos ficheros.

49

La unidad mínima de almacenamiento en PostgreSQL se denomina página (page) o bloque (block). Un bloque en PostgreSQL ocupa siempre por defecto 8K si se usa en su totalidad o solo parcialmente. El valor se puede cambiar durante la compilación.

El espacio en una página o bloque se compone de:

• Page_header : Cabecera de bloque. Ocupa 24 bytes. • ItemId : Matriz de pares de valores ItemId (offset, length) con la información

necesaria para localizar los elementos (Items) grabados en el bloque. Cada par ocupa 4 bytes.

• Item (row/index) : Cabecera de elemento más los datos en sí. Tamaño Variable.

• Espacio especial : Usado cuando el bloque pertenece a un índice. Tamaño variable.

El espacio usado por una cabecera de bloque (Page_header) se usa para guardar diferentes parámetros que nos ayudarán a localizar diferentes partes del bloque y guardar cierta información asociada al bloque. En cada elemento (Item) se guardan una cabecera de datos con un tamaño fijo (23 bytes), una pequeña cabecera opcional de datos con tamaño variable y los datos en sí de nuestras tablas o índices.

50

Para una completa descripción de estas cabeceras y las estructuras usadas para almacenar los datos en el disco se puede consultar la documentación y el código fuente.

PostgreSQL: Espacio en Disco Una base de datos PostgreSQL puede requerir hasta cinco veces el espacio en disco para almacenar datos de un archivo de texto. Como ejemplo, considere un archivo de 100.000 líneas con un entero y descripción de texto en cada línea. Supongamos que la cadena de texto mide en promedio veinte bytes de longitud. El archivo plano sería de 2.8 MB. El tamaño del Archivo de base de datos PostgreSQL que contenga estos datos se puede estimar en 5.2 MB:

24 bytes: cada fila de cabecera (aproximado) 24 bytes: un campo int y un campo de texto + 4 bytes: punteros en la página de tupla -------------------------------------------- 52 bytes por fila

El tamaño de página de datos en PostgreSQL es 8192 bytes (8 KB), por lo que:

8192 bytes por página --------------------- = 158 filas por página de base de datos (redondeado) 52 bytes por fila

100000 filas de datos --------------------- = 633 páginas de base de datos (redondeado hacia arriba) 158 filas por página

633 páginas bases de datos * 8192 bytes por pagina = 5,185,536 bytes (5.2 MB)

Los índices no requieren tanta sobrecarga, pero contienen los datos que se están indexando, por lo que pueden ser muy grandes también. Los valores NULL se almacenan como mapas de bits, por lo que usan muy poco espacio.

Tipos de Tablas En Linux, PostgreSQL almacena las bases de datos en el directorio “/var/lib/pgsql/data/base” y a partir de ahí un directorio para cada base. En Windows PostgreSQL almacena los archivos de las bases de datos en el directorio “C:\Archivos de Programa\PostgreSQL\9.2\data\Base”.

51

Cada tabla es considerada un archivo, así como los índices. Los nombres de las tablas pertenecientes al sistema llevan el prefijo “pg_”. Por ejemplo, el archivo PG_VERSION (presente en cada base de datos) contiene la versión mayor con la que fue creada la base y al cambiar de versión de PostgreSQL es importante respaldar este archivo. A continuación se muestra el contenido de las tablas que PostgreSQL utiliza como catálogos para mantener el sistema. Cada base de datos que se crea, tiene estas mismas tablas, salvo la primera (pg_database) que es única para todas las bases de datos:

Nombre tabla Descripción (que almacena)

pg_database Bases de datos

pg_class Clases o tablas

pg_attribute Atributos o campos de la clase o tabla

pg_index Índices secundarios

pg_proc Procedimientos

pg_type Tipos de datos (del sistema y definidos por el usuario)

pg_user Usuarios de PostgreSQL

pg_operator Operadores (del sistema y definidos por el usuario)

Tabla: Contenido de las tablas que se usan como catálogos del sistema Por ejemplo, para saber que bases de datos hay en el sistema se ejecuta la consulta:

1. postgres=# SELECT * FROM pg_database;

Para saber que tablas tengo en la base de datos actual:

2. postgres=# SELECT * FROM pg_class;

Si sólo queremos saber cuántos registros tiene una tabla, consultamos:

3. postgres=# SELECT relname,reltuples FROM pg_class WHERE relname='mitabla';

Tipos de Datos Como todos los manejadores de bases de datos, PostgreSQL implementa los tipos de datos definidos para los estándares SQL/92 y SQL3 (SQL/99) y aumenta algunos otros.

52

Algunos de estos tipos de datos se muestran en las tablas que están a continuación:

Tipo en Postgres

Correspondiente en SQL3

Descripción

Bool boolean valor lógico o booleano (true/false)

Char(n) character(n) cadena de caracteres de tamaño fijo

Date date fecha (sin hora)

Float8 real, double precision

número de punto flotante de doble precisión

int2 smallint entero de dos bytes con signo

int4 int, integer entero de cuatro bytes con signo

money decimal(9,2) cantidad monetaria

Time time Hora en horas, minutos, segundos y centésimas

timespan interval intervalo de tiempo

timestamp timestamp with time zone fecha y hora con zonificación

varchar(n) character varying(n) cadena de caracteres de tamaño variable

Tabla: Tipos de datos del Estándar SQL3 en PostgreSQL

Tipos de datos extendidos en PostgreSQL

Tipo Descripción

Box caja rectangular en el plano

Cidr dirección de red o de host en IP versión 4

circle círculo en el plano

Inet dirección de red o de host en IP versión 4

int8 entero de ocho bytes con signo

Line línea infinita en el plano

Lseg segmento de línea en el plano

Path trayectoria geométrica, abierta o cerrada, en el plano

53

point punto geométrico en el plano

polygon trayectoria geométrica cerrada en el plano

serial identificador numérico único con autoincremento

Tabla: Tipos de datos Extendidos en PostgreSQL

54

Gestión de Bases de Datos PostgreSQL Para la administración de un servidor de bases de datos PostgreSQL existen herramientas libres y gratuitas con interfaces gráficas e intuitivas para la administración completa de bases de datos PostgreSQL, entre estas herramientas están PhpPgAdmin o el PgAdmin y herramientas comerciales como PostgreSQL Manager. También hay una variedad de herramientas para la administración de bases de datos PostgreSQL en modo consola, como el terminal de cliente interactivo de postgreSQL “psql”, o las aplicaciones pg_dump y pg_restore para realizar copias de seguridad y restauración de bases de datos, o las herramientas para crear y eliminar bases de datos como createdb, dropdb, y otras más, generalmente ubicadas en el directorio “/bin” dentro del directorio donde se instaló PostgreSQL.

PSQL: La pequeña gran herramienta de PostgreSQL Asumiendo que PostgreSQL ya ha sido instalado e iniciado exitosamente, la herramienta principal para trabajar en modo línea de comandos con PostgreSQL es “psql”. Con psql tenemos una herramienta completa para poder manipular las bases de datos PostgreSQL mediante comandos DML (Lenguaje de Manipulación de datos) y DDL (Lenguaje de definición de datos). Psql viene incorporado en la instalación del paquete PostgreSQL para Windows o Linux con el mismo tipo de licencia que el paquete PostgreSQL, es decir BSD (Berkeley Software Distribution) y se instala en el directorio “/bin” dentro del directorio de instalación de PosgreSQL. PostgreSQL usa un modelo de comunicación cliente/servidor. Esto significa que el servidor está esperando las solicitudes de los clientes, las procesa y regresa el resultado. La interface psql permite interactuar con PostgreSQL, por lo que psql es el cliente interactivo de línea de comandos de PostgreSQL. Este programa nos permite realizar las tareas más comunes como ingresar, ejecutar un query y ver los resultados obtenidos, pero además incluye una serie de características muy completas en cuanto a la integración con scripts, que nos será de mucha utilidad para automatizar tareas repetitivas o periódicas. Esta es una breve introducción para su utilización, con la descripción de las funciones más usadas. Antes veamos las sentencias y datos más usados:

55

Sentencias básicas en SQL

Comando Descripción

CREATE TABLE Crear una tabla.

INSERT INTO Insertar datos en una tabla.

SELECT Desplegar datos.

WHERE Filas específicas.

DELETE Remover datos.

UPDATE Reemplazar ó actualizar datos.

ORDER BY Ordenar el resultado.

DROP Destruir.

Tipos más comunes de datos

Categoría Tipo Descripción

Cadena de caracteres

CHAR(long) Long. fija de almacenamiento.

VARCHAR(long) Long. variable de almacenamiento.

Número

INTEGER Entero, +/- 2 billones.

FLOAT Punto decimal, 15 dígitos.

NUMERIC (precisión, decimal)

Número usando una definición de precisión y de número de decimales.

Fecha/hora

DATE Fecha.

TIME Hora.

TIMESTAMP Fecha y hora.

Ejecutando psql desde el sistema operativo La aplicación psql se invoca desde la línea de comandos del sistema operativo con una serie de opciones (flags) muy abundante con la siguiente sintaxis. psql [OPCIONES] … [BASE DE DATOS [USUARIO]]

56

Algunas de Las opciones más utilizadas y útiles al momento de invocar la aplicación son:

Opción (flag) Descripción

--help Muestra todas las opciones de psql y sale.

-U USUARIO Especifica con que usuario se conectará a la base de datos. Si esta opción no es utilizada intentará conectarse el usuario que ejecute psql.

-d [BASE DE DATOS]

Especifica a que base de datos conectarse inicialmente. (Si esta opción no es utilizada intentará conectarse a una base de datos con el mismo nombre del usuario utilizado para conectarse.)

-h EQUIPO

Especifica en que equipo se encuentra el servidor de base de datos .Puede indicarse el nombre o la dirección IP del servidor. Si no se especifica este parámetro, intentará conectarse al equipo en donde se ejecuta psql.

-p PUERTO Especifica en que puerto se encuentra ejecutando el servicio de base de datos en el servidor .Si no se especifica este parámetro, intentará conectarse al puerto por defecto (5432).

-c [COMANDO] Ejecuta solo el comando indicado como parámetro y luego sale.

-f [ARCHIVO] Ejecuta los comandos SQL contenidos en archivo indicado como parámetro y luego sale.

-l Lista las bases de datos disponibles y luego sale.

-o [ARCHIVO] Envía los resultados de las consultas al archivo indicado como parámetro.

-L [ARCHIVO] Envía el registro de toda la sesión a un archivo indicado como parámetro. Es muy útil para mostrar una serie de comandos ejecutados y sus resultados.

-H Genera el resultado de las consultas en formato HTML.(La combinación de este comando con la opción “-o” nos permite realizar un script que genere reportes de manera muy sencilla.

-A Genera el resultado de las consultas sin formatear el resultado. Es muy útil para pasar datos a otros formatos (por ejemplo, para importar un archivo de texto a Excel)

-t Muestra solo las filas sin cabeceras y sin contadores.

-e Hace “eco” de la consulta enviada al servidor.

57

-E Hace “eco” de todas las consultas enviadas al servidor.

-x

Activa el formato expandido para mostrar los resultados. Este formato muestra cada fila como una especie de ficha, encabezada por el número de fila y con una línea por cada columna.

-F [CADENA] Utiliza la cadena indicada como separador entre columnas. Utilizando esta opción en conjunto con “-a” se pueden generar archivos tipo CSV (delimitados por comas). Por defecto es '|'.

-t Imprime solo las filas seleccionadas (sin encabezados).

Algunos ejemplos de la utilización de estas opciones:

Invocando psql especificando usuario, equipo, puerto y base de datos. 3. -bash-4.1$ psql -U testuser -h 192.168.0.10 -p 5432 -d test 4. psql (8.4.13) 5. Type "help" for help. 6. 7. test=#

Generar un archivo tipo CSV (delimitado por comas) con el resultado de una

consulta. Este archivo se puede abrir desde Excel. 1. -bash-4.1$ psql -U testuser -d test -F ";" -A -c "select * from table" -o table.csv 2. -bash-4.1$ cat testtable.csv 3. Id;col1;col2 4. 100;hola;mundo 5. 101;ciao;mondo 6. 102;hello;world 7. 103;bonjour;monde 8. (4 filas) 9. -bash-4.1$

58

Generar un reporte HTML a partir de una consulta.

1. -bash-4.1$ psql -U testuser -d test -H -c "select * from table" -o table.html 2. -bash-4.1$ firefox table.html

Ejecutando psql en modo interactivo Una vez dentro de psql, entramos en modo interactivo, es decir, ejecutamos un comando SQL y psql nos muestra el resultado del mismo. En este modo tenemos también una gran cantidad de posibilidades, gracias a los comandos propios de psql. Estos comandos se ejecutan directamente desde la línea de entrada (prompt) de pql y tienen la característica de estar siempre precedidos por la barra invertida “\“. Algunos de los comandos internos más utilizados son:


\c “BASE DE DATOS” USUARIO EQUIPO PUERTO

Permite reconectarse. Recibe como parámetros la base de datos, usuario, equipo y puerto.

\q Sale de psql.

\timing Habilita en reporte de tiempos de ejecución. Es muy útil para registrar cuánto tarda un SQL en ejecutarse.

\! COMANDO Ejecuta el parámetro ingresado en el sistema operativo. Si no se especifican parámetros sale temporalmente (hasta que se cierra con “exit”) a la línea de comandos del sistema operativo.

\e ARCHIVO Invoca al editor de textos predeterminado. Si no se especifica el parámetro ARCHIVO, edita el último comando SQL (del

59

búfer interno). Si se especifica un archivo lo abre con el mismo editor. (Para definir el editor preferido, usa la variable “EDITOR” del sistema operativo.).


\g ARCHIVO Ejecuta los comandos SQL almacenados en el búfer. Si se especifica el parámetro ARCHIVO, el resultado es enviado al archivo indicado, si no se muestra por pantalla.

\p Imprime el contenido el búfer de SQL.

\w ARCHIVO Guarda el contenido del búfer de SQL en un Archivo (parámetro obligatorio).

\s ARCHIVO Muestra la historia (últimos comandos SQL ejecutados). Si se especifica el parámetro ARCHIVO, el resultado es enviado al archivo indicado, si no se muestra por pantalla..

\i ARCHIVO Ejecuta los comandos SQL que estén almacenados en el archivo.

\o ARCHIVO Envía los resultados de las consultas al archivo indicado como parámetro.

\dt Lista todas las tablas.

\d TABLA Lista las columnas de la tabla indicada como parámetro. (Describe la tabla)

Algunos ejemplos de los comandos internos: 1. -bash-4.1$ psql 2. psql (8.4.13) 3. Type "help" for help.

1. postgres=# \l 2. List of databases 3. Name | Owner |Encoding| Collation | Ctype | Acces 4. ------------+----------+--------+-------------+-------------+----------------- 5. Pruebas | pruebas | UTF8 | en_US.UTF-8 | en_US.UTF-8 | 6. dbadmision | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | 7. fiscal | fiscal | UTF8 | en_US.UTF-8 | en_US.UTF-8 | 8. postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | 9. template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres 10. : postgres=CTc/postgres 11. template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres 12. : postgres=CTc/postgres 13. test | testuser | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =CTc/testuser 14. : testuser=CTc/testuser 15. 7 (rows) 16. 17. postgres=#\c test 18. You are now connected to database "test".

61

1. postgres=# create database test; 2. postgres=# \q -- para salir de PostgreSQL

Para conectarse a una base de datos hay que teclear: 3. -bash-4.1$ psql –d test –U testusr

En este caso test es el nombre de la base de datos, pero puede ser cualquier otro. Hay que diferenciar entre mayúsculas y minúsculas Una vez conectados al servidor PostgreSQL, teclear lo siguiente: 4. test=>SELECT CURRENT_USER; 5. current_user 6. -------------- 7. testuser 8. (1 row) 9. 10. test=>

Esto debe mostrar el login name bajo la línea punteada. La línea test=> indica que el servidor está esperando el siguiente query, Teclear: 11. test=>SELECT CURRENT_TIMESTAMP;; 12. now 13. ---------------------------- 14. 2013-05-31 23:03:15.578-05 15. (1 row) 16. 17. test=>

Esto debe mostrar la fecha y la hora actual.

Query multi-línea en modo interactivo En psql los comandos se completan cuando digitamos ; ó \g. Por ejemplo: 18. test=# SELECT -- el prompt cambia de =# a -# para indicar 19. test-# 1 + 3 -- que la sentencia SQL aún no finaliza 20. test -#; -- Esto debe responder con un 4 21. column 22. --------- 23. 4 24. (1 row) 25. 26. test=#

En PostgreSQL las ordenes SQL pueden escribirse en mayúsculas o en minúsculas. Por claridad se recomienda escribir palabras especiales en mayúsculas.

62

Con las teclas de las flechas derecha e izquierda se puede recorrer lo que se escribe, con las flechas arriba y abajo se recuperan las líneas previamente escritas.

Query buffer y help • Se puede teclear indefinidamente, hasta que se use el punto y coma (;) o la

diagonal invertida-g (\g). Todo esto se irá guardando en el query buffer. • Si se teclea \p, se puede ver todo lo acumulado en el query buffer. • Para borrar el buffer se teclea \r. • \? Muestra todos los comandos que inician con diagonal invertida • Para salir de una sesión usar \q. 1. test =# CREATE TABLE alltypes( 2. test -# estado CHAR(3), 3. test -# nombre char (20), 4. test -# hijo INTEGER, 5. test -# distancia FLOAT, 6. test -# presupuesto NUMERIC(16,2), 7. test -# nacimiento DATE, 8. test -# llegada TIME, 9. test -# inicio TIMESTAMP); 10. CREATE TABLE 11. test =#

12. test =# INSERT INTO alltypes 13. test -# VALUES( 14. test -# 'MEX', 15. test -# 'Mauricio', 16. test -# 0, 17. test -# 10.7, 18. test -# 6289.08, 19. test -# '14/08/1984', 20. test -# '7:45', 21. test -# '25/03/2007 10:30:00'); 22. INSERT 0 1 23. test =#

Usando valores NULL y BLANK NULL es un valor especial que es válido en cualquier columna. Lo usamos cuando se desconoce el valor o cuando no aplica.

1. test =# INSERT INTO alltypes (estado, nombre) 2. test -# VALUES ('AGS', 'Gabriel'); 3. test =# select * from alltypes;

En algunas columnas no se insertó valor, ¿Qué contienen?

63

4. test =# SELECT * FROM alltypes WHERE hijo IS NULL;

Hay diferencia entre cero y NULL (hijo->Mauricio y Gabriel)

5. test =# SELECT * FROM alltypes WHERE hijo <> 100;

Los que tienen NULL en la columna hijos no aparecen

6. test =# INSERT INTO alltypes (estado, nombre) 7. test -# VALUES ('', 'Arturo'); -- ES UNA CADENA BLANK, LOS CAMPOS 8. -- NUMERICOS NO PUEDEN SER BLANK 9. test -# VALUES (NULL, 'José'); 10. test =# SELECT * FROM alltypes WHERE estado IS NULL; 11. test =# SELECT * FROM alltypes WHERE estado = '';

NULL vs BLANK Una columna tipo cadena de caracteres además de ser NULL, también puede ser blank. Un campo numérico no puede ser blank, solo NULL.

12. test =# INSERT INTO alltypes (estado, nombre) 13. test -# VALUES ('', 'Arturo'); 14. test -# VALUES (NULL, 'José'); 15. test =# SELECT * FROM alltypes WHERE estado IS NULL; 16. test =# SELECT * FROM alltypes WHERE estado = '';

Valores DEFAULT

� Cuando no se da un valor a la columna se le asigna un NULL. � Cuando creamos una tabla, con la palabra clave DEFAULT en la columna,

podemos dar un valor cuando no se asigne alguno. � Por ejemplo, para la columna timestamp el valor de default puede ser una

variable interna de PostgrSQL que regresa la fecha y la hora actual.

1. test=# CREATE TABLE cuenta ( 2. test-# nombre VARCHAR(20), 3. test-# balance NUMERIC(16,2) DEFAULT 0, 4. test-# activa CHAR(2) DEFAULT 'SI', 5. test-# creada TIMESTAMP DEFAULT CURRENT_TIMESTAMP); 6. test=# \d cuenta 7. test=# INSERT INTO cuenta (nombre) 8. test-# VALUES ('Palacio de Hierro'); 9. test=# SELECT * FROM cuenta;

Ejemplos

64

1. test=# ALTER TABLE alltypes ALTER inicio 2. test-# SET DEFAULT CURRENT_TIMESTAMP; 3. ALTER TABLE 4. test=# INSERT INTO alltypes (estado, nombre) 5. test-# VALUES ('MEX', 'Melissa'); 6. INSERT 0 1 7. test=# SELECT * FROM alltypes;

pg_dump y pg_dumpall Las herramientas clientes pg_dump y pg_dumpall nos ayudan tanto para respaldar las tablas o bases de datos o para migrarlas de un sistema a otro en un formato transportable. Estas utilidades vienen incluidas en el paquete de instalación PostgreSQL para Windows o Linux y tienen el mismo tipo de licencia que el paquete PostgreSQL, es decir BSD (Berkeley Software Distribution). Se instalan en el directorio "\bin" dentro del directorio de instalación de PosgreSQL. La utilidad pg_dump apareció en la versión "release 0.02" de Postgres95, pero se mejoró desde la versión de PostgreSQL "release 7.1". En algunos casos al actualizar la versión de PostgreSQL será necesario primero respaldar las tablas con estas herramientas para posteriormente volverlas a cargar; pg_dump se emplea para respaldar una base de datos o una tabla en particular, mientras que pg_dumpall respalda todas las bases de datos del sistema. Su sintaxis es la siguiente:

pg_dump [opciones.] [nombre_base_datos] A continuación se muestran algunas de sus opciones más útiles:

-a: Respalda solo los datos en un archivo de texto plano, no las definiciones (esquema). -f formato: Selecciona el formato de salida para el archivo que puede ser:

p: Es el formato predeterminado; el archivo se genera en un archivo plano de texto de tipo "SQL script". t: La salida se genera en un archivo ".tar"; este formato puede ser modificado en la entrada del programa que recupera respaldos pg_restore. Usando este formato de archivo se permite excluir objetos de la base de datos en el momento en que se restaura. c: El archivo de salida se genera en un formato modificable para la entrada en el programa pg_restore. Este es el formato más flexible para reordenar la

65

carga de datos con las definiciones de los objetos (esquema). Este formato se comprime por defecto.

-s: Solamente respalda el esquema de la base de datos (definiciones de objetos), no los datos. -t tabla: Respalda los datos y el esquema de la tabla especificada.

La forma más general de emplear pg_dump para respaldar sólo una tabla, es la siguiente:

pg_dump -t mitabla mibase > mibase.mitabla.dump Con lo cual en el archivo "mibase.mitabla.dump" tenemos un archivo con los datos y el esquema de la tabla 'mitabla' para poder recuperar toda la información de la misma. Al respaldar una tabla de esta manera, lo primero que hace pg_dump es tratar de identificar al usuario creador de la tabla, luego crea la tabla, fija los permisos existentes, copia los datos y finalmente crea el índice si es que la tabla tiene un índice asociado. Es mejor comprimir los archivos para ocupar el mínimo espacio posible. Por ejemplo, podemos hacerlo así:

pg_dump -t mitabla mibase | gzip -9c > mibase.mitabla.dump.gz pg_dump -t mitabla mibase | bzip2 -c > mibase.mitabla.dump.bz

Ambos programas de compresión (gzip y bzip2) pueden ser consultados con el sistema man (de manual) en Linux. Para recuperar la información en la base de datos, podemos emplear la siguiente instrucción en la línea de comandos:

gunzip -c mibase.mitabla.dump.gz bunzip2 -c mibase.mitabla.dump.bz

pg_restore Esta herramienta restaura o recupera una base de datos PostgreSQL desde un archivo creado con la herramienta pg_dump. Pg_restore apareció por primera vez en la versión 7.1 de PostgreSQL. Al igual que las anteriores, esta utilidad se instala en el directorio "\bin" dentro del directorio de instalación de PosgreSQL y tiene el mismo tipo de licencia (BSD) que el paquete PostgreSQL.

66

Esta utilidad restaura una base de datos de PostgreSQL desde un archivo especificado, este archivo guarda los comandos SQL necesarios para reconstruir la base de datos al estado en el cual fue guardada o respaldada y su sintaxis es:

pg_restore [opciones] [nombre_archivo_a_restaurar] Algunas opciones que tiene pg_restore son las siguientes:

-a: Recupera solamente los datos, no el esquema o definición de objetos. -d nombre_db: Se conecta a la base de datos especificada y recupera directamente los datos en la misma. -e: Termina la operación si ocurre un error mientras se envían los comandos SQL para restaurar la base de datos. Por defecto, si se encuentra un error en la operación, continúa la recuperación y al final se muestra un contador y una descripción de los errores ocurridos. -f formato: Especifica el formato del archivo, esta opción no es necesaria ya que pg_restore determina el formato de manera automática. El formato del archivo puede ser uno de los siguientes: t: El archivo es un archivo ".tar". Usando este formato de archivo se permite excluir objetos de la base de datos en el momento en que se restaura. c: El formato de archivo se puede modificar en pg_restore. Este es el formato más flexible para reordenar la carga de datos con las definiciones de los objetos (esquema).

Este formato se comprime por defecto.

-s: Solamente recupera el esquema de la base de datos (definiciones de objetos), no los datos. -t tabla: Recupera la definición y/o los datos de la tabla especificada.

A continuación se muestra un ejemplo respaldando una base de datos llamada mydb con pg_dump y luego la restauro con pg_restore en una nueva base de datos llamada newdb:

pg_dump -ft mydb > db.tar pg_restore -d newdb db.tar

createdb y dropdb

67

Una de las primeras operaciones para probar la conexión y correcta instalación del servidor de bases de datos PostgreSQL es crear una base de datos. Para crear una base de datos se puede utilizar la aplicación createdb y para eliminarla del servidor se utiliza dropdb. Estas aplicaciones se instalan en el directorio "\bin" dentro del directorio de instalación de PosgreSQL y su sintaxis es la siguiente:

createdb nombre_base_datos dropdb nombre_base_datos

Un ejemplo de cómo crear una base de datos (mydb) mediante la aplicación createdb se muestra a continuación:

createdb mydb Createdb es una aplicación alternativa al comando SQL "CREATE DATABASE" y luego de ejecutarse esta aplicación se muestra la respuesta "CREATE DATABASE". Si se recibe un mensaje similar a "createdb: command not found" puede ser porque PostgreSQL no fue instalado apropiadamente o porque no se encuentra la ruta de la aplicación en el directorio "\bin" del directorio de instalación de PostgreSQL, entonces se debe probar escribiendo la ruta completa en donde se encuentra la aplicación y ejecutarla. En Linux por ejemplo, la aplicación createdb se encuentra en "/usr/bin/createdb" mientras que en Windows se encuentra en "C:\Archivos de programa\PostgreSQL\ 8.1\bin\createdb". Otra respuesta de error al ejecutar createdb puede ser la siguiente:

psql: could not connect to server: Connection refused Is the server running on host "nombre_server" and accepting TCP/IP connections on port 5432?

En cuyo caso deberemos revisar los archivos de configuración de PostgreSQL (pg_hba.conf, pg_ident.conf y postgresql.conf) ubicados en el directorio "/Data" donde se instaló el servidor PostgreSQL o debemos reinstalar el servidor PostgreSQL porque no se puede establecer una conexión al servidor. Para eliminar una base de datos físicamente del servidor se utiliza la aplicación dropdb, la misma que es una aplicación alternativa al comando SQL "DROP DATABASE".

68

Al eliminar una base de datos se eliminan también todos los objetos que contiene la base de datos, además se debe tener cuidado porque no se puede recuperar una base de datos una vez que esta se elimine.

Postmaster y pg_ctl Luego de instalar PostgreSQL y antes de acceder a cualquier base de datos se debe arrancar el servidor PostgreSQL. Esta operación se la puede realizar mediante el programa llamado postmaster, indicándole al mismo donde se encuentra el directorio "\Data" de PostgreSQL mediante la opción -D. Este programa se encuentra en el directorio "bin" donde se instaló PostgreSQL (/usr/bin/postmaster) y la manera más común de arrancar el servidor mediante la aplicación postmaster es:

postmaster -D /var/lib/pgsql/data Pg_ctl es una aplicación incluida también en el directorio "bin" donde se instala PostgreSQL, y se proporciona para simplificar algunas tareas como iniciar y detener al servidor PostgreSQL. Por ejemplo:

pg_ctl start -l logfile Esta operación arranca el servidor y coloca las transacciones en el archivo especificado (logfile). La opción de -D le indica al servidor PostgreSQL donde se encuentra el directorio "\Data" que es donde normalmente se guardan las bases de datos.

PgAdmin PgAdmin es una aplicación con interfaz gráfica comprensible para el diseño y administración total de bases de datos PostgreSQL; esta aplicación está diseñada para ejecutarse en sistemas operativos como GNU/Linux y Windows. PgAdmin versión 3 se ejecuta desde la versión de la base de datos PostgreSQL 7.3 y superiores. Para versiones anteriores de la base de datos, se debe usar la versión de PgAdmin2.

69

PgAdmin se distribuye libremente bajo licencia de tipo GNU separadamente de PostgreSQL y se puede descargar su última versión (PgAdmin3-1.4.3) desde el sitio web "http://www.pgadmin.org/download/" en las versiones para Linux o Windows. La versión de PgAdmin III tiene las siguientes características:

• Esquema de navegación de todos los objetos de PostgreSQL. • Diálogos de creación y propiedades de objetos (usuarios, tablas, bases de

datos, disparadores, etc.). • Herramienta de edición/visualización de tablas. • Habilidad para navegar y conectarse a múltiples servidores a la vez. • Interfaz de usuario intuitiva y traducida a más de 20 idiomas. • La ventana principal muestra la estructura de la base de datos y todos los

detalles de los objetos contenidos en la misma. • Se puede controlar o administrar los usuarios de las bases de datos,

manejando los privilegios, usuarios, grupos y contraseñas. • Permite llevar un control sobre el estado del servidor de bases de datos,

permitiendo iniciarlo o detenerlo. • Posee una herramienta avanzada para consultas, permitiendo ejecutar

cualquier sentencia SQL.

70

• Permite exportar datos en distintos formatos a partir de una consulta SQL generada. Permite ver y editar los datos de una consulta a una tabla o vista.

• Tiene una herramienta de Mantenimiento que ejecuta tareas como reconstruir las estadísticas de las bases de datos y tablas, limpiar o eliminar los datos sin usar y reorganizar los índices.

• Permite sacar copias o respaldos de las bases de datos y restaurarlas haciendo uso de las herramientas pg_dump y pg_restore de PostgreSQL.

• La ventana del "estado del servidor" muestra los usuarios actualmente conectados, los bloqueos y características del servidor seleccionado.

71

Administración de Sesiones

Inicio y Tipos de sesiones: Los usuarios pueden iniciar sesiones al acceder a una base de datos PostgreSQL utilizando diversos tipos de procesos frontend:

• Ejecutando el programa de terminal interactiva psql, que permite introducir, editar y ejecutar sentencias SQL sobre una base de datos.

• Utilizando una herramienta gráfica como PgAdmin III para crear y manipular bases de datos.

• Mediante un programa de aplicación hecho a medida que incorpore sentencias SQL para acceder a la base de datos.

En nuestras prácticas utilizaremos la terminal interactiva psql y PgAdmin III para conectarnos a una base de datos y practicar sobre ella el lenguaje SQL. Control de sesiones: Proceso: En primer lugar, nos conectamos a nuestro servidor de bases de datos PostgreSQL. 1. $ psql -h servidor -U postgres -d postgresql 2. (9.2.6, server 8.4.11) 3. Type "help" for help. 4. 5. postgres=#

Cantidad de sesiones y actividad de los usuarios Saber cuántas sesiones o cuantos usuarios están conectados a mi servicio postgres 6. postgres=# select count(*) from pg_stat_activity;

Retorna:

72

Obtener el registro completo de la actividad de los usuarios y determinar los campos de la tabla de sesiones: 7. postgres=# select * from pg_stat_activity;

Retorna:

Cerrar sesiones de usuarios conectados a Postgresql (conexiones activas de otros usuarios) Muchas veces hay usuarios que han dejado su conexión abierta y está bloqueando la base de datos impidiendo hacer algunas tareas de administración, vacuums, renames, etc o en ciertas ocasiones deseamos eliminar una Base de Datos y no se deja porque conservan conexiones sin actividad (IDLE). En PostgreSQL podemos solventarlo desde el cliente psql con la función pg_terminate_backend. Cada conexión a la base de datos en PostgreSQL tiene asociado un proceso PID (process id) y esta información se almacena en la tabla pg_stat_activity. La función pg_terminate_backend nos permite enviar una señal a dicho proceso para que finalice una sesión específica de un usuario. Proceso: En primer lugar, nos conectamos a nuestro servidor de bases de datos PostgreSQL 1. $ psql -h servidor -U postgres -d postgres 2. sql (9.1.2, server 8.4.11) 3. Type "help" for help. 4. 5. postgres=#

Ejecutamos la consulta que nos proporcionará el listado de todas las conexiones establecidas a nuestra base de datos. Este paso es opcional, pero realizarlo siempre nos dará una idea de las conexiones existentes, y si están activas, o solo permanece la conexión (IDLE). Obtener los procesos de postgres activos. Dentro del "psql" ejecuto el comando:

73

6. postgres=# select datname, pid, usename, application_name, client_addr, query from

pg_stat_activity;

Que regresa:

Como vemos existen 4 registros en la tabla pg_stat_activity, que se corresponden con 4 conexiones establecidas pero 3 sin actividad (IDLE). Para cada conexión se almacena además del PID del proceso pid, IP y puerto, usuario de conexión, query lanzada, estado, … Una vez obtenida la lista con los procesos, solo queda utilizar la función pg_terminate_backend(pid). Su uso es sencillo, si deseamos eliminar una determinada conexión le pasamos a la función, el pid de dicha conexión: Si se quiere matar al proceso 1108 (que está como IDLE en este ejemplo), se puede ejecutar el comando: 7. postgres=# select pg_terminate_backend(pid) from pg_stat_activity where pid = 1108;

8. postgres=# select datname, pid, usename, application_name, client_addr, query from

pg_stat_activity;

74

Que regresa:

Por supuesto, cuidado al matar procesos de Postgresql, se tiene que saber realmente lo que se está haciendo!!!!! Para eliminar todas las conexiones sin actividad, con una única instrucción: 9. select pg_terminate_backend(pid) from pg_stat_activity where query = '<IDLE>';

Para eliminar todas las conexiones de una determinada base de datos, con una única instrucción: 10. postgres=# select pg_terminate_backend(pid) from pg_stat_activity where datname =

'test';

Más información al respecto en: http://www.postgresql.org/docs/8.2/interactive/functions-admin.html#FUNCTIONS-ADMIN-SIGNAL-TABLE

75

Caso de Estudio El diagrama siguiente es un modelo pequeño, pero servirá para las demostraciones de la administración de la estructura de los objetos con el uso de Sentencias DDL, manipulación de la información con el uso de Sentencias DML, administración de los procesos transaccionales con el uso de Sentencias TCL y administración de privilegios en los objetos con el uso de Sentencias DCL en PostgreSQL. Descripción: Una universidad realiza el registro de contactos, que son las personas que podrían ser potenciales postulantes a diferentes carreras. Los postulantes deberán pertenecer a un periodo académico y deberán elegir una modalidad así como la carrera a la que quieren postular. Por lo general en un año solo existen dos periodos académicos, por ejemplo los periodos académicos del año 2008 fueron: 2008-1 y 2008-2. El contacto debe tener los datos personales del sujeto así como la fecha de creación. El postulante debe tener registrado si asistió a su examen de admisión, y si ingresó o no, debe tener también establecido el puntaje que alcanzó en el examen. Con esos datos podemos comenzar a elaborar la estructura de nuestro sistema de información y las relaciones que existen entre todos sus objetos: DBAdmision, Admisión, Persona, carrera, peracad (período académico), modalidad, postulante y contacto

76

Sentencias DDL El Lenguaje de Definición de Datos (Data Definition Language - DDL) sirve para modificar la estructura de los objetos en una base de datos. El DDL se utiliza para describir todas las estructuras de información y los programas que se usan para construir, actualizar e introducir la información que contiene una base de datos. El DDL permite al administrador de la base especificar los elementos de datos que la integran , su estructura y las relaciones que existen entre ellos, las reglas de integridad, los controles a efectuar antes de autorizar el acceso a la base. Estas sentencias básicamente son: CREATE, ALTER, DROP y TRUNCATE. --0) CREACION DE TABLESPACE

CREATE TABLESPACE tablespace_name [ OWNER user_name ] LOCATION ‘directory’

CREATE TABLESPACE registra un nuevo espacio de tabla en todo el clúster. El nombre del espacio de tabla debe ser distinto del nombre de un espacio de tabla existente en la base de datos del clúster. Un espacio de tablas permite a los superusuarios definir una ubicación alternativa del sistema donde los archivos de datos contienen los objetos de las Bases de Datos (como tablas e índices). Un usuario con privilegios adecuados puede pasar el tablespace_name para CREAR BASES DE DATOS, CREAR TABLAS, CREAR INDIXES o Agregar RESTRICCIONES para tener los archivos de datos para estos objetos almacenados en el espacio de tablas especificado.

Parámetros de Entrada

tablespace_name Nombre del Espacio de Datos, no debe comenzar con pg_, porque son nombres reservados para los sistemas.

user_name Nombre del propietario del tablespace, default - usuario que crea el tablespace. Solo los superusuarios pueden crear tablespaces, pero se les puede asignar a no superusuarios

directory Directorio usado por el tablespace. El directorio debe estar vacio, debe ser de propiedad del usuario del sistema PostgrSQL y especificado por una ruta absoluta.

Salidas más usuales

CREATE TABLESPACE Mensaje devuelto si la orden se completa satisfactoriamente.

77

ERROR: user ‘username’ is not allowed to create/drop tablespaces. Ha de tener el privilegio especial CREATEDB para crear bases de datos.

Los tablespaces son únicamente soportados por sistemas que soportan links simbólicos y la instrucción no puede ser ejecutada desde una transacción. Ejemplos: Crear el tablespace dbspace en /data/dbs: 1. CREATE TABLESPACE dbspace LOCATION '/data/dbs';

Crear el tablespace indexspace en /data/indexes de propiedad del usuario indexuser: 2. CREATE TABLESPACE indexspace OWNER indexuser LOCATION '/data/indexes';

--1) CREACIÓN DE BASE DE DATOS (DATABASE):

CREATE DATABASE name [ [ WITH ] [ OWNER [=] user_name ]

[ TEMPLATE [=] template ] [ ENCODING [=] encoding ] [ LC_COLLATE [=] lc_collate ] [ LC_CTYPE [=] lc_ctype ] [ TABLESPACE [=] tablespace_name ] [ CONNECTION LIMIT [=] connlimit ] ]

Para crear una base de datos, debe ser un superusuario o tener el privilegio especial CREATEDB. Normalmente, el creador se convierte en el propietario de la nueva base de datos. Los Superusuarios pueden crear bases de datos de propiedad de otros usuarios mediante el uso de la cláusula OWNER. Incluso pueden crear bases de datos pertenecientes a usuarios sin privilegios especiales. Los no superusuarios con privilegio CREATEDB sólo pueden crear bases de datos de su propiedad. De forma predeterminada, la nueva base de datos se crea clonando el sistema estándar de base de datos template1.

Parámetros de Entrada name Nombre de la Base de Datos

user_name Nombre del propietario de la Base de Datos, default - usuario que crea la base de datos

template template1: default - con locales adicionados. template0: base de datos virgen con los objetos estándar

78

encoding Set de caracteres. 'UTF8', 'SQL_ASCII'…..

lc_collate Orden de comparación. 'Spanish, Colombia' …… Afecta el orden y los índices aplicados a los strings (order by)

lc_ctype Clasificación de caracteres. 'Spanish, Colombia'…..Afecta a la clasificación de los caracteres: mayúsculas, minúsculas y dígitos.

tablespace_name Nombre del tablespace donde se ubica Base de Datos. Si no se especifica queda en el tablespace pg_default.

connlimit Número de conexiones simultáneas a la Base de Datos. -1 (default) significa que no hay límite.


CREATE DATABASE Mensaje devuelto si la orden se completa satisfactoriamente.

ERROR: user ‘username’ is not allowed to create/drop databases Ha de tener el privilegio especial CREATEDB para crear bases de datos.

Ejemplos:

Crear la Base de Datos DBAdmision: 1. CREATE DATABASE DBAdmision;

Crear la Base de Datos test de propietario testuser con el tablespace predeterminado testspace: 2. CREATE DATABASE test OWNER testuser TABLESPACE testspace;

Crear la Base de Datos musica que soporte el set de caracteres ISO-8859-1: 3. CREATE DATABASE musica ENCODING 'LATIN1' TEMPLATE template0;

En este ejemplo, la clausula TEMPLATE template0 debería ir únicamente sí el código de template1 no es ISO-8859-1, note que cambiarlo requiere seleccionar los nuevos LC_COLLATE y LC_CTYPE.

Para el propósito de la práctica:

4. CREATE DATABASE DBAdmision TEMPLATE template0;

Con la línea CREATE DATABASE DBAdmision TEMPLATE template0, se crea una tabla a partir de una plantilla que trae postgres llamada template0, la que nos crea la base de datos totalmente vacía.

79

--2) CREACIÓN DE ESQUEMAS (SCHEMAS): CREATE SCHEMA schema_name [AUTHORIZATION user_name]

[schema_element [ ... ] ] CREATE SCHEMA AUTHORIZATION user_name [schema_element [ .. ] ]

Los esquemas son importantes para agrupar objetos según especificaciones, y al mantener organizado la base de datos permite un mejor desempeño al momento de la administración. Un esquema es esencialmente un NAMESPACE: contiene objetos (tablas, tipos de datos, funciones y operadores), cuyos nombres pueden estar duplicados en otros esquemas. Los objetos se acceden por su nombre con el prefijo del esquema donde residen. Si no se le asigna un esquema al objeto, postgres lo asignará implícitamente al esquema “public“. Para definir que un objeto pertenezca a un esquema se pone el nombre del esquema seguido por un punto <.> y el nombre del objeto, que puede ser una tabla, una secuencia, etc…


schema_name

Nombre del Esquema. Si se omite, el nombre de usuario se usa como el nombre del Esquema. El nombre no puede empezar con pg_, porque están reservados para esquemas del sistema.

user_name

Nombre del usuario propietario del Esquema. Si se omite, el propietario será quien ejecute el comando Sólo los superusuarios pueden crear esquemas de propiedad para otros usuarios.

schema_element

Una sentencia de SQL que define un objeto que se crea en el esquema. Actualmente, sólo CREATE TABLE, CREATE VIEW, CREATE INDEX, CREATE SEQUENCE, CREATE TRIGGER y GRANT se aceptan como cláusulas de CREATE SCHEMA. Otros tipos de objetos pueden ser creados en comandos separados después de que se creó el esquema.


CREATE SCHEMA Mensaje devuelto si la orden se completa satisfactoriamente.

ERROR: user ‘username’ is not allowed to create/drop schemas. Ha de tener el privilegio especial CREATE en la Base de Datos.

Ejemplos:

Crear un esquema:

80

1. CREATE SCHEMA esquema;

Crear un esquema joe para el usuario joe: 2. CREATE SCHEMA AUTHORIZATION joe;

Crear el esquema cine que contenga la tabla films y la vista ganadores: 3. CREATE SCHEMA cine 4. CREATE TABLE films (titulo text, fecha date, premios text[]) 5. CREATE VIEW ganadores AS 6. SELECT titulo, fecha FROM films WHERE premios IS NOT NULL;

Note que los subcomandos individuales no terminan en punto y coma. La siguiente es una forma equivalente para obtener el mismo resultado: 7. CREATE SCHEMA cine; 8. CREATE TABLE films (titulo text, fecha date, premios text[]); 9. CREATE VIEW ganadores AS 10. SELECT titulo, fecha FROM films WHERE premios IS NOT NULL;

Para el propósito de la práctica:

11. CREATE SCHEMA Persona; 12. CREATE SCHEMA Admision;

Con las líneas CREATE SCHEMA Persona y CREATE SCHEMA Admision separamos la información relacionada. --3) CREACIÓN DE SECUENCIAS (SEQUENCE):

CREATE [ TEMP ] SEQUENCE name [ INCREMENT increment ] [ MINVALUE minvalue ] [ MAXVALUE maxvalue ] [ START start ] [ CACHE cache ] [ CYCLE ] [ OWNED BY { table_name.column_name | NONE } ]

Crea una nueva secuencia de generador de números. Esto involucra crear e inicializar una tabla especial de una sola fila con el nombre name. El generador será de propiedad del usuario que ejecuta el comando. La secuencia se crea de forma predeterminada en el esquema actual, de lo contrario debe precederla el nombre del esquema. Las secuencias temporales no se deben preceder del nombre de esquema porque se crean en un esquema especial. El nombre de la secuencia debe ser distinto de otros objetos que residan en el mismo esquema.

81

Para operar la secuencia se usan las funciones nextval, currval y setval.


TEMP Si se especifica, la secuencia se crea solo para esta sesión y se elimina al salir.

name Nombre de la Secuencia.

increment Especifica el incremento. Un valor positivo hará una secuencia ascendente, uno negativo hará una secuencia descendente. El valor por omisión es uno (1).

minvalue Valor mínimo que una secuencia puede generar. El valor por omisión es 1 y -2147483647 para secuencias ascendentes y descendentes, respectivamente.

maxvalue Valor máximo para una secuencia. Por omisión son 2147483647 y -1 para secuencias ascendentes y descendentes, respectivamente.

start Habilita la secuencia para que comience en cualquier lugar. El valor de inicio por omisión es minvalue para secuencias ascendentes y maxvalue para las descendentes.

cache

Permite que los números de la secuencia sean alojados (preallocated) y almacenados en memoria para un acceso mas rápido. El valor mínimo es 1 (solo se puede generar un valor cada vez, i.e. sin cache) y es también el valor por omisión.

CYCLE

Permite a la secuencia continuar cuando el valor de maxvalue o el de minvalue ha sido alcanzado por una secuencia ascendente o descendente respectivamente. Si el límite es alcanzado, el siguiente numero generado será cualquiera que para minvalue o maxvalue sea tomado como apropiado


CREATE SEQUENCE Mensaje devuelto si la orden se completa satisfactoriamente.

ERROR: user ‘username’ is not allowed to create/drop sequences. Ha de tener el privilegio especial CREATE en la Base de Datos.

Uso: Crea una secuencia ascendente llamada serial, comenzando en 101:

1. CREATE SEQUENCE serial START 101;

Seleccione el siguiente número de esta secuencia

2. SELECT NEXTVAL ('serial'); 3. nextval 4. -----

82

5. 101

Utilice esta secuencia en una INSERT:

6. ALTER TABLE testtable ADD id int; 7. INSERT INTO testtable VALUES ('BUEN', 'DIA',NEXTVAL('serial')); 8. SELECT * FOM testtable;

En nuestro caso, podemos obviar el paso 3, si definimos el tipo de datos de secuencia 'SERIAL', en los campos establecidos en la creación de tablas en el paso 4, deberían las líneas en cuestión ser de la siguiente forma:

9. IDContacto int default nextval('Persona.seq_idcontacto'),

Cambiar por:

10. IDContacto SERIAL,

También

11. IDModalidad int default nextval('Admision.seq_idmodalidad'),

Cambiar por:

12. IDModalidad SERIAL,

Con estos cambios, no será necesario realizar el paso 3, pero en realidad postgres si realiza implícitamente la creación de secuencias. Para el propósito de la práctica:

13. CREATE SEQUENCE Persona.seq_idcontacto START 1; --empieza desde 1 14. CREATE SEQUENCE Admision.seq_idmodalidad START 1; --empieza desde 1

--4) CREACIÓN DE TABLAS (TABLES):

CREATE [ TEMPORARY | TEMP ] TABLE table ( column type [ NULL | NOT NULL ] [ UNIQUE ] [ DEFAULT value ] [column_constraint_clause | PRIMARY KEY } [ ... ] ] [, ... ] [, PRIMARY KEY ( column [, ...] ) ] [, CHECK ( condition ) ] [, table_constraint_clause ] ) [ INHERITS ( inherited_table [, ...] ) ]

83

CREATE TABLE introducirá una nueva clase o tabla en la base de datos actual. El propietario de la tabla será del usuario que ejecuta el comando. Cada type puede ser un tipo simple, un tipo complejo (set) o un tipo array. Cada atributo puede ser especificado para ser no nulo, y puede tener un valor por defecto, especificado por la Cláusula DEFAULT. Una tabla no puede tener más de 1600 campos (realmente, esto viene limitado por el hecho que el máximo tamaño de una tupla debe ser menor que 8192 bytes)


TEMP

Si se especifica, la tabla se crea solo para esta sesión y es eliminada al salir. Si existen tablas con el mismo nombre, no son visibles mientras exista la tabla temporal

table Nombre de la clase o tabla.

column El tipo del campo. Puede incluir especificadores de array..

DEFAULT value

Valor por defecto para el campo: • un literal • una función de usuario • CURRENT_USER • CURRENT_DATE • CURRENT_TIME • CURRENT_TIMESTAMP

table_constraint_clause

La cláusula opcional CONSTRAINT especifica una lista de restricciones de integridad que las nuevas inserciones o las actualizaciones deberán satisfacer para que una sentencia insert o update tenga éxito. Cada restricción debe ser evaluada a una expresión booleana. Se pueden referenciar múltiples campos con una única restricción. Sólo se puede definir una única cláusula PRIMARY KEY por tabla; PRIMARY KEY column(una restricción de tabla) and PRIMARY KEY (una restricción de campo) son mutuamente excluyentes.

INHERITS inherited_table

La cláusula opcional INHERITS especifica una colección de nombres de tabla de las cuales esta tabla hereda todos los campos. Si algún campo heredado aparece más de una vez, Postgres informa de un error. Postgres permite automáticamente a la tabla creada heredar funciones de las tablas superiores a ella en la jerarquía de herencia.

84

Salidas más usuales CREATE TABLE

Mensaje devuelto si la orden se completa satisfactoriamente. ERROR:

Mensaje devuelto si la creación de la tabla falla. Este mensaje viene normalmente acompañado por algún texto explicativo, como:

ERROR: user ‘username’ is not allowed to create/drop tables. Ha de tener el privilegio especial CREATE en la Base de Datos.

ERROR: DEFAULT: type mismatched Si el tipo de datos o el valor por defecto no corresponde al tipo de datos de la definición del campo.

Para nuestro caso práctico:: Con “FechaCreacion date not null default now(),” asignaremos la fecha actual por defecto al campo FechaCreacion por medio de la función now().

1. create table Persona.Contacto 2. ( 3. IDContacto int default nextval('Persona.seq_idcontacto'), 4. Nombres varchar(30) not null, 5. Paterno varchar(30) not null, 6. Materno varchar(30) not null, 7. Genero char(1) default('0') not null, 8. DNI varchar(10) null, 9. FechaNac date null, 10. FechaCreacion date not null default now() 11. );

12. create table Persona.Postulante 13. ( 14. IDPostulante char(10) not null , 15. IDContacto int not null, 16. IDCarrera char(3) not null, 17. IDPerAcad char(6) not null, 18. IDModalidad int not null, 19. Puntaje int not null default(0), 20. AsistioExamen char(1) not null default('0'), 21. Ingreso char(1) not null default('0') 22. );

23. Create Table Admision.PerAcad 24. ( 25. IDPerAcad char(6) not null , 26. Periodo char(4), 27. Ano char(1) 28. );

29. create table Admision.Carrera 30. ( 31. IDCarrera char(3) not null, 32. Nombre varchar(150) not null 33. );

85

34. create table Admision.Modalidad 35. ( 36. IDModalidad int default nextval('Admision.seq_idmodalidad'), 37. Modalidad varchar(100) not null 38. );

--5) CREACIÓN DE RESTRICCIONES (CHECK):

[ CONSTRAINT name ] CHECK ( condition [, ...] ) La restricción CHECK especifica una restricción sobre los valores permitidos en un Campo y solamente pueden referirse a un campo.

Parámetros de Entrada name Nombre de la Restricción.

condition Cualquier expresión condicional válida que se evalué a un resultado booleano.


ALTER TABLE Mensaje devuelto si la orden se completa satisfactoriamente.

ERROR: Mensaje devuelto si la creación de la restricción falla. Este mensaje viene normalmente acompañado por algún texto explicativo, como:

ERROR: user ‘username’ is not allowed to create/drop constraints. Ha de tener el privilegio especial CREATE en la Base de Datos.

ERROR: the constraint check is violated for some row. Si algunas filas que existen violan la regla que se impone.

Para nuestro caso práctico: Los valores dentro de CHECK, por ejemplo “CHECK (Genero in (‘0′,’1′))”, son los únicos que serán permitidos insertar en el campo establecido, en este caso se le asigna esa restricción al campo Genero.

1. ALTER TABLE Persona.Contacto 2. ADD CONSTRAINT ck_Genero 3. CHECK (Genero in ('0','1'));

4. ALTER TABLE Persona.Postulante 5. ADD CONSTRAINT ck_asistioexamen 6. CHECK (asistioexamen in ('0','1'));

7. ALTER TABLE Persona.Postulante 8. ADD CONSTRAINT ck_ingreso 9. CHECK (ingreso in ('0','1'));

86

10. ALTER TABLE Persona.Postulante 11. add CONSTRAINT ck_puntaje 12. CHECK ( 13. (asistioexamen = '1' and puntaje >=0) 14. or 15. (asistioexamen = '0' and puntaje =0) 16. );

--6) CREACIÓN DE RESTRICCIONES (UNIQUE):

[ CONSTRAINT name ] UNIQUE ( column [, ...] ) La restricción UNIQUE especifica una regla que obliga a un grupo de uno o más campos de una tabla a contener valores únicos. Las definiciones de campo de las columnas especificadas no tienen porqué incluir una restricción NOT NULL para ser incluidos en una restricción UNIQUE. Tener más de un valor nulo en un campo sin la restricción NOT NULL, no viola la restricción UNIQUE. Cada restricción de campo UNIQUE debe nombrar un campo que es distinto del conjunto de campos nombrados por cualquier otra restricción UNIQUE o PRIMARY KEY definidas por la tabla. Nota: Postgres crea automáticamente un índice único por cada restricción UNIQUE, para asegurar la integridad de los datos.

Parámetros de Entrada name Nombre de la Restricción. ( column [, ...] ) Columnas a las que se aplica la restricción.





ERROR: the relationship already exists. Si ya existe la restricción.

Las restricciones UNIQUE, por ejemplo “UNIQUE(Modalidad)” , aseguran que en los datos en la misma columna, en este caso en el campo Modalidad, no sean repetidas, ya que no puede haber registradas 2 modalidades con el mismo nombre. Otro ejemplo se da en las tablas usuario, donde no permiten asignar el mismo login o nick a más de una persona.

87

1. ALTER TABLE Admision.Carrera 2. ADD CONSTRAINT uq_nombrecarrera 3. UNIQUE(Nombre);

4. ALTER TABLE Persona.Contacto 5. ADD CONSTRAINT uq_ContactoDNI 6. UNIQUE(DNI);

7. ALTER TABLE Admision.Modalidad 8. ADD CONSTRAINT uq_Modalidad 9. UNIQUE(Modalidad);

--7) CREACIÓN DE CLAVES PRIMARIAS (PRIMARY KEYS):

[ CONSTRAINT name ] PRIMARY KEY ( column [, ...] ) Las claves primarias, aparte de prevenir la duplicidad de datos, evitar los nulo y servir como nexo para relacionarse con otras tablas, tiene otro fin también importante, que es agilizar el proceso de búsqueda, no por el hecho de ser primary key, sino que al momento de la creación de una clave primaria se crea implícitamente un índice (index). Sólo se puede especificar una única clave primaria (PRIMARY KEY) por tabla.

Parámetros de Entrada name Nombre de la Restricción. ( column [, ...] ) Columnas a las que se aplica la restricción.

Salidas más usuales ALTER TABLE


Mensaje devuelto si la creación de la restricción falla. Este mensaje viene normalmente acompañado por algún texto explicativo, como:



Para nuestro caso:

1. ALTER TABLE Admision.PerAcad 2. ADD CONSTRAINT pk_IDPerAcad 3. PRIMARY KEY(IDPerAcad);

88

4. ALTER TABLE Admision.Carrera 5. ADD CONSTRAINT pk_IDCarrera 6. PRIMARY KEY(IDCarrera);

7. ALTER TABLE Persona.Contacto 8. ADD CONSTRAINT pk_IDContacto 9. PRIMARY KEY(IDContacto);

10. ALTER TABLE Persona.Postulante 11. ADD CONSTRAINT pk_IDPostulante 12. PRIMARY KEY(IDPostulante);

13. ALTER TABLE Admision.Modalidad 14. ADD CONSTRAINT pk_IDModalidad 15. PRIMARY KEY(IDModalidad);

--8) CREACIÓN DE CLAVES FORÁNEAS (FOREIGN KEYS):

CONSTRAINT fkey_name FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON UPDATE action ]

Una restricción de clave foránea especifica que valor en una columna (o grupo de columnas) debe coincidir con los valores en alguna de las filas de otra tabla. Mantiene la integridad referencial entre dos tablas. Las claves foráneas son campos que servirán de nexo para la relación entre 2 tablas, la clave primaria de otra tabla se relacionará con la clave foránea de ésta.

Parámetros de Entrada fkey_name Nombre de la Restricción. column_name Nombre de la columna afectada por la restricción.

reftable Tabla de referencia desde la cual se aplica se aplica la restricción.

refcolumn Si se omite, se usa la primary key de reftable MATCH FULL No permite columnas null . MATCH PARTIAL No implementada.

MATCH SIMPLE Permite columnas null. Opción predeterminada.

action

NO ACTION ó RESTRICT: Produce un error que indica que DELETE o UPDATE crearía una violación de restricción. CASCADE: Actualiza el valor de la columna referenciada. SET NULL: Ajusta la columna(s) de referenciad a null. SET DEFAULT: Ajuste la columna(s) de referencia a sus valores predeterminados.

89






1. ALTER TABLE Persona.Postulante 2. ADD CONSTRAINT fk_IDCarrera 3. FOREIGN KEY(idcarrera) 4. references Admision.Carrera(IDCarrera);

5. ALTER TABLE Persona.Postulante 6. ADD CONSTRAINT fk_IDPerAcad 7. FOREIGN KEY(IDPerAcad) 8. references Admision.PerAcad(IDPerAcad);

9. ALTER TABLE Persona.Postulante 10. ADD CONSTRAINT fk_IDModalidad 11. FOREIGN KEY(IDModalidad) 12. references Admision.Modalidad(IDModalidad);

13. ALTER TABLE Persona.Postulante 14. ADD CONSTRAINT fk_IDContacto 15. FOREIGN KEY(IDContacto) 16. references Persona.Contacto(IDContacto);;

El Lenguaje de Definición de Datos (Data Definition Language -DDL) sirve para modificar la estructura de los objetos en una base de datos y permite al administrador de la base especificar los elementos de datos que la integran, su estructura y las relaciones que existen entre ellos, las reglas de integridad, los controles a efectuar antes de autorizar el acceso a la base. En esta parte veremos cómo realizar la eliminación de los objetos de la base de datos creada anteriormente, pero con fines prácticos lo haremos paso a paso, ya que la eliminación podría ser tan sencilla como eliminar la base de datos directamente, o los componentes que lo componen en cascada si fuese necesario. ELIMINACIÓN DE RESTRICCIONES

ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ] DROP CONSTRAINT [ IF EXISTS ] constraint_name [ RESTRICT | CASCADE ]

90

Se utiliza para eliminar las restricciones de la tabla y de sus descendientes.


name Nombre de la Tabla.

constraint_name Nombre de la Restricción.

IF EXIST Si se incluye y el objeto no existe, no se produce error.

ONLY Altera solo esta tabla. Si no aparece, se altera la tabla y todas sus descendientes (si las hay).

* Explícitamente indica que se incluyen las tablas descendientes.

RESTRICT Predeterminado - No elimina la restricción si tiene objetos dependientes.

CASCADE Elimina la restricción y los objetos que dependen de ella. --1) ELIMINACIÓN DE CLAVES FORÁNEAS (FOREIGN KEYS):

1. ALTER TABLE Persona.Postulante 2. DROP CONSTRAINT fk_IDCarrera;

3. ALTER TABLE Persona.Postulante 4. DROP CONSTRAINT fk_IDPerAcad;

5. ALTER TABLE Persona.Postulante 6. DROP CONSTRAINT fk_IDModalidad;

7. ALTER TABLE Persona.Postulante 8. DROP CONSTRAINT fk_IDContacto;

--2) ELIMINACIÓN DE CLAVES PRIMARIAS (PRIMARY KEYS) :

1. ALTER TABLE Admision.PerAcad 2. DROP CONSTRAINT pk_IDPerAcad;

3. ALTER TABLE Admision.Carrera 4. DROP CONSTRAINT pk_IDCarrera;

5. ALTER TABLE Persona.Contacto 6. DROP CONSTRAINT pk_IDContacto;

7. ALTER TABLE Persona.Postulante 8. DROP CONSTRAINT pk_IDPostulante;

9. ALTER TABLE Admision.Modalidad 10. DROP CONSTRAINT pk_IDModalidad;

91

--3) ELIMINACIÓN DE RESTRICCIONES (UNIQUE):

1. ALTER TABLE Admision.Carrera 2. DROP CONSTRAINT uq_nombrecarrera;

3. ALTER TABLE Persona.Contacto 4. DROP CONSTRAINT uq_ContactoDNI;

5. ALTER TABLE Admision.Modalidad 6. DROP CONSTRAINT uq_Modalidad;

--4) ELIMINACIÓN DE RESTRICCIONES (CHECK):

1. ALTER TABLE Persona.Contacto 2. DROP CONSTRAINT ck_Genero;

3. ALTER TABLE Persona.Postulante 4. DROP CONSTRAINT ck_asistioexamen;

5. ALTER TABLE Persona.Postulante 6. DROP CONSTRAINT ck_ingreso;

7. ALTER TABLE Persona.Postulante 8. DROP CONSTRAINT ck_puntaje;

--5) ELIMINACIÓN DE COLUMNAS (COLUMNS):

ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ] DROP [ COLUMN ] [ IF EXISTS ] column_name [ RESTRICT | CASCADE ]

Se utiliza para eliminar las columnas de la tabla y de sus descendientes.


name Nombre de la Tabla.

column_name Nombre de la Columna.

IF EXIST Si se incluye y el objeto no existe, no se produce ningún error.

ONLY Altera solo esta tabla. Si no aparece, se altera la tabla y todas sus descendientes (si las hay).

* Explicitamente indica que se incluyen las tablas descendientes.


CASCADE Elimina la restricción y todos los objetos que dependen de ella.

92

1. ALTER TABLE Admision.Carrera 2. DROP COLUMN IDCarrera, 3. DROP COLUMN Nombre;

--6) ELIMINACIÓN DE TABLAS (TABLES):

DROP TABLE [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ] DROP TABLE elimina tablas de una base de datos. Solo su propietario (owner), el propietario del esquema o el superusuario pueden destruir una tabla o vista. En una tabla se pueden eliminar sus filas sin destruirla, usando DELETE o TRUNCATE. DROP TABLE siempre remueve algunos índices, las reglas, los disparadores, y las restricciones que existan para la tabla. Sin embargo para eliminar una tabla que es referenciada por una vista o un índice secundario (foreign-key) de otra tabla, estas deben ser removidas primero o especificar la clausula CASCADE. CASCADE removerá la vista dependiente enteramente, pero en el caso del índice secundario (foreign-key) únicamente removerá la restricción pero no afecta el contenido de la tabla subyacente.


name Nombre de la Tabla (opcionalmente esquema-tabla).



CASCADE Elimina la restricción y todos los objetos que dependen de la tabla.


DROPTABLE Mensaje devuelto si la orden se completa satisfactoriamente.



1. DROP TABLE Admision.Carrera; 2. DROP table Persona.Contacto; 3. DROP table Persona.Postulante; 4. DROP Table Admision.PerAcad; 5. DROP TABLE Admision.Modalidad;

93

--7) ELIMINACIÓN DE SECUENCIAS (SEQUENCE):

DROP SEQUENCE [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ] DROP SEQUENCE elimina una secuencia generadora de números de la base de datos. Una secuencia únicamente puede ser removida por su propio usuario o por el superusuario.


name Nombre de la Secuencia (opcionalmente esquema-secuencia).


RESTRICT Predeterminado - No elimina la secuencia si tiene objetos dependientes.

CASCADE Elimina la restricción y todos los objetos que dependen de la tabla.

1. DROP SEQUENCE Persona.seq_idcontacto; 2. DROP SEQUENCE Admision.seq_idmodalidad;

--8) ELIMINACIÓN DE ESQUEMAS (SCHEMAS):

DROP SCHEMA [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ] DROP SCHEMA remueve esquemas de la Base de Datos. Un esquema puede ser eliminado únicamente por su propio usuario o por el superusuario. Tenga en cuenta que el propietario puede eliminar el esquema (y por lo tanto todos los objetos que contiene), incluso si él no es el propietario de algunos de los objetos dentro del esquema.


name Nombre del Esquema.


RESTRICT Predeterminado - No elimina la esquema si tiene objetos dependientes.

CASCADE Elimina la restricción y todos los objetos que dependen del esquema.

1. DROP SCHEMA Persona; 2. DROP SCHEMA Admision;

94

--9) ELIMINACIÓN DE BASE DE DATOS (DATABASE): DROP DATABASE [ IF EXISTS ] name DROP DATABASE elimina las entradas de catálogo de una base de datos existente y borra el directorio que contiene los datos. Solamente puede ser ejecutado por el propietario de la base de datos (normalmente quien la creó). Esta orden no puede ser ejecutada mientras se está conectado a la base de datos objetivo. Conéctese otra base de datos para ejecutar el comando. Por lo tanto, puede ser más conveniente usar el shell script dropdb, que emplea este comando.


name Nombre del Esquema.


1. DROP DATABASE DBAdmision;

Como explicaba en un inicio, podríamos utilizar métodos más directos si queremos eliminar un objeto, por ejemplo: ELIMINACION DE OBJETOS: --1) Eliminar la base de datos con todos sus objeto s:

1. DROP DATABASE DBAdmision; --en modo desconectados

--2) Eliminar una secuencia que está siendo utiliza da por una tabla:

1. DROP SEQUENCE Persona.seq_idcontacto CASCADE; 2. DROP SEQUENCE Admision.seq_idmodalidad CASCADE;

--3) Eliminar una tabla con todos los constraints q ue posea:

1. DROP TABLE Admision.Carrera CASCADE; 2. DROP Table Admision.PerAcad CASCADE; 3. DROP TABLE Admision.Modalidad CASCADE; 4. DROP table Persona.Contacto CASCADE; 5. DROP table Persona.Postulante CASCADE;

--4) Eliminar un esquema con todos sus objetos

1. DROP SCHEMA Persona CASCADE; 2. DROP SCHEMA Admision CASCADE;

95

TRUNCATE

TRUNCATE [ TABLA ] name TRUNCATE remueve rápidamente todas las filas de una tabla. Tiene el mismo efecto que el DELETE pero al no recorrer la tabla resulta más rápido. Es más efectivo en tablas grandes.

Parámetros de Entrada name Nombre de la tabla a truncar.


TRUNCATE Mensaje devuelto si la orden se completa satisfactoriamente.

ERROR: Mensaje devuelto si la orden TRUNCATE falla. Este mensaje viene normalmente acompañado por algún texto explicativo, como:

ERROR: user ‘username’ is not allowed to truncate constraints. Ha de tener el privilegio especial CREATE en la Base de Datos..

Uso:

Truncar la tabla tablagrande:

1. TRUNCATE TABLE tablagrande;

96

Sentencias DML El lenguaje de Manipulación de Datos (Data Manipulation Language – DML) sirve para llevar a cabo las transacciones en las base de datos, entiéndase por transacciones los procesos de inserción, actualización, eliminación, selección. Es utilizado para escribir programas que crean, actualizan y extraen información de las bases de datos. Siempre de acuerdo con las especificaciones y las normas de seguridad dictadas por al administrador. Un lenguaje de manipulación de datos es un lenguaje que permite a los usuarios acceder o manipular los datos organizados mediante el modelo de datos apropiado. Hay dos tipos básicamente:

• DMLs procedimentales. Requieren que el usuario especifique qué datos se necesitan y cómo obtener esos datos.

• DMLs declarativos (o no procedimentales). Requiere que el usuario especifique qué datos se necesitan sin especificar cómo obtener esos datos.

Una consulta es una instrucción de solicitud para recuperar información. La parte de un DML se llama lenguaje de consultas. Ejemplo:

1. Select nombre, direccion 2. from cliente 3. where id_cliente = 2;

La secuencia conceptual de operaciones que ocurren para acceder cierta información que contiene una base de datos es la siguiente:

• El usuario solicita cierta información contenida en la base de datos. • El DBMS intercepta este requerimiento y lo interpreta. • El DBMS realiza las operaciones necesarias para acceder y/o

actualizar la información solicitada. Podemos clasificar a estas sentencias como: INSERT, UPDATE, DELETE, SELECT. Usaremos la base de datos DBAdmision estructurado en el post de Sentencias DDL para realizar las operaciones antes mencionadas.

97

En este ejemplo insertaremos, actualizaremos y eliminaremos datos de Admision.Modalidad, de la base de datos generada en la parte de Sentencias DDL. --1) LISTAR DATOS : SELECT SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]

expression [ AS name ] [, ...] [ INTO [ TEMPORARY | TEMP ] [ TABLE ] new_table ] [ FROM table [ alias ] [, ...] ] [ WHERE condition ] [ GROUP BY column [, ...] ] [ HAVING condition [, ...] ] [ { UNION [ ALL ] | INTERSECT | EXCEPT } select ] [ ORDER BY column [ ASC | DESC | USING operator ] [, ...] ] [ FOR UPDATE [ OF class_name [, ...] ] ] LIMIT { count | ALL } [ { OFFSET | , } start ]

Parámetros de Entrada expression El nombre de una columna de la tabla o una expresión..

name

Especifica otro nombre para una columna o una expresión que utilice la cláusula AS. Este nombre se utiliza principalmente como etiqueta para la columna de salida. El nombre no puede ser utilizado en las cláusulas WHERE, GROUP BY o HAVING. Sin embargo, puede ser referenciado en cláusulas ORDER BY

TEMPORARY TEMP

La tabla se crea solamente para esta sesión, y es automáticamente descartada al finalizar la misma.

new_table

Si se utiliza la cláusula INTO TABLE, el resultado de la consulta se almacenará en otra tabla con el nombre indicado. La tabla objetivo (new_table) será creada automáticamente y no deberá existir previamente a la utilización de este comando. Consulte el comando SELECT INTO para más información. Nota: La declaración CREATE TABLE AS también creará una nueva tabla a partir de la consulta.

table Un nombre alternativo para la tabla precedente table. Se utiliza para abreviar o eliminar ambigüedades en uniones dentro de una misma tabla.

condition Una expresión booleana que da como resultado verdadero o falso (true or false). Consulte la cláusula WHERE.

column El nombre de una columna de la tabla.

select Una declaración de selección (select) exceptuando la cláusula ORDER BY.

98

Salidas más usuales Registros

El conjunto completo de registros (filas) que resultan de la especificación de la consulta..

count: La cantidad de registros (filas) devueltos por la consulta.

SELECT devuelve registros de una o más tablas. Los candidatos a ser seleccionados son aquellos registros que cumplen la condición especificada con WHERE; si se omite WHERE, se retornan todos los registros. (Consulte Cláusula WHERE.) DISTINCT elimina registros duplicados del resultado. ALL (predeterminado) devolverá todos los registros, que cumplan con la consulta, incluyendo los duplicados. DISTINCT ON elimina los registros que cumplen con todas las expresiones especificadas, manteniendo solamente el primer registro de cada conjunto de duplicados. Note que no se puede predecir cuál será "el primer registro" a menos que se utilice ORDER BY para asegurar que el registro eseado es el que efectivamente aparece primero. Por ejemplo:

1. SELECT DISTINCT ON (location) location, time, report 2. FROM weatherReports 3. ORDER BY location, time DESC;

recupera el reporte de tiempo (weather report) más reciente para cada locación (location). Pero si no se hubiera utilizado ORDER BY para forzar el orden descendente de los valores de fecha para cada locación, se hubiesen recuperado reportes de una fecha impredecible para cada locación. La cláusula GROUP BY permite al usuario dividir una tabla conceptualmente en grupos. (Consulte Cláusula GROUP BY.) La cláusula HAVING especifica una tabla con grupos derivada de la eliminación de grupos del resultado de la cláusula previamente especificada. (Consulte Cláusula HAVING.) La cláusula ORDER BY permite al usuario especificar si quiere los registros ordenados de manera ascendente o descendente utilizando los operadores de modo ASC y DESC. (Consulte Cláusula ORDER BY.) El operador UNION permite permite que el resultado sea una colección de registros devueltos por las consultas involucradas. (Consulte Cláusula UNION.) El operador INTERSECT le da los registros comunes a ambas consultas. (Consulte Cláusula INTERSECT.)

99

El operador EXCEPT le da los registros devueltos por la primera consulta que no se encuentran en la segunda consulta. (Consulte Cláusula EXCEPT.) La cláusula FOR UPDATE permite a SELECT realizar un bloqueo exclusivo de los registros seleccionados. La cláusula LIMIT permite devolver al usuario un subconjunto de los registros producidos por la consulta. (Consulte Cláusula LIMIT.) Usted debe tener permiso de realizar SELECT sobre una tabla para poder leer sus valores. (Consulte las declaraciones GRANT/REVOKE). Cláusula WHERE La condición opcional WHERE tiene la forma general: WHERE boolean_expr boolean_expr puede consistir de cualquier expresión cuyo resultado sea un valor booleano. En muchos casos, esta expresión será:

expr cond_op expr o log_op expr

donde cond_op puede ser uno de: =, <, <=, >, >= or <>, un operador condicional como ALL, ANY, IN, LIKE o operador definido localmente, y log_op puede ser uno de: AND, OR, NOT. La comparación devuelve TRUE (verdadero) o FALSE (falso) y todas las instancias serán descartadas si la expresión resulta falsa. Cláusula GROUP BY GROUP BY especifica una tabla con grupos derivada de la aplicación de esta cláusula: GROUP BY column [, ...] GROUP BY condensará en una sola fila todos aquellos registros que compartan los mismos valores para las columnas agrupadas. Las funciones de agregación, si las hubiera, son computadas a través de todas las filas que conforman cada grupo, produciendo un valor separado por cada uno de los grupos (mientras que sin GROUP BY, una función de agregación produce un solo valor computado a través de todas las filas seleccionadas). Cuando GROUP BY está presente, no es válido hacer referencia a columnas no agrupadas excepto dentro de funciones de

100

agregación, ya que habría más de un posible valor de retorno para una columna no agrupada. Cláusula HAVING La condición opcional HAVING tiene la forma general: HAVING cond_expr donde cond_expr cumple las mismas condiciones que las especificadas para WHERE. HAVING especifica una tabla con grupos derivada de la eliminación de grupos, del resultado de la cláusula previamente especificada, que no cumplen con cond_expr. Cada columna referenciada en cond_expr debe referirse precisamente (sin ambigüedades) a una columna de grupo, a menos que la referencia aparezca dentro de una función de agregación. Cláusula ORDER BY ORDER BY column [ ASC | DESC ] [, ...] column puede ser tanto el nombre de una columna como un número ordinal. Los números ordinales hacen referencia a la posición (de izquierda a derecha) de la columna. Esta característica hace posible definir un orden basado en una columna que no tiene un nombre adecuado. Esto nunca es absolutamente necesario ya que siempre es posible asignar un nombre a una columna calculada utilizando la cláusula AS, por ej.: SELECT title, date_prod + 1 AS newlen FROM films ORDER BY newlen; A partir de la versión 6.4 de PostgreSQL, es también posible ordenar, con ORDER BY, según expresiones arbitrarias, incluyendo campos que no aparecen en el resultado de SELECT. Por lo tanto, la siguiente declaración es legal: SELECT name FROM distributors ORDER BY code; Opcionalmente una puede agregar la palabra clave DESC (descendente) o ASC (ascendente) luego del nombre de cada columna en la cláusula ORDER BY. Si no se especifica, se asume ASC de forma predeterminada. Alternativamente, puede indicarse un nombre de operador de orden específico. ASC es equivalente a USING ’<’ y DESC es equivalente a USING ’>’. Cláusula UNION table_query UNION [ ALL ] table_query

[ ORDER BY column [ ASC | DESC ] [, ...] ]

101

donde table_query especifica cualquier declaración SELECT sin la cláusula ORDER BY. El operador UNION permite que el resultado sea una colección de registros devueltos por las consultas involucradas. Los dos SELECTs que representan los dos operandos directos de la UNION deben producir el mismo número de columnas, y las columnas correspondientes deben ser de tipos de datos compatibles. De forma predeterminada, el resultado de UNION no contiene registros duplicados a menos que se especifique la cláusula ALL. Si se utilizan varios operadores UNION en la misma declaración SELECT se evalúan de izquierda a derecha. Note que la palabra clave ALL no es global, siendo aplicada solamente al par de tablas de resultado actual. Cláusula EXCEPT table_query EXCEPT table_query [ ORDER BY column [ ASC | DESC ] [, ...] ] donde table_query especifica cualquier expresión SELECT sin la cláusula ORDER BY. El operador EXCEPT le da los registros devueltos por la primera consulta pero no por la segunda. Los dos SELECTs que representan los operandos directos de la intersección deben producir el mismo número de columnas, y las columnas correspondientes deben ser de tipos de datos compatibles. Si se utilizan varios operadores INTERSECT en la misma declaración SELECT se evalúan de izquierda a derecha, a menos que se utilicen paréntesis para modificar esto. Cláusula LIMIT LIMIT { count | ALL } [ { OFFSET | , } start ] OFFSET start donde count especifica el máximo número de registros a devolver y start especifica el número de registros a saltear antes de empezar a devolver registros. LIMIT le permite recuperar sólo una porción de los registros que se generan por el resto de la consulta. Si se especifica un número límite, no se devolverán más registros que esa cantidad. Si se da un valor de desplazamiento, esa cantidad de registros será salteada antes de comenzar a devolver registros. Cuando se utiliza LIMIT es una buena idea utilizar la cláusula ORDER BY para colocar los registros del resultado en un orden único. De otra forma obtendrá un subconjunto impredecible de los registros de la consulta — tal vez esté buscando

102

los registros del décimo al vigésimo, ¿pero del décimo al vigésimo en qué orden? Usted no conoce el orden a menos que utilice ORDER BY. Ya en Postgres 7.0, el optimizador de consultas toma en cuenta a LIMIT cuando genera un plan de consulta, así que es muy factible que usted obtenga diferentes planes (abarcando diferentes criterios de ordenamiento de registros) dependiendo de los valores dados a LIMIT y OFFSET. Por lo tanto, utilizar diferentes valores para LIMIT/OFFSET para seleccionar diferentes subconjuntos del resultado de una consulta, provocará resultados inconsistentes a menos que usted se asegura un resultado predecible ordenando con ORDER BY. Esto no es un bug; es una consecuencia inherente al hecho de que SQL no establece ningún compromiso de entregar los resultados de una consulta en un orden en particular a menos que se utilice ORDER BY para especificar un criterio de orden explícitamente. Uso Para unir la tabla films con la tabla distributors:

1. SELECT f.title, f.did, d.name, f.date_prod, f.kind 2. FROM distributors d, films f 3. WHERE f.did = d.did

Para sumar la columna len (duración) de todos los filmes y agrupar los resultados según la columna kind (tipo):

1. SELECT kind, SUM(len) AS total FROM films GROUP BY kind;

Para sumar la columna len de todos los filmes, agrupar los resultados según la columna kind y mostrar los totales de esos grupos que sean menores a 5 horas:

1. SELECT kind, SUM(len) AS total 2. FROM films 3. GROUP BY kind 4. HAVING SUM(len) < INTERVAL '5 hour';

Los siguientes dos ejemplos muestran maneras idénticas de ordenar los resultados individuales de acuerdo con los contenidos de la segunda columna (name):

1. SELECT * FROM distributors ORDER BY name; 2. SELECT * FROM distributors ORDER BY 2;

Este ejemplo muestra cómo obtener la unión de las tablas distributors y actors, restringiendo los resultados a aquellos que comienzan con la letra W en cada tabla. No se quieren duplicados, así que la palabra clave ALL se omite.

1. - distributors: actors: 2. - did| name id |name 3. - ---+--------------+---------------

103

4. - 108|Westward 1 |Woody Allen 5. - 111|Walt Disney 2 |Warren Beatty 6. - 112|Warner Bros. 3|Walter Matthau 7. - ... ...

Muestra el contenido de Admision.Modalidad:

1. SELECT IDModalidad, Modalidad FROM Admision.Modalidad;

--2) INSERTAR DATOS: INSERT

INSERT INTO table_name [ ( column_name [, ...] ) ] { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query } [RETURNING * | output_expression [ [AS] output_name ] [, ..] ]

INSERT permite la inserción de nuevas filas en una clase o una tabla. Se puede insertar una fila a la vez o varias como el resultado de una consulta. Las columnas en el resultado pueden ser listadas en cualquier orden. Cada columna que no esté presente en la lista de origen será insertada usando el valor por defecto, que puede ser tanto un valor por defecto declarado DEFAULT o bien NULL. Postgres rechazará la nueva columna si se inserta un NULL en una columna declarada como NOT NULL. Si la expresión para cada columna no es del tipo de datos correcto, se intentará una corrección de tipos automáticamente. Debe tener privilegios de inserción en la tabla para añadir en ella, así como privilegios de selección en cualquier tabla especificadas en una clausula WHERE. La cláusula INSERT se usa para calcular y devolver el valor (s) en función de cada fila insertada en realidad. Esto es principalmente útil para la obtención de los valores que fueron suministrados por defecto, como por ejemplo un número de secuencia en serie. Sin embargo, se permite cualquier expresión mediante columnas de la tabla. La sintaxis de la lista RETURNING es idéntica a la de la lista de salida de SELECT. Debe tener el privilegio INSERT en una tabla con el fin de insertar en ella. Si se especifica una lista de columnas, sólo tiene privilegio INSERT en las columnas de la lista. El uso de la cláusula RETURNING requiere permiso SELECT en todas las columnas mencionadas en RETURNING. Si utiliza la cláusula de consulta para insertar filas de una consulta, por supuesto, necesita tener privilegio SELECT todas las tablas o columna que se utiliza en la consulta.


table_name El nombre de una la tabla.

104

column_name El nombre de una columna en table_name.

expression Una expresión o un valor válidos a asignar en column_name.

query Una consulta válida. Vea la instrucción SELECT para una mejor descripción de argumentos válidos.

DEFAULT VALUES

Todas las columnas serán llenadas con sus valores predeterminados.

output_expression

Una expresión que se calcula y devuelve el comando INSERT después de cada fila. La expresión puede usar los nombres de las columnas de la tabla nombrada por table_name. Escriba * para devolver todas las columnas de la fila insertada (s).

output_name Un nombre a usar para una columna retornada.

Salidas más usuales INSERT oid 1

Si solo se ha insertado una fila. oid es el número OID de la fila insertada. INSERT 0 #

Si se ha insertado más de una fila. # es el número de filas insertadas. Si el comando INSERT tiene RETURNING el resultado será similar a la sentencia SELECT que contenga las columnas y valores definidos en la lista RETURNING, calculadas sobre la fila (s) que se inserta por el comando.

Uso Inserta una fila en la tabla films:

1. INSERT INTO films VALUES 2. ('UA502','Bananas',105,'1971-07-13','Comedy',INTERVAL '82 minute');

En este segundo ejemplo la columna date_prod se omite y entonces tendrá el valor por defecto de NULL:

1. INSERT INTO films (code, title, did, date_prod, kind) 2. VALUES ('T_601','Yojimbo',106,DATE '1961-06-16','Drama');

Inserta varias filas en la tabla films desde la tabla tmp:

1. INSERT INTO films SELECT * FROM tmp;

INSERTAR DATOS EN Admision.Modalidad

1. INSERT INTO Admision.Modalidad(Modalidad)VALUES('Exámen Ordinario'); 2. --El campo IDModalidad es secuencial 3. SELECT * FROM Admision.Modalidad; --Comprobar Inserción

105

--3) ACTUALIZAR DATOS: UPDATE

UPDATE [ ONLY ] table_name [ * ] [ [ AS ] alias ] SET { column_name = { expression | DEFAULT } | ( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] ) } [, ...] [ FROM from_list ] [ WHERE condition] [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

UPDATE cambia el valor de la columnas especificadas por todas las filas que satisfacen la condición dada. Solamente necesita indicar las columnas que serán modificadas. Para referencias a listas se usa la misma sintaxis de SELECT. O sea, puede substituir un único elemento de una lista, un rango de elementos o una lista completa con una única petición. Debe tener permiso de escribir en la tabla para poder modificarla, así como permiso de lectura de cualquier tabla cuyos valores sean mencionados en la condición WHERE. Si la expresión para cada columna no es del tipo de datos correcto, se intentará una corrección de tipos automáticamente. La cláusula UPDATE se usa para calcular y devolver el valor (s) en función de cada fila actualizada en realidad. Esto es principalmente útil para la obtención de los valores que fueron suministrados por defecto, como por ejemplo un número de secuencia en serie. Sin embargo, se permite cualquier expresión mediante columnas de la tabla. La sintaxis de la lista RETURNING es idéntica a la de la lista de salida de SELECT. Debe tener el privilegio UPDATE en una tabla con el fin de insertar en ella. Si se especifica una lista de columnas, sólo tiene privilegio UPDATE en las columnas de la lista. El uso de la cláusula RETURNING requiere permiso SELECT en todas las columnas mencionadas en RETURNING. Si utiliza la cláusula de consulta para insertar filas de una consulta, por supuesto, necesita tener privilegio SELECT todas las tablas o columna que se utiliza en la consulta.



column_name El nombre de una columna en table_name.

expression Una expresión o un valor válidos a asignar en column_name.

from_lista Es una extensión no estándar de Postgres que permite la aparición de columnas de otras tablas en la condición WHERE.

106

DEFAULT La columnas será llenada con su valore predeterminado (sera NULL si no se especifica).

output_expression

Una expresión que se calcula y devuelve el comando IUPDATE después de cada fila actualizada. La expresión puede usar los nombres de las columnas de la tabla nombrada por table_name. Escriba * para devolver todas las columnas de la fila actualizadas (s).

output_name Un nombre a usar para una columna retornada.

Salidas más usuales UPDATE #

Mensaje obtenido si ha habido éxito. El símbolo # representa el número de filas que han sido actualizadas. Si # es igual a 0, ninguna fila fue actualizada.

Si el comando UPDATE tiene RETURNING el resultado será similar a la sentencia SELECT que contenga las columnas y valores definidos en la lista RETURNING, calculadas sobre la fila (s) que se inserta por el comando. Uso: Para cambiar la palabra "Drama" por "Dramatica" en la columna categoría:

1. UPDATE peliculas 355 2. SET categoria = 'Dramatica' 3. WHERE categoria = 'Drama '; 4. 5. SELECT * FROM peliculas WHERE categoria = 'Dramatico' OR categoria = 'Drama';

ACTUALIZAR DATOS EN Admision.Modalidad:

1. UPDATE Admision.Modalidad SET Modalidad='Ordinario' 2. WHERE IDModalidad='1'; 3. 4. SELECT * FROM Admision.Modalidad; --Comprobar Actualización

--4) ELIMINAR DATOS: DELETE

DELETE FROM [ ONLY ] table_name [ * ] [ [ AS ] alias ] [ USING using_list ] [ WHERE condition] [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

DELETE borra las filas que satisfacen la clausula WHERE de la tabla especificada. Si la condición (clausula WHERE) está ausente, el efecto es borrar todas las filas de la tabla. El resultado es una tabla valida, pero vacía.

107

Sugerencia: TRUNCATE es una extensión de Postgres el cual provee un mecanismo más rápido para borrar todas las filas de una tabla. Para modificar la tabla usted debe poseer acceso de escritura a la misma, así como acceso de lectura a cualquier tabla cuyos valores son leídos en la condición. La cláusula DELETE se usa para calcular y devolver el valor (s) en función de cada fila eliminada. Esto es principalmente útil para la obtención de los valores que fueron suministrados por defecto, como por ejemplo un número de secuencia en serie. Sin embargo, se permite cualquier expresión mediante columnas de la tabla. La sintaxis de la lista RETURNING es idéntica a la de la lista de salida de SELECT. Debe tener el privilegio DELETE en una tabla con el fin de eliminar en ella. Si se especifica una lista de columnas, El uso de la cláusula RETURNING requiere permiso SELECT en todas las columnas mencionadas en RETURNING. Si utiliza la cláusula de consulta para eliminar filas de una consulta, por supuesto, necesita tener privilegio SELECT todas las tablas o columna que se utiliza en la consulta.



alias Un nombre sustituto para la tabla.

using_list

Una lista de expresiones de tabla, permitiendo columnas de otras tablas que aparecer en la condición WHERE. Esto es similar a la lista de tablas que se pueden especificar en la cláusula FROM de una sentencia SELECT, por ejemplo, un alias para el nombre de la tabla se pueden especificar

condition Consulta SQL de selección la cual devuelve las filas a ser borradas.

output_expression

Una expresión que se calcula y devuelve el comando DELETE después de cada fila borrada. La expresión puede usar los nombres de las columnas de la tabla nombrada por table_name. Escriba * para devolver todas las columnas de la filas borradas (s).

output_name Nombre para la columna retornada.

Salidas más usuales DELETE count

Mensaje devuelto si los items son borrados exitosamente. El valor count es la cantidad de filas borradas. Si count es 0, ninguna fila fue borrada.

Si el comando DELETE tiene RETURNING el resultado será similar a la sentencia SELECT que contenga las columnas y valores definidos en la lista RETURNING, calculadas sobre la fila (s) que se inserta por el comando.

108

Uso: Borra todos los films excepto los musicales:

1. DELETE FROM films WHERE kind <> 'Musical'; 2. SELECT * FROM films;

Borra completamente la tabla films:

1. DELETE FROM films; 2. SELECT * FROM films;

ELIMINAR DATOS EN Admision.Modalidad:

1. DELETE FROM Admision.Modalidad 2. WHERE IDModalidad='1'; 3. 4. SELECT * FROM Admision.Modalidad; --Comprobar Eliminación

109

Sentencias TCL El Lenguaje de Control Transacciones (Transaction Control Language - TCL) se utiliza para administrar los procesos transaccionales en una base de datos en relación a los requerimientos de atomicidad, consistencia, aislamiento y durabilidad. Las cláusulas TCL que se utilizan en PostgreSQL para este fin son: COMMIT, SAVEPOINT, RELEASE SAVEPOINT, ROLLBACK.

COMMIT COMMIT [ WORK | TRANSACTION ] COMMIT realiza la transacción actual. Todos los cambios realizados por la transacción son visibles a las otras transacciones, y se garantiza que se conservan si se produce una caída de la máquina. Notas:

• Las palabras clave WORK y TRANSACTION son informativas, y pueden ser omitidas.

• Use ROLLBACK para abortar una transacción. Uso:

COMMIT; -- Para hacer todos los cambios permanentes.

SAVEPOINT SAVEPOINT savepoint SAVEPOINT establece un nuevo punto de salvaguarda dentro de la transacción actual. Un punto de salvaguarda es una marca especial dentro de una transacción que permite a todos los comandos que se ejecutan después revertirse, restaurando el estado de la transacción a lo que era en el momento que se estableció el punto de salvaguarda. Notas

• Utilice ROLLBACK TO SAVEPOINT para revertir a un punto de salvaguarda. • Utilice RELEASE SAVEPOINT para destruir un punto de salvaguarda.

110

• Los puntos de salvaguarda sólo se pueden establecer al interior de un bloque de transacciones. Puede haber varios puntos de salvaguarda definidos dentro de un bloque transacciones.

Ejemplo: Establecer un punto de salvaguarda y luego deshacer los efectos de todos los comandos ejecutados después de su creación. La transacción siguiente inserta los valores 1 y 3, pero no el 2:

BEGIN; INSERT INTO table1 VALUES (1); SAVEPOINT mi_savepoint; INSERT INTO table1 VALUES (2); ROLLBACK TO SAVEPOINT mi_savepoint; INSERT INTO table1 VALUES (3);

COMMIT;

RELEASE SAVEPOINT RELEASE [ SAVEPOINT ] savepoint RELEASE SAVEPOINT destruye todos los puntos de salvaguarda (savepoints) que se establecieron en el intervalo de creación y liberación, manteniendo el efecto de los comandos ejecutados en este intervalo.

La destrucción de un punto de salvaguarda hace que no aparezca como un punto de reversión y permite que el sistema recoja los recursos que le asigno antes de finalizar la transacción.

Notas:

• Usar el nombre de un punto de salvaguarda (savepoint) que no se ha creado genera un error.

• No es posible liberar un punto de retorno cuando la transacción se ha revertido. • Si varios puntos de salvaguarda tienen el mismo nombre, sólo el que se ha definido

más recientemente se libera. Ejemplo: Establecer y posteriormente destruir un punto de salvaguarda. La transacción siguiente inserta los valores 4 y 5:

111

BEGIN; INSERT INTO table1 VALUES (4); SAVEPOINT mi_savepoint; INSERT INTO table1 VALUES (5); RELEASE SAVEPOINT mi_savepoint;

COMMIT;

ROLLBACK ROLLBACK [ WORK | TRANSACTION ] [ TO SAVEPOINT savepoint ] ROLLBACK deshace la transacción actual y provoca que todas las modificaciones originadas por la misma sean descartadas. ROLLBACK TO SAVEPOINT Deshace todas las transacciones que se han ejecutado después del establecimiento del punto de salvaguarda (savepoint). El punto de retorno permanece y si se necesita, más adelante se puede revertir de nuevo. ROLLBACK TO SAVEPOINT destruye implícitamente a todos los puntos de salvaguarda que se establecieron después de que el punto utilizado. Notas:

• Utilice COMMIT para terminar una transacción de forma exitosa. • ABORT es un sinónimo de ROLLBACK. • Utilice RELEASE SAVEPOINT para destruir un punto de salvaguarda. • Cualquier cursor que se abre dentro de un punto de salvaguarda se cerrará

cuando se revierte. Si un cursor abierto previamente se ve afectado por una sentencia FETCH o MOVE dentro de un punto de salvaguarda que luego se revierte, el cursor permanece en la posición que FETCH dejó apuntando (es decir, el movimiento del cursor causado por FETCH no se deshace). La transacción puede ser restaurada usando ROLLBACK TO SAVEPOINT pero el cursor ya no se puede utilizar.

Uso:

ROLLBACK; -- Cancelar todos los cambios. ROLLBACK TO SAVEPOINT my_savepoint; -- Cancela los cambios posteriores a my_savepoint

112

CASO PRÁCTICO: Transacción para transferir 50 desde una cuenta A a una cuenta B: 1- CREACIÓN DE TABLA Y CHECK:

1. --DROP TABLE tblCuenta; 2. CREATE TABLE tblCuenta(NumCuenta char,Monto numeric(10,3)); 3. INSERT INTO tblCuenta VALUES('A',100); 4. INSERT INTO tblCuenta VALUES('B',100); 5. ALTER TABLE tblCuenta 6. ADD CONSTRAINT ck_monto CHECK (Monto >=0); --No Negativos

2- SECUENCIA DE EJECUCIÓN:

1. leer(A) 2. A = A - 50 3. escribir(A) 4. leer(B) 5. B = B + 50 6. escribir(B)

3- CREACIÓN DE LA FUNCIÓN:

1. CREATE OR REPLACE FUNCTION transaccion1() RETURNS VOID AS 2. $body$ 3. DECLARE 4. A numeric; 5. B numeric; 6. BEGIN 7. /*1*/ A := (SELECT monto from tblCuenta where NumCuenta = 'A'); 8. /*2*/ A := A - 50; 9. /*3*/ update tblCuenta set Monto = A where NumCuenta = 'A'; 10. /*4*/ B := (SELECT monto from tblCuenta where NumCuenta = 'B'); 11. /*5*/ B := B + 50; 12. /*6*/ update tblCuenta set Monto = B where NumCuenta = 'B'; 13. RAISE NOTICE 'Guardado'; 14. EXCEPTION 15. WHEN OTHERS THEN 16. ROLLBACK; 17. END; 18. $body$ 19. LANGUAGE 'plpgsql';

4- COMPROBANDO:

1. SELECT * FROM transaccion1(); 2. SELECT * FROM tblCuenta;

EL hecho de transferir dinero de una cuenta a otra requiere que toda la operación sea ejecutado como un todo, ‘HACE TODO O NADA’, de esa manera

113

garantizamos la integridad de la transacción, por ejemplo si se presenta un error en medio de la transferencia todo el procesos queda invalidado. Queda claro que Postgres realiza las transacciones implícitamente Es importante nombrar los 4 requisitos que deben de cumplirse en una transferencia: Requisito de atomicidad: Se realiza todo o nada Requisito de consistencia: A+B (INICIAL) = A+B (FIN AL) Requisito de aislamiento: Durante la operación se bloquea otros accesos. Requisito de durabilidad: Una vez actualizado debe quedar permanentemente.

114

Instalar Script

Instalar Script dbadmin_insert.sql desde pgAdminIII en Windows Si aún no tienes preparada la base de datos, utilizar el script dbadmin_insert.sql que es la unión de las sentencias DDL y DML creadas y poblada con datos. El script creará el entorno en el que se realizarán las demostraciones.

• Copiar el archivo dbadmin_insert.sql al directorio del usuario en Mis documentos.

• Ejecutar pgAdminIII.

• Crear la Base de Datos desde el ícono Database:

• Seleccionar el ícono dbadmision creado en Databases.

• Abrir las consultas SQL.

• Abrir el archivo dbadmin_insert.sql desde directorio del usuario Mis documentos.

115

• Ejecutar el Script

116

Instalar el Script dbadmin.sql desde psql en Linux Si aún no tienes preparada la base de datos, utilizar el script dbadmin.sql que es la unión de las sentencias DDL y DML creadas y poblada con datos. El script creará el entorno en el que se realizarán las demostraciones.

• Copiar el archivo dbadmin.sql, por facilidad, al directorio del usuario postgres /var/lib/pgsql/9.2

• Crear la Base de Datos:

1. [root@servidor ~]# su – postgres 2. -bash-4.1$ psql 8. (9.2.6, server 8.4.11) 3. Type "help" for help. 4. 5. postgres=# CREATE DATABASE dbadmision WITH TEMPLATE = template0 ENCODING = 'UTF8'; 6. CREATE DATABASE 7. postgres=#

• Asignar el propietario a la Base de Datos

8. postgres=# ALTER DATABASE dbadmision OWNER TO postgres; 9. ALTER DATABASE 10. postgres=# \q

• Ejecutar el script dbadmin.sql en psql desde El Shell del usuário postgres

11. -bash-4.1$ psql -U postgres -d dbadmision -f dbadmin.sql

117

Caso de Estudio - Continuación Después de los estudios realizados sobre las necesidades de la oficina de Admisión de la Universidad y una vez se ha construido la base de datos dbadmision con todos sus objetos y las relaciones entre estos. Las oficinas que trabajan con esta base de datos son:

Oficina Empleado Inicio de Sesión

Contraseña Tipo de Usuario

Admisión

Cecilia Calderón Ccalderon Patito Jefe

Carmen Peralta Cperalta Perrito Jefe

Yovana Vargas Yvargas Muchachita Operador

Liz Castro Lcastro Michimichi Operador

Marketing Katty Suarez Ksuarez Loquita Jefe

Martha Carrion Mcarrion Love Operador Se tiene desarrollada una aplicación que acceda a la base de datos con el usuario y contraseña concedidos a cada uno de los usuarios (Autenticación SQL). Con respecto a la aplicación la oficina de Admisión está solicitando que se implemente:

• Un reporte que dado un determinado periodo académico (IDPerAcad] liste: apellidos y nombres, carrera y modalidad de todos los postulantes de ese periodo.

• Un reporte que dada un determinado periodo académico (IDPerAcad) liste: apellidos y nombres, carrera y modalidad de todos los ingresantes de ese periodo.

• Un reporte que dada un determinado periodo académico (IDPerAcad) liste: apellidos y nombres, carrera y modalidad de todos los postulantes que no asistieron al examen de ese periodo.

• Los 10 mejores puestos por periodo (IDPerAcad). Así mismo los de la oficina de Marketing necesitan que se implemente lo siguiente:

• Un reporte que muestre la cantidad de postulantes por cada periodo (IDPerAcad)

• Un reporte que muestre la cantidad de postulantes por cada periodo (IDPerAcad), este reporte debe de mostrar las columnas: Periodo, varón, mujer y total.

• Un reporte que liste los apellidos y Nombres de todos aquellos que cumplan años, este reporte debe de funcionar en función a la fecha actual.

• Listado de aquellos contactos que nunca ingresaron.

118

Los usuarios operadores de la oficina de Admisión solo pueden:

• Seleccionar, Actualizar e Insertar Contactos y Postulante. • De los postulantes solo pueden tener acceso de selección en los campos

Puntaje, AsistioExamen e Ingreso. • Acceso a los requerimientos previamente solicitados.

Los Usuarios jefes de la oficina de Admisión pueden:

• Seleccionar y Eliminar Postulantes. • Actualizar los campos Puntaje, AsistioExamen e Ingreso de los postulantes. • Seleccionar Contactos • Seleccionar, Insertar, Eliminar y Actualizar Carreras, Modalidades y Periodos. • Acceso a los requerimientos previamente solicitados.

Los usuarios operadores y jefes de Marketing:

• Acceso a los requerimientos recientemente solicitados. Tenga en cuenta que en cualquier momento el jefe u operadores de las oficinas pueden cambiar, así que las soluciones planteadas deben de ser lo más flexible posible.

119

Sentencias DCL El Lenguaje de Control de Datos (Data Control Language – DCL) es utilizado para llevar a cabo la administración de privilegios en los objetos de la base de datos el cual ayuda a garantizar la accesibilidad y seguridad dentro de los objetos de la base de datos. Las cláusulas DCL que se usan para el control de accesos en PostgreSQL son: GRANT, REVOKE.

GRANT

GRANT privilegio [, ...] ON objeto [, ...] TO { PUBLIC | GROUP grupo | usuario }

GRANT permite al creador de un objeto asignarle permisos específicos a todos los usuarios (PUBLIC), a un cierto usuario o grupo. Usuarios distintos al creador pueden no tener permisos de acceso a menos que el creador se los conceda, una vez que el objeto ha sido creado. Una vez que un usuario tiene privilegios sobre un objeto, tiene la posibilidad de ejecutar ese privilegio. No hay necesidad de conceder privilegios al creador de un objeto; el creador obtiene automáticamente TODOS los privilegios, y puede también eliminar el objeto.


privilegio

SELECT Acceder a todas las columnas de una tabla o vista.

INSERT Insertar datos en todas las columnas de una tabla.

UPDATE Actualizar todas las columnas de tabla.

DELETE Eliminar filas de una tabla

RULE Definir reglas en una tabla o vista.

ALL Otorgar todos los privilegios.

objeto El nombre de un objeto al que se quiere conceder el acceso. Los posibles objetos son: tabla, vista, secuencia, índice.

grupo El nombre de un grupo al cual se otorga privilegios. El grupo debe haber sido creado explícitamente.

usuario El nombre de un usuario al que se quiere conceder privilegios.

PUBLIC Una abreviación para representar a todos los usuarios.


120

CHANGE Mensaje devuelto si la orden se completa satisfactoriamente.

ERROR: ChangeAcl: class "object" not found Mensaje que se devuelve si el objeto no está disponible o si es imposible dar los privilegios al grupo o a los usuarios.

Uso : Concede privilegios de inserción a todos los usuarios de la tabla testtable:

GRANT INSERT ON testtable TO PUBLIC; Concede todos los privilegios al usuario testuser sobre la vista clases:

GRANT ALL ON clases TO testuser; Consulte el comando psql \z para obtener más información sobre permisos en objetos existentes: 6. -bash-4.1$ psql –U testuser –d test 7. test=# \z 8. Privilegios 9. Esquema| Nombre | Tipo | Privileg |Privilegios de acceso a columnas 10. -------+--------------------+-----------+----------+--------------------------- 11. Public | amigos | tabla | | 12. Public | clientes | tabla | | 13. public | clientes_id_seq | secuencia| | 14. public | cuentas | tabla | | 15. public | cuentas_id_seq | secuencia| | 16. public | invitaciones | tabla | | 17. public | invitaciones_id_seq| secuencia| | 18. public | testtable | tabla | | 19. public | usuarios | tabla | | 20. public | usuarios_id_seq | secuencia| | 21. (10 filas)

REVOKE

REVOKE privilegio [, ...] ON objeto [, ...] FROM { PUBLIC | GROUP grupo | usuario }

REVOKE permite al creador de un objeto revocar permisos asignados anterior-mente a todos los usuarios (mediante PUBLIC), a un usuario o a un grupo.


privilegio SELECT Acceder a todas las columnas de una tabla o vista.

121

INSERT Insertar datos en todas las columnas de una tabla.

UPDATE Actualizar todas las columnas de tabla.

DELETE Borrar filas de una tabla

RULE Definir reglas en una tabla o vista.

ALL Rescindir todos los privilegios.

objeto El nombre de un objeto sobre el que revocar el acceso. Los posibles objetos son: tabla, vista, secuencia, índice.

grupo El nombre de un grupo al cual se revocan privilegios.

usuario El nombre de un usuario al cual se revocan privilegios.

PUBLIC Rescinde el/los privilegio(s) especificados(s) a todos los usuarios.

Salidas más usuales CHANGE


Mensaje que se devuelve si el objeto no está disponible o si es imposible revocarprivilegios al grupo o a los usuarios.

Uso : Revoca el privilegio de inserción a todos los usuarios de la tabla testtable:

REVOKE INSERT ON testtable TO PUBLIC; Revoca todos los privilegios al usuario testuser sobre la vista clases:

REVOKE ALL ON clases TO testuser; Usamos la base de datos dbadmision y nos enfocamos en un caso real de implementación para asignar los privilegios en los objetos para garantizar la accesibilidad y seguridad del sistema para una Universidad usando las sentencias GRANT y REVOKE de acuerdo a los requerimientos establecidos en la sección anterior. SUGERENCIA: Si has utilizado la base de datos dbadmision en ejercicios previos es mejor volver a crearla. Utilizar el Script: dbadmin.sql

122

DESARROLLO PARTE 1: CREACIÓN DE ROLES Y USUARIOS DE LOGIN –CREANDO ROLES

1. CREATE ROLE JefeADM; 2. CREATE ROLE OpeADM; 3. CREATE ROLE JefeMKT; 4. CREATE ROLE OpeMKT;

–CREANDO LOGINS

1. CREATE ROLE Ccalderon 2. WITH LOGIN PASSWORD 'Patito' 3. VALID UNTIL '2012-01-01' 4. IN ROLE JefeADM;

5. CREATE ROLE Cperalta 6. WITH LOGIN PASSWORD 'Perrito' 7. VALID UNTIL '2012-01-01' 8. IN ROLE JefeADM;

9. CREATE ROLE Yvargas 10. WITH LOGIN PASSWORD 'Muchachita' 11. VALID UNTIL '2012-01-01' 12. IN ROLE OpeADM;

13. CREATE ROLE Lcastro 14. WITH LOGIN PASSWORD 'Michimichi' 15. VALID UNTIL '2012-01-01' 16. IN ROLE OpeADM;

17. CREATE ROLE Ksuarez 18. WITH LOGIN PASSWORD 'Loquita' 19. VALID UNTIL '2012-01-01' 20. IN ROLE JefeMKT;

21. CREATE ROLE Mcarrion 22. WITH LOGIN PASSWORD 'Love' 23. VALID UNTIL '2012-01-01' 24. IN ROLE OpeMKT;

PARTE 2: CREACIÓN DE REQUERIMIENTOS –CREANDO ESQUEMAS

1. CREATE SCHEMA REQ_ADM; 2. CREATE SCHEMA REQ_MKT;

123

–CREANDO FUNCIONES REQUERIDAS Las siguientes funciones fueron creadas según los requerimientos realizados en el escenario mostrado inicialmente de las oficinas de Admision (ADM) y Marketing (MKT), que serán almacenados en los esquemas anteriormente definidos para organizarlo adecuadamente y también para facilitar la asignación de permisos por esquemas. PARTE 3: ASIGNACIÓN DE DERECHOS –(A LOS OPERADORES DE ADM) –(Derecho 1) : Seleccionar, Actualiza e Insertar Contactos y Postulantes

1. GRANT SELECT ON Persona.Contacto TO OpeADM; 2. GRANT INSERT ON Persona.Contacto TO OpeADM; 3. GRANT UPDATE ON Persona.Contacto TO OpeADM;

4. GRANT SELECT ON Persona.Postulante TO OpeADM; 5. GRANT INSERT ON Persona.Postulante TO OpeADM; 6. GRANT UPDATE ON Persona.Postulante TO OpeADM;

7. GRANT USAGE ON SCHEMA Persona TO OpeADM;

–(Derecho 2) : De los postulantes solo debe tener acceso de seleccion sobre

Puntaje, AsistioExamen e Ingreso

1. REVOKE SELECT ON Persona.Postulante FROM OpeADM; --Quitamos permiso de SELECT sobre Postulante

2. GRANT SELECT (AsistioExamen) ON Persona.Postulante TO OpeADM; 3. GRANT SELECT (Ingreso) ON Persona.Postulante TO OpeADM;

–(Derecho 3) : Acceso a los requerimientos previamente solicitados

1. GRANT USAGE ON SCHEMA REQ_ADM TO OpeADM;

–(A LOS JEFES DE ADM) –(Derecho 1) : Seleccionar y Eliminar Postulantes.

1. GRANT SELECT ON Persona.Postulante TO JefeADM; 2. GRANT DELETE ON Persona.Postulante TO JefeADM; 3. GRANT USAGE ON SCHEMA Persona TO JefeADM;

–(Derecho 2) : Actualizar los campos Puntaje, AsistioExamen e Ingreso de

Postulante

1. GRANT UPDATE (Puntaje) ON Persona.Postulante TO JefeADM; 2. GRANT UPDATE (AsistioExamen) ON Persona.Postulante TO JefeADM;

124

3. GRANT UPDATE (Ingreso) ON Persona.Postulante TO JefeADM;

–(Derecho 3) : Seleccionar Contactos

1. GRANT SELECT ON Persona.Contacto to JefeADM;

–(Derecho 4) : Seleccionar, Insertar, Eliminar y Actuzalizar Carreras, Modalidades

y Periodos

1. GRANT SELECT,INSERT,DELETE, UPDATE ON Admision.Carrera TO JefeADM; 2. GRANT SELECT,INSERT,DELETE, UPDATE ON Admision.Modalidad TO JefeADM; 3. GRANT SELECT,INSERT,DELETE, UPDATE ON Admision.PerAcad TO JefeADM;

4. GRANT USAGE ON SCHEMA Admision to JefeADM;


1. GRANT USAGE ON SCHEMA REQ_ADM TO JefeADM;

–(A LOS JEFES Y OPERADORES DE mkt)

1. GRANT USAGE ON SCHEMA REQ_MKT TO JefeMkt,OpeMKt;

PARTE 4: VERIFICACIÓN DE DERECHOS ASIGNADOS La siguiente parte es verificar que los permisos hayan sido otorgados correctamente, también es posible ir verificando estos a medida se vayan asignando los privilegios. –VERIFICANDO USUARIO ACTUAL

1. SELECT SESSION_USER, CURRENT_USER;

–COMPROBANDO DERECHOS –(DE LOS OPERADORES DE ADM)

1. SET SESSION AUTHORIZATION OpeADM;

–(Derecho 1) : Seleccionar, Actualiza e Insertar Contactos y Postulantes

1. SELECT * FROM Persona.Contacto;

2. INSERT INTO Persona.Contacto VALUES( 3. 176 4. ,'Anonymous' 5. ,'Anonymous'

125

6. ,'Anonymous' 7. ,0 8. ,00000000 9. ,'10/11/2000' 10. ,now());

11. UPDATE Persona.Contacto SET 12. nombres='Anonimo' 13. ,paterno='Anonimo' 14. ,materno='Anonimo' 15. WHERE IDCOntacto='176';

16. DELETE FROM Persona.Contacto WHERE IDCOntacto='176'; 17. --El rol OpeADM no tiene permiso para eliminar.

–(Derecho 2) De los postulantes solo debe tener acceso de selección sobre

Puntaje, AsistioExamen e Ingreso

1. SELECT IDPostulante,IDContacto,IDCarrera,IDModalidad 2. FROM Persona.Postulante;

3. SELECT Puntaje,AsistioExamen,Ingreso FROM Persona.Postulante;


1. SELECT * FROM REQ_ADM.pa_r1() AS (column1 unknown); 2. SELECT * FROM REQ_ADM.pa_r2() AS (column1 unknown); 3. SELECT * FROM REQ_ADM.pa_r3() AS (column1 unknown); 4. SELECT * FROM REQ_ADM.pa_r4() AS (column1 unknown);

–(DE LOS JEFES DE ADM)

1. SET SESSION AUTHORIZATION JefeADM;

–(Derecho 1) : Seleccionar y Eliminar Postulantes.

1. SELECT * FROM Persona.Postulante; 2. DELETE FROM Persona.Postulante WHERE IDContacto='175';

–(Derecho 2) : Actualizar los campos Puntaje, AsistioExamen e Ingreso de Postulante

1. UPDATE Persona.Postulante SET 2. Puntaje=20, AsistioExamen='1', Ingreso='1' WHERE IDContacto='174'; 3. UPDATE Persona.Postulante SET IDModalidad='0';--No tiene persmiso

–(Derecho 3) : Seleccionar Contactos

1. SELECT * FROM Persona.Contacto;

126

–(Derecho 4) : Seleccionar, Insertar, Eliminar y Actualizar Carreras, Modalidades y Periodos

1. SELECT * FROM Admision.Carrera; 2. SELECT * FROM Admision.Modalidad; 3. SELECT * FROM Admision.PerAcad;


1. SELECT * FROM REQ_ADM.pa_r1() AS (column1 unknown); 2. SELECT * FROM REQ_ADM.pa_r2() AS (column1 unknown); 3. SELECT * FROM REQ_ADM.pa_r3() AS (column1 unknown); 4. SELECT * FROM REQ_ADM.pa_r4() AS (column1 unknown);

–(DE LOS JEFES DE MKT)

1. SET SESSION AUTHORIZATION OpeMKT;


1. SELECT * FROM REQ_MKT.pa_r1() AS (column1 unknown); 2. SELECT * FROM REQ_MKT.pa_r2() AS (column1 unknown); 3. SELECT * FROM REQ_MKT.pa_r3() AS (column1 unknown); 4. SELECT * FROM REQ_MKT.pa_r4() AS (column1 unknown);

–(DE LOS OPERADORES DE MKT)

1. SET SESSION AUTHORIZATION JefeMKT;


1. SELECT * FROM REQ_MKT.pa_r1() AS (column1 unknown); 2. SELECT * FROM REQ_MKT.pa_r2() AS (column1 unknown); 3. SELECT * FROM REQ_MKT.pa_r3() AS (column1 unknown); 4. SELECT * FROM REQ_MKT.pa_r4() AS (column1 unknown);

Es importante señalar que las verificaciones se han realizado únicamente por medio de roles de grupo, lo ideal y correcto será realizar las operaciones como usuarios (roles de login) por ejemplo:

5. SET SESSION AUTHORIZATION OpeADM;

cambiarlo por:

6. SET SESSION AUTHORIZATION Yvargas;

El resultado deberá ser el mismo ya que Yvargas pertenece al grupo OpeADM.

127

PARTE 5: REESTABLECIMIENTO Y/O ELIMINACIÒN DE OBJET OS Si queremos eliminar algunos objetos o incluso la base de datos no debe existir ningún rol asociado a éste

1. RESET SESSION AUTHORIZATION; 2. --Regresamos al usuario principal por defecto.

3. SELECT SESSION_USER, CURRENT_USER;

4. DROP DATABASE dbadmision; 5. --ERROR: database "dbadmision" is being accessed by other users

6. REVOKE ALL ON SCHEMA REQ_MKT,REQ_ADM,Persona,Admision 7. FROM JefeADM,OpeADM,JefeMKT,OpeMKT;

8. REVOKE ALL ON TABLE Persona.Contacto 9. ,Persona.Postulante 10. ,Admision.Carrera 11. ,Admision.PerAcad 12. ,Admision.Modalidad 13. FROM JefeADM,OpeADM,JefeMKT,OpeMKT;

14. DROP USER CCalderon,CPeralta,Yvargas,LCastro,Ksuarez,Mcarrion;

15. DROP ROLE JefeADM,OpeADM,JefeMKT,OpeMKT;

16. DROP DATABASE dbadmision;

Para la eliminación de la base de datos habrá que confirmar además de que no exista ninguna conexión abierta de la base de datos por algún usuario.

17. SELECT * FROM pg_stat_activity;

128

Sentencias XML La tecnología XML nos ha facilitado constantemente en lograr compatibilidad entre diversos formatos y permitir compartirlos entre distintas aplicaciones y sin duda en bases de datos su funcionalidad también es muy útil. Veremos el uso del tipo de dato XML, el uso de las funciones XML y la exportación de consultas hacia XML con PostgreSQL. El esquema que habrá que respetar en el entorno de bases de datos, y en el caso específico de postgres es el siguiente:

1. <catalogname> 2. <schemaname> 3. <tablename> 4. <row> 5. <colname1>value</colname1> 6. <colname2 xsi:nil=’true’/> 7. ... 8. </row> 9. ... 10. </tablename> 11. ... 12. </schemaname> 13. ... 14. </catalogname>

No voy a entrar al detalle de lo que es XML, tampoco de todas las funciones habidas y por haber sobre el, sin embargo desarrollaremos una parte importante que nos sacará de apuros en algún momento o situación. Algunas de las funciones brindadas por postgres sobre XML que usaremos son:

• XMLCOMMENT • XMLCONCAT • XMLELEMENT • XMLFOREST • XMLAGG • QUERY_TO_XML • TABLE_TO_XML

Para comprender la funcionalidad de cada uno de ellos, usamos la base de datos dbadmision y nos enfocamos en un caso real de ejecución usando las sentencias anteriores. SUGERENCIA: Si has utilizado la base de datos dbadmision en ejercicios previos es mejor volver a crearla. Utilizar el Script: dbadmin.sql

129

1- Comprobando algunas funciones XML:

1. SELECT xmlelement(name Contacto, IDContacto) 2. FROM Persona.Contacto;

3. SELECT xmlelement(name Paterno, Paterno),xmlelement(name Materno, Materno) 4. FROM Persona.Contacto;

5. SELECT xmlforest(Paterno, Materno) 6. FROM Persona.Contacto;

7. SELECT xmlelement(name Contacto, xmlforest(Paterno, Materno)) 8. FROM Persona.Contacto;

9. SELECT XMLAgg(XMLForest(Paterno, Materno)) 10. FROM Persona.Contacto;

11. SELECT XMLElement(name Contacto, XMLAgg(XMLForest(Paterno, Materno))) 12. FROM Persona.Contacto;

2- Creamos una secuencia para usarla como numeraci ón de la estructura

resultante:

1. --DROP SEQUENCE seq_contacto; 2. CREATE TEMP SEQUENCE seq_contacto; 3. SELECT xmlelement(name Contacto 4. ,xmlattributes(nextval('seq_contacto') AS "row") 5. ,xmlforest(Paterno, Materno) ) 6. FROM Persona.Contacto;

7. SELECT xmlelement(name Contacto, xmlforest(Paterno, Materno, Nombres) ) 8. FROM Persona.Contacto;

9. SELECT xmlelement(name Persona 10. ,xmlelement(name Contacto 11. ,xmlattributes(IDContacto) 12. ,Paterno 13. ,Materno 14. ,Nombres) 15. ) 16. FROM Persona.Contacto;

3- Usando xmlcomment para comentar sobre XML

1. SELECT xmlelement(name Persona 2. ,xmlcomment('Comentario') 3. ,xmlforest(Paterno, Materno,Nombres)) 4. FROM Persona.Contacto;

5. SELECT xmlelement(name Paterno, Materno) 6. ,xmlelement(name Materno, Materno) 7. FROM Persona.Contacto;

130

8. SELECT xmlconcat(xmlelement(name Paterno, Materno) 9. ,xmlelement(name Nombres, Nombres)) 10. FROM Persona.Contacto;

11. SELECT xmlelement(name Contacto, xmlconcat( xmlelement(name Paterno, Paterno) 12. ,xmlelement(name Materbo, Materno))) 13. FROM Persona.Contacto;

4- Creamos una tabla que contenga un campo de tipo XML, veremos luego

una manera especial de manipularlas.

1. --DROP TABLE Persona.ContactoXML; 2. CREATE TABLE Persona.ContactoXML (col1 XML); 3. INSERT INTO Persona.ContactoXML(SELECT xmlelement(name Contacto 4. ,xmlconcat( xmlelement(name Paterno, Paterno) 5. ,xmlelement(name Materno, Materno) ) ) 6. FROM Persona.Contacto);

7. SELECT col1 FROM Persona.ContactoXML;

5- XPATH nos facilitará en la seleccion de campos d entro de una estructura

de datos XML:

1. SELECT xpath('/contacto/materno/text()', col1) 2. FROM Persona.ContactoXML ;

3. SELECT xpath('/contacto/paterno/text()', col1) 4. FROM Persona.ContactoXML ;

5. SELECT columna[1] 6. FROM ( SELECT xpath('/contacto/materno/text()', col1) AS columna 7. FROM Persona.ContactoXML ) AS xmlsource;

6- En estas dos últimas funciones, su poder radica en que puede generar

una completa estructura XML a nivel de consulta o d e una tabla completa:

1. SELECT query_to_xml('SELECT Nombres,Paterno,Materno FROM Persona.Contacto' 2. ,FALSE,FALSE,'Contactos')

3. SELECT table_to_xml('Persona.Contacto',FALSE,FALSE,'Contactos')

Recursos Utilizados: SQLXML-For-Postgres-Developers XML Document Support XML and Databases XML Type

131

PL/pgSQL

Introducción Ya hemos visto que SQL (DML) es un lenguaje simple y poderoso para manipular datos en una base de datos. PL/pgSQL es un lenguaje procedimental que permite crear funciones, procedimientos y triggers con el fin de realizar operaciones y computaciones más complejas dentro de la base de datos. Realizar dichas operaciones dentro de la base de datos, y no en las aplicaciones, puede mejorar el rendimiento del sistema puesto que se disminuye la comunicación entre la aplicación y el SGBD.

Antes de iniciar

Para los ejemplos descritos en este taller se requieren las siguientes tablas y datos:

CREATE TABLE CLIENTES (ID SERIAL PRIMARY KEY, CC VARCHAR(10) NOT NULL, NOMBRE VARCHAR(50) NOT NULL); CREATE TABLE CUENTAS (ID SERIAL PRIMARY KEY, SALDO REAL DEFAULT 0, CLI_ID INT NOT NULL REFERENCES CLIENTES(ID), FECHA_SOBREGIRO DATE, INTERESES REAL DEFAULT 0); INSERT INTO CLIENTES (CC,NOMBRE) VALUES ('11111','CLIENTE 1'); INSERT INTO CLIENTES (CC,NOMBRE) VALUES ('22222','CLIENTE 2'); INSERT INTO CLIENTES (CC,NOMBRE) VALUES ('33333','CLIENTE 3'); INSERT INTO CUENTAS (SALDO, CLI_ID) VALUES ( 5000,1); INSERT INTO CUENTAS (SALDO, CLI_ID) VALUES ( -2000,2); INSERT INTO CUENTAS (SALDO, CLI_ID) VALUES ( 3000,3);

Estructura La estructura general de un bloque PL/PgSQL es la siguiente:

[ <<label>> ] [ DECLARE declarations ] BEGIN statements END [ label ];

132

Utilización mediante SELECT Por ejemplo, la siguiente función retorna su argumento multiplicado por 3: /* Función que multiplica su argumento por 3 */ CREATE OR REPLACE FUNCTION MULT_3(x int) returns INT as $$ begin RETURN x*3; end; $$ LANGUAGE plpgsql;

La función puede ser utilizada en una cláusula SELECT: select mult_3(4); mult_3 -------- 12 (1 row)

DECLARE: Las variables, en caso de ser necesarias, deben ser declaradas en la cláusula DECLARE: /* Concatenando el nombre y el apellido y adicionando mayusculas */ CREATE OR REPLACE FUNCTION CONCAT_NOMBRE(NOMBRE VARCHAR, APELLIDO VARCHAR) RETURNS VARCHAR AS $$ DECLARE TEMP VARCHAR; BEGIN TEMP:= NOMBRE || ' ' || APELLIDO; RETURN INITCAP(TEMP); END; $$ LANGUAGE plpgsql;

Un ejemplo de uso: select concat_nombre('carlos','olarte'); concat_nombre --------------- Carlos Olarte

Insertar Datos en una Tabla

Esta función inserta un registro en la tabla CLIENTES: CREATE FUNCTION INS_CLIENTES(VARCHAR, VARCHAR)

133

RETURNS VOID AS $$ INSERT INTO CLIENTES (CC,NOMBRE) VALUES ($1,$2); $$ LANGUAGE SQL;

Un ejemplo de uso: SELECT INS_CLIENTES('44444','CLIENTE 4');

Note que en este caso, los argumentos de la función no tienen nombre y se refieren a ellos como $1 y $2 dentro del cuerpo de la función. Además, como solo lanzamos sentencias SQL, el lenguaje de la función es SQL.

Actualizar Datos en una Tabla (Transferir Dinero entre dos Cuentas)

/* Función que transfiere dinero de una cuenta a otra */ CREATE FUNCTION TRANSFER (CTA1 CUENTAS.ID%TYPE , CTA2 CUENTAS.ID%TYPE , MONTO CUENTAS.SALDO%TYPE) RETURNS CUENTAS.SALDO%TYPE AS $$ DECLARE NUEVO_SALDO CUENTAS.SALDO%TYPE; BEGIN UPDATE CUENTAS SET SALDO = SALDO - MONTO WHERE ID=CTA1; UPDATE CUENTAS SET SALDO = SALDO + MONTO WHERE ID=CTA2; SELECT SALDO INTO NUEVO_SALDO FROM CUENTAS WHERE ID=CTA2; RETURN NUEVO_SALDO; END $$ LANGUAGE PLPGSQL;

Ejemplo de uso: postgres=# select transfer(3,2,1000); transfer ---------- -1000 (1 row)

En este ejemplo hemos introducido algunas características interesantes del lenguaje: • Los tipos de las variables se pueden declarar de acuerdo con los tipos de

datos en las tablas como en CTA1 CUENTAS.ID%TYPE (es decir, CT1 es una variable del tipo del campo ID en la tablas CUENTAS).

• Hemos lanzado sentencias del DML como parte del procedimiento (UPDATE) • Utilizamos SELECT … INTO VAR. Cuando un SELECT retorna una UNICA

fila, esta se puede almacenar en una variable local.

134

Es posible realizar algunas validaciones. Por ejemplo, se debe verificar que las dos cuentas existan y que el monto de la segunda sea suficiente para realizar la transferencia. /* Función que transfiere dinero de una cuenta a otra */ /* Versión2: Validacion de los datos de entrada */ CREATE FUNCTION TRANSFER2 (CTA1 CUENTAS.ID%TYPE, CTA2 CUENTAS.ID%TYPE, MONTO CUENTAS.SALDO%TYPE) RETURNS CUENTAS.SALDO%TYPE AS $$ DECLARE NUEVO_SALDO CUENTAS.SALDO%TYPE; SALDO_DISPONIBLE CUENTAS.SALDO%TYPE; TEST_EXISTS INT; BEGIN SELECT COUNT(*) INTO TEST_EXISTS FROM CUENTAS WHERE ID=CTA1; IF TEST_EXISTS <> 1 THEN RAISE EXCEPTION 'La cuenta de origen % no existe', CTA1; ELSE SELECT COUNT(*) INTO TEST_EXISTS FROM CUENTAS WHERE ID=CTA2; IF TEST_EXISTS <> 1 THEN RAISE EXCEPTION 'La cuenta destino % no existe', CTA2; ELSE SELECT SALDO INTO SALDO_DISPONIBLE FROM CUENTAS WHERE ID = CTA1; IF SALDO_DISPONIBLE < MONTO THEN RAISE EXCEPTION 'No hay fondos suficientes para la transferencia'; ELSE /* realizando la transferencia */ UPDATE CUENTAS SET SALDO = SALDO - MONTO WHERE ID=CTA1; UPDATE CUENTAS SET SALDO = SALDO + MONTO WHERE ID=CTA2; /* Consultando y retornando el nuevo saldo de CTA2 */ SELECT SALDO INTO NUEVO_SALDO FROM CUENTAS WHERE ID=CTA2; RETURN NUEVO_SALDO; END IF; END IF; END IF; END $$ LANGUAGE PLPGSQL;

Ejemplo (Actualizando los intereses) Antes de presentar el ejemplo, vamos a adicionar algunas filas a la tabla cuentas: INSERT INTO CUENTAS (SALDO,CLI_ID, FECHA_SOBREGIRO) VALUES (-3000,3,'2010-01-01'), (-7000,2,'2010-03-01');

La siguiente función actualiza los intereses de mora en las cuentas que están sobregiradas. Los intereses que se cobran son proporcionales al saldo de la cuenta y se utiliza el porcentaje que se pasa como parámetro. /* Actualizando los intereses de las cuentas de acuerdo con el numero de dias en mora */

135

CREATE FUNCTION COBRAR_INTERESES(PORCENTAJE REAL) RETURNS VOID AS $$ BEGIN UPDATE CUENTAS SET INTERESES = SALDO * PORCENTAJE * EXTRACT('DAYS'FROM ( NOW()-FECHA_SOBREGIRO)) WHERE FECHA_SOBREGIRO IS NOT NULL; END; $$ LANGUAGE plpgsql;

Ejemplo de uso: bdi00=> SELECT COBRAR_INTERESES(0.01);

Utilizando Row Types

Utilizando %ROWTYPE es posible declarar RECORDS del mismo tipo de una tabla. En el siguiente ejemplo, se crean RECORDS del tipo de las tablas CUENTAS y CLIENTES. La función simplemente imprime información relacionada con la cuenta que se pasa como parámetro. /* Retornando el nombre del cliente y el saldo de una cuenta */ CREATE FUNCTION INF_CUENTA(NUMCUENTA INT) RETURNS VARCHAR AS $$ DECLARE RESULTADO VARCHAR; cta_row CUENTAS%ROWTYPE; --cta_row es del mismo tipo de la tabla CUENTAS cli_row CLIENTES%ROWTYPE; BEGIN -- Puede utilizar SELECT .. INTO cuando el resultado trae una sola fila SELECT * INTO CTA_ROW FROM CUENTAS WHERE ID = NUMCUENTA; IF NOT FOUND THEN RAISE EXCEPTION 'La cuenta % no existe. ', NUMCUENTA; ELSE SELECT * INTO CLI_ROW FROM CLIENTES WHERE ID = CTA_ROW.CLI_ID; RESULTADO := ' La cuenta pertenece a ' || CLI_ROW.Nombre || '. El saldo es ' || CTA_ROW.SALDO; RETURN RESULTADO; END IF; END; $$ LANGUAGE plpgsql;

Ejemplo de uso: bdi00=> Select INF_CUENTA(1); inf_cuenta ---------------------------------------------------- La cuenta pertenece a CLIENTE 1. El saldo es 5000 (1 row)

Note el uso de IF NOT FOUND THEN … para verificar si el SELECT trajo o no algún resultado. Obviamente la función anterior se hubiera podido escribir de

136

manera mucho más simple ejecutando una única consulta que traiga la información del cliente y de la cuenta: CREATE FUNCTION INF_CUENTA2(NUMCUENTA INT) RETURNS VARCHAR AS $$ DECLARE SALDO_CTA CUENTAS.SALDO%TYPE; NOMBRE_CLI VARCHAR; BEGIN SELECT CLI.NOMBRE, CTA.SALDO INTO NOMBRE_CLI,SALDO_CTA FROM CUENTAS CTA INNER JOIN CLIENTES CLI ON (CLI.ID = CTA.CLI_ID) WHERE ID = NUMCUENTA; IF NOT FOUND THEN RAISE EXCEPTION 'La cuenta % no existe. ', NUMCUENTA; ELSE RETURN ' La cuenta pertenece a ' || NOMBRE_CLI || '. El saldo es ' || SALDO_CTA; RETURN RESULTADO; END IF; END; $$ LANGUAGE plpgsql;

En este caso, note que la cláusula SELECT … INTO puede asignar varias variables al tiempo.

Cursores Un cursor es una estructura que permite recuperar los datos de una consulta fila por fila. Asuma una tabla con una serie de transacciones bancarias “pendientes”, i.e., transacciones que deben ser realizadas y afectar las cuentas de los clientes: CREATE TABLE PENDIENTES ( ID SERIAL PRIMARY KEY, MONTO REAL NOT NULL, CTA_ID INT NOT NULL REFERENCES CUENTAS(ID), FECHA DATE DEFAULT NOW(), OPERACION CHAR(1), REALIZADA BOOL DEFAULT FALSE); INSERT INTO PENDIENTES (MONTO,CTA_ID,OPERACION) VALUES (300,1,'D'), (400,2,'R'), (233,3,'D');

El siguiente procedimiento “recorre” la tabla pendientes y realiza la operación indicada en la tabla CUENTAS. CREATE OR REPLACE FUNCTION UPD_PENDIENTES() RETURNS VOID AS $$ DECLARE -- Declaración del Cursor -- Note la forma del Select ... FOR UPDATE! CUR_PEN CURSOR FOR SELECT * FROM PENDIENTES WHERE REALIZADA=FALSE FOR UPDATE; ACCION PENDIENTES%ROWTYPE; BEGIN -- Abrir el cursor

137

OPEN CUR_PEN; LOOP -- Extraer una fila del cursor FETCH CUR_PEN INTO ACCION; IF ACCION IS NULL THEN EXIT; END IF; IF ACCION.OPERACION = 'D' THEN UPDATE CUENTAS SET SALDO = SALDO + ACCION.MONTO WHERE ID = ACCION.CTA_ID; ELSE UPDATE CUENTAS SET SALDO = SALDO - ACCION.MONTO WHERE ID = ACCION.CTA_ID; END IF; -- Actualizar una fila de acuerdo con la posición del cursor UPDATE PENDIENTES SET REALIZADA=TRUE WHERE CURRENT OF CUR_PEN; END LOOP; -- Cierre del cursor. CLOSE CUR_PEN; END; $$ LANGUAGE plpgsql;

La cláusula FOR UPDATE evita que otras transacciones estén modificando las filas seleccionadas. Ejemplo de uso: bdi00=> select * from CUENTAS; id | saldo | cli_id | fecha_sobregiro | intereses ----+-------+--------+-----------------+----------- 1 | 5000 | 1 | | 0 2 | -2000 | 2 | | 0 3 | 3000 | 3 | | 0 (3 rows) bdi00=> select * from PENDIENTES; id | monto | cta_id | fecha | operacion | realizada ----+-------+--------+------------+-----------+----------- 1 | 300 | 1 | 2010-04-26 | D | f 2 | 400 | 2 | 2010-04-26 | R | f 3 | 233 | 3 | 2010-04-26 | D | f (3 rows) bdi00=> SELECT UPD_PENDIENTES(); upd_pendientes ---------------- (1 row)

bdi00=> select * from CUENTAS; id | saldo | cli_id | fecha_sobregiro | intereses ----+-------+--------+-----------------+----------- 1 | 5300 | 1 | | 0 2 | -2400 | 2 | | 0 3 | 3233 | 3 | | 0 bdi00=> select * from PENDIENTES; id | monto | cta_id | fecha | operacion | realizada ----+-------+--------+------------+-----------+----------- 1 | 300 | 1 | 2010-04-26 | D | t 2 | 400 | 2 | 2010-04-26 | R | t 3 | 233 | 3 | 2010-04-26 | D | t

138

(3 rows)

Cursores Implicitos con FOR <VAR> IN <QUERY> Otra forma de recorrer las tablas es utilizar cursores “implícitamente” usando FOR <VAR> IN <QUERY>. Por ejemplo, asuma que se tiene una tabla con la lista de usuarios que han efectuado mal uso de sus cuentas y deben ser desactivadas: CREATE TABLE USUARIOS_FRAUDE(ID SERIAL, CLI_ID INT); INSERT INTO USUARIOS_FRAUDE (CLI_ID) VALUES (1), (3);

Ahora adicionamos un atributo en cuenta para saber si está activa o no: ALTER TABLE CUENTAS ADD INACTIVA BOOL DEFAULT FALSE;

La siguiente función inhabilita las cuentas cuyo usuario se encuentra en la tabla de USUARIOS_FRAUDE CREATE OR REPLACE FUNCTION INACTIVA () RETURNS VOID AS $$ DECLARE USR USUARIOS_FRAUDE%ROWTYPE; BEGIN FOR USR IN SELECT * FROM USUARIOS_FRAUDE LOOP UPDATE CUENTAS SET INACTIVA = TRUE WHERE CLI_ID = USR.CLI_ID; END LOOP; END; $$ LANGUAGE plpgsql;

Para este caso particular, un lector atento hubiera podido descubrir que el ejercicio se podía realizar con una sola sentencia del DML: UPDATE CUENTAS SET INACTIVA = FALSE WHERE CLI_ID IN (SELECT CLI_ID FROM USUARIOS_FRAUDE);

Triggers

Un trigger es una acción que se lanza cuando se inserta, elimina o actualiza una o varios registros de una tabla. En PL/pgSQL, los triggers son funciones sin argumentos en las cuales se crean las siguientes variables: • NEW: Es un Record con los datos del registro que se está insertando

(actualizando). • OLD: Datos del registro que se está eliminando (o actualizando). • TG_OP: Operación que se está realizando, puede ser INSERT, UPDATE,

DELETE, o TRUNCATE.

139

Un ejemplo de Auditoria Suponga que cada que se modifica una cuenta, se debe registrar la acción en la siguiente tabla: CREATE TABLE AUDITORIA( OPERACION CHAR(1), FECHAUPD TIMESTAMP, USUARIO TEXT, ID INT, SALDO REAL, CLI_ID INT, FECHA_SOBREGIRO DATE, INTERESES REAL, INACTIVA BOOL);

La siguiente función deja un rastro en la tabla AUDITORIA cada que se realiza una modificación en la tabla CUENTAS: CREATE OR REPLACE FUNCTION process_cue_audit() RETURNS TRIGGER AS $$ BEGIN IF (TG_OP = 'DELETE') THEN INSERT INTO AUDITORIA SELECT 'D', now(), user, OLD.*; RETURN OLD; ELSIF (TG_OP = 'UPDATE') THEN INSERT INTO AUDITORIA SELECT 'U', now(), user, NEW.*; RETURN NEW; ELSIF (TG_OP = 'INSERT') THEN INSERT INTO AUDITORIA SELECT 'I', now(), user, NEW.*; RETURN NEW; END IF; END; $$ LANGUAGE plpgsql;

Ahora se crea el trigger sobre la tabla CUENTAS que se activa cada que se inserta, actualiza o elimina una (o varias) fila(s) en CUENTAS: CREATE TRIGGER cue_audit AFTER INSERT OR UPDATE OR DELETE ON CUENTAS FOR EACH ROW EXECUTE PROCEDURE process_cue_audit();

Algunos ejemplos: bdi00=> insert into cuentas (saldo,cli_id) values (3500,2); INSERT 0 1 bdi00=> select * from auditoria; operacion | fechaupd | usuario | id | saldo | cli_id | fecha_sobregiro | intereses -----------+----------------------------+---------+----+-------+--------+-----------------+---------- I | 2010-04-26 10:30:18.081372 | bdi00 | 6 | 3500 | 2 | | 0 (1 row) bdi00=> update cuentas set fecha_sobregiro = now() where id =1; UPDATE 1 bdi00=> select * from auditoria; operacion | fechaupd | usuario | id | saldo | cli_id | fecha_sobregiro | intereses -----------+----------------------------+---------+----+-------+--------+-----------------+---------- I | 2010-04-26 10:30:18.081372 | bdi00 | 6 | 3500 | 2 | | 0

140

U | 2010-04-26 10:31:23.2547 | bdi00 | 1 | 4067 | 1 | 2010-04-26 | 0 (2 rows)

Validación Los triggers son muy útiles cuando se requieren hacer validaciones que que no se pueden realizar con las restricciones de integridad referencial (PK, FK, UK,CK). Por ejemplo, asume que un cliente por política del banco, no puede tener más de 3 cuentas activas. Cada que se inserta una nueva cuenta, o se actualiza el titular de la cuenta, se debe verificar que no se incumple esta propiedad. Primero implementamos la función: /* Funcion que verifica que el numero de cuentas de un cliente no sea mayor a 3 */ CREATE OR REPLACE FUNCTION FUN_CK_NUM_CLIENTES() RETURNS TRIGGER AS $$ DECLARE NUM_CUENTAS INT; BEGIN IF (TG_OP = 'UPDATE' AND OLD.CLI_ID <> NEW.CLI_ID ) OR TG_OP='INSERT' THEN SELECT COUNT(*) INTO NUM_CUENTAS FROM CUENTAS WHERE CLI_ID = NEW.CLI_ID; IF NUM_CUENTAS >= 3 THEN RAISE EXCEPTION 'LIMITE DEL NUMERO DE CUENTAS ALCANZADO POR EL CLIENTE'; ELSE RETURN NEW; END IF; ELSE RETURN NEW; END IF; END; $$ LANGUAGE plpgsql;

/* Creacion del Trigger */ CREATE TRIGGER TRG_NUM_CLIENTES AFTER INSERT OR UPDATE ON CUENTAS FOR EACH ROW EXECUTE PROCEDURE FUN_CK_NUM_CLIENTES();

Valores Calculados Los triggers también pueden ser utilizados para mantener valores calculados. Por ejemplo, asumamos que en la tabla CLIENTES se desea mantener el acumulado en dinero que tiene el cliente en sus distintas cuentas. Para esto, modificamos la tabla: ALTER TABLE CLIENTES ADD COLUMN SALDO REAL DEFAULT 0.0;

Como no habíamos implementado el trigger que se encargara de llevar este saldo, por la primera vez lo calculamos “manualmente”: CREATE OR REPLACE FUNCTION UPDATE_SALDOS() RETURNS VOID AS $$

141

DECLARE CLIENTE_SALDO REAL; CLIENTE_ID REAL; BEGIN FOR CLIENTE_ID, CLIENTE_SALDO IN SELECT CLI_ID, SUM(SALDO) FROM CUENTAS GROUP BY CLI_ID LOOP UPDATE CLIENTES SET SALDO=CLIENTE_SALDO WHERE ID = CLIENTE_ID; END LOOP; RETURN; END;$$ LANGUAGE plpgsql;

Ahora, por cada modificación del saldo en una cuenta, actualizamos el saldo en el cliente: CREATE OR REPLACE FUNCTION SET_SALDO_CLIENTE() RETURNS TRIGGER AS $$ BEGIN IF(TG_OP = 'INSERT') THEN UPDATE CLIENTES SET SALDO = SALDO + NEW.SALDO WHERE ID = NEW.CLI_ID; RETURN NEW; ELSIF (TG_OP = 'UPDATE') THEN IF NEW.CLI_ID = OLD.CLI_ID THEN UPDATE CLIENTES SET SALDO = SALDO + NEW.SALDO - OLD.SALDO WHERE ID = NEW.CLI_ID; RETURN NEW; ELSE /* LA CUENTA CAMBIO DE TITULAR */ UPDATE CLIENTES SET SALDO = SALDO - OLD.SALDO WHERE ID = OLD.CLI_ID; UPDATE CLIENTES SET SALDO = SALDO + NEW.SALDO WHERE ID = NEW.CLI_ID; RETURN NEW; END IF; END IF; RETURN NEW; END; $$ LANGUAGE plpgsql;

/* Creacion del Trigger */ CREATE TRIGGER TRG_SALDO_CLIENTES AFTER INSERT OR UPDATE ON CUENTAS FOR EACH ROW EXECUTE PROCEDURE SET_SALDO_CLIENTE();

Realice algunas operaciones sobre la tabla CUENTAS (cambiando el titular y/o el saldo) para verificar que la cuenta se lleva correctamente.

Manejo de Errores Es posible lanzar y capturar errores de la siguiente manera: CREATE OR REPLACE FUNCTION TEST(int, int) RETURNS INT AS $$ DECLARE X INT; BEGIN X := $1/$2; RETURN X; EXCEPTION WHEN division_by_zero THEN

142

RAISE EXCEPTION 'Division por cero'; RETURN 0; WHEN others THEN RAISE NOTICE 'Error'; RETURN 0; END; $$ LANGUAGE plpgsql;

Ejemplo de uso: bdi00=> select test(6,2); test ------ 3 (1 row) bdi00=> select test(6,0); NOTICE: Division por cero test ------ 0 (1 row)

Ejercicio Red Social Asuma el siguiente esquema de bases de datos:

usuarios(*id,nombre,email, num_amigos) amigos(*id_usr1, *id_usr2) invitaciones(*id, fecha, *id_usr1, *id_usr2, mensaje, estado)

Cuando un usuario quiere ser amigo de otro, debe enviar una invitación. Los estados de la invitación pueden ser “pendiente”, “aceptado”, “rechazado”.

1. Cree el script de tablas 2. Implemente un procedimiento que cada que se acepte una invitación, se

adicione la relación de amistad en la tabla amigos. 3. Por cada nuevo amigo se debe actualizar el número de amigos en el

campo USUARIOS.NUM_AMIGOS 4. La relación de amigos se asume que es simétrica, es decir, si A es amigo

de B entonces B es amigo de A. Realice un trigger que evite la inserción de (A,B) en la tabla AMIGOS si la tuple (B,A) ya se encuentra. Además, se deben rechazar invitaciones de A,B si A,B ya son amigos (o B,A ya son amigos)

5. Adicione un trigger que rechace nuevas invitaciones de A a B si B ha rechazado previamente la invitación.

Solución Ejemplo de la Red Social con validaciones:

143

CREACION DE TABLAS

CREATE TABLE USUARIOS ( ID SERIAL NOT NULL, NOMBRE VARCHAR(20), EMAIL VARCHAR(20), NUM_AMIGOS INT DEFAULT 0);

CREATE TABLE AMIGOS( USR1_ID INT NOT NULL, USR2_ID INT NOT NULL);

CREATE TABLE INVITACIONES ( ID SERIAL NOT NULL, FECHA TIMESTAMP DEFAULT NOW(), USR1_ID INT NOT NULL, USR2_ID INT NOT NULL, ESTADO CHAR(1) DEFAULT 'P');

RESTRICCIONES

ALTER TABLE USUARIOS ADD CONSTRAINT USR_PK PRIMARY KEY(ID); ALTER TABLE AMIGOS ADD CONSTRAINT AMI_PK PRIMARY KEY(USR1_ID,USR2_ID); ALTER TABLE INVITACIONES ADD CONSTRAINT INV_PK PRIMARY KEY(ID);

ALTER TABLE AMIGOS ADD CONSTRAINT AMI_USR_1_FK FOREIGN KEY (USR1_ID) REFERENCES USUARIOS (ID); ALTER TABLE AMIGOS ADD CONSTRAINT AMI_USR_2_FK FOREIGN KEY (USR2_ID) REFERENCES USUARIOS (ID); ALTER TABLE INVITACIONES ADD CONSTRAINT INV_USR_1_FK FOREIGN KEY (USR1_ID) REFERENCES USUARIOS (ID);

ALTER TABLE INVITACIONES ADD CONSTRAINT INV_ESTADO_CK CHECK (ESTADO IN ('P','A','R'));

FUNCION ADD_FRIENDSHIP

Adiciona la relación de amistad si se acepta la invitación. Se asume que los dos usuarios no son amigos

CREATE OR REPLACE FUNCTION ADD_FRIENDSHIP() RETURNS TRIGGER AS $$ BEGIN IF NEW.ESTADO = 'A' THEN INSERT INTO AMIGOS (USR1_ID,USR2_ID) VALUES (NEW.USR1_ID,NEW.USR2_ID); END IF; RETURN NEW; END; $$ LANGUAGE PLPGSQL;

CREATE TRIGGER TRG_ADD_FRIENDSHIP AFTER UPDATE ON INVITACIONES FOR EACH ROW EXECUTE PROCEDURE ADD_FRIENDSHIP();

FUNCION UPDATE_NUM_AMIGOS

144

Actualiza el número de amigos de un usuario

CREATE OR REPLACE FUNCTION UPDATE_NUM_AMIGOS() RETURNS TRIGGER AS $$ BEGIN IF TG_OP = 'INSERT' THEN /* SE DEBEN ACTUALIZAR AMBOS USUARIOS PORQUE LA RELACION ES SIMETRICA */ UPDATE USUARIOS SET NUM_AMIGOS=NUM_AMIGOS+1 WHERE ID IN (NEW.USR1_ID,NEW.USR2_ID); ELSEIF TG_OP = 'DELTE' THEN UPDATE USUARIOS SET NUM_AMIGOS=NUM_AMIGOS-1 WHERE ID IN (NEW.USR1_ID,NEW.USR2_ID); END IF; RETURN NEW; END; $$ LANGUAGE PLPGSQL;

CREATE TRIGGER TRG_UPDATE_NUM_AMIGOS AFTER INSERT ON AMIGOS FOR EACH ROW EXECUTE PROCEDURE UPDATE_NUM_AMIGOS();

FUNCION CHECK_SIMETRIA

Verifica que no se inserte la relación de amigos B,A si A,B ya son amigos

CREATE OR REPLACE FUNCTION CHECK_SIMETRIA() RETURNS TRIGGER AS $$ DECLARE NUM INT; BEGIN IF TG_OP = 'INSERT' THEN SELECT COUNT(*) INTO NUM FROM AMIGOS WHERE USR2_ID = NEW.USR1_ID AND USR1_ID = NEW.USR2_ID; IF NUM >0 THEN RAISE EXCEPTION 'YA EXISTE LA RELACION DE AMIGOS ENTRE % Y %',NEW.USR2_ID,NEW.USR1_ID; END IF; ELSIF TG_OP = 'UPDATE' THEN SELECT COUNT(*) INTO NUM FROM AMIGOS WHERE USR2_ID = NEW.USR1_ID AND USR1_ID = NEW.USR2_ID; IF NUM >0 THEN RAISE EXCEPTION 'YA EXISTE LA RELACION DE AMIGOS ENTRE % Y %',NEW.USR2_ID,NEW.USR1_ID; END IF; END IF; RETURN NEW; END; $$ LANGUAGE PLPGSQL;

CREATE TRIGGER TRG_CHECK_SIMETRIA BEFORE UPDATE OR INSERT ON AMIGOS FOR EACH ROW EXECUTE PROCEDURE CHECK_SIMETRIA();

FUNCIONE CHECK_INVITACION

Verifica que no se inserte una nueva invitación que haya sido previamente rechazada o que las personas ya son amigas

CREATE OR REPLACE FUNCTION CHECK_INVITACION() RETURNS TRIGGER AS $$ DECLARE NUM INT; BEGIN IF TG_OP = 'INSERT' THEN

145

-- Verificar que ya no exista la relacion entre los dos usuarios SELECT COUNT(*) INTO NUM FROM AMIGOS WHERE (USR1_ID = NEW.USR1_ID AND USR2_ID = NEW.USR2_ID) OR (USR2_ID = NEW.USR1_ID AND USR1_ID = NEW.USR2_ID); IF NUM >0 THEN RAISE EXCEPTION 'YA EXISTE LA RELACION DE AMIGOS ENTRE % Y %',NEW.USR1_ID,NEW.USR2_ID; END IF; -- Verificar que no exista una invitacion para la misma relacion SELECT COUNT(*) INTO NUM FROM INVITACIONES WHERE ESTADO <> 'R' AND ( (USR1_ID = NEW.USR1_ID AND USR2_ID = NEW.USR2_ID) OR (USR2_ID = NEW.USR1_ID AND USR1_ID = NEW.USR2_ID) ); IF NUM >0 THEN RAISE EXCEPTION 'YA EXISTE UNA INVITACION ENTRE % Y %',NEW.USR1_ID,NEW.USR2_ID; END IF; -- Verificar que no exista una invitacion rechazada para la misma relacion SELECT COUNT(*) INTO NUM FROM INVITACIONES WHERE ESTADO = 'R' AND ( (USR1_ID = NEW.USR1_ID AND USR2_ID = NEW.USR2_ID) OR (USR2_ID = NEW.USR1_ID AND USR1_ID = NEW.USR2_ID) ); IF NUM >0 THEN RAISE EXCEPTION 'La relacion entre % Y % ya ha sido rechazada',NEW.USR1_ID,NEW.USR2_ID; END IF; ELSIF TG_OP = 'UPDATE' THEN -- Cualquier intento de actualizacion de los usuarios se rechaza IF NEW.USR1_ID <> OLD.USR1_ID OR NEW.USR2_ID <> OLD.USR2_ID THEN RAISE EXCEPTION 'Los atributos USR1_ID y USR2_ID no pueden ser modificados'; END IF; END IF; RETURN NEW; END; $$ LANGUAGE PLPGSQL;

CREATE TRIGGER TRG_CHECK_INVITACION BEFORE UPDATE OR INSERT ON INVITACIONES FOR EACH ROW EXECUTE PROCEDURE CHECK_INVITACION();

ALGUNAS PRUEBAS

INSERT INTO USUARIOS (NOMBRE, EMAIL) VALUES ('USUARIO 1','EMAIL1'), ('USUARIO 2','EMAIL2'), ('USUARIO 3','EMAIL3'), ('USUARIO 4','EMAIL4'), ('USUARIO 5','EMAIL5');

INSERT INTO INVITACIONES (USR1_ID, USR2_ID) VALUES (1,2);

La siguiente inserción es rechazada porque ya existe una invitación entre 2 y 1. Verificada por la FUNCION CHECK_SIMETRIA


Ahora se acepta la invitación

UPDATE INVITACIONES SET ESTADO = 'A' WHERE ID = 1;

Se pueden ver las actualizaciones en las tablas USUARIOS y AMIGOS

146

SELECT * FROM USUARIOS; SELECT * FROM AMIGOS;

Un intento de modificar los usuarios de la invitación conduce a un error:

UPDATE INVITACIONES SET USR1_ID = 3 WHERE ID = 1;

Una nueva invitación. La inserción es rechazada porque ya existe una invitación entre 3 y 1, Verificada por la FUNCION CHECK_SIMETRIA


Actualiza la Invitación

UPDATE INVITACIONES SET ESTADO = 'R' WHERE ID =2;

Ahora no se puede hacer una segunda petición de 1 a 3 o de 3 a 1 porque función FUNCION CHECK_SIMETRIA lo verifica.


Más información en el Manual de Postgres: http://www.postgresql.org/docs/9.2/interactive/plpgsql.html

147

Consultas Enumeradas Son consultas que retornan registros con una columna adicional que enumera todas las filas. En este ejemplo se hace uso de la base de datos DBAdmision poblada, el CASO DE ESTUDIO de la página 75 y se explica la consulta detalladamente. SUGERENCIA: Si has utilizado la base de datos dbadmision en ejercicios previos es mejor volver a crearla. Utilizar el Script: dbadmin.sql “…Crear una función que liste todos los postulantes , la lista debe estar enumerada y ordenada por apellido paterno, materno y nombres.”

Nº Apellidos y Nombres

1 Alanya Padilla Alina Susan

2 Alarcon Castro Gustavo Claudio Andres

3 Alarco Lama Ricardo Rafael

4 Altez Yañez Jazmín GApriela

5 Amable Salva Katerin Lisbet

6 Antonio Reyes Freddy Angel

7 Atachagua Cossio Jessica Roxana

8 Aucasi Huanca Angela Marine

9 Avila Salvador Pamely Lorena Kenny

10 Baldeón Balvín Olger

11 Baldeón SanApria Natalia Ivonne

… …

Primero debemos crear una función que nos retorne una tipo de dato numérico, la llamaremos rownumber, para ello también se realizará la creación temporal de un objeto SEQUENCE, cuyo nombre será el timestamp actual, por ejemplo: SELECT current_timestamp retorna algo como: “2009-11-17 06:18:53.057964-05″. La función nextval realizará el retorno del valor siguiente de dicha secuencia, lo que hará que por cada llamada que reciba el valor irá incementando de uno un uno.

148

CREATE OR REPLACE FUNCTION rownumber() RETURNS integer AS $$ BEGIN EXECUTE 'CREATE TEMP SEQUENCE "'||current_timestamp||'"'; RETURN nextval('"'||current_timestamp||'"'); EXCEPTION WHEN duplicate_table THEN RETURN nextval('"'||current_timestamp||'"'); END $$ LANGUAGE 'plpgsql';

La función anterior se creará por defecto en el esquema público. Como la función crea una secuencia temporal hace posible que al finalizar la ejecución de la consulta la secuencia se elimine y por lo tanto la numeración vuelva a reiniciarse en cada ejecución.

SELECT rownumber() "Numero",* FROM (SELECT PC.Paterno || ' ' || PC.Materno || ' ' || PC.Nombres "Apellidos y Nombres" FROM Persona.Contacto PC INNER JOIN Persona.Postulante PP ON PP.IDContacto=PC.IDContacto ORDER BY PC.Paterno,PC.Materno,PC.Nombres) AS tb1

A partir de la versión 8.4 de PostgreSQL, podemos ahorrarnos esfuerzos para construir esta consulta, ya que en ésta versión si exíste la función row_number, aunque habrá que hacer uso de Window Functions (Funciones Ventanas) que se ven más adelante.

149

Consultas, Funciones, Procedimientos y Vistas Con la base datos poblada y el CASO DE ESTUDIO de la página 75 podemos comenzar a elaborar nuestras primeras consultas usando instrucciones DML. Se crean funciones cuando se trata de retornar datos, como por ejemplo una consulta que utilice la cláusula SELECT, y procedimientos almacenados únicamente cuando se trata de realizar una operación que no retorne ningún dato, por ejemplo, INSERT, UPDATE o DELETE, aunque en Postgres también se puede retornar datos con procedimientos almacenados, sería ideal respetar la funcionalidad de cada uno. Las funciones desarrolladas se realizará haciendo uso del lenguaje procedural 'plpgsql', que generalmente trae PostgreSQL. SUGERENCIA: Si has utilizado la base de datos dbadmision en ejercicios previos es mejor volver a crearla. Utilizar el Script: dbadmin.sql CONSULTAS:

1) Crear una consulta que devuelva los siguientes d atos:

Periodo 101 309 310 Total

2005-1 1 7 7 15

2005-2 0 13 12 25

2006-1 0 17 18 35

2006-2 0 23 22 45

2007-1 0 27 28 55

SELECT idperacad AS "Periodo" ,SUM(CASE WHEN IDCarrera = '101' THEN 1 ELSE 0 END ) AS "101" ,SUM(CASE WHEN IDCarrera = '309' THEN 1 ELSE 0 END ) AS "309" ,SUM(CASE WHEN IDCarrera = '310' THEN 1 ELSE 0 END ) AS "310" ,COUNT(*) AS "TOTAL" FROM Persona.Postulante GROUP BY IDPerAcad ORDER BY IDPerAcad

2) Crear una consulta que devuelva los siguientes d atos:

Periodo 101 309 310 Total

2005-1 1 7 7 15

150

2005-2 0 13 12 25

2006-1 0 17 18 35

2006-2 0 23 22 45

2007-1 0 27 28 55

TOTAL 1 87 87 175 SELECT idperacad AS "Periodo" ,SUM(CASE WHEN IDCarrera = '101' THEN 1 ELSE 0 END ) AS "101" ,SUM(CASE WHEN IDCarrera = '309' THEN 1 ELSE 0 END ) AS "309" ,SUM(CASE WHEN IDCarrera = '310' THEN 1 ELSE 0 END ) AS "310" ,COUNT(*) AS "TOTAL" FROM Persona.Postulante GROUP BY IDPerAcad UNION SELECT 'TOTAL' AS "Periodo" ,SUM(CASE WHEN IDCarrera = '101' THEN 1 ELSE 0 END ) AS "101" ,SUM(CASE WHEN IDCarrera = '309' THEN 1 ELSE 0 END ) AS "309" ,SUM(CASE WHEN IDCarrera = '310' THEN 1 ELSE 0 END ) AS "310" ,COUNT(*) AS total FROM Persona.Postulante

FUNCIONES:

3) Crear una función que liste todos los postulante s, la lista debe estar enumerada y ordenada por apellido paterno, materno y nombres.





… …

CREATE OR REPLACE FUNCTION f_ejemplo_4() RETURNS SETOF "record" AS $BODY$ DECLARE r RECORD; BEGIN FOR r IN SELECT rownumber() "Numero",* FROM (SELECT PC.Paterno || ' ' || PC.Materno || ' ' || PC.Nombres "Apellidos y Nombres" FROM Persona.Contacto PC INNER JOIN Persona.Postulante PP ON PP.IDContacto=PC.IDContacto ORDER BY PC.Paterno,PC.Materno,PC.Nombres) AS tb2 LOOP RETURN NEXT r;

151

END LOOP; RETURN; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; --SELECT * FROM f_ejemplo_4() AS ("Numero" INTEGER, "Apellidos y Nombres" TEXT);

CREATE OR REPLACE FUNCTION rownumber() RETURNS integer AS $$ BEGIN EXECUTE 'CREATE TEMP SEQUENCE "'||current_timestamp||'"'; RETURN nextval('"'||current_timestamp||'"'); EXCEPTION WHEN duplicate_table THEN RETURN nextval('"'||current_timestamp||'"'); END $$ LANGUAGE 'plpgsql';

PROCEDIMIENTOS ALMACENADOS: 4) Crear un procedimiento almacenado que permita el iminar los postulantes registrados correspondientes a una modalidad.

--SELECT * FROM Persona.Postulante ORDER BY IDModalidad; CREATE OR REPLACE FUNCTION sp_ejemplo_12(p_IDModalidad integer) RETURNS integer AS $$ BEGIN DELETE FROM Persona.Postulante WHERE IDModalidad=p_IDModalidad; return p_IDModalidad; END; $$ LANGUAGE plpgsql; --SELECT sp_ejemplo_12(2); --SELECT * FROM Persona.Postulante ORDER BY IDModalidad;

VISTAS:

5) Crear una vista que muestre un resumen como el s iguiente:

Nombre 2005-1 2005-2 2006-1 2006-2 2007-1 TOTAL

Administración 5 8 12 14 23 62

Contabilidad 6 7 13 17 22 65

Ingeniería Informática

0 0 0 0 0 0

TOTAL 11 15 25 31 45 127

152

CREATE OR REPLACE VIEW v_ejemplo_11 AS ( SELECT AC.Nombre,SUM(CASE WHEN PP.Ingreso='1' AND PP.IDPerAcad IN ('2005-1') THEN 1 ELSE 0 END) "2005-1", SUM(CASE WHEN PP.Ingreso='1' AND PP.IDPerAcad IN ('2005-2') THEN 1 ELSE 0 END) "2005-2", SUM(CASE WHEN PP.Ingreso='1' AND PP.IDPerAcad IN ('2006-1') THEN 1 ELSE 0 END) "2006-1", SUM(CASE WHEN PP.Ingreso='1' AND PP.IDPerAcad IN ('2006-2') THEN 1 ELSE 0 END) "2006-2", SUM(CASE WHEN PP.Ingreso='1' AND PP.IDPerAcad IN ('2007-1') THEN 1 ELSE 0 END) "2007-1", SUM(CASE WHEN PP.Ingreso='1' AND PP.IDPerAcad IN ('2005-1','2005-2','2006-1','2006-2','2007-1') THEN 1 ELSE 0 END) "TOTAL" FROM Persona.Contacto PC INNER JOIN Persona.Postulante PP ON PP.IDContacto=PC.IDContacto INNER JOIN Admision.Carrera AC ON AC.IDCarrera=PP.IDCarrera GROUP BY AC.Nombre UNION SELECT 'TOTAL',SUM(CASE WHEN PP.Ingreso='1' AND PP.IDPerAcad IN ('2005-1') THEN 1 ELSE 0 END) "2005-1", SUM(CASE WHEN PP.Ingreso='1' AND PP.IDPerAcad IN ('2005-2') THEN 1 ELSE 0 END) "2005-2", SUM(CASE WHEN PP.Ingreso='1' AND PP.IDPerAcad IN ('2006-1') THEN 1 ELSE 0 END) "2006-1", SUM(CASE WHEN PP.Ingreso='1' AND PP.IDPerAcad IN ('2006-2') THEN 1 ELSE 0 END) "2006-2", SUM(CASE WHEN PP.Ingreso='1' AND PP.IDPerAcad IN ('2007-1') THEN 1 ELSE 0 END) "2007-1", SUM(CASE WHEN PP.Ingreso='1' AND PP.IDPerAcad IN ('2005-1','2005-2','2006-1','2006-2','2007-1') THEN 1 ELSE 0 END) "TOTAL" FROM Persona.Contacto PC INNER JOIN Persona.Postulante PP ON PP.IDContacto=PC.IDContacto INNER JOIN Admision.Carrera AC ON AC.IDCarrera=PP.IDCarrera ); --SELECT * FROM v_ejemplo_11;

153

Funciones Ventana (Window Functions) Las funciones ventana vienen incorporadas a partir de la versión 8.4 de PostgreSQL. Las funciones ventana permiten tener más variedad en al momento de realizar consultas y en otros casos se puede optimizar como se describirá a continuación. En el tema referente a Consultas Enumeradas (Página 147) se explica que es necesario crear una función extra para poder realizar la enumeración de las filas en una consulta, pues hacer esto con las funciones ventana es mucho más sencillo. SUGERENCIA: Si has utilizado la base de datos dbadmision en ejercicios previos es mejor volver a crearla. Utilizar el Script: dbadmin.sql 1) Crear una función que liste todos los postulante s, la lista debe estar

enumerada y ordenada por apellido paterno, materno y nombres.





… … SELECT row_number() OVER (ORDER BY PC.Paterno,PC.Materno,PC.Nombres) AS "N°" ,PC.Paterno || ' ' || PC.Materno || ' ' || PC.Nombres AS "Apellidos y Nombres" FROM Persona.Contacto PC INNER JOIN Persona.Postulante PP ON PP.IDContacto=PC.IDContacto

Si pudieron darse cuenta, a partir de la version 8.4 ya se puede hacer uso de la función row_number(), pero nunca de manera independiente, éste necesita de la cláusula OVER para ser ejecutada. Otra de las cosas que nos ahorramos hacer en este ejemplo en comparación con el desarrollado en el post anterior, es que ahora no necesitamos crear una consulta anidada.

154

2) Crear una función que liste todos los postulante s de un determinado periodo académico. La lista debe reiniciar la numer ación por orden alfabético.




… …

1 Baldeón Balvín Olger

2 Baldeón Sanabria Natalia Ivonne

… …

1 Cachuán Cámac Miguel Fernando

2 Cajachagua Chui Jose Arturo

… … SELECT row_number() OVER (PARTITION by substring(PC.Paterno FROM 1 FOR 1) ORDER BY PC.Paterno,PC.Materno,PC.Nombres) AS "N°" ,PC.Paterno || ' ' || PC.Materno || ', ' || PC.Nombres AS "Apellidos y Nombres" FROM Persona.Contacto PC INNER JOIN Persona.Postulante PP ON PP.IDContacto=PC.IDContacto

Aquí hacemos lo mismo que en la primera consulta, pero en ésta hacemos la partición por el inicio de cada apellido, por ejemplo: substring(‘ABCDEF’ from 1 for 1), retorna solo A., de esa manera la numeración se volverá a realizar cuando el inicio del apellido sea diferente al anterior. En conclusión, lo que hace un Window Function es ejecutar la consulta por cada segmento o grupo de filas, esto dá más poder a una consulta en comparación con los que se aplican implícitamente en todo el resultado.

3) Listar todos los postulantes que ingresaron con sus respectivos puntajes ordenado descendentemente y acompañado del puntaje promedio obtenido por cada carrera.

Carrera Postulantes Promedio Puntaje

Administración Cañari Rodriguez Jose Antonio 73.065 97

Administración Huamán Huamaní Nidia Anais 73.065 93

Administración Ricapa Quispe Nilton César 73.065 89

… … … …

Contabilidad Benito Dionisio Melissa 74.369 98

155

Katherine

Contabilidad Salazar Gutiérrez Enrique Paúl 74.369 98

Contabilidad Sulca Palomino Ivan 74.369 98

… … … … SELECT AC.Nombre AS "Carrera" ,PC.Paterno || ' ' || PC.Materno || ' ' || PC.Nombres AS "Postulantes" ,round(AVG(PP.Puntaje) OVER (PARTITION BY PP.idcarrera),3) AS "Promedio" ,PP.Puntaje AS "Puntaje" FROM Persona.Contacto PC INNER JOIN Persona.Postulante PP ON PP.IDContacto=PC.IDContacto INNER JOIN Admision.Carrera AC ON PP.IDCarrera=AC.IDCarrera WHERE PP.Ingreso='1' ORDER BY AC.idcarrera, PP.puntaje DESC

También existe otro mecanismo para realizar las segmentaciones, utilizando explìcitamente la cláusula WINDOW y referenciando el campo donde se aplicará con la clàusula OVER. SELECT AC.Nombre AS "Carrera" ,PC.Paterno || ' ' || PC.Materno || ' ' || PC.Nombres AS "Postulantes" ,ROUND(AVG(PP.Puntaje) OVER w ,3)AS "Promedio" ,PP.Puntaje AS "Puntaje" FROM Persona.Contacto PC INNER JOIN Persona.Postulante PP ON PP.IDContacto=PC.IDContacto INNER JOIN Admision.Carrera AC ON PP.IDCarrera=AC.IDCarrera WHERE PP.Ingreso='1' WINDOW w AS (PARTITION BY PP.idcarrera) ORDER BY AC.idcarrera, PP.puntaje DESC

Esta última consulta retornará exactamente lo mismo que en su forma anterior.

156

Automatización de Backups SUGERENCIA: Si has utilizado la base de datos dbadmision en ejercicios previos es mejor volver a crearla. Utilizar el Script: dbadmin.sql El proceso de creación de backups en postgres se realiza por medio de pg_dump, y la automatización para su ejecución se puede lograr por medio de herramientas como las que se incluye en PGADMIN haciendo uso de los jobs, steps y schedules o mediante el sistema operativo, como veremos en este ejemplo. 1- CREACIÓN DE SCRIPT Script para Linux: pg_backup.sh

1. #!/bin/bash 2. #————-Por: sAfOrAs—————- 3. PG_BIN='/usr/bin/pg_dump' 4. PG_HOST='localhost' 5. PG_PORT='5432' 6. PG_DATABASE='dbadmision' 7. PG_USER='postgres' 8. export PGPASSWORD='postgres' 9. PG_PATH='/var/PGBACKUP' 10. FECHAYHORA=$(date + "%d-%m-%Y-%H-%M-%S") 11. PG_FILENAME= "${PG_PATH}/${PG_DATABASE}-${FECHAYHORA}.backup" 12. ${PG_BIN} -i -h ${PG_HOST} -p ${PG_PORT} -U ${PG_USER} -F c -b -v -f

${PG_FILENAME} ${PG_DATABASE}

Estableciendo Permisos para ejecutar el Script:

1. # chmod +x pg_backup.sh

Cambiando de propietario del script y directorios donde se guardarán los backups, el propietario debe ser el usuario postgres.

2. # chown postgres:postgres pg_backup.sh --Script 3. # chown -R postgres:postgres /var/PGBACKUP --Directorio de Backups

Script para Windows: pg_backup.bat

1. @echo off 2. ::————-Por: sAfOrAs—————- 3. SET PG_BIN=C:\PostgresPlus\8.3R2AS\postgresstudio\pg_dump.exe 4. SET PG_HOST=localhost 5. SET PG_PORT=5432 6. SET PG_DATABASE=dbadmision

157

7. SET PG_USER=postgres 8. SET PGPASSWORD=postgres 9. SET PG_PATH=C:\PGBACKUP\ 10. SET FECHAYHORA=%date:/=-%-%time:~0,8% 11. SET FECHAYHORA=%FECHAYHORA::=-% 12. SET FECHAYHORA=%FECHAYHORA: =0% 13. SET PG_FILENAME=%PG_PATH%\%PG_DATABASE%-%FECHAYHORA%.backup 14. %PG_BIN% -i -h %PG_HOST% -p %PG_PORT% -U %PG_USER% -F c -b -v -f %PG_FILENAME%

%PG_DATABASE%

Se debe establecer permisos de ejecución del script y derechos del usuario postgres sobre los directorios donde se almacenarán los backups y sobre el script. 2- AUTOMATIZACIÓN: Automatización en linux: Por medio de crones.

15. # crontab -e 16. 17. 0 0 * 12 * /home/usuario/pg_backup.sh 18. 19. <<Guardar>> CTRL + O 20. <<Salir>> CRTL + X

Este script ejecuta pg_backup.sh del siguiente modo:

0: Minutos (00) 0: Horas (00) *:Dias del mes (todos) 12 Mes (Diciembre) * Dias de la semana (L,M,M,J,V,S,D)

Por lo tanto se ejecutará todos los dias de la semana durante el mes de Diciembre a las 00hh:00mm (Media Noche). Automatización en Windows: Con taskschd – CONTROL SCHEDTASK o AT SOBRE LAS VARIABLES DEL SCRIPT:

Variable descripción PG_BIN Ubicacion de pg_dump, es el binario que realiza los backups PG_HOST dirección del host PG_PORT puerto del servidor PG_DATABASE nombre de la base de datos PG_USER nombre del usuario

PGPASSWORD Contraseña del servidor: Es una variable especial registrada en el sistema, el nombre de esta variable debe permanecer inmutable en el sistema operativo.

158

Bibliografia http://www.postgresql.org/ http://www.postgresql.org/docs/ http://www.postgresql.org/about/casestudies/ http://www.postgresql.org/docs/7.3/interactive/diskusage.html http://www.postgresql.org.es/node/667 http://www.pgadmin.org/ http://es.wikipedia.org/wiki/Anexo:Comparaci%C3%B3n_de_sistemas_administradores_de_bases_de_datos_relacionales http://www.davidghedini.com/pg/entry/install_postgresql_9_on_centos http://www.if-not-true-then-false.com/2012/install-postgresql-on-fedora-centos-red-hat-rhel/ http://blog.neobytec.com/instalando-postgresql-en-centos-6-2-6-3/

http://phenobarbital.wordpress.com/2012/07/24/postgresql-una-instalacion-de-postgresql-basica-pero-mejor/ http://tuxapuntes.com/instalar-postgresql-en-centos-6-3/ http://cic.puj.edu.co/wiki/doku.php?id=materias:taller_pl_pgsql http://help.arcgis.com/es/arcgisdesktop/10.0/help/index.html#//001s00000001000000 http://wiki.centos.org/es/HowTos/Network/IPTables

Memorias PostgreSQL Guia Practica

Documents

Transcript of Memorias PostgreSQL Guia Practica