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.7 -r1.8
--- openacs-4/packages/assessment/sql/postgresql/assessment-collected-data-create.sql	19 Jan 2005 16:49:14 -0000	1.7
+++ openacs-4/packages/assessment/sql/postgresql/assessment-collected-data-create.sql	23 Jan 2005 18:10:48 -0000	1.8
@@ -42,6 +42,9 @@
 		check (percent_score <= 100)
 );
 
+create index as_sessions_assessment_id_idx on as_sessions (assessment_id);
+create index as_sessions_subject_id_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
@@ -62,9 +65,17 @@
 	staff_id integer
 		constraint as_section_data_staff_id_fk
 		references users(user_id),
-	points integer
+	points integer,
+	-- when the subject initiated the section
+	creation_datetime timestamptz,
+	-- when the final submission produced a complete section
+	completed_datetime timestamptz
 );
 
+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_subject_id_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
@@ -109,6 +120,9 @@
 	-- 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_subject_id_idx on as_item_data (subject_id);
 
 -- here the selected choices are stored
 create table as_item_data_choices (
@@ -123,6 +137,7 @@
 	primary key (item_data_id, choice_id)
 );
 
+create unique index as_item_data_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 (
@@ -137,6 +152,8 @@
 	primary key (session_id, section_id)
 );
 
+create unique index as_session_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
@@ -153,6 +170,8 @@
 	primary key (session_id, section_id, as_item_id)
 );
 
+create unique index as_session_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
@@ -172,6 +191,8 @@
 	primary key (session_id, section_id, as_item_id, choice_id)
 );
 
+create unique index as_session_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
@@ -183,3 +204,5 @@
 	constraint as_session_item_map_pk
 	primary key (session_id, item_data_id)
 );
+
+create unique index as_session_item_map_pk2 on as_session_item_map (item_data_id, session_id);