PostgreSQL/JDBC Test Suite Howto

================================

1 Introduction

2 Installation

3 Configuration

4 Running the test suite

5 Extending the test suite with new tests

6 Guidelines for developing new tests

7 Example

8 Running the JDBC 2 test suite from Sun against PostgreSQL

9 Credits, feedback

1 Introduction

--------------

The PostgreSQL source tree contains an automated test suite for

the JDBC driver. This document explains how to install,

configure and run this test suite. Furthermore, it offers

guidelines and an example for developers to add new test cases.

Sun provides two standard JDBC test suites that you may also

find useful.

http://java.sun.com/products/jdbc/download2.html (JDBC 1)

http://java.sun.com/products/jdbc/jdbctestsuite-1_2_1.html (JDBC

2, including J2EE requirements)

The JDBC 2 test suite is covered in section 8 below. The JDBC 1

test suite is not currently covered in this document.

2 Installation

--------------

Of course, you need to have a Java 2 JDK or JRE installed. The

standard JDK from Sun is OK. You can download it from

http://java.sun.com/.

You need to install the Ant build utility. You can download it

from http://jakarta.apache.org/ant/.

You also need to install the JUnit testing framework. You can

download it from http://www.junit.org/. Add junit.jar to your

CLASSPATH before you perform the following steps. Ant will

dynamically detect that JUnit is present and then build the JDBC

test suite.

You need to install and build the PostgreSQL source tree. You

can download it from http://www.postgresql.org/devel-corner/.

See README and INSTALL in the top of the tree for more

information.

You should run ./configure with the command line option

--with-java. You may also want to use --with-pgport to compile a

non-standard default port number (e.g. 5433) into all

components. This will cause the server to listen on this

non-standard port and it will cause the JDBC driver to connect

to this port by default. In this way your testing environment is

easily separated from other PostgreSQL applications on the same

system.

In this Howto we'll use $JDBC_SRC to refer to the directory

src/interfaces/jdbc of the PostgreSQL source tree in your

environment. The test suite is located in the subdirectory

$JDBC_SRC/org/postgresql/test.

3 Configuration

---------------

The test suite requires a PostgreSQL database to run the tests

against and a user to login as. For a full regression test of

the entire PostgreSQL system, you should run the test against a

server built from the same source tree as the driver you're

testing. The tests will create and drop many objects in this

database, so it should not contain production tables to avoid

loss of data. We recommend you assign the following names:

database: test

username: test

password: password

These names correspond with the default names set for the test

suite in $JDBC_SRC/build.xml. If you have chosen other names you

need to edit this file and change the properties "database",

"username" and "password" accordingly.

4 Running the test suite

------------------------

%cd $JDBC_SRC

%make

%make check

This will run the command line version of JUnit. If you'd like

to see an animated coloured progress bar as the tests are

executed, you may want to use one of the GUI versions of the

test runner. See the JUnit documentation for more information.

If the test suite reports errors or failures that you cannot

explain, please post the relevant parts of the output to the

mailing list pgsql-jdbc@postgresql.org.

5 Extending the test suite with new tests

-----------------------------------------

If you're not familiar with JUnit, we recommend that you

first read the introductory article "JUnit Test Infected:

Programmers Love Writing Tests" on

http://junit.sourceforge.net/doc/testinfected/testing.htm.

Before continuing, you should ensure you understand the

following concepts: test suite, test case, test, fixture,

assertion, failure.

The test suite consists of test cases, which consist of tests.

A test case is a collection of tests that test a particular

feature. The test suite is a collection of test cases that

together test the driver - and to an extent the PostgreSQL

backend - as a whole.

If you decide to add a test to an existing test case, all you

need to do is add a method with a name that begins with "test"

and which takes no arguments. JUnit will dynamically find this

method using reflection and run it when it runs the test case.

In your test method you can use the fixture that is setup for it

by the test case.

If you decide to add a new test case, you should do two things:

1) Add a class that extends junit.framework.TestCase. It should

contain setUp() and tearDown() methods that create and destroy

the fixture respectively.

2) Edit $JDBC_SRC/org/postgresql/test/JDBC2Tests.java and add a

suite.addTestSuite() call for your class. This will make the

test case part of the test suite.

6 Guidelines for developing new tests

-------------------------------------

Every test should create and drop its own tables. We suggest to

consider database objects (e.g. tables) part of the fixture for

the tests in the test case. The test should also succeed when a

table by the same name already exists in the test database, e.g.

by dropping the table before running the test (ignoring errors).

The recommended pattern for creating and dropping tables can be

found in the example in section 7 below.

Please note that JUnit provides several convenience methods to

check for conditions. See the TestCase class in the Javadoc

documentation of JUnit, which is installed on your system. For

example, you can compare two integers using

TestCase.assertEquals(int expected, int actual). This method

will print both values in case of a failure.

To simply report a failure use TestCase.fail().

The JUnit FAQ explains how to test for a thrown exception.

Avoid the use of the deprecated TestCase.assert(), since it will

collide with the new assert keyword in the Java 2 platform

version 1.4.

As a rule, the test suite should succeed. Any errors or failures

- which may be caused by bugs in the JDBC driver, the backend or

the test suite - should be fixed ASAP. Don't let a test fail

just to make it clear that something needs to be fixed somewhere.

That's what the TODO lists are for.

