diff options
Diffstat (limited to 'src/db')
| -rw-r--r-- | src/db/DatabaseTable.vala | 22 | ||||
| -rw-r--r-- | src/db/Db.vala | 65 | ||||
| -rw-r--r-- | src/db/EventTable.vala | 14 | ||||
| -rw-r--r-- | src/db/FaceLocationTable.vala | 75 | ||||
| -rw-r--r-- | src/db/FaceTable.vala | 56 | ||||
| -rw-r--r-- | src/db/PhotoTable.vala | 206 | ||||
| -rw-r--r-- | src/db/TagTable.vala | 8 | ||||
| -rw-r--r-- | src/db/TombstoneTable.vala | 6 | ||||
| -rw-r--r-- | src/db/VideoTable.vala | 64 |
9 files changed, 401 insertions, 115 deletions
diff --git a/src/db/DatabaseTable.vala b/src/db/DatabaseTable.vala index 5ec5be1..dea797a 100644 --- a/src/db/DatabaseTable.vala +++ b/src/db/DatabaseTable.vala @@ -21,12 +21,12 @@ public abstract class DatabaseTable { * 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; - + public const int SCHEMA_VERSION = 24; + protected static Sqlite.Database db; - + private static int in_transaction = 0; - + public string table_name = null; private static void prepare_db(string filename) { @@ -287,7 +287,19 @@ public abstract class DatabaseTable { if (res != Sqlite.DONE) throw_error("DatabaseTable.update_int64_by_id_2 %s.%s".printf(table_name, column), res); } - + + protected void update_double_by_id_2(int64 id, string column, double value) throws DatabaseError { + Sqlite.Statement stmt; + prepare_update_by_id(id, column, out stmt); + + int res = stmt.bind_double(1, value); + assert(res == Sqlite.OK); + + res = stmt.step(); + if (res != Sqlite.DONE) + throw_error("DatabaseTable.update_double_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); diff --git a/src/db/Db.vala b/src/db/Db.vala index 3eca8ce..5072967 100644 --- a/src/db/Db.vala +++ b/src/db/Db.vala @@ -293,7 +293,7 @@ private VerifyResult upgrade_database(int input_version) { } version = 16; - + // // Version 17: // * Added comment column to PhotoTable and VideoTable @@ -349,11 +349,72 @@ private VerifyResult upgrade_database(int input_version) { // version = 20; + // - // Finalize the upgrade process + // Version 21: + // * Add has_gps, gps_lat and gps_lon columns to PhotoTable + + if (!DatabaseTable.ensure_column("PhotoTable", "has_gps", "INTEGER DEFAULT -1", + "upgrade_database: adding gps_lat column to PhotoTable") + || !DatabaseTable.ensure_column("PhotoTable", "gps_lat", "REAL", + "upgrade_database: adding gps_lat column to PhotoTable") + || !DatabaseTable.ensure_column("PhotoTable", "gps_lon", "REAL", + "upgrade_database: adding gps_lon column to PhotoTable")) { + return VerifyResult.UPGRADE_ERROR; + } + + version = 21; + + // + // Version 22: + // * Create face detection tables even if feasture is not enabled + // * Added face pixels column to FaceLocationTable + // * Added face vector column to FaceTable // + FaceTable.get_instance(); + FaceLocationTable.get_instance(); + if (!DatabaseTable.has_column("FaceLocationTable", "vec")) { + message("upgrade_database: adding vec column to FaceLocationTable"); + if (!DatabaseTable.add_column("FaceLocationTable", "vec", "TEXT")) + return VerifyResult.UPGRADE_ERROR; + } + if (!DatabaseTable.has_column("FaceLocationTable", "guess")) { + message("upgrade_database: adding guess column to FaceLocationTable"); + if (!DatabaseTable.add_column("FaceLocationTable", "guess", "INTEGER DEFAULT 0")) + return VerifyResult.UPGRADE_ERROR; + } + if (!DatabaseTable.has_column("FaceTable", "ref")) { + message("upgrade_database: adding ref column to FaceTable"); + if (!DatabaseTable.add_column("FaceTable", "ref", "INTEGER DEFAULT -1")) + return VerifyResult.UPGRADE_ERROR; + } + version = 22; + + // + // Finalize the upgrade process + // + + if (input_version < 23) { + // Run the settings migrator to copy settings data from /org/yorba/shotwell to /org/gnome/shotwell + GSettingsConfigurationEngine.run_gsettings_migrator_v2(); + } + + version = 23; + + if (input_version < 24) { + // Convert timestamp 0 to NULL to represent unset date and free 0 to be 1.1.1970 00:00 + message("upgrade_database: Shifting times from 0 to null for unset times"); + try { + PhotoTable.upgrade_for_unset_timestamp(); + VideoTable.upgrade_for_unset_timestamp(); + version = 24; + } catch (DatabaseError err) { + critical("Failed to upgrade database to version 24: %s", err.message); + } + } + assert(version == DatabaseTable.SCHEMA_VERSION); VersionTable.get_instance().update_version(version, Resources.APP_VERSION); diff --git a/src/db/EventTable.vala b/src/db/EventTable.vala index 593d51c..3b7df17 100644 --- a/src/db/EventTable.vala +++ b/src/db/EventTable.vala @@ -25,7 +25,7 @@ public struct EventID { public class EventRow { public EventID event_id; public string? name; - public time_t time_created; + public int64 time_created; public string? primary_source_id; public string? comment; } @@ -80,7 +80,7 @@ public class EventTable : DatabaseTable { -1, out stmt); assert(res == Sqlite.OK); - time_t time_created = (time_t) now_sec(); + int64 time_created = now_sec(); res = stmt.bind_text(1, primary_source_id); assert(res == Sqlite.OK); @@ -151,7 +151,7 @@ public class EventTable : DatabaseTable { 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.time_created = stmt.column_int64(3); row.comment = stmt.column_text(4); return row; @@ -183,7 +183,7 @@ public class EventTable : DatabaseTable { 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.time_created = stmt.column_int64(4); row.comment = stmt.column_text(5); event_rows.add(row); @@ -218,12 +218,12 @@ public class EventTable : DatabaseTable { return update_text_by_id(event_id.id, "primary_source_id", primary_source_id); } - public time_t get_time_created(EventID event_id) { + public DateTime? get_time_created(EventID event_id) { Sqlite.Statement stmt; if (!select_by_id(event_id.id, "time_created", out stmt)) - return 0; + return null; - return (time_t) stmt.column_int64(0); + return new DateTime.from_unix_utc(stmt.column_int64(0)); } public bool set_comment(EventID event_id, string new_comment) { diff --git a/src/db/FaceLocationTable.vala b/src/db/FaceLocationTable.vala index 8398616..f4c88d7 100644 --- a/src/db/FaceLocationTable.vala +++ b/src/db/FaceLocationTable.vala @@ -27,6 +27,7 @@ public class FaceLocationRow { public FaceID face_id; public PhotoID photo_id; public string geometry; + public string vec; } public class FaceLocationTable : DatabaseTable { @@ -42,7 +43,9 @@ public class FaceLocationTable : DatabaseTable { + "id INTEGER NOT NULL PRIMARY KEY, " + "face_id INTEGER NOT NULL, " + "photo_id INTEGER NOT NULL, " - + "geometry TEXT" + + "geometry TEXT, " + + "vec TEXT, " + + "guess INTEGER DEFAULT 0" + ")", -1, out stmt); assert(res == Sqlite.OK); @@ -58,10 +61,10 @@ public class FaceLocationTable : DatabaseTable { return instance; } - public FaceLocationRow add(FaceID face_id, PhotoID photo_id, string geometry) throws DatabaseError { + public FaceLocationRow add(FaceID face_id, PhotoID photo_id, string geometry, string? vec = null) throws DatabaseError { Sqlite.Statement stmt; int res = db.prepare_v2( - "INSERT INTO FaceLocationTable (face_id, photo_id, geometry) VALUES (?, ?, ?)", + "INSERT INTO FaceLocationTable (face_id, photo_id, geometry, vec) VALUES (?, ?, ?, ?)", -1, out stmt); assert(res == Sqlite.OK); @@ -71,6 +74,9 @@ public class FaceLocationTable : DatabaseTable { assert(res == Sqlite.OK); res = stmt.bind_text(3, geometry); assert(res == Sqlite.OK); + if (vec == null) vec = ""; + res = stmt.bind_text(4, vec); + assert(res == Sqlite.OK); res = stmt.step(); if (res != Sqlite.DONE) @@ -81,6 +87,7 @@ public class FaceLocationTable : DatabaseTable { row.face_id = face_id; row.photo_id = photo_id; row.geometry = geometry; + row.vec = vec; return row; } @@ -88,7 +95,7 @@ public class FaceLocationTable : DatabaseTable { public Gee.List<FaceLocationRow?> get_all_rows() throws DatabaseError { Sqlite.Statement stmt; int res = db.prepare_v2( - "SELECT id, face_id, photo_id, geometry FROM FaceLocationTable", + "SELECT id, face_id, photo_id, geometry, vec FROM FaceLocationTable", -1, out stmt); assert(res == Sqlite.OK); @@ -107,6 +114,7 @@ public class FaceLocationTable : DatabaseTable { row.face_id = FaceID(stmt.column_int64(1)); row.photo_id = PhotoID(stmt.column_int64(2)); row.geometry = stmt.column_text(3); + row.vec = stmt.column_text(4); rows.add(row); } @@ -195,4 +203,63 @@ public class FaceLocationTable : DatabaseTable { if (res != Sqlite.DONE) throw_error("FaceLocationTable.update_face_location_serialized_geometry", res); } + + public void update_face_location_face_data(FaceLocation face_location) + throws DatabaseError { + Sqlite.Statement stmt; + int res = db.prepare_v2("UPDATE FaceLocationTable SET geometry=?, vec=? WHERE id=?", -1, out stmt); + assert(res == Sqlite.OK); + + FaceLocationData face_data = face_location.get_face_data(); + res = stmt.bind_text(1, face_data.geometry); + assert(res == Sqlite.OK); + res = stmt.bind_text(2, face_data.vec); + assert(res == Sqlite.OK); + res = stmt.bind_int64(3, face_location.get_face_location_id().id); + assert(res == Sqlite.OK); + + res = stmt.step(); + if (res != Sqlite.DONE) + throw_error("FaceLocationTable.update_face_location_serialized_geometry", res); + } + public Gee.List<FaceLocationRow?> get_face_ref_vecs(Gee.List<FaceRow?> face_rows) + throws DatabaseError { + Sqlite.Statement stmt; + + string[] where_in = {}; + foreach (var r in face_rows) { + if (r != null) where_in += "?"; + } + int res = db.prepare_v2( + "SELECT id, face_id, photo_id, geometry, vec FROM FaceLocationTable WHERE photo_id IN (%s)" + .printf(string.joinv(",", where_in)), + -1, out stmt); + assert(res == Sqlite.OK); + int c = 1; + foreach (var r in face_rows) { + if (r != null) { + res = stmt.bind_int64(c, r.ref.id); + assert(res == Sqlite.OK); + } + c++; + } + + Gee.List<FaceLocationRow?> rows = new Gee.ArrayList<FaceLocationRow?>(); + for (;;) { + res = stmt.step(); + if (res == Sqlite.DONE) + break; + else if (res != Sqlite.ROW) + throw_error("FaceLocationTable.get_face_ref_vecs", res); + + FaceLocationRow row = new FaceLocationRow(); + row.face_location_id = FaceLocationID(stmt.column_int64(0)); + row.face_id = FaceID(stmt.column_int64(1)); + row.photo_id = PhotoID(stmt.column_int64(2)); + row.geometry = stmt.column_text(3); + row.vec = stmt.column_text(4); + rows.add(row); + } + return rows; + } } diff --git a/src/db/FaceTable.vala b/src/db/FaceTable.vala index 4836910..e799f97 100644 --- a/src/db/FaceTable.vala +++ b/src/db/FaceTable.vala @@ -25,7 +25,9 @@ public struct FaceID { public class FaceRow { public FaceID face_id; public string name; - public time_t time_created; + public int64 time_created; + public PhotoID ref; + public string vec; } public class FaceTable : DatabaseTable { @@ -40,7 +42,8 @@ public class FaceTable : DatabaseTable { + "(" + "id INTEGER NOT NULL PRIMARY KEY, " + "name TEXT NOT NULL, " - + "time_created TIMESTAMP" + + "time_created TIMESTAMP, " + + "ref INTEGER DEFAULT -1" + ")", -1, out stmt); assert(res == Sqlite.OK); @@ -62,7 +65,7 @@ public class FaceTable : DatabaseTable { out stmt); assert(res == Sqlite.OK); - time_t time_created = (time_t) now_sec(); + var time_created = now_sec(); res = stmt.bind_text(1, name); assert(res == Sqlite.OK); @@ -129,7 +132,7 @@ public class FaceTable : DatabaseTable { FaceRow row = new FaceRow(); row.face_id = face_id; row.name = stmt.column_text(0); - row.time_created = (time_t) stmt.column_int64(1); + row.time_created = stmt.column_int64(1); return row; } @@ -153,7 +156,7 @@ public class FaceTable : DatabaseTable { FaceRow row = new FaceRow(); row.face_id = FaceID(stmt.column_int64(0)); row.name = stmt.column_text(1); - row.time_created = (time_t) stmt.column_int64(2); + row.time_created = stmt.column_int64(2); rows.add(row); } @@ -164,4 +167,47 @@ public class FaceTable : DatabaseTable { public void rename(FaceID face_id, string new_name) throws DatabaseError { update_text_by_id_2(face_id.id, "name", new_name); } + + public void set_reference(FaceID face_id, PhotoID photo_id) + throws DatabaseError { + Sqlite.Statement stmt; + int res = db.prepare_v2("UPDATE FaceTable SET ref=? WHERE id=?", -1, out stmt); + assert(res == Sqlite.OK); + res = stmt.bind_int64(1, photo_id.id); + assert(res == Sqlite.OK); + res = stmt.bind_int64(2, face_id.id); + assert(res == Sqlite.OK); + + res = stmt.step(); + if (res != Sqlite.DONE) + throw_error("FaceTable.set_reference", res); + } + + public Gee.List<FaceRow?> get_ref_rows() throws DatabaseError { + Sqlite.Statement stmt; + int res = db.prepare_v2("SELECT id, name, time_created, ref FROM FaceTable WHERE ref != -1", -1, + out stmt); + assert(res == Sqlite.OK); + + Gee.List<FaceRow?> rows = new Gee.ArrayList<FaceRow?>(); + + for (;;) { + res = stmt.step(); + if (res == Sqlite.DONE) + break; + else if (res != Sqlite.ROW) + throw_error("FaceTable.get_all_rows", res); + + // res == Sqlite.ROW + FaceRow row = new FaceRow(); + row.face_id = FaceID(stmt.column_int64(0)); + row.name = stmt.column_text(1); + row.time_created = stmt.column_int64(2); + row.ref = PhotoID(stmt.column_int64(3)); + + rows.add(row); + } + + return rows; + } } diff --git a/src/db/PhotoTable.vala b/src/db/PhotoTable.vala index 24cec86..4e3f672 100644 --- a/src/db/PhotoTable.vala +++ b/src/db/PhotoTable.vala @@ -44,9 +44,7 @@ public struct ImportID { } public static ImportID generate() { - TimeVal timestamp = TimeVal(); - timestamp.get_current_time(); - int64 id = timestamp.tv_sec; + int64 id = GLib.get_real_time () / Util.USEC_PER_SEC; return ImportID(id); } @@ -72,7 +70,7 @@ public struct ImportID { public class PhotoRow { public PhotoID photo_id; public BackingPhotoRow master; - public time_t exposure_time; + public DateTime? exposure_time; public ImportID import_id; public EventID event_id; public Orientation orientation; @@ -80,13 +78,14 @@ public class PhotoRow { public string md5; public string thumbnail_md5; public string exif_md5; - public time_t time_created; + public int64 time_created; public uint64 flags; public Rating rating; public string title; + public GpsCoords gps_coords; public string comment; public string? backlinks; - public time_t time_reimported; + public int64 time_reimported; public BackingPhotoID editable_id; public bool metadata_dirty; @@ -103,6 +102,10 @@ public class PhotoRow { development_ids = new BackingPhotoID[RawDeveloper.as_array().length]; foreach (RawDeveloper d in RawDeveloper.as_array()) development_ids[d] = BackingPhotoID(); + gps_coords = GpsCoords(); + development_ids = new BackingPhotoID[RawDeveloper.as_array().length]; + foreach (RawDeveloper d in RawDeveloper.as_array()) + development_ids[d] = BackingPhotoID(); } } @@ -140,6 +143,9 @@ public class PhotoTable : DatabaseTable { + "develop_shotwell_id INTEGER DEFAULT -1, " + "develop_camera_id INTEGER DEFAULT -1, " + "develop_embedded_id INTEGER DEFAULT -1, " + + "has_gps INTEGER DEFAULT -1, " + + "gps_lat REAL, " + + "gps_lon REAL, " + "comment TEXT" + ")", -1, out stmt); assert(res == Sqlite.OK); @@ -209,12 +215,12 @@ public class PhotoTable : DatabaseTable { 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 (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", + + "exif_md5, time_created, file_format, title, rating, editable_id, developer, has_gps, gps_lat, gps_lon, comment) " + + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", -1, out stmt); assert(res == Sqlite.OK); - ulong time_created = now_sec(); + var time_created = now_sec(); res = stmt.bind_text(1, photo_row.master.filepath); assert(res == Sqlite.OK); @@ -224,9 +230,17 @@ public class PhotoTable : DatabaseTable { 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); + if (photo_row.master.timestamp == null) { + res = stmt.bind_null(5); + } else { + res = stmt.bind_int64(5, photo_row.master.timestamp.to_unix()); + } assert(res == Sqlite.OK); - res = stmt.bind_int64(6, photo_row.exposure_time); + if (photo_row.exposure_time == null) { + res = stmt.bind_null(6); + } else { + res = stmt.bind_int64(6, photo_row.exposure_time.to_unix()); + } assert(res == Sqlite.OK); res = stmt.bind_int(7, photo_row.master.original_orientation); assert(res == Sqlite.OK); @@ -254,7 +268,13 @@ public class PhotoTable : DatabaseTable { 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); + res = stmt.bind_int(20, photo_row.gps_coords.has_gps); + assert(res == Sqlite.OK); + res = stmt.bind_double(21, photo_row.gps_coords.latitude); + assert(res == Sqlite.OK); + res = stmt.bind_double(22, photo_row.gps_coords.longitude); + assert(res == Sqlite.OK); + res = stmt.bind_text(23, photo_row.comment); assert(res == Sqlite.OK); res = stmt.step(); @@ -269,7 +289,7 @@ public class PhotoTable : DatabaseTable { 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.time_created = time_created; photo_row.flags = 0; return photo_row.photo_id; @@ -285,11 +305,12 @@ public class PhotoTable : DatabaseTable { 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 = ? " + + "exif_md5 = ?, thumbnail_md5 = ?, file_format = ?, title = ?, " + + "has_gps = ?, gps_lat = ?, gps_lon = ?, time_reimported = ? " + "WHERE id = ?", -1, out stmt); assert(res == Sqlite.OK); - time_t time_reimported = (time_t) now_sec(); + var time_reimported = now_sec(); res = stmt.bind_int(1, row.master.dim.width); assert(res == Sqlite.OK); @@ -297,9 +318,13 @@ public class PhotoTable : DatabaseTable { assert(res == Sqlite.OK); res = stmt.bind_int64(3, row.master.filesize); assert(res == Sqlite.OK); - res = stmt.bind_int64(4, row.master.timestamp); + res = stmt.bind_int64(4, row.master.timestamp.to_unix()); assert(res == Sqlite.OK); - res = stmt.bind_int64(5, row.exposure_time); + if (row.exposure_time == null) { + res = stmt.bind_null(5); + } else { + res = stmt.bind_int64(5, row.exposure_time.to_unix()); + } assert(res == Sqlite.OK); res = stmt.bind_int(6, row.master.original_orientation); assert(res == Sqlite.OK); @@ -315,9 +340,15 @@ public class PhotoTable : DatabaseTable { assert(res == Sqlite.OK); res = stmt.bind_text(12, row.title); assert(res == Sqlite.OK); - res = stmt.bind_int64(13, time_reimported); + res = stmt.bind_int(13, row.gps_coords.has_gps); + assert(res == Sqlite.OK); + res = stmt.bind_double(14, row.gps_coords.latitude); + assert(res == Sqlite.OK); + res = stmt.bind_double(15, row.gps_coords.longitude); + assert(res == Sqlite.OK); + res = stmt.bind_int64(16, time_reimported); assert(res == Sqlite.OK); - res = stmt.bind_int64(14, row.photo_id.id); + res = stmt.bind_int64(17, row.photo_id.id); assert(res == Sqlite.OK); res = stmt.step(); @@ -328,7 +359,7 @@ public class PhotoTable : DatabaseTable { row.orientation = row.master.original_orientation; } - public bool master_exif_updated(PhotoID photoID, int64 filesize, long timestamp, + public bool master_exif_updated(PhotoID photoID, int64 filesize, DateTime timestamp, string md5, string? exif_md5, string? thumbnail_md5, PhotoRow row) { Sqlite.Statement stmt; int res = db.prepare_v2( @@ -338,7 +369,7 @@ public class PhotoTable : DatabaseTable { res = stmt.bind_int64(1, filesize); assert(res == Sqlite.OK); - res = stmt.bind_int64(2, timestamp); + res = stmt.bind_int64(2, timestamp.to_unix()); assert(res == Sqlite.OK); res = stmt.bind_text(3, md5); assert(res == Sqlite.OK); @@ -372,7 +403,7 @@ public class PhotoTable : DatabaseTable { // 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 + // For further details, please see https://bugzilla.gnome.org/show_bug.cgi?id=718194 and // https://bugzilla.gnome.org/show_bug.cgi?id=663818 . private void validate_orientation(PhotoRow row) { if ((row.orientation < Orientation.MIN) || @@ -390,7 +421,7 @@ public class PhotoTable : DatabaseTable { + "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 " + + "develop_camera_id, develop_embedded_id, has_gps, gps_lat, gps_lon, comment " + "FROM PhotoTable WHERE id=?", -1, out stmt); assert(res == Sqlite.OK); @@ -406,8 +437,12 @@ public class PhotoTable : DatabaseTable { 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.master.timestamp = new DateTime.from_unix_utc(stmt.column_int64(4)); + if (stmt.column_type(5) == Sqlite.NULL) { + row.exposure_time = null; + } else { + row.exposure_time = new DateTime.from_unix_utc(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); @@ -416,13 +451,13 @@ public class PhotoTable : DatabaseTable { 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.time_created = 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.time_reimported = 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)) : @@ -430,7 +465,10 @@ public class PhotoTable : DatabaseTable { 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); + row.gps_coords.has_gps = stmt.column_int(27); + row.gps_coords.latitude = stmt.column_double(28); + row.gps_coords.longitude = stmt.column_double(29); + row.comment = stmt.column_text(30); return row; } @@ -442,7 +480,7 @@ public class PhotoTable : DatabaseTable { + "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", + + "develop_embedded_id, has_gps, gps_lat, gps_lon, comment FROM PhotoTable", -1, out stmt); assert(res == Sqlite.OK); @@ -454,8 +492,12 @@ public class PhotoTable : DatabaseTable { 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.master.timestamp = new DateTime.from_unix_utc(stmt.column_int64(5)); + if (stmt.column_type(6) == Sqlite.NULL) { + row.exposure_time = null; + } else { + row.exposure_time = new DateTime.from_unix_utc(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); @@ -464,13 +506,13 @@ public class PhotoTable : DatabaseTable { 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.time_created = 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.time_reimported = 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)) : @@ -478,7 +520,10 @@ public class PhotoTable : DatabaseTable { 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); + row.gps_coords.has_gps = stmt.column_int(28); + row.gps_coords.latitude = stmt.column_double(29); + row.gps_coords.longitude = stmt.column_double(30); + row.comment = stmt.column_text(31); validate_orientation(row); @@ -500,9 +545,9 @@ public class PhotoTable : DatabaseTable { 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 (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", + + "file_format, title, has_gps, gps_lat, gps_lon, editable_id, developer, " + + "develop_shotwell_id, develop_camera_id, develop_embedded_id, comment) " + + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", -1, out stmt); assert(res == Sqlite.OK); @@ -514,9 +559,13 @@ public class PhotoTable : DatabaseTable { assert(res == Sqlite.OK); res = stmt.bind_int64(4, original.master.filesize); assert(res == Sqlite.OK); - res = stmt.bind_int64(5, original.master.timestamp); + res = stmt.bind_int64(5, original.master.timestamp.to_unix()); assert(res == Sqlite.OK); - res = stmt.bind_int64(6, original.exposure_time); + if (original.exposure_time == null) { + res = stmt.bind_null(6); + } else { + res = stmt.bind_int64(6, original.exposure_time.to_unix()); + } assert(res == Sqlite.OK); res = stmt.bind_int(7, original.orientation); assert(res == Sqlite.OK); @@ -544,18 +593,23 @@ public class PhotoTable : DatabaseTable { assert(res == Sqlite.OK); res = stmt.bind_text(19, original.title); assert(res == Sqlite.OK); - res = stmt.bind_int64(20, editable_id.id); + res = stmt.bind_int(20, original.gps_coords.has_gps); assert(res == Sqlite.OK); - - res = stmt.bind_text(21, original.developer.to_string()); + res = stmt.bind_double(21, original.gps_coords.latitude); assert(res == Sqlite.OK); - res = stmt.bind_int64(22, develop_shotwell.id); + res = stmt.bind_double(22, original.gps_coords.longitude); assert(res == Sqlite.OK); - res = stmt.bind_int64(23, develop_camera_id.id); + res = stmt.bind_int64(23, editable_id.id); assert(res == Sqlite.OK); - res = stmt.bind_int64(24, develop_embedded_id.id); + res = stmt.bind_text(24, original.developer.to_string()); assert(res == Sqlite.OK); - res = stmt.bind_text(25, original.comment); + res = stmt.bind_int64(25, develop_shotwell.id); + assert(res == Sqlite.OK); + res = stmt.bind_int64(26, develop_camera_id.id); + assert(res == Sqlite.OK); + res = stmt.bind_int64(27, develop_embedded_id.id); + assert(res == Sqlite.OK); + res = stmt.bind_text(28, original.comment); assert(res == Sqlite.OK); res = stmt.step(); @@ -572,7 +626,15 @@ public class PhotoTable : DatabaseTable { 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 void set_gps_coords(PhotoID photo_id, GpsCoords new_gps_coords) throws DatabaseError { + update_int_by_id_2(photo_id.id, "has_gps", new_gps_coords.has_gps); + if (new_gps_coords.has_gps > 0) { + update_double_by_id_2(photo_id.id, "gps_lat", new_gps_coords.latitude); + update_double_by_id_2(photo_id.id, "gps_lon", new_gps_coords.longitude); + } + } + public bool set_comment(PhotoID photo_id, string? new_comment) { return update_text_by_id(photo_id.id, "comment", new_comment != null ? new_comment : ""); } @@ -581,12 +643,12 @@ public class PhotoTable : DatabaseTable { 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 void update_timestamp(PhotoID photo_id, DateTime timestamp) throws DatabaseError { + update_int64_by_id_2(photo_id.id, "timestamp", timestamp.to_unix()); } - public bool set_exposure_time(PhotoID photo_id, time_t time) { - return update_int64_by_id(photo_id.id, "exposure_time", (int64) time); + public bool set_exposure_time(PhotoID photo_id, DateTime time) { + return update_int64_by_id(photo_id.id, "exposure_time", time.to_unix()); } public void set_import_id(PhotoID photo_id, ImportID import_id) throws DatabaseError { @@ -1051,6 +1113,16 @@ public class PhotoTable : DatabaseTable { public void remove_development(PhotoRow row, RawDeveloper rd) throws DatabaseError { update_raw_development(row, rd, BackingPhotoID()); } + + public static void upgrade_for_unset_timestamp() throws DatabaseError { + Sqlite.Statement stmt; + int res = db.prepare_v2("UPDATE PhotoTable SET exposure_time = NULL WHERE exposure_time = '0'", -1, out stmt); + assert(res == Sqlite.OK); + res = stmt.step(); + if (res != Sqlite.DONE) { + throw_error("PhotoTable.upgrade_for_unset_timestamp", res); + } + } } @@ -1084,10 +1156,10 @@ public struct BackingPhotoID { public class BackingPhotoRow { public BackingPhotoID id; - public time_t time_created; + public int64 time_created; public string? filepath = null; public int64 filesize; - public time_t timestamp; + public DateTime? timestamp; public PhotoFileFormat file_format; public Dimensions dim; public Orientation original_orientation; @@ -1095,15 +1167,21 @@ public class BackingPhotoRow { public bool matches_file_info(FileInfo info) { if (filesize != info.get_size()) return false; + + if (timestamp == null) + return false; - return timestamp == info.get_modification_time().tv_sec; + return timestamp.equal(info.get_modification_date_time()); } public bool is_touched(FileInfo info) { if (filesize != info.get_size()) return false; + + if (timestamp == null) + return true; - return timestamp != info.get_modification_time().tv_sec; + return !timestamp.equal(info.get_modification_date_time()); } // Copies another backing photo row into this one. @@ -1162,11 +1240,11 @@ public class BackingPhotoTable : DatabaseTable { -1, out stmt); assert(res == Sqlite.OK); - time_t time_created = (time_t) now_sec(); + var time_created = now_sec(); res = stmt.bind_text(1, state.filepath); assert(res == Sqlite.OK); - res = stmt.bind_int64(2, state.timestamp); + res = stmt.bind_int64(2, state.timestamp.to_unix()); assert(res == Sqlite.OK); res = stmt.bind_int64(3, state.filesize); assert(res == Sqlite.OK); @@ -1208,12 +1286,12 @@ public class BackingPhotoTable : DatabaseTable { BackingPhotoRow row = new BackingPhotoRow(); row.id = id; row.filepath = stmt.column_text(0); - row.timestamp = (time_t) stmt.column_int64(1); + row.timestamp = new DateTime.from_unix_utc(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); + row.time_created = stmt.column_int64(7); return row; } @@ -1227,7 +1305,7 @@ public class BackingPhotoTable : DatabaseTable { -1, out stmt); assert(res == Sqlite.OK); - res = stmt.bind_int64(1, row.timestamp); + res = stmt.bind_int64(1, row.timestamp.to_unix()); assert(res == Sqlite.OK); res = stmt.bind_int64(2, row.filesize); assert(res == Sqlite.OK); @@ -1247,13 +1325,13 @@ public class BackingPhotoTable : DatabaseTable { throw_error("BackingPhotoTable.update", res); } - public void update_attributes(BackingPhotoID id, time_t timestamp, int64 filesize) throws DatabaseError { + public void update_attributes(BackingPhotoID id, DateTime 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); + res = stmt.bind_int64(1, timestamp.to_unix()); assert(res == Sqlite.OK); res = stmt.bind_int64(2, filesize); assert(res == Sqlite.OK); @@ -1273,8 +1351,8 @@ public class BackingPhotoTable : DatabaseTable { 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); + public void update_timestamp(BackingPhotoID id, DateTime timestamp) throws DatabaseError { + update_int64_by_id_2(id.id, "timestamp", timestamp.to_unix()); } } diff --git a/src/db/TagTable.vala b/src/db/TagTable.vala index d650641..ce191c1 100644 --- a/src/db/TagTable.vala +++ b/src/db/TagTable.vala @@ -26,7 +26,7 @@ public class TagRow { public TagID tag_id; public string name; public Gee.Set<string>? source_id_list; - public time_t time_created; + public int64 time_created; } public class TagTable : DatabaseTable { @@ -79,7 +79,7 @@ public class TagTable : DatabaseTable { out stmt); assert(res == Sqlite.OK); - time_t time_created = (time_t) now_sec(); + var time_created = now_sec(); res = stmt.bind_text(1, name); assert(res == Sqlite.OK); @@ -151,7 +151,7 @@ public class TagTable : DatabaseTable { 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); + row.time_created = stmt.column_int64(2); return row; } @@ -176,7 +176,7 @@ public class TagTable : DatabaseTable { 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); + row.time_created = stmt.column_int64(3); rows.add(row); } diff --git a/src/db/TombstoneTable.vala b/src/db/TombstoneTable.vala index 892198f..5c19c5c 100644 --- a/src/db/TombstoneTable.vala +++ b/src/db/TombstoneTable.vala @@ -27,7 +27,7 @@ public class TombstoneRow { public string filepath; public int64 filesize; public string? md5; - public time_t time_created; + public int64 time_created; public Tombstone.Reason reason; } @@ -71,7 +71,7 @@ public class TombstoneTable : DatabaseTable { -1, out stmt); assert(res == Sqlite.OK); - time_t time_created = (time_t) now_sec(); + var time_created = now_sec(); res = stmt.bind_text(1, filepath); assert(res == Sqlite.OK); @@ -124,7 +124,7 @@ public class TombstoneTable : DatabaseTable { 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.time_created = stmt.column_int64(4); row.reason = Tombstone.Reason.unserialize(stmt.column_int(5)); rows[index++] = row; diff --git a/src/db/VideoTable.vala b/src/db/VideoTable.vala index 7bd1bb7..8af1278 100644 --- a/src/db/VideoTable.vala +++ b/src/db/VideoTable.vala @@ -38,20 +38,20 @@ public class VideoRow { public VideoID video_id; public string filepath; public int64 filesize; - public time_t timestamp; + public DateTime timestamp; public int width; public int height; public double clip_duration; public bool is_interpretable; - public time_t exposure_time; + public DateTime? exposure_time; public ImportID import_id; public EventID event_id; public string md5; - public time_t time_created; + public int64 time_created; public Rating rating; public string title; public string? backlinks; - public time_t time_reimported; + public int64 time_reimported; public uint64 flags; public string comment; } @@ -119,7 +119,7 @@ public class VideoTable : DatabaseTable { -1, out stmt); assert(res == Sqlite.OK); - ulong time_created = now_sec(); + var time_created = now_sec(); res = stmt.bind_text(1, video_row.filepath); assert(res == Sqlite.OK); @@ -133,9 +133,13 @@ public class VideoTable : DatabaseTable { assert(res == Sqlite.OK); res = stmt.bind_int64(6, video_row.filesize); assert(res == Sqlite.OK); - res = stmt.bind_int64(7, video_row.timestamp); + res = stmt.bind_int64(7, video_row.timestamp.to_unix()); assert(res == Sqlite.OK); - res = stmt.bind_int64(8, video_row.exposure_time); + if (video_row.exposure_time == null) { + stmt.bind_null(8); + } else { + res = stmt.bind_int64(8, video_row.exposure_time.to_unix()); + } assert(res == Sqlite.OK); res = stmt.bind_int64(9, video_row.import_id.id); assert(res == Sqlite.OK); @@ -159,7 +163,7 @@ public class VideoTable : DatabaseTable { // 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.time_created = time_created; video_row.flags = 0; return video_row.video_id; @@ -208,16 +212,19 @@ public class VideoTable : DatabaseTable { 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); + if (stmt.column_type(6) == Sqlite.NULL) { + row.exposure_time = null; + } else { + row.exposure_time = new DateTime.from_unix_utc(stmt.column_int64(6)); + } 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.time_created = 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.time_reimported = stmt.column_int64(15); row.flags = stmt.column_int64(16); row.comment = stmt.column_text(17); @@ -244,16 +251,20 @@ public class VideoTable : DatabaseTable { 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.timestamp = new DateTime.from_unix_utc(stmt.column_int64(7)); + if (stmt.column_type(8) == Sqlite.NULL) { + row.exposure_time = null; + } else { + row.exposure_time = new DateTime.from_unix_utc(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.time_created = 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.time_reimported = stmt.column_int64(16); row.flags = stmt.column_int64(17); row.comment = stmt.column_text(18); @@ -275,8 +286,8 @@ public class VideoTable : DatabaseTable { 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_exposure_time(VideoID video_id, DateTime time) throws DatabaseError { + update_int64_by_id_2(video_id.id, "exposure_time", time.to_unix()); } public void set_rating(VideoID video_id, Rating rating) throws DatabaseError { @@ -455,8 +466,19 @@ public class VideoTable : DatabaseTable { 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); + public void set_timestamp(VideoID video_id, DateTime timestamp) throws DatabaseError { + update_int64_by_id_2(video_id.id, "timestamp", timestamp.to_unix()); } + + public static void upgrade_for_unset_timestamp() throws DatabaseError { + Sqlite.Statement stmt; + int res = db.prepare_v2("UPDATE VideoTable SET exposure_time = NULL WHERE exposure_time = '0'", -1, out stmt); + assert(res == Sqlite.OK); + res = stmt.step(); + if (res != Sqlite.DONE) { + throw_error("VideoTable.upgrade_for_unset_timestamp", res); + } + } + } |