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

Both sides previous revisionPrevious revision
Next revision
Previous revision
podoc:view_lens_sql_view [2007/04/17 13:13] Luudpodoc:view_lens_sql_view [2007/10/19 20:13] (current) Luud
Line 11: Line 11:
 === Schema === === Schema ===
  
-<code>+<code sql>
 create view view_lens as create view view_lens as
   select lens.identifier, manufacturer.name, model, variation,   select lens.identifier, manufacturer.name, model, variation,
Line 17: Line 17:
         s_max_focal_length, lens.serial_number, lens.users,         s_max_focal_length, lens.serial_number, lens.users,
         date_of_purchase, lens.purchased_new,         date_of_purchase, lens.purchased_new,
-        manufacturer.url, (access_type.value) as access_rights+        manufacturer.url, (access_type.value) as access_rights
 +        exiftool_lens_id
     from lens_type, lens, manufacturer, access_type,     from lens_type, lens, manufacturer, access_type,
          (select identifier as identifier, value as s_min_focal_length          (select identifier as identifier, value as s_min_focal_length
Line 45: Line 46:
   * [[lens_type sql table|lens_type]]   * [[lens_type sql table|lens_type]]
   * [[manufacturer sql table|manufacturer]]   * [[manufacturer sql table|manufacturer]]
- 
-=== Related indexes === 
- 
-=== Related functions === 
- 
-=== Related sequences === 
  
 === History === === History ===
Line 66: Line 61:
 = Version 2.5 = = Version 2.5 =
  
-<code>+<code sql>
 create view view_lens as 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   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
Line 86: Line 81:
 = Version 2.32 = = Version 2.32 =
  
-<code>+<code sql>
 -- Convert focal length from a varchar to an integer -- Convert focal length from a varchar to an integer
 --  and aperture to a numeric type --  and aperture to a numeric type
Line 98: Line 93:
         date_of_purchase, lens.purchased_new,         date_of_purchase, lens.purchased_new,
         manufacturer.url, (access_type.value) as access_rights         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.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,     from lens_type, lens, manufacturer, access_type,
          (select identifier as identifier, value as s_min_focal_length          (select identifier as identifier, value as s_min_focal_length
podoc/view_lens_sql_view.1176815613.txt.gz · Last modified: 2007/04/17 13:13 by Luud