all-node-oracle

Noradle is for Node & Oracle integration,
with the aid of Node,
just write nearly all business logic in a infomation system in PL/SQL store procedure code,
node, in front of oracle, provide access to oracle by HTTP,
and node javascript code can access oracle directly by noradle NDBC,
oracle plsql can send out message to node by repeat NDBC call.

The formal repository name is 'noradle',
all-node-oracle is just a synonym used for npm search and github search,
never npm install all-node-oracle.

see noradle for latest version in github.

see noradle for latest npm publishment in npm registry.

The work at Now
==========

Sorry for long time of broken install scripts and demos.
I have just checked installation scripts and demos,
they are passed test for initial and overlap installation,
demo app work fine.

But documentation may be somewhat old,
not sync with the very latest work.
See demo first, and I'm rushing to make a set of refreshed documentation.
But noradle is not a small utility project,
it's a full oracle-node-based server-side information system architecture,
it have a framework and library API.
Through it's more easy to develope/maintain a oracle-based information system,
complete and concise documentation require big effort.

The recent changes are list below:

* use node-store-based session, remove oracle GAC(global application context) based session store.
support session across different oracle instances among RAC instances, data-guard nodes, distributed databases
* oracle result-cache refresh mechanism prefer user session marker based update checker
* no longer rely on GAC for features, so oracle GAC memeory overlow will never occur, no GA required
* obsolete old complex printing/url API p(k_xhtp)/u, add new concise print API(x,m,tb,tr,sty,l)
that servlet code is formatted well for both plsql code and html/xml code
* noradle core have fine architecture, split to layers, servlet engine is just a http handler,
and can be easy integrated to connect/express like environment.
* support response filter plugin architecture, now support lines/resultsets format converters.
* All none core features is removed or refactored as internal plugins.
* old DCO(exthub+worker) call-out mechanism is removed,
use repeated NDBC call-in to listen to pipe message(as call-out request header/body)

The noradle project is now advanced to v0.10.x release.

Roadmap:

* one DBPool instance can hold oracle connections from different oracle instance among RAC, data-guard, distributed db.
and one request can route to the right connect among them.
* Good response caching, server cache that can serve requests even if it's session controled page.
* GAC based result cache version updater
*
* connection tunnel that let oracle to connect to node front positioned behind NAT
* better file upload/post design

Overview
==========

1. psp.web. NodeJS act as http gateway to convert and pass http request data onto oracle PL/SQL procedure
and receive and transfer back what the PL/SQL produce.
2. call in db driver. Provide javascript API to access PL/SQL page and facilities to product result sets
and convert them into javascript objects.
3. call out facility as repeated call in listen for new messages.

see Introduction for more intro.

see Noradle's Goal to understand what noradle aim for.

cd ./demo && node server.js will start demo server.
or see ready demo server at http://unidialbook.com/demo.
All noradle features are shown in demo app.

Installation
===========================

$3

* Oracle Instant Client or Any Oracle Client is installed first
* Python, used by node-gyp
* C++ Compiler toolchain (GCC, Visual Studio or similar)
* set ENV variables OCI_LIB_DIR, OCI_INCLUDE_DIR, OCI_VERSION, NLS_LANG
* Create symlinks for libclntsh and libocci
* (Linux) Install libaio
* Configure the dynamic library path on your platform to include $OCI_LIB_DIR

No No No, all items in the above list is not required at all.

All you need to install on is just node and oracle,
there are no any types of oracle client driver required,
nor oracle instant client required.
If you can install node and oracle on your server on any OS,
you can install noradle on your server.

$3

Noradle will install two parts:

* one is node javascript code,
run on top of node,
npm -g install noradle is ok.
* the other is oracle schema units,
install/run on top of oracle database.
cd oracle && sqlplus "/ as sysdba" @install.sql will create supporting schema and its objects in oracle database.

see Deployment for detailed info.

Part 1 : psp.web (plsql http servlet)
==========

very basic demo

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

``plsql

procedure show_user_name 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.write('hello ' || v.user_name);
end;

// access http://host/dbu/show_user_name?uid=xxx then

