A flashback to a few years in the past, this has sat as a draft for too long. See previously: Getting your photos out of Shotwell and Every time I program a Mac…
So…. what does the code to read the Shotwell database look like?
First, we’re going to have to import some of the modules we need:
import Foundation
import SQLite3
import SwiftUI
import PhotosI forget what SwiftUI here is for… I think it’s for my bit of integration with the UI I have.
From here I can start to model the bits of the Shotwell database that I’ll possibly need, but using more idiomatic Swift than raw SQL queries in random places. This is what I ended up for having information about a Photo:
class ShotwellPhotoInfo : Codable, Identifiable, Hashable {
static func == (lhs: ShotwellPhotoInfo, rhs: ShotwellPhotoInfo) -> Bool {
return lhs.id == rhs.id
}
func hash(into hasher: inout Hasher) {
hasher.combine(id)
}
var id : Int32 = 0
var filename : String = ""
var translated_filename : String = ""
var orientation : Int32 = 0
var original_orientation : Int32 = 0
var import_id : Int32 = 0
var event_id : Int32 = 0
var event : ShotwellEventInfo?
var transformations : String = ""
var md5 : String = ""
var flags: Int32 = 0
var rating: Int32 = 0
var title: String = ""
var raw_backlinks : String = ""
var backlinks : ShotwellBacklinks?
var comment : String = ""
var has_gps : Bool = false
var gps_lat : Double = 0.0
var gps_lon : Double = 0.0
}The comparison function is just comparing the id, which since that’s a primary key in the database, are unique. The hash function also just acts on id because of this.
The translated_filename is not in the Shotwell database, but rather for me to do various transformations on the filename in the shotwell database so that I can actually find the file. The natural translation is going to be for wherever I have mounted my photos, but there were some other weirdness likely due to some symlinks and such I’ve had.
The Shotwell Database Architecture documentation says this:
https://wiki.gnome.org/Apps/Shotwell/Architecture/Database
- backlinks are persistent links to container objects, i.e. Events and Tags. These are used when a Photo has been removed from one (for example, it’s been trashed or marked offline). If the Photo is returned to the main heap of photos (i.e. untrashed), these backlinks are used to restore its connections to the containers.
So it appears that Backlinks may be rather useful to find out everything I need to know about a Photo that I am looking to import. Thus, we will need to model Events and Tags as well, which are familiar concepts from the Shotwell UI.
Since there are Tags in Shotwell, I need to model them as well:
class ShotwellTag : Codable, Identifiable, Hashable {
static func == (lhs: ShotwellTag, rhs: ShotwellTag) -> Bool {
return lhs.id == rhs.id
}
func hash(into hasher: inout Hasher) {
hasher.combine(id)
}
let id : Int32
let name : String
let photo_id_list : [Int32]
let time_created : Int32
let photos : [ShotwellPhotoInfo]
init(id: Int32, name: String, photo_id_list: [Int32], time_created: Int32, photos: [ShotwellPhotoInfo]) {
self.id = id
self.name = name
self.photo_id_list = photo_id_list
self.time_created = time_created
self.photos = photos
}
}Again, we have an id as a primary key, and the photos in a tag are referenced as a list of IDs.
For an Event, this is what I ended up with:
struct ShotwellEventInfo : Codable, Identifiable {
let id : Int32
let name : String
let primary_photo_id : Int32
let time_created : Int32
let primary_source_id : Int32
let comment : String
}and for backlinks:
class ShotwellBacklinks : Codable {
var tag : [ShotwellTag?] = []
var event : [String] = []
var other : [String] = []
}But as for just getting some basic information out of the database? That we can start off with by having a class for opening it, and run some simple queries, and even display them in the UI just to prove we can.
class ShotwellDatabase {
var db: OpaquePointer?
var import_query: OpaquePointer?
var dbPath : String?
struct db_version {
var schema_version: Int32 = 0
var app_version: String = ""
init() {
schema_version = 0
app_version = ""
}
}
func open_db(shotwellDB: String) -> Bool {
dbPath = shotwellDB
if sqlite3_open(shotwellDB, &db) == SQLITE_OK {
print("Opened Shotwell Database!")
}
return db != nil
}
func get_db_version() -> db_version {
let query = "SELECT schema_version, app_version FROM VersionTable"
var queryStatement: OpaquePointer?
var r = db_version()
if sqlite3_prepare_v2(db, query, -1, &queryStatement, nil) == SQLITE_OK {
if sqlite3_step(queryStatement) == SQLITE_ROW {
r.schema_version = sqlite3_column_int(queryStatement, 0)
guard let app_version = sqlite3_column_text(queryStatement, 1) else {
print("Couldn't get app version")
r.app_version = ""
return r
}
r.app_version = String(cString: app_version)
}
}
return r
}The database version is more about just verifying I could link things together than anything actually useful. Continuing the ShotwellDatabase class, getting the total number of photos is pretty easy (added to the ShotwellDatabase class above):
func get_photos_count() -> Int64 {
let query = "SELECT COUNT(id) FROM PhotoTable"
var queryStatement: OpaquePointer?
if sqlite3_prepare_v2(db, query, -1, &queryStatement, nil) == SQLITE_OK {
if sqlite3_step(queryStatement) == SQLITE_ROW {
return sqlite3_column_int64(queryStatement, 0)
}
}
return 0
}Again, that’s mostly just useful for some nice “oh look, I linked up the UI to the database” rather than anything of use (although progress bars may want to use it).
So, now that we can talk to the database, we can go and set things up to iterate over all the photos and load them up into memory. I’m not doing anything too fancy here as this is all very quick and dirty, and was literally the first time writing any Swift code.
The below code starts by opening the Database, then get_next_photo will iterate over everything before done_all_photos can close things up. The commented out block of code below does something pretty neat, and that’s print out a JSON representation of our photo. I found this incredibly easy to set up in Swift.
func start_get_all_photos() -> OpaquePointer? {
var db: OpaquePointer?
if sqlite3_open(dbPath, &db) == SQLITE_OK {
print("Opened Shotwell Database!")
}
start_import(db: db)
return db
}
func get_next_photo(db: OpaquePointer?) -> ShotwellPhotoInfo? {
var photo: ShotwellPhotoInfo?
do {
photo = db_get_next_photo(db: db)
if photo != nil {
/* let encoder = JSONEncoder()
encoder.outputFormatting = .prettyPrinted
let jsonData = try encoder.encode(photo)
let jsonString = String(data: jsonData, encoding: .utf8)!
photo_info = jsonString
print(jsonString)
do_some_photo_things()
progress += 1 */
all_photos[photo!.id] = photo
}
} catch { print(error) }
return photo
}
func done_all_photos(db: OpaquePointer?) {
sqlite3_close(db)
}For parsing that row, I have the following class:
class ShotwellPhotoInfo : Codable, Identifiable, Hashable {
static func == (lhs: ShotwellPhotoInfo, rhs: ShotwellPhotoInfo) -> Bool {
return lhs.id == rhs.id
}
func hash(into hasher: inout Hasher) {
hasher.combine(id)
}
var id : Int32 = 0
var filename : String = ""
var translated_filename : String = ""
var orientation : Int32 = 0
var original_orientation : Int32 = 0
var import_id : Int32 = 0
var event_id : Int32 = 0
var event : ShotwellEventInfo?
var transformations : String = ""
var md5 : String = ""
var flags: Int32 = 0
var rating: Int32 = 0
var title: String = ""
var raw_backlinks : String = ""
var backlinks : ShotwellBacklinks?
var comment : String = ""
var has_gps : Bool = false
var gps_lat : Double = 0.0
var gps_lon : Double = 0.0
}
and function in the ShotwellDatabase class:
func db_get_next_photo(db: OpaquePointer?) -> ShotwellPhotoInfo? {
var r = ShotwellPhotoInfo()
if sqlite3_step(import_query) == SQLITE_ROW {
r.id = sqlite3_column_int(import_query, 0)
r.filename = String(cString: sqlite3_column_text(import_query, 1))
r.orientation = sqlite3_column_int(import_query, 2)
r.original_orientation = sqlite3_column_int(import_query, 3)
r.import_id = sqlite3_column_int(import_query, 4)
r.event_id = sqlite3_column_int(import_query, 5)
// FIXME: r.event = get_event_info(db: db, id: r.event_id)
let transformation = sqlite3_column_text(import_query, 6)
if let t = transformation {
r.transformations = String(cString: t)
}
let md5 = sqlite3_column_text(import_query, 7)
if let t = md5 {
r.md5 = String(cString: t)
}
r.flags = sqlite3_column_int(import_query, 8)
r.rating = sqlite3_column_int(import_query, 9)
let title = sqlite3_column_text(import_query, 10)
if let t = title {
r.title = String(cString: t)
}
let backlinks = sqlite3_column_text(import_query, 11)
if let t = backlinks {
r.raw_backlinks = String(cString: t)
r.backlinks = process_backlinks(all_backlinks: r.raw_backlinks)
}
let comment = sqlite3_column_text(import_query, 12)
if let t = comment {
r.comment = String(cString: t)
}
r.has_gps = sqlite3_column_int(import_query, 13) != 0
r.gps_lat = sqlite3_column_double(import_query, 14)
r.gps_lon = sqlite3_column_double(import_query, 15)
} else {
return nil
}
return r
}So with that combined, I ended up with my global var all_photos: [Int32: ShotwellPhotoInfo] = [:] containing all the photos in the shotwell database able to be looked up by ID. I’m sure there’s a more idiomatic way to do this in Swift… but the learning as I went, and need to only ever run this once certainly encouraged me to take some shortcuts.
There’s two interesting bits in that code. There’s the process_backlinks function, which does the thing of processing this giant text string that Shotwell stores that points the photo back to everywhere it has been referenced, as well as the FIXME around event information. I ended up giving up on translating Events, as these were never something I used much.
Parsing the backlinks I did with this:
func process_backlinks(all_backlinks : String) -> ShotwellBacklinks {
var r = ShotwellBacklinks()
let backlinks = all_backlinks.split(separator: "\n").map( {String($0)})
for backlink in backlinks {
let kv = backlink.split(separator: "=")
switch kv.first {
case "tag":
let tag_ids = kv[1].split(separator: "|").map( {Int32($0)})
for tag_id in tag_ids {
if tag_id != nil {
if tag_id! > 0 {
r.tag.append(all_tags[tag_id!])
}
}
}
break
case "event":
let events = kv[1].split(separator: "|").map( {String($0)})
r.event = events
break
default:
r.other.append(contentsOf: kv.map({String($0)}))
}
}
return r
}and the very simple ShotwellBacklinks class:
class ShotwellBacklinks : Codable {
var tag : [ShotwellTag?] = []
var event : [String] = []
var other : [String] = []
}But to start to get a view as to what Tags exist in the shotwell database, we can just go through the TagTable in the Shotwell SQLite database (added to the ShotwellDatabase class above):
func get_all_tags() {
var db: OpaquePointer?
if sqlite3_open(dbPath, &db) == SQLITE_OK {
print("Opened Shotwell Database!")
}
let query = "SELECT id, name, photo_id_list, time_created FROM TagTable"
var q : OpaquePointer?
if sqlite3_prepare_v2(db, query, -1, &q, nil) != SQLITE_OK {
print("Failed to prepare query")
assert(false)
}
while sqlite3_step(q) == SQLITE_ROW {
let r = tag_row_to_ShotwellTag(q: q)
all_tags[r.id] = r
for photo_id in r.photo_id_list {
photoToTags[photo_id]?.append(r)
}
}
sqlite3_finalize(q)
sqlite3_close(db)
}I did the hacky thing and just added var all_tags: [Int32: ShotwellTag] = [:] as a global variable, that I then used in the UI element showing them (well… via a wrapper, but that’s how that goes).
We just now need the tag_row_to_ShotwellTag that’s called above:
func tag_row_to_ShotwellTag(q: OpaquePointer?) -> ShotwellTag {
let id = sqlite3_column_int(q, 0)
let c_name = sqlite3_column_text(q, 1)
let c_photo_id_list = sqlite3_column_text(q, 2)
let time_created = sqlite3_column_int(q, 3 )
var name : String = ""
var photo_id_list : [Int32] = []
var photos : [ShotwellPhotoInfo] = []
if let c_name = c_name {
name = String(cString: c_name)
}
if let c_photo_id_list = c_photo_id_list {
let photo_ids = String(cString: c_photo_id_list).split(separator: ",")
photo_id_list.reserveCapacity(photo_ids.count)
for photo_id in photo_ids {
if photo_id.hasPrefix("thumb") {
var id = photo_id
id.removeFirst(5)
photo_id_list.append(Int32(id, radix: 16)!)
let photo = all_photos[Int32(id, radix: 16)!]
if photo == nil {
print("Couldn't find photo id " + id)
} else {
photos.append(photo!)
}
} else {
print("UNKNOWN PREFIX FOR " + photo_id)
}
}
}
return ShotwellTag(id: id, name: name, photo_id_list: photo_id_list, time_created: time_created, photos: photos)
}