Index: openacs-4/packages/imsld/sql/postgresql/imsld-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/imsld/sql/postgresql/imsld-create.sql,v
diff -u -r1.1 -r1.2
--- openacs-4/packages/imsld/sql/postgresql/imsld-create.sql	5 Aug 2005 15:25:33 -0000	1.1
+++ openacs-4/packages/imsld/sql/postgresql/imsld-create.sql	13 Sep 2005 13:15:02 -0000	1.2
@@ -12,16 +12,14 @@
                             on delete cascade
                             constraint imsld_lo_id_pk   
                             primary key, 
+    identifier              varchar(100)
+                            not null,
     class                   varchar(4000),                  
-    environment_id          integer    
-                            constraint imsld_lo_envid_fk    
-                            references cr_items(item_id), --imsld_environments
     is_visible_p            char(1) 
                             check (is_visible_p in ('t','f')) 
                             default 't', 
     type                    varchar(100),
-    schema_version          varchar(100),
-    parameters               varchar(4000)
+    parameters              varchar(4000)
 );
 
 create index imsld_learning_o_env_id_idx on imsld_learning_objects(environment_id);
@@ -60,7 +58,13 @@
     sequence_used_p         char(1)
                             constraint imsld_seq_ck
                             check (sequence_used_p in ('t','f'))
-                            default 'f'
+                            default 'f',
+    learning_objective_id   integer
+                            constraint imsld_lobj_id_fk
+                            references cr_items,    --imsld_learning_objectives
+    prerequisite_id         integer
+                            constraint imsld_prereq_id_fk
+                            references cr_items     --imsld_prerequisites
 );
 
 comment on table imsld_imslds is '
@@ -82,14 +86,12 @@
                         on delete cascade
                         constraint imsld_leo_pk
                         primary key,
-    imsld_id            integer
-                        constraint ismld_leo_imsldid_fk
-                        references cr_items     --imsld_imslds
+    pretty_title        varchar(200)
 );
 
 comment on table imsld_learning_objectives is '
 This table holds the learning objectives of the IMS-LD. 
-Technically it is just a mapping table between items and the imsld_id, but this table was created to provide simplicity and clarification in the data model';
+Technically it is just a mapping table between items and the imsld_id or learning_activity_id, but this table was created to provide simplicity and clarification in the data model';
 
 create table imsld_prerequisites (
     prerequisite_id     integer
@@ -98,14 +100,12 @@
                         on delete cascade
                         constraint imsld_prereq_pk
                         primary key,
-    imsld_id            integer
-                        constraint ismld_prereq_imsldid_fk
-                        references cr_items     --imsld_imslds
+    pretty_title        varchar(200)
 );
 
 comment on table imsld_prerequisites is '
 This table holds the prerequisites of the IMS-LD. 
-Technically it is just a mapping table between items and the imsld_id, but this table was created to provide simplicity and clarification in the data model';
+Technically it is just a mapping table between items and the imsld_id or learning_activity_id, but this table was created to provide simplicity and clarification in the data model';
 
 create table imsld_items (
     imsld_item_id   integer
@@ -114,6 +114,9 @@
                     on delete cascade
                     constraint imsld_items_id_pk
                     primary key,
+    parent_item_id  integer
+                    constraint imsld_items_pid_fk
+                    references cr_items,    --imsld_items
     identifier      varchar(100),
     identifierref   varchar(100),
     is_visible_p    char(1)
@@ -125,6 +128,9 @@
 comment on table imsld_items is '
 This table holds the imsld items of the unit of learning';
 
+comment on column imsld_items.parent_item_id is '
+In case it is a nested item.';
+
 comment on column imsld_items.identifier is '
 Unique identifier of the item in the unit of learning';
 
@@ -160,8 +166,13 @@
                     on delete cascade
                     constraint imsld_roles_id_pk
                     primary key,
+    component_id    integer
+                    constraint imsld_roles_comp_id_fk
+                    references cr_items     --imsld_components
+                    not null,
     identifier      varchar(100),
-    role_type       varchar(100)
+    role_type       varchar(7)
+                    check (role_type in ('learner','staff'))
                     not null,
     parent_role_id  integer
                     constraint imsld_roles_proleid_fk
@@ -170,9 +181,11 @@
                     check (create_new_p in ('t','f'))
                     default 't',
     match_persons_p char(1)
-                    check (match_persons_p in ('t','f')),
+                    check (match_persons_p in ('t','f'))
+                    default 'f',
     max_persons     integer,
-    min_persons     integer
+    min_persons     integer,
+    href            varchar(2000)
 );
 
 create index imsld_roles_parent_id_idx on imsld_roles(parent_role_id);
