0.0.4 • Published 5 years ago

plasoft-firebird v0.0.4

Weekly downloads
8
License
ISC
Repository
github
Last release
5 years ago

Plasoft Logo

Firebird Logo

NPM version NPM downloads

NPM NPM

Cliente Firebird JavaScript para Node.js.

Cliente Firebird assíncrono puramente JavaScript para Node.js ajustado para suportar firebird 2.1. baseado completamente no node-firebird

Firebird nas redes sociais

Instalação

npm install plasoft-firebird --save

Utilização

var  Firebird = require('plasoft-firebird');

Métodos

  • Firebird.escape(value) -> return {String} - prevent for SQL Injections

  • Firebird.attach(options, function(err, db)) attach a database

  • Firebird.create(options, function(err, db)) create a database

  • Firebird.attachOrCreate(options, function(err, db)) attach or create database

  • Firebird.pool(max, options, function(err, db)) -> return {Object} create a connection pooling

Connection types

Connection options

var  options = {};

  

options.host = '127.0.0.1';

options.port = 3050;

options.database = 'database.fdb';

options.user = 'SYSDBA';

options.password = 'masterkey';

Classic

Firebird.attach(options, function(err, db) {

  

if (err)

throw  err;

  

// db = DATABASE

db.query('SELECT * FROM TABLE', function(err, result) {

// IMPORTANT: close the connection

db.detach();

});

  

});

Pooling

// 5 = the number is count of opened sockets

var  pool = Firebird.pool(5, options);

  

// Get a free pool

pool.get(function(err, db) {

  

if (err)

throw  err;

  

// db = DATABASE

db.query('SELECT * FROM TABLE', function(err, result) {

// IMPORTANT: release the pool connection

db.detach();

});

});

  

// Destroy pool

pool.destroy();

Database object (db)

Methods

  • db.query(query, [params], function(err, result)) - classic query, returns Array of Object

  • db.execute(query, [params], function(err, result)) - classic query, returns Array of Array

  • db.sequentially(query, [params], function(row, index), function(err)) - sequentially query

  • db.detach(function(err)) detach a database

  • db.transaction(isolation, function(err, transaction)) create transaction

Transaction methods

  • transaction.query(query, [params], function(err, result)) - classic query, returns Array of Object

  • transaction.execute(query, [params], function(err, result)) - classic query, returns Array of Array

  • transaction.commit(function(err)) commit current transaction

  • transaction.rollback(function(err)) rollback current transaction

Examples

PARAMETRIZED QUERIES

Parameters

Firebird.attach(options, function(err, db) {

  

if (err)

throw  err;

  

// db = DATABASE

db.query('INSERT INTO USERS (ID, ALIAS, CREATED) VALUES(?, ?, ?) RETURNING ID', [1, 'Pe\'ter', new  Date()] function(err, result) {

console.log(result[0].id);

db.query('SELECT * FROM USERS WHERE Alias=?', ['Peter'], function(err, result) {

console.log(result);

db.detach();

});

});

});

BLOB (stream)

Firebird.attach(options, function(err, db) {

  

if (err)

throw  err;

  

// db = DATABASE

// INSERT STREAM as BLOB

db.query('INSERT INTO USERS (ID, ALIAS, FILE) VALUES(?, ?, ?)', [1, 'Peter', fs.createReadStream('/users/image.jpg')] function(err, result) {

// IMPORTANT: close the connection

db.detach();

});

});

BLOB (buffer)

Firebird.attach(options, function(err, db) {

  

if (err)

throw  err;

  

// db = DATABASE

// INSERT BUFFER as BLOB

db.query('INSERT INTO USERS (ID, ALIAS, FILE) VALUES(?, ?, ?)', [1, 'Peter', fs.readFileSync('/users/image.jpg')] function(err, result) {

// IMPORTANT: close the connection

db.detach();

});

});

READING BLOBS (ASYNCHRONOUS)

Firebird.attach(options, function(err, db) {

  

if (err)

throw  err;

  

// db = DATABASE

db.query('SELECT ID, ALIAS, USERPICTURE FROM USER', function(err, rows) {

  

if (err)

throw  err;

  

// first row

rows[0].userpicture(function(err, name, e) {

  

if (err)

throw  err;

  

// +v0.2.4

// e.pipe(writeStream/Response);

  

// e === EventEmitter

e.on('data', function(chunk) {

// reading data

});

  

e.on('end', function() {

// end reading

// IMPORTANT: close the connection

db.detach();

});

});

  

});

});

STREAMING A BIG DATA

Firebird.attach(options, function(err, db) {

  

if (err)

throw  err;

  

// db = DATABASE

db.sequentially('SELECT * FROM BIGTABLE', function(row, index) {

  

// EXAMPLE

stream.write(JSON.stringify(row));

  

}, function(err) {

// END

// IMPORTANT: close the connection

db.detach();

});

});

TRANSACTIONS

Transaction types:

  • Firebird.ISOLATION_READ_UNCOMMITTED

  • Firebird.ISOLATION_READ_COMMITED

  • Firebird.ISOLATION_REPEATABLE_READ

  • Firebird.ISOLATION_SERIALIZABLE

  • Firebird.ISOLATION_READ_COMMITED_READ_ONLY

Firebird.attach(options, function(err, db) {

  

if (err)

throw  err;

  

// db = DATABASE

db.transaction(Firebird.ISOLATION_READ_COMMITED, function(err, transaction) {

transaction.query('INSERT INTO users VALUE(?,?)', [1, 'Janko'], function(err, result) {

  

if (err) {

transaction.rollback();

return;

}

  

transaction.commit(function(err) {

if (err)

transaction.rollback();

else

db.detach();

});

});

});

});

