Debería usar un Stored Procedure?

[neodani]

Buenas,

Me gustaría saber vuestra opinión al respecto. Resulta que en cada registro nuevo de usuario en mi aplicación web, debo realizar 3 inserts a 3 tablas diferentes.

¿Me convendría usar un Stored Procedure? Nunca llegué a usar uno y no sé si en este caso sería buena idea o no.

Muchas gracias de antemano

[gnzsoloyo]

Iniciado por neodani

Buenas,

Me gustaría saber vuestra opinión al respecto. Resulta que en cada registro nuevo de usuario en mi aplicación web, debo realizar 3 inserts a 3 tablas diferentes.

¿Me convendría usar un Stored Procedure? Nunca llegué a usar uno y no sé si en este caso sería buena idea o no.

Muchas gracias de antemano

La respuesta básica es si, te convendría si los parámetros de los inserts no son compartidos.
Pero si las otras dos tablas toman datos que ya están en la primera o son producidos por ella (por ejemplo, el ID si es numérico) es posible que con un TRIGGER sea suficiente.

¿Cuál es el caso? Danos un poquito de detalles respeto de los parámetros y cómo se reparten.

[neodani]

Iniciado por gnzsoloyo

La respuesta básica es si, te convendría si los parámetros de los inserts no son compartidos.
Pero si las otras dos tablas toman datos que ya están en la primera o son producidos por ella (por ejemplo, el ID si es numérico) es posible que con un TRIGGER sea suficiente.

¿Cuál es el caso? Danos un poquito de detalles respeto de los parámetros y cómo se reparten.

Buenas gnzsoloyo,

La función que inserta en las tres tablas es la siguiente:

Código PHP:

public function addNewUser($nombre,$usuario,$password,$correo,$pais,$birthday,$sexo,$userReferido){
        
        $this->_nombre=$nombre; 
        $this->_usuario=$usuario;
        $this->_password=$password;
        $this->_correo=$correo;
        $this->_pais=$pais;
        $this->_birthday=$birthday;
        $this->_sexo=$sexo;
        $this->_userReferido=$userReferido;
        $this->_userKey = $this->randomKeygen($length=10);
        $ip=getRealIP();
        $this->_userId=1; /*hardcore */
        
        // Inserta datos en la TBL_USERS
        $query = "INSERT INTO tbl_user (user_login,user_pass,user_activation_key) VALUES (:user_login,:user_pass,:user_activation_key)";
 
        $comando = $this->_con->prepare($query);
        $comando->execute(array(':user_login'=>$this->_usuario,
                                ':user_pass'=>$this->_password,
                                ':user_activation_key'=>$this->_userKey));
        
        // Inserta datos en la TBL_USERS_PERSONAL
        $query = "INSERT INTO tbl_user_personal (user_id,user_name,user_mail,user_birthday,user_sexo,user_pais) VALUES (:user_id,:user_name,:user_mail,:user_birthday,:user_sexo,:user_pais)";
        $comando = $this->_con->prepare($query);
        $comando->execute(array(':user_id'=>$this->_userId,
                                ':user_name'=>$this->_nombre,
                                ':user_mail'=>$this->_correo,
                                ':user_birthday'=>$this->_birthday,
                                ':user_sexo'=>$this->_sexo,
                                ':user_pais'=>$this->_pais));
        
        // Inserta datos en la TBL_REGISTRO
        $query = "INSERT INTO tbl_registro (user_id,fecha_registro,ip_registro) VALUES (:user_id,now(),:ip)";
        $comando = $this->_con->prepare($query);
        
        $comando->execute(array(':user_id'=>$this->_userId,
                                ':ip'=>$ip));
        
    }
 

Primero inserto en la TBL_USERS la cual tiene un campo user_id es autoincremental y PK.
El problema está en las dos siguientes inserciones que dependen del valor del user_id insertado en la TBL_USERS para luego en las otras dos tablas poder ingresarlo (de ahí la relación de FK).
En el ejemplo lo he puesto "hardcore" este valor (linea 13).

Te pego el diseño de las tablas para que lo veas mas claro.
Aprovecho para preguntarte si las dos claves foráneas de las tablas USER_PERSONAL y REGISTRO, deberían ser también primarias?...



