using natural keys for user, group and class references in cs_history

[jeanatcismet]

-- ### ADDING NEW COLUMNS ### 
ALTER TABLE cs_history 
    ADD COLUMN IF NOT EXISTS usr_key TEXT,
    ADD COLUMN IF NOT EXISTS ug_key TEXT,
    ADD COLUMN IF NOT EXISTS class_key TEXT;

-- ### FILLING NEW COLUMNS ### 
--UPDATE cs_history SET usr_key = (SELECT login_name FROM cs_usr WHERE id = cs_history.usr_id) WHERE usr_key IS NULL;
--UPDATE cs_history SET ug_key = (SELECT name FROM cs_ug  WHERE id = cs_history.ug_id) WHERE ug_key IS NULL;
--UPDATE cs_history SET class_key = (SELECT table_name FROM cs_class  WHERE id = cs_history.class_id) WHERE class_key IS NULL;
UPDATE cs_history SET 
    usr_key = (SELECT login_name FROM cs_usr WHERE id = cs_history.usr_id),
    ug_key = (SELECT name FROM cs_ug  WHERE id = cs_history.ug_id),
    class_key = (SELECT table_name FROM cs_class  WHERE id = cs_history.class_id);

ALTER TABLE cs_history ALTER COLUMN usr_key SET NOT NULL;
ALTER TABLE cs_history ALTER COLUMN ug_key SET NOT NULL; -- ? anmeldung mit NULL gruppe (zb Wunda)
ALTER TABLE cs_history ALTER COLUMN class_key SET NOT NULL;

-- ### DROPPING OLD COLUMNS ### 
ALTER TABLE cs_history 
    DROP COLUMN IF EXISTS usr_id,
    DROP COLUMN IF EXISTS ug_id,
    DROP COLUMN IF EXISTS class_id;

-- ### INDEXING NEW COLUMNS ### 
CREATE INDEX resident_usr_key_idx ON cs_history(usr_key);
CREATE INDEX resident_ug_key_idx ON cs_history(ug_key);
CREATE INDEX resident_class_key_idx ON cs_history(class_key);