Foros del Web » Programación para mayores de 30 ;) » Bases de Datos General » Mysql »

Provocar un update con dos filas de la misma tabla

Estas en el tema de Provocar un update con dos filas de la misma tabla en el foro de Mysql en Foros del Web. Hola, Estoy tratando de diseñar una base de datos en la que los usuarios seleccionan un objeto para compartir con otro usuario. Esto se almacena ...
  #1 (permalink)  
Antiguo 29/09/2011, 16:21
Avatar de J_de_Prado  
Fecha de Ingreso: junio-2011
Ubicación: Alpedrete, Madrid,España
Mensajes: 28
Antigüedad: 8 años, 8 meses
Puntos: 2
Pregunta Provocar un update con dos filas de la misma tabla

Hola,
Estoy tratando de diseñar una base de datos en la que los usuarios seleccionan un objeto para compartir con otro usuario.
Esto se almacena en una tabla llamada enviados. funciona así, en la tabla básicamente se registra un d_user ( U1 quien registra el mensaje) un c_mess (el objeto a compartir) y un m_to ( U2 es con quien se quiere compartir), cuando este usuario U2 registra el mismo objeto para compartir con el usuario U1 como es el caso de las dos primera filas, cuando se da ese caso los campos full debería cambiar y pasar por ejemplo a "1"
Mis dudas son:
¿Cómo detectar cuando se dan la condiciones de reciprocidad, como el caso de las dos primeras líneas? Con un where anidado tal vez?

Al estar en la misma tabla no me funciona el TRIGGER, ni siquiera si este llama a una rutina, cosa que si funciona si la rutita actúa sobre otra tabla, si bien los eventos que produce en esta última no provocan el disparo de los TRIGGER que tenga. ¿no tendré mas remedio que crear otra tabla?

Espero haberme explicado bien en esencia lo que intento es con esta base de datos es algo como:
..........Yo estaría dispuesto a comprar algo de a medias contigo si tu estas dispuesto a lo mismo

Gracias


