Generar Localizador Reserva aleatorio con PROCEDURE Y TRIGGER

[digdig]

Hola, después de investigar un poco he descubierto como utilizar PROCEDURE de MySqL para generar un codigo de reserva que tenga 6 caracteres con valores entre A-Z y 0-9 y que tome valores no consecutivos, es decir, aleatorios.
Ejemplos de localizadores de reserva:
R5FCX1
T4WDPD
PNTZ4Z


En primer lugar me creo una tabla con dos columnas (posicion, caracter). Tenemos tantos registros como caracteres A-Z y 0-9, vamos 36 caracteres.
+----------+----------+
| caracter | posicion |
+----------+----------+
| 0 | 0 |
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 5 |
| 6 | 6 |
| 7 | 7 |
| 8 | 8 |
| 9 | 9 |
| A | 10 |
| B | 11 |
| C | 12 |
| D | 13 |
| E | 14 |
| F | 15 |
| G | 16 |
| H | 17 |
| I | 18 |
| J | 19 |
| K | 20 |
| L | 21 |
| M | 22 |
| N | 23 |
| O | 24 |
| P | 25 |
| Q | 26 |
| R | 27 |
| S | 28 |
| T | 29 |
| U | 30 |
| V | 31 |
| W | 32 |
| X | 33 |
| Y | 34 |
| Z | 35 |
+----------+----------+
36 rows in set (0.00 sec)


El código para generar el procedimiento es el siguiente:

Código PHP:

  DELIMITER $$
 
DROP PROCEDURE IF EXISTS `test`.`generaLocalizador`$$
CREATE PROCEDURE `test`.`generaLocalizador` (
OUT localizador VARCHAR(6)
)
BEGIN
 
DECLARE  A1 VARCHAR(1);
DECLARE  A2 VARCHAR(1);
DECLARE  A3 VARCHAR(1);
DECLARE  A4 VARCHAR(1);
DECLARE  A5 VARCHAR(1);
DECLARE  A6 VARCHAR(1);
 
DECLARE  C1 VARCHAR(1);
DECLARE  C2 VARCHAR(1);
DECLARE  C3 VARCHAR(1);
DECLARE  C4 VARCHAR(1);
DECLARE  C5 VARCHAR(1);
DECLARE  C6 VARCHAR(1);
 
SELECT MOD(FLOOR(RAND()*1000), 35)
INTO A1;
SELECT caracter  as CaracterAleatorio
FROM caracteres
WHERE posicion= A1
INTO C1;
 
SELECT MOD(FLOOR(RAND()*1000), 35)
INTO A2;
SELECT caracter  as CaracterAleatorio
FROM caracteres
WHERE posicion= A2
INTO C2;
 
SELECT MOD(FLOOR(RAND()*1000), 35)
INTO A3;
SELECT caracter  as CaracterAleatorio
FROM caracteres
WHERE posicion= A3
INTO C3;
 
SELECT MOD(FLOOR(RAND()*1000), 35)
INTO A4;
SELECT caracter  as CaracterAleatorio
FROM caracteres
WHERE posicion= A4
INTO C4;
 
SELECT MOD(FLOOR(RAND()*1000), 35)
INTO A5;
SELECT caracter  as CaracterAleatorio
FROM caracteres
WHERE posicion= A5
INTO C5;
 
SELECT MOD(FLOOR(RAND()*1000), 35)
INTO A6;
SELECT caracter  as CaracterAleatorio
FROM caracteres
WHERE posicion= A6
INTO C6;
 
SELECT CONCAT(C1, C2, C3, C4, C5, C6) 
INTO localizador;
 
END$$
 
DELIMITER ; 
   

ANALISIS
MOD(FLOOR(RAND()*1000), 35)
Genera valor aleatorio, multiplica por 1000 y saca el módulo 35. El modulo 35 da valores entre 0 y 35 (Es el resto de dividir un numero entre 35).
Ese valor lo usamos para acceder a la fila de la tabla caracteres y recuperar el valor.

Para ejecutar el procedimiento usamos estas sentencias:

Código PHP:

  CALL generaLocalizador(@localizador);
 
select @localizador; 
   

Y nos mostraría la siguiente salida:

+----------------+
| @localizador |
+----------------+
| Q3706Q |
+----------------+

ó

+----------------+
| @localizador |
+----------------+
| ZR2ADS |
+----------------+

etc


Me falta crear el DISPARADOR que llame a mi procedimiento despues de insetar un nuevo registro en la tabla 'reserva' y meta el localizador devuelto por el procedimiento en la clave principal del nuevo registro.
Además tengo que ver como evito que se intente dar un valor de localizador ya existente o como lo detecto para probar con otro valor.

Es la primera vez que trabajo con procedimientos asi que las sugerencias para mejorar mi código o como codificar el TRIGGER son bienvenidas.


