bien planteado para manejar claves foraneas y FULLTEXT

[franroa]

Hola buenas,

tengo en la base de datos una tabla en la que quiero hacer una búsqueda de texto completo. Pero esta tabla esta relacionada con otras. Y lo que habia pensado era hacer la tabla "archivos" de tipo InnoDB para poder relacionar con las demas, y después hacer una tabla "archivosBusqueda" de tipo MySAM que se borraria a trabes de la aplicacion cada vez que se borre un archivo por parte de cualquier otra que este asociada a la pimera tabla con el borrado en cascada.

Está bien planteado? O hay alguna razón por la que otra opción sea mejor?

Muchas gracias!

[leonardo_josue]

Hola franroa:

El planteamiento que haces creo que es correcto, sin embargo tengo una duda en cuanto a esto que pones:

hacer una tabla "archivosBusqueda" de tipo MySAM que se borraria a trabes de la aplicacion cada vez que se borre un archivo por parte de cualquier otra que este asociada a la pimera tabla con el borrado en cascada.

El concepto de eliminación en cascada sólo aplica sobre tablas InnoDB, cuando defines llaves foráneas entre dos o más tablas, sin embargo con tablas MyISAM no lo puedes hacer... también tengo dudas en cuando a hacerlo a través de una aplicación, en realidad tampoco creo que sea conveniente, pues te expones a que cualquier persona que tenga acceso al servidor haga modificaciones a la BD sin que te des cuenta y por lo tanto la tabla quede desactualizada. Lo ideal es que dejes que el DBMS se encargue de hacer la eliminación o actualización de tu tabla MyISAM a partir de lo que haces en tu tabla InnoDB. Una idea sería mediante un trigger, checa estas ligas, creo que ahí se explica bastante bien a lo que me refiero.


http://www.joinfu.com/2006/02/emulat...myisam-tables/

http://stackoverflow.com/questions/5...storage-engine

Saludos
Leo.

[franroa]

Sisi, lo de las limitaciones de MySAM lo conocía.
Quería decir que tengo las tablas:

archivos (InnoDB)
usuarios (InnoDB)

Y la union de ellas seria archivosUsuarios (InnoDB)
Y hay varias tablas mas relacionadas con la tabla "archivos"

Pero el campo "descripcion" de los archivos sería fulltext. Asi que pensaba hacer una tabla archivosBuscar (MySAM) donde estaria solo la descripcion del id del archivo.


El problema que le veía al planteamiento es que tengo que mandar en cada busqueda cada id del campo descripcion en el que se haya encontrado algo. Y le veia ahi un fallo.
Pero ahora que me dices lo de los triggers es verdad que no lo tenía en cuenta. Error de novato .

Muchas gacias

[gnzsoloyo]

Pero el campo "descripcion" de los archivos sería fulltext.

FULLTEXT no es un tipo de campo, sino de índice, el cual no aplica a las tablas InnoDB.
No confundir.

Tal vez te estés refiriendo a un campo TEXT. que es otra cosa, y me parece algo excesivo para una columna que contiene meras descripciones.

[franroa]

Cierto, no he hablado con propiedad. Quería decir índice.

He mirado la pagina que me has recomendado y resolvi hacer este trigger:


DELIMITER |

CREATE TRIGGER deletecascade AFTER DELETE ON files
FOR EACH ROW
BEGIN
DELETE FROM portalfiles WHERE pfFileID = old.fileID;
DELETE FROM userfiles WHERE ufFileID = old.fileID;
DELETE FROM subjectfiles WHERE sfFileID = old.fileID;
END
|

DELIMITER ;


Me lanza el error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELIMITER #

CREATE TRIGGER deletecascade AFTER DELETE ON files
FOR EACH ROW
' at line 1

[leonardo_josue]

Hola franroa:

El código que pones no tienen ningún error, además menciona un caracter # el cual ni siguiera pones en el código... Checa este script, creo las cuatro tablas que mencionas, con un solo campo, inserto datos en las cuatro tablas, creo el trigger, elimino un elemento de la tabla files y se eliminan de las otras tablas, no hay ningún error en el código:

Código MySQL:

mysql> SELECT * FROM files;
+--------+
| fileID |
+--------+
|      1 |
|      2 |
|      3 |
+--------+
3 rows in set (0.00 sec)
 
mysql> SELECT * FROM portalfiles;
+----------+
| pfFileID |
+----------+
|        1 |
|        2 |
|        3 |
+----------+
3 rows in set (0.00 sec)
 
mysql> SELECT * FROM userfiles;
+----------+
| ufFileID |
+----------+
|        1 |
|        2 |
|        3 |
+----------+
3 rows in set (0.00 sec)
 
mysql> SELECT * FROM subjectfiles;
+----------+
| sfFileID |
+----------+
|        1 |
|        2 |
|        3 |
+----------+
3 rows in set (0.00 sec)
 
mysql> DELIMITER |
mysql> CREATE TRIGGER deletecascade AFTER DELETE ON files
    -> FOR EACH ROW
    -> BEGIN
    -> DELETE FROM portalfiles WHERE pfFileID = old.fileID;
    -> DELETE FROM userfiles WHERE ufFileID = old.fileID;
    -> DELETE FROM subjectfiles WHERE sfFileID = old.fileID;
    -> END
    -> |
