Introduction

This post is the last part of a blog post serie about CKEditor5 image upload, you can read the part 1 here and the part 2 here, if it's not already done. After publishing the second part Jon Dixon challenged me on Twitter

He was right, it was not the best solution to store the images as a blob, even if it worked properly. So let's see if we can meet this challenge!

Set up your OCI bucket

I will assume that you have a bucket ready to use. I recommend you these two excellent blog posts on the subject:

• Secure APEX Access to OCI Object Storage written by Jon Dixon
• Better File Storage in Oracle Cloud written by Adrian Png

After following one of these post, you should end up with

• the URL to upload and download your file
• a web credential in your APEX workspace to authenticate our web service calls.

How it will work?

Here is the simple schema of the different components.

When a picture is a added to the CKEditor5

1. the file will be send to the ORDS POST handler
2. the POST handler will create a row in the CKEDITOR_IMAGES_OCI table
3. the POST handler will upload the file to the OCI bucket with the id as its name
4. the POST handler will return the url where the file is accessible. This url will point to a GET handler that will use the id stored in the CKEDITOR_IMAGES_OCI table to retrieve the file from the bucket and serve it.

At this point, I was thinking that maybe I was wrong to accept this challenge... But let's see where it all goes.

Implementation

Data model

First of all, we need a table to store/create the identifiers of our images:

-- create tables
create table ckeditor_images_oci (
    id                             number generated by default on null as identity 
                                   constraint ckeditor_images_oci_id_pk primary key,
    content_type               varchar2(500),
    created                        date not null,
    created_by                     varchar2(255 char) not null,
    updated                        date not null,
    updated_by                     varchar2(255 char) not null
)
;

-- triggers
create or replace trigger ckeditor_images_oci_biu
    before insert or update 
    on ckeditor_images_oci
    for each row
begin
    if inserting then
        :new.created := sysdate;
        :new.created_by := coalesce(sys_context('APEX$SESSION','APP_USER'),user);
    end if;
    :new.updated := sysdate;
    :new.updated_by := coalesce(sys_context('APEX$SESSION','APP_USER'),user);
end ckeditor_images_oci_biu;
/

ORDS REST handlers

As mentionned previously, we need a POST and a GET handler

DECLARE
  l_roles     OWA.VC_ARR;
  l_modules   OWA.VC_ARR;
  l_patterns  OWA.VC_ARR;

