Building acceptence tests and more DB thoughts

Discussion of testing theory and practice, including methodologies (such as TDD, BDD, DDD, Agile, XP) and software - anything to do with testing goes here. (Formerly "The Testing Side of Development")

Moderator: General Moderators

User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Building acceptence tests and more DB thoughts

Post by nielsene »

Rather than tackling the four missing features for my current development iteration, I want to try to tackle the four outstanding bugs from the last spiral.

These four bugs center around the behavour of a 5-step import "wizzzard" (its magic is on pair with Rincewind's :) ). I suspect I'll just script a "clickSumbit" navigation through the five forms, filling out all the forms, etc. I'll embed a few asserts along the way to catch the current bugs, but keep this as minimal as possible to avoid display level changes. Upon completition of the wizzard I'll navigate to where the updates should be visible to do some spot checks. A few of the current bugs will require a few queries against the DB, which brings me to:

I'm still having a hard time figuring out how to integrate the database aspect with the test. Here's my current thoughts:

I want to develop "abstract" subclasses (one off the Unit tree and one of the Web Tree). (Abstract in quotes since I'm still PHP4) The setup procedure will connect (as a database superuser ) to a database named in the concrete subclass of the new class, checking for an explicit "test" prefix to the dbname or fail. It will then drop all tables from the DB and reload from a SQL file also identified in the constructor, defaulting to a "stock" populated test DB of the identified "DB Type" (I have "Central" and "Per-Event" databases with different schemas). The final step is to drop the connection and reconnect as the user identified in the subclass constructor (ie a normal DB user), failinng if not possible.

It'll also probably off some new assertions
  • assertTableInlucdes($tablename, $rows,$expected)
  • assertTableDoesNotInlucde($tablename,$rowss)
In the two cases $rows would be an array of associative arrays, something like

Code: Select all

$rows = array(array("peopleid"=>1,"firstname"=>"Eric","lastname"=>"Nielsen"));
It then constructs a SELECT against $tablename with a WHERE using each key=$value pairing and does a numrows count to compare against the $expected array (defaults to 1 if not provideded). The primary key doesn't need to be included in the row list as that may be too brittle.

I've thought a lot about providing an assertDatabaseContents($filename) that would loop through textual schema dump file and check explicitly for every roww in every table. Tables not listed in the schema dump would be ignored. Tables listed would have to match exactly, ie equal number of rows and all specified rows included. But I think I've concluded that this would be too brittle and lead to countless "false fails". Testing behavoir of on update/delete rules will have to be done via explicit calls to the two new assertions.

Is this basically what you've been saying, with the modification that I have the luxury/requirement of multiple database and not masquerading a database inside a prefixed table approach?
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post by nielsene »

PS. Of course I'll need to develop this DBTest subclass using TDD. Is there anything "tricky" about testing test code?
McGruff
DevNet Master
Posts: 2893
Joined: Thu Jan 30, 2003 8:26 pm
Location: Glasgow, Scotland

Post by McGruff »

Yes that sounds quite similar.

One thought: populating sample data from an sql file could make the tests less clear since the data in sample rows isn't explicitly listed in the test, in an sql statement. On the other hand, if you want to load many rows of real data it would be a lot easier to do it from an sql dump. In order to make sure that db queries do work properly, I think you will at least need to copy the table structure.

Whatever is simplest and clearest and still allows you to make the required constraints.

Some custom assertions can be very useful. For assertDatabaseContents, testing by making queries might be easier than reading through an sql file?
McGruff
DevNet Master
Posts: 2893
Joined: Thu Jan 30, 2003 8:26 pm
Location: Glasgow, Scotland

Post by McGruff »

nielsene wrote:Is there anything "tricky" about testing test code?
Just the usual ;)
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post by nielsene »

Well what I'm worried about is, my databases makes extensive use of cascading deletes as well as a large number of "audit" tables populated automatically by functions in the databasse. (Triggers/Rules/Functions in PostGreSQL terms).

I want to ensure that all the needed rippling happened, and that none that shouldn't have. This seems to imply a complete test against the DB, thus a need for an order-independnet method for comparing the entire contents of the DB. Ick. That's why I'm a little worried about the time required to run these tests.

The database uses something quite similar to Date/Darwen/Lorentzos's Temporal Model, so one base table often has N history tables (where N is the number of columns of the original table). Allowing full history tracking of any change at its own rate of change. Unlike the somewhat more standard "one audit table to rule them all" these history tables still have their "proper" meaning -- they use the same primary keys as the original for easy of joining and are combined vertically into views that show the complete history of a tuple in the database.


My plan is to populate the "delta" from the "stock" databases with explicit queries in the test. The schema load is primarily for creating the tables, and loading all the "lookup table" style rows, as well as a representative sampling of real data for mosts tests. Most integration tests and many unit tests won't need anything more custom.

However the corner cases might require me to setup a more specialized case -- and yes, you're right that should be done in the test case.
McGruff
DevNet Master
Posts: 2893
Joined: Thu Jan 30, 2003 8:26 pm
Location: Glasgow, Scotland

Post by McGruff »

I haven't worked with this kind of db design so I'm not sure if I can help much. It feels (note the "feels" I'm a bit out of my depth here) a bit like web-testing: ie the testing tool is making a black-box test of a system which it can't peer into directly. That could still be useful but are there testing tools for the db itself?
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post by nielsene »

Well I trust the DB code and they have decent regression tests if not full unit/acceptence tests.

I don't trust my inside the DB code as much, and I hope to unit test it from the outside using these classes once they are in good enough shape.
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post by nielsene »

Here's the start of my base class. (The Unit Test one, the WebTest one will be basically an exact copy....) This is just to share, no question here.

Its currently rather locked to my application and my database. I didn't want to use a DB abstraction layer within the testcase as the test case is designed to test databases, (circular dependency). This doesn't have any custom assertions yet, and I'll need to do some method renaming I think, but it does handle the schema loads, database drop/creates and blocks out access to non-test databasses. (Currently via a regexp for test in the database name. I think'll I'll want to make that stricter, reqiureing test as the prefix.) This also relies on the normal PostGreSQL pgpass file to avoid putting passing passwords on command-line database commands.

Code: Select all

<?php
  /**
   * Extends SimpleTest subclasses for database testing.
   *
   * This file is part of CompInaBox.
   * @copyright CompInaBox Copyright 2001-2005. Eric D. Nielsen, All rights reserverd.
   * @license http://opensource.org/licenses/gpl-license.php GNU Public License
   *
   * @author Eric D. Nielsen <redacted>
   * @package Tests
   * @subpackage TestTools
  */
  /**
   * Extends the UnitTestCase with Database Support
   *
   * @package Tests
   * @subpackage TestTools
   */
class DatabaseUnitTestCase extends UnitTestCase {
  /**
   * Test Case Database Connection
   * @access private
   * @var resource $db a raw database connection
   */
  var $db;

  /**#@+
   * @access private
   * @var string
   */
  /* Database Hostname */
  var $dbhost;
  /* Database Name */
  var $dbname;
  /* Database User */
  var $dbuser;
  /* Database Pass */
  var $dbpass;
  /* Database Preload State */
  var $schemaFile;
  /**#@-*/

  /**
   * DatabaseUnitTestCase Constructor.
   */
  function DatabaseUnitTestCase() {
    $this->db=NULL;
    $this->dbhost=TEST_DB_HOST;
    $this->dbname=TEST_DB_NAME;
    $this->dbuser=TEST_DB_USER;
    $this->dbpass=TEST_DB_PASS;
    $this->schemaFile=TEST_SCHEMA_FILE;
  }

  /**
   * Setup a sandboxed database for testing.
   */
  function setUp() {
    if ($this->dbname!="") {
      $this->_fullyClose();
      $cmd = "/usr/local/bin/dropdb -h '{$this->dbhost}' -U '{$this->dbuser}' '{$this->dbname}'";
      exec($cmd);
      $cmd = "/usr/local/bin/createdb -h '{$this->dbhost}' -U '{$this->dbuser}' '{$this->dbname}'";
      exec($cmd);
    }
    $this->db=@pg_connect("host='{$this->dbhost}' dbname='{$this->dbname}' user='{$this->dbuser}' password='{$this->dbpass}'",true);
    if ($this->_isValidConnection() && $this->schemaFile!='')
      $this->_loadSchemaFile();
  }

  function tearDown() {
    $this->_fullyClose();
  }    
  
  function _loadSchemaFile() {
    $cmd = "/usr/local/bin/psql -h '{$this->dbhost}' -d '{$this->dbname}' -U '{$this->dbuser}' -f '{$this->schemaFile}'";
    exec($cmd);
  }

  function _getDatabaseName() {
    return ($this->_isConnectionValid() ? pg_dbname($this->db) : "");
  }

  function _isConnectionValid() {
    return ($this->db!==NULL ? 
	    (pg_connection_status($this->db)===PGSQL_CONNECTION_OK)
	    : FALSE);
  }

  function setDBName($dbname) {
    $this->_fullyClose();
    if (preg_match("/test/i",$dbname)) {
      $this->dbname=$dbname;
      return TRUE;
    } else {
      $this->dbname="";
      return FALSE;
    }
  }
  function setSchemaFile($file) {
    if (file_exists($file)) {
      $this->schemaFile=$file;
      return TRUE;
    } else {
      $this->schemaFile="";
      return FALSE;
    }
  }
  function query($q) {
    if ($this->_isConnectionValid())
      return pg_query($this->db,$q);
     else
      return NULL;
  }

  function _fullyClose()  {
    $liveConnection=$this->_isConnectionValid();
    if ($liveConnection) pg_close($this->db);
    $this->db=NULL;
  }  
}
?>
And the tests

Code: Select all

<?php 
  /**
   * Test the Testing Subclasses for Database Tests
   *
   * This file is part of CompInaBox.
   * @package Tests
   * @subpackage TestTools
   * @author Eric D. Nielsen <redacted>
   * @copyright CompInaBox Copyright 2005, Eric D. Nielsen.  All Rights Reserved.
   * @license http://opensource.org/licenses/gpl-license.php GNU Public License
   */


/**
 * TestDatabaseUnitTest
 * @package Tests
 * @subpackage TestTools
 */
class TestDatabaseUnitTest extends UnitTestCase {

  var $dbTC;
  function TestDatabaseUnitTest() {
    $this->UnitTestCase('DatabaseUnitTest');
  }

  function setUp() {
    $this->dbTC=new DatabaseUnitTestCase();
  }


  function TestEstablishingTestDBConnection() {
    $this->dbTC->setUp();
    $this->assertPattern("/test/i",$this->dbTC->_getDatabaseName());
    $this->dbTC->tearDown();
  }

  function TestInitialRejectionOfNonTestDB() { 
    $this->dbTC->setDBName('cib_central');
    $this->dbTC->setUp();
    $this->assertFalse($this->dbTC->_isConnectionValid());
    $this->dbTC->tearDown();
  }
  
  function TestDroppedConnectionWhenChanged() { 
    $this->dbTC->setUp();
    $this->assertTrue($this->dbTC->_isConnectionValid());
    $this->dbTC->setDBName('cib_central'); 
    $this->dbTC->setUp();
    $this->assertFalse($this->dbTC->_isConnectionValid());
    $this->dbTC->tearDown();
  }
  
  function TestSchemaLoad() {
    $this->dbTC->setSchemaFile(COMPINABOX.
			       'tests/include/sql/testLoadSchema.sql');
    $this->dbTC->setup();
    $query="SELECT * FROM test_table;";
    $result=$this->dbTC->query($query);
    $this->assertEqual(pg_num_rows($result),2);
    $this->dbTC->tearDown();
  }
  
  function TestRepeatability() {
    $this->dbTC->setSchemaFile(COMPINABOX.
			       'tests/include/sql/testLoadSchema.sql');
    $this->dbTC->setUp();
    $query="SELECT * FROM test_table;";
    $result=$this->dbTC->query($query);
    $this->assertEqual(pg_num_rows($result),2);
    $this->dbTC->setUp();
    $query="SELECT * FROM test_table;";
    $result=$this->dbTC->query($query);
    $this->assertEqual(pg_num_rows($result),2);
    $this->dbTC->tearDown();
  }

}

?>
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post by nielsene »

I'm back working on this; and I just ran into a brick wall...

The prime motivation to start working on the DBTestCases was to help wrap some current bugs with failing tests so I can start isolating them and know when I've fixed them.

These bugs are deep within a multi-page wizzard, so I thought a top level acceptance test would be the best first step, then add the lower level unit tests as I work by way down through the code.

Towards that end, I got the previously listed DB test case tool written. I wasn't exactly sure what the new assertions should look like, so I went back to writing the acceptence test -- ie to find out what type of interface I'll want.

However I just realized that I don't seem to have a good way to get the acceptence test to use the sandboxed database. Of course, unlike the regular unit tests, the acceptence(web test) is not runnning in the same process so I can't "hack" up the Registry to change the DB, etc.

The only solution I'm thinking of is to checkout another copy of the source, so I can override its DB connections. Since I'll still want to have a more "permenant" db (ie not using the DB wipe/reload test code) version for other manual testing/play. Is this the normal way of dealing with injecting a test database into acceptence testing?
McGruff
DevNet Master
Posts: 2893
Joined: Thu Jan 30, 2003 8:26 pm
Location: Glasgow, Scotland

Post by McGruff »

I've got so much to do I'm starting to get punch-drunk and I might have missed something in your plan but if you just need to switch db connection parameters, and database name, a test install could use a different config file. That's pretty straightforward and could be scripted along with a cvs checkout. If you need to get a different object into the code, you could set the class name & path in the config files.

Passing an extra GET var could allow the relevant classes to instantiate different files. That will interfere with the test though. If you're strict about request syntax checks suddenly all the validation would start complaining about query string tampering. Probably not a real option.

DependencyInjection might be another option? It's not something I've boned up on but the consensus seems to be that it's hard to find situations where it's really useful. Maybe you've just found one.

Or maybe just ask Rincewind to wave his magic wand.

On second thoughts...

I'm not sure what people do normally. I'd guess that a sandbox isn't widely used but I like the extra protection this gives you - particularly if you have to work withina single db. I've been meaning to get some peer review, just as soon as I can find time to tidy up the code I've been working on.
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post by nielsene »

I think the sandbox approach is a good idea and basically a requirement. And yes its trivial to do a second check-out with different configuration, just wanted to see if its the normal approach.

How do you sandbox off the testing checkout, though? While, the database test cases will make sure any required purge/setup happens in the sandboxed database, I can't see how to ensure that the acceptance tests only use a configuration where its connected to the same database...
McGruff
DevNet Master
Posts: 2893
Joined: Thu Jan 30, 2003 8:26 pm
Location: Glasgow, Scotland

Post by McGruff »

I'm assuming a (sandboxed) test install with one config file and then, if everything runs green, a live install with the normal config file & db followed by a final live test run, omitting any test cases which might disturb live data (for example ading a sample new user or making forum posts).

These have already been tested although strictly speaking not against the live db. If you're using sql dumps to make exact copies of the db structure that's OK but don't ask me how you copy triggers & scripts around. I really must find some time to mess around with postgres and firebird.

If there are library files stored off root, the second checkout could just grab the htdocs stuff and install that.
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post by nielsene »

So I guess what this means, is that I should change my current local checkout/db pair to my local "integration installation". And move my development to the new sandboxed db/checkout.

Otherwise I can't test while coding without committing -- and I don't want to commit until the tests pass, so I have to be coding where I'm testing.
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post by nielsene »

One other possible option, please let me know if this sounds reasonable or just wrong.

The SandboxDatabaseWebTestCase subclass could potentially swap a configuration file/in/out in the setUp/teadDown. Probably make a command-line script, setuid, etc so it can swap the regular CIB_config.inc with a testing_CIB_config.inc and back. Then the sandboxed DB will be talking to the DB being used accessed by the test cases.

Plus its still only one checkout of the code -- so development activities can be instantly tested via the test suite or by visual inspection before checkin.
McGruff
DevNet Master
Posts: 2893
Joined: Thu Jan 30, 2003 8:26 pm
Location: Glasgow, Scotland

Post by McGruff »

I'm not sure I've fully understood the problem.

I'll initially be developing locally, checking in regularly from a local working copy. I'll at least have a copy of the live db structure made with an sql dump - possibly a copy of live data as well (unless it's a multi-gigabyte monster). Sandboxing isn't as important when testing locally but it's still possible to mess up any other data you've got lying around for other projects so I would be doing it. Tests would set up the local, sandbox db by copying table structure etc from the local copy of the live db.

Deployment would be in two stages: first a sandboxed test install then a live install if all the tests pass.
Post Reply