Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

CUBRID Node.js 2.0 Tutorial – What’s new? (Part II)


The 1st part of this tutorial was focused on the new queries queuing feature. In this 2nd part of the tutorial we will show you some other new features implemented in the 2.0 release:

  • Support for database parameters
  • LOB objects handling support
  • Extended database schema support

And others…

Database parameters

After connecting to a database, a user can specify some “global” session parameters that will control the behavior of SQL statements transactions’ isolation level execution, the auto-commit behavior and others.

The complete list of these CUBRID database parameters is defined in the Constants.js file:

exports.CCIDbParam = {
  CCI_PARAM_ISOLATION_LEVEL   : 1,
  CCI_PARAM_LOCK_TIMEOUT      : 2,
  CCI_PARAM_MAX_STRING_LENGTH : 3,
  CCI_PARAM_AUTO_COMMIT       : 4
};

For each parameter, the CUBRID communication protocol implements a dedicated support for GET and SET operations. Therefore, in order to manipulate them, also a dedicate functionality was needed in the Node.js driver and this is what we did in the 2.0 release.

Please note one exception - the MAX_STRING_LENGTH parameter cannot be set programmatically from code; it’s a broker parameter and the client can only query its current value.

We have provided support for:

  • Getting a parameter value, using the getDatabaseParameter (…) method
  • Setting a parameter value, using the setDatabaseParameter (…) method

Let’s see some examples. First, let set the value of the ISOLATION_LEVEL parameter:

