🌍
English
Announcement
- [2024-04-17] Piwigo 14.4.0
- [2024-04-01] Piwigo in Hobbit runes
- [2024-03-01] Piwigo 14.3.0
- [2024-01-30] Piwigo 14.2.0
- [2023-12-29] Piwigo 14.1.0
#1 2019-08-07 02:53:12
- dd-b
- Member
- Minneapolis, MN USA
- 2018-04-16
- 70
Best practices for extending the database
I'm looking at what it would take to write a plugin to make some of the metadata-to-database transferring (and displaying of this additional metadata) more stable across Piwigo updates.
My prototype, not done as an extension, worked (back in the spring when I built it), I just added some columns to database tables. Is that a good practice, though? Seems like there's at least some risk of future database updates to tables Piwigo think belong to it damaging my data.
What about doing it as an extension? More work up front, but can this be more stable across software updates, and can use a private table (I think it'd be parallel to the existing Images table, and use the same ids, and use triggers to keep itself in sync; it would contain the additional metadata I wanted to store in the database so it could be searched on and such. I need to capture the category fields, and the special instructions fields, and it looked like city wasn't captured in the database anywhere for some weird reason, and event and location fields). Is a table dedicated to the extension the best practice for significant database additions in a plugin?
Piwigo 2.9.5
Operating system: Linux
PHP: 7.0.33 (Show info) [2019-08-06 17:52:38]
MySQL: 5.7.25-log [2019-08-06 17:52:38]
Graphics Library: ImageMagick 6.9.7-4
Offline
#2 2019-08-13 16:58:51
- plg
- Piwigo Team
- Nantes, France, Europe
- 2002-04-05
- 13791
Re: Best practices for extending the database
If you need to add extra fields to an image, you have several solutions:
1) you add extra columns in table `piwigo_images`.
2) you create a new table `piwigo_ddb_images` with `image_id`as primary key and your extra fields.
The problem with solution 2 is that you will quickly get out of sync: each time a photo is added, you need to add a line in your table, each time a photo is removed you need to remove the line from your table.
Solution 1 removes this problem, by design. I would go to this solution. Actually this is what I do with some extensions, such as [extension by plg] Download Counter or [extension by plg] Birthdate
Offline
#3 2019-08-14 03:11:33
- dd-b
- Member
- Minneapolis, MN USA
- 2018-04-16
- 70
Re: Best practices for extending the database
Sync isn't hard, I can use the same unique ID, and I can use a mysql trigger to insure that deletes coming from the man code also handle my table. Mug Shot installs a trigger like that for its table (as I discovered when doing some manual cleanup in a scratch install; I briefly had a config where the trigger was still there but I had deleted the table it referred to).
But on further consideration the issue is using new fields; my current thought is to use virtual keywords (and get them turned into actual tags) instead of new fields.
Offline