Muchas gracias de antemano!

[gnzsoloyo]

Bueno, en ese contexto, lo que más te conviene es usar un stored procedure, ya que los datos a ingresar en las tres tablas son diferentes. No hay modo de administrar eso por un TRIGGER, ya que un TRIGGER sólo puede recibir como parámetros los datos de su propia tabla.
El único inconveniente para la codificación de un SP así es la cantidad de datos a ingresar, pero eso no es un limtante.

[neodani]

Iniciado por gnzsoloyo

Bueno, en ese contexto, lo que más te conviene es usar un stored procedure, ya que los datos a ingresar en las tres tablas son diferentes. No hay modo de administrar eso por un TRIGGER, ya que un TRIGGER sólo puede recibir como parámetros los datos de su propia tabla.
El único inconveniente para la codificación de un SP así es la cantidad de datos a ingresar, pero eso no es un limtante.

Gracias gnzsoloyo,

El SP, sería así?

Código SQL:

CREATE PROCEDURE nuevoUsuario (
@user_id INT,
@user_login VARCHAR(25),
@user_pass VARCHAR(30),
@user_activation_key VARCHAR(40),
@user_name VARCHAR(30),
@user_mail VARCHAR(45),
@user_birthday DATE,
@user_sexo CHAR(1),
@user_pais SMALLINT,
@fecha_registro TIMESTAMP,
@ip_registro VARCHAR(15)
)
 
BEGIN
INSERT INTO tbl_user (user_login,user_pass,user_activation_key) VALUES (@user_login,@user_pass,@user_activation_key);
 
INSERT INTO tbl_user_personal (user_id,user_name,user_mail,user_birthday,user_sexo,user_pais) VALUES (@user_id,@user_name,@user_mail,@user_birthday,@user_sexo,@user_pais);
 
INSERT INTO tbl_registro (user_id,fecha_registro,ip_registro) VALUES (@user_id,now(),@ip);
END
 

El problema que tengo es como sé el user_id de los dos últimos inserts. Ya que al hacer el primer insert, se crea una nueva fila en la tbl_user, la cual tiene una PK que es el user_id. Pues como diablos hago para que los dos inserts siguientes usen el user_id que se acaba de crear en la tbl_user?


¿Alguien sabe y puede ayudarme?

Muchas gracias de antemano

[Nano_]

Saludos

Se me ocurre algo asi:

Código MySQL:

BEGIN
 
INSERT INTO tbl_user (user_login,user_pass,user_activation_key) VALUES (@user_login,@user_pass,@user_activation_key);
 
 
 
INSERT INTO tbl_user_personal (user_id,user_name,user_mail,user_birthday,user_sexo,user_pais) VALUES ((SELECT LAST_INSERT_ID()),@user_name,@user_mail,@user_birthday,@user_sexo,@user_pais);
 
 
 
INSERT INTO tbl_registro (user_id,fecha_registro,ip_registro) VALUES ((SELECT LAST_INSERT_ID()),now(),@ip);
 
END
 

Prueba y cualquier novedad lo comentas.

Hasta Pronto

[gnzsoloyo]

Vamos a algunos detalles:

1) Cuando haces una inserción de este tipo, en el que se ven afectadas más de una tabla, es conveniente usar transacciones, de modo que si algo falla, se deshagan todas las inserciones (cuestiones de consistencia).

2) Las variables de entrada no se usan con "@". Eso es de SQL Server (Microsoft) y no de MySQL. Las variables se usan simplemente declarandolas en el prototipo y usandolas con su nombre.

3) Las variables con "@" son variables de usuario y siguen existiendo y con su valor mientras dure la conexión. No es conveniente usarlas si no es estrictamente necesario precisamente por su permanencia, y cada vez que se las invoca deben ser inicializadas para evitar que guarden basura. Son muy útiles en cierto tipo de operaciones, pero no es buena prática usarlas en el prototipo de los SP, entre otras cosas porque si por alguna razón dos SP distintos que usan la misma variable son invocados por el mismo usuario en la misma sesión (threads de ejecución diferentes), el cambio de valor de la variable en un thread afectará el valor de la variable en el otro thread, simplemente porque apuntan a lo mismo...

