Logo Search packages:      
Sourcecode: f-spot version File versions  Download package

PhotoStore.cs

/*
 * PhotoStore.cs
 *
 * Author(s):
 *    Ettore Perazzoli <ettore@perazzoli.org>
 *    Larry Ewing <lewing@gnome.org>
 *    Stephane Delcroix <stephane@delcroix.org>
 * 
 * This is free software. See COPYING for details.
 */

using Gdk;
using Gtk;

using Mono.Data.SqliteClient;
using Mono.Unix;

using System.Collections;
using System.Collections.Generic;
using System.IO;
using System.Text;
using System;

using FSpot;
using FSpot.Query;
using FSpot.Utils;
using FSpot.Platform;

using Banshee.Database;


public class PhotoStore : DbStore<Photo> {
      public int TotalPhotos {
            get {
                  SqliteDataReader reader = Database.Query("SELECT COUNT(*) AS photo_count FROM photos");
                  reader.Read ();
                  int total = Convert.ToInt32 (reader ["photo_count"]);
                  reader.Close ();
                  return total;
            }
      }

      // FIXME this is a hack.  Since we don't have Gnome.ThumbnailFactory.SaveThumbnail() in
      // GTK#, and generate them by ourselves directly with Gdk.Pixbuf, we have to make sure here
      // that the "large" thumbnail directory exists.
      private static void EnsureThumbnailDirectory ()
      {
            string large_thumbnail_file_name_template = Gnome.Thumbnail.PathForUri ("file:///boo", Gnome.ThumbnailSize.Large);
            string large_thumbnail_directory_path = System.IO.Path.GetDirectoryName (large_thumbnail_file_name_template);

            if (! System.IO.File.Exists (large_thumbnail_directory_path))
                  System.IO.Directory.CreateDirectory (large_thumbnail_directory_path);
      }

      //
      // Generates the thumbnail, returns the Pixbuf, and also stores it as a side effect
      //

      private static Pixbuf GenerateThumbnail (System.Uri uri)
      {
            using (FSpot.ImageFile img = FSpot.ImageFile.Create (uri)) {
                  return GenerateThumbnail (uri, img);
            }
      }

      private static Pixbuf GenerateThumbnail (System.Uri uri, ImageFile img)
      {
            Pixbuf thumbnail = null;

            if (img is FSpot.IThumbnailContainer) {
                  try {
                        thumbnail = ((FSpot.IThumbnailContainer)img).GetEmbeddedThumbnail ();
                  } catch (Exception e) {
                        Log.Debug ("Exception while loading embedded thumbail {0}", e.ToString ());
                  }
            }

            // Save embedded thumbnails in a silightly invalid way so that we know to regnerate them later.
            if (thumbnail != null)
                  //FIXME with gio, set it to uri time minus a few sec
                  ThumbnailFactory.SaveThumbnail (thumbnail, uri, new DateTime (1980, 1, 1));
             else
                  thumbnail = FSpot.ThumbnailGenerator.Create (uri);
            
            return thumbnail;
      }

      // Constructor

      public PhotoStore (QueuedSqliteDatabase database, bool is_new)
            : base (database, false)
      {
            EnsureThumbnailDirectory ();

            if (! is_new)
                  return;
            
            Database.ExecuteNonQuery ( 
                  "CREATE TABLE photos (\n" +
                  "     id                INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, \n" +
                  "     time              INTEGER NOT NULL, \n" +
                  "     base_uri          STRING NOT NULL, \n" +
                " filename          STRING NOT NULL, \n" +
                  "     description       TEXT NOT NULL, \n" +
                  "     roll_id                 INTEGER NOT NULL, \n" +
                  "     default_version_id      INTEGER NOT NULL, \n" +
                  "     rating                  INTEGER NULL, \n" +
                  "     md5_sum                 TEXT NULL\n" +
                  ")");

            Database.ExecuteNonQuery (
                  "CREATE TABLE photo_tags (\n" +
                  "     photo_id    INTEGER, \n" +
                  "       tag_id          INTEGER, \n" +
                  "       UNIQUE (photo_id, tag_id)\n" +
                  ")");

            Database.ExecuteNonQuery (
                  "CREATE TABLE photo_versions (\n"+
                  "     photo_id    INTEGER, \n" +
                  "     version_id  INTEGER, \n" +
                  "     name        STRING, \n" +
                  "     base_uri          STRING NOT NULL, \n" +
                " filename          STRING NOT NULL, \n" +
                  "     md5_sum           TEXT NULL, \n" +
                  "     protected   BOOLEAN, \n" +
                  "     UNIQUE (photo_id, version_id)\n" +
                  ")");

            Database.ExecuteNonQuery ("CREATE INDEX idx_photo_versions_id ON photo_versions(photo_id)");
            Database.ExecuteNonQuery ("CREATE INDEX idx_photos_roll_id ON photos(roll_id)");
      }

