Index: openacs-4/packages/static-pages/sql/postgresql/static-pages-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/static-pages/sql/postgresql/static-pages-create.sql,v
diff -u -r1.16 -r1.17
--- openacs-4/packages/static-pages/sql/postgresql/static-pages-create.sql	3 Dec 2001 16:58:46 -0000	1.16
+++ openacs-4/packages/static-pages/sql/postgresql/static-pages-create.sql	8 Dec 2001 01:18:00 -0000	1.17
@@ -24,7 +24,7 @@
                         references sp_folders(folder_id),
         package_id	integer constraint sp_folders_package_id_fk
                         references apm_packages,
-	tree_sortkey	varchar(4000)
+	tree_sortkey	varbit
 );
 
 
@@ -58,30 +58,24 @@
 
 create function sp_folders_insert_tr () returns opaque as '
 declare
-        v_parent_sk     varchar;
-        max_key         varchar;
+        v_parent_sk     varbit default null;
+        v_max_value     integer;
 begin
-	 if new.parent_id is null then
-            select max(tree_sortkey) into max_key
+	if new.parent_id is null then
+            select max(tree_leaf_key_to_int(tree_sortkey)) into v_max_value
               from sp_folders
              where parent_id is null;
-
-            v_parent_sk := '''';
 	else
-
-	select max(tree_sortkey) into max_key 
+	  select max(tree_leaf_key_to_int(tree_sortkey)) into v_max_value 
           from sp_folders 
-         where parent_id = new.parent_id;
+          where parent_id = new.parent_id;
 
-        select coalesce(max(tree_sortkey),'''') into v_parent_sk 
+          select tree_sortkey into v_parent_sk 
           from sp_folders 
-         where folder_id = new.parent_id;
+          where folder_id = new.parent_id;
 	end if;
-        new.tree_sortkey := v_parent_sk || ''/'' || tree_next_key(max_key);
-
-
+        new.tree_sortkey := tree_next_key(v_parent_sk, v_max_value);
         return new;
-
 end;' language 'plpgsql';
 
 create trigger sp_folders_insert_tr before insert 
@@ -90,8 +84,8 @@
 
 create function sp_folders_update_tr () returns opaque as '
 declare
-        v_parent_sk     varchar;
-        max_key         varchar;
+        v_parent_sk     varbit default null;
+        v_max_value     integer;
         v_rec           record;
         clr_keys_p      boolean default ''t'';
 begin
@@ -103,31 +97,27 @@
 
         end if;
 
-        for v_rec in select folder_id
+        for v_rec in select folder_id, parent_id
                        from sp_folders 
-                      where tree_sortkey like new.tree_sortkey || ''%''
+                      where tree_sortkey between new.tree_sortkey and tree_right(new.tree_sortkey)
                    order by tree_sortkey
         LOOP
             if clr_keys_p then
                update sp_folders set tree_sortkey = null
-               where tree_sortkey like new.tree_sortkey || ''%'';
+               where tree_sortkey between new.tree_sortkey and tree_right(new.tree_sortkey);
                clr_keys_p := ''f'';
             end if;
             
-            select max(tree_sortkey) into max_key
+            select max(tree_leaf_key_to_int(tree_sortkey)) into v_max_value
               from sp_folders 
-              where parent_id = (select parent_id 
-                                   from sp_folders 
-                                  where folder_id = v_rec.folder_id);
+              where parent_id = v_rec.parent_id;
 
