Index: openacs-4/packages/assessment/sql/oracle/assessment-collected-data-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/assessment/sql/oracle/assessment-collected-data-create.sql,v
diff -u -r1.2 -r1.3
--- openacs-4/packages/assessment/sql/oracle/assessment-collected-data-create.sql	6 Dec 2004 12:06:56 -0000	1.2
+++ openacs-4/packages/assessment/sql/oracle/assessment-collected-data-create.sql	30 Jan 2005 14:16:14 -0000	1.3
@@ -42,6 +42,9 @@
 		check (percent_score <= 100)
 );
 
+create index as_sessions_ass_idx on as_sessions (assessment_id);
+create index as_sessions_subj_idx on as_sessions (subject_id);
+
 --Assessment Section Data: tracks the state of each Section in the Assessment.
 create table as_section_data (
 	section_data_id integer
@@ -61,9 +64,18 @@
 		references persons(person_id),
 	staff_id integer
 		constraint as_section_data_staff_id_fk
-		references users(user_id)
+		references users(user_id),
+	points integer,
+	-- when the subject initiated the section
+	creation_datetime date,
+	-- when the final submission produced a complete section
+	completed_datetime date
 );
 
+create unique index as_section_data_pk2 on as_section_data (session_id, section_id);
+create unique index as_section_data_pk3 on as_section_data (section_id, session_id);
+create index as_section_data_subj_idx on as_section_data (subject_id);
+
 -- Assessment Item Data: is the "long skinny table" where all the primary data go
 create table as_item_data (
 	item_data_id integer
@@ -85,13 +97,12 @@
 	as_item_id integer
 		constraint as_item_data_item_id
 		references as_items(as_item_id),
+ 	section_id integer
+		constraint as_item_data_section_id
+		references as_sections(section_id),
 	is_unknown_p char(1) default 'f'
 		constraint as_item_data_unknown_p_ck
 		check (is_unknown_p in ('t','f')),
-	-- references as_item_choices
-	choice_id_answer integer
-		constraint as_item_data_choice_id_fk
-		references as_item_choices(choice_id),
 	boolean_answer	char(1)
 			constraint as_item_data_bool_ck
 			check (boolean_answer in ('t','f')),
@@ -106,9 +117,94 @@
 		constraint as_item_data_content_fk
 		references cr_revisions,
 	-- This field stores the signed entered data
-	signed_data varchar(500)
-	--percent_score integer
-	-- constraint as_item_data_percent_ck
-	--check (percent_score <=100)
+	signed_data varchar(500),
+	points integer
 	-- 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);
+create index as_item_data_pk3 on as_item_data (as_item_id, section_id, session_id);
+create index as_item_data_subj_idx on as_item_data (subject_id);
+
+-- here the selected choices are stored
+create table as_item_data_choices (
+	item_data_id	integer
+			constraint as_idata_cho_data_id_fk
+			references as_item_data,
+	-- references as_item_choices
+	choice_id	integer
+			constraint as_idata_cho_choice_id_fk
+			references as_item_choices,
+	constraint as_idata_choices_pk
+	primary key (item_data_id, choice_id)
+);
+
+create unique index as_idata_choices_pk2 on as_item_data_choices (choice_id, item_data_id);
+
+-- here the order of the displayed sections is stored per session
+create table as_session_sections (
+	session_id	integer
+			constraint as_sess_sect_session_fk
+			references as_sessions,
+	section_id	integer
+			constraint as_sess_sect_section_fk
+			references as_sections,
+	sort_order	integer,
+	constraint as_sess_sections_pk
+	primary key (session_id, section_id)
+);
+
+create unique index as_sess_sections_pk2 on as_session_sections (section_id, session_id);
+
+-- here the order of the displayed items is stored per session
+create table as_session_items (
+	session_id	integer
+			constraint as_sess_items_session_fk
+			references as_sessions,
+	section_id	integer
+			constraint as_sess_items_section_fk
+			references as_sections,
+	as_item_id	integer
+			constraint as_sess_items_item_fk
+			references as_items,
+	sort_order	integer,
+	constraint as_sess_items_pk
+	primary key (session_id, section_id, as_item_id)
+);
+
+create unique index as_sess_items_pk2 on as_session_items (as_item_id, section_id, session_id);
+
+-- here the order of the displayed item choices is stored per session
+create table as_session_choices (
+	session_id	integer
+			constraint as_sess_cho_session_fk
+			references as_sessions,
+	section_id	integer
+			constraint as_sess_cho_section_fk
+			references as_sections,
+	as_item_id	integer
+			constraint as_sess_cho_item_fk
+			references as_items,
+	choice_id	integer
+			constraint as_sess_cho_choice_fk
+			references as_item_choices,
+	sort_order	integer,
+	constraint as_sess_choices_pk
+	primary key (session_id, section_id, as_item_id, choice_id)
+);
+
+create unique index as_sess_choices_pk2 on as_session_choices (choice_id, as_item_id, section_id, session_id);
+
+-- here all references to answers of a session are stored
+create table as_session_item_map (
+	session_id	integer
+			constraint as_sess_imap_session_fk
+			references as_sessions,
+	item_data_id	integer
+			constraint as_sess_imap_item_data_fk
+			references as_item_data,
+	constraint as_sess_imap_pk
+	primary key (session_id, item_data_id)
+);
+
+create unique index as_sess_imap_pk2 on as_session_item_map (item_data_id, session_id);