Index: openacs-4/packages/bookmarks/sql/postgresql/upgrade/upgrade-5.0d1-5.0d2.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/bookmarks/sql/postgresql/upgrade/upgrade-5.0d1-5.0d2.sql,v
diff -u -N -r1.1.2.1 -r1.1.2.2
--- openacs-4/packages/bookmarks/sql/postgresql/upgrade/upgrade-5.0d1-5.0d2.sql	30 Jun 2004 05:28:32 -0000	1.1.2.1
+++ openacs-4/packages/bookmarks/sql/postgresql/upgrade/upgrade-5.0d1-5.0d2.sql	4 Oct 2004 04:47:32 -0000	1.1.2.2
@@ -50,4 +50,69 @@
 end;' language 'plpgsql';
 
 
+DROP FUNCTION bookmark__initialize_in_closed_p (integer, integer);
 
+CREATE FUNCTION bookmark__initialize_in_closed_p (integer, integer, 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';
+