DBMS – Postgresql

CONCEPTO DE BASE DE DATOS:

Es una colección de datos relacionados que representa  información                    operacional de una organización.

Tiene las siguientes propiedades implícitas:

* Una base de datos es diseñada, creada y llenada con datos para un propósito específico.

* Una base de datos puede ser de cualquier tamaño y de complejidad variable.

¿Cómo se genera una base de datos?

La base de datos puede ser generada y mantenida manualmente o por medio de una computadora.

Una base de datos computarizada puede ser creada y mantenida por un grupo de programas de aplicación escritos específicamente para esta tarea o por un sistema manejador de base de datos.

Una base de datos representa algún aspecto del mundo real.

SISTEMA MANEJADOR DE BASE DE DATOS(SMBD) (DBMS):

Es un software que tiene como propósito general facilitar el proceso de

definir,  construir y manipular bases de datos que se utilizan para diferentes tipos de aplicaciones.

Componentes de software principales asociados a un SMDB :

•Generadores de aplicación.
•Lenguajes de cuarta generación (4GL).
•Software de consulta a la base de datos.
•Generadores de reportes y pantallas.

Ejemplos de software: postgresql, Mysql, Informix 4GL, Oracle, SQL server…

PROCESOS RELACIONADOS CON LOS SMBD:

*Definir la base de datos significa la declaración de:

•Los tipos de datos
•La estructura
•Las restricciones de los datos a ser almacenados en la base de datos.

* Crear o construir la base de datos: es el proceso de almacenar los datos en algún medio de almacenamiento, esto es controlado por el SMBD.

* Manipular una base de datos incluye funciones como:

•Consultar la base de datos para obtener algunos datos específicos.

•Actualizar la base de datos para reflejar cambios en el minimundo.

•Generar reportes de los datos.
•Eliminar algunos datos

UN SMBD QUE OFRECE :

•Control de redundancias.

•Restricción de accesos no autorizados

•Proporcionar múltiples interfaces de usuario.
•Representar relaciones complejas entre datos.
•Forzar el uso de restricciones de integridad.
•Proporcionar métodos de respaldos y recuperación.
Los tres niveles de arquitectura de un SMBD

Nivel Interno: Definido por el DBA para su optimización.

Vista Externa: Nivel mas alto, visto por el programador  de aplicaciones o el usuario, en esta vista solo porciones de la base de datos son de interés para el usuario o programador de aplicaciones, se representa por el esquema externo.

Vista Conceptual o global : En esta vista se incluyen todas las entidades de la base de datos y las relaciones  entre ellas. La vista conceptual representa la base de datos entera,  definida por el esquema conceptual.

Vista Interna: Nivel mas bajo de abstracción de la base de datos, contiene la definición del almacenamiento de registros, el método de representación de datos y el acceso utilizado, expresado por el esquema interno.

COMPONENTES DE UN SMBD

Lenguaje de Definiciòn de Datos (DDL): Utilizado para definir el esquema conceptual y proporciona detalles acerca de cómo implementar este esquema en los dispositivos físicos utilizados para el almacenamiento de datos.

* Diccionario de datos: contiene la información de los datos y es consultado por el SMBD antes de cualquier operación de manipulación.

* Catálogo del Sistema o Directorio.

*Metadata: Datos acerca de los datos, estructura de la base de datos.

*Definir la base de datos significa la declaración de:

•Los tipos de datos
•La estructura
•Las restricciones de los datos a ser almacenados en la base de datos.
Sentencias DDL:

Las sentencias DDL (Lenguaje de Definición de Datos) son CREATE, ALTER y DROP. Siempre se usan estas sentencias junto con el tipo de objeto y el nombre del objeto.

  • CREATE. Crea una base de datos o un objeto.
  • ALTER. Modifica la estructura de una base de datos o de un objeto.
  • DROP. Elimina una base de datos o un objeto.

Lenguaje de Manipulaciòn de Data (DML): Lenguaje utilizado para manipular los datos, este  permite a que los usuarios puedan llevar a cabo las tareas de consulta o manipulación de los datos.

• Recuperación de datos
• Inserción de nuevos datos
• Borrar datos
• Modificar datos

Manipular una base de datos incluye funciones como:

• Consultar la base de datos para obtener algunos datos específicos  (SELECT)

• Insertar nuevos registros a la base de datos (INSERT INTO)
• Actualizar la base de datos (UPDATE)
• Eliminar algunos datos. (DELETE)
A continuación les voy a coloacr un material, que considero les puede ser de mucha utilidad. De igual forma deseo agradecer a mi Ex-Compañero de trabajo pero siempre amigo Ing. Javier Mendez, el cual tiene ya sumado una cantidad de años como un DBA,  en el IPASME.  Gracias por siempre estar presto a brindar apoyo a los nuevos hombres y mujeres, que se estan formando como futuros Ingeniros e Ingenieras de la Universdidad Politecnica del Oeste Mariscal Sucre (UPOMS):

Este material contiene parte del día a día de un DBA, así ejemplos de  una serie de distintos tipos de consultas:
Este otro documento contiene una gama importante de funciones  y las mismas estan expuestas sus sintaxis y ejemplos, en Postgresql, Mysql y Oracle.
SENTENCIAS QUE PODEMOS UTILIZAR EN NUESTRO SMBD POSTGRESQL:

La sentencia SELECT

La sentencia SELECT nos permite consultar los datos almacenados en una tabla de la base de datos.

A continuación les detallo, la sintaxis para trabajar con esta sentencia:

