5.18. How to Access Databases from Squish Test Scripts

5.18.1. How to Compare Application Data with Database Data
5.18.2. How to Log Test Results Directly into a Database

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

There are two main uses that can be made of database access, both of which are show in this section's two subsections. We can access a database to compare a database table's contents with data in the AUT, and we can log our test results directly to a database if that is more convenient than processing Squish's test log files.

[Note]Python-specific

The examples in this section use SQLite 3, bindings to which are provided by the pysqlite package which has been supplied as part of Python's standard library since Python 2.5.

By default Squish binary packages for all platforms use the Python 2.4 that is installed inside the Squish directory. On Unix-like systems, if Squish is built from a source package, Squish uses whatever Python 2.x it finds installed on the system. If you have Python 2.3 or 2.4 (the oldest versions that Squish supports), you will need to install the pysqlite package manually. The package is available from pysqlite and is available in source form for all systems and in binary form for Windows—make sure that you install it using the same Python as Squish uses (e.g., the one in the Squish directory, or the system Python on Unix-like systems for source installs).

Incidentally, PyPI (Python Package Index) provides many different database bindings packages, so you are not limited to SQLite when using Python.

[Note]Perl-specific

The examples in this section use SQLite 3, bindings to which are provided by the DBD::SQLite package which is available from CPAN.

Windows users can install this package by starting a Console session and invoking Perl at the command line with perl -MCPAN -e shell (this assumes that the machine is connected to the Internet). This will produce the CPAN prompt where you must install two packages. First type in install DBI, and then install DBD::SQLite. For Unix-like system users who are using the system's Perl installation (rather than a Perl supplied with Squish), use the package management tools to install the DBI and DBD::SQLite packages.

Make sure that the packages are installed using the same Perl as Squish uses (e.g., the one in the Squish directory). This may not work if you are using a Perl that is supplied with a Squish binary package; in such cases contact froglogic support.

[Note]Tcl-specific

The examples in this section use SQLite 3, bindings to which are provided by the SQLite developers.

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. Mac OS X users might have to build the package from source—like the Windows binary package it is available from SQLite after clicking the Download link.

5.18.1. 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. Unfortunately this isn't the case for JavaScript, so Squish provides the SQL Object (Section 6.16.7.1) 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.

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) (Section 5.4.6.1.3), How to Test the Table Widget and Use External Data Files (Java/SWT) (Section 5.4.6.2.3), and How to Test Table Widgets and Use External Data Files (Qt 4) (Section 5.2.6.4).)

Python

def main():
    startApplication("CsvTableSwing.jar")
    source(findFile("scripts", "common.py"))
    filename = "before.csv"
    doFileOpen(filename)
    jtable = waitForObject("{type='javax.swing.JTable' visible='true'}")
    compareTableWithDatabase(jtable)
JavaScript

function main()
{
    startApplication("CsvTableSwing.jar");
    source(findFile("scripts", "common.js"));
    var filename = "before.csv";
    doFileOpen(filename);
    var jtable = waitForObject(
        "{type='javax.swing.JTable' visible='true'}");
    compareTableWithDatabase(jtable);
}
Perl

sub main
{
    startApplication("CsvTableSwing.jar");
    source(findFile("scripts", "common.pl"));
    my $filename = "before.csv";
    doFileOpen($filename);
    my $jtable = waitForObject(
        "{type='javax.swing.JTable' visible='true'}");
    compareTableWithDatabase($jtable);
}
Tcl

proc main {} {
    startApplication "CsvTableSwing.jar"
    source [findFile "scripts" "common.tcl"]
    set filename "before.csv"
    doFileOpen $filename
    set 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.

Unfortunately, the database APIs vary quite a lot between the different scripting languages, so although the structure of the custom compareTableWithDatabase functions are all the same, the details are somewhat different. In view of this we will look at each language's implementation in is own separate subsubsection—each subsubsection is complete in itself, so you only need to read the one relevant to the scripting language that interests you.

5.18.1.1. Comparing a GUI Table with a Database Table in Python

Python

import sqlite3

def compareTableWithDatabase(jtable):
    db3file = findFile("testdata", "before.db3")
    db = cursor = None
    try:
        tableModel = jtable.getModel()
        db = sqlite3.connect(db3file)
        cursor = db.cursor()
        cursor.execute("SELECT id, pollutant, type, standard, "
            "averaging_time, regulatory_citation FROM csv ORDER BY id")
        for record in cursor:
            row = record[0] - 1
            for column in range(0, 5):
                test.compare(tableModel.getValueAt(row, column)
                             .toString(), record[column + 1])
    finally:
        if cursor is not None:
            cursor.close()
        if db is not None:
            db.close()

The first thing we must do—before writing any of our functions—is import the sqlite3 module that the pysqlite package provides.

To connect to a SQLite database we only need to supply a filename. The means of connection varies between scripting languages and libraries, as do their SQL APIs, but they are all the same in principle, even if the details of the syntax vary, although in most cases they require a username, password, hostname, and port, rather than a filename.

In Python we must obtain a connection, and then use the connection to obtain a database “cursor”. It is through this cursor that we execute queries. 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 and cursor, 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. We can iterate over the rows returned by the query (if any), by iterating over the cursor.

Each row returned by the cursor is effectively a tuple. We begin by retrieving the record's id which is the record tuple's first item, 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.)

