OpenACS

arjun
committed
MAIN:arjun:20020531114055
on 31 May 02
fix create script
/upgrade/upgrade-5.2.0d1-5.2.0d2.sql (+180)
  1 create or replace function static_page__get_root_folder (
  2                 integer         -- package_id   in apm_packages.package_id%TYPE
  3         ) returns integer as '
  4 declare
  5                 p_package_id            alias for $1;
  6                 v_folder_exists_p       integer;
  7                 v_folder_id             sp_folders.folder_id%TYPE;
  8                 v_rows                  integer;
  9 begin
  10                 -- If there isn''t a root folder for this package, create one.
  11                 -- Otherwise, just return its id.
  12                 select count(*) into v_folder_exists_p where exists (
  13                         select 1 from sp_folders
  14                         where package_id = p_package_id
  15                         and parent_id is null
  16                 );
  17
  18                 if v_folder_exists_p = 0 then
  19                         -- name NEEDS to be unique, label does not
  20                         v_folder_id := static_page__new_folder (
  21                                 null,
  22                                 ''sp_root_package_id_'' || p_package_id,      -- name
  23                                 ''sp_root_package_id_'' || p_package_id,       -- label
  24                                 null,
  25                                 null,
  26                                 null,
  27                                 null,
  28                                 null,
  29                                 null,
  30                                 p_package_id
  31                         );
  32
  33                         update sp_folders
  34                                 set package_id = p_package_id
  35                                 where folder_id = v_folder_id;
  36
  37                         PERFORM acs_permission__grant_permission (
  38                                 v_folder_id,                            -- object_id
  39                                 acs__magic_object_id(''the_public''),   -- grantee_id
  40                                 ''general_comments_create''             -- privilege
  41                         );
  42                         -- The comments will inherit read permission from the pages,
  43                         -- so the public should be able to read the static pages.
  44                         PERFORM acs_permission__grant_permission (
  45                                 v_folder_id,                            -- object_id
  46                                 acs__magic_object_id(''the_public''),   -- grantee_id
  47                                 ''read''                                  -- privilege
  48                         );
  49                 else
  50                         select folder_id into v_folder_id from sp_folders
  51                         where package_id = p_package_id
  52                         and parent_id is null;
  53                 end if;
  54
  55                 return v_folder_id;
  56 end;' language 'plpgsql';
  57
  58
  59 -- The one that does not take package_id should just go away.
  60 drop function static_page__new_folder (integer, character varying, character varying, text, integer, timestamp with time zone, integer, character varying, integer);
  61
  62 create or replace function static_page__new_folder (
  63                 integer,        -- folder_id    in sp_folders.folder_id%TYPE
  64                                 --        default null,
  65                 varchar,        -- name in cr_items.name%TYPE,
  66                 varchar,        -- label        in cr_folders.label%TYPE,
  67                 text,           -- description  in cr_folders.description%TYPE default null,
  68                 integer,        -- parent_id    in cr_items.parent_id%TYPE default null,
  69                 timestamptz,    -- creation_date        in acs_objects.creation_date%TYPE
  70                                 --        default sysdate,
  71                 integer,        -- creation_user        in acs_objects.creation_user%TYPE
  72                                 --        default null,
  73                 varchar,        -- creation_ip  in acs_objects.creation_ip%TYPE
  74                                 --        default null,
  75                 integer,        -- context_id   in acs_objects.context_id%TYPE
  76                                 --        default null
  77                 integer         -- package_id
  78         ) returns integer as '
  79         declare
  80                 p_folder_id       alias for $1;
  81                 p_name            alias for $2;
  82                 p_label           alias for $3;
  83                 p_description     alias for $4;
  84                 p_parent_id       alias for $5;
  85                 p_creation_date   alias for $6;
  86                 p_creation_user   alias for $7;
  87                 p_creation_ip     alias for $8;
  88                 p_context_id      alias for $9;
  89                 p_package_id      alias for $10;
  90
  91                 v_folder_id             sp_folders.folder_id%TYPE;
  92                 v_parent_id             cr_items.parent_id%TYPE;
  93                 v_package_id            apm_packages.package_id%TYPE;
  94                 v_creation_date         acs_objects.creation_date%TYPE;
  95                 v_permission_row        RECORD;
  96         begin
  97                 if p_creation_date is null then
  98                         v_creation_date := now();
  99                 else
  100                         v_creation_date := p_creation_date;
  101                 end if;
  102
  103                 if p_parent_id is null then
  104                         v_parent_id := 0;
  105                 else
  106                         v_parent_id := p_parent_id;
  107                 end if;
  108
  109
  110                 if p_parent_id is not null then
  111                         if p_package_id is null then
  112                                 -- Get the package_id from the parent:
  113                                 select package_id into v_package_id from sp_folders
  114                                         where folder_id = p_parent_id;
  115                         else
  116                                 v_package_id := p_package_id;
  117                         end if;
  118                 else
  119                         v_package_id := p_package_id;
  120                 end if;
  121
  122                 v_folder_id := content_folder__new (
  123                         p_name,            -- name
  124                         p_label,           -- label
  125                         p_description,     -- description               
  126                         v_parent_id,       -- parent_id
  127                         p_context_id,      -- context_id
  128                         p_folder_id,       -- folder_id
  129                         v_creation_date,   -- creation_date
  130                         p_creation_user,   -- creation_user
  131                         p_creation_ip,     -- creation_ip
  132                         ''f'',             -- secuity_inherit_p 
  133                         v_package_id
  134                 );
  135
  136
  137                 if p_parent_id is not null then
  138                         insert into sp_folders (folder_id, parent_id, package_id)
  139                                 values (v_folder_id, p_parent_id, v_package_id);
  140
  141 --                        update acs_objects set security_inherit_p = ''f''
  142 --                                where object_id = v_folder_id;
  143
  144                         -- Copy permissions from the parent:
  145                         for v_permission_row in
  146                                 select * from acs_permissions
  147                                         where object_id = p_parent_id
  148                         loop
  149                                 perform acs_permission__grant_permission(
  150                                         v_folder_id,                    -- object_id
  151                                         v_permission_row.grantee_id,    -- grantee_id
  152                                         v_permission_row.privilege      -- privilege
  153                                 );
  154                         end loop;
  155                 else
  156                         insert into sp_folders (folder_id, parent_id, package_id)
  157                                 values (v_folder_id, p_parent_id, p_package_id);
  158
  159                         -- if it''s a root folder, allow it to contain static pages and
  160                         -- other folders (subfolders will inherit these properties)
  161                         PERFORM  content_folder__register_content_type (
  162                                         v_folder_id,              -- folder_id
  163                                         ''static_page'',           -- content_type
  164                                         ''f''
  165                                 );
  166                         PERFORM  content_folder__register_content_type (
  167                                         v_folder_id,            -- folder_id
  168                                         ''content_revision'',      -- content_type
  169                                         ''f''
  170                                 );
  171                         PERFORM  content_folder__register_content_type (
  172                                         v_folder_id,            -- folder_id
  173                                         ''content_folder'',      -- content_type
  174                                         ''f''
  175                                 );
  176                 end if;
  177
  178                 return v_folder_id;
  179 end;' language 'plpgsql';
  180