Index: openacs-4/packages/acs-kernel/sql/postgresql/site-nodes-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/postgresql/site-nodes-create.sql,v
diff -u -r1.30 -r1.31
--- openacs-4/packages/acs-kernel/sql/postgresql/site-nodes-create.sql	1 May 2018 10:45:43 -0000	1.30
+++ openacs-4/packages/acs-kernel/sql/postgresql/site-nodes-create.sql	3 Sep 2024 15:37:33 -0000	1.31
@@ -7,7 +7,6 @@
 --
 
 
-
 --
 -- procedure inline_0/0
 --
@@ -59,191 +58,29 @@
         -- mapped to this node?
         pattern_p       boolean default false not null,
         object_id       integer constraint site_nodes_object_id_fk
-                        references acs_objects (object_id),
-        tree_sortkey    varbit
+                        references acs_objects (object_id)
 );
 
+--
+-- Avoid potential loops on site_node parent_ids. A parent_id must be
+-- different from the node_id. 
+-- Note that this constraint is not guaranteed to avoid all loops; 
+-- it is still possible to create indirect recursive
+-- loops but excludes some real-world problems.
+--
+ALTER TABLE site_nodes ADD CONSTRAINT site_nodes_parent_id_ck CHECK (node_id <> parent_id);
+
 create index site_nodes_object_id_idx on site_nodes (object_id);
 create index site_nodes_parent_object_node_id_idx on site_nodes(parent_id, object_id, node_id);
-create index site_nodes_tree_skey_idx on site_nodes (tree_sortkey);
 create index site_nodes_parent_id_idx on site_nodes(parent_id);
 
-
 --
--- procedure site_node_get_tree_sortkey/1
+-- procedure site_node__new/8
 --
-select define_function_args('site_node_get_tree_sortkey','node_id');
 
