view release on metacpan or  search on metacpan

migrations/029-lensmodel-views.sql  view on Meta::CPAN

                1
            THEN
                IFNULL(`ARCHIVE`.`name`,
                        'Owned; location unknown')
            WHEN
                0
            THEN
                IFNULL(`PRINT`.`location`,
                        'Not owned; location unknown')
            ELSE 'No location information'
        END) AS `Location`
    FROM
        ((((((((((((((`PRINT`
        JOIN `PAPER_STOCK` ON ((`PRINT`.`paper_stock_id` = `PAPER_STOCK`.`paper_stock_id`)))
        JOIN `MANUFACTURER` `PAPER_STOCK_MANUFACTURER` ON ((`PAPER_STOCK`.`manufacturer_id` = `PAPER_STOCK_MANUFACTURER`.`manufacturer_id`)))
        LEFT JOIN `ENLARGER` ON ((`PRINT`.`enlarger_id` = `ENLARGER`.`enlarger_id`)))
        JOIN `MANUFACTURER` `ENLARGER_MANUFACTURER` ON ((`ENLARGER`.`manufacturer_id` = `ENLARGER_MANUFACTURER`.`manufacturer_id`)))
        LEFT JOIN `LENS` ON ((`PRINT`.`lens_id` = `LENS`.`lens_id`)))
        JOIN `LENSMODEL` ON ((`LENS`.`lensmodel_id` = `LENSMODEL`.`lensmodel_id`)))
        JOIN `MANUFACTURER` `LENS_MANUFACTURER` ON ((`LENSMODEL`.`manufacturer_id` = `LENS_MANUFACTURER`.`manufacturer_id`)))
        LEFT JOIN `TONER` `FIRSTTONER` ON ((`PRINT`.`toner_id` = `FIRSTTONER`.`toner_id`)))
        LEFT JOIN `MANUFACTURER` `FIRSTTONER_MANUFACTURER` ON ((`FIRSTTONER`.`manufacturer_id` = `FIRSTTONER_MANUFACTURER`.`manufacturer_id`)))
        LEFT JOIN `TONER` `SECONDTONER` ON ((`PRINT`.`2nd_toner_id` = `SECONDTONER`.`toner_id`)))
        LEFT JOIN `MANUFACTURER` `SECONDTONER_MANUFACTURER` ON ((`SECONDTONER`.`manufacturer_id` = `SECONDTONER_MANUFACTURER`.`manufacturer_id`)))
        LEFT JOIN `NEGATIVE` ON ((`PRINT`.`negative_id` = `NEGATIVE`.`negative_id`)))
        LEFT JOIN `PERSON` ON ((`NEGATIVE`.`photographer_id` = `PERSON`.`person_id`)))
        LEFT JOIN `ARCHIVE` ON ((`PRINT`.`archive_id` = `ARCHIVE`.`archive_id`)));


CREATE
    OR REPLACE ALGORITHM = UNDEFINED
