Memorias PostgreSQL Guía Práctica
ING. SERGIO ARBEY MONSALVE BETANCUR
Medellín-Colombia, 1 de Junio del 2013
[email protected]
1
Indice Índice Que es PostgreSQL Historia y Antecedentes Ventajas de PostgreSQL Características de PostgreSQL Herramientas de PostgreSQL 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 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 Instalar y Configurar el PostgreSQL 9.x Instalación del Servidor de Bases de Datos PostgreSQL P ostgreSQL 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 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
2 5 6 7 8 13 15 15 15 16 17 17 18 19 20 21 22 22 23 23 23 23 23 25 27 28 28 32 38 38 41 42 43 43 43 45 46 48 50 50 51
2
Indice Índice Que es PostgreSQL Historia y Antecedentes Ventajas de PostgreSQL Características de PostgreSQL Herramientas de PostgreSQL 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 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 Instalar y Configurar el PostgreSQL 9.x Instalación del Servidor de Bases de Datos PostgreSQL P ostgreSQL 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 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
2 5 6 7 8 13 15 15 15 16 17 17 18 19 20 21 22 22 23 23 23 23 23 25 27 28 28 32 38 38 41 42 43 43 43 45 46 48 50 50 51
2
Indice Gestión de Bases de Datos PostgreSQL PSQL: La pequeña gran herramienta de PostgreSQL Ejecutando psql desde el sistema operativo Ejecutando psql 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 Administración de sesiones Inicio y Tipos de sesiones Control de sesiones Caso de Estudio Sentencias DDL CREATE ALTER ….. ADD ALTER ….. DROP DROP TRUNCATE Sentencias DML SELECT INSERT UPDATE DELETE Sentencias TCL COMMIT SAVEPOINT RELEASE SAVEPOINT ROLLBAK Instalar Script Caso de Estudio - Continuación Sentencias DCL GRANT REVOKE Sentencias XML
54 54 55 58 61 62 63 64 64 66 67 68 69 71 71 71 75 76 76 85 89 92 95 96 96 104 105 106 109 109 109 110 111 114 117 119 119 120 128
3
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
IN Triggers Validación Valores Calculados Manejo de Errores Ejercicio Red Social Consultas Enumeradas Consultas, Funciones, Procedimientos y Vistas Consultas Funciones Procedimientos Vistas Funciones Ventana (Windows Functions) Automatización de Backups Bibliografia
131 131 131 132 132 132 133 135 136 138 138 140 140 141 142 147 149 149 150 151 151 153 156 158
4
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
5
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 6
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/
7
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 8
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).
9
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 W ORK. 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. 10
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 11
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.
12
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.
13
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.
14
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 Adaptive Server Sybase/iAnywhere Anywhere Adaptive Server Sybase Inc Enterprise ANTs Data Server ANTs Software DB2 IBM
Fecha de la primera Última versión versión pública estable
Licencia software
1992
10.0
Propietario
1987
15.0
Propietario
1999 1982
3.6 9
de
[Firebird]
Firebird Foundation
25 de julio de 2000
2.1
Informix HSQLDB
1985 2001
10.0 1.9
1980
2006
CA-TOSL
InterBase
Informix Software Hsqldb.Org Berkeley University, Computer Associates Borland
Propietario Propietario Licencia Pública InterBase Propietario Licencia BSD
1985
7.5.1
SapDB
SAP AG
?
MaxDB Microsoft SQL Server MySQL Oracle
MySQL AB, SAP AG ? Microsoft 1989 MySQL AB Noviembre de 1996 Oracle Corporation 1977 PostgreSQL Global Junio de 1989 Development Group SmallSQL 16 de abril de 2005 D. Richard Hipp 17 de agosto de 2000
Ingres
PostgreSQL SmallSQL SQLite
Propietario GPL con 7.4 drivers LGPL 7.7 GPL o propietario 2008 Propietario 5.0 GPL o propietario 11g Release 2 Propietario 9.0
Licencia BSD
0.12 3.6.16 Última Fecha de la primera versión versión pública estable
Creador
LGPL Dominio público Licencia software
de
Soporte del sistema operativo Adaptive Server Enterprise ANTs Data Server DB2 [[Firebird]]
Windows
Mac OS X
Linux
BSD
Unix
Sí
Sí
Sí
Sí
Sí
Sí Sí Sí
Sí Sí Sí
Sí Sí Sí
Sí Sí Sí
Sí Sí Sí
z/OS No ?
Sí Quizá
15
Particionamiento Información acerca de que métodos de particionamiento son soportados nativamente. Rango Adaptive Server AA Enterprise ANTs Data Server Sí DB2 Sí Firebird HSQLDB Informix Ingres InterBase SapDB MaxDB Microsoft SQL Server
Compuesto (Rango+Hash)
Lista
AA
AA
AA
No
Sí Sí
Sí Sí
Sí Sí
No
No
No
?
?
?
?
?
?
?
?
Sí
Sí
Sí
Sí
No
No
No
No
? ?
? ?
Sí
MySQL Oracle
Sí Sí
PostgreSQL
Sí
SQLite
Hash
Sí Rango
? ?
No Sí Sí No Sí Hash
? ?
No Sí Sí No Sí Compuesto (Rango+Hash)
No Sí Sí Sí Sí Listas
19
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: La aplicación sobre la que trabaja el usuario Postmaster Procesos en el Servidor Un proceso demonio supervisor de la Base de Uno o más servidores de bases de datos Back End Control datos en segundo plano (servidor PostgreSQL) Front End
Procesos en el Cliente
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
20
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. 21
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
22
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: 23
• • • •
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. 24
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. 25
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 26
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.
27
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.26.noarch.rpm Si instalamos en CentOS 6 x32, necesitamos: 28
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. 2.
[root@server1 ~]# wget http: //yum.pgrpms.org/9.2/redhat/rhel-6-x6!6"/pgdge$tos92-9.2-6.$oarh.rpm --2%11-11-%1 %%:11:&%-http: //yum.pgrpms.org/9.2/redhat/rhel-6-x6!6"/pgdge$tos92-9.2-6.$oarh.rpm (esolv)$g yum.pgrpms.org... 9.129.19.11" *o$$et)$g to yum.pgrpms.org+9.129.19.11"+:%... o$$eted. , reuest se$t0 awa)t)$g respo$se... 2%% 3e$gth: &12" 4&.%5 [appl)at)o$/x-redhat- package-ma$ager] 7av)$g to: pgdg-e$tos92-9.2-6.$oarh.rpm
'. ". &. 6. . . 9. 1%%8[] &012" --.-/s in %s 1%. 11. 2%11-11-%1 %%:11:&1 4'1% ;
1.3 Excluir del CentOS, Fedora, Red Hat y Scientific 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. 2.
[root@server1 ~]# d /et/yum.repos.d [root@server1 ~]# v) *e$t7-
1. 2. '. ". &. 6. . . 9. 1%.
[=ase] .... .... exludepostgresl> # released updates [updates] .... .... exludepostgresl>
29
Fedora Adicionamos la clausula 'exclude = postgresql* ' en la sección [fedora] del archivo /etc/yum.repos.d/fedora.repo : 1. 2.
[root@server1 ~]# d /et/yum.repos.d [root@server1 yum.repos.d]# v) ?edora.repo
1. 2. '. ".
[?edora] .... .... exludepostgresl>
Adicionamos la clausula 'exclude = postgresql* ' en la sección [updates] del archivo /etc/yum.repos.d/fedora-updates.repo : 1. 1. 2. '. ".
[root@server1 yum.repos.d]# v) ?edora-updates.repo [updates] .... .... exludepostgresl>
Red Hat (RHEL) Adicionamos la clausula 'exclude = postgresql* ' en la sección [main] del archivo /etc/yum/pluginconf.d/rhnplugin.conf : 1. 2.
[root@server1 ~]# d /et/yum/plug)$o$?.d [root@server1 yum.repos.d]# v) rh$plug)$.o$?
1. 2. '. ".
[ma)$] .... .... exludepostgresl>
Scientific Linux (SL) Adicionamos la clausula 'exclude = postgresql* ' en las secciones [sl] y [slsecurity] del archivo /etc/yum.repos.d/sl.repo : 1. 2. 1. 2. '. ". &. 6. . . 9.
[root@server1 ~]# d /et/yum.repos.d [root@server1 yum.repos.d]# v) sl.repo [sl] .... .... exludepostgresl> # released sl-seur)ty [sl-seur)ty] .... ....
30
1%. exludepostgresl>
1.4 Instalar el Repositorio PostgreSQL 9.2. Ahora, instalamos el Repositorio CentOS 1.
[root@server1 ~]# rpm -) pgdg-e$tos92-9.2-6.$oarh.rpm
Fedora 1. [root@server1 ~]# rpm -) pgdg-?edora92-9.2-6.$oarh.rpm
Red Hat (RHEL) 1. [root@server1 ~]# rpm -) pgdg-redhat92-9.2-.$oarh.rpm
Scientific Linux (SL) 1. [root@server1 ~]# rpm -) pgdg-sl92-9.2-.$oarh.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. 2. '. ". &.
[root@server1 yum.repos.d]# rpm yum l)st postgres> .... .... postgresl92.x6!6" ....
1.6 Instalar el PostgreSQL 9.2 con YUM. Ahora podemos instalar el PostgreSQL usando yum: 1. 2. '. ". &. 6. . . 9. 1%. 11. 12.
[root@server1 ~]# yum )$stall postgresl92 postgresl92-devel postgresl92server postgresl92-l)=s postgresl92-o$tr)= .... $stalled: postgresl92.x6!6" %:9.2.6-1ABA.rhel6 postgresl92-devel.x6!6" %:9.2.6-1ABA.rhel6 postgresl92-l)=s.x6!6" %:9.2.6-1ABA.rhel6 postgresl92-server.x6!6" %:9.2.6-1ABA.rhel6 *ompleteC [root@server1 ~]#
31
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. 2. '.
[root@server1 ~]# serv)e postgresl-9.2 )$)td= $)t)al)D)$g data=ase: [ ] [root@server1 ~]#
Arrancar el Servidor Postgres 1. 2. '.
[root@server1 ~]# serv)e postgresl-9.2 start 7tart)$g postgresl-9.1 serv)e: [ ] [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. 2. '.
[ -? /et/pro?)le ] EE soure /et/pro?)le ABFF/ var/l)=/pgsl/9.2/data export ABFF
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. 2. '. ". &.
[ -? /et/pro?)le ] EE soure /et/pro?)le ABFF/ var/l)=/pgsl/9.2/data export ABFF F,GF,:G,;H/=)$:/usr/pgsl-9.2/=)$ export F,
32
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. 2. '. ". &. 6. .
I=ash-".1G psl postgres postgres sl 49.2.65 ype JhelpJ for help. postgres# alter user postgres with password KpostgresKL F3H( (3H 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. 2. '. ". &. 6. . . 9.
[root@server1 ~]# v) / var/l)=/pgsl/9.2/data/pg!h=a.o$? .... # MH BFF
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. '. ".
.... # MH BFF
;H,B
33
&. 6. . . 9.
host host # v6 host ....
all all 12.%.%.1/'2 md& all all %.%.%.%/% md& loal o$$et)o$s: all all ::1/12 md&
Se puede restringir el acceso al servidor postgres como se desee, así: host host
all test
all 10.20.4.0/24 md5 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. 2. '. ".
[root@server1 ~]# su - postgres -=ash-".1G pg!tl reload server s)g$aled -=ash-".1G
1. 2. '. ". &. 6. . . 9. 1%. 11.
-=ash-".1G psl postgres postgres psl 49.2.65 ype JhelpJ for help.
1. 2. '. ". &. 6. . .
-=ash-".1G psl postgres postgres - Jselet pg!reload!o$?45LJ assword for user postgres: pg!reload!o$? ---------------t 41 row5
postgres# selet pg!reload!o$?45L pg!reload!o$? ---------------t 41 row5 postgres#
-=ash-".1G
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.
#-----------------------------------------------------------------------------# *OOH*O7 FOB FN,HO*FO
34
'. ". &. 6. . . 9. 1%. 11.
#-----------------------------------------------------------------------------# - *o$$et)o$ 7ett)$gs #l)ste$!addresses KloalhostK # what address4es5 to l)ste$ o$L # omma-separated l)st o? addressesL # de?aults to KloalhostK0 K>K all # 4ha$ge reu)res restart5 #port &"'2 # 4ha$ge reu)res restart5
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. '. ". &. 6. . . 9. 1%. 11.
#-----------------------------------------------------------------------------# *OOH*O7 FOB FN,HO*FO #-----------------------------------------------------------------------------# - *o$$et)o$ 7ett)$gs l)ste$!addresses K>K # what address4es5 to l)ste$ o$L # omma-separated l)st o? addressesL # de?aults to KloalhostK0 K>K all # 4ha$ge reu)res restart5 port &"'2 # 4ha$ge reu)res restart5
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. '. ". &. 6. . . 9. 1%. 11.
#-----------------------------------------------------------------------------# *OOH*O7 FOB FN,HO*FO #-----------------------------------------------------------------------------# - *o$$et)o$ 7ett)$gs l)ste$!addresses K192.1.2.''K # what address4es5 to l)ste$ o$L # omma-separated l)st o? addressesL # de?aults to KloalhostK0 K>K all # 4ha$ge reu)res restart5 port &"'2 # 4ha$ge reu)res restart5
Si cambia el puerto, reinicie el servicio postgresql. 1. 2. '. ".
serv)e postgresl-9.2 restart 7topp)$g postgresl-9.2 serv)e: 7tart)$g postgresl-9.2 serv)e: [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. 35
Verifique si ha realizado cambios en listen_address o port : 1. 2. '. ". &. 6. . . 9. 1%. 11. 12. 1'. 1". 1&. 16. 1. 1.
-=ash-".1G psl assword: psl 49.2.65 ype JhelpJ for help. postgres# show l)ste$!addressesL l)ste$!addresses -----------------> 41 row5 postgres# show portL port -----&"'2 41 row5 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. 2. '. ". &. 6.
[root@server1 yum.repos.d]# psl postgres postgres assword for user postgres: psl 49.2.65 ype JhelpJ for help. postgres#
Crear un usuario: 1. 2.
postgres# reate user testuser with password KseretoKL *(HFH (3H
Crear una Base de datos y asignarle el usuario propietario: 1. 2.
postgres# reate data=ase test ow$ertestuserL *(HFH BFF
Conectar a la Base de Datos como usuario: 1. 2. '.
postgres# P test testuser assword for user testuser: Mou are $ow o$$eted to data=ase JtestJ as user JtestuserJ .
Crear una Tabla e Insertar Filas: 1. 2.
test reate ta=le testta=le 4ol1 varhar5L *(HFH F<3H
36
'. ".
test )$sert )$to testta=le values4 KhelloK5L O7H( % 1
Consultas en la Tabla 1. 2. '. ". &. 6. .
test selet > ?rom testta=leL ol1 ------hello 41 row5 test
Listar las Tablas de la Base de Datos test : 1. 2. '. ". &. 6.
test Pdt 3)st o? relat)o$s 7hema + Oame + ype + w$er --------Q-----------Q-------Q-------public + testta=le + ta=le + myuser 41 row5
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. '. ". &. 6. .
[root@server1 ~]# serv)e postgresl- 9.2 start ## OR ##
[root@server1 ~]# /et/)$)t.d/postgresl- 9.2 start ## Iniciar PostgreSQL 9.2 en cada arranque del equipo ##
[root@server1 ~]# hRo$?)g --levels 2'& postgresl-9.2 o$
Fedora 18/17/16 1.
## Iniciar PostgreSQL 9.2 ##
2. '. ". &.
[root@server1 ~]# systemtl start postgresl- 9.2.serv)e ## Iniciar PostgreSQL 9.2 en cada arranque del equipo ##
[root@server1 ~]# systemtl e$a=le postgresl- 9.2.serv)e
37
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. 2. '.
[root@server1 ~]# v) /et/syso$?)g/)pta=les -A ON -m state --state OHS -m tp -p tp --dport &"'2 -j F**H
3.2 Reiniciar el Firewall iptables CentOS, Red Hat (RHEL), Scientific Linux (SL) 6.4/5.9 1. 2. '.
[root@server1 ~]# serv)e )pta=les restart ## OR ##
[root@server1 ~]# /et/)$)t.d/)pta=les restart
Fedora 18/17/16 1.
[root@server1 ~]# systemtl restart )pta=les.serv)e
4 Optimización 4.1 Crear enlaces simbólicos para compatibilidad con 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 ~]# l$ -s /usr/pgsl-9.2/=)$/pg!o$?)g /usr/=)$
Enlace 2: Enlace simbólico de la anterior ubicación del directorio de datos /var/lib/pgsql 1. 2.
[root@server1 ~]# l$ -s / var/l)=/pgsl/9.2/data / var/l)=/pgsl [root@server1 ~]# l$ -s / var/l)=/pgsl/9.2/=aRups / var/l)=/pgsl
38
4.2 Optimizar el rendimiento del Postgres usando el archivo postgresql.conf Y cambiamos algunas opciones básicas del archivo postgresql.conf: 1.
shared!=u??ers 2&6;<
‘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!=u??ers 16;<
‘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. '.
worR!mem 16;<
‘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). ".
max!staR!depth ;<
‘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”. &.
shared!preload!l)=rar)es KGl)=d)r/plpytho$2.soK
‘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. 39
6.
=gwr)ter!delay &%%ms
‘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. 2. '. ". &. 6. .
Rer$el.sem 1%% '2%%% 1%% 12 Rer$el.shmall '29&" Rer$el.shmmax 2912"" Rer$el.shmm$) 192 ?s.?)le-max 2&' vm.d)rty!=ytes 61%6" vm.d)rty!=aRgrou$d!=ytes 1'"212
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. 2. '. ". &. 6. . .
[root@server1 ~]# systl -p -Rer$el.sem 1%% '2%%% 1%% 12 Rer$el.shmall '29&" Rer$el.shmmax 2912"" Rer$el.shmm$) 192 ?s.?)le-max 2&'
40
9. vm.d)rty!=ytes 61%6" 1%. vm.d)rty!=aRgrou$d!=ytes 1'"212
Ya, con estos sencillos cambios, podemos reiniciar el postresql: 1. 2.
[root@server1 ~]# /et/)$)t.d/postgresl restart (estart)$g ostgre7T3 9.1 data=ase server: ma)$.
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 ~]# mRd)r /srv/postgresl
Asignamos el directorio a sus propietarios. Debe ser un superusuario postgres: 2. [root@server1 ~]# how$ postgres /srv/postgresl
Ahora entramos al usuario postgres '. [root@server1 ~]# su - postgres
Al instalar la BD, cambiamos los parámetros del comando initdb para Inicializar el Cluster en el directorio creado anteriormente: ". -=ash-".1G /usr/pgsl-9.2/=)$/)$)td= -B /srv/postgresl
Ahora iniciamos la BD: &. -=ash-".1G /usr/pgsl-9.2/=)$/postmaster -B /srv/postgresl
6 Instalar el PostgreSQL en un Directorio Diferente usando tablespace 41
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. -=ash-".1G psl
En la consola, ejecutamos el comando para crear un espacio de tablas: '. postgres# *(HFH F<3H7F*H te!s)stema SOH( postgres 3*FO K/srv/postgreslKL
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: ". postgres# *(HFH BFF
S,
HO*BOAKNUK
SOH(postgres
Y como verán, le pasamos el tablespace “te_sistema” que hemos creado anteriormente
42
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 Ejecutables Librerías
Ubicación
/usr/bin /usr/lib /usr/share/doc/postgresql-x.y. z Documentación /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 43
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. Directorio con subdirectorios para todo el cluster, como global pg_database. pg_clog Directorio con datos de transacciones. Directorio que contiene la multitransacción de los Datos (utilizado pg_multixact para los bloqueos de fila compartidas). pg_notify Directorio que contiene el estado de los datos LISTEN/NOTIFY. Directorio que contiene información sobre las transacciones pg_serial serializables committed pg_snapshots Directorio que contiene las instantáneas (snapshots) exportadas Directorio con archivos temporales para el subsistema de pg_stat_tmp estadísticas pg_subtrans Directorio con datos de subtransacciones. pg_tblspc Directorio con enlaces simbólicos a tablespaces. con archivos de estado para transacciones pg_twophase Directorio preparadas. pg_xlog Directorio con archivos WAL (Write Ahead Log). postmaster.opts Fichero con opciones de arranque de postmaster utilizadas. Fichero de bloqueo con el PID actual del postmaster, La ruta del directorio del cluster de datos, fecha y hora de inicio del número de puerto, ruta del directorio del dominio postmaster.pid postmaster, 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 44
Í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í. all í. Cada tabla y el índice se almacenan en un archivo independiente. Para las relaciones normales, estos archivos tienen el nombre de d e 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.
Sin límites. Tabla: Límites de espacio en disco que maneja PostgreSQL
índices en una tabla
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 45
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. 2. '. ". &. 6. .
[root@server1 ~]# su - postgres -=ash-".1G -=ash-".1G psl postgres# postgres# 7,S data!d)retoryL data!d)retory --------------------/var/l)=/pgsl/9.2/data 41 row5
Dentro del directorio de datos encontramos el directorio base donde se graban todos los datos contenidos en nuestras bases de datos. 1. 2. '. ". &. 6. .
-=ash-".1G -=ash-".1G -=ash-".1G -=ash-".1G -=ash-".1G -=ash-".1G total 2 drwx-----drwx-----drwx-----drwx-----drwx-----drwx------
d /var/l)=/pgsl/9.2/data/=a /var/l)=/pgsl/9.2/data/=ase se ls -l ls Il 2 postgres $ogroup 122 2%11-1%-%" 1:&' 1 2 postgres $ogroup "%96 2%11-1%-%" 1:&' 119'9 2 postgres $ogroup "%96 2%11-1%-%" 1:&' 119"
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# postgres# 7H3H* dat)d0dat$ame ?rom pg!stat!data=aseL pg!stat!data=aseL 2. dat)d + dat$ame '. -------Q-------------". 1 + template1 &. 119'9 + template% 6. 119" + p ostgres . 4' rows5
46
Los valores de datid se y 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# postgres# *(HFH BFF
Se crea un directorio con el nombre 16407 que corresponde a la base de datos creada. 1. 2. '. ". &. 6.
-=ash-".1G -=ash-".1G total 2" drwx-----drwx-----drwx-----drwx-----drwx-----drwx-----drwx-----drwx------
ls -l /var/l)=/pgs / var/l)=/pgsl/9.2/data/ l/9.2/data/=ase/ =ase/ 2 2 2 2
postgres postgres postgres postgres
$ogroup $ogroup $ogroup $ogroup
122 2%11-1%-%" 1:&' 1 "%96 2%11-1%-%" 1:&' 119'9 "%96 2%11-1%-%" 1:&' 119" "%96 2%11-1%-%& 11:'1 16"%
1. postgres# postgres# 7H3H* dat)d0dat$ame ?rom pg!stat!data=aseL pg!stat!data=aseL 2. dat)d + dat$ame '. -------Q----------------------". 1 + template1 &. 119'9 + template% 6. 119" + p ostgres . 16"% + t est!)$ter$o est!)$ter$o . 4" rows5
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. 2. '. ". &. 6. . . 9. 1%. 11. 12. 1'. 1". 1&.
postgres# postgres# P test!)$ter$o Mou are $ow o$$eted to data=ase Jtest!)$ter$oJ Jtest!)$ter$oJ as user JpostgresJ. test!)$ter$o# test!)$ter$o# *(HFH F<3H test%%1 4)d OHAH(0 ode OHAH(0 pr)mary Rey4)d55L O*H: *(HFH F<3H/(;F(M HM w)ll reate )mpl))t )$dex Jtest%%1!pReyV *(HFH F<3H test!)$ter$o# Pd test%%1 a=le Jpu=l).test%%1J Jpu=l).test%%1J *olum$ + ype + ;od)?)ers --------Q---------Q----------)d + )$teger + $ot $ull ode + )$teger + $dexes: Jtest%%1!pReyJ Jtest%%1!pReyJ (;F(M HM0 =tree 4)d5
El identificador de la tabla test001 y el fichero correspondiente lo podemos obtener así: 1. test!)$ter$o# test!)$ter$o# 7H3H*
47
2. pg!relat)o$!?)le$ode4K test001K50pg!relat)o$!?)lepath4Ktest001 K5L '. ". pg!relat)o$!?)le$ode + pg!relat)o$!?)lepath &. ----------------------Q-------------------------6. 16"6& + =ase/16"%/ 1646 . 41 row5
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. 2. '. ". &. 6. .
test!)$ter$o# 7H3H* pg!relat)o$!?)le$ode4K test001!p"eK50pg!relat)o$!?)lepath4Ktest001!p"e K5L pg!relat)o$!?)le$ode + pg!relat)o$!?)lepath ----------------------Q--------------------------16"6 + =ase/16"%/ 1646$ 41 row5
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. -=ash-".1G ls -l /var/l)=/pgsl/9.2/data/=ase/16"%/16"6& 2. -rw------- 1 postgres $ogroup % 2%11-1%-%6 12:%9 =ase/16"%/ 1646 1. -=ash-".1G ls -l /var/l)=/pgsl/9.2/data/=ase/16"%/16"6 2. -rw------- 1 postgres $ogroup 192 2%11-1%-%6 12:%9 =ase/16"%/ 1646$
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. 2. '. ". &.
-=ash-".1G -rw-------rw-------rw-------rw-------
ls -l /var/l)=/pgsl/9.2/data/=ase/16"%/16"6&> 1 postgres pgd=a 1%'"12" Wul & 1&:11 =ase/16"%/ 1646 1 postgres pgd=a 1%'"12" Wul 6 1&:11 =ase/16"%/ 1646.1 1 postgres pgd=a 1%'"12" Wul 1&:11 =ase/16"%/ 1646.2 1 postgres pgd=a &'6%912 Wul 1&:11 =ase/16"%/ 1646.%
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.
48
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.
49
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: 2" =ytes: ada ?)la de a=eera 4aprox)mado5 2" =ytes: u$ ampo )$t y u$ ampo de texto Q " =ytes: pu$teros e$ la pXg)$a de tupla -------------------------------------------&2 =ytes por ?)la
El tamaño de página de datos en PostgreSQL es 8192 bytes (8 KB), por lo que: 192 =ytes por pXg)$a --------------------- 1& ?)las por pXg)$a de =ase de datos 4redo$deado5 &2 =ytes por ?)la 1%%%%% ?)las de datos --------------------- 6'' pXg)$as de =ase de datos 4redo$deado ha)a arr)=a5 1& ?)las por pXg)$a 6'' pXg)$as =ases de datos > 192 =ytes por pag)$a &01&0&'6 =ytes 4&.2 ;<5
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 ”. 50
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 pg_database pg_class pg_attribute pg_index pg_proc pg_type pg_user pg_operator
Descripción (que almacena) Bases de datos Clases o tablas Atributos o campos de la clase o tabla Índices secundarios Procedimientos Tipos de datos (del sistema y definidos por el usuario) Usuarios de PostgreSQL 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# 7H3H* > U(; pg!data=aseL
Para saber que tablas tengo en la base de datos actual: 2. postgres# 7H3H* > U(; pg!lassL
Si sólo queremos saber cuántos registros tiene una tabla, consultamos: '. postgres# 7H3H* rel$ame0reltuples U(; pg!lass S,H(H rel$ameKm)ta=laKL
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. 51
Algunos de estos tipos de datos se muestran en las tablas que están a continuación: Tipo en Postgres
Correspondiente en SQL3
Bool
boolean
Char(n)
character(n)
Date
int2 int4 money
date real, double precision smallint int, integer decimal(9,2)
Time
time
timespan
interval timestamp with time zone
Float8
timestamp varchar(n)
valor lógico o booleano (true/false) cadena de caracteres de tamaño fijo fecha (sin hora) número de punto flotante de doble precisión entero de dos bytes con signo entero de cuatro bytes con signo cantidad monetaria Hora en horas, minutos, segundos y centésimas intervalo de tiempo fecha y hora con zonificación
cadena de caracteres de tamaño variable Tabla: Tipos de datos del Estándar SQL3 en PostgreSQL
Tipo Box Cidr circle Inet int8 Line Lseg Path
Descripción
character varying(n)
Tipos de datos extendidos en PostgreSQL Descripción caja rectangular en el plano dirección de red o de host en IP versión 4 círculo en el plano dirección de red o de host en IP versión 4 entero de ocho bytes con signo línea infinita en el plano segmento de línea en el plano trayectoria geométrica, abierta o cerrada, en el plano
52
point polygon serial
punto geométrico en el plano trayectoria geométrica cerrada en el plano identificador numérico único con autoincremento Tabla: Tipos de datos Extendidos en PostgreSQL
53
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:
54
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
Cadena de caracteres
CHAR(long)
Long. fija de almacenamiento.
VARCHAR(long)
Long. variable de almacenamiento.
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.
DATE
Fecha.
TIME
Hora.
TIMESTAMP
Fecha y hora.
Número
Fecha/hora
Descripción
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]] 55
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. 56
-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. '. ". &. 6. .
-=ash-".1G psl -N testuser -h 192.16.%.1% -p &"'2 -d test psl 4.".1'5 ype JhelpJ ?or help. test#
Generar un archivo tipo CSV (delimitado por comas) con el resultado de una consulta. Este archivo se puede abrir desde Excel. 1. 2. '. ". &. 6. . . 9.
-=ash-".1G psl -N testuser -d test -U JLJ -F - Jselet > ?rom ta=leJ -o ta=le.sv -=ash-".1G at testta=le.sv dLol1Lol2 1%%LholaLmu$do 1%1L)aoLmo$do 1%2LhelloLworld 1%'L=o$YourLmo$de 4" ?)las5 -=ash-".1G
57
Generar un reporte HTML a partir de una consulta. 1. 2.
-=ash-".1G psl -N testuser -d test -, - Jselet > ?rom ta=leJ -o ta=le.html -=ash-".1G ?)re?ox ta=le.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: Comando
Descripción
\c “BASE DE reconectarse. Recibe como parámetros la base de DATOS” USUARIO Permite EQUIPO PUERTO 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 58
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.). Comando
Descripción
\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. 2. '.
-=ash-".1G psl psl 4.".1'5 ype JhelpJ ?or help.
1. 2. '. ". &. 6. . . 9. 1%. 11. 12. 1'. 1". 1&. 16. 1. 1.
postgres# Pl 3)st o? data=ases Oame + w$er +H$od)$g+ *ollat)o$ + *type + Fes ------------Q----------Q--------Q-------------Q-------------Q----------------rue=as + prue=as + NU + e$!N7.NU- + e$!N7.NU- + d=adm)s)o$ + postgres + NU + e$!N7.NU- + e$!N7.NU- + ?)sal + ?)sal + NU + e$!N7.NU- + e$!N7.NU- + postgres + postgres + NU + e$!N7.NU- + e$!N7.NU- + template% + postgres + NU + e$!N7.NU- + e$!N7.NU- + /postgres : postgres*/postgres template1 + postgres + NU + e$!N7.NU- + e$!N7.NU- + /postgres : postgres*/postgres test + testuser + NU + e$!N7.NU- + e$!N7.NU- + */testuser : testuser*/testuser 4rows5 postgres#P test Mou are $ow o$$eted to data=ase JtestJ.
59
19. test#
Listado de bases de datos, conexión a una base de datos en particular. 1. 2. '. ". &. 6. . . 9. 1%. 11. 12. 1'. 1". 1&. 16. 1. 1. 19. 2%. 21. 22. 2'. 2".
test# PC v) p.sl selet > ?rom testta=leL selet ol20ol1 ?rom testta=leL ~ ~ :w test# P) p.sl )d + ol1 + ol2 -----Q---------Q------1%% + hola + mu$do 1%1 + )ao + mo$do 1%2 + hello + world 1%' + =o$Your + mo$de 4" rows5
-- *rea el arh)vo cp.sql o$ v)
-- Ara=a cp.sql e$ shell -- HYeuta cp.scl
ol2 + ol1 -------Q--------mu$do + hola mo$do + )ao world + hello mo$de + =o$Your 4" rows5 test#
Ejecutar un comando del Sistema Operativo. Ejecutar SQLs desde un archivo. 1. 2. '. ". &. 6. . . 9. 1%. 11. 12. 1'. 1". 1&. 16. 1. 1. 19.
test# Pdt 3)st o? relat)o$s Hshem + Oame + ype + w$er ---------Q--------------Q-------Q---------pu=l) + am)gos + ta=la + testuser pu=l) + l)e$tes + ta=la + testuser pu=l) + ue$tas + ta=la + testuser pu=l) + )$v)ta)o$es + ta=la + testuser pu=l) + testta=le + ta=la + testuser pu=l) + usuar)os + ta=la + postgres 46 rows5 test#Pd testa=le a=le pu=l).testta=le *olum$ + ype + ;od)?)ers ---------Q-------------------Q-----------)d + )$teger + ol1 + harater vary)$g + ol2 + harater vary)$g +
Listado de tablas, lista de columnas de una tabla (describe).
Controlando una Sesión en psql en modo interactivo Para crear una base de datos:
60
1. 2.
postgres# reate data=ase testL postgres# P -- para sal)r de ostgre7T3
Para conectarse a una base de datos hay que teclear: '.
-=ash-".1G psl Id test IN 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: ". &. 6. . . 9. 1%.
test7H3H* *N((HO!N7H(L urre$t!user -------------testuser 41 row5 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. 12. 1'. 1". 1&. 16. 1.
test7H3H* *N((HO!;H7F;LL $ow ---------------------------2%1'-%&-'1 2':%':1&.&-%& 41 row5 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: 1. 19. 2%. 21. 22. 2'. 2". 2&. 26.
test# 7H3H* test-# 1 Q ' test -#L olum$ --------" 41 row5
-- el prompt am=)a de # a -# para )$d)ar -- ue la se$te$)a 7T3 aZ$ $o ?)$al)Da -- Hsto de=e respo$der o$ u$ "
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.
61
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. 2. '. ". &. 6. . . 9. 1%. 11.
test # *(HFH F<3H alltypes4 test -# estado *,F(4'50 test -# $om=re har 42%50 test -# h)Yo OHAH(0 test -# d)sta$)a U3F0 test -# presupuesto ON;H(*4160250 test -# $a)m)e$to BFH0 test -# llegada ;H0 test -# )$))o ;H7F;5L *(HFH F<3H test #
12. 1'. 1". 1&. 16. 1. 1. 19. 2%. 21. 22. 2'.
test # O7H( O alltypes test -# F3NH74 test -# K;H\K0 test -# K;aur))oK0 test -# %0 test -# 1%.0 test -# 629.%0 test -# K1"/%/19"K0 test -# K:"&K0 test -# K2&/%'/2%% 1%:'%:%%K5L O7H( % 1 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 # O7H( O alltypes 4estado0 $om=re5 2. test -# F3NH7 4KFA7K0 KAa=r)elK5L '. test # selet > ?rom alltypesL
En algunas columnas no se insertó valor, ¿Qué contienen?
62
". test # 7H3H* > U(; alltypes S,H(H h)Yo 7 ON33L
Hay diferencia entre cero y NULL (hijo->Mauricio y Gabriel) &. test # 7H3H* > U(; alltypes S,H(H h)Yo 1%%L
Los que tienen NULL en la columna hijos no aparecen 6. . . 9. 1%. 11.
test # O7H( O alltypes 4estado0 $om=re5 test -# F3NH7 4KK0 KFrturoK5L -- H7 NOF *FBHOF <3FO0 37 *F;7 -- ON;H(*7 O NHBHO 7H( <3FO test -# F3NH7 4ON330 KWos^K5L test # 7H3H* > U(; alltypes S,H(H estado 7 ON33L test # 7H3H* > U(; alltypes S,H(H estado KKL
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. 1'. 1". 1&. 16.
test test test test test
# -# -# # #
O7H( F3NH7 F3NH7 7H3H* 7H3H*
O alltypes 4estado0 $om=re5 4KK0 KFrturoK5L 4ON330 KWos^K5L > U(; alltypes S,H(H estado 7 ON33L > U(; alltypes S,H(H estado KKL
Valores DEFAULT
1. 2. '. ". &. 6. . . 9.
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. test# test-# test-# test-# test-# test# test# test-# test#
*(HFH F<3H ue$ta 4 $om=re F(*,F(42%50 =ala$e ON;H(*416025 BHUFN3 %0 at)va *,F(425 BHUFN3 K7K0 reada ;H7F; BHUFN3 *N((HO!;H7F;5L Pd ue$ta O7H( O ue$ta 4$om=re5 F3NH7 4Kala)o de ,)erroK5L 7H3H* > U(; ue$taL
Ejemplos 63
1. 2. '. ". &. 6. .
test# F3H( F<3H alltypes F3H( )$))o test-# 7H BHUFN3 *N((HO!;H7F;L F3H( F<3H test# O7H( O alltypes 4estado0 $om=re5 test-# F3NH7 4K;H\K0 K;el)ssaK5L O7H( % 1 test# 7H3H* > U(; alltypesL
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 64
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.
65
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 66
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".
67
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.
68
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.
• •
• • • •
•
•
•
69
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.
•
•
•
•
70
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. 2. '. ". &.
G psl -h serv)dor -N postgres -d postgresl 49.2.60 server .".115 ype JhelpJ ?or help. 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# selet ou$t4>5 ?rom pg!stat!at)v)tyL
Retorna:
71
Obtener el registro completo de la actividad de los usuarios y determinar los campos de la tabla de sesiones: .
postgres# selet > ?rom pg!stat!at)v)tyL
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. 2. '. ". &.
G psl -h serv)dor -N postgres -d postgres sl 49.1.20 server .".115 ype JhelpJ ?or help. 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: 72
6.
postgres# selet dat$ame0 p)d0 use$ame0 appl)at)o$!$ame0 l)e$t!addr0 uery ?rom pg!stat!at)v)tyL
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: .
postgres# selet pg!term)$ate!=aRe$d4p)d5 ?rom pg!stat!at)v)ty where p)d
11%L
.
postgres# selet dat$ame0 p)d0 use$ame0 appl)at)o$!$ame0 l)e$t!addr0 uery ?rom pg!stat!at)v)tyL
73
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.
selet pg!term)$ate!=aRe$d4p)d5 ?rom pg!stat!at)v)ty where uery K B3HKL
Para eliminar todas las conexiones de una determinada base de datos, con una única instrucción: 1%. postgres# KtestKL
selet pg!term)$ate!=aRe$d4p)d5 ?rom pg!stat!at)v)ty where dat$ame
Más información al respecto en: http://www.postgresql.org/docs/8.2/interactive/functions-admin.html#FUNCTIONSADMIN-SIGNAL-TABLE
74
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
75
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 Nombre del Espacio de Datos, no debe comenzar con pg_ , tablespace_name porque son nombres reservados para los sistemas. Nombre del propietario del tablespace , default - usuario que user_name crea el tablespace . Solo los superusuarios pueden crear tablespaces , pero se les puede asignar a no superusuarios Directorio usado por el tablespace. El directorio debe estar directory 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. 76
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.
*(HFH F<3H7F*H d=spae 3*FO K/data/d=sKL
Crear el tablespace indexspace en /data/indexes de propiedad del usuario indexuser: 2.
*(HFH F<3H7F*H )$dexspae SOH( )$dexuser 3*FO K/data/)$dexesKL
--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. name user_name template
Parámetros de Entrada Nombre de la Base de Datos Nombre del propietario de la Base de Datos, default usuario que crea la base de datos template1 : default - con locales adicionados. template0 : base de datos virgen con los objetos estándar 77
Set de caracteres. 'UTF8', 'SQL_ASCII'….. Orden de comparación. 'Spanish, Colombia' …… Afecta el lc_collate orden y los índices aplicados a los strings (order by ) Clasificación de caracteres. 'Spanish, Colombia'…..Afecta a lc_ctype la clasificación de los caracteres: mayúsculas, minúsculas y dígitos. Nombre del tablespace donde se ubica Base de Datos. Si tablespace_name no se especifica queda en el tablespace pg_default . Número de conexiones simultáneas a la Base de Datos. connlimit -1 (default ) significa que no hay límite. encoding
Salidas más usuales 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.
*(HFH BFF
Crear la Base de Datos test de propietario testuser con el tablespace predeterminado testspace : 2.
*(HFH BFF
Crear la Base de Datos musica que soporte el set de caracteres ISO-8859-1: '.
*(HFH BFF
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: ".
*(HFH BFF
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.
78
--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… Parámetros de Entrada Nombre del Esquema. Si se omite, el nombre de usuario se usa como el nombre del Esquema. schema_name El nombre no puede empezar con pg_, porque están reservados para esquemas del sistema. Nombre del usuario propietario del Esquema. Si se omite, el propietario será quien ejecute el comando user_name Sólo los superusuarios pueden crear esquemas de propiedad para otros usuarios. 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 schema_element 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. Salidas más usuales 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: 79
1.
*(HFH 7*,H;F esuemaL
Crear un esquema joe para el usuario joe: 2.
*(HFH 7*,H;F FN,(_FO YoeL
Crear el esquema cine que contenga la tabla films y la vista ganadores: '. ". &. 6.
*(HFH *(HFH *(HFH 7H3H*
7*,H;F )$e F<3H ?)lms 4t)tulo text0 ?eha date0 prem)os text[]5 HS ga$adores F7 t)tulo0 ?eha U(; ?)lms S,H(H prem)os 7 O ON33L
Note que los subcomandos individuales no terminan en punto y coma. La siguiente es una forma equivalente para obtener el mismo resultado: . . 9. 1%.
*(HFH *(HFH *(HFH 7H3H*
7*,H;F )$eL F<3H ?)lms 4t)tulo text0 ?eha date0 prem)os text[]5L HS ga$adores F7 t)tulo0 ?eha U(; ?)lms S,H(H prem)os 7 O ON33L
Para el propósito de la práctica: 11. *(HFH 7*,H;F erso$aL 12. *(HFH 7*,H;F Fdm)s)o$L
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. 80
Para operar la secuencia se usan las funciones nextval, currval y setval .
TEMP name increment
minvalue
maxvalue
start
cache
CYCLE
Parámetros de Entrada Si se especifica, la secuencia se crea solo para esta sesión y se elimina al salir. Nombre de la Secuencia. Especifica el incremento. Un valor positivo hará una secuencia ascendente, uno negativo hará una secuencia descendente. El valor por omisión es uno (1). Valor mínimo que una secuencia puede generar. El valor por omisión es 1 y -2147483647 para secuencias ascendentes y descendentes, respectivamente. Valor máximo para una secuencia. Por omisión son 2147483647 y -1 para secuencias ascendentes y descendentes, respectivamente. 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. 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. 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 Salidas más usuales
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. *(HFH 7HTNHO*H ser)al 7F( 1%1L
Seleccione el siguiente número de esta secuencia 2. 7H3H* OH\F3 4Kser)alK5L '. $extval ". -----
81
&. 1%1
Utilice esta secuencia en una INSERT: 6. F3H( F<3H testta=le FBB )d )$tL . O7H( O testta=le F3NH7 4K U; testta=leL
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. B*o$tato )$t de?ault $extval4Kerso$a.se!)do$tatoK50
Cambiar por: 1%. B*o$tato 7H(F30
También 11. B;odal)dad )$t de?ault $extval4KFdm)s)o$.se!)dmodal)dadK5 0
Cambiar por: 12. B;odal)dad 7H(F30
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: 1'. *(HFH 7HTNHO*H erso$a.se!)do$tato 7F( 1L --emp)eDa desde 1 1". *(HFH 7HTNHO*H Fdm)s)o$.se!)dmodal)dad 7F( 1L --emp)eDa 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 [, ...] ) ]
82
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) Parámetros de Entrada Si se especifica, la tabla se crea solo para esta sesión y es eliminada al salir. Si existen tablas con el mismo TEMP nombre, no son visibles mientras exista la tabla temporal Nombre de la clase o tabla. table El tipo del campo. Puede incluir especificadores de column array.. Valor por defecto para el campo: un literal una función de usuario DEFAULT value CURRENT_USER CURRENT_DATE CURRENT_TIME CURRENT_TIMESTAMP 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 table_constraint_clause 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. 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 INHERITS aparece más de una vez, Postgres informa de un inherited_table error. Postgres permite automáticamente a la tabla creada heredar funciones de las tablas superiores a ella en la jerarquía de herencia. • • • • • •
83
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. 2. '. ". &. 6. . . 9. 1%. 11.
reate ta=le erso$a.*o$tato 4 B*o$tato )$t de?ault $extval4Kerso$a.se!)do$tatoK50 Oom=res varhar4'%5 $ot $ull0 ater$o varhar4'%5 $ot $ull0 ;ater$o varhar4'%5 $ot $ull0 Ae$ero har415 de?ault4K%K5 $ot $ull0 BO varhar41%5 $ull0 UehaOa date $ull0 Ueha*rea)o$ date $ot $ull de?ault $ow45 5L
12. 1'. 1". 1&. 16. 1. 1. 19. 2%. 21. 22.
reate ta=le erso$a.ostula$te 4 Bostula$te har41%5 $ot $ull 0 B*o$tato )$t $ot $ull0 B*arrera har4'5 $ot $ull0 BerFad har465 $ot $ull0 B;odal)dad )$t $ot $ull0 u$taYe )$t $ot $ull de?ault4%50 Fs)st)oHxame$ har415 $ot $ull de?ault4K%K50 $greso har415 $ot $ull de?ault4K%K5 5L
2'. 2". 2&. 26. 2. 2.
*reate a=le Fdm)s)o$.erFad 4 BerFad har465 $ot $ull 0 er)odo har4"50 F$o har415 5L
29. '%. '1. '2. ''.
reate ta=le Fdm)s)o$.*arrera 4 B*arrera har4'5 $ot $ull0 Oom=re varhar41&%5 $ot $ull 5L
84
'". '&. '6. '. '.
reate ta=le Fdm)s)o$.;odal)dad 4 B;odal)dad )$t de?ault $extval 4KFdm)s)o$.se!)dmodal)dadK50 ;odal)dad varhar41%%5 $ot $ull 5L
--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. name condition
Parámetros de Entrada Nombre de la Restricción. Cualquier expresión condicional válida que se evalué a un resultado booleano. Salidas más usuales
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. F3H( F<3H erso$a.*o$tato 2. FBB *O7(FO R!Ae$ero '. *,H* 4Ae$ero )$ 4K%K0K1K5 5L ". F3H( F<3H erso$a.ostula$te &. FBB *O7(FO R!as)st)oexame$ 6. *,H* 4as)st)oexame$ )$ 4K%K0K1K5 5L . F3H( F<3H erso$a.ostula$te . FBB *O7(FO R!)$greso 9. *,H* 4)$greso )$ 4K%K0K1K5 5L
85
1%. 11. 12. 1'. 1". 1&. 16.
F3H( F<3H erso$a.ostula$te add *O7(FO R!pu$taYe *,H* 4 4as)st)oexame$ K1K a$d pu$taYe %5 or 4as)st)oexame$ K%K a$d pu$taYe %5 5L
--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. name ( column [, ...] )
Parámetros de Entrada Nombre de la Restricción. Columnas a las que se aplica la restricción. Salidas más usuales
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 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. 86
1. F3H( F<3H Fdm)s)o$.*arrera 2. FBB *O7(FO u!$om=rearrera '. NOTNH4Oom=re5L ". F3H( F<3H erso$a.*o$tato &. FBB *O7(FO u!*o$tatoBO 6. NOTNH4BO5L . F3H( F<3H Fdm)s)o$.;odal)dad . FBB *O7(FO u!;odal)dad 9. NOTNH4;odal)dad5L
--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 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 relationship already exists.
Si ya existe la restricción.
Para nuestro caso: 1. F3H( F<3H Fdm)s)o$.erFad 2. FBB *O7(FO pR!BerFad '. (;F(M HM4BerFad5L
87
". F3H( F<3H Fdm)s)o$.*arrera &. FBB *O7(FO pR!B*arrera 6. (;F(M HM4B*arrera5L . F3H( F<3H erso$a.*o$tato . FBB *O7(FO pR!B*o$tato 9. (;F(M HM4B*o$tato5L 1%. F3H( F<3H erso$a.ostula$te 11. FBB *O7(FO pR!Bostula$te 12. (;F(M HM4Bostula$te5L 1'. F3H( F<3H Fdm)s)o$.;odal)dad 1". FBB *O7(FO pR!B;odal)dad 1&. (;F(M HM4B;odal)dad5L
--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.
fkey_name column_name reftable refcolumn MATCH FULL MATCH PARTIAL MATCH SIMPLE
action
Parámetros de Entrada Nombre de la Restricción. Nombre de la columna afectada por la restricción. Tabla de referencia desde la cual se aplica se aplica la restricción. Si se omite, se usa la primary key de reftable No permite columnas null . No implementada. Permite columnas null. Opción predeterminada. 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. 88
Salidas más usuales 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 relationship already exists.
Si ya existe la restricción.
1. 2. '. ".
F3H( F<3H erso$a.ostula$te FBB *O7(FO ?R!B*arrera U(HAO HM4)darrera5 re?ere$es Fdm)s)o$.*arrera4B*arrera5L
&. 6. . .
F3H( F<3H erso$a.ostula$te FBB *O7(FO ?R!BerFad U(HAO HM4BerFad5 re?ere$es Fdm)s)o$.erFad4BerFad5L
9. 1%. 11. 12.
F3H( F<3H erso$a.ostula$te FBB *O7(FO ?R!B;odal)dad U(HAO HM4B;odal)dad5 re?ere$es Fdm)s)o$.;odal)dad4B;odal)dad5L
1'. 1". 1&. 16.
F3H( F<3H erso$a.ostula$te FBB *O7(FO ?R!B*o$tato U(HAO HM4B*o$tato5 re?ere$es erso$a.*o$tato4B*o$tato5LL
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 ] 89
Se utiliza para eliminar las restricciones de la tabla y de sus descendientes.
name constraint_name IF EXIST ONLY * RESTRICT CASCADE
Parámetros de Entrada Nombre de la Tabla. Nombre de la Restricción. Si se incluye y el objeto no existe, no se produce error. 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. Predeterminado - No elimina la restricción si tiene objetos dependientes. Elimina la restricción y los objetos que dependen de ella.
--1) ELIMINACIÓN DE CLAVES FORÁNEAS (FOREIGN KEYS): 1. F3H( F<3H erso$a.ostula$te 2. B( *O7(FO ?R!B*arreraL '. F3H( F<3H erso$a.ostula$te ". B( *O7(FO ?R!BerFadL &. F3H( F<3H erso$a.ostula$te 6. B( *O7(FO ?R!B;odal)dadL . F3H( F<3H erso$a.ostula$te . B( *O7(FO ?R!B*o$tatoL
--2) ELIMINACIÓN DE CLAVES PRIMARIAS (PRIMARY KEYS): 1. F3H( F<3H Fdm)s)o$.erFad 2. B( *O7(FO pR!BerFadL '. F3H( F<3H Fdm)s)o$.*arrera ". B( *O7(FO pR!B*arreraL &. F3H( F<3H erso$a.*o$tato 6. B( *O7(FO pR!B*o$tatoL . F3H( F<3H erso$a.ostula$te . B( *O7(FO pR!Bostula$teL 9. F3H( F<3H Fdm)s)o$.;odal)dad 1%. B( *O7(FO pR!B;odal)dadL
90
--3) ELIMINACIÓN DE RESTRICCIONES (UNIQUE): 1. F3H( F<3H Fdm)s)o$.*arrera 2. B( *O7(FO u!$om=rearreraL '. F3H( F<3H erso$a.*o$tato ". B( *O7(FO u!*o$tatoBOL &. F3H( F<3H Fdm)s)o$.;odal)dad 6. B( *O7(FO u!;odal)dadL
--4) ELIMINACIÓN DE RESTRICCIONES (CHECK): 1. F3H( F<3H erso$a.*o$tato 2. B( *O7(FO R!Ae$eroL '. F3H( F<3H erso$a.ostula$te ". B( *O7(FO R!as)st)oexame$L &. F3H( F<3H erso$a.ostula$te 6. B( *O7(FO R!)$gresoL . F3H( F<3H erso$a.ostula$te . B( *O7(FO R!pu$taYeL
--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. Parámetros de Entrada 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. Altera solo esta tabla. Si no aparece, se altera la tabla y todas ONLY sus descendientes (si las hay). * Explicitamente indica que se incluyen las tablas descendientes. Predeterminado - No elimina la restricción si tiene objetos RESTRICT dependientes. CASCADE Elimina la restricción y todos los objetos que dependen de ella.
91
1. F3H( F<3H Fdm)s)o$.*arrera 2. B( *3N;O B*arrera0 '. B( *3N;O Oom=reL
--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 IF EXIST RESTRICT CASCADE
Parámetros de Entrada Nombre de la Tabla (opcionalmente esquema-tabla). Si se incluye y el objeto no existe, no se produce ningún error. Predeterminado - No elimina la restricción si tiene objetos dependientes. Elimina la restricción y todos los objetos que dependen de la tabla. Salidas más usuales
DROPTABLE
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. 1. 2. '. ". &.
B( B( B( B( B(
F<3H ta=le ta=le a=le F<3H
Fdm)s)o$.*arreraL erso$a.*o$tatoL erso$a.ostula$teL Fdm)s)o$.erFadL Fdm)s)o$.;odal)dadL
92
--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 IF EXIST RESTRICT CASCADE
Parámetros de Entrada Nombre de la Secuencia (opcionalmente esquema-secuencia). Si se incluye y el objeto no existe, no se produce ningún error. Predeterminado - No elimina la secuencia si tiene objetos dependientes. Elimina la restricción y todos los objetos que dependen de la tabla.
1. B( 7HTNHO*H erso$a.se!)do$tatoL 2. B( 7HTNHO*H Fdm)s)o$.se!)dmodal)dadL
--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 IF EXIST RESTRICT CASCADE
Parámetros de Entrada Nombre del Esquema. Si se incluye y el objeto no existe, no se produce ningún error. Predeterminado - No elimina la esquema si tiene objetos dependientes. Elimina la restricción y todos los objetos que dependen del esquema.
1. B( 7*,H;F erso$aL 2. B( 7*,H;F Fdm)s)o$L
93
--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 IF EXIST
Parámetros de Entrada Nombre del Esquema. Si se incluye y el objeto no existe, no se produce ningún error.
1. B( BFF
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 objetos: 1. B( BFF
--2) Eliminar una secuencia que está siendo utilizada por una tabla: 1. B( 7HTNHO*H erso$a.se!)do$tato *F7*FBHL 2. B( 7HTNHO*H Fdm)s)o$.se!)dmodal)dad *F7*FBHL
--3) Eliminar una tabla con todos los constraints que posea: 1. 2. '. ". &.
B( B( B( B( B(
F<3H a=le F<3H ta=le ta=le
Fdm)s)o$.*arrera *F7*FBHL Fdm)s)o$.erFad *F7*FBHL Fdm)s)o$.;odal)dad *F7*FBHL erso$a.*o$tato *F7*FBHL erso$a.ostula$te *F7*FBHL
--4) Eliminar un esquema con todos sus objetos 1. B( 7*,H;F erso$a *F7*FBHL 2. B( 7*,H;F Fdm)s)o$ *F7*FBHL
94
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. name
Parámetros de Entrada Nombre de la tabla a truncar. Salidas más usuales
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. (NO*FH F<3H ta=lagra$deL
95
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. 7elet $om=re0 d)re)o$ 2. ?rom l)e$te '. where )d!l)e$te 2L
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.
96
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 ] expression
name
TEMPORARY TEMP
new_table
table condition column select
Parámetros de Entrada El nombre de una columna de la tabla o una expresión.. 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 La tabla se crea solamente para esta sesión, y es automáticamente descartada al finalizar la misma. 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. Un nombre alternativo para la tabla precedente table. Se utiliza para abreviar o eliminar ambigüedades en uniones dentro de una misma tabla. Una expresión booleana que da como resultado verdadero o falso (true or false). Consulte la cláusula WHERE. El nombre de una columna de la tabla. Una declaración de selección (select) exceptuando la cláusula ORDER BY.
97
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. 7H3H* B7O* O 4loat)o$5 loat)o$0 t)me0 report 2. U(; weather(eports '. (BH(
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.) 98
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 99
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 ] [, ...] ] 100
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 101
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. 7H3H* ?.t)tle0 ?.d)d0 d.$ame0 ?.date!prod0 ?.R)$d 2. U(; d)str)=utors d0 ?)lms ? '. S,H(H ?.d)d d.d)d
Para sumar la columna len (duración) de todos los filmes y agrupar los resultados según la columna kind (tipo): 1. 7H3H* R)$d0 7N;4le$5 F7 total U(; ?)lms A(N
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. 2. '. ".
7H3H* R)$d0 7N;4le$5 F7 total U(; ?)lms A(N
Los siguientes dos ejemplos muestran maneras idénticas de ordenar los resultados individuales de acuerdo con los contenidos de la segunda columna (name): 1. 7H3H* > U(; d)str)=utors (BH( U(; d)str)=utors (BH(
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. - d)str)=utors: ators: 2. - d)d+ $ame )d +$ame '. - ---Q--------------Q---------------
102
". &. 6. .
-
1%+Sestward 1 +Soody Flle$ 111+Salt B)s$ey 2 +Sarre$
Muestra el contenido de Admision.Modalidad : 1. 7H3H* B;odal)dad0 ;odal)dad U(; Fdm)s)o$.;odal)dadL
--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
Parámetros de Entrada El nombre de una la tabla. 103
El nombre de una columna en table_name. Una expresión o un valor válidos a asignar en expression column_name. Una consulta válida. Vea la instrucción SELECT para una query mejor descripción de argumentos válidos. DEFAULT Todas las columnas serán llenadas con sus valores VALUES predeterminados. Una expresión que se calcula y devuelve el comando INSERT después de cada fila. La expresión puede usar los output_expression 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. column_name
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. O7H( O ?)lms F3NH7 2. 4KNF&%2K0K
En este segundo ejemplo la columna date_prod se omite y entonces tendrá el valor por defecto de NULL: 1. O7H( O ?)lms 4ode0 t)tle0 d)d0 date!prod0 R)$d5 2. F3NH7 4K!6%1K0KMoY)m=oK01%60BFH K1961-%6-16K0KBramaK5L
Inserta varias filas en la tabla films desde la tabla tmp: 1. O7H( O ?)lms 7H3H* > U(; tmpL
INSERTAR DATOS EN Admision.Modalidad 1. O7H( O Fdm)s)o$.;odal)dad4;odal)dad5F3NH74KHxXme$ rd)$ar)oK5L 2. --Hl ampo B;odal)dad es seue$)al '. 7H3H* > U(; Fdm)s)o$.;odal)dadL --*ompro=ar $ser)`$
104
--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.
table_name column_name expression from_lista
Parámetros de Entrada El nombre de una la tabla. El nombre de una columna en table_name. Una expresión o un valor válidos a asignar en column_name. Es una extensión no estándar de Postgres que permite la aparición de columnas de otras tablas en la condición WHERE. 105
La columnas será llenada con su valore predeterminado (sera NULL si no se especifica). Una expresión que se calcula y devuelve el comando IUPDATE después de cada fila actualizada. La expresión output_expression 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. DEFAULT
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. 2. '. ". &.
NBFH pel)ulas '&& 7H ategor)a K Bramat)aK S,H(H ategor)a K Brama KL 7H3H* > U(; pel)ulas S,H(H ategor)a KBramat)oK ( ategor)a KBramaKL
ACTUALIZAR DATOS EN Admision.Modalidad : 1. NBFH Fdm)s)o$.;odal)dad 7H ;odal)dadKrd)$ar)oK 2. S,H(H B;odal)dadK1KL '. ". 7H3H* > U(; Fdm)s)o$.;odal)dadL --*ompro=ar Ftual)Da)`$
--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.
106
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. Parámetros de Entrada table_name El nombre de una la tabla. alias Un nombre sustituto para la tabla. Una lista de expresiones de tabla, permitiendo columnas de otras tablas que aparecer en la condición WHERE. Esto es using_list 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 Consulta SQL de selección la cual devuelve las filas a ser condition borradas. Una expresión que se calcula y devuelve el comando DELETE después de cada fila borrada. La expresión puede usar los output_expression 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. 107
Uso: Borra todos los films excepto los musicales: 1. BH3HH U(; ?)lms S,H(H R)$d K;us)alKL 2. 7H3H* > U(; ?)lmsL
Borra completamente la tabla films: 1. BH3HH U(; ?)lmsL 2. 7H3H* > U(; ?)lmsL
ELIMINAR DATOS EN Admision.Modalidad : 1. BH3HH U(; Fdm)s)o$.;odal)dad 2. S,H(H B;odal)dadK1KL '. ". 7H3H* > U(; Fdm)s)o$.;odal)dadL --*ompro=ar Hl)m)$a)`$
108
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. • •
109
•
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:
110
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
111
CASO PRÁCTICO: Transacción para transferir 50 desde una cuenta A a una cuenta B:
1- CREACIÓN DE TABLA Y CHECK: 1. 2. '. ". &. 6.
--B( F<3H t=l*ue$taL *(HFH F<3H t=l*ue$ta4Oum*ue$ta har0;o$to $umer)41%0'55L O7H( O t=l*ue$ta F3NH74KFK01%%5L O7H( O t=l*ue$ta F3NH74K
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. 2. '. ". &. 6. . . 9. 1%. 11. 12. 1'. 1". 1&. 16. 1. 1. 19.
*(HFH ( (H3F*H UNO*O tra$sa)o$145 (HN(O7 B F7 G=odyG BH*3F(H F $umer)L < $umer)L 1>/ F : 47H3H* mo$to ?rom t=l*ue$ta where Oum*ue$ta KFK5L />2>/ F : F - &%L />'>/ update t=l*ue$ta set ;o$to F where Oum*ue$ta KFKL />">/ < : 47H3H* mo$to ?rom t=l*ue$ta where Oum*ue$ta K&>/ < : < Q &%L />6>/ update t=l*ue$ta set ;o$to < where Oum*ue$ta K
4- COMPROBANDO: 1. 7H3H* > U(; tra$sa)o$145L 2. 7H3H* > U(; t=l*ue$taL
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 112
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 (FINAL) Requisito de aislamiento: Durante la operación se bloquea otros accesos. Requisito de durabilidad: Una vez actualizado debe quedar permanentemente.
113
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 .
•
114
•
Ejecutar el Script
115
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
•
1. 2. . '. ". &. 6. . •
Crear la Base de Datos: [root@serv)dor ~]# su I postgres -=ash-".1G psl 49.2.60 server .".115 ype JhelpJ ?or help. postgres# *(HFH BFF
Asignar el propietario a la Base de Datos
. postgres# F3H( BFF
Ejecutar el script dbadmin.sql en psql desde El Shell del usuário postgres
11. -=ash-".1G
psl -N postgres -d d=adm)s)o$
-? d=adm)$.sl
116
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 Cecilia Calderón
Admisión
Marketing
Carmen Peralta Yovana Vargas Liz Castro Katty Suarez Martha Carrion
Inicio de Sesión Ccaldero n Cperalta Yvargas Lcastro Ksuarez Mcarrion
Contraseña
Tipo de Usuario
Patito
Jefe
Perrito Muchachita Michimichi Loquita Love
Jefe Operador Operador Jefe 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. 117
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.
118
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. Parámetros de Entrada 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. privilegio DELETE Eliminar filas de una tabla RULE Definir reglas en una tabla o vista. ALL Otorgar todos los privilegios. El nombre de un objeto al que se quiere conceder el acceso. Los objeto posibles objetos son: tabla, vista, secuencia, índice. El nombre de un grupo al cual se otorga privilegios. El grupo debe grupo haber sido creado explícitamente. El nombre de un usuario al que se quiere conceder privilegios. usuario PUBLIC Una abreviación para representar a todos los usuarios. Salidas más usuales 119
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. . . 9. 1%. 11. 12. 1'. 1". 1&. 16. 1. 1. 19. 2%. 21.
-=ash-".1G psl IN testuser Id test test# PD r)v)leg)os Hsuema+ Oom=re + )po + r)v)leg +r)v)leg)os de aeso a olum$as -------Q--------------------Q-----------Q----------Q--------------------------u=l) + am)gos + ta=la + + u=l) + l)e$tes + ta=la + + pu=l) + l)e$tes!)d!se + seue$)a+ + pu=l) + ue$tas + ta=la + + pu=l) + ue$tas!)d!se + seue$)a+ + pu=l) + )$v)ta)o$es + ta=la + + pu=l) + )$v)ta)o$es!)d!se+ seue$)a+ + pu=l) + testta=le + ta=la + + pu=l) + usuar)os + ta=la + + pu=l) + usuar)os!)d!se + seue$)a+ + 41% ?)las5
REVOKE REVOKE privilegio [, ...] ON objeto [, ...] FROM { PUBLIC | GROUP grupo | usuario } REVOKE permite al creador de un objeto revocar permisos asignados anteriormente a todos los usuarios (mediante PUBLIC), a un usuario o a un grupo. Parámetros de Entrada privilegio SELECT Acceder a todas las columnas de una tabla o vista. 120
objeto grupo usuario PUBLIC
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. El nombre de un objeto sobre el que revocar el acceso. Los posibles objetos son: tabla, vista, secuencia, índice. El nombre de un grupo al cual se revocan privilegios. El nombre de un usuario al cual se revocan privilegios. Rescinde el/los privilegio(s) especificados(s) a todos los usuarios. Salidas más usuales
CHANGE
Mensaje devuelto si la orden se completa satisfactoriamente.
ERROR:
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
121
DESARROLLO PARTE 1: CREACIÓN DE ROLES Y USUARIOS DE LOGIN –CREANDO ROLES 1. 2. '. ".
*(HFH *(HFH *(HFH *(HFH
(3H (3H (3H (3H
We?eFB;L peFB;L We?e;L pe;L
–CREANDO LOGINS 1. 2. '. ".
*(HFH (3H *aldero$ S, 3AO F77S(B Kat)toK F3B NO3 K2%12-%1-%1K O (3H We?eFB;L
&. 6. . .
*(HFH (3H *peralta S, 3AO F77S(B Kerr)toK F3B NO3 K2%12-%1-%1K O (3H We?eFB;L
9. 1%. 11. 12.
*(HFH (3H Mvargas S, 3AO F77S(B K;uhah)taK F3B NO3 K2%12-%1-%1K O (3H peFB;L
1'. 1". 1&. 16.
*(HFH (3H 3astro S, 3AO F77S(B K;)h)m)h)K F3B NO3 K2%12-%1-%1K O (3H peFB;L
1. 1. 19. 2%.
*(HFH (3H suareD S, 3AO F77S(B K3ou)taK F3B NO3 K2%12-%1-%1K O (3H We?e;L
21. 22. 2'. 2".
*(HFH (3H ;arr)o$ S, 3AO F77S(B K3oveK F3B NO3 K2%12-%1-%1K O (3H pe;L
PARTE 2: CREACIÓN DE REQUERIMIENTOS –CREANDO ESQUEMAS 1. *(HFH 7*,H;F (HT!FB;L 2. *(HFH 7*,H;F (HT!;L
122
–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. A(FO 7H3H* O erso$a.*o$tato peFB;L 2. A(FO O7H( O erso$a.*o$tato peFB;L '. A(FO NBFH O erso$a.*o$tato peFB;L ". A(FO 7H3H* O erso$a.ostula$te peFB;L &. A(FO O7H( O erso$a.ostula$te peFB;L 6. A(FO NBFH O erso$a.ostula$te peFB;L . A(FO N7FAH O 7*,H;F erso$a peFB;L
–(Derecho 2) : De los postulantes solo debe tener acceso de seleccion sobre Puntaje, AsistioExamen e Ingreso 1. (HH 7H3H* O erso$a.ostula$te U(; peFB;L --Tu)tamos perm)so de 7H3H* so=re ostula$te 2. A(FO 7H3H* 4Fs)st)oHxame$5 O erso$a.ostula$te peFB;L '. A(FO 7H3H* 4$greso5 O erso$a.ostula$te peFB;L
–(Derecho 3) : Acceso a los requerimientos previamente solicitados 1. A(FO N7FAH O 7*,H;F (HT!FB; peFB;L
–(A LOS JEFES DE ADM) –(Derecho 1) : Seleccionar y Eliminar Postulantes. 1. A(FO 7H3H* O erso$a.ostula$te We?eFB;L 2. A(FO BH3HH O erso$a.ostula$te We?eFB;L '. A(FO N7FAH O 7*,H;F erso$a We?eFB;L
–(Derecho 2) : Actualizar los campos Puntaje, AsistioExamen e Ingreso de Postulante 1. A(FO NBFH 4u$taYe5 O erso$a.ostula$te We?eFB;L 2. A(FO NBFH 4Fs)st)oHxame$5 O erso$a.ostula$te We?eFB;L
123
'. A(FO NBFH 4$greso5 O erso$a.ostula$te We?eFB;L
–(Derecho 3) : Seleccionar Contactos 1. A(FO 7H3H* O erso$a.*o$tato to We?eFB;L
–(Derecho 4) : Seleccionar, Insertar, Eliminar y Actuzalizar Carreras, Modalidades y Periodos 1. A(FO 7H3H*0O7H(0BH3HH0 7H3H*0O7H(0BH3HH0 NBFH O Fdm)s)o$.*arrera We?eFB;L 2. A(FO 7H3H*0O7H(0BH 7H3H*0O7H(0BH3HH0 3HH0 NBFH O Fdm)s)o$.;odal)dad We?eFB;L '. A(FO 7H3H*0O7H(0BH3HH0 7H3H*0O7H(0BH3HH0 NBFH O Fdm)s)o$.erFad We?eFB;L ". A(FO N7FAH O 7*,H;F Fdm)s)o$ to We?eFB;L
–(Derecho 5) : Acceso a los requerimientos previamente solicitados 1. A(FO N7FAH O 7*,H;F (HT!FB; We?eFB;L
–(A LOS JEFES Y OPERADORES DE mkt) 1. A(FO N7FAH O 7*,H;F (HT!; We?e;Rt0pe;tL We?e;Rt0pe;tL
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. 7H3H* 7H77O!N7H(0 *N((HO!N7H(L
–COMPROBANDO DERECHOS –(DE LOS OPERADORES DE ADM) 1. 7H 7H77O FN,(_FO peFB;L
–(Derecho 1) : Seleccionar, Actualiza e Insertar Contactos y Postulantes 1. 7H3H* > U(; erso$a.*o$tatoL erso$a.*o$tatoL 2. '. ". &.
O7H( O erso$a.*o$tato F3NH74 16 0KF$o$ymousK 0KF$o$ymousK
124
6. . . 9. 1%.
0KF$o$ymousK 0% 0%%%%%%%% 0K1%/11/2%%%K 0$ow455L
11. 12. 1'. 1". 1&.
NBFH erso$a.*o$tato 7H $om=resKF$o$)moK 0pater$oKF$o$)moK 0mater$oKF$o$)moK S,H(H B*$tatoK16KL B*$tatoK16KL
16. BH3HH U(; erso$a.*o$tato erso$a.*o$tato S,H(H B*$tatoK16KL 1. --Hl rol peFB; $o t)e$e perm)so para el)m)$ar.
–(Derecho 2) De los postulantes solo debe tener acceso de selección sobre Puntaje, AsistioExamen e Ingreso 1. 7H3H* Bostula$te0B*o$tato0B Bostula$te0B*o$tato0B*arrera0B;od *arrera0B;odal)dad al)dad 2. U(; erso$a.ostula$teL erso$a.ostula$teL '. 7H3H* u$taYe0Fs)st)oHxame$0$greso u$taYe0Fs)st)oHxame$0$greso U(; erso$a.ostula$teL erso$a.ostula$teL
–(Derecho 3) : Acceso a los requerimientos previamente solicitados 1. 2. '. ".
7H3H* 7H3H* 7H3H* 7H3H*
> > > >
U(; U(; U(; U(;
(HT!FB;.pa!r145 (HT!FB;.pa!r245 (HT!FB;.pa!r'45 (HT!FB;.pa!r"45
F7 F7 F7 F7
4olum$1 4olum$1 4olum$1 4olum$1
u$R$ow$5L u$R$ow$5L u$R$ow$5L u$R$ow$5L
–(DE LOS JEFES DE ADM) 1. 7H 7H77O FN,(_FO We?eFB;L
–(Derecho 1) : Seleccionar y Eliminar Postulantes. 1. 7H3H* > U(; erso$a.ostula$teL 2. BH3HH U(; erso$a.ostula$te erso$a.ostula$te S,H(H B*o$tatoK1&KL
–(Derecho 2) : Actualizar los campos Puntaje, AsistioExamen e Ingreso de Postulante 1. NBFH erso$a.ostula$te 7H 2. u$taYe2%0 u$taYe2%0 Fs)st)oHxame$K1K0 Fs)st)oHxame$K1K0 $gresoK1K $gresoK1K S,H(H B*o$tatoK1"KL '. NBFH erso$a.ostula$te 7H B;odal)dadK%KL--Oo B;odal)dadK%KL--Oo t)e$e persm)so
–(Derecho 3) : Seleccionar Contactos 1. 7H3H* > U(; erso$a.*o$tatoL erso$a.*o$tatoL
125
–(Derecho 4) : Seleccionar, Insertar, Eliminar y Actualizar Carreras, Modalidades y Periodos 1. 7H3H* > U(; Fdm)s)o$.*arreraL Fdm)s)o$.*arreraL 2. 7H3H* > U(; Fdm)s)o$.;odal)dadL '. 7H3H* > U(; Fdm)s)o$.erFadL Fdm)s)o$.erFadL
–(Derecho 5) : Acceso a los requerimientos previamente solicitados 1. 2. '. ".
7H3H* 7H3H* 7H3H* 7H3H*
> > > >
U(; U(; U(; U(;
(HT!FB;.pa!r145 (HT!FB;.pa!r245 (HT!FB;.pa!r'45 (HT!FB;.pa!r"45
F7 F7 F7 F7
4olum$1 4olum$1 4olum$1 4olum$1
u$R$ow$5L u$R$ow$5L u$R$ow$5L u$R$ow$5L
–(DE LOS JEFES DE MKT) 1. 7H 7H77O FN,(_FO pe;L
–(Derecho 1) : Acceso a los requerimientos previamente solicitados 1. 2. '. ".
7H3H* 7H3H* 7H3H* 7H3H*
> > > >
U(; U(; U(; U(;
(HT!;.pa!r145 (HT!;.pa!r245 (HT!;.pa!r'45 (HT!;.pa!r"45
F7 F7 F7 F7
4olum$1 4olum$1 4olum$1 4olum$1
u$R$ow$5L u$R$ow$5L u$R$ow$5L u$R$ow$5L
–(DE LOS OPERADORES DE MKT) 1. 7H 7H77O FN,(_FO We?e;L
–(Derecho 1) : Acceso a los requerimientos previamente solicitados 1. 2. '. ".
7H3H* 7H3H* 7H3H* 7H3H*
> > > >
U(; U(; U(; U(;
(HT!;.pa!r145 (HT!;.pa!r245 (HT!;.pa!r'45 (HT!;.pa!r"45
F7 F7 F7 F7
4olum$1 4olum$1 4olum$1 4olum$1
u$R$ow$5L u$R$ow$5L u$R$ow$5L u$R$ow$5L
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: &. 7H 7H77O FN,(_FO peFB;L
cambiarlo por: 6. 7H 7H77O FN,(_FO MvargasL
El resultado deberá ser el mismo ya que Yvargas pertenece al grupo OpeADM.
126
PARTE 5: REESTABLECIMIENTO Y/O ELIMINACIÒN DE OBJETOS Si queremos eliminar algunos objetos o incluso la base de datos no debe existir ningún rol asociado a éste 1. (H7H 7H77O FN,(_FOL 2. --(egresamos al usuar)o pr)$)pal por de?eto. '. 7H3H* 7H77O!N7H(0 *N((HO!N7H(L ". B( BFF
(HH F33 O F<3H erso$a.*o$tato 0erso$a.ostula$te 0Fdm)s)o$.*arrera 0Fdm)s)o$.erFad 0Fdm)s)o$.;odal)dad U(; We?eFB;0peFB;0We?e;0pe;L
1". B( N7H( **aldero$0*eralta0Mvargas03*astro0suareD0;arr)o$L 1&. B( (3H We?eFB;0peFB;0We?e;0pe;L 16. B( BFF
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. 1. 7H3H* > U(; pg!stat!at)v)tyL
127
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. atalog$ame 2. shema$ame '. ta=le$ame ". row &. ol$ame1value/ol$ame1 6. ol$ame2 xs):$)ltrue/ . ... . /row 9. ... 1%. /ta=le$ame 11. ... 12. /shema$ame 1'. ... 1". /atalog$ame
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 128
1- Comprobando algunas funciones XML: 1. 7H3H* xmleleme$t4$ame *o$tato0 B*o$tato5 2. U(; erso$a.*o$tatoL '. 7H3H* xmleleme$t4$ame ater$o0 ater$o50xmleleme$t4$ame ;ater$o0 ;ater$o5 ". U(; erso$a.*o$tatoL &. 7H3H* xml?orest4ater$o0 ;ater$o5 6. U(; erso$a.*o$tatoL . 7H3H* xmleleme$t4$ame *o$tato0 xml?orest4ater$o0 ;ater$o55 . U(; erso$a.*o$tatoL 9. 7H3H* \;3Fgg4\;3Uorest4ater$o0 ;ater$o55 1%. U(; erso$a.*o$tatoL 11. 7H3H* \;3Hleme$t4$ame *o$tato0 \;3Fgg4\;3Uorest4ater$o0 ;ater$o555 12. U(; erso$a.*o$tatoL
2- Creamos una secuencia para usarla como numeración de la estructura resultante: 1. 2. '. ". &. 6.
--B( 7HTNHO*H se!o$tatoL *(HFH H; 7HTNHO*H se!o$tatoL 7H3H* xmleleme$t4$ame *o$tato 0xmlattr)=utes4$extval4Kse!o$tatoK5 F7 JrowJ5 0xml?orest4ater$o0 ;ater$o5 5 U(; erso$a.*o$tatoL
. 7H3H* xmleleme$t4$ame *o$tato0 xml?orest4ater$o0 ;ater$o0 Oom=res5 5 . U(; erso$a.*o$tatoL 9. 1%. 11. 12. 1'. 1". 1&. 16.
7H3H* xmleleme$t4$ame erso$a 0xmleleme$t4$ame *o$tato 0xmlattr)=utes4B*o$tato5 0ater$o 0;ater$o 0Oom=res5 5 U(; erso$a.*o$tatoL
3- Usando xmlcomment para comentar sobre XML 1. 2. '. ".
7H3H* xmleleme$t4$ame erso$a 0xmlomme$t4K*ome$tar)oK5 0xml?orest4ater$o0 ;ater$o0Oom=res55 U(; erso$a.*o$tatoL
&. 7H3H* xmleleme$t4$ame ater$o0 ;ater$o5 6. 0xmleleme$t4$ame ;ater$o0 ;ater$o5 . U(; erso$a.*o$tatoL
129
. 7H3H* xmlo$at4xmleleme$t4$ame ater$o0 ;ater$o5 9. 0xmleleme$t4$ame Oom=res0 Oom=res55 1%. U(; erso$a.*o$tatoL 11. 7H3H* xmleleme$t4$ame *o$tato0 xmlo$at4 xmleleme$t4$ame ater$o0 ater$o5 12. 0xmleleme$t4$ame ;ater=o0 ;ater$o555 1'. U(; erso$a.*o$tatoL
4- Creamos una tabla que contenga un campo de tipo XML, veremos luego una manera especial de manipularlas. 1. 2. '. ". &. 6.
--B( F<3H erso$a.*o$tato\;3L *(HFH F<3H erso$a.*o$tato\;3 4ol1 \;35L O7H( O erso$a.*o$tato\;347H3H* xmleleme$t4$ame *o$tato 0xmlo$at4 xmleleme$t4$ame ater$o0 ater$o5 0xmleleme$t4$ame ;ater$o0 ;ater$o5 5 5 U(; erso$a.*o$tato5L
. 7H3H* ol1 U(; erso$a.*o$tato\;3L
5- XPATH nos facilitará en la seleccion de campos dentro de una estructura de datos XML: 1. 7H3H* xpath4K/o$tato/mater$o/text45K0 ol15 2. U(; erso$a.*o$tato\;3 L '. 7H3H* xpath4K/o$tato/pater$o/text45K0 ol15 ". U(; erso$a.*o$tato\;3 L &. 7H3H* olum$a[1] 6. U(; 4 7H3H* xpath4K/o$tato/mater$o/text45K0 ol15 F7 olum$a . U(; erso$a.*o$tato\;3 5 F7 xmlsoureL
6- En estas dos últimas funciones, su poder radica en que puede generar una completa estructura XML a nivel de consulta o de una tabla completa: 1. 7H3H* uery!to!xml4K7H3H* Oom=res0ater$o0;ater$o U(; erso$a.*o$tatoK 2. 0UF37H0UF37H0K*o$tatosK5 '. 7H3H* ta=le!to!xml4Kerso$a.*o$tatoK0UF37H0UF37H0K*o$tatosK5
Recursos Utilizados: SQLXML-For-Postgres-Developers XML Document Support XML and Databases XML Type
130
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: *(HFH F<3H *3HOH7 4B 7H(F3 (;F(M HM0 ** F(*,F(41%5 O ON330 O;<(H F(*,F(4&%5 O ON335L *(HFH F<3H *NHOF7 4B 7H(F3 (;F(M HM0 7F3B (HF3 BHUFN3 %0 *3!B O O ON33 (HUH(HO*H7 *3HOH74B50 UH*,F!7<(HA( BFH0 OH(H7H7 (HF3 BHUFN3 %5L O7H( O7H( O7H( O7H( O7H( O7H(
O O O O O O
*3HOH7 4**0O;<(H5 F3NH7 4K11111K0K*3HOH 1K5L *3HOH7 4**0O;<(H5 F3NH7 4K22222K0K*3HOH 2K5L *3HOH7 4**0O;<(H5 F3NH7 4K'''''K0K*3HOH 'K5L *NHOF7 47F3B0 *3!B5 F3NH7 4 &%%%015L *NHOF7 47F3B0 *3!B5 F3NH7 4 -2%%%025L *NHOF7 47F3B0 *3!B5 F3NH7 4 '%%%0'5L
Estructura La estructura general de un bloque PL/PgSQL es la siguiente: [ la=el ] [ BH*3F(H delarat)o$s ]
131
Utilización mediante SELECT Por ejemplo, la siguiente función retorna su argumento multiplicado por 3: /> Uu$)`$ ue mult)pl)a su argume$to por ' >/ *(HFH ( (H3F*H UNO*O ;N3!'4x )$t5 retur$s O as GG =eg)$ (HN(O x>'L e$dL GG 3FOANFAH plpgslL
La función puede ser utilizada en una cláusula SELECT: selet mult!'4"5L mult!' -------12 41 row5
DECLARE: Las variables, en caso de ser necesarias, deben ser declaradas en la cláusula DECLARE: /> *o$ate$a$do el $om=re y el apell)do y ad))o$a$do mayusulas >/ *(HFH ( (H3F*H UNO*O *O*F!O;<(H4O;<(H F(*,F(0 FH33B F(*,F(5 (HN(O7 F(*,F( F7 GG BH*3F(H H; F(*,F(L
Un ejemplo de uso: selet o$at!$om=re4KarlosK0KolarteK5L o$at!$om=re --------------*arlos larte
Insertar Datos en una Tabla Esta función inserta un registro en la tabla CLIENTES: *(HFH UNO*O O7!*3HOH74F(*,F(0 F(*,F(5
132
(HN(O7 B F7 GG O7H( O *3HOH7 4**0O;<(H5 F3NH7 4G10G25L GG 3FOANFAH 7T3L
Un ejemplo de uso: 7H3H* O7!*3HOH74K"""""K0K*3HOH "K5L
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) /> Uu$)`$ ue tra$s?)ere d)$ero de u$a ue$ta a otra >/ *(HFH UNO*O (FO7UH( 4*F1 *NHOF7.B8MH 0 *F2 *NHOF7.B8MH 0 ;O *NHOF7.7F3B8MH5 (HN(O7 *NHOF7.7F3B8MH F7 GG BH*3F(H ONH!7F3B *NHOF7.7F3B8MHL
Ejemplo de uso: postgres# selet tra$s?er4'0201%%%5L tra$s?er ----------1%%% 41 row5
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.
133
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. /> Uu$)`$ ue tra$s?)ere d)$ero de u$a ue$ta a otra >/ /> ers)`$2: al)da)o$ de los datos de e$trada >/ *(HFH UNO*O (FO7UH(2 4*F1 *NHOF7.B8MH0 *F2 *NHOF7.B8MH0 ;O *NHOF7.7F3B8MH5 (HN(O7 *NHOF7.7F3B8MH F7 GG BH*3F(H ONH!7F3B *NHOF7.7F3B8MHL 7F3B!B7O<3H *NHOF7.7F3B8MHL H7!H\77 OL 5 O H7!H\77 U(; *NHOF7 S,H(H B*F1L U H7!H\77 1 ,HO (F7H H\*HO K3a ue$ta de or)ge$ 8 $o ex)steK0 *F1L H37H 7H3H* *NO4>5 O H7!H\77 U(; *NHOF7 S,H(H B*F2L U H7!H\77 1 ,HO (F7H H\*HO K3a ue$ta dest)$o 8 $o ex)steK0 *F2L H37H 7H3H* 7F3B O 7F3B!B7O<3H U(; *NHOF7 S,H(H B *F1L U 7F3B!B7O<3H ;O ,HO (F7H H\*HO KOo hay ?o$dos su?))e$tes para la tra$s?ere$)aKL H37H /> real)Da$do la tra$s?ere$)a >/ NBFH *NHOF7 7H 7F3B 7F3B - ;O S,H(H B*F1L NBFH *NHOF7 7H 7F3B 7F3B Q ;O S,H(H B*F2L /> *o$sulta$do y retor$a$do el $uevo saldo de *F2 >/ 7H3H* 7F3B O ONH!7F3B U(; *NHOF7 S,H(H B*F2L (HN(O ONH!7F3BL HOB UL HOB UL HOB UL HOB GG 3FOANFAH 3A7T3L
Ejemplo (Actualizando los intereses) Antes de presentar el ejemplo, vamos a adicionar algunas filas a la tabla cuentas: O7H( O *NHOF7 47F3B0*3!B0 UH*,F!7<(HA(5 F3NH7 4-'%%%0'0K2%1%-%1-%1K50 4-%%%020K2%1%-%'-%1K5L
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. /> Ftual)Da$do los )$tereses de las ue$tas de auerdo o$ el $umero de d)as e$ mora >/
134
*(HFH UNO*O *<(F(!OH(H7H74(*HOFWH (HF35 (HN(O7 B F7 GG (*HOFWH > H\(F*4KBFM7KU(; 4 OS45-UH*,F!7<(HA(55 S,H(H UH*,F!7<(HA( 7 O ON33L HOBL GG 3FOANFAH plpgslL
Ejemplo de uso: =d)%% 7H3H* *<(F(!OH(H7H74%.%15L
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. /> (etor$a$do el $om=re del l)e$te y el saldo de u$a ue$ta >/ *(HFH UNO*O OU!*NHOF4ON;*NHOF O5 (HN(O7 F(*,F( F7 GG BH*3F(H (H7N3FB F(*,F(L ta!row *NHOF78(SMHL --ta!row es del m)smo t)po de la ta=la *NHOF7 l)!row *3HOH78(SMHL O *F!(S U(; *NHOF7 S,H(H B ON;*NHOFL U O UNOB ,HO (F7H H\*HO K3a ue$ta 8 $o ex)ste. K0 ON;*NHOFL H37H 7H3H* > O *3!(S U(; *3HOH7 S,H(H B *F!(S.*3!BL (H7N3FB : K 3a ue$ta perte$ee a K ++ *3!(S.Oom=re ++ K. Hl saldo es K ++ *F!(S.7F3BL (HN(O (H7N3FBL HOB UL HOBL GG 3FOANFAH plpgslL
Ejemplo de uso: =d)%% 7elet OU!*NHOF415L )$?!ue$ta ---------------------------------------------------3a ue$ta perte$ee a *3HOH 1. Hl saldo es &%%% 41 row5
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
135
manera mucho más simple ejecutando una única consulta que traiga la información del cliente y de la cuenta: *(HFH UNO*O OU!*NHOF24ON;*NHOF O5 (HN(O7 F(*,F( F7 GG BH*3F(H 7F3B!*F *NHOF7.7F3B8MHL O;<(H!*3 F(*,F(L
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: *(HFH F<3H HOBHOH7 4 B 7H(F3 (;F(M HM0 ;O (HF3 O ON330 *F!B O O ON33 (HUH(HO*H7 *NHOF74B50 UH*,F BFH BHUFN3 OS450 H(F*O *,F(4150 (HF3_FBF <3 BHUFN3 UF37H5L O7H( O HOBHOH7 4;O0*F!B0H(F*O5 4'%%010KBK50 4"%%020K(K50 42''0'0KBK5L
F3NH7
El siguiente procedimiento “recorre” la tabla pendientes y realiza la operación indicada en la tabla CUENTAS. *(HFH ( (H3F*H UNO*O NB!HOBHOH745 (HN(O7 B F7 GG BH*3F(H -- Belara)`$ del *ursor -- Oote la ?orma del 7elet ... U( NBFHC *N(!HO *N(7( U( 7H3H* > U(; HOBHOH7 S,H(H (HF3_FBFUF37H U( NBFHL F**O HOBHOH78(SMHL
136
HO *N(!HOL 3 -- Hxtraer u$a ?)la del ursor UH*, *N(!HO O F**OL U F**O 7 ON33 ,HO H\L HOB UL U F**O.H(F*O KBK ,HO NBFH *NHOF7 7H 7F3B 7F3B Q F**O.;O S,H(H B F**O.*F!BL H37H NBFH *NHOF7 7H 7F3B 7F3B - F**O.;O S,H(H B F**O.*F!BL HOB UL -- Ftual)Dar u$a ?)la de auerdo o$ la pos))`$ del ursor NBFH HOBHOH7 7H (HF3_FBF(NH S,H(H *N((HO U *N(!HOL HOB 3L -- *)erre del ursor . *37H *N(!HOL HOBL GG 3FOANFAH plpgslL
La cláusula FOR UPDATE evita que otras transacciones estén modificando las filas seleccionadas. Ejemplo de uso: =d)%% selet > ?rom *NHOF7L )d + saldo + l)!)d + ?eha!so=reg)ro + )$tereses ----Q-------Q--------Q-----------------Q----------1 + &%%% + 1 + + % 2 + -2%%% + 2 + + % ' + '%%% + ' + + % 4' rows5 =d)%% selet > ?rom HOBHOH7L )d + mo$to + ta!)d + ?eha + opera)o$ + real)Dada ----Q-------Q--------Q------------Q-----------Q----------1 + '%% + 1 + 2%1%-%"-26 + B + ? 2 + "%% + 2 + 2%1%-%"-26 + ( + ? ' + 2'' + ' + 2%1%-%"-26 + B + ? 4' rows5 =d)%% 7H3H* NB!HOBHOH745L upd!pe$d)e$tes ---------------41 row5 =d)%% selet > ?rom *NHOF7L )d + saldo + l)!)d + ?eha!so=reg)ro + )$tereses ----Q-------Q--------Q-----------------Q----------1 + &'%% + 1 + + % 2 + -2"%% + 2 + + % ' + '2'' + ' + + % =d)%% selet > ?rom HOBHOH7L )d + mo$to + ta!)d + ?eha + opera)o$ + real)Dada ----Q-------Q--------Q------------Q-----------Q----------1 + '%% + 1 + 2%1%-%"-26 + B + t 2 + "%% + 2 + 2%1%-%"-26 + ( + t ' + 2'' + ' + 2%1%-%"-26 + B + t
137
4' rows5
Cursores Implicitos con FOR IN Otra forma de recorrer las tablas es utilizar cursores “implícitamente” usando FOR IN . 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: *(HFH F<3H N7NF(7!U(FNBH4B 7H(F30 *3!B O5L O7H( O N7NF(7!U(FNBH 4*3!B5 F3NH7 4150 4'5L
Ahora adicionamos un atributo en cuenta para saber si está activa o no: F3H( F<3H *NHOF7 FBB OF*F <3 BHUFN3 UF37HL
La siguiente función inhabilita las cuentas cuyo usuario se encuentra en la tabla de USUARIOS_FRAUDE *(HFH ( (H3F*H UNO*O OF*F 45 (HN(O7 B F7 GG BH*3F(H N7( N7NF(7!U(FNBH8(SMHL U(; N7NF(7!U(FNBH 3 NBFH *NHOF7 7H OF*F (NH S,H(H *3!B N7(.*3!BL HOB 3L HOBL GG 3FOANFAH plpgslL
Para este caso particular, un lector atento hubiera podido descubrir que el ejercicio se podía realizar con una sola sentencia del DML: NBFH *NHOF7 7H OF*F UF37H S,H(H *3!B O 47H3H* *3!B U(; N7NF(7!U(FNBH5L
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.
•
• •
138
Un ejemplo de Auditoria Suponga que cada que se modifica una cuenta, se debe registrar la acción en la siguiente tabla: *(HFH F<3H FNB(F4 H(F*O *,F(4150 UH*,FNB ;H7F;0 N7NF( H\0 B O0 7F3B (HF30 *3!B O0 UH*,F!7<(HA( BFH0 OH(H7H7 (HF30 OF*F <35L
La siguiente función deja un rastro en la tabla AUDITORIA cada que se realiza una modificación en la tabla CUENTAS: *(HFH ( (H3F*H UNO*O proess!ue!aud)t45 (HN(O7 (AAH( F7 GG L (HN(O 3BL H37U 4A! KNBFHK5 ,HO O7H( O FNB(F 7H3H* KNK0 $ow450 user0 OHS.>L (HN(O OHSL H37U 4A! KO7H(K5 ,HO O7H( O FNB(F 7H3H* KK0 $ow450 user0 OHS.>L (HN(O OHSL HOB UL HOBL GG 3FOANFAH plpgslL
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: *(HFH (AAH( ue!aud)t FUH( O7H( ( NBFH ( BH3HH O *NHOF7 U( HF*, (S H\H*NH (*HBN(H proess!ue!aud)t45L
Algunos ejemplos: =d)%% )$sert )$to ue$tas 4saldo0l)!)d5 values 4'&%%025L O7H( % 1 =d)%% selet > ?rom aud)tor)aL opera)o$ + ?ehaupd + usuar)o + )d + saldo + l)!)d + ?eha!so=reg)ro + )$tereses -----------Q----------------------------Q---------Q----Q-------Q--------Q-----------------Q--------- + 2%1%-%"-26 1%:'%:1.%1'2 + =d)%% + 6 + '&%% + 2 + + % 41 row5 =d)%% update ue$tas set ?eha!so=reg)ro $ow45 where )d 1L NBFH 1 =d)%% selet > ?rom aud)tor)aL opera)o$ + ?ehaupd + usuar)o + )d + saldo + l)!)d + ?eha!so=reg)ro + )$tereses -----------Q----------------------------Q---------Q----Q-------Q--------Q-----------------Q--------- + 2%1%-%"-26 1%:'%:1.%1'2 + =d)%% + 6 + '&%% + 2 + + %
139
N 42 rows5
+ 2%1%-%"-26 1%:'1:2'.2&"
+ =d)%%
+ 1 +
"%6 +
1 + 2%1%-%"-26
+
%
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: /> Uu$)o$ ue ver)?)a ue el $umero de ue$tas de u$ l)e$te $o sea mayor a ' >/ *(HFH ( (H3F*H UNO*O UNO!*!ON;!*3HOH745 (HN(O7 (AAH( F7 GG BH*3F(H ON;!*NHOF7 OL 5 O ON;!*NHOF7 U(; *NHOF7 S,H(H *3!B OHS.*3!BL U ON;!*NHOF7 ' ,HO (F7H H\*HO K3;H BH3 ON;H( BH *NHOF7 F3*FO_FB ( H3 *3HOHKL H37H (HN(O OHSL HOB UL H37H (HN(O OHSL HOB UL HOBL GG 3FOANFAH plpgslL /> *rea)o$ del r)gger >/ *(HFH (AAH( (A!ON;!*3HOH7 FUH( O7H( ( NBFH O *NHOF7 U( HF*, (S H\H*NH (*HBN(H UNO!*!ON;!*3HOH745L
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: F3H( F<3H *3HOH7 FBB *3N;O 7F3B (HF3 BHUFN3 %.%L
Como no habíamos implementado el trigger que se encargara de llevar este saldo, por la primera vez lo calculamos “manualmente”: *(HFH ( (H3F*H UNO*O NBFH!7F3B745 (HN(O7 B F7 GG
140
BH*3F(H *3HOH!7F3B (HF3L *3HOH!B (HF3L
Ahora, por cada modificación del saldo en una cuenta, actualizamos el saldo en el cliente: *(HFH ( (H3F*H UNO*O 7H!7F3B!*3HOH45 (HN(O7 (AAH( F7 GG 3F *NHOF *F;< BH N3F( >/ NBFH *3HOH7 7H 7F3B 7F3B - 3B.7F3B S,H(H B 3B.*3!BL NBFH *3HOH7 7H 7F3B 7F3B Q OHS.7F3B S,H(H B OHS.*3!BL (HN(O OHSL HOB UL HOB UL (HN(O OHSL HOBL GG 3FOANFAH plpgslL /> *rea)o$ del r)gger >/ *(HFH (AAH( (A!7F3B!*3HOH7 FUH( O7H( ( NBFH O *NHOF7 U( HF*, (S H\H*NH (*HBN(H 7H!7F3B!*3HOH45L
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: *(HFH ( (H3F*H UNO*O H74)$t0 )$t5 (HN(O7 O F7 GG BH*3F(H \ OL
141
(F7H H\*HO KB)v)s)o$ por eroKL (HN(O %L S,HO others ,HO (F7H O*H KHrrorKL (HN(O %L HOBL GG 3FOANFAH plpgslL
Ejemplo de uso: =d)%% selet test46025L test -----' 41 row5 =d)%% selet test460%5L O*H: B)v)s)o$ por ero test -----% 41 row5
Ejercicio Red Social Asuma el siguiente esquema de bases de datos: usuar)os4>)d0$om=re0ema)l0 $um!am)gos5 am)gos4>)d!usr10 >)d!usr25 )$v)ta)o$es4>)d0 ?eha0 >)d!usr10 >)d!usr20 me$saYe0 estado5
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:
142
CREACION DE TABLAS *(HFH F<3H N7NF(7 4 B 7H(F3 O ON330 O;<(H F(*,F(42%50 H;F3 F(*,F(42%50 ON;!F;A7 O BHUFN3 %5L *(HFH F<3H F;A74 N7(1!B O O ON330 N7(2!B O O ON335L *(HFH F<3H OF*OH7 4 B 7H(F3 O ON330 UH*,F ;H7F; BHUFN3 OS450 N7(1!B O O ON330 N7(2!B O O ON330 H7FB *,F(415 BHUFN3 KK5L
RESTRICCIONES F3H( F<3H N7NF(7 FBB *O7(FO N7(! (;F(M HM4B5L F3H( F<3H F;A7 FBB *O7(FO F;! (;F(M HM4N7(1!B0N7(2!B5L F3H( F<3H OF*OH7 FBB *O7(FO O! (;F(M HM4B5L F3H( F<3H F;A7 FBB *O7(FO F;!N7(!1!U U(HAO HM 4N7(1!B5 (HUH(HO*H7 N7NF(7 4B5L F3H( F<3H F;A7 FBB *O7(FO F;!N7(!2!U U(HAO HM 4N7(2!B5 (HUH(HO*H7 N7NF(7 4B5L F3H( F<3H OF*OH7 FBB *O7(FO O!N7(!1!U U(HAO HM 4N7(1!B5 (HUH(HO*H7 N7NF(7 4B5L
F3H( F<3H OF*OH7 FBB *O7(FO O!H7FB!* *,H* 4H7FB O 4KK0KFK0K(K55L
FUNCION ADD_FRIENDSHIP Adiciona la relación de amistad si se acepta la invitación. Se asume que los dos usuarios no son amigos *(HFH ( (H3F*H UNO*O FBB!U(HOB7,45 (HN(O7 (AAH( F7 GG
FUNCION UPDATE_NUM_AMIGOS 143
Actualiza el número de amigos de un usuario *(HFH ( (H3F*H UNO*O NBFH!ON;!F;A745 (HN(O7 (AAH( F7 GG 7H BH/ NBFH N7NF(7 7H ON;!F;A7ON;!F;A7Q1 S,H(H B O 4OHS.N7(1!B0OHS.N7(2!B5L H37HU A! KBH3HK ,HO NBFH N7NF(7 7H ON;!F;A7ON;!F;A7-1 S,H(H B O 4OHS.N7(1!B0OHS.N7(2!B5L HOB UL (HN(O OHSL HOBL GG 3FOANFAH 3A7T3L *(HFH (AAH( (A!NBFH!ON;!F;A7 FUH( O7H( O F;A7 U( HF*, (S H\H*NH (*HBN(H NBFH!ON;!F;A745L
FUNCION CHECK_SIMETRIA Verifica que no se inserte la relación de amigos B,A si A,B ya son amigos *(HFH ( (H3F*H UNO*O *,H*!7;H(F45 (HN(O7 (AAH( F7 GG BH*3F(H ON; OL 5 O ON; U(; F;A7 S,H(H N7(2!B OHS.N7(1!B FOB N7(1!B OHS.N7(2!BL U ON; % ,HO (F7H H\*HO KMF H\7H 3F (H3F*O BH F;A7 HO(H 8 M 8K0OHS.N7(2!B0OHS.N7(1!BL HOB UL H37U A! KNBFHK ,HO 7H3H* *NO4>5 O ON; U(; F;A7 S,H(H N7(2!B OHS.N7(1!B FOB N7(1!B OHS.N7(2!BL U ON; % ,HO (F7H H\*HO KMF H\7H 3F (H3F*O BH F;A7 HO(H 8 M 8K0OHS.N7(2!B0OHS.N7(1!BL HOB UL HOB UL (HN(O OHSL HOBL GG 3FOANFAH 3A7T3L
*(HFH (AAH( (A!*,H*!7;H(F
FUNCIONE CHECK_INVITACION Verifica que no se inserte una nueva invitación que haya sido previamente rechazada o que las personas ya son amigas *(HFH ( (H3F*H UNO*O *,H*!OF*O45 (HN(O7 (AAH( F7 GG BH*3F(H ON; OL
144
-- er)?)ar ue ya $o ex)sta la rela)o$ e$tre los dos usuar)os 7H3H* *NO4>5 O ON; U(; F;A7 S,H(H 4N7(1!B OHS.N7(1!B FOB N7(2!B OHS.N7(2!B5 ( 4N7(2!B OHS.N7(1!B FOB N7(1!B OHS.N7(2!B5L U ON; % ,HO (F7H H\*HO KMF H\7H 3F (H3F*O BH F;A7 HO(H 8 M 8K0OHS.N7(1!B0OHS.N7(2!BL HOB UL -- er)?)ar ue $o ex)sta u$a )$v)ta)o$ para la m)sma rela)o$ 7H3H* *NO4>5 O ON; U(; OF*OH7 S,H(H H7FB K(K FOB 4 4N7(1!B OHS.N7(1!B FOB N7(2!B OHS.N7(2!B5 ( 4N7(2!B OHS.N7(1!B FOB N7(1!B OHS.N7(2!B5 5L U ON; % ,HO (F7H H\*HO KMF H\7H NOF OF*O HO(H 8 M 8K0OHS.N7(1!B0OHS.N7(2!BL HOB UL -- er)?)ar ue $o ex)sta u$a )$v)ta)o$ rehaDada para la m)sma rela)o$ 7H3H* *NO4>5 O ON; U(; OF*OH7 S,H(H H7FB K(K FOB 4 4N7(1!B OHS.N7(1!B FOB N7(2!B OHS.N7(2!B5 ( 4N7(2!B OHS.N7(1!B FOB N7(1!B OHS.N7(2!B5 5L U ON; % ,HO (F7H H\*HO K3a rela)o$ e$tre 8 M 8 ya ha s)do rehaDadaK0OHS.N7(1!B0OHS.N7(2!BL HOB UL H37U A! KNBFHK ,HO -- *ualu)er )$te$to de atual)Da)o$ de los usuar)os se rehaDa U OHS.N7(1!B 3B.N7(1!B ( OHS.N7(2!B 3B.N7(2!B ,HO (F7H H\*HO K3os atr)=utos N7(1!B y N7(2!B $o puede$ ser mod)?)adosKL HOB UL HOB UL (HN(O OHSL HOBL GG 3FOANFAH 3A7T3L
*(HFH (AAH( (A!*,H*!OF*O
ALGUNAS PRUEBAS O7H( O N7NF(7 4O;<(H0 H;F35 F3NH7 4KN7NF( 1K0KH;F31K50 4KN7NF( 2K0KH;F32K50 4KN7NF( 'K0KH;F3'K50 4KN7NF( "K0KH;F3"K50 4KN7NF( &K0KH;F3&K5L O7H( O OF*OH7 4N7(1!B0 N7(2!B5 F3NH7 41025L
La siguiente inserción es rechazada porque ya existe una invitación entre 2 y 1. Verificada por la FUNCION CHECK_SIMETRIA O7H( O OF*OH7 4N7(1!B0 N7(2!B5 F3NH7 42015L
Ahora se acepta la invitación NBFH OF*OH7 7H H7FB KFK S,H(H B 1L
Se pueden ver las actualizaciones en las tablas USUARIOS y AMIGOS 145
7H3H* > U(; N7NF(7L 7H3H* > U(; F;A7L
Un intento de modificar los usuarios de la invitación conduce a un error: NBFH OF*OH7 7H N7(1!B ' S,H(H B 1L
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 O7H( O OF*OH7 4N7(1!B0 N7(2!B5 F3NH7 4'015L
Actualiza la Invitación NBFH OF*OH7 7H H7FB K(K S,H(H B 2L
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. O7H( O OF*OH7 4N7(1!B0 N7(2!B5 F3NH7 4'015L
Más información en el Manual de Postgres: http://www.postgresql.org/docs/9.2/interactive/plpgsql.html
146
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º 1 2
Apellidos y Nombres Alanya Padilla Alina Susan Alarcon Castro Gustavo Claudio Andres
3 4 5 6 7 8 9 10 11 …
Alarco Lama Ricardo Rafael Altez Yañez Jazmín GApriela Amable Salva Katerin Lisbet Antonio Reyes Freddy Angel Atachagua Cossio Jessica Roxana Aucasi Huanca Angela Marine Avila Salvador Pamely Lorena Kenny Baldeón Balvín Olger 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. ″
147
*(HFH ( (H3F*H UNO*O row$um=er45 (HN(O7 )$teger F7 GG
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. 7H3H* row$um=er45 JOumeroJ0> U(; 47H3H* *.ater$o ++ K K ++ *.;ater$o ++ K K ++ *.Oom=res JFpell)dos y Oom=resJ U(; erso$a.*o$tato * OOH( WO erso$a.ostula$te O .B*o$tato*.B*o$tato (BH(
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.
148
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 datos: Periodo 2005-1 2005-2 2006-1 2006-2 2007-1
101 1 0 0 0 0
309 7 13 17 23 27
310 7 12 18 22 28
Total 15 25 35 45 55
7H3H* )dperaad F7 Jer)odoJ 07N;4*F7H S,HO B*arrera K1%1K ,HO 1 H37H % HOB 5 F7 J1%1J 07N;4*F7H S,HO B*arrera K'%9K ,HO 1 H37H % HOB 5 F7 J'%9J 07N;4*F7H S,HO B*arrera K'1%K ,HO 1 H37H % HOB 5 F7 J'1%J 0*NO4>5 F7 JF3J U(; erso$a.ostula$te A(N
2) Crear una consulta que devuelva los siguientes datos: Periodo 2005-1
101 1
309 7
310 7
Total 15 149
2005-2 2006-1 2006-2 2007-1 TOTAL
0 0 0 0 1
7H3H* )dperaad F7 )dperaad F7 Jer)odoJ 07N;4*F7H 07N;4*F7H S,HO B*arrera K1%1K K1%1K ,HO ,HO 07N;4*F7H 07N;4*F7H S,HO B*arrera K'%9K K'%9K ,HO ,HO 07N;4*F7H 07N;4*F7H S,HO B*arrera K'1%K K'1%K ,HO ,HO 0*NO4>5 F7 0*NO4>5 F7 JF3J U(; erso$a.ostula$te A(N 5 F7 0*NO4>5 F7 total U(; erso$a.ostula$te
13 17 23 27 87
12 18 22 28 87
25 35 45 55 175
1 H37H % HOB 5 F7 J1%1J 1 H37H % HOB 5 F7 J'%9J 1 H37H % HOB 5 F7 J'1%J
1 H37H % HOB 5 F7 J1%1J 1 H37H % HOB 5 F7 J'%9J 1 H37H % HOB 5 F7 J'1%J
FUNCIONES: 3) Crear una función que liste todos los postulantes, la lista debe estar enumerada y ordenada por apellido paterno, materno y nombres. Nº 1 2 3 …
Apellidos y Nombres Alanya Padilla Alina Susan Alarcon Castro Gustavo Claudio Andres Alarco Lama Ricardo Rafael …
*(HFH ( (H3F*H UNO*O ?!eYemplo!"45 (HN(O7 7HU JreordJ JreordJ F7 F7 G<BMG <BMG BH*3F(H r (H*(B (H*(BL L 0> U(; 47H3H* *.ater$o ++ K K ++ *.;ater$o ++ K K ++ *.Oom=res JFpell)dos y Oom=resJ U(; erso$a.*o$tato erso$a.*o$tato * OOH( WO erso$a.ostula$te O .B*o$tato*.B*o$tato (BH(
150
HOB 3L 3 L (HN(O L (HN(OL HOBL HOB L G<BMG <BMG 3FOANFAH KplpgslK KplpgslK 3F3H 3F3HL L --7H3H* > U(; ?!eYemplo!"45 F7 4JOumeroJ OHAH(0 JFpell)dos y Oom=resJ H\5L *(HFH ( (H3F*H UNO*O row$um=er45 (HN(O7 row$um=er45 (HN(O7 )$teger F7 GG
PROCEDIMIENTOS ALMACENADOS: 4) Crear un procedimiento almacenado que permita eliminar los postulantes registrados correspondientes a una modalidad. --7H3H* > U(; erso$a.ost erso$a.ostula$te ula$te (BH( U(; erso$a.ost erso$a.ostula$te ula$te (BH(
VISTAS: 5) Crear una vista que muestre un resumen como el siguiente: Nombre
2005-1
2005-2
2006-1
2006-2
2007-1
Administración
TOTAL
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 151
*(HFH ( (H3F*H HS v!eYemplo!11 F7 4 7H3H* F*.Oom=re07N;4 *F7H S,HO .$gresoK1K .$gresoK1K FOB FOB .BerFad O 4K2%%&-1K K2%%&-1K5 5 ,HO 1 H37H % HOB5 HOB 5 J2%%&-1J J2%%&-1J0 0 7N;4*F7H 7N;4*F7H S,HO .$greso K1K K1K FOB FOB .BerFad O 4 K2%%&-2K K2%%&-2K5 5 ,HO 1 H37H % HOB HOB5 5 J2%%&-2J J2%%&-2J0 0 7N;4*F7H 7N;4*F7H S,HO .$greso K1K K1K FOB FOB .BerFad O 4 K2%%6-1K K2%%6-1K5 5 ,HO 1 H37H % HOB HOB5 5 J2%%6-1J J2%%6-1J0 0 7N;4*F7H 7N;4*F7H S,HO .$greso K1K K1K FOB FOB .BerFad O 4 K2%%6-2K K2%%6-2K5 5 ,HO 1 H37H % HOB5 J2%%6-2J J2%%6-2J0 0 7N;4*F7H 7N;4*F7H S,HO .$greso K1K K1K FOB FOB .BerFad O 4 K2%%-1K K2%%-1K5 5 ,HO 1 H37H % HOB HOB5 5 J2%%-1J J2%%-1J0 0 7N;4*F7H 7N;4*F7H S,HO .$greso K1K K1K FOB FOB .BerFad O 4 K2%%&-1K K2%%&-1K0 0K2%%&-2K K2%%&-2K0 0K2%%6-1K K2%%6-1K0 0K2%%6-2 K2%%6-2K0 K0K2%%K2%%1K5 1K 5 ,HO 1 H37H % HOB HOB5 5 JF3J U(; erso$a.*o$tato * OOH( WO erso$a.ostula$te O .B*o$tato*.B*o$tato OOH( WO Fdm)s)o$.*arrera F* O F* O F*.B*arrera.B*arrera A(N U(; v!eYemplo!11L
152
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 postulantes, la lista debe estar enumerada y ordenada por apellido paterno, materno y nombres. Nº 1 2 3 …
Apellidos y Nombres Alanya Padilla Alina Susan Alarcon Castro Gustavo Claudio Andres Alarco Lama Ricardo Rafael …
7H3H* row!$um=er45 H( 4 (BH(
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.
153
2) Crear una función que liste todos los postulantes de un determinado periodo académico. La lista debe reiniciar la numeración por orden alfabético. Nº 1 2 … 1 2 … 1 2 …
Apellidos y Nombres Alanya Padilla Alina Susan Alarcon Castro Gustavo Claudio Andres … Baldeón Balvín Olger Baldeón Sanabria Natalia Ivonne … Cachuán Cámac Miguel Fernando Cajachagua Chui Jose Arturo …
7H3H* row!$um=er45 H( 4F(O =y su=str)$g4*.ater$o U(; 1 U( 15 (BH(
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 Administración Administración Administración … Contabilidad
Postulantes Cañari Rodriguez Jose Antonio Huamán Huamaní Nidia Anais Ricapa Quispe Nilton César … Benito Dionisio Melissa
Promedio 73.065 73.065 73.065 … 74.369
Puntaje 97 93 89 … 98 154
Contabilidad Contabilidad …
Katherine Salazar Gutiérrez Enrique Paúl Sulca Palomino Ivan …
74.369 74.369 …
98 98 …
7H3H* F*.Oom=re F7 J*arreraJ 0*.ater$o ++ K K ++ *.;ater$o ++ K K ++ *.Oom=res F7 Jostula$tesJ 0rou$d4FA4.u$taYe5 H( 4F(O
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. 7H3H* F*.Oom=re F7 J*arreraJ 0*.ater$o ++ K K ++ *.;ater$o ++ & & ++ *.Oom=res F7 Jostula$tesJ 0(NOB4FA4.u$taYe5 H( w 0'5F7 Jromed)oJ 0.u$taYe F7 Ju$taYeJ U(; erso$a.*o$tato * OOH( WO erso$a.ostula$te O .B*o$tato*.B*o$tato OOH( WO Fdm)s)o$.*arrera F* O .B*arrera F*.B*arrera S,H(H .$gresoK1K SOBS w F7 4F(O
Esta última consulta retornará exactamente lo mismo que en su forma anterior.
155
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. 2. '. ". &. 6. . . 9. 1%. 11. 12.
#C/=)$/=ash #cccc-or: sF?rFscccccA!<OK/usr/=)$/pg!dumpK A!,7KloalhostK A!(K&"'2K A!BFF
-U
-=
-v
-?
Estableciendo Permisos para ejecutar el Script: 1. # hmod Qx pg!=aRup.sh
Cambiando de propietario del script y directorios donde se guardarán los backups, el propietario debe ser el usuario postgres. 2. # how$ postgres:postgres pg!=aRup.sh --7r)pt '. # how$ -( postgres:postgres /var/A
Script para Windows: pg_backup.bat 1. 2. '. ". &. 6.
@eho o?? ::cccc-or: sF?rFsccccc7H A!<O*:PostgreslusP.'(2F7Ppostgresstud)oPpg!dump.exe 7H A!,7loalhost 7H A!(&"'2 7H A!BFF
156
. . 9. 1%. 11. 12. 1'. 1".
7H A!N7H(postgres 7H AF77S(Bpostgres 7H A!F,*:PA
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. 1&. 16. 1. 1. 19. 2%.
# ro$ta= -e
% % > 12 >
/home/usuar)o/pg!=aRup.sh
Auardar *(3 Q 7al)r *(3 Q \
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 Ubicacion de pg_dump , es el binario que realiza los backups PG_BIN PG_HOST dirección del host PG_PORT puerto del servidor PG_DATABASE nombre de la base de datos PG_USER nombre del usuario Contraseña del servidor: Es una variable especial registrada PGPASSWORD en el sistema, el nombre de esta variable debe permanecer inmutable en el sistema operativo.
157