And at the end, we close the cursor and 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.)

5.18.1.2. Comparing a GUI Table with a Database Table in JavaScript

JavaScript

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();
    }
}

For the SQLite database it isn't necessary to provide a host, username, or password, but we have done so here in the JavaScript version because they are needed by pretty well every other database (although in most cases, host will sensibly default to localhost if not specified). Another SQLite quirk is that we must specify a database filename. The means of connection varies between scripting languages and libraries, as do their SQL APIs, but they are all the same in principle, even if the details of the syntax vary.

In JavaScript using Squish's SQL Object (Section 6.16.7.1), we can execute queries on the connection object itself. In fact, the JavaScript API has two kinds of query function we can use, the sqlConnection.query function for executing SELECT statements, and the sqlConnection.execute 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 (Section 6.16.7.3), 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 (Section 6.16.7.3)'s isValid property is true if we have navigated to a valid record. The sqlResult.value 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.)

5.18.1.3. Comparing a GUI Table with a Database Table in Perl

Perl

require Encode;
use DBI;

sub compareTableWithDatabase
{
    my $jtable = shift(@_);
    my $db3file = findFile("testdata", "before.db3");
    eval {
        my $db = DBI->connect("dbi:SQLite:$db3file") ||
            die("Failed to connect: $DBI::errstr");
        my $tableModel = $jtable->getModel();
        my $records = $db->selectall_arrayref(
                "SELECT id, pollutant, type, standard, averaging_time, " .
                "regulatory_citation FROM csv ORDER BY id");
        foreach my $record (@$records) {
            my $row = $record->[0] - 1;
            foreach $column (0..4) {
                my $field = $record->[$column + 1];
                Encode::_utf8_on($field);
                test::compare($tableModel->getValueAt($row, $column)->
                    toString(), $field);
            }
        }
    };
    if ($@) {
        test::fatal("$@");
    }
    else {
        $db->disconnect;
    }
}

The first thing we must do—before writing any of our functions—is require the Encode module (the need for which we will explain shortly), and use the DBI module that provides Perl's database access.

To connect to a SQLite database we only need to supply a filename. The means of connection varies between scripting languages and libraries, as do their SQL APIs, but they are all the same in principle, even if the details of the syntax vary, although in most cases they require a username, password, hostname, and port, rather than a filename.

In Perl we must obtain a connection and then use the connection object to perform our database operations. 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, asking to get our results as a reference to the results array (rather than copying the array, which would be inefficient). We can iterate over the rows returned by the query (if any), by iterating over the array's items.

Each array element holds one record. We begin by retrieving the record's id which is the record's first item, 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.)

Java™—and therefore the JTable—stores text as Unicode, and the text in our SQLite 3 database is also stored as Unicode (using the UTF-8 encoding). However, Perl assumes that text uses the local 8-bit encoding by default, so when we retrieve each text field from each record we must make sure that Perl knows that it is Unicode so that the comparison is correctly performed between Unicode strings and not between a Unicode string from the JTable and (possibly invalid) local 8-bit text from the database. This is achieved by using the Encode module's _utf8_on method. (Note that this method should only be used if we are certain that the string we mark holds UTF-8 text.)

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 an eval block 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.)

5.18.1.4. Comparing a GUI Table with a Database Table in Tcl

Tcl

package require sqlite3

proc compareTableWithDatabase {jtable} {
    sqlite3 db [findFile "testdata" "before.db3"]
    set tableModel [invoke $jtable getModel]
    set fields [list pollutant type standard averaging_time \
        regulatory_citation]
    set row 0
    db eval {SELECT id, pollutant, type, standard, averaging_time, \
            regulatory_citation FROM csv ORDER BY id} values {
        for {set column 0} {$column < 5} {incr column} {
            set table_value [invoke [invoke $tableModel getValueAt \
                $row $column] toString]
            set db_value $values([lindex $fields $column])
            test compare $table_value $db_value
        }
        incr row
    }
    db close
}

