How to create the Exif Information Store and how it can disappear

[gianso]

311/5000
:)After updating from version 1.5.42 to 1.5.46 I noticed that in the dbase (readable by an external plugin) where the exif information was stored, many have disappeared. How was it possible? Is there a way to restore dbase? From reading this post, it would seem no, but it was 2013 !!!!
http://forum.coppermine-gallery.net/index.php/topic,76207.msg367804.html#msg367804

Dopo l'aggiornamento dalla versione 1.5.42 a 1.5.46 mi sono accorto che nel dbase (leggibile da un plugin esterno) dove erano memorizzate le informazioni exif, molte sono scomparse. Com'è stato possibile? C'è un modo per ripristinare il dbase? Dalla lettura di questo post, sembrerebbe di no, ma era il 2013!!!!
http://forum.coppermine-gallery.net/index.php/topic,76207.msg367804.html#msg367804

[Αndré]

How was it possible?

I just found this line in update.sql:

Code Select Expand

TRUNCATE TABLE CPG_exif;

By now I wasn't aware that it exists. It has been added in SVN revision 5689: https://sourceforge.net/p/coppermine/code/5689/

Is there a way to restore dbase?

If neither you nor your hosting provider have backups of your MySQL database, there's no way to restore the EXIF table. I created a script a while ago which reads the EXIF data of all pictures. Maybe this helps if you have no backup.

[gmc]

As I recall - that table was cleared during upgrades dating back to when changes were being made - and needed the info to be refreshed... Apparently still carried forward in future updates. (old SQL never dies...)

The information in the exif table will be repopulated as photos are viewed (assuming EXIF options are turned on) - as it was populated originally.  Visitors will not know the difference - they will still see whatever info you have it set to display.

[gianso]

I will reply to the technician who created for www.naturavventura.it the plugin, definitely linked to a dbase archive that should contain all the exif data of every uploaded image; Will be able to explain better technically and also say how to use the plugin correctly (hopefully)

Farò rispondere al tecnico che ha realizzato per www.naturavventura.it il plugin, collegato sicuramente ad un archvio nel dbase che dovrebbe contenere tutti i dati exif di ogni immagine caricata; potrà spiegarsi meglio tecnicamente e dire anche come poter utilizzare correttamente il plugin (spero)

[toaster]

Dear All,

I am Raffaele, the developer of the plugin that gianso was talking about some posts ago: If I have understood well every time there is an update the exif table is truncated and it will be populated again each time a user visualize a photo am I correct (populated with the exif data associated with that photo) ? All our problems comes from the plugin I developed that basically allows to change the foto owner, (gianso had this needs to better organize and reassign the owner for more than 30.000 photos). To do that I build an html table where also exif information are shown (basically the camera brand and model) and make the possibility to filter results also based on the exif data (like give me all the photos taken from a Canon). I think the best way is for me to create a script that will repopulate the exif table just scanning through all the photos and, after every update, I will execute the script again, could it be a good way to solve this issue? Maybe there would be better ideas?

Thanks in advance

Raffaele

[Αndré]

Next time, I'd just delete that line from sql/update.sql when updating the database:

Code Select Expand

TRUNCATE TABLE CPG_exif;

I think the best way is for me to create a script that will repopulate the exif table just scanning through all the photos

I've already done that

I created a script a while ago which reads the EXIF data of all pictures.

but for some reason gianso seems to ignore me.

[gmc]

Perhaps a safer way to prevent emptying is to remove the TRUNCATE privilege from your dbuser Id in MySQL...

it should be rare that we need to empty a table... But once we do, the statement remains in future upgrades since we allow near unlimited jumping of releases.

The upgrade script does not look at the success/failure of each statement... As many fail if previously executed on your system. Do it will not be affected by the security failure.

At least this way you wouldn't have to remember to change the code with each upgrade...

Perhaps a better long-term fix is a change to the upgrade process that would know what version the upgrade script was last run for, and be able to conditionally execute statements.

Something like
Config table entry 'update_release' populated at end of upgrade script with current release
On running update.PHP, it retrieves value... If not found, uses 0.
Each statement in update.PHP begins with the release requiring the statement.
If greater than 'update_release', then statement is executed... Otherwise bypassed...
At end, update config value.

Would actually allow adding error checking (if 'update_release' set) as we get rid of the 'already done' condition...
And allows easy verification that update.PHP was run.... Could warn an admin accessing the gallery that update.PHP is needed.

[gianso]

 :)Dear Andre, I am sorry for late reply. I would be interested in the php script you mentioned earlier, It will be very helpful and save my life, if it won't bother too much you may send it to me via mail or it the way you prefer, also paste it here!".
Thank you so much for your precious help

Caro Andre, mi dispiace per il ritardo della mia risposta. Sarei interessato allo script php che hai citato in precedenza, sarà molto utile e salvarmi la vita, se non ti preoccuperebbe troppo potresti inviarlo via posta o o nel modo che preferisci, anche incollarlo qui!
Grazie mille per il tuo prezioso contributo

[Αndré]

Here's the script I created for altenae.

Create a file update_exif.php in the root directory of your gallery and paste the following code:

Code Select Expand

<?php
define('IN_COPPERMINE', true);

require_once('include/init.inc.php');
include("include/exif_php.inc.php");

if (!GALLERY_ADMIN_MODE) {
    cpg_die(ERROR, $lang_errors['access_denied'], __FILE__, __LINE__);
}

$limit = 10;
$start = $superCage->get->keyExists('start') ? $superCage->get->getInt('start') : 0;
if ($start == 0) {
    cpg_db_query("DELETE FROM {$CONFIG['TABLE_EXIF']}");
}
$result = cpg_db_query("SELECT * FROM {$CONFIG['TABLE_PICTURES']} LIMIT $start,$limit");
$done = !mysql_num_rows($result) ? true : false;

pageheader('Update EXIF data');

while ($row = mysql_fetch_assoc($result)) {
    $path_to_pic = $CONFIG['fullpath'] . $row['filepath'] . $row['filename'];
    $path_to_orig_pic = $CONFIG['fullpath'] . $row['filepath'] . $CONFIG['orig_pfx'] . $row['filename'];
    $metadata_path = file_exists($path_to_orig_pic) ? $path_to_orig_pic : $path_to_pic;
    exif_parse_file($metadata_path, $row['pid']);
    echo $row['pid']. ': '.$metadata_path.'<br />';
}

if ($done) {
    echo "Done.";
} else {
    echo '<meta http-equiv="refresh" content="0; URL='.$CPG_PHP_SELF.'?start='.($start+$limit).'">';
}

pagefooter();

//EOF

You could try to increase the $limit value to e.g. 50 or higher. This will speed up the process, but you need to be careful to not exceed the maximum PHP execution time.

[gianso]

 
Thanks André for the code. I will inform Raffaele (toaster) to apply it and then repopulate the exif data table. When everything is working, I will inform everyone. Thanks again

Grazie André per il codice. Informerò Raffaele (toaster) per applicarlo e così ripopolare la tabella dei dati exif. Quando sarà tutto funzionante, informerò tutti. Grazie ancora