Forum OpenACS Q&A: Response to Breakthrough on bookmarks module
Posted by
Bob Fuller
on 01/09/01 09:33 PM
The revised edit-bookmark-2.tcl file
Here's the complete revised edit-bookmark-2.tcl file:
# edit-bookmark-2.tcl,v 3.0 2000/02/06 03:35:21 ron Exp
# edit-bookmark-2.tcl
#
# edit a bookmark in your bookmark list
#
# by aure@arsdigita.com and dh@arsdigita.com
set_the_usual_form_variables
# local_title, complete_url, bookmark_id, parent_id, return_url
validate_integer bookmark_id $bookmark_id
set user_id [ad_verify_and_get_user_id]
ad_maybe_redirect_for_registration
set db [ns_db gethandle]
# start error-checking
set exception_text ""
set exception_count 0
if {(![info exists bookmark_id])||([empty_string_p $bookmark_id])} {
incr exception_count
append exception_text "<li>No bookmark was specified"
}
# make sure that the user owns the bookmark
set ownership_query "
select count(*)
from bm_list
where owner_id=$user_id
and bookmark_id=$bookmark_id"
set ownership_test [database_to_tcl_string $db $ownership_query]
if {$ownership_test==0} {
incr exception_count
append exception_text "<li>You can not edit this bookmark"
}
# return errors
if { $exception_count> 0 } {
ad_return_complaint $exception_count $exception_text
return 0
}
if { ![info exists parent_id] || [empty_string_p $parent_id] } {
set parent_id "null"
}
ns_db dml $db "begin transaction"
# ### not sure why I had to change this section, but it didn't work ###
# if the bookmark to edit is a folder, complete_url won't be defined
# if { ![info exists complete_url] } {
# if { [empty_string_p $complete_url]||![info exists complete_url] } {
# this is a folder so edit its name
# set sql_update "
# update bm_list
# set local_title = '[DoubleApos $local_title]',
# private_p = '$private_p',
# parent_id = $parent_id
# where owner_id = $user_id
# and bookmark_id = $bookmark_id"
# set sql_update "
# update bm_list
# set local_title = '$local_title',
# private_p = '$private_p',
# parent_id = $parent_id
# where owner_id = $user_id
# and bookmark_id = $bookmark_id"
# ns_db dml $db $sql_update
# } else {
if { [info exists complete_url] } {
# entry is a bookmark - need to update both name and url
set host_url [bm_host_url $complete_url]
# check to see if we already have the url in our database
set url_query "select url_id
from bm_urls
where complete_url = '[DoubleApos $complete_url]'"
set url_id [database_to_tcl_string_or_null $db $url_query]
# if we don't have the url, then insert the url into the database
if {[empty_string_p $url_id]} {
set url_id [database_to_tcl_string $db "select bm_url_id_seq.nextval from dual"]
ns_db dml $db "
insert into bm_urls
(url_id, host_url, complete_url)
values
($url_id, '[DoubleApos $host_url]', '[DoubleApos $complete_url]')"
}
# have added the url if needed - now just update the name
set sql_update "
update bm_list
set local_title = '[DoubleApos $local_title]',
url_id = $url_id,
private_p = '$private_p',
parent_id = $parent_id
where bookmark_id = $bookmark_id"
ns_db dml $db $sql_update
} else {
# this is a folder so edit its name
set sql_update "
update bm_list
set local_title = '[DoubleApos $local_title]',
private_p = '$private_p',
parent_id = $parent_id
where owner_id = $user_id
and bookmark_id = $bookmark_id"
ns_db dml $db $sql_update
}
# the bookmark_id's are tagged by a "before update" trigger on bm_list
# -- and they end up as v_updated_ids in bm_list_pkg
# just checking to make sure we have some updates to do...
set id_count [database_to_tcl_string $db "
select count(*)
from bm_list_pkg
where v_num_entries > 0
and v_updated_ids is not null"]
set i 0
# ### for some reason, I had to do the other selects in a different loop ###
# ... so, in this loop, I collect everything into arrays
if {$id_count > 0} {
set selection [ns_db select $db "
select distinct v_updated_ids, coalesce(parent_id, -1) as parent_id,
case when parent_sort_key is null then local_sort_key
else parent_sort_key || cast(local_sort_key as varchar(3)) end
as parent_sort_key,
folder_p
from bm_list_pkg, bm_list
where v_num_entries > 0
and v_updated_ids = bookmark_id"]
while {[ns_db getrow $db $selection]} {
set_variables_after_query
set v_upd_ids($i) $v_updated_ids
set new_parent_id($i) $parent_id
set old_parent_sort_key($i) $parent_sort_key
set is_folder($i) $folder_p
incr i
}
while {$i > 0} {
incr i -1
if {$new_parent_id($i) > -1} {
set v_parent_sort_key [database_to_tcl_string $db "
select case when parent_sort_key is null
then local_sort_key
else parent_sort_key || cast(local_sort_key as varchar(3)) end
from bm_list where bookmark_id = $new_parent_id($i)"]
set v_last_sort_key [database_to_tcl_string $db "
select max(local_sort_key) from bm_list
where parent_id = $new_parent_id($i)
and bookmark_id != $v_upd_ids($i)"]
ns_db dml $db "update bm_list set parent_sort_key = '$v_parent_sort_key',
local_sort_key = new_sort_key('$v_last_sort_key')
where bookmark_id = $v_upd_ids($i)"
} else {
set v_last_sort_key [database_to_tcl_string $db "
select max(local_sort_key) from bm_list where parent_id is null
and bookmark_id != $v_upd_ids($i)"]
ns_db dml $db "update bm_list set parent_sort_key = null,
local_sort_key = new_sort_key('$v_last_sort_key')
where bookmark_id = $v_upd_ids($i) and parent_id is null"
}
set v_new_sort_key [database_to_tcl_string $db "
select case when parent_sort_key is null then local_sort_key
else parent_sort_key || cast(local_sort_key as varchar(3)) end
from bm_list where bookmark_id = $v_upd_ids($i)"]
set len_old_sort_key [string length $old_parent_sort_key($i)]
ns_db dml $db "update bm_list
set parent_sort_key = '$v_new_sort_key' ||
substr(parent_sort_key, $len_old_sort_key+1,
length(parent_sort_key)-$len_old_sort_key)
where parent_sort_key like '$old_parent_sort_key($i)%'"
}
}
# ### need to delete all the entries "tagged" by the update trigger
# ### now that we're done updating sort keys
ns_db dml $db "delete from bm_list_pkg where v_num_entries > 0"
# ### I tried to do this as a Tcl proc in tcl/bookmarks-defs.tcl, ###
# ### but no luck on that (so far)...
# bm_recalc_sort_keys $db $user_id
# ### this may need to be changed, at some point...
bm_set_hidden_p $db $user_id
# ### the first of these is my modification of bm_set_in_closed_p, ###
# ### which, as you can tell, does only ONE folder
# ### forget why I had to comment it out, but it's NOT relevant to
# ### this part of the bookmark module
# ### (bm_set_in_closed_p is commented out because it didn't work)
# bm_set_one_in_closed_p $db $user_id $bookmark_id
# bm_set_in_closed_p $db $user_id
ns_db dml $db "end transaction"
# send the user back to where they came from before editing began
ns_returnredirect $return_url
