-- I have very many images. I can track them with their filename, which shouldn't
-- change. Some maybe uppercase, some might be lower, though they can be
-- normalized.  I'll have to make mysql SELECTs case insensitive.
-- 
-- The images are all over the place on my system and other systems. So I want to
-- run a script over them to record where they are and do some checking like
-- md5sum.
-- 
-- Lets take an example: DSC_0002.JPG
-- On my system one copy is:
-- frodo:/home/hendry/projects/imagetrack/a/DSC_0002.JPG
-- Another on:
-- frodo:/home/hendry/projects/imagetrack/b/dsc_0002.jpg
-- 
-- So once my script finds them I want my images table to look like:
-- 
-- id filename     flickr_uploaded
-- 1  dsc_0002.jpg 0
-- 
-- And the track table:
-- id  path    date  \\  size     md5sum  image
-- 1   frodo:/home/hendry/projects/imagetrack/a/DSC_0002.JPG 1140053626 \\
-- 2328605 b3fd01f075448578e0cfe909e663cc2d 1
-- 2   frodo:/home/hendry/projects/imagetrack/b/dsc_0002.jpg 1140053626 \\
-- 2328605 b3fd01f075448578e0cfe909e663cc2d 1
-- 
-- So every record in the images table should have at least one entry in the track table.
-- And likewise every record in the track table must have a "parent" in the images table.

drop table if exists images;
CREATE TABLE images (
  image_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `filename` varchar(64) NOT NULL,
  flickr_uploaded tinyint(1) unsigned NOT NULL default '0',
  flag tinyint(1) unsigned NOT NULL default '0',
  copy_count bigint(20) NOT NULL default '0',
  PRIMARY KEY (image_id),
  UNIQUE KEY `filename` (`filename`)
);

drop table if exists copies;
CREATE TABLE copies (
   id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `path` varchar(128) CHARACTER SET binary NOT NULL,
  `hostname` varchar(12) NOT NULL,
  `date` datetime NOT NULL,
  badexif tinyint(1) unsigned NOT NULL default '0',
  size INT NOT NULL,
  `md5sum` varchar(32) NOT NULL,
  image_id    INT NOT NULL,
  FOREIGN KEY (image_id) REFERENCES images (image_id),
  INDEX (image_id),
  UNIQUE (hostname, path),
  PRIMARY KEY  (`id`)
);