User Tools

Site Tools


podoc:view_lens_sql_view

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Next revision
Previous revision
podoc:view_lens_sql_view [2007/04/12 12:24] – created Luudpodoc:view_lens_sql_view [2007/10/19 20:13] (current) Luud
Line 1: Line 1:
-===== Database View Description =====+===== Database View Description of "view_lens" =====
  
 === Name === === Name ===
 +
 +view_lens
  
 === Description === === Description ===
Line 9: Line 11:
 === Schema === === Schema ===
  
-=== Related tables===+<code sql> 
 +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; 
 +</code>
  
-=== Related sequences ===+=== Related tables ===
  
-=== Related indexes === +  * [[access_type sql table|access_type]] 
- +  * [[aperture sql table|aperture]] 
-=== Related functions ===+  * [[focal_length sql table|focal_length]] 
 +  * [[lens sql table|lens]] 
 +  * [[lens_type sql table|lens_type]] 
 +  * [[manufacturer sql table|manufacturer]]
  
 === History === === History ===
Line 31: Line 61:
 = Version 2.5 = = Version 2.5 =
  
-  create view ... ( +<code sql> 
-    ...   +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; 
 +</code> 
 + 
 += Version 2.32 = 
 + 
 +<code sql> 
 +-- 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.valueas 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; 
 +</code> 
 + 
 += Version 2.34 = 
 + 
 +<code sql> 
 +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; 
 +</code> 
podoc/view_lens_sql_view.1176380650.txt.gz · Last modified: 2007/04/12 12:24 by Luud