@@ -194,6 +207,20 @@
 This attribute is used when there are several sub roles (e.g. chair, secretary, member). Persons can be matched exclusively to the sub roles, meaning that a person, who has the role of chair, may not be bound to one of the other roles at the same time. When it is not exclusive, persons may be bound to more than one sub role (this is the default situation).
 True means exclusively-in-roles and false means not-exlusively.';
 
+create table imsld_activity_description (
+    description_id      integer
+                        constraint imsld_act_desc_fk
+                        references cr_revisions
+                        on delete cascade
+                        constraint imsld_act_desc_pk
+                        primary key,
+    pretty_title        varchar(200)
+);
+
+comment on table iimsld_activity_description is '
+This table holds the descrition of a learning activity.
+Technically it is just a mapping table between items and the learning activity, but this table was created to provide simplicity and clarification in the data model';
+
 create table imsld_learning_activities (
     activity_id             integer
                             constraint imsld_la_id_fk
@@ -217,7 +244,13 @@
                             references cr_items,    --imsld_time_limits
     on_completion_id        integer
                             constraint imdls_la_oncompid_fk
-                            references cr_items     --imsld_on_completion
+                            references cr_items,    --imsld_on_completion
+    learning_objective_id   integer
+                            constraint imsld_la_lobjid_fk
+                            references cr_items,    --imsld_learning_objectives
+    prerequisite_id         integer
+                            constraint imsld_la_prereqid_fk
+                            references cr_items     --imsld_prerequisites
 );
 
 create index imsld_la_comp_id_idx on imsld_learning_activities(component_id);
@@ -319,7 +352,10 @@
                         constraint imsld_env_compid_fk
                         references cr_items     --imsld_components
                         not null,
-    identifier          varchar(100)
+    identifier          varchar(100),
+    learning_object_id  integer
+                        constraint imsld_env_loid_fk
+                        references cr_items,    --imsld_learning_objects
 );
 
 create index imsld_envs_comp_id_idx on imsld_environments(component_id);
@@ -329,33 +365,61 @@
 The learning objects and services are mapped to this table through those tables 
 and the nested environments are mapped through a mapping table.';
 
+create table imsld_services (
+    service_id      integer
+                    constraint imsld_serv_fk
+                    references cr_revisions
+                    on delete cascade
+                    constraint imsld_serv_pk
+                    primary key,
+    environment_id  integer
+                    constraint imsld_serv_env_fk
+                    references cr_items     --imsld_environments
+                    not null,
+    identifier      varchar(100),
+    class           varchar(4000),
+    is_visible_p    char(1) 
+                    check (is_visible_p in ('t','f')) 
+                    default 't',
+    parameters      varchar(4000),
+    service_type    varchar(10)
+                    check (service_type in ('send-mail','conference'))
+);
+
+create index imsld_serv_env_id_idx on imsld_services(environment_id);
+
+comment on table imsld_services is '
+This table stores all the services that are found in the IMS-LD.
+The services are supposed to use the other .LRN packages (because that is how
+the service is provided) but we also hold the information in this table.'
+
+comment on column imsld_services.is_visible_p is '
+Initial visibility attribute';
+
+comment on column imsld_services.service_type is '
+Service type. Currently only send-mail and conference service types are supported
+and their respective information is sotred in the tables imsld_send_mail_services and imsld_conference_services, respectively.';
+
 create table imsld_send_mail_services (
     mail_id         integer 
                     constraint imsld_emailserv_fk 
                     references cr_revisions 
                     on delete cascade
                     constraint imsld_emailserv_pk 
                     primary key, 
-    environment_id  integer
-                    constraint imsld_emailsevr_env_fk
-                    references cr_items     --imsld_environments
+    service_id      integer
+                    constraint imsld_emailsevr_service_fk
+                    references cr_items     --imsld_services
                     not null,
-    recipients      char(11)
+    recipients      varchar(11)
                     check (recipients in ('all-in-role','selection')),
-    is_visible_p    char(1) 
-                    check (is_visible_p in ('t','f')) 
-                    default 't',
-    parameters      varchar(4000)
 );
 