SELECT [ALL | DISTINCT ]
<nombre_campo> [{,<nombre_campo>}]
FROM <nombre_tabla>|<nombre_vista>
[{,<nombre_tabla>|<nombre_vista>}]
[WHERE <condicion> [{ AND|OR <condicion>}]]
[GROUP BY <nombre_campo> [{,<nombre_campo >}]]
[HAVING <condicion>[{ AND|OR <condicion>}]]
[ORDER BY <nombre_campo>|<indice_campo> [ASC | DESC]
[{,<nombre_campo>|<indice_campo> [ASC | DESC]}]]

Para que tengamos una idea de lo que se refiere cada elemento que integra la sentencia, les indico lo siguiente:

Significado
SELECT Palabra clave que indica la sentencia de SQL que deseamos ejecutar.
  ALL Se refiere que queremos seleccionar todos los valores.Es el valor por defecto y por lo general nunca se especifica.
DISTINCT Indica que queremos seleccionar sólo los valores distintos.
FROM Indica la tabla o conjunto de tablas que se desea recuperar los datos. Si tenemos mas de una tabla entonces  se le denomina a la consulta “consulta combinada” o “JOIN”. En las consultas combinadas es necesario aplicar una condición de combinación a través de una cláusula WHERE. Al disponer mas de una tablas las mismas deben separarlas por comas.
WHERE Especifica una condición que debe cumplirse para que los datos sean devueltos por la consulta. Admite los operadores lógicos AND y OR. y los operadores RELACIONALES.
GROUP BY Lo utilizamos cuando deseamos agrupar los datos de la consulta por algún o algunos campos.
ORDER BY Muestra el resultado ordenado por la o las columnas indicadas. El orden puede expresarse con ASC (orden ascendente) y DESC (orden descendente). El valor por defecto es ASC.

Aquí, les dispongo algunas consultas  (SELECT) que he desarrollado:

Esta consulta, nos permite visualizar todos los egresados de distintas opciones formativas y de distintos programas productivos, de acuerdo al año que se le indique en la condición, en función de un esquema de base de datos que controla la acción docente a nivel nacional.
SELECT g.denominacion as gerencia,p.cedula,p.nombres,p.apellidos,p.direccion,p.tel_hab,p.tel_cel,
s.id_seccion,o.denominacion as opcion, pr.denominacion as programa,sp.estatus
FROM participante as p
INNER JOIN seccion_participante as sp on sp.id_participante = p.id_participante
INNER JOIN seccion as s on s.id_seccion = sp.id_seccion
INNER JOIN gerencia_regional as g on g.id_gerencia_regional = s.id_gerencia_regional
INNER JOIN opcion_formativa as o on o.codigo_opcion = s.codigo_opcion
INNER JOIN programa as pr on pr.codigo_programa = o.codigo_programa
where sp.estatus in (‘finalizado’,’aprobado’) and s.id_tipo_programacion in (177)
order by gerencia,programa,opcion,p.nombres/*Valores para el campo s.id_tipo_programacion*//*Para 2007: 177*/
/*Para 2008: 261*/
/*Para 2009: 306,297*/
/*Para 2010: 313,309,364,377,332*/
/*Para 2011: 337,427,420,432*/

Consulta SQL, que muestra todos los operadores de la tabla “usuarios”, ordenados por estados o regiones:

SELECT r.nombre as estado,u.id_usuario, g.nombre as grupo, u.login, u.password,
u.cedula, u.nombre, u.apellido, u.email, c.cargo, u.id_pregunta, u.respuesta, u.estatus,
u.fecha_reg
FROM usuarios u, grupos g, cargos_ince c, regiones r
WHERE u.id_grupo = g.id_grupo and u.id_cargo = c.id_cargo
and u.id_estado = r.id_regiones
ORDER BY r.nombre

Consulta SQL para mostrar los centros de enseñanza con sus respectivos tipos de ambientes:

SELECT gr.denominacion as Estados, af.descripcion as centro_formacion, m.descripcion as Tipo_Ambiente
FROM ambiente_formacion af, maestros m, gerencia_regional gr
WHERE af.id_tipo_ambiente = m.id_codigo and af.id_gerencia_regional = gr.id_gerencia_regional
ORDER BY Estados

Consulta SQL para mostrar todos los participantes que fueron certificados en las distintas especialidades, durante todo un año:

/*Certificados Por Especialidad*/

/*Se efectua por cada subarea o especialidad existente*/

SELECT g.denominacion,(select count(sc.id_saberes_certificar)

from saberes_certificar as sc, gerencia_regional as g

INNER JOIN saberes as s on s.id_saber = sc.id_saber

INNER JOIN actor_social as a on a.id_actor = sc.id_actor

INNER JOIN evaluacion_certificacion as e on e.id_saberes_certificar = sc.id_saberes_certificar

WHERE sc.estatus in (‘aprobado’,’certificado’) and e.fecha_reg >= ’01/01/2011′ and e.fecha_reg <= ’25/11/2011′

and a.id_gerencia_regional = g.id_gerencia_regional )

ORDER BY g.denominacion

Consulta SQL para mostrar todos los usuarios registrados en la tabla de usuarios, esta consulta muestra la descripción del grupo al que pertenece el usuario así como al estado o región:

SELECT u.id_usuario, g.nombre AS grupo, r.nombre AS estado, u.login, u.password, u.cedula, u.nombre, u.apellido, u.estatus, u.fecha_reg
FROM usuarios u
JOIN grupos g ON u.id_grupo = g.id_grupo
JOIN regiones r ON u.id_estado = r.id_regiones
ORDER BY r.nombre, g.nombre, u.cedula;