Jorge F.M.

[abulon]

En tu trigger pones solo el nombre del procedure , y debes guardar la variable ,pero debes declararla dentro del trigger

CREATE TRIGGER OK
AFTER INSERT ON RESERVA
FOR EACH ROW

BEGIN

DECLARE variable tipo_de_dato;


'aki llmas tu procedure y lo ke te devuelva ke lo guarde en la variable

END;

Y en cuanto a ke se repite deberia de ser unique el campo o primary key..

Saludos

[digdig]

Gracia Abulon por responder, sólo me falta que al repetirse un localizador pruebe automaticamente con otro localizador aleatorio hasta que no coincida. No deberia dejarse de introducir una reserva sólo porque el localizador estaba repetido.

Como el localizador es de 6 caracteres hay millones de valores posibles por lo que ya sería mucha casualiadad que coincida más de 3 veces con otro localizador. Creo que la sentencia insert tiene una opción para decidir que hacer si la clave ya existe, algo asi como:

Código PHP:

  INSERT INTO [...]
SI SE REPITE clave= claverepetida+1        //Prueba con el valor siguiente. 
   

¿Alguien sabe la sintaxis exacta?

Otra dudilla, ¿procedure soporta arrays como variable? lo digo porque prefiero almacenar los caracteres posibles en un array en vez de, tal como está ahora, en una tabla.
¿Como me crearía una variable array y como accedería a los valores?

Pseudocodigo::

Código PHP:

  caracteres varchar [36] = {'A','B','C','D', ... , '8', '9', '0' }; 
C1 = caracteres [A1];
[...]
SELECT CONCAT(C1, C2, C3, C4, C5, C6) INTO localizador 
   

Gracias y un saludo a todos, me está gustando esto del foro

Jorge F.M. ( Calico )

[abulon]

Los procedures no manejan array, pero manejan excepcions o handling excepcions, si arroja el error tal de duplicate key entoncs generar clave aleatoria de nuevo...un saludo

[digdig]

Bueno para que no quede la cosa sin terminar os cuelgo el codigo, ya probado, para generar el TRIGGER:

Código PHP:

  DELIMITER $$
 
CREATE TRIGGER nuevareserva
BEFORE INSERT ON reserva
FOR EACH ROW
 
BEGIN
 
DECLARE locAux VARCHAR(6);
 
CALL generaLocalizador(@loc);
SELECT @loc INTO locAux;
SET NEW.localizador= locAux;
 
END$$
 
DELIMITER ; 
   

Si necesitamos borrar el trigger usariamos la sentencia:

Código PHP:

  DROP trigger nuevareserva; 
   

Finalmente para que veais que no miento cuando digo que funciona os pongo las salidas al insertar dos nuevas reservas (comprador y crucero son los identificadores de los respectivos registros en otras tablas):

Código PHP:

  mysql> insert into reserva (comprador, crucero) values (3432, 123);
Query OK, 1 row affected, 1 warning (0.01 sec)
 
mysql> select * from reserva;
+-------------+-----------+---------+
| localizador | comprador | crucero |
+-------------+-----------+---------+
| HNW32L      |      3432 |     123 | 
+-------------+-----------+---------+
1 row in set (0.00 sec)
 
mysql> insert into reserva (comprador, crucero) values (3563, 165);
Query OK, 1 row affected, 1 warning (0.00 sec)
 
mysql> select * from reserva;
+-------------+-----------+---------+
| localizador | comprador | crucero |
+-------------+-----------+---------+
| HNW32L      |      3432 |     123 | 
| SP4A8S      |      3563 |     165 | 
+-------------+-----------+---------+
2 rows in set (0.00 sec) 
   

RESUMEN:
Necesitabamos que mysql generara automáticamente localizadores aleatorios formados por caracteres y dígitos. La tendencia de los programadores es delegar esta función en la aplicación cliente, sin embargo, el correcto uso de procedure y trigger permite al usuario de la base datos abstraerse de esta tarea con las ventajas que eso supone.
La solución que hemos optado es crear un procedimiento que concatena caracteres recuperados aleatoriamente de una tabla auxiliar rellena de los 36 caracteres del localizador. Con el disparador before insert lo que hacemos es llamar al procedimiento para obtener un localizador válido y usarlo para almacenarlo en el campo localizador de cada fila insertada.

Un saludo a todos

Jorge F.M. (Calico UAX)

[dbm_85]

Hola, yo estoy haciendo un proyecto de reservas de autos y necesito algo asi tambien para la reserva, me podrias ayudar como lograste que no se repitiera el codigo de reserva? si tienes el codigo aun podrias ponerlo soy nueva en esto y nunca he hecho un procedimiento almacenado. Gracias