Forum OpenACS Q&A: Re: spam module charset encoding issue when inserting to blob

Hi again,

There is a bug half related with this topic #822 Add text storage support to cr_import_content.

Maybe adding a CLOB storage type would be the perfect solution, but since I am not very comfortable with this kind of datatype I have done a workaround with varchar2(4000).

So if you want to send emails in iso-8859-2 but reducing the capacity of a 'text' type in your content repository to 4000 characters, here are the changes:

*** Attention! I have not test all the side effects outside the acs-mail and spam packages. UAYOR ***

• add the 'text' column in your 'cr_revisions' table.

  >alter table CR_REVISIONS add text varchar2(4000)

• Comment the 'text to lob' action at the beggining of your 'content_item.new' pl/sql function.

  --  if storage_type = 'text' then
  --     v_storage_type := 'lob';
  --  else
      v_storage_type := storage_type;
  --  end if;

• Modify the 'content_revision.new' pl/sql function. This funtion is overloaded, change only te one with the 'text' parameter.

  function new (
    title         in cr_revisions.title%TYPE,
    description   in cr_revisions.description%TYPE default null,
    publish_date  in cr_revisions.publish_date%TYPE default sysdate,
    mime_type   	in cr_revisions.mime_type%TYPE default 'text/plain',
    nls_language 	in cr_revisions.nls_language%TYPE default null,
    text		    in varchar2 default null,
    item_id       in cr_items.item_id%TYPE,
    revision_id   in cr_revisions.revision_id%TYPE default null,
    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;
  
  begin
  
    v_content_type := content_item.get_content_type(item_id);
    
    v_revision_id := acs_object.new(
        object_id     => revision_id,
        object_type   => v_content_type,
        creation_date => creation_date,
        creation_user => creation_user,
        creation_ip   => creation_ip,
        context_id    => item_id
    );
    
    insert into cr_revisions (
      revision_id, title, description, mime_type, publish_date,
      nls_language, text, item_id
    ) values (
      v_revision_id, title, description, mime_type, publish_date,
      nls_language, text, item_id
    );
  
    return v_revision_id;
  
  end new;

• Change the 'acs_mail_body_to_mime_get_content_simple' query in the 'packages/acs-mail/tcl/acs-mail-procs.xql' file.

  select content, text, mime_type as v_content_type
          from cr_revisions
          where revision_id = :revision_id

• Change the 'acs_mail_encode_content' function in the 'packages/acs-mail/tcl/acs-mail-procs.tcl' file.

  [...]
                  if [db_0or1row acs_mail_body_to_mime_get_content_simple {
                          select content, text, mime_type as v_content_type
                          from cr_revisions
                          where revision_id = :revision_id
                  }] {
                          if [string equal $storage_type text] {
                                  ns_log "Notice" "acs-mail: encode: one part hit $content_item_id"
                                  # vinodk: no need for this, since we're checking
                                  #         storage_type
                                  #
                                  # We win!  Hopefully.  Check if there are 8bit characters/data.
                                  # HT NL CR SP-~  The full range of ASCII with spaces but no
                                  # control characters.
                                  if ![regexp "\[^\u0009\u000A\u000D\u0020-\u007E\]" $content] {
                                          ns_log "Notice" "acs-mail: encode: good code $content_item_id"
                                          # We're still okay.  Use it!
                                          return [list $v_content_type $text]
                                  }
                                  ns_log "Notice" "acs-mail: encode: bad code $content_item_id"
                          } else {
  [...]

I think I don't leave anything, hope it helps,

\-.-/juanjo