4) Las variables no deben bajo ninguna circunstancia tener el mismo nombre de campos, tablas, bases o cualquier otro objeto de base de datos, porque el parser confundirá los objetos, ya que jerarquiza los mismos y en ese caso una tabla o un campo tienen mayor jerarquía que una variable, en consecuencia intentará acceder al campo o tabla y no a la variable, lo que puede dar lugar a errores aleatorios.

Este sería un modelo, basado en tu ejemplo:

Código MySQL:

CREATE PROCEDURE nuevoUsuario (
  IN vuser_id int,
  IN vuser_login varchar(25),
  IN vuser_pass varchar(30),
  IN vuser_activation_key varchar(40),
  IN vuser_name varchar(30),
  IN vuser_mail varchar(45),
  IN vuser_birthday date,
  IN vuser_sexo char(1),
  IN vuser_pais smallint,
  IN vfecha_registro timestamp,
  IN vip_registro varchar(15))
 
BEGIN
 
START TRANSACTION;
 
  INSERT INTO tbl_user(
    user_login,
    user_pass,
    user_activation_key)
  VALUES(
    vuser_login,
    vuser_pass,
    vuser_activation_key);
 
  INSERT INTO tbl_user_personal(
    vuser_id,
    vuser_name,
    vuser_mail,
    vuser_birthday,
    vuser_sexo,
    vuser_pais)
  VALUES (
    vuser_id,
    vuser_name,
    vuser_mail,
    vuser_birthday,
    vuser_sexo,
    vuser_pais);
 
  INSERT INTO tbl_registro (
    vuser_id,
    vfecha_registro,
    vip_registro)
  VALUES (
    vuser_id,
    NOW(),
    vip_registro);
 
COMMIT;
 
END
 

Es de buenas prácticas Poner el sentido de entrada/salida de las variables, por más que el IN sea implícito. Facilita cualquier análisis rápido y además de ese modo no te olvidas de poner los OUT o INOUT cuando corresponden.

[neodani]

Iniciado por gnzsoloyo

Vamos a algunos detalles:

1) Cuando haces una inserción de este tipo, en el que se ven afectadas más de una tabla, es conveniente usar transacciones, de modo que si algo falla, se deshagan todas las inserciones (cuestiones de consistencia).

Muchas gracias por la explicación gnzsoloyo, fenomenal

Respecto al punto 1 que he citado.

1) Las transacciones solo se pueden hacer en SP, no? en triger o cualquier otra función no existen verdad?

2) Si hay algún insert que falla, (ej. el número dos) automáticamente se hace rollback de todo, o tengo que indicárselo en algún lado?

3) Existe alguna forma de confirmar que el procedimiento ha ido bien? generar algún tipo de respuesta TRUE o FALSE para que yo desde PHP pueda saber si se ejecutó correctamente?

Muchas gracias de antemano

[gnzsoloyo]

1) Las transacciones solo se pueden hacer en SP, no? en triger o cualquier otra función no existen verdad?

En MySQL, al menos, un TRIGGER no puede llamar a una transaccion.
Sí puedes iniciar una transacción por fuera del SP (a nivel programático) y cerrar el commint una vez que el resultado ha sido el esperado.
Lo que viene bien para esto último es hacer un control de flujo de ejecuciones por medio del ROW_COUNT(), por ejemplo, que devuelve la cantidad de registros afectados por la sentencia.

2) Si hay algún insert que falla, (ej. el número dos) automáticamente se hace rollback de todo, o tengo que indicárselo en algún lado?

Si cierras las conexión sin COMMIT, el ROLLBACK es implícito. normalmente lo mismo ocurre si el SP se corta o sale sin el COMMIT correspondiente.

3) Existe alguna forma de confirmar que el procedimiento ha ido bien? generar algún tipo de respuesta TRUE o FALSE para que yo desde PHP pueda saber si se ejecutó correctamente?

Eso es medio obvio: SI una ejecución de sentencia (un SP es invocado como sentencia) se ejecuta bien, el valor del result de esa llamada es siempre TRUE... Pero eso lo peudes ver en el manual de PHP.