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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 |
-- -------------------------------------------------------------------- -- 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
