OpenACS

daveb
committed
MAIN:daveb:20040109210146
on 09 Jan 04
file upgrade-1.3-1.5.sql was initially added on branch oacs-5-0.
MAIN:daveb:20040109210146
/postgresql/edit-this-page-create.sql (+98 -0)
55 55
56 56   insert into cr_items (
57 57     item_id, parent_id, name, content_type
58 58   ) values (
59 59     v_item_id, v_folder_id, p_name, p_content_type
60 60   );
61 61
62 62   v_revision_id := acs_object__new(null, p_content_type, now(), null, null, v_item_id);
63 63
64 64   insert into cr_revisions (revision_id, item_id, title,
65 65                             publish_date, mime_type)
66 66   values (v_revision_id, v_item_id, p_title, now(), ''text/html'');
67 67
68 68   update cr_items set live_revision = v_revision_id
69 69                   where item_id = v_item_id;
70 70
71 71   return 1;
72 72 end;
73 73 ' language 'plpgsql';
74 74
  75 create or replace function etp__create_page(integer, integer, varchar, varchar, varchar)
  76 returns integer as '
  77 declare
  78   p_item_id alias for $1;       
  79   p_package_id alias for $2;
  80   p_name alias for $3;
  81   p_title alias for $4;
  82   p_content_type alias for $5;  -- default null -> use content_revision
  83   v_item_id integer;
  84   v_revision_id integer;
  85   v_folder_id integer;
  86 begin
  87   if p_item_id is null then
  88       v_item_id := acs_object__new(null, ''content_item'', now(), null, null, p_package_id);
  89   else
  90       v_item_id := acs_object__new(p_item_id, ''content_item'', now(), null, null, p_package_id);
  91   end if;
  92
  93   v_folder_id := etp__get_folder_id(p_package_id);
  94
  95 -- due to a change in acs_object__delete we can reference the actual
  96 -- object type we want
  97 -- using this we can more easily search, but we will have to create a service
  98 -- contract for each custom content type
  99 -- we define a default etp_page_revision and service contract to go with it
  100 -- make sure to subtype from etp_page_revision for any custom types
  101 -- 2003-01-12 DaveB
  102
  103   insert into cr_items (
  104     item_id, parent_id, name, content_type
  105   ) values (
  106     v_item_id, v_folder_id, p_name, p_content_type
  107   );
  108
  109   v_revision_id := acs_object__new(null, p_content_type, now(), null, null, v_item_id);
  110
  111   insert into cr_revisions (revision_id, item_id, title,
  112                             publish_date, mime_type)
  113   values (v_revision_id, v_item_id, p_title, now(), ''text/html'');
  114
  115   update cr_items set live_revision = v_revision_id
  116                   where item_id = v_item_id;
  117
  118   return 1;
  119 end;
  120 ' language 'plpgsql';
  121
75 122 create function etp__create_extlink(integer, varchar, varchar, varchar)
76 123 returns integer as '
77 124 declare
78 125   p_package_id alias for $1;
79 126   p_url alias for $2;
80 127   p_title alias for $3;
81 128   p_description alias for $4;
82 129   v_item_id integer;
83 130   v_folder_id integer;
84 131 begin
85 132   v_item_id := acs_object__new(null, ''content_extlink'');
86 133   v_folder_id := etp__get_folder_id(p_package_id);
87 134
88 135   insert into cr_items (
89 136     item_id, parent_id, name, content_type
90 137   ) values (
91 138     v_item_id, v_folder_id, ''extlink '' || etp_auto_page_number_seq.nextval, ''content_extlink''
92 139   );
93 140
94 141   insert into cr_extlinks
 
162 209     into v_new_revision_id from dual;
163 210
164 211   insert into acs_objects (object_id, object_type, creation_date, creation_user, context_id)
165 212   values (v_new_revision_id, v_content_type, now(), p_user_id, v_item_id);
166 213
167 214   insert into cr_revisions (revision_id, item_id, title, description, content, mime_type)
168 215   select v_new_revision_id, item_id, title, description, content, mime_type
169 216     from cr_revisions r
170 217    where r.revision_id = v_revision_id;
171 218
172 219   -- copy extended attributes to the new revision, if there are any
173 220   insert into acs_attribute_values (object_id, attribute_id, attr_value)
174 221   select v_new_revision_id as object_id, attribute_id, attr_value
175 222     from acs_attribute_values
176 223    where object_id = v_revision_id;
177 224
178 225   return 1;
179 226 end;
180 227 ' language 'plpgsql';
181 228
  229 create or replace function etp__create_new_revision(integer, varchar, integer, integer)
  230 returns integer as '
  231 declare
  232   p_package_id alias for $1;
  233   p_name alias for $2;
  234   p_user_id alias for $3;
  235   p_revision_id alias for $4;
  236   v_revision_id integer;
  237   v_item_id integer;
  238   v_content_type varchar;
  239 begin
182 240
  241   select max(r.revision_id)
  242     into v_revision_id
  243     from cr_revisions r, cr_items i
  244    where i.name = p_name
  245      and i.parent_id = etp__get_folder_id(p_package_id)
  246      and r.item_id = i.item_id;
  247
  248   select item_id
  249     into v_item_id
  250     from cr_revisions
  251    where revision_id = v_revision_id;
  252
  253   select object_type
  254     into v_content_type
  255     from acs_objects
  256    where object_id = v_revision_id;
  257
  258   -- cannot use acs_object__new because it creates attributes with their
  259   -- default values, which is not what we want.
  260
  261
  262   insert into acs_objects (object_id, object_type, creation_date, creation_user, context_id)
  263   values (p_revision_id, v_content_type, now(), p_user_id, v_item_id);
  264
  265   insert into cr_revisions (revision_id, item_id, title, description, content, mime_type)
  266   select p_revision_id, item_id, title, description, content, mime_type
  267     from cr_revisions r
  268    where r.revision_id = v_revision_id;
  269
  270   -- copy extended attributes to the new revision, if there are any
  271   insert into acs_attribute_values (object_id, attribute_id, attr_value)
  272   select p_revision_id as object_id, attribute_id, attr_value
  273     from acs_attribute_values
  274    where object_id = v_revision_id;
  275
  276   return 1;
  277 end;
  278 ' language 'plpgsql';
  279
  280
183 281 create function etp__get_folder_id (integer)
184 282 returns integer as '
185 283 declare
186 284     p_package_id alias for $1;
187 285     v_folder_id integer;
188 286     v_parent_id integer;
189 287 begin
190 288     select folder_id into v_folder_id
191 289       from cr_folders
192 290      where package_id = p_package_id;
193 291     if not found then
194 292         select parent_id into v_parent_id
195 293           from site_nodes
196 294          where object_id = p_package_id;
197 295         if found and v_parent_id is null then
198 296             v_folder_id := content_item_globals.c_root_folder_id;
199 297         else
200 298             -- This is probably an ETP app instance that
201 299             -- was created through the Site Map; by returning
202 300             -- 0 we ensure the get_page_attributes query will