      public Photo CheckForDuplicate (System.Uri uri) {
            // Here we can go wild in comparing photos,
            // for now we check on uri and md5
            Photo found = GetByUri (uri);
            
            if (found != null)
                  return found;

            string md5 = Photo.GenerateMD5 (uri);                 
            Gnome.Vfs.FileInfo info = new Gnome.Vfs.FileInfo (uri.ToString (), Gnome.Vfs.FileInfoOptions.GetMimeType);

            Photo[] md5_matches = GetByMD5 (md5);

            foreach (Photo match in md5_matches)
            {
                  Gnome.Vfs.FileInfo match_info = new Gnome.Vfs.FileInfo (match.DefaultVersionUri.ToString (), Gnome.Vfs.FileInfoOptions.GetMimeType);

                  // same mimetype?
                  if (info.MimeType != match_info.MimeType)
                        continue;

                  // other comparisons?

                  // TODO? load pixbuf and compare sizes?   

                  return match;
            }

            return null;
      }

      public Photo Create (System.Uri uri, uint roll_id, out Pixbuf thumbnail)
      {
            return Create (uri, uri, roll_id, out thumbnail);
      }

      public Photo Create (System.Uri new_uri, System.Uri orig_uri, uint roll_id, out Pixbuf thumbnail)
      {
            Photo photo;
            using (FSpot.ImageFile img = FSpot.ImageFile.Create (orig_uri)) {
                  long unix_time = DbUtils.UnixTimeFromDateTime (img.Date);
                  string description = img.Description != null  ? img.Description.Split ('\0') [0] : String.Empty;
                  string md5_sum = Photo.GenerateMD5 (new_uri);

                  uint id = (uint) Database.Execute (
                        new DbCommand (
                              "INSERT INTO photos (time, base_uri, filename, description, roll_id, default_version_id, rating, md5_sum) " +
                              "VALUES (:time, :base_uri, :filename, :description, :roll_id, :default_version_id, :rating, :md5_sum)",
                              "time", unix_time,
                              "base_uri", new_uri.GetDirectoryUri ().ToString (),
                              "filename", new_uri.GetFilename (),
                              "description", description,
                              "roll_id", roll_id,
                              "default_version_id", Photo.OriginalVersionId,
                              "rating", "0",
                              "md5_sum", (md5_sum != String.Empty ? md5_sum : null)
                        )
                  );
      
                  photo = new Photo (id, unix_time, new_uri, md5_sum);
                  AddToCache (photo);
                  photo.Loaded = true;
      
                  thumbnail = GenerateThumbnail (new_uri, img);         
                  EmitAdded (photo);
            }
            return photo;
      }


      private void GetVersions (Photo photo)
      {
            SqliteDataReader reader = Database.Query(
                  new DbCommand("SELECT version_id, name, base_uri, filename, md5_sum, protected " + 
                              "FROM photo_versions " + 
                              "WHERE photo_id = :id", 
                              "id", photo.Id
                  )
            );

            while (reader.Read ()) {
                  uint version_id = Convert.ToUInt32 (reader ["version_id"]);
                  string name = reader["name"].ToString ();
                  System.Uri uri = new Uri (new Uri (reader ["base_uri"].ToString ()), reader ["filename"].ToString ());
                  string md5_sum = reader["md5_sum"] != null ? reader ["md5_sum"].ToString () : null;
                  bool is_protected = Convert.ToBoolean (reader["protected"]);
                                                
                  photo.AddVersionUnsafely (version_id, uri, md5_sum, name, is_protected);
            }
            reader.Close();
      }

      private void GetTags (Photo photo)
      {
            SqliteDataReader reader = Database.Query(new DbCommand("SELECT tag_id FROM photo_tags WHERE photo_id = :id", "id", photo.Id));

            while (reader.Read ()) {
                  uint tag_id = Convert.ToUInt32 (reader ["tag_id"]);
                  Tag tag = Core.Database.Tags.Get (tag_id) as Tag;
                  photo.AddTagUnsafely (tag);
            }
            reader.Close();
      }           
      
      private void GetAllVersions  () {
            SqliteDataReader reader = Database.Query("SELECT photo_id, version_id, name, base_uri, filename, md5_sum, protected FROM photo_versions");
            
            while (reader.Read ()) {
                  uint id = Convert.ToUInt32 (reader ["photo_id"]);
                  Photo photo = LookupInCache (id);
                        
                  if (photo == null) {
                        //Console.WriteLine ("Photo {0} not found", id);
                        continue;
                  }
                        
                  if (photo.Loaded) {
                        //Console.WriteLine ("Photo {0} already Loaded", photo);
                        continue;
                  }

                  if (reader ["version_id"] != null) {
                        uint version_id = Convert.ToUInt32 (reader ["version_id"]);
                        string name = reader["name"].ToString ();
                        System.Uri uri = new Uri (new Uri (reader ["base_uri"].ToString ()), reader ["filename"].ToString ());
                        string md5_sum = reader["md5_sum"] != null ? reader ["md5_sum"].ToString () : null;
                        bool is_protected = Convert.ToBoolean (reader["protected"]);
                        
                        photo.AddVersionUnsafely (version_id, uri, md5_sum, name, is_protected);
                  }

                  /*
                  string directory_path = null;
                  if (reader [3] != null)
                        directory_path = reader [3].ToString ();
                  System.Console.WriteLine ("directory_path = {0}", directory_path);
                  */
            }
            reader.Close();
      }