`

see noradle-demo 'server.js'
for how to integrate a noradle servlet engine to a node http server.

Noradle support concise printing API as below:

* x(tag) print jade like tag for xml/xhtml
* m(multi) multiply template with array
* tb(list) quick print table
* tr(tree) quick print hierachical/nested tags/data
* sty(style) embed/link css
* l(url) link other resouce with concise code

see demo app for all of above.

documentation links

_Note: doc content may be old or obsolete._

please see Introduction at doc/introduction.md on github (format will lose)

please see Documentation Index at my site

please see Introduction at my site

please see Deployment at my site

please see API demo at my demo site

please see Basic Coding Guide of Noradle

please see SAAS app "dialbook" developed on Noradle (you can use any mobile number
11digits to login)

please see License of PSP.WEB at doc/license.md


Part 2 : NDBC (node database connectivity)
======

rs.print(name, sys_refcursor) can print a named SQL result set that is compact formatted.

$3

`javascript

var Noradle = require('..')
, parse = Noradle.RSParser.rsParse
, inspect = require('util').inspect
;

var dbPool = new Noradle.DBPool(1522, {
FreeConnTimeout : 60000
});

var dbc = new Noradle.NDBC(dbPool, {
x$dbu : 'demo',
__parse : true
});

dbc.call('db_src_b.example', {limit : 10}, function(status, headers, page){
console.log("no:", no);
if (status != 200) {
console.error('status is', status);
console.error(page);
console.error(headers);
return;
}
log(page);
if (page instanceof String) {
console.log(inspect(parse(page), {depth : 8}));
} else {
console.log(inspect(page, {depth : 8}));
}

});


`

$3

`plsql
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
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;

`

$3

`text
[test]
OBJECT_NAME:1,SUBOBJECT_NAME:1,OBJECT_TYPE:1,CREATED:12
MEDIA_B,,PACKAGE,2014-05-13 11:31:37
MEDIA_B,,PACKAGE BODY,2014-05-13 11:31:37
LIST_B,,PACKAGE,2014-07-04 11:32:16
LIST_B,,PACKAGE BODY,2014-07-04 11:32:16
ATTR_TAGP_DEMO_B,,PACKAGE,2014-07-04 15:49:37
ATTR_TAGP_DEMO_B,,PACKAGE BODY,2014-07-04 15:50:04
STYLE_B,,PACKAGE,2014-07-03 09:40:08
STYLE_B,,PACKAGE BODY,2014-07-03 09:45:21
PO_IFRAME_B,,PACKAGE,2014-10-10 10:56:41
PO_IFRAME_B,,PACKAGE BODY,2014-10-10 10:56:43

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

`

$3

Result sets print support main-sub table data print, can be synthesized to hierachical javascript/JSON object.

please see Call oracle plsql stored procedure with javascript at
doc/js_call_plsql.md

Part 3 : call out net proxy
======

The "call out proxy facility" is depleted,
use repeated NDBC call to monitor call-out messages,
use normal NDBC call to write back call-out response to oracle.

The two demos below use repeated NDBC call to pull message from oracle.

use named pipe, sep by line message format, direct send pipe demo

$3

`javascript

var Noradle = require('noradle')
, log = console.log
, inspect = require('util').inspect
;

var dbPool = new Noradle.DBPool(1522, {
FreeConnTimeout : 60000
})
, callout = new Noradle.NDBC(dbPool, {
__parse : true,
__repeat : true,
__parallel : 1,
__ignore_error : true,
x$dbu : 'public',
timeout : 1
})
, callin = new Noradle.NDBC(dbPool, {
x$dbu : 'public'
})
;

callout.call('mp_h.pipe2node', {pipename : 'pipe_only'}, function(status, headers, p){
var pipename = p.pop()
, oper = p[0]
, p1 = parseInt(p[1])
, p2 = parseInt(p[2])
, result
;
console.log('callout input params', p);
if (pipename) {
switch (oper) {
case 'add':
result = p1 + p2;
break;
case 'minus':
result = p1 - p2;
break;
case 'multiply':
result = p1 * p2;
break;
default:
result = 0;
}
// need call back with response to oracle
callin.call('mp_h.node2pipe', {
h$pipename : pipename,
oper : oper,
result : result
});
}
});
`

$3

`plsql

