How to Access Databases from Squish Test Scripts

Squish test scripts can access databases where the underlying scripting language provides a suitable library. In the case of JavaScript, which has no such library, Squish provides one—see SQL.

You can use a database to provide input or verification data to the test script, or you can log test results directly to the database. We have examples of both kinds of usage in the following sections.

Python-specific

The binary releases of Squish include a stripped down version of Python which does not include some standard libraries such as ssl, sqlite3, pip, mysql, and odbc. To access databases from Squish tests, you must use Squish with a full install of Python. You can replace the version that is used by an existing install, or compile Squish from source, and specify --with-python=/path/to/full/python when you run configure.

Incidentally, PyPI (Python Package Index) provides many different database bindings packages, and pip can be used to install them, so you are not limited to standard libraries when using Python.

For examples, see Accessing Databases via External Tools on our Knowledge Base.

Perl-specific

See Accessing Databases from Perl Test Scripts in our Knowledge Base.

Tcl-specific

Linux users should be able to obtain the bindings via their package management tools—the package name should be tclsqlite or similar—providing that you are using the system's Tcl and not one supplied with Squish. Windows users will need to download the bindings from SQLite. Click the Download link and get the binary tclsqlite-version.zip package. macOS users might have to build the package from source—like the Windows binary package it is available from SQLite after clicking the Download link.

How to Compare Application Data with Database Data

Sometimes it is convenient to compare application data with data in a database. Some scripting languages include modules for database access in their standard libraries. This isn't the case for JavaScript, so Squish provides the SQL Object which can be used to interact with databases from JavaScript test scripts.

In this subsection we will look at how to read data from a table widget and for each row, verify that each cell has the same data as the corresponding SQL database's row's field. In the examples we will use Java AWT/Swing's JTable as the data-holding widget, but of course, we could use exactly the same approach using a Java SWT Table or a Qt QTableWidget, or any other supported toolkit's table.

Because Squish ships a database library with JavaScript, we will show examples using that. For other script languages, you can choose the database library and accomplish the same thing using its API, but each API can be quite different.

The structure of our main function is very similar to one we used earlier in the CsvTable example where we compared the contents of a JTable with the contents of the .csv file from which the table was populated. Here though, instead of a custom compareTableWithDataFile function, we have a compareTableWithDatabase function. (See How to Test JTable and Use External Data Files (Java—AWT/Swing) How to Test the Table Widget and Use External Data Files (Java/SWT) How to Test Table Widgets and Use External Data Files.)

function main()
{
    startApplication('"' + OS.getenv("SQUISH_PREFIX") + '/examples/java/csvtable/CsvTableSwing.jar"');
    source(findFile("scripts", "common.js"));
    var filename = "before.csv";
    doFileOpen(filename);
    var jtable = waitForObject(
        "{type='javax.swing.JTable' visible='true'}");
    compareTableWithDatabase(jtable);
}

The main function begins by loading some common convenience functions, including a doOpenFile function that navigates the AUT's menu system to open a file with the given name. Once the file is loaded the JTable is populated with the file's contents and we then call the custom compareTableWithDatabase function to see if what we've loaded from the .csv file matches the data in a SQLite 3 database file.

function compareTableWithDatabase(jtable)
{
    var db3file = findFile("testdata", "before.db3");
    var db;
    try {
        var tableModel = jtable.getModel();
        db = SQL.connect({Driver: "SQLite", Host: "localhost",
                          Database: db3file, UserName: "", Password: ""});
        var result = db.query("SELECT id, pollutant, type, standard, " +
            "averaging_time, regulatory_citation FROM csv ORDER BY id");
        while (result.isValid) {
            var row = result.value("id") - 1;
            for (var column = 0; column < 5; ++column)
                test.compare(tableModel.getValueAt(row, column)
                    .toString(), result.value(column + 1));
            result.toNext();
        }
    }
    finally {
        if (db)
            db.close();
    }
}

In JavaScript using Squish's SQL Object, we can execute queries on the connection object itself. In fact, the JavaScript API has two kinds of query function we can use, the SQLResult sqlConnection.query(sql) function for executing SELECT statements, and the Number sqlConnection.execute(sql) function for all other kinds of SQL statements (e.g., DELETE, INSERT, UPDATE).

In this particular example, the SQL database table has a field that isn't present in the .csv file—id—which actually corresponds to the record's row (but using 1-based indexing). Once we have the connection, we get a reference to the JTable's underlying model—naturally, this is different if we use a different toolkit, but whether we access a table widget's cells directly or via a model, we still get access to each cell's data. Then we execute the SELECT query. The query returns a SQLResult Object, and this automatically navigates to the first record in the result set (assuming that there were any results). This gives us access to the first record in the results set.

The JavaScript API's SQLResult Object's isValid property is true if we have navigated to a valid record. The String sqlResult.value(fieldNumber|fieldName) method can accept either a field index (in this case, 0 for the id field, 1 for the pollutant field, and so on), or a field name. We begin by retrieving the record's id using the field name, and deducting 1 to account for the fact that the JTable uses 0-based rows and the database uses 1-based IDs that correspond to rows. Then we iterate over every column, retrieving the JTable's text for the given row and column and comparing it with the database record with the corresponding row (ID) and column. (We have to add 1 to the database column because the database has an extra column at the beginning storing the IDs.) Once all the table's row's cells have been compared with the database's record's fields, we attempt to navigate to the next record in the database using the sqlResult.toNext() method.

