Index: openacs-4/packages/curriculum-central/sql/postgresql/uos-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/curriculum-central/sql/postgresql/uos-create.sql,v
diff -u -r1.13 -r1.14
--- openacs-4/packages/curriculum-central/sql/postgresql/uos-create.sql	22 Jan 2006 08:43:15 -0000	1.13
+++ openacs-4/packages/curriculum-central/sql/postgresql/uos-create.sql	2 Feb 2006 11:45:49 -0000	1.14
@@ -44,12 +44,7 @@
 	parent_id		integer,
 	live_revision_id	integer,
 	-- denormalised from cc_uos_revisions	
-	uos_code 		varchar(256)
-				constraint cc_uos_uos_code_nn not null
-				constraint cc_uos_uos_code_un unique,
-	uos_name 		varchar(256)
-				constraint cc_uos_uos_name_nn not null
-				constraint cc_uos_uos_name_un unique,
+	uos_name_id 		integer,    -- references cc_uos_name(name_id)
 	unit_coordinator_id	integer
 				constraint cc_uos_coordinator_id_fk
 				references users(user_id)
@@ -66,18 +61,28 @@
 				constraint cc_uos_rev_uos_id_fk
 				references cr_revisions(revision_id)
 				on delete cascade,
-	uos_code 		varchar(256)
-				constraint cc_uos_rev_uos_code_nn not null,
-	uos_name 		varchar(256)
-				constraint cc_uos_rev_uos_name_nn not null,
+	uos_name_id 		integer
+				constraint cc_uos_rev_uos_name_id_fk
+				references cc_uos_name(name_id)
+				constraint cc_uos_rev_uos_name_id_nn not null,
 	credit_value		integer,
+	department_id		integer
+				constraint cc_stream_department_id_fk
+				references cc_department(department_id)
+				constraint cc_stream_department_id_nn
+				not null,
 	unit_coordinator_id	integer
 				constraint cc_uos_rev_coordinator_id_fk
 				references users(user_id)
 				constraint cc_uos_rev_coordinator_id_nn
                         	not null,
-	activity_log		text,
-	activity_log_format	varchar(256)
+	session_ids		varchar(256),
+	prerequisite_ids	varchar(256),
+	assumed_knowledge_ids	varchar(256),
+	corequisite_ids		varchar(256),
+	prohibition_ids		varchar(256),
+	no_longer_offered_ids	varchar(256),
+	activity_log		text
 );
 
 -- Create the UoS revision content type.
@@ -92,16 +97,21 @@
 );
 
 
