[Change OBSERVERS table to reflect reality. Karl O. Pinc **20110407004956 Change OBSERVERS.initials from a limit of 3 to a limit of 4 characters. Add a Role column. Change GPSInitials column to OldGPSInitials and make it, in effect, another notes column. ] hunk ./db/alterations 1099 +------------------------------------------------------------- +-- +-- Change OBSERVERS table to reflect reality. +-- +BEGIN; + +-- observers.initials gets an extra character to deal with duplicate +-- initials. +ALTER TABLE observers + ALTER COLUMN initials TYPE VARCHAR(4); + +-- observers.gpsinitials is changed to oldgpsinitials. The column +-- can now be NULL because some folks do not collect gps data. +-- The column is now effectively a comment, it is not used programmaticaly, +-- so allow unlimited length. The trigger has been removed so as to +-- allow duplicates. Likewise the index on gpsinitials to enforce +-- uniqueness is removed. +DROP INDEX observers_gpsinitials; + +DROP FUNCTION observers_func() CASCADE; + +ALTER TABLE observers + RENAME COLUMN gpsinitials TO oldgpsinitials; + +ALTER TABLE observers + ALTER COLUMN oldgpsinitials DROP NOT NULL; + +ALTER TABLE observers + ALTER COLUMN oldgpsinitials TYPE TEXT; + +-- observers.role is a new column. +ALTER TABLE observers + ADD COLUMN role TEXT + CONSTRAINT "Role: Cannot be empty or nothing but spaces" + CHECK(trim(from role) != ''); +UPDATE observers + SET role = 'Unclassified'; + +ALTER TABLE observers + ALTER COLUMN role SET NOT NULL; + +-- +-- Update all the columns that reference observers.initials. +-- +ALTER TABLE samples + ALTER COLUMN observer TYPE VARCHAR(4); + +-- Must not have views/rules that reference the altered column. +DROP VIEW actor_actees CASCADE; +DROP VIEW actor_actees_sorted CASCADE; +DROP VIEW actor_actees_ex CASCADE; +DROP VIEW actor_actees_ex_sorted CASCADE; +DROP VIEW interact CASCADE; +DROP VIEW interact_sorted CASCADE; +ALTER TABLE interact_data + ALTER COLUMN observer TYPE VARCHAR(4); + +DROP VIEW min_maxs CASCADE; +DROP VIEW min_maxs_sorted CASCADE; +ALTER TABLE wreadings + ALTER COLUMN wrperson TYPE VARCHAR(4); + +ALTER TABLE rgsetups + ALTER COLUMN rgsperson TYPE VARCHAR(4); + +COMMIT; + +-- Then re-install the functions, triggers, and views. The former 2 +-- just in case we reference the type of a column or one of the views +-- we dropped somewhere. +------------------------------------------------------------- + hunk ./db/makeindexes 46 -CREATE UNIQUE INDEX observers_gpsinitials ON observers (gpsinitials); hunk ./db/populate_test 74 -insert into observers(initials, gpsinitials, name) - values('RSM', 'RSM', 'Rapheal'); -insert into observers(initials, gpsinitials, name) - values('SNS', 'SNS', 'Sarah'); -insert into observers(initials, gpsinitials, name) - values('JKW', 'JKW', 'Kenua'); +insert into observers(initials, oldgpsinitials, name, role) + values('RSM', 'RSM', 'Rapheal', 'Observer L1'); +insert into observers(initials, oldgpsinitials, name, role) + values('SNS', 'SNS', 'Sarah', 'Observer L1'); +insert into observers(initials, oldgpsinitials, name, role) + values('JKW', 'JKW', 'Kenua', 'Observer L1'); hunk ./db/triggers/Makefile 34 - groups statuses dcauses observers mstatuses \ + groups statuses dcauses mstatuses \ hunk ./db/triggers/destroytriggers 109 -DROP FUNCTION observers_func() CASCADE; - hunk ./doc/babase_system.xml 13149 - OBSERVERS (Field Data Collection Staff) + OBSERVERS (Data Collection Staff) hunk ./doc/babase_system.xml 13153 - of who recorded it. + of who recorded it. This table may include people who assist + the data collection process, whether in our out of the field + and whether or not their initials appear in those database + columns for which the OBSERVERS table provides a validation + vocabulary because the initials of all these people may appear + in paper or unvalidated electronic records. hunk ./doc/babase_system.xml 13161 - key columns: Initials and GPSInitials. Which key is used in - the field depends upon the data collection protocols. When - entered into Babase all GPSInitials values are translated into - their respective Initials values, so it is the Initials values - that Babase always uses to reference the individual. - - Both the Initials value and the GPSInitials value of any - one row must be unique in the entire pool of all Initials and - GPSInitials values -- the Initials value of any one row may - not match either the Initials or the GPSInitials value of a - different row. + key columns: Initials and OldGPSInititals. Which key is used + in the field depends upon the data collection protocols. When + entered into Babase all OldGPSInititals values are translated + into their respective Initials values, so it is the Initials + values that Babase always uses to reference the + individual. hunk ./doc/babase_system.xml 13208 -
- The GPSInitials Column +
+ The OldGPSInitials Column hunk ./doc/babase_system.xml 13211 - The initials used to identify the person when - recording GPS data. + The initials, or notes regarding the initials, used to + identify the person when recording GPS data. hunk ./doc/babase_system.xml 13223 - &emptytext; + This column may be NULL. + &emptytext; hunk ./doc/babase_system.xml 13258 + +
+ The Role column + + Any notes you may wish to make regarding the role the + person has filled in regards to data collection. + + This column must not be NULL. + &emptytext; +