search

roundcube / plugin-installer

A composer installer for Roundcube plugins
26 stars 15 forks source link

Cannot reference (FK) other tables when using table prefix #19

Closed mochja closed 3 years ago

mochja commented 5 years ago

Hello I'm working on plugin that uses database. I do have simple initial mysql SQL migration.

It seems like I'm not able to run migrations successfully when table prefix is used and SQL is mentioning foreign tables.

It seems like a table list is done per sql command, which is not able to replace referencing tables as it does not know them. https://github.com/roundcube/plugin-installer/blob/98af3c61566b4387aeae76cfa89076a8c39a0ddc/src/bin/rcubeinitdb.sh#L44-L60

Also generic tables (eg. users) for roundcube cannot be referenced when using prefix.

example sql

CREATE TABLE `a` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  KEY `id` (`id`)
) /*!40000 ENGINE=InnoDB */ DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE `b` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `a_id` int(11) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `a_id` (`a_id`),
  CONSTRAINT `a_id_ibfk_1` FOREIGN KEY (`a_id`) REFERENCES `a`(`id`) ON DELETE CASCADE ON UPDATE CASCADE
) /*!40000 ENGINE=InnoDB */ DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
alecpl commented 5 years ago

rcube_db class has this problem resolved, so a solution for plugin-manager would be to replace the script with rcmail_utils::db_init() and rcmail_utils::db_update() use as suggested in #21.

rcmail_utils::db_init() has been added in Roundcube 1.2, so it would be a breaking change for those still using installer on an old version, but I think we can live with that. Maybe just print an error when such version is detected.

alecpl commented 3 years ago

It should be fixed in plugin-installer 0.3.0.