updating via sql script?

[ks]

Hi,

I use Imatch(www.photools.com) at home to manage my images. Imatch is scriptable and I would like to write a script to export the keywords and annotations made in imatch to my coppermine database. The Imatch script for example  could copy and resize the files to a temporary directoy and create a suitable sql script. All this gets uploaded to the webserver and the sql script is run. Voila.
My question is: which columns in the 'pictures' table are essential for coppermine to work and cannot be left out (date/time filesize fields for example) ? Can I force coppermine to create the thumbnails and intermediate images or should I create them with imatch? What about the exif info?

thanks for any input!


klaus

[Joachim Müller]

all fields in the table are necessary, what answer did you expect ?
If you manage to create this code, you should do the resizing (intermediate pics and thumbnails) on your client before upload, both for quality and performance reasons. Your idea sound interessting, please post the code you come up with.

Exif info is being read by Coppermine, but not all possible exif data is being displayed (this is being worked on for a future coppermine release).

Joachim

[ks]

Joachim, thanks for your answer. One more question: what do I do with the ctime column? Is it capture date in seconds since xxxx ??
If I come up with something useful I will sure post it here and in the imatch forum.

klaus

[Joachim Müller]

both times are being represented as Unix timestamps, your php script should be able to convert them - see http://www.php.net/manual/en/function.time.php

GauGau

[ks]

by browsing through the cpg php files I found the following function:
function add_picture in picmgmt.inc.php. Sure it would be nice to use it, but I guess I need some kind of initialization code ( connect to db, setting variables, admin mode etc). Is there some  barebone code to do that? If not, which scripts do this job in cpg? If you think it is way to complex for a php newbie please tell me!

thanks!

klaus

[Joachim Müller]

as a barebone, initialization, use

Code Select Expand


define('IN_COPPERMINE', true);
define('CALENDAR_PHP', true);

require('include/init.inc.php');

Your script has to reside in the coppermine root for this.
To actually query the db, just add

Code Select Expand

$foobar = db_query ("SELECT * FROM {$CONFIG['TABLE_BANNED']}");or similar - the mySQL connection has already been opened for you when using the include above.
As a sort of "coding standard", you shouldn't code using your actual table names, but the "aliases" defined around line 225 in include/init.inc.php:

Code Select Expand

$CONFIG['TABLE_PICTURES']        = $CONFIG['TABLE_PREFIX']."pictures";
$CONFIG['TABLE_ALBUMS']                = $CONFIG['TABLE_PREFIX']."albums";
$CONFIG['TABLE_COMMENTS']        = $CONFIG['TABLE_PREFIX']."comments";
$CONFIG['TABLE_CATEGORIES']        = $CONFIG['TABLE_PREFIX']."categories";
$CONFIG['TABLE_CONFIG']                = $CONFIG['TABLE_PREFIX']."config";
$CONFIG['TABLE_USERGROUPS']        = $CONFIG['TABLE_PREFIX']."usergroups";
$CONFIG['TABLE_VOTES']                = $CONFIG['TABLE_PREFIX']."votes";
$CONFIG['TABLE_USERS']                = $CONFIG['TABLE_PREFIX']."users";
$CONFIG['TABLE_BANNED']                = $CONFIG['TABLE_PREFIX']."banned";
$CONFIG['TABLE_EXIF']                = $CONFIG['TABLE_PREFIX']."exif";
$CONFIG['TABLE_FILETYPES']          = $CONFIG['TABLE_PREFIX']."filetypes";
$CONFIG['TABLE_ECARDS']          = $CONFIG['TABLE_PREFIX']."ecards";
$CONFIG['TABLE_TEMPDATA']        = $CONFIG['TABLE_PREFIX']."temp_data";This way, your code should become portable.

Joachim

[ks]

thanks again Joachim,

this will get me on the way  !

klaus

[ks]

A first version of the imatch script is working It creates a php script for importing the files transfered via ftp into coppermine.

one problem occurs... I get a timeout message after processing a few images with the add_picture function:

Fatal error: Maximum execution time of 10 seconds exceeded in /home/www/web294/html/images/include/picmgmt.inc.php on line 217

I can call the server php script several times to get my task completed. In each run the processing stops after 4 or 5 images. The php script I generate contains a bunch of sequences as the one shown below: ANy idea how to prevent this timeout?

klaus

$result_id = mysql_query ("SELECT pid FROM " . $CONFIG['TABLE_PICTURES'] . " WHERE filename Like '08070001.JPG'") Or Exit ();
$pid = -1;
if( mysql_num_rows($result_id) > 0 ){
$row = mysql_fetch_row ($result_id);
$pid = (int)$row[0];
}
mysql_free_result ($result_id);

if( $pid < 0 ){

add_picture(1, 'temp/','08070001.JPG','00001 ','',' 2004-08-Fahrradtour-Siethen Familie-Albert Familie-Klaus FileTypes-jpg Digicam-Casio-QV4000 Deutschland-Großbeeren','','','','',0,'','',0,0);

}
Else {
mysql_query("UPDATE " .  $CONFIG['TABLE_PICTURES'] . "SET title='00001 ',caption='',keywords=' 2004-08-Fahrradtour-Siethen Familie-Albert Familie-Klaus FileTypes-jpg Digicam-Casio-QV4000 Deutschland-Großbeeren',user1='',user2='',user3='',user4='' WHERE pid=" .  pid);
}
mysql_query("UPDATE " . $CONFIG['TABLE_PICTURES'] . " Set approved=1, owner_id=1 WHERE filename Like '08070001.JPG'");

[Joachim Müller]

you can only stop the timeout from happening if the server is yours to administer. Check the sticky threads on this board for some hints on server configuration. If you're webhosted, there's little you can do about this.

Joachim

[ks]

problem is solved!

I checked the php manual for set_time_limit(). Unfortunately, my php runs in safe mode and I'm not the administrator. However imagemagick comes to a rescue. I have used gd2 before. The convert command of  imagemagick is called via the shell and thus does not add to the scripts timeout counter (accoarding to the php manual). It seems to work now.

klaus