procedure multiple_callout_easy_resp is
v_result number;
v_rpipename varchar2(100) := r.cfg || '.' || r.slot;
p1 number := r.getn('p1', 5);
p2 number := r.getn('p2', 3);
v_oper varchar2(30);
v_opers varchar2(100);
v_add number;
v_minus number;
v_multiply number;
begin
-- clear receive reponse pipe first
dbms_pipe.purge(v_rpipename);

-- callout 1
dbms_pipe.pack_message('add');
dbms_pipe.pack_message(p1);
dbms_pipe.pack_message(p2);
dbms_pipe.pack_message(v_rpipename);
tmp.n := dbms_pipe.send_message('pipe_only');

-- callout 2
dbms_pipe.pack_message('minus');
dbms_pipe.pack_message(p1);
dbms_pipe.pack_message(p2);
dbms_pipe.pack_message(v_rpipename);
tmp.n := dbms_pipe.send_message('pipe_only');

-- callout 3
dbms_pipe.pack_message('multiply');
dbms_pipe.pack_message(p1);
dbms_pipe.pack_message(p2);
dbms_pipe.pack_message(v_rpipename);
tmp.n := dbms_pipe.send_message('pipe_only');

-- receive all the callout response, with any order
for i in 1 .. 3 loop
if not mp.pipe2param(v_rpipename, 15) then
-- callout timeout
h.status_line(400);
x.t('callout timeout!');
return;
end if;
v_oper := r.getc('oper');
v_result := r.getn('result');

v_opers := v_opers || v_oper || ',';
case v_oper
when 'add' then
v_add := v_result;
when 'minus' then
v_minus := v_result;
when 'multiply' then
v_multiply := v_result;
else
null;
end case;
end loop;

x.p('

', 'p1:' || p1);
x.p('

', 'p2:' || p2);
x.p('

', 'response receive order:' || v_opers);
x.p('

', 'add:' || v_add);
x.p('

', 'minus:' || v_minus);
x.p('

', 'multiply:' || v_multiply);
end;

`

use default named pipe, sep by line message format, use standard print API to generate request demo

$3

` javascript

var Noradle = require('noradle')
, log = console.log
, inspect = require('util').inspect
;

var dbPool = new Noradle.DBPool(1522, {
FreeConnTimeout : 60000
});
var callout = new Noradle.NDBC(dbPool, {
__repeat : true,
__parallel : 1,
__ignore_error : false,
__parse : true,
timeout : 1
});

var callin = new Noradle.NDBC(dbPool, {});

/**
* you can fetch multiple types of call-out messages from one named pipe
* use header to differentiate them
*/
callout.call('demo.mp_h.fetch_msg', function(status, headers, message){
var msgType = headers['Msg-Type'];
switch (msgType) {
case 'type1':
console.log('type 1 message received.');
break;
case 'type2':
console.log('type 2 message received.');
break;
case 'type3':
console.log('type 3 message received.');
break;
case 'type4':
console.log('type 4 message received.');
// mimic call external service to get result and send it back to oracle as synchronized call return value
setTimeout(function(){
callin.call('demo1.mp_h.node2pipe', {h$pipename : headers['Callback-Pipename'], temperature : -3});
}, 1000);

break;
}
console.log(headers);
console.log(message);
});
`

$3

` plsql
procedure sync_sendout4 is
begin
x.p('

', 'a call-out message is send as this page is produced!');
mp.begin_msg;
mp.set_callback_pipename;
h.header('Content-Type', 'text/items');
h.header('Msg-Type', 'type4');
h.line('Tianjin');
mp.send_msg;

if not mp.pipe2param then
h.status_line(504);
x.t('callout(get termperature) timeout!');
return;
end if;
x.t('temperature is ' || r.getn('temperature') || ' degree');
end;
``

$3

* see noradle-demo
for example app of noradle
that use http servlet, NDBC call, call-out features.
* see noradle-cm
for how to do Software Configuration Management with noradle app(PLSQL app).
* see unidialbook
for a noradle based production app.
It's a chinese language SAAS web app that serve address book for group customers,
developed for China Unicom(Tianjin).
And unidialbook have some shared plugin app like EXAM, they are all noradle based.