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;
}
Hyena.Data.Sqlite.IDataReader reader = Database.Query(query_builder.ToString());
reader.Read();
int count = Convert.ToInt32(reader ["count"]);
reader.Dispose();
return(count);
}
private void LoadAllItems()
{
Hyena.Data.Sqlite.IDataReader reader = Database.Query("SELECT id, name, data FROM meta");
while (reader.Read())
{
uint id = Convert.ToUInt32(reader ["id"]);
string name = reader ["name"].ToString();
string data = null;
if (reader ["data"] != null)
{
data = reader ["data"].ToString();
}
MetaItem item = new MetaItem(id, name, data);
AddToCache(item);
}
reader.Dispose();
if (FSpotVersion.Value != Defines.VERSION)
{
FSpotVersion.Value = Defines.VERSION;
Commit(FSpotVersion);
}
}
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.Execute("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.Execute(query_builder.ToString());
int minyear = Int32.MaxValue;
int maxyear = Int32.MinValue;
// FIXME: There appears to be a race condition here where it tries to query the population
// table before Database.Execute (query_builder.ToString ()); creates it.
Hyena.Data.Sqlite.IDataReader 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.Dispose();
//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);
}
public Roll [] GetRolls(int limit)
{
List <Roll> rolls = new List <Roll> ();
string query = "SELECT DISTINCT rolls.id AS roll_id, rolls.time AS roll_time FROM rolls, photos WHERE photos.roll_id = rolls.id ORDER BY rolls.time DESC";
if (limit >= 0)
{
query += " LIMIT " + limit;
}
using (Hyena.Data.Sqlite.IDataReader reader = Database.Query(query)) {
while (reader.Read())
{
uint id = Convert.ToUInt32(reader ["roll_id"]);
Roll roll = LookupInCache(id);
if (roll == null)
{
roll = new Roll(id, Convert.ToUInt32(reader ["roll_time"]));
AddToCache(roll);
}
rolls.Add(roll);
}
reader.Dispose();
}
return(rolls.ToArray());
}
private void GetAllTags(string ids)
{
Hyena.Data.Sqlite.IDataReader reader = Database.Query("SELECT photo_id, tag_id FROM photo_tags WHERE photo_id IN " + ids);
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.AllVersionsLoaded)
{
//Console.WriteLine ("Photo {0} already Loaded", photo.Id);
continue;
}
if (reader [1] != null)
{
uint tag_id = Convert.ToUInt32(reader ["tag_id"]);
Tag tag = App.Instance.Database.Tags.Get(tag_id);
photo.AddTagUnsafely(tag);
}
}
reader.Dispose();
}
private ExportItem LoadItem(Hyena.Data.Sqlite.IDataReader reader)
{
return(new ExportItem(Convert.ToUInt32(reader ["id"]),
Convert.ToUInt32(reader ["image_id"]),
Convert.ToUInt32(reader ["image_version_id"]),
reader ["export_type"].ToString(),
reader ["export_token"].ToString()));
}
private Job LoadItem(Hyena.Data.Sqlite.IDataReader reader)
{
return(CreateJob(
reader ["job_type"].ToString(),
Convert.ToUInt32(reader ["id"]),
reader ["job_options"].ToString(),
DateTimeUtil.ToDateTime(Convert.ToInt32(reader ["run_at"])),
(JobPriority)Convert.ToInt32(reader ["job_priority"])));
}
private Job LoadItem(Hyena.Data.Sqlite.IDataReader reader)
{
return((Job)Activator.CreateInstance(
Type.GetType(reader ["job_type"].ToString()),
Convert.ToUInt32(reader["id"]),
reader["job_options"].ToString(),
Convert.ToInt32(reader["run_at"]),
(JobPriority)Convert.ToInt32(reader["job_priority"]),
true));
}
private void LoadAllItems()
{
Hyena.Data.Sqlite.IDataReader reader = Database.Query("SELECT id, image_id, image_version_id, export_type, export_token FROM exports");
while (reader.Read())
{
AddToCache(LoadItem(reader));
}
reader.Dispose();
}
private void GetTags(Photo photo)
{
Hyena.Data.Sqlite.IDataReader reader = Database.Query(new HyenaSqliteCommand("SELECT tag_id FROM photo_tags WHERE photo_id = ?", photo.Id));
while (reader.Read())
{
uint tag_id = Convert.ToUInt32(reader ["tag_id"]);
Tag tag = App.Instance.Database.Tags.Get(tag_id);
photo.AddTagUnsafely(tag);
}
reader.Dispose();
}
int [] IndicesOf(string query)
{
uint timer = Log.DebugTimerStart();
List <int> list = new List <int> ();
Hyena.Data.Sqlite.IDataReader reader = Database.Query(query);
while (reader.Read())
{
list.Add(Convert.ToInt32(reader ["row_id"]) - 1);
}
reader.Dispose();
Log.DebugTimerPrint(timer, "IndicesOf took {0} : " + query);
return(list.ToArray());
}
public uint PhotosInRoll(Roll roll)
{
uint number_of_photos = 0;
using (Hyena.Data.Sqlite.IDataReader reader = Database.Query(new HyenaSqliteCommand("SELECT count(*) AS count FROM photos WHERE roll_id = ?", roll.Id))) {
if (reader.Read())
{
number_of_photos = Convert.ToUInt32(reader ["count"]);
}
reader.Dispose();
}
return(number_of_photos);
}
private int IndexOf(string query)
{
uint timer = Log.DebugTimerStart();
Hyena.Data.Sqlite.IDataReader reader = Database.Query(query);
int index = -1;
if (reader.Read())
{
index = Convert.ToInt32(reader ["row_id"]);
}
reader.Dispose();
Log.DebugTimerPrint(timer, "IndexOf took {0} : " + query);
return(index - 1); //ROWID starts counting at 1
}
public List <ExportItem> GetByImageId(uint image_id, uint image_version_id)
{
Hyena.Data.Sqlite.IDataReader reader = Database.Query(new HyenaSqliteCommand("SELECT id, image_id, image_version_id, export_type, export_token FROM exports WHERE image_id = ? AND image_version_id = ?",
image_id, image_version_id));
List <ExportItem> export_items = new List <ExportItem> ();
while (reader.Read())
{
export_items.Add(LoadItem(reader));
}
reader.Dispose();
return(export_items);
}
public Photo GetByUri(SafeUri uri)
{
Photo photo = null;
var base_uri = uri.GetBaseUri();
var filename = uri.GetFilename();
Hyena.Data.Sqlite.IDataReader reader =
Database.Query(new HyenaSqliteCommand("SELECT id, time, description, roll_id, default_version_id, rating " +
" FROM photos " +
" LEFT JOIN photo_versions AS pv ON photos.id = pv.photo_id" +
" WHERE (photos.base_uri = ? AND photos.filename = ?)" +
" OR (pv.base_uri = ? AND pv.filename = ?)",
base_uri.ToString(), filename,
base_uri.ToString(), filename));
if (reader.Read())
{
photo = new Photo(Convert.ToUInt32(reader ["id"]),
Convert.ToInt64(reader ["time"]));
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.Dispose();
if (photo == null)
{
return(null);
}
Photo cached = LookupInCache(photo.Id);
if (cached != null)
{
return(cached);
}
AddToCache(photo);
GetTags(photo);
GetVersions(photo);
return(photo);
}
private void LoadAllItems()
{
Hyena.Data.Sqlite.IDataReader reader = Database.Query("SELECT id, job_type, job_options, run_at, job_priority FROM jobs");
Scheduler.Suspend();
while (reader.Read())
{
Job job = LoadItem(reader);
AddToCache(job);
job.Finished += HandleRemoveJob;
Scheduler.Schedule(job, job.JobPriority);
job.Status = JobStatus.Scheduled;
}
reader.Dispose();
}
private void GetAllVersions(string ids)
{
Hyena.Data.Sqlite.IDataReader reader = Database.Query("SELECT photo_id, version_id, name, base_uri, filename, import_md5, protected FROM photo_versions WHERE photo_id IN " + ids);
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.AllVersionsLoaded)
{
//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();
var base_uri = new SafeUri(reader ["base_uri"].ToString(), true);
var filename = reader ["filename"].ToString();
string import_md5 = reader["import_md5"] != null ? reader ["import_md5"].ToString() : null;
bool is_protected = Convert.ToBoolean(reader["protected"]);
photo.AddVersionUnsafely(version_id, base_uri, filename, import_md5, 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.Dispose();
}
public override Roll Get(uint id)
{
Roll roll = LookupInCache(id) as Roll;
if (roll != null)
{
return(roll);
}
Hyena.Data.Sqlite.IDataReader reader = Database.Query(new HyenaSqliteCommand("SELECT time FROM rolls WHERE id = ?", id));
if (reader.Read())
{
roll = new Roll(id, Convert.ToUInt32(reader ["time"]));
AddToCache(roll);
}
reader.Dispose();
return(roll);
}
private void GetVersions(Photo photo)
{
Hyena.Data.Sqlite.IDataReader reader = Database.Query(
new HyenaSqliteCommand("SELECT version_id, name, base_uri, filename, import_md5, protected " +
"FROM photo_versions " +
"WHERE photo_id = ?",
photo.Id
)
);
while (reader.Read())
{
uint version_id = Convert.ToUInt32(reader ["version_id"]);
string name = reader["name"].ToString();
var base_uri = new SafeUri(reader ["base_uri"].ToString(), true);
var filename = reader ["filename"].ToString();
string import_md5 = reader["import_md5"] != null ? reader ["import_md5"].ToString() : null;
bool is_protected = Convert.ToBoolean(reader["protected"]);
photo.AddVersionUnsafely(version_id, base_uri, filename, import_md5, name, is_protected);
}
reader.Dispose();
}
public override Photo Get(uint id)
{
Photo photo = LookupInCache(id);
if (photo != null)
{
return(photo);
}
Hyena.Data.Sqlite.IDataReader reader = Database.Query(
new HyenaSqliteCommand("SELECT time, description, roll_id, default_version_id, rating " +
"FROM photos " +
"WHERE id = ?", id
)
);
if (reader.Read())
{
photo = new Photo(id, Convert.ToInt64(reader ["time"]));
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.Dispose();
if (photo == null)
{
return(null);
}
GetTags(photo);
GetVersions(photo);
return(photo);
}
static Updater()
{
// Update from version 0 to 1: Remove empty Other tags
AddUpdate(new Version("1"), delegate() {
string other_id = SelectSingleString("SELECT id FROM tags WHERE name = 'Other'");
if (other_id == null)
{
return;
}
// Don't do anything if there are subtags
string tag_count = SelectSingleString(
String.Format("SELECT COUNT(*) FROM tags WHERE category_id = {0}", other_id));
if (tag_count == null || System.Int32.Parse(tag_count) != 0)
{
return;
}
// Don't do anything if there are photos tagged with this
string photo_count = SelectSingleString(
String.Format("SELECT COUNT(*) FROM photo_tags WHERE tag_id = {0}", other_id));
if (photo_count == null || System.Int32.Parse(photo_count) != 0)
{
return;
}
// Finally, we know that the Other tag exists and has no children, so remove it
Execute("DELETE FROM tags WHERE name = 'Other'");
});
// Update from version 1 to 2: Restore Other tags that were removed leaving dangling child tags
AddUpdate(new Version("2"), delegate() {
string tag_count = SelectSingleString("SELECT COUNT(*) FROM tags WHERE category_id != 0 AND category_id NOT IN (SELECT id FROM tags)");
// If there are no dangling tags, then don't do anything
if (tag_count == null || System.Int32.Parse(tag_count) == 0)
{
return;
}
int id = ExecuteScalar("INSERT INTO tags (name, category_id, is_category, icon) VALUES ('Other', 0, 1, 'stock_icon:f-spot-other.png')");
Execute(String.Format(
"UPDATE tags SET category_id = {0} WHERE id IN " +
"(SELECT id FROM tags WHERE category_id != 0 AND category_id " +
"NOT IN (SELECT id FROM tags))",
id));
Log.Debug("Other tag restored. Sorry about that!");
});
// Update from version 2 to 3: ensure that Hidden is the only tag left which is a real tag (not category)
AddUpdate(new Version("3"), delegate() {
Execute("UPDATE tags SET is_category = 1 WHERE name != 'Hidden'");
});
//Version 3.1, clean old (and unused) items in Export
AddUpdate(new Version(3, 1), delegate() {
if (TableExists("exports"))
{
ExecuteScalar("DELETE FROM exports WHERE export_type='fspot:Folder'");
}
});
//Version 4.0, bump the version number to a integer, for backward compatibility
AddUpdate(new Version(4, 0), delegate() {});
//Version 5.0, add a roll_id field to photos, rename table 'imports' to 'rolls'
//and fix bgo 324425.
AddUpdate(new Version(5, 0), delegate() {
Log.Debug("Will add a roll_id field to photos!");
string tmp_photos = MoveTableToTemp("photos");
Execute(
"CREATE TABLE photos ( " +
" id INTEGER PRIMARY KEY NOT NULL, "+
" time INTEGER NOT NULL, "+
" directory_path STRING NOT NULL, "+
" name STRING NOT NULL, "+
" description TEXT NOT NULL, "+
" roll_id INTEGER NOT NULL, "+
" default_version_id INTEGER NOT NULL "+
")");
ExecuteScalar(String.Format("INSERT INTO photos SELECT id, time, directory_path, name, description, 0, default_version_id FROM {0}", tmp_photos));
Log.Debug("Will rename imports to rolls!");
string tmp_rolls = MoveTableToTemp("imports");
Execute(
"CREATE TABLE rolls ( " +
" id INTEGER PRIMARY KEY NOT NULL, "+
" time INTEGER NOT NULL " +
")");
ExecuteScalar(String.Format("INSERT INTO rolls SELECT id, time FROM {0}", tmp_rolls));
Log.Debug("Cleaning weird descriptions, fixes bug #324425.");
Execute("UPDATE photos SET description = \"\" WHERE description LIKE \"Invalid size of entry%\"");
});
//Version 6.0, change tag icon f-spot-tag-other to emblem-generic
AddUpdate(new Version(6, 0), delegate() {
ExecuteScalar("UPDATE tags SET icon = \"stock_icon:emblem-generic\" " +
" WHERE icon LIKE \"stock_icon:f-spot-other.png\"");
});
//Update to version 7.0, keep photo uri instead of path
AddUpdate(new Version(7, 0), delegate() {
string tmp_photos = MoveTableToTemp("photos");
Execute(
"CREATE TABLE photos (" +
" id INTEGER PRIMARY KEY NOT NULL,"+
" time INTEGER NOT NULL," +
" uri STRING NOT NULL," +
" description TEXT NOT NULL," +
" roll_id INTEGER NOT NULL," +
" default_version_id INTEGER NOT NULL" +
")");
Execute(String.Format(
"INSERT INTO photos (id, time, uri, description, roll_id, default_version_id) "+
"SELECT id, time, 'file://' || directory_path || '/' || name, "+
"description, roll_id, default_version_id FROM {0}", tmp_photos));
}, true);
// Update to version 8.0, store full version uri
AddUpdate(new Version(8, 0), delegate() {
string tmp_versions = MoveTableToTemp("photo_versions");
Execute(
"CREATE TABLE photo_versions ( " +
" photo_id INTEGER, " +
" version_id INTEGER, " +
" name STRING, " +
" uri STRING NOT NULL " +
")");
Hyena.Data.Sqlite.IDataReader reader = ExecuteReader(String.Format(
"SELECT photo_id, version_id, name, uri " +
"FROM {0}, photos " +
"WHERE photo_id = id ", tmp_versions));
while (reader.Read())
{
System.Uri photo_uri = new System.Uri(reader [3] as string);
string name_without_extension = System.IO.Path.GetFileNameWithoutExtension(photo_uri.AbsolutePath);
string extension = System.IO.Path.GetExtension(photo_uri.AbsolutePath);
string uri = photo_uri.Scheme + "://" +
photo_uri.Host +
System.IO.Path.GetDirectoryName(photo_uri.AbsolutePath) + "/" +
name_without_extension + " (" + (reader [2]).ToString() + ")" + extension;
Execute(new HyenaSqliteCommand(
"INSERT INTO photo_versions (photo_id, version_id, name, uri) " +
"VALUES (?, ?, ?, ?)",
Convert.ToUInt32(reader [0]),
Convert.ToUInt32(reader [1]),
(reader [2]).ToString(),
uri));
}
}, true);
// Update to version 9.0
AddUpdate(new Version(9, 0), delegate() {
string tmp_versions = MoveTableToTemp("photo_versions");
Execute(
"CREATE TABLE photo_versions ( " +
" photo_id INTEGER, " +
" version_id INTEGER, " +
" name STRING, " +
" uri STRING NOT NULL," +
" protected BOOLEAN "+
")");
Execute(String.Format(
"INSERT INTO photo_versions (photo_id, version_id, name, uri, protected) " +
"SELECT photo_id, version_id, name, uri, 0 " +
"FROM {0} ", tmp_versions));
});
// Update to version 10.0, make id autoincrement
AddUpdate(new Version(10, 0), delegate() {
string tmp_photos = MoveTableToTemp("photos");
Execute(
"CREATE TABLE photos ( " +
" id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "+
" time INTEGER NOT NULL, "+
" uri STRING NOT NULL, "+
" description TEXT NOT NULL, "+
" roll_id INTEGER NOT NULL, "+
" default_version_id INTEGER NOT NULL "+
")");
Execute(String.Format(
"INSERT INTO photos (id, time, uri, description, roll_id, default_version_id) " +
"SELECT id, time, uri, description, roll_id, default_version_id " +
"FROM {0} ", tmp_photos));
}, false);
// Update to version 11.0, rating
AddUpdate(new Version(11, 0), delegate() {
string tmp_photos = MoveTableToTemp("photos");
Execute(
"CREATE TABLE photos ( " +
" id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "+
" time INTEGER NOT NULL, "+
" uri STRING NOT NULL, "+
" description TEXT NOT NULL, "+
" roll_id INTEGER NOT NULL, "+
" default_version_id INTEGER NOT NULL, "+
" rating INTEGER NULL "+
")");
Execute(String.Format(
"INSERT INTO photos (id, time, uri, description, roll_id, default_version_id, rating) " +
"SELECT id, time, uri, description, roll_id, default_version_id, null " +
"FROM {0} ", tmp_photos));
});
//Update to version 12.0, remove dead associations, bgo #507950, #488545
AddUpdate(new Version(12, 0), delegate() {
Execute("DELETE FROM photo_tags WHERE tag_id NOT IN (SELECT id FROM tags)");
});
// Update to version 13.0
AddUpdate(new Version(13, 0), delegate() {
Execute("UPDATE photos SET rating = 0 WHERE rating IS NULL");
});
// Update to version 14.0
AddUpdate(new Version(14, 0), delegate() {
Execute("UPDATE photos SET rating = 0 WHERE rating IS NULL");
});
// Update to version 15.0
AddUpdate(new Version(15, 0), delegate() {
string tmp_photo_tags = MoveTableToTemp("photo_tags");
Execute(
"CREATE TABLE photo_tags ( " +
" photo_id INTEGER, "+
" tag_id INTEGER, " +
" UNIQUE (photo_id, tag_id) " +
")");
Execute(String.Format(
"INSERT OR IGNORE INTO photo_tags (photo_id, tag_id) " +
"SELECT photo_id, tag_id FROM {0}", tmp_photo_tags));
string tmp_photo_versions = MoveTableToTemp("photo_versions");
Execute(
"CREATE TABLE photo_versions ( "+
" photo_id INTEGER, "+
" version_id INTEGER, "+
" name STRING, "+
" uri STRING NOT NULL,"+
" protected BOOLEAN, "+
" UNIQUE (photo_id, version_id) "+
")");
Execute(String.Format(
"INSERT OR IGNORE INTO photo_versions "+
"(photo_id, version_id, name, uri, protected) "+
"SELECT photo_id, version_id, name, uri, protected FROM {0}", tmp_photo_versions));
});
// Update to version 16.0
AddUpdate(new Version(16, 0), delegate() {
string temp_table = MoveTableToTemp("photos");
Execute("CREATE TABLE photos ( " +
" id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "+
" time INTEGER NOT NULL, "+
" uri STRING NOT NULL, "+
" description TEXT NOT NULL, "+
" roll_id INTEGER NOT NULL, "+
" default_version_id INTEGER NOT NULL, "+
" rating INTEGER NULL, "+
" md5_sum TEXT NULL "+
")"
);
Execute(string.Format("INSERT INTO photos (id, time, uri, description, roll_id, " +
"default_version_id, rating, md5_sum) " +
"SELECT id, time, uri, description, roll_id, " +
" default_version_id, rating, '' " +
"FROM {0} ",
temp_table
)
);
string temp_versions_table = MoveTableToTemp("photo_versions");
Execute("CREATE TABLE photo_versions ( "+
" photo_id INTEGER, "+
" version_id INTEGER, "+
" name STRING, "+
" uri STRING NOT NULL,"+
" md5_sum STRING NOT NULL,"+
" protected BOOLEAN "+
")");
Execute(String.Format("INSERT INTO photo_versions (photo_id, version_id, name, uri, md5_sum, protected) " +
"SELECT photo_id, version_id, name, uri, '', protected " +
"FROM {0} ",
temp_versions_table
)
);
JobStore.CreateTable(db);
// This is kind of hacky but should be a lot faster on
// large photo databases
Execute(string.Format("INSERT INTO jobs (job_type, job_options, run_at, job_priority) " +
"SELECT '{0}', id, {1}, {2} " +
"FROM photos ",
typeof(Jobs.CalculateHashJob).ToString(),
DateTimeUtil.FromDateTime(DateTime.Now),
0
)
);
}, true);
// Update to version 16.1
AddUpdate(new Version(16, 1), delegate() {
Execute("CREATE INDEX idx_photo_versions_id ON photo_versions(photo_id)");
}, false);
// Update to version 16.2
AddUpdate(new Version(16, 2), delegate() {
Execute("CREATE INDEX idx_photos_roll_id ON photos(roll_id)");
}, false);
// Update to version 16.3
AddUpdate(new Version(16, 3), delegate() {
Execute(String.Format("DELETE FROM jobs WHERE job_type = '{0}'", typeof(Jobs.CalculateHashJob).ToString()));
}, false);
// Update to version 16.4
AddUpdate(new Version(16, 4), delegate() { //fix the tables schema EOL
string temp_table = MoveTableToTemp("exports");
Execute(
"CREATE TABLE exports (\n" +
" id INTEGER PRIMARY KEY NOT NULL, \n"+
" image_id INTEGER NOT NULL, \n"+
" image_version_id INTEGER NOT NULL, \n"+
" export_type TEXT NOT NULL, \n"+
" export_token TEXT NOT NULL\n"+
")");
Execute(String.Format(
"INSERT INTO exports (id, image_id, image_version_id, export_type, export_token) " +
"SELECT id, image_id, image_version_id, export_type, export_token " +
"FROM {0}", temp_table));
temp_table = MoveTableToTemp("jobs");
Execute(
"CREATE TABLE jobs (\n" +
" id INTEGER PRIMARY KEY NOT NULL, \n"+
" job_type TEXT NOT NULL, \n"+
" job_options TEXT NOT NULL, \n"+
" run_at INTEGER, \n"+
" job_priority INTEGER NOT NULL\n"+
")");
Execute(String.Format(
"INSERT INTO jobs (id, job_type, job_options, run_at, job_priority) " +
"SELECT id, job_type, job_options, run_at, job_priority " +
"FROM {0}", temp_table));
temp_table = MoveTableToTemp("meta");
Execute(
"CREATE TABLE meta (\n" +
" id INTEGER PRIMARY KEY NOT NULL, \n"+
" name TEXT UNIQUE NOT NULL, \n"+
" data TEXT\n"+
")");
Execute(String.Format(
"INSERT INTO meta (id, name, data) " +
"SELECT id, name, data " +
"FROM {0}", temp_table));
temp_table = MoveTableToTemp("photos");
Execute(
"CREATE TABLE photos (\n" +
" id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, \n"+
" time INTEGER NOT NULL, \n"+
" uri 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"+
")");
Execute(String.Format(
"INSERT INTO photos (id, time, uri, description, roll_id, default_version_id, rating, md5_sum) " +
"SELECT id, time, uri, description, roll_id, default_version_id, rating, md5_sum " +
"FROM {0}", temp_table));
temp_table = MoveTableToTemp("photo_tags");
Execute(
"CREATE TABLE photo_tags (\n" +
" photo_id INTEGER, \n"+
" tag_id INTEGER, \n"+
" UNIQUE (photo_id, tag_id)\n" +
")");
Execute(String.Format(
"INSERT OR IGNORE INTO photo_tags (photo_id, tag_id) " +
"SELECT photo_id, tag_id " +
"FROM {0}", temp_table));
temp_table = MoveTableToTemp("photo_versions");
Execute(
"CREATE TABLE photo_versions (\n" +
" photo_id INTEGER, \n"+
" version_id INTEGER, \n"+
" name STRING, \n"+
" uri STRING NOT NULL, \n"+
" md5_sum STRING NOT NULL, \n"+
" protected BOOLEAN, \n"+
" UNIQUE (photo_id, version_id)\n"+
")");
Execute(String.Format(
"INSERT OR IGNORE INTO photo_versions (photo_id, version_id, name, uri, md5_sum, protected) " +
"SELECT photo_id, version_id, name, uri, md5_sum, protected " +
"FROM {0}", temp_table));
Execute("CREATE INDEX idx_photo_versions_id ON photo_versions(photo_id)");
Execute("CREATE INDEX idx_photos_roll_id ON photos(roll_id)");
temp_table = MoveTableToTemp("rolls");
Execute(
"CREATE TABLE rolls (\n" +
" id INTEGER PRIMARY KEY NOT NULL, \n"+
" time INTEGER NOT NULL\n"+
")");
Execute(String.Format(
"INSERT INTO rolls (id, time) " +
"SELECT id, time " +
"FROM {0}", temp_table));
temp_table = MoveTableToTemp("tags");
Execute(
"CREATE TABLE tags (\n" +
" id INTEGER PRIMARY KEY NOT NULL, \n"+
" name TEXT UNIQUE, \n"+
" category_id INTEGER, \n"+
" is_category BOOLEAN, \n"+
" sort_priority INTEGER, \n"+
" icon TEXT\n"+
")");
Execute(String.Format(
"INSERT INTO tags (id, name, category_id, is_category, sort_priority, icon) " +
"SELECT id, name, category_id, is_category, sort_priority, icon " +
"FROM {0}", temp_table));
});
// Update to version 16.5
AddUpdate(new Version(16, 5), delegate() { //fix md5 null in photos and photo_versions table
string temp_table = MoveTableToTemp("photo_versions");
Execute(
"CREATE TABLE photo_versions (\n" +
" photo_id INTEGER, \n"+
" version_id INTEGER, \n"+
" name STRING, \n"+
" uri STRING NOT NULL, \n"+
" md5_sum TEXT NULL, \n"+
" protected BOOLEAN, \n"+
" UNIQUE (photo_id, version_id)\n"+
")");
Execute(String.Format(
"INSERT OR IGNORE INTO photo_versions (photo_id, version_id, name, uri, md5_sum, protected) " +
"SELECT photo_id, version_id, name, uri, md5_sum, protected " +
"FROM {0}", temp_table));
Execute("CREATE INDEX idx_photo_versions_id ON photo_versions(photo_id)");
Execute("UPDATE photos SET md5_sum = NULL WHERE md5_sum = ''");
Execute("UPDATE photo_versions SET md5_sum = NULL WHERE md5_sum = ''");
});
// Update to version 17.0, split uri and filename
AddUpdate(new Version(17, 0), delegate() {
string tmp_photos = MoveTableToTemp("photos");
string tmp_versions = MoveTableToTemp("photo_versions");
Execute(
"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"+
")");
Execute(
"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"+
")");
Hyena.Data.Sqlite.IDataReader reader = ExecuteReader(String.Format(
"SELECT id, time, uri, description, roll_id, default_version_id, rating, md5_sum " +
"FROM {0} ", tmp_photos));
while (reader.Read())
{
System.Uri photo_uri = new System.Uri(reader ["uri"] as string);
string filename = photo_uri.GetFilename();
Uri base_uri = photo_uri.GetDirectoryUri();
string md5 = reader ["md5_sum"] != null ? reader ["md5_sum"].ToString() : null;
Execute(new HyenaSqliteCommand(
"INSERT INTO photos (id, time, base_uri, filename, description, roll_id, default_version_id, rating, md5_sum) " +
"VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)",
Convert.ToUInt32(reader ["id"]),
reader ["time"],
base_uri.ToString(),
filename,
reader ["description"].ToString(),
Convert.ToUInt32(reader ["roll_id"]),
Convert.ToUInt32(reader ["default_version_id"]),
Convert.ToUInt32(reader ["rating"]),
String.IsNullOrEmpty(md5) ? null : md5));
}
reader.Dispose();
reader = ExecuteReader(String.Format(
"SELECT photo_id, version_id, name, uri, md5_sum, protected " +
"FROM {0} ", tmp_versions));
while (reader.Read())
{
System.Uri photo_uri = new System.Uri(reader ["uri"] as string);
string filename = photo_uri.GetFilename();
Uri base_uri = photo_uri.GetDirectoryUri();
string md5 = reader ["md5_sum"] != null ? reader ["md5_sum"].ToString() : null;
Execute(new HyenaSqliteCommand(
"INSERT INTO photo_versions (photo_id, version_id, name, base_uri, filename, protected, md5_sum) " +
"VALUES (?, ?, ?, ?, ?, ?, ?)",
Convert.ToUInt32(reader ["photo_id"]),
Convert.ToUInt32(reader ["version_id"]),
reader ["name"].ToString(),
base_uri.ToString(),
filename,
Convert.ToBoolean(reader ["protected"]),
String.IsNullOrEmpty(md5) ? null : md5));
}
Execute("CREATE INDEX idx_photos_roll_id ON photos(roll_id)");
Execute("CREATE INDEX idx_photo_versions_id ON photo_versions(photo_id)");
}, true);
// Update to version 17.1, Rename 'Import Tags' to 'Imported Tags'
AddUpdate(new Version(17, 1), delegate() {
Execute("UPDATE tags SET name = 'Imported Tags' WHERE name = 'Import Tags'");
});
// Update to version 17.2, Make sure every photo has an Original version in photo_versions
AddUpdate(new Version(17, 2), delegate() {
// Find photos that have no original version;
var have_original_query = "SELECT id FROM photos LEFT JOIN photo_versions AS pv ON pv.photo_id = id WHERE pv.version_id = 1";
var no_original_query = String.Format("SELECT id, base_uri, filename FROM photos WHERE id NOT IN ({0})", have_original_query);
var reader = ExecuteReader(no_original_query);
while (reader.Read())
{
Execute(new HyenaSqliteCommand(
"INSERT INTO photo_versions (photo_id, version_id, name, base_uri, filename, protected, md5_sum) " +
"VALUES (?, ?, ?, ?, ?, ?, ?)",
Convert.ToUInt32(reader ["id"]),
1,
"Original",
reader ["base_uri"].ToString(),
reader ["filename"].ToString(),
1,
""));
}
}, true);
// Update to version 18.0, Import MD5 hashes
AddUpdate(new Version(18, 0), delegate() {
string tmp_photos = MoveTableToTemp("photos");
string tmp_versions = MoveTableToTemp("photo_versions");
Execute(
"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"+
")");
Execute(
"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"+
" import_md5 TEXT NULL, \n"+
" protected BOOLEAN, \n"+
" UNIQUE (photo_id, version_id)\n"+
")");
var reader = ExecuteReader(String.Format(
"SELECT id, time, base_uri, filename, description, roll_id, default_version_id, rating " +
"FROM {0} ", tmp_photos));
while (reader.Read())
{
Execute(new HyenaSqliteCommand(
"INSERT INTO photos (id, time, base_uri, filename, description, roll_id, default_version_id, rating) " +
"VALUES (?, ?, ?, ?, ?, ?, ?, ?)",
Convert.ToUInt32(reader ["id"]),
reader ["time"],
reader ["base_uri"].ToString(),
reader ["filename"].ToString(),
reader ["description"].ToString(),
Convert.ToUInt32(reader ["roll_id"]),
Convert.ToUInt32(reader ["default_version_id"]),
Convert.ToUInt32(reader ["rating"])));
}
reader.Dispose();
reader = ExecuteReader(String.Format(
"SELECT photo_id, version_id, name, base_uri, filename, protected " +
"FROM {0} ", tmp_versions));
while (reader.Read())
{
Execute(new HyenaSqliteCommand(
"INSERT INTO photo_versions (photo_id, version_id, name, base_uri, filename, protected, import_md5) " +
"VALUES (?, ?, ?, ?, ?, ?, ?)",
Convert.ToUInt32(reader ["photo_id"]),
Convert.ToUInt32(reader ["version_id"]),
reader ["name"].ToString(),
reader ["base_uri"].ToString(),
reader ["filename"].ToString(),
Convert.ToBoolean(reader ["protected"]),
""));
}
Execute("CREATE INDEX idx_photo_versions_import_md5 ON photo_versions(import_md5)");
}, true);
}
private Photo [] Query(HyenaSqliteCommand query)
{
uint timer = Log.DebugTimerStart();
Hyena.Data.Sqlite.IDataReader 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"]));
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.Dispose();
bool need_load = false;
string photo_ids = "(";
foreach (Photo photo in new_photos)
{
AddToCache(photo);
photo_ids = photo_ids + Convert.ToString(photo.Id) + ",";
need_load |= !photo.AllVersionsLoaded;
}
photo_ids = photo_ids + "-1)";
if (need_load)
{
GetAllTags(photo_ids);
GetAllVersions(photo_ids);
foreach (Photo photo in new_photos)
{
photo.AllVersionsLoaded = true;
}
}
else
{
//Console.WriteLine ("Skipped Loading Data");
}
foreach (Photo photo in new_photos)
{
photo.Changes = null;
}
Log.DebugTimerPrint(timer, "Query took {0} : " + query.Text);
return(query_result.ToArray());
}
