import sqlite3
import os

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.)

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.8.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.8.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.8.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.)

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.)

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.

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.

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.

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.

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.