Index: openacs-4/packages/acs-content-repository/sql/postgresql/content-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/sql/postgresql/content-create.sql,v diff -u -r1.8 -r1.9 --- openacs-4/packages/acs-content-repository/sql/postgresql/content-create.sql 1 Apr 2001 15:03:49 -0000 1.8 +++ openacs-4/packages/acs-content-repository/sql/postgresql/content-create.sql 7 Apr 2001 15:54:55 -0000 1.9 @@ -392,17 +392,21 @@ references cr_mime_types, nls_language varchar(50) default '' not null, -- use Don's postgresql lob hack for now. - storage_type varchar(10) default 'lob' + storage_type varchar(10) default 'lob' not null constraint cr_revisions_storage_type check (storage_type in ('lob','text','file')), + -- lob_id if storage_type = lob. lob integer, + -- content holds the file name if storage type = file + -- otherwise it holds the text data if storage_type = text. content text default '' not null, content_length integer ); create trigger cr_revisions_lob_trig before delete or update or insert on cr_revisions for each row execute procedure on_lob_ref(); + create index cr_revisions_by_mime_type on cr_revisions(mime_type); create index cr_revisions_title_idx on cr_revisions(title); -- create index cr_revisions_lower_title_idx on cr_revisions(lower(title)); Index: openacs-4/packages/acs-content-repository/sql/postgresql/content-revision.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/sql/postgresql/content-revision.sql,v diff -u -r1.9 -r1.10 --- openacs-4/packages/acs-content-repository/sql/postgresql/content-revision.sql 4 Apr 2001 23:56:49 -0000 1.9 +++ openacs-4/packages/acs-content-repository/sql/postgresql/content-revision.sql 7 Apr 2001 15:54:55 -0000 1.10 @@ -49,12 +49,12 @@ insert into cr_revisions ( revision_id, title, description, mime_type, publish_date, - nls_language, lob, item_id, storage_type + nls_language, lob, item_id, storage_type, content_length ) values ( v_revision_id, coalesce(new__title,''''), coalesce(new__description,''''), new__mime_type, new__publish_date, coalesce(new__nls_language,''''), new__data, - new__item_id, ''lob'' + new__item_id, ''lob'', 1 ); return v_revision_id; @@ -444,7 +444,7 @@ blob_loc integer; begin - -- what is this? FIXME + -- FIXME -- ctx_doc.filter(''cr_doc_filter_index'', revision_id, tmp_clob); select @@ -581,10 +581,11 @@ lob = v_new_lob where revision_id = v_revision_id_dest; else + -- FIXME: need to modify for file storage type. update cr_revisions set content = v_content, content_length = v_content_length, - storage_type = ''text'', + storage_type = v_storage_type, lob = null where revision_id = v_revision_id_dest; end if; @@ -595,7 +596,7 @@ end;' language 'plpgsql'; --- procedure content_copy +-- procedure content__get_content create function content_revision__get_content (integer) returns text as ' declare Index: openacs-4/packages/acs-kernel/sql/postgresql/acs-install.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/postgresql/acs-install.sql,v diff -u -r1.1 -r1.2 --- openacs-4/packages/acs-kernel/sql/postgresql/acs-install.sql 14 Mar 2001 04:39:10 -0000 1.1 +++ openacs-4/packages/acs-kernel/sql/postgresql/acs-install.sql 7 Apr 2001 15:54:55 -0000 1.2 @@ -148,13 +148,6 @@ null ); - -- select user into schema_user from dual; - - -- dbms_job.submit ( - -- jobnum, - -- 'dbms_stats.gather_schema_stats (''' || schema_user || ''', 10, cascade => true);', - -- trunc(sysdate+1) + 4/24, - -- 'trunc(sysdate+1) + 4/24' ); Index: openacs-4/packages/acs-kernel/sql/postgresql/lob.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/postgresql/lob.sql,v diff -u -r1.1 -r1.2 --- openacs-4/packages/acs-kernel/sql/postgresql/lob.sql 30 Mar 2001 03:15:14 -0000 1.1 +++ openacs-4/packages/acs-kernel/sql/postgresql/lob.sql 7 Apr 2001 15:54:55 -0000 1.2 @@ -100,6 +100,8 @@ from_id alias for $1; to_id alias for $2; begin + insert into lobs (lob_id,refcount) values (to_id,0); + insert into lob_data select to_id as lob_id, segment, byte_len, data from lob_data @@ -108,3 +110,10 @@ return null; end;' language 'plpgsql'; + +create function lob_length(integer) returns integer as ' +declare + id alias for $1; +begin + return sum(byte_len) from lob_data where lob_id = id; +end;' language 'plpgsql'; Index: openacs-4/packages/acs-kernel/sql/postgresql/postgresql.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/postgresql/postgresql.sql,v diff -u -r1.6 -r1.7 --- openacs-4/packages/acs-kernel/sql/postgresql/postgresql.sql 2 Apr 2001 23:14:07 -0000 1.6 +++ openacs-4/packages/acs-kernel/sql/postgresql/postgresql.sql 7 Apr 2001 15:54:55 -0000 1.7 @@ -1,5 +1,8 @@ create view dual as select now() as sysdate; +-- used to support anonymous plsql blocks in the db_plsql function call in tcl. +create sequence anon_func_seq; + create function instr(varchar,char,integer,integer) returns integer as ' declare str alias for $1; @@ -114,7 +117,7 @@ end loop; if NOT FOUND then - raise EXCEPTION ''PACKAGE: % NOT FOUND'', package_name; + raise NOTICE ''PACKAGE: % NOT FOUND'', package_name; else raise NOTICE ''PACKAGE: %: DROPPED'', package_name; end if; Index: openacs-4/packages/acs-tcl/tcl/10-database-procs-postgresql.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-tcl/tcl/Attic/10-database-procs-postgresql.tcl,v diff -u -r1.4 -r1.5 --- openacs-4/packages/acs-tcl/tcl/10-database-procs-postgresql.tcl 5 Apr 2001 18:23:38 -0000 1.4 +++ openacs-4/packages/acs-tcl/tcl/10-database-procs-postgresql.tcl 7 Apr 2001 15:54:55 -0000 1.5 @@ -35,11 +35,150 @@ } db_with_handle db { - set selection [db_exec 0or1row $db $full_statement_name $sql] + # plsql calls that are simple selects bypass the plpgsql + # mechanism for creating anonymous functions (OpenACS - Dan). + set test_sql [db_fullquery_replace_sql $full_statement_name $sql] + if {[regexp -nocase -- {^\s*select} $test_sql match]} { + ns_log Notice "PLPGSQL: bypassed anon function" + set selection [db_exec 0or1row $db $full_statement_name $sql] + } else { + ns_log Notice "PLPGSQL: using anonymous function" + set selection [db_exec_plpgsql $db $full_statement_name $sql \ + $statement_name] + } return [ns_set value $selection 0] } } +ad_proc -private db_exec_plpgsql { db statement_name sql fname } { + + A helper procedure to execute a SQL statement, potentially binding + depending on the value of the $bind variable in the calling environment + (if set). + + Low level replacement for db_exec which replaces inline code with a proc. + db proc is dropped after execution. This is a temporary fix until we can + port all of the db_exec_plsql calls to simple selects of the inline code + wrapped in function calls. + +} { + set start_time [clock clicks] + + ns_log Notice "PRE-QD: the SQL is $sql" + + # Query Dispatcher (OpenACS - ben) + set sql [db_fullquery_replace_sql $statement_name $sql] + + ns_log Notice "POST-QD: the SQL is $sql" + + set unique_id [db_nextval "anon_func_seq"] + + set function_name "__exec_${unique_id}_${fname}" + + ns_log Notice "PLPGSQL: converted: $sql to: select $function_name ()" + + # create a function definition statement for the inline code + # binding is emulated in tcl. (OpenACS - Dan) + + set errno [catch { + upvar bind bind + if { [info exists bind] && [llength $bind] != 0 } { + if { [llength $bind] == 1 } { + set bind_vars [list] + set len [ns_set size $bind] + for {set i 0} {$i < $len} {incr i} { + lappend bind_vars [ns_set key $bind $i] \ + [ns_set value $bind $i] + } + set proc_sql [db_bind_var_substitution $sql $bind_vars] + } else { + set proc_sql [db_bind_var_substitution $sql $bind] + } + } else { + set proc_sql [uplevel 2 [list db_bind_var_substitution $sql]] + } + + ns_db dml $db "create function $function_name () returns varchar as ' + [DoubleApos $proc_sql] + ' language 'plpgsql'" + + set ret_val [ns_db 0or1row $db "select $function_name ()"] + + # drop the anonymous function (OpenACS - Dan) + ns_db dml $db "drop function $function_name ()" + + return $ret_val + + } error] + + global errorInfo errorCode + set errinfo $errorInfo + set errcode $errorCode + + ad_call_proc_if_exists ds_collect_db_call $db 0or1row $statement_name $sql $start_time $errno $error + + if { $errno == 2 } { + return $error + } + + return -code $errno -errorinfo $errinfo -errorcode $errcode $error +} + +ad_proc -private db_bind_var_substitution { sql { bind "" } } { + + This proc emulates the bind variable substitution in the postgresql driver. + Since this is a temporary hack, we do it in tcl instead of hacking up the + driver to support plsql calls. This is only used for the db_exec_plpgsql + function. + +} { + if {[string equal $bind ""]} { + upvar __db_sql lsql + set lsql $sql + uplevel { + set __db_lst [regexp -inline -indices -all -- {:?:\w+} $__db_sql] + for {set i [expr [llength $__db_lst] - 1]} {$i >= 0} {incr i -1} { + set __db_ws [lindex [lindex $__db_lst $i] 0] + set __db_we [lindex [lindex $__db_lst $i] 1] + set __db_bind_var [string range $__db_sql $__db_ws $__db_we] + if {![string match "::*" $__db_bind_var]} { + set __db_tcl_var [string range $__db_bind_var 1 end] + set __db_tcl_var [set $__db_tcl_var] + if {[string equal $__db_tcl_var ""]} { + set __db_tcl_var null + } else { + set __db_tcl_var "'$__db_tcl_var'" + } + set __db_sql [string replace $__db_sql $__db_ws $__db_we $__db_tcl_var] + } + } + } + } else { + + array set bind_vars $bind + + set lsql $sql + set lst [regexp -inline -indices -all -- {:?:\w+} $sql] + for {set i [expr [llength $lst] - 1]} {$i >= 0} {incr i -1} { + set ws [lindex [lindex $lst $i] 0] + set we [lindex [lindex $lst $i] 1] + set bind_var [string range $sql $ws $we] + if {![string match "::*" $bind_var]} { + set tcl_var [string range $bind_var 1 end] + set val $bind_vars($tcl_var) + if {[string equal $val ""]} { + set val null + } else { + set val "'$val'" + } + set lsql [string replace $lsql $ws $we $val] + } + } + } + + return $lsql +} + ad_proc -private db_exec { type db statement_name sql args } { A helper procedure to execute a SQL statement, potentially binding