-select define_function_args('cc_uos__new', 'uos_id,uos_code,uos_name,unit_coordinator_id,credit_value,activity_log,activity_log_format,creation_user,creation_ip,context_id,item_subtype;cc_uos,content_type;cc_uos_revision,object_type,package_id');
+select define_function_args('cc_uos__new', 'uos_id,uos_name_id,unit_coordinator_id,credit_value,department_id,session_ids,prerequisite_ids,assumed_knowledge_ids,corequisite_ids,prohibition_ids,no_longer_offered_ids,activity_log,creation_user,creation_ip,context_id,item_subtype;cc_uos,content_type;cc_uos_revision,object_type,package_id');
 
 create function cc_uos__new(
 	integer,	-- uos_id
-	varchar,	-- uos_code
-	varchar,	-- uos_name
+	integer,	-- uos_name_id
 	integer,	-- unit_coordinator_id
 	integer,	-- credit_value
+	integer,	-- department_id
+	varchar,	-- session_ids
+	varchar,	-- prerequisite_ids
+	varchar,	-- assumed_knowledge_ids
+	varchar,	-- corequisite_ids
+	varchar,	-- prohibition_ids
+	varchar,	-- no_longer_offered_ids
 	text,		-- activity_log
-	varchar,	-- activity_log_format
 	integer,	-- creation_user
 	varchar,	-- creation_ip
 	integer,	-- context_id
@@ -113,32 +123,41 @@
 declare
 
 	p_uos_id			alias for $1;
-	p_uos_code			alias for $2;
-	p_uos_name			alias for $3;
-	p_unit_coordinator_id		alias for $4;
-	p_credit_value			alias for $5;
-	p_activity_log			alias for $6;
-	p_activity_log_format		alias for $7;
-	p_creation_user			alias for $8;
-	p_creation_ip			alias for $9;
-	p_context_id			alias for $10;
-	p_item_subtype			alias for $11;
-	p_content_type			alias for $12;
-	p_object_type			alias for $13;
-	p_package_id			alias for $14;
+	p_uos_name_id			alias for $2;
+	p_unit_coordinator_id		alias for $3;
+	p_credit_value			alias for $4;
+	p_department_id			alias for $5;
+	p_session_ids			alias for $6;
+	p_prerequisite_ids		alias for $7;
+	p_assumed_knowledge_ids		alias for $8;
+	p_corequisite_ids		alias for $9;
+	p_prohibition_ids		alias for $10;
+	p_no_longer_offered_ids		alias for $11;
+	p_activity_log			alias for $12;
+	p_creation_user			alias for $13;
+	p_creation_ip			alias for $14;
+	p_context_id			alias for $15;
+	p_item_subtype			alias for $16;
+	p_content_type			alias for $17;
+	p_object_type			alias for $18;
+	p_package_id			alias for $19;
 
 	v_uos_id			cc_uos.uos_id%TYPE;
 	v_folder_id			integer;
 	v_revision_id			integer;
+	v_uos_name			varchar;
 begin
 	-- get the content folder for this instance
 	select folder_id into v_folder_id
 	    from cc_curriculum
 	    where curriculum_id = p_package_id;
 
+	select uos_name into v_uos_name from cc_uos_name
+	    where name_id = p_uos_name_id;
+
 	-- create the content item
 	v_uos_id := content_item__new (
-			p_uos_name,		-- name
+			v_uos_name,		-- name
 			v_folder_Id,		-- parent_id
 			p_uos_id,		-- item_id
 			null,			-- locale
@@ -158,20 +177,25 @@
 
 	-- create the item type row
 	insert into cc_uos (uos_id, package_id, parent_id,
-	    uos_code, uos_name, unit_coordinator_id)
-	VALUES (v_uos_id, p_package_id, v_folder_id, p_uos_code,
-            p_uos_name, p_unit_coordinator_id);
+	    uos_name_id, unit_coordinator_id)
+	VALUES (v_uos_id, p_package_id, v_folder_id,
+            p_uos_name_id, p_unit_coordinator_id);
 
 	-- create the initial revision
 	v_revision_id := cc_uos_revision__new (
 		null,				-- uos_revision_id
 		v_uos_id,			-- uos_id
-		p_uos_code,			-- uos_code
-		p_uos_name,			-- uos_name
+		p_uos_name_id,			-- uos_name_id
 		p_credit_value,			-- credit_value
+		p_department_id,		-- department_id
 		p_unit_coordinator_id,		-- unit_coordinator_id
+		p_session_ids,			-- session_ids
+		p_prerequisite_ids,		-- requisite_ids
+		p_assumed_knowledge_ids,	-- assumed_knowledge_ids
+		p_corequisite_ids,		-- corequisite_ids
+		p_prohibition_ids,		-- prohibition_ids
+		p_no_longer_offered_ids,	-- no_longer_offered_ids
 		p_activity_log,			-- activity_log
-		p_activity_log_format,		-- activity_log_format
 		now(),				-- creation_date
 		p_creation_user,		-- creation_user
 		p_creation_ip			-- creation_ip
@@ -223,13 +247,16 @@
 create function cc_uos__name (integer)
 returns varchar as '
 declare
-    p_uos_id      alias for $1;
-    v_uos_name    cc_uos.uos_name%TYPE;
+    p_uos_id      	alias for $1;
+    v_uos_name    	varchar;
+    v_uos_name_id 	cc_uos.uos_name_id%TYPE;
 begin
-	select uos_name into v_uos_name
-		from cc_uos
+	select uos_name_id into v_uos_name_id from cc_uos
 		where uos_id = p_uos_id;
 
+	select uos_name into v_uos_name from cc_uos_name
+		where name_id = v_uos_name_id;
+
     return v_uos_name;
 end;
 ' language 'plpgsql';
@@ -238,12 +265,17 @@
 create or replace function cc_uos_revision__new (
 	integer,			-- uos_revision_id
 	integer,			-- uos_id
-	varchar,			-- uos_code
-	varchar,			-- uos_name
+	integer,			-- uos_name_id
 	integer,			-- credit_value
+	integer,			-- department_id
 	integer,			-- unit_coordinator_id
+	varchar,			-- session_ids
+	varchar,			-- prerequisite_ids
+	varchar,			-- assumed_knowledge_ids
+	varchar,			-- corequisite_ids
+	varchar,			-- prohibition_ids
+	varchar,			-- no_longer_offered_ids
 	text,				-- activity_log
-	varchar,			-- activity_log_format
 	timestamptz,			-- creation_date
 	integer,			-- creation_user
 	varchar				-- creation_ip
@@ -252,21 +284,30 @@
 declare
 	p_uos_revision_id		alias for $1;
 	p_uos_id			alias for $2;
-	p_uos_code			alias for $3;
-	p_uos_name			alias for $4;
-	p_credit_value			alias for $5;
+	p_uos_name_id			alias for $3;
+	p_credit_value			alias for $4;
+	p_department_id			alias for $5;
 	p_unit_coordinator_id		alias for $6;
-	p_activity_log			alias for $7;
-	p_activity_log_format		alias for $8;
-	p_creation_date			alias for $9;
-	p_creation_user			alias for $10;
-	p_creation_ip			alias for $11;
+	p_session_ids			alias for $7;
+	p_prerequisite_ids		alias for $8;
+	p_assumed_knowledge_ids		alias for $9;
+	p_corequisite_ids		alias for $10;
+	p_prohibition_ids		alias for $11;
+	p_no_longer_offered_ids		alias for $12;
+	p_activity_log			alias for $13;
+	p_creation_date			alias for $14;
+	p_creation_user			alias for $15;
+	p_creation_ip			alias for $16;
 
 	v_revision_id			integer;
+	v_uos_name			varchar;
 begin
+	select uos_name into v_uos_name from cc_uos_name
+		where name_id = p_uos_name_id;
+
 	-- create the initial revision
 	v_revision_id := content_revision__new (
-		p_uos_name,		-- title
+		v_uos_name,		-- title
 		null,			-- description
 		current_timestamp,	-- publish_date
 		null,			-- mime_type
@@ -280,14 +321,33 @@
 	);
 
 	-- insert into the uos-specific revision table
-	insert into cc_uos_revisions
-		(uos_revision_id, uos_code, uos_name, credit_value,
-		unit_coordinator_id, activity_log,
-		activity_log_format)
-	values
-		(v_revision_id, p_uos_code, p_uos_name, p_credit_value,
-		p_unit_coordinator_id, p_activity_log,
-		p_activity_log_format);
+	INSERT INTO cc_uos_revisions (
+		uos_revision_id,
+		uos_name_id,
+		credit_value,
+		department_id,
+		unit_coordinator_id,
+		session_ids,
+		prerequisite_ids,
+		assumed_knowledge_ids,
+		corequisite_ids,
+		prohibition_ids,
+		no_longer_offered_ids,
+		activity_log
+	) VALUES (
+		v_revision_id,
+		p_uos_name_id,
+		p_credit_value,
+		p_department_id,
+		p_unit_coordinator_id,
+		p_session_ids,
+		p_prerequisite_ids,
+		p_assumed_knowledge_ids,
+		p_corequisite_ids,
+		p_prohibition_ids,
+		p_no_longer_offered_ids,
+		p_activity_log
+	);
 
 	return v_revision_id;
 end;
@@ -324,8 +384,8 @@
 -- UoS Schedule
 \i uos-schedule-create.sql
 
--- Semester
-\i semester-create.sql
+-- Session
+\i session-create.sql
 
 -- Year: 1st Year, 2nd Year, 3rd Year or Freshman, Sophomore, etc.
 \i year-create.sql