Scripts SQL Reentrantes

Carlos García Pérez

Técnico especialista en informática de empresa (CEU).

Ingeniero Técnico en Informática de Sistemas (UPM)

Creador de MobileTest, Haaala!, Girillo, toi18n.

Charla sobre desarrollo de aplicaciones en Android.

@cgpcosmad

Ver todos los tutoriales del autor

Fecha de publicación del tutorial: 2008-06-30

Tutorial visitado 7.419 veces Descargar en PDF

Scripts SQL Reentrantes

A medida que un software evoluciona suele ser necesario realizar modificaciones en el modelo de datos.

Para realizar estas modificaciones, a mi particularmente me gusta realizar un script DDL y otro DML que pueda ser ejecutado N veces (reentrante) sin que se produzcan errores.

A continuación y a modo de consulta os voy a mostrar unos script de ejemplo autocomentados en donde creamos un esquema de BD, unas tablas, índices, restricciones y campos.

Ejemplo en MySql
Ejemplo en SQLServer

Ejemplo en MySql.

DELIMITER $$

-- Creamos el Schema si no existe
CREATE SCHEMA IF NOT EXISTS db_test;

DROP PROCEDURE IF EXISTS db_test.procedureTemp;

CREATE PROCEDURE db_test.procedureTemp()
BEGIN

-- --------------------------------------------------------------------
  -- Declaración de variables
  -- --------------------------------------------------------------------

DECLARE isOk    BOOL DEFAULT TRUE;
  DECLARE cuenta  INT DEFAULT 0;
  DECLARE cursor1 CURSOR FOR SELECT COUNT(*) FROM db_test.companies WHERE companykey='Autentia';

-- Al crear un índice que ya existe se genera el siguiente error
  DECLARE CONTINUE HANDLER FOR 1061 SET isOk = False;

-- Al crear un Foreign Key que ya existe se genera el siguiente error
  DECLARE CONTINUE HANDLER FOR 1005 SET isOk = False;

-- Al eliminar un Foreign Key que no existe se genera el siguiente error
  DECLARE CONTINUE HANDLER FOR 1025 SET isOk = False;

-- Para más información sobre los errores:
  --   http://dev.mysql.com/doc/refman/5.0/en/declare-handlers.html
  --   http://dev.mysql.com/doc/refman/5.0/en/error-messages-server.html

-- --------------------------------------------------------------------------------------------------------------------------------------
  -- Creamos una tabla si no existiese, para ello consultamos las tablas de MySQL que mantienen metainformación sobre los esquemas.
  -- Representa los datos de empresas
  -- --------------------------------------------------------------------------------------------------------------------------------------

SELECT COUNT(*) INTO cuenta FROM `information_schema`.`tables` WHERE TABLE_SCHEMA='db_test' AND TABLE_NAME='companies' LIMIT 1;
  IF (cuenta = 0)  THEN
      CREATE TABLE db_test.companies (
          id         INT(3)      UNSIGNED NOT NULL AUTO_INCREMENT,
          companykey VARCHAR(25) NOT NULL,
          name       VARCHAR(100) NOT NULL,
          url        VARCHAR(255),
          PRIMARY KEY  (id),
          UNIQUE companyKeyIDX (companykey)        -- No puede haber dos empresas con el mismo identificador
      ) ENGINE=InnoDB CHARSET=utf8 collate=utf8_general_ci;
  END IF;

-- --------------------------------------------------------------------
  -- Creamos una tabla si no existiese.
  -- Representa los datos de empleados (contactos) dentro de cada empresa
  -- --------------------------------------------------------------------

SELECT COUNT(*) INTO cuenta FROM `information_schema`.`tables` WHERE TABLE_SCHEMA='db_test' AND TABLE_NAME='employees' LIMIT 1;
  IF (cuenta = 0)  THEN
      CREATE TABLE db_test.employees (
          id          INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
          employeekey VARCHAR(25) NOT NULL,
          companyid   INT(3)  UNSIGNED NOT NULL,
          name        VARCHAR(20),
          lastNames   VARCHAR(30),
          PRIMARY KEY  (id),
          UNIQUE employeeIdx (companyid, employeekey),    -- No puede haber dos empleados en la misma empresa con el mismo identificador
          KEY    companyIDX  (companyid),                 -- Indice necesario para crear la restricción Foreign Key con la tabla companies
          CONSTRAINT empToCompFK FOREIGN KEY (companyid) REFERENCES companies(id) ON DELETE CASCADE ON UPDATE CASCADE
      ) ENGINE=InnoDB CHARSET=utf8 collate=utf8_general_ci;
  END IF;