The first thing we must do—before writing any of our functions—is import the sqlite3 module. If the package is installed in a standard location it can be imported using a package require statement. Otherwise it is necessary to load the shared library that contains the bindings—for example, by replacing the package require statement with, say, load "C:\tclsqlite3.dll".

To connect to a SQLite database we only need to supply a filename. The means of connection varies between scripting languages and libraries, as do their SQL APIs, but they are all the same in principle, even if the details of the syntax vary, although in most cases they require a username, password, hostname, and port, rather than a filename.

In Tcl we can perform all our database operations through the connection object. 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 each row in turn in the values array.

In this example we ignore the first field of each row that's returned (the id) since this field isn't present in the JTable. For the remaining fields, we iterate over each one, retrieving the JTable's text for the given row and column and comparing it with the database record with the corresponding row and column.

And at the end, we close the connection to the database. Although it doesn't matter much for SQLite, closing the connection to other databases is usually very important—of course, Squish would close the connection for us anyway, but we prefer to be explicit about our intentions.

5.18.2. 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 (Section 5.24) and Processing Test Results (Section 7.1.3).) 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 function have been replaced with calls to a custom DB class instances's log method, and similarly calls to Squish's test.compare and test.verify 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.)

Python

def main():
    startApplication("ItemViewsSwing.jar")
    db = None
    try:
        db = DB()
        tableWidgetName = ":Item Views_javax.swing.JTable"
        tableWidget = waitForObject(tableWidgetName)
        model = tableWidget.getModel()
        for row in range(model.getRowCount()):
            for column in range(model.getColumnCount()):
                item = model.getValueAt(row, column)
                selected = ""
                if tableWidget.isCellSelected(row, column):
                    selected = " +selected"
                message = "(%d, %d) '%s'%s" % (row, column, item.toString(),
                        selected)
                db.log(message)
            expected = bool(row in (14, 24))
            db.compare(model.getValueAt(row, 0).toString(), str(expected))
        db.verify(model.getRowCount() == 25)
    finally:
        if db is not None:
            db.close()
JavaScript

function main()
{
    startApplication("ItemViewsSwing.jar");
    var db;
    try {
        db = new DB();
        var tableWidgetName = ":Item Views_javax.swing.JTable";
        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();
    }
}
Perl

sub main
{
    startApplication("ItemViewsSwing.jar");
    my $db;
    eval {
        $db = new DB(findFile("testdata", "logfile.db3"));
        my $tableWidgetName = ":Item Views_javax.swing.JTable";
        my $tableWidget = waitForObject($tableWidgetName);
        my $model = $tableWidget->getModel();
        for (my $row = 0; $row < $model->getRowCount(); ++$row) {
            for (my $column = 0; $column < $model->getColumnCount();
                ++$column) {
                my $item = $model->getValueAt($row, $column);
                my $selected = "";
                if ($tableWidget->isCellSelected($row, $column)) {
                    $selected = " +selected";
                }
                $db->log("($row, $column) '$item'$selected");
            }
            my $expected = ($row == 14 || $row == 24) ? "true" : "false";
            $db->compare($model->getValueAt($row, 0), $expected);
        }
        $db->verify($model->getRowCount() == 25);
    };
    if ($@) {
        test::fatal("$@");
    }
    else {
        $db->close;
    }
}
Tcl

proc main {} {
    startApplication "ItemViewsSwing.jar"
    sqlite3 db [findFile "testdata" "logfile.db3"]
    set tableWidgetName ":Item Views_javax.swing.JTable"
    set tableWidget [waitForObject $tableWidgetName]
    set model [invoke $tableWidget getModel]
    for {set row 0} {$row < [invoke $model getRowCount]} {incr row} {
        for {set column 0} {$column < [invoke $model getColumnCount]} \
            {incr column} {
            set item [invoke $model getValueAt $row $column]
            set selected ""
            if {[invoke $tableWidget isCellSelected $row $column]} {
                set selected " +selected"
            }
            set text [invoke $item toString]
            set message "($row, $column) '$text'$selected"
            db:log db $message
        }
        set expected "false"
        if {$row == 14 || $row == 24} {
            set expected "true"
        }
        set value [invoke [invoke $model getValueAt $row 0] toString]
        db:compare db $value $expected
    }
    db:verify db [expr {[invoke $model getRowCount] == 25}]
    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) (Section 5.4.6.1.2)). 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.