Add some comments to your tests to explain to others what it is

you're testing. A long sequence of JDBC method calls and JUnit

assertions can be hard to comprehend.

For example, in the comments you can explain where a certain test

condition originates from. Is it a JDBC requirement, PostgreSQL

behaviour or the intended implementation of a feature?

7 Example (incomplete)

----------------------

package org.postgresql.test.jdbc2;

import org.postgresql.test.JDBC2Tests;

import junit.framework.TestCase;

import java.sql.*;

/**

* Test case for ...

*/

public class FooTest extends TestCase {

private Connection con;

private Statement stmt;

public FooTest(String name) {

super(name);

}

protected void setUp() throws Exception {

con = JDBC2Tests.openDB();

stmt = con.createStatement();

// Drop the test table if it already exists for some

// reason. It is not an error if it doesn't exist.

try {

stmt.executeUpdate("DROP TABLE testfoo");

} catch (SQLException e) {

// Intentionally ignore. We cannot distinguish

// "table does not exist" from other errors, since

// PostgreSQL doesn't support error codes yet.

}

stmt.executeUpdate(

"CREATE TABLE testfoo(pk INTEGER, col1 INTEGER)");

stmt.executeUpdate("INSERT INTO testfoo VALUES(1, 0)");

// You may want to call con.setAutoCommit(false) at

// this point, if most tests in this test case require

// the use of transactions.

}

protected void tearDown() throws Exception {

con.setAutoCommit(true);

if (stmt != null) {

stmt.executeUpdate("DROP TABLE testfoo");

stmt.close();

}

if (con != null) {

JDBC2Tests.closeDB(con);

}

}

public void testFoo() {

// Use the assert methods in junit.framework.TestCase

// for the actual tests

// Just some silly examples

assertNotNull(con);

if (stmt == null) {

fail("Where is my statement?");

}

}

public void testBar() {

// Another test.

}

}

8. Running the JDBC 2 test suite from Sun against PostgreSQL

------------------------------------------------------------

Download the test suite from

http://java.sun.com/products/jdbc/jdbctestsuite-1_2_1.html

This is the JDBC 2 test suite that includes J2EE requirements.

1. Configure PostgreSQL so that it accepts TCP/IP connections and

start the server. Prepare PostgreSQL by creating two users (cts1

and cts2) and two databases (DB1 and DB2) in the cluster that is

going to be used for JDBC testing.

2. Download the latest release versions of the J2EE, J2SE, and JDBC

test suite from Sun's Java site (http://java.sun.com), and install

according to Sun's documentation.

3. The following environment variables should be set:

CTS_HOME=<path where JDBC test suite installed (eg: /usr/local/jdbccts)>

J2EE_HOME=<path where J2EE installed (eg: /usr/local/j2sdkee1.2.1)>

JAVA_HOME=<path where J2SE installed (eg: /usr/local/jdk1.3.1)>

NO_JAVATEST=Y

LOCAL_CLASSES=<path to PostgreSQL JDBC driver jar>

4. In $J2EE_HOME/config/default.properties:

jdbc.drivers=org.postgresql.Driver

jdbc.datasources=jdbc/DB1|jdbc:postgresql://localhost:5432/DB1|jdbc/DB2|jdbc:postgresq://localhost:5432/DB2

Of course, if PostgreSQL is running on a computer different from

the one running the application server, localhost should be changed

to the proper host. Also, 5432 should be changed to whatever port

PostgreSQL is listening on (5432 is the default).

In $J2EE_HOME/bin/userconfig.sh:

Add $CTS_HOME/lib/harness.jar, $CTS_HOME/lib/moo.jar,

$CTS_HOME/lib/util.jar to J2EE_CLASSPATH. Also add the path to

the PostgreSQL JDBC jar to J2EE_CLASSPATH. Set the JAVA_HOME

variable to where you installed the J2SE. You should end up with

something like this:

CTS_HOME=/home/liams/linux/java/jdbccts

J2EE_CLASSPATH=/home/liams/work/inst/postgresql-7.1.2/share/java/postgresql.jar:$CTS_HOME/lib/harness.jar:$CTS_HOME/lib/moo.jar:$CTS_HOME/lib/util.jar

export J2EE_CLASSPATH

JAVA_HOME=/home/liams/linux/java/jdk1.3.1

export JAVA_HOME

In $CTS_HOME/bin/cts.jte:

webServerHost=localhost

webServerPort=8000

servletServerHost=localhost

servletServerPort=8000

5. Start the application server (j2ee):

$ cd $J2EE_HOME

$ bin/j2ee -verbose

The server can be stopped after the tests have finished:

$ cd $J2EE_HOME

$ bin/j2ee -stop

6. Run the JDBC tests:

$ cd $CTS_HOME/tests/jdbc/ee

$ make jdbc-tests

At the time of writing of this document, a great number of tests

in this test suite fail.

9 Credits, feedback

-------------------

The parts of this document describing the PostgreSQL test suite

were originally written by Rene Pijlman. Liam Stewart contributed

the section on the Sun JDBC 2 test suite.

Please send your questions about the JDBC test suites or suggestions

for improvement to the pgsql-jdbc@postgresql.org mailing list.

The source of this document is maintained in

src/interfaces/jdbc/org/postgresql/test/README in CVS. Patches for

improvement can be send to the mailing list

pgsql-patches@postgresql.org.