database = new PDO('mysql:host=localhost;dbname=digikam4', '', '', array( PDO::ATTR_PERSISTENT => false)); } catch(PDOException $e) { echo $e->getMessage(); echo "

Database -- NOT -- loaded successfully .. "; die( "

Query Closed !!! $error"); } $this->database->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING); } /** * return the correct column content type for the given column name */ private function getColumnType( $colName ) { switch ( $colName ) { case ":limit": case ":offset": case ":album_id": case ":image_id": return PDO::PARAM_INT; break; default: return PDO::PARAM_STR; } } private function createAlbumImageFromRow( $row ) { return new AlbumImage($this, $row); } private function createTagFromRow( $row ) { return new Tag($this, $row); } /** * Execute the given sql, using key/value pairs in $vars to bind values to the sql */ private function executeQuery( $sql, $vars ) { $stmt = $this->executeSql($sql, $vars); return $stmt->fetchAll(); } public function executeSql( $sql, $vars ) { $stmt = $this->database->prepare($sql); if ( $vars != null ) { foreach( $vars as $key => $value ) { $colType = $this->getColumnType( $key ); if( $colType == PDO::PARAM_INT ) { $value = (int) $value; } $stmt->bindValue($key, $value, $colType); } } $stmt->execute(); return $stmt; } /** * Execute a query (with parameters) and return the first column in the first row * handy for things like getting a count or a specific element from a table */ private function getFirstColFromFirstResult( $sql, $vars ) { $stmt = $this->executeSql($sql, $vars); return $stmt->fetchColumn(); } /** * For a given path, count the number of sub-albums in that path */ public function isAlbumGroup( $groupPath ) { $tableName = "Albums"; $groupPath = str_replace( "'", "''", $groupPath ); $sql = "SELECT count(*) as album_count FROM $tableName a WHERE a.relativePath like '$groupPath/%' AND a.relativePath not like '$groupPath/%/%'"; return $this->getFirstColFromFirstResult( $sql, null ); } /** * For a given album id, return the relativePath */ public function getGroupPath( $groupAlbumId ) { if ( $groupAlbumId == "" ) { return ""; } $tableName = "Albums"; $sql = "SELECT relativePath FROM $tableName WHERE id = :album_id"; $vars = array(":album_id" => $groupAlbumId); return $this->getFirstColFromFirstResult( $sql, $vars ); } /** * if Exact is 1, this will return information for the groupAlbumId given * if Exact is 2, this will return information for ALL galleries * if Exact is 0 (default), this will return the albums which are * nested under the given groupAlbumId */ public function getAlbum( $groupAlbumId, $exact, $limit, $offset ) { $private = (isset($_COOKIE['private_cookie']) && $_COOKIE['private_cookie'] == 'private' ) ? '_private' : '' ; $tableName = "AlbumsView$private"; $vars = array(); $sql = "SELECT album_id, album_path, album_caption, collection, album_date, image_id, image_name, image_path, image_title, image_caption, creationDate, modificationDate, make, model, lens, aperture, focalLength, exposureTime, sensitivity, meteringMode, latitudeNumber, longitudeNumber, num_images FROM $tableName WHERE 1 = 1 "; if ( $exact == 1 ) { $vars[":album_id"] = $groupAlbumId; $sql .= " AND album_id = :album_id"; } elseif ( $exact == 2 ) { } else { $groupPath = $this->getGroupPath( $groupAlbumId ); $groupPath = str_replace( "'", "''", $groupPath ); $sql .= " AND album_path like '$groupPath/%' AND album_path not like '$groupPath/%/%'"; } $sql .= " ORDER BY album_date DESC, album_path\n"; if ( $limit != "" ) { $vars[":limit"] = $limit; $sql .= " LIMIT :limit"; } if ( $offset != "" ) { $vars[":offset"] = $offset; $sql .= " OFFSET :offset"; } if( $exact == 1 ) { return $this->createAlbumImageFromRow( $this->executeSql( $sql, $vars )->fetch() ); } else { return array_map( array( $this, 'createAlbumImageFromRow'), $this->executeQuery( $sql, $vars ) ); } } public function getImages( $groupAlbumId, $tagId, $limit, $offset ) { $private = (isset($_COOKIE['private_cookie']) && $_COOKIE['private_cookie'] == 'private' ) ? '_private' : '' ; $tableName = "ImagesView$private"; $tableNameTags = "ImageTagsView$private"; $vars = array(); $sql = "SELECT album_id, album_path as album_path, album_caption, collection, album_date, i.image_id, image_name, image_path, image_title, image_caption, creationDate, modificationDate, make, model, lens, aperture, focalLength, exposureTime, sensitivity, meteringMode, latitudeNumber, longitudeNumber, 0 as num_images FROM $tableName i"; if ( $tagId != "" ) { $vars[':tag_id'] = $tagId; $sql .= " INNER JOIN ( SELECT image_id from $tableNameTags WHERE tag_id = :tag_id ) t ON t.image_id = i.image_id"; } //filter by group if ( $groupAlbumId != "" ) { $vars[':album_id'] = $groupAlbumId; $sql .= " WHERE i.album_id = :album_id"; } $sql .= " ORDER BY album_date DESC, image_path, creationDate"; if ( $limit != "" ) { $x = array( ":limit" => $limit ); $vars = array_merge( $vars, $x); $sql .= " LIMIT :limit"; } if ( $offset != "" ) { $x = array( ":offset" => $offset ); $vars = array_merge( $vars, $x); $sql .= " OFFSET :offset"; } return array_map( array( $this, 'createAlbumImageFromRow'), $this->executeQuery( $sql, $vars ) ); } public function getImageById( $imageId) { $private = (isset($_COOKIE['private_cookie']) && $_COOKIE['private_cookie'] == 'private' ) ? '_private' : '' ; $tableName = "ImagesView$private"; $vars = array(); $sql = "SELECT album_id, album_path as album_path, album_caption, collection, album_date, image_id, image_name, image_path, image_title, image_caption, creationDate, modificationDate, make, model, lens, aperture, focalLength, exposureTime, sensitivity, meteringMode, latitudeNumber, longitudeNumber, 0 as num_images FROM $tableName"; $vars[':image_id'] = $imageId; $sql .= " WHERE image_id = :image_id"; return array_map( array( $this, 'createAlbumImageFromRow'), $this->executeQuery( $sql, $vars ) ); } /** * This returns multiple rows for an image, it will include all tags for the image */ public function getImageTags ( $albumId, $tagId, $imageId ) { $private = (isset($_COOKIE['private_cookie']) && $_COOKIE['private_cookie'] == 'private' ) ? '_private' : '' ; $tableName = "ImageTagsView$private"; $vars = array(); $sql = "SELECT DISTINCT tag_id, tag_name, count(*) as how_many FROM $tableName WHERE 1 = 1"; if( $imageId != "" ) { $sql .= "\n AND image_id = :image_id "; $vars[":image_id"] = $imageId ; } if( $albumId != "" ) { $sql .= "\n AND album_id = :album_id "; $vars[":album_id"] = $albumId ; } if( $tagId != "" ) { $sql .= "\n AND tag_id = :tag_id "; $vars[":tag_id"] = $tagId ; } $sql .= "\n GROUP BY tag_id, tag_name "; return array_map( array( $this, 'createTagFromRow'), $this->executeQuery( $sql, $vars ) ); } } ?>