README

Brief Introduction

What's NORADLE, what's the word mean?

NORADLE is the two words NODE and ORACLE combined, imply NORADLE is for integration between NODE and ORACLE.

Nodejs ecosystem is thriving, while ORACLE is a great old brand and have largest share on DB market. Nodejs lead to purely whole javascript web stack, bring great simplicity and creativity against the old traditional multi-languages stacks as J2EE, PHP. ORACLE database is often the first and only choice for mission critical and enterprise information systems.

But so sorry, there are no trustable and simple software that can integrate between the best network/middleware app technique stack nodejs and the best database oracle.That' why NORADLE is brought to world by me.

NORADLE will be the most concise solution for single page applications, web sites, hybrids, or public HTTP APIs(REST) services for oracle based projects.

the application scene of NORADLE

• as a nodejs-to-oracle access driver like JDBC, NORADLE call it NDBC
• use solely PL/SQL stored procedure to executing as http servlet without any other language including javascript
• let PL/SQL code to send message or call to nodejs service, extending PL/SQL's ability

the technical design principles of NORADLE

• simple TCP based internal protocol to connect client to oracle, not over native db driver, favor http like request/response mode
• layered tech stack design from basic frame dispatcher to high layer http servlet container
• not another db-driver only, but full duplex way integration between nodejs and oracle
• simple intuitive usage/API, simple internal supporting design, discard any dazed complex user/internal design
• encourage all SQL in db, all business logic code in db, with PL/SQL
• with the support/leverage of nodejs, let PL/SQL alone, to support entire backend business logic
• easy learning curve, use PL/SQL solely for backend and PL/SQL+javascript for all, no other overhead language required
• support critical production requirement, reliable robustness, performance, availability and scalability

technical stack overview

basic network architecture

clients  ======>  dispatcher  <-----  oracle processes
      (multiplexed)   ^
                 monitor/console

for details, see technique-stack

installation and deployment

• ensure XMLDB is installed on oracle, and use it to set network access right to NORADLE
• install NORADLE schema PSP as core units on oracle side
• install NORADLE schema DEMO as demo app to test installation
• configure server_control_t table records for OSPs to connect to NORADLE dispatcher
• start NORADLE dispatcher with right configuration to listen for OSP,client and monitor/console
• start DEMO as http server who use PL/SQL servlet and check all is right

detail reference deployment guide

Develop with NORADLE

Write PL/SQL servlet code

The very basic demo that use r.getc to get request parameter, call h.write to print response body.

create or replace procedure show_user_name_b is
  v user_tab%rowtype;
begin
  v.user_id := r.getc('uid');
  select a.* into v from user_tab a where a.user_id = v.user_id;
  h.content_type('text/plain');
  h.header('x-'||v.user_id, v.user_name);
  h.write('hello ' || v.user_name);
end;

There some points to note:

• use table%rowtype or table.column%type to define local variable(s) is very convenient and concise
• parameter binding in SQL is direct and simple
• use r.getx series API to get servlet input is relax and flexible versus PL/SQL procedure parameter binding
• you can call h.write to write response entity body
• you can call h.header(name,value) to set any name-value pair in response header
• you can call predefined h.header API like h.content_type to set particular name/value pair in response header

see PL/SQL dev guide
see all basic input/output API

Note there are no parameters in the PL/SQL stored procedure, and no return value for output, NORADE doesn't respect PL/SQL procedure's IN/OUT/INOUT parameters and return value as ORACLE's mod_plsql servlet or node-oracledb API doc does, NORADLE's input/output API is like PHP way, just get named request parameter value(s) and set response headers and write response body, it's more simple, relax, intuitive and flexible.

create DBPool to connect to oracle through dispatcher

see dispatcher architecture for background knowledge.

start dispatcher

listen_port=1522 client_config="filepath.js" noradle-dispatcher
or
noradle-dispatcher [listen_port:=1522] [client_config]

• ENV variable listen_port set the port dispatcher is listening
• ENV variable client_config set the path of client configuration file, is a js file like

note: cmd argument have higher priority over named ENV variable

client_config's content is like below, it's also the default configuration if this parameter is not set

module.exports = {
  demo: {
    min_concurrency: 3,
    max_concurrency: 3,
    passwd: 'demo'
  }
};

let OSP to register to dispatcher

log PSP user to oracle db, configure record of server_control_t, set a record like below.

CFG_ID	test_dispatcher
GW_HOST	test.noradle.com
GW_PORT	9009
MIN_SERVERS	10
MAX_REQUESTS	1000
MAX_LIFETIME	+0000 00:10:00

when execute exec k_pmon.run_job, MIN_SERVERS number of OSPs are started and kept, all OSPs for CFG_ID test_dispatcher will connect to test.noradle.com:9009 as it's the listening address of dispatcher. when a OSP served MAX_REQUESTS number of requests, or running over MAX_LIFETIME timespan, OSP will quit safely and restarted again.

run exec k_pmon.stop will stop all OSPs for all server_control_t config.