Consulta SQl, para mostrar la cantidad de participantes que hay en cada sección:

SELECT s.id_seccion, count(sp.*) AS participantes, sp.estatus
FROM seccion s
JOIN seccion_participante sp ON sp.id_seccion = s.id_seccion
GROUP BY s.id_seccion, sp.estatus;

Consulta SQl, para mostrar la cantidad de participantes que hay en cada estado o región:

SELECT r.id_regiones, r.nombre, count(*) AS cuenta
FROM participante p, regiones r
WHERE p.id_estado_reg = r.id_regiones
GROUP BY r.nombre, r.id_regiones
ORDER BY r.nombre;
¿Donde puedo descargar el SMBD, Postgresql?
Aqui le recomiendo una dirección Web, oficial para que descargue la versión que desee, de acuerdo al sistema operativo que tenga instlado en su computador:
Transacciones:
Una Transacción es un unidad de la ejecución de un programa que accede y, posiblemente, actualiza varios elementos de datos.  Una Transacción está delimitada por instrucciones de inicio transacción y fin transacción (la transacción consiste en todas las operaciones que se ejecutan entre
inicio transacción y fin transacción).

Propiedades ACID:

En bases de datos se denomina ACID a un conjunto de características necesarias para que una serie de instrucciones puedan ser consideradas como una transacción.
De forma específica ACID es un acrónimo de Atomicity, Consistency, Isolation and Durability: Atomicidad, Consistencia, Aislamiento y Durabilidad.
En base a esto las propiedades de las transacciones que debe mantener el sistema de base de datos para garantizar la integridad de los datos, son las siguientes:
Atomicidad: O se realizan adecuadamente, en la base de datos, todas las operaciones de la transacción o no se realiza ninguna de ellas
Consistencia: La ejecución aislada de la transacción (es decir, sin otra transacción que se ejecute concurrentemente) conserva la consistencia de la base de datos.
Aislamiento: Aunque se ejecuten varias transacciones concurrentemente, el sistema garantiza cada transacción ignora al resto de transacciones (para cada Ti el resto de Tj no ha comenzado o ya ha acabado.
Durabilidad: Tras la finalización con éxito de una transacción, los cambios realizados en la base de datos permanecen, incluso si hay fallos en el sistema.

Para esto, el lenguaje de consulta de datos SQL (Structured Query Language), provee los mecanismos para especificar que un conjunto de acciones deben constituir una transacción, las cuales se pueden utilizar perfectamente en nuestro SMBD Postgresql:

  • BEGIN TRAN: Especifica que va a empezar una transacción.
  • COMMIT TRAN: Le indica al motor que puede considerar la transacción completada con éxito.
  • ROLLBACK TRAN: Indica que se ha alcanzado un fallo y que debe restablecer la base al punto de integridad.
  • SAVEPOINT  ( punto de recuperación),  es una forma de implementar subtransacciones (también conocidas como transacciones anidadas) dentro de un smbd indicando un punto dentro de una transacción  de base de datos que puede ser “rolled back” (devuelta) sin afectar a cualquier trabajo realizado en la transacción antes de que el punto de recuperación fuera creado. Varios savepoints pueden existir dentro de una transacción individual.

Un ejemplo muy comun de transacción es el traspaso de una cantidad de dinero entre cuentas bancarias. Normalmente se realiza mediante dos operaciones distintas, una en la que se disminuye el saldo de la cuenta origen y otra en la que se debe incrementar  el saldo de la cuenta destino. Para garantizar la consistencia del sistema (es decir, para que no aparezca o  desaparezca dinero),  las dos operaciones deben ser atómicas, es decir, el sistema debe garantizar que, bajo cualquier circunstancia (incluso una caída del sistema), el resultado final es que, o bien se han realizado las dos operaciones, o bien no se ha realizado ninguna.

Vistas:

Constituyen una herramienta muy útil cuando deseamos mostrar en una sola tabla el contenido de varias. Una vista es como una tabla virtual que almacena una consulta.

Una vista almacena una consulta como un objeto para utilizarse posteriormente. Las tablas consultadas en una vista se llaman tablas base. En general, se puede dar un nombre a cualquier consulta y almacenarla como una vista.

Por lo general a una vista se le llaman tablas virtuales porque los resultados que retorna y la manera de referenciarlas es la misma que para una tabla.

Una utilidad muy práctica en el uso de las vistas, lo constituyen la generación de listados o reportes, ellas permiten que los mismos se ejecuten mucho más rapido, ya que los datos previamente fueron consolidados en una vista y cuando se manda a ejecutar el reporte se va hace el llamado directamente de la vista.

A continuación algunos ejemplos:

Tenemos una tabla llamada usuarios, la cual presenta la siguiente estructura:

Como podrá observar, existen varios campos en esta tabla, que hacen referencia a otras tablas como lo son: id_grupo, id_cargo, id_estado, id_pregunta, en esta tabla sólo se guarda el código de cada uno de esos campos, pero las descripciones o los detalles de las mismas se encuentran, en las otras, aqui pueden observar las restricciones entre esta tabla: usuarios y las otras tablas:

Como pueden observar, las tablas que estan relacionadas son: cargos_ince,  grupos, maestros y regiones. A continuación les dispongo las estructuras de estas tablas:

Asumiendo que en cada una de estas tablas hay registros, y nosotros deseamos gererar un listado de los datos de los usuarios, pero en vez de mostrar los códigos de: cargos, grupo al que pertenece el usuario, pregunta seleccionada, así como el estado al cual pertenece, debemos proceder a realizar la vista, puede hacerla en forma manual o en forma grafica, en forma gráfica, estando dentro de la base de datos a trabajar, debe apuntar a donde dice Vistas, luego le da al boton derecho del mouse y le dice Nueva Vista, aparece la siguiente pantalla:

Aqui podrá identificar tanto el nombre de la vista así como el usuario responsable de la misma, en este caso se esta colocando el usuario postgres, luego de dar clic, sobre la opción Definición, aparece la siguiente pantalla y usted debe escribir el siguiente código, tal como se lo he descrito allí:

Guarde la vista accionando, Aplicar, Ok.

las vistas se ejecutan por medio de una sentencia SELECT, tal como se lo indico a continuación:

Para lograr el mismo efecto, puede ejecutar la creación de la vista de forma manual, accionando clic, en Ejecutar Consultas SQL Arbitrarias y escribe lo siguiente:

— View: vista_usuarios_sistema

— DROP VIEW vista_usuarios_sistema;

CREATE OR REPLACE VIEW vista_usuarios_sistema AS
SELECT u.id_usuario, g.nombre AS grupo, r.nombre AS estado, u.login, u.password, u.cedula, u.nombre, u.apellido, u.estatus, u.fecha_reg
FROM usuarios u
JOIN grupos g ON u.id_grupo = g.id_grupo
JOIN regiones r ON u.id_estado = r.id_regiones
ORDER BY r.nombre, g.nombre, u.cedula;

ALTER TABLE vista_usuarios_sistema OWNER TO postgres;

FUNCIONES

las Funciones son bloques de código que se ejecutan en el servidor. Pueden ser escritos en varios lenguajes de programación, considerando las bombades  que cada uno de ellos proporciona, desde las operaciones básicas de programación, tales como bifurcaciones y bucles, hasta las complejidades de la programación orientada a objetos o la programación funcional.

En la pagina oficial de postgresql, http://www.postgresql.org, podrá encontrar información en detalle sobre este tema, en esta siguiente dirección tiene la información en ingles: http://www.postgresql.org/docs/current/static/sql-createfunction.html y en esta otra  dirección web, tiene la información traducida:  http://translate.google.co.ve/translate?hl=es&langpair=en|es&u=http://www.postgresql.org/docs/current/static/sql-createfunction.html.

A continuación les dispongo un ejemplo muy sencillo del uso de las funciones, es importante observar que la utilidad de las mismas,  cada uno de nosotros la veremos de acuerdo a las necesidades de los desarrollos que nos correspondar afrontar.

EJEMPLOS DE FUNCIONES:

Función para determinar cual es el empleado que tiene el sueldo mayor al que uno indique:

Una vez realizada la función, usted la podrá ejecutar  accionando clic, en Ejecutar Consultas SQL Arbitrarias dentro de PGADMIN y escribe lo siguiente:

select rrhh.fun_sueldo_mayor(2000)

De donde: rrhh, se refiere al esquema creado dentro de la base de datos nomina y fun_sueldo_mayor(), se refiere al nombre de la función creada, en este caso se le esta pasando un valor, en el cual se desea entonces saber cual es el empleado que posea un sueldo mayor a 2000 bs.

En la siguiente pantalla podrá observar el nombre el nombre de la base de datos así como el esquema y el nombre de la función creada, tal como lo observaría en el PGADMIN.

En este sentido el contenido de la función fun_sueldo_mayor, es el siguiente:

DECLARE

cedula integer;

sueldo numeric;

BEGIN

sueldo = $1;

cedula=(SELECT a.cedula

   FROM rrhh.rrhh_personal a, rrhh.rrhh_cargo b, rrhh.rrhh_nomina c, rrhh.rrhh_concepto d

  WHERE a.cod_cargo = b.cod_cargo AND a.cedula = c.cedula AND c.cod_concepto = d.cod_concepto AND c.monto > sueldo::numeric AND c.cod_concepto = 1);

INSERT INTO rrhh.rrhh_cliente_sueldo

    VALUES (cedula,sueldo);

return cedula;

END;

Tal como lo podemos observar en la siguiente pantalla:

No olvide que en la pestaña propiedades, debe usted especificar el nombre la función, asi como el propietario y el lenguaje a utilizar, tal como lo podemos notar en la siguiente pantalla:

A continuación otro ejemplo sencillo del uso de funciones:

Funcion sumar dos numeros:

Una vez realizada la función, usted la podrá ejecutar  accionando clic, en Ejecutar Consultas SQL Arbitrarias dentro de PGADMIN 

y escribe lo siguiente:

Select sumar(2,2);

La función que va a crear se llama: sumar, y la misma debe contener lo siguiente:

declare

valor1 integer;

valor2 integer;

resultado integer;

BEGIN

valor1= $1;

valor2=$2;

resultado:=valor1+valor2;

return resultado;

END;


DISPARADORES – TRIGGERS.

Una de las funcionalidades disponibles en PostgreSQL son los denominados disparadores (triggers). Estos constituyen un elemento muy útil, ya que por medio de ellos podemos ejecutar una diversidad de acciones, las cuales son totalmente transparentes para los usuarios finales que estan trabajando con las aplicaciones.

Un disparador no es otra cosa que una acción definida en una tabla de nuestra base de datos y ejecutada automáticamente por una función programada por nosotros. Esta acción se activará, segun la definamos, cuando realicemos un INSERT, un UPDATE ó un DELETE en la tabla.

Un disparador se puede definir de las siguientes maneras:

  • Para que ocurra ANTES de cualquier INSERT,UPDATE ó DELETE
  • Para que ocurra DESPUES de cualquier INSERT,UPDATE ó DELETE

usted puede ampliar la información teórica en la siguientes direcciones:

http://www.postgresql.org/docs/9.0/static/sql-createtrigger.html

http://www.postgresql.org.es/node/301

http://www.ibiblio.org/pub/linux/docs/LuCaS/Postgresql-es/web/navegable/todopostgresql/triggers.html

EJEMPLOS SENCILLOS DE DISPARADORES:

Funcion trigger: tri_aud_concepto

Para crear la función disparadora, usted debe estar ubicado dentro de la base de datos creada, en nuestro ejemplo nomina, dentro del esquema creado, en nuestro caso rrhh tal como podemos observar en la siguiente pantalla:

Esta funcion al tratar de insertar un nuevo registro de concepto, se guarda en la tabla Tabla de auditoria de concepto, que usuario, que fecha y a que hora se efectúo el cambio.

El contenido de la función del disparador: tri_aud_concepto, es el siguiente:

begin

if (TG_OP=’INSERT’) then

 insert into rrhh.rrhh_auditoria_concepto values (new.cod_concepto, new.des_concepto,new.tipo_concepto,current_user,current_date,current_time);

end if;

return null;

end;

Para que esta función se pueda ejecutar, necesariamente debemos crear en la tabla: rrhh_concepto, el siguiente disparador, llamado: trigger_concepto:

— Trigger: trigger_concepto on rrhh.rrhh_concepto

— DROP TRIGGER trigger_concepto ON rrhh.rrhh_concepto;

CREATE TRIGGER trigger_concepto

AFTER INSERT

ON rrhh.rrhh_concepto

FOR EACH ROW

EXECUTE PROCEDURE rrhh.tri_aud_concepto();

COMMENT ON TRIGGER trigger_concepto ON rrhh.rrhh_concepto IS ‘graba’;

La idea es que cuando uno trata de hacer una inserción de un nuevo concepto, automaticamente se active el disparador y este llama a la function trigger creada.

Si en el PGADMIN, en la opción Ejecutar Consultas SQl Arbitrarias, escribimos lo siguiente:

INSERT INTO rrhh.rrhh_concepto(

            cod_concepto, des_concepto, tipo_concepto)

    VALUES (4, ‘HORAS EXTRAS’, ‘A’);

Se ejecutaría el disparador el cual llama a la función disparadora creada:

Aquí podemos ver como queda, guardada la información en la tabla de rrhh_concepto.

Función disparadora para borrar cargos de los empleados:

Si en el PGADMIN, en la opción Ejecutar Consultas SQl Arbitrarias, escribimos lo siguiente:

delete from rrhh.rrhh_cargo where cod_cargo=4

Desaparecerá de la tabla cargo, el código del cargo indicado, pero la function trigger se dispara y guarda una copia de los datos, en la tabla de auditoria de cargo.

Al borrar algún cargo, este hace disparar el trigger borra cargo, para que almacene en la auditoria de cargo, quien borro, que borro cuando borro, al dispararse la función de trigger, borra cargos.

Función triggers:

 begin

if (TG_OP=’DELETE’) then

insert into rrhh.rrhh_auditoria_cargo values (old.cod_cargo,old.des_cargo,current_user,current_date,current_time);

end if;

return null;

end;

Triggers, realizado sobre la tabla cargo:

— Trigger: tri_borrar_cargos on rrhh.rrhh_cargo

— DROP TRIGGER tri_borrar_cargos ON rrhh.rrhh_cargo;

CREATE TRIGGER tri_borrar_cargos

  AFTER DELETE

  ON rrhh.rrhh_cargo

  FOR EACH ROW

  EXECUTE PROCEDURE rrhh.fun_tri_borrar_cargos();

COMMENT ON TRIGGER tri_borrar_cargos ON rrhh.rrhh_cargo IS ‘guarda en la tabla de auditoria los cargos que puedan ser borrados’;

Aquí les planteo otro ejemplo sencillo, para que observen la utilidad de los disparadores:

Vamos a suponer que tenemos una tabla en una base de datos llamada “preinscripcion”, la misma posee la siguiente estructura:  (Para que ono pierda tiempo puede copiar la siguiente estructura ingresar a su Postgresql, dar clic, en el icono que indica “Ejecutar Consultas SQL”, en la ventana que se apertura, pega lo siguiente y posteriormente, le da clic en el icono de su Postgresql, que indica “Ejecutar Consulta”, verá que automaticamente se creará la tabla “preincripcion”, sobre alguna base de datos que usted tenga creada por ejemplo base de datos “gestion_academica”.

— Table: preinscripcion

— DROP TABLE preinscripcion;

CREATE TABLE preinscripcion
(
id_preinscripcion integer NOT NULL ,
codigo_opcion character varying(20) NOT NULL,
id_participante integer NOT NULL,
estatus character varying(20),
fecha_reg date NOT NULL DEFAULT now(),
CONSTRAINT preinscripcion_pkey PRIMARY KEY (id_preinscripcion),
)
WITH (
OIDS=FALSE
);
ALTER TABLE preinscripcion OWNER TO postgres;

Luego que un estudiante se preinscribe el campo estatus se almacena: “preinscrito”.

Ahora bien, nos imaginamos que dentro de nuestra base de datos, tambien tenemos una tabla donde registramos a los participantes de los cursos, en este sentido se tiene la siguiente estructura de la tabla llamada: “participante”:

— Table: participante

— DROP TABLE participante;

CREATE TABLE participante
(
id_participante serial NOT NULL,
cedula bigint NOT NULL,
tipo_doc character(1), — si@Tipo de Documento:@Op|Cedula=C,Pasaporte=P
nombres character varying(50) NOT NULL,
apellidos character varying(50) NOT NULL,
nacionalidad character(1), — si@Nacionalidad:@Op|Venezolano=V,Extranjero=E
fecha_nac date NOT NULL,
genero character(1), — si@Genero:@Op|Masculino=M,Femenino=F
CONSTRAINT pk_tblparticipante PRIMARY KEY (id_participante),
)
WITH (
OIDS=FALSE
);
ALTER TABLE participante OWNER TO postgres;

la idea es la siguiente:

En el momento que se inserte un estudiante en la tabla “participante”, de inmediato se debe activar una función disparadora, la cual cambiaría el valor  del campo  estatus de la tabla: “preincripcion”.

En este sentido la función disparadora la vamos a llamar “cambiar_estatus” y quedaría así:

— Function: cambiar_estatus()

— DROP FUNCTION cambiar_estatus();

CREATE OR REPLACE FUNCTION cambiar_estatus()
RETURNS trigger AS
$BODY$
DECLARE
BEGIN

UPDATE preinscripcion SET estatus = ‘inscrito’ WHERE cedula = NEW.cedula;

RETURN NULL;
END;
$BODY$
LANGUAGE ‘plpgsql’ VOLATILE
COST 100;
ALTER FUNCTION cambiar_estatus() OWNER TO postgres;

Ahora debemos  considerar en que momento se va a ejecutar esta función disparadora (Triggers Function), esta se ejecutará cada vez que un usuario inserte un nuevo estudiante a la tabla “participante”, por lo tanto, nosotros debemos crear a la tabla “participante”,  el siguiente disparador (Triggers):

— Trigger: cambiar_estatus on participante

— DROP TRIGGER cambiar_estatus ON participante;

CREATE TRIGGER cambiar_estatus
AFTER INSERT
ON participante
FOR EACH ROW
EXECUTE PROCEDURE cambiar_estatus();

Este disparador lo que hace es: Después que ocurra un insert into, una inserción de registro sobre la tabla “particioante”, se llamara a que se ejecute la función disparadora llamada “cambiar_estatus”.

Es importante considerar que estos ejemplos para que puedan funcionar adecuadamente usted debe crear la base de datos, el esquema y las tablas respectivas, le invito a que analice lo que aqui le dispuse y usted cree su propio ambiente de trabajo y desarrolle sus propias funciones y disparadores. En las pantallas dispuestas, asi como en las sentencias SQL mostradas en los  ejemplos tiene: base de dato, esquema, tablas, funciones, disparadores y estructura de la tabla, sin embargo usted puede crear su propio ambiente y generar sus propios ejemplos.

A continuación les dispongo un extracto de un sistema que desarrolle,  se los coloco aquí, sólo para que lo puedan utilizar como referecnia para que observen la manera como desde PHP podemos interactuar con nuestro Sistema Manejador de base de Datos Postgresql,  Aquí podran observar una de las tantas formas que podemos programar para ejecutar los procesos de mantenimiento básico sobre una tabla (Incluir, modificar, eliminar),  dentro del programa les he colocado alguna teoría que sustenta los comandos o funciones utilizadas.

les agradecería no se queden sólo con esta información, ustedes pueden buscar ampliar estos conocimientos tanto en la web así como en los valisos libros de programación bajo PHP:

La tabla que estoy utilizando se llama: urbanismos, aquí les coloco la estructura:

— Table: urbanismos

— DROP TABLE urbanismos;

CREATE TABLE urbanismos
(
id_urbanismo serial NOT NULL,
id_estado integer NOT NULL,
id_municipio integer NOT NULL,
id_parroquia integer NOT NULL,
nombre_urbanismo character varying(50) NOT NULL,
direccion_urbanismo character varying(50) NOT NULL,
CONSTRAINT pk_urbanismos PRIMARY KEY (id_urbanismo)
)
WITH (
OIDS=FALSE
);
ALTER TABLE urbanismos OWNER TO postgres;

A continuación les dispongo las pantallas, las cuales estan programadas en HTML:

Aquí pueden observar arriba a la derecha los iconos representativos de los procesos de Incluir, Eliminar y Modificar.

En esta pantalla, pueden notar la apertura del formulario para efectuar la carga de los datos, dispuestos en cada uno de los campos:

Finalmente, luego que se registran los datos solicitados en el formulario y se acciona clic sobre el icono que muestra un diskette, dicho registro se guarda y el sistema retorna a la siguiente pantalla:

// Inicio del programa

<?

/*Aquí les dispongo algunos basamentos teóricos que sustentan ciertas instrucciones o comandos usadas en estos estractos de programas
que les dispongo, sólo de ejemplo, para que observen como podemos ejecutar las acciones de mantenimiento básicas (Incluir, Modificar,
Eliminar registros sobre tablas almacenadas en el SMBD Postgresql.

$_POST, $_GET

Cuando deseamos enviar o recibir de datos existen una serie de normas no escritas sobre qué método usar.

Recuerden que para lograr efectuar el envio o recepción de datos necesitaremos un formulario HTML
Para enviar los datos. En la etiqueta <form> será dónde definiremos la propiedad method que podrá tener los siguientes valores:

post: Envía ocultos los datos
get: Envía los datos en la URL

Es importante tener en cuenta  en qué tipo de operaciones podemos usar get y post.

Post:

Para las operaciones de modificación de registros de una Base de datos (Insert, Delete y Update) .
Para formularios de acceso.

Get:

Consultas  (Select)

—————————————–
utf8_encode, utf8_decode

UTF-8 (8-bit Unicode Transformation Format) es un formato de codificación estandar de caracteres.

La instrucción “utf8_encode o utf8_decode ”  es utilizada para lograr que el navegador interprete adecuadamente las cadenas de caracteres.
En reiteradas oportunidades las páginas creadas con PHP con cofificación UTF-8 devuelven algunos caracteres (acentos, interrogaciones, eñes, etc) con mal aspecto, debido a que la codificación de algunos de los fragmentos del texto no coinciden con la del documento, y eso hace que el navegador no los interprete adecuadamente.
——————————————
$this->

Cuando trabajamos bajo programación orientada a objeto, durante la ejecución de un método de un objeto, automaticamente se crea la varible $this, está hace referencia al objeto en el que estamos trabajando. Es decir, desde un método podemos acceder a otros
métodos o propiedades dentro del objeto.

Para acceder a una propiedad o métodos usamos -> con el signo $ por delante de la propiedad, es decir $this->nombre
De la misma manera cuando tenemos un objeto instanciado y queremos acceder a los métodos o propiedades,
$objeto->getNombre() u $objeto->nombre
——————————————
pg_query, pg_pconnect()

Para establecer una conexión persistente, debemos utilizar la función pg_pconnect() con los mismos parámetros que ven aqui.
A continuación, se utiliza la función pg_query() para lanzar la consulta a la base de datos.
Para comprobar errores, la API de PostgreSQL distingue entre un error de conexión, y errores sobre los recursos devueltos.
En el primer caso, deberemos usar pg_connection_status(), mientras que en el segundo podemos optar por pg_last_error() o bien pg_result_error($recurso) para obtener el mensaje de error que pueda haber devuelto un recurso en concreto.

La función pg_query() puede devolver los siguientes resultados:
• FALSE si ha habido un error.
• Una referencia a una estructura si la sentencia ha tenido éxito.

La función pg_affected_rows($recurso) nos permite conocer el número de filas que se han visto afectadas por sentencias
de actualización, borrado o inserción. Esta función deberá recibir como parámetro el recurso devuelto por
la función pg_query().

La función pg_num_rows($recurso) nos permite conocer el número de filas devuelto por sentencias de consulta.
Una vez obtenido el recurso a partir de los resultados de la consulta, PHP proporciona multitud de formas de iterar sobre sus resultados o de acceder a uno de ellos directamente.

Le indico de igual forma que:

• $fila = pg_fetch_array($recurso, <tipo_de_array>)
Esta función va iterando sobre el recurso, devolviendo una fila cada vez, hasta que no quedan más filas y devuelve FALSE.
La forma del array devuelto, dependerá del parámetro <tipo_de_array> que puede tomar estos valores:

• PG_NUM: devuelve un array con índices numéricos para los campos. Es decir, en $fila[0] tendremos el primer campo
del SELECT, en $fila[1], el segundo, etc.
• PG_ASSOC: devuelve un array asociativo donde los índices son los nombres de campo o alias que hayamos indicado en la sentencia SQL.
• PG_BOTH: devuelve un array con los dos métodos de acceso.

• $objeto = pg_fetch_object($recurso)

Esta función va iterando sobre los resultados, devolviendo un objeto cada vez, de forma que el acceso a los datos de cada campo se realiza por medio de las propiedades del objeto. Al igual que en el array asociativo, hay que vigilar con los nombres de los
campos en consulta, evitando que devuelva campos con el mismo nombre fruto de combinaciones de varias tablas, ya que sólo podremos acceder al último de ellos.

Podemos pasar a la función pg_fetch_object() un segundo parámetro para indicar la fila concreta que queremos obtener:
$resultado = pg_fetch_all($recurso)
Esta función devuelve toda la hoja de datos correspondiente a $recurso; es decir, una array con todas las filas y columnas
que forman el resultado de la consulta.

*/

// Funciones utilizadas


function AccionRegistrarUrbanismos(){
$Sql=”insert into \”urbanismos\” (\”id_estado\”,\”id_municipio\”,\”id_parroquia\”,
\”nombre_urbanismo\”,\”direccion_urbanismo\”)values (‘”.$_SESSION[‘id_estado’].”‘,
‘”.$_POST[‘id_municipio’].”‘,'”.$_POST[‘id_parroquia’].”‘,'”.utf8_encode($_POST[‘nombre_urbanismo’]).”‘,
‘”.utf8_encode($_POST[‘direccion_urbanismo’]).”‘)”;
$Resultado=$this->ConsultaBD($Sql);
$Retorno='<blink>Urbanismo Ingresado Exitosamente</blink>’;
return $Retorno;
}

function AccionModificarUrbanismos(){
$Sql=”update \”urbanismos\” set \”id_municipio\”='”.$_POST[‘id_municipio’].”‘,
\”id_parroquia\”='”.$_POST[‘id_parroquia’].”‘,\”nombre_urbanismo\”='”.utf8_encode($_POST[‘nombre_urbanismo’]).     “‘,\”direccion_urbanismo\”='”.utf8_encode($_POST[‘direccion_urbanismo’]).”‘
where \”id_urbanismo\”='”.$_POST[‘id_urbanismos’].”‘”;
$Resultado=$this->ConsultaBD($Sql);
$Retorno='<blink>Modificado Exitosamente</blink>’;
return $Retorno;
}
function AccionEliminarUrbanismos(){
$Sql=”select * from \”participantes\” where \”id_refugio\”='”.$_GET[‘id_urbanismos’].”‘ and \”tipo_refugio\”=1″;
$Resultado=$this->ConsultaBD($Sql);
$Lista=$this->CantidadResultados($Resultado);
if($Lista==0){
$Sql=”delete from \”urbanismos\” where \”id_urbanismo\”='”.$_GET[‘id_urbanismos’].”‘”;
$Resultado=$this->ConsultaBD($Sql);
$Retorno='<blink>El Urbanismo fu&eacute; Eliminado Satisfactoriamente</blink>’;
}else{
$Retorno='<blink>No se puede Eliminar porque tiene Registros Asociados</blink>’;
}
return $Retorno;
}
}

//Funciones públicas


public function ConsultaBD($Sql){
$Link=$this->CrearConexion();
if($this->ManejadorBD==’postgresql’){
if(!@$Resultado=pg_query($Link,$Sql)){
echo(‘error: ‘.utf8_decode(pg_last_error($Link)));
die(“Disculpe. Error de conectividad con la tabla base de datos! intente mas tarde”);
}
}
}

$this->CerrarConexion($Link);
return $Resultado;
}

