Monday, November 2, 2009

Testing jQuery with env.js + RhinoUnit

The previous (Web application) project I was on started adopting jQuery to implement more client-side browser behaviour. We were already using RhinoUnit for testing some generic Javascript logic, but jQuery required access to the browser DOM. Hence, env.js was used to provided a DOM to RhinoUnit. This combination would be very quick due to RhinoUnit running headlessly and not relying on a browser, which was perfect for TDD. Getting these three pieces to work together properly took some work, as described below.

  • jQuery 1.3.2
  • env-js.1.0.rc7, using env.rhino.js
  • RhinoUnit 1.2.1

Replaced self
As mentioned in Adam Scott's blog, references to self in RhinoUnit needed to be replaced. So at the top of the rhinoUnitAnt.js, I added this:
var rhinoUnitSelf = self;
All references to self below this were replaced with references to rhinoUnitSelf.

Replaced print
Adam Scott's blog also mentioned the need to replace calls to print with self.log function. Specifically, reimplemented $env.log in env.rhino.js like so:

$env.log = function(msg, level){
rhinoUnitSelf.log(' '+ (level?level:'LOG') + ':\t['+ new Date()+"] {ENVJS} "+msg);

Implemented load
For some strange reason, env.js relies on a load function that could not be found. The solution was just to implement it using RhinoUnit's loadFile. The following was added to the top of env.rhino.js:

load = function(x) {

Ignored global variables
jQuery and our unit tests created a lot of global variables that RhinoUnit didn't like. Instead of configuring RhinoUnit to ignore a massive list of global variables, I decided to simply remove the code that checked for this. Specifically, reimplemented ignoreGlobalVariableName in rhinoUnitAnt.js as such:

function ignoreGlobalVariableName(name) {
return true;

Cleanup after tests
At this stage, our unit tests could run individually, but they failed when executed together (consecutively, from Ant). This was because the global variables that were created during the first test were not cleaned up and they corrupted the environment for all subsequent tests. In order to fix this, I modified the end of the runTest(file) function in rhinoUnitAnt.js so that global variables created in one testcase (test file) were cleaned up prior to running the next testcase.

/***** ADDED to remember global variables that existed prior to each test file *****/
var origGlobalVars = {};
var varName;
for (varName in this) {
origGlobalVars[varName] = true;


var testCount = 0;
var failingTests = 0;
var erroringTests = 0;

executeTestCases(test, test.setUp, test.tearDown);

if (testCount === 0) {
erroringTests += 1;
testfailed = true;
failingTestMessage(file, "No tests defined");

rhinoUnitSelf.log("Tests run: " + testCount + ", Failures: " + failingTests + ", Errors: " + erroringTests);

/****** ADDED to remove only those global variables that have been
added by the test file, but not those that existed before. ******/
for (varName in this) {
if (origGlobalVars[varName] != true) {
delete this[varName];

After these changes, our jQuery unit tests ran perfectly, allowing us to do TDD for our jQuery code.

Thursday, March 26, 2009

Cacti : Graphing remote service WITHOUT using SNMP

What is Cacti

Cacti is a popular Web-based tool for generating graphs of various data/statistics from remote servers. It provides default "templates" for generating graphs of CPU load, processes, memory, etc out-of-the-box, mainly retrieving these data from remote machines via SNMP (Simple Network Management Protocol). Cacti can be set up to graph custom data, again using SNMP by default. In fact, most of the instructions available online assume that Cacti talks SNMP to remote machines.

This blog is based on Cacti version 0.8.7d, running on a Linux machine.

The problem with SNMP

However, I find SNMP to be very complicated to set up for anything more complex than a single value data. Personally, I find SNMP to be overkill and far too difficult to manage, especially when it comes to setting up a custom SNMP table to group data in a way that can be traversed by Cacti.

Alternative to SNMP

This blog shows how Cacti can be used to retrieve data, via HTTP, from a URL that points to a "Web service" (not the heavy SOAP kind) that returns data in a simple text format. This can be implemented using JSP, PHP, a simple Java servlet or just any Web technology. All it needs to do is to return in its response the data in the following textual format:

fieldName1:fieldValue1 fieldName2:fieldValue2 fieldName3:fieldValue3 ...


Lets assume I'm hosting a Java Web application, and I want to graph the following:
- The number of requests that generated successful responses.
- The number of requests that generated errors.

Two things are required on the Java application side:
- Some sort of filter/interceptor to capture these two types of request.
- A servlet to count the number of these requests and return a response in the desired format. This servlet will reset its counters after sending each response, so that each time it returns the count since the last response. The response content type is set to text/plain and the content looks something like:

successCount:1623 errorCount:23

This servlet is deployed on the same Web container as the main Web application, and mapped to the URL:

Cacti Configuration

Poller Settings

Firstly, tell Cacti to simply use Ping to detect hosts for polling. Click on Settings on the left bar, then click on the Poller tab and change Downed Host Detection to just Ping, as shown below:


After that, set up a device to point to the server that is hosting the Web application. Assume it is reachable by name remoteserver. Create a device of type None as shown below, meaning Cacti will use ping to detect the server.

Note: This version of Cacti has a bug (at least on my Firefox browser) where it tries to validate that the SNMP Version 3 passwords match even when SNMP is set to Not In Use. To work around this, first select SNMP Version 3, blank out both passwords, then select Not In Use.

Data Input Method

A Data Input Method is set up to tell Cacti to retrieve the data from the servlet (using wget), and map the field names in the response text to fields that Cacti understands. Refer to screenshot below:

Name this Data Input Method as RemoteServerInputMethod. Select Script/Command as the Input Type, and type the following in the Input String :
wget --quiet --no-cache -O - http://<hostname>:<data_port>/myapp/statistics

Create hostname and data_port input fields, that are used to pass in values to the <hostname> and <data_port> parameters in the script command.

Create successCount and errorCount output fields, that are mapped to the field names in the response text. Make sure these output field names exactly match those in the servlet response text.

Data Template

The Data Input Method is used to create a Data Template, as shown below:

Name this Data Template as RemoteServerStatisticsDataTemplate. Name the Data Source as RemoteServerStatisticsDataSource, this will automatically become the data source name (see later).

Create a Data Source Item to map to each of the data fields, i.e. successCount and errorCount. Most of the settings here can stick to the defaults. The important part is to make sure that the Output Field selectors are mapped to the correct fields.

Its better to tick the checkbox under the Port on server Custom Data to tell Cacti to assign the port from the data source, so that we don't have to hardcode 8080 in the Data Template.

Data Source

Create a Data Source that connects the Data Template with the Host Device that were configured earlier. Select appropriate options for these two drop-down selectors. Do not change the Data Source Path. Enter 8080 as the Port on server.

This screen creates a Data Source that automatically gets the name as specified in the Data Template screen before.

Graph Template

Set up a Graph Template to tell Cacti how to plot the data in the graph. Name it RemoteServerStatisticsGraphTemplate, and give the graph a descriptive Title. All other fields below the Title can be left in their default values. Create two Graph Template Items as shown in the screenshot below.

Note that creating a Graph Template Item will automatically create a corresponding Graph Item Input.

The screenshot below shows the screen for creating a Graph Template Item. Simply choose the matching Data Source for the field, and enter a descriptive Text Format.

Graph Management

Enter Graph Management to create an association between Graph Template, Host Device and the Data Sources. The screenshot below should be self-explanatory.

Graph Tree

Finally, enter Graph Tree to place the graph in the hierarchical tree. This is quite straightforward. Create a Tree Item of type Host, and point it to the Host.

The new Tree Item will appear like so in the Graph Tree:


Finally, its all done. Just click on the Graph tab, drill down the tree and see the graph of successful and error request counts.

Thursday, February 26, 2009

SQL*Plus connection to remote Oracle database

SQL*Plus is a very handy command line tool when you want to quickly access an Oracle database or execute SQL statements from scripts, e.g. BASH scripts. However, getting it to connect to a remote Oracle database server in a script proved to be quite a challenge. This blog documents how to write the connection string in various cases.

Local database

Connecting to a local database is trivial, just use:

$ sqlplus dbUser/dbPassword@dbSid

Remote database from command line

Here's the nasty syntax for connecting to a remote database using its SID:

$ sqlplus dbUser/dbPassword@'(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=remoteServer)(PORT=1521)))(CONNECT_DATA=(SID=dbSid)))'

You can also connect using a SERVICE_NAME instead of SID.
Note that the single-quotes are needed to preserve the brackets.

Remote database from a BASH script

I needed to write a script that called SQL*Plus several times. In order to make it maintainable, the username, password and the part after the @ were specified as environment variables. Unfortunately, there did not seem to be any obvious way of setting the value of the environment variable so that the single-quotes were properly sent to the sqlplus command at run time. I had to explicitly escape each bracket with a backslash, like so:


Because this script was part of a Java project, I subsequently moved this value to a Java properties file. This means every backslash had to be escaped with another backslash to prevent Java from interpreting it. The entry in the Java properties file:

db.connection = \\(DESCRIPTION=\\(ADDRESS_LIST=\\(ADDRESS=\\(PROTOCOL=TCP\\)\\(HOST=remoteServer\\)\\(PORT=1521\\)\\)\\)\\(CONNECT_DATA=\\(SID=dbSid\\)\\)\\)

(I know I could have just used JDBC, but there was a good reason why I had to use SQL*Plus for this Java project.)

Define a local service name to a remote server

An alternative to using the big ugly connection string is to define a local service name in the local tnsnames.ora that references the remote instance. Add the following:

(ADDRESS = (PROTOCOL= TCP)(Host= remoteServer)(Port= 1521))
(CONNECT_DATA = (SID = dbSid))



Connecting to the remote database is simply:

$ sqlplus dbUser/dbPassword@DB_LOCAL_NAME

Of course, this solution has a few drawbacks (enough to dissuade me from using it).
- Need write permission to the tnsnames.ora.
- The application relies of custom entries in tnsnames.ora, which must be maintained together with the application code.
- Every environment (e.g. development, staging, UAT, production) in which the application is deployed to will need to have its tnsnames.ora modified and maintained.

Friday, January 9, 2009

Date sensitive integration testing of database code with Oracle

  • Commands starting with $ are to be entered in a shell, e.g. BASH.

  • Commands starting with SQL> are to be entered in a database client, e.g. SQLPlus or JDBC code


Lately, I've been working with a lot of database code, mostly packaged as stored procedures, running on Oracle 10g XE. These stored procedures are tested using a set of automated integration tests written in Java (JUnit + DBUnit). Everything was fine until one day, I needed to implement a logic that filters out data based on whether a date field stores a date within a certain period of time. The SQL for this was quite trivial, for example:

SELECT ... WHERE table1.the_date_field > ADD_MONTHS(SYSDATE, -6)

This example would filter out all results with the_date_field storing a date older than 6 months ago.

The Problem

The problem arose when I needed to write automated tests for this. The tests loaded up a set of hardcoded test data fixture (using DBUnit). The test data would have hardcoded date values. Obviously, these tests would give different results over time because SYSDATE would return the current system time by default.

The Solution(s)

One possible solution would be to dynamically inject the dates into the test data when the tests were executed. However, this would require a lot of work because of the quantity and spread of the dates. I would need to convert existing hardcoded dates into offsets from SYSDATE, then write some code to perform the injection at runtime.

Given this, I chose the reverse approach :- make SYSDATE return a fixed value.

Stopping time

Luckily, Oracle provided a way to let me do just this.
Firstly, I had to login to the database as a SYSDBA and grant my Oracle user the ALTER SYSTEM system privilege.
Now, I could invoke the following SQL in the set-up code of the JUnit tests:

SQL> alter system set fixed_date = '01-APR-2005'

This would make SYSDATE always return April 1st, 2005. All the test data could now be measured relative to this date, and the tests would not break no matter when they were run.

The tear-down code of the JUnit tests would invoke:

SQL> alter system set fixed_date = 'none'

to revert SYSDATE back to its default behaviour.

PFile and SPFile

However, when I tried to restart Oracle server, it failed to start up. After a few hours, I found out why. Oracle by default stored parameter settings in a binary file format called SPFile. Any changes made with ALTER SYSTEM were always persisted to this file, so that when Oracle was restarted, it tried to load them again. For some reason (a bug IMHO), Oracle could not load the fixed_date value of 'none', it simply died.

The workaround was to change to a different file format called PFile. PFile is a normal text file that could be editted, and unlike SPFile, Oracle would not persist any parameter changes to it. In my opinion, PFile is a better choice for a development or automated testing database, where changes made during tests should not be persisted or interfere each other. To convert from SPFile to PFile, first login as a SYSDBA:

$ $ORACLE_HOME/bin/sqlplus sys as sysdba

then execute:

SQL> create pfile from spfile;

Then shut down Oracle server, and remove the SPFile. The SPFile name has the following format: spfileSID.ora, where SID is the database SID. I was developing on a Ubuntu machine and my SID was XE. Hence, I ran this:

$ rm $ORACLE_HOME/dbs/spfileXE.ora

The PFile name has the format initSID.ora, so it was initXE.ora on my machine. Edit this file with a text editor and delete the line:


Finally, restart Oracle.


I found that when SYSDATE was fixed to a hardcoded value, jobs could not be scheduled using DBMS_JOB functions. DBMS_JOB.SUBMIT did not seem to work at all. Therefore, I can't yet think of a way of both freezing time and testing any code that rely on scheduled jobs.