[Linpha-cvs] SF.net SVN: linpha: [4655] trunk/linpha2
Status: Inactive
Brought to you by:
bzrudi
From: <fan...@us...> - 2006-11-15 23:22:29
|
Revision: 4655 http://svn.sourceforge.net/linpha/?rev=4655&view=rev Author: fangehrn Date: 2006-11-15 15:22:22 -0800 (Wed, 15 Nov 2006) Log Message: ----------- 2006-11-15 flo * resolve category names in MetaData::readInformations() * "cache sql querys" in MetaData::readInformations() if we have to read a lot of metadata of the thumbnails the sql queries are reduced now by factor 3-4 Modified Paths: -------------- trunk/linpha2/ChangeLog trunk/linpha2/install/sql/sql.mysql.php trunk/linpha2/install/sql/sql.oci8po.php trunk/linpha2/install/sql/sql.postgres.php trunk/linpha2/install/sql/sql.sqlite.php trunk/linpha2/lib/classes/linpha.imgview.class.php trunk/linpha2/lib/classes/linpha.import.class.php trunk/linpha2/lib/classes/linpha.metadata.class.php Modified: trunk/linpha2/ChangeLog =================================================================== --- trunk/linpha2/ChangeLog 2006-11-15 15:40:45 UTC (rev 4654) +++ trunk/linpha2/ChangeLog 2006-11-15 23:22:22 UTC (rev 4655) @@ -1,3 +1,9 @@ +2006-11-15 flo + * resolve category names in MetaData::readInformations() + * "cache sql querys" in MetaData::readInformations() + if we have to read a lot of metadata of the thumbnails + the sql queries are reduced now by factor 3-4 + 2006-11-15 bzrudi * fixed bug in photos_cache table, where img_width and img_height were wrong for images which needs rotating (by EXIF tag) Modified: trunk/linpha2/install/sql/sql.mysql.php =================================================================== --- trunk/linpha2/install/sql/sql.mysql.php 2006-11-15 15:40:45 UTC (rev 4654) +++ trunk/linpha2/install/sql/sql.mysql.php 2006-11-15 23:22:22 UTC (rev 4655) @@ -66,6 +66,7 @@ "md5sum VARCHAR(32) NOT NULL default '', " . "width INT NOT NULL default '0', " . "height INT NOT NULL default '0', " . + "filesize INT NOT NULL default '0', " . "time_add INT NOT NULL default '0', " . "time_mod INT NOT NULL default '0', " . "time_exif INT NOT NULL default '0', " . Modified: trunk/linpha2/install/sql/sql.oci8po.php =================================================================== --- trunk/linpha2/install/sql/sql.oci8po.php 2006-11-15 15:40:45 UTC (rev 4654) +++ trunk/linpha2/install/sql/sql.oci8po.php 2006-11-15 23:22:22 UTC (rev 4655) @@ -61,6 +61,7 @@ "md5sum VARCHAR2(32), " . "width NUMBER(10) default '0', " . "height NUMBER(10) default '0', " . + "filesize NUMBER(10) default '0', " . "time_add NUMBER(12) DEFAULT '0', " . "time_mod NUMBER(12) DEFAULT '0', " . "time_exif NUMBER(12) DEFAULT '0', " . Modified: trunk/linpha2/install/sql/sql.postgres.php =================================================================== --- trunk/linpha2/install/sql/sql.postgres.php 2006-11-15 15:40:45 UTC (rev 4654) +++ trunk/linpha2/install/sql/sql.postgres.php 2006-11-15 23:22:22 UTC (rev 4655) @@ -61,6 +61,7 @@ "md5sum VARCHAR(32) NOT NULL default '', " . "width INT NOT NULL default '0', " . "height INT NOT NULL default '0', " . + "filesize INT NOT NULL default '0', " . "time_add INT NOT NULL default '0', " . "time_mod INT NOT NULL default '0', " . "time_exif INT NOT NULL default '0', " . Modified: trunk/linpha2/install/sql/sql.sqlite.php =================================================================== --- trunk/linpha2/install/sql/sql.sqlite.php 2006-11-15 15:40:45 UTC (rev 4654) +++ trunk/linpha2/install/sql/sql.sqlite.php 2006-11-15 23:22:22 UTC (rev 4655) @@ -61,6 +61,7 @@ "md5sum VARCHAR(32) NOT NULL default '', " . "width INTEGER NOT NULL default '0', " . "height INTEGER NOT NULL default '0', " . + "filesize INTEGER NOT NULL default '0', " . "time_add INTEGER NOT NULL default '0', " . "time_mod INTEGER NOT NULL default '0', " . "time_exif INTEGER NOT NULL default '0', " . Modified: trunk/linpha2/lib/classes/linpha.imgview.class.php =================================================================== --- trunk/linpha2/lib/classes/linpha.imgview.class.php 2006-11-15 15:40:45 UTC (rev 4654) +++ trunk/linpha2/lib/classes/linpha.imgview.class.php 2006-11-15 23:22:22 UTC (rev 4655) @@ -28,6 +28,7 @@ "".LIN_PREFIX."photos.id AS id, ".LIN_PREFIX."photos.name AS name, " . "".LIN_PREFIX."photos.img_type AS img_type, ".LIN_PREFIX."photos.md5sum AS md5sum, " . "".LIN_PREFIX."photos.width AS width, ".LIN_PREFIX."photos.height AS height, " . + "".LIN_PREFIX."photos.filesize AS filesize, " . "".LIN_PREFIX."photos.time_add AS time_add, ".LIN_PREFIX."photos.time_mod AS time_mod, " . "".LIN_PREFIX."photos.time_exif AS time_exif, ".LIN_PREFIX."photos.stats_numbers AS stats_numbers, " . "".LIN_PREFIX."photos.stats_views AS stats_views, ".LIN_PREFIX."photos.stats_downloads AS stats_downloads" @@ -623,6 +624,7 @@ * create thumbnail info text (filename, description, etc.) */ $i=0; + $MetaData = new LinMetaDataRead(); foreach($this->photos_filtered AS $key=>$photo_value) { if($_SESSION['mode_thumbview'] == 'thumbdetail') { @@ -631,11 +633,7 @@ $flag_nr = 13; // flag nr of thumb } - $array_image_infos = LinMetaDataRead::readInformations( - $photo_value, - LinSql::getFullImagePath( $photo_value['id'] ), - $flag_nr - ); + $array_image_infos = $MetaData->readInformations($photo_value,$flag_nr); $GLOBALS['linpha']->template->output['thumb_infos'][$photo_value['id']]['before'] = ''; $GLOBALS['linpha']->template->output['thumb_infos'][$photo_value['id']]['after'] = ''; @@ -669,7 +667,6 @@ $this->nr_text_lines = $i; // used in LinThumbnails.js to calculate the space for the thumb height - /** * switch between javascript */ @@ -948,14 +945,10 @@ /** * album informations */ - + $MetaData = new LinMetaDataRead(); foreach($this->photos_filtered AS $photo_key=>$photo_value) { - $array_image_infos = LinMetaDataRead::readInformations( - $photo_value, - LinSql::getFullImagePath( $photo_value['id'] ), - $flag_nr=12 - ); + $array_image_infos = $MetaData->readInformations($photo_value,$flag_nr=12); $this->photos_filtered[$photo_key]['album_infos'] = ''; foreach($array_image_infos AS $key=>$value) @@ -1097,7 +1090,7 @@ "FROM ".LIN_PREFIX."meta_comments " . "WHERE md5sum = '".$GLOBALS['linpha']->template->idCurrent."'"); - if( $query->EOF ) + if( $query->EOF ) // insert { $GLOBALS['linpha']->db->Execute("INSERT into ".LIN_PREFIX."meta_comments (meta_time, md5sum, meta_author, meta_comment)" . "VALUES ('".time()."','".$GLOBALS['linpha']->template->idCurrent."','".linSql::linAddslashes($_POST['author'])."'," . @@ -1134,7 +1127,6 @@ { $GLOBALS['linpha']->template->output['comment_text'] = ''; } - } @@ -1239,9 +1231,9 @@ $this->viewImgStoreImageInformation(); - $GLOBALS['linpha']->template->output['image_infos'] = LinMetaDataRead::readInformations( + $MetaData = new LinMetaDataRead(); + $GLOBALS['linpha']->template->output['image_infos'] = $MetaData->readInformations( $this->photos_filtered[ $this->current_key ], - $this->src_file, $flag_nr=10 ); @@ -1418,8 +1410,6 @@ $GLOBALS['linpha']->template->setModuleName('view_img_static'); $GLOBALS['linpha']->template->overrideModule('css','view_img'); - $this->viewImgCommon(); - if($GLOBALS['linpha']->sql->checkPermission('metadata_edit')) { /** @@ -1446,12 +1436,14 @@ { $GLOBALS['linpha']->db->Execute("INSERT into ".LIN_PREFIX."meta_data ". "(field_id, md5sum, meta_data) VALUES " . - "('".linSql::linAddslashes($key)."','".$GLOBALS['linpha']->imgview->md5sum."','".linSql::linAddslashes($value)."')"); + "('".LinSql::linAddslashes($key)."','".$GLOBALS['linpha']->imgview->md5sum."','".linSql::linAddslashes($value)."')"); } } } } } + + $this->viewImgCommon(); } } // end class linImgview Modified: trunk/linpha2/lib/classes/linpha.import.class.php =================================================================== --- trunk/linpha2/lib/classes/linpha.import.class.php 2006-11-15 15:40:45 UTC (rev 4654) +++ trunk/linpha2/lib/classes/linpha.import.class.php 2006-11-15 23:22:22 UTC (rev 4655) @@ -170,13 +170,14 @@ linLog(LOG_TYPE_IMPORT,LOG_NOTICE,'import_new',"New: ".$dirname.'/'.$filename); - list($md5sum,$file_type,$org_width,$org_height,$stats_number,$time_exif,$rotate) = + list($md5sum,$file_type,$org_width,$org_height,$stats_number,$time_exif,$rotate,$filesize) = LinImport::getFileInformation($dirname,$filename); $GLOBALS['linpha']->db->Execute("INSERT into ".LIN_PREFIX."photos ( " . - "parent_id, name, img_type, md5sum, width, height, time_add, time_mod, time_exif, rotate, stats_numbers ) " . + "parent_id, name, img_type, md5sum, width, height, filesize, time_add, time_mod, time_exif, rotate, stats_numbers ) " . "VALUES ( '".$parent_id."' , '".$GLOBALS['linpha']->sql->linAddslashes($filename)."', '".$file_type."'," . - "'".$md5sum."' , '".$org_width."' , '".$org_height."' , '".time()."', '".filemtime($dirname.'/'.$filename)."', '".$time_exif."', '".$rotate."', '".$stats_number."' )"); + "'".$md5sum."' , '".$org_width."' , '".$org_height."' , '".$filesize."', '".time()."', " . + "'".filemtime($dirname.'/'.$filename)."', '".$time_exif."', '".$rotate."', '".$stats_number."' )"); /** * delete thumbnail if one exists with the same id @@ -279,14 +280,14 @@ */ function updateEntry( $id , $dirname , $filename ) { - list($md5sum,$file_type,$org_width,$org_height,$stats_number,$time_exif,$rotate) + list($md5sum,$file_type,$org_width,$org_height,$stats_number,$time_exif,$rotate,$filesize) = LinImport::getFileInformation($dirname,$filename); $data = $GLOBALS['linpha']->db->GetRow("SELECT img_type, md5sum " . "FROM ".LIN_PREFIX."photos WHERE id = '".$id."'"); /** - * update file_type + * file type changed (not the file content) -> update file_type * * on change of file_type, change: * - img_type @@ -294,7 +295,7 @@ * - time_exif * - stats_numbers */ - if($file_type != $data[0]) + if($file_type != $data['img_type']) { linSysLog(i18n("Import").': '.i18n("Detected a change of file type, update entry of:").' '.htmlspecialchars($dirname.'/'.$filename,ENT_QUOTES)); linLog(LOG_TYPE_IMPORT,LOG_NOTICE,'import_update',"Detected a change of file type, update entry of: ".$dirname.'/'.$filename); @@ -305,26 +306,30 @@ } /** - * update md5sum + * file data change -> update md5sum * * on change of md5sum, change: * - width and height + * - filesize * - time_exif, time_mod - * - rotate ?) + * - rotate (maybe the file got an orientation tag, or the file has been autorotated with an external program) */ /** * @todo hey, thats a nice feature! we are now able to detect * changes on images and update the md5sum of the images in the comments etc.!! */ - if($md5sum != $data[1]) + if($md5sum != $data['md5sum']) { linSysLog(i18n("Import").': '.i18n("Detected a change of file data, update entry of:").' '.htmlspecialchars($dirname.'/'.$filename,ENT_QUOTES)); linLog(LOG_TYPE_IMPORT,LOG_NOTICE,'import_update',"Detected a change of file data, update entry of: ".$dirname.'/'.$filename); - $GLOBALS['linpha']->db->Execute("UPDATE ".LIN_PREFIX."photos " . - "SET md5sum = '".$md5sum."', width = '".$org_width."', height = '".$org_height."', time_mod = '".filemtime($dirname.'/'.$filename)."', time_exif = '".$time_exif."', rotate = '".$rotate."' WHERE id = '".$id."'"); + $GLOBALS['linpha']->db->Execute("UPDATE ".LIN_PREFIX."photos SET " . + "md5sum = '".$md5sum."', width = '".$org_width."', height = '".$org_height."', " . + "filesize='".$filesize."', time_mod = '".filemtime($dirname.'/'.$filename)."', " . + "time_exif = '".$time_exif."', rotate = '".$rotate."' " . + "WHERE id = '".$id."'"); - LinImport::updateMd5sum( $id, $data[1], $md5sum ); + LinImport::updateMd5sum( $id, $data['md5sum'], $md5sum ); $thumbnail = new LinImage(); $_GET['force'] = true; @@ -386,6 +391,7 @@ $rotate = "0"; $org_width = "0"; $org_height = "0"; + $filesize = 0; $ext = LinFilesys::getFileExtFromPath($filename); if($filename{0} == '.') @@ -426,6 +432,7 @@ elseif( is_file( $dirname.'/'.$filename ) ) { list($org_width,$org_height,$file_type) = LinIdentify::linGetImageSize($dirname.'/'.$filename); + $filesize = filesize($dirname.'/'.$filename); /** * calculate md5sum (not for videos we have @@ -481,7 +488,7 @@ linLog(LOG_TYPE_IMPORT,LOG_NOTICE,'import_fileinfo','Error: what am i? '.htmlspecialchars($dirname.'/'.$filename,ENT_QUOTES)); } - return array($md5sum,$file_type,$org_width,$org_height,$stats_number,$time_exif,$rotate); + return array($md5sum,$file_type,$org_width,$org_height,$stats_number,$time_exif,$rotate,$filesize); } /** Modified: trunk/linpha2/lib/classes/linpha.metadata.class.php =================================================================== --- trunk/linpha2/lib/classes/linpha.metadata.class.php 2006-11-15 15:40:45 UTC (rev 4654) +++ trunk/linpha2/lib/classes/linpha.metadata.class.php 2006-11-15 23:22:22 UTC (rev 4655) @@ -31,6 +31,8 @@ */ class LinMetaDataRead extends LinMetaData { + public $cachedFieldNames, $cachedSelectedFieldNames, $cachedFieldIds; + /** * read image information from database * and escape data with htmlspecialchars() if necessary @@ -38,25 +40,35 @@ * @param array array_src_infos array containing needed source file informations, same as LIN_SELECT_FIELDS * */ - function readInformations($array_src_infos, $full_filename, $flag_nr) + function readInformations($array_src_infos, $flag_nr) { - $query = $GLOBALS['linpha']->db->Execute("SELECT name " . - "FROM ".LIN_PREFIX."meta_fields " . - "WHERE flags = '".$flag_nr."' ORDER by id"); - - while($fields_data = $query->FetchRow(ADODB_FETCH_NUM)) + //$GLOBALS['linpha']->db->debug = true; + + /** + * cache field names if not already done + * readInformations() is executed for each thumbnail on a folder + * save a lot of time if do it only once! + */ + if( !isset($this->cachedSelectedFieldNames) OR !is_array($this->cachedSelectedFieldNames)) { - $name = LinMetaData::getNameOfField( $fields_data['0'], false ); + $query = $GLOBALS['linpha']->db->Execute("SELECT name " . + "FROM ".LIN_PREFIX."meta_fields " . + "WHERE flags = '".$flag_nr."' ORDER by id"); + + while($fields_data = $query->FetchRow(ADODB_FETCH_NUM)) + { + $this->cachedSelectedFieldNames[] = $fields_data['0']; + } + } + + foreach($this->cachedSelectedFieldNames AS $field_name) + { + $name = $this->getNameOfField( $field_name, false ); $value = ''; - $pos = strpos($fields_data['0'],'_'); - $first_part = substr($fields_data['0'],0,$pos); - $last_part = substr($fields_data['0'],$pos+1); - /** - * get field id - */ - $data = $GLOBALS['linpha']->db->GetRow("SELECT id " . - "FROM ".LIN_PREFIX."meta_fields WHERE name = '".$last_part."'"); + $pos = strpos($field_name,'_'); + $first_part = substr($field_name,0,$pos); + $last_part = substr($field_name,$pos+1); switch($first_part) { @@ -64,67 +76,86 @@ switch($last_part) { - case 'filename': - $value = $array_src_infos['name']; // is already escaped - break; - case 'imagesize': - $value = LinFilesys::niceFilesize( filesize( $full_filename ), 2 ); - break; - case 'dimension': - $value = $array_src_infos['width'].'x'.$array_src_infos['height']; - break; - case 'thumbnail': - $value = 'thumbnail'; - break; - case 'time_add': - $name = i18n("Date Added"); - $value = linStrftime($array_src_infos['time_add']); - break; - case 'time_mod': - $name = i18n("Date Modified"); - $value = linStrftime($array_src_infos['time_mod']); - break; - case 'time_exif': - $name = i18n("Date Taken"); - $value = linStrftime($array_src_infos['time_exif']); - break; - case 'stats_numbers': - $name = i18n("Photos"); - $value = $array_src_infos['stats_numbers']; - break; - case 'stats_views': - $name = i18n("Nr Of Views"); - $value = $array_src_infos['stats_views']; - break; - case 'stats_downloads': - $name = i18n("Nr Of Downloads"); - $value = $array_src_infos['stats_downloads']; - break; + case 'filename': + $value = $array_src_infos['name']; // is already escaped + break; + case 'imagesize': + $value = LinFilesys::niceFilesize( $array_src_infos['filesize'], 2 ); + break; + case 'dimension': + $value = $array_src_infos['width'].'x'.$array_src_infos['height']; + break; + case 'thumbnail': + $value = 'thumbnail'; + break; + case 'time_add': + $name = i18n("Date Added"); + $value = linStrftime($array_src_infos['time_add']); + break; + case 'time_mod': + $name = i18n("Date Modified"); + $value = linStrftime($array_src_infos['time_mod']); + break; + case 'time_exif': + $name = i18n("Date Taken"); + $value = linStrftime($array_src_infos['time_exif']); + break; + case 'stats_numbers': + $name = i18n("Photos"); + $value = $array_src_infos['stats_numbers']; + break; + case 'stats_views': + $name = i18n("Nr Of Views"); + $value = $array_src_infos['stats_views']; + break; + case 'stats_downloads': + $name = i18n("Nr Of Downloads"); + $value = $array_src_infos['stats_downloads']; + break; + + default: + /** + * description, category, persons, date (field entries which are stored in linpha_meta_data) + */ + /** + * get field id, and cache it + */ + if( !isset($this->cachedFieldIds[$last_part]) ) + { + $this->cachedFieldIds[$last_part] = $GLOBALS['linpha']->db->GetOne("SELECT id " . + "FROM ".LIN_PREFIX."meta_fields WHERE name = '".$last_part."'"); + } + + /** + * get data + */ + $meta_data = $GLOBALS['linpha']->db->GetOne("SELECT meta_data " . + "FROM ".LIN_PREFIX."meta_data ". + "WHERE field_id = '".$this->cachedFieldIds[$last_part]."' " . + "AND md5sum = '".$array_src_infos['md5sum']."'"); + + if( !empty($meta_data) ) { + $value = htmlspecialchars($meta_data,ENT_QUOTES); + } + break; + } // end switch($last_part) + break; // end case 'builtin' - default: - - /** - * description, category, persons, date (field entries which are stored in linpha_meta_data) - */ - $meta_data = $GLOBALS['linpha']->db->GetRow("SELECT meta_data " . - "FROM ".LIN_PREFIX."meta_data ". - "WHERE field_id = '".$data['id']."' " . - "AND md5sum = '".$array_src_infos['md5sum']."'"); - - if(isset($meta_data['meta_data'])) { - $value = htmlspecialchars($meta_data['meta_data'],ENT_QUOTES); - } - } case 'id': // own fields - $meta_data = $GLOBALS['linpha']->db->GetRow("SELECT meta_data " . + + /** + * get data + */ + $meta_data = $GLOBALS['linpha']->db->GetOne("SELECT meta_data " . "FROM ".LIN_PREFIX."meta_data " . - "WHERE field_id = '".$data['id']."' " . + "WHERE field_id = '".$last_part."' " . "AND md5sum = '".$array_src_infos['md5sum']."'"); - if(isset($meta_data['meta_data'])) { - $value = htmlspecialchars($meta_data['meta_data'],ENT_QUOTES); + if(isset($meta_data) && !empty($meta_data)) { + $value = htmlspecialchars($meta_data,ENT_QUOTES); } break; + case 'exif': if($GLOBALS['linpha']->sql->config->value['sys_image_exif']) { $exif_data = $GLOBALS['linpha']->db->GetRow("SELECT ".$last_part." " . @@ -149,7 +180,43 @@ if($value != '') { - $output_array[$fields_data['name']] = Array('name' => $name, 'value' => $value); + /** + * get category names if the data looks like ';5;4;44;' or at least ';2;' + */ + if( preg_match( '/^;[0-9;]*;$/', $value) ) + { + $array_catids = linExplodeAndSlice(';',$value); + $str_catnames = ''; + $isValidCategory = true; + for($i=1; list($key,$catid) = each($array_catids) ; $i++) + { + $str_catnames .= $GLOBALS['linpha']->db->GetOne("SELECT name FROM ".LIN_PREFIX."meta_category WHERE id = '".LinSql::linAddslashes($catid)."'"); + + /** + * was this a valid category id? + * maybe someone just added ;nice; as a description + * + * this will only check the first category id, but thats okay + */ + if(empty($str_catnames)) + { + $isValidCategory = false; + break; + } + + if( $i < count($array_catids) ) + { + $str_catnames .= ', '; + } + } + + if($isValidCategory) + { + $value = $str_catnames; + } + } + + $output_array[$field_name] = Array('name' => $name, 'value' => $value); } } @@ -869,8 +936,25 @@ break; case 'id': // own fields - $data2 = $GLOBALS['linpha']->db->GetRow("SELECT name FROM ".LIN_PREFIX."meta_fields WHERE id = '".$last_part."'"); - $value = i18n( ucfirst( $data2['name'] ) ); + + /** + * enable cache (only possible in object context) + * we wont have to read this for each thumbnail + */ + if(isset($this->cachedFieldNames[$last_part])) + { + $value = i18n( ucfirst( $this->cachedFieldNames[$last_part] ) ); + } + else + { + $data = $GLOBALS['linpha']->db->GetRow("SELECT name FROM ".LIN_PREFIX."meta_fields WHERE id = '".$last_part."'"); + $value = i18n( ucfirst( $data['name'] ) ); + + if(isset($this)) // cache entry, but only in object context + { + $this->cachedFieldNames[$last_part] = $data['name']; + } + } break; case 'exif': $value = LinMetaData::$Tags['exif'][$last_part]; This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site. |