      private void GetAllTags () {
            SqliteDataReader reader = Database.Query("SELECT photo_id, tag_id FROM photo_tags");

            while (reader.Read ()) {
                  uint id = Convert.ToUInt32 (reader ["photo_id"]);
                  Photo photo = LookupInCache (id);
                        
                  if (photo == null) {
                        //Console.WriteLine ("Photo {0} not found", id);
                        continue;
                  }
                        
                  if (photo.Loaded) {
                        //Console.WriteLine ("Photo {0} already Loaded", photo.Id);
                        continue;
                  }

                    if (reader [1] != null) {
                        uint tag_id = Convert.ToUInt32 (reader ["tag_id"]);
                        Tag tag = Core.Database.Tags.Get (tag_id) as Tag;
                        photo.AddTagUnsafely (tag);
                  }
            }
            reader.Close();
      }

      public override Photo Get (uint id)
      {
            Photo photo = LookupInCache (id);
            if (photo != null)
                  return photo;
            
            SqliteDataReader reader = Database.Query(
                  new DbCommand("SELECT time, base_uri, filename, description, roll_id, default_version_id, rating, md5_sum " + 
                              "FROM photos " + 
                              "WHERE id = :id", "id", id
                             )
            );

            if (reader.Read ()) {
                  photo = new Photo (id,
                        Convert.ToInt64 (reader ["time"]),
                      new Uri (new Uri (reader ["base_uri"].ToString ()), reader ["filename"].ToString ()),
                        reader["md5_sum"] != null ? reader["md5_sum"].ToString () : null
                  );

                  photo.Description = reader["description"].ToString ();
                  photo.RollId = Convert.ToUInt32 (reader["roll_id"]);
                  photo.DefaultVersionId = Convert.ToUInt32 (reader["default_version_id"]);
                  photo.Rating = Convert.ToUInt32 (reader ["rating"]);
                  AddToCache (photo);
            }
            reader.Close();

            if (photo == null)
                  return null;
            
            GetTags (photo);
            GetVersions (photo);

            return photo;
      }

      public Photo GetByUri (System.Uri uri)
      {
            Photo photo = null;

            uint timer = Log.DebugTimerStart ();

            SqliteDataReader reader =
                  Database.Query (new DbCommand ("SELECT id, time, description, roll_id, default_version_id, rating, photos.md5_sum AS md5_sum " +
                                                 " FROM photos " +
                                                 " LEFT JOIN photo_versions AS pv ON photos.id = pv.photo_id" +
                                                 " WHERE (photos.base_uri = :base_uri AND photos.filename = :filename)" +
                                                 " OR (pv.base_uri = :base_uri AND pv.filename = :filename)",
                                                 "base_uri", uri.GetDirectoryUri ().ToString (),
                                                 "filename", uri.GetFilename ()));

            if (reader.Read ()) {
                  photo = new Photo (Convert.ToUInt32 (reader ["id"]),
                                 Convert.ToInt64 (reader ["time"]),
                                 uri,
                                 reader["md5_sum"] != null ? reader["md5_sum"].ToString () : null);

                  photo.Description = reader["description"].ToString ();
                  photo.RollId = Convert.ToUInt32 (reader["roll_id"]);
                  photo.DefaultVersionId = Convert.ToUInt32 (reader["default_version_id"]);
                  photo.Rating = Convert.ToUInt32 (reader ["rating"]);
            }
            
            reader.Close();
            Log.DebugTimerPrint (timer, "GetByUri query took {0}");

            if (photo == null)
                  return null;

            Photo cached = LookupInCache (photo.Id);

            if (cached != null)
                  return cached;

            AddToCache (photo);
      
            GetTags (photo);
            GetVersions (photo);

            return photo;
      }

