OpenACS

marioa
committed
lors-oracle-port:marioa:20060829112934
on 29 Aug 06
Upgrade datamodel for Oracle compatibility
openacs-4/.../postgresql/lors-imscp-create.sql (+5 -5)
113 113 -- Organizations
114 114 create table ims_cp_organizations (
115 115     org_id          integer
116 116                     constraint ims_cp_org_id_fk
117 117                     references cr_revisions
118 118                     on delete cascade
119 119                     constraint ims_cp_org_id_pk
120 120                     primary key,
121 121     man_id          integer
122 122                     constraint ims_cp_org_man_id_fk
123 123                     references ims_cp_manifests(man_id)
124 124                     on delete cascade,
125 125     identifier      varchar(100),
126 126     structure       varchar(100),
127 127     org_title       varchar(1000),
128 128     hasmetadata     boolean default 'f' not null,
129 129     isshared        boolean default 'f' not null
130 130 );
131 131
132 132 -- create index for ims_cp_organizations
133   create index ims_cp_organizations__man_id_idx on ims_cp_organizations (man_id);
  133 create index ims_cp_org_man_id_idx on ims_cp_organizations (man_id);
134 134
135 135
136 136 -- create ims organization content type
137 137 --
138 138 select content_type__create_type (
139 139        'ims_organization_object',    -- content_type
140 140        'content_revision',       -- supertype. We search revision content
141 141                                  -- first, before item metadata
142 142        'IMS Organization Object',    -- pretty_name
143 143        'IMS Organization Objects',   -- pretty_plural
144 144        'ims_cp_organizations',        -- table_name
145 145        'org_id',                 -- id_column
146 146        'ims_organization__get_title' -- name_method
147 147 );
148 148
149 149 create or replace function ims_organization__get_title (integer)
150 150 returns varchar as '
151 151 declare
152 152   get_title__org_id             alias for $1; 
153 153   get_title__identifier        varchar(200); 
 
270 270 end;' language 'plpgsql';
271 271
272 272
273 273 -- An item can have reference to one of more resources
274 274 -- therefore we need a table that takes care of this multiple
275 275 -- relationship
276 276
277 277 create table ims_cp_items_to_resources (
278 278     ims_item_id         integer
279 279                     constraint ims_cp_items_to_res_item_id_fk
280 280                     references ims_cp_items(ims_item_id)
281 281                     on delete cascade,
282 282     res_id          integer
283 283                     constraint ims_cp_items_to_resources_fk
284 284                     references ims_cp_resources(res_id)
285 285                     on delete cascade,
286 286                     primary key (ims_item_id, res_id)
287 287 );
288 288
289 289 -- create index for ims_cp_items_to_resources
290   create index ims_cp_items_to_resources__item_id_idx on ims_cp_items_to_resources (ims_item_id);
291   create index ims_cp_items_to_resources__res_id_idx on ims_cp_items_to_resources (res_id);
  290 create index ims_cp_items_2_res_item_id_idx on ims_cp_items_to_resources (ims_item_id);
  291 create index ims_cp_items_2_res_id_idx on ims_cp_items_to_resources (res_id);
292 292
293 293 -- Resource dependencies
294 294
295 295 create sequence ims_cp_dependencies_seq start 1;
296 296
297 297 create table ims_cp_dependencies (
298 298     dep_id          integer
299 299                     constraint ims_cp_dependencies_dep_id_pk
300 300                     primary key,
301 301     res_id          integer
302 302                     constraint ims_cp_dependencies_res_id_fk
303 303                     references ims_cp_resources(res_id)
304 304                     on delete cascade,
305 305     identifierref   varchar(2000)
306 306 );
307 307
308 308 -- create index for ims_cp_dependencies
309   create index ims_cp_dependencies__res_id_idx on ims_cp_dependencies (res_id);
  309 create index ims_cp_dependencies_res_id_idx on ims_cp_dependencies (res_id);
310 310
311 311 -- Resource files
312 312
313 313 create table ims_cp_files (
314 314     file_id         integer
315 315                     constraint ims_cp_files_file_if_fk
316 316                     references cr_revisions(revision_id)
317 317                     on delete cascade,
318 318     res_id          integer
319 319                     constraint ims_cp_file_res_id_fk
320 320                     references ims_cp_resources(res_id)
321 321                     on delete cascade,
322 322     pathtofile      varchar(2000),
323 323     filename        varchar(2000),
324 324     hasmetadata     boolean default 'f' not null,
325 325                     constraint ims_cp_file_pk
326 326                     primary key (file_id, res_id)
327 327 );
328 328    
329 329 -- create index for ims_cp_files