picurl Database Layout

Until now, nearly all store/image data gathered by picurl vanished after exiting the program. As this limits the usefulness of our software, we want to implement a sqlite database backend, which is capable of storing and retrieving the collected data. Furthermore, the user should have the possibility to query image information from the database in an advanced way (e.g. show all pictures from my Olympus e410 Camera that I uploaded to Flickr).

To be more precise, our database model should meet the following demands:

Store Configuration and Settings

  • If the users configures a store permanentely, all entered configuration data is saved under an unique alias name in the database.
  • If the configuration data contains passwords, they should be encrypted. Decryption can take place using a picurl master password.
  • Further properties we want to save for a store:
    • storetype
    • is_writeable
    • recurse_subdirs if the indexing process should also visit subdirectories in the store.
    • is_removeable (e.g. DVD/CD-R)
    • update_by_default (yes|no) specifies if the store should be re-scanned during the standard indexing process. if set to no, the store must be updated explicitely using something like picurl-client update alias. This is useful if you have e.g. a photo-collection on 10 DVD's and don't want to re-insert those with every update-db call.

Images and Metadata

For each image, we should save the following properties:

  • Post-Header-MD5 Sum: We take the first 5 Kilobytes after the image header and generate a MD5 checksum out of it.
  • Title:
  • Description:
  • Copyright Note: This should be a foreign key to the copyright table
  • Rating: An image rating (1-5 stars)
  • Album: The album the image belongs to (foreign key to albums table)
  • Keywords = Tags - foreign key to the tag table
  • Flagged (yes|no): flags can be used to save permanent image selections that span more albums.
  • Location: foreign key to the locations table

Plus, some EXIF-Data:

  • Camera Maker and Model
  • width and height:
  • Orientation
  • Exposure Time
  • ISO Equivalent
  • Flash used (yes/no)
  • ExposureProgram
  • LightSource
  • FocalDistance
  • FStopNumber (Blendenzahl)
  • All EXIF data in a non-searchable dictionary (the user can turn this off also in configuration)

Thumbnail path doesn't need to be stored directly in the database - see FileAndDirectoryStructure on how the thumbnail path is constructed.

Tags and Tag Attributes

As picurl supports a more tag model with explanatory tag attributes, we will not store tags directly in the image table. Instead the tags are referenced with a foreign key in the image table. Until we haven't planned the tag attribute model entirely, this table will just contain tag words.

Albums

An album is a container for thematically related images. Each image holds a foreign key to its corresponding album. For each album we can save the following data fields:

  • album name
  • album description
  • album location (first just textual, could later be resolved in GPS coordinates using geolocation services)

Image Versions

This table links identical images on different stores that were generated through the publishing process in picurl. Later this table will also link auto-detected image versions.

Locations

This table can hold the different GPS coordinates encountered in images.

Copyright Information & Licenses

This table stores Copyright Profiles with the following information:

  • Name of the copyright holder
  • Type of selected CreativeCommons License as Constant (if applicable)
  • Copyright String

There are more copyright profiles possible, as the user might access other people's works through picurl.

Publishing Settings

  • Store different publishing settings with their parameters (Convert to Format, Resize to, Extract metadata yes/no)

Some general principles:

  • database layout should be as complex as necessary, as easy as possible.
  • we expect significant changes of the database layout between the alpha releases of picurl.
  • there should be no local data source aside from the database (e.g. config.ini files) to prevent data corruption.