And at the end, we close the connection to the database, providing we made a successful connection in the first place. Although it doesn't matter much for SQLite, closing the connection to other databases is usually very important, so we have used a try ... finally construct to ensure that no matter what happens after the connection is made, the connection is safely closed in the end. (Of course, Squish would close the connection for us anyway, but we prefer to take a best-practice approach to our test code.)

How to Log Test Results Directly into a Database

Squish can output its test results in plain text or XML, so it is very easy to parse the results to analyze them and to produce reports. (See, for example, How to Do Automated Batch Testing and Processing Test Results.) However, if we prefer, we can log the test results directly from our test scripts ourselves. One way to do this is to use the scripting language's logging facilities (if it has any)—for example, using Python's logging module. Another way is to log the results directly into a database—this is the approach we will look at in this subsection.

For our example we will use a simple SQLite 3 database stored in the test suite's shared test data in file logfile.db3. The database has three fields, id (an auto-incrementing integer), result, and message, both text fields. Our test code assumes that the database exists (and so, is initially empty).

We will start by looking at a test case's main function and where calls to Squish's test.log(message) function have been replaced with calls to a custom DB class instances's log method, and similarly calls to Squish's Boolean test.compare(value1, value2) and Boolean test.verify(condition) functions have been replaced with calls to our custom db object's compare and verify methods. (Note that for Tcl we don't create a custom class or object, but just use plain functions.)

function main()
{
    startApplication('"' + OS.getenv("SQUISH_PREFIX") + '/examples/java/itemviews/ItemViewsSwing.jar"');
    var db;
    try {
        db = new DB();
        var tableWidgetName = names.itemViewsJavaxSwingJTable;
        var tableWidget = waitForObject(tableWidgetName);
        var model = tableWidget.getModel();
        for (var row = 0; row < model.getRowCount(); ++row) {
            for (var column = 0; column < model.getColumnCount(); ++column) {
                var item = model.getValueAt(row, column);
                var selected = "";
                if (tableWidget.isCellSelected(row, column)) {
                    selected = " +selected";
                }
                var message = "(" + String(row) + ", " + String(column) + ") '" +
                         item.toString() + "'" + selected;
                db.log(message);
            }
            var expected = new Boolean((row == 14 || row == 24) ? true : false);
            db.compare(model.getValueAt(row, 0).toString(), expected.toString());
        }
        db.verify(model.getRowCount() == 25);
    }
    finally {
        if (db)
            db.close();
    }
}

The main function is very similar to one we saw in the itemviews example (see How to Test JList, JTable, and JTree widgets (Java—AWT/Swing)). The function iterates over every row in a table widget and over every cell in every row. For each cell we log its contents with a string of the form "(row, column) text", optionally appending "+selected" to the text for cells that are selected. The table's first row consists of checkboxes—the text for these comes out as "true" or "false"—and we check each one to make sure that it is unchecked (or in the case of rows 14 and 24, checked). And at the end we verify that the table has exactly 25 rows.

In terms of the DB class, we begin by creating an instance—and as we will see in a moment, the database connection is made in the constructor. Then we call methods on the db object in place of the Squish test methods we would normally use.

Because JavaScript does not have destructors, we close the db object in the finally clause to ensure the connection is always closed.

We are now ready to review the DB class and its methods.

function DB()
{
    var logfile = findFile("testdata", "logfile.db3");
    this.connection = SQL.connect({Driver: "SQLite", Host: "localhost",
        Database: logfile, UserName: "", Password: ""});
}

DB.prototype.log = function(message)
{
    message = message.replace(RegExp("'", "g"), "");
    this.connection.execute("INSERT INTO log (result, message) " +
        "VALUES ('LOG', '" + message + "')");
}

DB.prototype.compare = function(first, second)
{
    var result = first == second ? "PASS" : "FAIL";
    this.connection.execute("INSERT INTO log (result, message) " +
        "VALUES ('" + result + "', 'Comparison')");
}

DB.prototype.verify = function(condition)
{
    var result = condition ? "PASS" : "FAIL";
    this.connection.execute("INSERT INTO log (result, message) " +
        "VALUES ('" + result + "', 'Verification')");
}

DB.prototype.close = function()
{
    this.connection.close();
}

The DB function is the constructor and we use it to create the database connection. To provide the object returned by calling new DB() with methods, we create anonymous functions which we immediately assign to the DB class's prototype, using the names by which we want to call them.

In the case of the DB.log method, we remove any single quotes from the message since we create the SQL to execute purely as a string, and single quotes would confuse things. (An alternative would be to escape them.)

The DB class assumes that the database already exists and contains a table called log that has at least two text fields, result and message. In fact, for this example the SQLite SQL we used to create the table was: CREATE TABLE log (id INTEGER PRIMARY KEY, result TEXT, message TEXT). The id field is autoincrementing which is why we don't need to explicitly insert values for it.

Clearly the DB class is very simple, but it shows the fundamentals of how we could create a database-savvy object that we could use to store whatever test data and results we liked, ready for post-processing or reporting.