CUBRIDClient.connect(function (err) {
    CUBRIDClient.setDatabaseParameter(CAS.CCIDbParam.CCI_PARAM_ISOLATION_LEVEL,
      CAS.CUBRIDIsolationLevel.TRAN_REP_CLASS_COMMIT_INSTANCE, function (err) {
          CUBRIDClient.close(function (err) {
…

And let’s see how we can retrieve the value of the LOCK_TIMEOUT parameter for the current session:

CUBRIDClient.connect(function (err) {
  CUBRIDClient.getDatabaseParameter(CAS.CCIDbParam.CCI_PARAM_LOCK_TIMEOUT, function (err, value) {
    Helpers.logInfo('LOCK_TIMEOUT is: ' + value);
    CUBRIDClient.close(function (err) {
    });
  });
});

The output result is:

LOCK_TIMEOUT is: -1

Note: The same value can be obtained also from CUBRID Manager Client:

manager.png

If you need to change the default values for these parameters, it is highly recommended to do it immediately after connect (). One consequence is that you must use an explicit connect () statement in your application, and not the “implicit connect” driver feature (the “implicit connect” feature means that the driver can auto-connect when a query is first executed, without the need to issues an explicit connect() command).

LOB objects handling

In the previous driver version, the support for LOB (CLOB/BLOB) objects was limited to query only. Now, we have implemented the completed protocol required to update such data type columns. Please note that, as in the other CUBRID drivers, we have implemented dedicated driver query functions for handling LOB objects, as follows:

  • lobNew(…)
  • lobRead(…)
  • lobWrite(…)

Let’s see a simple example of inserting a LOB value (the code below is an “extract” from an ActionQueue series of method calls):

…
    function (cb) {
      CUBRIDClient.lobNew(CUBRIDClient.LOB_TYPE_BLOB, cb);
    },

    function (lobObject, cb) {
      Helpers.logInfo('Number of bytes to write: ' + 2 * CUBRIDClient._LOB_MAX_IO_LENGTH);
      var data = new Buffer(2 * CUBRIDClient._LOB_MAX_IO_LENGTH);
      for (var i = 0; i < 2 * CUBRIDClient._LOB_MAX_IO_LENGTH; i++) {
        datai = i;
      }
      CUBRIDClient.lobWrite(lobObject, 1, data, cb);
    },

    function (lobObject, written_length, cb) {
      CUBRIDClient.executeWithTypedParams('insert into test_blob values(1, ?)', lobObject, 'blob', cb);
    },
…

And this is how we retrieve a LOB value:

…
    function (cb) {
      CUBRIDClient.query('select * from test_lob', cb);
    },

    function (result, queryHandle, cb) {
      var arr = Result2Array.RowsArray(result);
      var lobObject = arr00;
      CUBRIDClient.lobRead(lobObject, 1, lobObject.lobLength, cb);
    },

    function (str, read_length, cb) {
…

(the str variable will contain the LOB data)

As you can see, it’s pretty simple to deal with LOB data types!

Database schema

If you remember, in the previous driver versions, we provided integrated support for querying two database schemas:

  • TABLEs
  • VIEWs

But CUBRID provides many more and some of the information cannot be obtained by querying the system objects – see, for example, the foreign keys.

Therefore, one of the things we did in this release was to improve the support for database schema, and so we add support for:

  • ATTRIBUTEs (Table columns)
  • CONSTRAINTs
  • PRIMARY KEYs
  • IMPORTED KEYs (Foreign keys)
  • EXPORTED KEYs
  • TABLE PRIVILEGEs

The method which must be used to retrieve a database schema is:

CUBRIDClient.getSchema(…)

Depending on schema, some method parameters might be mandatory!

For example, if you query the CONSTRAINTS schema, you need to provide the table name.

See the driver documentation for more details.

Let’s see an example for retrieving the list of foreign keys for a table (we will use the game table in the demodb database):

athlete.png

 The code is pretty simple:

ActionQueue.enqueue(
  [
    function (callback) {
      CUBRIDClient.connect(callback);
    },

    function (callback) {
      Helpers.logInfo('Getting foreign keys for the game table...');
      CUBRIDClient.getSchema(CUBRIDClient.SCHEMA_IMPORTED_KEYS, 'game', callback);
    },

    function (result, callback) {
      for (var i = 0; i < result.length; i++) {
        Helpers.logInfo(result[!i]);
      }
      }
      CUBRIDClient.close(callback);
    }
  ],
…

And the output is: 

{ FkName: 'fk_game_event_code',
  PkName: 'pk_event_code',
  FkTableName: 'game',
  PkTableName: 'event',
  FkColumnName: 'event_code',
  PkColumnName: 'code',
  UpdateAction: 1,
  DeleteAction: 1 }
{ FkName: 'fk_game_athlete_code',
  PkName: 'pk_athlete_code',
  FkTableName: 'game',
  PkTableName: 'athlete',
  FkColumnName: 'athlete_code',
  PkColumnName: 'code',
  UpdateAction: 1,
  DeleteAction: 1 }

Please note that some of the database schemas can be obtained very easily by querying the appropriate system table – you don’t need the getSchema(…) method. For such schemas, we will not provide a dedicated functionality in the driver.

One simple example is the USERs schema, which can be obtained like this:

ActionQueue.enqueue(
  [
    function (callback) {
      CUBRIDClient.connect(callback);
    },

    function (callback) {
      CUBRIDClient.query('select [name] from db_user', callback);
    },

    function (result, queryHandle, callback) {
      Helpers.logInfo(Result2Array.ColumnNamesArray(result).toString());
      var arr = Result2Array.RowsArray(result);
      for (var i = 0; i < arr.length; i++) {
        Helpers.logInfo(arr[i].toString());
      }

      callback();
    },
…

When running this script on the demodb database, we will get the list of users defined in the demodb:

name
DBA
PUBLIC

Other features

Connection timeout

One of the requests we got for the 2.0 driver release was to implement a connection timeout feature. Simply said - wait for the connection to the database to complete within the specified number of seconds and eventually throw an error if the timeout occurs.

Obviously, the key thing here was to set the connection timeout at the Node.js socket connection layer level (and not on the consumer level):

self._socket = Net.createConnection(self.initialBrokerPort, self.brokerServer);
self._socket.setNoDelay(true);
self._socket.setTimeout(this._CONNECTION_TIMEOUT);

Now let’s see this in action:

var client = new CUBRIDConnection('10.255.255.1', 33000, 'public', '', 'demodb');

Helpers.logInfo(module.filename.toString() + ' started...');

client.setConnectionTimeout(2000);
client.connect(function (err) {
  if (err) {
    assert(err.message === ErrorMessages.ERROR_CONNECTION_TIMEOUT);
    Helpers.logInfo('Connection timeout');
…

As you see, the timeout specified is 2.000, which is 2 sec.

After the 2 seconds, the script will timeout:

Connection timeout
Process finished with exit code 0

Extended events information

Another thing we have enhanced in the 2.0 release is the events information provided to the user.

This is actually one of the key things we are constantly looking into improving in our driver – the events model – because we believe it provides many advantages over the “classical” callbacks programming model.

For example, the query() events now returns also the source SQL statement:

CUBRIDClient.connect();

CUBRIDClient.on(CUBRIDClient.EVENT_CONNECTED, function () {
  CUBRIDClient.addQuery(SQL_A, null);
});

CUBRIDClient.on(CUBRIDClient.EVENT_QUERY_DATA_AVAILABLE, function (result, queryHandle, sql) {

For more details about the events model, please take a look at the driver documentation.

Objects array

Remember that in the version 1.0, when you we were retrieving the data returned by a query, we were accessing the data using an array notation:

Helpers.logInfo(arr[j][0] + "," + arr[j][1] + "," + arr[j][2] + "," + arr[j][2]);

Now, you can also do this - use an object notation:

Helpers.logInfo(arr[j].code + "," + arr[j].name + "," + arr[j].continent + "," + arr[j].capital);

Even if it looks easier to understand, we do not recommend using the object-type notation, unless you have good reasons. This is because of one reason – the syntax errors cannot be detected at development time, but only at runtime!

What we usually recommend instead is to use named-indexes in the array notation, for example:

if (self._queriesQueue[idx][self._QUERY_INFO.CALLBACK] &&
    typeof(self._queriesQueue[idx][self._QUERY_INFO.CALLBACK]) === 'function') {

Under the hood…

In this section, we will look at some internal driver implementation details.

The queue processor

In order to have an “automatic” queue processor, which takes care of executing the statements the users added to the queue, we needed a way to implement a routine which could (auto-)execute in a recursive manner, but without blocking the events queue. And this without using a “timer”, because this is not available in Node.js.

Note: Just to clarify, when we say that the “standard timer” feature is not available in Node.js, we are referring to the Windows timer functionality, where you don’t need to auto-call the recurrent function by yourself again and again, but you simply setup the timer function handler only once and the system takes care automatically of running it without any other actions required from the application.

Here is the logical structure of this routine we have developed:

// Check if some query is still in execution - if YES, exit and retry
for (i = 0; i < self._queriesQueue.length; i++) {
  if (self._queriesQueue[i][self._QUERY_INFO.STATUS] === self._QUERY_STATUS.IN_EXECUTION) {
    // retry queue processing after a while
    setTimeout(function () {
      self._enableQueriesBackgroundProcessor();
    }, self._QUERIES_QUEUE_CHECK_INTERVAL);
    return;
  }
}

// Find the first query not started and execute it
for (i = 0; i < self._queriesQueue.length; i++) {
  if (self._queriesQueue[i][self._QUERY_INFO.STATUS] === self._QUERY_STATUS.NOT_STARTED) {
    ...
    break;
  }
}

// Re-start queries processor
setTimeout(function () {
  self._enableQueriesBackgroundProcessor();
}, self._QUERIES_QUEUE_CHECK_INTERVAL);

Tip: As you can see there is an internal parameter - _QUERIES_QUEUE_CHECK_INTERVAL - which controls after which interval the routine restart itself. The default value is 1.000 msec. If for some reason, you need a “faster” queue, you can lower down the values of this parameter, for example:

<your_CUBRIDConnection_client_instance_name>._QUERIES_QUEUE_CHECK_INTERVAL = 500;

Some backward compatibility notes

Maybe you have asked if the “old” query(…) statement is still available…

The short answer is: Yes, it is still there in the driver and not only as an empty “shell”, but complete - nothing was changed in its implementation.

We made this choice for a couple of reasons:

  • Not everybody will actually need the queries queue functionality
  • Executing a query using the ”old” query(…) function is slighter faster and uses lesser resources. The reason is obvious – you will be bypassing entirely the queue processor.

Tip: Before you start coding, think about if you need the queue processor and make the best decision for your applications. Remember, there are 3 main ways to use the driver:

  • Through the queries queue
  • Through the ActionQueue (or any similar implementation,  see Async for example)
  • Using simple “callback-chained“ queries

References and links

CUBRID Node.js driver home page

https://github.com/CUBRID/node-cubrid

https://github.com/organizations/CUBRID

Online driver documentation

https://github.com/CUBRID/node-cubrid/tree/master/documentation

Online Wiki

http://www.cubrid.org/wiki_apis/entry/cubrid-node-js-driver

Driver tutorials and examples

http://www.cubrid.org/wiki_apis/entry/cubrid-node-js-tutorials

We hope you enjoyed this tutorial!

Please let us know your feedback and suggestions, so we can improve! And do not forget, tryout the CUBRID Node.js 2.0 driver!

comments powered by Disqus
Page info
viewed 1349 times
translations en
Author
posted last year by
CUBRID
Contributors
updated last year by
View revisions
Share this article