BEGIN

  ORDS.DEFINE_MODULE(
      p_module_name    => 'CKEditor',
      p_base_path      => '/ckeditor/',
      p_items_per_page => 25,
      p_status         => 'PUBLISHED',
      p_comments       => NULL);

  ORDS.DEFINE_TEMPLATE(
      p_module_name    => 'CKEditor',
      p_pattern        => 'file',
      p_priority       => 0,
      p_etag_type      => 'HASH',
      p_etag_query     => NULL,
      p_comments       => NULL);

  ORDS.DEFINE_TEMPLATE(
      p_module_name    => 'CKEditor',
      p_pattern        => 'oci_file',
      p_priority       => 0,
      p_etag_type      => 'HASH',
      p_etag_query     => NULL,
      p_comments       => NULL);

  ORDS.DEFINE_HANDLER(
      p_module_name    => 'CKEditor',
      p_pattern        => 'oci_file',
      p_method         => 'POST',
      p_source_type    => 'plsql/block',
      p_mimes_allowed  => '',
      p_comments       => NULL,
      p_source         => 
'declare
    l_id                    number;
    l_blob                  blob;
    e_file_too_big          exception;
    upload_failed_exception exception;
    l_response              clob;
    l_base_url              varchar2(500) := ''YOUR BUCKET URL'';
    l_credential_static_id  varchar2(100) := ''YOUR OCI WEB CREDENTIAL'';
begin 
    dbms_lob.createtemporary(l_blob, false);
    l_blob := :body;

    if (sys.dbms_lob.getlength(l_blob) > 100000) then
        raise e_file_too_big;
    end if;

    insert into ckeditor_images_oci (content_type) values (:content_type)
    returning id into l_id;

    apex_web_service.g_request_headers(1).name := ''Content-Type'';
    apex_web_service.g_request_headers(1).value := :content_type;
    l_response := apex_web_service.make_rest_request(
      p_url => l_base_url || l_id
      , p_http_method => ''PUT''
      , p_body_blob => l_blob
      , p_credential_static_id => l_credential_static_id
    );

    if apex_web_service.g_status_code != 200 then  
      raise upload_failed_exception;
    end if;

    commit;

    dbms_lob.freetemporary(l_blob);

    :status := 201;

    apex_json.open_object;
    apex_json.write(
        p_name => ''url'', 
        p_value => owa_util.get_cgi_env(''REQUEST_PROTOCOL'') ||
                   ''://'' ||
                   owa_util.get_cgi_env(''SERVER_NAME'') ||
                   owa_util.get_cgi_env(''SCRIPT_NAME'')||
                   owa_util.get_cgi_env(''PATH_INFO'') || 
                   ''/'' || l_id 
    );
    apex_json.close_object;
exception
when upload_failed_exception then
    apex_json.open_object;
    apex_json.open_object(''error'');
    apex_json.write(''message'', ''Error during upload on the OCI bucket.'');
    apex_json.close_object;
    apex_json.close_object;
when e_file_too_big then 
    apex_json.open_object;
    apex_json.open_object(''error'');
    apex_json.write(''message'', ''File too big (max 100kb).'');
    apex_json.close_object;
    apex_json.close_object;
when others then
    apex_json.open_object;
    apex_json.open_object(''error'');
    apex_json.write(''message'', ''Something wrong!'');
    apex_json.close_object;
    apex_json.close_object;
end;');

  ORDS.DEFINE_PARAMETER(
      p_module_name        => 'CKEditor',
      p_pattern            => 'oci_file',
      p_method             => 'POST',
      p_name               => 'X-ORDS-STATUS-CODE',
      p_bind_variable_name => 'status',
      p_source_type        => 'HEADER',
      p_param_type         => 'STRING',
      p_access_method      => 'OUT',
      p_comments           => NULL);

  ORDS.DEFINE_TEMPLATE(
      p_module_name    => 'CKEditor',
      p_pattern        => 'oci_file/:id',
      p_priority       => 0,
      p_etag_type      => 'HASH',
      p_etag_query     => NULL,
      p_comments       => NULL);

  ORDS.DEFINE_HANDLER(
      p_module_name    => 'CKEditor',
      p_pattern        => 'oci_file/:id',
      p_method         => 'GET',
      p_source_type    => 'plsql/block',
      p_mimes_allowed  => '',
      p_comments       => NULL,
      p_source         => 
'declare
    l_id number := :id;
    l_file blob;
    l_content_type varchar2(400);
    l_base_url              varchar2(500) := ''YOUR BUCKET URL'';
    l_credential_static_id  varchar2(100) := ''YOUR OCI WEB CREDENTIAL'';
begin
    l_file := apex_web_service.make_rest_request_b
       (p_url => l_base_url || :id,
        p_http_method => ''GET''
        ,p_credential_static_id => l_credential_static_id);

    select content_type
    into l_content_type
    from ckeditor_images_oci
    where id = :id;

    sys.htp.init;
    sys.owa_util.mime_header( l_content_type, FALSE );
    sys.htp.p(''Content-length: '' || sys.dbms_lob.getlength( l_file ));
    sys.htp.p(''Content-Disposition: attachment; filename="'' || :id || ''"'' );
    sys.owa_util.http_header_close;
    sys.wpg_docload.download_file( l_file );
end;');

  ORDS.DEFINE_PARAMETER(
      p_module_name        => 'CKEditor',
      p_pattern            => 'oci_file/:id',
      p_method             => 'GET',
      p_name               => 'id',
      p_bind_variable_name => 'id',
      p_source_type        => 'URI',
      p_param_type         => 'STRING',
      p_access_method      => 'IN',
      p_comments           => NULL);



  l_roles(1) := 'RESTful Services';
  l_modules(1) := 'CKEditor';

  ORDS.DEFINE_PRIVILEGE(
      p_privilege_name => 'Privilege_CKEditor',
      p_roles          => l_roles,
      p_patterns       => l_patterns,
      p_modules        => l_modules,
      p_label          => 'CKEditor Privilege',
      p_description    => '',
      p_comments       => NULL);  

  l_roles.DELETE;
  l_modules.DELETE;
  l_patterns.DELETE;                                    

COMMIT;

END;

CKEditor5 settings

Actually, nothing has changed here, unless you have, like me, created two new ORDS handlers and you need to update the url in the simpleUpload configuration of the CKEditor item. If you haven't finished [part 1] (lmoreaux.hashnode.dev/ckeditor5-image-upload), I recommend you to read it first.

// configure the uploader
    options.editorOptions.simpleUpload = {
            uploadUrl: "YOUR ORDS POST HANDLER URL",
            withCredentials: true,
            headers: {
               'Apex-Session': apexSession
            }
    };

Working example can be found here.

The pros and the cons

Pros:

• The images are stored outside the database in a secure location
• OCI Object Storage is cheaper than database storage
• The challenge is successful 😊

Cons:

• The blob is first send to the database resulting of using ressources.

Conclusion

The ideal solution would be to send the file via OCI on the client side. But since the simple adapter uses POST requests, and the OCI object storage offers a PUT handler, the only solution would be to create a custom uploader and use an OAuth2 authentication. I think this is a bit too complicated for a low code platform, what do you think?