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.8 -r1.9
--- openacs-4/packages/dotlrn/sql/oracle/dotlrn-users-create.sql	10 Jan 2002 19:30:17 -0000	1.8
+++ openacs-4/packages/dotlrn/sql/oracle/dotlrn-users-create.sql	8 Feb 2002 20:50:56 -0000	1.9
@@ -1,157 +1,77 @@
-
 --
--- The DotLRN basic system
--- copyright 2001, OpenForce, Inc.
--- distributed under the GNU GPL v2
+-- Create the dotLRN Users package
 --
--- for Oracle 8/8i. (We're guessing 9i works, too).
+-- @author <a href="mailto:yon@openforce.net">yon@openforce.net</a>
+-- @version $Id$
 --
--- ben@openforce.net
--- October 30th, 2001
--- we remember September 11th, 2001
---
 
---
--- Users of the dotLRN system
---
-
--- 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                     varchar2(200) not null
-                                constraint dlrn_user_type_un unique
+create table dotlrn_user_profile_rels (
+    rel_id                      constraint dotlrn_usr_prfl_rels_rel_id_fk
+                                references user_profile_rels (rel_id)
+                                constraint dotlrn_user_profile_rels_pk
+                                primary key,
+    id                          varchar2(100)
 );
 
-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 table dotlrn_full_user_profile_rels (
+    rel_id                      constraint dotlrn_fup_rels_rel_fk
+                                references dotlrn_user_profile_rels (rel_id)
+                                constraint dotlrn_full_user_prfl_rels_pk
+                                primary key,
+    portal_id                   constraint dotlrn_fup_rels_portal_fk
+                                references portals (portal_id)
+                                constraint dotlrn_fup_rels_portal_nn
+                                not null,
+    theme_id                    constraint dotlrn_fup_rels_theme_fk
+                                references portal_element_themes (theme_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)
+create table dotlrn_user_types (
+    type                        varchar2(100)
+                                constraint dotlrn_user_types_pk
+                                primary key,
+    pretty_name                 varchar2(200),
+    group_id                    constraint dotlrn_user_types_group_id_fk
+                                references profiled_groups (group_id)
+                                constraint dotlrn_user_types_group_id_nn
+                                not null
 );
 
--- 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
+create or replace view dotlrn_users
 as
     select acs_rels.rel_id,
+           dotlrn_user_profile_rels.id,
            registered_users.user_id,
-           first_names,
-           last_name,
-           email,
-           dotlrn_user_types.type_id,
-           type
-    from dotlrn_user_rels,
+           registered_users.first_names,
+           registered_users.last_name,
+           registered_users.email,
+           dotlrn_user_types.type
+    from dotlrn_user_profile_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;
+    and acs_rels.object_id_one = dotlrn_user_types.group_id
+    and acs_rels.rel_id = dotlrn_user_profile_rels.rel_id;
 
-create view dotlrn_full_users
+create or replace view dotlrn_full_users
 as
-    select acs_rels.rel_id,
-           registered_users.user_id,
-           first_names,
-           last_name,
-           email,
-           dotlrn_user_types.type_id,
-           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;
+    select dotlrn_users.*,
+           dotlrn_full_user_profile_rels.portal_id,
+           dotlrn_full_user_profile_rels.theme_id
+    from dotlrn_users,
+         dotlrn_full_user_profile_rels
+    where dotlrn_users.rel_id = dotlrn_full_user_profile_rels.rel_id;
 
--- The packages
+@@ dotlrn-user-profile-provider-create.sql
+@@ dotlrn-users-init.sql
+@@ dotlrn-users-package-create.sql
 
-declare
-    v_group_id integer;
-    foo integer;
-begin
-    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
-    );
+-- create administrators
+@@ dotlrn-admins-create.sql
 
-    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
-    );
+-- create professors
+@@ dotlrn-professors-create.sql
 
-    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
+-- create students
+@@ dotlrn-students-create.sql