-create index imsld_send_m_env_id_idx on imsld_send_mail_services(environment_id);
+create index imsld_send_m_serv_id_idx on imsld_send_mail_services(service_id);
 
 comment on column imsld_send_mail_services.recipients is '
 Fixed choice: ''all-in-role'' or ''selection''. With the first choice, the user agent only allows messages to be sent to the role, indicating that all persons in the role get the message. With the second choice, the user agent allows a user to select one or more individuals within the specified role to send the message to';
 
-comment on column imsld_send_mail_services.is_visible_p is '
-Initial visibility attribute';
-
 create table imsld_send_mail_data (
     data_id         integer  
                     constraint imsld_semaildata_fk  
@@ -384,30 +448,22 @@
                         on delete cascade
                         constraint imsld_confs_pk   
                         primary key,   
-    environment_id      integer 
-                        constraint imsld_confs_env_fk 
-                        references cr_items     --imsld_environments
-                        not null, 
+    service_id          integer
+                        constraint imsld_emailsevr_service_fk
+                        references cr_items     --imsld_services
+                        not null,
     conference_type     char(12)
                         check (conference_type in ('synchronous','asynchronous','announcement')),
-    is_visible_p        char(1)  
-                        check (is_visible_p in ('t','f'))  
-                        default 't', 
     imsld_item_id       integer
                         constraint imsld_confs_item_itemid_fk
                         references cr_items,    --imsld_items
-    manager_id          integer
-                        constraint imsld_confs_manager_fk
-                        references cr_items,    --imsld_roles
     moderator_id        integer 
                         constraint imsld_confs_moderator_fk 
-                        references cr_items,    --imsld_roles
-    parameters          varchar(4000)
+                        references cr_items     --imsld_roles
 );
 
-create index imsld_confs_env_id on imsld_conference_services(environment_id);
+create index imsld_confs_serv_id on imsld_conference_services(service_id);
 create index imsld_confs_item_id on imsld_conference_services(imsld_item_id);
-create index imsld_confs_manag_id on imsld_conference_services(manager_id);
 create index imsld_confs_moder_id on imsld_conference_services(moderator_id);
 
 comment on table imsld_conference_services is '
@@ -575,18 +631,14 @@
                             on delete cascade
                             constraint imsld_oncomp_id_pk  
                             primary key, 
-    feedback_id             integer             -- a feedback is an imsld_item
-                            constraint imsld_oncomp_feedbid_fk   
-                            references cr_items(item_id)
-                            not null
+    feedback_title          varchar(200)
 );
 
 create index imsld_oncomp_feedb_id_idx on imsld_on_completion(feedback_id);
 
-comment on table imsld_on_completion is'
-The underlying item elements point to a resource (of type webcontent or imsldcontent), where the feedback description can be found. After completion (of the component pointing to this row) this text becomes visible.';
+comment on table imsld_on_completion is '
+The underlying item elements point to a resource (of type webcontent or imsldcontent), where the feedback description can be found. After completion (of the component pointing to this row) this text becomes visible.
 
-comment on column imsld_on_completion.feedback_id is '
-Reference to the item that holds the feedback.';
+Feedback are items that are mapped to this table with the imsld_feedback_rel.';
 
 \i imsld-cp-create.sql
\ No newline at end of file