-CREATE OR REPLACE FUNCTION site_node_get_tree_sortkey(
-   p_node_id integer
-) RETURNS varbit AS $$
-DECLARE
-BEGIN
-  return tree_sortkey from site_nodes where node_id = p_node_id;
-END;
-$$ LANGUAGE plpgsql stable strict;
-
-
-
---
--- procedure site_node_insert_tr/0
---
-CREATE OR REPLACE FUNCTION site_node_insert_tr(
-
-) RETURNS trigger AS $$
-DECLARE
-        v_parent_sk     varbit default null;
-        v_max_value     integer;
-BEGIN
-        if new.parent_id is null then
-            select max(tree_leaf_key_to_int(tree_sortkey)) into v_max_value 
-              from site_nodes 
-             where parent_id is null;
-        else
-            select max(tree_leaf_key_to_int(tree_sortkey)) into v_max_value 
-              from site_nodes 
-             where parent_id = new.parent_id;
-
-            select tree_sortkey into v_parent_sk 
-              from site_nodes 
-             where node_id = new.parent_id;
-        end if;
-
-        new.tree_sortkey := tree_next_key(v_parent_sk, v_max_value);
-
-        return new;
-
-END;
-$$ LANGUAGE plpgsql;
-
-create trigger site_node_insert_tr before insert 
-on site_nodes for each row 
-execute procedure site_node_insert_tr ();
-
-
-
---
--- procedure site_node_update_tr/0
---
-CREATE OR REPLACE FUNCTION site_node_update_tr(
-
-) RETURNS trigger AS $$
-DECLARE
-        v_parent_sk     varbit default null;
-        v_max_value     integer;
-        p_id            integer;
-        v_rec           record;
-        clr_keys_p      boolean default 't';
-BEGIN
-        if new.node_id = old.node_id and 
-           ((new.parent_id = old.parent_id) or 
-            (new.parent_id is null and old.parent_id is null)) then
-
-           return new;
-
-        end if;
-
-        for v_rec in select node_id
-                       from site_nodes 
-                      where tree_sortkey between new.tree_sortkey and tree_right(new.tree_sortkey)
-                   order by tree_sortkey
-        LOOP
-            if clr_keys_p then
-               update site_nodes set tree_sortkey = null
-               where tree_sortkey between new.tree_sortkey and tree_right(new.tree_sortkey);
-               clr_keys_p := 'f';
-            end if;
-            
-            select parent_id into p_id
-              from site_nodes 
-             where node_id = v_rec.node_id;
-
-            if p_id is null then
-                select max(tree_leaf_key_to_int(tree_sortkey)) into v_max_value
-                  from site_nodes
-                 where parent_id is null;
-            else
-                select max(tree_leaf_key_to_int(tree_sortkey)) into v_max_value
-                  from site_nodes 
-                 where parent_id = p_id;
-
-                select tree_sortkey into v_parent_sk 
-                  from site_nodes 
-                 where node_id = p_id;
-            end if;
-
-            update site_nodes 
-               set tree_sortkey = tree_next_key(v_parent_sk, v_max_value)
-             where node_id = v_rec.node_id;
-
-        end LOOP;
-
-        return new;
-
-END;
-$$ LANGUAGE plpgsql;
-
-create trigger site_node_update_tr after update 
-on site_nodes
-for each row 
-execute procedure site_node_update_tr ();
-
-
--- create or replace package site_node
--- as
--- 
---   -- Create a new site node. If you set directory_p to be 'f' then you
---   -- cannot create nodes that have this node as their parent.
--- 
---   function new (
---     node_id             in site_nodes.node_id%TYPE default null,
---     parent_id           in site_nodes.node_id%TYPE default null,
---     name                in site_nodes.name%TYPE,
---     object_id           in site_nodes.object_id%TYPE default null,
---     directory_p         in site_nodes.directory_p%TYPE,
---     pattern_p           in site_nodes.pattern_p%TYPE default 'f',
---     creation_user       in acs_objects.creation_user%TYPE default null,
---     creation_ip         in acs_objects.creation_ip%TYPE default null
---   ) return site_nodes.node_id%TYPE;
--- 
---   -- Delete a site node.
--- 
---   procedure delete (
---     node_id             in site_nodes.node_id%TYPE
---   );
--- 
---   -- Return the node_id of a URL. If the url begins with '/' then the
---   -- parent_id must be null. This will raise the no_data_found
---   -- exception if there is no mathing node in the site_nodes table.
---   -- This will match directories even if no trailing slash is included
---   -- in the url.
--- 
---   function node_id (
---     url                 in varchar2,
---     parent_id   in site_nodes.node_id%TYPE default null
---   ) return site_nodes.node_id%TYPE;
--- 
---   -- Return the url of a node_id.
--- 
---   function url (
---     node_id             in site_nodes.node_id%TYPE
---   ) return varchar2;
--- 
--- end;
-
--- show errors
-
-
--- old define_function_args ('site_node__new', 'node_id,parent_id,name,object_id,directory_p,pattern_p,creation_user,creation_ip')
--- new
 select define_function_args('site_node__new','node_id;null,parent_id;null,name,object_id;null,directory_p,pattern_p;f,creation_user;null,creation_ip;null');
 
 
-
-
---
--- procedure site_node__new/8
---
 CREATE OR REPLACE FUNCTION site_node__new(
    new__node_id integer,       -- default null
    new__parent_id integer,     -- default null
@@ -288,7 +125,7 @@
       new__directory_p, new__pattern_p);
 
      return v_node_id;
-   
+
 END;
 $$ LANGUAGE plpgsql;
 
@@ -312,7 +149,7 @@
 
     PERFORM acs_object__delete(delete__node_id);
 
-    return 0; 
+    return 0;
 END;
 $$ LANGUAGE plpgsql;
 
@@ -347,7 +184,7 @@
     else
       return site_node__find_pattern(v_parent_id);
     end if;