The DB class's methods (and for Tcl, the db:* functions) are simpler and less sophisticated than Squish's built-in test methods, but they show the proof of concept—you can of course make your own database logging functions as advanced as you like.

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.

Most scripting languages either don't have destructors, or have destructors that are not guaranteed to be called (or in the case of JavaScript, don't have a notion of destructors at all), so we use the appropriate scripting-language construct to ensure that if the db object is created successfully, it is closed at the end—and inside this close method, the database connection is closed.

We are now ready to review the DB class and its methods (or for Tcl, the db:* functions). But, as we mentioned in the previous section, the database APIs vary quite a lot between the different scripting languages, so we will look at each language's implementation of this class in is own separate subsubsection—each subsubsection is complete in itself, so you only need to read the one relevant to the scripting language that interests you.

5.18.2.1. Logging Results Directly to a Database in Python

Python

import sqlite3

class DB:

    def __init__(self):
        self.db = self.cursor = None
        self.db = sqlite3.connect(findFile("testdata", "logfile.db3"))
        self.cursor = self.db.cursor()

    def log(self, message):
        self.cursor.execute("INSERT INTO log (result, message) "
            "VALUES ('LOG', ?)", (message,))

    def compare(self, first, second):
        if first == second:
            result = "PASS"
        else:
            result = "FAIL"
        self.cursor.execute("INSERT INTO log (result, message) "
            "VALUES (?, 'Comparison')", (result,))

    def verify(self, condition):
        if condition:
            result = "PASS"
        else:
            result = "FAIL"
        self.cursor.execute("INSERT INTO log (result, message) "
            "VALUES (?, 'Verification')", (result,))

    def close(self):
        if self.db is not None:
            self.db.commit()
        if self.cursor is not None:
            self.cursor.close()
        if self.db is not None:
            self.db.close()

We must, of course, begin by importing the sqlite3 module.

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.

One small point to note is that if we use placeholders in calls to the cursor.execute method (i.e., “?”, as we ought to, and do here), then the second argument must be a tuple, even if we only have one value to pass as in all the methods implemented here.

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.

5.18.2.2. Logging Results Directly to a Database in JavaScript

JavaScript

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.

5.18.2.3. Logging Results Directly to a Database in Perl

Perl

use DBI;

package DB;

sub new {
    my $self = shift;
    my $class = ref($self) || $self;
    my $db3file = shift;
    my $db = DBI->connect("dbi:SQLite:$db3file") ||
        die("Failed to connect: $DBI::errstr");
    $self = { "db" => $db };
    return bless $self, $class;
}

sub log {
    my ($self, $message) = @_;
    my $query = $self->{db}->prepare("INSERT INTO log (result, message) " .
        "VALUES ('LOG', ?)");
    $query->execute($message);
}

sub compare {
    my ($self, $first, $second) = @_;
    my $result = ($first eq $second) ? "PASS" : "FAIL";
    my $query = $self->{db}->prepare("INSERT INTO log (result, message) " .
        "VALUES (?, 'Comparison')");
    $query->execute($result);
}

sub verify {
    my ($self, $condition) = @_;
    my $result = $condition ? "PASS" : "FAIL";
    my $query = $self->{db}->prepare("INSERT INTO log (result, message) " .
        "VALUES (?, 'Verification')");
    $query->execute($result);
}

sub close {
    my $self = shift;
    $self->{db}->disconnect;
}

We must, of course, begin by using the DBI module to provide database access.

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.

One small point to note is that if we use placeholders in calls to the prepare function (i.e., “?”, as we ought to, and do here), we must pass the actual values to use when we call the execute method.

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.

5.18.2.4. Logging Results Directly to a Database in Tcl

Tcl

package require sqlite3

proc db:log {db message} {
    db eval {INSERT INTO log (result, message) VALUES ("LOG", $message)}
}

proc db:compare {db first second} {
    if {$first == $second} {
        set result "PASS"
    } else {
        set result "FAIL"
    }
    db eval {INSERT INTO log (result, message) VALUES \
        ($result, "Comparison")}
}

proc db:verify {db condition} {
    if {$condition} {
        set result "PASS"
    } else {
        set result "FAIL"
    }
    db eval {INSERT INTO log (result, message) VALUES \
        ($result, "Verification")}
}

We must, of course, begin by importing the sqlite3 module. (See Comparing a GUI Table with a Database Table in Tcl (Section 5.18.1.4) for another way to do the import.)

The db:* functions all expect to be passed a SQLite 3 database connection object as their first argument. All three functions assume 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 these functions are very simple, but they show the fundamentals of how we could create database-savvy functions that we could use to store whatever test data and results we liked, ready for post-processing or reporting.