Index: openacs-4/packages/acs-content-repository/acs-content-repository.info =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/acs-content-repository.info,v diff -u -r1.114 -r1.115 --- openacs-4/packages/acs-content-repository/acs-content-repository.info 29 Jan 2018 14:34:47 -0000 1.114 +++ openacs-4/packages/acs-content-repository/acs-content-repository.info 21 Feb 2018 14:00:56 -0000 1.115 @@ -7,7 +7,7 @@ t t - + f t OpenACS @@ -21,7 +21,7 @@ GPL 3 - + Index: openacs-4/packages/acs-content-repository/sql/postgresql/content-item.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/sql/postgresql/content-item.sql,v diff -u -r1.74 -r1.75 --- openacs-4/packages/acs-content-repository/sql/postgresql/content-item.sql 4 Sep 2017 06:23:58 -0000 1.74 +++ openacs-4/packages/acs-content-repository/sql/postgresql/content-item.sql 21 Feb 2018 14:00:56 -0000 1.75 @@ -1022,42 +1022,41 @@ CREATE OR REPLACE FUNCTION content_item__del( delete__item_id integer ) RETURNS integer AS $$ -DECLARE - v_revision_val record; - v_child_val record; BEGIN + + -- Also child relationships must be deleted. On delete cascade would + -- not help here, as related acs_object would stay. + PERFORM acs_object__delete(object_id) + from acs_objects where object_id in + (select rel_id from cr_child_rels where + child_id = delete__item_id or + parent_id = delete__item_id); + -- -- Delete all revisions of this item -- - -- The following loop could be dropped / replaced by a cascade - -- operation, when proper foreign keys are used along the - -- inheritance path. + -- On delete cascade should work for us, but not in case of + -- relationships. Therefore, we call acs_object__delete explicitly + -- on the revisions. Is is also safer in general, as referential + -- integrity might not have been enforced everytime. -- - for v_revision_val in select revision_id - from cr_revisions - where item_id = delete__item_id - LOOP - PERFORM acs_object__delete(v_revision_val.revision_id); - end loop; + PERFORM acs_object__delete(revision_id) + from cr_revisions where item_id = delete__item_id; -- -- Delete all children of this item via a recursive call. -- - -- The following loop is just needed to delete the revisions of - -- child items. It could be removed, when proper foreign keys are - -- used along the inheritance path of cr_content_revisions (which is - -- not enforced and not always the case). + -- On delete cascade should work for us, but not in case of + -- relationships. Therefore, we call acs_object__delete explicitly + -- on the revisions. Is is also safer in general, as referential + -- integrity might not have been enforced everytime. -- - for v_child_val in select item_id - from cr_items - where parent_id = delete__item_id - LOOP - PERFORM content_item__delete(v_child_val.item_id); - end loop; + PERFORM content_item__delete(item_id) + from cr_items where parent_id = delete__item_id; -- -- Finally, delete the acs_object of the item. - -- + -- PERFORM acs_object__delete(delete__item_id); return 0; Index: openacs-4/packages/acs-content-repository/sql/postgresql/upgrade/upgrade-5.10.0d4-5.10.0d5.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/sql/postgresql/upgrade/upgrade-5.10.0d4-5.10.0d5.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-content-repository/sql/postgresql/upgrade/upgrade-5.10.0d4-5.10.0d5.sql 21 Feb 2018 14:00:56 -0000 1.1 @@ -0,0 +1,77 @@ + +begin; + +-- apisano 2018-02-21: +-- - added proper removal of cr_child_rels when item is deleted. +-- - streamlined idioms +-- - added missing on delete cascade +-- - removed dead acs_objects formerly linked to deleted cr_child_rels + +-- Cleanup + +-- delete dead tuples coming from sins of the past (mostly erased portraits) +select acs_object__delete(object_id) from acs_objects o + where object_type = 'cr_item_child_rel' and + not exists (select 1 from cr_child_rels where rel_id = o.object_id); + + +-- Data model upgrade + +alter table images + drop constraint images_image_id_fk, + add constraint images_image_id_fk foreign key (image_id) + references cr_revisions(revision_id) on delete cascade; + +alter table cr_revision_attributes + drop constraint cr_revision_attributes_fk, + add constraint cr_revision_attributes_fk foreign key (revision_id) + references cr_revisions(revision_id) on delete cascade; + +-- +-- procedure content_item__del/1 +-- +CREATE OR REPLACE FUNCTION content_item__del( + delete__item_id integer +) RETURNS integer AS $$ +BEGIN + + -- Also child relationships must be deleted. On delete cascade + -- would not help here, as related acs_object would stay. + PERFORM acs_object__delete(object_id) + from acs_objects where object_id in + (select rel_id from cr_child_rels where + child_id = delete__item_id or + parent_id = delete__item_id); + + -- + -- Delete all revisions of this item + -- + -- On delete cascade should work for us, but not in case of + -- relationships. Therefore, we call acs_object__delete explicitly + -- on the revisions. Is is also safer in general, as referential + -- integrity might not have been enforced everytime. + -- + PERFORM acs_object__delete(revision_id) + from cr_revisions where item_id = delete__item_id; + + -- + -- Delete all children of this item via a recursive call. + -- + -- On delete cascade should work for us, but not in case of + -- relationships. Therefore, we call acs_object__delete explicitly + -- on the revisions. Is is also safer in general, as referential + -- integrity might not have been enforced everytime. + -- + PERFORM content_item__delete(item_id) + from cr_items where parent_id = delete__item_id; + + -- + -- Finally, delete the acs_object of the item. + -- + PERFORM acs_object__delete(delete__item_id); + + return 0; +END; +$$ LANGUAGE plpgsql; + +end; Index: openacs-4/packages/acs-kernel/acs-kernel.info =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/acs-kernel.info,v diff -u -r1.140 -r1.141 --- openacs-4/packages/acs-kernel/acs-kernel.info 5 Oct 2017 09:33:45 -0000 1.140 +++ openacs-4/packages/acs-kernel/acs-kernel.info 21 Feb 2018 14:00:56 -0000 1.141 @@ -9,15 +9,15 @@ f t - + OpenACS Core Team Routines and data models providing the foundation for OpenACS-based Web services. 2017-08-06 OpenACS The OpenACS kernel contains the core datamodel create and drop scripts for such things as objects, groups, partiies and the supporting PL/SQL and PL/pgSQL procedures. 3 - + 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 -r1.69 -r1.70 --- openacs-4/packages/acs-kernel/sql/postgresql/acs-objects-create.sql 24 Dec 2017 13:10:50 -0000 1.69 +++ openacs-4/packages/acs-kernel/sql/postgresql/acs-objects-create.sql 21 Feb 2018 14:00:56 -0000 1.70 @@ -817,7 +817,16 @@ DECLARE obj_type record; BEGIN - + + -- Also child relationships must be deleted. On delete cascade + -- would not help here, as only tuple in acs_rels would go, while + -- related acs_object would stay. + PERFORM acs_object__delete(object_id) + from acs_objects where object_id in + (select rel_id from acs_rels where + object_id_one = delete__object_id or + object_id_two = delete__object_id); + -- GN: the following deletion operation iterates over the id_columns -- of the acs_object_types of the type tree for the object and -- performs manual deletions in these tables by trying to delete the Index: openacs-4/packages/acs-kernel/sql/postgresql/upgrade/upgrade-5.10.0d3-5.10.0d4.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/postgresql/upgrade/upgrade-5.10.0d3-5.10.0d4.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-kernel/sql/postgresql/upgrade/upgrade-5.10.0d3-5.10.0d4.sql 21 Feb 2018 14:00:56 -0000 1.1 @@ -0,0 +1,80 @@ + +begin; + +-- apisano 2018-02-21: +-- - added proper removal of acs_rels when deleting an acs object +-- - remove dead acs_object coming from erased portraits. Not clear +-- wether other kinds of dead objects due to other kind of +-- relationships will still be around... + +-- Cleanup + + select acs_object__delete(object_id) + from acs_objects o + where object_type = 'user_portrait_rel' + and not exists ( + select 1 from acs_rels + where rel_id = o.object_id); + +-- Data model upgrade + +-- +-- procedure acs_object__delete/1 +-- +CREATE OR REPLACE FUNCTION acs_object__delete( + delete__object_id integer +) RETURNS integer AS $$ +DECLARE + obj_type record; +BEGIN + + -- Also child relationships must be deleted. On delete cascade + -- would not help here, as only tuple in acs_rels would go, while + -- related acs_object would stay. + PERFORM acs_object__delete(object_id) + from acs_objects where object_id in + (select rel_id from acs_rels where + object_id_one = delete__object_id or + object_id_two = delete__object_id); + + -- GN: the following deletion operation iterates over the id_columns + -- of the acs_object_types of the type tree for the object and + -- performs manual deletions in these tables by trying to delete the + -- delete__object_id from the id_column. This deletion includes as + -- well the deletion in acs_objects. + -- + -- In the best of all possible worlds, this would not + -- be necessary, when the objects would have specified "on delete + -- cascade" for the id_columns. + + for obj_type + in select ot2.table_name, ot2.id_column + from acs_object_types ot1, acs_object_types ot2 + where ot1.object_type = (select object_type + from acs_objects o + where o.object_id = delete__object_id) + and ot1.tree_sortkey between ot2.tree_sortkey and tree_right(ot2.tree_sortkey) + order by ot2.tree_sortkey desc + loop + -- Delete from the table. + + -- DRB: I removed the quote_ident calls that DanW originally included + -- because the table names appear to be stored in upper case. Quoting + -- causes them to not match the actual lower or potentially mixed-case + -- table names. We will just forbid squirrely names that include quotes. + + -- daveB + -- ETP is creating a new object, but not a table, although it does specify a + -- table name, so we need to check if the table exists. Wp-slim does this too + + if table_exists(obj_type.table_name) then + execute 'delete from ' || obj_type.table_name || + ' where ' || obj_type.id_column || ' = ' || delete__object_id; + end if; + end loop; + + return 0; +END; +$$ LANGUAGE plpgsql; + +end; Index: openacs-4/packages/acs-subsite/lib/home.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/lib/home.tcl,v diff -u -r1.4 -r1.5 --- openacs-4/packages/acs-subsite/lib/home.tcl 7 Aug 2017 23:47:57 -0000 1.4 +++ openacs-4/packages/acs-subsite/lib/home.tcl 21 Feb 2018 14:00:56 -0000 1.5 @@ -47,10 +47,15 @@ set portrait_upload_url [export_vars -base "../user/portrait/upload" { { return_url [ad_return_url] } }] if {[parameter::get -parameter SolicitPortraitP -default 0]} { - # we have portraits for some users - if {![db_0or1row get_portrait_info {}]} { + # we have portraits for some users + set portrait_id [acs_user::get_portrait_id -user_id $user_id] + if {$portrait_id == 0} { set portrait_state "upload" } else { + content::item::get -item_id $portrait_id -array_name portrait + set publish_date $portrait(publish_date) + set portrait_title $portrait(title) + set portrait_description $portrait(description) if { $portrait_title eq "" } { set portrait_title "[_ acs-subsite.no_portrait_title_message]" } Fisheye: Tag 1.3 refers to a dead (removed) revision in file `openacs-4/packages/acs-subsite/lib/home.xql'. Fisheye: No comparison available. Pass `N' to diff? Index: openacs-4/packages/acs-subsite/www/user/portrait/index.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/user/portrait/index.tcl,v diff -u -r1.13 -r1.14 --- openacs-4/packages/acs-subsite/www/user/portrait/index.tcl 7 Aug 2017 23:47:59 -0000 1.13 +++ openacs-4/packages/acs-subsite/www/user/portrait/index.tcl 21 Feb 2018 14:00:56 -0000 1.14 @@ -59,21 +59,11 @@ return } - -if {![db_0or1row get_item_id { - select live_revision as revision_id, item_id - from acs_rels a, cr_items c - where a.object_id_two = c.item_id - and a.object_id_one = :user_id - and a.rel_type = 'user_portrait_rel' - and live_revision is not null - order by revision_id desc - limit 1 -}] || $revision_id eq ""} { - # The user doesn't have a portrait yet - set portrait_p 0 -} else { - set portrait_p 1 +set item_id [acs_user::get_portrait_id \ + -user_id $user_id] +set portrait_p [expr {$item_id != 0}] +if {$portrait_p} { + set revision_id [content::item::get_live_revision -item_id $item_id] } if { $admin_p } { Fisheye: Tag 1.2 refers to a dead (removed) revision in file `openacs-4/packages/acs-subsite/www/user/portrait/upload-oracle.xql'. Fisheye: No comparison available. Pass `N' to diff? Fisheye: Tag 1.2 refers to a dead (removed) revision in file `openacs-4/packages/acs-subsite/www/user/portrait/upload-postgresql.xql'. Fisheye: No comparison available. Pass `N' to diff? Index: openacs-4/packages/acs-subsite/www/user/portrait/upload.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/www/user/portrait/upload.tcl,v diff -u -r1.21 -r1.22 --- openacs-4/packages/acs-subsite/www/user/portrait/upload.tcl 19 Feb 2018 12:31:58 -0000 1.21 +++ openacs-4/packages/acs-subsite/www/user/portrait/upload.tcl 21 Feb 2018 14:00:56 -0000 1.22 @@ -15,11 +15,13 @@ set current_user_id [ad_conn user_id] -set portrait_p [db_0or1row checkportrait {}] +set portrait_id [acs_user::get_portrait_id -user_id $user_id] +set portrait_p [expr {$portrait_id != 0}] if { $portrait_p } { + content::item::get -item_id $portrait_id -array_name portrait set doc(title) [_ acs-subsite.upload_a_replacement_por] - set description [db_string getstory {}] + set description $portrait(description) } else { set doc(title) [_ acs-subsite.Upload_Portrait] set description "" @@ -36,13 +38,17 @@ permission::require_permission -object_id $user_id -privilege "write" -if {![db_0or1row get_name {}]} { +if {![person::person_p -party_id $user_id]} { ad_return_error \ "Account Unavailable" \ "We can't find you (user #$user_id) in the users table. Probably your account was deleted for some reason." ad_script_abort } +acs_user::get -user_id $user_id -array user +set first_names $user(first_names) +set last_name $user(last_name) + if { $return_url eq "" } { set return_url [ad_pvt_home] } @@ -109,89 +115,23 @@ } -on_submit { - # this stuff only makes sense to do if we know the file exists - set tmp_filename [ns_queryget upload_file.tmpfile] - - set file_extension [string tolower [file extension $upload_file]] - - # remove the first . from the file extension - regsub "\." $file_extension "" file_extension - - set guessed_file_type [ns_guesstype $upload_file] - - set n_bytes [file size $tmp_filename] - - # Sizes we want for the portrait - set sizename_list {avatar thumbnail} - array set resized_portrait [list] - - # strip off the C:\directories... crud and just get the file name - if {![regexp {([^/\\]+)$} $upload_file match client_filename]} { - # couldn't find a match - set client_filename $upload_file - } - - # Wrap the whole creation along with the relationship in a big transaction - # Just to make sure it really worked. - db_transaction { - set item_id [content::item::get_id_by_name -name "portrait-of-user-$user_id" -parent_id $user_id] - if { $item_id eq ""} { - # The user doesn't have a portrait relation yet - set item_id [content::item::new -name "portrait-of-user-$user_id" -parent_id $user_id -content_type image] - } else { - foreach sizename $sizename_list { - set resized_portrait($sizename) [image::get_resized_item_id \ - -item_id $item_id \ - -size_name $sizename] - } - } - # Load the file into the revision - set revision_id [cr_import_content \ - -item_id $item_id \ - -image_only \ - -storage_type file \ - -creation_user [ad_conn user_id] \ - -creation_ip [ad_conn peeraddr] \ - -description $portrait_comment \ - $user_id \ - $tmp_filename \ - $n_bytes \ - $guessed_file_type \ - "portrait-of-user-$user_id"] - - content::item::set_live_revision -revision_id $revision_id + acs_user::create_portrait \ + -user_id $user_id \ + -description $portrait_comment \ + -filename $upload_file \ + -file [ns_queryget upload_file.tmpfile] - foreach name [array names resized_portrait] { - if { $resized_portrait($name) ne "" } { - # Delete the item - content::item::delete -item_id $resized_portrait($name) - - # Resize the item - image::resize -item_id $item_id -size_name $name - } - } - - # Only create the new relationship if there does not exist one already - set user_portrait_rel_id [relation::get_id -object_id_one $user_id -object_id_two $item_id -rel_type "user_portrait_rel"] - if {$user_portrait_rel_id eq ""} { - db_exec_plsql create_rel {} - } } - # Flush the portrait cache - util_memoize_flush [list acs_user::get_portrait_id_not_cached -user_id $user_id] - } -after_submit { ad_returnredirect $return_url ad_script_abort } -ad_return_template - # Local variables: # mode: tcl # tcl-indent-level: 4 Fisheye: Tag 1.2 refers to a dead (removed) revision in file `openacs-4/packages/acs-subsite/www/user/portrait/upload.xql'. Fisheye: No comparison available. Pass `N' to diff? Index: openacs-4/packages/acs-tcl/tcl/community-core-procs-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-tcl/tcl/community-core-procs-oracle.xql,v diff -u -r1.20 -r1.21 --- openacs-4/packages/acs-tcl/tcl/community-core-procs-oracle.xql 20 Nov 2017 15:52:23 -0000 1.20 +++ openacs-4/packages/acs-tcl/tcl/community-core-procs-oracle.xql 21 Feb 2018 14:00:55 -0000 1.21 @@ -22,5 +22,18 @@ end; - + + + + + begin + :1 := acs_rel.new ( + rel_type => 'user_portrait_rel', + object_id_one => :user_id, + object_id_two => :item_id); + end; + + + + Index: openacs-4/packages/acs-tcl/tcl/community-core-procs-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-tcl/tcl/community-core-procs-postgresql.xql,v diff -u -r1.21 -r1.22 --- openacs-4/packages/acs-tcl/tcl/community-core-procs-postgresql.xql 20 Nov 2017 15:52:23 -0000 1.21 +++ openacs-4/packages/acs-tcl/tcl/community-core-procs-postgresql.xql 21 Feb 2018 14:00:55 -0000 1.22 @@ -16,5 +16,21 @@ + + + + + select acs_rel__new ( + null, + 'user_portrait_rel', + :user_id, + :item_id, + null, + null, + null + ) + + + Index: openacs-4/packages/acs-tcl/tcl/community-core-procs.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-tcl/tcl/community-core-procs.tcl,v diff -u -r1.65 -r1.66 --- openacs-4/packages/acs-tcl/tcl/community-core-procs.tcl 19 Jan 2018 20:56:00 -0000 1.65 +++ openacs-4/packages/acs-tcl/tcl/community-core-procs.tcl 21 Feb 2018 14:00:56 -0000 1.66 @@ -640,26 +640,82 @@ @param user_id user_id of the user for whom we need the portrait } { - return [db_string get_item_id "" -default 0] + set item_id [content::item::get_id_by_name \ + -name "portrait-of-user-$user_id" \ + -parent_id $user_id] + return [expr {$item_id ne "" ? $item_id : 0}] } -ad_proc -private acs_user::erase_portrait { +ad_proc -public acs_user::create_portrait { {-user_id:required} + {-description ""} + {-filename ""} + {-mime_type ""} + {-file:required} +} { + Sets (or resets) the portraif for current user to the one + specified. + + @param user_id user_id of user whose portrait we want to set. + + @param description A caption for the portrait. + + @param filename Original filename of the portrait. Used to guess + the mimetype if an explicit one is not specified. + + @param mime_type mimetype of the portrait. If missing, filename + will be used to guess one. + + @param file Actual file containing the portrait + + @return item_id of the new content item } { + # Delete old portrait, if any + acs_user::erase_portrait -user_id $user_id + + if {$mime_type eq ""} { + # This simple check will suffice here. CR has its own means to + # ensure a valid mimetype + set mime_type [ns_guesstype $filename] + } + + # Create the new portrait + set item_id [content::item::new \ + -name "portrait-of-user-$user_id" \ + -parent_id $user_id \ + -content_type image \ + -storage_type file \ + -creation_user [ad_conn user_id] \ + -creation_ip [ad_conn peeraddr] \ + -description $description \ + -tmp_filename $file \ + -is_live t \ + -mime_type $mime_type] + + # Create portrait relationship + db_exec_plsql create_rel {} + + return $item_id + +} + +ad_proc -public acs_user::erase_portrait { + {-user_id:required} +} { Erases portrait of a user @param user_id user_id of user whose portrait we want to delete } { set item_id [acs_user::get_portrait_id \ -user_id $user_id] - if {$item_id != 0} { + if { $item_id != 0 } { # Delete the item content::item::delete -item_id $item_id - - # Flush the portrait cache - util_memoize_flush [list acs_user::get_portrait_id_not_cached -user_id $user_id] } + + # Flush the portrait cache + util_memoize_flush [list acs_user::get_portrait_id_not_cached -user_id $user_id] } # Local variables: Index: openacs-4/packages/acs-tcl/tcl/community-core-procs.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-tcl/tcl/community-core-procs.xql,v diff -u -r1.28 -r1.29 --- openacs-4/packages/acs-tcl/tcl/community-core-procs.xql 27 Jan 2018 16:55:43 -0000 1.28 +++ openacs-4/packages/acs-tcl/tcl/community-core-procs.xql 21 Feb 2018 14:00:56 -0000 1.29 @@ -153,16 +153,6 @@ - - - select c.item_id - from acs_rels a, cr_items c - where a.object_id_two = c.item_id - and a.object_id_one = :user_id - and a.rel_type = 'user_portrait_rel' - - -