Differences

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

--- podoc:aperture_sql_table [2007/04/12 13:03] – Luud
+++ podoc:aperture_sql_table [2007/04/20 14:22] (current) – Luud
@@ Line 1: / Line 1: @@
-===== Database Table Description of "aperture " =====
+===== Database Table Description of "aperture" =====
 === Name ===
 aperture
 === Description ===
+This table contains a list of all valid apertures (eg f/2, f/5.6, etc).
 === Purpose ===
+To enforce referential integrity.
+Note:  This table will likely be eliminated in the future in favor of direct aperture representation -- this is an unnecessary abstraction.
 === Schema ===
-  create table aperture (
+<code sql>
-    identifier            integer not null primary key,
+create table aperture (
-    value                 varchar(10)
+  identifier            integer not null primary key,
-  );
+  value                 varchar(10)
+);
+</code>
+=== References tables ===
+None.
+=== Is referenced by tables ===
+  * [[lens_type sql table|lens_type]](max_aperture) references identifier
+  * [[lens_type sql table|lens_type]](min_aperture) references identifier
+  * [[photo_tech sql table|photo_tech]](aperture) references identifier
 === Related sequences ===
-  * [[ aperture_id_sequence_sql_sequence | aperture_id_sequence]]
+  * [[aperture_id_sequence sql sequence|aperture_id_sequence]]
 === Related indexes ===
+None.
 === Related functions ===
+None.
 === Related views ===
+  * [[view_lens sql view|view_lens]]
+  * [[view_lens_type sql view|view_lens_type]]
 === History ===
@@ Line 36: / Line 61: @@
 Still in use
-== Change history ==
+== Schema change history ==
+= Version 2.5 =
+<code sql>
+create table aperture (
+  identifier            integer not null primary key,
+  value                 numeric
+);
+</code>
+= Version 2.32 =
+<code sql>
+-- Convert aperture from a varchar to a numeric type
+alter table aperture add value_num numeric;
+update aperture set value_num = value::text::numeric;
+alter table aperture drop value;
+alter table aperture rename value_num to value;
+</code>
+== Data change history ==
 = Version 2.5 =
-  create table aperture (
+<code sql>
-    identifier            integer not null primary key,
+insert into aperture values (nextval('aperture_id_sequence'), '0.5');
-    value                 varchar(10)
+insert into aperture values (nextval('aperture_id_sequence'), '0.75');
-  );
+insert into aperture values (nextval('aperture_id_sequence'), '1.0');
+insert into aperture values (nextval('aperture_id_sequence'), '1.2');
+insert into aperture values (nextval('aperture_id_sequence'), '1.3');
+insert into aperture values (nextval('aperture_id_sequence'), '1.4');
+insert into aperture values (nextval('aperture_id_sequence'), '1.6');
+insert into aperture values (nextval('aperture_id_sequence'), '1.8');
+insert into aperture values (nextval('aperture_id_sequence'), '2.0');
+insert into aperture values (nextval('aperture_id_sequence'), '2.2');
+insert into aperture values (nextval('aperture_id_sequence'), '2.5');
+insert into aperture values (nextval('aperture_id_sequence'), '2.8');
+insert into aperture values (nextval('aperture_id_sequence'), '3.2');
+insert into aperture values (nextval('aperture_id_sequence'), '3.5');
+insert into aperture values (nextval('aperture_id_sequence'), '4.0');
+insert into aperture values (nextval('aperture_id_sequence'), '4.5');
+insert into aperture values (nextval('aperture_id_sequence'), '5.0');
+insert into aperture values (nextval('aperture_id_sequence'), '5.6');
+insert into aperture values (nextval('aperture_id_sequence'), '6.3');
+insert into aperture values (nextval('aperture_id_sequence'), '7.1');
+insert into aperture values (nextval('aperture_id_sequence'), '8.0');
+insert into aperture values (nextval('aperture_id_sequence'), '9.0');
+insert into aperture values (nextval('aperture_id_sequence'), '10');
+insert into aperture values (nextval('aperture_id_sequence'), '11');
+insert into aperture values (nextval('aperture_id_sequence'), '13');
+insert into aperture values (nextval('aperture_id_sequence'), '14');
+insert into aperture values (nextval('aperture_id_sequence'), '16');
+insert into aperture values (nextval('aperture_id_sequence'), '18');
+insert into aperture values (nextval('aperture_id_sequence'), '20');
+insert into aperture values (nextval('aperture_id_sequence'), '22');
+insert into aperture values (nextval('aperture_id_sequence'), '25');
+insert into aperture values (nextval('aperture_id_sequence'), '28');
+insert into aperture values (nextval('aperture_id_sequence'), '32');
+insert into aperture values (nextval('aperture_id_sequence'), '36');
+insert into aperture values (nextval('aperture_id_sequence'), '40');
+insert into aperture values (nextval('aperture_id_sequence'), '45');
+insert into aperture values (nextval('aperture_id_sequence'), '51');
+insert into aperture values (nextval('aperture_id_sequence'), '57');
+insert into aperture values (nextval('aperture_id_sequence'), '64');
+insert into aperture values (nextval('aperture_id_sequence'), '72');
+insert into aperture values (nextval('aperture_id_sequence'), '81');
+insert into aperture values (nextval('aperture_id_sequence'), '90');
+insert into aperture values (nextval('aperture_id_sequence'), '103');
+insert into aperture values (nextval('aperture_id_sequence'), '115');
+insert into aperture values (nextval('aperture_id_sequence'), '128');
+</code>