      public Photo[] GetByMD5 (string md5_sum)
      {
            List<Photo> photos = new List<Photo> ();
            
            SqliteDataReader reader = Database.Query (
                  new DbCommand ("SELECT DISTINCT " + 
                               "id, time, photos.base_uri AS base_uri, photos.filename AS filename, description, roll_id, default_version_id, rating " + 
                               "FROM photos " + 
                               "LEFT JOIN photo_versions " + 
                               "ON   photos.id = photo_versions.photo_id " +
                               "WHERE photos.md5_sum = :md5_sum " +
                               "OR photo_versions.md5_sum = :md5_sum", 
                               "md5_sum", md5_sum
                              )
            );

            while (reader.Read ()) {
                  Photo photo =
                        new Photo (Convert.ToUInt32 (reader ["id"]),
                                   Convert.ToInt64 (reader ["time"]),
                                   new Uri (new Uri (reader ["base_uri"].ToString ()), reader ["filename"].ToString ()),
                                   md5_sum);

                  photo.Description = reader["description"].ToString ();
                  photo.RollId = Convert.ToUInt32 (reader["roll_id"]);
                  photo.DefaultVersionId = Convert.ToUInt32 (reader["default_version_id"]);
                  photo.Rating = Convert.ToUInt32 (reader ["rating"]);
                  photo.MD5Sum = md5_sum;

                  // get cached if possible
                  Photo cached = LookupInCache (photo.Id);

                  if (cached != null)
                  {
                        photos.Add (cached);
                        continue;
                  }

                  // Add to cache and fully load if not found in cache
                  AddToCache (photo);
      
                  GetTags (photo);
                  GetVersions (photo);

                  // add to collection
                  photos.Add (photo);
            }

              reader.Close();

            return photos.ToArray ();
      }

      public void Remove (Tag []tags)
      {
            Photo [] photos = Query (tags, String.Empty, null, null);   

            foreach (Photo photo in photos)
                  photo.RemoveCategory (tags);
            Commit (photos);

            foreach (Tag tag in tags)
                  Core.Database.Tags.Remove (tag);
            
      }

      public void Remove (Photo []items)
      {
            EmitRemoved (items);

            ArrayList query_builder = new ArrayList (items.Length);
            for (int i = 0; i < items.Length; i++) {
                  query_builder.Add (String.Format ("{0}", items[i].Id));
                  RemoveFromCache (items[i]);
            }

            String id_list = String.Join ("','", ((string []) query_builder.ToArray (typeof (string))));
            Database.ExecuteNonQuery (String.Format ("DELETE FROM photos WHERE id IN ('{0}')", id_list));
            Database.ExecuteNonQuery (String.Format ("DELETE FROM photo_tags WHERE photo_id IN ('{0}')", id_list));
            Database.ExecuteNonQuery (String.Format ("DELETE FROM photo_versions WHERE photo_id IN ('{0}')", id_list));

      }

      public override void Remove (Photo item)
      {
            Remove (new Photo [] { (Photo)item });
      }

      public override void Commit (Photo item)
      {
            Commit (new Photo [] {item});
      }

      public void Commit (Photo [] items)
      {
            uint timer = Log.DebugTimerStart ();
            // Only use a transaction for multiple saves. Avoids recursive transactions.
            bool use_transactions = !Database.InTransaction && items.Length > 1;

            if (use_transactions)
                  Database.BeginTransaction ();

            PhotosChanges changes = new PhotosChanges ();
            foreach (DbItem item in items)
                  changes |= Update ((Photo)item);

            if (use_transactions)
                  Database.CommitTransaction ();

            EmitChanged (items, new PhotoEventArgs (items, changes));
            Log.DebugTimerPrint (timer, "Commit took {0}");
      }

      private PhotoChanges Update (Photo photo) {
            PhotoChanges changes = photo.Changes;
            // Update photo.
            if (changes.DescriptionChanged || changes.DefaultVersionIdChanged || changes.TimeChanged || changes.UriChanged || changes.RatingChanged || changes.MD5SumChanged )
                  Database.ExecuteNonQuery (
                        new DbCommand (
                              "UPDATE photos " + 
                              "SET description = :description, " + 
                              "    default_version_id = :default_version_id, " + 
                              "    time = :time, " + 
                              "    base_uri = :base_uri, " +
                              "    filename = :filename, " +       
                              "    rating = :rating, " +
                              "    md5_sum = :md5_sum " +
                              "WHERE id = :id ",
                              "description", photo.Description,
                              "default_version_id", photo.DefaultVersionId,
                              "time", DbUtils.UnixTimeFromDateTime (photo.Time),
                              "base_uri", photo.VersionUri (Photo.OriginalVersionId).GetDirectoryUri ().ToString (),
                              "filename", photo.VersionUri (Photo.OriginalVersionId).GetFilename (),
                              "rating", String.Format ("{0}", photo.Rating),
                              "md5_sum", (photo.MD5Sum != String.Empty ? photo.MD5Sum : null),
                              "id", photo.Id
                        )
                  );

            // Update tags.
            if (changes.TagsRemoved != null)
                  foreach (Tag tag in changes.TagsRemoved)
                        Database.ExecuteNonQuery (new DbCommand (
                              "DELETE FROM photo_tags WHERE photo_id = :photo_id AND tag_id = :tag_id",
                              "photo_id", photo.Id,
                              "tag_id", tag.Id));

            if (changes.TagsAdded != null)
                  foreach (Tag tag in changes.TagsAdded)
                        Database.ExecuteNonQuery (new DbCommand (
                              "INSERT OR IGNORE INTO photo_tags (photo_id, tag_id) " +
                              "VALUES (:photo_id, :tag_id)",
                              "photo_id", photo.Id,
                              "tag_id", tag.Id));

            // Update versions.
            if (changes.VersionsRemoved != null)
                  foreach (uint version_id in changes.VersionsRemoved)
                        Database.ExecuteNonQuery (new DbCommand (
                              "DELETE FROM photo_versions WHERE photo_id = :photo_id AND version_id = :version_id",
                              "photo_id", photo.Id,
                              "version_id", version_id));

            if (changes.VersionsAdded != null)
                  foreach (uint version_id in changes.VersionsAdded) {
                        PhotoVersion version = photo.GetVersion (version_id) as PhotoVersion;
                        Database.ExecuteNonQuery (new DbCommand (
                              "INSERT OR IGNORE INTO photo_versions (photo_id, version_id, name, base_uri, filename, protected, md5_sum) " +
                              "VALUES (:photo_id, :version_id, :name, :base_uri, :filename, :is_protected, :md5_sum)",
                              "photo_id", photo.Id,
                              "version_id", version_id,
                              "name", version.Name,
                          "base_uri", version.Uri.GetDirectoryUri ().ToString (),
                              "filename", version.Uri.GetFilename (),
                              "is_protected", version.IsProtected,
                              "md5_sum", (version.MD5Sum != String.Empty ? version.MD5Sum : null)));
                  }
            if (changes.VersionsModified != null)
                  foreach (uint version_id in changes.VersionsModified) {
                        PhotoVersion version = photo.GetVersion (version_id) as PhotoVersion;
                        Database.ExecuteNonQuery (new DbCommand (
                              "UPDATE photo_versions SET name = :name, " +
                              "base_uri = :base_uri, filename = :filename, protected = :protected, md5_sum = :md5_sum " +
                              "WHERE photo_id = :photo_id AND version_id = :version_id",
                              "name", version.Name,
                              "base_uri", version.Uri.GetDirectoryUri ().ToString (),
                              "filename", version.Uri.GetFilename (),
                              "protected", version.IsProtected,
                              "photo_id", photo.Id,
                              "md5_sum", (version.MD5Sum != String.Empty ? version.MD5Sum : null),
                              "version_id", version_id));
                  }
            photo.Changes = null;
            return changes;
      }

      public void UpdateMD5Sum (Photo photo) {
            string md5_sum = Photo.GenerateMD5 (photo.VersionUri (Photo.OriginalVersionId)); 
            photo.MD5Sum = md5_sum;

            Database.ExecuteNonQuery (
                  new DbCommand (
                        "UPDATE photos " +
                        "SET    md5_sum = :md5_sum " +
                        "WHERE  ID = :id",
                        "md5_sum", (md5_sum != String.Empty ? md5_sum : null),
                        "id", photo.Id
                  )
            );

            foreach (uint version_id in photo.VersionIds) {
                  if (version_id == Photo.OriginalVersionId)
                        continue;

                  PhotoVersion version = photo.GetVersion (version_id) as PhotoVersion;

                  string version_md5_sum = Photo.GenerateMD5 (version.Uri);

                  if (version.MD5Sum == version_md5_sum)
                        continue;

                  version.MD5Sum = version_md5_sum; 
                  photo.Changes.ChangeVersion (version_id);
            }

            Commit (photo);
      }
      
      // Dbus
      public event EventHandler<DbItemEventArgs<Photo>> ItemsAddedOverDBus;
      public event EventHandler<DbItemEventArgs<Photo>> ItemsRemovedOverDBus;

      public Photo CreateOverDBus (string new_path, string orig_path, uint roll_id, out Gdk.Pixbuf pixbuf)  {
            Photo photo = Create (UriUtils.PathToFileUri (new_path), UriUtils.PathToFileUri (orig_path), roll_id, out pixbuf);
            EmitAddedOverDBus (photo);

            return photo;
      }

      public void RemoveOverDBus (Photo photo) {
            Remove (photo);
            EmitRemovedOverDBus (photo);
      }


      protected void EmitAddedOverDBus (Photo photo) {
            EmitAddedOverDBus (new Photo [] { photo });
      }

      protected void EmitAddedOverDBus (Photo [] photos) {
            if (ItemsAddedOverDBus != null)
                  ItemsAddedOverDBus (this, new DbItemEventArgs<Photo> (photos));
      }

      protected void EmitRemovedOverDBus (Photo photo) {
            EmitRemovedOverDBus (new Photo [] { photo });
      }

      protected void EmitRemovedOverDBus (Photo [] photos) {
            if (ItemsRemovedOverDBus != null)
                  ItemsRemovedOverDBus (this, new DbItemEventArgs<Photo> (photos)); 
      }

      public int Count (string table_name, params IQueryCondition [] conditions)
      {
            StringBuilder query_builder = new StringBuilder ("SELECT COUNT(*) AS count FROM " + table_name + " ");
            bool where_added = false;
            foreach (IQueryCondition condition in conditions) {
                  if (condition == null)
                        continue;
                  if (condition is IOrderCondition)
                        continue;
                  query_builder.Append (where_added ? " AND " : " WHERE ");
                  query_builder.Append (condition.SqlClause ());
                  where_added = true;
            }

            SqliteDataReader reader = Database.Query (query_builder.ToString());
            reader.Read ();
            int count = Convert.ToInt32 (reader ["count"]);
            reader.Close();
            return count;
      }

      public int [] IndicesOf (string table_name, uint [] items)
      {
            StringBuilder query_builder = new StringBuilder ("SELECT ROWID AS row_id FROM ");
            query_builder.Append (table_name);
            query_builder.Append (" WHERE id IN (");
            for (int i = 0; i < items.Length; i++) {
                  query_builder.Append (items [i]);
                  query_builder.Append ((i != items.Length - 1) ? ", " : ")" );
            }
            return IndicesOf (query_builder.ToString ());
      }

      public int IndexOf (string table_name, Photo photo)
      {
            string query = String.Format ("SELECT ROWID AS row_id FROM {0} WHERE id = {1}", table_name, photo.Id);
            return IndexOf (query);
      }

      public int IndexOf (string table_name, DateTime time, bool asc)
      {
            string query = String.Format ("SELECT ROWID AS row_id FROM {0} WHERE time {2} {1} ORDER BY time {3} LIMIT 1",
                        table_name,
                        DbUtils.UnixTimeFromDateTime (time),
                        asc ? ">=" : "<=",
                        asc ? "ASC" : "DESC");
            return IndexOf (query);
      }

      private int IndexOf (string query)
      {
            uint timer = Log.DebugTimerStart ();
            SqliteDataReader reader = Database.Query (query);
            int index = - 1;
            if (reader.Read ())
                  index = Convert.ToInt32 (reader ["row_id"]);
            reader.Close();
            Log.DebugTimerPrint (timer, "IndexOf took {0} : " + query);
            return index - 1; //ROWID starts counting at 1
      }

      int [] IndicesOf (string query)
      {
            uint timer = Log.DebugTimerStart ();
            List<int> list = new List<int> ();
            SqliteDataReader reader = Database.Query (query);
            while (reader.Read ())
                  list.Add (Convert.ToInt32 (reader ["row_id"]) - 1);
            reader.Close ();
            Log.DebugTimerPrint (timer, "IndicesOf took {0} : " + query);
            return list.ToArray ();
      }

      public Dictionary<int,int[]> PhotosPerMonth (params IQueryCondition [] conditions)
      {
            uint timer = Log.DebugTimerStart ();
            Dictionary<int, int[]> val = new Dictionary<int, int[]> ();

            //Sqlite is way more efficient querying to a temp then grouping than grouping at once
            Database.ExecuteNonQuery ("DROP TABLE IF EXISTS population");
            StringBuilder query_builder = new StringBuilder ("CREATE TEMPORARY TABLE population AS SELECT strftime('%Y%m', datetime(time, 'unixepoch')) AS month FROM photos");
            bool where_added = false;
            foreach (IQueryCondition condition in conditions) {
                  if (condition == null)
                        continue;
                  if (condition is IOrderCondition)
                        continue;
                  query_builder.Append (where_added ? " AND " : " WHERE ");
                  query_builder.Append (condition.SqlClause ());
                  where_added = true;
            }
            Database.ExecuteNonQuery (query_builder.ToString ());

            int minyear = Int32.MaxValue;
            int maxyear = Int32.MinValue;

            SqliteDataReader reader = Database.Query ("SELECT COUNT (*) as count, month from population GROUP BY month");
            while (reader.Read ()) {
                  string yyyymm = reader ["month"].ToString ();
                  int count = Convert.ToInt32 (reader ["count"]);
                  int year = Convert.ToInt32 (yyyymm.Substring (0,4));
                  maxyear = Math.Max (year, maxyear);
                  minyear = Math.Min (year, minyear);
                  int month = Convert.ToInt32 (yyyymm.Substring (4));
                  if (!val.ContainsKey (year))
                        val.Add (year, new int[12]);
                  val[year][month-1] = count;
            }
            reader.Close ();

            //Fill the blank
            for (int i = minyear; i <= maxyear; i++)
                  if (!val.ContainsKey (i))
                        val.Add (i, new int[12]);

            Log.DebugTimerPrint (timer, "PhotosPerMonth took {0}");
            return val;
      }

      // Queries.
      [Obsolete ("drop this, use IQueryCondition correctly instead")]
      public Photo [] Query (Tag [] tags) {
            return Query (tags, null, null, null, null);
      }
      
      private string BuildQuery (params IQueryCondition [] conditions)
      {
            StringBuilder query_builder = new StringBuilder ("SELECT * FROM photos ");

            bool where_added = false;
            bool hidden_contained = false;
            foreach (IQueryCondition condition in conditions) {
                  
                  if (condition == null)
                        continue;
                  
                  if (condition is HiddenTag)
                        hidden_contained = true;
                  
                  if (condition is IOrderCondition)
                        continue;
                  
                  string sql_clause = condition.SqlClause ();
                  
                  if (sql_clause == null || sql_clause.Trim () == String.Empty)
                        continue;
                  query_builder.Append (where_added ? " AND " : " WHERE ");
                  query_builder.Append (sql_clause);
                  where_added = true;
            }
            
            /* if a HiddenTag condition is not explicitly given, we add one */
            if ( ! hidden_contained) {
                  string sql_clause = HiddenTag.HideHiddenTag.SqlClause ();
                  
                  if (sql_clause != null && sql_clause.Trim () != String.Empty) {
                        query_builder.Append (where_added ? " AND " : " WHERE ");
                        query_builder.Append (sql_clause);
                  }
            }

            bool order_added = false;
            foreach (IQueryCondition condition in conditions) {
                  if (condition == null)
                        continue;
                  
                  if (!(condition is IOrderCondition))
                        continue;
                  
                  string sql_clause = condition.SqlClause ();
                  
                  if (sql_clause == null || sql_clause.Trim () == String.Empty)
                        continue;
                  query_builder.Append (order_added ? " , " : "ORDER BY ");
                  query_builder.Append (sql_clause);
                  order_added = true;
            }
            
            return query_builder.ToString ();
      }

      public Photo [] Query (params IQueryCondition [] conditions)
      {
            return Query (BuildQuery (conditions));
      }

      public void QueryToTemp (string temp_table, params IQueryCondition [] conditions)
      {
            QueryToTemp (temp_table, BuildQuery (conditions));
      }

      public void QueryToTemp(string temp_table, string query)
      {
            uint timer = Log.DebugTimerStart ();
            Log.Debug ("Query Started : {0}", query);
            Database.BeginTransaction ();
            Database.ExecuteNonQuery (String.Format ("DROP TABLE IF EXISTS {0}", temp_table));
            //Database.ExecuteNonQuery (String.Format ("CREATE TEMPORARY TABLE {0} AS {1}", temp_table, query));
            Database.Query (String.Format ("CREATE TEMPORARY TABLE {0} AS {1}", temp_table, query)).Close ();
            Database.CommitTransaction ();
            Log.DebugTimerPrint (timer, "QueryToTemp took {0} : " + query);
      }

      public Photo [] QueryFromTemp (string temp_table)
      {
            return QueryFromTemp (temp_table, 0, -1);
      }

      public Photo [] QueryFromTemp (string temp_table, int offset, int limit)
      {
            return Query (String.Format ("SELECT * FROM {0} LIMIT {1} OFFSET {2}", temp_table, limit, offset));
      }

      public Photo [] Query (string query)
      {
            return Query (new DbCommand (query));
      }

      private Photo [] Query (DbCommand query)
      {
            uint timer = Log.DebugTimerStart ();
            SqliteDataReader reader = Database.Query(query);

            List<Photo> new_photos = new List<Photo> ();
            List<Photo> query_result = new List<Photo> ();
            while (reader.Read ()) {
                  uint id = Convert.ToUInt32 (reader ["id"]);
                  Photo photo = LookupInCache (id);

                  if (photo == null) {
                        photo =
                              new Photo (id,
                                         Convert.ToInt64 (reader ["time"]),
                                         new Uri (new Uri (reader ["base_uri"].ToString ()), reader ["filename"].ToString ()),
                                         reader["md5_sum"] != null ? reader ["md5_sum"].ToString () : null
                        );
                        photo.Description = reader["description"].ToString ();
                        photo.RollId = Convert.ToUInt32 (reader["roll_id"]);
                        photo.DefaultVersionId = Convert.ToUInt32 (reader["default_version_id"]);
                        photo.Rating = Convert.ToUInt32 (reader ["rating"]);
                        new_photos.Add (photo);
                  }

                  query_result.Add (photo);
            }
            reader.Close();

            bool need_load = false;
            foreach (Photo photo in new_photos) {
                  AddToCache (photo);
                  need_load |= !photo.Loaded;
            }
            
            if (need_load) {
                  GetAllTags ();
                  GetAllVersions ();
                  foreach (Photo photo in new_photos)
                        photo.Loaded = true;
            } else {
                  //Console.WriteLine ("Skipped Loading Data");
            }
            foreach (Photo photo in new_photos)
                  photo.Changes = null;

            Log.DebugTimerPrint (timer, "Query took {0} : " + query.CommandText);
            return query_result.ToArray ();
      }

      public Photo [] Query (System.Uri uri)
      {
            string filename = uri.GetFilename ();
            
            /* query by file */
            if ( ! String.IsNullOrEmpty (filename)) {
                  return Query (new DbCommand (
                  "SELECT id, "                 +
                        "time, "                +
                        "base_uri, "            +
                        "filename, "            +
                        "description, "         +
                        "roll_id, "       +
                        "default_version_id, "  +
                        "rating, "        +
                        "md5_sum "        +
                  "FROM photos "                      +
                  "WHERE base_uri LIKE :base_uri "          +
                  "AND filename LIKE :filename",
                  "base_uri", uri.GetDirectoryUri ().ToString (),
                  "filename", filename));
            }
            
            /* query by directory */
            return Query (new DbCommand (
                  "SELECT id, "                 +
                        "time, "                +
                        "base_uri, "            +
                        "filename, "            +
                        "description, "         +
                        "roll_id, "       +
                        "default_version_id, "  +
                        "rating, "        +
                        "md5_sum "        +
                  "FROM photos "                      +
                  "WHERE base_uri LIKE :base_uri "          +
                  "AND base_uri NOT LIKE :base_uri_",
                  "base_uri", uri.ToString () + "%",
                  "base_uri_", uri.ToString () + "/%/%"));
      }

      [Obsolete ("drop this, use IQueryCondition correctly instead")]
      public Photo [] Query (Tag [] tags, string extra_condition, DateRange range, RollSet importidrange)
      {
            return Query (FSpot.OrTerm.FromTags(tags), extra_condition, range, importidrange, null);
      }

      [Obsolete ("drop this, use IQueryCondition correctly instead")]
      public Photo [] Query (Tag [] tags, string extra_condition, DateRange range, RollSet importidrange, RatingRange ratingrange)
      {
            return Query (FSpot.OrTerm.FromTags(tags), extra_condition, range, importidrange, ratingrange);
      }

      [Obsolete ("drop this, use IQueryCondition correctly instead")]
      public Photo [] Query (Term searchexpression, string extra_condition, DateRange range, RollSet importidrange, RatingRange ratingrange)
      {
            bool hide = (extra_condition == null);

            // The SQL query that we want to construct is:
            //
            // SELECT photos.id
            //        photos.time
            //        photos.uri,
            //        photos.description,
            //      photos.roll_id,
            //        photos.default_version_id
            //        photos.rating
            //                  FROM photos, photo_tags
            //              WHERE photos.time >= time1 AND photos.time <= time2
            //                      AND photos.rating >= rat1 AND photos.rating <= rat2
            //                      AND photos.id NOT IN (select photo_id FROM photo_tags WHERE tag_id = HIDDEN)
            //                      AND photos.id IN (select photo_id FROM photo_tags where tag_id IN (tag1, tag2..)
            //                      AND extra_condition_string
            //                  GROUP BY photos.id
            
            StringBuilder query_builder = new StringBuilder ();
            ArrayList where_clauses = new ArrayList ();
            query_builder.Append ("SELECT id, "                   +
                                   "time, "                 +
                                   "base_uri, "             +
                                   "filename, "             +
                                   "description, "          +
                                         "roll_id, "              +
                                   "default_version_id, "   +
                                   "rating, "               +
                                   "md5_sum "               +
                              "FROM photos ");
            
            if (range != null) {
                  where_clauses.Add (String.Format ("time >= {0} AND time <= {1}",
                                            DbUtils.UnixTimeFromDateTime (range.Start), 
                                            DbUtils.UnixTimeFromDateTime (range.End)));

            }

            if (ratingrange != null) {
                  where_clauses.Add (ratingrange.SqlClause ());
            }

            if (importidrange != null) {
                  where_clauses.Add (importidrange.SqlClause ());
            }           
            
            if (hide && Core.Database.Tags.Hidden != null) {
                  where_clauses.Add (String.Format ("id NOT IN (SELECT photo_id FROM photo_tags WHERE tag_id = {0})", 
                                            FSpot.Core.Database.Tags.Hidden.Id));
            }
            
            if (searchexpression != null) {
                  where_clauses.Add (searchexpression.SqlCondition ());
            }

            if (extra_condition != null && extra_condition.Trim () != String.Empty) {
                  where_clauses.Add (extra_condition);
            }
            
            if (where_clauses.Count > 0) {
                  query_builder.Append (" WHERE ");
                  query_builder.Append (String.Join (" AND ", ((String []) where_clauses.ToArray (typeof(String)))));
            }
            query_builder.Append (" ORDER BY time");
            return Query (query_builder.ToString ());
      }
}

Generated by  Doxygen 1.6.0   Back to index