Scripts SQL Reentrantes

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