EVENTS

Firebird.attach(options, function(err, db) {

  

if (err)

throw  err;

  

db.on('row', function(row, index, isObject) {

// index === Number

// isObject === is row object or array?

});

  

db.on('result', function(result) {

// result === Array

});

  

db.on('attach', function() {

  

});

  

db.on('detach', function(isPoolConnection) {

// isPoolConnection == Boolean

});

  

db.on('reconnect', function() {

  

});

  

db.on('error', function(err) {

  

});

  

db.on('transaction', function(isolation) {

// isolation === Number

});

  

db.on('commit', function() {

  

});

  

db.on('rollback', function() {

  

});

  

db.detach();

});

Escaping query values

var  sql1 = 'SELECT * FROM TBL_USER WHERE ID>' + Firebird.escape(1);

var  sql2 = 'SELECT * FROM TBL_USER WHERE NAME=' + Firebird.escape('Pe\'er');

var  sql3 = 'SELECT * FROM TBL_USER WHERE CREATED<=' + Firebird.escape(new  Date());

var  sql4 = 'SELECT * FROM TBL_USER WHERE NEWSLETTER=' + Firebird.escape(true);

  

// or db.escape()

  

console.log(sql1);

console.log(sql2);

console.log(sql3);

console.log(sql4);

Service Manager functions

  • backup

  • restore

  • fixproperties

  • serverinfo

  • database validation

  • commit transaction

  • rollback transaction

  • recover transaction

  • database stats

  • users infos

  • user actions (add modify remove)

  • get firebird file log

  • tracing

// each row : fctname : [params], typeofreturn

var  fbsvc = {

"backup" : { [ "options"], "stream" },

"nbackup" : { [ "options"], "stream" },

"restore" : { [ "options"], "stream" },

"nrestore" : { [ "options"], "stream" },

"setDialect": { [ "database","dialect"], "stream" },

"setSweepinterval": { [ "database","sweepinterval"], "stream" },

"setCachebuffer" : { [ "database","nbpagebuffers"], "stream" },

"BringOnline" : { [ "database"], "stream" },

"Shutdown" : { [ "database","shutdown","shutdowndelay","shutdownmode"], "stream" },

"setShadow" : { [ "database","activateshadow"], "stream" },

"setForcewrite" : { [ "database","forcewrite"], "stream" },

"setReservespace" : { [ "database","reservespace"], "stream" },

"setReadonlyMode" : { [ "database"], "stream" },

"setReadwriteMode" : { [ "database"], "stream" },

"validate" : { [ "options"], "stream" },

"commit" : { [ "database", "transactid"], "stream" },

"rollback" : { [ "database", "transactid"], "stream" },

"recover" : { [ "database", "transactid"], "stream" },

"getStats" : { [ "options"], "stream" },

"getLog" : { [ "options"], "stream" },

"getUsers" : { [ "username"], "object" },

"addUser" : { [ "username", "password", "options"], "stream" },

"editUser" : { [ "username", "options"], "stream" },

"removeUser" : { [ "username","rolename"], "stream" },

"getFbserverInfos" : { [ "options", "options"], "object" },

"startTrace" : { [ "options"], "stream" },

"suspendTrace" : { [ "options"], "stream" },

"resumeTrace" : { [ "options"], "stream" },

"stopTrace" : { [ "options"], "stream" },

"getTraceList" : { [ "options"], "stream" },

"hasActionRunning" : { [ "options"], "object"}

}

  

Backup Service example

  

Firebird.attach(options, function(err, svc) {

if (err)

return;

svc.backup(

{

database:'/DB/MYDB.FDB',

files: [

{

filename:'/DB/MYDB.FBK',

sizefile:'0'

}

]

},

function(err, data) {

console.log(data);

});

getLog and getFbserverInfos Service examples with use of stream and object return

fb.attach(_connection, function(err, svc) {

if (err)

return;

// all function that return a stream take two optional parameter

// optread => byline or buffer byline use isc_info_svc_line and buffer use isc_info_svc_to_eof

// buffersize => is the buffer for service manager it can't exceed 8ko (i'm not sure)

  

svc.getLog({optread:'buffer', buffersize:2048}, function (err, data) {

// data is a readablestream that contain the firebird.log file

console.log(err);

data.on('data', function (data) {

console.log(data.toString());

});

data.on('end', function() {

console.log('finish');

});

});

  

// an other exemple to use function that return object

svc.getFbserverInfos(

{

"dbinfo" : true,

"fbconfig" : true,

"svcversion" : true,

"fbversion" : true,

"fbimplementation" : true,

"fbcapatibilities" : true,

"pathsecuritydb" : true,

"fbenv" : true,

"fbenvlock" : true,

"fbenvmsg" : true

}, {}, function (err, data) {

console.log(err);

console.log(data);

});

});

  

Charset for database connection is always UTF-8

node-firebird doesn't let you chose the charset connection, it will always use UTF8.

Node is unicode, no matter if your database is using another charset to store string or blob, Firebird will transliterate automatically.

This is why you should use Firebird 2.5 server at least.

Firebird 3.0 Support

Firebird new wire protocol is not supported yet so

for Firebird 3.0 you need to add the following in firebird.conf

AuthServer = Legacy_Auth

WireCrypt = Disabled