podoc:view_lens_sql_view
Differences
This shows you the differences between two versions of the page.
Next revision | Previous revision | ||
podoc:view_lens_sql_view [2007/04/12 12:24] – created Luud | podoc:view_lens_sql_view [2007/10/19 20:13] (current) – Luud | ||
---|---|---|---|
Line 1: | Line 1: | ||
- | ===== Database View Description ===== | + | ===== Database View Description |
=== Name === | === Name === | ||
+ | |||
+ | view_lens | ||
=== Description === | === Description === | ||
Line 9: | Line 11: | ||
=== Schema === | === Schema === | ||
- | === Related tables=== | + | <code sql> |
+ | create view view_lens as | ||
+ | select lens.identifier, | ||
+ | s_min_aperture, | ||
+ | s_max_focal_length, | ||
+ | date_of_purchase, | ||
+ | manufacturer.url, | ||
+ | exiftool_lens_id | ||
+ | from lens_type, lens, manufacturer, | ||
+ | | ||
+ | from focal_length) as t_min_focal_length, | ||
+ | | ||
+ | from focal_length) as t_max_focal_length, | ||
+ | | ||
+ | aperture) as t_min_aperture, | ||
+ | | ||
+ | aperture) as t_max_aperture | ||
+ | where lens_type.manufacturer | ||
+ | and lens_type.identifier | ||
+ | and lens_type.min_focal_length | ||
+ | and lens_type.max_focal_length | ||
+ | and lens_type.min_aperture | ||
+ | and lens_type.max_aperture | ||
+ | and lens.access_rights = access_type.identifier | ||
+ | order by manufacturer.name, | ||
+ | </ | ||
- | === Related | + | === Related |
- | === 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 = | ||
- | | + | <code sql> |
- | ... | + | create view view_lens as |
- | ); | + | select lens.identifier, manufacturer.name, model, variation, s_min_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, | ||
+ | </ | ||
+ | |||
+ | = 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 | ||
+ | | ||
+ | s_min_aperture, | ||
+ | s_max_focal_length, | ||
+ | date_of_purchase, | ||
+ | manufacturer.url, | ||
+ | from lens_type, lens, manufacturer, | ||
+ | | ||
+ | from focal_length) as t_min_focal_length, | ||
+ | | ||
+ | from focal_length) as t_max_focal_length, | ||
+ | | ||
+ | aperture) as t_min_aperture, | ||
+ | | ||
+ | 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, | ||
+ | </ | ||
+ | |||
+ | = Version 2.34 = | ||
+ | |||
+ | <code sql> | ||
+ | drop view view_lens; | ||
+ | |||
+ | create view view_lens as | ||
+ | select lens.identifier, | ||
+ | s_min_aperture, | ||
+ | s_max_focal_length, | ||
+ | date_of_purchase, | ||
+ | manufacturer.url, | ||
+ | exiftool_lens_id | ||
+ | from lens_type, lens, manufacturer, | ||
+ | | ||
+ | from focal_length) as t_min_focal_length, | ||
+ | | ||
+ | from focal_length) as t_max_focal_length, | ||
+ | | ||
+ | aperture) as t_min_aperture, | ||
+ | | ||
+ | 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, | ||
+ | </ |
podoc/view_lens_sql_view.1176380650.txt.gz · Last modified: 2007/04/12 12:24 by Luud