Index: openacs-4/packages/acs-content-repository/acs-content-repository.info
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/acs-content-repository.info,v
diff -u -r1.60 -r1.61
--- openacs-4/packages/acs-content-repository/acs-content-repository.info 5 Jun 2006 00:02:36 -0000 1.60
+++ openacs-4/packages/acs-content-repository/acs-content-repository.info 26 Jul 2006 20:52:47 -0000 1.61
@@ -7,7 +7,7 @@
t
t
-
+
OpenACS
The canonical repository for OpenACS content.
@@ -21,7 +21,7 @@
other CMS backing functionality. Utilized by Bug Tracker, File Storage, and other packages.
-
+
Index: openacs-4/packages/acs-content-repository/sql/postgresql/content-type.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/sql/postgresql/content-type.sql,v
diff -u -r1.45 -r1.46
--- openacs-4/packages/acs-content-repository/sql/postgresql/content-type.sql 4 Jun 2006 00:45:23 -0000 1.45
+++ openacs-4/packages/acs-content-repository/sql/postgresql/content-type.sql 26 Jul 2006 20:52:47 -0000 1.46
@@ -465,8 +465,8 @@
end;' language 'plpgsql';
+-- function trigger_insert_statement
select define_function_args('content_type__trigger_insert_statement','content_type');
-
create or replace function content_type__trigger_insert_statement (varchar)
returns varchar as '
declare
@@ -496,40 +496,15 @@
object_type = trigger_insert_statement__content_type
LOOP
cols := cols || '', '' || attr_rec.attribute_name;
- vals := vals || '', new.'' || attr_rec.attribute_name;
+ vals := vals || '', p_new.'' || attr_rec.attribute_name;
end LOOP;
return ''insert into '' || v_table_name ||
- '' ( '' || v_id_column || cols || '' ) values (cr_dummy.val'' ||
+ '' ( '' || v_id_column || cols || '' ) values (v_revision_id'' ||
vals || '')'';
end;' language 'plpgsql' stable;
--- dummy table provides a target for updates in dynamically generated trigger
--- statements. If type is cr_revisions then rule would end up having only a
--- select statement which causes an error to be thrown by the dml command.
--- dml command checks for NS_ROWS result and throws an error if found.
--- Using a dummy update causes NS_OK to be returned which satisfies the dml
--- result checking.
-
--- DCW, 2001-06-09
-
-create table cr_dummy (
- val integer
-);
-
-insert into cr_dummy (val) values (null);
-
-create function cr_dummy_ins_del_tr () returns opaque as '
-begin
- raise exception ''Only updates are allowed on cr_dummy'';
- return null;
-end;' language 'plpgsql';
-
-create trigger cr_dummy_ins_del_tr before insert or delete on
-cr_dummy for each row execute procedure cr_dummy_ins_del_tr ();
-
-
-- FIXME: need to look at this in more detail. This probably can't be made
-- to work reliably in postgresql. Currently we are using a rule to insert
-- into the input view when a new content revision is added. Pg locks the
@@ -555,11 +530,13 @@
declare
refresh_trigger__content_type alias for $1;
rule_text text default '''';
+ function_text text default '''';
v_table_name acs_object_types.table_name%TYPE;
type_rec record;
begin
-- get the table name for the content type (determines view name)
+ raise NOTICE ''refresh trigger for % '', refresh_trigger__content_type;
select table_name
into v_table_name
@@ -568,26 +545,30 @@
--=================== start building rule code =======================
- rule_text := ''create rule '' || v_table_name || ''_r as on insert to '' ||
- v_table_name || ''i do instead (
- update cr_dummy set val = (
- select content_revision__new(
- new.title,
- new.description,
+ function_text := function_text ||
+ ''create or replace function '' || v_table_name || ''_f (p_new ''|| v_table_name || ''i)
+ returns void as ''''
+ declare
+ v_revision_id integer;
+ begin
+
+ select content_revision__new(
+ p_new.title,
+ p_new.description,
now(),
- new.mime_type,
- new.nls_language,
- case when new.text is null
- then new.data
- else new.text
+ p_new.mime_type,
+ p_new.nls_language,
+ case when p_new.text is null
+ then p_new.data
+ else p_new.text
end,
- content_symlink__resolve(new.item_id),
- new.revision_id,
+ content_symlink__resolve(p_new.item_id),
+ p_new.revision_id,
now(),
- new.creation_user,
- new.creation_ip,
- new.object_package_id
- ));
+ p_new.creation_user,
+ p_new.creation_ip,
+ p_new.object_package_id
+ ) into v_revision_id;
'';
-- add an insert statement for each subtype in the hierarchy for this type
@@ -600,13 +581,21 @@
and ot1.tree_sortkey between ot2.tree_sortkey and tree_right(ot2.tree_sortkey)
order by level asc
LOOP
- rule_text := rule_text || '' '' || content_type__trigger_insert_statement(type_rec.object_type) || '';'';
+ function_text := function_text || '' '' || content_type__trigger_insert_statement(type_rec.object_type) || '';
+ '';
end loop;
+ function_text := function_text || ''
+ return;
+ end;'''' language ''''plpgsql'''';
+ '';
-- end building the rule definition code
- rule_text := rule_text || '' );'';
+ -- create the new function
+ execute function_text;
+ rule_text := ''create rule '' || v_table_name || ''_r as on insert to '' ||
+ v_table_name || ''i do instead SELECT '' || v_table_name || ''_f(new); '' ;
--================== done building rule code =======================
-- drop the old rule
@@ -671,7 +660,7 @@
-- create the input view (includes content columns)
if table_exists(v_table_name || ''i'') then
- execute ''drop view '' || v_table_name || ''i'';
+ execute ''drop view '' || v_table_name || ''i'' || '' CASCADE'';
end if;
-- FIXME: need to look at content_revision__get_content. Since the CR
Index: openacs-4/packages/acs-content-repository/sql/postgresql/upgrade/upgrade-5.3.0d1-5.3.0d2.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/sql/postgresql/upgrade/upgrade-5.3.0d1-5.3.0d2.sql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/acs-content-repository/sql/postgresql/upgrade/upgrade-5.3.0d1-5.3.0d2.sql 26 Jul 2006 20:52:47 -0000 1.1
@@ -0,0 +1,267 @@
+-- procedure refresh_trigger
+select define_function_args('content_type__refresh_trigger','content_type');
+create or replace function content_type__refresh_trigger (varchar)
+returns integer as '
+declare
+ refresh_trigger__content_type alias for $1;
+ rule_text text default '''';
+ function_text text default '''';
+ v_table_name acs_object_types.table_name%TYPE;
+ type_rec record;
+begin
+
+ -- get the table name for the content type (determines view name)
+ raise NOTICE ''refresh trigger for % '', refresh_trigger__content_type;
+
+ select table_name
+ into v_table_name
+ from acs_object_types
+ where object_type = refresh_trigger__content_type;
+
+ --=================== start building rule code =======================
+
+ function_text := function_text ||
+ ''create or replace function '' || v_table_name || ''_f (p_new ''|| v_table_name || ''i)
+ returns void as ''''
+ declare
+ v_revision_id integer;
+ begin
+
+ select content_revision__new(
+ p_new.title,
+ p_new.description,
+ now(),
+ p_new.mime_type,
+ p_new.nls_language,
+ case when p_new.text is null
+ then p_new.data
+ else p_new.text
+ end,
+ content_symlink__resolve(p_new.item_id),
+ p_new.revision_id,
+ now(),
+ p_new.creation_user,
+ p_new.creation_ip,
+ p_new.object_package_id
+ ) into v_revision_id;
+ '';
+
+ -- add an insert statement for each subtype in the hierarchy for this type
+
+ for type_rec in select ot2.object_type, tree_level(ot2.tree_sortkey) as level
+ from acs_object_types ot1, acs_object_types ot2
+ where ot2.object_type <> ''acs_object''
+ and ot2.object_type <> ''content_revision''
+ and ot1.object_type = refresh_trigger__content_type
+ and ot1.tree_sortkey between ot2.tree_sortkey and tree_right(ot2.tree_sortkey)
+ order by level asc
+ LOOP
+ function_text := function_text || '' '' || content_type__trigger_insert_statement(type_rec.object_type) || '';
+ '';
+ end loop;
+
+ function_text := function_text || ''
+ return;
+ end;'''' language ''''plpgsql'''';
+ '';
+ -- end building the rule definition code
+
+ -- create the new function
+ execute function_text;
+
+ rule_text := ''create rule '' || v_table_name || ''_r as on insert to '' ||
+ v_table_name || ''i do instead SELECT '' || v_table_name || ''_f(new); '' ;
+ --================== done building rule code =======================
+
+ -- drop the old rule
+ if rule_exists(v_table_name || ''_r'', v_table_name || ''i'') then
+
+ -- different syntax for dropping a rule in 7.2 and 7.3 so check which
+ -- version is being used (olah).
+ if version() like ''%PostgreSQL 7.2%'' then
+ execute ''drop rule '' || v_table_name || ''_r'';
+ else
+ -- 7.3 syntax
+ execute ''drop rule '' || v_table_name || ''_r '' || ''on '' || v_table_name || ''i'';
+ end if;
+
+ end if;
+
+ -- create the new rule for inserts on the content type
+ execute rule_text;
+
+ return null;
+
+end;' language 'plpgsql';
+
+
+-- function trigger_insert_statement
+select define_function_args('content_type__trigger_insert_statement','content_type');
+create or replace function content_type__trigger_insert_statement (varchar)
+returns varchar as '
+declare
+ trigger_insert_statement__content_type alias for $1;
+ v_table_name acs_object_types.table_name%TYPE;
+ v_id_column acs_object_types.id_column%TYPE;
+ cols varchar default '''';
+ vals varchar default '''';
+ attr_rec record;
+begin
+ if trigger_insert_statement__content_type is null then
+ return exception ''content_type__trigger_insert_statement called with null content_type'';
+ end if;
+
+ select
+ table_name, id_column into v_table_name, v_id_column
+ from
+ acs_object_types
+ where
+ object_type = trigger_insert_statement__content_type;
+
+ for attr_rec in select
+ attribute_name
+ from
+ acs_attributes
+ where
+ object_type = trigger_insert_statement__content_type
+ LOOP
+ cols := cols || '', '' || attr_rec.attribute_name;
+ vals := vals || '', p_new.'' || attr_rec.attribute_name;
+ end LOOP;
+
+ return ''insert into '' || v_table_name ||
+ '' ( '' || v_id_column || cols || '' ) values (v_revision_id'' ||
+ vals || '')'';
+
+end;' language 'plpgsql' stable;
+
+select define_function_args('content_type__refresh_view','content_type');
+
+create or replace function content_type__refresh_view (varchar)
+returns integer as '
+declare
+ refresh_view__content_type alias for $1;
+ cols varchar default '''';
+ tabs varchar default '''';
+ joins varchar default '''';
+ v_table_name varchar;
+ join_rec record;
+begin
+
+ for join_rec in select ot2.table_name, ot2.id_column, tree_level(ot2.tree_sortkey) as level
+ from acs_object_types ot1, acs_object_types ot2
+ where ot2.object_type <> ''acs_object''
+ and ot2.object_type <> ''content_revision''
+ and lower(ot2.table_name) <> ''acs_objects''
+ and lower(ot2.table_name) <> ''cr_revisions''
+ and ot1.object_type = refresh_view__content_type
+ and ot1.tree_sortkey between ot2.tree_sortkey and tree_right(ot2.tree_sortkey)
+ order by ot2.tree_sortkey desc
+ LOOP
+ if join_rec.table_name is not null then
+ cols := cols || '', '' || join_rec.table_name || ''.*'';
+ tabs := tabs || '', '' || join_rec.table_name;
+ joins := joins || '' and acs_objects.object_id = '' ||
+ join_rec.table_name || ''.'' || join_rec.id_column;
+ end if;
+ end loop;
+
+ select table_name into v_table_name from acs_object_types
+ where object_type = refresh_view__content_type;
+
+ if length(v_table_name) > 25 then
+ raise exception ''Table name cannot be longer than 25 characters, because that causes conflicting rules when we create the views.'';
+ end if;
+
+ -- create the input view (includes content columns)
+
+ if table_exists(v_table_name || ''i'') then
+ execute ''drop view '' || v_table_name || ''i'' || '' CASCADE'';
+ end if;
+
+ -- FIXME: need to look at content_revision__get_content. Since the CR
+ -- can store data in a lob, a text field or in an external file, getting
+ -- the data attribute for this view will be problematic.
+
+ execute ''create view '' || v_table_name ||
+ ''i as select acs_objects.object_id,
+ acs_objects.object_type,
+ acs_objects.title as object_title,
+ acs_objects.package_id as object_package_id,
+ acs_objects.context_id,
+ acs_objects.security_inherit_p,
+ acs_objects.creation_user,
+ acs_objects.creation_date,
+ acs_objects.creation_ip,
+ acs_objects.last_modified,
+ acs_objects.modifying_user,
+ acs_objects.modifying_ip,
+ acs_objects.tree_sortkey,
+ acs_objects.max_child_sortkey, cr.revision_id, cr.title, cr.item_id,
+ content_revision__get_content(cr.revision_id) as data,
+ cr_text.text_data as text,
+ cr.description, cr.publish_date, cr.mime_type, cr.nls_language'' ||
+ cols ||
+ '' from acs_objects, cr_revisions cr, cr_text'' || tabs || '' where
+ acs_objects.object_id = cr.revision_id '' || joins;
+
+ -- create the output view (excludes content columns to enable SELECT *)
+
+ if table_exists(v_table_name || ''x'') then
+ execute ''drop view '' || v_table_name || ''x'';
+ end if;
+
+ execute ''create view '' || v_table_name ||
+ ''x as select acs_objects.object_id,
+ acs_objects.object_type,
+ acs_objects.title as object_title,
+ acs_objects.package_id as object_package_id,
+ acs_objects.context_id,
+ acs_objects.security_inherit_p,
+ acs_objects.creation_user,
+ acs_objects.creation_date,
+ acs_objects.creation_ip,
+ acs_objects.last_modified,
+ acs_objects.modifying_user,
+ acs_objects.modifying_ip,
+ acs_objects.tree_sortkey,
+ acs_objects.max_child_sortkey, cr.revision_id, cr.title, cr.item_id,
+ cr.description, cr.publish_date, cr.mime_type, cr.nls_language,
+ i.name, i.parent_id'' ||
+ cols ||
+ '' from acs_objects, cr_revisions cr, cr_items i, cr_text'' || tabs ||
+ '' where acs_objects.object_id = cr.revision_id
+ and cr.item_id = i.item_id'' || joins;
+
+ PERFORM content_type__refresh_trigger(refresh_view__content_type);
+
+-- exception
+-- when others then
+-- dbms_output.put_line(''Error creating attribute view or trigger for''
+-- || content_type);
+
+ return 0;
+end;' language 'plpgsql';
+
+
+create or replace function inline_0() returns integer as '
+declare v_row record;
+begin
+ for v_row in select distinct o.object_type,o.table_name
+ from acs_object_type_supertype_map m,
+ acs_object_types o
+ where (m.ancestor_type=''content_revision''
+ and o.object_type=m.object_type)
+ or (o.object_type=''content_revision'')
+ loop
+ if table_exists(v_row.table_name) then
+ perform content_type__refresh_trigger(v_row.object_type);
+ end if;
+ end loop;
+return 0;
+end;' language 'plpgsql';
+
+select inline_0();
+
+drop function inline_0();
+
Index: openacs-4/packages/acs-content-repository/tcl/content-item-procs.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/tcl/content-item-procs.tcl,v
diff -u -r1.14 -r1.15
--- openacs-4/packages/acs-content-repository/tcl/content-item-procs.tcl 4 Jun 2006 00:45:23 -0000 1.14
+++ openacs-4/packages/acs-content-repository/tcl/content-item-procs.tcl 26 Jul 2006 20:52:48 -0000 1.15
@@ -111,6 +111,7 @@
# the cr_item subtype here and content_type as part of
# var_list
db_transaction {
+ db_dml lock_objects "LOCK TABLE acs_objects IN SHARE ROW EXCLUSIVE MODE"
set item_id [package_exec_plsql \
-var_list $var_list \
content_item new]
Index: openacs-4/packages/acs-content-repository/tcl/content-revision-procs.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/tcl/content-revision-procs.tcl,v
diff -u -r1.14 -r1.15
--- openacs-4/packages/acs-content-repository/tcl/content-revision-procs.tcl 5 Jun 2006 00:02:36 -0000 1.14
+++ openacs-4/packages/acs-content-repository/tcl/content-revision-procs.tcl 26 Jul 2006 20:52:48 -0000 1.15
@@ -130,16 +130,18 @@
}
set table_name [db_string get_table_name "select table_name from acs_object_types where object_type=:content_type"]
- set table_name "${table_name}i"
- set query_text "insert into ${table_name}
+ set query_text "insert into ${table_name}i
(revision_id, object_type, creation_user, creation_date, creation_ip, title, description, item_id, object_package_id, mime_type $attribute_names)
values (:revision_id, :content_type, :creation_user, :creation_date, :creation_ip, :title, :description, :item_id, :package_id, :mime_type $attribute_values)"
db_transaction {
- if {[string equal "" $revision_id]} {
+ db_dml lock_objects "LOCK TABLE acs_objects IN SHARE ROW EXCLUSIVE MODE"
+ if {$revision_id eq ""} {
set revision_id [db_nextval "acs_object_id_seq"]
}
- db_dml insert_revision $query_text
+ # the postgres "insert into view" is rewritten by the rule into a "select"
+ [expr {[db_driverkey ""] eq "postgresql" ? "db_0or1row" : "db_dml"}] \
+ insert_revision $query_text
::content::revision::update_content \
-item_id $item_id \
-revision_id $revision_id \
Index: openacs-4/packages/dynamic-types/tcl/form-procs.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/dynamic-types/tcl/form-procs.tcl,v
diff -u -r1.13 -r1.14
--- openacs-4/packages/dynamic-types/tcl/form-procs.tcl 25 Apr 2006 12:10:41 -0000 1.13
+++ openacs-4/packages/dynamic-types/tcl/form-procs.tcl 26 Jul 2006 20:52:47 -0000 1.14
@@ -409,6 +409,9 @@
# Perform the insert or update as appropriate
#
+ # the postgres "insert into view" is rewritten by the rule into a "select", so no dml..
+ set db_stmt [expr {[db_driverkey ""] eq "postgresql" ? "db_0or1row" : "db_dml"}]
+
# title, description, object_title
if {$content_type_p} {
set pos [lsearch -exact $columns package_id]
@@ -418,7 +421,7 @@
db_transaction {
if {$new_p} {
- db_dml insert_statement "
+ $db_stmt insert_statement "
insert into ${type_info(table_name)}i
([join $columns ", "])
values
@@ -427,7 +430,7 @@
set latest_revision [content::item::get_latest_revision -item_id $item_id]
set object_id [db_nextval acs_object_id_seq]
- db_dml insert_statement "
+ $db_stmt insert_statement "
insert into ${type_info(table_name)}i
([join [concat $columns $missing_columns] ", "])
select
@@ -465,7 +468,7 @@
}
} else {
if {$new_p} {
- db_dml insert_statement "
+ $db_stmt insert_statement "
insert into ${type_info(table_name)}i ([join $columns ", "])
values ([join $values ", "])"
} else {