Query OK, 0 rows affected (0.05 sec)
 
mysql> DELIMITER ;
mysql> DELETE FROM files WHERE fileID = 1;
Query OK, 1 row affected (0.04 sec)
 
mysql> SELECT * FROM files;
+--------+
| fileID |
+--------+
|      2 |
|      3 |
+--------+
2 rows in set (0.00 sec)
 
mysql> SELECT * FROM portalfiles;
+----------+
| pfFileID |
+----------+
|        2 |
|        3 |
+----------+
2 rows in set (0.00 sec)
 
mysql> SELECT * FROM userfiles;
+----------+
| ufFileID |
+----------+
|        2 |
|        3 |
+----------+
2 rows in set (0.00 sec)
 
mysql> SELECT * FROM subjectfiles;
+----------+
| sfFileID |
+----------+
|        2 |
|        3 |
+----------+
2 rows in set (0.00 sec)
 

Revisa bien qué es lo que trataste de ejecutar y qué es lo que postaste.

Saludos
Leo.

[franroa]

Es eso lo que qería hacer. Pero definitivamente hay algo cuando defino los triggers que no hago bien... Y no doy con ello.

Por ejemplo, esto me lanza el mismo error:

Código MySQL:

CREATE TABLE files(
fileID BIGINT NOT NULL AUTO_INCREMENT,
fPath TEXT NOT NULL,/*UNIQUE*/
fDescription VARCHAR(250) NOT NULL,
fName VARCHAR (20) NOT NULL,T NULL,
fUploader INT NOT NULL,
PRIMARY KEY (fileID),
)ENGINE=INNODB;
 
 
CREATE TABLE filedescriptions(
fileID BIGINT NOT NULL,
fName VARCHAR (20) NOT NULL,
fDescription VARCHAR(250) NOT NULL, 
PRIMARY KEY (fileID),
FULLTEXT KEY description (fName, fDescription)
)ENGINE=MyISAM;
 
 
 
 
DELIMITER ;;
CREATE TRIGGER 'insert_file' AFTER INSERT ON 'files' FOR EACH ROW BEGIN
    INSERT INTO filedescriptions (fileID, fName, fDescription)
        VALUES (new.fileID, new.fName, new.fDescription);
  END;;
 
 
CREATE TRIGGER 'update_file' AFTER UPDATE ON 'files' FOR EACH ROW BEGIN
    IF (old.fileID!= new.fileID) or (old.fDescription!= new.fDescription)
    THEN
        UPDATE filedescriptions
            SET fileID=new.fileID,
                fDescription=new.fDescription,
                fileID=new.fileID
        WHERE fileID=old.fileID;
    END IF;
  END;;
 
 
CREATE TRIGGER 'delete_file' AFTER DELETE ON 'files' FOR EACH ROW BEGIN
    DELETE FROM filedescriptions WHERE fileID = old.fileID;
  END;;
 
DELIMITER ;
 

Ya siento robaros el tiempo. Pero llevo horas con esto, y de verdad que no tengo ni idea de que puede ser.

[franroa]

Bueno, he estado mirando en la pagina oficial de MySQL y he intentado implementar el trigger que tienen ahi como ejemplo. Me vuelve a lanzar el mismo error. Así que no es fallo de sintaxis.

Tengo la version 5.0 del MySQL. Tengo entendido que a partir de esta se pueden usar los triggers. Aunque tal vez use otra sintaxis. O no me queda mas remdio que actualizar?...

[leonardo_josue]

Hola de nuevo franroa:

Creo que debes poner un poco más de atención a lo que lees en los manuales de MySQL o lo que ponemos en el Foro, porque estás cometiendo errores infantiles con tu código. Vayamos por partes:

Código:

CREATE TABLE files(
fileID BIGINT NOT NULL AUTO_INCREMENT,
fPath TEXT NOT NULL,/*UNIQUE*/
fDescription VARCHAR(250) NOT NULL,
fName VARCHAR (20) NOT NULL,T NULL,
fUploader INT NOT NULL,
PRIMARY KEY (fileID),
)ENGINE=INNODB;

Esta sentencia está mal por el código que está marcado en rojo, no sé si copiaste mal la sentencia o se cortó alguna parte, pero en la primer parte NO ESTÁS DEFINIENDO NINGÚN CAMPO, después solo pones T NULL, eso no corresponde a ninguna declaración, MySQL toma la coma como separador de la definición de cada campo, por lo tanto, toma T como un campo, pero no colocas ningún tipo de datos, ni nada por el estilo. En la última parte, después del PRIMARY KEY (fileID) NO DEBES COLOCAR NINGUNA COMA, porque te repito indica separador de campos... la sentencia debería quedar así:

Código MySQL:

CREATE TABLE files(
fileID BIGINT NOT NULL AUTO_INCREMENT,
fPath TEXT NOT NULL,
fDescription VARCHAR(250) NOT NULL,
fName VARCHAR (20) NOT NULL,
fUploader INT NOT NULL,
PRIMARY KEY (fileID)
)ENGINE=INNODB;
 

Siguiendo con los errores:

Código:

DELIMITER ;;
CREATE TRIGGER 'insert_file' AFTER INSERT ON 'files' FOR EACH ROW BEGIN
    INSERT INTO filedescriptions (fileID, fName, fDescription)
        VALUES (new.fileID, new.fName, new.fDescription);
  END;;

checa esta liga:

http://dev.mysql.com/doc/refman/5.0/es/legal-names.html

El carácter de encomillado de identificador es el acento grave: ('`'):

la comilla, tal como la colocas indica una CADENA DE TEXTO, para definir los nombres de los objetos de BD debes utilizar el ACENTO GRAVE (`) o en su defecto omitir este caracter...

Tu sentencia debería quedar así:

Código MySQL:

DELIMITER ;;
CREATE TRIGGER insert_file AFTER INSERT ON files FOR EACH ROW BEGIN
    INSERT INTO filedescriptions (fileID, fName, fDescription)
        VALUES (new.fileID, new.fName, new.fDescription);
  END;;
 

o con el acento grave:

Código MySQL:

DELIMITER ;;
CREATE TRIGGER `insert_file` AFTER INSERT ON `files` FOR EACH ROW BEGIN
    INSERT INTO filedescriptions (fileID, fName, fDescription)
        VALUES (new.fileID, new.fName, new.fDescription);
  END;;
 

Este código se ejecuta perfectamente en un servidor 5.5:

Código MySQL:

mysql> CREATE TABLE files(
    -> fileID BIGINT NOT NULL AUTO_INCREMENT,
    -> fPath TEXT NOT NULL,
    -> fDescription VARCHAR(250) NOT NULL,
    -> fName VARCHAR (20) NOT NULL,
    -> fUploader INT NOT NULL,
    -> PRIMARY KEY (fileID)
    -> )ENGINE=INNODB;
Query OK, 0 rows affected (0.09 sec)
 
mysql> CREATE TABLE filedescriptions(
    -> fileID BIGINT NOT NULL,
    -> fName VARCHAR (20) NOT NULL,
    -> fDescription VARCHAR(250) NOT NULL,
    -> PRIMARY KEY (fileID),
    -> FULLTEXT KEY description (fName, fDescription)
    -> )ENGINE=MYISAM;
Query OK, 0 rows affected (0.07 sec)
 
mysql> DELIMITER ;;
mysql> CREATE TRIGGER insert_file AFTER INSERT ON files FOR EACH ROW BEGIN
    ->     INSERT INTO filedescriptions (fileID, fName, fDescription)
    ->         VALUES (new.fileID, new.fName, new.fDescription);
    ->   END;;
Query OK, 0 rows affected (0.07 sec)
 
mysql> DELIMITER ;;
mysql> CREATE TRIGGER update_file AFTER UPDATE ON files FOR EACH ROW BEGIN
    ->     IF (old.fileID!= new.fileID) OR (old.fDescription!= new.fDescription)
 
    ->     THEN
    ->         UPDATE filedescriptions
    ->             SET fileID=new.fileID,
    ->                 fDescription=new.fDescription,
    ->                 fileID=new.fileID
    ->         WHERE fileID=old.fileID;
    ->     END IF;
    ->   END;;
Query OK, 0 rows affected (0.07 sec)
 
mysql> CREATE TRIGGER delete_file AFTER DELETE ON files FOR EACH ROW BEGIN
    ->     DELETE FROM filedescriptions WHERE fileID = old.fileID;
    ->   END;;
Query OK, 0 rows affected (0.07 sec)
 

Haz las correcciones pertinentes y pruébalo en el servidor 5.0, y si, deberías considerar hacer la migración de tu BD, pero insisto que ante todo debes poner atención en lo que haces.

Saludos
Leo.

[gnzsoloyo]

Nota adicional a lo dicho por @leonardo_josue:
En la creación de rutinas (procedimientos, funciones, triggers, eventos), se usa DELIMITER para cambiar el comportamiento del intérprete de modo que tome la creación del objeto como un todo.
Eso está bien, pero lo que no debes hacer jamás es usar para nuevo delimitador caracteres que sean parte de los reservados para uso de MYSQL.
Nunca.
Estos son: !%&/()=^`´:;\|@#+-
Si observas con cuidado los ejemplos del manual verás que siempre usa "$$", porque ese par jamás se usa dentro del SQL.
No inventes cosas. Si se hace en el manual oficial de determinada forma es porque es conveniente hacerlo así de modo seguro.

[franroa]

Perdona leonardo_josue, me ha pasado eso que dices.

En mi tabla no tengo esos errores, pero quise recortarla porque no queria poner informacion basura que solo ocupara espacio. Se supone que despues del primary key hay un foreign Key. Y el "T NULL" es porque no borre bien lo que había en medio.

Tampoco tengo las comillas en mi tabla. Aunque confieso que no me di cuenta que las tenia hasta que las vi posteadas.


Definitivamente es seguro que no funciona en 5.0



De verdad que siento el despiste... En mi tabla estan bien.
Muchas gracias a los dos.