-- ------------------------------------------------------------------------------------------------------
  -- Insertamos una empresa de prueba si no existiese (Lo vamos a hacer a través de un cursor)
  -- ------------------------------------------------------------------------------------------------------

OPEN  cursor1;
  FETCH cursor1 INTO cuenta;
  IF (cuenta = 0)  THEN
      INSERT INTO db_test.companies (id, companykey, name, url) VALUES (1, 'Autentia', 'Autentia Real Business Solutions', 'http://www.autentia.com');
  END IF;
  CLOSE cursor1;

-- ------------------------------------------------------------------------------------------------------
  -- Insertamos un empleado de prueba si no existiese. (Lo vamos a hacer a traves de SELECT INTO)
  -- ------------------------------------------------------------------------------------------------------

SELECT COUNT(*) INTO cuenta FROM db_test.employees WHERE ((companyid=1) AND (employeekey='carlosgarcia'));
  IF (cuenta = 0)  THEN
      INSERT INTO db_test.employees (companyid, employeekey, name, lastNames) VALUES (1, 'carlosgarcia','Carlos', 'García Pérez');
  END IF;

-- ------------------------------------------------------------------------------------------------------
  -- Creamos el campo email del empleado si no existe
  -- ------------------------------------------------------------------------------------------------------

SELECT COUNT(*) INTO cuenta FROM `information_schema`.`COLUMNS` WHERE ((TABLE_SCHEMA='db_test') AND (TABLE_NAME='employees') AND (COLUMN_NAME='email')) LIMIT 1;
  IF (cuenta = 0)  THEN
      ALTER TABLE db_test.employees ADD COLUMN email VARCHAR(100) NULL;
  END IF;

-- ------------------------------------------------------------------------------------------------------
  -- Se accede mucho por el email del empleado, vamos a crear un indice por el campo email del empleado
  -- ------------------------------------------------------------------------------------------------------

-- Notas: No existe en el esquema una tabla que muestre los indices (Aunque si las restricciones en la tabla KEY_COLUMN_USAGE)
  -- Una forma de recuperarlos sería a través de un cursor y "SHOW INDEX FROM db_test.employees", Pero es más fácil hacerlo
  -- controlando el error 1061 que se genera segun la documentación.
  ALTER TABLE db_test.employees ADD INDEX `idxEmployeeEmail` (email);

-- ------------------------------------------------------------------------------------------------------
  -- Creamos una Foreign Key entre la tabla employess y companies
  -- ------------------------------------------------------------------------------------------------------

SELECT COUNT(*) INTO cuenta  FROM `information_schema`.`KEY_COLUMN_USAGE` WHERE (CONSTRAINT_SCHEMA='db_test')   AND 
                                                                                  (CONSTRAINT_NAME='FK_Temporal') AND 
                                                                                  (TABLE_NAME='employees');
  IF (cuenta = 0)  THEN
      ALTER TABLE db_test.employees ADD CONSTRAINT `FK_Temporal` FOREIGN KEY `FK_Temporal` (companyid) REFERENCES companies(id) ON DELETE CASCADE ON UPDATE CASCADE;
  END IF;

-- Borramos una ForeignKey.Si el Foreign Key no existiese se produciría el error 1025 (Como tenemos controlado el error el Script continuará con normalidad)
  ALTER TABLE db_test.employees DROP FOREIGN KEY `FK_Temporal`;

END

DELIMITER ;

-- Invocamos el procedimiento almacenado
CALL db_test.procedureTemp();

-- Borramos el procedimiento almacenado
DROP PROCEDURE IF EXISTS db_test.procedureTemp;

Ejemplo en SQLServer.

-- --------------------------------------------------------------------
-- Creamos una base de datos si no existiese.
-- --------------------------------------------------------------------

IF NOT EXISTS (SELECT * from sys.databases where name = 'db_test')
BEGIN
	CREATE DATABASE db_test;
END

-- Establecemos la  base de datos prodeterminada
USE db_test;

-- --------------------------------------------------------------------
-- Creamos una tabla si no existiese.
-- Representa los datos de empresas
-- --------------------------------------------------------------------

IF NOT EXISTS (SELECT * FROM sys.sysobjects WHERE name='companies' AND xtype='U')
BEGIN
	CREATE TABLE companies (
	  id         INTEGER      NOT NULL IDENTITY,
	  companykey VARCHAR(25)  NOT NULL,
	  name       VARCHAR(100) NOT NULL,
	  url        VARCHAR(255),
	  PRIMARY KEY (id)  
	);
