Database

Introduction

The database that will hold all of the details concerning the images for the Image Processing Application will be defined in a few tables.

Master Image Table

The master image table will contain all of the meta data concerning each image. There will only ever be one instance

Instance Table

The instance table will record and keep track of each instance of an image. There will always be at least one instance of any image because there will always be the master copy but there could be numerous other copies of an image such as a copy for Piwigo or another copy that is being used on the main website or a copy for some other reason.

Directory State Tables

We will need to have a semi permanent state table for each file system or server. This table will only describe the state of the individual hard drives. So it will need to have a reference to the file instance and then record the file meta data, using this state table it should be possible to know if the file is a new instance or has been changed. While designing this system it was questioned as to what happens if there is a power failure or some other problem during an update cycle of the Directory state table and the problem caused an inconsistent table but this should not be too much of a problem as this is just a helper state table and is not expected to be authoritive. Most of the time this table will not change and when it does there will only be a few hundred new files at any one directory scan. Probably the best thing is to set a flag of update started when the first change is made and the flag is cleared when the update cycle is completed.

Temporary state table

The temporary state table will be purely in memory. It will be a complete list of the meta data of each and every file that can be used to build the semi permanent Directory state table. the reason for it being an in memory is that it can be used quickly and it does not need to be written to file at all so no matter how often the directory is scanned it will not have an adverse effect on the SSD's write count. It should be noted that if the table is in memory there will be a limit to how many files that should be scanned so try to keep the file count to something below about 50k, I would guess that most directories will be no more that 20k so this should not be too much of an issue even for the master copy.