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 -N -r1.3 -r1.4
--- openacs-4/packages/static-pages/sql/postgresql/static-pages-create.sql 2 Jul 2001 19:39:43 -0000 1.3
+++ openacs-4/packages/static-pages/sql/postgresql/static-pages-create.sql 24 Jul 2001 19:51:56 -0000 1.4
@@ -24,8 +24,12 @@
parent_id integer constraint sp_folders_parent_id_fk
references sp_folders(folder_id),
package_id integer constraint sp_folders_package_id_fk
- references apm_packages
+ references apm_packages,
+ tree_sortkey varchar(4000)
);
+
+
+
comment on table sp_folders is '
Holds the folder hierarchy, mirroring the structure of the folders in
the content repository. Used for navigating through the static
@@ -48,6 +52,84 @@
create index sp_folders_parent_id_idx on sp_folders(parent_id);
create index sp_folders_package_id_idx on sp_folders(package_id);
+--tree_sortkey index DaveB
+create index sp_folders_tree_skey_idx on sp_folders (tree_sortkey);
+
+-- tree_sortkey triggers DaveB
+
+create function sp_folders_insert_tr () returns opaque as '
+declare
+ v_parent_sk varchar;
+ max_key varchar;
+begin
+ select max(tree_sortkey) into max_key
+ from sp_folders
+ where parent_id = new.parent_id;
+
+ select coalesce(max(tree_sortkey),'''') into v_parent_sk
+ from sp_folders
+ where folder_id = new.folder_id;
+
+ new.tree_sortkey := v_parent_sk || ''/'' || tree_next_key(max_key);
+
+ return new;
+
+end;' language 'plpgsql';
+
+create trigger sp_folders_insert_tr before insert
+on sp_folders for each row
+execute procedure sp_folders_insert_tr ();
+
+create function sp_folders_update_tr () returns opaque as '
+declare
+ v_parent_sk varchar;
+ max_key varchar;
+ v_rec record;
+ clr_keys_p boolean default ''t'';
+begin
+ if new.folder_id = old.folder_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 folder_id
+ from sp_folders
+ where tree_sortkey like 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 || ''%'';
+ clr_keys_p := ''f'';
+ end if;
+
+ select max(tree_sortkey) into max_key
+ from sp_folders
+ where parent_id = (select parent_id
+ from sp_folders
+ where folder_id = v_rec.folder_id);
+
+ select coalesce(max(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);
+
+ update sp_folders
+ set tree_sortkey = v_parent_sk || ''/'' || tree_next_key(max_key)
+ where folder_id = v_rec.folder_id;
+
+ end LOOP;
+
+ return new;
+
+end;' language 'plpgsql';
+-- end of tree_sortkey triggers DaveB
+
+
-- The title and content of each page will go into cr_revisions. Here
-- we record the filename.
--
@@ -131,10 +213,11 @@
';
-- Here's where we get our session_ids:
-create sequence sp_session_id_seq;
-create view sp_session_id_sequence as select nextval('sp_session_id_seq') as nextval;
+create sequence sp_session_id_sequence;
+create view sp_session_id_seq as select nextval('sp_session_id_sequence') as nextval;
+
-- this also creates the acs_object type
select content_type__create_type (
'static_page', -- content_type
@@ -160,6 +243,7 @@
-- create or replace package body static_page as
+-- create or replace package body static_page as
create function static_page__new (
integer, -- static_page_id in static_pages.static_page_id%TYPE
-- default null,
@@ -190,39 +274,49 @@
p_context_id alias for $10;
v_item_id static_pages.static_page_id%TYPE;
- v_permission_row acs_permissions%ROWTYPE;
+ v_permission_row RECORD;
+ v_revision_id integer;
+ v_is_live boolean default ''t'';
+ v_mime_type cr_revisions.mime_type%TYPE default ''text/html'';
+ v_storage_type cr_items.storage_type%TYPE default ''text'';
begin
-- Create content item; this also makes the content revision.
-- One might be tempted to set the content_type to static_page,
-- But this would confuse site-wide-search, which expects to
-- see a content_type of content_revision.
+
v_item_id := content_item__new(
p_static_page_id, -- item_id
- p_folder_id, -- parent_id
- p_filename, -- name
+ p_filename, -- name
+ p_folder_id, -- parent_id
p_title, -- title
- ''text/html'', -- mime_type
p_creation_date, -- creation_date
p_creation_user, -- creation_user
- p_creation_ip, -- creation_ip
p_context_id, -- context_id
- ''t'', -- is_live
- p_content -- data
+ p_creation_ip, -- creation_ip
+ v_is_live, -- is_live
+ v_mime_type, -- mime_type
+ p_content, -- text
+ v_storage_type, -- storage_type
+ FALSE -- security_inherit_p
);
+
-- We want to be able to have non-commentable folders below
-- commentable folders. We can''t do this if we leave security
-- inheritance enabled.
--
- update acs_objects set security_inherit_p = ''f''
- where object_id = v_item_id;
+-- uses overloaded content_item__new and acs_object__new to set
+-- security_inherit_p to ''f'' DaveB
+-- update acs_objects set security_inherit_p = ''f''
+-- where object_id = v_item_id;
-- Copy permissions from the parent:
- for v_permission_row in (
+ for v_permission_row in
select grantee_id,privilege from acs_permissions
- where object_id = p_folder_id;
- ) loop
- acs_permission__grant_permission(
+ where object_id = p_folder_id
+ loop
+ perform acs_permission__grant_permission(
v_item_id, -- object_id
v_permission_row.grantee_id, -- grantee_id
v_permission_row.privilege -- privilege
@@ -242,6 +336,37 @@
return v_item_id;
end;' language 'plpgsql';
+create function static_page__new (
+
+ integer, -- folder_id in sp_folders.folder_id%TYPE,
+ varchar, -- filename in static_pages.filename%TYPE default null,
+ varchar -- title in cr_revisions.title%TYPE default null
+ ) returns integer as '
+ declare
+ p_folder_id alias for $1;
+ p_filename alias for $2;
+ p_title alias for $3;
+
+ v_static_page_id static_pages.static_page_id%TYPE;
+ v_item_id static_pages.static_page_id%TYPE;
+
+ begin
+ return static_page__new (
+ NULL, -- static_page_id
+ p_folder_id, -- folder_id
+ p_filename, -- filename
+ p_title, -- title
+ NULL, -- content
+ ''t'', -- show_comments_p
+ now(), -- creation_date
+ NULL, -- creation_user
+ NULL, -- creation_ip
+ NULL -- conext_id
+ );
+
+end;' language 'plpgsql';
+
+
create function static_page__delete (
integer -- static_page_id in static_pages.static_page_id%TYPE
) returns integer as '
@@ -255,25 +380,25 @@
-- Drop all comments on this page. general-comments doesn''t have
-- a comment.delete() function, so I just do this (see the
-- general-comments drop script):
- for v_comment_row in (
+ for v_comment_row in
select comment_id from general_comments
- where object_id = p_static_page_id;
- ) loop
+ where object_id = p_static_page_id
+ loop
delete from images
where image_id in (
select latest_revision
from cr_items
where parent_id = v_comment_row
- );
+ );
- acs_message__delete(v_comment_row);
+ PERFORM acs_message__delete(v_comment_row);
end loop;
-- Delete the page.
-- WE SHOULDN''T NEED TO DO THIS: CONTENT_ITEM.DELETE SHOULD TAKE CARE OF
-- DELETING FROM STATIC PAGES.
delete from static_pages where static_page_id = p_static_page_id;
- content_item__delete(p_static_page_id);
+ PERFORM content_item__delete(p_static_page_id);
return 0;
end;' language 'plpgsql';
@@ -294,23 +419,31 @@
);
if v_folder_exists_p = 0 then
+
v_folder_id := static_page__new_folder (
- ''sp_root'', -- name
- ''sp_root'' -- label
+ null,
+ ''sp_root'', -- name
+ ''sp_root'', -- label
+ null,
+ null,
+ null,
+ null,
+ null,
+ null
);
update sp_folders
set package_id = p_package_id
where folder_id = v_folder_id;
- acs_permission__grant_permission (
+ PERFORM acs_permission__grant_permission (
v_folder_id, -- object_id
acs__magic_object_id(''the_public''), -- grantee_id
''general_comments_create'' -- privilege
);
-- The comments will inherit read permission from the pages,
-- so the public should be able to read the static pages.
- acs_permission__grant_permission (
+ PERFORM acs_permission__grant_permission (
v_folder_id, -- object_id
acs__magic_object_id(''the_public''), -- grantee_id
''read'' -- privilege
@@ -373,13 +506,14 @@
v_folder_id := content_folder__new (
p_name, -- name
p_label, -- label
- p_folder_id, -- folder_id
+ p_description, -- description
v_parent_id, -- parent_id
- p_description, -- description
+ p_context_id, -- context_id
+ p_folder_id, -- folder_id
v_creation_date, -- creation_date
p_creation_user, -- creation_user
- p_creation_ip, -- creation_ip
- p_context_id -- context_id
+ p_creation_ip -- creation_ip
+
);
if p_parent_id is not null then
@@ -394,11 +528,11 @@
where object_id = v_folder_id;
-- Copy permissions from the parent:
- for v_permission_row in (
+ for v_permission_row in
select grantee_id,privilege from acs_permissions
- where object_id = p_parent_id;
- ) loop
- acs_permission__grant_permission(
+ where object_id = p_parent_id
+ loop
+ perform acs_permission__grant_permission(
v_folder_id, -- object_id
v_permission_row.grantee_id, -- grantee_id
v_permission_row.privilege -- privilege
@@ -410,17 +544,20 @@
-- if it''s a root folder, allow it to contain static pages and
-- other folders (subfolders will inherit these properties)
- content_folder__register_content_type (
+ PERFORM content_folder__register_content_type (
v_folder_id, -- folder_id
- ''static_page'' -- content_type
+ ''static_page'', -- content_type
+ ''f''
);
- content_folder__register_content_type (
+ PERFORM content_folder__register_content_type (
v_folder_id, -- folder_id
- ''content_revision'' -- content_type
+ ''content_revision'', -- content_type
+ ''f''
);
- content_folder__register_content_type (
+ PERFORM content_folder__register_content_type (
v_folder_id, -- folder_id
- ''content_folder'' -- content_type
+ ''content_folder'', -- content_type
+ ''f''
);
end if;
@@ -435,22 +572,23 @@
v_folder_row sp_folders.folder_id%TYPE;
v_page_row static_pages.static_page_id%TYPE;
begin
- for v_folder_row in (
+ for v_folder_row in
select folder_id from (
- select folder_id,level as path_depth from sp_folders
- start with folder_id = p_folder_id
- connect by parent_id = prior folder_id;
+ select folder_id, tree_level(''folder_id'') as path_depth from sp_folders
+ where tree_sortkey like ( select tree_sortkey || ''%''
+ from sp_folders
+ where folder_id = p_folder_id)
) order by path_depth desc
- ) loop
- for v_page_row in (
+ loop
+ for v_page_row in
select static_page_id from static_pages
- where folder_id = v_folder_row;
- ) loop
+ where folder_id = v_folder_row
+ loop
static_page__delete(v_page_row);
end loop;
delete from sp_folders where folder_id = v_folder_row;
- content_folder__delete(v_folder_row);
+ PERFORM content_folder__delete(v_folder_row);
end loop;
end;' language 'plpgsql';
@@ -462,27 +600,34 @@
p_session_id alias for $1;
p_package_id alias for $2;
v_root_folder_id sp_folders.folder_id%TYPE;
- v_stale_file_row static_pages.static_page_id%TYPE;
- v_stale_folder_row sp_folders.folder_id%TYPE;
+ v_stale_file_row static_pages%ROWTYPE;
+ v_stale_folder_row sp_folders%ROWTYPE;
begin
v_root_folder_id := static_page__get_root_folder(p_package_id);
-- First delete all files that are descendants of the root folder
- -- but aren''t in sp_extant_files:
- --
- for v_stale_file_row in (
+ -- 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
- start with folder_id = v_root_folder_id
- connect by parent_id = prior folder_id
- ) and static_page_id not in (
- select static_page_id
- from sp_extant_files
- where session_id = p_session_id
- );
- ) loop
- static_page__delete(v_stale_file_row);
+ where static_page_id not in (
+ select static_page_id
+ from sp_extant_files
+ where session_id = p_session_id
+ )
+
+-- 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
+-- )
+ loop
+ PERFORM static_page__delete(v_stale_file_row);
end loop;
-- Now delete all folders that aren''t in sp_extant_folders. There are two
@@ -493,7 +638,7 @@
-- show up in the filesystem search, so it will be missing from
-- sp_extant_folders.
--
- for v_stale_folder_row in (
+ for v_stale_folder_row in
select dead.folder_id from
(select folder_id from sp_folders
where (folder_id) not in (
@@ -502,22 +647,23 @@
where session_id = p_session_id
)
) dead,
- (select folder_id,level as depth from sp_folders
- start with folder_id = v_root_folder_id
- connect by parent_id = prior folder_id
+ (select folder_id,tree_level(''folder_id'') as depth from sp_folders
+ where tree_sortkey like ( select tree_sortkey || ''%''
+ from sp_folders
+ where folder_id = v_root_folder_id)
) path
where dead.folder_id = path.folder_id
and dead.folder_id <> v_root_folder_id
- order by path.depth desc;
- ) loop
+ order by path.depth desc
+ loop
delete from sp_folders
where folder_id = v_stale_folder_row;
- content_folder__delete(v_stale_folder_row);
+ perform content_folder__delete(v_stale_folder_row);
end loop;
return 0;
end;' language 'plpgsql';
-
+
create function static_page__grant_permission (
integer, -- item_id in acs_permissions.object_id%TYPE,
integer, -- grantee_id in acs_permissions.grantee_id%TYPE,
@@ -534,34 +680,36 @@
begin
if recursive_p = ''t'' then
-- For each folder that is a descendant of item_id, grant.
- for v_folder_row in (
+ for v_folder_row in
select folder_id from sp_folders
- start with folder_id = p_item_id
- connect by parent_id = prior folder_id;
- ) loop
- acs_permission__grant_permission(
+ where tree_sortkey like ( select tree_sortkey || ''%''
+ from sp_folders
+ where folder_id = p_item_id)
+ loop
+ perform acs_permission__grant_permission(
v_folder_row, -- object_id
p_grantee_id, -- grantee_id
p_privilege -- privilege
);
end loop;
-- For each file that is a descendant of item_id, grant.
- for file_row in (
+ for file_row in
select static_page_id from static_pages
where folder_id in (
select folder_id from sp_folders
- start with folder_id = p_item_id
- connect by parent_id = prior folder_id
- );
- ) loop
+ where tree_sortkey like ( select tree_sortkey || ''%''
+ from sp_folders
+ where folder_id = p_item_id)
+ )
+ loop
acs_permission__grant_permission(
v_file_row, -- object_id
p_grantee_id, -- grantee_id
p_privilege -- privilege
);
end loop;
else
- acs_permission__grant_permission(
+ perform acs_permission__grant_permission(
p_item_id, -- object_id
p_grantee_id, -- grantee_id
p_privilege -- privilege
@@ -586,34 +734,36 @@
begin
if p_recursive_p = ''t'' then
-- For each folder that is a descendant of item_id, revoke.
- for v_folder_row in (
+ for v_folder_row in
select folder_id from sp_folders
- start with folder_id = p_item_id
- connect by parent_id = prior folder_id;
- ) loop
- acs_permission__revoke_permission(
+ where tree_sortkey like ( select tree_sortkey || ''%''
+ from sp_folders
+ where folder_id = p_item_id)
+ loop
+ perform acs_permission__revoke_permission(
v_folder_row, -- object_id
p_grantee_id, -- grantee_id
p_privilege -- privilege
);
end loop;
-- For each file that is a descendant of item_id, revoke.
- for v_file_row in (
+ for v_file_row in
select static_page_id from static_pages
where folder_id in (
select folder_id from sp_folders
- start with folder_id = p_item_id
- connect by parent_id = prior folder_id;
+ where tree_sortkey like ( select tree_sortkey || ''%''
+ from sp_folders
+ where folder_id = p_item_id)
)
- ) loop
- acs_permission__revoke_permission(
+ loop
+ perform acs_permission__revoke_permission(
v_file_row, -- object_id
p_grantee_id, -- grantee_id
p_privilege -- privilege
);
end loop;
else
- acs_permission__revoke_permission(
+ perform acs_permission__revoke_permission(
p_item_id, -- object_id
p_grantee_id, -- grantee_id
p_privilege -- privilege
Index: openacs-4/packages/static-pages/sql/postgresql/static-pages-drop.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/static-pages/sql/postgresql/static-pages-drop.sql,v
diff -u -N -r1.2 -r1.3
--- openacs-4/packages/static-pages/sql/postgresql/static-pages-drop.sql 6 May 2001 19:54:15 -0000 1.2
+++ openacs-4/packages/static-pages/sql/postgresql/static-pages-drop.sql 24 Jul 2001 19:51:56 -0000 1.3
@@ -20,9 +20,9 @@
declare
v_root_folder_row sp_folders.folder_id%TYPE;
begin
- for v_root_folder_row in (
- select folder_id from sp_folders where parent_id is null;
- ) loop
+ for v_root_folder_row in
+ select folder_id from sp_folders where parent_id is null
+ loop
static_page__delete_folder(v_root_folder_row);
end loop;
return 0;
@@ -33,14 +33,19 @@
drop function inline__0();
+-- FIXME this won't work until content_type__drop_attribute in
+-- acs-content-repository/sql/postgresql/content-type.sql is fixed DaveB
+
-- Delete content type 'static_page' and its attributes.
-select content_type__drop_type (
- 'static_page', -- content_type
+select content_type__drop_type (
+ 'static_page', -- content_type
't', -- drop_children_p
'f' -- drop_table_p
);
+
+
select drop_package('static_page');
drop sequence sp_session_id_seq;
Index: openacs-4/packages/static-pages/tcl/static-pages-procs-postgresql.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/static-pages/tcl/static-pages-procs-postgresql.xql,v
diff -u -N -r1.2 -r1.3
--- openacs-4/packages/static-pages/tcl/static-pages-procs-postgresql.xql 6 May 2001 19:54:35 -0000 1.2
+++ openacs-4/packages/static-pages/tcl/static-pages-procs-postgresql.xql 24 Jul 2001 19:51:56 -0000 1.3
@@ -47,10 +47,11 @@
- select static_page.new(
+ select static_page__new(
+ :parent_folder_id, -- folder_id
:file, -- filename
- :page_title, -- title
- :parent_folder_id -- folder_id
+ :page_title -- title
+
);
@@ -69,10 +70,13 @@
- static_page__delete_stale_items(:sync_session_id,:package_id);
-
- delete from sp_extant_folders where session_id = :sync_session_id;
- delete from sp_extant_files where session_id = :sync_session_id;
+ begin
+ perform static_page__delete_stale_items(:sync_session_id,:package_id);
+-- delete from sp_extant_folders where session_id = :sync_session_id;
+--
+ delete from sp_extant_files where session_id = :sync_session_id;
+ return null;
+ end;