User Tools

Site Tools


podoc:view_lens_sql_view

Database View Description of "view_lens"

Name

view_lens

Description

Purpose

Schema

CREATE VIEW view_lens AS
  SELECT lens.identifier, manufacturer.name, model, variation,
        s_min_aperture, s_max_aperture, s_min_focal_length,
        s_max_focal_length, lens.serial_number, lens.users,
        date_of_purchase, lens.purchased_new,
        manufacturer.url, (access_type.value) AS access_rights,
        exiftool_lens_id
    FROM lens_type, lens, manufacturer, access_type,
         (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.identifier = lens.type
     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
     AND lens.access_rights = access_type.identifier
   ORDER BY manufacturer.name, s_min_focal_length, model, variation;

History

Used first

Version 2.5

Used last

Still in use

Change history

= Version 2.5 =

CREATE VIEW view_lens AS
  SELECT lens.identifier, manufacturer.name, model, variation, s_min_aperture, s_max_aperture, s_min_focal_length, s_max_focal_length, lens.serial_number, lens.users, date_of_purchase, lens.purchased_new, manufacturer.url, (access_type.value) AS access_rights
    FROM lens_type, lens, manufacturer, access_type,
         (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.identifier = lens.type
     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
     AND lens.access_rights = access_type.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;
 
CREATE VIEW view_lens AS
  SELECT lens.identifier, manufacturer.name, model, variation,
        s_min_aperture, s_max_aperture, s_min_focal_length,
        s_max_focal_length, lens.serial_number, lens.users,
        date_of_purchase, lens.purchased_new,
        manufacturer.url, (access_type.value) AS access_rights
    FROM lens_type, lens, manufacturer, access_type,
         (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.identifier = lens.type
     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
     AND lens.access_rights = access_type.identifier
   ORDER BY manufacturer.name, s_min_focal_length, model, variation;

= Version 2.34 =

DROP VIEW view_lens;
 
CREATE VIEW view_lens AS
  SELECT lens.identifier, manufacturer.name, model, variation,
        s_min_aperture, s_max_aperture, s_min_focal_length,
        s_max_focal_length, lens.serial_number, lens.users,
        date_of_purchase, lens.purchased_new,
        manufacturer.url, (access_type.value) AS access_rights,
        exiftool_lens_id
    FROM lens_type, lens, manufacturer, access_type,
         (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.identifier = lens.type
     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
     AND lens.access_rights = access_type.identifier
   ORDER BY manufacturer.name, s_min_focal_length, model, variation;
podoc/view_lens_sql_view.txt · Last modified: 2007/10/19 20:13 by Luud