App-PhotoDB
view release on metacpan or search on metacpan
migrations/000-base-schema.sql view on Meta::CPAN
SET TIME_ZONE='+00:00';
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO';
SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0;
DROP TABLE IF EXISTS `ACCESSORY_TYPE`;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `ACCESSORY_TYPE` (
`accessory_type_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Unique ID for this type of accessory',
`accessory_type` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Type of accessory',
PRIMARY KEY (`accessory_type_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Table to catalog types of photographic accessory';
SET character_set_client = @saved_cs_client;
DROP TABLE IF EXISTS `ACCESSORY`;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `ACCESSORY` (
`accessory_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Unique ID of this accessory',
`accessory_type_id` int(11) DEFAULT NULL COMMENT 'ID of this type of accessory',
`manufacturer_id` int(11) DEFAULT NULL COMMENT 'ID of the manufacturer',
`model` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Model of the accessory',
`acquired` date DEFAULT NULL COMMENT 'Date that this accessory was acquired',
`cost` decimal(5,2) DEFAULT NULL COMMENT 'Purchase cost of the accessory',
`lost` date DEFAULT NULL COMMENT 'Date that this accessory was lost',
`lost_price` decimal(5,2) DEFAULT NULL COMMENT 'Sale price of the accessory',
PRIMARY KEY (`accessory_id`),
KEY `fk_ACCESSORY_1_idx` (`accessory_type_id`),
CONSTRAINT `fk_ACCESSORY_1` FOREIGN KEY (`accessory_type_id`) REFERENCES `ACCESSORY_TYPE` (`accessory_type_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Table to catalog accessories that are not tracked in more specific tables';
SET character_set_client = @saved_cs_client;
DROP TABLE IF EXISTS `PERSON`;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `PERSON` (
`person_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Unique ID for the person',
`name` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Name of the photographer',
PRIMARY KEY (`person_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Table to catalog photographers';
SET character_set_client = @saved_cs_client;
DROP TABLE IF EXISTS `ARCHIVE_TYPE`;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `ARCHIVE_TYPE` (
`archive_type_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Unique ID of archive type',
`archive_type` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Name of this type of archive',
PRIMARY KEY (`archive_type_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Table to list the different types of archive available for materials';
SET character_set_client = @saved_cs_client;
DROP TABLE IF EXISTS `CONDITION`;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `CONDITION` (
`condition_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Unique condition ID',
`code` varchar(6) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Condition shortcode (e.g. EXC)',
`name` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Full name of condition (e.g. Excellent)',
`min_rating` int(11) DEFAULT NULL COMMENT 'The lowest percentage rating that encompasses this condition',
`max_rating` int(11) DEFAULT NULL COMMENT 'The highest percentage rating that encompasses this condition',
`description` varchar(300) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Longer description of condition',
PRIMARY KEY (`condition_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Table to list of physical condition descriptions that can be used to evaluate equipment';
SET character_set_client = @saved_cs_client;
DROP TABLE IF EXISTS `LOG`;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `LOG` (
`log_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Unique ID of the log entry',
`datetime` datetime DEFAULT NULL COMMENT 'Timestamp for the log entry',
`type` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Type of log message, e.g. ADD, EDIT',
`message` varchar(450) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Log message',
PRIMARY KEY (`log_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Table to store data modification logs';
SET character_set_client = @saved_cs_client;
DROP TABLE IF EXISTS `BATTERY`;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `BATTERY` (
`battery_type` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Unique battery ID',
`battery_name` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Common name of the battery',
`voltage` decimal(4,2) DEFAULT NULL COMMENT 'Nominal voltage of the battery',
`chemistry` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Battery chemistry (e.g. Alkaline, Lithium, etc)',
`other_names` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Alternative names for this kind of battery',
PRIMARY KEY (`battery_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Table to catalog of types of battery';
SET character_set_client = @saved_cs_client;
DROP TABLE IF EXISTS `PROCESS`;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `PROCESS` (
`process_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID of this development process',
`name` varchar(12) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Name of this developmenmt process (e.g. C-41, E-6)',
`colour` tinyint(1) DEFAULT NULL COMMENT 'Whether this is a colour process',
`positive` tinyint(1) DEFAULT NULL COMMENT 'Whether this is a positive/reversal process',
PRIMARY KEY (`process_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Table to catalog chemical processes that can be used to develop film and paper';
SET character_set_client = @saved_cs_client;
DROP TABLE IF EXISTS `MANUFACTURER`;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `MANUFACTURER` (
`manufacturer_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Unique ID of the manufacturer',
`manufacturer` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Name of the manufacturer',
`city` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'City in which the manufacturer is based',
`country` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Country in which the manufacturer is based',
`url` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'URL to the manufacturer''s main website',
`founded` smallint(6) DEFAULT NULL COMMENT 'Year in which the manufacturer was founded',
`dissolved` smallint(6) DEFAULT NULL COMMENT 'Year in which the manufacturer was dissolved',
PRIMARY KEY (`manufacturer_id`),
UNIQUE KEY `manufacturer_UNIQUE` (`manufacturer`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Table to catalog manufacturers of equipment and consumables';
SET character_set_client = @saved_cs_client;
DROP TABLE IF EXISTS `EXPOSURE_PROGRAM`;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `EXPOSURE_PROGRAM` (
`exposure_program_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID of exposure program as defined by EXIF tag ExposureProgram',
`exposure_program` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Name of exposure program as defined by EXIF tag ExposureProgram',
PRIMARY KEY (`exposure_program_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Exposure programs as defined by EXIF tag ExposureProgram';
SET character_set_client = @saved_cs_client;
DROP TABLE IF EXISTS `EXHIBITION`;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `EXHIBITION` (
`exhibition_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Unique ID for this exhibition',
`title` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Title of the exhibition',
`location` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Location of the exhibition',
`start_date` date DEFAULT NULL COMMENT 'Start date of the exhibition',
`end_date` date DEFAULT NULL COMMENT 'End date of the exhibition',
PRIMARY KEY (`exhibition_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Table to record exhibition events';
SET character_set_client = @saved_cs_client;
DROP TABLE IF EXISTS `FOCUS_TYPE`;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `FOCUS_TYPE` (
`focus_type_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Unique ID of focus type',
`focus_type` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Name of focus type',
PRIMARY KEY (`focus_type_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Table to catalog different focusing methods';
SET character_set_client = @saved_cs_client;
DROP TABLE IF EXISTS `METERING_MODE`;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `METERING_MODE` (
`metering_mode_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID of metering mode as defined by EXIF tag MeteringMode',
`metering_mode` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Name of metering mode as defined by EXIF tag MeteringMode',
PRIMARY KEY (`metering_mode_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Metering modes as defined by EXIF tag MeteringMode';
SET character_set_client = @saved_cs_client;
DROP TABLE IF EXISTS `SHUTTER_SPEED`;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `SHUTTER_SPEED` (
`shutter_speed` varchar(10) CHARACTER SET latin1 NOT NULL COMMENT 'Shutter speed in fractional notation, e.g. 1/250',
`duration` decimal(7,5) DEFAULT NULL COMMENT 'Shutter speed in decimal notation, e.g. 0.04',
PRIMARY KEY (`shutter_speed`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Table to list all possible shutter speeds';
SET character_set_client = @saved_cs_client;
DROP TABLE IF EXISTS `SHUTTER_TYPE`;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `SHUTTER_TYPE` (
`shutter_type_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Unique ID of the shutter type',
`shutter_type` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Name of the shutter type (e.g. Focal plane, Leaf, etc)',
PRIMARY KEY (`shutter_type_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Table to catalog the different types of camera shutter';
SET character_set_client = @saved_cs_client;
DROP TABLE IF EXISTS `PAPER_STOCK`;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `PAPER_STOCK` (
`paper_stock_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Unique ID of this paper stock',
`name` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Name of this paper stock',
`manufacturer_id` int(11) DEFAULT NULL COMMENT 'ID of the manufacturer of this paper stock',
`resin_coated` tinyint(1) DEFAULT NULL COMMENT 'Whether the paper is resin-coated',
`tonable` tinyint(1) DEFAULT NULL COMMENT 'Whether this paper accepts chemical toning',
`colour` tinyint(1) DEFAULT NULL COMMENT 'Whether this is a colour paper',
`finish` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'The finish of the paper surface',
PRIMARY KEY (`paper_stock_id`),
KEY `fk_PAPER_STOCK_1` (`manufacturer_id`),
CONSTRAINT `fk_PAPER_STOCK_1` FOREIGN KEY (`manufacturer_id`) REFERENCES `MANUFACTURER` (`manufacturer_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Table to catalog different paper stocks available';
SET character_set_client = @saved_cs_client;
DROP TABLE IF EXISTS `METERING_TYPE`;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `METERING_TYPE` (
`metering_type_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Unique ID of the metering type',
`metering` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Name of the metering type (e.g. Selenium)',
PRIMARY KEY (`metering_type_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Table to catalog different metering technologies and cell types';
SET character_set_client = @saved_cs_client;
DROP TABLE IF EXISTS `FORMAT`;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `FORMAT` (
`format_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Unique ID for this format',
`format` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'The name of this film/sensor format',
`digital` tinyint(1) DEFAULT NULL COMMENT 'Whether this is a digital format',
PRIMARY KEY (`format_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Table to catalogue different film formats. These are distinct from negative sizes.';
SET character_set_client = @saved_cs_client;
DROP TABLE IF EXISTS `FLASH_PROTOCOL`;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `FLASH_PROTOCOL` (
`flash_protocol_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Unique ID of this flash protocol',
`manufacturer_id` int(11) DEFAULT NULL COMMENT 'ID of the manufacturer that introduced this flash protocol',
`name` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Name of the flash protocol',
PRIMARY KEY (`flash_protocol_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Table to catalog different protocols used to communicate with flashes';
SET character_set_client = @saved_cs_client;
DROP TABLE IF EXISTS `FILTER_ADAPTER`;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `FILTER_ADAPTER` (
`filter_adapter_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Unique ID of filter adapter',
`camera_thread` decimal(3,1) DEFAULT NULL COMMENT 'Diameter of camera-facing screw thread in mm',
`filter_thread` decimal(3,1) DEFAULT NULL COMMENT 'Diameter of filter-facing screw thread in mm',
PRIMARY KEY (`filter_adapter_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Table to catalogue filter adapter rings';
SET character_set_client = @saved_cs_client;
DROP TABLE IF EXISTS `FILTER`;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `FILTER` (
`filter_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Unique filter ID',
`thread` decimal(4,1) DEFAULT NULL COMMENT 'Diameter of screw thread in mm',
`type` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Filter type (e.g. Red, CPL, UV)',
`attenuation` decimal(2,1) DEFAULT NULL COMMENT 'Attenuation of this filter in decimal stops',
`qty` int(11) DEFAULT NULL COMMENT 'Quantity of these filters available',
`manufacturer_id` int(11) DEFAULT NULL COMMENT 'Denotes the manufacturer of the filter.',
PRIMARY KEY (`filter_id`),
KEY `fk_FILTER_1_idx` (`manufacturer_id`),
CONSTRAINT `fk_FILTER_1` FOREIGN KEY (`manufacturer_id`) REFERENCES `MANUFACTURER` (`manufacturer_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Table to catalog filters';
SET character_set_client = @saved_cs_client;
DROP TABLE IF EXISTS `ARCHIVE`;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `ARCHIVE` (
`archive_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Unique ID of this archive',
`archive_type_id` int(11) DEFAULT NULL COMMENT 'ID of this type of archive',
`name` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Name of this archive',
`max_width` int(11) DEFAULT NULL COMMENT 'Maximum width of media that this archive can store',
`max_height` int(11) DEFAULT NULL COMMENT 'Maximum height of media that this archive can store',
`location` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Location of this archive',
`storage` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'The type of storage used for this archive, e.g. box, folder, ringbinder, etc',
`sealed` tinyint(1) DEFAULT '0' COMMENT 'Whether or not this archive is sealed (closed to new additions)',
PRIMARY KEY (`archive_id`),
KEY `fk_ARCHIVE_3_idx` (`archive_type_id`),
CONSTRAINT `fk_ARCHIVE_3` FOREIGN KEY (`archive_type_id`) REFERENCES `ARCHIVE_TYPE` (`archive_type_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Table to list all archives that exist for storing physical media';
SET character_set_client = @saved_cs_client;
DROP TABLE IF EXISTS `LIGHT_METER`;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `LIGHT_METER` (
`light_meter_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Unique ID for this light meter',
`manufacturer_id` int(11) DEFAULT NULL COMMENT 'Denotes ID of manufacturer of the light meter',
`model` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Model name or number of the light meter',
`metering_type` int(11) DEFAULT NULL COMMENT 'ID of metering technology used in this light meter',
`reflected` tinyint(1) DEFAULT NULL COMMENT 'Whether the meter is capable of reflected-light metering',
`incident` tinyint(1) DEFAULT NULL COMMENT 'Whether the meter is capable of incident-light metering',
`flash` tinyint(1) DEFAULT NULL COMMENT 'Whether the meter is capable of flash metering',
`spot` tinyint(1) DEFAULT NULL COMMENT 'Whether the meter is capable of spot metering',
`min_asa` int(11) DEFAULT NULL COMMENT 'Minimum ISO/ASA that this meter is capable of handling',
`max_asa` int(11) DEFAULT NULL COMMENT 'Maximum ISO/ASA that this meter is capable of handling',
`min_lv` int(11) DEFAULT NULL COMMENT 'Minimum light value (LV/EV) that this meter is capable of handling',
`max_lv` int(11) DEFAULT NULL COMMENT 'Maximum light value (LV/EV) that this meter is capable of handling',
PRIMARY KEY (`light_meter_id`),
KEY `fk_LIGHT_METER_1_idx` (`manufacturer_id`),
KEY `fk_LIGHT_METER_2_idx` (`metering_type`),
CONSTRAINT `fk_LIGHT_METER_1` FOREIGN KEY (`manufacturer_id`) REFERENCES `MANUFACTURER` (`manufacturer_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `fk_LIGHT_METER_2` FOREIGN KEY (`metering_type`) REFERENCES `METERING_TYPE` (`metering_type_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Table to catalog light meters';
SET character_set_client = @saved_cs_client;
DROP TABLE IF EXISTS `BODY_TYPE`;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `BODY_TYPE` (
`body_type_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Unique body type ID',
`body_type` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Name of camera body type (e.g. SLR, compact, etc)',
PRIMARY KEY (`body_type_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Table to catalog types of camera body style';
SET character_set_client = @saved_cs_client;
DROP TABLE IF EXISTS `NEGATIVE_SIZE`;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `NEGATIVE_SIZE` (
`negative_size_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Unique ID of negative size',
`width` decimal(4,1) DEFAULT NULL COMMENT 'Width of the negative size in mm',
`height` decimal(4,1) DEFAULT NULL COMMENT 'Height of the negative size in mm',
`negative_size` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Common name of the negative size (e.g. 35mm, 6x7, etc)',
`crop_factor` decimal(4,2) DEFAULT NULL COMMENT 'Crop factor of this negative size',
`area` int(11) DEFAULT NULL COMMENT 'Area of this negative size in sq. mm',
`aspect_ratio` decimal(4,2) DEFAULT NULL COMMENT 'Aspect ratio of this negative size, expressed as a single decimal. (e.g. 3:2 is expressed as 1.5)',
PRIMARY KEY (`negative_size_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Table to catalog different negative sizes available. Negtives sizes are distinct from film formats.';
SET character_set_client = @saved_cs_client;
DROP TABLE IF EXISTS `MOUNT`;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `MOUNT` (
`mount_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Unique ID of this lens mount',
`mount` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Name of this lens mount (e.g. Canon FD)',
`fixed` tinyint(1) DEFAULT NULL COMMENT 'Whether this is a fixed (non-interchangable) lens mount',
`shutter_in_lens` tinyint(1) DEFAULT NULL COMMENT 'Whether this lens mount system incorporates the shutter into the lens',
`type` varchar(25) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'The physical mount type of this lens mount (e.g. Screw, Bayonet, etc)',
`purpose` varchar(25) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'The intended purpose of this lens mount (e.g. camera, enlarger, projector)',
`notes` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Freeform notes field',
`digital_only` tinyint(1) DEFAULT NULL COMMENT 'Whether this mount is intended only for digital cameras',
`manufacturer_id` int(11) DEFAULT NULL COMMENT 'Manufacturer ID of this lens mount, if applicable',
PRIMARY KEY (`mount_id`),
KEY `fk_MOUNT_1_idx` (`manufacturer_id`),
CONSTRAINT `fk_MOUNT_1` FOREIGN KEY (`manufacturer_id`) REFERENCES `MANUFACTURER` (`manufacturer_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Table to catalog different lens mount standards. This is mostly used for camera lens mounts, but can also be used for enlarger and projector lenses.';
SET character_set_client = @saved_cs_client;
DROP TABLE IF EXISTS `FILMSTOCK`;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `FILMSTOCK` (
`filmstock_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Unique ID of the filmstock',
`manufacturer_id` int(11) DEFAULT NULL COMMENT 'ID of the manufacturer of the film',
`name` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Name of the film',
`iso` int(11) DEFAULT NULL COMMENT 'Nominal ISO speed of the film',
`colour` tinyint(1) DEFAULT NULL COMMENT 'Whether the film is colour',
`process_id` int(11) DEFAULT NULL COMMENT 'ID of the normal process for this film',
`panchromatic` tinyint(1) DEFAULT NULL COMMENT 'Whether this film is panchromatic',
PRIMARY KEY (`filmstock_id`),
KEY `fk_manufacturer_id` (`manufacturer_id`),
KEY `fk_FILMSTOCK_1_idx` (`process_id`),
CONSTRAINT `fk_FILMSTOCK_1` FOREIGN KEY (`process_id`) REFERENCES `PROCESS` (`process_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `fk_manufacturer_id` FOREIGN KEY (`manufacturer_id`) REFERENCES `MANUFACTURER` (`manufacturer_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Table to list different brands of film stock';
SET character_set_client = @saved_cs_client;
DROP TABLE IF EXISTS `DEVELOPER`;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `DEVELOPER` (
`developer_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Unique developer ID',
`manufacturer_id` int(11) DEFAULT NULL COMMENT 'Denotes the manufacturer ID',
`name` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Name of the developer',
`for_paper` tinyint(1) DEFAULT NULL COMMENT 'Whether this developer can be used with paper',
`for_film` tinyint(1) DEFAULT NULL COMMENT 'Whether this developer can be used with film',
`chemistry` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'The key chemistry on which this developer is based (e.g. phenidone)',
PRIMARY KEY (`developer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Table to list film and paper developers';
SET character_set_client = @saved_cs_client;
DROP TABLE IF EXISTS `MOUNT_ADAPTER`;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `MOUNT_ADAPTER` (
`mount_adapter_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Unique ID of lens mount adapter',
`lens_mount` int(11) DEFAULT NULL COMMENT 'ID of the mount used between the adapter and the lens',
`camera_mount` int(11) DEFAULT NULL COMMENT 'ID of the mount used between the adapter and the camera',
`has_optics` tinyint(1) DEFAULT NULL COMMENT 'Whether this adapter includes optical elements',
`infinity_focus` tinyint(1) DEFAULT NULL COMMENT 'Whether this adapter allows infinity focus',
`notes` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Freeform notes',
PRIMARY KEY (`mount_adapter_id`),
KEY `fk_MOUNT_ADAPTER_1` (`lens_mount`),
KEY `fk_MOUNT_ADAPTER_2` (`camera_mount`),
CONSTRAINT `fk_MOUNT_ADAPTER_1` FOREIGN KEY (`lens_mount`) REFERENCES `MOUNT` (`mount_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `fk_MOUNT_ADAPTER_2` FOREIGN KEY (`camera_mount`) REFERENCES `MOUNT` (`mount_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Table to catalog adapters to mount lenses on other cameras';
SET character_set_client = @saved_cs_client;
DROP TABLE IF EXISTS `FILM_BULK`;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `FILM_BULK` (
`film_bulk_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Unique ID of this bulk roll of film',
`format_id` int(11) DEFAULT NULL COMMENT 'ID of the format of this bulk roll',
`filmstock_id` int(11) DEFAULT NULL COMMENT 'ID of the filmstock',
`purchase_date` date DEFAULT NULL COMMENT 'Purchase date of this bulk roll',
`cost` decimal(5,2) DEFAULT NULL COMMENT 'Purchase cost of this bulk roll',
`source` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Place where this bulk roll was bought from',
`batch` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Batch code of this bulk roll',
`expiry` date DEFAULT NULL COMMENT 'Expiry date of this bulk roll',
PRIMARY KEY (`film_bulk_id`),
KEY `fk_FILM_BULK_1_idx` (`format_id`),
KEY `fk_FILM_BULK_2_idx` (`filmstock_id`),
CONSTRAINT `fk_FILM_BULK_1` FOREIGN KEY (`format_id`) REFERENCES `FORMAT` (`format_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `fk_FILM_BULK_2` FOREIGN KEY (`filmstock_id`) REFERENCES `FILMSTOCK` (`filmstock_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Table to record bulk film stock, from which individual films can be cut';
SET character_set_client = @saved_cs_client;
DROP TABLE IF EXISTS `TELECONVERTER`;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `TELECONVERTER` (
`teleconverter_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Unique ID of this teleconverter',
`mount_id` int(11) DEFAULT NULL COMMENT 'ID of the lens mount used by this teleconverter',
`factor` decimal(4,2) DEFAULT NULL COMMENT 'Magnification factor of this teleconverter (numerical part only, e.g. 1.4)',
`manufacturer_id` int(11) DEFAULT NULL COMMENT 'ID of the manufacturer of this teleconverter',
`model` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Model name of this teleconverter',
`elements` tinyint(4) DEFAULT NULL COMMENT 'Number of optical elements used in this teleconverter',
`groups` tinyint(4) DEFAULT NULL COMMENT 'Number of optical groups used in this teleconverter',
`multicoated` tinyint(1) DEFAULT NULL COMMENT 'Whether this teleconverter is multi-coated',
PRIMARY KEY (`teleconverter_id`),
KEY `fk_TELECONVERTER_1` (`manufacturer_id`),
KEY `fk_TELECONVERTER_2` (`mount_id`),
CONSTRAINT `fk_TELECONVERTER_1` FOREIGN KEY (`manufacturer_id`) REFERENCES `MANUFACTURER` (`manufacturer_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `fk_TELECONVERTER_2` FOREIGN KEY (`mount_id`) REFERENCES `MOUNT` (`mount_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Table to catalog teleconverters (multipliers)';
SET character_set_client = @saved_cs_client;
DROP TABLE IF EXISTS `TONER`;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `TONER` (
`toner_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Unique ID of the toner',
`manufacturer_id` int(11) DEFAULT NULL COMMENT 'ID of the manufacturer of the toner',
`toner` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Name of the toner',
`formulation` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Chemical formulation of the toner',
`stock_dilution` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Stock dilution of the toner',
PRIMARY KEY (`toner_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Table to catalog paper toners that can be used during the printing process';
SET character_set_client = @saved_cs_client;
DROP TABLE IF EXISTS `ENLARGER`;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `ENLARGER` (
`enlarger_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Unique enlarger ID',
`manufacturer_id` int(11) DEFAULT NULL COMMENT 'Manufacturer ID of the enlarger',
`enlarger` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Name/model of the enlarger',
`negative_size_id` int(11) DEFAULT NULL COMMENT 'ID of the largest negative size that the enlarger can handle',
`acquired` date DEFAULT NULL COMMENT 'Date on which the enlarger was acquired',
`lost` date DEFAULT NULL COMMENT 'Date on which the enlarger was lost/sold',
`introduced` year(4) DEFAULT NULL COMMENT 'Year in which the enlarger was introduced',
`discontinued` year(4) DEFAULT NULL COMMENT 'Year in which the enlarger was discontinued',
`cost` decimal(6,2) DEFAULT NULL COMMENT 'Purchase cost of the enlarger',
`lost_price` decimal(6,2) DEFAULT NULL COMMENT 'Sale price of the enlarger',
PRIMARY KEY (`enlarger_id`),
KEY `fk_ENLARGER_1` (`manufacturer_id`),
KEY `fk_ENLARGER_2` (`negative_size_id`),
CONSTRAINT `fk_ENLARGER_1` FOREIGN KEY (`manufacturer_id`) REFERENCES `MANUFACTURER` (`manufacturer_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `fk_ENLARGER_2` FOREIGN KEY (`negative_size_id`) REFERENCES `NEGATIVE_SIZE` (`negative_size_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Table to list enlargers';
SET character_set_client = @saved_cs_client;
DROP TABLE IF EXISTS `FLASH`;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `FLASH` (
`flash_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Unique ID of external flash unit',
`manufacturer_id` int(11) DEFAULT NULL COMMENT 'Manufacturer ID of the flash',
`model` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Model name/number of the flash',
`guide_number` int(11) DEFAULT NULL COMMENT 'Guide number of the flash',
`gn_info` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Extra freeform info about how the guide number was measured',
`battery_powered` tinyint(1) DEFAULT NULL COMMENT 'Whether this flash takes batteries',
`pc_sync` tinyint(1) DEFAULT NULL COMMENT 'Whether the flash has a PC sync socket',
`hot_shoe` tinyint(1) DEFAULT NULL COMMENT 'Whether the flash has a hot shoe connection',
`light_stand` tinyint(1) DEFAULT NULL COMMENT 'Whether the flash can be used on a light stand',
`battery_type_id` int(11) DEFAULT NULL COMMENT 'ID of battery type',
`battery_qty` int(11) DEFAULT NULL COMMENT 'Quantity of batteries needed in this flash',
`manual_control` tinyint(1) DEFAULT NULL COMMENT 'Whether this flash offers manual power control',
`swivel_head` tinyint(1) DEFAULT NULL COMMENT 'Whether this flash has a horizontal swivel head',
`tilt_head` tinyint(1) DEFAULT NULL COMMENT 'Whether this flash has a vertical tilt head',
`zoom` tinyint(1) DEFAULT NULL COMMENT 'Whether this flash can zoom',
migrations/000-base-schema.sql view on Meta::CPAN
`flash_protocol_id` int(11) DEFAULT NULL COMMENT 'ID of flash TTL metering protocol',
`trigger_voltage` decimal(4,1) DEFAULT NULL COMMENT 'Trigger voltage of the flash, in Volts',
`own` tinyint(1) DEFAULT NULL COMMENT 'Whether we currently own this flash',
`acquired` date DEFAULT NULL COMMENT 'Date this flash was acquired',
`cost` decimal(5,2) DEFAULT NULL COMMENT 'Purchase cost of this flash',
PRIMARY KEY (`flash_id`),
KEY `fk_FLASH_1_idx` (`flash_protocol_id`),
KEY `fk_FLASH_2_idx` (`battery_type_id`),
CONSTRAINT `fk_FLASH_1` FOREIGN KEY (`flash_protocol_id`) REFERENCES `FLASH_PROTOCOL` (`flash_protocol_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `fk_FLASH_2` FOREIGN KEY (`battery_type_id`) REFERENCES `BATTERY` (`battery_type`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Table to catlog flashes, flashguns and speedlights';
SET character_set_client = @saved_cs_client;
DROP TABLE IF EXISTS `PROJECTOR`;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `PROJECTOR` (
`projector_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Unique ID of this projector',
`manufacturer_id` int(11) DEFAULT NULL COMMENT 'ID of the manufacturer of this projector',
`model` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Model name of this projector',
`mount_id` int(11) DEFAULT NULL COMMENT 'ID of the lens mount of this projector, if it has interchangeable lenses',
`negative_size_id` int(11) DEFAULT NULL COMMENT 'ID of the largest negative size that this projector can handle',
`own` tinyint(1) DEFAULT NULL COMMENT 'Whether we currently own this projector',
`cine` tinyint(1) DEFAULT NULL COMMENT 'Whether this is a cine (movie) projector',
PRIMARY KEY (`projector_id`),
KEY `fk_PROJECTOR_1_idx` (`manufacturer_id`),
KEY `fk_PROJECTOR_2_idx` (`mount_id`),
KEY `fk_PROJECTOR_3_idx` (`negative_size_id`),
CONSTRAINT `fk_PROJECTOR_1` FOREIGN KEY (`manufacturer_id`) REFERENCES `MANUFACTURER` (`manufacturer_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `fk_PROJECTOR_2` FOREIGN KEY (`mount_id`) REFERENCES `MOUNT` (`mount_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `fk_PROJECTOR_3` FOREIGN KEY (`negative_size_id`) REFERENCES `NEGATIVE_SIZE` (`negative_size_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Table to catalog projectors (still and movie)';
SET character_set_client = @saved_cs_client;
DROP TABLE IF EXISTS `LENS`;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `LENS` (
`lens_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Unique ID for this lens',
`mount_id` int(11) DEFAULT NULL COMMENT 'Denotes the ID of the lens mount, if this is an interchangeable lens',
`zoom` tinyint(1) DEFAULT NULL COMMENT 'Whether this is a zoom lens',
`min_focal_length` int(11) DEFAULT NULL COMMENT 'Shortest focal length of this lens, in mm',
`max_focal_length` int(11) DEFAULT NULL COMMENT 'Longest focal length of this lens, in mm',
`manufacturer_id` int(11) DEFAULT NULL COMMENT 'ID of the manufacturer of this lens',
`model` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Model name of this lens',
`closest_focus` int(11) DEFAULT NULL COMMENT 'The closest focus possible with this lens, in cm',
`max_aperture` decimal(4,1) DEFAULT NULL COMMENT 'Maximum (widest) aperture available on this lens (numerical part only, e.g. 2.8)',
`min_aperture` decimal(4,1) DEFAULT NULL COMMENT 'Minimum (narrowest) aperture available on this lens (numerical part only, e.g. 22)',
`elements` int(11) DEFAULT NULL COMMENT 'Number of optical lens elements',
`groups` int(11) DEFAULT NULL COMMENT 'Number of optical groups',
`weight` int(11) DEFAULT NULL COMMENT 'Weight of this lens, in grammes (g)',
`nominal_min_angle_diag` int(11) DEFAULT NULL COMMENT 'Nominal minimum diagonal field of view from manufacturer''s specs',
`nominal_max_angle_diag` int(11) DEFAULT NULL COMMENT 'Nominal maximum diagonal field of view from manufacturer''s specs',
`aperture_blades` int(11) DEFAULT NULL COMMENT 'Number of aperture blades',
`autofocus` tinyint(1) DEFAULT NULL COMMENT 'Whether this lens has autofocus capability',
`filter_thread` decimal(4,1) DEFAULT NULL COMMENT 'Diameter of lens filter thread, in mm',
`magnification` decimal(5,3) DEFAULT NULL COMMENT 'Maximum magnification ratio of the lens, expressed like 0.765',
`url` varchar(145) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'URL to more information about this lens',
`serial` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Serial number of this lens',
`date_code` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Date code of this lens, if different from the serial number',
`introduced` smallint(6) DEFAULT NULL COMMENT 'Year in which this lens model was introduced',
`discontinued` smallint(6) DEFAULT NULL COMMENT 'Year in which this lens model was discontinued',
`manufactured` smallint(6) DEFAULT NULL COMMENT 'Year in which this specific lens was manufactured',
`negative_size_id` int(11) DEFAULT NULL COMMENT 'ID of the negative size which this lens is designed for',
`acquired` date DEFAULT NULL COMMENT 'Date on which this lens was acquired',
`cost` decimal(6,2) DEFAULT NULL COMMENT 'Price paid for this lens in local currency units',
`fixed_mount` tinyint(1) DEFAULT NULL COMMENT 'Whether this is a fixed lens (i.e. on a compact camera)',
`notes` text COLLATE utf8mb4_unicode_ci COMMENT 'Freeform notes field',
`own` tinyint(1) DEFAULT NULL COMMENT 'Whether we currently own this lens',
`lost` date DEFAULT NULL COMMENT 'Date on which lens was lost/sold/disposed',
`lost_price` decimal(6,2) DEFAULT NULL COMMENT 'Price for which the lens was sold',
`source` varchar(150) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Place where the lens was acquired from',
`coating` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Notes about the lens coating type',
`hood` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Model number of the compatible lens hood',
`exif_lenstype` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'EXIF LensID number, if this lens has one officially registered. See documentation at http://www.sno.phy.queensu.ca/~phil/exiftool/TagNames/',
`rectilinear` tinyint(1) DEFAULT NULL COMMENT 'Whether this is a rectilinear lens',
`length` int(11) DEFAULT NULL COMMENT 'Length of lens in mm',
`diameter` int(11) DEFAULT NULL COMMENT 'Width of lens in mm',
`condition_id` int(11) DEFAULT NULL COMMENT 'Denotes the cosmetic condition of the camera',
`image_circle` int(11) DEFAULT NULL COMMENT 'Diameter of image circle projected by lens, in mm',
`formula` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Name of the type of lens formula (e.g. Tessar)',
`shutter_model` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Name of the integrated shutter, if any',
PRIMARY KEY (`lens_id`),
KEY `fk_LENS_2` (`manufacturer_id`),
KEY `fk_LENS_3` (`mount_id`),
KEY `fk_LENS_4` (`negative_size_id`),
KEY `fk_LENS_1_idx` (`condition_id`),
CONSTRAINT `fk_LENS_1` FOREIGN KEY (`condition_id`) REFERENCES `CONDITION` (`condition_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `fk_LENS_2` FOREIGN KEY (`manufacturer_id`) REFERENCES `MANUFACTURER` (`manufacturer_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `fk_LENS_3` FOREIGN KEY (`mount_id`) REFERENCES `MOUNT` (`mount_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `fk_LENS_4` FOREIGN KEY (`negative_size_id`) REFERENCES `NEGATIVE_SIZE` (`negative_size_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Table to catalog lenses';
SET character_set_client = @saved_cs_client;
DROP TABLE IF EXISTS `CAMERA`;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `CAMERA` (
`camera_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Auto-incremented camera ID',
`manufacturer_id` int(11) DEFAULT NULL COMMENT 'Denotes the manufacturer of the camera.',
`model` varchar(45) DEFAULT NULL COMMENT 'The model name of the camera',
`mount_id` int(11) DEFAULT NULL COMMENT 'Denotes the lens mount of the camera if it is an interchangeable-lens camera',
`format_id` int(11) DEFAULT NULL COMMENT 'Denotes the film format of the camera',
migrations/000-base-schema.sql view on Meta::CPAN
CONSTRAINT `fk_mount` FOREIGN KEY (`mount_id`) REFERENCES `MOUNT` (`mount_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `fk_negative_size` FOREIGN KEY (`negative_size_id`) REFERENCES `NEGATIVE_SIZE` (`negative_size_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `fk_shutter_type` FOREIGN KEY (`shutter_type_id`) REFERENCES `SHUTTER_TYPE` (`shutter_type_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Table to catalog cameras - both cameras with fixed lenses and cameras with interchangeable lenses';
SET character_set_client = @saved_cs_client;
DROP TABLE IF EXISTS `MOVIE`;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `MOVIE` (
`movie_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Unique ID for this motion picture film / movie',
`title` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Title of this movie',
`camera_id` int(11) DEFAULT NULL COMMENT 'ID of the camera used to shoot this movie',
`lens_id` int(11) DEFAULT NULL COMMENT 'ID of the lens used to shoot this movie',
`format_id` int(11) DEFAULT NULL COMMENT 'ID of the film format on which this movie was shot',
`sound` tinyint(1) DEFAULT NULL COMMENT 'Whether this movie has sound',
`fps` int(11) DEFAULT NULL COMMENT 'Frame rate of this movie, in fps',
`filmstock_id` int(11) DEFAULT NULL COMMENT 'ID of the filmstock used to shoot this movie',
`feet` int(11) DEFAULT NULL COMMENT 'Length of this movie in feet',
`date_loaded` date DEFAULT NULL COMMENT 'Date that the filmstock was loaded into a camera',
`date_shot` date DEFAULT NULL COMMENT 'Date on which this movie was shot',
`date_processed` date DEFAULT NULL COMMENT 'Date on which this movie was processed',
`process_id` int(11) DEFAULT NULL COMMENT 'ID of the process used to develop this film',
`description` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Table to catalog motion picture films (movies)',
PRIMARY KEY (`movie_id`),
KEY `fk_MOVIE_1_idx` (`camera_id`),
KEY `fk_MOVIE_2_idx` (`lens_id`),
KEY `fk_MOVIE_3_idx` (`format_id`),
KEY `fk_MOVIE_4_idx` (`filmstock_id`),
KEY `fk_MOVIE_5_idx` (`process_id`),
CONSTRAINT `fk_MOVIE_1` FOREIGN KEY (`camera_id`) REFERENCES `CAMERA` (`camera_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `fk_MOVIE_2` FOREIGN KEY (`lens_id`) REFERENCES `LENS` (`lens_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `fk_MOVIE_3` FOREIGN KEY (`format_id`) REFERENCES `FORMAT` (`format_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `fk_MOVIE_4` FOREIGN KEY (`filmstock_id`) REFERENCES `FILMSTOCK` (`filmstock_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `fk_MOVIE_5` FOREIGN KEY (`process_id`) REFERENCES `PROCESS` (`process_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Table to catalog motion picture films (movies)';
SET character_set_client = @saved_cs_client;
DROP TABLE IF EXISTS `METERING_MODE_AVAILABLE`;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `METERING_MODE_AVAILABLE` (
`camera_id` int(11) NOT NULL COMMENT 'ID of camera',
`metering_mode_id` int(11) NOT NULL COMMENT 'ID of metering mode',
PRIMARY KEY (`camera_id`,`metering_mode_id`),
KEY `fk_METERING_MODE_AVAILABLE_2_idx` (`metering_mode_id`),
CONSTRAINT `fk_METERING_MODE_AVAILABLE_1` FOREIGN KEY (`camera_id`) REFERENCES `CAMERA` (`camera_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `fk_METERING_MODE_AVAILABLE_2` FOREIGN KEY (`metering_mode_id`) REFERENCES `METERING_MODE` (`metering_mode_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Table to associate cameras with available metering modes';
SET character_set_client = @saved_cs_client;
DROP TABLE IF EXISTS `EXPOSURE_PROGRAM_AVAILABLE`;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `EXPOSURE_PROGRAM_AVAILABLE` (
`camera_id` int(11) NOT NULL COMMENT 'ID of camera',
`exposure_program_id` int(11) NOT NULL COMMENT 'ID of exposure program',
PRIMARY KEY (`camera_id`,`exposure_program_id`),
KEY `fk_EXPOSURE_PROGRAM_AVAILABLE_1_idx` (`camera_id`),
KEY `fk_EXPOSURE_PROGRAM_AVAILABLE_2_idx` (`exposure_program_id`),
CONSTRAINT `fk_EXPOSURE_PROGRAM_AVAILABLE_1` FOREIGN KEY (`camera_id`) REFERENCES `CAMERA` (`camera_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `fk_EXPOSURE_PROGRAM_AVAILABLE_2` FOREIGN KEY (`exposure_program_id`) REFERENCES `EXPOSURE_PROGRAM` (`exposure_program_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Table to associate cameras with available exposure programs';
SET character_set_client = @saved_cs_client;
DROP TABLE IF EXISTS `ACCESSORY_COMPAT`;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `ACCESSORY_COMPAT` (
`compat_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Unique ID for this compatibility',
`accessory_id` int(11) NOT NULL COMMENT 'ID of the accessory',
`camera_id` int(11) DEFAULT NULL COMMENT 'ID of the compatible camera',
`lens_id` int(11) DEFAULT NULL COMMENT 'ID of the compatible lens',
PRIMARY KEY (`compat_id`),
KEY `fk_ACCESSORY_COMPAT_1_idx` (`accessory_id`),
KEY `fk_ACCESSORY_COMPAT_2_idx` (`camera_id`),
KEY `fk_ACCESSORY_COMPAT_3_idx` (`lens_id`),
CONSTRAINT `fk_ACCESSORY_COMPAT_1` FOREIGN KEY (`accessory_id`) REFERENCES `ACCESSORY` (`accessory_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `fk_ACCESSORY_COMPAT_2` FOREIGN KEY (`camera_id`) REFERENCES `CAMERA` (`camera_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `fk_ACCESSORY_COMPAT_3` FOREIGN KEY (`lens_id`) REFERENCES `LENS` (`lens_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Table to define compatibility between accessories and cameras or lenses';
SET character_set_client = @saved_cs_client;
DROP TABLE IF EXISTS `SHUTTER_SPEED_AVAILABLE`;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `SHUTTER_SPEED_AVAILABLE` (
`camera_id` int(11) NOT NULL COMMENT 'ID of the camera',
`shutter_speed` varchar(10) CHARACTER SET latin1 NOT NULL COMMENT 'Shutter speed that this camera has',
PRIMARY KEY (`camera_id`,`shutter_speed`),
KEY `fk_SHUTTER_SPEED_AVAILABLE_1_idx` (`shutter_speed`),
KEY `fk_SHUTTER_SPEED_AVAILABLE_2_idx` (`camera_id`),
migrations/000-base-schema.sql view on Meta::CPAN
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Table to associate cameras with shutter speeds';
SET character_set_client = @saved_cs_client;
DROP TABLE IF EXISTS `REPAIR`;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `REPAIR` (
`repair_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Unique ID for the repair job',
`camera_id` int(11) DEFAULT NULL COMMENT 'ID of camera that was repaired',
`lens_id` int(11) DEFAULT NULL COMMENT 'ID of lens that was repaired',
`date` date DEFAULT NULL COMMENT 'The date of the repair',
`summary` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Brief summary of the repair',
`description` varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Longer description of the repair',
PRIMARY KEY (`repair_id`),
KEY `fk_REPAIR_1_idx` (`camera_id`),
KEY `fk_REPAIR_2_idx` (`lens_id`),
CONSTRAINT `fk_REPAIR_1` FOREIGN KEY (`camera_id`) REFERENCES `CAMERA` (`camera_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `fk_REPAIR_2` FOREIGN KEY (`lens_id`) REFERENCES `LENS` (`lens_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Tabe to catalog all repairs and servicing undertaken on cameras and lenses in the collection';
SET character_set_client = @saved_cs_client;
DROP TABLE IF EXISTS `FILM`;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `FILM` (
`film_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Unique ID of the film',
`filmstock_id` int(11) DEFAULT NULL COMMENT 'ID of the filmstock used',
`exposed_at` int(11) DEFAULT NULL COMMENT 'ISO at which the film was exposed',
`format_id` int(11) DEFAULT NULL COMMENT 'ID of the film format',
`date_loaded` date DEFAULT NULL COMMENT 'Date when the film was loaded into a camera',
`date` date DEFAULT NULL COMMENT 'Date when the film was processed',
`camera_id` int(11) DEFAULT NULL COMMENT 'ID of the camera that exposed this film',
`notes` varchar(145) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Title of the film',
`frames` int(11) DEFAULT NULL COMMENT 'Expected (not actual) number of frames from the film',
`developer_id` int(11) DEFAULT NULL COMMENT 'ID of the developer used to process this film',
`directory` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Name of the directory that contains the scanned images from this film',
`photographer_id` int(11) DEFAULT NULL COMMENT 'ID of the photographer who took these pictures',
`dev_uses` int(11) DEFAULT NULL COMMENT 'Numnber of previous uses of the developer',
`dev_time` time DEFAULT NULL COMMENT 'Duration of development',
`dev_temp` decimal(3,1) DEFAULT NULL COMMENT 'Temperature of development',
`dev_n` int(11) DEFAULT NULL COMMENT 'Number of the Push/Pull rating of the film, e.g. N+1, N-2',
`development_notes` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Extra freeform notes about the development process',
`film_bulk_id` int(11) DEFAULT NULL COMMENT 'ID of bulk film from which this film was cut',
`film_bulk_loaded` date DEFAULT NULL COMMENT 'Date that this film was cut from a bulk roll',
`film_batch` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Batch number of the film',
`film_expiry` date DEFAULT NULL COMMENT 'Expiry date of the film',
`purchase_date` date DEFAULT NULL COMMENT 'Date this film was purchased',
`price` decimal(4,2) DEFAULT NULL COMMENT 'Price paid for this film',
`processed_by` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Person or place that processed this film',
`archive_id` int(11) DEFAULT NULL COMMENT 'ID of the archive to which this film belongs',
PRIMARY KEY (`film_id`),
KEY `fk_filmstock_id` (`filmstock_id`),
KEY `fk_camera_id` (`camera_id`),
KEY `fk_format_id` (`format_id`),
KEY `fk_FILM_1` (`developer_id`),
KEY `fk_FILM_2_idx` (`photographer_id`),
KEY `fk_FILM_3_idx` (`archive_id`),
KEY `fk_FILM_4_idx` (`film_bulk_id`),
CONSTRAINT `fk_FILM_1` FOREIGN KEY (`developer_id`) REFERENCES `DEVELOPER` (`developer_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `fk_FILM_2` FOREIGN KEY (`photographer_id`) REFERENCES `PERSON` (`person_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `fk_FILM_3` FOREIGN KEY (`archive_id`) REFERENCES `ARCHIVE` (`archive_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `fk_FILM_4` FOREIGN KEY (`film_bulk_id`) REFERENCES `FILM_BULK` (`film_bulk_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `fk_camera_id` FOREIGN KEY (`camera_id`) REFERENCES `CAMERA` (`camera_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `fk_filmstock_id` FOREIGN KEY (`filmstock_id`) REFERENCES `FILMSTOCK` (`filmstock_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `fk_format_id` FOREIGN KEY (`format_id`) REFERENCES `FORMAT` (`format_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Table to list films which consist of one or more negatives. A film can be a roll film, one or more sheets of sheet film, one or more photographic plates, etc.';
SET character_set_client = @saved_cs_client;
DROP TABLE IF EXISTS `NEGATIVE`;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `NEGATIVE` (
`negative_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Unique ID of this negative',
`film_id` int(11) DEFAULT NULL COMMENT 'ID of the film that this negative belongs to',
`frame` varchar(5) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT 'Frame number or code of this negative',
`description` varchar(145) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT 'Caption of this picture',
`date` datetime DEFAULT NULL COMMENT 'Date & time on which this picture was taken',
migrations/000-base-schema.sql view on Meta::CPAN
CONSTRAINT `fk_NEGATIVE_1` FOREIGN KEY (`film_id`) REFERENCES `FILM` (`film_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `fk_NEGATIVE_10` FOREIGN KEY (`copy_of`) REFERENCES `NEGATIVE` (`negative_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `fk_NEGATIVE_2` FOREIGN KEY (`lens_id`) REFERENCES `LENS` (`lens_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `fk_NEGATIVE_3` FOREIGN KEY (`filter_id`) REFERENCES `FILTER` (`filter_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `fk_NEGATIVE_4` FOREIGN KEY (`teleconverter_id`) REFERENCES `TELECONVERTER` (`teleconverter_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `fk_NEGATIVE_5` FOREIGN KEY (`mount_adapter_id`) REFERENCES `MOUNT_ADAPTER` (`mount_adapter_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `fk_NEGATIVE_6` FOREIGN KEY (`metering_mode`) REFERENCES `METERING_MODE` (`metering_mode_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `fk_NEGATIVE_7` FOREIGN KEY (`exposure_program`) REFERENCES `EXPOSURE_PROGRAM` (`exposure_program_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `fk_NEGATIVE_8` FOREIGN KEY (`photographer_id`) REFERENCES `PERSON` (`person_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `fk_NEGATIVE_9` FOREIGN KEY (`shutter_speed`) REFERENCES `SHUTTER_SPEED` (`shutter_speed`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Table to catalog negatives (which includes positives/slide too). Negatives are created by cameras, belong to films and can be used to create scans or prints.';
SET character_set_client = @saved_cs_client;
DROP TABLE IF EXISTS `PRINT`;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `PRINT` (
`print_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Unique ID for the print',
`negative_id` int(11) DEFAULT NULL COMMENT 'ID of the negative that this print was made from',
`date` date DEFAULT NULL COMMENT 'The date that the print was made',
`paper_stock_id` int(11) DEFAULT NULL COMMENT 'ID of the paper stock used',
`height` decimal(4,1) DEFAULT NULL COMMENT 'Height of the print in inches',
`width` decimal(4,1) DEFAULT NULL COMMENT 'Width of the print in inches',
`aperture` decimal(3,1) DEFAULT NULL COMMENT 'Aperture used to make this print (numerical part only, e.g. 5.6)',
`exposure_time` decimal(5,1) DEFAULT NULL COMMENT 'Exposure time of this print in seconds',
`filtration_grade` decimal(2,1) DEFAULT NULL COMMENT 'Contrast grade of paper used',
`development_time` int(11) DEFAULT NULL COMMENT 'Development time of this print in seconds',
`bleach_time` time DEFAULT NULL COMMENT 'Duration of bleaching',
`toner_id` int(11) DEFAULT NULL COMMENT 'ID of the first toner used to make this print',
`toner_dilution` varchar(6) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Dilution of the first toner used to make this print',
`toner_time` time DEFAULT NULL COMMENT 'Duration of first toning',
`2nd_toner_id` int(11) DEFAULT NULL COMMENT 'ID of the second toner used to make this print',
`2nd_toner_dilution` varchar(6) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Dilution of the second toner used to make this print',
`2nd_toner_time` time DEFAULT NULL COMMENT 'Duration of second toning',
`own` tinyint(1) DEFAULT NULL COMMENT 'Whether we currently own this print',
`location` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'The place where this print is currently',
`sold_price` decimal(5,2) DEFAULT NULL COMMENT 'Sale price of the print',
`enlarger_id` int(11) DEFAULT NULL COMMENT 'ID of the enlarger used to make this print',
`lens_id` int(11) DEFAULT NULL COMMENT 'ID of the lens used to make this print',
`developer_id` int(11) DEFAULT NULL COMMENT 'ID of the developer used to develop this print',
`fine` tinyint(1) DEFAULT NULL COMMENT 'Whether this is a fine print',
`notes` text COLLATE utf8mb4_unicode_ci COMMENT 'Freeform notes about this print, e.g. dodging, burning & complex toning',
`archive_id` int(11) DEFAULT NULL COMMENT 'ID of the archive to which this print belongs',
`printer_id` int(11) DEFAULT NULL COMMENT 'ID of the person who made this print',
PRIMARY KEY (`print_id`),
KEY `fk_PRINT_1` (`paper_stock_id`),
KEY `fk_PRINT_2` (`negative_id`),
KEY `fk_PRINT_3` (`toner_id`),
KEY `fk_PRINT_4` (`enlarger_id`),
KEY `fk_PRINT_6` (`developer_id`),
KEY `fk_PRINT_5_idx` (`lens_id`),
KEY `fk_PRINT_7_idx` (`archive_id`),
KEY `fk_PRINT_8_idx` (`printer_id`),
CONSTRAINT `fk_PRINT_1` FOREIGN KEY (`paper_stock_id`) REFERENCES `PAPER_STOCK` (`paper_stock_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `fk_PRINT_2` FOREIGN KEY (`negative_id`) REFERENCES `NEGATIVE` (`negative_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `fk_PRINT_3` FOREIGN KEY (`toner_id`) REFERENCES `TONER` (`toner_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `fk_PRINT_4` FOREIGN KEY (`enlarger_id`) REFERENCES `ENLARGER` (`enlarger_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `fk_PRINT_5` FOREIGN KEY (`lens_id`) REFERENCES `LENS` (`lens_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `fk_PRINT_6` FOREIGN KEY (`developer_id`) REFERENCES `DEVELOPER` (`developer_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `fk_PRINT_7` FOREIGN KEY (`archive_id`) REFERENCES `ARCHIVE` (`archive_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `fk_PRINT_8` FOREIGN KEY (`printer_id`) REFERENCES `PERSON` (`person_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Table to catalog prints made from negatives';
SET character_set_client = @saved_cs_client;
DROP TABLE IF EXISTS `SCAN`;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `SCAN` (
`scan_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Unique ID for this scan',
`negative_id` int(11) DEFAULT NULL COMMENT 'ID of the negative that was scanned',
`print_id` int(11) DEFAULT NULL COMMENT 'ID of the print that was scanned',
`filename` varchar(128) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Filename of the scan',
`date` date DEFAULT NULL COMMENT 'Date that this scan was made',
`colour` tinyint(1) DEFAULT NULL COMMENT 'Whether this is a colour image',
`width` int(11) DEFAULT NULL COMMENT 'Width of the scanned image in pixels',
`height` int(11) DEFAULT NULL COMMENT 'Height of the scanned image in pixels',
PRIMARY KEY (`scan_id`),
KEY `fk_SCAN_1_idx` (`negative_id`),
KEY `fk_SCAN_2_idx` (`print_id`),
CONSTRAINT `fk_SCAN_1` FOREIGN KEY (`negative_id`) REFERENCES `NEGATIVE` (`negative_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `fk_SCAN_2` FOREIGN KEY (`print_id`) REFERENCES `PRINT` (`print_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Table to record all the images that have been scanned digitally';
SET character_set_client = @saved_cs_client;
DROP TABLE IF EXISTS `TO_PRINT`;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `TO_PRINT` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Unique ID of this table',
`negative_id` int(11) DEFAULT NULL COMMENT 'Negative ID to be printed',
`width` int(11) DEFAULT NULL COMMENT 'Width of print to be made',
`height` int(11) DEFAULT NULL COMMENT 'Height of print to be made',
`printed` tinyint(1) DEFAULT '0' COMMENT 'Whether the print has been made',
`print_id` int(11) DEFAULT NULL COMMENT 'ID of print made',
`recipient` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Recipient of the print',
`added` date DEFAULT NULL COMMENT 'Date that record was added',
PRIMARY KEY (`id`),
KEY `fk_TO_PRINT_1_idx` (`negative_id`),
CONSTRAINT `fk_TO_PRINT_1` FOREIGN KEY (`negative_id`) REFERENCES `NEGATIVE` (`negative_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Table to catalogue negatives that should be printed';
SET character_set_client = @saved_cs_client;
DROP TABLE IF EXISTS `EXHIBIT`;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `EXHIBIT` (
`exhibit_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Unique ID for this exhibit',
`exhibition_id` int(11) DEFAULT NULL COMMENT 'ID of the exhibition',
`print_id` int(11) DEFAULT NULL COMMENT 'ID of the print',
PRIMARY KEY (`exhibit_id`),
KEY `fk_EXHIBIT_1_idx` (`exhibition_id`),
KEY `fk_EXHIBIT_2_idx` (`print_id`),
CONSTRAINT `fk_EXHIBIT_1` FOREIGN KEY (`exhibition_id`) REFERENCES `EXHIBITION` (`exhibition_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `fk_EXHIBIT_2` FOREIGN KEY (`print_id`) REFERENCES `PRINT` (`print_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Table to record which prints were displayed in which exhibitions';
SET character_set_client = @saved_cs_client;
SET TIME_ZONE=@OLD_TIME_ZONE;
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT;
SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS;
SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION;
SET SQL_NOTES=@OLD_SQL_NOTES;
migrations/002-functions.sql view on Meta::CPAN
SET @saved_cs_client = @@character_set_client ;
SET @saved_cs_results = @@character_set_results ;
SET @saved_col_connection = @@collation_connection ;
SET character_set_client = utf8 ;
SET character_set_results = utf8 ;
SET collation_connection = utf8_general_ci ;
SET @saved_sql_mode = @@sql_mode ;
SET sql_mode = 'NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' ;
CREATE FUNCTION `DISPLAYSIZE`(`width` decimal(5,2), `height` decimal(5,2)) RETURNS varchar(10) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci
DETERMINISTIC
BEGIN
declare size varchar(10);
set size = concat(ifnull((trim(width) + 0),'?'),'×',ifnull((trim(height) + 0),'?'), '"');
RETURN size;
END ;;
SET sql_mode = @saved_sql_mode ;
SET character_set_client = @saved_cs_client ;
SET character_set_results = @saved_cs_results ;
SET collation_connection = @saved_col_connection ;
SET @saved_cs_client = @@character_set_client ;
SET @saved_cs_results = @@character_set_results ;
SET @saved_col_connection = @@collation_connection ;
SET character_set_client = utf8 ;
SET character_set_results = utf8 ;
SET collation_connection = utf8_general_ci ;
SET @saved_sql_mode = @@sql_mode ;
SET sql_mode = 'NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' ;
CREATE FUNCTION `lenstype`(n int) RETURNS varchar(32) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci
BEGIN
declare x varchar(32);
if n <= 8 then set x = 'Super telephoto' ;
elseif n > 8 and n <= 25 then set x ='Medium telephoto';
elseif n > 25 and n <= 39 then set x ='Short telephoto';
elseif n > 39 and n <= 62 then set x ='Normal';
elseif n > 62 and n <= 84 then set x ='Wide angle';
elseif n > 84 and n <= 120 then set x ='Super wide angle';
else set x = 'Fisheye';
end if;
migrations/002-functions.sql view on Meta::CPAN
SET character_set_results = @saved_cs_results ;
SET collation_connection = @saved_col_connection ;
SET @saved_cs_client = @@character_set_client ;
SET @saved_cs_results = @@character_set_results ;
SET @saved_col_connection = @@collation_connection ;
SET character_set_client = utf8 ;
SET character_set_results = utf8 ;
SET collation_connection = utf8_general_ci ;
SET @saved_sql_mode = @@sql_mode ;
SET sql_mode = 'NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' ;
CREATE FUNCTION `printbool`(b int) RETURNS varchar(3) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci
DETERMINISTIC
BEGIN
return if(b, 'Yes', 'No');
END ;;
SET sql_mode = @saved_sql_mode ;
SET character_set_client = @saved_cs_client ;
SET character_set_results = @saved_cs_results ;
SET collation_connection = @saved_col_connection ;
SET @saved_cs_client = @@character_set_client ;
SET @saved_cs_results = @@character_set_results ;
migrations/003-views.sql view on Meta::CPAN
) ENGINE=MyISAM;
SET character_set_client = @saved_cs_client;
DROP TABLE IF EXISTS `archive_contents`;
SET @saved_cs_client = @@character_set_client;
SET @saved_cs_results = @@character_set_results;
SET @saved_col_connection = @@collation_connection;
SET character_set_client = utf8;
SET character_set_results = utf8;
SET collation_connection = utf8_general_ci;
CREATE ALGORITHM=UNDEFINED
VIEW `archive_contents` AS select concat('Film #',`FILM`.`film_id`) AS `id`,(`FILM`.`notes` collate utf8mb4_unicode_ci) AS `opt`,`FILM`.`archive_id` AS `archive_id` from `FILM` union select concat('Print #',`PRINT`.`print_id`) AS `id`,`NEGATIVE`.`des...
SET character_set_client = @saved_cs_client;
SET character_set_results = @saved_cs_results;
SET collation_connection = @saved_col_connection;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `camera_chooser` (
`id` tinyint NOT NULL,
`opt` tinyint NOT NULL,
`manufacturer_id` tinyint NOT NULL,
`mount_id` tinyint NOT NULL,
migrations/022-create-lens-model.sql view on Meta::CPAN
CREATE TABLE `LENSMODEL` (
`lensmodel_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Unique ID for this lens model',
`mount_id` int(11) DEFAULT NULL COMMENT 'Denotes the ID of the lens mount, if this is an interchangeable lens',
`zoom` tinyint(1) DEFAULT NULL COMMENT 'Whether this is a zoom lens',
`min_focal_length` int(11) DEFAULT NULL COMMENT 'Shortest focal length of this lens, in mm',
`max_focal_length` int(11) DEFAULT NULL COMMENT 'Longest focal length of this lens, in mm',
`manufacturer_id` int(11) DEFAULT NULL COMMENT 'ID of the manufacturer of this lens',
`model` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Model name of this lens',
`closest_focus` int(11) DEFAULT NULL COMMENT 'The closest focus possible with this lens, in cm',
`max_aperture` decimal(4,1) DEFAULT NULL COMMENT 'Maximum (widest) aperture available on this lens (numerical part only, e.g. 2.8)',
`min_aperture` decimal(4,1) DEFAULT NULL COMMENT 'Minimum (narrowest) aperture available on this lens (numerical part only, e.g. 22)',
`elements` int(11) DEFAULT NULL COMMENT 'Number of optical lens elements',
`groups` int(11) DEFAULT NULL COMMENT 'Number of optical groups',
`weight` int(11) DEFAULT NULL COMMENT 'Weight of this lens, in grammes (g)',
`nominal_min_angle_diag` int(11) DEFAULT NULL COMMENT 'Nominal minimum diagonal field of view from manufacturer''s specs',
`nominal_max_angle_diag` int(11) DEFAULT NULL COMMENT 'Nominal maximum diagonal field of view from manufacturer''s specs',
`aperture_blades` int(11) DEFAULT NULL COMMENT 'Number of aperture blades',
`autofocus` tinyint(1) DEFAULT NULL COMMENT 'Whether this lens has autofocus capability',
`filter_thread` decimal(4,1) DEFAULT NULL COMMENT 'Diameter of lens filter thread, in mm',
`magnification` decimal(5,3) DEFAULT NULL COMMENT 'Maximum magnification ratio of the lens, expressed like 0.765',
`url` varchar(145) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'URL to more information about this lens',
`introduced` smallint(6) DEFAULT NULL COMMENT 'Year in which this lens model was introduced',
`discontinued` smallint(6) DEFAULT NULL COMMENT 'Year in which this lens model was discontinued',
`negative_size_id` int(11) DEFAULT NULL COMMENT 'ID of the negative size which this lens is designed for',
`fixed_mount` tinyint(1) DEFAULT NULL COMMENT 'Whether this is a fixed lens (i.e. on a compact camera)',
`notes` text COLLATE utf8mb4_unicode_ci COMMENT 'Freeform notes field',
`coating` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Notes about the lens coating type',
`hood` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Model number of the compatible lens hood',
`exif_lenstype` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'EXIF LensID number, if this lens has one officially registered. See documentation at http://www.sno.phy.queensu.ca/~phil/exiftool/TagNames/',
`rectilinear` tinyint(1) DEFAULT NULL COMMENT 'Whether this is a rectilinear lens',
`length` int(11) DEFAULT NULL COMMENT 'Length of lens in mm',
`diameter` int(11) DEFAULT NULL COMMENT 'Width of lens in mm',
`image_circle` int(11) DEFAULT NULL COMMENT 'Diameter of image circle projected by lens, in mm',
`formula` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Name of the type of lens formula (e.g. Tessar)',
`shutter_model` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Name of the integrated shutter, if any',
PRIMARY KEY (`lensmodel_id`),
KEY `fk_LENS_2` (`manufacturer_id`),
KEY `fk_LENS_3` (`mount_id`),
KEY `fk_LENS_4` (`negative_size_id`),
CONSTRAINT `fk_LENSMODEL_1` FOREIGN KEY (`manufacturer_id`) REFERENCES `MANUFACTURER` (`manufacturer_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `fk_LENSMODEL_2` FOREIGN KEY (`mount_id`) REFERENCES `MOUNT` (`mount_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `fk_LENSMODEL_3` FOREIGN KEY (`negative_size_id`) REFERENCES `NEGATIVE_SIZE` (`negative_size_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Table to catalog lens models';
migrations/035-series.sql view on Meta::CPAN
CREATE TABLE `SERIES` (
`series_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Unique ID of this series',
`name` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Name of this collection, e.g. Canon FD SLRs',
PRIMARY KEY (`series_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Table to list all series of cameras and lenses';
CREATE TABLE `SERIES_MEMBER` (
`series_member_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Unique ID of this series membership',
`series_id` int(11) DEFAULT NULL COMMENT 'ID of the series to which this camera model or lens model belongs',
`cameramodel_id` int(11) DEFAULT NULL COMMENT 'ID of the camera model',
`lensmodel_id` int(11) DEFAULT NULL COMMENT 'ID of the lens model',
PRIMARY KEY (`series_member_id`),
KEY `fk_SERIES_MEMBER_1_idx` (`series_id`),
KEY `fk_SERIES_MEMBER_2_idx` (`cameramodel_id`),
KEY `fk_SERIES_MEMBER_3_idx` (`lensmodel_id`),
CONSTRAINT `fk_SERIES_MEMBER_1` FOREIGN KEY (`series_id`) REFERENCES `SERIES` (`series_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `fk_SERIES_MEMBER_2` FOREIGN KEY (`cameramodel_id`) REFERENCES `CAMERAMODEL` (`cameramodel_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `fk_SERIES_MEMBER_3` FOREIGN KEY (`lensmodel_id`) REFERENCES `LENSMODEL` (`lensmodel_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Table to record which cameras and lenses belong to which series';
migrations/036-series-info.sql view on Meta::CPAN
CREATE
OR REPLACE ALGORITHM = UNDEFINED
VIEW `info_series` AS
SELECT
`SERIES_MEMBER`.`series_id` AS `Series ID`,
CONCAT(`CM`.`manufacturer`,
' ',
`CAMERAMODEL`.`model`) COLLATE utf8mb4_unicode_ci AS `Model`
FROM
((`SERIES_MEMBER`
LEFT JOIN `CAMERAMODEL` ON (`SERIES_MEMBER`.`cameramodel_id` = `CAMERAMODEL`.`cameramodel_id`))
LEFT JOIN `MANUFACTURER` `CM` ON (`CAMERAMODEL`.`manufacturer_id` = `CM`.`manufacturer_id`))
WHERE
`CAMERAMODEL`.`model` IS NOT NULL
UNION SELECT
`SERIES_MEMBER`.`series_id` AS `Series ID`,
CONCAT(`LM`.`manufacturer`,
' ',
`LENSMODEL`.`model`) COLLATE utf8mb4_unicode_ci AS `Model`
FROM
((`SERIES_MEMBER`
LEFT JOIN `LENSMODEL` ON (`SERIES_MEMBER`.`lensmodel_id` = `LENSMODEL`.`lensmodel_id`))
LEFT JOIN `MANUFACTURER` `LM` ON (`LENSMODEL`.`manufacturer_id` = `LM`.`manufacturer_id`))
WHERE
`LENSMODEL`.`model` IS NOT NULL;
migrations/037-series-info-views.sql view on Meta::CPAN
CREATE
OR REPLACE ALGORITHM = UNDEFINED
VIEW `info_series_got` AS
SELECT
`SERIES_MEMBER`.`series_id` AS `Series ID`,
CONCAT(`CM`.`manufacturer`,
' ',
`CAMERAMODEL`.`model`) COLLATE utf8mb4_unicode_ci AS `Model`
FROM
(((`SERIES_MEMBER`
LEFT JOIN `CAMERAMODEL` ON (`SERIES_MEMBER`.`cameramodel_id` = `CAMERAMODEL`.`cameramodel_id`))
LEFT JOIN `MANUFACTURER` `CM` ON (`CAMERAMODEL`.`manufacturer_id` = `CM`.`manufacturer_id`))
JOIN `CAMERA` ON (`CAMERA`.`cameramodel_id` = `CAMERAMODEL`.`cameramodel_id`))
WHERE
`CAMERAMODEL`.`model` IS NOT NULL
UNION SELECT
`SERIES_MEMBER`.`series_id` AS `Series ID`,
CONCAT(`LM`.`manufacturer`,
' ',
`LENSMODEL`.`model`) COLLATE utf8mb4_unicode_ci AS `Model`
FROM
(((`SERIES_MEMBER`
LEFT JOIN `LENSMODEL` ON (`SERIES_MEMBER`.`lensmodel_id` = `LENSMODEL`.`lensmodel_id`))
LEFT JOIN `MANUFACTURER` `LM` ON (`LENSMODEL`.`manufacturer_id` = `LM`.`manufacturer_id`))
JOIN `LENS` ON (`LENS`.`lensmodel_id` = `LENSMODEL`.`lensmodel_id`))
WHERE
`LENSMODEL`.`model` IS NOT NULL;
CREATE
OR REPLACE ALGORITHM = UNDEFINED
VIEW `info_series_need` AS
SELECT
`SERIES_MEMBER`.`series_id` AS `Series ID`,
CONCAT(`CM`.`manufacturer`,
' ',
`CAMERAMODEL`.`model`) COLLATE utf8mb4_unicode_ci AS `Model`
FROM
(((`SERIES_MEMBER`
LEFT JOIN `CAMERAMODEL` ON (`SERIES_MEMBER`.`cameramodel_id` = `CAMERAMODEL`.`cameramodel_id`))
LEFT JOIN `MANUFACTURER` `CM` ON (`CAMERAMODEL`.`manufacturer_id` = `CM`.`manufacturer_id`))
LEFT JOIN `CAMERA` ON (`CAMERA`.`cameramodel_id` = `CAMERAMODEL`.`cameramodel_id`))
WHERE
`CAMERAMODEL`.`model` IS NOT NULL
AND `CAMERA`.`cameramodel_id` IS NULL
UNION SELECT
`SERIES_MEMBER`.`series_id` AS `Series ID`,
CONCAT(`LM`.`manufacturer`,
' ',
`LENSMODEL`.`model`) COLLATE utf8mb4_unicode_ci AS `Model`
FROM
(((`SERIES_MEMBER`
LEFT JOIN `LENSMODEL` ON (`SERIES_MEMBER`.`lensmodel_id` = `LENSMODEL`.`lensmodel_id`))
LEFT JOIN `MANUFACTURER` `LM` ON (`LENSMODEL`.`manufacturer_id` = `LM`.`manufacturer_id`))
LEFT JOIN `LENS` ON (`LENS`.`lensmodel_id` = `LENSMODEL`.`lensmodel_id`))
WHERE
`LENSMODEL`.`model` IS NOT NULL
AND `LENS`.`lensmodel_id` IS NULL;
migrations/041-series-info.sql view on Meta::CPAN
CREATE
OR REPLACE ALGORITHM = UNDEFINED
VIEW `info_series` AS
SELECT
`SERIES_MEMBER`.`series_id` AS `Series ID`,
CONCAT(`CM`.`manufacturer`,
' ',
`CAMERAMODEL`.`model`) COLLATE utf8mb4_unicode_ci AS `Model`,
IF(`CAMERA`.`camera_id` IS NOT NULL,
'✓',
'✗') AS `Got`
FROM
(((`SERIES_MEMBER`
LEFT JOIN `CAMERAMODEL` ON (`SERIES_MEMBER`.`cameramodel_id` = `CAMERAMODEL`.`cameramodel_id`))
LEFT JOIN `MANUFACTURER` `CM` ON (`CAMERAMODEL`.`manufacturer_id` = `CM`.`manufacturer_id`))
LEFT JOIN `CAMERA` ON (`CAMERA`.`cameramodel_id` = `CAMERAMODEL`.`cameramodel_id`))
WHERE
`CAMERAMODEL`.`model` IS NOT NULL
UNION SELECT
`SERIES_MEMBER`.`series_id` AS `Series ID`,
CONCAT(`LM`.`manufacturer`,
' ',
`LENSMODEL`.`model`) COLLATE utf8mb4_unicode_ci AS `Model`,
IF(`LENS`.`lens_id` IS NOT NULL,
'✓',
'✗') AS `Got`
FROM
(((`SERIES_MEMBER`
LEFT JOIN `LENSMODEL` ON (`SERIES_MEMBER`.`lensmodel_id` = `LENSMODEL`.`lensmodel_id`))
LEFT JOIN `MANUFACTURER` `LM` ON (`LENSMODEL`.`manufacturer_id` = `LM`.`manufacturer_id`))
LEFT JOIN `LENS` ON (`LENS`.`lensmodel_id` = `LENSMODEL`.`lensmodel_id`))
WHERE
`LENSMODEL`.`model` IS NOT NULL;
( run in 1.841 second using v1.01-cache-2.11-cpan-88abd93f124 )