Index: openacs-4/packages/download/sql/oracle/download-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/download/sql/oracle/download-drop.sql,v diff -u -r1.1 -r1.2 --- openacs-4/packages/download/sql/oracle/download-drop.sql 20 Apr 2001 20:51:10 -0000 1.1 +++ openacs-4/packages/download/sql/oracle/download-drop.sql 10 May 2001 20:32:55 -0000 1.2 @@ -49,6 +49,7 @@ drop table download_archive_types; drop table download_repository; drop table download_archive_descs; + /* acs_object_type */ begin content_type.unregister_child_type( @@ -65,6 +66,8 @@ end; / +--fixme VK drop the content_types, not the acs type directly + begin acs_object_type.drop_type( object_type => 'cr_download_archive_desc', Index: openacs-4/packages/download/sql/postgresql/download-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/download/sql/postgresql/download-create.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/download/sql/postgresql/download-create.sql 10 May 2001 20:32:55 -0000 1.1 @@ -0,0 +1,418 @@ +-- Note cr_items has available: +-- parent_id +-- name +-- publish_status +-- Note cr_revisions has available: +-- title +-- description +-- publish_date +-- content + +-- QUESTION: How do we store information about vendors like their +-- URL, or a description + +-- +-- Download Repository Instances: +-- Each has a description and some help text. +-- parent_id is the package_id +-- +create table download_repository ( + repository_id integer + constraint download_repository_id_fk + references cr_items (item_id) on delete cascade + constraint download_repository_id_pk primary key +); + +create function inline_0 () +returns integer as ' +begin + PERFORM content_type__create_type ( + ''cr_download_rep'', + ''content_revision'', + ''Download Repository'', + ''Download Repositories'', + ''download_repository'', + ''repository_id'', + null + ); + + return 0; +end;' language 'plpgsql'; + +select inline_0 (); + +drop function inline_0 (); + +--begin +-- content_type.create_type ( +-- content_type => 'cr_download_rep', +-- pretty_name => 'Download Repository', +-- pretty_plural => 'Download Repositories', +-- table_name => 'download_repository', +-- id_column => 'repository_id' +-- ); +--end; +--/ +--show errors + + + +comment on table download_repository is ' +This table stores the actual download repositories. Each repository has a title +and description of the repository. Meta information about what can be stored in +the repository is keyed to this table'; + +-- DESIGN QUESTION: We could make the archive type part of the metadata, +-- but then we couldn't very well conditionalize other metadata based on it. + +-- Each download module will support certain archive types, we need to indicate +-- what those types are. +create sequence download_archive_type_seq; +create table download_archive_types ( + archive_type_id integer + constraint download_archive_types_pk + primary key, + repository_id integer + constraint download_archive_rep_id_fk + references download_repository (repository_id), + pretty_name varchar(100) not null, + description varchar(500) not null +-- unique (repository_id, short_name) constraint download_archive_types_shrt_name_un unique +); + +comment on table download_archive_types is ' + This table stores the types of archives that can be stored in a given download repository. +'; + +create sequence download_reasons_seq; +create table download_reasons ( + download_reason_id integer + constraint download_archive_reasons_pk + primary key, + repository_id integer + constraint download_reason_id_fk + references download_repository (repository_id) , + reason varchar(500) not null +-- unique (repository_id, reason) constraint download_reason_un unique +); + +comment on table download_archive_types is ' + This table stores the types of archives that can be stored in a given download repository. +'; + + +-- +-- Meta Information for Each Archive in a Particular Repository +-- We must be able to support meta info per archive for things like: +-- owners (email addresses) +-- vendors +-- dependencies +-- This is basically survey simple. +-- +create table download_archive_metadata ( + metadata_id integer + constraint download_ma_pk + primary key, + repository_id integer + constraint download_ma_rep_id_fk + references download_repository (repository_id), + --if archive_type_id is null, applies to all archive types + archive_type_id integer + constraint download_ma_type_fk + references download_archive_types, + sort_key integer + constraint download_ma_sort_key_nn + not null, + pretty_name varchar(100) + constraint download_ma_pretty_name_nn + not null, + data_type varchar(30) + constraint download_data_type_ck + check (data_type in ('text', 'shorttext', 'boolean', 'number', 'integer', 'date', 'choice')), + required_p boolean, + --linked_p indicates whether we should have links to show all + --archives with a particular link + linked_p boolean, + --is this field shown on the main page? + mainpage_p boolean, + --is this field computed, or should we ask the user to enter it? + computed_p boolean +); + +--When a piece of metadata has a fixed set of responses +create sequence download_md_choice_id_sequence start 1; + +create table download_metadata_choices ( + choice_id integer + constraint download_mc_choice_id_nn + not null + constraint download_mc_choice_id_pk + primary key, + metadata_id integer + constraint download_mc_met_id_nn + not null + constraint download_mc_met_id_fk + references download_archive_metadata, + -- human readable + label varchar(500) + constraint download_mc_label_nn + not null, + -- might be useful for averaging or whatever, generally null + numeric_value number, + -- lower is earlier + sort_order integer +); + +comment on table download_archive_metadata is ' + This table stores information about all metadata stored for each archive in a given + repository.'; + +-- +-- The Archives Themselves +-- We need at least the following pieces of info: +-- archive_name (via cr_item.name) +-- repository_id (via cr_item.parent_id) +-- summary (via archive_desc_id.title) +-- description (via archive_desc_id.text) +-- description_type (i.e. archive_desc_id.mime_type for description) +-- +create table download_archives ( + archive_id integer + constraint download_archives_a_id_fk + references cr_items (item_id) on delete cascade + constraint download_archives_a_id_pk + primary key, + archive_type_id integer + constraint download_archives_at_id_fk + references download_archive_types, +-- we use another content_type to hold the content of the archive description, +-- which we need in addition to version descriptions + archive_desc_id integer + constraint download_archives_ad_id_fk + references cr_revisions +); + +-- We need at least the following +-- +-- approved_p +-- approved_date +-- approved_user +-- approved_comment +-- archive_id (via cr_revision.cr_item) +-- version_name (via cr_revision.description) +-- file_name (via cr_revision.title) +-- file_type (via cr_revision.mime_type) +-- file_content (via cr_revision.content) +-- version_url (via metadata) +-- release_notes (via metadata) +-- release_date (via metadata) +-- vendor (via metadata) +-- owner (via metadata) + +create table download_archive_revisions ( + revision_id integer + constraint download_ar_id_fk + references cr_revisions (revision_id) on delete cascade + constraint download_ar_id_pk + primary key, + approved_p boolean, + approved_date timestamp, + approved_user integer + constraint download_ar_usr_fk + references users, + approved_comment varchar(1000) +); + + +-- Storage of the metadata per archive +-- Long skinny table. +create table download_revision_data ( + revision_id integer + constraint download_revision_data_fk + references download_archive_revisions(revision_id), + metadata_id integer + constraint download_revision_metadata_fk + references download_archive_metadata(metadata_id), + --The possible responses. + choice_id integer + constraint download_revision_choice_fk + references download_metadata_choices (choice_id), + boolean_answer boolean, + clob_answer text, + number_answer number, + varchar_answer varchar(4000), + date_answer timestamp +); + + +-------------------------------------------------------------------------------- +-------------------------------------------------------------------------------- +-- Information about who has downloaded stuff +-------------------------------------------------------------------------------- +-------------------------------------------------------------------------------- + +-- We want to collect statistics on downloads. +create sequence download_downloads_seq; +create table download_downloads ( + download_id integer + constraint download_downloads_id_pk + primary key, + user_id integer + constraint download_downloads_user_fk + references users + on delete set null, + revision_id integer + constraint download_downloads_rev_fk + references download_archive_revisions + on delete cascade, + download_date timestamp not null, + download_hostname varchar(400), + download_ip varchar(20), + user_agent varchar(200), + reason_id integer + constraint download_downloads_reason_fl + references download_reasons(download_reason_id) + on delete set null, + reason varchar(1000) +); + +create function inline_1 () +returns integer as ' +begin + PERFORM content_type__create_type ( + ''cr_download_archive'', + ''content_revision'', + ''Download Archive'', + ''Download Archives'', + ''download_archives'', + ''archive_id'', + null + ); + + PERFORM content_type__register_child_type( + ''cr_download_rep'', + ''cr_download_archive'', + ''generic'', + 0, + null + ); + + return 0; +end;' language 'plpgsql'; + +select inline_1 (); + +drop function inline_1 (); + +create function inline_2 () +returns integer as ' +begin + PERFORM content_type__create_type ( + ''cr_download_archive_desc'', + ''content_revision'', + ''Download Archive Description'', + ''Download Archive Descriptions'', + ''download_archive_descs'', + ''archive_desc_id'', + null + ); + + PERFORM content_type__register_child_type( + ''cr_download_rep'', + ''cr_download_archive_desc'', + ''generic'', + 0, + null + ); + + return 0; +end;' language 'plpgsql'; + +select inline_2 (); + +drop function inline_2 (); + +--begin +-- content_type.create_type ( +-- content_type => 'cr_download_archive', +-- pretty_name => 'Download Archive', +-- pretty_plural => 'Download Archive', +-- table_name => 'download_archives', +-- id_column => 'archive_id' +-- ); +-- +-- content_type.register_child_type( +-- parent_type => 'cr_download_rep', +-- child_type => 'cr_download_archive' +-- ); +-- +-- +-- content_type.create_type ( +-- content_type => 'cr_download_archive_desc', +-- pretty_name => 'Download Archive Description', +-- pretty_plural => 'Download Archive Description', +-- table_name => 'download_archive_descs', +-- id_column => 'archive_desc_id' +-- ); +-- +-- content_type.register_child_type( +-- parent_type => 'cr_download_rep', +-- child_type => 'cr_download_archive_desc' +-- ); +-- +--end; +--/ +--show errors + +create view download_repository_obj as + select repository_id, + o.*, + i.parent_id, + r.title, + r.description, + r.content as help_text + from download_repository dr, acs_objects o, cr_items i, cr_revisions r + where dr.repository_id = o.object_id and + i.item_id = o.object_id and + r.revision_id = i.live_revision; + +create view download_archives_obj as + select cri.parent_id as repository_id, + cri.name as archive_name, + cri.latest_revision, + cri.live_revision, + da.archive_id, + da.archive_type_id, + da.archive_desc_id, + desc_item.title as summary, + desc_item.description as description, + desc_item.mime_type as description_type, + desc_item.creation_user, + desc_item.creation_date, + desc_item.creation_ip + from download_archives da, cr_items cri, download_archive_descsi desc_item + where da.archive_desc_id = desc_item.revision_id and + da.archive_id = cri.item_id; + +create view download_arch_revisions_obj as + select dar.*, + o.*, + r.item_id as archive_id, + r.title as file_name, + r.description as version_name, + r.publish_date, + r.mime_type, + r.content + from download_archive_revisions dar, acs_objects o, cr_revisions r + where dar.revision_id = o.object_id and + dar.revision_id = r.revision_id; + +create view download_downloads_repository as + select dd.*, + (select repository_id + from download_archives_obj da, cr_revisions r + where dd.revision_id = r.revision_id and + r.item_id = da.archive_id) as repository_id + from download_downloads dd; + +\i download-packages.sql Index: openacs-4/packages/download/sql/postgresql/download-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/download/sql/postgresql/download-drop.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/download/sql/postgresql/download-drop.sql 10 May 2001 20:32:55 -0000 1.1 @@ -0,0 +1,166 @@ +-- +-- Drop the data model and the PL/SQL packages. +-- + +/* Drop all content items */ + +create function inline_0 () +returns integer as ' +declare + archive_rec cr_items%ROWTYPE; +begin + for archive_rec in select item_id from cr_items + where content_type in ( ''cr_download_archive_desc'', + ''cr_download_archive'', + ''cr_download_rep'' ) loop + PERFORM content_item__delete( archive_rec.item_id ); + end loop; + + return 0; +end;' language 'plpgsql'; + +select inline_0 (); + +drop function inline_0 (); + +--begin +-- for archive_rec in (select item_id from cr_items where content_type = 'cr_download_archive_desc') +-- loop +-- content_item.delete(archive_rec.item_id); +-- end loop; +--end; +--/ +-- +--begin +-- for archive_rec in (select item_id from cr_items where content_type = 'cr_download_archive') +-- loop +-- content_item.delete(archive_rec.item_id); +-- end loop; +--end; +--/ +-- +--begin +-- for archive_rec in (select item_id from cr_items where content_type = 'cr_download_rep') +-- loop +-- content_item.delete(archive_rec.item_id); +-- end loop; +--end; +--/ + +/* Sequences */ +drop sequence download_archive_type_seq; +drop sequence download_reasons_seq; +drop sequence download_md_choice_id_sequence; +drop sequence download_downloads_seq; + +/* Views */ +drop view download_repository_obj; +drop view download_archives_obj; +drop view download_arch_revisions_obj; +drop view download_downloads_repository; +--drop view download_archive_descsi; +--drop view download_archive_descsx; + +/* Tables */ +drop table download_downloads; +drop table download_revision_data; +drop table download_archive_revisions; +drop table download_archives; +drop table download_metadata_choices; +drop table download_archive_metadata; +drop table download_reasons; +drop table download_archive_types; +drop table download_repository; +--drop table download_archive_descs; + +/* acs_object_type */ + +create function inline_1 () +returns integer as ' +begin + PERFORM content_type__unregister_child_type ( + ''cr_download_rep'', + ''cr_download_archive'', + ''generic'' + ); + + PERFORM content_type__unregister_child_type ( + ''cr_download_rep'', + ''cr_download_archive_desc'', + ''generic'' + ); + + PERFORM content_type__drop_type ( + ''cr_download_archive_desc'', + ''t'', + ''t'' + ); + + PERFORM content_type__drop_type ( + ''cr_download_archive'', + ''t'', + ''f'' + ); + + PERFORM content_type__drop_type ( + ''cr_download_rep'', + ''t'', + ''f'' + ); + + return 0; +end;' language 'plpgsql'; + +select inline_1 (); + +drop function inline_1 (); + +--begin +-- content_type.unregister_child_type( +-- parent_type => 'cr_download_rep', +-- child_type => 'cr_download_archive', +-- relation_tag => 'generic' +-- ); +-- +-- content_type.unregister_child_type( +-- parent_type => 'cr_download_rep', +-- child_type => 'cr_download_archive_desc', +-- relation_tag => 'generic' +-- ); +--end; +--/ +-- +--begin +-- acs_object_type.drop_type( +-- object_type => 'cr_download_archive_desc', +-- cascade_p => 't' +-- ); +--end; +--/ +--show errors +-- +--begin +-- acs_object_type.drop_type( +-- object_type => 'cr_download_rep', +-- cascade_p => 't' +-- ); +--end; +--/ +--show errors +-- +--begin +-- acs_object_type.drop_type( +-- object_type => 'cr_download_archive', +-- cascade_p => 't' +-- ); +-- +--end; +--/ +--show errors +-- + +drop function download_rep__new (integer,varchar,varchar,varchar,timestamp,integer,integer,integer,varchar); +drop function download_rep__edit (integer,varchar,varchar,varchar,timestamp,integer,varchar); +drop function download_rep__delete (integer); + +--drop package download_rep; Index: openacs-4/packages/download/sql/postgresql/download-packages.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/download/sql/postgresql/download-packages.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/download/sql/postgresql/download-packages.sql 10 May 2001 20:32:55 -0000 1.1 @@ -0,0 +1,255 @@ +--Defines the following packages: +-- +-- Download Repository +-- -new +-- -delete +-- -edit +-- -new_archive_type (no need for proc) +-- -delete_archive_type (no need for proc) +-- -edit_archive_type (no need for proc) +-- -new_reason (no need for proc) +-- -delete_reason (no need for proc) +-- -edit_reason (no need for proc) +-- -new_metadata (no need for proc) +-- -delete_metadata (no need for proc) +-- -edit_metadata (no need for proc) +-- -new_metadata_choice (no need for proc) +-- -delete_metadata_choice (no need for proc) +-- -edit_metadata_choice (no need for proc) +-- +-- +-- Download Archive +-- -new +-- -delete +-- -edit +-- -new_revision +-- -approve_revision +-- -set_metadata_value +-- -downloaded_by + +create function download_rep__new (integer,varchar,varchar,varchar,timestamp,integer,integer,integer,varchar) +returns integer as ' +declare + new__repository_id alias for $1; + new__title alias for $2; + new__description alias for $3; + new__help_text alias for $4; -- default null + new__creation_date alias for $5; -- default now() + new__creation_user alias for $6; -- default null + new__parent_id alias for $7; -- default null + new__context_id alias for $8; -- default null + new__creation_ip alias for $9; -- default null + v_name cr_items.name%TYPE; + v_repository_id integer; +begin + v_name := ''download_repository'' || new__repository_id; + select into v_repository_id content_item__new ( + v_name, + new__parent_id, + new__repository_id, + null, -- locale + new__creation_date, + new__creation_user, + new__context_id, + new__creation_ip, + ''content_item'', -- item_subtype + ''cr_download_rep'', + new__title, + new__description, + ''text/plain'', -- mime_type + null, -- nls_language + new__help_text, + ''text'' -- storage_type (vk - not sure about this) + ); + + insert into download_repository + (repository_id) + values + (new__repository_id); + + return v_repository_id; + +end;' language 'plpgsql'; + +create function download_rep__edit (integer,varchar,varchar,varchar,timestamp,integer,varchar) +returns integer as ' +declare + edit__repository_id alias for $1; + edit__title alias for $2; + edit__description alias for $3; + edit__help_text alias for $4; + edit__last_modified alias for $5; + edit__modifying_user alias for $6; + edit__modifying_ip alias for $7; + v_revision_id integer; +begin + select into v_revision_id content_revision__new ( + edit__title, + edit__description, + now(), -- publish_date + ''text/plain'', -- mime_type + null, -- nls_language + edit__help_text, + edit__repository_id, + null, -- revision_id + edit__last_modified, + edit__modifying_user, + edit__modifying_ip + ); + + PERFORM content_item__set_live_revision(v_revision_id); + + update acs_objects set + last_modified = edit__last_modified, + modifying_user = edit__modifying_user, + modifying_ip = edit__modifying_ip + where object_id = edit__repository_id; + + return 0; +end;' language 'plpgsql'; + + + -- removed from below function, cuz it doesn;t work FIXME + -- update acs_objects + -- set context_id = null + -- where context_id = delete__repository_id; + + +create function download_rep__delete (integer) +returns integer as ' +declare + delete__repository_id alias for $1; +begin + delete from acs_objects where context_id = delete__repository_id; + + delete from download_repository + where repository_id = delete__repository_id; + + PERFORM acs_object__delete( delete__repository_id ); + + return 0; +end;' language 'plpgsql'; + +--create or replace package download_rep as +-- +-- function new ( +-- repository_id in acs_objects.object_id%TYPE, +-- title in cr_revisions.title%TYPE, +-- description in cr_revisions.description%TYPE, +-- help_text in varchar2 default null, +-- creation_date in acs_objects.creation_date%TYPE default sysdate, +-- creation_user in acs_objects.creation_user%TYPE default null, +-- parent_id in cr_items.parent_id%TYPE default null, +-- context_id in acs_objects.context_id%TYPE default null, +-- creation_ip in acs_objects.creation_ip%TYPE default null +-- ) return download_repository.repository_id%TYPE; +-- +-- procedure edit ( +-- repository_id in acs_objects.object_id%TYPE, +-- title in cr_revisions.title%TYPE, +-- description in cr_revisions.description%TYPE, +-- help_text in varchar2 default null, +-- last_modified in acs_objects.last_modified%TYPE default sysdate, +-- modifying_user in acs_objects.modifying_user%TYPE default null, +-- modifying_ip in acs_objects.modifying_ip%TYPE default null +-- ); +-- +-- procedure delete ( +-- repository_id in acs_objects.object_id%TYPE +-- ); +-- +--end download_rep; +--/ +--show errors +-- +-- +--create or replace package body download_rep as +-- +-- function new ( +-- repository_id in acs_objects.object_id%TYPE, +-- title in cr_revisions.title%TYPE, +-- description in cr_revisions.description%TYPE, +-- help_text in varchar2 default null, +-- creation_date in acs_objects.creation_date%TYPE default sysdate, +-- creation_user in acs_objects.creation_user%TYPE default null, +-- parent_id in cr_items.parent_id%TYPE default null, +-- context_id in acs_objects.context_id%TYPE default null, +-- creation_ip in acs_objects.creation_ip%TYPE default null +-- ) return download_repository.repository_id%TYPE +-- is +-- v_name cr_items.name%TYPE; +-- v_repository_id integer; +-- begin +-- v_name := 'download_repository' || repository_id; +-- v_repository_id := content_item.new ( +-- content_type => 'cr_download_rep', +-- item_id => new.repository_id, +-- name => v_name, +-- parent_id => new.parent_id, +-- context_id => new.context_id, +-- title => new.title, +-- description => new.description, +-- text => new.help_text, +-- creation_date => new.creation_date, +-- creation_user => new.creation_user, +-- creation_ip => new.creation_ip, +-- is_live => 't' +-- ); +-- +-- insert into download_repository +-- (repository_id) +-- values +-- (new.repository_id); +-- +-- return v_repository_id; +-- end new; +-- +-- procedure edit ( +-- repository_id in acs_objects.object_id%TYPE, +-- title in cr_revisions.title%TYPE, +-- description in cr_revisions.description%TYPE, +-- help_text in varchar2 default null, +-- last_modified in acs_objects.last_modified%TYPE default sysdate, +-- modifying_user in acs_objects.modifying_user%TYPE default null, +-- modifying_ip in acs_objects.modifying_ip%TYPE default null +-- ) +-- is +-- v_revision_id integer; +-- begin +-- v_revision_id := content_revision.new ( +-- item_id => edit.repository_id, +-- title => edit.title, +-- description => edit.description, +-- text => edit.help_text, +-- creation_date => edit.last_modified, +-- creation_user => edit.modifying_user, +-- creation_ip => edit.modifying_ip +-- ); +-- content_item.set_live_revision(v_revision_id); +-- +-- +-- update acs_objects set +-- last_modified = edit.last_modified, +-- modifying_user = edit.modifying_user, +-- modifying_ip = edit.modifying_ip +-- where object_id = edit.repository_id; +-- +-- end edit; +-- +-- +-- procedure delete ( +-- repository_id in acs_objects.object_id%TYPE +-- ) +-- is +-- begin +-- update acs_objects set context_id = null where context_id = download_rep.delete.repository_id; +-- +-- delete from download_repository +-- where repository_id = download_rep.delete.repository_id; +-- +-- acs_object.delete(repository_id); +-- end; +-- +--end download_rep; +--/ +--show errors