Forum OpenACS Development: Re: File Storage Saving file in the database

Collapse
7: Re: File Storage Saving file in the database (response to 6)
Posted by Gustaf Neumann on 11/29/22 05:16 PM
It is always best to add such clear bugs to the bug tracker.

Apparently, this named SQL statement was already missing in the OpenACS 5.10.0 release. I have revived it in the CVS repository, we have now:

https://openacs.org/api-doc/proc-view?proc=content::revision::update_content-lob&source_p=1

The reason, nobody noticed this before, was that LOBs are somewhat deprecated for PostgreSQL installations. There are just there for legacy data. These are not in wide use, I would not be surprised, if there are more bugs lurching in the PostgreSQL lowlands.

Collapse
8: Re: File Storage Saving file in the database (response to 7)
Posted by Raul Rodriguez on 11/30/22 05:35 AM
I applied the change but I get the same result as my 5.9 install:
Database operation "dml" failed (exception ERROR, "ERROR:  insert or update on table "cr_revisions" violates foreign key constraint "cr_revisions_lob_fk"
DETAIL:  Key (lob)=(4) is not present in table "lobs".
")
ERROR:  insert or update on table "cr_revisions" violates foreign key constraint "cr_revisions_lob_fk"
DETAIL:  Key (lob)=(4) is not present in table "lobs".
SQL:

        update cr_revisions
        set mime_type = 'application/pdf',
          lob = 4
        where revision_id = '1627'


    while executing
"ns_pg_bind dml nsdb0 {

        update cr_revisions
        set mime_type = :mime_type,
          lob = 4
        where revision_id = :revision_id

    }"
    ("uplevel" body line 1)
   invoked from within
"uplevel $ulevel [list ns_pg_bind $type $db $sql]"
    invoked from within
"db_exec -subst $subst dml $db $full_statement_name $sql"
    ("uplevel" body line 21)
    invoked from within
"uplevel 1 $code_block "
    invoked from within
"db_with_handle -dbn $dbn db {
            # another ugly hack to avoid munging Tcl files.
            # __lob_id needs to be set inside of a query (.x..."
    (procedure "::nsf::procs::db_dml" line 70)
    invoked from within
"db_dml set_content "" -blob_files [list $tmp_filename]"
    (procedure "::nsf::procs::content::revision::update_content-lob" line 5)
    invoked from within
"::content::revision::update_content-$storage_type  -item_id $item_id  -revision_id $revision_id  -content $content  -mime_type $mime_type  -tmp_filena..."
    (procedure "::nsf::procs::content::revision::update_content" line 3)
    invoked from within
"::content::revision::update_content  -storage_type $storage_type  -item_id $item_id  -revision_id $revision_id  -content ""  -mime_type $mime_type  -t..."
    ("uplevel" body line 107)
    invoked from within
"uplevel 1 $transaction_code"
    (procedure "::nsf::procs::db_transaction" line 1)
    invoked from within