public function CrearConexion(){
if($this->ManejadorBD==’postgresql’){
$Conexion=’host=’.$this->Servidor.’ port=’.$this->Puerto.’ dbname=’.$this->BaseDatos
.’ user=’.$this->Usuario.’ password=’.$this->Clave;
if(!$Link=pg_connect($Conexion)){
echo(‘error: ‘.utf8_decode(pg_last_error($Link)));
die(“Disculpe. Error de conectividad con el servidor! intente mas tarde”);
}
}
return $Link;
}

public function CerrarConexion($Link){
if($this->ManejadorBD==’postgresql’){
pg_close($Link);

// pg_close: Función que permite cerrar la conexió  a la base de datos
}
}

public function CantidadResultados($Resultado){

if($this->ManejadorBD==’postgresql’){
$CantidadResultados= pg_num_rows($Resultado);
}
return $CantidadResultados;
}
// Fin del programa
?>



6 pensamientos en “DBMS – Postgresql”

  1. Apreciado amigo Ivan.

    Fijese lo siguiente anteriormente era algo dificil el poder realizar una pagina web, ya que necesariamente debias aprender muchas herramientas de diseño grafico, aparte de conocer un lenguaje de programación llamado html, hoy por hoy es muy facil crear una pagina web ya que en internet encontraras muchos servicios completamente gratuitos para que lo logres.

    En este sentido te recomiendo este servicio llamado blog o weblog, por medio de este lograras crear de una forma rapida tu pagina web.

    Este servicio como puedes ver que tengo creada la mia http://www.wordpress.com, por aqui sólo siguiendo unos pasos super sencillos lograras crear tu pagina de forma sencilla.

    En otro orden de ideas te indico existen una gran cantidad de servicios que brinda la web 2.0 como por ejemplo: joomla, phpnuke, mambo, etc, los cuales son cms que puedes descargar de internet y en tiempo record logras montar tremendo portal web, ojo: debes tener presente que al utilizarlos debes tener un web server, junto con la ip pública para poder subirla.

    Un placer y estamos en contacto.

  2. Amigo, estuve observando tu pagina web y en realidad apoyo la idea de compartir los conocimientos.. Esa es la filosofia principal del Software Libre.. Actualmente estoy desempleado pero con la frente en alto tratando de formarme en el area de diseño y programacion de paginas web, soy TSU en Informática y tengo experiencia en el area de soporte tecnico nivel 1,2, y hasta he realizado algunos proyectos en las compañias en las que he trabajado, hace 2 años hice un curso de Linux y me gradue como Profesional Certificado Linux.. Ahora bien, te escribo para FELICITARTE por la iniciativa, y para consultarte algo. Como te comente estoy ingresando enel mundo de las paginas web, bd, diseños, etc.. incluso hace poco hice un curso de DreamweaverCS4 y obtuve mi certificado Adobe International. Quería saber si actualmente en Venezuela funcionará algun software como el que provee NO-IP, en el cual registras temporalmente un dominio que se ata a tu IP actual dinamica.. ?, ya que como comento el compañero anterior trato de que mi pagina web se vea desde un cyber. Gracias por la respuesta.

    1. Amigo Luis Miguel, realmente le doy las gracias por sus comentarios y adicionalmente le deseo muchos exitos en su futuro profesional, en lo que respecta a su pregunta dejeme investigar al respecto ya que hasta el momento las que he visitado no cumplen con esa bondad, pero dejeme voy a consultar a mis amigos de la comunidad SOLVE, y espero pronto poder responderle de forma positiva.

  3. Buenas Tardes Prof Fuentes le escribo para saber si podría usted participar como ponente en el Seminario Tendencias de las TIC en Venezuela, evento que se realizara el jueves 13 de enero del 2011, en la Unefa Chuao, si me permite un correo le podría hacer llegar la invitación formal y decirle cual seria el punto del temario que desearía que pudiera abordar, muchas gracias de ante mano saludos.

    1. Apreciados organizadores del Evento: Seminario Tendencias de las TIC en Venezuela.

      Para mi constituye un gran privilegio, el poder participar en tan importante evento. Cuenten ustedes con mi presencia en el mismo, esperando nunca defraudarles con mi humilde participación. Mi correo: cedavilu@gmail.com.
      Estaré bien pendiente para recibir el temario, para así preparar y enviarles con suficiente anticipación mi ponencia.

      De antemano, muy agradecido por su invitación.

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

A %d blogueros les gusta esto: