diff options
Diffstat (limited to 'src/db')
| -rw-r--r-- | src/db/DatabaseTable.vala | 384 | ||||
| -rw-r--r-- | src/db/Db.vala | 366 | ||||
| -rw-r--r-- | src/db/EventTable.vala | 235 | ||||
| -rw-r--r-- | src/db/PhotoTable.vala | 1245 | ||||
| -rw-r--r-- | src/db/SavedSearchDBTable.vala | 641 | ||||
| -rw-r--r-- | src/db/TagTable.vala | 248 | ||||
| -rw-r--r-- | src/db/TombstoneTable.vala | 146 | ||||
| -rw-r--r-- | src/db/VersionTable.vala | 98 | ||||
| -rw-r--r-- | src/db/VideoTable.vala | 462 | ||||
| -rw-r--r-- | src/db/mk/db.mk | 35 |
10 files changed, 3860 insertions, 0 deletions
diff --git a/src/db/DatabaseTable.vala b/src/db/DatabaseTable.vala new file mode 100644 index 0000000..55d440d --- /dev/null +++ b/src/db/DatabaseTable.vala @@ -0,0 +1,384 @@ +/* Copyright 2009-2014 Yorba Foundation + * + * This software is licensed under the GNU LGPL (version 2.1 or later). + * See the COPYING file in this distribution. + */ + +public errordomain DatabaseError { + ERROR, + BACKING, + MEMORY, + ABORT, + LIMITS, + TYPESPEC +} + +public abstract class DatabaseTable { + /*** + * This number should be incremented every time any database schema is altered. + * + * NOTE: Adding or removing tables or removing columns do not need a new schema version, because + * tables are created on demand and tables and columns are easily ignored when already present. + * However, the change should be noted in upgrade_database() as a comment. + ***/ + public const int SCHEMA_VERSION = 20; + + protected static Sqlite.Database db; + + private static int in_transaction = 0; + + public string table_name = null; + + private static void prepare_db(string filename) { + // Open DB. + int res = Sqlite.Database.open_v2(filename, out db, Sqlite.OPEN_READWRITE | Sqlite.OPEN_CREATE, + null); + if (res != Sqlite.OK) + AppWindow.panic(_("Unable to open/create photo database %s: error code %d").printf(filename, + res)); + + // Check if we have write access to database. + if (filename != Db.IN_MEMORY_NAME) { + try { + File file_db = File.new_for_path(filename); + FileInfo info = file_db.query_info(FileAttribute.ACCESS_CAN_WRITE, FileQueryInfoFlags.NONE); + if (!info.get_attribute_boolean(FileAttribute.ACCESS_CAN_WRITE)) + AppWindow.panic(_("Unable to write to photo database file:\n %s").printf(filename)); + } catch (Error e) { + AppWindow.panic(_("Error accessing database file:\n %s\n\nError was: \n%s").printf(filename, + e.message)); + } + } + } + + public static void init(string filename) { + // Open DB. + prepare_db(filename); + + // Try a query to make sure DB is intact; if not, try to use the backup + Sqlite.Statement stmt; + int res = db.prepare_v2("CREATE TABLE IF NOT EXISTS VersionTable (" + + "id INTEGER PRIMARY KEY, " + + "schema_version INTEGER, " + + "app_version TEXT, " + + "user_data TEXT NULL" + + ")", -1, out stmt); + + // Query on db failed, copy over backup and open it + if(res != Sqlite.OK) { + db = null; + + string backup_path = filename + ".bak"; + string cmdline = "cp " + backup_path + " " + filename; + Posix.system(cmdline); + + prepare_db(filename); + } + + // disable synchronized commits for performance reasons ... this is not vital, hence we + // don't error out if this fails + res = db.exec("PRAGMA synchronous=OFF"); + if (res != Sqlite.OK) + warning("Unable to disable synchronous mode", res); + } + + public static void terminate() { + // freeing the database closes it + db = null; + } + + // XXX: errmsg() is global, and so this will not be accurate in a threaded situation + protected static void fatal(string op, int res) { + error("%s: [%d] %s", op, res, db.errmsg()); + } + + // XXX: errmsg() is global, and so this will not be accurate in a threaded situation + protected static void warning(string op, int res) { + GLib.warning("%s: [%d] %s", op, res, db.errmsg()); + } + + protected void set_table_name(string table_name) { + this.table_name = table_name; + } + + // This method will throw an error on an SQLite return code unless it's OK, DONE, or ROW, which + // are considered normal results. + protected static void throw_error(string method, int res) throws DatabaseError { + string msg = "(%s) [%d] - %s".printf(method, res, db.errmsg()); + + switch (res) { + case Sqlite.OK: + case Sqlite.DONE: + case Sqlite.ROW: + return; + + case Sqlite.PERM: + case Sqlite.BUSY: + case Sqlite.READONLY: + case Sqlite.IOERR: + case Sqlite.CORRUPT: + case Sqlite.CANTOPEN: + case Sqlite.NOLFS: + case Sqlite.AUTH: + case Sqlite.FORMAT: + case Sqlite.NOTADB: + throw new DatabaseError.BACKING(msg); + + case Sqlite.NOMEM: + throw new DatabaseError.MEMORY(msg); + + case Sqlite.ABORT: + case Sqlite.LOCKED: + case Sqlite.INTERRUPT: + throw new DatabaseError.ABORT(msg); + + case Sqlite.FULL: + case Sqlite.EMPTY: + case Sqlite.TOOBIG: + case Sqlite.CONSTRAINT: + case Sqlite.RANGE: + throw new DatabaseError.LIMITS(msg); + + case Sqlite.SCHEMA: + case Sqlite.MISMATCH: + throw new DatabaseError.TYPESPEC(msg); + + case Sqlite.ERROR: + case Sqlite.INTERNAL: + case Sqlite.MISUSE: + default: + throw new DatabaseError.ERROR(msg); + } + } + + protected bool exists_by_id(int64 id) { + Sqlite.Statement stmt; + int res = db.prepare_v2("SELECT id FROM %s WHERE id=?".printf(table_name), -1, out stmt); + assert(res == Sqlite.OK); + + res = stmt.bind_int64(1, id); + assert(res == Sqlite.OK); + + res = stmt.step(); + if (res != Sqlite.ROW && res != Sqlite.DONE) + fatal("exists_by_id [%s] %s".printf(id.to_string(), table_name), res); + + return (res == Sqlite.ROW); + } + + protected bool select_by_id(int64 id, string columns, out Sqlite.Statement stmt) { + string sql = "SELECT %s FROM %s WHERE id=?".printf(columns, table_name); + + int res = db.prepare_v2(sql, -1, out stmt); + assert(res == Sqlite.OK); + + res = stmt.bind_int64(1, id); + assert(res == Sqlite.OK); + + res = stmt.step(); + if (res != Sqlite.ROW && res != Sqlite.DONE) + fatal("select_by_id [%s] %s %s".printf(id.to_string(), table_name, columns), res); + + return (res == Sqlite.ROW); + } + + // Caller needs to bind value #1 before calling execute_update_by_id() + private void prepare_update_by_id(int64 id, string column, out Sqlite.Statement stmt) { + string sql = "UPDATE %s SET %s=? WHERE id=?".printf(table_name, column); + + int res = db.prepare_v2(sql, -1, out stmt); + assert(res == Sqlite.OK); + + res = stmt.bind_int64(2, id); + assert(res == Sqlite.OK); + } + + private bool execute_update_by_id(Sqlite.Statement stmt) { + int res = stmt.step(); + if (res != Sqlite.DONE) { + fatal("execute_update_by_id", res); + + return false; + } + + return true; + } + + protected bool update_text_by_id(int64 id, string column, string text) { + Sqlite.Statement stmt; + prepare_update_by_id(id, column, out stmt); + + int res = stmt.bind_text(1, text); + assert(res == Sqlite.OK); + + return execute_update_by_id(stmt); + } + + protected void update_text_by_id_2(int64 id, string column, string text) throws DatabaseError { + Sqlite.Statement stmt; + prepare_update_by_id(id, column, out stmt); + + int res = stmt.bind_text(1, text); + assert(res == Sqlite.OK); + + res = stmt.step(); + if (res != Sqlite.DONE) + throw_error("DatabaseTable.update_text_by_id_2 %s.%s".printf(table_name, column), res); + } + + protected bool update_int_by_id(int64 id, string column, int value) { + Sqlite.Statement stmt; + prepare_update_by_id(id, column, out stmt); + + int res = stmt.bind_int(1, value); + assert(res == Sqlite.OK); + + return execute_update_by_id(stmt); + } + + protected void update_int_by_id_2(int64 id, string column, int value) throws DatabaseError { + Sqlite.Statement stmt; + prepare_update_by_id(id, column, out stmt); + + int res = stmt.bind_int(1, value); + assert(res == Sqlite.OK); + + res = stmt.step(); + if (res != Sqlite.DONE) + throw_error("DatabaseTable.update_int_by_id_2 %s.%s".printf(table_name, column), res); + } + + protected bool update_int64_by_id(int64 id, string column, int64 value) { + Sqlite.Statement stmt; + prepare_update_by_id(id, column, out stmt); + + int res = stmt.bind_int64(1, value); + assert(res == Sqlite.OK); + + return execute_update_by_id(stmt); + } + + protected void update_int64_by_id_2(int64 id, string column, int64 value) throws DatabaseError { + Sqlite.Statement stmt; + prepare_update_by_id(id, column, out stmt); + + int res = stmt.bind_int64(1, value); + assert(res == Sqlite.OK); + + res = stmt.step(); + if (res != Sqlite.DONE) + throw_error("DatabaseTable.update_int64_by_id_2 %s.%s".printf(table_name, column), res); + } + + protected void delete_by_id(int64 id) throws DatabaseError { + Sqlite.Statement stmt; + int res = db.prepare_v2("DELETE FROM %s WHERE id=?".printf(table_name), -1, out stmt); + assert(res == Sqlite.OK); + + res = stmt.bind_int64(1, id); + assert(res == Sqlite.OK); + + res = stmt.step(); + if (res != Sqlite.DONE) + throw_error("%s.remove".printf(table_name), res); + } + + public static bool has_column(string table_name, string column_name) { + Sqlite.Statement stmt; + int res = db.prepare_v2("PRAGMA table_info(%s)".printf(table_name), -1, out stmt); + assert(res == Sqlite.OK); + + for (;;) { + res = stmt.step(); + if (res == Sqlite.DONE) { + break; + } else if (res != Sqlite.ROW) { + fatal("has_column %s".printf(table_name), res); + + break; + } else { + string column = stmt.column_text(1); + if (column != null && column == column_name) + return true; + } + } + + return false; + } + + public static bool has_table(string table_name) { + Sqlite.Statement stmt; + int res = db.prepare_v2("PRAGMA table_info(%s)".printf(table_name), -1, out stmt); + assert(res == Sqlite.OK); + + res = stmt.step(); + + return (res != Sqlite.DONE); + } + + public static bool add_column(string table_name, string column_name, string column_constraints) { + Sqlite.Statement stmt; + int res = db.prepare_v2("ALTER TABLE %s ADD COLUMN %s %s".printf(table_name, column_name, + column_constraints), -1, out stmt); + assert(res == Sqlite.OK); + + res = stmt.step(); + if (res != Sqlite.DONE) { + critical("Unable to add column %s %s %s: (%d) %s", table_name, column_name, column_constraints, + res, db.errmsg()); + + return false; + } + + return true; + } + + // This method will only add the column if a table exists (relying on the table object + // to build a new one when first referenced) and only if the column does not exist. In essence, + // it's a cleaner way to run has_table(), has_column(), and add_column(). + public static bool ensure_column(string table_name, string column_name, string column_constraints, + string upgrade_msg) { + if (!has_table(table_name) || has_column(table_name, column_name)) + return true; + + message("%s", upgrade_msg); + + return add_column(table_name, column_name, column_constraints); + } + + public int get_row_count() { + Sqlite.Statement stmt; + int res = db.prepare_v2("SELECT COUNT(id) AS RowCount FROM %s".printf(table_name), -1, out stmt); + assert(res == Sqlite.OK); + + res = stmt.step(); + if (res != Sqlite.ROW) { + critical("Unable to retrieve row count on %s: (%d) %s", table_name, res, db.errmsg()); + + return 0; + } + + return stmt.column_int(0); + } + + // This is not thread-safe. + public static void begin_transaction() { + if (in_transaction++ != 0) + return; + + int res = db.exec("BEGIN TRANSACTION"); + assert(res == Sqlite.OK); + } + + // This is not thread-safe. + public static void commit_transaction() throws DatabaseError { + assert(in_transaction > 0); + if (--in_transaction != 0) + return; + + int res = db.exec("COMMIT TRANSACTION"); + if (res != Sqlite.DONE) + throw_error("commit_transaction", res); + } +} + diff --git a/src/db/Db.vala b/src/db/Db.vala new file mode 100644 index 0000000..ced530a --- /dev/null +++ b/src/db/Db.vala @@ -0,0 +1,366 @@ +/* Copyright 2011-2014 Yorba Foundation + * + * This software is licensed under the GNU Lesser General Public License + * (version 2.1 or later). See the COPYING file in this distribution. + */ + +namespace Db { + +public static const string IN_MEMORY_NAME = ":memory:"; + +private string? filename = null; + +// Passing null as the db_file will create an in-memory, non-persistent database. +public void preconfigure(File? db_file) { + filename = (db_file != null) ? db_file.get_path() : IN_MEMORY_NAME; +} + +public void init() throws Error { + assert(filename != null); + + DatabaseTable.init(filename); +} + +public void terminate() { + DatabaseTable.terminate(); +} + +public enum VerifyResult { + OK, + FUTURE_VERSION, + UPGRADE_ERROR, + NO_UPGRADE_AVAILABLE +} + +public VerifyResult verify_database(out string app_version, out int schema_version) { + VersionTable version_table = VersionTable.get_instance(); + schema_version = version_table.get_version(out app_version); + + if (schema_version >= 0) + debug("Database schema version %d created by app version %s", schema_version, app_version); + + if (schema_version == -1) { + // no version set, do it now (tables will be created on demand) + debug("Creating database schema version %d for app version %s", DatabaseTable.SCHEMA_VERSION, + Resources.APP_VERSION); + version_table.set_version(DatabaseTable.SCHEMA_VERSION, Resources.APP_VERSION); + app_version = Resources.APP_VERSION; + schema_version = DatabaseTable.SCHEMA_VERSION; + } else if (schema_version > DatabaseTable.SCHEMA_VERSION) { + // Back to the future + return Db.VerifyResult.FUTURE_VERSION; + } else if (schema_version < DatabaseTable.SCHEMA_VERSION) { + // Past is present + VerifyResult result = upgrade_database(schema_version); + if (result != VerifyResult.OK) + return result; + } + + return VerifyResult.OK; +} + +private VerifyResult upgrade_database(int input_version) { + assert(input_version < DatabaseTable.SCHEMA_VERSION); + + int version = input_version; + + // No upgrade available from version 1. + if (version == 1) + return VerifyResult.NO_UPGRADE_AVAILABLE; + + message("Upgrading database from schema version %d to %d", version, DatabaseTable.SCHEMA_VERSION); + + // + // Version 2: For all intents and purposes, the baseline schema version. + // * Removed start_time and end_time from EventsTable + // + + // + // Version 3: + // * Added flags column to PhotoTable + // + + if (!DatabaseTable.has_column("PhotoTable", "flags")) { + message("upgrade_database: adding flags column to PhotoTable"); + if (!DatabaseTable.add_column("PhotoTable", "flags", "INTEGER DEFAULT 0")) + return VerifyResult.UPGRADE_ERROR; + } + + version = 3; + + // + // ThumbnailTable(s) removed. + // + + // + // Version 4: + // * Added file_format column to PhotoTable + // + + if (!DatabaseTable.has_column("PhotoTable", "file_format")) { + message("upgrade_database: adding file_format column to PhotoTable"); + if (!DatabaseTable.add_column("PhotoTable", "file_format", "INTEGER DEFAULT 0")) + return VerifyResult.UPGRADE_ERROR; + } + + version = 4; + + // + // Version 5: + // * Added title column to PhotoTable + // + + if (!DatabaseTable.has_column("PhotoTable", "title")) { + message("upgrade_database: adding title column to PhotoTable"); + if (!DatabaseTable.add_column("PhotoTable", "title", "TEXT")) + return VerifyResult.UPGRADE_ERROR; + } + + version = 5; + + // + // Version 6: + // * Added backlinks column to PhotoTable + // + + if (!DatabaseTable.has_column("PhotoTable", "backlinks")) { + message("upgrade_database: adding backlinks column to PhotoTable"); + if (!DatabaseTable.add_column("PhotoTable", "backlinks", "TEXT")) + return VerifyResult.UPGRADE_ERROR; + } + + version = 6; + + // + // * Ignore the exif_md5 column from PhotoTable. Because removing columns with SQLite is + // painful, simply ignoring the column for now. Keeping it up-to-date when possible in + // case a future requirement is discovered. + // + + // + // Version 7: + // * Added BackingPhotoTable (which creates itself if needed) + // * Added time_reimported and editable_id columns to PhotoTable + // + + if (!DatabaseTable.has_column("PhotoTable", "time_reimported")) { + message("upgrade_database: adding time_reimported column to PhotoTable"); + if (!DatabaseTable.add_column("PhotoTable", "time_reimported", "INTEGER")) + return VerifyResult.UPGRADE_ERROR; + } + + if (!DatabaseTable.has_column("PhotoTable", "editable_id")) { + message("upgrade_database: adding editable_id column to PhotoTable"); + if (!DatabaseTable.add_column("PhotoTable", "editable_id", "INTEGER DEFAULT -1")) + return VerifyResult.UPGRADE_ERROR; + } + + version = 7; + + // + // * Ignore the orientation column in BackingPhotoTable. (See note above about removing + // columns from tables.) + // + + // + // Version 8: + // * Added rating column to PhotoTable + // + + if (!DatabaseTable.has_column("PhotoTable", "rating")) { + message("upgrade_database: adding rating column to PhotoTable"); + if (!DatabaseTable.add_column("PhotoTable", "rating", "INTEGER DEFAULT 0")) + return VerifyResult.UPGRADE_ERROR; + } + + // + // Version 9: + // * Added metadata_dirty flag to PhotoTable. Default to 1 rather than 0 on upgrades so + // changes to metadata prior to upgrade will be caught by MetadataWriter. + // + + if (!DatabaseTable.has_column("PhotoTable", "metadata_dirty")) { + message("upgrade_database: adding metadata_dirty column to PhotoTable"); + if (!DatabaseTable.add_column("PhotoTable", "metadata_dirty", "INTEGER DEFAULT 1")) + return VerifyResult.UPGRADE_ERROR; + } + + version = 9; + + // + // Version 10: + // * Added flags column to VideoTable + // + + if (DatabaseTable.has_table("VideoTable") && !DatabaseTable.has_column("VideoTable", "flags")) { + message("upgrade_database: adding flags column to VideoTable"); + if (!DatabaseTable.add_column("VideoTable", "flags", "INTEGER DEFAULT 0")) + return VerifyResult.UPGRADE_ERROR; + } + + version = 10; + + // + // Version 11: + // * Added primary_source_id column to EventTable + // + + if (!DatabaseTable.has_column("EventTable", "primary_source_id")) { + message("upgrade_database: adding primary_source_id column to EventTable"); + if (!DatabaseTable.add_column("EventTable", "primary_source_id", "INTEGER DEFAULT 0")) + return VerifyResult.UPGRADE_ERROR; + } + + version = 11; + + // + // Version 12: + // * Added reason column to TombstoneTable + // + + if (!DatabaseTable.ensure_column("TombstoneTable", "reason", "INTEGER DEFAULT 0", + "upgrade_database: adding reason column to TombstoneTable")) { + return VerifyResult.UPGRADE_ERROR; + } + + version = 12; + + // + // Version 13: + // * Added RAW development columns to Photo table. + // + + if (!DatabaseTable.has_column("PhotoTable", "developer")) { + message("upgrade_database: adding developer column to PhotoTable"); + if (!DatabaseTable.add_column("PhotoTable", "developer", "TEXT")) + return VerifyResult.UPGRADE_ERROR; + } + + if (!DatabaseTable.has_column("PhotoTable", "develop_shotwell_id")) { + message("upgrade_database: adding develop_shotwell_id column to PhotoTable"); + if (!DatabaseTable.add_column("PhotoTable", "develop_shotwell_id", "INTEGER DEFAULT -1")) + return VerifyResult.UPGRADE_ERROR; + } + + if (!DatabaseTable.has_column("PhotoTable", "develop_camera_id")) { + message("upgrade_database: adding develop_camera_id column to PhotoTable"); + if (!DatabaseTable.add_column("PhotoTable", "develop_camera_id", "INTEGER DEFAULT -1")) + return VerifyResult.UPGRADE_ERROR; + } + + if (!DatabaseTable.has_column("PhotoTable", "develop_embedded_id")) { + message("upgrade_database: adding develop_embedded_id column to PhotoTable"); + if (!DatabaseTable.add_column("PhotoTable", "develop_embedded_id", "INTEGER DEFAULT -1")) + return VerifyResult.UPGRADE_ERROR; + } + + version = 13; + + // + // Version 14: + // * Upgrades tag names in the TagTable for hierarchical tag support + // + + if (input_version < 14) + TagTable.upgrade_for_htags(); + + version = 14; + + // + // Version 15: + // * Upgrades the version number to prevent Shotwell 0.11 users from opening + // Shotwell 0.12 databases. While the database schema hasn't changed, + // straighten was only partially implemented in 0.11 but is fully + // implemented in 0.12, so when 0.11 users open an 0.12 database with + // straightening information, they see partially and/or incorrectly + // rotated photos. + // + + version = 15; + + // + // Version 16: + // * Migration of dconf settings data from /apps/shotwell to /org/yorba/shotwell. + // + // The database itself doesn't change; this is to force the path migration to + // occur. + // + + if (input_version < 16) { + // Run the settings migrator to copy settings data from /apps/shotwell to /org/yorba/shotwell. + // Please see https://mail.gnome.org/archives/desktop-devel-list/2011-February/msg00064.html + GSettingsConfigurationEngine.run_gsettings_migrator(); + } + + version = 16; + + // + // Version 17: + // * Added comment column to PhotoTable and VideoTable + // + + if (!DatabaseTable.has_column("PhotoTable", "comment")) { + message("upgrade_database: adding comment column to PhotoTable"); + if (!DatabaseTable.add_column("PhotoTable", "comment", "TEXT")) + return VerifyResult.UPGRADE_ERROR; + } + if (!DatabaseTable.has_column("VideoTable", "comment")) { + message("upgrade_database: adding comment column to VideoTable"); + if (!DatabaseTable.add_column("VideoTable", "comment", "TEXT")) + return VerifyResult.UPGRADE_ERROR; + } + + version = 17; + + // + // Version 18: + // * Added comment column to EventTable + // + + if (!DatabaseTable.has_column("EventTable", "comment")) { + message("upgrade_database: adding comment column to EventTable"); + if (!DatabaseTable.add_column("EventTable", "comment", "TEXT")) + return VerifyResult.UPGRADE_ERROR; + } + + version = 18; + + // + // Version 19: + // * Deletion and regeneration of camera-raw thumbnails from previous versions, + // since they're likely to be incorrect. + // + // The database itself doesn't change; this is to force the thumbnail fixup to + // occur. + // + + if (input_version < 19) { + Application.get_instance().set_raw_thumbs_fix_required(true); + } + + version = 19; + + // + // Version 20: + // * No change to database schema but fixing issue #6541 ("Saved searches should be aware of + // comments") added a new enumeration value that is stored in the SavedSearchTable. The + // presence of this heretofore unseen enumeration value will cause prior versions of + // Shotwell to yarf, so we bump the version here to ensure this doesn't happen + // + + version = 20; + + // + // Finalize the upgrade process + // + + assert(version == DatabaseTable.SCHEMA_VERSION); + VersionTable.get_instance().update_version(version, Resources.APP_VERSION); + + message("Database upgrade to schema version %d successful", version); + + return VerifyResult.OK; +} + +} + diff --git a/src/db/EventTable.vala b/src/db/EventTable.vala new file mode 100644 index 0000000..016fa00 --- /dev/null +++ b/src/db/EventTable.vala @@ -0,0 +1,235 @@ +/* Copyright 2011-2014 Yorba Foundation + * + * This software is licensed under the GNU Lesser General Public License + * (version 2.1 or later). See the COPYING file in this distribution. + */ + +public struct EventID { + public const int64 INVALID = -1; + + public int64 id; + + public EventID(int64 id = INVALID) { + this.id = id; + } + + public bool is_invalid() { + return (id == INVALID); + } + + public bool is_valid() { + return (id != INVALID); + } +} + +public class EventRow { + public EventID event_id; + public string? name; + public time_t time_created; + public string? primary_source_id; + public string? comment; +} + +public class EventTable : DatabaseTable { + private static EventTable instance = null; + + private EventTable() { + Sqlite.Statement stmt; + int res = db.prepare_v2("CREATE TABLE IF NOT EXISTS EventTable (" + + "id INTEGER PRIMARY KEY, " + + "name TEXT, " + + "primary_photo_id INTEGER, " + + "time_created INTEGER," + + "primary_source_id TEXT," + + "comment TEXT" + + ")", -1, out stmt); + assert(res == Sqlite.OK); + + res = stmt.step(); + if (res != Sqlite.DONE) + fatal("create photo table", res); + + set_table_name("EventTable"); + } + + public static EventTable get_instance() { + if (instance == null) + instance = new EventTable(); + + return instance; + } + + // Returns a valid source ID, creating one from a legacy primary photo ID when needed. + private string? source_id_upgrade(int64 primary_photo_id, string? primary_source_id) { + if (MediaCollectionRegistry.get_instance().is_valid_source_id(primary_source_id)) { + return primary_source_id; + } + if (primary_photo_id != PhotoID.INVALID) { + // Upgrade to source_id from photo_id. + return PhotoID.upgrade_photo_id_to_source_id(PhotoID(primary_photo_id)); + } + return null; + } + + public EventRow create(string? primary_source_id, string? comment) throws DatabaseError { + assert(primary_source_id != null && primary_source_id != ""); + + Sqlite.Statement stmt; + int res = db.prepare_v2( + "INSERT INTO EventTable (primary_source_id, time_created, comment) VALUES (?, ?, ?)", + -1, out stmt); + assert(res == Sqlite.OK); + + time_t time_created = (time_t) now_sec(); + + res = stmt.bind_text(1, primary_source_id); + assert(res == Sqlite.OK); + res = stmt.bind_int64(2, time_created); + assert(res == Sqlite.OK); + res = stmt.bind_text(3, comment); + assert(res == Sqlite.OK); + + res = stmt.step(); + if (res != Sqlite.DONE) + throw_error("EventTable.create", res); + + EventRow row = new EventRow(); + row.event_id = EventID(db.last_insert_rowid()); + row.name = null; + row.primary_source_id = primary_source_id; + row.time_created = time_created; + row.comment = comment; + + return row; + } + + // NOTE: The event_id in EventRow is ignored here. No checking is done to prevent + // against creating duplicate events or for the validity of other fields in the row (i.e. + // the primary photo ID). + public EventID create_from_row(EventRow row) { + Sqlite.Statement stmt; + int res = db.prepare_v2("INSERT INTO EventTable (name, primary_photo_id, primary_source_id, time_created, comment) VALUES (?, ?, ?, ?, ?)", + -1, out stmt); + assert(res == Sqlite.OK); + + res = stmt.bind_text(1, row.name); + assert(res == Sqlite.OK); + res = stmt.bind_int64(2, PhotoID.INVALID); + assert(res == Sqlite.OK); + res = stmt.bind_text(3, row.primary_source_id); + assert(res == Sqlite.OK); + res = stmt.bind_int64(4, row.time_created); + assert(res == Sqlite.OK); + res = stmt.bind_text(5, row.comment); + assert(res == Sqlite.OK); + + res = stmt.step(); + if (res != Sqlite.DONE) { + fatal("Event create_from_row", res); + + return EventID(); + } + + return EventID(db.last_insert_rowid()); + } + + public EventRow? get_row(EventID event_id) { + Sqlite.Statement stmt; + int res = db.prepare_v2( + "SELECT name, primary_photo_id, primary_source_id, time_created, comment FROM EventTable WHERE id=?", -1, out stmt); + assert(res == Sqlite.OK); + + res = stmt.bind_int64(1, event_id.id); + assert(res == Sqlite.OK); + + if (stmt.step() != Sqlite.ROW) + return null; + + EventRow row = new EventRow(); + row.event_id = event_id; + row.name = stmt.column_text(0); + if (row.name != null && row.name.length == 0) + row.name = null; + row.primary_source_id = source_id_upgrade(stmt.column_int64(1), stmt.column_text(2)); + row.time_created = (time_t) stmt.column_int64(3); + row.comment = stmt.column_text(4); + + return row; + } + + public void remove(EventID event_id) throws DatabaseError { + delete_by_id(event_id.id); + } + + public Gee.ArrayList<EventRow?> get_events() { + Sqlite.Statement stmt; + int res = db.prepare_v2("SELECT id, name, primary_photo_id, primary_source_id, time_created, comment FROM EventTable", + -1, out stmt); + assert(res == Sqlite.OK); + + Gee.ArrayList<EventRow?> event_rows = new Gee.ArrayList<EventRow?>(); + for (;;) { + res = stmt.step(); + if (res == Sqlite.DONE) { + break; + } else if (res != Sqlite.ROW) { + fatal("get_events", res); + + break; + } + + EventRow row = new EventRow(); + + row.event_id = EventID(stmt.column_int64(0)); + row.name = stmt.column_text(1); + row.primary_source_id = source_id_upgrade(stmt.column_int64(2), stmt.column_text(3)); + row.time_created = (time_t) stmt.column_int64(4); + row.comment = stmt.column_text(5); + + event_rows.add(row); + } + + return event_rows; + } + + public bool rename(EventID event_id, string? name) { + return update_text_by_id(event_id.id, "name", name != null ? name : ""); + } + + public string? get_name(EventID event_id) { + Sqlite.Statement stmt; + if (!select_by_id(event_id.id, "name", out stmt)) + return null; + + string name = stmt.column_text(0); + + return (name != null && name.length > 0) ? name : null; + } + + public string? get_primary_source_id(EventID event_id) { + Sqlite.Statement stmt; + if (!select_by_id(event_id.id, "primary_source_id", out stmt)) + return null; + + return stmt.column_text(0); + } + + public bool set_primary_source_id(EventID event_id, string primary_source_id) { + return update_text_by_id(event_id.id, "primary_source_id", primary_source_id); + } + + public time_t get_time_created(EventID event_id) { + Sqlite.Statement stmt; + if (!select_by_id(event_id.id, "time_created", out stmt)) + return 0; + + return (time_t) stmt.column_int64(0); + } + + public bool set_comment(EventID event_id, string new_comment) { + return update_text_by_id(event_id.id, "comment", new_comment != null ? new_comment : ""); + } + +} + + diff --git a/src/db/PhotoTable.vala b/src/db/PhotoTable.vala new file mode 100644 index 0000000..9891fe6 --- /dev/null +++ b/src/db/PhotoTable.vala @@ -0,0 +1,1245 @@ +/* Copyright 2011-2014 Yorba Foundation + * + * This software is licensed under the GNU Lesser General Public License + * (version 2.1 or later). See the COPYING file in this distribution. + */ + +public struct PhotoID { + public const int64 INVALID = -1; + + public int64 id; + + public PhotoID(int64 id = INVALID) { + this.id = id; + } + + public bool is_invalid() { + return (id == INVALID); + } + + public bool is_valid() { + return (id != INVALID); + } + + public uint hash() { + return int64_hash(id); + } + + public static bool equal(void *a, void *b) { + return ((PhotoID *) a)->id == ((PhotoID *) b)->id; + } + + public static string upgrade_photo_id_to_source_id(PhotoID photo_id) { + return ("%s%016" + int64.FORMAT_MODIFIER + "x").printf(Photo.TYPENAME, photo_id.id); + } +} + +public struct ImportID { + public const int64 INVALID = 0; + + public int64 id; + + public ImportID(int64 id = INVALID) { + this.id = id; + } + + public static ImportID generate() { + TimeVal timestamp = TimeVal(); + timestamp.get_current_time(); + int64 id = timestamp.tv_sec; + + return ImportID(id); + } + + public bool is_invalid() { + return (id == INVALID); + } + + public bool is_valid() { + return (id != INVALID); + } + + public static int compare_func(ImportID? a, ImportID? b) { + assert (a != null && b != null); + return (int) (a.id - b.id); + } + + public static int64 comparator(void *a, void *b) { + return ((ImportID *) a)->id - ((ImportID *) b)->id; + } +} + +public class PhotoRow { + public PhotoID photo_id; + public BackingPhotoRow master; + public time_t exposure_time; + public ImportID import_id; + public EventID event_id; + public Orientation orientation; + public Gee.HashMap<string, KeyValueMap>? transformations; + public string md5; + public string thumbnail_md5; + public string exif_md5; + public time_t time_created; + public uint64 flags; + public Rating rating; + public string title; + public string comment; + public string? backlinks; + public time_t time_reimported; + public BackingPhotoID editable_id; + public bool metadata_dirty; + + // Currently selected developer (RAW only) + public RawDeveloper developer; + + // Currently selected developer (RAW only) + public BackingPhotoID[] development_ids; + + + public PhotoRow() { + master = new BackingPhotoRow(); + editable_id = BackingPhotoID(); + development_ids = new BackingPhotoID[RawDeveloper.as_array().length]; + foreach (RawDeveloper d in RawDeveloper.as_array()) + development_ids[d] = BackingPhotoID(); + } +} + +public class PhotoTable : DatabaseTable { + private static PhotoTable instance = null; + + private PhotoTable() { + Sqlite.Statement stmt; + int res = db.prepare_v2("CREATE TABLE IF NOT EXISTS PhotoTable (" + + "id INTEGER PRIMARY KEY, " + + "filename TEXT UNIQUE NOT NULL, " + + "width INTEGER, " + + "height INTEGER, " + + "filesize INTEGER, " + + "timestamp INTEGER, " + + "exposure_time INTEGER, " + + "orientation INTEGER, " + + "original_orientation INTEGER, " + + "import_id INTEGER, " + + "event_id INTEGER, " + + "transformations TEXT, " + + "md5 TEXT, " + + "thumbnail_md5 TEXT, " + + "exif_md5 TEXT, " + + "time_created INTEGER, " + + "flags INTEGER DEFAULT 0, " + + "rating INTEGER DEFAULT 0, " + + "file_format INTEGER DEFAULT 0, " + + "title TEXT, " + + "backlinks TEXT, " + + "time_reimported INTEGER, " + + "editable_id INTEGER DEFAULT -1, " + + "metadata_dirty INTEGER DEFAULT 0, " + + "developer TEXT, " + + "develop_shotwell_id INTEGER DEFAULT -1, " + + "develop_camera_id INTEGER DEFAULT -1, " + + "develop_embedded_id INTEGER DEFAULT -1, " + + "comment TEXT" + + ")", -1, out stmt); + assert(res == Sqlite.OK); + + res = stmt.step(); + if (res != Sqlite.DONE) + fatal("create photo table", res); + + // index on event_id + Sqlite.Statement stmt2; + int res2 = db.prepare_v2("CREATE INDEX IF NOT EXISTS PhotoEventIDIndex ON PhotoTable (event_id)", + -1, out stmt2); + assert(res2 == Sqlite.OK); + + res2 = stmt2.step(); + if (res2 != Sqlite.DONE) + fatal("create photo table", res2); + + set_table_name("PhotoTable"); + } + + public static PhotoTable get_instance() { + if (instance == null) + instance = new PhotoTable(); + + return instance; + } + + // PhotoRow.photo_id, event_id, master.orientation, flags, and time_created are ignored on input. + // All fields are set on exit with values stored in the database. editable_id field is ignored. + public PhotoID add(PhotoRow photo_row) { + Sqlite.Statement stmt; + int res = db.prepare_v2( + "INSERT INTO PhotoTable (filename, width, height, filesize, timestamp, exposure_time, " + + "orientation, original_orientation, import_id, event_id, md5, thumbnail_md5, " + + "exif_md5, time_created, file_format, title, rating, editable_id, developer, comment) " + + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", + -1, out stmt); + assert(res == Sqlite.OK); + + ulong time_created = now_sec(); + + res = stmt.bind_text(1, photo_row.master.filepath); + assert(res == Sqlite.OK); + res = stmt.bind_int(2, photo_row.master.dim.width); + assert(res == Sqlite.OK); + res = stmt.bind_int(3, photo_row.master.dim.height); + assert(res == Sqlite.OK); + res = stmt.bind_int64(4, photo_row.master.filesize); + assert(res == Sqlite.OK); + res = stmt.bind_int64(5, photo_row.master.timestamp); + assert(res == Sqlite.OK); + res = stmt.bind_int64(6, photo_row.exposure_time); + assert(res == Sqlite.OK); + res = stmt.bind_int(7, photo_row.master.original_orientation); + assert(res == Sqlite.OK); + res = stmt.bind_int(8, photo_row.master.original_orientation); + assert(res == Sqlite.OK); + res = stmt.bind_int64(9, photo_row.import_id.id); + assert(res == Sqlite.OK); + res = stmt.bind_int64(10, EventID.INVALID); + assert(res == Sqlite.OK); + res = stmt.bind_text(11, photo_row.md5); + assert(res == Sqlite.OK); + res = stmt.bind_text(12, photo_row.thumbnail_md5); + assert(res == Sqlite.OK); + res = stmt.bind_text(13, photo_row.exif_md5); + assert(res == Sqlite.OK); + res = stmt.bind_int64(14, time_created); + assert(res == Sqlite.OK); + res = stmt.bind_int(15, photo_row.master.file_format.serialize()); + assert(res == Sqlite.OK); + res = stmt.bind_text(16, photo_row.title); + assert(res == Sqlite.OK); + res = stmt.bind_int64(17, photo_row.rating.serialize()); + assert(res == Sqlite.OK); + res = stmt.bind_int64(18, BackingPhotoID.INVALID); + assert(res == Sqlite.OK); + res = stmt.bind_text(19, photo_row.developer.to_string()); + assert(res == Sqlite.OK); + res = stmt.bind_text(20, photo_row.comment); + assert(res == Sqlite.OK); + + res = stmt.step(); + if (res != Sqlite.DONE) { + if (res != Sqlite.CONSTRAINT) + fatal("add_photo", res); + + return PhotoID(); + } + + // fill in ignored fields with database values + photo_row.photo_id = PhotoID(db.last_insert_rowid()); + photo_row.orientation = photo_row.master.original_orientation; + photo_row.event_id = EventID(); + photo_row.time_created = (time_t) time_created; + photo_row.flags = 0; + + return photo_row.photo_id; + } + + // The only fields recognized in the PhotoRow are photo_id, dimensions, + // filesize, timestamp, exposure_time, original_orientation, file_format, + // and the md5 fields. When the method returns, time_reimported and master.orientation has been + // updated. editable_id is ignored. transformations are untouched; use + // remove_all_transformations() if necessary. + public void reimport(PhotoRow row) throws DatabaseError { + Sqlite.Statement stmt; + int res = db.prepare_v2( + "UPDATE PhotoTable SET width = ?, height = ?, filesize = ?, timestamp = ?, " + + "exposure_time = ?, orientation = ?, original_orientation = ?, md5 = ?, " + + "exif_md5 = ?, thumbnail_md5 = ?, file_format = ?, title = ?, time_reimported = ? " + + "WHERE id = ?", -1, out stmt); + assert(res == Sqlite.OK); + + time_t time_reimported = (time_t) now_sec(); + + res = stmt.bind_int(1, row.master.dim.width); + assert(res == Sqlite.OK); + res = stmt.bind_int(2, row.master.dim.height); + assert(res == Sqlite.OK); + res = stmt.bind_int64(3, row.master.filesize); + assert(res == Sqlite.OK); + res = stmt.bind_int64(4, row.master.timestamp); + assert(res == Sqlite.OK); + res = stmt.bind_int64(5, row.exposure_time); + assert(res == Sqlite.OK); + res = stmt.bind_int(6, row.master.original_orientation); + assert(res == Sqlite.OK); + res = stmt.bind_int(7, row.master.original_orientation); + assert(res == Sqlite.OK); + res = stmt.bind_text(8, row.md5); + assert(res == Sqlite.OK); + res = stmt.bind_text(9, row.exif_md5); + assert(res == Sqlite.OK); + res = stmt.bind_text(10, row.thumbnail_md5); + assert(res == Sqlite.OK); + res = stmt.bind_int(11, row.master.file_format.serialize()); + assert(res == Sqlite.OK); + res = stmt.bind_text(12, row.title); + assert(res == Sqlite.OK); + res = stmt.bind_int64(13, time_reimported); + assert(res == Sqlite.OK); + res = stmt.bind_int64(14, row.photo_id.id); + assert(res == Sqlite.OK); + + res = stmt.step(); + if (res != Sqlite.DONE) + throw_error("PhotoTable.reimport_master", res); + + row.time_reimported = time_reimported; + row.orientation = row.master.original_orientation; + } + + public bool master_exif_updated(PhotoID photoID, int64 filesize, long timestamp, + string md5, string? exif_md5, string? thumbnail_md5, PhotoRow row) { + Sqlite.Statement stmt; + int res = db.prepare_v2( + "UPDATE PhotoTable SET filesize = ?, timestamp = ?, md5 = ?, exif_md5 = ?," + + "thumbnail_md5 =? WHERE id = ?", -1, out stmt); + assert(res == Sqlite.OK); + + res = stmt.bind_int64(1, filesize); + assert(res == Sqlite.OK); + res = stmt.bind_int64(2, timestamp); + assert(res == Sqlite.OK); + res = stmt.bind_text(3, md5); + assert(res == Sqlite.OK); + res = stmt.bind_text(4, exif_md5); + assert(res == Sqlite.OK); + res = stmt.bind_text(5, thumbnail_md5); + assert(res == Sqlite.OK); + res = stmt.bind_int64(6, photoID.id); + assert(res == Sqlite.OK); + + res = stmt.step(); + if (res != Sqlite.DONE) { + if (res != Sqlite.CONSTRAINT) + fatal("write_update_photo", res); + + return false; + } + + row.master.filesize = filesize; + row.master.timestamp = timestamp; + row.md5 = md5; + row.exif_md5 = exif_md5; + row.thumbnail_md5 = thumbnail_md5; + + return true; + } + + // Force corrupted orientations to a safe value. + // + // In previous versions of Shotwell, this field could be written to + // the DB as a zero due to Vala 0.14 breaking the way it handled + // objects passed as 'ref' arguments to methods. + // + // For further details, please see http://redmine.yorba.org/issues/4354 and + // https://bugzilla.gnome.org/show_bug.cgi?id=663818 . + private void validate_orientation(PhotoRow row) { + if ((row.orientation < Orientation.MIN) || + (row.orientation > Orientation.MAX)) { + // orientation was corrupted; set it to top left. + set_orientation(row.photo_id, Orientation.MIN); + row.orientation = Orientation.MIN; + } + } + + public PhotoRow? get_row(PhotoID photo_id) { + Sqlite.Statement stmt; + int res = db.prepare_v2( + "SELECT filename, width, height, filesize, timestamp, exposure_time, orientation, " + + "original_orientation, import_id, event_id, transformations, md5, thumbnail_md5, " + + "exif_md5, time_created, flags, rating, file_format, title, backlinks, " + + "time_reimported, editable_id, metadata_dirty, developer, develop_shotwell_id, " + + "develop_camera_id, develop_embedded_id, comment " + + "FROM PhotoTable WHERE id=?", + -1, out stmt); + assert(res == Sqlite.OK); + + res = stmt.bind_int64(1, photo_id.id); + assert(res == Sqlite.OK); + + if (stmt.step() != Sqlite.ROW) + return null; + + PhotoRow row = new PhotoRow(); + row.photo_id = photo_id; + row.master.filepath = stmt.column_text(0); + row.master.dim = Dimensions(stmt.column_int(1), stmt.column_int(2)); + row.master.filesize = stmt.column_int64(3); + row.master.timestamp = (time_t) stmt.column_int64(4); + row.exposure_time = (time_t) stmt.column_int64(5); + row.orientation = (Orientation) stmt.column_int(6); + row.master.original_orientation = (Orientation) stmt.column_int(7); + row.import_id.id = stmt.column_int64(8); + row.event_id.id = stmt.column_int64(9); + row.transformations = marshall_all_transformations(stmt.column_text(10)); + row.md5 = stmt.column_text(11); + row.thumbnail_md5 = stmt.column_text(12); + row.exif_md5 = stmt.column_text(13); + row.time_created = (time_t) stmt.column_int64(14); + row.flags = stmt.column_int64(15); + row.rating = Rating.unserialize(stmt.column_int(16)); + row.master.file_format = PhotoFileFormat.unserialize(stmt.column_int(17)); + row.title = stmt.column_text(18); + row.backlinks = stmt.column_text(19); + row.time_reimported = (time_t) stmt.column_int64(20); + row.editable_id = BackingPhotoID(stmt.column_int64(21)); + row.metadata_dirty = stmt.column_int(22) != 0; + row.developer = stmt.column_text(23) != null ? RawDeveloper.from_string(stmt.column_text(23)) : + RawDeveloper.CAMERA; + row.development_ids[RawDeveloper.SHOTWELL] = BackingPhotoID(stmt.column_int64(24)); + row.development_ids[RawDeveloper.CAMERA] = BackingPhotoID(stmt.column_int64(25)); + row.development_ids[RawDeveloper.EMBEDDED] = BackingPhotoID(stmt.column_int64(26)); + row.comment = stmt.column_text(27); + + return row; + } + + public Gee.ArrayList<PhotoRow?> get_all() { + Sqlite.Statement stmt; + int res = db.prepare_v2( + "SELECT id, filename, width, height, filesize, timestamp, exposure_time, orientation, " + + "original_orientation, import_id, event_id, transformations, md5, thumbnail_md5, " + + "exif_md5, time_created, flags, rating, file_format, title, backlinks, time_reimported, " + + "editable_id, metadata_dirty, developer, develop_shotwell_id, develop_camera_id, " + + "develop_embedded_id, comment FROM PhotoTable", + -1, out stmt); + assert(res == Sqlite.OK); + + Gee.ArrayList<PhotoRow?> all = new Gee.ArrayList<PhotoRow?>(); + + while ((res = stmt.step()) == Sqlite.ROW) { + PhotoRow row = new PhotoRow(); + row.photo_id.id = stmt.column_int64(0); + row.master.filepath = stmt.column_text(1); + row.master.dim = Dimensions(stmt.column_int(2), stmt.column_int(3)); + row.master.filesize = stmt.column_int64(4); + row.master.timestamp = (time_t) stmt.column_int64(5); + row.exposure_time = (time_t) stmt.column_int64(6); + row.orientation = (Orientation) stmt.column_int(7); + row.master.original_orientation = (Orientation) stmt.column_int(8); + row.import_id.id = stmt.column_int64(9); + row.event_id.id = stmt.column_int64(10); + row.transformations = marshall_all_transformations(stmt.column_text(11)); + row.md5 = stmt.column_text(12); + row.thumbnail_md5 = stmt.column_text(13); + row.exif_md5 = stmt.column_text(14); + row.time_created = (time_t) stmt.column_int64(15); + row.flags = stmt.column_int64(16); + row.rating = Rating.unserialize(stmt.column_int(17)); + row.master.file_format = PhotoFileFormat.unserialize(stmt.column_int(18)); + row.title = stmt.column_text(19); + row.backlinks = stmt.column_text(20); + row.time_reimported = (time_t) stmt.column_int64(21); + row.editable_id = BackingPhotoID(stmt.column_int64(22)); + row.metadata_dirty = stmt.column_int(23) != 0; + row.developer = stmt.column_text(24) != null ? RawDeveloper.from_string(stmt.column_text(24)) : + RawDeveloper.CAMERA; + row.development_ids[RawDeveloper.SHOTWELL] = BackingPhotoID(stmt.column_int64(25)); + row.development_ids[RawDeveloper.CAMERA] = BackingPhotoID(stmt.column_int64(26)); + row.development_ids[RawDeveloper.EMBEDDED] = BackingPhotoID(stmt.column_int64(27)); + row.comment = stmt.column_text(28); + + validate_orientation(row); + + all.add(row); + } + + return all; + } + + // Create a duplicate of the specified row. A new byte-for-byte duplicate (including filesystem + // metadata) of PhotoID's file needs to back this duplicate and its editable (if exists). + public PhotoID duplicate(PhotoID photo_id, string new_filename, BackingPhotoID editable_id, + BackingPhotoID develop_shotwell, BackingPhotoID develop_camera_id, + BackingPhotoID develop_embedded_id) { + // get a copy of the original row, duplicating most (but not all) of it + PhotoRow original = get_row(photo_id); + + Sqlite.Statement stmt; + int res = db.prepare_v2("INSERT INTO PhotoTable (filename, width, height, filesize, " + + "timestamp, exposure_time, orientation, original_orientation, import_id, event_id, " + + "transformations, md5, thumbnail_md5, exif_md5, time_created, flags, rating, " + + "file_format, title, editable_id, developer, develop_shotwell_id, develop_camera_id, " + + "develop_embedded_id, comment) " + + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", + -1, out stmt); + assert(res == Sqlite.OK); + + res = stmt.bind_text(1, new_filename); + assert(res == Sqlite.OK); + res = stmt.bind_int(2, original.master.dim.width); + assert(res == Sqlite.OK); + res = stmt.bind_int(3, original.master.dim.height); + assert(res == Sqlite.OK); + res = stmt.bind_int64(4, original.master.filesize); + assert(res == Sqlite.OK); + res = stmt.bind_int64(5, original.master.timestamp); + assert(res == Sqlite.OK); + res = stmt.bind_int64(6, original.exposure_time); + assert(res == Sqlite.OK); + res = stmt.bind_int(7, original.orientation); + assert(res == Sqlite.OK); + res = stmt.bind_int(8, original.master.original_orientation); + assert(res == Sqlite.OK); + res = stmt.bind_int64(9, original.import_id.id); + assert(res == Sqlite.OK); + res = stmt.bind_int64(10, original.event_id.id); + assert(res == Sqlite.OK); + res = stmt.bind_text(11, unmarshall_all_transformations(original.transformations)); + assert(res == Sqlite.OK); + res = stmt.bind_text(12, original.md5); + assert(res == Sqlite.OK); + res = stmt.bind_text(13, original.thumbnail_md5); + assert(res == Sqlite.OK); + res = stmt.bind_text(14, original.exif_md5); + assert(res == Sqlite.OK); + res = stmt.bind_int64(15, now_sec()); + assert(res == Sqlite.OK); + res = stmt.bind_int64(16, (int64) original.flags); + assert(res == Sqlite.OK); + res = stmt.bind_int64(17, original.rating.serialize()); + assert(res == Sqlite.OK); + res = stmt.bind_int(18, original.master.file_format.serialize()); + assert(res == Sqlite.OK); + res = stmt.bind_text(19, original.title); + assert(res == Sqlite.OK); + res = stmt.bind_int64(20, editable_id.id); + assert(res == Sqlite.OK); + + res = stmt.bind_text(21, original.developer.to_string()); + assert(res == Sqlite.OK); + res = stmt.bind_int64(22, develop_shotwell.id); + assert(res == Sqlite.OK); + res = stmt.bind_int64(23, develop_camera_id.id); + assert(res == Sqlite.OK); + res = stmt.bind_int64(24, develop_embedded_id.id); + assert(res == Sqlite.OK); + res = stmt.bind_text(25, original.comment); + assert(res == Sqlite.OK); + + res = stmt.step(); + if (res != Sqlite.DONE) { + if (res != Sqlite.CONSTRAINT) + fatal("duplicate", res); + + return PhotoID(); + } + + return PhotoID(db.last_insert_rowid()); + } + + public bool set_title(PhotoID photo_id, string? new_title) { + return update_text_by_id(photo_id.id, "title", new_title != null ? new_title : ""); + } + + public bool set_comment(PhotoID photo_id, string? new_comment) { + return update_text_by_id(photo_id.id, "comment", new_comment != null ? new_comment : ""); + } + + public void set_filepath(PhotoID photo_id, string filepath) throws DatabaseError { + update_text_by_id_2(photo_id.id, "filename", filepath); + } + + public void update_timestamp(PhotoID photo_id, time_t timestamp) throws DatabaseError { + update_int64_by_id_2(photo_id.id, "timestamp", timestamp); + } + + public bool set_exposure_time(PhotoID photo_id, time_t time) { + return update_int64_by_id(photo_id.id, "exposure_time", (int64) time); + } + + public void set_import_id(PhotoID photo_id, ImportID import_id) throws DatabaseError { + update_int64_by_id_2(photo_id.id, "import_id", import_id.id); + } + + public bool remove_by_file(File file) { + Sqlite.Statement stmt; + int res = db.prepare_v2("DELETE FROM PhotoTable WHERE filename=?", -1, out stmt); + assert(res == Sqlite.OK); + + res = stmt.bind_text(1, file.get_path()); + assert(res == Sqlite.OK); + + res = stmt.step(); + if (res != Sqlite.DONE) { + warning("remove", res); + + return false; + } + + return true; + } + + public void remove(PhotoID photo_id) throws DatabaseError { + delete_by_id(photo_id.id); + } + + public Gee.ArrayList<PhotoID?> get_photos() { + Sqlite.Statement stmt; + int res = db.prepare_v2("SELECT id FROM PhotoTable", -1, out stmt); + assert(res == Sqlite.OK); + + Gee.ArrayList<PhotoID?> photo_ids = new Gee.ArrayList<PhotoID?>(); + for (;;) { + res = stmt.step(); + if (res == Sqlite.DONE) { + break; + } else if (res != Sqlite.ROW) { + fatal("get_photos", res); + + break; + } + + photo_ids.add(PhotoID(stmt.column_int64(0))); + } + + return photo_ids; + } + + public bool set_orientation(PhotoID photo_id, Orientation orientation) { + return update_int_by_id(photo_id.id, "orientation", (int) orientation); + } + + public bool replace_flags(PhotoID photo_id, uint64 flags) { + return update_int64_by_id(photo_id.id, "flags", (int64) flags); + } + + public bool set_rating(PhotoID photo_id, Rating rating) { + return update_int_by_id(photo_id.id, "rating", rating.serialize()); + } + + public int get_event_photo_count(EventID event_id) { + Sqlite.Statement stmt; + int res = db.prepare_v2("SELECT id FROM PhotoTable WHERE event_id = ?", -1, out stmt); + assert(res == Sqlite.OK); + + res = stmt.bind_int64(1, event_id.id); + assert(res == Sqlite.OK); + + int count = 0; + for (;;) { + res = stmt.step(); + if (res == Sqlite.DONE) { + break; + } else if (res != Sqlite.ROW) { + fatal("get_event_photo_count", res); + + break; + } + + count++; + } + + return count; + } + + public Gee.ArrayList<string> get_event_source_ids(EventID event_id) { + Sqlite.Statement stmt; + int res = db.prepare_v2("SELECT id FROM PhotoTable WHERE event_id = ?", -1, out stmt); + assert(res == Sqlite.OK); + + res = stmt.bind_int64(1, event_id.id); + assert(res == Sqlite.OK); + + Gee.ArrayList<string> result = new Gee.ArrayList<string>(); + for(;;) { + res = stmt.step(); + if (res == Sqlite.DONE) { + break; + } else if (res != Sqlite.ROW) { + fatal("get_event_source_ids", res); + + break; + } + + result.add(PhotoID.upgrade_photo_id_to_source_id(PhotoID(stmt.column_int64(0)))); + } + + return result; + } + + public bool event_has_photos(EventID event_id) { + Sqlite.Statement stmt; + int res = db.prepare_v2("SELECT id FROM PhotoTable WHERE event_id = ? LIMIT 1", -1, out stmt); + assert(res == Sqlite.OK); + + res = stmt.bind_int64(1, event_id.id); + assert(res == Sqlite.OK); + + res = stmt.step(); + if (res == Sqlite.DONE) { + return false; + } else if (res != Sqlite.ROW) { + fatal("event_has_photos", res); + + return false; + } + + return true; + } + + public bool drop_event(EventID event_id) { + Sqlite.Statement stmt; + int res = db.prepare_v2("UPDATE PhotoTable SET event_id = ? WHERE event_id = ?", -1, out stmt); + assert(res == Sqlite.OK); + + res = stmt.bind_int64(1, EventID.INVALID); + assert(res == Sqlite.OK); + res = stmt.bind_int64(2, event_id.id); + assert(res == Sqlite.OK); + + res = stmt.step(); + if (res != Sqlite.DONE) { + fatal("drop_event", res); + + return false; + } + + return true; + } + + public bool set_event(PhotoID photo_id, EventID event_id) { + return update_int64_by_id(photo_id.id, "event_id", event_id.id); + } + + private string? get_raw_transformations(PhotoID photo_id) { + Sqlite.Statement stmt; + if (!select_by_id(photo_id.id, "transformations", out stmt)) + return null; + + string trans = stmt.column_text(0); + if (trans == null || trans.length == 0) + return null; + + return trans; + } + + private bool set_raw_transformations(PhotoID photo_id, string trans) { + return update_text_by_id(photo_id.id, "transformations", trans); + } + + public bool set_transformation_state(PhotoID photo_id, Orientation orientation, + Gee.HashMap<string, KeyValueMap>? transformations) { + Sqlite.Statement stmt; + int res = db.prepare_v2("UPDATE PhotoTable SET orientation = ?, transformations = ? WHERE id = ?", + -1, out stmt); + assert(res == Sqlite.OK); + + res = stmt.bind_int(1, orientation); + assert(res == Sqlite.OK); + res = stmt.bind_text(2, unmarshall_all_transformations(transformations)); + assert(res == Sqlite.OK); + res = stmt.bind_int64(3, photo_id.id); + assert(res == Sqlite.OK); + + res = stmt.step(); + if (res != Sqlite.DONE) { + fatal("set_transformation_state", res); + + return false; + } + + return true; + } + + public static Gee.HashMap<string, KeyValueMap>? marshall_all_transformations(string? trans) { + if (trans == null || trans.length == 0) + return null; + + try { + KeyFile keyfile = new KeyFile(); + if (!keyfile.load_from_data(trans, trans.length, KeyFileFlags.NONE)) + return null; + + Gee.HashMap<string, KeyValueMap> map = new Gee.HashMap<string, KeyValueMap>(); + + string[] objects = keyfile.get_groups(); + foreach (string object in objects) { + string[] keys = keyfile.get_keys(object); + if (keys == null || keys.length == 0) + continue; + + KeyValueMap key_map = new KeyValueMap(object); + for (int ctr = 0; ctr < keys.length; ctr++) + key_map.set_string(keys[ctr], keyfile.get_string(object, keys[ctr])); + + map.set(object, key_map); + } + + return map; + } catch (Error err) { + error("%s", err.message); + } + } + + public static string? unmarshall_all_transformations(Gee.HashMap<string, KeyValueMap>? transformations) { + if (transformations == null || transformations.keys.size == 0) + return null; + + KeyFile keyfile = new KeyFile(); + + foreach (string object in transformations.keys) { + KeyValueMap map = transformations.get(object); + + foreach (string key in map.get_keys()) { + string? value = map.get_string(key, null); + assert(value != null); + + keyfile.set_string(object, key, value); + } + } + + size_t length; + string unmarshalled = keyfile.to_data(out length); + assert(unmarshalled != null); + assert(unmarshalled.length > 0); + + return unmarshalled; + } + + public bool set_transformation(PhotoID photo_id, KeyValueMap map) { + string trans = get_raw_transformations(photo_id); + + try { + KeyFile keyfile = new KeyFile(); + if (trans != null) { + if (!keyfile.load_from_data(trans, trans.length, KeyFileFlags.NONE)) + return false; + } + + Gee.Set<string> keys = map.get_keys(); + foreach (string key in keys) { + string value = map.get_string(key, null); + assert(value != null); + + keyfile.set_string(map.get_group(), key, value); + } + + size_t length; + trans = keyfile.to_data(out length); + assert(trans != null); + assert(trans.length > 0); + } catch (Error err) { + error("%s", err.message); + } + + return set_raw_transformations(photo_id, trans); + } + + public bool remove_transformation(PhotoID photo_id, string object) { + string trans = get_raw_transformations(photo_id); + if (trans == null) + return true; + + try { + KeyFile keyfile = new KeyFile(); + if (!keyfile.load_from_data(trans, trans.length, KeyFileFlags.NONE)) + return false; + + if (!keyfile.has_group(object)) + return true; + + keyfile.remove_group(object); + + size_t length; + trans = keyfile.to_data(out length); + assert(trans != null); + } catch (Error err) { + error("%s", err.message); + } + + return set_raw_transformations(photo_id, trans); + } + + public bool remove_all_transformations(PhotoID photo_id) { + if (get_raw_transformations(photo_id) == null) + return false; + + return update_text_by_id(photo_id.id, "transformations", ""); + } + + // Use PhotoFileFormat.UNKNOWN if not to search for matching file format; it's only used if + // searching for MD5 duplicates. + private Sqlite.Statement get_duplicate_stmt(File? file, string? thumbnail_md5, string? md5, + PhotoFileFormat file_format) { + assert(file != null || thumbnail_md5 != null || md5 != null); + + string sql = "SELECT id FROM PhotoTable WHERE"; + bool first = true; + + if (file != null) { + sql += " filename=?"; + first = false; + } + + if (thumbnail_md5 != null || md5 != null) { + if (first) + sql += " (("; + else + sql += " OR (("; + first = false; + + if (thumbnail_md5 != null) + sql += " thumbnail_md5=?"; + + if (md5 != null) { + if (thumbnail_md5 == null) + sql += " md5=?"; + else + sql += " OR md5=?"; + } + + sql += ")"; + + if (file_format != PhotoFileFormat.UNKNOWN) + sql += " AND file_format=?"; + + sql += ")"; + } + + Sqlite.Statement stmt; + int res = db.prepare_v2(sql, -1, out stmt); + assert(res == Sqlite.OK); + + int col = 1; + + if (file != null) { + res = stmt.bind_text(col++, file.get_path()); + assert(res == Sqlite.OK); + } + + if (thumbnail_md5 != null) { + res = stmt.bind_text(col++, thumbnail_md5); + assert(res == Sqlite.OK); + } + + if (md5 != null) { + res = stmt.bind_text(col++, md5); + assert(res == Sqlite.OK); + } + + if ((thumbnail_md5 != null || md5 != null) && file_format != PhotoFileFormat.UNKNOWN) { + res = stmt.bind_int(col++, file_format.serialize()); + assert(res == Sqlite.OK); + } + + return stmt; + } + + public bool has_duplicate(File? file, string? thumbnail_md5, string? md5, PhotoFileFormat file_format) { + Sqlite.Statement stmt = get_duplicate_stmt(file, thumbnail_md5, md5, file_format); + int res = stmt.step(); + + if (res == Sqlite.DONE) { + // not found + return false; + } else if (res == Sqlite.ROW) { + // at least one found + return true; + } else { + fatal("has_duplicate", res); + + return false; + } + } + + public PhotoID[] get_duplicate_ids(File? file, string? thumbnail_md5, string? md5, + PhotoFileFormat file_format) { + Sqlite.Statement stmt = get_duplicate_stmt(file, thumbnail_md5, md5, file_format); + + PhotoID[] ids = new PhotoID[0]; + + int res = stmt.step(); + while (res == Sqlite.ROW) { + ids += PhotoID(stmt.column_int64(0)); + res = stmt.step(); + } + + return ids; + } + + public void update_backlinks(PhotoID photo_id, string? backlinks) throws DatabaseError { + update_text_by_id_2(photo_id.id, "backlinks", backlinks != null ? backlinks : ""); + } + + public void attach_editable(PhotoRow row, BackingPhotoID editable_id) throws DatabaseError { + update_int64_by_id_2(row.photo_id.id, "editable_id", editable_id.id); + + row.editable_id = editable_id; + } + + public void detach_editable(PhotoRow row) throws DatabaseError { + update_int64_by_id_2(row.photo_id.id, "editable_id", BackingPhotoID.INVALID); + + row.editable_id = BackingPhotoID(); + } + + public void set_metadata_dirty(PhotoID photo_id, bool dirty) throws DatabaseError { + update_int_by_id_2(photo_id.id, "metadata_dirty", dirty ? 1 : 0); + } + + public void update_raw_development(PhotoRow row, RawDeveloper rd, BackingPhotoID backing_photo_id) + throws DatabaseError { + + string col; + switch (rd) { + case RawDeveloper.SHOTWELL: + col = "develop_shotwell_id"; + break; + + case RawDeveloper.CAMERA: + col = "develop_camera_id"; + break; + + case RawDeveloper.EMBEDDED: + col = "develop_embedded_id"; + break; + + default: + assert_not_reached(); + } + + row.development_ids[rd] = backing_photo_id; + update_int64_by_id_2(row.photo_id.id, col, backing_photo_id.id); + + if (backing_photo_id.id != BackingPhotoID.INVALID) + update_text_by_id_2(row.photo_id.id, "developer", rd.to_string()); + } + + public void remove_development(PhotoRow row, RawDeveloper rd) throws DatabaseError { + update_raw_development(row, rd, BackingPhotoID()); + } + +} + +// +// BackingPhotoTable +// +// BackingPhotoTable is designed to hold any number of alternative backing photos +// for a Photo. In the first implementation it was designed for editable photos (Edit with +// External Editor), but if other such alternates are needed, this is where to store them. +// +// Note that no transformations are held here. +// + +public struct BackingPhotoID { + public const int64 INVALID = -1; + + public int64 id; + + public BackingPhotoID(int64 id = INVALID) { + this.id = id; + } + + public bool is_invalid() { + return (id == INVALID); + } + + public bool is_valid() { + return (id != INVALID); + } +} + +public class BackingPhotoRow { + public BackingPhotoID id; + public time_t time_created; + public string? filepath = null; + public int64 filesize; + public time_t timestamp; + public PhotoFileFormat file_format; + public Dimensions dim; + public Orientation original_orientation; + + public bool matches_file_info(FileInfo info) { + if (filesize != info.get_size()) + return false; + + return timestamp == info.get_modification_time().tv_sec; + } + + public bool is_touched(FileInfo info) { + if (filesize != info.get_size()) + return false; + + return timestamp != info.get_modification_time().tv_sec; + } + + // Copies another backing photo row into this one. + public void copy_from(BackingPhotoRow from) { + id = from.id; + time_created = from.time_created; + filepath = from.filepath; + filesize = from.filesize; + timestamp = from.timestamp; + file_format = from.file_format; + dim = from.dim; + original_orientation = from.original_orientation; + } +} + +public class BackingPhotoTable : DatabaseTable { + private static BackingPhotoTable instance = null; + + private BackingPhotoTable() { + set_table_name("BackingPhotoTable"); + + Sqlite.Statement stmt; + int res = db.prepare_v2("CREATE TABLE IF NOT EXISTS " + + "BackingPhotoTable " + + "(" + + "id INTEGER PRIMARY KEY, " + + "filepath TEXT UNIQUE NOT NULL, " + + "timestamp INTEGER, " + + "filesize INTEGER, " + + "width INTEGER, " + + "height INTEGER, " + + "original_orientation INTEGER, " + + "file_format INTEGER, " + + "time_created INTEGER " + + ")", -1, out stmt); + assert(res == Sqlite.OK); + + res = stmt.step(); + if (res != Sqlite.DONE) + fatal("create PhotoBackingTable", res); + } + + public static BackingPhotoTable get_instance() { + if (instance == null) + instance = new BackingPhotoTable(); + + return instance; + } + + public void add(BackingPhotoRow state) throws DatabaseError { + Sqlite.Statement stmt; + int res = db.prepare_v2("INSERT INTO BackingPhotoTable " + + "(filepath, timestamp, filesize, width, height, original_orientation, " + + "file_format, time_created) " + + "VALUES (?, ?, ?, ?, ?, ?, ?, ?)", + -1, out stmt); + assert(res == Sqlite.OK); + + time_t time_created = (time_t) now_sec(); + + res = stmt.bind_text(1, state.filepath); + assert(res == Sqlite.OK); + res = stmt.bind_int64(2, state.timestamp); + assert(res == Sqlite.OK); + res = stmt.bind_int64(3, state.filesize); + assert(res == Sqlite.OK); + res = stmt.bind_int(4, state.dim.width); + assert(res == Sqlite.OK); + res = stmt.bind_int(5, state.dim.height); + assert(res == Sqlite.OK); + res = stmt.bind_int(6, state.original_orientation); + assert(res == Sqlite.OK); + res = stmt.bind_int(7, state.file_format.serialize()); + assert(res == Sqlite.OK); + res = stmt.bind_int64(8, (int64) time_created); + assert(res == Sqlite.OK); + + res = stmt.step(); + if (res != Sqlite.DONE) + throw_error("PhotoBackingTable.add", res); + + state.id = BackingPhotoID(db.last_insert_rowid()); + state.time_created = time_created; + } + + public BackingPhotoRow? fetch(BackingPhotoID id) throws DatabaseError { + Sqlite.Statement stmt; + int res = db.prepare_v2("SELECT filepath, timestamp, filesize, width, height, " + + "original_orientation, file_format, time_created FROM BackingPhotoTable WHERE id=?", + -1, out stmt); + assert(res == Sqlite.OK); + + res = stmt.bind_int64(1, id.id); + assert(res == Sqlite.OK); + + res = stmt.step(); + if (res == Sqlite.DONE) + return null; + else if (res != Sqlite.ROW) + throw_error("BackingPhotoTable.fetch_for_photo", res); + + BackingPhotoRow row = new BackingPhotoRow(); + row.id = id; + row.filepath = stmt.column_text(0); + row.timestamp = (time_t) stmt.column_int64(1); + row.filesize = stmt.column_int64(2); + row.dim = Dimensions(stmt.column_int(3), stmt.column_int(4)); + row.original_orientation = (Orientation) stmt.column_int(5); + row.file_format = PhotoFileFormat.unserialize(stmt.column_int(6)); + row.time_created = (time_t) stmt.column_int64(7); + + return row; + } + + // Everything but filepath is updated. + public void update(BackingPhotoRow row) throws DatabaseError { + Sqlite.Statement stmt; + int res = db.prepare_v2("UPDATE BackingPhotoTable SET timestamp=?, filesize=?, " + + "width=?, height=?, original_orientation=?, file_format=? " + + "WHERE id=?", + -1, out stmt); + assert(res == Sqlite.OK); + + res = stmt.bind_int64(1, row.timestamp); + assert(res == Sqlite.OK); + res = stmt.bind_int64(2, row.filesize); + assert(res == Sqlite.OK); + res = stmt.bind_int(3, row.dim.width); + assert(res == Sqlite.OK); + res = stmt.bind_int(4, row.dim.height); + assert(res == Sqlite.OK); + res = stmt.bind_int(5, row.original_orientation); + assert(res == Sqlite.OK); + res = stmt.bind_int(6, row.file_format.serialize()); + assert(res == Sqlite.OK); + res = stmt.bind_int64(7, row.id.id); + assert(res == Sqlite.OK); + + res = stmt.step(); + if (res != Sqlite.DONE) + throw_error("BackingPhotoTable.update", res); + } + + public void update_attributes(BackingPhotoID id, time_t timestamp, int64 filesize) throws DatabaseError { + Sqlite.Statement stmt; + int res = db.prepare_v2("UPDATE BackingPhotoTable SET timestamp=?, filesize=? WHERE id=?", + -1, out stmt); + assert(res == Sqlite.OK); + + res = stmt.bind_int64(1, timestamp); + assert(res == Sqlite.OK); + res = stmt.bind_int64(2, filesize); + assert(res == Sqlite.OK); + res = stmt.bind_int64(3, id.id); + assert(res == Sqlite.OK); + + res = stmt.step(); + if (res != Sqlite.DONE) + throw_error("BackingPhotoTable.update_attributes", res); + } + + public void remove(BackingPhotoID backing_id) throws DatabaseError { + delete_by_id(backing_id.id); + } + + public void set_filepath(BackingPhotoID id, string filepath) throws DatabaseError { + update_text_by_id_2(id.id, "filepath", filepath); + } + + public void update_timestamp(BackingPhotoID id, time_t timestamp) throws DatabaseError { + update_int64_by_id_2(id.id, "timestamp", timestamp); + } +} + diff --git a/src/db/SavedSearchDBTable.vala b/src/db/SavedSearchDBTable.vala new file mode 100644 index 0000000..d986038 --- /dev/null +++ b/src/db/SavedSearchDBTable.vala @@ -0,0 +1,641 @@ +/* Copyright 2011-2014 Yorba Foundation + * + * This software is licensed under the GNU Lesser General Public License + * (version 2.1 or later). See the COPYING file in this distribution. + */ + +public struct SavedSearchID { + public const int64 INVALID = -1; + + public int64 id; + + public SavedSearchID(int64 id = INVALID) { + this.id = id; + } + + public bool is_invalid() { + return (id == INVALID); + } + + public bool is_valid() { + return (id != INVALID); + } +} + +public class SavedSearchRow { + public SavedSearchID search_id; + + public string name; + public SearchOperator operator; + public Gee.List<SearchCondition> conditions; +} + +public class SavedSearchDBTable : DatabaseTable { + private static SavedSearchDBTable instance = null; + + private SavedSearchDBTable() { + set_table_name("SavedSearchDBTable"); + + // Create main search table. + Sqlite.Statement stmt; + int res = db.prepare_v2("CREATE TABLE IF NOT EXISTS " + + "SavedSearchDBTable " + + "(" + + "id INTEGER PRIMARY KEY, " + + "name TEXT UNIQUE NOT NULL, " + + "operator TEXT NOT NULL" + + ")", -1, out stmt); + assert(res == Sqlite.OK); + + res = stmt.step(); + if (res != Sqlite.DONE) + fatal("create SavedSearchDBTable", res); + + // Create search text table. + res = db.prepare_v2("CREATE TABLE IF NOT EXISTS " + + "SavedSearchDBTable_Text " + + "(" + + "id INTEGER PRIMARY KEY, " + + "search_id INTEGER NOT NULL, " + + "search_type TEXT NOT NULL, " + + "context TEXT NOT NULL, " + + "text TEXT" + + ")", -1, out stmt); + assert(res == Sqlite.OK); + + res = stmt.step(); + if (res != Sqlite.DONE) + fatal("create SavedSearchDBTable_Text", res); + + // Create search media type table. + res = db.prepare_v2("CREATE TABLE IF NOT EXISTS " + + "SavedSearchDBTable_MediaType " + + "(" + + "id INTEGER PRIMARY KEY, " + + "search_id INTEGER NOT NULL, " + + "search_type TEXT NOT NULL, " + + "context TEXT NOT NULL, " + + "type TEXT NOT_NULL" + + ")", -1, out stmt); + assert(res == Sqlite.OK); + + res = stmt.step(); + if (res != Sqlite.DONE) + fatal("create SavedSearchDBTable_MediaType", res); + + // Create flagged search table. + res = db.prepare_v2("CREATE TABLE IF NOT EXISTS " + + "SavedSearchDBTable_Flagged " + + "(" + + "id INTEGER PRIMARY KEY, " + + "search_id INTEGER NOT NULL, " + + "search_type TEXT NOT NULL, " + + "flag_state TEXT NOT NULL" + + ")", -1, out stmt); + assert(res == Sqlite.OK); + + res = stmt.step(); + if (res != Sqlite.DONE) + fatal("create SavedSearchDBTable_Flagged", res); + + // Create modified search table. + res = db.prepare_v2("CREATE TABLE IF NOT EXISTS " + + "SavedSearchDBTable_Modified " + + "(" + + "id INTEGER PRIMARY KEY, " + + "search_id INTEGER NOT NULL, " + + "search_type TEXT NOT NULL, " + + "context TEXT NOT NULL, " + + "modified_state TEXT NOT NULL" + + ")", -1, out stmt); + assert(res == Sqlite.OK); + + res = stmt.step(); + if (res != Sqlite.DONE) + fatal("create SavedSearchDBTable_Modified", res); + + // Create rating search table. + res = db.prepare_v2("CREATE TABLE IF NOT EXISTS " + + "SavedSearchDBTable_Rating " + + "(" + + "id INTEGER PRIMARY KEY, " + + "search_id INTEGER NOT NULL, " + + "search_type TEXT NOT NULL, " + + "rating INTEGER NOT_NULL, " + + "context TEXT NOT NULL" + + ")", -1, out stmt); + assert(res == Sqlite.OK); + + res = stmt.step(); + if (res != Sqlite.DONE) + fatal("create SavedSearchDBTable_Rating", res); + + // Create date search table. + res = db.prepare_v2("CREATE TABLE IF NOT EXISTS " + + "SavedSearchDBTable_Date " + + "(" + + "id INTEGER PRIMARY KEY, " + + "search_id INTEGER NOT NULL, " + + "search_type TEXT NOT NULL, " + + "context TEXT NOT NULL, " + + "date_one INTEGER NOT_NULL, " + + "date_two INTEGER NOT_NULL" + + ")", -1, out stmt); + assert(res == Sqlite.OK); + + res = stmt.step(); + if (res != Sqlite.DONE) + fatal("create SavedSearchDBTable_Rating", res); + + // Create indexes. + res = db.prepare_v2("CREATE INDEX IF NOT EXISTS " + + "SavedSearchDBTable_Text_Index " + + "ON SavedSearchDBTable_Text(search_id)", -1, out stmt); + assert(res == Sqlite.OK); + res = stmt.step(); + if (res != Sqlite.DONE) + fatal("create SavedSearchDBTable_Text_Index", res); + + res = db.prepare_v2("CREATE INDEX IF NOT EXISTS " + + "SavedSearchDBTable_MediaType_Index " + + "ON SavedSearchDBTable_MediaType(search_id)", -1, out stmt); + assert(res == Sqlite.OK); + res = stmt.step(); + if (res != Sqlite.DONE) + fatal("create SavedSearchDBTable_MediaType_Index", res); + + res = db.prepare_v2("CREATE INDEX IF NOT EXISTS " + + "SavedSearchDBTable_Flagged_Index " + + "ON SavedSearchDBTable_Flagged(search_id)", -1, out stmt); + assert(res == Sqlite.OK); + res = stmt.step(); + if (res != Sqlite.DONE) + fatal("create SavedSearchDBTable_Flagged_Index", res); + + res = db.prepare_v2("CREATE INDEX IF NOT EXISTS " + + "SavedSearchDBTable_Modified_Index " + + "ON SavedSearchDBTable_Modified(search_id)", -1, out stmt); + assert(res == Sqlite.OK); + res = stmt.step(); + if (res != Sqlite.DONE) + fatal("create SavedSearchDBTable_Modified_Index", res); + + res = db.prepare_v2("CREATE INDEX IF NOT EXISTS " + + "SavedSearchDBTable_Rating_Index " + + "ON SavedSearchDBTable_Rating(search_id)", -1, out stmt); + assert(res == Sqlite.OK); + res = stmt.step(); + if (res != Sqlite.DONE) + fatal("create SavedSearchDBTable_Rating_Index", res); + + res = db.prepare_v2("CREATE INDEX IF NOT EXISTS " + + "SavedSearchDBTable_Date_Index " + + "ON SavedSearchDBTable_Date(search_id)", -1, out stmt); + assert(res == Sqlite.OK); + res = stmt.step(); + if (res != Sqlite.DONE) + fatal("create SavedSearchDBTable_Date_Index", res); + } + + public static SavedSearchDBTable get_instance() { + if (instance == null) + instance = new SavedSearchDBTable(); + + return instance; + } + + public SavedSearchRow add(string name, SearchOperator operator, + Gee.ArrayList<SearchCondition> conditions) throws DatabaseError { + Sqlite.Statement stmt; + int res = db.prepare_v2("INSERT INTO SavedSearchDBTable (name, operator) VALUES (?, ?)", -1, + out stmt); + assert(res == Sqlite.OK); + + res = stmt.bind_text(1, name); + assert(res == Sqlite.OK); + res = stmt.bind_text(2, operator.to_string()); + assert(res == Sqlite.OK); + + res = stmt.step(); + if (res != Sqlite.DONE) + throw_error("SavedSearchDBTable.add", res); + + SavedSearchRow row = new SavedSearchRow(); + row.search_id = SavedSearchID(db.last_insert_rowid()); + row.name = name; + row.operator = operator; + row.conditions = conditions; + + foreach (SearchCondition sc in conditions) { + add_condition(row.search_id, sc); + } + + return row; + } + + private void add_condition(SavedSearchID id, SearchCondition condition) throws DatabaseError { + if (condition is SearchConditionText) { + SearchConditionText text = condition as SearchConditionText; + Sqlite.Statement stmt; + int res = db.prepare_v2("INSERT INTO SavedSearchDBTable_Text (search_id, search_type, context, " + + "text) VALUES (?, ?, ?, ?)", -1, + out stmt); + assert(res == Sqlite.OK); + + res = stmt.bind_int64(1, id.id); + assert(res == Sqlite.OK); + + res = stmt.bind_text(2, text.search_type.to_string()); + assert(res == Sqlite.OK); + + res = stmt.bind_text(3, text.context.to_string()); + assert(res == Sqlite.OK); + + res = stmt.bind_text(4, text.text); + assert(res == Sqlite.OK); + + res = stmt.step(); + if (res != Sqlite.DONE) + throw_error("SavedSearchDBTable_Text.add", res); + } else if (condition is SearchConditionMediaType) { + SearchConditionMediaType media_type = condition as SearchConditionMediaType; + Sqlite.Statement stmt; + int res = db.prepare_v2("INSERT INTO SavedSearchDBTable_MediaType (search_id, search_type, context, " + + "type) VALUES (?, ?, ?, ?)", -1, + out stmt); + assert(res == Sqlite.OK); + + res = stmt.bind_int64(1, id.id); + assert(res == Sqlite.OK); + + res = stmt.bind_text(2, media_type.search_type.to_string()); + assert(res == Sqlite.OK); + + res = stmt.bind_text(3, media_type.context.to_string()); + assert(res == Sqlite.OK); + + res = stmt.bind_text(4, media_type.media_type.to_string()); + assert(res == Sqlite.OK); + + res = stmt.step(); + if (res != Sqlite.DONE) + throw_error("SavedSearchDBTable_MediaType.add", res); + } else if (condition is SearchConditionFlagged) { + SearchConditionFlagged flag_state = condition as SearchConditionFlagged; + Sqlite.Statement stmt; + int res = db.prepare_v2("INSERT INTO SavedSearchDBTable_Flagged (search_id, search_type, " + + "flag_state) VALUES (?, ?, ?)", -1, + out stmt); + assert(res == Sqlite.OK); + + res = stmt.bind_int64(1, id.id); + assert(res == Sqlite.OK); + + res = stmt.bind_text(2, flag_state.search_type.to_string()); + assert(res == Sqlite.OK); + + res = stmt.bind_text(3, flag_state.state.to_string()); + assert(res == Sqlite.OK); + + res = stmt.step(); + if (res != Sqlite.DONE) + throw_error("SavedSearchDBTable_Flagged.add", res); + } else if (condition is SearchConditionModified) { + SearchConditionModified modified_state = condition as SearchConditionModified; + Sqlite.Statement stmt; + int res = db.prepare_v2("INSERT INTO SavedSearchDBTable_Modified (search_id, search_type, context, " + + "modified_state) VALUES (?, ?, ?, ?)", -1, + out stmt); + assert(res == Sqlite.OK); + + res = stmt.bind_int64(1, id.id); + assert(res == Sqlite.OK); + + res = stmt.bind_text(2, modified_state.search_type.to_string()); + assert(res == Sqlite.OK); + + res = stmt.bind_text(3, modified_state.context.to_string()); + assert(res == Sqlite.OK); + + res = stmt.bind_text(4, modified_state.state.to_string()); + assert(res == Sqlite.OK); + + res = stmt.step(); + if (res != Sqlite.DONE) + throw_error("SavedSearchDBTable_Modified.add", res); + } else if (condition is SearchConditionRating) { + SearchConditionRating rating = condition as SearchConditionRating; + Sqlite.Statement stmt; + int res = db.prepare_v2("INSERT INTO SavedSearchDBTable_Rating (search_id, search_type, rating, " + + "context) VALUES (?, ?, ?, ?)", -1, + out stmt); + assert(res == Sqlite.OK); + + res = stmt.bind_int64(1, id.id); + assert(res == Sqlite.OK); + + res = stmt.bind_text(2, rating.search_type.to_string()); + assert(res == Sqlite.OK); + + res = stmt.bind_int(3, rating.rating.serialize()); + assert(res == Sqlite.OK); + + res = stmt.bind_text(4, rating.context.to_string()); + assert(res == Sqlite.OK); + + res = stmt.step(); + if (res != Sqlite.DONE) + throw_error("SavedSearchDBTable_Rating.add", res); + } else if (condition is SearchConditionDate) { + SearchConditionDate date = condition as SearchConditionDate; + Sqlite.Statement stmt; + int res = db.prepare_v2("INSERT INTO SavedSearchDBTable_Date (search_id, search_type, " + + "context, date_one, date_two) VALUES (?, ?, ?, ?, ?)", -1, + out stmt); + assert(res == Sqlite.OK); + + res = stmt.bind_int64(1, id.id); + assert(res == Sqlite.OK); + + res = stmt.bind_text(2, date.search_type.to_string()); + assert(res == Sqlite.OK); + + res = stmt.bind_text(3, date.context.to_string()); + assert(res == Sqlite.OK); + + res = stmt.bind_int64(4, date.date_one.to_unix()); + assert(res == Sqlite.OK); + + res = stmt.bind_int64(5, date.date_two.to_unix()); + assert(res == Sqlite.OK); + + res = stmt.step(); + if (res != Sqlite.DONE) + throw_error("SavedSearchDBTable_Date.add", res); + } else { + assert_not_reached(); + } + } + + // Removes the conditions of a search. Used on delete. + private void remove_conditions_for_search_id(SavedSearchID search_id) throws DatabaseError { + remove_conditions_for_table("SavedSearchDBTable_Text", search_id); + remove_conditions_for_table("SavedSearchDBTable_MediaType", search_id); + remove_conditions_for_table("SavedSearchDBTable_Flagged", search_id); + remove_conditions_for_table("SavedSearchDBTable_Modified", search_id); + remove_conditions_for_table("SavedSearchDBTable_Rating", search_id); + remove_conditions_for_table("SavedSearchDBTable_Date", search_id); + } + + private void remove_conditions_for_table(string table_name, SavedSearchID search_id) + throws DatabaseError { + Sqlite.Statement stmt; + int res = db.prepare_v2("DELETE FROM %s WHERE search_id=?".printf(table_name), -1, out stmt); + assert(res == Sqlite.OK); + + res = stmt.bind_int64(1, search_id.id); + assert(res == Sqlite.OK); + + res = stmt.step(); + if (res != Sqlite.DONE) + throw_error("%s.remove".printf(table_name), res); + } + + // Returns all conditions for a given search. Used on loading a search. + private Gee.List<SearchCondition> get_conditions_for_id(SavedSearchID search_id) + throws DatabaseError { + Gee.List<SearchCondition> list = new Gee.ArrayList<SearchCondition>(); + Sqlite.Statement stmt; + int res; + + // Get all text conditions. + res = db.prepare_v2("SELECT search_type, context, text FROM SavedSearchDBTable_Text " + + "WHERE search_id=?", + -1, out stmt); + assert(res == Sqlite.OK); + + res = stmt.bind_int64(1, search_id.id); + assert(res == Sqlite.OK); + + for (;;) { + res = stmt.step(); + if (res == Sqlite.DONE) + break; + else if (res != Sqlite.ROW) + throw_error("SavedSearchDBTable_Text.get_all_rows", res); + + SearchConditionText condition = new SearchConditionText( + SearchCondition.SearchType.from_string(stmt.column_text(0)), + stmt.column_text(2), + SearchConditionText.Context.from_string(stmt.column_text(1))); + + list.add(condition); + } + + // Get all media type conditions. + res = db.prepare_v2("SELECT search_type, context, type FROM SavedSearchDBTable_MediaType " + + "WHERE search_id=?", + -1, out stmt); + assert(res == Sqlite.OK); + + res = stmt.bind_int64(1, search_id.id); + assert(res == Sqlite.OK); + + for (;;) { + res = stmt.step(); + if (res == Sqlite.DONE) + break; + else if (res != Sqlite.ROW) + throw_error("SavedSearchDBTable_MediaType.get_all_rows", res); + + SearchConditionMediaType condition = new SearchConditionMediaType( + SearchCondition.SearchType.from_string(stmt.column_text(0)), + SearchConditionMediaType.Context.from_string(stmt.column_text(1)), + SearchConditionMediaType.MediaType.from_string(stmt.column_text(2))); + + list.add(condition); + } + + // Get all flagged state conditions. + res = db.prepare_v2("SELECT search_type, flag_state FROM SavedSearchDBTable_Flagged " + + "WHERE search_id=?", + -1, out stmt); + assert(res == Sqlite.OK); + + res = stmt.bind_int64(1, search_id.id); + assert(res == Sqlite.OK); + + for (;;) { + res = stmt.step(); + if (res == Sqlite.DONE) + break; + else if (res != Sqlite.ROW) + throw_error("SavedSearchDBTable_Flagged.get_all_rows", res); + + SearchConditionFlagged condition = new SearchConditionFlagged( + SearchCondition.SearchType.from_string(stmt.column_text(0)), + SearchConditionFlagged.State.from_string(stmt.column_text(1))); + + list.add(condition); + } + + // Get all modified state conditions. + res = db.prepare_v2("SELECT search_type, context, modified_state FROM SavedSearchDBTable_Modified " + + "WHERE search_id=?", + -1, out stmt); + assert(res == Sqlite.OK); + + res = stmt.bind_int64(1, search_id.id); + assert(res == Sqlite.OK); + + for (;;) { + res = stmt.step(); + if (res == Sqlite.DONE) + break; + else if (res != Sqlite.ROW) + throw_error("SavedSearchDBTable_Modified.get_all_rows", res); + + SearchConditionModified condition = new SearchConditionModified( + SearchCondition.SearchType.from_string(stmt.column_text(0)), + SearchConditionModified.Context.from_string(stmt.column_text(1)), + SearchConditionModified.State.from_string(stmt.column_text(2))); + + list.add(condition); + } + + // Get all rating conditions. + res = db.prepare_v2("SELECT search_type, rating, context FROM SavedSearchDBTable_Rating " + + "WHERE search_id=?", + -1, out stmt); + assert(res == Sqlite.OK); + + res = stmt.bind_int64(1, search_id.id); + assert(res == Sqlite.OK); + + for (;;) { + res = stmt.step(); + if (res == Sqlite.DONE) + break; + else if (res != Sqlite.ROW) + throw_error("SavedSearchDBTable_Rating.get_all_rows", res); + + SearchConditionRating condition = new SearchConditionRating( + SearchCondition.SearchType.from_string(stmt.column_text(0)), + Rating.unserialize(stmt.column_int(1)), + SearchConditionRating.Context.from_string(stmt.column_text(2))); + + list.add(condition); + } + + // Get all date conditions. + res = db.prepare_v2("SELECT search_type, context, date_one, date_two FROM SavedSearchDBTable_Date " + + "WHERE search_id=?", + -1, out stmt); + assert(res == Sqlite.OK); + + res = stmt.bind_int64(1, search_id.id); + assert(res == Sqlite.OK); + + for (;;) { + res = stmt.step(); + if (res == Sqlite.DONE) + break; + else if (res != Sqlite.ROW) + throw_error("SavedSearchDBTable_Date.get_all_rows", res); + + SearchConditionDate condition = new SearchConditionDate( + SearchCondition.SearchType.from_string(stmt.column_text(0)), + SearchConditionDate.Context.from_string(stmt.column_text(1)), + new DateTime.from_unix_local(stmt.column_int64(2)), + new DateTime.from_unix_local(stmt.column_int64(3))); + list.add(condition); + } + + return list; + } + + // All fields but search_id are respected in SavedSearchRow. + public SavedSearchID create_from_row(SavedSearchRow row) throws DatabaseError { + Sqlite.Statement stmt; + int res = db.prepare_v2("INSERT INTO SavedSearchDBTable (name, operator) VALUES (?, ?)", + -1, out stmt); + assert(res == Sqlite.OK); + + res = stmt.bind_text(1, row.name); + assert(res == Sqlite.OK); + res = stmt.bind_text(2, row.operator.to_string()); + assert(res == Sqlite.OK); + + res = stmt.step(); + if (res != Sqlite.DONE) + throw_error("SavedSearchDBTable.create_from_row", res); + + SavedSearchID search_id = SavedSearchID(db.last_insert_rowid()); + + foreach (SearchCondition sc in row.conditions) { + add_condition(search_id, sc); + } + + return search_id; + } + + public void remove(SavedSearchID search_id) throws DatabaseError { + remove_conditions_for_search_id(search_id); + delete_by_id(search_id.id); + } + + public SavedSearchRow? get_row(SavedSearchID search_id) throws DatabaseError { + Sqlite.Statement stmt; + int res = db.prepare_v2("SELECT name, operator FROM SavedSearchDBTable WHERE id=?", + -1, out stmt); + assert(res == Sqlite.OK); + + res = stmt.bind_int64(1, search_id.id); + assert(res == Sqlite.OK); + + res = stmt.step(); + if (res == Sqlite.DONE) + return null; + else if (res != Sqlite.ROW) + throw_error("SavedSearchDBTable.get_row", res); + + SavedSearchRow row = new SavedSearchRow(); + row.search_id = search_id; + row.name = stmt.column_text(0); + row.operator = SearchOperator.from_string(stmt.column_text(1)); + + return row; + } + + public Gee.List<SavedSearchRow?> get_all_rows() throws DatabaseError { + Sqlite.Statement stmt; + int res = db.prepare_v2("SELECT id, name, operator FROM SavedSearchDBTable", -1, + out stmt); + assert(res == Sqlite.OK); + + Gee.List<SavedSearchRow?> rows = new Gee.ArrayList<SavedSearchRow?>(); + + for (;;) { + res = stmt.step(); + if (res == Sqlite.DONE) + break; + else if (res != Sqlite.ROW) + throw_error("SavedSearchDBTable.get_all_rows", res); + + SavedSearchRow row = new SavedSearchRow(); + row.search_id = SavedSearchID(stmt.column_int64(0)); + row.name = stmt.column_text(1); + row.operator = SearchOperator.from_string(stmt.column_text(2)); + row.conditions = get_conditions_for_id(row.search_id); + + rows.add(row); + } + + return rows; + } + + public void rename(SavedSearchID search_id, string new_name) throws DatabaseError { + update_text_by_id_2(search_id.id, "name", new_name); + } +} + diff --git a/src/db/TagTable.vala b/src/db/TagTable.vala new file mode 100644 index 0000000..a0fade8 --- /dev/null +++ b/src/db/TagTable.vala @@ -0,0 +1,248 @@ +/* Copyright 2011-2014 Yorba Foundation + * + * This software is licensed under the GNU Lesser General Public License + * (version 2.1 or later). See the COPYING file in this distribution. + */ + +public struct TagID { + public const int64 INVALID = -1; + + public int64 id; + + public TagID(int64 id = INVALID) { + this.id = id; + } + + public bool is_invalid() { + return (id == INVALID); + } + + public bool is_valid() { + return (id != INVALID); + } +} + +public class TagRow { + public TagID tag_id; + public string name; + public Gee.Set<string>? source_id_list; + public time_t time_created; +} + +public class TagTable : DatabaseTable { + private static TagTable instance = null; + + private TagTable() { + set_table_name("TagTable"); + + Sqlite.Statement stmt; + int res = db.prepare_v2("CREATE TABLE IF NOT EXISTS " + + "TagTable " + + "(" + + "id INTEGER PRIMARY KEY, " + + "name TEXT UNIQUE NOT NULL, " + + "photo_id_list TEXT, " + + "time_created INTEGER" + + ")", -1, out stmt); + assert(res == Sqlite.OK); + + res = stmt.step(); + if (res != Sqlite.DONE) + fatal("create TagTable", res); + } + + public static TagTable get_instance() { + if (instance == null) + instance = new TagTable(); + + return instance; + } + + public static void upgrade_for_htags() { + TagTable table = get_instance(); + + try { + Gee.List<TagRow?> rows = table.get_all_rows(); + + foreach (TagRow row in rows) { + row.name = row.name.replace(Tag.PATH_SEPARATOR_STRING, "-"); + table.rename(row.tag_id, row.name); + } + } catch (DatabaseError e) { + error ("TagTable: can't upgrade tag names for hierarchical tag support: %s", e.message); + } + } + + public TagRow add(string name) throws DatabaseError { + Sqlite.Statement stmt; + int res = db.prepare_v2("INSERT INTO TagTable (name, time_created) VALUES (?, ?)", -1, + out stmt); + assert(res == Sqlite.OK); + + time_t time_created = (time_t) now_sec(); + + res = stmt.bind_text(1, name); + assert(res == Sqlite.OK); + res = stmt.bind_int64(2, time_created); + assert(res == Sqlite.OK); + + res = stmt.step(); + if (res != Sqlite.DONE) + throw_error("TagTable.add", res); + + TagRow row = new TagRow(); + row.tag_id = TagID(db.last_insert_rowid()); + row.name = name; + row.source_id_list = null; + row.time_created = time_created; + + return row; + } + + // All fields but tag_id are respected in TagRow. + public TagID create_from_row(TagRow row) throws DatabaseError { + Sqlite.Statement stmt; + int res = db.prepare_v2("INSERT INTO TagTable (name, photo_id_list, time_created) VALUES (?, ?, ?)", + -1, out stmt); + assert(res == Sqlite.OK); + + res = stmt.bind_text(1, row.name); + assert(res == Sqlite.OK); + res = stmt.bind_text(2, serialize_source_ids(row.source_id_list)); + assert(res == Sqlite.OK); + res = stmt.bind_int64(3, row.time_created); + assert(res == Sqlite.OK); + + res = stmt.step(); + if (res != Sqlite.DONE) + throw_error("TagTable.create_from_row", res); + + return TagID(db.last_insert_rowid()); + } + + public void remove(TagID tag_id) throws DatabaseError { + delete_by_id(tag_id.id); + } + + public string? get_name(TagID tag_id) throws DatabaseError { + Sqlite.Statement stmt; + if (!select_by_id(tag_id.id, "name", out stmt)) + return null; + + return stmt.column_text(0); + } + + public TagRow? get_row(TagID tag_id) throws DatabaseError { + Sqlite.Statement stmt; + int res = db.prepare_v2("SELECT name, photo_id_list, time_created FROM TagTable WHERE id=?", + -1, out stmt); + assert(res == Sqlite.OK); + + res = stmt.bind_int64(1, tag_id.id); + assert(res == Sqlite.OK); + + res = stmt.step(); + if (res == Sqlite.DONE) + return null; + else if (res != Sqlite.ROW) + throw_error("TagTable.get_row", res); + + TagRow row = new TagRow(); + row.tag_id = tag_id; + row.name = stmt.column_text(0); + row.source_id_list = unserialize_source_ids(stmt.column_text(1)); + row.time_created = (time_t) stmt.column_int64(2); + + return row; + } + + public Gee.List<TagRow?> get_all_rows() throws DatabaseError { + Sqlite.Statement stmt; + int res = db.prepare_v2("SELECT id, name, photo_id_list, time_created FROM TagTable", -1, + out stmt); + assert(res == Sqlite.OK); + + Gee.List<TagRow?> rows = new Gee.ArrayList<TagRow?>(); + + for (;;) { + res = stmt.step(); + if (res == Sqlite.DONE) + break; + else if (res != Sqlite.ROW) + throw_error("TagTable.get_all_rows", res); + + // res == Sqlite.ROW + TagRow row = new TagRow(); + row.tag_id = TagID(stmt.column_int64(0)); + row.name = stmt.column_text(1); + row.source_id_list = unserialize_source_ids(stmt.column_text(2)); + row.time_created = (time_t) stmt.column_int64(3); + + rows.add(row); + } + + return rows; + } + + public void rename(TagID tag_id, string new_name) throws DatabaseError { + update_text_by_id_2(tag_id.id, "name", new_name); + } + + public void set_tagged_sources(TagID tag_id, Gee.Collection<string> source_ids) throws DatabaseError { + Sqlite.Statement stmt; + int res = db.prepare_v2("UPDATE TagTable SET photo_id_list=? WHERE id=?", -1, out stmt); + assert(res == Sqlite.OK); + + res = stmt.bind_text(1, serialize_source_ids(source_ids)); + assert(res == Sqlite.OK); + res = stmt.bind_int64(2, tag_id.id); + assert(res == Sqlite.OK); + + res = stmt.step(); + if (res != Sqlite.DONE) + throw_error("TagTable.set_tagged_photos", res); + } + + private string? serialize_source_ids(Gee.Collection<string>? source_ids) { + if (source_ids == null) + return null; + + StringBuilder result = new StringBuilder(); + + foreach (string source_id in source_ids) { + result.append(source_id); + result.append(","); + } + + return (result.len != 0) ? result.str : null; + } + + private Gee.Set<string> unserialize_source_ids(string? text_list) { + Gee.Set<string> result = new Gee.HashSet<string>(); + + if (text_list == null) + return result; + + string[] split = text_list.split(","); + foreach (string token in split) { + if (is_string_empty(token)) + continue; + + // handle current and legacy encoding of source ids -- in the past, we only stored + // LibraryPhotos in tags so we only needed to store the numeric database key of the + // photo to uniquely identify it. Now, however, tags can store arbitrary MediaSources, + // so instead of simply storing a number we store the source id, a string that contains + // a typename followed by an identifying number (e.g., "video-022354"). + if (token[0].isdigit()) { + // this is a legacy entry + result.add(PhotoID.upgrade_photo_id_to_source_id(PhotoID(parse_int64(token, 10)))); + } else if (token[0].isalpha()) { + // this is a modern entry + result.add(token); + } + } + + return result; + } +} + diff --git a/src/db/TombstoneTable.vala b/src/db/TombstoneTable.vala new file mode 100644 index 0000000..9e108bd --- /dev/null +++ b/src/db/TombstoneTable.vala @@ -0,0 +1,146 @@ +/* Copyright 2011-2014 Yorba Foundation + * + * This software is licensed under the GNU Lesser General Public License + * (version 2.1 or later). See the COPYING file in this distribution. + */ + +public struct TombstoneID { + public const int64 INVALID = -1; + + public int64 id; + + public TombstoneID(int64 id = INVALID) { + this.id = id; + } + + public bool is_invalid() { + return (id == INVALID); + } + + public bool is_valid() { + return (id != INVALID); + } +} + +public class TombstoneRow { + public TombstoneID id; + public string filepath; + public int64 filesize; + public string? md5; + public time_t time_created; + public Tombstone.Reason reason; +} + +public class TombstoneTable : DatabaseTable { + private static TombstoneTable instance = null; + + private TombstoneTable() { + set_table_name("TombstoneTable"); + + Sqlite.Statement stmt; + int res = db.prepare_v2("CREATE TABLE IF NOT EXISTS " + + "TombstoneTable " + + "(" + + "id INTEGER PRIMARY KEY, " + + "filepath TEXT NOT NULL, " + + "filesize INTEGER, " + + "md5 TEXT, " + + "time_created INTEGER, " + + "reason INTEGER DEFAULT 0 " + + ")", -1, out stmt); + assert(res == Sqlite.OK); + + res = stmt.step(); + if (res != Sqlite.DONE) + fatal("create TombstoneTable", res); + } + + public static TombstoneTable get_instance() { + if (instance == null) + instance = new TombstoneTable(); + + return instance; + } + + public TombstoneRow add(string filepath, int64 filesize, string? md5, Tombstone.Reason reason) + throws DatabaseError { + Sqlite.Statement stmt; + int res = db.prepare_v2("INSERT INTO TombstoneTable " + + "(filepath, filesize, md5, time_created, reason) " + + "VALUES (?, ?, ?, ?, ?)", + -1, out stmt); + assert(res == Sqlite.OK); + + time_t time_created = (time_t) now_sec(); + + res = stmt.bind_text(1, filepath); + assert(res == Sqlite.OK); + res = stmt.bind_int64(2, filesize); + assert(res == Sqlite.OK); + res = stmt.bind_text(3, md5); + assert(res == Sqlite.OK); + res = stmt.bind_int64(4, (int64) time_created); + assert(res == Sqlite.OK); + res = stmt.bind_int(5, reason.serialize()); + assert(res == Sqlite.OK); + + res = stmt.step(); + if (res != Sqlite.DONE) + throw_error("TombstoneTable.add", res); + + TombstoneRow row = new TombstoneRow(); + row.id = TombstoneID(db.last_insert_rowid()); + row.filepath = filepath; + row.filesize = filesize; + row.md5 = md5; + row.time_created = time_created; + row.reason = reason; + + return row; + } + + public TombstoneRow[]? fetch_all() throws DatabaseError { + int row_count = get_row_count(); + if (row_count == 0) + return null; + + Sqlite.Statement stmt; + int res = db.prepare_v2("SELECT id, filepath, filesize, md5, time_created, reason " + + "FROM TombstoneTable", -1, out stmt); + assert(res == Sqlite.OK); + + TombstoneRow[] rows = new TombstoneRow[row_count]; + + int index = 0; + for (;;) { + res = stmt.step(); + if (res == Sqlite.DONE) + break; + else if (res != Sqlite.ROW) + throw_error("TombstoneTable.fetch_all", res); + + TombstoneRow row = new TombstoneRow(); + row.id = TombstoneID(stmt.column_int64(0)); + row.filepath = stmt.column_text(1); + row.filesize = stmt.column_int64(2); + row.md5 = stmt.column_text(3); + row.time_created = (time_t) stmt.column_int64(4); + row.reason = Tombstone.Reason.unserialize(stmt.column_int(5)); + + rows[index++] = row; + } + + assert(index == row_count); + + return rows; + } + + public void update_file(TombstoneID tombstone_id, string filepath) throws DatabaseError { + update_text_by_id_2(tombstone_id.id, "filepath", filepath); + } + + public void remove(TombstoneID tombstone_id) throws DatabaseError { + delete_by_id(tombstone_id.id); + } +} + diff --git a/src/db/VersionTable.vala b/src/db/VersionTable.vala new file mode 100644 index 0000000..9003b1b --- /dev/null +++ b/src/db/VersionTable.vala @@ -0,0 +1,98 @@ +/* Copyright 2011-2014 Yorba Foundation + * + * This software is licensed under the GNU Lesser General Public License + * (version 2.1 or later). See the COPYING file in this distribution. + */ + +public class VersionTable : DatabaseTable { + private static VersionTable instance = null; + + private VersionTable() { + Sqlite.Statement stmt; + int res = db.prepare_v2("CREATE TABLE IF NOT EXISTS VersionTable (" + + "id INTEGER PRIMARY KEY, " + + "schema_version INTEGER, " + + "app_version TEXT, " + + "user_data TEXT NULL" + + ")", -1, out stmt); + assert(res == Sqlite.OK); + + res = stmt.step(); + if (res != Sqlite.DONE) + fatal("create version table", res); + + set_table_name("VersionTable"); + } + + public static VersionTable get_instance() { + if (instance == null) + instance = new VersionTable(); + + return instance; + } + + public int get_version(out string app_version) { + Sqlite.Statement stmt; + int res = db.prepare_v2("SELECT schema_version, app_version FROM VersionTable ORDER BY schema_version DESC LIMIT 1", + -1, out stmt); + assert(res == Sqlite.OK); + + res = stmt.step(); + if (res != Sqlite.ROW) { + if (res != Sqlite.DONE) + fatal("get_version", res); + + app_version = null; + + return -1; + } + + app_version = stmt.column_text(1); + + return stmt.column_int(0); + } + + public void set_version(int version, string app_version, string? user_data = null) { + Sqlite.Statement stmt; + + string bitbucket; + if (get_version(out bitbucket) != -1) { + // overwrite existing row + int res = db.prepare_v2("UPDATE VersionTable SET schema_version=?, app_version=?, user_data=?", + -1, out stmt); + assert(res == Sqlite.OK); + } else { + // insert new row + int res = db.prepare_v2("INSERT INTO VersionTable (schema_version, app_version, user_data) VALUES (?,?, ?)", + -1, out stmt); + assert(res == Sqlite.OK); + } + + int res = stmt.bind_int(1, version); + assert(res == Sqlite.OK); + res = stmt.bind_text(2, app_version); + assert(res == Sqlite.OK); + res = stmt.bind_text(3, user_data); + assert(res == Sqlite.OK); + + res = stmt.step(); + if (res != Sqlite.DONE) + fatal("set_version %d %s %s".printf(version, app_version, user_data), res); + } + + public void update_version(int version, string app_version) { + Sqlite.Statement stmt; + int res = db.prepare_v2("UPDATE VersionTable SET schema_version=?, app_version=?", -1, out stmt); + assert(res == Sqlite.OK); + + res = stmt.bind_int(1, version); + assert(res == Sqlite.OK); + res = stmt.bind_text(2, app_version); + assert(res == Sqlite.OK); + + res = stmt.step(); + if (res != Sqlite.DONE) + fatal("update_version %d".printf(version), res); + } +} + diff --git a/src/db/VideoTable.vala b/src/db/VideoTable.vala new file mode 100644 index 0000000..c681dc2 --- /dev/null +++ b/src/db/VideoTable.vala @@ -0,0 +1,462 @@ +/* Copyright 2011-2014 Yorba Foundation + * + * This software is licensed under the GNU Lesser General Public License + * (version 2.1 or later). See the COPYING file in this distribution. + */ + +public struct VideoID { + public const int64 INVALID = -1; + + public int64 id; + + public VideoID(int64 id = INVALID) { + this.id = id; + } + + public bool is_invalid() { + return (id == INVALID); + } + + public bool is_valid() { + return (id != INVALID); + } + + public static uint hash(VideoID? a) { + return int64_hash(a.id); + } + + public static bool equal(void *a, void *b) { + return ((VideoID *) a)->id == ((VideoID *) b)->id; + } + + public static string upgrade_video_id_to_source_id(VideoID video_id) { + return ("%s-%016" + int64.FORMAT_MODIFIER + "x").printf(Video.TYPENAME, video_id.id); + } +} + +public class VideoRow { + public VideoID video_id; + public string filepath; + public int64 filesize; + public time_t timestamp; + public int width; + public int height; + public double clip_duration; + public bool is_interpretable; + public time_t exposure_time; + public ImportID import_id; + public EventID event_id; + public string md5; + public time_t time_created; + public Rating rating; + public string title; + public string? backlinks; + public time_t time_reimported; + public uint64 flags; + public string comment; +} + +public class VideoTable : DatabaseTable { + private static VideoTable instance = null; + + private VideoTable() { + Sqlite.Statement stmt; + int res = db.prepare_v2("CREATE TABLE IF NOT EXISTS VideoTable (" + + "id INTEGER PRIMARY KEY, " + + "filename TEXT UNIQUE NOT NULL, " + + "width INTEGER, " + + "height INTEGER, " + + "clip_duration REAL, " + + "is_interpretable INTEGER, " + + "filesize INTEGER, " + + "timestamp INTEGER, " + + "exposure_time INTEGER, " + + "import_id INTEGER, " + + "event_id INTEGER, " + + "md5 TEXT, " + + "time_created INTEGER, " + + "rating INTEGER DEFAULT 0, " + + "title TEXT, " + + "backlinks TEXT, " + + "time_reimported INTEGER, " + + "flags INTEGER DEFAULT 0, " + + "comment TEXT " + + ")", -1, out stmt); + assert(res == Sqlite.OK); + + res = stmt.step(); + if (res != Sqlite.DONE) + fatal("VideoTable constructor", res); + + // index on event_id + Sqlite.Statement stmt2; + int res2 = db.prepare_v2("CREATE INDEX IF NOT EXISTS VideoEventIDIndex ON VideoTable (event_id)", + -1, out stmt2); + assert(res2 == Sqlite.OK); + + res2 = stmt2.step(); + if (res2 != Sqlite.DONE) + fatal("VideoTable constructor", res2); + + set_table_name("VideoTable"); + } + + public static VideoTable get_instance() { + if (instance == null) + instance = new VideoTable(); + + return instance; + } + + // VideoRow.video_id, event_id, time_created are ignored on input. All fields are set on exit + // with values stored in the database. + public VideoID add(VideoRow video_row) throws DatabaseError { + Sqlite.Statement stmt; + int res = db.prepare_v2( + "INSERT INTO VideoTable (filename, width, height, clip_duration, is_interpretable, " + + "filesize, timestamp, exposure_time, import_id, event_id, md5, time_created, title, comment) " + + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", + -1, out stmt); + assert(res == Sqlite.OK); + + ulong time_created = now_sec(); + + res = stmt.bind_text(1, video_row.filepath); + assert(res == Sqlite.OK); + res = stmt.bind_int(2, video_row.width); + assert(res == Sqlite.OK); + res = stmt.bind_int(3, video_row.height); + assert(res == Sqlite.OK); + res = stmt.bind_double(4, video_row.clip_duration); + assert(res == Sqlite.OK); + res = stmt.bind_int(5, (video_row.is_interpretable) ? 1 : 0); + assert(res == Sqlite.OK); + res = stmt.bind_int64(6, video_row.filesize); + assert(res == Sqlite.OK); + res = stmt.bind_int64(7, video_row.timestamp); + assert(res == Sqlite.OK); + res = stmt.bind_int64(8, video_row.exposure_time); + assert(res == Sqlite.OK); + res = stmt.bind_int64(9, video_row.import_id.id); + assert(res == Sqlite.OK); + res = stmt.bind_int64(10, EventID.INVALID); + assert(res == Sqlite.OK); + res = stmt.bind_text(11, video_row.md5); + assert(res == Sqlite.OK); + res = stmt.bind_int64(12, time_created); + assert(res == Sqlite.OK); + res = stmt.bind_text(13, video_row.title); + assert(res == Sqlite.OK); + res = stmt.bind_text(14, video_row.comment); + assert(res == Sqlite.OK); + + res = stmt.step(); + if (res != Sqlite.DONE) { + if (res != Sqlite.CONSTRAINT) + throw_error("VideoTable.add", res); + } + + // fill in ignored fields with database values + video_row.video_id = VideoID(db.last_insert_rowid()); + video_row.event_id = EventID(); + video_row.time_created = (time_t) time_created; + video_row.flags = 0; + + return video_row.video_id; + } + + public bool drop_event(EventID event_id) { + Sqlite.Statement stmt; + int res = db.prepare_v2("UPDATE VideoTable SET event_id = ? WHERE event_id = ?", -1, out stmt); + assert(res == Sqlite.OK); + + res = stmt.bind_int64(1, EventID.INVALID); + assert(res == Sqlite.OK); + res = stmt.bind_int64(2, event_id.id); + assert(res == Sqlite.OK); + + res = stmt.step(); + if (res != Sqlite.DONE) { + fatal("VideoTable.drop_event", res); + + return false; + } + + return true; + } + + public VideoRow? get_row(VideoID video_id) { + Sqlite.Statement stmt; + int res = db.prepare_v2( + "SELECT filename, width, height, clip_duration, is_interpretable, filesize, timestamp, " + + "exposure_time, import_id, event_id, md5, time_created, rating, title, backlinks, " + + "time_reimported, flags, comment FROM VideoTable WHERE id=?", + -1, out stmt); + assert(res == Sqlite.OK); + + res = stmt.bind_int64(1, video_id.id); + assert(res == Sqlite.OK); + + if (stmt.step() != Sqlite.ROW) + return null; + + VideoRow row = new VideoRow(); + row.video_id = video_id; + row.filepath = stmt.column_text(0); + row.width = stmt.column_int(1); + row.height = stmt.column_int(2); + row.clip_duration = stmt.column_double(3); + row.is_interpretable = (stmt.column_int(4) == 1); + row.filesize = stmt.column_int64(5); + row.timestamp = (time_t) stmt.column_int64(6); + row.exposure_time = (time_t) stmt.column_int64(7); + row.import_id.id = stmt.column_int64(8); + row.event_id.id = stmt.column_int64(9); + row.md5 = stmt.column_text(10); + row.time_created = (time_t) stmt.column_int64(11); + row.rating = Rating.unserialize(stmt.column_int(12)); + row.title = stmt.column_text(13); + row.backlinks = stmt.column_text(14); + row.time_reimported = (time_t) stmt.column_int64(15); + row.flags = stmt.column_int64(16); + row.comment = stmt.column_text(17); + + return row; + } + + public Gee.ArrayList<VideoRow?> get_all() { + Sqlite.Statement stmt; + int res = db.prepare_v2( + "SELECT id, filename, width, height, clip_duration, is_interpretable, filesize, " + + "timestamp, exposure_time, import_id, event_id, md5, time_created, rating, title, " + + "backlinks, time_reimported, flags, comment FROM VideoTable", + -1, out stmt); + assert(res == Sqlite.OK); + + Gee.ArrayList<VideoRow?> all = new Gee.ArrayList<VideoRow?>(); + + while ((res = stmt.step()) == Sqlite.ROW) { + VideoRow row = new VideoRow(); + row.video_id.id = stmt.column_int64(0); + row.filepath = stmt.column_text(1); + row.width = stmt.column_int(2); + row.height = stmt.column_int(3); + row.clip_duration = stmt.column_double(4); + row.is_interpretable = (stmt.column_int(5) == 1); + row.filesize = stmt.column_int64(6); + row.timestamp = (time_t) stmt.column_int64(7); + row.exposure_time = (time_t) stmt.column_int64(8); + row.import_id.id = stmt.column_int64(9); + row.event_id.id = stmt.column_int64(10); + row.md5 = stmt.column_text(11); + row.time_created = (time_t) stmt.column_int64(12); + row.rating = Rating.unserialize(stmt.column_int(13)); + row.title = stmt.column_text(14); + row.backlinks = stmt.column_text(15); + row.time_reimported = (time_t) stmt.column_int64(16); + row.flags = stmt.column_int64(17); + row.comment = stmt.column_text(18); + + all.add(row); + } + + return all; + } + + public void set_filepath(VideoID video_id, string filepath) throws DatabaseError { + update_text_by_id_2(video_id.id, "filename", filepath); + } + + public void set_title(VideoID video_id, string? new_title) throws DatabaseError { + update_text_by_id_2(video_id.id, "title", new_title != null ? new_title : ""); + } + + public void set_comment(VideoID video_id, string? new_comment) throws DatabaseError { + update_text_by_id_2(video_id.id, "comment", new_comment != null ? new_comment : ""); + } + + public void set_exposure_time(VideoID video_id, time_t time) throws DatabaseError { + update_int64_by_id_2(video_id.id, "exposure_time", (int64) time); + } + + public void set_rating(VideoID video_id, Rating rating) throws DatabaseError { + update_int64_by_id_2(video_id.id, "rating", rating.serialize()); + } + + public void set_flags(VideoID video_id, uint64 flags) throws DatabaseError { + update_int64_by_id_2(video_id.id, "flags", (int64) flags); + } + + public void update_backlinks(VideoID video_id, string? backlinks) throws DatabaseError { + update_text_by_id_2(video_id.id, "backlinks", backlinks != null ? backlinks : ""); + } + + public void update_is_interpretable(VideoID video_id, bool is_interpretable) throws DatabaseError { + update_int_by_id_2(video_id.id, "is_interpretable", (is_interpretable) ? 1 : 0); + } + + public bool set_event(VideoID video_id, EventID event_id) { + return update_int64_by_id(video_id.id, "event_id", event_id.id); + } + + public void remove_by_file(File file) throws DatabaseError { + Sqlite.Statement stmt; + int res = db.prepare_v2("DELETE FROM VideoTable WHERE filename=?", -1, out stmt); + assert(res == Sqlite.OK); + + res = stmt.bind_text(1, file.get_path()); + assert(res == Sqlite.OK); + + res = stmt.step(); + if (res != Sqlite.DONE) + throw_error("VideoTable.remove_by_file", res); + } + + public void remove(VideoID videoID) throws DatabaseError { + Sqlite.Statement stmt; + int res = db.prepare_v2("DELETE FROM VideoTable WHERE id=?", -1, out stmt); + assert(res == Sqlite.OK); + + res = stmt.bind_int64(1, videoID.id); + assert(res == Sqlite.OK); + + res = stmt.step(); + if (res != Sqlite.DONE) + throw_error("VideoTable.remove", res); + } + + public bool is_video_stored(File file) { + return get_id(file).is_valid(); + } + + public VideoID get_id(File file) { + Sqlite.Statement stmt; + int res = db.prepare_v2("SELECT ID FROM VideoTable WHERE filename=?", -1, out stmt); + assert(res == Sqlite.OK); + + res = stmt.bind_text(1, file.get_path()); + assert(res == Sqlite.OK); + + res = stmt.step(); + + return (res == Sqlite.ROW) ? VideoID(stmt.column_int64(0)) : VideoID(); + } + + public Gee.ArrayList<VideoID?> get_videos() throws DatabaseError { + Sqlite.Statement stmt; + int res = db.prepare_v2("SELECT id FROM VideoTable", -1, out stmt); + assert(res == Sqlite.OK); + + Gee.ArrayList<VideoID?> video_ids = new Gee.ArrayList<VideoID?>(); + for (;;) { + res = stmt.step(); + if (res == Sqlite.DONE) { + break; + } else if (res != Sqlite.ROW) { + throw_error("VideoTable.get_videos", res); + } + + video_ids.add(VideoID(stmt.column_int64(0))); + } + + return video_ids; + } + + private Sqlite.Statement get_duplicate_stmt(File? file, string? md5) { + assert(file != null || md5 != null); + + string sql = "SELECT id FROM VideoTable WHERE"; + bool first = true; + + if (file != null) { + sql += " filename=?"; + first = false; + } + + if (md5 != null) { + if (!first) + sql += " OR "; + + sql += " md5=?"; + } + + Sqlite.Statement stmt; + int res = db.prepare_v2(sql, -1, out stmt); + assert(res == Sqlite.OK); + + int col = 1; + + if (file != null) { + res = stmt.bind_text(col++, file.get_path()); + assert(res == Sqlite.OK); + } + + if (md5 != null) { + res = stmt.bind_text(col++, md5); + assert(res == Sqlite.OK); + } + + return stmt; + } + + public bool has_duplicate(File? file, string? md5) { + Sqlite.Statement stmt = get_duplicate_stmt(file, md5); + int res = stmt.step(); + + if (res == Sqlite.DONE) { + // not found + return false; + } else if (res == Sqlite.ROW) { + // at least one found + return true; + } else { + fatal("VideoTable.has_duplicate", res); + } + + return false; + } + + public VideoID[] get_duplicate_ids(File? file, string? md5) { + Sqlite.Statement stmt = get_duplicate_stmt(file, md5); + + VideoID[] ids = new VideoID[0]; + + int res = stmt.step(); + while (res == Sqlite.ROW) { + ids += VideoID(stmt.column_int64(0)); + res = stmt.step(); + } + + return ids; + } + + public Gee.ArrayList<string> get_event_source_ids(EventID event_id) { + Sqlite.Statement stmt; + int res = db.prepare_v2("SELECT id FROM VideoTable WHERE event_id = ?", -1, out stmt); + assert(res == Sqlite.OK); + + res = stmt.bind_int64(1, event_id.id); + assert(res == Sqlite.OK); + + Gee.ArrayList<string> result = new Gee.ArrayList<string>(); + for(;;) { + res = stmt.step(); + if (res == Sqlite.DONE) { + break; + } else if (res != Sqlite.ROW) { + fatal("get_event_source_ids", res); + + break; + } + + result.add(VideoID.upgrade_video_id_to_source_id(VideoID(stmt.column_int64(0)))); + } + + return result; + } + + public void set_timestamp(VideoID video_id, time_t timestamp) throws DatabaseError { + update_int64_by_id_2(video_id.id, "timestamp", (int64) timestamp); + } +} + diff --git a/src/db/mk/db.mk b/src/db/mk/db.mk new file mode 100644 index 0000000..421961a --- /dev/null +++ b/src/db/mk/db.mk @@ -0,0 +1,35 @@ + +# UNIT_NAME is the Vala namespace. A file named UNIT_NAME.vala must be in this directory with +# a init() and terminate() function declared in the namespace. +UNIT_NAME := Db + +# UNIT_DIR should match the subdirectory the files are located in. Generally UNIT_NAME in all +# lowercase. The name of this file should be UNIT_DIR.mk. +UNIT_DIR := db + +# All Vala files in the unit should be listed here with no subdirectory prefix. +# +# NOTE: Do *not* include the unit's master file, i.e. UNIT_NAME.vala. +UNIT_FILES := \ + DatabaseTable.vala \ + PhotoTable.vala \ + EventTable.vala \ + TagTable.vala \ + TombstoneTable.vala \ + VideoTable.vala \ + VersionTable.vala \ + SavedSearchDBTable.vala + +# Any unit this unit relies upon (and should be initialized before it's initialized) should +# be listed here using its Vala namespace. +# +# NOTE: All units are assumed to rely upon the unit-unit. Do not include that here. +UNIT_USES := + +# List any additional files that are used in the build process as a part of this unit that should +# be packaged in the tarball. File names should be relative to the unit's home directory. +UNIT_RC := + +# unitize.mk must be called at the end of each UNIT_DIR.mk file. +include unitize.mk + |