-   
+
 END;
 $$ LANGUAGE plpgsql;
 
@@ -364,12 +201,12 @@
 DECLARE
   v_pos                  integer;
   v_first                site_nodes.name%TYPE;
-  v_rest                 text; 
-  v_node_id              integer;       
+  v_rest                 text;
+  v_node_id              integer;
   v_pattern_p            site_nodes.pattern_p%TYPE;
-  v_url                  text; 
+  v_url                  text;
   v_directory_p          site_nodes.directory_p%TYPE;
-  v_trailing_slash_p     boolean;       
+  v_trailing_slash_p     boolean;
 BEGIN
     v_url := p_url;
 
@@ -383,7 +220,7 @@
     end if;
 
     --
-    -- Split the URL on the first "/" into v_first and v_rest. 
+    -- Split the URL on the first "/" into v_first and v_rest.
     --
     select position('/' in v_url) into v_pos;
 
@@ -401,7 +238,7 @@
       v_rest := substr(v_url, v_pos + 1);
     end if;
 
-    if p_parent_id is not null then 
+    if p_parent_id is not null then
       select node_id, directory_p into v_node_id, v_directory_p
       from site_nodes
       where parent_id = p_parent_id
@@ -412,16 +249,16 @@
       --
       select node_id, directory_p into v_node_id, v_directory_p
       from site_nodes
-      where parent_id is null 
+      where parent_id is null
       and name = v_first;
     end if;
 
-    if NOT FOUND then 
+    if NOT FOUND then
         return site_node__find_pattern(p_parent_id);
     end if;
 
     --
-    -- v_first was found. 
+    -- v_first was found.
     --
     if v_rest is null then
       --
@@ -454,33 +291,32 @@
 RETURNS integer AS $inline_0$
 BEGIN
 
-    raise notice 'starting site-nodes doing the recursive part -- vguerra';
+    -- raise notice 'starting site-nodes doing the recursive part -- vguerra';
 
     IF cmp_pg_version('8.4') >= 0 THEN
         -- recursive site_nodes recursive - START
 
         CREATE OR REPLACE FUNCTION site_node__url(
            url__node_id integer
         ) RETURNS varchar AS $$
-        BEGIN
-            return ( With RECURSIVE site_nodes_recursion(parent_id, path, directory_p, node_id) as (
-            
+
+            WITH RECURSIVE site_nodes_path(parent_id, path, directory_p, node_id) as (
+
                 select parent_id, ARRAY[name || case when directory_p then '/' else ' ' end]::text[] as path, directory_p, node_id
                 from site_nodes where node_id = url__node_id
-            
+
                 UNION ALL
-            
+
                 select sn.parent_id, sn.name::text || snr.path , sn.directory_p, snr.parent_id
-                from site_nodes sn join site_nodes_recursion snr on sn.node_id = snr.parent_id 
-                where snr.parent_id is not null    
+                from site_nodes sn join site_nodes_path snr on sn.node_id = snr.parent_id
+                where snr.parent_id is not null
 
-            ) select array_to_string(path,'/') from site_nodes_recursion where parent_id is null
-        );
-        END; 
-        $$ LANGUAGE plpgsql; 
+            ) select array_to_string(path,'/') from site_nodes_path where parent_id is null
 
+        $$ LANGUAGE sql strict stable;
+
         -- recursive site_nodes END
-    
+
     ELSE
 
         CREATE OR REPLACE FUNCTION site_node__url(
@@ -505,16 +341,21 @@
             else
               return site_node__url(v_parent_id) || v_name;
             end if;
-           
+
         END;
         $$ LANGUAGE plpgsql;
 
     END IF;
-    
+
     return null;
-END; 
+END;
 $inline_0$ LANGUAGE plpgsql;
 
 select inline_0();
 drop function inline_0();
 
+--
+-- Local variables:
+--   mode: sql
+--   indent-tabs-mode: nil
+-- End:
