Photo Organizer

view_lens_type

CREATE VIEW view_lens_type AS SELECT lens_type.identifier, manufacturer.name,
        model, variation, s_min_aperture, s_max_aperture,
        s_min_focal_length, s_max_focal_length, manufacturer.url,
        exiftool_lens_id
    FROM lens_type, manufacturer,
         (SELECT identifier AS identifier, VALUE AS s_min_focal_length
        FROM focal_length) AS t_min_focal_length,
         (SELECT identifier AS identifier, VALUE AS s_max_focal_length
        FROM focal_length) AS t_max_focal_length,
         (SELECT identifier AS identifier, VALUE AS s_min_aperture FROM
        aperture) AS t_min_aperture,
         (SELECT identifier AS identifier, VALUE AS s_max_aperture FROM
        aperture) AS t_max_aperture
   WHERE lens_type.manufacturer = manufacturer.identifier
     AND lens_type.min_focal_length = t_min_focal_length.identifier
     AND lens_type.max_focal_length = t_max_focal_length.identifier
     AND lens_type.min_aperture = t_min_aperture.identifier
     AND lens_type.max_aperture = t_max_aperture.identifier
   ORDER BY manufacturer.name, s_min_focal_length, model, variation;

• aperture
• focal_length
• lens_type
• manufacturer

Version 2.5

Still in use

= Version 2.5 =

CREATE VIEW view_lens_type AS
  SELECT lens_type.identifier, manufacturer.name, model, variation, s_min_aperture, s_max_aperture, s_min_focal_length, s_max_focal_length, manufacturer.url
    FROM lens_type, manufacturer,
         (SELECT identifier AS identifier, VALUE AS s_min_focal_length FROM focal_length) AS t_min_focal_length,
         (SELECT identifier AS identifier, VALUE AS s_max_focal_length FROM focal_length) AS t_max_focal_length,
         (SELECT identifier AS identifier, VALUE AS s_min_aperture FROM aperture) AS t_min_aperture,
         (SELECT identifier AS identifier, VALUE AS s_max_aperture FROM aperture) AS t_max_aperture
   WHERE lens_type.manufacturer = manufacturer.identifier
     AND lens_type.min_focal_length = t_min_focal_length.identifier
     AND lens_type.max_focal_length = t_max_focal_length.identifier
     AND lens_type.min_aperture = t_min_aperture.identifier
     AND lens_type.max_aperture = t_max_aperture.identifier
   ORDER BY manufacturer.name, s_min_focal_length, model, variation;

= Version 2.32 =

-- Convert focal length from a varchar to an integer
--  and aperture to a numeric type
 
DROP VIEW view_lens_type;
 
CREATE VIEW view_lens_type AS SELECT lens_type.identifier, 
manufacturer.name,
        model, variation, s_min_aperture, s_max_aperture,
        s_min_focal_length, s_max_focal_length, manufacturer.url
    FROM lens_type, manufacturer,
         (SELECT identifier AS identifier, VALUE AS s_min_focal_length
        FROM focal_length) AS t_min_focal_length,
         (SELECT identifier AS identifier, VALUE AS s_max_focal_length
        FROM focal_length) AS t_max_focal_length,
         (SELECT identifier AS identifier, VALUE AS s_min_aperture FROM
        aperture) AS t_min_aperture,
         (SELECT identifier AS identifier, VALUE AS s_max_aperture FROM
        aperture) AS t_max_aperture
   WHERE lens_type.manufacturer = manufacturer.identifier
     AND lens_type.min_focal_length = t_min_focal_length.identifier
     AND lens_type.max_focal_length = t_max_focal_length.identifier
     AND lens_type.min_aperture = t_min_aperture.identifier
     AND lens_type.max_aperture = t_max_aperture.identifier
   ORDER BY manufacturer.name, s_min_focal_length, model, variation;

= Version 2.34 =

DROP VIEW view_lens_type;
 
CREATE VIEW view_lens_type AS SELECT lens_type.identifier, manufacturer.name,
        model, variation, s_min_aperture, s_max_aperture,
        s_min_focal_length, s_max_focal_length, manufacturer.url,
        exiftool_lens_id
    FROM lens_type, manufacturer,
         (SELECT identifier AS identifier, VALUE AS s_min_focal_length
        FROM focal_length) AS t_min_focal_length,
         (SELECT identifier AS identifier, VALUE AS s_max_focal_length
        FROM focal_length) AS t_max_focal_length,
         (SELECT identifier AS identifier, VALUE AS s_min_aperture FROM
        aperture) AS t_min_aperture,
         (SELECT identifier AS identifier, VALUE AS s_max_aperture FROM
        aperture) AS t_max_aperture
   WHERE lens_type.manufacturer = manufacturer.identifier
     AND lens_type.min_focal_length = t_min_focal_length.identifier
     AND lens_type.max_focal_length = t_max_focal_length.identifier
     AND lens_type.min_aperture = t_min_aperture.identifier
     AND lens_type.max_aperture = t_max_aperture.identifier
   ORDER BY manufacturer.name, s_min_focal_length, model, variation;