"db_transaction {

        if { ![db_0or1row is_registered {
            select 1
            from cr_content_mime_type_map
            where mime_type..."
    (procedure "::nsf::procs::cr_import_content" line 53)
    invoked from within
"cr_import_content  -item_id $item_id  -storage_type $storage_type  -creation_user $creation_user  -creation_ip $creation_ip  -other_type "file_storage..."
    (procedure "::nsf::procs::fs::add_version" line 15)
    invoked from within
"fs::add_version  -name $name  -tmp_filename $tmp_filename  -package_id $package_id  -item_id $item_id  -creation_user $creation_user  -creation_ip $cr..."
    ("uplevel" body line 27)
    invoked from within
"uplevel 1 $transaction_code"
    (procedure "::nsf::procs::db_transaction" line 42)
    invoked from within
"db_transaction {
        if {![db_string item_exists {}]} {

            set item_id [content::item::new  -item_id $item_id  -parent_id $parent_id  -c..."
    (procedure "::nsf::procs::fs::add_file" line 30)
    invoked from within
"fs::add_file  -name $upload_file  -item_id $this_file_id  -parent_id $folder_id  -tmp_filename $tmpfile -creation_user $user_id  -creation_ip [ad_conn..."
    ("foreach" body line 45)
    invoked from within
"foreach upload_file $upload_files tmpfile $upload_tmpfiles {
        set this_file_id $file_id
        set this_title $title
        set mime_type [cr..."
    ("uplevel" body line 61)
    invoked from within
"uplevel #$level $new_data"
    (procedure "::nsf::procs::ad_form" line 675)
    invoked from within
"ad_form -extend -form {} -select_query_name get_file -new_data {

    if { [string is true -strict $unpack_p]
         && $unpack_binary ne ""
       ..."
    ("uplevel" body line 166)
    invoked from within
"uplevel {
    ad_page_contract {
    page to add a new file to the system

    @author Kevin Scaldeferri (mailto:kevin@arsdigita.com)
    @creation-date 6 No..."
    (procedure "::template::code::bootstrap-icons-tcl::/var/www/oacs-5-10-0/..." line 2)
    invoked from within
"[adp_init tcl $__adp_stub]"
    ("uplevel" body line 10)
    invoked from within
"uplevel {

        if { [file exists $__adp_stub.tcl] } {

            # Remember the file_stub in case the procedure changes it
            set __adp..."
    (procedure "adp_prepare" line 2)
    invoked from within
"adp_prepare"
    invoked from within
"template::adp_parse $themed_template {}"
    (procedure "adp_parse_ad_conn_file" line 20)
    invoked from within
"$handler"
    ("::try" body line 3)
    invoked from within
"rp_serve_concrete_file [ad_conn file]"
    (procedure "::nsf::procs::rp_serve_abstract_file" line 60)
    invoked from within
"rp_serve_abstract_file "$root/$extra_url""
    ("::try" body line 2)
    invoked from within
"ad_try {
            rp_serve_abstract_file "$root/$extra_url"
            set ::tcl_url2file([ad_conn url]) [ad_conn file]
            set ::tcl_url2..."
    (procedure "rp_handle_request" line 49)
    invoked from within
"rp_handle_request"
    ("::try" body line 2)

What was lob replaced with? Is that reflected in the content repository?

Thank you

Collapse
9: Re: File Storage Saving file in the database (response to 8)
Posted by Gustaf Neumann on 11/30/22 08:49 AM
I applied the change but I get the same result as my 5.9 install:

I am not surprised, the code for lobs has not changed for at least 10 years (aside of being moved around).

DETAIL: Key (lob)=(4) is not present in table "lobs".

It says that the lob_id 4 is not in lobs.... Check this with "select * from lobs". Can it be that this is a broken entry from previous attempts?
Why are you trying to use LOBs on fresh PostgreSQL installations?

Collapse
10: Re: File Storage Saving file in the database (response to 9)
Posted by Raul Rodriguez on 12/01/22 05:01 AM
The same lob error shows up on my 5.9 install where the xml query was not missing. I will look more into what is going on.

I am comparing Oracle and PostgreSQL file storage installs.

If lobs are not used in fresh PostgreSQL installations, what was it replaced with?

Thank you

Collapse
11: Re: File Storage Saving file in the database (response to 10)
Posted by Gustaf Neumann on 12/01/22 10:04 AM
The same lob error shows up on my 5.9 install where the xml query was not missing
As said before, I am not surprised, since it is in essence the lob code of 5.10 is the same code as in 5.9 (and earlier versions). But my point was: since the update operation fails, was the "set_content" operation performed before this? Maybe, item is already broken due to earlier attempts, then one has to look at different places. So, it can make a difference if the content item exists before the operation or not.... Therefore, my question, what lobs are in your database. Do you have a lob with id=4 in your database? Do you have any lobs already in the database? If yes, then "set_content" works sometimes.... The select query of above should answer some of this.

what was it replaced with?
In essence, files can be stored in the content repository either as "file" or "lob". The OpenACS application decides, how to store it. For example, the file-storage has a parameter "StoreFilesInDatabaseP", which is per default "0", so files are stored by default with the storage_type "file", which means: in the file system.

[1] https://openacs.org/api-doc/proc-view?proc=content::item::new&source_p=1
[2] https://openacs.org/api-doc/proc-view?source_p=1&proc=fs::add_file&version_id=5681478