Index: openacs-4/packages/dotlrn/sql/oracle/dotlrn-users-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn/sql/oracle/Attic/dotlrn-users-create.sql,v
diff -u -r1.5 -r1.6
--- openacs-4/packages/dotlrn/sql/oracle/dotlrn-users-create.sql	27 Nov 2001 23:16:31 -0000	1.5
+++ openacs-4/packages/dotlrn/sql/oracle/dotlrn-users-create.sql	1 Dec 2001 23:16:27 -0000	1.6
@@ -15,35 +15,118 @@
 -- Users of the dotLRN system
 --
 
-create table dotlrn_users (
-       user_id		  integer not null
-			  constraint dlrn_user_id_fk
-			  references users(user_id)
-			  constraint dlrn_user_id_pk
-			  primary key,
-       role		  varchar(100) default 'student' not null check (role in ('student','guest','professor','admin')),
-       theme_id		  references portal_element_themes(theme_id),
-       page_id		  integer 
-			  constraint dlrn_user_page_id_fk
-			  references portals(portal_id)
+-- not an object, there's really no use for it
+-- this is just so other, non-university systems can set up their own user types
+create table dotlrn_user_types (
+       type_id		       integer not null
+			       constraint dlrn_user_type_pk primary key,
+       type		       varchar(200) not null
+			       constraint dlrn_user_type_un unique
 );
 
+create table dotlrn_user_rels (
+       rel_id		      integer not null
+			      constraint dlrn_user_rel_fk references membership_rels(rel_id)
+			      constraint dlrn_user_rel_pk primary key,
+       type_id		      integer not null
+			      constraint dlrn_user_rel_type_fk references dotlrn_user_types(type_id)
+);
 
-create view dotlrn_users_full as select registered_users.user_id, first_names, last_name, email, role, page_id from dotlrn_users, registered_users where dotlrn_users.user_id= registered_users.user_id;
+create table dotlrn_full_user_rels (
+       rel_id			   integer not null
+				   constraint dlrn_full_user_rel_fk references dotlrn_user_rels(rel_id)
+				   constraint dlrn_full_user_rel_pk primary key,
+       theme_id			   integer
+				   constraint dlrn_full_user_theme_fk references portal_element_themes(theme_id),
+       portal_id		   integer not null
+				   constraint dlrn_full_user_portal_fk references portals(portal_id)
+);
 
+-- the user group
+declare
+	foo integer;
+begin
+	foo:= acs_group.new (
+		group_id => NULL,
+		object_type => 'group',
+		creation_date => sysdate,
+		group_name => 'dotLRN Users'
+	);
+end;
+/
+show errors
 
+
+create or replace function dotlrn_get_group_id return integer is
+        p_group_id	   integer;
+begin
+	select max(group_id) into p_group_id from groups where group_name= 'dotLRN Users';
+
+	return p_group_id;
+end;
+/
+show errors
+
+create view dotlrn_users as select acs_rels.rel_id, registered_users.user_id, first_names, last_name, email, type
+from dotlrn_user_rels, acs_rels, registered_users, dotlrn_user_types where 
+acs_rels.object_id_two = registered_users.user_id and
+acs_rels.object_id_one = dotlrn_get_group_id() and
+acs_rels.rel_id = dotlrn_user_rels.rel_id and
+dotlrn_user_rels.type_id= dotlrn_user_types.type_id;
+
+create view dotlrn_full_users as select acs_rels.rel_id, registered_users.user_id, first_names, last_name, email, type, portal_id, theme_id
+from dotlrn_user_rels, dotlrn_full_user_rels, acs_rels, registered_users, dotlrn_user_types where 
+acs_rels.object_id_two = registered_users.user_id and
+acs_rels.object_id_one = dotlrn_get_group_id() and
+acs_rels.rel_id = dotlrn_user_rels.rel_id and
+dotlrn_user_rels.rel_id = dotlrn_full_user_rels.rel_id and
+dotlrn_user_types.type_id = dotlrn_user_rels.type_id;
+
+-- The packages
+
 declare
+	v_group_id integer;
+	foo integer;
 begin
-   acs_object_type.create_type (
-	    supertype => 'user',
-	    object_type => 'dotlrn_user',
-	    pretty_name => 'dotLRN User',
-	    pretty_plural => 'dotLRN Users',
-	    table_name => 'dotlrn_users',
-	    id_column => 'user_id',
-	    package_name => 'dotlrn_user',
-	    name_method => 'acs_object.name'
+   acs_rel_type.create_type (
+	rel_type => 'dotlrn_user_rel',
+	supertype => 'membership_rel',
+	pretty_name => 'dotLRN User Membership',
+	pretty_plural => 'dotLRN User Memberships',
+	package_name => 'dotlrn_user_rel',
+	table_name => 'dotlrn_user_rels',
+	id_column => 'rel_id',
+	object_type_one => 'group', role_one => NULL, min_n_rels_one => 0, max_n_rels_one => NULL,
+	object_type_two => 'user', role_two => NULL, min_n_rels_two => 0, max_n_rels_two => 1
    );
+
+   acs_rel_type.create_type (
+	rel_type => 'dotlrn_full_user_rel',
+	supertype => 'dotlrn_user_rel',
+	pretty_name => 'dotLRN Full User Membership',
+	pretty_plural => 'dotLRN Full User Memberships',
+	package_name => 'dotlrn_full_user_rel',
+	table_name => 'dotlrn_full_user_rels',
+	id_column => 'rel_id',
+	object_type_one => 'group', role_one => NULL, min_n_rels_one => 0, max_n_rels_one => NULL,
+	object_type_two => 'user', role_two => NULL, min_n_rels_two => 0, max_n_rels_two => 1
+   );
+
+   v_group_id:= dotlrn_get_group_id();
+
+   -- Now we create the rel segments!
+   foo:= rel_segment.new (
+	segment_name => 'dotLRN Users',
+	group_id => v_group_id,
+	rel_type => 'dotlrn_user_rel'
+   );
+
+   foo:= rel_segment.new (
+	segment_name => 'dotLRN Full Access Users',
+	group_id => v_group_id,
+	rel_type => 'dotlrn_full_user_rel'
+   );
+
 end;
 /
 show errors