2 -- Add a group_id column to relate docs to a group
4 ALTER TABLE doc_data ADD COLUMN group_id INT;
5 UPDATE doc_data SET group_id=(SELECT group_id FROM doc_groups WHERE doc_group=doc_data.doc_group);
6 UPDATE doc_data SET stateid=4 WHERE stateid=100;
8 -- Add fkey constraints
10 ALTER TABLE doc_data ADD CONSTRAINT docdata_groupid
11 FOREIGN KEY (group_id) REFERENCES groups(group_id) ON DELETE CASCADE;
12 ALTER TABLE doc_data ADD CONSTRAINT docdata_docgroupid
13 FOREIGN KEY (doc_group) REFERENCES doc_groups(doc_group);
14 ALTER TABLE doc_data ADD CONSTRAINT docdata_stateid
15 FOREIGN KEY (stateid) REFERENCES doc_states(stateid);
16 ALTER TABLE doc_groups ADD CONSTRAINT docgroups_groupid
17 FOREIGN KEY (group_id) REFERENCES groups(group_id) ON DELETE CASCADE;
19 -- Re-use old columns in the groups table
21 ALTER TABLE groups RENAME COLUMN new_task_address TO new_doc_address;
22 ALTER TABLE groups RENAME COLUMN send_all_tasks TO send_all_docs;
24 UPDATE groups SET new_doc_address='',send_all_docs='0';
28 -- Create a convenience view for selecting from docman
30 CREATE VIEW docdata_vw AS
31 SELECT users.user_name,users.realname,users.email,
32 d.group_id,d.docid,d.stateid,d.title,d.updatedate,d.createdate,d.created_by,
33 d.doc_group,d.description,d.language_id,d.filename,d.filetype,
34 doc_states.name AS state_name,
35 doc_groups.groupname AS group_name,
36 sl.name as language_name
38 NATURAL JOIN doc_states
39 NATURAL JOIN doc_groups
40 JOIN supported_languages sl ON (sl.language_id=d.language_id)
41 JOIN users ON (users.user_id=d.created_by);
44 -- NEW VIEW FOR TRACKER
46 CREATE VIEW artifact_group_list_vw AS
47 SELECT agl.*,aca.count,aca.open_count
48 FROM artifact_group_list agl
49 LEFT JOIN artifact_counts_agg aca USING (group_artifact_id);