Hello Piwigo team and plugin coders :-)
As described in [Forum, post 108426 by plg in topic 15259] Can a plugin alter a table ?, I wrote:
plg wrote:
I was not aware that so many plugins were modifying the history.section column. And this is a problem. It will clearly break if the next upgrade updates the column. We should think of another way to list the possible sections than an enum field. I'll open a topic for this specific problem and invite plugin authors.
I also read in [Bugtracker] ticket 722 that the ENUM type for history.section column was a problem and the conclusion was to convert history.section into a foreign to history_section.id
Before inserting a line in the history table, it would be necessary to find the correct history_section.id. It would be nice BUT it means 1 additional request each time we log a page view. And I would like to avoid this request. I've tried to perform a subselect in an insert request:
mysql> create table history (name varchar(40), section_id int);
Query OK, 0 rows affected (0.00 sec)
mysql> create table history_section (id int not null auto_increment, section_name varchar(40), primary key(id));
Query OK, 0 rows affected (0.00 sec)
mysql> insert into history_section (section_name) values ('abc'), ('def'), ('ghi');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from history_section;
+----+--------------+
| id | section_name |
+----+--------------+
| 1 | abc |
| 2 | def |
| 3 | ghi |
+----+--------------+
3 rows in set (0.01 sec)
mysql> insert into history (name, section_id) values ('line 1', (select id from history_section where section_name = 'def'));
Query OK, 1 row affected (0.00 sec)
mysql> insert into history (name, section_id) values ('line 2', (select id from history_section where section_name = 'abc'));
Query OK, 1 row affected (0.00 sec)
mysql> select * from history;
+---------+------------+
| name | section_id |
+---------+------------+
| line 1 | 2 |
| line 2 | 1 |
+---------+------------+
2 rows in set (0.00 sec)So I think it is quite good. It is the first time we use subselect as far as I can remember.
This would mean that plugins like...
* [extension by P@t] Additional Pages
* [extension by P@t] Has High
* [extension by rub] Web services statistics
* [extension by P@t] Lightbox
* [extension by P@t] Most Commented
... will insert lines into history_section instead of altering the structure of history table.
What's your opinion about this?
Offline
plg wrote:
It would be nice BUT it means 1 additional request each time we log a page view. And I would like to avoid this request. I've tried to perform a subselect in an insert request:
[...]
mysql> insert into history (name, section_id) values ('line 1', (select id from history_section where section_name = 'def'));
Query OK, 1 row affected (0.00 sec)
by doing a subselect query, it's like making 2 queries.
just, it's made directly by the server (like a JOIN) so, we can think it's optimized (compared with 2 requests made with PHP).
Offline
I dislike a foreign key more than enum itself...
I will take time to analyse and to make a proposal.
;-)
Offline
For me, enum list is a problem for my listed plugins...
So I agree to modify it.
Offline
P@t wrote:
For me, enum list is a problem for my listed plugins...
So I agree to modify it.
Let's wait another few days for VDigital proposition that would be better than a foreign key :-)
Offline
Thanks because I forget that point.
By this weekend I promise.
;-)
Offline