Index: openacs-4/packages/acs-kernel/sql/oracle/upgrade/upgrade-5.0d7-5.0d9.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/oracle/upgrade/upgrade-5.0d7-5.0d9.sql,v diff -u -r1.1 -r1.2 --- openacs-4/packages/acs-kernel/sql/oracle/upgrade/upgrade-5.0d7-5.0d9.sql 24 Sep 2003 12:20:27 -0000 1.1 +++ openacs-4/packages/acs-kernel/sql/oracle/upgrade/upgrade-5.0d7-5.0d9.sql 24 Sep 2003 17:25:05 -0000 1.2 @@ -10,117 +10,121 @@ drop table apm_package_file_types cascade constraints; drop table apm_package_files cascade constraints; drop view apm_file_info; --- Recreating apm_version_package to remove the add_file and remove_file procs and functions +-- Recreating apm_version_package and apm_package packages to remove the following procs and functions: +-- apm_package_version.add_file +-- apm_package_version.remove_file +-- apm_package.enable +-- apm_package.disable create or replace package apm_package_version as function new ( - version_id in apm_package_versions.version_id%TYPE - default null, - package_key in apm_package_versions.package_key%TYPE, - version_name in apm_package_versions.version_name%TYPE - default null, - version_uri in apm_package_versions.version_uri%TYPE, - summary in apm_package_versions.summary%TYPE, - description_format in apm_package_versions.description_format%TYPE, - description in apm_package_versions.description%TYPE, - release_date in apm_package_versions.release_date%TYPE, - vendor in apm_package_versions.vendor%TYPE, - vendor_uri in apm_package_versions.vendor_uri%TYPE, + version_id in apm_package_versions.version_id%TYPE + default null, + package_key in apm_package_versions.package_key%TYPE, + version_name in apm_package_versions.version_name%TYPE + default null, + version_uri in apm_package_versions.version_uri%TYPE, + summary in apm_package_versions.summary%TYPE, + description_format in apm_package_versions.description_format%TYPE, + description in apm_package_versions.description%TYPE, + release_date in apm_package_versions.release_date%TYPE, + vendor in apm_package_versions.vendor%TYPE, + vendor_uri in apm_package_versions.vendor_uri%TYPE, auto_mount in apm_package_versions.auto_mount%TYPE, - installed_p in apm_package_versions.installed_p%TYPE - default 'f', - data_model_loaded_p in apm_package_versions.data_model_loaded_p%TYPE - default 'f' + installed_p in apm_package_versions.installed_p%TYPE + default 'f', + data_model_loaded_p in apm_package_versions.data_model_loaded_p%TYPE + default 'f' ) return apm_package_versions.version_id%TYPE; procedure delete ( - version_id in apm_packages.package_id%TYPE + version_id in apm_packages.package_id%TYPE ); procedure enable ( - version_id in apm_package_versions.version_id%TYPE + version_id in apm_package_versions.version_id%TYPE ); procedure disable ( - version_id in apm_package_versions.version_id%TYPE + version_id in apm_package_versions.version_id%TYPE ); function edit ( - new_version_id in apm_package_versions.version_id%TYPE - default null, - version_id in apm_package_versions.version_id%TYPE, - version_name in apm_package_versions.version_name%TYPE - default null, - version_uri in apm_package_versions.version_uri%TYPE, - summary in apm_package_versions.summary%TYPE, - description_format in apm_package_versions.description_format%TYPE, - description in apm_package_versions.description%TYPE, - release_date in apm_package_versions.release_date%TYPE, - vendor in apm_package_versions.vendor%TYPE, - vendor_uri in apm_package_versions.vendor_uri%TYPE, + new_version_id in apm_package_versions.version_id%TYPE + default null, + version_id in apm_package_versions.version_id%TYPE, + version_name in apm_package_versions.version_name%TYPE + default null, + version_uri in apm_package_versions.version_uri%TYPE, + summary in apm_package_versions.summary%TYPE, + description_format in apm_package_versions.description_format%TYPE, + description in apm_package_versions.description%TYPE, + release_date in apm_package_versions.release_date%TYPE, + vendor in apm_package_versions.vendor%TYPE, + vendor_uri in apm_package_versions.vendor_uri%TYPE, auto_mount in apm_package_versions.auto_mount%TYPE, - installed_p in apm_package_versions.installed_p%TYPE - default 'f', - data_model_loaded_p in apm_package_versions.data_model_loaded_p%TYPE - default 'f' + installed_p in apm_package_versions.installed_p%TYPE + default 'f', + data_model_loaded_p in apm_package_versions.data_model_loaded_p%TYPE + default 'f' ) return apm_package_versions.version_id%TYPE; -- Add an interface provided by this version. function add_interface( - interface_id in apm_package_dependencies.dependency_id%TYPE - default null, - version_id in apm_package_versions.version_id%TYPE, - interface_uri in apm_package_dependencies.service_uri%TYPE, - interface_version in apm_package_dependencies.service_version%TYPE + interface_id in apm_package_dependencies.dependency_id%TYPE + default null, + version_id in apm_package_versions.version_id%TYPE, + interface_uri in apm_package_dependencies.service_uri%TYPE, + interface_version in apm_package_dependencies.service_version%TYPE ) return apm_package_dependencies.dependency_id%TYPE; procedure remove_interface( - interface_id in apm_package_dependencies.dependency_id%TYPE + interface_id in apm_package_dependencies.dependency_id%TYPE ); procedure remove_interface( - interface_uri in apm_package_dependencies.service_uri%TYPE, - interface_version in apm_package_dependencies.service_version%TYPE, - version_id in apm_package_versions.version_id%TYPE + interface_uri in apm_package_dependencies.service_uri%TYPE, + interface_version in apm_package_dependencies.service_version%TYPE, + version_id in apm_package_versions.version_id%TYPE ); -- Add a requirement for this version. A requirement is some interface that this -- version depends on. function add_dependency( - dependency_id in apm_package_dependencies.dependency_id%TYPE - default null, - version_id in apm_package_versions.version_id%TYPE, - dependency_uri in apm_package_dependencies.service_uri%TYPE, - dependency_version in apm_package_dependencies.service_version%TYPE + dependency_id in apm_package_dependencies.dependency_id%TYPE + default null, + version_id in apm_package_versions.version_id%TYPE, + dependency_uri in apm_package_dependencies.service_uri%TYPE, + dependency_version in apm_package_dependencies.service_version%TYPE ) return apm_package_dependencies.dependency_id%TYPE; procedure remove_dependency( - dependency_id in apm_package_dependencies.dependency_id%TYPE + dependency_id in apm_package_dependencies.dependency_id%TYPE ); procedure remove_dependency( - dependency_uri in apm_package_dependencies.service_uri%TYPE, - dependency_version in apm_package_dependencies.service_version%TYPE, - version_id in apm_package_versions.version_id%TYPE + dependency_uri in apm_package_dependencies.service_uri%TYPE, + dependency_version in apm_package_dependencies.service_version%TYPE, + version_id in apm_package_versions.version_id%TYPE ); -- Given a version_name (e.g. 3.2a), return -- something that can be lexicographically sorted. function sortable_version_name ( - version_name in apm_package_versions.version_name%TYPE + version_name in apm_package_versions.version_name%TYPE ) return varchar2; -- Given two version names, return 1 if one > two, -1 if two > one, 0 otherwise. -- Deprecate? function version_name_greater( - version_name_one in apm_package_versions.version_name%TYPE, - version_name_two in apm_package_versions.version_name%TYPE + version_name_one in apm_package_versions.version_name%TYPE, + version_name_two in apm_package_versions.version_name%TYPE ) return integer; function upgrade_p( - path in varchar2, - initial_version_name in apm_package_versions.version_name%TYPE, - final_version_name in apm_package_versions.version_name%TYPE + path in varchar2, + initial_version_name in apm_package_versions.version_name%TYPE, + final_version_name in apm_package_versions.version_name%TYPE ) return integer; procedure upgrade( @@ -134,37 +138,37 @@ create or replace package body apm_package_version as function new ( - version_id in apm_package_versions.version_id%TYPE - default null, - package_key in apm_package_versions.package_key%TYPE, - version_name in apm_package_versions.version_name%TYPE - default null, - version_uri in apm_package_versions.version_uri%TYPE, - summary in apm_package_versions.summary%TYPE, - description_format in apm_package_versions.description_format%TYPE, - description in apm_package_versions.description%TYPE, - release_date in apm_package_versions.release_date%TYPE, - vendor in apm_package_versions.vendor%TYPE, - vendor_uri in apm_package_versions.vendor_uri%TYPE, + version_id in apm_package_versions.version_id%TYPE + default null, + package_key in apm_package_versions.package_key%TYPE, + version_name in apm_package_versions.version_name%TYPE + default null, + version_uri in apm_package_versions.version_uri%TYPE, + summary in apm_package_versions.summary%TYPE, + description_format in apm_package_versions.description_format%TYPE, + description in apm_package_versions.description%TYPE, + release_date in apm_package_versions.release_date%TYPE, + vendor in apm_package_versions.vendor%TYPE, + vendor_uri in apm_package_versions.vendor_uri%TYPE, auto_mount in apm_package_versions.auto_mount%TYPE, - installed_p in apm_package_versions.installed_p%TYPE - default 'f', - data_model_loaded_p in apm_package_versions.data_model_loaded_p%TYPE - default 'f' + installed_p in apm_package_versions.installed_p%TYPE + default 'f', + data_model_loaded_p in apm_package_versions.data_model_loaded_p%TYPE + default 'f' ) return apm_package_versions.version_id%TYPE is v_version_id apm_package_versions.version_id%TYPE; begin if version_id is null then select acs_object_id_seq.nextval - into v_version_id - from dual; + into v_version_id + from dual; else v_version_id := version_id; end if; - v_version_id := acs_object.new( - object_id => v_version_id, - object_type => 'apm_package_version' + v_version_id := acs_object.new( + object_id => v_version_id, + object_type => 'apm_package_version' ); insert into apm_package_versions (version_id, package_key, version_name, version_uri, summary, description_format, description, @@ -174,11 +178,11 @@ summary, description_format, description, release_date, vendor, vendor_uri, auto_mount, installed_p, data_model_loaded_p); - return v_version_id; + return v_version_id; end new; procedure delete ( - version_id in apm_packages.package_id%TYPE + version_id in apm_packages.package_id%TYPE ) is begin @@ -189,91 +193,90 @@ where version_id = apm_package_version.delete.version_id; delete from apm_package_versions - where version_id = apm_package_version.delete.version_id; + where version_id = apm_package_version.delete.version_id; acs_object.delete(apm_package_version.delete.version_id); end delete; procedure enable ( - version_id in apm_package_versions.version_id%TYPE + version_id in apm_package_versions.version_id%TYPE ) is begin update apm_package_versions set enabled_p = 't' - where version_id = enable.version_id; + where version_id = enable.version_id; end enable; procedure disable ( - version_id in apm_package_versions.version_id%TYPE + version_id in apm_package_versions.version_id%TYPE ) is begin update apm_package_versions set enabled_p = 'f' - where version_id = disable.version_id; + where version_id = disable.version_id; end disable; - function copy( - version_id in apm_package_versions.version_id%TYPE, - new_version_id in apm_package_versions.version_id%TYPE default null, - new_version_name in apm_package_versions.version_name%TYPE, - new_version_uri in apm_package_versions.version_uri%TYPE + version_id in apm_package_versions.version_id%TYPE, + new_version_id in apm_package_versions.version_id%TYPE default null, + new_version_name in apm_package_versions.version_name%TYPE, + new_version_uri in apm_package_versions.version_uri%TYPE ) return apm_package_versions.version_id%TYPE is - v_version_id integer; + v_version_id integer; begin - v_version_id := acs_object.new( - object_id => new_version_id, - object_type => 'apm_package_version' + v_version_id := acs_object.new( + object_id => new_version_id, + object_type => 'apm_package_version' ); - insert into apm_package_versions(version_id, package_key, version_name, - version_uri, summary, description_format, description, - release_date, vendor, vendor_uri, auto_mount) - select v_version_id, package_key, copy.new_version_name, - copy.new_version_uri, summary, description_format, description, - release_date, vendor, vendor_uri, auto_mount - from apm_package_versions - where version_id = copy.version_id; + insert into apm_package_versions(version_id, package_key, version_name, + version_uri, summary, description_format, description, + release_date, vendor, vendor_uri, auto_mount) + select v_version_id, package_key, copy.new_version_name, + copy.new_version_uri, summary, description_format, description, + release_date, vendor, vendor_uri, auto_mount + from apm_package_versions + where version_id = copy.version_id; - insert into apm_package_dependencies(dependency_id, version_id, dependency_type, service_uri, service_version) - select acs_object_id_seq.nextval, v_version_id, dependency_type, service_uri, service_version - from apm_package_dependencies - where version_id = copy.version_id; + insert into apm_package_dependencies(dependency_id, version_id, dependency_type, service_uri, service_version) + select acs_object_id_seq.nextval, v_version_id, dependency_type, service_uri, service_version + from apm_package_dependencies + where version_id = copy.version_id; insert into apm_package_callbacks (version_id, type, proc) select v_version_id, type, proc from apm_package_callbacks where version_id = copy.version_id; - insert into apm_package_owners(version_id, owner_uri, owner_name, sort_key) - select v_version_id, owner_uri, owner_name, sort_key - from apm_package_owners - where version_id = copy.version_id; + insert into apm_package_owners(version_id, owner_uri, owner_name, sort_key) + select v_version_id, owner_uri, owner_name, sort_key + from apm_package_owners + where version_id = copy.version_id; - return v_version_id; + return v_version_id; end copy; function edit ( - new_version_id in apm_package_versions.version_id%TYPE - default null, - version_id in apm_package_versions.version_id%TYPE, - version_name in apm_package_versions.version_name%TYPE - default null, - version_uri in apm_package_versions.version_uri%TYPE, - summary in apm_package_versions.summary%TYPE, - description_format in apm_package_versions.description_format%TYPE, - description in apm_package_versions.description%TYPE, - release_date in apm_package_versions.release_date%TYPE, - vendor in apm_package_versions.vendor%TYPE, - vendor_uri in apm_package_versions.vendor_uri%TYPE, + new_version_id in apm_package_versions.version_id%TYPE + default null, + version_id in apm_package_versions.version_id%TYPE, + version_name in apm_package_versions.version_name%TYPE + default null, + version_uri in apm_package_versions.version_uri%TYPE, + summary in apm_package_versions.summary%TYPE, + description_format in apm_package_versions.description_format%TYPE, + description in apm_package_versions.description%TYPE, + release_date in apm_package_versions.release_date%TYPE, + vendor in apm_package_versions.vendor%TYPE, + vendor_uri in apm_package_versions.vendor_uri%TYPE, auto_mount in apm_package_versions.auto_mount%TYPE, - installed_p in apm_package_versions.installed_p%TYPE - default 'f', - data_model_loaded_p in apm_package_versions.data_model_loaded_p%TYPE - default 'f' + installed_p in apm_package_versions.installed_p%TYPE + default 'f', + data_model_loaded_p in apm_package_versions.data_model_loaded_p%TYPE + default 'f' ) return apm_package_versions.version_id%TYPE is v_version_id apm_package_versions.version_id%TYPE; @@ -286,37 +289,37 @@ and version_name = edit.version_name; if version_unchanged_p <> 1 then v_version_id := copy( - version_id => edit.version_id, - new_version_id => edit.new_version_id, - new_version_name => edit.version_name, - new_version_uri => edit.version_uri - ); + version_id => edit.version_id, + new_version_id => edit.new_version_id, + new_version_name => edit.version_name, + new_version_uri => edit.version_uri + ); else - v_version_id := edit.version_id; + v_version_id := edit.version_id; end if; update apm_package_versions - set version_uri = edit.version_uri, - summary = edit.summary, - description_format = edit.description_format, - description = edit.description, - release_date = trunc(sysdate), - vendor = edit.vendor, - vendor_uri = edit.vendor_uri, + set version_uri = edit.version_uri, + summary = edit.summary, + description_format = edit.description_format, + description = edit.description, + release_date = trunc(sysdate), + vendor = edit.vendor, + vendor_uri = edit.vendor_uri, auto_mount = edit.auto_mount, - installed_p = edit.installed_p, - data_model_loaded_p = edit.data_model_loaded_p - where version_id = v_version_id; - return v_version_id; + installed_p = edit.installed_p, + data_model_loaded_p = edit.data_model_loaded_p + where version_id = v_version_id; + return v_version_id; end edit; -- Add an interface provided by this version. function add_interface( - interface_id in apm_package_dependencies.dependency_id%TYPE - default null, - version_id in apm_package_versions.version_id%TYPE, - interface_uri in apm_package_dependencies.service_uri%TYPE, - interface_version in apm_package_dependencies.service_version%TYPE + interface_id in apm_package_dependencies.dependency_id%TYPE + default null, + version_id in apm_package_versions.version_id%TYPE, + interface_uri in apm_package_dependencies.service_uri%TYPE, + interface_version in apm_package_dependencies.service_version%TYPE ) return apm_package_dependencies.dependency_id%TYPE is v_dep_id apm_package_dependencies.dependency_id%TYPE; @@ -331,12 +334,12 @@ (dependency_id, version_id, dependency_type, service_uri, service_version) values (v_dep_id, add_interface.version_id, 'provides', add_interface.interface_uri, - add_interface.interface_version); + add_interface.interface_version); return v_dep_id; end add_interface; procedure remove_interface( - interface_id in apm_package_dependencies.dependency_id%TYPE + interface_id in apm_package_dependencies.dependency_id%TYPE ) is begin @@ -345,9 +348,9 @@ end remove_interface; procedure remove_interface( - interface_uri in apm_package_dependencies.service_uri%TYPE, - interface_version in apm_package_dependencies.service_version%TYPE, - version_id in apm_package_versions.version_id%TYPE + interface_uri in apm_package_dependencies.service_uri%TYPE, + interface_version in apm_package_dependencies.service_version%TYPE, + version_id in apm_package_versions.version_id%TYPE ) is v_dep_id apm_package_dependencies.dependency_id%TYPE; @@ -361,11 +364,11 @@ -- Add a requirement for this version. A requirement is some interface that this -- version depends on. function add_dependency( - dependency_id in apm_package_dependencies.dependency_id%TYPE - default null, - version_id in apm_package_versions.version_id%TYPE, - dependency_uri in apm_package_dependencies.service_uri%TYPE, - dependency_version in apm_package_dependencies.service_version%TYPE + dependency_id in apm_package_dependencies.dependency_id%TYPE + default null, + version_id in apm_package_versions.version_id%TYPE, + dependency_uri in apm_package_dependencies.service_uri%TYPE, + dependency_version in apm_package_dependencies.service_version%TYPE ) return apm_package_dependencies.dependency_id%TYPE is v_dep_id apm_package_dependencies.dependency_id%TYPE; @@ -380,12 +383,12 @@ (dependency_id, version_id, dependency_type, service_uri, service_version) values (v_dep_id, add_dependency.version_id, 'requires', add_dependency.dependency_uri, - add_dependency.dependency_version); + add_dependency.dependency_version); return v_dep_id; end add_dependency; procedure remove_dependency( - dependency_id in apm_package_dependencies.dependency_id%TYPE + dependency_id in apm_package_dependencies.dependency_id%TYPE ) is begin @@ -395,9 +398,9 @@ procedure remove_dependency( - dependency_uri in apm_package_dependencies.service_uri%TYPE, - dependency_version in apm_package_dependencies.service_version%TYPE, - version_id in apm_package_versions.version_id%TYPE + dependency_uri in apm_package_dependencies.service_uri%TYPE, + dependency_version in apm_package_dependencies.service_version%TYPE, + version_id in apm_package_versions.version_id%TYPE ) is v_dep_id apm_package_dependencies.dependency_id%TYPE; @@ -409,138 +412,138 @@ end remove_dependency; function sortable_version_name ( - version_name in apm_package_versions.version_name%TYPE + version_name in apm_package_versions.version_name%TYPE ) return varchar2 is a_fields integer; - a_start integer; - a_end integer; - a_order varchar2(1000); - a_char char(1); - a_seen_letter char(1) := 'f'; + a_start integer; + a_end integer; + a_order varchar2(1000); + a_char char(1); + a_seen_letter char(1) := 'f'; begin a_fields := 0; - a_start := 1; - loop - a_end := a_start; + a_start := 1; + loop + a_end := a_start; - -- keep incrementing a_end until we run into a non-number - while substr(version_name, a_end, 1) >= '0' and substr(version_name, a_end, 1) <= '9' loop - a_end := a_end + 1; - end loop; - if a_end = a_start then - return -1; - -- raise_application_error(-20000, 'Expected number at position ' || a_start); - end if; - if a_end - a_start > 4 then - return -1; - -- raise_application_error(-20000, 'Numbers within versions can only be up to 4 digits long'); - end if; + -- keep incrementing a_end until we run into a non-number + while substr(version_name, a_end, 1) >= '0' and substr(version_name, a_end, 1) <= '9' loop + a_end := a_end + 1; + end loop; + if a_end = a_start then + return -1; + -- raise_application_error(-20000, 'Expected number at position ' || a_start); + end if; + if a_end - a_start > 4 then + return -1; + -- raise_application_error(-20000, 'Numbers within versions can only be up to 4 digits long'); + end if; - -- zero-pad and append the number - a_order := a_order || substr('0000', 1, 4 - (a_end - a_start)) || - substr(version_name, a_start, a_end - a_start) || '.'; + -- zero-pad and append the number + a_order := a_order || substr('0000', 1, 4 - (a_end - a_start)) || + substr(version_name, a_start, a_end - a_start) || '.'; a_fields := a_fields + 1; - if a_end > length(version_name) then - -- end of string - we're outta here - if a_seen_letter = 'f' then - -- append the "final" suffix if there haven't been any letters - -- so far (i.e., not development/alpha/beta) - a_order := a_order || lpad(' ',(7 - a_fields)*5,'0000.') || ' 3F.'; - end if; - return a_order; - end if; + if a_end > length(version_name) then + -- end of string - we're outta here + if a_seen_letter = 'f' then + -- append the "final" suffix if there haven't been any letters + -- so far (i.e., not development/alpha/beta) + a_order := a_order || lpad(' ',(7 - a_fields)*5,'0000.') || ' 3F.'; + end if; + return a_order; + end if; - -- what's the next character? if a period, just skip it - a_char := substr(version_name, a_end, 1); - if a_char = '.' then - null; - else - -- if the next character was a letter, append the appropriate characters - if a_char = 'd' then - a_order := a_order || lpad(' ',(7 - a_fields)*5,'0000.') || ' 0D.'; - elsif a_char = 'a' then - a_order := a_order || lpad(' ',(7 - a_fields)*5,'0000.') || ' 1A.'; - elsif a_char = 'b' then - a_order := a_order || lpad(' ',(7 - a_fields)*5,'0000.') || ' 2B.'; - end if; + -- what's the next character? if a period, just skip it + a_char := substr(version_name, a_end, 1); + if a_char = '.' then + null; + else + -- if the next character was a letter, append the appropriate characters + if a_char = 'd' then + a_order := a_order || lpad(' ',(7 - a_fields)*5,'0000.') || ' 0D.'; + elsif a_char = 'a' then + a_order := a_order || lpad(' ',(7 - a_fields)*5,'0000.') || ' 1A.'; + elsif a_char = 'b' then + a_order := a_order || lpad(' ',(7 - a_fields)*5,'0000.') || ' 2B.'; + end if; - -- can't have something like 3.3a1b2 - just one letter allowed! - if a_seen_letter = 't' then - return -1; - -- raise_application_error(-20000, 'Not allowed to have two letters in version name ''' - -- || version_name || ''''); - end if; - a_seen_letter := 't'; + -- can't have something like 3.3a1b2 - just one letter allowed! + if a_seen_letter = 't' then + return -1; + -- raise_application_error(-20000, 'Not allowed to have two letters in version name ''' + -- || version_name || ''''); + end if; + a_seen_letter := 't'; - -- end of string - we're done! - if a_end = length(version_name) then - return a_order; - end if; - end if; - a_start := a_end + 1; - end loop; + -- end of string - we're done! + if a_end = length(version_name) then + return a_order; + end if; + end if; + a_start := a_end + 1; + end loop; end sortable_version_name; function version_name_greater( - version_name_one in apm_package_versions.version_name%TYPE, - version_name_two in apm_package_versions.version_name%TYPE + version_name_one in apm_package_versions.version_name%TYPE, + version_name_two in apm_package_versions.version_name%TYPE ) return integer is - a_order_a varchar2(1000); - a_order_b varchar2(1000); + a_order_a varchar2(1000); + a_order_b varchar2(1000); begin - a_order_a := sortable_version_name(version_name_one); - a_order_b := sortable_version_name(version_name_two); - if a_order_a < a_order_b then - return -1; - elsif a_order_a > a_order_b then - return 1; - end if; - return 0; + a_order_a := sortable_version_name(version_name_one); + a_order_b := sortable_version_name(version_name_two); + if a_order_a < a_order_b then + return -1; + elsif a_order_a > a_order_b then + return 1; + end if; + return 0; end version_name_greater; function upgrade_p( - path in varchar2, - initial_version_name in apm_package_versions.version_name%TYPE, - final_version_name in apm_package_versions.version_name%TYPE + path in varchar2, + initial_version_name in apm_package_versions.version_name%TYPE, + final_version_name in apm_package_versions.version_name%TYPE ) return integer is - v_pos1 integer; - v_pos2 integer; - v_path varchar2(1500); - v_version_from apm_package_versions.version_name%TYPE; - v_version_to apm_package_versions.version_name%TYPE; + v_pos1 integer; + v_pos2 integer; + v_path varchar2(1500); + v_version_from apm_package_versions.version_name%TYPE; + v_version_to apm_package_versions.version_name%TYPE; begin - -- Set v_path to the tail of the path (the file name). - v_path := substr(upgrade_p.path, instr(upgrade_p.path, '/', -1) + 1); + -- Set v_path to the tail of the path (the file name). + v_path := substr(upgrade_p.path, instr(upgrade_p.path, '/', -1) + 1); - -- Remove the extension, if it's .sql. - v_pos1 := instr(v_path, '.', -1); - if v_pos1 > 0 and substr(v_path, v_pos1) = '.sql' then - v_path := substr(v_path, 1, v_pos1 - 1); - end if; + -- Remove the extension, if it's .sql. + v_pos1 := instr(v_path, '.', -1); + if v_pos1 > 0 and substr(v_path, v_pos1) = '.sql' then + v_path := substr(v_path, 1, v_pos1 - 1); + end if; - -- Figure out the from/to version numbers for the individual file. - v_pos1 := instr(v_path, '-', -1, 2); - v_pos2 := instr(v_path, '-', -1); - if v_pos1 = 0 or v_pos2 = 0 then - -- There aren't two hyphens in the file name. Bail. - return 0; - end if; + -- Figure out the from/to version numbers for the individual file. + v_pos1 := instr(v_path, '-', -1, 2); + v_pos2 := instr(v_path, '-', -1); + if v_pos1 = 0 or v_pos2 = 0 then + -- There aren't two hyphens in the file name. Bail. + return 0; + end if; - v_version_from := substr(v_path, v_pos1 + 1, v_pos2 - v_pos1 - 1); - v_version_to := substr(v_path, v_pos2 + 1); + v_version_from := substr(v_path, v_pos1 + 1, v_pos2 - v_pos1 - 1); + v_version_to := substr(v_path, v_pos2 + 1); - if version_name_greater(upgrade_p.initial_version_name, v_version_from) <= 0 and - version_name_greater(upgrade_p.final_version_name, v_version_to) >= 0 then - return 1; - end if; + if version_name_greater(upgrade_p.initial_version_name, v_version_from) <= 0 and + version_name_greater(upgrade_p.final_version_name, v_version_to) >= 0 then + return 1; + end if; - return 0; + return 0; exception when others then - -- Invalid version number. - return 0; + -- Invalid version number. + return 0; end upgrade_p; procedure upgrade( @@ -549,17 +552,311 @@ is begin update apm_package_versions - set enabled_p = 'f', - installed_p = 'f' - where package_key = (select package_key from apm_package_versions - where version_id = upgrade.version_id); + set enabled_p = 'f', + installed_p = 'f' + where package_key = (select package_key from apm_package_versions + where version_id = upgrade.version_id); update apm_package_versions - set enabled_p = 't', - installed_p = 't' - where version_id = upgrade.version_id; + set enabled_p = 't', + installed_p = 't' + where version_id = upgrade.version_id; end upgrade; end apm_package_version; / show errors + +create or replace package apm_package +as + +function new ( + package_id in apm_packages.package_id%TYPE + default null, + instance_name in apm_packages.instance_name%TYPE + default null, + package_key in apm_packages.package_key%TYPE, + object_type in acs_objects.object_type%TYPE + default 'apm_package', + creation_date in acs_objects.creation_date%TYPE + default sysdate, + creation_user in acs_objects.creation_user%TYPE + default null, + creation_ip in acs_objects.creation_ip%TYPE + default null, + context_id in acs_objects.context_id%TYPE + default null + ) return apm_packages.package_id%TYPE; + + procedure delete ( + package_id in apm_packages.package_id%TYPE + ); + + function initial_install_p ( + package_key in apm_packages.package_key%TYPE + ) return integer; + + function singleton_p ( + package_key in apm_packages.package_key%TYPE + ) return integer; + + function num_instances ( + package_key in apm_package_types.package_key%TYPE + ) return integer; + + function name ( + package_id in apm_packages.package_id%TYPE + ) return varchar2; + + function highest_version ( + package_key in apm_package_types.package_key%TYPE + ) return apm_package_versions.version_id%TYPE; + + function parent_id ( + package_id in apm_packages.package_id%TYPE + ) return apm_packages.package_id%TYPE; + +end apm_package; +/ +show errors + +create or replace package body apm_package +as + procedure initialize_parameters ( + package_id in apm_packages.package_id%TYPE, + package_key in apm_package_types.package_key%TYPE + ) + is + v_value_id apm_parameter_values.value_id%TYPE; + cursor cur is + select parameter_id, default_value + from apm_parameters + where package_key = initialize_parameters.package_key; + begin + -- need to initialize all params for this type + for cur_val in cur + loop + v_value_id := apm_parameter_value.new( + package_id => initialize_parameters.package_id, + parameter_id => cur_val.parameter_id, + attr_value => cur_val.default_value + ); + end loop; + end initialize_parameters; + + function new ( + package_id in apm_packages.package_id%TYPE + default null, + instance_name in apm_packages.instance_name%TYPE + default null, + package_key in apm_packages.package_key%TYPE, + object_type in acs_objects.object_type%TYPE + default 'apm_package', + creation_date in acs_objects.creation_date%TYPE + default sysdate, + creation_user in acs_objects.creation_user%TYPE + default null, + creation_ip in acs_objects.creation_ip%TYPE + default null, + context_id in acs_objects.context_id%TYPE + default null + ) return apm_packages.package_id%TYPE + is + v_singleton_p integer; + v_package_type apm_package_types.package_type%TYPE; + v_num_instances integer; + v_package_id apm_packages.package_id%TYPE; + v_instance_name apm_packages.instance_name%TYPE; + begin + v_singleton_p := apm_package.singleton_p( + package_key => apm_package.new.package_key + ); + v_num_instances := apm_package.num_instances( + package_key => apm_package.new.package_key + ); + + if v_singleton_p = 1 and v_num_instances >= 1 then + select package_id into v_package_id + from apm_packages + where package_key = apm_package.new.package_key; + return v_package_id; + else + v_package_id := acs_object.new( + object_id => package_id, + object_type => object_type, + creation_date => creation_date, + creation_user => creation_user, + creation_ip => creation_ip, + context_id => context_id + ); + if instance_name is null then + v_instance_name := package_key || ' ' || v_package_id; + else + v_instance_name := instance_name; + end if; + + select package_type into v_package_type + from apm_package_types + where package_key = apm_package.new.package_key; + + insert into apm_packages + (package_id, package_key, instance_name) + values + (v_package_id, package_key, v_instance_name); + + if v_package_type = 'apm_application' then + insert into apm_applications + (application_id) + values + (v_package_id); + else + insert into apm_services + (service_id) + values + (v_package_id); + end if; + + initialize_parameters( + package_id => v_package_id, + package_key => apm_package.new.package_key + ); + return v_package_id; + + end if; +end new; + + procedure delete ( + package_id in apm_packages.package_id%TYPE + ) + is + cursor all_values is + select value_id from apm_parameter_values + where package_id = apm_package.delete.package_id; + cursor all_site_nodes is + select node_id from site_nodes + where object_id = apm_package.delete.package_id; + begin + -- Delete all parameters. + for cur_val in all_values loop + apm_parameter_value.delete(value_id => cur_val.value_id); + end loop; + delete from apm_applications where application_id = apm_package.delete.package_id; + delete from apm_services where service_id = apm_package.delete.package_id; + delete from apm_packages where package_id = apm_package.delete.package_id; + -- Delete the site nodes for the objects. + for cur_val in all_site_nodes loop + site_node.delete(cur_val.node_id); + end loop; + -- Delete the object. + acs_object.delete ( + object_id => package_id + ); + end delete; + + function initial_install_p ( + package_key in apm_packages.package_key%TYPE + ) return integer + is + v_initial_install_p integer; + begin + select 1 into v_initial_install_p + from apm_package_types + where package_key = initial_install_p.package_key + and initial_install_p = 't'; + return v_initial_install_p; + + exception + when NO_DATA_FOUND + then + return 0; + end initial_install_p; + + function singleton_p ( + package_key in apm_packages.package_key%TYPE + ) return integer + is + v_singleton_p integer; + begin + select 1 into v_singleton_p + from apm_package_types + where package_key = singleton_p.package_key + and singleton_p = 't'; + return v_singleton_p; + + exception + when NO_DATA_FOUND + then + return 0; + end singleton_p; + + function num_instances ( + package_key in apm_package_types.package_key%TYPE + ) return integer + is + v_num_instances integer; + begin + select count(*) into v_num_instances + from apm_packages + where package_key = num_instances.package_key; + return v_num_instances; + + exception + when NO_DATA_FOUND + then + return 0; + end num_instances; + + function name ( + package_id in apm_packages.package_id%TYPE + ) return varchar2 + is + v_result apm_packages.instance_name%TYPE; + begin + select instance_name into v_result + from apm_packages + where package_id = name.package_id; + + return v_result; + end name; + + function highest_version ( + package_key in apm_package_types.package_key%TYPE + ) return apm_package_versions.version_id%TYPE + is + v_version_id apm_package_versions.version_id%TYPE; + begin + select version_id into v_version_id + from apm_package_version_info i + where apm_package_version.sortable_version_name(version_name) = + (select max(apm_package_version.sortable_version_name(v.version_name)) + from apm_package_version_info v where v.package_key = highest_version.package_key) + and package_key = highest_version.package_key; + return v_version_id; + exception + when NO_DATA_FOUND + then + return 0; + end highest_version; + + function parent_id ( + package_id in apm_packages.package_id%TYPE + ) return apm_packages.package_id%TYPE + is + v_package_id apm_packages.package_id%TYPE; + begin + select sn1.object_id + into v_package_id + from site_nodes sn1 + where sn1.node_id = (select sn2.parent_id + from site_nodes sn2 + where sn2.object_id = apm_package.parent_id.package_id); + + return v_package_id; + + exception when NO_DATA_FOUND then + return -1; + end parent_id; + +end apm_package; +/ +show errors