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;
Related tables
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