Código MySQL:
Ver original
  1. CREATE TABLE IF NOT EXISTS `enviados` (
  2.   `id_sent` int(20) NOT NULL AUTO_INCREMENT,
  3.   `id_user` int(5) NOT NULL DEFAULT '0',
  4.   `c_mess` int(5) NOT NULL DEFAULT '0',
  5.   `m_to` int(5) NOT NULL DEFAULT '0',
  6.   `full` int(11) NOT NULL DEFAULT '0',
  7.   PRIMARY KEY (`id_sent`),
  8.   KEY `id_user` (`id_user`),
  9.   KEY `m_to` (`m_to`),
  10.   KEY `c_mess` (`c_mess`)
  11. ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci COMMENT='mensajes enviados' AUTO_INCREMENT=5 ;
  12.  
  13. --
  14. -- Volcado de datos para la tabla `enviados`
  15. --
  16.  
  17. INSERT INTO `enviados` (`id_sent`, `id_user`, `c_mess`, `m_to`, `full`, `date`) VALUES
  18. (1, 1, 2, 3, 0, '2011-09-29 20:47:29'),
  19. (2, 3, 2, 1, 0, '2011-09-29 20:47:29'),
  20. (3, 3, 1, 1, 0, '2011-09-29 18:40:50'),
  21. (4, 1, 2, 3, 0, '2011-09-29 18:40:50');
  #2 (permalink)  
Antiguo 03/10/2011, 01:44
Avatar de J_de_Prado  
Fecha de Ingreso: junio-2011
Ubicación: Alpedrete, Madrid,España
Mensajes: 28
Antigüedad: 8 años, 8 meses
Puntos: 2
Respuesta: Provocar un update con dos filas de la misma tabla

Cita:
¿Cómo detectar cuando se dan la condiciones de reciprocidad, como el caso de las dos primeras líneas? Con un where anidado tal vez?
¿tan tara es la pregunta?
  #3 (permalink)  
Antiguo 03/10/2011, 08:02
Avatar de gnzsoloyo
Moderador criollo
 
Fecha de Ingreso: noviembre-2007
Ubicación: Actualmente en Buenos Aires (el enemigo ancestral)
Mensajes: 23.323
Antigüedad: 12 años, 2 meses
Puntos: 2654
Respuesta: Provocar un update con dos filas de la misma tabla

Rara no, pero tienes que analizarlo de una forma simple.
¿Qué forma toman los datos donde existe reciprocidad?

Si esto está determinado por tres campos:
A comparte con B el objeto C: (1,2,'C')
B comparte con A el objeto C:(2,1,'C')

¿Qué condiciones tiene la igualdad?
compartidor1 = compartido2 ^ compartidor2 = compartido1 ^ objeto1 = objeto2

Campos más, campos menos, esa sería la lógica a cumplir.
En definitiva, es un INNER JOIN de una tabla sobre si misma con tres condiciones de igualdad.

Revisa tu tabla y fíjate como se implementa en tu caso.
__________________
¿A quién le enseñan sus aciertos?, si yo aprendo de mis errores constantemente...
"El problema es la interfase silla-teclado." (Gillermo Luque)
  #4 (permalink)  
Antiguo 06/10/2011, 17:32
Avatar de J_de_Prado  
Fecha de Ingreso: junio-2011
Ubicación: Alpedrete, Madrid,España
Mensajes: 28
Antigüedad: 8 años, 8 meses
Puntos: 2
Respuesta: Provocar un update con dos filas de la misma tabla

Hola de nuevo

Utilizando JOIN no he podido hacer nada no me deja sobre la misma tabla, asi que lo he intentado con un IF en un trigger y me da un error que no comprendo, me dice :
ERROR 1054: Unknown column 'm_to' in 'field list'

¿Por que me dará este error si la columna si existe?

El IF que utilizo lo pueden ver en el código
Código MySQL:
Ver original
  1. TRIGGER `base5`.`enviados`
  2. BEFORE INSERT ON `base5`.`enviados`
  3. IF NEW.id_user = m_to AND NEW.c_mess = c_mess AND NEW.m_to = id_user
  4. INSERT  `a otra tabla...
  5. END IF;
  6. END$$
  #5 (permalink)  
Antiguo 06/10/2011, 18:32
Avatar de gnzsoloyo
Moderador criollo
 
Fecha de Ingreso: noviembre-2007
Ubicación: Actualmente en Buenos Aires (el enemigo ancestral)
Mensajes: 23.323
Antigüedad: 12 años, 2 meses
Puntos: 2654
Respuesta: Provocar un update con dos filas de la misma tabla

¿Esa columna existe en qué tabla?
Si es una columna de la tabla "enviados" debe ser accedida con NEW.m_to. En caso contrario no existe.

¿A qué tabla pertenece esa columna?
__________________
¿A quién le enseñan sus aciertos?, si yo aprendo de mis errores constantemente...
"El problema es la interfase silla-teclado." (Gillermo Luque)
  #6 (permalink)  
Antiguo 07/10/2011, 03:30
Avatar de J_de_Prado  
Fecha de Ingreso: junio-2011
Ubicación: Alpedrete, Madrid,España
Mensajes: 28
Antigüedad: 8 años, 8 meses
Puntos: 2
Respuesta: Provocar un update con dos filas de la misma tabla

Pues si, la la columna existe (existen en la tabla enviados) y si cambio el orden del IF pasa lo mismo, en la primera columna a que intenta comparar un NEW da el error, incluso he creado la tabla de nuevo...La versión de MYSQL es: 5.0.51a.

He probado asi
Código MySQL:
Ver original
  1. IF  NEW.c_mess = `c_mess` AND NEW.m_to = id_user AND NEW.id_user = `m_to`  
  2. ERROR 1054: Unknown column 'c_mess' in 'field list'

y así, es decir sin las comillas entre los nombres de las columnas sobre las que se compara el NEW
Código MySQL:
Ver original
  1. IF  NEW.c_mess = c_mess AND NEW.m_to = id_user AND NEW.id_user = m_to
  2. ERROR 1054: Unknown column 'c_mess' in 'field list'

Mas tarde probare con una versión 5.5 a ver si se comporta lo mismo
  #7 (permalink)  
Antiguo 07/10/2011, 05:23
Avatar de gnzsoloyo
Moderador criollo
 
Fecha de Ingreso: noviembre-2007
Ubicación: Actualmente en Buenos Aires (el enemigo ancestral)
Mensajes: 23.323
Antigüedad: 12 años, 2 meses
Puntos: 2654
Respuesta: Provocar un update con dos filas de la misma tabla

Es que lo que debe usarse no es:
Código MySQL:
Ver original
  1. NEW.c_mess = `c_mess` AND NEW.m_to = id_user AND NEW.id_user = `m_to`
ni tampoco
Código MySQL:
Ver original
  1. NEW.c_mess = c_mess AND NEW.m_to = id_user AND NEW.id_user = m_to
sino
Código MySQL:
Ver original
  1. NEW.c_mess = NEW.`c_mess` AND NEW.m_to = NEW.id_user AND NEW.id_user = NEW.`m_to`
En un TRIGGER los únicos parámetros que existen son o bien los campos del registroo entrante (INSERT y UPDATE) o los del antiguo (UPDATE y DELETE). Pero siempre, en todos los casos su nombre debe estar precedido por NEW u OLD, según el caso.
Cualquier otro nombre debe ser, o bien una variable local declarada, o un campo de una tabla invocada en una sentencia dentro del TRIGGER (que no puede ser la propia tabla).
Cambiar de versión de MySQL no alterará nada, porque el error no es de versiones sino que estás escribiendo mal la invocación a los campos.

Por otro lado un tema de lógica de INSERT: Si estás haciendo una comparación dentro del propio INSERT, ¿por qué no resuelves eso en la programación de la aplicación y le quitas a la base una responsabilidad que no se necesita?
A fin de cuentas esa validación bien la puedes hacer antes de enviar los datos, ya que sólo estás operando con los VALUE que mandas en el mismo INSERT.
__________________
¿A quién le enseñan sus aciertos?, si yo aprendo de mis errores constantemente...
"El problema es la interfase silla-teclado." (Gillermo Luque)
  #8 (permalink)  
Antiguo 08/10/2011, 08:44
Avatar de J_de_Prado  
Fecha de Ingreso: junio-2011
Ubicación: Alpedrete, Madrid,España
Mensajes: 28
Antigüedad: 8 años, 8 meses
Puntos: 2
Respuesta: Provocar un update con dos filas de la misma tabla

Cita:
Iniciado por gnzsoloyo Ver Mensaje
Por otro lado un tema de lógica de INSERT: Si estás haciendo una comparación dentro del propio INSERT, ¿por qué no resuelves eso en la programación de la aplicación y le quitas a la base una responsabilidad que no se necesita?
A fin de cuentas esa validación bien la puedes hacer antes de enviar los datos, ya que sólo estás operando con los VALUE que mandas en el mismo INSERT.
Mi intención es que “esto” que estoy intentando desarrollar funcione en un servidor de Internet y la plataforma de acceso sea a través de PHP, (además de para PC también para teléf. móvil o celular si lo prefiere) y por lo que he leído sobre PHP y MYSQL creo que por temas de de seguridad todo lo que haga la BD por si misma puede que evite tener que dar determinados permisos a los usuarios normales de la Web. En [URL="http://dev.mysql.com/doc/refman/5.1/en/stored-programs-security.html"]Access Control for Stored,[/URL] se dicen cosas a las que se le puede sacar provecho en temas de seguridad, o al menos eso creo...

Cita:
si no
Código MySQL:
Ver original
  1. NEW.c_mess = NEW.`c_mess` AND NEW.m_to = NEW.id_user AND NEW.id_user = NEW.`m_to`
En un TRIGGER los únicos parámetros que existen son o bien los campos del registroo entrante (INSERT y UPDATE) o los del antiguo (UPDATE y DELETE). Pero siempre, en todos los casos su nombre debe estar precedido por NEW u OLD, según el caso.
Con este código a mi me parece que lo que hace es comparar sobre los mismos datos que se insertan, así que he pensado lo que escribo mas abajo.

Cita:
Cualquier otro nombre debe ser, o bien una variable local declarada, o un campo de una tabla invocada en una sentencia dentro del TRIGGER (que no puede ser la propia tabla).
Cambiar de versión de MySQL no alterará nada, porque el error no es de versiones sino que estás escribiendo mal la invocación a los campos.
Viendo la imposibilidad de comparar en un trigget los campos que se van a insertar, con todas las filas de la propia tabla, ahora estoy probando a comparar datos con otra tabla que sea idéntica a la portadora del trigger, creo que seria una forma de 'burlar' las limitaciones que hay para utilizar los datos de la tabla que porta el trigger, luego esta se va vaciando
Insisto en lo del triger por lo que se dice en el tema seguridad pues al parecer se ejecuta con los permisos del creador.
Después de crear otra tabla (envios) lo que actualmente me esta dando problemas la manera de referirme al campo de una tabla diferente. lo estoy intentando con comillas y si ellas , pero me dice que mas o menos :ERROR 1109: Unknown table 'envios' in field list
¿Hay que utilizar alias? ¿Si es asi se pone en dentro del trigger? he buscado y no encuentro un ejemplo de cita a otra tabla dentro de un IF.


Código MySQL:
Ver original
  1. TRIGGER `base5`.`enviados`
  2. BEFORE INSERT ON `base5`.`enviados`
  3.  #no confundir enviados con envios
  4. IF  NEW.c_mess = envios.c_mess  AND NEW.m_to = envios.id_user AND NEW.id_user = envios.m_to
  5. # este update lo tengo por que es fácil de comprobar si funciona lo anterior
  6. UPDATE `base5`.`usuarios` SET `acces`= CURRENT_TIMESTAMP WHERE`id_user`='1';
  7. END$$
  #9 (permalink)  
Antiguo 08/10/2011, 14:34
Avatar de gnzsoloyo
Moderador criollo
 
Fecha de Ingreso: noviembre-2007
Ubicación: Actualmente en Buenos Aires (el enemigo ancestral)
Mensajes: 23.323
Antigüedad: 12 años, 2 meses
Puntos: 2654
Respuesta: Provocar un update con dos filas de la misma tabla

Vamos a aclarar algunos detalles:
- PHP es un lenguaje del lado del servidor, las operaciones no se realizan en el cliente (como el JavaScript), de modo que realizar ciertas operaciones dentro de un script PHP es bastante más seguro de lo que piensas. Ten en cuenta que para llegar a tus scripts (y a tu ejecución) primero tienen que hackearte el host y la base... con lo que el que capturen el proceso es el menor de tus problemas. A esa altura es probable que ya no te quede ni website, ni base...
- Los SP son mucho más seguros que usar consultas directas, fundamentalmente porque son invulnerables al sql-injection, ya que como sólo pueden recibir parámetros, y los parámetros operan dentro del SP, no pueden incrustar una sentencia ilegal dentro de ellos.
- Por otro lado, los SP permiten hacer un conjunto de operaciones mucho más extensas que simples consultas, lo que los vuelve ideales para procesos más o menos complejos.
- El uso de diferentes usuarios que certifiquen contra el servidor de MySQL es una forma ideal de seguridad por muchas razones, como: 1) El sistema de encriptación de las claves en MySQL no es desencriptable; 2) Permite no recargar conexiones a usuarios y restringir la cantidad de conexiones que un usuario puede hacer; 3) Permite definir una buena granularidad de permisos para los roles de los usuarios, lo que se puede llevar al extremo de que ciertos usuarios sólo puedan acceder a consultas por medio de SP... imagínate.

Respecto a tu "trigger", sigues cayendo en el problema de que quieres invocar valores que no se pueden reconocer porque no son parte de la tabla disparadora del mismo TRIGGER, aunque ahora queda un poco más claro lo que quieres hacer.
El problema es que no puedes invocar una tabla simplemente llamando a esto:
Código MySQL:
Ver original
  1. envios.c_mess
Eso no es nada para el SQL. No hay ningún indicativo de cómo recuperar eso o qué es lo que quieres hacer o de dónde lo obtiene. De hecho, con esa sintaxis parece que te refirieras a la base de datos "envíos" y que busque una tabla denominada "c_mess".

Empecemos desde el principio: La única forma en que un DBMS opere con datos en tablas es usando alguna de las sentencias DML (Data Management Language), las cuales son sólo cuatro: SELECT, INSERT, DELETE o UPDATE. Y tu no estás usando ninguna para esta comparación. Ninguna.
¿Cómo quieres que el SQL sepa de dónde o cómo obtener ese dato? No se lo estás diciendo.

En definitiva, si quieres comparar uno o más valores con algún registro de una tabla, debes realizar un SELECT que te permita definir si existe algún registro que cumpla con la condición dada.
A diferencia de un lenguaje de programación, "envios" no es un objeto contenedor o una Collection de algún tipo a la que puedas acceder por alguna propiedad o método. Es una tabla de datos en disco que sólo se puede leer con SELECT.

Bien, yendo a tu problema, la cosa tiene solución:

Con TRIGGER sería más o menos así:
Código MySQL:
Ver original
  1. DELIMITER $$
  2. DROP TRIGGER IF EXISTS `base5`.`enviados`$$
  3. CREATE TRIGGER `base5`.`enviados`
  4. BEFORE INSERT ON `base5`.`enviados`
  5.     IF (SELECT id_user FROM envios WHERE envios.c_mess = NEW.c_mess AND id_user = NEW.m_to  AND envios.m_to  = NEW.id_user) IS NOT NULL THEN
  6.         -- este update lo tengo por que es fácil de comprobar si funciona lo anterior
  7.         UPDATE `base5`.`usuarios` SET `acces`= CURRENT_TIMESTAMP WHERE `id_user`=NEW.id_user;
  8.     END IF;
  9. END$$
  10. DELIMITER ;

Mas o menos por allí.
__________________
¿A quién le enseñan sus aciertos?, si yo aprendo de mis errores constantemente...
"El problema es la interfase silla-teclado." (Gillermo Luque)
  #10 (permalink)  
Antiguo 11/10/2011, 04:09
Avatar de J_de_Prado  
Fecha de Ingreso: junio-2011
Ubicación: Alpedrete, Madrid,España
Mensajes: 28
Antigüedad: 8 años, 8 meses
Puntos: 2
Respuesta: Provocar un update con dos filas de la misma tabla

Por fin parece que funciona , he utilizado este código, aunque quedan cosas pendientes como cambiar el nombre de las tablas para que no den lugar a confusión, la cosa funciona,

Código MySQL:
Ver original
  1. DROP TRIGGER IF EXISTS `enviados`//
  2. CREATE TRIGGER `enviados` BEFORE INSERT ON `enviados`
  3.  IF
  4.     (SELECT id_user FROM envios WHERE envios.c_mess = NEW.c_mess
  5.     AND id_user = NEW.m_to  AND envios.m_to  = NEW.id_user) IS NOT NULL
  6.  THEN -- del id_user coincidente, ponemos la col 'ok' a '1'          
  7.     UPDATE `base5`.`envios` SET `ok` = 1 WHERE `id_user`= NEW.m_to
  8. AND envios.c_mess = NEW.c_mess AND envios.m_to = NEW.id_user;;
  9.    -- y pasamos los nuevos valores de la nueva fila a la tabla donde quedara.
  10.    INSERT INTO `base5`.`envios` (`id_user`, `c_mess`, `m_to`, `ok`)
  11.     VALUES (NEW.id_user,NEW.c_mess,NEW.m_to, 1);
  12.  ELSE  -- pero si ni ha habido coincidencia pues lo mismo, menos la col `ok`
  13.     INSERT INTO `base5`.`envios` (`id_user`, `c_mess`, `m_to`, `ok`)
  14.     VALUES (NEW.id_user,NEW.c_mess,NEW.m_to, 0);
  15.     -- queda pendiente borrar filas para que no crezca mucho esta tabla,  
  16.    
  17.  END IF;
  18.  
  19. //
Como puedes ver en utilizo la tabla 'enviados' para poder insertar y editar la tabla que me interesa, como te decía parece que funciona bien aunque le faltan cosas como comprobar filas duplicadas y borrar el contenido de la tabla que contiene el trigger (ya se que esto no se puede desde el propio trigger)

Cita:
Respecto a tu "trigger", sigues cayendo en el problema de que quieres invocar valores que no se pueden reconocer porque no son parte de la tabla disparadora del mismo TRIGGER, aunque ahora queda un poco más claro lo que quieres hacer.
Ya veo... y se que al final todo se ejecuta en la memoria, pero tenia la equivocada idea que el una BD todo lo que le pidas, el motor de la BD lo busca.

Cita:
- PHP es un lenguaje del lado del servidor, las operaciones no se realizan en el cliente (como el JavaScript), de modo que realizar ciertas operaciones dentro de un script PHP es bastante más seguro de lo que piensas. Ten en cuenta que para llegar a tus scripts (y a tu ejecución) primero tienen que hackearte el host y la base... con lo que el que capturen el proceso es el menor de tus problemas. A esa altura es probable que ya no te quede ni website, ni base...
- Los SP son mucho más seguros que usar consultas directas, fundamentalmente porque son invulnerables al sql-injection, ya que como sólo pueden recibir parámetros, y los parámetros operan dentro del SP, no pueden incrustar una sentencia ilegal dentro de ellos.
- Por otro lado, los SP permiten hacer un conjunto de operaciones mucho más extensas que simples consultas, lo que los vuelve ideales para procesos más o menos complejos.
- El uso de diferentes usuarios que certifiquen contra el servidor de MySQL es una forma ideal de seguridad por muchas razones, como: 1) El sistema de encriptación de las claves en MySQL no es desencriptable; 2) Permite no recargar conexiones a usuarios y restringir la cantidad de conexiones que un usuario puede hacer; 3) Permite definir una buena granularidad de permisos para los roles de los usuarios, lo que se puede llevar al extremo de que ciertos usuarios sólo puedan acceder a consultas por medio de SP... imagínate.
Este proyecto que estoy intentando poner en marcha se realizo hace varios años en V Basic -Acces aunque no se puso en circulación por la irrupción de la Internet.
Hace unos meses comencé a tratar de ver ma mejor la forma de "ponerlo en la calle" y decidí probar con MYSQL y PHP, comencé leyendo algo de cada tema y me llamo la atención la existencia de programas de pago para disfrazar el código PHP así que pensé en que cuanto menos permisos para los usuarios desde la web tanto mejor. Por por lo que dices la seguridad no es problema. Tengo la intención de que cuando tenga mas o menos la base de datos funcional,que intervengan otras personas y yo mismo creando aplicaciones (android) o con el mismo PHP y en un principio creo facilitara las cosas el que algunas funciones las realice la propia base de datos, y que desde PHP solo se lea o inserte en las tablas, aunque al final termine la cosa como tu apuntas.

¡MUCHAS GRACIAS POR TU PACIENCIA Y CONSEJOS!

Última edición por J_de_Prado; 11/10/2011 a las 04:50

Etiquetas: campos, filas, tabla, update
Atención: Estás leyendo un tema que no tiene actividad desde hace más de 6 MESES, te recomendamos abrir un Nuevo tema en lugar de responder al actual.
Respuesta




La zona horaria es GMT -6. Ahora son las 01:07.