-            select coalesce(max(tree_sortkey),'''') into v_parent_sk 
+            select tree_sortkey into v_parent_sk 
               from sp_folders 
-             where folder_id = (select parent_id
-                                   from sp_folders 
-                                  where folder_id = v_rec.folder_id);
+             where folder_id = v_rec.parent_id
 
             update sp_folders 
-               set tree_sortkey = v_parent_sk || ''/'' || tree_next_key(max_key)
+               set tree_sortkey = tree_next_key(v_parent_sk, v_max_value)
              where folder_id = v_rec.folder_id;
 
         end LOOP;
@@ -609,13 +599,12 @@
                 v_folder_row    RECORD;
                 v_page_row      RECORD;
         begin
-                for v_folder_row in 
-                        select folder_id from (
-                                select folder_id, tree_level(tree_sortkey)  as path_depth, tree_sortkey from sp_folders
-		where tree_sortkey like ( select tree_sortkey || ''%''
-		from sp_folders
-		where folder_id = p_folder_id)
-                        ) folders order by path_depth desc
+                for v_folder_row in
+                  select s1.folder_id, tree_level(s1.tree_sortkey) as path_depth
+                  from sp_folders s1, sp_folders s2
+                  where s2.folder_id = p_folder_id
+		    and s1.tree_sortkey between s2.tree_sortkey and tree_right(s2.tree_sortkey)
+                  order by path_depth desc
                  loop
                         for v_page_row in 
                                 select static_page_id from static_pages
@@ -647,19 +636,15 @@
            -- but aren''t in sp_extant_files
                 
 	for v_stale_file_row in 
-	   select static_page_id from static_pages
-		where folder_id in (
-		   select folder_id from sp_folders
-			where tree_sortkey like (
-			   select tree_sortkey || ''%''
-				from sp_folders
-				where folder_id = v_root_folder_id )
-				)
-		and
-		   static_page_id not in (
-			select static_page_id from
-			   sp_extant_files
-				where session_id = p_session_id )
+	   select sp.static_page_id 
+           from static_pages sp, sp_folders s1, sp_folders s2
+	   where sp.folder_id = s1.folder_id
+             and s2.folder_id = v_root_folder_id
+             and s1.tree_sortkey between s2.tree_sortkey and tree_right(s2.tree_sortkey)
+	     and not exists (select 1
+                             from sp_extant_files sef
+                             where sef.session_id = p_session_id
+                             and sp.static_page_id = sef.static_page_id)
 	loop
 		PERFORM static_page__delete(v_stale_file_row.static_page_id);
 	end loop;
@@ -681,10 +666,10 @@
                                         where session_id = p_session_id
                                 )
                         ) dead,
-                        (select folder_id,tree_level(tree_sortkey) as depth, tree_sortkey from sp_folders
-		where tree_sortkey like ( select tree_sortkey || ''%''
-		from sp_folders
-		where folder_id = v_root_folder_id)
+                        (select s1.folder_id,tree_level(s1.tree_sortkey) as depth
+                         from sp_folders s1, sp_folders s2
+                         where s2.folder_id = v_root_folder_id
+                           and s1.tree_sortkey between s2.tree_sortkey and tree_right(s2.tree_sortkey)
                         ) path
                         where dead.folder_id = path.folder_id
                                 and dead.folder_id <> v_root_folder_id
@@ -716,10 +701,9 @@
                 if p_recursive_p = ''t'' then
                         -- For each folder that is a descendant of item_id, grant.
                         for v_folder_row in 
-                                select folder_id from sp_folders
-		where tree_sortkey like ( select tree_sortkey || ''%''
-		from sp_folders
-		where folder_id = p_item_id)
+                          select s1.folder_id from sp_folders s1, sp_folders s2
+                          where s2.folder_id = p_item_id
+                            and s1.tree_sortkey between s2.tree_sortkey and tree_right(s2.tree_sortkey)
                          loop
                                 perform acs_permission__grant_permission(
 				v_folder_row.folder_id,	-- object_id 
@@ -729,13 +713,11 @@
                         end loop;
                         -- For each file that is a descendant of item_id, grant.
                         for v_file_row in 
-                                select static_page_id from static_pages
-                                where folder_id in (
-                                        select folder_id from sp_folders
-		where tree_sortkey like ( select tree_sortkey || ''%''
-		from sp_folders
-		where folder_id = p_item_id)
-                                )
+                          select sp.static_page_id
+                          from static_pages sp, sp_folders s1, sp_folders s2
+                          where sp.folder_id = s1.folder_id
+                            and s2.folder_id = p_item_id
+                            and s1.tree_sortkey between s2.tree_sortkey and tree_right(s2.tree_sortkey)
                          loop
                                 perform acs_permission__grant_permission(
 				v_file_row.static_page_id,	-- object_id 
@@ -771,10 +753,9 @@
                 if p_recursive_p = ''t'' then
                         -- For each folder that is a descendant of item_id, revoke.
                         for v_folder_row in 
-                                select folder_id from sp_folders
-		where tree_sortkey like ( select tree_sortkey || ''%''
-		from sp_folders
-		where folder_id = p_item_id)
+                          select s1.folder_id from sp_folders s1, sp_folders s2
+                          where s2.folder_id = p_item_id
+                            and s1.tree_sortkey between s2.tree_sortkey and tree_right(s2.tree_sortkey)
                          loop
                                 perform acs_permission__revoke_permission(
 				v_folder_row.folder_id,	-- object_id 
@@ -784,13 +765,11 @@
                         end loop;
                         -- For each file that is a descendant of item_id, revoke.
                         for v_file_row in 
-                                select static_page_id from static_pages
-                                where folder_id in (
-                                        select folder_id from sp_folders
-		where tree_sortkey like ( select tree_sortkey || ''%''
-		from sp_folders
-		where folder_id = p_item_id)
-                                )
+                          select sp.static_page_id
+                          from static_pages sp, sp_folders s1, sp_folders s2
+                          where sp.folder_id = s1.folder_id
+                            and s2.folder_id = p_item_id
+                            and s1.tree_sortkey between s2.tree_sortkey and tree_right(s2.tree_sortkey)
                          loop
                                 perform acs_permission__revoke_permission(
 				v_file_row.static_page_id,	-- object_id 
