Index: openacs-4/packages/bookmarks/bookmarks.info
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/bookmarks/bookmarks.info,v
diff -u -r1.28 -r1.29
--- openacs-4/packages/bookmarks/bookmarks.info 18 Apr 2014 06:49:48 -0000 1.28
+++ openacs-4/packages/bookmarks/bookmarks.info 26 Jan 2016 08:43:56 -0000 1.29
@@ -9,7 +9,7 @@
<implements-subsite-p>f</implements-subsite-p>
<inherit-templates-p>t</inherit-templates-p>
- <version name="5.1" url="http://openacs.org/repository/download/apm/bookmarks-5.1.apm">
+ <version name="5.2" url="http://openacs.org/repository/download/apm/bookmarks-5.2.apm">
<owner url="http://petermarklund.com">Peter Marklund</owner>
<summary>Web based user bookmark application.</summary>
<release-date>2002-10-27</release-date>
@@ -27,7 +27,7 @@
</ul>
</description>
- <provides url="bookmarks" version="5.1"/>
+ <provides url="bookmarks" version="5.2"/>
<requires url="acs-kernel" version="5.0d13"/>
<callbacks>
Index: openacs-4/packages/bookmarks/sql/postgresql/bookmarks-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/bookmarks/sql/postgresql/bookmarks-create.sql,v
diff -u -r1.26 -r1.27
--- openacs-4/packages/bookmarks/sql/postgresql/bookmarks-create.sql 9 Sep 2013 11:21:42 -0000 1.26
+++ openacs-4/packages/bookmarks/sql/postgresql/bookmarks-create.sql 26 Jan 2016 08:43:56 -0000 1.27
@@ -285,7 +285,7 @@
not null
constraint bm_in_closed_p_bookmark_id_fk
references bm_bookmarks (bookmark_id),
- in_closed_p_id integer
+ in_closed_p_id bigint
constraint bm_in_closed_p_id_nn
not null,
in_closed_p boolean default 't',
@@ -550,8 +550,8 @@
END;
' LANGUAGE 'plpgsql';
-CREATE FUNCTION bookmark__update_in_closed_p_one_user (integer, integer)
-RETURNS integer AS '
+CREATE OR REPLACE FUNCTION bookmark__update_in_closed_p_one_user (integer, bigint)
+RETURNS integer AS $$
DECLARE
p_bookmark_id ALIAS FOR $1; -- in bm_bookmarks.bookmark_id%TYPE,
p_browsing_user_id ALIAS FOR $2; -- in bm_bookmarks.owner_id%TYPE
@@ -575,8 +575,8 @@
select bm.bookmark_id from
bm_bookmarks bm, bm_in_closed_p bip
where bm.bookmark_id = bip.bookmark_id
- and bm.folder_p = ''t''
- and bip.closed_p = ''t''
+ and bm.folder_p = 't'
+ and bip.closed_p = 't'
and bip.in_closed_p_id = p_browsing_user_id
LOOP
UPDATE bm_in_closed_p set in_closed_p = TRUE
@@ -596,7 +596,7 @@
END LOOP;
RETURN 0;
END;
-' LANGUAGE 'plpgsql';
+$$ LANGUAGE plpgsql;
CREATE FUNCTION bookmark__update_in_closed_p_all_users (integer, integer)
@@ -622,8 +622,8 @@
' LANGUAGE 'plpgsql';
-CREATE FUNCTION bookmark__toggle_open_close (integer, integer)
-RETURNS integer AS '
+CREATE OR REPLACE FUNCTION bookmark__toggle_open_close (integer, bigint)
+RETURNS integer AS $$
DECLARE
p_bookmark_id ALIAS FOR $1; -- in bm_bookmarks.bookmark_id%TYPE,
p_browsing_user_id ALIAS FOR $2; -- in bm_bookmarks.owner_id%TYPE
@@ -645,7 +645,7 @@
perform bookmark__update_in_closed_p_one_user (p_bookmark_id, p_browsing_user_id);
RETURN 0;
END;
-' LANGUAGE 'plpgsql';
+$$ LANGUAGE plpgsql;
CREATE FUNCTION bookmark__toggle_open_close_all (integer, boolean, integer)
RETURNS integer AS '
@@ -823,16 +823,16 @@
' LANGUAGE 'plpgsql';
-CREATE FUNCTION bookmark__initialize_in_closed_p (integer, integer, integer)
-RETURNS integer AS '
+CREATE OR REPLACE FUNCTION bookmark__initialize_in_closed_p (integer, bigint, integer)
+RETURNS integer AS $$
DECLARE
- p_viewed_user_id ALIAS FOR $1; -- in users.user_id%TYPE,
- p_in_closed_p_id ALIAS FOR $2; -- in users.user_id%TYPE
- p_package_id ALIAS FOR $3; -- in apm_packages.package_id%TYPE
- v_root_id bm_bookmarks.bookmark_id%TYPE;
- c_bookmark RECORD;
- v_in_closed_p bm_in_closed_p.in_closed_p%TYPE;
- v_closed_p bm_in_closed_p.closed_p%TYPE;
+ p_viewed_user_id ALIAS FOR $1; -- in users.user_id%TYPE
+ p_in_closed_p_id ALIAS FOR $2; -- in users.user_id%TYPE
+ p_package_id ALIAS FOR $3; -- in apm_packages.package_id%TYPE
+ v_root_id bm_bookmarks.bookmark_id%TYPE;
+ c_bookmark RECORD;
+ v_in_closed_p bm_in_closed_p.in_closed_p%TYPE;
+ v_closed_p bm_in_closed_p.closed_p%TYPE;
BEGIN
-- We want to initialize all bookmarks to the closed state, except for
-- the root folder. That means we need to have the following settings
@@ -885,5 +885,5 @@
RETURN 0;
END;
-' LANGUAGE 'plpgsql';
+$$ LANGUAGE plpgsql;
Index: openacs-4/packages/bookmarks/sql/postgresql/upgrade/upgrade-5.1-5.2.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/bookmarks/sql/postgresql/upgrade/upgrade-5.1-5.2.sql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/bookmarks/sql/postgresql/upgrade/upgrade-5.1-5.2.sql 26 Jan 2016 08:43:56 -0000 1.1
@@ -0,0 +1,145 @@
+ALTER TABLE bm_in_closed_p ALTER COLUMN in_closed_p_id TYPE bigint;
+
+DROP FUNCTION IF EXISTS bookmark__toggle_open_close (integer, integer);
+DROP FUNCTION IF EXISTS bookmark__update_in_closed_p_one_user (integer, integer);
+DROP FUNCTION IF EXISTS bookmark__initialize_in_closed_p (integer, integer, integer);
+
+CREATE OR REPLACE FUNCTION bookmark__toggle_open_close (integer, bigint)
+RETURNS integer AS $$
+DECLARE
+ p_bookmark_id ALIAS FOR $1; -- in bm_bookmarks.bookmark_id%TYPE,
+ p_browsing_user_id ALIAS FOR $2; -- in bm_bookmarks.owner_id%TYPE
+
+BEGIN
+ -- Toggle the closed_p flag
+ UPDATE bm_in_closed_p SET closed_p =
+ (
+ SELECT CASE WHEN closed_p = TRUE THEN FALSE ELSE TRUE END
+ FROM bm_in_closed_p
+ WHERE bookmark_id = p_bookmark_id
+ AND in_closed_p_id = p_browsing_user_id
+ )
+ WHERE bookmark_id = p_bookmark_id
+ AND in_closed_p_id = p_browsing_user_id;
+
+ -- Now update the in_closed_p status for this user for all bookmarks under
+ -- the toggled folder
+ perform bookmark__update_in_closed_p_one_user (p_bookmark_id, p_browsing_user_id);
+ RETURN 0;
+END;
+$$ LANGUAGE plpgsql;
+
+
+CREATE OR REPLACE FUNCTION bookmark__update_in_closed_p_one_user (integer, bigint)
+RETURNS integer AS $$
+DECLARE
+ p_bookmark_id ALIAS FOR $1; -- in bm_bookmarks.bookmark_id%TYPE,
+ p_browsing_user_id ALIAS FOR $2; -- in bm_bookmarks.owner_id%TYPE
+ v_parent_ids RECORD;
+
+BEGIN
+ -- Update the in_closed_p flag of bookmarks and folders that lie under
+ -- the toggled folder in the tree for one particular user/session.
+ -- First set all in_closed_p flags to f ...
+ UPDATE bm_in_closed_p SET in_closed_p = FALSE
+ WHERE bookmark_id IN
+ (
+ select bm.bookmark_id from bm_bookmarks bm, bm_bookmarks bm2
+ where bm2.bookmark_id = p_bookmark_id
+ and bm.tree_sortkey between bm2.tree_sortkey and tree_right(bm2.tree_sortkey)
+ )
+ AND in_closed_p_id = p_browsing_user_id;
+
+ -- then set all in_closed_p flags to t that lie under a closed folder
+ FOR v_parent_ids IN
+ select bm.bookmark_id from
+ bm_bookmarks bm, bm_in_closed_p bip
+ where bm.bookmark_id = bip.bookmark_id
+ and bm.folder_p = 't'
+ and bip.closed_p = 't'
+ and bip.in_closed_p_id = p_browsing_user_id
+ LOOP
+ UPDATE bm_in_closed_p set in_closed_p = TRUE
+ WHERE bookmark_id IN
+ (
+ select bm.bookmark_id from bm_bookmarks bm, bm_bookmarks bm2
+ where bm2.bookmark_id = v_parent_ids.bookmark_id
+ and bm.tree_sortkey between bm2.tree_sortkey and tree_right(bm2.tree_sortkey)
+ INTERSECT
+ select bm.bookmark_id from bm_bookmarks bm, bm_bookmarks bm2
+ where bm2.bookmark_id = p_bookmark_id
+ and bm.tree_sortkey between bm2.tree_sortkey and tree_right(bm2.tree_sortkey)
+ )
+ AND in_closed_p_id = p_browsing_user_id
+ AND bookmark_id <> v_parent_ids.bookmark_id
+ AND bookmark_id <> p_bookmark_id;
+ END LOOP;
+ RETURN 0;
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE OR REPLACE FUNCTION bookmark__initialize_in_closed_p (integer, bigint, integer)
+RETURNS integer AS $$
+DECLARE
+ p_viewed_user_id ALIAS FOR $1; -- in users.user_id%TYPE
+ p_in_closed_p_id ALIAS FOR $2; -- in users.user_id%TYPE
+ p_package_id ALIAS FOR $3; -- in apm_packages.package_id%TYPE
+ v_root_id bm_bookmarks.bookmark_id%TYPE;
+ c_bookmark RECORD;
+ v_in_closed_p bm_in_closed_p.in_closed_p%TYPE;
+ v_closed_p bm_in_closed_p.closed_p%TYPE;
+BEGIN
+ -- We want to initialize all bookmarks to the closed state, except for
+ -- the root folder. That means we need to have the following settings
+ -- based on the tree_level the bookmark/folder is at:
+ -- bookmark type in_closed_p closed_p tree_level
+ -- -------------- ----------- -------- ----------
+ -- root f f 1
+ -- top folders/bm f t 2
+ -- all others t t 3+
+
+ -- The bookmarks package can be mounted a number of times, and the same
+ -- user can have bookmarks at more than one mount point, so we need to
+ -- pick the right root_folder:
+ v_root_id := bookmark__get_root_folder(p_package_id, p_viewed_user_id);
+
+ FOR c_bookmark IN
+ SELECT bookmark_id, tree_level(tree_sortkey) AS t_level FROM bm_bookmarks
+ WHERE owner_id = p_viewed_user_id
+ AND bookmark_id IN
+ (
+ -- Select bookmarks that belong to the root of this package_id only
+ SELECT bm.bookmark_id FROM bm_bookmarks bm, bm_bookmarks bm2
+ WHERE bm2.bookmark_id = v_root_id
+ AND bm.tree_sortkey BETWEEN bm2.tree_sortkey AND tree_right(bm2.tree_sortkey)
+ )
+ AND bookmark_id NOT IN
+ (
+ SELECT bookmark_id FROM bm_in_closed_p
+ WHERE in_closed_p_id = p_in_closed_p_id
+ )
+ LOOP
+ IF c_bookmark.t_level = 1 THEN
+ v_in_closed_p := FALSE;
+ v_closed_p := FALSE;
+ ELSIF c_bookmark.t_level = 2 THEN
+ v_in_closed_p := FALSE;
+ v_closed_p := TRUE;
+ ELSE
+ v_in_closed_p := TRUE;
+ v_closed_p := TRUE;
+ END IF;
+
+ INSERT INTO bm_in_closed_p (bookmark_id, in_closed_p_id, in_closed_p, closed_p, creation_date)
+ VALUES (c_bookmark.bookmark_id, p_in_closed_p_id, v_in_closed_p, v_closed_p, now());
+ -- This is not quite right in the case bm_in_closed_p already contains some entries for
+ -- this p_in_closed_p_id, but it is no worse than what we had before so it will do for now.
+ -- in_closed_p should really be based on the parent folder state -- and the parent folder
+ -- must be inserted first.
+ END LOOP;
+
+ RETURN 0;
+END;
+$$ LANGUAGE plpgsql;
+
+