waterdb

v1.0.6

Published

2 years ago

WaterDB by MonetDB

Downloads

0High
0Medium
0Low

xuantien1985

waterdb

MonetDB NodeJS connector version 1.*

[ [

This NodeJS module provides an easy and powerful way to use MonetDB inside your NodeJS programs.

For version 0.* users:

Example usage

var MDB = require('monetdb')();

var options = {
	host     : 'localhost', 
	port     : 50000, 
	dbname   : 'mydb', 
	user     : 'monetdb', 
	password : 'monetdb'
};

var conn = new MDB(options);
conn.connect();

conn.query('SELECT * FROM mytable').then(function(result) {
    // Do something with the result
});

conn.close();

Options

There are three flavors of setting options:

providing global options
providing local options
changing local options

Let's look at how we could use these flavors to setup connections for multiple users:

// We set the global option 'dbname' to 'db', for all connections
var MDB = require('monetdb')({dbname: 'db'});

// We can now create two connections for different users
// Both connections will use the globally set 'dbname' option
var conn1 = new MDB({user: 'foo', password: 'bar'});
var conn2 = new MDB({user: 'dumb', password: '0000'});

// Now let's change a local option of conn1 
// (which is only possible for non-connection specific options)
conn1.option("setMaxReconnects", 1);

In general, the order in which options are consider is:

local
global
default (if available)

The MonetDB NodeJS module recognizes many options. You do not need to know them all. There is only one option that does not have a default value, and that is the database name (dbname).

The available options can be subdivided into several categories:

Connection options

These options are used to make the actual connection to MonetDB. Note that these options can not be changed with the MonetDBConnection.option method. If you want to change these settings on an open connection, just construct a new connection.

Reconnection options

These options are used to determine how to reconnect whenever a connection fails.

Query options

These options influence the way queries are executed.

Logging

The MonetDB NodeJS module comes with a very extensive logging functionality. It distinguishes between many types of different messages, and allows you to fully customize the way the MonetDB NodeJS module deals with log messages when they occur. See the logging section for more information.

Testing

Options provided solely for testing.

MonetDBConnection

Getting a MonetDBConnection object is easy:

var MDB = require('monetdb')();

// MDB is a constructor function; let's construct a MonetDBConnection object, shall we?
var conn = new MDB({dbname: 'mydb'});

// conn now is a MonetDBConnection object

All of its exposed attributes and methods are listed here:

MonetDBConnection([options])

Constructor for a MonetDBConnection object, that takes an optional options object. For possible options, see the options section.

Throws an error when the provided options object is not ok.

.mapiConnection

Instance of MapiConnection. This attribute will be managed by the MonetDBConnection object, so in normal cases you should not use this. Only if you want to e.g. fire raw queries against the database, you might want to reference it directly. Just in case you are wondering, here is the documentation.

.connect()

This method links to MapiConnection.connect

Open the connection to the MonetDB server.

Returns a promise that resolves when the connection succeeded, and gets rejected with an error message otherwise. Note that you do not have to wait for the connection promise to be resolved before issueing queries, since incoming queries will be properly cached.

.open()

Alias for MonetDBConnection.connect.

.query(query, [params], [prettyResult])

Issue a query against the database. For a simple example, see here.

Example of a query with parameters:

var MDB = require('monetdb')();

var conn = new MDB({dbname: 'mydb'});
conn.connect();

conn.query(
    'SELECT * FROM mytable WHERE c < ? AND d > ?', 
    [40, 102]
).then(function(result) {
    // Do something with the result
});

Returns a promise that resolves with an object with the following properties:

.querystream(query)

Stream monetdb response.

Example:

var MDB = require('monetdb')();

var conn = new MDB({dbname: 'mydb'});
conn.connect();

function test_stream (conn, sql) {
    let result;
	return new Promise((resolve, reject) => {
		conn.querystream(sql)
			.on('error', (err) => {
				return reject(err);
			})
			.on('header', (header) => {
                "add header to result"
                
            })
			.on('data', (data) => {
				"add data to result"
			})
			.on('end', () => {
                "all rows being received"
                resolve(result);
			});
	});
}

.request(query, [params], [prettyResult])

Alias for MonetDBConnection.query.

.prepare(query, [prettyResult])

Prepares a query for repeated execution, and generates execution and release convenience functions.

Returns a promise that resolves with an object with the following properties:

| Property | Type | Description | | :--------------------- | :----------- | :-------------- | | prepare | object | The regular query result for the PREPARE statement, as is described under MonetDBConnection.query. | exec | function | A function that executes the prepared statement. As its first and only argument, it takes an array of values. It returns a promise equivalent to the promise returned by MonetDBConnection.query. | release | function | A parameterless function that you can call when you want to free the resources used by the prepared statement. After calling this function, calls to the exec function will fail. This function does not return anything. You cannot be notified of whether or not this worked.

Example:

var MDB = require('monetdb')();

var conn = new MDB({dbname: 'mydb'});
conn.connect();

conn.prepare('SELECT * FROM mytable WHERE c < ? AND d > ?').then(function(prepResult) {
    // execute query twice
    prepResult.exec([10, 5]).then(function(result) {
        // do something with the result
    });
    prepResult.exec([50, -20]).then(function(result) {
        // do something with the result
    });
    
    // we are done, release it (and do not wait for it, release method does not return a promise)
    prepResult.release();
});

.env()

Get the connection environment. Example:

var MDB = require('monetdb')();

var conn = new MDB({dbname: 'mydb'});
conn.connect();

conn.env().then(function(env) {
    console.log(env);
});

Will output:

{
	"gdk_dbpath": "/home/robin/dbfarm/test",
	"gdk_debug": "0",
	"gdk_vmtrim": "no",
    ..

.option(name, [value])

Get or set an option.

Throws an error if either the provided option is not found, or if the provided value is invalid.

If the second argument is omitted, the value of the option is returned.

.getState()

This method links to MapiConnection.getState

Get the current state of the connection. For normal usage you will never need to use this.

Returns one of the following state strings:

| State | Meaning | | :------------ | :------------ | | disconnected | There is currently no open connection, either because it has never been opened yet, or because a reconnect is going on. | connected | There is an open connection to the server, but authentication has not finished yet. | ready | There is an open connection to the server, and we have successfully authenticated. The connection is ready to accept queries. | destroyed | The connection is destroyed, either because it was explicitly destroyed by a call to MonetDBConnection.destroy, or because of a failure to keep the connection open.

Regardless of the return value of this method, you can safely issue queries to the connection, since they will be properly queued until the connection is ready.

.close()

This method links to MapiConnection.close

Finishes all the current queries in the queue, and then destroys the socket by calling MonetDBConnection.destroy. After closing a connection, it cannot be reopened. If reopening is desired, you should create a new MonetDBConnection object.

When queries are issued after calling close, they are still accepted and put into the queue, so a connection remains active until it becomes idle.

Note that when you have issued a query with parameters, this will under the hood be executed in two steps (one prepare step and one execution step). If the close method is called after firing a prepared statement, it might therefore fail because the socket can be destroyed after finishing the first step.

Returns a promise that resolves when all queries in the queue are done and the socket is destroyed.

.disconnect()

Alias for MonetDBConnection.close.

.destroy([msg])

This method links to MapiConnection.destroy

Fails all queries currently in the queue (including the one that is currently being executed), and destroys the socket.

| Argument | Type | Required | Description | | :------------ | :------------------ | :------------- | :-------------- | | msg | string | no | An error message that will be sent to all queries that are rejected. If none is given, a default error message is sent.

.getCallbackWrapper()

This method is added for backward compatability. For information on how to use it, see the updates page.

Pretty query results

By default, every row in a query result is represented by an array. However, if the pretty flag is set, the query result will instead be an array of objects, where every object has the column names as its properties. This makes using the result a lot more intuitive and fault-tolerant (e.g. if you hard code indices into row arrays, your code might start failing when you change the SQL query). The default value of the pretty flag can be overwritten by providing the global or local option 'prettyResult'. Even then, you can override this default value on a per query basis, by providing a boolean value as last argument to either the MonetDBConnection.query or the MonetDBConnection.prepare function. Example:

var MDB = require('monetdb')();

var conn = new MDB({dbname: 'mydb'});
conn.connect();

// we did not set the option 'prettyResult', hence we will pass true to our queries to have them return prettified results.
conn.query("SELECT a, b FROM yourtable").then(function(result) {
    console.log(result.data);
});

Outputs (depending on the values of columnns a and b and the number of resulting tuples):

[
    { a: 'val1', b: 'val2' },
    { a: 'val3', b: 'val4' }
]

Connection time zone

For every connection that is opened to the server, the timezone is automatically set to the current timezone of the system that opens the connection. You can change this behavior by passing a value for the option 'timezoneOffset'. The value for this option represents the number of minutes to deviate from GMT. This value is used by MonetDB to present timestamps with timezone. Example:

var MDB = require('monetdb')();

// Create two connections with different time zone

// One GMT+02:00
var conn1 = new MDB({timezoneOffset: 120, dbname: 'mydb'});

// And one GMT-03:30 (= -210 minutes)
var conn2 = new MDB({timezoneOffset: -210, dbname: 'mydb'});

conn1.connect();
conn2.connect();

conn1.query('SELECT NOW').then(function(result) {
    // result.data[0][0] will be something like '2015-11-02 08:11:31.000000+02:00'
});

conn2.query('SELECT NOW').then(function(result) {
    // result.data[0][0] will be something like '2015-11-02 02:45:56.000000-03:30'
});

Logging

As you can see from the options section, there are many logging options. By default, only warning messages are logged, and console.log is used for this. The following types of log events can occur:

Warnings
- Unrecognized options
- Connection failures
- Queries are issued before calling connect()
Debug messages
- State changes (see MapiConnection object for possible states)
- Read/write counts on opened socket
Resolved requests
Mapi messages

You can manipulate whether or not these events are logged or not, and which functions are used to do this. For all of these events, a default log function is specified in utils.js Every log function receives as its first argument the logger that it should use, where the logger simply takes a string and logs it somewhere. By default, the logger is console.log. You can change this in the options.

In case you want to overwrite the default log functions, here are the function signatures:

options.warningFn(logger, msg)

options.debugFn(logger, msg)

Same as options.warningFn

options.debugRequestFn(logger, request, error, result)

options.debugMapiFn(logger, type, msg)

Published

Vulnerabilities

Links

Maintainers

Keywords

Readme

MonetDB NodeJS connector version 1.*

Example usage

Options

Connection options

Reconnection options

Query options

Logging

Testing

MonetDBConnection

MonetDBConnection([options])

.mapiConnection

.connect()

.open()

.query(query, [params], [prettyResult])

.querystream(query)

.request(query, [params], [prettyResult])

.prepare(query, [prettyResult])

.env()

.option(name, [value])

.getState()

.close()

.disconnect()

.destroy([msg])

.getCallbackWrapper()

Pretty query results

Connection time zone

Logging

options.warningFn(logger, msg)

options.debugFn(logger, msg)

options.debugRequestFn(logger, request, error, result)

options.debugMapiFn(logger, type, msg)