Index: openacs-4/packages/acs-content-repository/sql/oracle/content-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/sql/oracle/content-create.sql,v
diff -u -r1.1 -r1.2
--- openacs-4/packages/acs-content-repository/sql/oracle/content-create.sql 24 Mar 2001 22:00:48 -0000 1.1
+++ openacs-4/packages/acs-content-repository/sql/oracle/content-create.sql 11 May 2001 04:43:39 -0000 1.2
@@ -182,7 +182,10 @@
),
content_type varchar2(100)
constraint cr_items_rev_type_fk
- references acs_object_types
+ references acs_object_types,
+ storage_type varchar2(10) default 'lob' not null
+ constraint cr_revisions_storage_type
+ check (storage_type in ('lob','file'))
);
create index cr_items_by_locale on cr_items(locale);
@@ -288,10 +291,30 @@
constraint cr_revisions_mime_type_ref
references cr_mime_types,
nls_language varchar2(50),
- content BLOB
+ storage_type varchar2(10) default 'lob' not null,
+ filename varchar2(4000),
+ content BLOB,
+ content_length integer
);
+create or replace trigger cr_check_revision_storage_tr
+before insert or update on cr_revisions
+for each row
+declare
+ v_storage_type cr_items.storage_type%TYPE;
+begin
+ select storage_type into v_storage_type
+ from cr_items
+ where item_id = :new.item_id;
+
+ if v_storage_type <> :new.storage_type then
+ raise_application_error(-20000, 'Invalid storage type: ' || :new.storage_type || ' for revisions_id = ' || :new.revision_id);
+ end if;
+end;
+/
+show errors
+
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/oracle/content-item.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/sql/oracle/content-item.sql,v
diff -u -r1.1 -r1.2
--- openacs-4/packages/acs-content-repository/sql/oracle/content-item.sql 24 Mar 2001 22:00:48 -0000 1.1
+++ openacs-4/packages/acs-content-repository/sql/oracle/content-item.sql 11 May 2001 04:43:39 -0000 1.2
@@ -72,7 +72,8 @@
text in varchar2 default null,
data in cr_revisions.content%TYPE default null,
relation_tag in cr_child_rels.relation_tag%TYPE default null,
- is_live in char default 'f'
+ is_live in char default 'f',
+ storage_type in cr_items.storage_type%TYPE default 'lob'
) return cr_items.item_id%TYPE
is
v_parent_id cr_items.parent_id%TYPE;
@@ -163,10 +164,10 @@
--end if;
insert into cr_items (
- item_id, name, content_type, parent_id
+ item_id, name, content_type, parent_id, storage_type
) values (
v_item_id, content_item.new.name,
- content_item.new.content_type, v_parent_id
+ content_item.new.content_type, v_parent_id, content_item.new.storage_type
);
-- if the parent is not a folder, insert into cr_child_rels
@@ -1245,6 +1246,7 @@
v_is_registered char(1);
v_old_revision_id cr_revisions.revision_id%TYPE;
v_new_revision_id cr_revisions.revision_id%TYPE;
+ v_storage_type cr_items.storage_type%TYPE;
begin
-- call content_folder.copy if the item is a folder
@@ -1280,9 +1282,9 @@
select
content_type, name, locale,
- nvl(live_revision, latest_revision)
+ nvl(live_revision, latest_revision), storage_type
into
- v_content_type, v_name, v_locale, v_revision_id
+ v_content_type, v_name, v_locale, v_revision_id, v_storage_type
from
cr_items
where
@@ -1303,7 +1305,8 @@
locale => v_locale,
content_type => v_content_type,
creation_user => copy2.creation_user,
- creation_ip => copy2.creation_ip
+ creation_ip => copy2.creation_ip,
+ storage_type => v_storage_type
);
-- get the latest revision of the old item
Index: openacs-4/packages/acs-content-repository/sql/oracle/content-revision.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/sql/oracle/content-revision.sql,v
diff -u -r1.1 -r1.2
--- openacs-4/packages/acs-content-repository/sql/oracle/content-revision.sql 24 Mar 2001 22:00:48 -0000 1.1
+++ openacs-4/packages/acs-content-repository/sql/oracle/content-revision.sql 11 May 2001 04:43:39 -0000 1.2
@@ -25,10 +25,12 @@
creation_date in acs_objects.creation_date%TYPE default sysdate,
creation_user in acs_objects.creation_user%TYPE default null,
creation_ip in acs_objects.creation_ip%TYPE default null
+
) return cr_revisions.revision_id%TYPE is
v_revision_id integer;
v_content_type acs_object_types.object_type%TYPE;
+ v_storage_type cr_items.storage_type%TYPE;
begin
@@ -43,12 +45,16 @@
context_id => item_id
);
+ select storage_type into v_storage_type
+ from cr_items
+ where item_id = new.item_id;
+
insert into cr_revisions (
revision_id, title, description, mime_type, publish_date,
- nls_language, content, item_id
+ nls_language, content, item_id, storage_type
) values (
v_revision_id, title, description, mime_type, publish_date,
- nls_language, data, item_id
+ nls_language, data, item_id, v_storage_type
);
return v_revision_id;
@@ -70,6 +76,7 @@
) return cr_revisions.revision_id%TYPE is
v_revision_id integer;
+ v_storage_type cr_items.storage_type%TYPE;
blob_loc cr_revisions.content%TYPE;
begin
@@ -462,11 +469,12 @@
revision_id in cr_revisions.revision_id%TYPE,
revision_id_dest in cr_revisions.revision_id%TYPE default null
) is
- lobs blob;
- lobd blob;
v_item_id cr_items.item_id%TYPE;
v_content_length integer;
v_revision_id_dest cr_revisions.revision_id%TYPE;
+ v_storage_type cr_revisions.storage_type%TYPE;
+ v_filename cr_revisions.filename%TYPE;
+ v_content blob;
begin
select
@@ -499,9 +507,26 @@
When a BLOB, CLOB, or NCLOB is copied from one row to another row in
the same table or in a different table, the actual LOB value is
copied, not just the LOB locator. */
- update cr_revisions
- set content = ( select content from cr_revisions
- where revision_id = content_copy.revision_id )
+
+ select
+ storage_type, filename, content_length
+ into
+ v_storage_type, v_filename, v_content_length
+ from
+ cr_revisions
+ where
+ revision_id = content_copy.revision_id;
+
+ -- need to update the file name after the copy,
+ -- if this content item is in CR file storage. The file name is based
+ -- off of the item_id and revision_id and it will be invalid for the
+ -- copied revision.
+
+ update cr_revisions
+ set content = (select content from cr_revisions where revision_id = content_copy.revision_id),
+ storage_type = v_storage_type,
+ filename = v_filename,
+ content_length = v_content_length
where revision_id = v_revision_id_dest;
end if;
Index: openacs-4/packages/acs-content-repository/sql/oracle/content-type.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/sql/oracle/content-type.sql,v
diff -u -r1.1 -r1.2
--- openacs-4/packages/acs-content-repository/sql/oracle/content-type.sql 24 Mar 2001 22:00:48 -0000 1.1
+++ openacs-4/packages/acs-content-repository/sql/oracle/content-type.sql 11 May 2001 04:43:39 -0000 1.2
@@ -166,9 +166,19 @@
acs_object_types
where
object_type = drop_type.content_type;
+
+ -- drop the input/output views for the type
+ -- being dropped.
+ -- FIXME: does the trigger get dropped when the
+ -- view is dropped? This did not exist in the 4.2 release,
+ -- and it needs to be tested.
+
- execute immediate 'drop table ' || v_table_name ;
+ execute immediate 'drop view ' || v_table_name || 'x';
+ execute immediate 'drop view ' || v_table_name || 'i';
+ execute immediate 'drop table ' || v_table_name;
+
end if;
acs_object_type.drop_type(
Index: openacs-4/packages/acs-content-repository/sql/oracle/packages-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/sql/oracle/packages-create.sql,v
diff -u -r1.1 -r1.2
--- openacs-4/packages/acs-content-repository/sql/oracle/packages-create.sql 24 Mar 2001 22:00:48 -0000 1.1
+++ openacs-4/packages/acs-content-repository/sql/oracle/packages-create.sql 11 May 2001 04:43:39 -0000 1.2
@@ -371,7 +371,8 @@
text in varchar2 default null,
data in cr_revisions.content%TYPE default null,
relation_tag in cr_child_rels.relation_tag%TYPE default null,
- is_live in char default 'f'
+ is_live in char default 'f',
+ storage_type in cr_items.storage_type%TYPE default 'lob'
) return cr_items.item_id%TYPE;
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.15 -r1.16
--- openacs-4/packages/acs-content-repository/sql/postgresql/content-revision.sql 9 May 2001 03:31:42 -0000 1.15
+++ openacs-4/packages/acs-content-repository/sql/postgresql/content-revision.sql 11 May 2001 04:43:39 -0000 1.16
@@ -47,14 +47,18 @@
-- This routine only inserts the lob id. It would need to be followed by
-- ns_pg blob_dml from within a tcl script to actually insert the lob data.
+ -- After the lob data is inserted, the content_length needs to be updated
+ -- as well.
+ -- DanW, 2001-05-10.
+
insert into cr_revisions (
revision_id, title, description, mime_type, publish_date,
nls_language, lob, item_id, storage_type, content_length
) values (
v_revision_id, new__title, new__description,
new__mime_type,
new__publish_date, new__nls_language, new__data,
- new__item_id, ''lob'', 1
+ new__item_id, ''lob'', 0
);
return v_revision_id;
@@ -85,7 +89,6 @@
end;' language 'plpgsql';
--- function new
create function content_revision__new (varchar,varchar,timestamp,varchar,varchar,text,integer,integer,timestamp,integer,varchar)
returns integer as '
declare
@@ -100,9 +103,42 @@
new__creation_date alias for $9; -- default now()
new__creation_user alias for $10; -- default null
new__creation_ip alias for $11; -- default null
+begin
+ return content_revision__new(new__title,
+ new__description,
+ new__publish_date,
+ new__mime_type,
+ new__nls_language,
+ new__text,
+ new__item_id,
+ new__revision_id,
+ new__creation_date,
+ new__creation_user,
+ new__creation_ip,
+ null
+ );
+end;' language 'plpgsql';
+
+-- function new
+create function content_revision__new (varchar,varchar,timestamp,varchar,varchar,text,integer,integer,timestamp,integer,varchar,integer)
+returns integer as '
+declare
+ new__title alias for $1;
+ new__description alias for $2; -- default null
+ new__publish_date alias for $3; -- default now()
+ new__mime_type alias for $4; -- default ''text/plain''
+ new__nls_language alias for $5; -- default null
+ new__text alias for $6; -- default '' ''
+ new__item_id alias for $7;
+ new__revision_id alias for $8; -- default null
+ new__creation_date alias for $9; -- default now()
+ new__creation_user alias for $10; -- default null
+ new__creation_ip alias for $11; -- default null
+ new__content_length alias for $12; -- default null
v_revision_id integer;
v_content_type acs_object_types.object_type%TYPE;
v_storage_type cr_items.storage_type%TYPE;
+ v_length cr_revisions.content_length%TYPE;
begin
v_content_type := content_item__get_content_type(new__item_id);
@@ -120,6 +156,12 @@
from cr_items
where item_id = new__item_id;
+ if v_storage_type = ''text'' then
+ v_length := length(new__text);
+ else
+ v_length := coalesce(new__content_length,0);
+ end if;
+
-- text data is stored directly in cr_revisions using text datatype.
insert into cr_revisions (
@@ -130,7 +172,7 @@
new__mime_type,
new__publish_date, new__nls_language,
new__text, new__item_id, v_storage_type,
- length(new__text)
+ v_length
);
return v_revision_id;
@@ -580,13 +622,25 @@
PERFORM lob_copy(v_lob, v_new_lob);
update cr_revisions
- set content = v_content,
+ set content = null,
content_length = v_content_length,
storage_type = ''lob''
lob = v_new_lob
where revision_id = v_revision_id_dest;
else
- -- FIXME: need to modify for file storage type.
+ -- this will work for both file and text types... well sort of.
+ -- this really just creates a reference to the first file which is
+ -- wrong since, the item_id, revision_id uniquely describes the
+ -- location of the file in the content repository file system.
+ -- after copy is called, the content attribute needs to be updated
+ -- with the new relative file path:
+
+ -- update cr_revisions
+ -- set content = ''[cr_create_content_file $item_id $revision_id [cr_fs_path]$old_rel_path]''
+ -- where revision_id = :revision_id
+
+ -- old_rel_path is the content attribute value of the content revision
+ -- that is being copied.
update cr_revisions
set content = v_content,
content_length = v_content_length,
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.15 -r1.16
--- openacs-4/packages/acs-content-repository/sql/postgresql/content-type.sql 9 May 2001 03:31:42 -0000 1.15
+++ openacs-4/packages/acs-content-repository/sql/postgresql/content-type.sql 11 May 2001 04:43:39 -0000 1.16
@@ -100,15 +100,16 @@
drop_type__content_type alias for $1;
drop_type__drop_children_p alias for $2; -- default ''f''
drop_type__drop_table_p alias for $3; -- default ''f''
- table_exists boolean;
+ table_exists_p boolean;
v_table_name varchar;
is_subclassed_p boolean;
child_rec record;
attr_row record;
begin
- -- first we''ll rid ourselves of any dependent child types, if any , along with their
- -- own dependent grandchild types
+ -- first we''ll rid ourselves of any dependent child types, if any ,
+ -- along with their own dependent grandchild types
+
select
count(*) > 0 into is_subclassed_p
from
@@ -152,26 +153,37 @@
-- we''ll remove the associated table if it exists
select
- table_exists(lower(table_name)) into table_exists
+ table_exists(lower(table_name)) into table_exists_p
from
acs_object_types
where
object_type = drop_type__content_type;
- if table_exists and content_type__drop_table_p then
+ if table_exists_p and drop_type__drop_table_p then
select
table_name into v_table_name
from
acs_object_types
where
object_type = drop_type__content_type;
- execute ''drop table '' || v_table_name ;
+ -- drop the rule and input/output views for the type
+ -- being dropped.
+ -- FIXME: this did not exist in the oracle code and it needs to be
+ -- tested. Thanks to Vinod Kurup for pointing this out.
+ -- The rule dropping might be redundant as the rule might be dropped
+ -- when the view is dropped.
+
+ execute ''drop rule '' || v_table_name || ''_r'';
+ execute ''drop view '' || v_table_name || ''x'';
+ execute ''drop view '' || v_table_name || ''i'';
+
+ execute ''drop table '' || v_table_name;
end if;
PERFORM acs_object_type__drop_type(drop_type__content_type, ''f'');
-return 0;
+ return 0;
end;' language 'plpgsql';
@@ -629,6 +641,10 @@
execute ''drop view '' || v_table_name || ''i'';
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.*, cr.revision_id, cr.title, cr.item_id,
content_revision__get_content(cr.revision_id) as data,
Index: openacs-4/packages/acs-tcl/tcl/00-database-procs-oracle.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-tcl/tcl/Attic/00-database-procs-oracle.tcl,v
diff -u -r1.4 -r1.5
--- openacs-4/packages/acs-tcl/tcl/00-database-procs-oracle.tcl 10 May 2001 05:38:11 -0000 1.4
+++ openacs-4/packages/acs-tcl/tcl/00-database-procs-oracle.tcl 11 May 2001 04:43:39 -0000 1.5
@@ -176,7 +176,7 @@
set full_statement_name [db_qd_get_fullname $statement_name]
db_with_handle db {
- db_exec write_blob $db $full_statement_name $sql
+ db_exec write_blob_lob $db $full_statement_name $sql
}
}
@@ -186,10 +186,114 @@
set full_statement_name [db_qd_get_fullname $statement_name]
db_with_handle db {
- eval [list db_exec blob_get_file $db $full_statement_name $sql 2 $file] $args
+ eval [list db_exec_lob blob_get_file $db $full_statement_name $sql 2 $file] $args
}
}
+ad_proc -private db_exec_lob { type db statement_name pre_sql {ulevel 2} args } {
+
+ A helper procedure to execute a SQL statement, potentially binding
+ depending on the value of the $bind variable in the calling environment
+ (if set).
+
+} {
+ set start_time [clock clicks]
+
+ ns_log Notice "PRE-QD: the SQL is $pre_sql for $statement_name"
+
+ # Query Dispatcher (OpenACS - ben)
+ set sql [db_qd_replace_sql $statement_name $pre_sql]
+
+ # insert tcl variable values (Openacs - Dan)
+ if {![string equal $sql $pre_sql]} {
+ set sql [uplevel $ulevel [list subst -nobackslashes $sql]]
+ }
+
+ set file_storage_p 0
+ upvar $ulevel storage_type storage_type
+
+ if {[info exists storage_type] && [string equal $storage_type file]} {
+ set file_storage_p 1
+ set original_type $type
+ set qtype 1row
+ ns_log Notice "db_exec_lob: file storage in use"
+ } else {
+ set qtype $type
+ ns_log Notice "db_exec_lob: blob storage in use"
+ }
+
+ ns_log Notice "POST-QD: the SQL is $sql"
+
+ set errno [catch {
+ upvar bind bind
+ if { [info exists bind] && [llength $bind] != 0 } {
+ if { [llength $bind] == 1 } {
+ set selection [eval [list ns_ora $qtype $db -bind $bind $sql] $args]
+ } else {
+ set bind_vars [ns_set create]
+ foreach { name value } $bind {
+ ns_set put $bind_vars $name $value
+ }
+ set selection [eval [list ns_ora $qtype $db -bind $bind_vars $sql] $args]
+ }
+ } else {
+ set selection [uplevel $ulevel [list ns_ora $qtype $db $sql] $args]
+ }
+
+ if {$file_storage_p} {
+ set content [ns_set value $selection 0]
+ for {set i 0} {$i < [ns_set size $selection]} {incr i} {
+ set name [ns_set key $selection $i]
+ if {[string equal $name storage_type]} {
+ set storage_type [ns_set value $selection $i]
+ } elseif {[string equal $name content]} {
+ set content [ns_set value $selection $i]
+ }
+ }
+
+ switch $original_type {
+
+ blob_get_file {
+ if {[file exists $content]} {
+ set file [lindex $args 0]
+ set ifp [open $content r]
+ set ofp [open $file w]
+ ns_cpfp $ifp $ofp
+ close $ifp
+ close $ofp
+ return $selection
+ } else {
+ error "file: $content doesn't exist"
+ }
+ }
+
+ write_blob_lob {
+
+ if {[file exists $content]} {
+ set ofp [open $content r]
+ ns_writefp $ofp
+ close $ofp
+ return $selection
+ } else {
+ error "file: $content doesn't exist"
+ }
+ }
+ }
+ } else {
+ return $selection
+ }
+
+ } error]
+
+ ad_call_proc_if_exists ds_collect_db_call $db $type $statement_name $sql $start_time $errno $error
+ if { $errno == 2 } {
+ return $error
+ }
+
+ global errorInfo errorCode
+ return -code $errno -errorinfo $errorInfo -errorcode $errorCode $error
+}
+
ad_proc db_get_sql_user { } {
Returns a valid user@database/password string to access a database through sqlplus.
Index: openacs-4/packages/acs-tcl/tcl/apm-file-procs-oracle.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-tcl/tcl/apm-file-procs-oracle.xql,v
diff -u -r1.5 -r1.6
--- openacs-4/packages/acs-tcl/tcl/apm-file-procs-oracle.xql 5 May 2001 17:39:19 -0000 1.5
+++ openacs-4/packages/acs-tcl/tcl/apm-file-procs-oracle.xql 11 May 2001 04:43:39 -0000 1.6
@@ -8,7 +8,8 @@
begin
:1 := content_item.new(name => :name,
- creation_ip => :creation_ip
+ creation_ip => :creation_ip,
+ storage_type => 'file'
);
end;
@@ -40,9 +41,8 @@
update cr_revisions
- set content = empty_blob()
+ set filename = '[set content_file [cr_create_content_file $item_id $revision_id $tmpfile]]'
where revision_id = :revision_id
- returning content into :1
@@ -51,9 +51,7 @@
update apm_package_versions
- set content_length = (select dbms_lob.getlength(content)
- from cr_revisons
- where revision_id = :revision_id)
+ set content_length = [cr_file_size $content_file]
where version_id = :version_id
@@ -63,7 +61,7 @@
- select content
+ select '[cr_fs_path]' || filename as content, storage_type
from cr_revisions
where revision_id = (select content_item.get_latest_revision(item_id)
from apm_package_versions