run exec kill([cfgId] [,slotId]) can quit all or part of OSPs.

• exec kill with no parameter will let all OSPs to quit
• exec kill(cfgId) will let all OSPs with the specified cfgId to quit
• exec kIll(cfgId,slotId) will let a OSP with specified cfgId and slotId to quit

let client to connect to dispatcher

var noradle = require('noradle');
var dbPool = noradle.DBDriver.connect([port, ip], {cid:"xxx", passwd:"xxx"});

• first parameter for noradle.DBDriver.connect is the same as node's socket.connect(), id can be [port], [port,ip] or [path].
• secondary parameter for noradle.DBDriver.connect is client's client id and password for dispatcher

Use internal rudimentary API to access ORACLE

it's like a node http request API with a db-driver like find free path to server beforehand.

dbPool.findFree(env, db_selector, function(err, oraReq){
  // env: identifier or marker for the request for logger or monitor
  // db_selector: when dispatcher hold OSP from RAC/DG instances, specify the selection rule
  // when dbPool find a free access slot, findFree callback will be called with a new oraReq instance
  if (err) {
    // basically, there are no error for dbPool.findFree
    console.error(err);
    return;
  }
  oraReq
    .init('DATA', '')
    .addHeader('uid', 'kaven276')
    .addHeaders({dbu:'demo',prog:'show_user_name_b'}, 'x$')
    .write(body)
    .on('response', onResponse)
    .on('error', onError)
    .end(onResponse)
  ;
  // .addHeader(s) can add name/value(s) pair that can be got by pl/sql r.getx series API
  // .write(body) data will fill pl/sql package variable rb.blob_entity
  // .end(onResponse) can is just .on('response', onRepsonse).end() combined
  // any exception for the request/response cycle will emit error
  // any pl/sql servlet call must have x$dbu,x$prog headers to specify which PL/SQL procedure to execute
  function onResponse(oraRes) {
     console.log(oraRes.status, oraRes.headers);
     oraRes.on('data', function(data){
       ...
     }
     oraRes.on('end', function(){
       ...
     }
  }
  function onError(error){
    ...
  });

Use NDBC API to access ORACLE

through PL/SQL servlet can print arbitrary content to feed NDBC call, the most NDBC use is for fetch SQL result sets, so the example is for that.

The node javascript client who call in oracle plsql servlet

var noradle = require('noradle')
  , dbPool = noradle.DBDriver.connect([9009], {cid : 'test', passwd : 'test'})
  , dbc = new noradle.NDBC(dbPool, {__parse : true, 'x$dbu': 'demo'})
  ;
dbc.call('db_src_b.example', {limit : 3}, function(status, headers, page){
  if(status!==200) {
    ...
    return;
  }
  console.log(status);
  console.log(headers);
  console.log(page);
});

Note:

• NDBC instance have dbPool as oracle access path supporting object as (dbPool, {...
• NDBC have default parameter in second parameter as {__parse : true, 'x$dbu': 'demo'}
• ndbc.call(stored_procedure_name, parameter, cb) will combine default parameter to make a PL/SQL servlet request
• callback(status, headers, page) is just like http response
• headers is name-value(s) pairs
• page is Buffer, String or Object type as headers['content-type'] indicate
• __parse : true will let noradle automatically convert certain response type to parsed javascript objects

The oracle plsql sevlet code who generate SQL result sets

create or replace package body db_src_b is

    procedure example is
        cur sys_refcursor;
        v1  varchar2(50) := 'psp.web';
        v2  number := 123456;
        v3  date := date '1976-10-26';
    begin
        h.content_type('text/resultsets');

        open cur for
            select a.object_name, a.subobject_name, a.object_type, a.created
                from user_objects a
             where rownum <= r.getn('limit', 8);
        rs.print('test', cur);

        open cur for
            select v1 as name, v2 as val, v3 as ctime from dual;
        rs.print('namevals', cur);
    end;

end db_src_b;

Note:

• r.getn('limit', 8) will get number type parameter limit, as js code, it's 3, and default to 8 if no this input
• h.content_type('text/resultsets'); specify the response content-type is result sets that can be converted to javascript object or JSON text
• rs.print(name, sys_refcursor) will print SQL result and its meta data to condensed table format
• v1,v2,v3 is varchar2/number/date types, all scalar data can be printed out with from dual SQL

the data in transfer

[objects]
OBJECT_NAME:1,SUBOBJECT_NAME:1,OBJECT_TYPE:1,CREATED:12
TOOL,,TYPE,2015-04-20 16:38:39
TOOL2,,TYPE,2015-04-20 16:38:39
TERM_T,,TABLE,2015-04-20 16:38:45

[namevals]
NAME:1,VAL:2,CTIME:12,P1:1,P2:1,PNULL:1
psp.web,123456,1976-10-26 00:00:00,value1,value2,

Note:

• SQL resultsets is printed section by section
• one resultset have one meta line and zero, one or more data lines each line for one record
• columns/lines are separate not only with comma and linefeed, but with a hidden ACSII char together with separator so column content can safely have comma and linefeed.

the output

200
{ Date: 'Fri, 24 Jul 2015 00:55:46 GMT',
  'Content-Encoding': '?',
  'Content-Length': '649',
  'Content-Type': 'text/resultsets; charset=UTF-8',
  'x-pw-timespan': '40 / 40 ms' }
{ objects: 
   { name: 'objects',
     attrs: 
      [ { name: 'object_name', dataType: 1 },
        { name: 'subobject_name', dataType: 1 },
        { name: 'object_type', dataType: 1 },
        { name: 'created', dataType: 12 } ],
     rows: 
      [ { object_name: 'TOOL',
          subobject_name: '',
          object_type: 'TYPE',
          created: '2015-04-20 16:38:39' },
        { object_name: 'TOOL2',
          subobject_name: '',
          object_type: 'TYPE',
          created: '2015-04-20 16:38:39' },
        { object_name: 'TERM_T',
          subobject_name: '',
          object_type: 'TABLE',
          created: '2015-04-20 16:38:45' } ] },
  namevals: 
   { name: 'namevals',
     attrs: 
      [ { name: 'name', dataType: 1 },
        { name: 'val', dataType: 2 },
        { name: 'ctime', dataType: 12 },
        { name: 'p1', dataType: 1 },
        { name: 'p2', dataType: 1 },
        { name: 'pnull', dataType: 1 } ],
     rows: 
      [ { name: 'psp.web',
          val: 123456,
          ctime: '1976-10-26 00:00:00',
          p1: 'value1',
          p2: 'value2',
          pnull: '' } ] } }

Note:

• the parsed final result is javascript object, each key stand for one result set
• each result set have name, attrs(column meta data) array and rows array

Use NORADLE's http handler to let PL/SQL stored procedure act solely as http servlet

With noradle.handlerHTTP(req,res,next) as nodejs http handler, PL/SQL servlet can get all input http input including url sections, headers, cookies, request body, form submit parameters, file upload, ajax post ...

PL/SQL servlet can set http status code, response headers and write response body for various text-based formats. Set response particular response header value will trigger certain noradle.handlerHTTP filter plugins include those below:

• resultsets converter to JSON
• template type/file to bind response data
• convert post response with body to a 303 response and a 200 http get request to avoid repeated post
• link dynamic generated css to `' to second text/css request to avoid repeated css transfer
• compute response content body digest, used in content-md5 for validation and ETag for cache (`h.etag_md5_on|h.etag_md5_off|h.content_md5_off|h.content_md5_on|h.content_md5_auto)
• gzip transfer (`h.content_encoding_try_zip|h.content_encoding_auto|h.content_encoding_identity)

or just specify http behavior directly:

• specified status code other than default 200 (h.status_line(304))
• use file download with specified filename (h.content_disposition_attachment|h.content_disposition_inline)
• specify content-type (h.content_type('text/html','UTF-8'))
• print with specified character set
• specify response language (h.content_language('zh-CN'))
• redirect to another url (h.redirect(url,[status]) or h.gol(url,[status]))
• refresh itself or to other url (h.refresh(seconds,[url]))
• chunked transfer (flush parts, work, flush another)
• give ETag value (h.etag(value))

As you see in the above, NORADLE respect all http features as it make sense, it aim to full http protocol support as origin server role and as dynamic content service provider. For cache support, see NORADLE cache.

DEMO have completed range of demos for all aspect of NORADLE PL/SQL servlet for http. Online demo is here, source code link is provided on most pages.

set up nodejs http server who mount noradle.handlerHTTP

var cfg = require('./cfg.js')
  , http = require('http')
  , noradle = require('noradle')
  , harp = require('harp')
  , connect = require('connect')
  , app = connect.createServer()
  , dbPool = noradle.DBDriver.connect([9009], {cid: 'test', passwd: 'test'})
  ;

function ReqBaseC(req){
  this.y$static = y$static;
  // you custom oraReq headers set here
  // set x$dbu,x$prog to specify which stored procedure to execute
  // for complete set of control headers, see doc
}

app.use(noradle.handlerHTTP(dbPool, ReqBaseC, {
  static_url: '<the url prefix to static asset>',
  upload_dir: '<the root path for store uploaded files>'
}));

app.use('<static url root>', connect.static(cfg.static_root, {
    maxAge: 24*60*60*1000,
    redirect: false
}));

app.use(y$static, harp.mount(cfg.static_root));

/**
 * start a combined http server, which inlucde
 * plsql servlet, static file, harp compiler
 */
http.createServer(app).listen(port, function(){
  console.log('http server is listening at ' + port);
});

use function ReqBaseC(req) to set control headers, particular required headers like x$dbu, x$prog.

Advanced Features list

• server-side session store
• hierachical resultsets support
• converters
• multiple character set support
• PL/SQL DCO(direct call out) to nodejs service
• RAC/DG for availability and scalability
• server-side cache