(Republished from Cincom Smalltalk Tech Tips)

I’ve been using Linux as my primary desktop platform for some years now. I generally try to keep up with the releases and stick with the default choices as much as possible. Recently I tried to use F-Spot because it’s the default photo manager for GNOME now. It’s got some nice features and is generally OK, although not very flexible, very much within the spirit of today’s UI design dogmas (“You can’t handle flexibility, you … user … you!”). Anyway, I noticed that F-Spot uses sqlite3 as its database, so I wasn’t too afraid to spend some effort tagging pictures etc.

Recently, as I was upgrading my computers, I decided to move the pictures to a different location. Unfortunately F-Spot doesn’t seem to provide a way to update its database accordingly. Poking around in the database it seemed to be fairly simple database update, so I decided to whip up a quick, Glorp based, database mapping and do the update with a script.

The database has a PHOTOS table with following definition:

CREATE TABLE photos (
	id			INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, 
	time			INTEGER NOT NULL, 
	base_uri		STRING NOT NULL, 
	filename		STRING NOT NULL, 
	description		TEXT NOT NULL, 
	roll_id			INTEGER NOT NULL, 
	default_version_id	INTEGER NOT NULL, 
	rating			INTEGER NULL, 
	md5_sum			TEXT NULL
);

The path to the picture is stored in the base_uri field, usually looking something like ‘file:///home/user/Photos/…’. I needed to change all of them to something like ‘file:///pub/photos….’ instead. So, first I created a Photo class with a simplified version of the above:

	id <Integer> database id
	time <Timestamp> time taken
	base_uri <String> location of the photo
	filename <String> location of the photo
	description <String> any notes

Mappings are defined on subclasses of DescriptorSystem, so I created one and started with description of the class model:

classModelForPhoto: aModel

	aModel newAttributeNamed: #id.
	aModel newAttributeNamed: #time type: Timestamp.
	aModel newAttributeNamed: #base_uri type: String.
	aModel newAttributeNamed: #filename type: String.
	aModel newAttributeNamed: #description type: String.

then the table description.

tableForPHOTOS: aTable

	(aTable createFieldNamed: 'id' type: (self fakeSequenceFor: aTable)) bePrimaryKey.
	(aTable createFieldNamed: 'time' type: platform int4) beIndexed.
	aTable createFieldNamed: 'base_uri' type: platform varchar.
	aTable createFieldNamed: 'filename' type: platform varchar.
	aTable createFieldNamed: 'description' type: platform text.

and finally the mapping between the two:

descriptorForPhoto: aDescriptor

	| table |
	table := self tableNamed: 'PHOTOS'.
	aDescriptor table: table.
	(aDescriptor newMapping: DirectMapping) from: #id to: (table fieldNamed: 'id').
	(aDescriptor newMapping: DirectMapping) from: #base_uri to: (table fieldNamed: 'base_uri').
	(aDescriptor newMapping: DirectMapping) from: #filename to: (table fieldNamed: 'filename').
	(aDescriptor newMapping: DirectMapping) from: #time to: (table fieldNamed: 'time').
	(aDescriptor newMapping: DirectMapping) from: #description to: (table fieldNamed: 'description').

With these in place I could try to connect to the database. For that I needed to provide the connection information, so I added 2 class side methods:

newLogin

	^(Login new)
		database: SQLite3Platform new;
		connectString: (PortableFilename named: '$(HOME)/.config/f-spot/photos.db') asFilename asString.

newSession

	^self sessionForLogin: self newLogin

With this I could invoke #newSession and get a connected session back. Time to start experimenting with the database.

Reading a photo is easy:

	session readOneOf: Photo.

To figure out what are all the places from which I’ve imported pictures I used this:

	query := (Query read: Photo) retrieve: [ :e | e base_uri ].
	(session execute: query) asSet.

It reads all the base_uri values and puts them into a Set. A smarter database query can do this more efficiently, but this was fine as well in my database of about 6k pictures. I found out I imported pictures from two locations. I decided to deal with them one by one. To perform the update I ran the following:

photos := session read: Photo where: [ :p | p base_uri like: '%home/mk/Photos%' ].
session modify: photos in: [
	photos do: [ :p | p base_uri: (p base_uri copyReplaceAll: 'home/mk/Photos' with: 'pub/photos') ] ].

It reads each photo with the selected location in base_uri and updates it with the new one. Then I did the same for the second location. The entire update operation took less than 20 seconds. Later I found out that there’s a plugin for F-Spot for this sort of migration, but its comment said that it can take a few hours. I don’t know how big a database they had in mind, but that sounds a bit excessive still.

Since then I fleshed out the mappings, created a Glorp Workbook so that it’s more convenient for quick experiments (you get a toolbar button for quick access) and packed it all up. I published the package to the public repository as F-Spot, hoping it might be useful to someone else too. As far as future plans go, there really aren’t any beyond finishing the mapping layer. One thing I’m considering is that I find the imports into F-Spot excruciatingly slow. I might use this package for that task instead.