Photo database front-end, 2nd try

I've reported previously that my attempts to create a simple photo database to manage my negatives and prints failed miserably with OpenOffice Base. I thought that installing a web frontend for SQLite might simplify adding and retrieving the data to a SQL database, but the results were not entirely to my taste. The tool simplified the database creation somewhat, and it is fairly easy to check the rows in the tables. However, there are two limitations:

- if I need all information about a print - the print info, the negative info, and the film info - I have to join three tables. You can do this in the web interface as well, but only by typing the SQL query by hand - this isn't any simpler than doing it on the command line
- as the data are relational (each print belongs to a negative, and this in turn to a film), inserting data with the web interface isn't straightforward. You can easily fill in a form with the data of one table at a time, but you have to run separate queries to find the foreign keys that associate prints, negatives, and films.

I figured my photo database should be easier to manage than this. I recalled the ease with which I can retrieve an address or a phone number with my computer: switch to Emacs (it is open by default anyway), run "Alt-x rolo-grep", and there you go. Why shouldn't "Alt-x photo-find-negatives" provide the same convenience for my photo database?

As photo-mode would just be another front-end for a command-line application (sqlite3 in this case), I figured I could borrow some code from refdb-mode to get started real quick. And presto, within one-and-a-half days I had a feature-complete prototype running. Beat that with any tool or language other than Emacs Lisp.

Before describing how the system works, I'll have to recap how the data are organized. A film is either a 35mm/roll film or a set of sheet films which are developed together in the same tray using the same conditions. All development-related information, such as film type, developer type, developing conditions, goes into the t_film table. Each film may contain one to many negatives (the individual images). The exposure information, as well as a description and a foreign key that associates the negative with its film, go into the t_negative table. Finally I can make one or more prints from each negative. The paper type, exposure tricks, development conditions, and a foreign key which associates the print with a negative, go into the t_print table. I won't have to enter each individual print into this table, but rather different papers, exposure and development conditions that I try out while approaching the "perfect" print.

I settled for a unified naming scheme for each of the items which also simplifies data entry as will be described shortly. Film names look like "YYYYMMDD-XY" using the development date and a numerical suffix if I happen to develop more than one film per day. Each negative receives another two-digit number, i.e. "YYYYMMDD-XY-ZQ". The number ZQ corresponds to the position of the negative on the film roll or in the negative preserver (for sheet film). Each print (or rather print condition) receives yet another two-digit number, as in "YYYYMMDD-XY-ZQ-RS". This number on the back of each print is all it takes to find the relevant information in the database.

The database structure is defined as a SQL script. Run it through the sqlite3 command line client to generate an empty database.

As expected there are three sets of functions to enter new data, one for each table. In order to simplify entering several items at a time, each set has one function which creates an empty template (photo-create-film, photo-create-negative, photo-create-print), and another function that actually sends the data to the database (photo-add-film, photo-add-negative, photo-add-print). After creating and filling in a template, you can reuse it any number of times, so you'll have to change only the variant fields for similar datasets. photo-mode automatically inserts the appropriate foreign keys - all you need to specify is the negative (for print entry) or film (for negative entry) name - these are simple to remember due to the strict naming scheme.

Once the data are in the database, we'll need several functions to get at the data again. These functions are listed below:

- photo-find-negatives: searches the negative table for datasets that contain your search term in the location or description field. This is useful to find negatives if you know where they were taken, or what they show.
- photo-list-prints-of-negative, photo-list-prints-of-film: shows all prints that were made from a particular negative or film, respectively
- photo-print-info, photo-negative-info, photo-film-info: shows all information related to a particular print, negative, or film, respectively. The print query also displays the associated negative and film info. Accordingly, the negative query also displays the associated film info.
- photo-list-prints-of-film, photo-list-prints-of-negative: display a list of prints along with the negative description that were made from a particular film or negative, respectively.
- the preceding two categories also have equivalent functions that use a range of marked text, instead of text that you enter at the prompt, to retrieve the information. These functions are convenient to reuse matches returned by the *-find-* or *-list-* family of functions for further queries.

All this is coded in roughly 800 lines of Emacs Lisp. After some cleanup and writing brief instructions, I'll make the package available to the public, i.e. to all three photographers that are also fond of Emacs :-).


Markus Hoenicka meint:

I've published the code at Sourceforge a while ago. See the web page:


and the project page (downloads and such):
Montag 19 Juli 16:59

Mein Kommentar

Dieser Artikel ist geschlossen. Keine Kommentare mehr möglich.