Photo Organizer

This is an old revision of the document!

Database View Description of "view_lens_type"

Name

view_lens_type

Description

Purpose

Schema

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;

Related tables

Related indexes

Related functions

Related sequences

History

Used first

Version 2.5

Used last

Still in use

Change history

= 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;