Trabajando con el lenguaje de bases de datos hay cosas que no son tan sencillas de hacer. Por un lado, una base de datos aporta dinamicidad a una página web (Dynamic SQL) . Es decir, aporta diferentes contenidos que se pueden ir actualizando, se pueden realizar busquedas, registrar usuarios.
Todo esto se puede hacer con una estructura de bases de datos estática. O sea que tendremos siempre el mismo número de tablas e iremos trabajando sobre ellas.
Pero ahora pensemos que queremos extender la estructura de nuestra base de datos a medida que se desarrolla nuestra aplicación. Por ejemplo, podríamos crear una serie de tablas o una base de datos personalizada para un usuario concreto que se registra en nuestra página web. Por lo que una vez que este usuario verifica su registro, se crean automáticamente; y esto sí que se corresponde con la definición de una base de datos dinámica.
La dinamicidad y automatización en bases de datos se consigue haciendo uso de rutinas y triggers en sus tablas.
Además estas tablas se pueden relacionar con otras, pero tiene algunas dificultades asociadas, en cuanto a su implementación desde algunas interfaces. En mi caso suelo utilizar MySQL Workbench para realizar mis bases de datos, pero cuando la cosa se complica a la hora de crear triggers o rutinas, no es mi recomendación utilizar este modelo sin conocer bien el lenguaje sql.
Para realizar un ejemplo vamos a crear una base de datos dinámica a la que llamaremos dynamic_db y dentro de la misma crearemos una tabla de registro de usuarios.
DROP SCHEMA IF EXISTS `dynamic_db` ; -- ----------------------------------------------------- -- Schema jsk_dynamic_db -- ----------------------------------------------------- CREATE SCHEMA IF NOT EXISTS `dynamic_db` ; CREATE TABLE IF NOT EXISTS `dynamic_db`.`users` ( `nid_user` INT NOT NULL AUTO_INCREMENT COMMENT '', `name` VARCHAR(45) NOT NULL COMMENT '', `surname` VARCHAR(45) NULL COMMENT '', `password` VARCHAR(45) NULL COMMENT '', PRIMARY KEY (`nid_user`, `name`) COMMENT '') ENGINE = InnoDB;
Supongamos que en esta tabla definimos los usuarios que se registran en nuestra página, pero un paso más allá,será la definición de una tabla que relacione estos usuarios como si fueran amigos en una lista. Esto se consigue haciendo una relación n:m consigo misma. De esta manera podremos relacionar usuarios entre sí.
Además de esto vamos a crear un disparador, es decir, un trigger que ejecutará la creación de un registro en la tabla que relacione a un usuario consigo mismo. De esta manera cuando nos registremos podremos ser nuestro propio amigo, hay que quererse a uno mismo, para que los demás te valoren por lo que eres. 🙂
DROP TABLE IF EXISTS `dynamic_db`.`uf` ; CREATE TABLE IF NOT EXISTS `dynamic_db`.`uf` ( `id_uf` INT NOT NULL AUTO_INCREMENT COMMENT '', `table_msg` VARCHAR(100) NULL COMMENT '', `accepted` TINYINT(1) NULL DEFAULT 0 COMMENT '', `createtime` DATETIME NULL COMMENT '', `user_nid` INT NOT NULL COMMENT '', `user` VARCHAR(45) NOT NULL COMMENT '', `userfriend_nid` INT NOT NULL COMMENT '', `userfriend` VARCHAR(45) NOT NULL COMMENT '', PRIMARY KEY (`id_uf`) COMMENT '', INDEX `fk_uf_users_idx` (`user_nid` ASC, `user` ASC) COMMENT '', INDEX `fk_uf_users1_idx` (`userfriend_nid` ASC, `userfriend` ASC) COMMENT '', CONSTRAINT `fk_uf_users` FOREIGN KEY (`user_nid` , `user`) REFERENCES `dynamic_db`.`users` (`nid_user` , `name`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fk_uf_users1` FOREIGN KEY (`userfriend_nid` , `userfriend`) REFERENCES `dynamic_db`.`users` (`nid_user` , `name`) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE = InnoDB; DELIMITER $$ USE `dynamic_db`$$ DROP TRIGGER IF EXISTS `dynamic_db`.`users_AFTER_INSERT` $$ USE `dynamic_db`$$ CREATE DEFINER = CURRENT_USER TRIGGER `dynamic_db`.`users_AFTER_INSERT` AFTER INSERT ON `users` FOR EACH ROW BEGIN INSERT INTO dynamic_db.uf (user_nid, userfriend_nid,user, userfriend, createtime, table_msg) VALUES (NEW.nid_user,NEW.nid_user,NEW.name,NEW.name,NOW(),CONCAT(NEW.name,'_',NEW.name)); END $$ DELIMITER ;
Ahora podemos hacer un insert muy sencillo y podremos observar que este disparador funciona correctamente.
INSERT INTO `dynamic_db`.`users`(`nid_user`, `name`, `surname`, `password`) VALUES (1, 'blascarr', 'blas', 'password');
Dentro de esta tabla, podemos ver que existe un campo que se llama ‘table_msg’. Pues en este campo vamos a guardar un nombre identificativo de una tabla donde guardaremos los mensajes que se intercambian amigos entre ellos. Supondremos que los nombres de usuario son únicos y este nombre será también único con el siguiente formato; “user_userfriend”; donde “user” es el nombre del usuario que envia el mensaje y “userfriend”, el destinatario.
Ahora solo necesitaremos una rutina en la que introduciremos este nombre y la lectura de estos mensajes se encontrarán en esta tabla con el formato del nombre indicado anteriormente.
Aquí viene lo complicado… 🙁
A la hora de crear rutinas, hay que hacer una serie de cosas con anterioridad. Lo primero es cambiar el delimitador, que por defecto es el punto y coma.
Como las sentencias SQL se enumeran una tras otra con éste símbolo “;”, habremos de cambiarlo, usualmente se utiliza el doble símbolo del dolar “$$”, pero podríamos utilizar el que quisieramos.
Para crear una tabla dinámica utilizaremos una concatenación de la sentencia como si se tratara de una cadena; y luego la ejecutamos con la función SQL PREPARE y EXECUTE.
DELIMITER $$
-- ESTE PROCEDIMIENTO FUNCIONA PSEUDOCORRECTAMENTE PARA LA CREACION DINAMICA DE TABLAS SIN ID AUTOINCREMENTAL
CREATE PROCEDURE `dynamic_db`.`msg_table` (IN user_table CHAR(50))
BEGIN
SET @s = CONCAT('DROP TABLE IF EXISTS `dynamic_db`.',user_table);
PREPARE stm FROM @s;
EXECUTE stm;
SET @s = CONCAT('CREATE TABLE IF NOT EXISTS `dynamic_db`.',user_table,' (
`title` VARCHAR(45) NULL COMMENT '',
`text` VARCHAR(45) NULL COMMENT '');');
PREPARE stm FROM @s;
EXECUTE stm;
END ;
$$
DELIMITER ;
Una vez hecha esta función la ejecutaremos con la intrucción CALL e introduciendo el nombre que le queramos dar a la tabla. Por ejemplo “blascarr_blascarr”, que indicará que esta tabla guardará los mensajes que me envio yo a mi mismo.
CALL msg_table ('blascarr_blascarr');
Esta bien, vamos a ver el resultado de la ejecución de esta rutina.
Eeeeyyy espera un momento… por qué me aparece un campo cuando he definido claramente dos de ellos¿?¿? El del título está bien, pero y el texto¿?¿?
Bien, pues la respuesta es la siguiente; dentro de la rutina, estamos tratando todo el conjunto como una cadena que está delimitada por comillas. Si nos fijamos en la definición de la tabla, podemos observar que los campos tienen un “comment ” “; estas dos comillas destrozan la integración de toda la cadena; por lo que hay que escaparlas con el siguiente carácter “\”.
DELIMITER $$
CREATE PROCEDURE `dynamic_db`.`msg_table_ok` (IN user_table CHAR(50))
BEGIN
SET @s = CONCAT('DROP TABLE IF EXISTS `dynamic_db`.',user_table);
PREPARE stm FROM @s;
EXECUTE stm;
SET @s = CONCAT('CREATE TABLE IF NOT EXISTS `dynamic_db`.',user_table,' (
`title` VARCHAR(45) NULL COMMENT \'\',
`text` VARCHAR(45) NULL COMMENT \'\')
ENGINE=InnoDB;');
PREPARE stm FROM @s;
EXECUTE stm;
END ;
$$
DELIMITER ;
E introducimos una nueva tabla aquí dentro.
CALL msg_table_ok('blascarr_blascarr_ok');
Vaaaaaale, ahora sí que tenemos todos los campos bien definidos como debe ser.
La situación ahora es la siguiente… si podemos definir completamente esta ejecución, podemos seguir creando relaciones a raiz de esta con claves foráneas y todo lo que nos apetezca. De esta creamos una dinámica escalable para nuestra aplicación.
DELIMITER $$
CREATE PROCEDURE `dynamic_db`.`msg_table_pro` (IN user_table CHAR(50))
BEGIN
SET @s = CONCAT('DROP TABLE IF EXISTS `dynamic_db`.',user_table);
PREPARE stm FROM @s;
EXECUTE stm;
SET @s = CONCAT('CREATE TABLE IF NOT EXISTS `dynamic_db`.',user_table,' (
`idmessages` INT NOT NULL AUTO_INCREMENT COMMENT \'\',
`title` VARCHAR(45) NULL COMMENT \'\',
`text` VARCHAR(45) NULL COMMENT \'\',
`uf_id_uf` INT NOT NULL COMMENT \'\',
PRIMARY KEY (`idmessages`) COMMENT \'\',
INDEX `fk_messages_uf1_idx` (`uf_id_uf` ASC) COMMENT \'\',
CONSTRAINT `fk_messages_uf1`
FOREIGN KEY (`uf_id_uf`)
REFERENCES `dynamic_db`.`uf` (`id_uf`)
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE=InnoDB DEFAULT CHARSET=utf8;');
PREPARE stm FROM @s;
EXECUTE stm;
END ;
$$
Ejecutamos la rutina y observamos que se adapta perfectamente a nuestra estructura.
CALL msg_table_pro('blascarr_blascarr_pro');
Bueno, pero no ha acabado todo aquí. El problema que nos encontraremos ahora será el siguiente… Si creamos una segunda tabla nos aparecerá este mensaje.
Esto se debe a que a la hora de crear la clave foránea, hemos derivado de un nombre estático; que en este caso es ‘fk_messages_uf1_idx’ y ‘fk_messages_uf1’. Esto proviene de la creación de una tabla genérica y a la hora de ejecutar la rutina, se crea esa misma clave para dos tablas distintas, de manera que queda duplicada.
Así que tendremos que hacer este nombre también dinámico; y para ello utilizaremos el mismo nombre de la tabla; porque al fin y al cabo el nombre nos da igual; solamente queremos que no se repita.
DELIMITER $$
CREATE PROCEDURE `dynamic_db`.`msg_table_pro` (IN user_table CHAR(50))
BEGIN
SET @s = CONCAT('DROP TABLE IF EXISTS `dynamic_db`.',user_table);
PREPARE stm FROM @s;
EXECUTE stm;
SET @s = CONCAT('CREATE TABLE IF NOT EXISTS `dynamic_db`.',user_table,' (
`idmessages` INT NOT NULL AUTO_INCREMENT COMMENT \'\',
`title` VARCHAR(45) NULL COMMENT \'\',
`text` VARCHAR(45) NULL COMMENT \'\',
`uf_id_uf` INT NOT NULL COMMENT \'\',
PRIMARY KEY (`idmessages`) COMMENT \'\',
INDEX `fk_',user_table,'_uf1_idx` (`uf_id_uf` ASC) COMMENT \'\',
CONSTRAINT `fk_',user_table,'_uf1`
FOREIGN KEY (`uf_id_uf`)
REFERENCES `dynamic_db`.`uf` (`id_uf`)
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE=InnoDB DEFAULT CHARSET=utf8;');
PREPARE stm FROM @s;
EXECUTE stm;
END ;
$$
Bien, objetivo cumplido. Solo nos faltaría automatizar la creación de una tabla con esta rutina, pero cuidado, no se puede llamar a una rutina dentro de un trigger. Esta ejecución la podemos hacer desde PHP haciendo una consulta como ya sabremos hacer, pero no se puede introducir dentro de un disparador. Si no nos aparecerá el siguiente mensaje:
ERROR 1336 (0A000): Dynamic SQL is not allowed in stored function or trigger
