[RCD] plugin install with SQL and database prefix

[Sébastien BLAISOT]

Hi dear roundcube community,

First of all, I wish you all a happy new year.

I am the maintainer of the automatic_addressbook plugin (or at least I 
used to be, as I don't have much time for it now, and I would be happy 
to have it integrated in roundcube default plugins if you want, but 
that's an other story) .

I regularly get issues from users regarding database prefix when 
installing the plugin. I understand this is complicated to handle when 
installing manually, but it seems to also be the case when using 
composer (At least I got reports about failed SQL statements when 
installing with composer)

As far as I can see, SQL statements in roundcube codebase have no 
prefix, as my sql statements in my plugin. How are database prefix 
handled when installing roundcube? How are they handled when installing 
a plugin with composer? (ok, I found how, see below)

I have 2 concerns:
- One is table creation (CREATE TABLE statements), on which prefix 
should added. Is that handled automatically by composer? (It seems to be 
the case)
- The second one is foreign keys that reference roundcube standard 
tables like REFERENCES `users`(`user_id`) which should be changed to 
REFERENCES `PREFIX_users`(`user_id`). Is that automatically handled by 
composer at plugin installation? (It seems to be the missing one)

Is there any documentation for plugin coders on how to properly handle 
plugins databases with references to standard roundcube databases 
regarding database prefix?

As far as I can see here: 
https://github.com/roundcube/plugin-installer/blob/0.1.6/src/bin/rcubeinitdb.sh#L91-L128
This problem is already handled for CREATE DATABASE statements and for 
CREATE SEQUENCE statements but not for REFERENCES statements as used in 
the automatic_addressbook plugin here:
- 
https://github.com/sblaisot/automatic_addressbook/blob/master/SQL/mysql.initial.sql#L14
- 
https://github.com/sblaisot/automatic_addressbook/blob/master/SQL/postgres.initial.sql#L21

I should be able to propose a PR on plugin_installer for that. Is there 
something more I need to be aware of before coding?

Best regards and keep making such a good webmail!

Sebastien

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.roundcube.net/pipermail/dev/attachments/20200105/fb1273ef/attachment.html>