•  » Engine
  •  » [coding] history.section, enum type or foreign key

#1 2009-08-20 22:39:09

plg
Piwigo Team
Nantes, France, Europe
2002-04-05
14067

[coding] history.section, enum type or foreign key

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:

Code:

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

 

#2 2009-08-20 23:38:29

grum
Former Piwigo Team
Pantin
2007-09-10
1371

Re: [coding] history.section, enum type or foreign key

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).


My pictures with Piwigo, of course !
[ www.grum.fr ]

Offline

 

#3 2009-08-21 07:58:24

VDigital
Former Piwigo Team
Paris (FR)
2005-05-04
17680

Re: [coding] history.section, enum type or foreign key

I dislike a foreign key more than enum itself...
I will take time to analyse and to make a proposal.
;-)


Piwigo.com: Start and run your own photo gallery. Signup and get 30 days to try for free, no commitment.
8-)

Offline

 

#4 2009-09-23 13:07:07

P@t
Piwigo Team
Nice
2007-06-13
4098

Re: [coding] history.section, enum type or foreign key

For me, enum list is a problem for my listed plugins...
So I agree to modify it.


P@t

Offline

 

#5 2009-09-23 21:34:04

plg
Piwigo Team
Nantes, France, Europe
2002-04-05
14067

Re: [coding] history.section, enum type or foreign key

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

 

#6 2009-09-23 21:43:38

VDigital
Former Piwigo Team
Paris (FR)
2005-05-04
17680

Re: [coding] history.section, enum type or foreign key

Thanks because I forget that point.
By this weekend I promise.
;-)


Piwigo.com: Start and run your own photo gallery. Signup and get 30 days to try for free, no commitment.
8-)

Offline

 
  •  » Engine
  •  » [coding] history.section, enum type or foreign key

Board footer

Powered by FluxBB