Index: openacs-4/packages/acs-kernel/sql/oracle/acs-objects-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/oracle/acs-objects-create.sql,v diff -u -N -r1.6.2.4 -r1.6.2.5 --- openacs-4/packages/acs-kernel/sql/oracle/acs-objects-create.sql 15 Feb 2003 22:30:32 -0000 1.6.2.4 +++ openacs-4/packages/acs-kernel/sql/oracle/acs-objects-create.sql 23 Jun 2003 18:47:02 -0000 1.6.2.5 @@ -430,6 +430,8 @@ procedure update_last_modified ( object_id in acs_objects.object_id%TYPE, + modifying_user in acs_objects.modifying_user%TYPE, + modifying_ip in acs_objects.modifying_ip%TYPE, last_modified in acs_objects.last_modified%TYPE default sysdate ); @@ -1047,13 +1049,15 @@ procedure update_last_modified ( object_id in acs_objects.object_id%TYPE, + modifying_user in acs_objects.modifying_user%TYPE, + modifying_ip in acs_objects.modifying_ip%TYPE, last_modified in acs_objects.last_modified%TYPE default sysdate ) is v_parent_id acs_objects.context_id%TYPE; begin update acs_objects - set acs_objects.last_modified = acs_object.update_last_modified.last_modified + set acs_objects.last_modified = acs_object.update_last_modified.last_modified, acs_objects.modifying_user = acs_object.update_last_modified.modifying_user, acs_objects.modifying_ip = acs_object.update_last_modified.modifying_ip where acs_objects.object_id in (select ao.object_id from acs_objects ao connect by prior ao.context_id = ao.object_id Index: openacs-4/packages/acs-kernel/sql/oracle/upgrade/upgrade-4.6.4-4.6.5.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/oracle/upgrade/upgrade-4.6.4-4.6.5.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-kernel/sql/oracle/upgrade/upgrade-4.6.4-4.6.5.sql 23 Jun 2003 18:47:02 -0000 1.1.2.1 @@ -0,0 +1,702 @@ +-- The only change is to update_last_modified, to add the user's id and IP +-- address. +-- $Id + +create or replace package acs_object +as + + function new ( + object_id in acs_objects.object_id%TYPE default null, + object_type in acs_objects.object_type%TYPE + default 'acs_object', + 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 acs_objects.object_id%TYPE; + + procedure delete ( + object_id in acs_objects.object_id%TYPE + ); + + function name ( + object_id in acs_objects.object_id%TYPE + ) return varchar2; + + -- The acs_object_types.name_method for "acs_object" + -- + function default_name ( + object_id in acs_objects.object_id%TYPE + ) return varchar2; + + -- Determine where the attribute is stored and what sql needs to be + -- in the where clause to retreive it + -- Used in get_attribute and set_attribute + procedure get_attribute_storage ( + object_id_in in acs_objects.object_id%TYPE, + attribute_name_in in acs_attributes.attribute_name%TYPE, + v_column out varchar2, + v_table_name out varchar2, + v_key_sql out varchar2 + ); + + -- Get/set the value of an object attribute, as long as + -- the type can be cast to varchar2 + function get_attribute ( + object_id_in in acs_objects.object_id%TYPE, + attribute_name_in in acs_attributes.attribute_name%TYPE + ) return varchar2; + + procedure set_attribute ( + object_id_in in acs_objects.object_id%TYPE, + attribute_name_in in acs_attributes.attribute_name%TYPE, + value_in in varchar2 + ); + + function check_representation ( + object_id in acs_objects.object_id%TYPE + ) return char; + + procedure update_last_modified ( + object_id in acs_objects.object_id%TYPE, + modifying_user in acs_objects.modifying_user%TYPE, + modifying_ip in acs_objects.modifying_ip%TYPE, + last_modified in acs_objects.last_modified%TYPE default sysdate + ); + +end acs_object; +/ +show errors + +create or replace package body acs_object +as + + procedure initialize_attributes ( + object_id in acs_objects.object_id%TYPE + ) + is + v_object_type acs_objects.object_type%TYPE; + begin + -- XXX This should be fixed to initialize supertypes properly. + + -- Initialize dynamic attributes + insert into acs_attribute_values + (object_id, attribute_id, attr_value) + select + initialize_attributes.object_id, a.attribute_id, a.default_value + from acs_attributes a, acs_objects o + where a.object_type = o.object_type + and o.object_id = initialize_attributes.object_id + and a.storage = 'generic' + and a.static_p = 'f'; + + -- Retreive type for static attributes + select object_type into v_object_type from acs_objects + where object_id = initialize_attributes.object_id; + + -- Initialize static attributes + begin + insert into acs_static_attr_values + (object_type, attribute_id, attr_value) + select + v_object_type, a.attribute_id, a.default_value + from acs_attributes a, acs_objects o + where a.object_type = o.object_type + and o.object_id = initialize_attributes.object_id + and a.storage = 'generic' + and a.static_p = 't' + and not exists (select 1 from acs_static_attr_values + where object_type = a.object_type); + exception when no_data_found then null; + end; + + end initialize_attributes; + + function new ( + object_id in acs_objects.object_id%TYPE default null, + object_type in acs_objects.object_type%TYPE + default 'acs_object', + 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 acs_objects.object_id%TYPE + is + v_object_id acs_objects.object_id%TYPE; + begin + if object_id is null then + select acs_object_id_seq.nextval + into v_object_id + from dual; + else + v_object_id := object_id; + end if; + + insert into acs_objects + (object_id, object_type, context_id, + creation_date, creation_user, creation_ip) + values + (v_object_id, object_type, context_id, + creation_date, creation_user, creation_ip); + + acs_object.initialize_attributes(v_object_id); + + return v_object_id; + end new; + + procedure delete ( + object_id in acs_objects.object_id%TYPE + ) + is + v_exists_p char; + begin + + -- Delete dynamic/generic attributes + delete from acs_attribute_values where object_id = acs_object.delete.object_id; + + -- Delete directly assigned permissions + -- + -- JCD: We do this as an execute rather than just a direct query since + -- the acs_permissions table is not created when this file is + -- sourced. We need to clean up the creates and once that is done + -- we can turn this into a simple delete statement. + -- + execute immediate 'delete from acs_permissions where object_id = :object_id' + using in object_id; + + for object_type + in (select table_name, id_column + from acs_object_types + start with object_type = (select object_type + from acs_objects o + where o.object_id = acs_object.delete.object_id) + connect by object_type = prior supertype) + loop + -- Delete from the table if it exists. + select decode(count(*),0,'f','t') into v_exists_p + from user_tables + where table_name = upper(object_type.table_name); + + if v_exists_p = 't' then + execute immediate 'delete from ' || object_type.table_name || + ' where ' || object_type.id_column || ' = :object_id' + using in object_id; + end if; + + end loop; + + end delete; + + function name ( + object_id in acs_objects.object_id%TYPE + ) + return varchar2 + is + object_name varchar2(500); + v_object_id integer := object_id; + begin + -- Find the name function for this object, which is stored in the + -- name_method column of acs_object_types. Starting with this + -- object's actual type, traverse the type hierarchy upwards until + -- a non-null name_method value is found. + -- + for object_type + in (select name_method + from acs_object_types + start with object_type = (select object_type + from acs_objects o + where o.object_id = name.object_id) + connect by object_type = prior supertype) + loop + if object_type.name_method is not null then + + -- Execute the first name_method we find (since we're traversing + -- up the type hierarchy from the object's exact type) using + -- Native Dynamic SQL, to ascertain the name of this object. + -- + --execute immediate 'select ' || object_type.name_method || '(:1) from dual' + execute immediate 'begin :1 := ' || object_type.name_method || '(:2); end;' + using out object_name, in object_id; + --into object_name + + exit; + end if; + end loop; + + return object_name; + end name; + + function default_name ( + object_id in acs_objects.object_id%TYPE + ) return varchar2 + is + object_type_pretty_name acs_object_types.pretty_name%TYPE; + begin + select ot.pretty_name + into object_type_pretty_name + from acs_objects o, acs_object_types ot + where o.object_id = default_name.object_id + and o.object_type = ot.object_type; + + return object_type_pretty_name || ' ' || object_id; + end default_name; + + procedure get_attribute_storage ( + object_id_in in acs_objects.object_id%TYPE, + attribute_name_in in acs_attributes.attribute_name%TYPE, + v_column out varchar2, + v_table_name out varchar2, + v_key_sql out varchar2 + ) + is + v_object_type acs_attributes.object_type%TYPE; + v_static acs_attributes.static_p%TYPE := null; + v_attr_id acs_attributes.attribute_id%TYPE := null; + v_storage acs_attributes.storage%TYPE := null; + v_attr_name acs_attributes.attribute_name%TYPE := null; + v_id_column varchar2(200) := null; + v_sql varchar2(4000) := null; + v_return varchar2(4000) := null; + + -- Fetch the most inherited attribute + cursor c_attribute is + select + a.attribute_id, a.static_p, a.storage, a.table_name, a.attribute_name, + a.object_type, a.column_name, t.id_column + from + acs_attributes a, + (select + object_type, id_column + from + acs_object_types + connect by + object_type = prior supertype + start with + object_type = (select object_type from acs_objects + where object_id = object_id_in) + ) t + where + a.attribute_name = attribute_name_in + and + a.object_type = t.object_type; + + begin + + -- Determine the attribute parameters + open c_attribute; + fetch c_attribute into + v_attr_id, v_static, v_storage, v_table_name, v_attr_name, + v_object_type, v_column, v_id_column; + if c_attribute%NOTFOUND then + close c_attribute; + raise_application_error (-20000, + 'No such attribute ' || v_object_type || '::' || attribute_name_in || + ' in acs_object.get_attribute_storage.'); + end if; + close c_attribute; + + -- This should really be done in a trigger on acs_attributes, + -- instead of generating it each time in this function + + -- If there is no specific table name for this attribute, + -- figure it out based on the object type + if v_table_name is null then + + -- Determine the appropriate table name + if v_storage = 'generic' then + -- Generic attribute: table name/column are hardcoded + + v_column := 'attr_value'; + + if v_static = 'f' then + v_table_name := 'acs_attribute_values'; + v_key_sql := '(object_id = ' || object_id_in || ' and ' || + 'attribute_id = ' || v_attr_id || ')'; + else + v_table_name := 'acs_static_attr_values'; + v_key_sql := '(object_type = ''' || v_object_type || ''' and ' || + 'attribute_id = ' || v_attr_id || ')'; + end if; + + else + -- Specific attribute: table name/column need to be retreived + + if v_static = 'f' then + select + table_name, id_column + into + v_table_name, v_id_column + from + acs_object_types + where + object_type = v_object_type; + else + raise_application_error(-20000, + 'No table name specified for storage specific static attribute ' || + v_object_type || '::' || attribute_name_in || + ' in acs_object.get_attribute_storage.'); + end if; + + end if; + else + -- There is a custom table name for this attribute. + -- Get the id column out of the acs_object_tables + -- Raise an error if not found + select id_column into v_id_column from acs_object_type_tables + where object_type = v_object_type + and table_name = v_table_name; + + end if; + + if v_column is null then + + if v_storage = 'generic' then + v_column := 'attr_value'; + else + v_column := v_attr_name; + end if; + + end if; + + if v_key_sql is null then + if v_static = 'f' then + v_key_sql := v_id_column || ' = ' || object_id_in ; + else + v_key_sql := v_id_column || ' = ''' || v_object_type || ''''; + end if; + end if; + + exception when no_data_found then + if c_attribute%ISOPEN then + close c_attribute; + end if; + raise_application_error(-20000, 'No data found for attribute ' || + v_object_type || '::' || attribute_name_in || + ' in acs_object.get_attribute_storage'); + + end get_attribute_storage; + + -- Get/set the value of an object attribute, as long as + -- the type can be cast to varchar2 + function get_attribute ( + object_id_in in acs_objects.object_id%TYPE, + attribute_name_in in acs_attributes.attribute_name%TYPE + ) return varchar2 + is + v_table_name varchar2(200); + v_column varchar2(200); + v_key_sql varchar2(4000); + v_return varchar2(4000); + begin + + get_attribute_storage(object_id_in, attribute_name_in, + v_column, v_table_name, v_key_sql); + + begin + execute immediate 'select ' + || v_column || ' from ' || v_table_name || ' where ' || v_key_sql + into + v_return; + exception when no_data_found then + return null; + end; + + return v_return; + end get_attribute; + + procedure set_attribute ( + object_id_in in acs_objects.object_id%TYPE, + attribute_name_in in acs_attributes.attribute_name%TYPE, + value_in in varchar2 + ) + is + v_table_name varchar2(200); + v_column varchar2(200); + v_key_sql varchar2(4000); + v_return varchar2(4000); + v_dummy integer; + begin + + get_attribute_storage(object_id_in, attribute_name_in, + v_column, v_table_name, v_key_sql); + + execute immediate 'update ' + || v_table_name || ' set ' || v_column || ' = :value where ' || v_key_sql + using value_in; + + end set_attribute; + + function check_context_index ( + object_id in acs_objects.object_id%TYPE, + ancestor_id in acs_objects.object_id%TYPE, + n_generations in integer + ) return char + is + n_rows integer; + n_gens integer; + begin + -- Verify that this row exists in the index. + select decode(count(*),0,0,1) into n_rows + from acs_object_context_index + where object_id = check_context_index.object_id + and ancestor_id = check_context_index.ancestor_id; + + if n_rows = 1 then + -- Verify that the count is correct. + select n_generations into n_gens + from acs_object_context_index + where object_id = check_context_index.object_id + and ancestor_id = check_context_index.ancestor_id; + + if n_gens != n_generations then + acs_log.error('acs_object.check_representation', 'Ancestor ' || + ancestor_id || ' of object ' || object_id || + ' reports being generation ' || n_gens || + ' when it is actually generation ' || n_generations || + '.'); + return 'f'; + else + return 't'; + end if; + else + acs_log.error('acs_object.check_representation', 'Ancestor ' || + ancestor_id || ' of object ' || object_id || + ' is missing an entry in acs_object_context_index.'); + return 'f'; + end if; + end; + + function check_object_ancestors ( + object_id in acs_objects.object_id%TYPE, + ancestor_id in acs_objects.object_id%TYPE, + n_generations in integer + ) return char + is + context_id acs_objects.context_id%TYPE; + security_inherit_p acs_objects.security_inherit_p%TYPE; + n_rows integer; + n_gens integer; + result char(1); + begin + -- OBJECT_ID is the object we are verifying + -- ANCESTOR_ID is the current ancestor we are tracking + -- N_GENERATIONS is how far ancestor_id is from object_id + + -- Note that this function is only supposed to verify that the + -- index contains each ancestor for OBJECT_ID. It doesn''t + -- guarantee that there aren''t extraneous rows or that + -- OBJECT_ID''s children are contained in the index. That is + -- verified by seperate functions. + + result := 't'; + + -- Grab the context and security_inherit_p flag of the current + -- ancestor''s parent. + select context_id, security_inherit_p into context_id, security_inherit_p + from acs_objects + where object_id = check_object_ancestors.ancestor_id; + + if ancestor_id = 0 then + if context_id is null then + result := 't'; + else + -- This can be a constraint, can''t it? + acs_log.error('acs_object.check_representation', + 'Object 0 doesn''t have a null context_id'); + result := 'f'; + end if; + else + if context_id is null or security_inherit_p = 'f' then + context_id := 0; + end if; + + if check_context_index(object_id, ancestor_id, n_generations) = 'f' then + result := 'f'; + end if; + + if check_object_ancestors(object_id, context_id, + n_generations + 1) = 'f' then + result := 'f'; + end if; + end if; + + return result; + end; + + function check_object_descendants ( + object_id in acs_objects.object_id%TYPE, + descendant_id in acs_objects.object_id%TYPE, + n_generations in integer + ) return char + is + result char(1); + begin + -- OBJECT_ID is the object we are verifying. + -- DESCENDANT_ID is the current descendant we are tracking. + -- N_GENERATIONS is how far the current DESCENDANT_ID is from + -- OBJECT_ID. + + -- This function will verfy that each actualy descendant of + -- OBJECT_ID has a row in the index table. It does not check that + -- there aren't extraneous rows or that the ancestors of OBJECT_ID + -- are maintained correctly. + + result := 't'; + + -- First verify that OBJECT_ID and DESCENDANT_ID are actually in + -- the index. + if check_context_index(descendant_id, object_id, n_generations) = 'f' then + result := 'f'; + end if; + + -- For every child that reports inheriting from OBJECT_ID we need to call + -- ourselves recursively. + for obj in (select * + from acs_objects + where context_id = descendant_id + and security_inherit_p = 't') loop + if check_object_descendants(object_id, obj.object_id, + n_generations + 1) = 'f' then + result := 'f'; + end if; + end loop; + + return result; + end; + + function check_path ( + object_id in acs_objects.object_id%TYPE, + ancestor_id in acs_objects.object_id%TYPE + ) return char + is + context_id acs_objects.context_id%TYPE; + security_inherit_p acs_objects.security_inherit_p%TYPE; + begin + if object_id = ancestor_id then + return 't'; + end if; + + select context_id, security_inherit_p into context_id, security_inherit_p + from acs_objects + where object_id = check_path.object_id; + + if context_id is null or security_inherit_p = 'f' then + context_id := 0; + end if; + + return check_path(context_id, ancestor_id); + end; + + function check_representation ( + object_id in acs_objects.object_id%TYPE + ) return char + is + result char(1); + object_type acs_objects.object_type%TYPE; + n_rows integer; + begin + result := 't'; + acs_log.notice('acs_object.check_representation', + 'Running acs_object.check_representation on object_id = ' || + object_id || '.'); + + -- If this fails then there isn''t even an object associated with + -- this id. I'm going to let that error propogate as an exception. + select object_type into object_type + from acs_objects + where object_id = check_representation.object_id; + + acs_log.notice('acs_object.check_representation', + 'OBJECT STORAGE INTEGRITY TEST'); + + -- Let's look through every primary storage table associated with + -- this object type and all of its supertypes and make sure there + -- is a row with OBJECT_ID as theh primary key. + for t in (select t.object_type, t.table_name, t.id_column + from acs_object_type_supertype_map m, acs_object_types t + where m.ancestor_type = t.object_type + and m.object_type = check_representation.object_type + union + select object_type, table_name, id_column + from acs_object_types + where object_type = check_representation.object_type) loop + execute immediate 'select decode(count(*),0,0,1) from ' || t.table_name || + ' where ' || t.id_column || ' = ' || object_id + into n_rows; + + if n_rows = 0 then + result := 'f'; + acs_log.error('acs_object.check_representation', + 'Table ' || t.table_name || ' (primary storage for ' || + t.object_type || ') doesn''t have a row for object ' || + object_id || ' of type ' || object_type || '.'); + end if; + end loop; + + acs_log.notice('acs_object.check_representation', + 'OBJECT CONTEXT INTEGRITY TEST'); + + -- Do a bunch of dirt simple sanity checks. + + -- First let's check that all of our ancestors appear in + -- acs_object_context_index with the correct generation listed. + if check_object_ancestors(object_id, object_id, 0) = 'f' then + result := 'f'; + end if; + + -- Now let's check that all of our descendants appear in + -- acs_object_context_index with the correct generation listed. + if check_object_descendants(object_id, object_id, 0) = 'f' then + result := 'f'; + end if; + + -- Ok, we know that the index contains every entry that it is + -- supposed to have. Now let's make sure it doesn't contain any + -- extraneous entries. + for row in (select * + from acs_object_context_index + where object_id = check_representation.object_id + or ancestor_id = check_representation.object_id) loop + if check_path(row.object_id, row.ancestor_id) = 'f' then + acs_log.error('acs_object.check_representation', + 'acs_object_context_index contains an extraneous row: ' || + 'object_id = ' || row.object_id || ', ancestor_id = ' || + row.ancestor_id || ', n_generations = ' || + row.n_generations || '.'); + result := 'f'; + end if; + end loop; + + acs_log.notice('acs_object.check_representation', + 'Done running acs_object.check_representation ' || + 'on object_id = ' || object_id || '.'); + return result; + end check_representation; + + procedure update_last_modified ( + object_id in acs_objects.object_id%TYPE, + modifying_user in acs_objects.modifying_user%TYPE, + modifying_ip in acs_objects.modifying_ip%TYPE, + last_modified in acs_objects.last_modified%TYPE default sysdate + ) + is + v_parent_id acs_objects.context_id%TYPE; + begin + update acs_objects + set acs_objects.last_modified = acs_object.update_last_modified.last_modified, acs_objects.modifying_user = acs_object.update_last_modified.modifying_user, acs_objects.modifying_ip = acs_object.update_last_modified.modifying_ip + where acs_objects.object_id in (select ao.object_id + from acs_objects ao + connect by prior ao.context_id = ao.object_id + start with ao.object_id = acs_object.update_last_modified.object_id) + and acs_objects.context_id is not null + and acs_objects.object_id != 0; + end update_last_modified; + +end acs_object; +/ +show errors Index: openacs-4/packages/acs-kernel/sql/postgresql/acs-objects-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/postgresql/acs-objects-create.sql,v diff -u -N -r1.35.2.4 -r1.35.2.5 --- openacs-4/packages/acs-kernel/sql/postgresql/acs-objects-create.sql 2 Mar 2003 22:33:33 -0000 1.35.2.4 +++ openacs-4/packages/acs-kernel/sql/postgresql/acs-objects-create.sql 23 Jun 2003 18:47:02 -0000 1.35.2.5 @@ -1340,21 +1340,25 @@ end;' language 'plpgsql'; -create function acs_object__update_last_modified (integer) +create function acs_object__update_last_modified (integer, integer, integer) returns integer as ' declare - acs_object__update_last_modified__object_id alias for $1; + acs_object__update_last_modified__object_id alias for $1; + acs_object__update_last_modified__modifying_user alias for $2; + acs_object__update_last_modified__modifying_ip alias for $3; begin - return acs_object__update_last_modified(acs_object__update_last_modified__object_id, now()); + return acs_object__update_last_modified(acs_object__update_last_modified__object_id, acs_object__update_last_modified__modifying_user, acs_object__update_last_modified__modifying_ip, now()); end;' language 'plpgsql'; -create function acs_object__update_last_modified (integer, timestamptz) +create function acs_object__update_last_modified (integer, integer, integer, timestamptz) returns integer as ' declare - acs_object__update_last_modified__object_id alias for $1; - acs_object__update_last_modified__last_modified alias for $2; -- default now() - v_parent_id integer; - v_last_modified timestamptz; + acs_object__update_last_modified__object_id alias for $1; + acs_object__update_last_modified__modifying_user alias for $2; + acs_object__update_last_modified__modifying_ip alias for $3; + acs_object__update_last_modified__last_modified alias for $4; -- default now() + v_parent_id integer; + v_last_modified timestamptz; begin if acs_object__update_last_modified__last_modified is null then v_last_modified := now(); @@ -1363,7 +1367,9 @@ end if; update acs_objects - set last_modified = v_last_modified + set last_modified = v_last_modified, + modifying_user = acs_object__update_last_modified__modifying_user, + modifying_ip = acs_object__update_last_modified__modifying_ip where object_id = acs_object__update_last_modified__object_id; select context_id @@ -1372,7 +1378,7 @@ where object_id = acs_object__update_last_modified__object_id; if v_parent_id is not null and v_parent_id != 0 then - perform acs_object__update_last_modified(v_parent_id, v_last_modified); + perform acs_object__update_last_modified(v_parent_id, acs_object__update_last_modified__modifying_user, acs_object__update_last_modified__modifying_ip, v_last_modified); end if; return acs_object__update_last_modified__object_id; Index: openacs-4/packages/acs-kernel/sql/postgresql/upgrade/upgrade-4.6.4-4.6.5.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/postgresql/upgrade/upgrade-4.6.4-4.6.5.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-kernel/sql/postgresql/upgrade/upgrade-4.6.4-4.6.5.sql 23 Jun 2003 18:47:02 -0000 1.1.2.1 @@ -0,0 +1,47 @@ +-- Add user id and IP address to update_last_modified() +-- $Id + +create or replace function acs_object__update_last_modified (integer, integer, integer) +returns integer as ' +declare + acs_object__update_last_modified__object_id alias for $1; + acs_object__update_last_modified__modifying_user alias for $2; + acs_object__update_last_modified__modifying_ip alias for $3; +begin + return acs_object__update_last_modified(acs_object__update_last_modified__object_id, acs_object__update_last_modified__modifying_user, acs_object__update_last_modified__modifying_ip, now()); +end;' language 'plpgsql'; + +create or replace function acs_object__update_last_modified (integer, integer, integer, timestamptz) +returns integer as ' +declare + acs_object__update_last_modified__object_id alias for $1; + acs_object__update_last_modified__modifying_user alias for $2; + acs_object__update_last_modified__modifying_ip alias for $3; + acs_object__update_last_modified__last_modified alias for $4; -- default now() + v_parent_id integer; + v_last_modified timestamptz; +begin + if acs_object__update_last_modified__last_modified is null then + v_last_modified := now(); + else + v_last_modified := acs_object__update_last_modified__last_modified; + end if; + + update acs_objects + set last_modified = v_last_modified, + modifying_user = acs_object__update_last_modified__modifying_user, + modifying_ip = acs_object__update_last_modified__modifying_ip + where object_id = acs_object__update_last_modified__object_id; + + select context_id + into v_parent_id + from acs_objects + where object_id = acs_object__update_last_modified__object_id; + + if v_parent_id is not null and v_parent_id != 0 then + perform acs_object__update_last_modified(v_parent_id, acs_object__update_last_modified__modifying_user, acs_object__update_last_modified__modifying_ip, v_last_modified); + end if; + + return acs_object__update_last_modified__object_id; +end;' language 'plpgsql'; + Index: openacs-4/packages/file-storage/sql/oracle/file-storage-package-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/file-storage/sql/oracle/file-storage-package-create.sql,v diff -u -N -r1.4.2.3 -r1.4.2.4 --- openacs-4/packages/file-storage/sql/oracle/file-storage-package-create.sql 21 Mar 2003 14:49:32 -0000 1.4.2.3 +++ openacs-4/packages/file-storage/sql/oracle/file-storage-package-create.sql 23 Jun 2003 18:47:02 -0000 1.4.2.4 @@ -75,7 +75,9 @@ -- Move a file, and all its versions, to a new folder -- file_id in cr_items.item_id%TYPE, - target_folder_id in cr_items.parent_id%TYPE + target_folder_id in cr_items.parent_id%TYPE, + creation_user in acs_objects.creation_user%TYPE, + creation_ip in acs_objects.creation_ip%TYPE ); function get_title( @@ -305,7 +307,7 @@ ); end if; - acs_object.update_last_modified(file_storage.new_file.folder_id); + acs_object.update_last_modified(file_storage.new_file.folder_id,new_file.creation_user,new_file.creation_ip); return v_item_id; end new_file; @@ -400,7 +402,7 @@ content_length = v_content_length where revision_id = v_new_version_id; - acs_object.update_last_modified(file_storage.copy_file.target_folder_id); + acs_object.update_last_modified(file_storage.copy_file.target_folder_id,file_storage.copy_file.creation_user,file_storage.copy_file.creation_ip); return v_new_version_id; end copy_file; @@ -410,7 +412,9 @@ -- Move a file, and all its versions, to a new folder -- file_id in cr_items.item_id%TYPE, - target_folder_id in cr_items.parent_id%TYPE + target_folder_id in cr_items.parent_id%TYPE, + creation_user in acs_objects.creation_user%TYPE, + creation_ip in acs_objects.creation_ip%TYPE ) is begin @@ -419,7 +423,7 @@ target_folder_id => file_storage.move_file.target_folder_id ); - acs_object.update_last_modified(file_storage.move_file.target_folder_id); + acs_object.update_last_modified(file_storage.move_file.target_folder_id,file_storage.move_file.creation_user,file_storage.move_file.creation_ip); end; @@ -457,7 +461,7 @@ from cr_items where cr_items.item_id = file_storage.new_version.item_id; - acs_object.update_last_modified(v_folder_id); + acs_object.update_last_modified(v_folder_id,new_version.creation_user,new_version.creation_ip); return v_revision_id; Index: openacs-4/packages/file-storage/sql/oracle/upgrade/upgrade-4.6.2-4.6.3.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/file-storage/sql/oracle/upgrade/upgrade-4.6.2-4.6.3.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/file-storage/sql/oracle/upgrade/upgrade-4.6.2-4.6.3.sql 23 Jun 2003 18:47:02 -0000 1.1.2.1 @@ -0,0 +1,708 @@ +-- Need to recreate package, to add user and IP to update_last_modified +-- $Id + +create or replace package file_storage +as + + function get_root_folder( + -- + -- Returns the root folder corresponding to a particular + -- package instance. + -- + package_id in apm_packages.package_id%TYPE + ) return fs_root_folders.folder_id%TYPE; + + function get_package_id( + item_id in cr_items.item_id%TYPE + ) return fs_root_folders.package_id%TYPE; + + function new_root_folder( + -- + -- Creates a new root folder + -- + package_id in apm_packages.package_id%TYPE, + folder_name in cr_folders.label%TYPE default null, + description in cr_folders.description%TYPE default null + ) return fs_root_folders.folder_id%TYPE; + + function new_file( + -- + -- Create a file in CR in preparation for actual storage + -- Wrapper for content_item.new + -- + item_id in cr_items.item_id%TYPE default null, + title in cr_items.name%TYPE, + folder_id in cr_items.parent_id%TYPE, + creation_user in acs_objects.creation_user%TYPE, + creation_ip in acs_objects.creation_ip%TYPE, + indb_p in char default 't' + ) return cr_items.item_id%TYPE; + + procedure delete_file( + -- + -- Delete a file and all its version + -- Wrapper to content_item.delete + -- + file_id in cr_items.item_id%TYPE + ); + + procedure rename_file( + -- + -- Rename a file and all + -- Wrapper to content_item__rename + -- + file_id in cr_items.item_id%TYPE, + title in cr_items.name%TYPE + ); + + function copy_file( + -- + -- Copy a file, but only copy the live_revision + -- + file_id in cr_items.item_id%TYPE, + target_folder_id in cr_items.parent_id%TYPE, + creation_user in acs_objects.creation_user%TYPE, + creation_ip in acs_objects.creation_ip%TYPE + ) return cr_revisions.revision_id%TYPE; + + procedure move_file( + -- + -- Move a file, and all its versions, to a new folder + -- + file_id in cr_items.item_id%TYPE, + target_folder_id in cr_items.parent_id%TYPE, + creation_user in acs_objects.creation_user%TYPE, + creation_ip in acs_objects.creation_ip%TYPE + ); + + function get_title( + -- + -- Unfortunately, title in the file-storage context refers + -- to the name attribute in cr_items, not the title attribute in + -- cr_revisions + item_id in cr_items.item_id%TYPE + ) return varchar; + + function get_parent_id( + item_id in cr_items.item_id%TYPE + ) return cr_items.item_id%TYPE; + + function get_content_type( + -- + -- Wrapper for content_item. get_content_type + -- + item_id in cr_items.item_id%TYPE + ) return cr_items.content_type%TYPE; + + function get_folder_name( + -- + -- Wrapper for content_folder__get_label + -- + folder_id in cr_folders.folder_id%TYPE + ) return cr_folders.label%TYPE; + + function new_version( + -- + -- Create a new version of a file + -- Wrapper for content_revision.new + -- + filename in cr_revisions.title%TYPE, + description in cr_revisions.description%TYPE, + mime_type in cr_revisions.mime_type%TYPE, + item_id in cr_items.item_id%TYPE, + creation_user in acs_objects.creation_user%TYPE, + creation_ip in acs_objects.creation_ip%TYPE + ) return cr_revisions.revision_id%TYPE; + + function delete_version( + -- + -- Delete a version of a file + -- + file_id in cr_items.item_id%TYPE, + version_id in cr_revisions.revision_id%TYPE + ) return cr_items.parent_id%TYPE; + + function new_folder( + -- + -- Create a folder + -- + name in cr_items.name%TYPE, + folder_name in cr_folders.label%TYPE, + parent_id in cr_items.parent_id%TYPE, + creation_user in acs_objects.creation_user%TYPE, + creation_ip in acs_objects.creation_ip%TYPE + ) return cr_folders.folder_id%TYPE; + + procedure delete_folder( + -- + -- Delete a folder + -- + folder_id in cr_folders.folder_id%TYPE + ); + +end file_storage; +/ +show errors + +create or replace package body file_storage +as + + function get_root_folder( + package_id in apm_packages.package_id%TYPE + ) return fs_root_folders.folder_id%TYPE + is + v_folder_id fs_root_folders.folder_id%TYPE; + v_count integer; + begin + select count(*) + into v_count + from fs_root_folders + where package_id = get_root_folder.package_id; + + if v_count > 0 then + select folder_id + into v_folder_id + from fs_root_folders + where package_id = get_root_folder.package_id; + else + -- must be a new instance. Gotta create a new root folder + v_folder_id := new_root_folder(package_id); + end if; + + return v_folder_id; + end get_root_folder; + + function get_package_id( + item_id in cr_items.item_id%TYPE + ) return fs_root_folders.package_id%TYPE + is + v_package_id fs_root_folders.package_id%TYPE; + begin + select fs_root_folders.package_id + into v_package_id + from fs_root_folders, + (select cr_items.item_id + from cr_items + connect by prior cr_items.parent_id = cr_items.item_id + start with cr_items.item_id = get_package_id.item_id) this + where fs_root_folders.folder_id = this.item_id; + + return v_package_id; + + exception when NO_DATA_FOUND then + return null; + end get_package_id; + + function new_root_folder( + -- + -- A hackish function to get around the fact that we can't run + -- code automatically when a new package instance is created. + -- + package_id in apm_packages.package_id%TYPE, + folder_name in cr_folders.label%TYPE default null, + description in cr_folders.description%TYPE default null + ) return fs_root_folders.folder_id%TYPE + is + v_folder_id fs_root_folders.folder_id%TYPE; + v_package_name apm_packages.instance_name%TYPE; + v_package_key apm_packages.package_key%TYPE; + v_folder_name cr_folders.label%TYPE; + v_description cr_folders.description%TYPE; + begin + select instance_name, package_key + into v_package_name, v_package_key + from apm_packages + where package_id = new_root_folder.package_id; + + if new_root_folder.folder_name is null + then + v_folder_name := v_package_name || ' Root Folder'; + else + v_folder_name := folder_name; + end if; + + if new_root_folder.description is null + then + v_description := 'Root folder for the file-storage system. All other folders in file storage are subfolders of this one.'; + else + v_description := description; + end if; + + v_folder_id := content_folder.new( + name => v_package_key || '_' || package_id, + label => v_folder_name, + description => v_description + ); + + insert + into fs_root_folders + (package_id, folder_id) + values + (package_id, v_folder_id); + + -- allow child items to be added + content_folder.register_content_type(v_folder_id,'content_revision','t'); + content_folder.register_content_type(v_folder_id,'content_folder','t'); + content_folder.register_content_type(v_folder_id,'content_extlink','t'); + content_folder.register_content_type(v_folder_id,'content_symlink','t'); + + -- set up default permissions + acs_permission.grant_permission( + object_id => v_folder_id, + grantee_id => acs.magic_object_id('the_public'), + privilege => 'read' + ); + + acs_permission.grant_permission( + object_id => v_folder_id, + grantee_id => acs.magic_object_id('registered_users'), + privilege => 'write' + ); + + return v_folder_id; + end new_root_folder; + + function new_file( + -- + -- Create a file in CR in preparation for actual storage + -- Wrapper for content_item.new + -- + item_id in cr_items.item_id%TYPE default null, + title in cr_items.name%TYPE, + folder_id in cr_items.parent_id%TYPE, + creation_user in acs_objects.creation_user%TYPE, + creation_ip in acs_objects.creation_ip%TYPE, + indb_p in char default 't' + ) return cr_items.item_id%TYPE + is + v_item_id cr_items.item_id%TYPE; + begin + if new_file.indb_p = 't' + then + v_item_id := content_item.new( + item_id => new_file.item_id, + name => new_file.title, + parent_id => new_file.folder_id, + creation_user => new_file.creation_user, + context_id => new_file.folder_id, + creation_ip => new_file.creation_ip, + content_type => 'file_storage_object', + item_subtype => 'content_item' + ); + else + v_item_id := content_item.new( + name => new_file.title, + parent_id => new_file.folder_id, + creation_user => new_file.creation_user, + context_id => new_file.folder_id, + creation_ip => new_file.creation_ip, + content_type => 'file_storage_object', + item_subtype => 'content_item', + storage_type => 'file' + ); + end if; + + acs_object.update_last_modified(file_storage.new_file.folder_id,new_file.creation_user,new_file.creation_ip); + + return v_item_id; + end new_file; + + procedure delete_file( + -- + -- Delete a file and all its version + -- Wrapper to content_item__delete + -- + file_id in cr_items.item_id%TYPE + ) + is + begin + content_item.delete(item_id => file_storage.delete_file.file_id); + end delete_file; + + procedure rename_file( + -- + -- Rename a file and all + -- Wrapper to content_item__rename + -- + file_id in cr_items.item_id%TYPE, + title in cr_items.name%TYPE + ) + is + begin + content_item.rename( + item_id => file_storage.rename_file.file_id, + name => file_storage.rename_file.title + ); + end rename_file; + + function copy_file( + -- + -- Copy a file, but only copy the live_revision + -- + file_id in cr_items.item_id%TYPE, + target_folder_id in cr_items.parent_id%TYPE, + creation_user in acs_objects.creation_user%TYPE, + creation_ip in acs_objects.creation_ip%TYPE + ) return cr_revisions.revision_id%TYPE + is + v_title cr_items.name%TYPE; + v_live_revision cr_items.live_revision%TYPE; + v_filename cr_revisions.title%TYPE; + v_description cr_revisions.description%TYPE; + v_mime_type cr_revisions.mime_type%TYPE; + v_content_length cr_revisions.content_length%TYPE; + v_lob cr_revisions.content%TYPE; + v_file_path cr_revisions.filename%TYPE; + v_new_file_id cr_items.item_id%TYPE; + v_new_version_id cr_revisions.revision_id%TYPE; + v_indb_p char; + begin + -- We copy only the title from the file being copied, and attributes of the + -- live revision + select i.name, i.live_revision, r.title, r.description, + r.mime_type, r.content, r.filename, r.content_length, + decode(i.storage_type,'lob','t','f') + into v_title, v_live_revision, v_filename, v_description, + v_mime_type, v_lob, v_file_path, v_content_length, + v_indb_p + from cr_items i, cr_revisions r + where r.item_id = i.item_id + and r.revision_id = i.live_revision + and i.item_id = file_storage.copy_file.file_id; + + -- We should probably use the copy functions of CR + -- when we optimize this function + v_new_file_id := file_storage.new_file( + title => v_title, + folder_id => file_storage.copy_file.target_folder_id, + creation_user => file_storage.copy_file.creation_user, + creation_ip => file_storage.copy_file.creation_ip, + indb_p => v_indb_p + ); + + v_new_version_id := file_storage.new_version( + filename => v_filename, + description => v_description, + mime_type => v_mime_type, + item_id => v_new_file_id, + creation_user => file_storage.copy_file.creation_user, + creation_ip => file_storage.copy_file.creation_ip + ); + + -- Oracle is easier, since lobs are true lobs + -- For now, we simply copy the file name + update cr_revisions + set filename = v_file_path, + content = v_lob, + content_length = v_content_length + where revision_id = v_new_version_id; + + acs_object.update_last_modified(file_storage.copy_file.target_folder_id,file_storage.copy_file.creation_user,file_storage.copy_file.creation_ip); + + return v_new_version_id; + end copy_file; + + procedure move_file( + -- + -- Move a file, and all its versions, to a new folder + -- + file_id in cr_items.item_id%TYPE, + target_folder_id in cr_items.parent_id%TYPE, + creation_user in acs_objects.creation_user%TYPE, + creation_ip in acs_objects.creation_ip%TYPE + ) + is + begin + content_item.move( + item_id => file_storage.move_file.file_id, + target_folder_id => file_storage.move_file.target_folder_id + ); + + acs_object.update_last_modified(file_storage.move_file.target_folder_id,file_storage.move_file.creation_user,file_storage.move_file.creation_ip); + + end; + + function new_version( + -- + -- Create a new version of a file + -- Wrapper for content_revision.new + -- + filename in cr_revisions.title%TYPE, + description in cr_revisions.description%TYPE, + mime_type in cr_revisions.mime_type%TYPE, + item_id in cr_items.item_id%TYPE, + creation_user in acs_objects.creation_user%TYPE, + creation_ip in acs_objects.creation_ip%TYPE + ) return cr_revisions.revision_id%TYPE + is + v_revision_id cr_revisions.revision_id%TYPE; + v_folder_id cr_items.parent_id%TYPE; + begin + -- Create a revision + v_revision_id := content_revision.new( + title => new_version.filename, + description => new_version.description, + mime_type => new_version.mime_type, + item_id => new_version.item_id, + creation_user => new_version.creation_user, + creation_ip => new_version.creation_ip + ); + + -- Make live the newly created revision + content_item.set_live_revision(revision_id => v_revision_id); + + select cr_items.parent_id + into v_folder_id + from cr_items + where cr_items.item_id = file_storage.new_version.item_id; + + acs_object.update_last_modified(v_folder_id,new_version.creation_user,new_version.creation_ip); + + return v_revision_id; + + exception when NO_DATA_FOUND then + return v_revision_id; + end new_version; + + function get_title( + -- + -- Unfortunately, title in the file-storage context refers + -- to the name attribute in cr_items, not the title attribute in + -- cr_revisions + item_id in cr_items.item_id%TYPE + ) return varchar + is + v_title cr_items.name%TYPE; + v_content_type cr_items.content_type%TYPE; + begin + select content_type + into v_content_type + from cr_items + where item_id = get_title.item_id; + + if v_content_type = 'content_folder' + then + select label + into v_title + from cr_folders + where folder_id = get_title.item_id; + else if v_content_type = 'content_symlink' + then + select label into v_title + from cr_symlinks + where symlink_id = get_title.item_id; + else + select name into v_title + from cr_items + where item_id = get_title.item_id; + end if; + end if; + + return v_title; + end get_title; + + function get_parent_id( + item_id in cr_items.item_id%TYPE + ) return cr_items.item_id%TYPE + is + v_parent_id cr_items.item_id%TYPE; + begin + select parent_id + into v_parent_id + from cr_items + where item_id = get_parent_id.item_id; + + return v_parent_id; + end get_parent_id; + + function get_content_type( + -- + -- Wrapper for content_item. get_content_type + -- + item_id in cr_items.item_id%TYPE + ) return cr_items.content_type%TYPE + is + v_content_type cr_items.content_type%TYPE; + begin + v_content_type := content_item.get_content_type( + item_id => file_storage.get_content_type.item_id + ); + + return v_content_type; + end get_content_type; + + function get_folder_name( + -- + -- Wrapper for content_folder.get_label + -- + folder_id in cr_folders.folder_id%TYPE + ) return cr_folders.label%TYPE + is + v_folder_name cr_folders.label%TYPE; + begin + v_folder_name := content_folder.get_label( + folder_id => file_storage.get_folder_name.folder_id + ); + + return v_folder_name; + end get_folder_name; + + function delete_version( + -- + -- Delete a version of a file + -- + file_id in cr_items.item_id%TYPE, + version_id in cr_revisions.revision_id%TYPE + ) return cr_items.parent_id%TYPE + is + v_parent_id cr_items.parent_id%TYPE; + begin + if file_storage.delete_version.version_id = content_item.get_live_revision(file_storage.delete_version.file_id) + then + content_revision.delete(file_storage.delete_version.version_id); + content_item.set_live_revision( + content_item.get_latest_revision(file_storage.delete_version.file_id) + ); + else + content_revision.delete(file_storage.delete_version.version_id); + end if; + + -- If the live revision is null, we have deleted the last version above + select decode(live_revision, null, parent_id, 0) + into v_parent_id + from cr_items + where item_id = file_storage.delete_version.file_id; + + -- Unfortunately, due to PostgreSQL behavior with regards referential integrity, + -- we cannot delete the content_item entry if there are no more revisions. + return v_parent_id; + end delete_version; + + function new_folder( + -- + -- Create a folder + -- + name in cr_items.name%TYPE, + folder_name in cr_folders.label%TYPE, + parent_id in cr_items.parent_id%TYPE, + creation_user in acs_objects.creation_user%TYPE, + creation_ip in acs_objects.creation_ip%TYPE + ) return cr_folders.folder_id%TYPE + is + v_folder_id cr_folders.folder_id%TYPE; + begin + -- Create a new folder + v_folder_id := content_folder.new( + name => file_storage.new_folder.name, + label => file_storage.new_folder.folder_name, + parent_id => file_storage.new_folder.parent_id, + creation_user => file_storage.new_folder.creation_user, + creation_ip => file_storage.new_folder.creation_ip + ); + + -- register the standard content types + content_folder.register_content_type( + v_folder_id, -- folder_id + 'content_revision', -- content_type + 't' -- include_subtypes + ); + + content_folder.register_content_type( + v_folder_id, -- folder_id + 'content_folder', -- content_type + 't' -- include_subtypes + ); + + content_folder.register_content_type( + v_folder_id, -- folder_id + 'content_extlink', -- content_type + 't' -- include_subtypes + ); + + content_folder.register_content_type( + v_folder_id, -- folder_id + 'content_symlink', -- content_type + 't' -- include_subtypes + ); + + -- Give the creator admin privileges on the folder + acs_permission.grant_permission( + v_folder_id, -- object_id + file_storage.new_folder.creation_user, -- grantee_id + 'admin' -- privilege + ); + + return v_folder_id; + end new_folder; + + procedure delete_folder( + -- + -- Delete a folder + -- + folder_id in cr_folders.folder_id%TYPE + ) + is + begin + content_folder.delete( + folder_id => file_storage.delete_folder.folder_id + ); + end delete_folder; + +end file_storage; +/ +show errors; + +-- JS: BEFORE DELETE TRIGGER to clean up CR +create or replace trigger fs_package_items_delete_trig +before delete on fs_root_folders +for each row +declare + cursor v_cursor is + select item_id,content_type + from cr_items + where item_id != :old.folder_id + connect by parent_id = prior item_id + start with item_id = :old.folder_id + order by level desc; +begin + for v_rec in v_cursor + loop + -- We delete the item. On delete cascade should take care + -- of deletion of revisions. + if v_rec.content_type = 'file_storage_object' + then + content_item.delete(v_rec.item_id); + end if; + + -- Instead of doing an if-else, we make sure we are deleting a folder. + if v_rec.content_type = 'content_folder' + then + content_folder.delete(v_rec.item_id); + end if; + + -- Instead of doing an if-else, we make sure we are deleting a folder. + if v_rec.content_type = 'content_symlink' + then + content_symlink.delete(v_rec.item_id); + end if; + + -- Instead of doing an if-else, we make sure we are deleting a folder. + if v_rec.content_type = 'content_extlink' + then + content_extlink.delete(v_rec.item_id); + end if; + + end loop; +end; +/ +show errors; + +-- JS: AFTER DELETE TRIGGER to clean up last entry in CR +create or replace trigger fs_root_folder_delete_trig +after delete on fs_root_folders +for each row +begin + content_folder.delete(:old.folder_id); +end; +/ +show errors; Index: openacs-4/packages/file-storage/sql/postgresql/file-storage-package-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/file-storage/sql/postgresql/file-storage-package-create.sql,v diff -u -N -r1.8.2.3 -r1.8.2.4 --- openacs-4/packages/file-storage/sql/postgresql/file-storage-package-create.sql 21 Mar 2003 14:50:17 -0000 1.8.2.3 +++ openacs-4/packages/file-storage/sql/postgresql/file-storage-package-create.sql 23 Jun 2003 18:47:03 -0000 1.8.2.4 @@ -225,7 +225,7 @@ end if; - perform acs_object__update_last_modified(new_file__folder_id); + perform acs_object__update_last_modified(new_file__folder_id,new_file__user_id,new_file__creation_ip); return v_item_id; @@ -392,7 +392,7 @@ end if; - perform acs_object__update_last_modified(copy_file__target_folder_id); + perform acs_object__update_last_modified(copy_file__target_folder_id,copy_file__creation_user,copy_file__creation_ip); return v_new_version_id; @@ -410,14 +410,16 @@ declare move_file__file_id alias for $1; move_file__target_folder_id alias for $2; + move_file__creation_user alias for $3; + move_file__creation_ip alias for $4; begin perform content_item__move( move_file__file_id, -- item_id move_file__target_folder_id -- target_folder_id ); - perform acs_object__update_last_modified(move_file__target_folder_id); + perform acs_object__update_last_modified(move_file__target_folder_id,move_file__creation_user,move_file__creation_ip); return 0; end;' language 'plpgsql'; @@ -554,7 +556,7 @@ from cr_items where cr_items.item_id = new_version__item_id; - perform acs_object__update_last_modified(v_folder_id); + perform acs_object__update_last_modified(v_folder_id,new_version__creation_user,new_version__creation_ip); return v_revision_id; Index: openacs-4/packages/file-storage/sql/postgresql/upgrade/upgrade-4.6.2-4.6.3.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/file-storage/sql/postgresql/upgrade/upgrade-4.6.2-4.6.3.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/file-storage/sql/postgresql/upgrade/upgrade-4.6.2-4.6.3.sql 23 Jun 2003 18:47:03 -0000 1.1.2.1 @@ -0,0 +1,253 @@ +-- Add user_id and IP to update_last_modified +-- $Id + +create function file_storage__new_file( + -- + -- Create a file in CR in preparation for actual storage + -- Wrapper for content_item__new + -- + -- DRB: I added this version to allow one to predefine item_id, among other things to + -- make it easier to use with ad_form + varchar, -- cr_items.name%TYPE, + integer, -- cr_items.parent_id%TYPE, + integer, -- acs_objects.creation_user%TYPE, + varchar, -- acs_objects.creation_ip%TYPE, + boolean, -- store in db? + integer -- cr_items.item_id%TYPE, +) returns integer as ' -- cr_items.item_id%TYPE +declare + new_file__title alias for $1; + new_file__folder_id alias for $2; + new_file__user_id alias for $3; + new_file__creation_ip alias for $4; + new_file__indb_p alias for $5; + new_file__item_id alias for $6; + v_item_id integer; +begin + + if new_file__indb_p + then + v_item_id := content_item__new ( + new_file__title, -- name + new_file__folder_id, -- parent_id + new_file__item_id, -- item_id (default) + null, -- locale (default) + now(), -- creation_date (default) + new_file__user_id, -- creation_user + new_file__folder_id, -- context_id + new_file__creation_ip, -- creation_ip + ''content_item'', -- item_subtype (default) + ''file_storage_object'', -- content_type (needed by site-wide search) + null, -- title (default) + null, -- description + ''text/plain'', -- mime_type (default) + null, -- nls_language (default) + null -- data (default) + ); + else + v_item_id := content_item__new ( + new_file__title, -- name + new_file__folder_id, -- parent_id + new_file__item_id, -- item_id (default) + null, -- locale (default) + now(), -- creation_date (default) + new_file__user_id, -- creation_user + new_file__folder_id, -- context_id + new_file__creation_ip, -- creation_ip + ''content_item'', -- item_subtype (default) + ''file_storage_object'', -- content_type (needed by site-wide search) + null, -- title (default) + null, -- description + ''text/plain'', -- mime_type (default) + null, -- nls_language (default) + null, -- text (default) + ''file'' -- storage_type + ); + + end if; + + perform acs_object__update_last_modified(new_file__folder_id,new_file__user_id,new_file__creation_ip); + + return v_item_id; + +end;' language 'plpgsql'; + + +create function file_storage__copy_file( + -- + -- Copy a file, but only copy the live_revision + -- + integer, -- cr_items.item_id%TYPE, + integer, -- cr_items.parent_id%TYPE, + integer, -- acs_objects.creation_user%TYPE, + varchar -- acs_objects.creation_ip%TYPE +) returns integer as ' -- cr_revisions.revision_id%TYPE +declare + copy_file__file_id alias for $1; + copy_file__target_folder_id alias for $2; + copy_file__creation_user alias for $3; + copy_file__creation_ip alias for $4; + v_title cr_items.name%TYPE; + v_live_revision cr_items.live_revision%TYPE; + v_filename cr_revisions.title%TYPE; + v_description cr_revisions.description%TYPE; + v_mime_type cr_revisions.mime_type%TYPE; + v_content_length cr_revisions.content_length%TYPE; + v_lob_id cr_revisions.lob%TYPE; + v_new_lob_id cr_revisions.lob%TYPE; + v_file_path cr_revisions.content%TYPE; + v_new_file_id cr_items.item_id%TYPE; + v_new_version_id cr_revisions.revision_id%TYPE; + v_indb_p boolean; +begin + + -- We copy only the title from the file being copied, and attributes of the + -- live revision + select i.name,i.live_revision,r.title,r.description,r.mime_type,r.content_length, + (case when i.storage_type = ''lob'' + then true + else false + end) + into v_title,v_live_revision,v_filename,v_description,v_mime_type,v_content_length,v_indb_p + from cr_items i, cr_revisions r + where r.item_id = i.item_id + and r.revision_id = i.live_revision + and i.item_id = copy_file__file_id; + + -- We should probably use the copy functions of CR + -- when we optimize this function + v_new_file_id := file_storage__new_file( + v_title, -- title + copy_file__target_folder_id, -- folder_id + copy_file__creation_user, -- creation_user + copy_file__creation_ip, -- creation_ip + v_indb_p -- indb_p + ); + + v_new_version_id := file_storage__new_version ( + v_filename, -- title + v_description, -- description + v_mime_type, -- mime_type + v_new_file_id, -- item_id + copy_file__creation_user, -- creation_user + copy_file__creation_ip -- creation_ip + ); + + if v_indb_p + then + + -- Lob to copy from + select lob into v_lob_id + from cr_revisions + where revision_id = v_live_revision; + + -- New lob id + v_new_lob_id := empty_lob(); + + -- copy the blob + perform lob_copy(v_lob_id,v_new_lob_id); + + -- Update the lob id on the new version + update cr_revisions + set lob = v_new_lob_id, + content_length = v_content_length + where revision_id = v_new_version_id; + + else + + -- For now, we simply copy the file name + select content into v_file_path + from cr_revisions + where revision_id = v_live_revision; + + -- Update the file path + update cr_revisions + set content = v_file_path, + content_length = v_content_length + where revision_id = v_new_version_id; + + end if; + + perform acs_object__update_last_modified(copy_file__target_folder_id,copy_file__creation_user,copy_file__creation_ip); + + return v_new_version_id; + +end;' language 'plpgsql'; + + +create function file_storage__move_file ( + -- + -- Move a file (ans all its versions) to a different folder. + -- Wrapper for content_item__move + -- + integer, -- cr_folders.folder_id%TYPE, + integer -- cr_folders.folder_id%TYPE +) returns integer as ' -- 0 for success +declare + move_file__file_id alias for $1; + move_file__target_folder_id alias for $2; + move_file__creation_user alias for $3; + move_file__creation_ip alias for $4; +begin + + perform content_item__move( + move_file__file_id, -- item_id + move_file__target_folder_id -- target_folder_id + ); + + perform acs_object__update_last_modified(move_file__target_folder_id,move_file__creation_user,move_file__creation_ip); + + return 0; +end;' language 'plpgsql'; + +create function file_storage__new_version ( + -- + -- Create a new version of a file + -- Wrapper for content_revision__new + -- + varchar, -- cr_revisions.title%TYPE, + varchar, -- cr_revisions.description%TYPE, + varchar, -- cr_revisions.mime_type%TYPE, + integer, -- cr_items.item_id%TYPE, + integer, -- acs_objects.creation_user%TYPE, + varchar -- acs_objects.creation_ip%TYPE +) returns integer as ' -- cr_revisions.revision_id +declare + new_version__filename alias for $1; + new_version__description alias for $2; + new_version__mime_type alias for $3; + new_version__item_id alias for $4; + new_version__creation_user alias for $5; + new_version__creation_ip alias for $6; + v_revision_id cr_revisions.revision_id%TYPE; + v_folder_id cr_items.parent_id%TYPE; +begin + -- Create a revision + v_revision_id := content_revision__new ( + new_version__filename, -- title + new_version__description, -- description + now(), -- publish_date + new_version__mime_type, -- mime_type + null, -- nls_language + null, -- data (default) + new_version__item_id, -- item_id + null, -- revision_id + now(), -- creation_date + new_version__creation_user, -- creation_user + new_version__creation_ip -- creation_ip + ); + + -- Make live the newly created revision + perform content_item__set_live_revision(v_revision_id); + + select cr_items.parent_id + into v_folder_id + from cr_items + where cr_items.item_id = new_version__item_id; + + perform acs_object__update_last_modified(v_folder_id,new_version__creation_user,new_version__creation_ip); + + return v_revision_id; + +end;' language 'plpgsql'; + Index: openacs-4/packages/file-storage/www/file-move-2-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/file-storage/www/Attic/file-move-2-oracle.xql,v diff -u -N -r1.2 -r1.2.4.1 --- openacs-4/packages/file-storage/www/file-move-2-oracle.xql 25 Sep 2001 16:52:03 -0000 1.2 +++ openacs-4/packages/file-storage/www/file-move-2-oracle.xql 23 Jun 2003 18:47:03 -0000 1.2.4.1 @@ -9,7 +9,9 @@ begin file_storage.move_file ( file_id => :file_id, - target_folder_id => :parent_id + target_folder_id => :parent_id, + creation_user => :user_id, + creation_ip => :address ); end; Index: openacs-4/packages/file-storage/www/file-move-2-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/file-storage/www/Attic/file-move-2-postgresql.xql,v diff -u -N -r1.2 -r1.2.4.1 --- openacs-4/packages/file-storage/www/file-move-2-postgresql.xql 25 Sep 2001 16:52:16 -0000 1.2 +++ openacs-4/packages/file-storage/www/file-move-2-postgresql.xql 23 Jun 2003 18:47:03 -0000 1.2.4.1 @@ -8,7 +8,9 @@ select file_storage__move_file ( :file_id, -- file_id - :parent_id -- target_folder_id + :parent_id, -- target_folder_id + :user_id, -- creation_user + :address -- creation_ip ); Index: openacs-4/packages/file-storage/www/file-move-2.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/file-storage/www/Attic/file-move-2.tcl,v diff -u -N -r1.6 -r1.6.2.1 --- openacs-4/packages/file-storage/www/file-move-2.tcl 18 Sep 2002 12:09:59 -0000 1.6 +++ openacs-4/packages/file-storage/www/file-move-2.tcl 23 Jun 2003 18:47:03 -0000 1.6.2.1 @@ -26,13 +26,18 @@ ad_require_permission $file_id write ad_require_permission $parent_id write +set user_id [ad_conn user_id] +set address [ad_conn peeraddr] + db_transaction { db_exec_plsql file_move " begin file_storage.move_file ( file_id => :file_id, - target_folder_id => :parent_id + target_folder_id => :parent_id, + creation_user => :user_id, + creation_ip => :address: ); end;"