VIEW `info_lens` AS
    SELECT
        `LENS`.`lens_id` AS `Lens ID`,
        `MOUNT`.`mount` AS `Mount`,
        IF(`LENSMODEL`.`zoom`,
            CONCAT(`LENSMODEL`.`min_focal_length`,
                    '-',
                    `LENSMODEL`.`max_focal_length`,
                    'mm'),
            CONCAT(`LENSMODEL`.`min_focal_length`, 'mm')) AS `Focal length`,
        CONCAT(`MANUFACTURER`.`manufacturer`,
                ' ',
                `LENSMODEL`.`model`) AS `Lens`,
        CONCAT(`LENSMODEL`.`closest_focus`, 'cm') AS `Closest focus`,
        CONCAT('f/', `LENSMODEL`.`max_aperture`) AS `Maximum aperture`,
        CONCAT('f/', `LENSMODEL`.`min_aperture`) AS `Minimum aperture`,
        CONCAT(`LENSMODEL`.`elements`,
                '/',
                `LENSMODEL`.`groups`) AS `Elements/Groups`,
        CONCAT(`LENSMODEL`.`weight`, 'g') AS `Weight`,
        IF(`LENSMODEL`.`zoom`,
            CONCAT(`LENSMODEL`.`nominal_max_angle_diag`,
                    '°-',
                    `LENSMODEL`.`nominal_min_angle_diag`,
                    '°'),
            CONCAT(`LENSMODEL`.`nominal_max_angle_diag`,
                    '°')) AS `Angle of view`,
        `LENSMODEL`.`aperture_blades` AS `Aperture blades`,
        PRINTBOOL(`LENSMODEL`.`autofocus`) AS `Autofocus`,
        CONCAT(`LENSMODEL`.`filter_thread`, 'mm') AS `Filter thread`,
        CONCAT(`LENSMODEL`.`magnification`, '×') AS `Maximum magnification`,
        `LENSMODEL`.`url` AS `URL`,
        `LENS`.`serial` AS `Serial number`,
        `LENS`.`date_code` AS `Date code`,
        CONCAT(IFNULL(`LENSMODEL`.`introduced`, '?'),
                '-',
                IFNULL(`LENSMODEL`.`discontinued`, '?')) AS `Manufactured between`,
        `LENS`.`manufactured` AS `Year of manufacture`,
        `NEGATIVE_SIZE`.`negative_size` AS `Negative size`,
        `LENS`.`acquired` AS `Date acquired`,
        CONCAT('£', `LENS`.`cost`) AS `Cost`,
        `LENS`.`notes` AS `Notes`,
        `LENS`.`lost` AS `Date lost`,
        CONCAT('£', `LENS`.`lost_price`) AS `Price sold`,
        `LENS`.`source` AS `Source`,
        `LENSMODEL`.`coating` AS `Coating`,
        `LENSMODEL`.`hood` AS `Hood`,
        `LENSMODEL`.`exif_lenstype` AS `EXIF LensType`,
        PRINTBOOL(`LENSMODEL`.`rectilinear`) AS `Rectilinear`,
        CONCAT(`LENSMODEL`.`length`,
                '×',
                `LENSMODEL`.`diameter`,
                'mm') AS `Dimensions (l×w)`,
        `CONDITION`.`name` AS `Condition`,
        CONCAT(`LENSMODEL`.`image_circle`, 'mm') AS `Image circle`,
        `LENSMODEL`.`formula` AS `Optical formula`,
        `LENSMODEL`.`shutter_model` AS `Shutter model`,
        COUNT(`NEGATIVE`.`negative_id`) AS `Frames shot`
    FROM
        ((((((`LENS`
        JOIN `LENSMODEL` ON ((`LENS`.`lensmodel_id` = `LENSMODEL`.`lensmodel_id`)))
        LEFT JOIN `MOUNT` ON ((`LENSMODEL`.`mount_id` = `MOUNT`.`mount_id`)))
        LEFT JOIN `MANUFACTURER` ON ((`LENSMODEL`.`manufacturer_id` = `MANUFACTURER`.`manufacturer_id`)))
        LEFT JOIN `CONDITION` ON ((`LENS`.`condition_id` = `CONDITION`.`condition_id`)))
        LEFT JOIN `NEGATIVE_SIZE` ON ((`LENSMODEL`.`negative_size_id` = `NEGATIVE_SIZE`.`negative_size_id`)))
        LEFT JOIN `NEGATIVE` ON ((`NEGATIVE`.`lens_id` = `LENS`.`lens_id`)))
    WHERE
        ((`LENS`.`own` = 1)
            AND (`LENSMODEL`.`fixed_mount` = 0))
    GROUP BY `LENS`.`lens_id`;


CREATE
    OR REPLACE ALGORITHM = UNDEFINED
VIEW `exifdata` AS
    SELECT
        `f`.`film_id` AS `film_id`,
        `n`.`negative_id` AS `negative_id`,
        `PRINT`.`print_id` AS `print_id`,
        `cm`.`manufacturer` AS `Make`,
        CONCAT(`cm`.`manufacturer`, ' ', `cmod`.`model`) AS `Model`,
        `p`.`name` AS `Author`,
        `lm`.`manufacturer` AS `LensMake`,
        CONCAT(`lm`.`manufacturer`, ' ', `lmod`.`model`) AS `LensModel`,
        CONCAT(`lm`.`manufacturer`, ' ', `lmod`.`model`) AS `Lens`,
        `l`.`serial` AS `LensSerialNumber`,
        `c`.`serial` AS `SerialNumber`,
        CONCAT(`f`.`directory`, '/', `s`.`filename`) AS `path`,
        `lmod`.`max_aperture` AS `MaxApertureValue`,
        `f`.`directory` AS `directory`,