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.2 -r1.3 --- openacs-4/packages/imsld/sql/postgresql/imsld-create.sql 13 Sep 2005 13:15:02 -0000 1.2 +++ openacs-4/packages/imsld/sql/postgresql/imsld-create.sql 19 Sep 2005 17:20:14 -0000 1.3 @@ -22,8 +22,6 @@ parameters varchar(4000) ); -create index imsld_learning_o_env_id_idx on imsld_learning_objects(environment_id); - comment on table imsld_learning_objects is ' Learning objects are incorporated (in dotLRN) by referencing resources through the item elements.'; @@ -33,9 +31,6 @@ comment on column imsld_learning_objects.type is ' The type of learning object (e.g. knowledge-object, tool-object test-object). Vocabulary used can be the one of ''learning resource type'' element from the IEEE LTSC LOM.'; -comment on column imsld_learning_objects.schema_version is ' -Indicate the version of the schema to be used.'; - comment on column imsld_learning_objects.is_visible_p is ' Initial visibility attribute'; @@ -67,6 +62,9 @@ references cr_items --imsld_prerequisites ); +create index imsld_imsld_loid_idx on imsld_imslds(learning_objective_id); +create index imsld_imsld_pid_idx on imsld_imslds(prerequisite_id); + comment on table imsld_imslds is ' IMS-LD main table, where the imsld general information is stored.'; @@ -125,6 +123,8 @@ parameters varchar(4000) ); +create index imsld_items_pid_idx on imsld_items(parent_item_id); + comment on table imsld_items is ' This table holds the imsld items of the unit of learning'; @@ -189,6 +189,7 @@ ); create index imsld_roles_parent_id_idx on imsld_roles(parent_role_id); +create index imsld_roles_comp_id_idx on imsld_roles(component_id); comment on table imsld_roles is ' Roles of the unit of learning. Whenever possible, they are treated just like the roles in dotLRN'; @@ -207,7 +208,7 @@ 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 ( +create table imsld_activity_desc ( description_id integer constraint imsld_act_desc_fk references cr_revisions @@ -217,7 +218,7 @@ pretty_title varchar(200) ); -comment on table iimsld_activity_description is ' +comment on table imsld_activity_desc 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'; @@ -233,6 +234,10 @@ constraint imsld_la_component_id_fk references cr_items --imsld_components not null, + activity_description_id integer + constraint imsld_la_desc_id_fk + references cr_items --imsld_activity_descs + not null, parameters varchar(4000), is_visible_p char(1) check (is_visible_p in ('t','f')) @@ -254,8 +259,11 @@ ); create index imsld_la_comp_id_idx on imsld_learning_activities(component_id); +create index imsld_la_ad_id_idx on imsld_learning_activities(activity_description_id); create index imsld_la_timel_id_idx on imsld_learning_activities(time_limit_id); create index imsld_la_oncomp_id_idx on imsld_learning_activities(on_completion_id); +create index imsld_la_lo_id_idx on imsld_learning_activities(learning_objective_id); +create index imsld_la_prereq_id_idx on imsld_learning_activities(prerequisite_id); comment on table imsld_learning_activities is ' This table stores the learning activitis of the component of the unit of learning.'; @@ -322,13 +330,19 @@ constraint imsld_as_component_id_fk references cr_items --imsld_components not null, + information_id integer + constraint imsld_as_info_id_fk + references cr_items --imsld_components + not null, identifier varchar(100), number_to_select integer, structure_type char(9) - check (structure_type in ('selection','sequence')) + check (structure_type in ('selection','sequence')), + sort varchar(4) ); create index imsld_as_comp_id_idx on imsld_activity_structures(component_id); +create index imsld_as_info_id_idx on imsld_activity_structures(information_id); comment on table imsld_activity_structures is ' The activity structures of a component determine the sets and order of activities in the unit of learning, as well as the @@ -355,10 +369,11 @@ identifier varchar(100), learning_object_id integer constraint imsld_env_loid_fk - references cr_items, --imsld_learning_objects + references cr_items --imsld_learning_objects ); create index imsld_envs_comp_id_idx on imsld_environments(component_id); +create index imsld_learno_id_idx on imsld_environments(learning_object_id); comment on table imsld_environments is ' The environments are learning objects, services or more environments that complement a given activity. @@ -391,7 +406,7 @@ 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.' +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'; @@ -412,7 +427,7 @@ references cr_items --imsld_services not null, recipients varchar(11) - check (recipients in ('all-in-role','selection')), + check (recipients in ('all-in-role','selection')) ); create index imsld_send_m_serv_id_idx on imsld_send_mail_services(service_id); @@ -519,9 +534,9 @@ check (is_visible_p in ('t','f')) default 't', identifier varchar(100), - when_last_act_completed_id integer - constraint imsld_plays_lastact_fk - references cr_items, --imsld_acts + when_last_act_completed_p char(1) + check (when_last_act_completed_p in ('t','f')) + default 't', time_limit_id integer constraint imsld_plays_timelid_fk references cr_items, --imsld_time_limits @@ -531,7 +546,6 @@ ); create index imsld_plays_meth_id_idx on imsld_plays(method_id); -create index imsld_plays_act_id_idx on imsld_plays(when_last_act_completed_id); create index imsld_plays_timel_id_idx on imsld_plays(time_limit_id); create index imsld_plays_oncomp_id_idx on imsld_plays(on_completion_id); @@ -585,9 +599,6 @@ support_activity_id integer constraint imsld_rp_said_fk references cr_items, --imsld_support_activities - unit_of_learning_id integer - constraint imsld_rp_imslds_fk - references cr_items, --imsld_imslds activity_structure_id integer constraint imsld_rp_asid_fk references cr_items, --imsld_activity_structures @@ -599,7 +610,6 @@ create index imsld_rp_role_id_idx on imsld_role_parts(role_id); create index imsld_rp_la_id_idx on imsld_role_parts(learning_activity_id); create index imsld_rp_sa_id_idx on imsld_role_parts(support_activity_id); -create index imsld_rp_imsld_id_idx on imsld_role_parts(unit_of_learning_id); create index imsld_rp_as_id_idx on imsld_role_parts(activity_structure_id); create index imsld_rp_env_id_idx on imsld_role_parts(environment_id); @@ -634,8 +644,6 @@ 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.