pgpool2.git / summary / log / commit / refs
commit 5006520d2aeb5c97780d765a49f27dcc0e7196fd
Author: Yoshiyuki Asaba <y-asaba at pgfoundry.org>
Date: Mon Oct 22 08:47:58 2007 +0000
Add test program using Java+JDBC driver.
test/jdbc/AutoCommitTest.java | 45 +++++++++++++++++++
test/jdbc/BatchTest.java | 55 +++++++++++++++++++++++
test/jdbc/ColumnTest.java | 60 +++++++++++++++++++++++++
test/jdbc/LockTest.java | 36 +++++++++++++++
test/jdbc/PgpoolTest.java | 75 +++++++++++++++++++++++++++++++
test/jdbc/RunTest.java | 77 ++++++++++++++++++++++++++++++++
test/jdbc/SelectTest.java | 37 ++++++++++++++++
test/jdbc/UpdateTest.java | 32 ++++++++++++++
test/jdbc/expected/autocommit | 10 +++++
test/jdbc/expected/batch | 1 +
test/jdbc/expected/column | 100 ++++++++++++++++++++++++++++++++++++++++++
test/jdbc/expected/lock | 100 ++++++++++++++++++++++++++++++++++++++++++
test/jdbc/expected/select | 2 +
test/jdbc/expected/update | 1 +
test/jdbc/pgpool.properties | 7 +++
test/jdbc/prepare.sql | 50 +++++++++++++++++++++
test/jdbc/run.sh | 6 +++
17 files changed, 694 insertions(+)
diff --git a/test/jdbc/AutoCommitTest.java b/test/jdbc/AutoCommitTest.java
new file mode 100644
index 000000000..5b8194f4c
--- /dev/null
+++ b/test/jdbc/AutoCommitTest.java
@@ -0,0 +1,45 @@
+import java.util.*;
+import java.io.*;
+import java.sql.*;
+
+public class AutoCommitTest extends PgpoolTest{
+ public String sql = "select * from autocommit where a = ?";
+
+ public static void main(String[] args) throws SQLException {
+ AutoCommitTest test = new AutoCommitTest();
+ test.do_test();
+ }
+
+ public void do_test() throws SQLException
+ {
+ connection.setAutoCommit(true);
+
+ PreparedStatement stmt = null;
+ ResultSet rs = null;
+
+ try {
+ for(int i = 0; i < 10; i++) {
+ stmt = connection.prepareStatement(sql);
+ try {
+ stmt.setInt(1, i + 1);
+ rs = stmt.executeQuery();
+
+ while(rs.next()){
+ logwriter.print(rs.getInt(1) + " ");
+ }
+ logwriter.println();
+ } finally {
+ if(rs != null) rs.close();
+ if(stmt != null) stmt.close();
+ }
+ }
+ } finally {
+ connection.close();
+ logwriter.close();
+ }
+ }
+
+ public String getTestName() {
+ return "autocommit";
+ }
+}
diff --git a/test/jdbc/BatchTest.java b/test/jdbc/BatchTest.java
new file mode 100644
index 000000000..664f43950
--- /dev/null
+++ b/test/jdbc/BatchTest.java
@@ -0,0 +1,55 @@
+import java.util.*;
+import java.io.*;
+import java.sql.*;
+
+public class BatchTest extends PgpoolTest {
+ public String [] batchSqls = new String[] {
+ "INSERT INTO batch VALUES (1)",
+ "INSERT INTO batch VALUES (1)",
+ "INSERT INTO batch VALUES (1)",
+ "INSERT INTO batch VALUES (1)",
+ "INSERT INTO batch VALUES (1)",
+ "INSERT INTO batch VALUES (1)",
+ "INSERT INTO batch VALUES (1)",
+ "INSERT INTO batch VALUES (1)",
+ "INSERT INTO batch VALUES (1)",
+ "INSERT INTO batch VALUES (1)",
+ "INSERT INTO batch VALUES (1)",
+ };
+
+ public void do_test() throws SQLException {
+ connection.setAutoCommit(false);
+
+ Statement stmt = null;
+ ResultSet rs = null;
+
+ try {
+ try {
+ stmt = connection.createStatement();
+ for(int i=0; i<batchSqls.length; i++) {
+ stmt.addBatch(batchSqls[i]);
+ }
+ int [] r = stmt.executeBatch();
+
+ connection.commit();
+ stmt.close();
+
+ stmt = connection.createStatement();
+ rs = stmt.executeQuery("SELECT count(*) FROM batch");
+ rs.next();
+ logwriter.println(rs.getInt(1));
+ connection.commit();
+ } finally {
+ if(rs != null) rs.close();
+ if(stmt != null) stmt.close();
+ }
+ } finally {
+ connection.close();
+ logwriter.close();
+ }
+ }
+
+ public String getTestName() {
+ return "batch";
+ }
+}
diff --git a/test/jdbc/ColumnTest.java b/test/jdbc/ColumnTest.java
new file mode 100644
index 000000000..b4402337a
--- /dev/null
+++ b/test/jdbc/ColumnTest.java
@@ -0,0 +1,60 @@
+import java.sql.Connection;
+import java.sql.DriverManager;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.sql.Statement;
+
+public class ColumnTest extends PgpoolTest {
+ public static String sql =
+ "SELECT"
+ +" columntest.v1 as abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz001,"
+ +" columntest.v2 as abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz002,"
+ +" columntest.v3 as abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz003,"
+ +" columntest.v4 as abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz004,"
+ +" columntest.v5 as abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz005,"
+ +" columntest.v6 as abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz006,"
+ +" columntest.v7 as abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz007,"
+ +" columntest.v8 as abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz008,"
+ +" columntest.v9 as abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz009,"
+ +" columntest.v10 as abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz010,"
+ +" columntest.v11 as abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz011,"
+ +" columntest.v12 as abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz012,"
+ +" columntest.v13 as abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz013,"
+ +" columntest.v14 as abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz014,"
+ +" columntest.v15 as abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz015"
+ +" FROM columntest";
+ static String url = "jdbc:postgresql://localhost:9999/test";
+ static String user = "y-asaba";
+ static String password = "postgres";
+ static final int COUNT = 100;
+
+ public void do_test() throws SQLException {
+ connection.setAutoCommit(false);
+
+ Statement stmt = null;
+ ResultSet rs = null;
+
+ try {
+ for (int i = 0; i < COUNT; i++) {
+ try {
+ stmt = connection.createStatement();
+ rs = stmt.executeQuery(sql);
+
+ while(rs.next()){
+ logwriter.println(rs.getInt(1));
+ }
+ } finally {
+ if(rs != null) rs.close();
+ if(stmt != null) stmt.close();
+ }
+ }
+ } finally {
+ connection.close();
+ logwriter.close();
+ }
+ }
+
+ public String getTestName() {
+ return "column";
+ }
+}
diff --git a/test/jdbc/LockTest.java b/test/jdbc/LockTest.java
new file mode 100644
index 000000000..2509ce1e8
--- /dev/null
+++ b/test/jdbc/LockTest.java
@@ -0,0 +1,36 @@
+import java.sql.*;
+import java.io.*;
+
+public class LockTest extends PgpoolTest {
+ public final int COUNT = 100;
+
+ public void do_test() throws SQLException, IOException {
+ PreparedStatement pstmt = null;
+ Statement stmt = null;
+ ResultSet rs = null;
+ try{
+ for (int i = 0; i < COUNT; i++) {
+ connection.setAutoCommit(false);
+ /* table lock */
+ stmt = connection.createStatement();
+ stmt.executeUpdate("LOCK locktest");
+
+ pstmt = connection.prepareStatement("SELECT * FROM locktest");
+ rs = pstmt.executeQuery();
+ rs.next();
+ logwriter.println(rs.getInt(1));
+ rs.close();
+ connection.commit();
+ }
+ } finally {
+ if (connection != null) {
+ connection.close();
+ }
+
+ }
+ }
+
+ public String getTestName() {
+ return "lock";
+ }
+}
diff --git a/test/jdbc/PgpoolTest.java b/test/jdbc/PgpoolTest.java
new file mode 100644
index 000000000..d07f05a37
--- /dev/null
+++ b/test/jdbc/PgpoolTest.java
@@ -0,0 +1,75 @@
+import java.util.*;
+import java.io.*;
+import java.sql.*;
+
+public abstract class PgpoolTest {
+ protected String url = "jdbc:postgresql://";
+ protected String user;
+ protected String password;
+ protected String dbname;
+ protected String host;
+ protected String port;
+ protected String options;
+ protected Connection connection;
+ protected PrintWriter logwriter;
+
+ public PgpoolTest()
+ {
+ try {
+ Properties prop = new Properties();
+ prop.load(new FileInputStream("pgpool.properties"));
+ host = prop.getProperty("pgpooltest.host");
+ port = prop.getProperty("pgpooltest.port");
+ user = prop.getProperty("pgpooltest.user");
+ password = prop.getProperty("pgpooltest.password");
+ dbname = prop.getProperty("pgpooltest.dbname");
+ options = prop.getProperty("pgpooltest.options");
+
+ url = url + host + ":" + port + "/" + dbname;
+
+ if (!options.equals(""))
+ url = url + "?" + options;
+
+ File dir = new File("result");
+ dir.mkdir();
+
+ logwriter = new PrintWriter(new FileWriter("result/" + getTestName()), true);
+ try {
+ Class.forName("org.postgresql.Driver");
+ connection = DriverManager.getConnection(url, user, password);
+ } catch (ClassNotFoundException e) {
+ System.out.println("cannot load JDBC Driver");
+ System.exit(1);
+ } catch (Exception e) {
+ System.out.println("cannot connect to pgpool");
+ System.exit(1);
+ }
+ } catch (Exception e) {
+ System.out.println("cannot read property file");
+ System.exit(1);
+ }
+ }
+
+ public void check() {
+ try {
+ String command_line = "diff -u expected/" + getTestName() + " result/" +
+ getTestName();
+ Process proc;
+
+ proc = Runtime.getRuntime().exec(command_line);
+ proc.waitFor();
+ System.out.print(getTestName() + ": ");
+ if (proc.exitValue() == 0)
+ System.out.println("ok");
+ else if (proc.exitValue() == 0)
+ System.out.println("FAIL");
+ else
+ System.out.println("unknown error" + proc.exitValue());
+ } catch (Exception e) {
+ e.printStackTrace();
+ }
+ }
+
+ public abstract void do_test() throws SQLException, IOException;
+ public abstract String getTestName();
+}
diff --git a/test/jdbc/RunTest.java b/test/jdbc/RunTest.java
new file mode 100644
index 000000000..018f1b7a0
--- /dev/null
+++ b/test/jdbc/RunTest.java
@@ -0,0 +1,77 @@
+import java.sql.*;
+import java.util.*;
+import java.io.*;
+
+public class RunTest {
+ public static void main(String[] args)
+ {
+ RunTest test = new RunTest();
+ test.run();
+ }
+
+ public void run()
+ {
+ try {
+ PgpoolTest test;
+ Properties prop = new Properties();
+ prop.load(new FileInputStream("pgpool.properties"));
+ String tests = prop.getProperty("pgpooltest.tests");
+ String host = prop.getProperty("pgpooltest.host");
+ String port = prop.getProperty("pgpooltest.port");
+ String user = prop.getProperty("pgpooltest.user");
+ String password = prop.getProperty("pgpooltest.password");
+ String dbname = prop.getProperty("pgpooltest.dbname");
+
+ // setup database
+ String command_line = "psql -f prepare.sql";
+ command_line = command_line + " -h " + host + " -p " + port +
+ " -U " + user + " " + dbname;
+ Process proc = Runtime.getRuntime().exec(command_line);
+ proc.waitFor();
+
+ StringTokenizer tokenizer = new StringTokenizer(tests, " ");
+
+ while(tokenizer.hasMoreTokens()) {
+ test = testFactory(tokenizer.nextToken());
+ if (test == null) {
+ System.out.println("unknown testcase");
+ System.exit(1);
+ }
+ test.do_test();
+ test.check();
+ }
+ } catch (IOException e) {
+ System.out.println("cannot read property file");
+ System.exit(1);
+ } catch (Exception e) {
+ e.printStackTrace();
+ }
+ }
+
+ public PgpoolTest testFactory(String testcase)
+ {
+ if (testcase == null)
+ return null;
+
+ if (testcase.equals("autocommit"))
+ return new AutoCommitTest();
+
+ if (testcase.equals("batch"))
+ return new BatchTest();
+
+ if (testcase.equals("column"))
+ return new ColumnTest();
+
+ if (testcase.equals("lock"))
+ return new LockTest();
+
+ if (testcase.equals("select"))
+ return new SelectTest();
+
+ if (testcase.equals("update"))
+ return new UpdateTest();
+
+ /* unknown test case */
+ return null;
+ }
+}
diff --git a/test/jdbc/SelectTest.java b/test/jdbc/SelectTest.java
new file mode 100644
index 000000000..86dcb8a2d
--- /dev/null
+++ b/test/jdbc/SelectTest.java
@@ -0,0 +1,37 @@
+import java.sql.*;
+import java.io.*;
+
+public class SelectTest extends PgpoolTest {
+ public void do_test() throws SQLException {
+ try {
+ for (int i = 0; i < 1; i++) {
+ connection.setAutoCommit(false);
+ Statement stmt = null;
+ PreparedStatement pstmt = null;
+ ResultSet rs = null;
+
+ pstmt = connection.prepareStatement("SELECT count(*) FROM sel");
+ rs = pstmt.executeQuery();
+ rs.next();
+ logwriter.println(rs.getInt(1));
+ rs.close();
+ pstmt.close();
+
+ pstmt = connection.prepareStatement("SELECT sum(a) FROM sel");
+ rs = pstmt.executeQuery();
+ rs.next();
+ logwriter.println(rs.getInt(1));
+ rs.close();
+ pstmt.close();
+ connection.commit();
+ }
+ } finally {
+ connection.close();
+ logwriter.close();
+ }
+ }
+
+ public String getTestName() {
+ return "select";
+ }
+}
diff --git a/test/jdbc/UpdateTest.java b/test/jdbc/UpdateTest.java
new file mode 100644
index 000000000..7284c2af5
--- /dev/null
+++ b/test/jdbc/UpdateTest.java
@@ -0,0 +1,32 @@
+import java.sql.*;
+
+public class UpdateTest extends PgpoolTest {
+ public void do_test() throws SQLException {
+ int N = 100;
+
+ try {
+ ResultSet rs;
+ Statement stmt = null;
+
+ for (int i = 0; i < N; i++) {
+ connection.setAutoCommit(false);
+ stmt = connection.createStatement();
+ stmt.executeUpdate("UPDATE up SET a = a + 1" );
+ stmt.close();
+ connection.commit();
+ }
+ connection.setAutoCommit(true);
+ stmt = connection.createStatement();
+ rs = stmt.executeQuery("SELECT max(a) FROM up" );
+ rs.next();
+ logwriter.println(rs.getInt(1));
+ }
+ finally {
+ connection.close();
+ }
+ }
+
+ public String getTestName() {
+ return "update";
+ }
+}
diff --git a/test/jdbc/expected/autocommit b/test/jdbc/expected/autocommit
new file mode 100644
index 000000000..918e94db8
--- /dev/null
+++ b/test/jdbc/expected/autocommit
@@ -0,0 +1,10 @@
+1
+2
+3
+4
+5
+6
+7
+8
+9
+10
diff --git a/test/jdbc/expected/batch b/test/jdbc/expected/batch
new file mode 100644
index 000000000..b4de39476
--- /dev/null
+++ b/test/jdbc/expected/batch
@@ -0,0 +1 @@
+11
diff --git a/test/jdbc/expected/column b/test/jdbc/expected/column
new file mode 100644
index 000000000..f6d8fb48f
--- /dev/null
+++ b/test/jdbc/expected/column
@@ -0,0 +1,100 @@
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
diff --git a/test/jdbc/expected/lock b/test/jdbc/expected/lock
new file mode 100644
index 000000000..f6d8fb48f
--- /dev/null
+++ b/test/jdbc/expected/lock
@@ -0,0 +1,100 @@
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
diff --git a/test/jdbc/expected/select b/test/jdbc/expected/select
new file mode 100644
index 000000000..7fe69129e
--- /dev/null
+++ b/test/jdbc/expected/select
@@ -0,0 +1,2 @@
+100
+5050
diff --git a/test/jdbc/expected/update b/test/jdbc/expected/update
new file mode 100644
index 000000000..257e56326
--- /dev/null
+++ b/test/jdbc/expected/update
@@ -0,0 +1 @@
+102
diff --git a/test/jdbc/pgpool.properties b/test/jdbc/pgpool.properties
new file mode 100644
index 000000000..a55e06433
--- /dev/null
+++ b/test/jdbc/pgpool.properties
@@ -0,0 +1,7 @@
+pgpooltest.host=localhost
+pgpooltest.port=9999
+pgpooltest.user=pgpool
+pgpooltest.password=
+pgpooltest.dbname=pgpooltest
+pgpooltest.options=
+pgpooltest.tests=autocommit batch column lock select update
diff --git a/test/jdbc/prepare.sql b/test/jdbc/prepare.sql
new file mode 100644
index 000000000..336515f61
--- /dev/null
+++ b/test/jdbc/prepare.sql
@@ -0,0 +1,50 @@
+-- autocommit
+DROP TABLE autocommit;
+CREATE TABLE autocommit (a int);
+INSERT INTO autocommit SELECT generate_series(1, 10);
+
+-- batch
+DROP TABLE batch;
+CREATE TABLE batch (a int);
+
+-- column
+DROP TABLE columntest;
+CREATE TABLE columntest
+(
+ v1 int4,
+ v2 int4,
+ v3 int4,
+ v4 int4,
+ v5 int4,
+ v6 int4,
+ v7 int4,
+ v8 int4,
+ v9 int4,
+ v10 int4,
+ v11 int4,
+ v12 int4,
+ v13 int4,
+ v14 int4,
+ v15 int4)
+WITHOUT OIDS;
+INSERT INTO columntest VALUES (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15);
+
+-- lock
+DROP TABLE locktest;
+CREATE TABLE locktest (a int);
+INSERT INTO locktest VALUES (1);
+INSERT INTO locktest VALUES (2);
+INSERT INTO locktest VALUES (3);
+INSERT INTO locktest VALUES (4);
+INSERT INTO locktest VALUES (5);
+
+-- select
+DROP TABLE sel;
+CREATE TABLE sel (a int);
+INSERT INTO sel SELECT generate_series(1, 100);
+
+-- update
+DROP TABLE up;
+CREATE TABLE up (a int);
+INSERT INTO up VALUES (1);
+INSERT INTO up VALUES (2);
diff --git a/test/jdbc/run.sh b/test/jdbc/run.sh
new file mode 100755
index 000000000..319f246b3
--- /dev/null
+++ b/test/jdbc/run.sh
@@ -0,0 +1,6 @@
+#! /bin/sh
+
+rm -f *.class
+rm -f result/*
+javac *.java
+java RunTest
[parent: 8efb571bb208]