Index: openacs-4/packages/assessment/sql/postgresql/assessment-collected-data-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/assessment/sql/postgresql/assessment-collected-data-create.sql,v diff -u -r1.11 -r1.12 --- openacs-4/packages/assessment/sql/postgresql/assessment-collected-data-create.sql 16 Oct 2006 13:17:04 -0000 1.11 +++ openacs-4/packages/assessment/sql/postgresql/assessment-collected-data-create.sql 15 May 2007 20:14:16 -0000 1.12 @@ -11,19 +11,23 @@ constraint as_sessions_session_id_pk primary key constraint as_sessions_session_id_fk - references cr_revisions(revision_id), + references cr_revisions(revision_id) + on delete cascade, assessment_id integer constraint as_sessions_assessment_id_fk - references as_assessments(assessment_id), + references as_assessments(assessment_id) + on delete cascade, -- References a Subjects entity that we don't define in this package. -- if subjects can't be "persons" then Assessment will have to define an as_subjects table for its own use. subject_id integer constraint as_sessions_subject_id_fk - references persons(person_id), + references persons(person_id) + on delete cascade, -- references Users if someone is doing the Assessment as a proxy for the real subject staff_id integer constraint as_sessions_staff_id_fk - references users(user_id), + references users(user_id) + on delete cascade, -- when the subject should do the Assessment target_datetime timestamptz, -- when the subject initiated the Assessment @@ -51,20 +55,25 @@ constraint as_section_data_section_data_id_pk primary key constraint as_section_data_section_data_id_fk - references cr_revisions(revision_id), + references cr_revisions(revision_id) + on delete cascade, session_id integer constraint as_section_data_session_id_fk - references as_sessions(session_id), + references as_sessions(session_id) + on delete cascade, section_id integer constraint as_section_data_section_id_fk - references as_sections(section_id), + references as_sections(section_id) + on delete cascade, -- if subjects can't be "persons" then Assessment will have to define an as_subjects table for its own use. subject_id integer constraint as_section_data_subject_id_fk - references persons(person_id), + references persons(person_id) + on delete cascade, staff_id integer constraint as_section_data_staff_id_fk - references users(user_id), + references users(user_id) + on delete cascade, points integer, -- when the subject initiated the section creation_datetime timestamptz, @@ -82,24 +91,30 @@ constraint as_item_data_item_data_id_pk primary key constraint as_item_data_item_data_id_fk - references cr_revisions(revision_id), + references cr_revisions(revision_id) + on delete cascade, session_id integer constraint as_item_data_session_id_fk - references as_sessions(session_id), + references as_sessions(session_id) + on delete cascade, -- if subjects can't be "persons" then Assessment will have to define an as_subjects table for its own use subject_id integer constraint as_item_data_subject_id_fk - references persons(person_id), + references persons(person_id) + on delete cascade, -- missing foreign key staff_id integer constraint as_item_data_staff_id_fk - references users(user_id), + references users(user_id) + on delete cascade, as_item_id integer constraint as_item_data_item_id - references as_items(as_item_id), + references as_items(as_item_id) + on delete cascade, section_id integer constraint as_item_data_section_id - references as_sections(section_id), + references as_sections(section_id) + on delete cascade, is_unknown_p char(1) default 'f' constraint as_item_data_is_unknown_p_ck check (is_unknown_p in ('t','f')), @@ -113,14 +128,16 @@ -- references cr_revisions content_answer integer constraint as_item_data_content_answer_fk - references cr_revisions, + references cr_revisions + on delete cascade, -- This field stores the signed entered data signed_data varchar(500), points integer, file_id integer constraint as_item_data_file_id_fk references cr_revisions(revision_id) - -- to do: figure out how attachment answers should be supported; the Attachment package is still in need of considerable help. Can we rely on it here? + on delete cascade +-- to do: figure out how attachment answers should be supported; the Attachment package is still in need of considerable help. Can we rely on it here? ); create index as_item_data_pk2 on as_item_data (session_id, section_id, as_item_id); @@ -133,10 +150,12 @@ constraint as_session_results_result_id_pk primary key constraint as_session_results_result_id_fk - references cr_revisions(revision_id), + references cr_revisions(revision_id) + on delete cascade, target_id integer constraint as_session_results_target_id_fk - references cr_revisions(revision_id), + references cr_revisions(revision_id) + on delete cascade, points integer ); @@ -146,11 +165,13 @@ create table as_item_data_choices ( item_data_id integer constraint as_item_data_choices_data_id_fk - references as_item_data, + references as_item_data + on delete cascade, -- references as_item_choices choice_id integer constraint as_item_data_choices_choice_id_fk - references as_item_choices, + references as_item_choices + on delete cascade, constraint as_item_data_choices_pk primary key (item_data_id, choice_id) ); @@ -161,10 +182,12 @@ create table as_session_sections ( session_id integer constraint as_session_sections_session_fk - references as_sessions, + references as_sessions + on delete cascade, section_id integer constraint as_session_sections_section_fk - references as_sections, + references as_sections + on delete cascade, sort_order integer, constraint as_session_sections_pk primary key (session_id, section_id) @@ -215,10 +238,10 @@ create table as_session_item_map ( session_id integer constraint as_session_item_map_session_fk - references as_sessions, + references as_sessions on delete cascade, item_data_id integer constraint as_session_item_map_item_data_fk - references as_item_data, + references as_item_data on delete cascade, constraint as_session_item_map_pk primary key (session_id, item_data_id) );