END

-- --------------------------------------------------------------------
-- Creamos un índice si no existe.
-- No puede haber dos empresas con el mismo identificador
-- --------------------------------------------------------------------

IF NOT EXISTS (SELECT * FROM sys.sysindexes WHERE name='companyKeyIDX' AND id=(SELECT id FROM sys.sysobjects WHERE name='companies'))
BEGIN
	CREATE UNIQUE INDEX "companyKeyIDX" ON companies (companykey);	
END

-- --------------------------------------------------------------------
-- Creamos una tabla si no existiese.
-- Representa los datos de empleados (contactos) dentro de cada empresa
-- --------------------------------------------------------------------

IF NOT EXISTS (SELECT * FROM sys.sysobjects WHERE name='employees' AND xtype='U')
BEGIN
	CREATE TABLE employees (
	  id          INTEGER		NOT NULL IDENTITY,
	  employeekey VARCHAR(25)	NOT NULL,
	  companyid   INTEGER		NOT NULL,
	  name        VARCHAR(20),
	  lastNames   VARCHAR(30),
	  PRIMARY KEY  (id),
	  CONSTRAINT employeeIdx UNIQUE (companyid, employeekey)    -- No puede haber dos empleados en la misma empresa con el mismo identificador
	)
END

-- ------------------------------------------------------------------------------------------------------
-- Insertamos una empresa de prueba si no existiese
-- ------------------------------------------------------------------------------------------------------

IF NOT EXISTS (SELECT id FROM companies WHERE companykey='Autentia')
BEGIN
	SET IDENTITY_INSERT companies ON;	-- Necesario para poder insertar en un campo IDENTITY (Autonumérico)
	INSERT INTO companies (id, companykey, name, url) VALUES (1, 'Autentia', 'Autentia Real Business Solutions', 'http://www.autentia.com');
	SET IDENTITY_INSERT companies OFF;
END

-- ------------------------------------------------------------------------------------------------------
-- Insertamos un empleado de prueba si no existiese. (Lo vamos a hacer a traves de SELECT INTO)
-- ------------------------------------------------------------------------------------------------------

IF NOT EXISTS (SELECT id FROM employees WHERE ((companyid=1) AND (employeekey='carlosgarcia')))
BEGIN   
	INSERT INTO employees (companyid, employeekey, name, lastNames) VALUES (1, 'carlosgarcia','Carlos', 'García Pérez');
END

-- ------------------------------------------------------------------------------------------------------
-- Creamos el campo email del empleado si no existe
-- ------------------------------------------------------------------------------------------------------

IF NOT EXISTS (SELECT id FROM sys.syscolumns where name='email' and id=(SELECT id FROM sys.sysobjects WHERE name='employees' AND xtype='U'))
BEGIN
	ALTER TABLE employees ADD email VARCHAR(100) NULL;
END

-- ------------------------------------------------------------------------------------------------------
-- Se accede mucho por el email del empleado, vamos a crear un indice por el campo email del empleado
-- ------------------------------------------------------------------------------------------------------

IF NOT EXISTS (SELECT id from sys.sysindexes WHERE ((name='idxEmployeeEmail') AND (id=(SELECT id FROM sys.sysobjects WHERE name='employees' AND xtype='U'))))
BEGIN
	CREATE INDEX "idxEmployeeEmail" ON employees (email);
END

-- ------------------------------------------------------------------------------------------------------
-- Creamos una Foreign Key entre la tabla employess y companies
-- ------------------------------------------------------------------------------------------------------

IF NOT EXISTS (SELECT * from sys.objects WHERE (name='empToCompFK') AND (type='F') AND (parent_object_id=(SELECT id FROM sys.sysobjects WHERE name='employees' AND xtype='U')))
BEGIN
	ALTER TABLE employees ADD CONSTRAINT empToCompFK  FOREIGN KEY (companyid) REFERENCES companies(id) ON DELETE CASCADE ON UPDATE CASCADE;
END

Bueno, espero que os haya servido de utilidad este pequeño tutorial, yo personalmente lo usaré a modo de consulta, pues hay veces que uno se tira mucho tiempo en otros entornos y luego vienen bien estos tutoriales para refrescar la memoria ;-).

Carlos García Pérez. Creador de MobileTest, un complemento educativo para los profesores y sus alumnos.
cgpcosmad@gmail.com