Foros del Web - Generar Localizador Reserva aleatorio con PROCEDURE Y TRIGGER

Foros del Web (http://www.forosdelweb.com/)

- Mysql (http://www.forosdelweb.com/f86/)

- - Generar Localizador Reserva aleatorio con PROCEDURE Y TRIGGER (http://www.forosdelweb.com/f86/generar-localizador-reserva-aleatorio-con-procedure-trigger-474036/)

Generar Localizador Reserva aleatorio con PROCEDURE Y TRIGGER

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.

Re: Generar Localizador Reserva con PROCEDURE

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

Re: Generar Localizador Reserva con PROCEDURE

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 :borracho:

Jorge F.M. ( Calico )

Re: Generar Localizador Reserva con PROCEDURE

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

Re: Generar Localizador Reserva con PROCEDURE

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)

Respuesta: Generar Localizador Reserva aleatorio con PROCEDURE Y TRIGGER

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