From e162542c19ca2b380a9d71aa5527ccef92bb71ea Mon Sep 17 00:00:00 2001 From: leo Date: Tue, 11 Mar 2008 02:13:11 +0000 Subject: [PATCH] added test codes git-svn-id: http://www.xerial.org/svn/project/XerialJ/trunk/sqlite-jdbc@2027 ae02f08e-27ec-0310-ae8c-8ba02fe2eafd --- pom.xml | 2 +- src/test/java/org/sqlite/ConnectionTest.java | 45 ++ src/test/java/org/sqlite/DBMetaDataTest.java | 461 ++++++++++++++++++ src/test/java/org/sqlite/PrepStmtTest.java | 529 +++++++++++++++++++++ src/test/java/org/sqlite/RSMetaDataTest.java | 104 ++++ src/test/java/org/sqlite/StatementTest.java | 322 +++++++++++++ src/test/java/org/sqlite/TransactionTest.java | 284 +++++++++++ src/test/java/org/sqlite/UDFTest.java | 480 +++++++++++++++++++ .../xerial/db/sql/sqlite/SQLiteJDBCLoaderTest.java | 52 +- 9 files changed, 2257 insertions(+), 22 deletions(-) create mode 100644 src/test/java/org/sqlite/ConnectionTest.java create mode 100644 src/test/java/org/sqlite/DBMetaDataTest.java create mode 100644 src/test/java/org/sqlite/PrepStmtTest.java create mode 100644 src/test/java/org/sqlite/RSMetaDataTest.java create mode 100644 src/test/java/org/sqlite/StatementTest.java create mode 100644 src/test/java/org/sqlite/TransactionTest.java create mode 100644 src/test/java/org/sqlite/UDFTest.java diff --git a/pom.xml b/pom.xml index 041ab73..e38e8d5 100644 --- a/pom.xml +++ b/pom.xml @@ -13,7 +13,7 @@ 4.0.0 org.xerial sqlite-jdbc - v042 + v042.1-SNAPSHOT SQLite JDBC SQLite JDBC library diff --git a/src/test/java/org/sqlite/ConnectionTest.java b/src/test/java/org/sqlite/ConnectionTest.java new file mode 100644 index 0000000..b5b2d3f --- /dev/null +++ b/src/test/java/org/sqlite/ConnectionTest.java @@ -0,0 +1,45 @@ +package org.sqlite; + +import java.io.File; +import java.sql.*; +import org.junit.*; +import static org.junit.Assert.*; + +/** These tests check whether access to files is woring correctly and + * some Connection.close() cases. */ +public class ConnectionTest +{ + @BeforeClass public static void forName() throws Exception { + Class.forName("org.sqlite.JDBC"); + } + + @Test public void openMemory() throws SQLException { + Connection conn = DriverManager.getConnection("jdbc:sqlite:"); + conn.close(); + } + + @Test public void isClosed() throws SQLException { + Connection conn = DriverManager.getConnection("jdbc:sqlite:"); + conn.close(); + assertTrue(conn.isClosed()); + } + + @Test public void openFile() throws SQLException { + File testdb = new File("test.db"); + if (testdb.exists()) testdb.delete(); + assertFalse(testdb.exists()); + Connection conn = DriverManager.getConnection("jdbc:sqlite:test.db"); + conn.close(); + assertTrue(testdb.exists()); + testdb.delete(); + } + + @Test(expected= SQLException.class) + public void closeTest() throws SQLException { + Connection conn = DriverManager.getConnection("jdbc:sqlite:"); + PreparedStatement prep = conn.prepareStatement("select null;"); + ResultSet rs = prep.executeQuery(); + conn.close(); + prep.clearParameters(); + } +} diff --git a/src/test/java/org/sqlite/DBMetaDataTest.java b/src/test/java/org/sqlite/DBMetaDataTest.java new file mode 100644 index 0000000..e7b8692 --- /dev/null +++ b/src/test/java/org/sqlite/DBMetaDataTest.java @@ -0,0 +1,461 @@ +package org.sqlite; + +import static org.junit.Assert.assertEquals; +import static org.junit.Assert.assertFalse; +import static org.junit.Assert.assertNotNull; +import static org.junit.Assert.assertTrue; + +import java.sql.Connection; +import java.sql.DatabaseMetaData; +import java.sql.DriverManager; +import java.sql.ResultSet; +import java.sql.ResultSetMetaData; +import java.sql.SQLException; +import java.sql.Statement; + +import org.junit.After; +import org.junit.Before; +import org.junit.BeforeClass; +import org.junit.Test; + +/** These tests are designed to stress Statements on memory databases. */ +public class DBMetaDataTest +{ + private Connection conn; + private Statement stat; + private DatabaseMetaData meta; + + @BeforeClass + public static void forName() throws Exception + { + Class.forName("org.sqlite.JDBC"); + } + + @Before + public void connect() throws Exception + { + conn = DriverManager.getConnection("jdbc:sqlite:"); + stat = conn.createStatement(); + stat.executeUpdate("create table test (id integer primary key, fn, sn);"); + stat.executeUpdate("create view testView as select * from test;"); + meta = conn.getMetaData(); + } + + @After + public void close() throws SQLException + { + meta = null; + stat.close(); + conn.close(); + } + + @Test + public void getTables() throws SQLException + { + ResultSet rs = meta.getTables(null, null, null, null); + assertNotNull(rs); + assertTrue(rs.next()); + assertEquals(rs.getString("TABLE_NAME"), "TEST"); // 3 + assertEquals(rs.getString("TABLE_TYPE"), "TABLE"); // 4 + assertTrue(rs.next()); + assertEquals(rs.getString("TABLE_NAME"), "TESTVIEW"); + assertEquals(rs.getString("TABLE_TYPE"), "VIEW"); + rs.close(); + + rs = meta.getTables(null, null, "bob", null); + assertFalse(rs.next()); + rs.close(); + rs = meta.getTables(null, null, "test", null); + assertTrue(rs.next()); + assertFalse(rs.next()); + rs.close(); + rs = meta.getTables(null, null, "test%", null); + assertTrue(rs.next()); + assertTrue(rs.next()); + rs.close(); + + rs = meta.getTables(null, null, null, new String[] { "table" }); + assertTrue(rs.next()); + assertEquals(rs.getString("TABLE_NAME"), "TEST"); + assertFalse(rs.next()); + rs.close(); + + rs = meta.getTables(null, null, null, new String[] { "view" }); + assertTrue(rs.next()); + assertEquals(rs.getString("TABLE_NAME"), "TESTVIEW"); + assertFalse(rs.next()); + rs.close(); + } + + @Test + public void getTableTypes() throws SQLException + { + ResultSet rs = meta.getTableTypes(); + assertNotNull(rs); + assertTrue(rs.next()); + assertEquals(rs.getString("TABLE_TYPE"), "TABLE"); + assertTrue(rs.next()); + assertEquals(rs.getString("TABLE_TYPE"), "VIEW"); + assertFalse(rs.next()); + } + + @Test + public void getTypeInfo() throws SQLException + { + ResultSet rs = meta.getTypeInfo(); + assertNotNull(rs); + assertTrue(rs.next()); + assertEquals(rs.getString("TYPE_NAME"), "BLOB"); + assertTrue(rs.next()); + assertEquals(rs.getString("TYPE_NAME"), "INTEGER"); + assertTrue(rs.next()); + assertEquals(rs.getString("TYPE_NAME"), "NULL"); + assertTrue(rs.next()); + assertEquals(rs.getString("TYPE_NAME"), "REAL"); + assertTrue(rs.next()); + assertEquals(rs.getString("TYPE_NAME"), "TEXT"); + assertFalse(rs.next()); + } + + @Test + public void getColumns() throws SQLException + { + ResultSet rs = meta.getColumns(null, null, "test", "id"); + assertTrue(rs.next()); + assertEquals(rs.getString("TABLE_NAME"), "test"); + assertEquals(rs.getString("COLUMN_NAME"), "id"); + assertFalse(rs.next()); + + rs = meta.getColumns(null, null, "test", "fn"); + assertTrue(rs.next()); + assertEquals(rs.getString("COLUMN_NAME"), "fn"); + assertFalse(rs.next()); + + rs = meta.getColumns(null, null, "test", "sn"); + assertTrue(rs.next()); + assertEquals(rs.getString("COLUMN_NAME"), "sn"); + assertFalse(rs.next()); + + rs = meta.getColumns(null, null, "test", "%"); + assertTrue(rs.next()); + assertEquals(rs.getString("COLUMN_NAME"), "id"); + assertTrue(rs.next()); + assertEquals(rs.getString("COLUMN_NAME"), "fn"); + assertTrue(rs.next()); + assertEquals(rs.getString("COLUMN_NAME"), "sn"); + assertFalse(rs.next()); + + rs = meta.getColumns(null, null, "test", "%n"); + assertTrue(rs.next()); + assertEquals(rs.getString("COLUMN_NAME"), "fn"); + assertTrue(rs.next()); + assertEquals(rs.getString("COLUMN_NAME"), "sn"); + assertFalse(rs.next()); + + rs = meta.getColumns(null, null, "test%", "%"); + assertTrue(rs.next()); + assertEquals(rs.getString("COLUMN_NAME"), "id"); + assertTrue(rs.next()); + assertEquals(rs.getString("COLUMN_NAME"), "fn"); + assertTrue(rs.next()); + assertEquals(rs.getString("COLUMN_NAME"), "sn"); + assertFalse(rs.next()); + + rs = meta.getColumns(null, null, "%", "%"); + assertTrue(rs.next()); + assertEquals(rs.getString("TABLE_NAME"), "test"); + assertEquals(rs.getString("COLUMN_NAME"), "id"); + assertTrue(rs.next()); + assertEquals(rs.getString("COLUMN_NAME"), "fn"); + assertTrue(rs.next()); + assertEquals(rs.getString("COLUMN_NAME"), "sn"); + assertFalse(rs.next()); + } + + @Test + public void columnOrderOfgetTables() throws SQLException + { + ResultSet rs = meta.getTables(null, null, null, null); + assertTrue(rs.next()); + ResultSetMetaData rsmeta = rs.getMetaData(); + assertEquals(rsmeta.getColumnCount(), 10); + assertEquals(rsmeta.getColumnName(1), "TABLE_CAT"); + assertEquals(rsmeta.getColumnName(2), "TABLE_SCHEM"); + assertEquals(rsmeta.getColumnName(3), "TABLE_NAME"); + assertEquals(rsmeta.getColumnName(4), "TABLE_TYPE"); + assertEquals(rsmeta.getColumnName(5), "REMARKS"); + assertEquals(rsmeta.getColumnName(6), "TYPE_CAT"); + assertEquals(rsmeta.getColumnName(7), "TYPE_SCHEM"); + assertEquals(rsmeta.getColumnName(8), "TYPE_NAME"); + assertEquals(rsmeta.getColumnName(9), "SELF_REFERENCING_COL_NAME"); + assertEquals(rsmeta.getColumnName(10), "REF_GENERATION"); + } + + @Test + public void columnOrderOfgetTableTypes() throws SQLException + { + ResultSet rs = meta.getTableTypes(); + assertTrue(rs.next()); + ResultSetMetaData rsmeta = rs.getMetaData(); + assertEquals(rsmeta.getColumnCount(), 1); + assertEquals(rsmeta.getColumnName(1), "TABLE_TYPE"); + } + + @Test + public void columnOrderOfgetTypeInfo() throws SQLException + { + ResultSet rs = meta.getTypeInfo(); + assertTrue(rs.next()); + ResultSetMetaData rsmeta = rs.getMetaData(); + assertEquals(rsmeta.getColumnCount(), 18); + assertEquals(rsmeta.getColumnName(1), "TYPE_NAME"); + assertEquals(rsmeta.getColumnName(2), "DATA_TYPE"); + assertEquals(rsmeta.getColumnName(3), "PRECISION"); + assertEquals(rsmeta.getColumnName(4), "LITERAL_PREFIX"); + assertEquals(rsmeta.getColumnName(5), "LITERAL_SUFFIX"); + assertEquals(rsmeta.getColumnName(6), "CREATE_PARAMS"); + assertEquals(rsmeta.getColumnName(7), "NULLABLE"); + assertEquals(rsmeta.getColumnName(8), "CASE_SENSITIVE"); + assertEquals(rsmeta.getColumnName(9), "SEARCHABLE"); + assertEquals(rsmeta.getColumnName(10), "UNSIGNED_ATTRIBUTE"); + assertEquals(rsmeta.getColumnName(11), "FIXED_PREC_SCALE"); + assertEquals(rsmeta.getColumnName(12), "AUTO_INCREMENT"); + assertEquals(rsmeta.getColumnName(13), "LOCAL_TYPE_NAME"); + assertEquals(rsmeta.getColumnName(14), "MINIMUM_SCALE"); + assertEquals(rsmeta.getColumnName(15), "MAXIMUM_SCALE"); + assertEquals(rsmeta.getColumnName(16), "SQL_DATA_TYPE"); + assertEquals(rsmeta.getColumnName(17), "SQL_DATETIME_SUB"); + assertEquals(rsmeta.getColumnName(18), "NUM_PREC_RADIX"); + } + + @Test + public void columnOrderOfgetColumns() throws SQLException + { + ResultSet rs = meta.getColumns(null, null, "test", null); + assertTrue(rs.next()); + ResultSetMetaData rsmeta = rs.getMetaData(); + assertEquals(rsmeta.getColumnCount(), 22); + assertEquals(rsmeta.getColumnName(1), "TABLE_CAT"); + assertEquals(rsmeta.getColumnName(2), "TABLE_SCHEM"); + assertEquals(rsmeta.getColumnName(3), "TABLE_NAME"); + assertEquals(rsmeta.getColumnName(4), "COLUMN_NAME"); + assertEquals(rsmeta.getColumnName(5), "DATA_TYPE"); + assertEquals(rsmeta.getColumnName(6), "TYPE_NAME"); + assertEquals(rsmeta.getColumnName(7), "COLUMN_SIZE"); + assertEquals(rsmeta.getColumnName(8), "BUFFER_LENGTH"); + assertEquals(rsmeta.getColumnName(9), "DECIMAL_DIGITS"); + assertEquals(rsmeta.getColumnName(10), "NUM_PREC_RADIX"); + assertEquals(rsmeta.getColumnName(11), "NULLABLE"); + assertEquals(rsmeta.getColumnName(12), "REMARKS"); + assertEquals(rsmeta.getColumnName(13), "COLUMN_DEF"); + assertEquals(rsmeta.getColumnName(14), "SQL_DATA_TYPE"); + assertEquals(rsmeta.getColumnName(15), "SQL_DATETIME_SUB"); + assertEquals(rsmeta.getColumnName(16), "CHAR_OCTET_LENGTH"); + assertEquals(rsmeta.getColumnName(17), "ORDINAL_POSITION"); + assertEquals(rsmeta.getColumnName(18), "IS_NULLABLE"); + // should be SCOPE_CATALOG, but misspelt in the standard + assertEquals(rsmeta.getColumnName(19), "SCOPE_CATLOG"); + assertEquals(rsmeta.getColumnName(20), "SCOPE_SCHEMA"); + assertEquals(rsmeta.getColumnName(21), "SCOPE_TABLE"); + assertEquals(rsmeta.getColumnName(22), "SOURCE_DATA_TYPE"); + } + + // the following functions always return an empty resultset, so + // do not bother testing their parameters, only the column types + + @Test + public void columnOrderOfgetProcedures() throws SQLException + { + ResultSet rs = meta.getProcedures(null, null, null); + assertFalse(rs.next()); + ResultSetMetaData rsmeta = rs.getMetaData(); + assertEquals(rsmeta.getColumnCount(), 8); + assertEquals(rsmeta.getColumnName(1), "PROCEDURE_CAT"); + assertEquals(rsmeta.getColumnName(2), "PROCEDURE_SCHEM"); + assertEquals(rsmeta.getColumnName(3), "PROCEDURE_NAME"); + // currently (Java 1.5), cols 4,5,6 are undefined + assertEquals(rsmeta.getColumnName(7), "REMARKS"); + assertEquals(rsmeta.getColumnName(8), "PROCEDURE_TYPE"); + } + + @Test + public void columnOrderOfgetProcedurColumns() throws SQLException + { + ResultSet rs = meta.getProcedureColumns(null, null, null, null); + assertFalse(rs.next()); + ResultSetMetaData rsmeta = rs.getMetaData(); + assertEquals(rsmeta.getColumnCount(), 13); + assertEquals(rsmeta.getColumnName(1), "PROCEDURE_CAT"); + assertEquals(rsmeta.getColumnName(2), "PROCEDURE_SCHEM"); + assertEquals(rsmeta.getColumnName(3), "PROCEDURE_NAME"); + assertEquals(rsmeta.getColumnName(4), "COLUMN_NAME"); + assertEquals(rsmeta.getColumnName(5), "COLUMN_TYPE"); + assertEquals(rsmeta.getColumnName(6), "DATA_TYPE"); + assertEquals(rsmeta.getColumnName(7), "TYPE_NAME"); + assertEquals(rsmeta.getColumnName(8), "PRECISION"); + assertEquals(rsmeta.getColumnName(9), "LENGTH"); + assertEquals(rsmeta.getColumnName(10), "SCALE"); + assertEquals(rsmeta.getColumnName(11), "RADIX"); + assertEquals(rsmeta.getColumnName(12), "NULLABLE"); + assertEquals(rsmeta.getColumnName(13), "REMARKS"); + } + + @Test + public void columnOrderOfgetSchemas() throws SQLException + { + ResultSet rs = meta.getSchemas(); + assertFalse(rs.next()); + ResultSetMetaData rsmeta = rs.getMetaData(); + assertEquals(rsmeta.getColumnCount(), 2); + assertEquals(rsmeta.getColumnName(1), "TABLE_SCHEM"); + assertEquals(rsmeta.getColumnName(2), "TABLE_CATALOG"); + } + + @Test + public void columnOrderOfgetCatalogs() throws SQLException + { + ResultSet rs = meta.getCatalogs(); + assertFalse(rs.next()); + ResultSetMetaData rsmeta = rs.getMetaData(); + assertEquals(rsmeta.getColumnCount(), 1); + assertEquals(rsmeta.getColumnName(1), "TABLE_CAT"); + } + + @Test + public void columnOrderOfgetColumnPrivileges() throws SQLException + { + ResultSet rs = meta.getColumnPrivileges(null, null, null, null); + assertFalse(rs.next()); + ResultSetMetaData rsmeta = rs.getMetaData(); + assertEquals(rsmeta.getColumnCount(), 8); + assertEquals(rsmeta.getColumnName(1), "TABLE_CAT"); + assertEquals(rsmeta.getColumnName(2), "TABLE_SCHEM"); + assertEquals(rsmeta.getColumnName(3), "TABLE_NAME"); + assertEquals(rsmeta.getColumnName(4), "COLUMN_NAME"); + assertEquals(rsmeta.getColumnName(5), "GRANTOR"); + assertEquals(rsmeta.getColumnName(6), "GRANTEE"); + assertEquals(rsmeta.getColumnName(7), "PRIVILEGE"); + assertEquals(rsmeta.getColumnName(8), "IS_GRANTABLE"); + } + + @Test + public void columnOrderOfgetTablePrivileges() throws SQLException + { + ResultSet rs = meta.getTablePrivileges(null, null, null); + assertFalse(rs.next()); + ResultSetMetaData rsmeta = rs.getMetaData(); + assertEquals(rsmeta.getColumnCount(), 7); + assertEquals(rsmeta.getColumnName(1), "TABLE_CAT"); + assertEquals(rsmeta.getColumnName(2), "TABLE_SCHEM"); + assertEquals(rsmeta.getColumnName(3), "TABLE_NAME"); + assertEquals(rsmeta.getColumnName(4), "GRANTOR"); + assertEquals(rsmeta.getColumnName(5), "GRANTEE"); + assertEquals(rsmeta.getColumnName(6), "PRIVILEGE"); + assertEquals(rsmeta.getColumnName(7), "IS_GRANTABLE"); + } + + @Test + public void columnOrderOfgetBestRowIdentifier() throws SQLException + { + ResultSet rs = meta.getBestRowIdentifier(null, null, null, 0, false); + assertFalse(rs.next()); + ResultSetMetaData rsmeta = rs.getMetaData(); + assertEquals(rsmeta.getColumnCount(), 8); + assertEquals(rsmeta.getColumnName(1), "SCOPE"); + assertEquals(rsmeta.getColumnName(2), "COLUMN_NAME"); + assertEquals(rsmeta.getColumnName(3), "DATA_TYPE"); + assertEquals(rsmeta.getColumnName(4), "TYPE_NAME"); + assertEquals(rsmeta.getColumnName(5), "COLUMN_SIZE"); + assertEquals(rsmeta.getColumnName(6), "BUFFER_LENGTH"); + assertEquals(rsmeta.getColumnName(7), "DECIMAL_DIGITS"); + assertEquals(rsmeta.getColumnName(8), "PSEUDO_COLUMN"); + } + + @Test + public void columnOrderOfgetVersionColumns() throws SQLException + { + ResultSet rs = meta.getVersionColumns(null, null, null); + assertFalse(rs.next()); + ResultSetMetaData rsmeta = rs.getMetaData(); + assertEquals(rsmeta.getColumnCount(), 8); + assertEquals(rsmeta.getColumnName(1), "SCOPE"); + assertEquals(rsmeta.getColumnName(2), "COLUMN_NAME"); + assertEquals(rsmeta.getColumnName(3), "DATA_TYPE"); + assertEquals(rsmeta.getColumnName(4), "TYPE_NAME"); + assertEquals(rsmeta.getColumnName(5), "COLUMN_SIZE"); + assertEquals(rsmeta.getColumnName(6), "BUFFER_LENGTH"); + assertEquals(rsmeta.getColumnName(7), "DECIMAL_DIGITS"); + assertEquals(rsmeta.getColumnName(8), "PSEUDO_COLUMN"); + } + + @Test + public void columnOrderOfgetPrimaryKeys() throws SQLException + { + ResultSet rs; + ResultSetMetaData rsmeta; + + stat.executeUpdate("create table nopk (c1, c2, c3, c4);"); + stat.executeUpdate("create table pk1 (col1 primary key, col2, col3);"); + stat.executeUpdate("create table pk2 (col1, col2 primary key, col3);"); + stat.executeUpdate("create table pk3 (col1, col2, col3, col4, " + "primary key (col2, col3));"); + + rs = meta.getPrimaryKeys(null, null, "nopk"); + assertFalse(rs.next()); + rsmeta = rs.getMetaData(); + assertEquals(rsmeta.getColumnCount(), 6); + assertEquals(rsmeta.getColumnName(1), "TABLE_CAT"); + assertEquals(rsmeta.getColumnName(2), "TABLE_SCHEM"); + assertEquals(rsmeta.getColumnName(3), "TABLE_NAME"); + assertEquals(rsmeta.getColumnName(4), "COLUMN_NAME"); + assertEquals(rsmeta.getColumnName(5), "KEY_SEQ"); + assertEquals(rsmeta.getColumnName(6), "PK_NAME"); + rs.close(); + + rs = meta.getPrimaryKeys(null, null, "pk1"); + assertTrue(rs.next()); + assertEquals(rs.getString("COLUMN_NAME"), "col1"); + assertFalse(rs.next()); + rs.close(); + + rs = meta.getPrimaryKeys(null, null, "pk2"); + assertTrue(rs.next()); + assertEquals(rs.getString("COLUMN_NAME"), "col2"); + assertFalse(rs.next()); + rs.close(); + + rs = meta.getPrimaryKeys(null, null, "pk3"); + assertTrue(rs.next()); + assertEquals(rs.getString("COLUMN_NAME"), "col2"); + assertTrue(rs.next()); + assertEquals(rs.getString("COLUMN_NAME"), "col3"); + assertFalse(rs.next()); + rs.close(); + } + + /* + * TODO @Test public void columnOrderOfgetImportedKeys() throws SQLException { + * @Test public void columnOrderOfgetExportedKeys() throws SQLException { + * @Test public void columnOrderOfgetCrossReference() throws SQLException { + * @Test public void columnOrderOfgetTypeInfo() throws SQLException { @Test + * public void columnOrderOfgetIndexInfo() throws SQLException { @Test + * public void columnOrderOfgetSuperTypes() throws SQLException { @Test + * public void columnOrderOfgetSuperTables() throws SQLException { @Test + * public void columnOrderOfgetAttributes() throws SQLException { + */ + + @Test + public void columnOrderOfgetUDTs() throws SQLException + { + ResultSet rs = meta.getUDTs(null, null, null, null); + assertFalse(rs.next()); + ResultSetMetaData rsmeta = rs.getMetaData(); + assertEquals(rsmeta.getColumnCount(), 7); + assertEquals(rsmeta.getColumnName(1), "TYPE_CAT"); + assertEquals(rsmeta.getColumnName(2), "TYPE_SCHEM"); + assertEquals(rsmeta.getColumnName(3), "TYPE_NAME"); + assertEquals(rsmeta.getColumnName(4), "CLASS_NAME"); + assertEquals(rsmeta.getColumnName(5), "DATA_TYPE"); + assertEquals(rsmeta.getColumnName(6), "REMARKS"); + assertEquals(rsmeta.getColumnName(7), "BASE_TYPE"); + } +} diff --git a/src/test/java/org/sqlite/PrepStmtTest.java b/src/test/java/org/sqlite/PrepStmtTest.java new file mode 100644 index 0000000..066e54e --- /dev/null +++ b/src/test/java/org/sqlite/PrepStmtTest.java @@ -0,0 +1,529 @@ +package org.sqlite; + +import static org.junit.Assert.assertEquals; +import static org.junit.Assert.assertFalse; +import static org.junit.Assert.assertNotNull; +import static org.junit.Assert.assertNull; +import static org.junit.Assert.assertTrue; + +import java.sql.Connection; +import java.sql.Date; +import java.sql.DriverManager; +import java.sql.PreparedStatement; +import java.sql.ResultSet; +import java.sql.ResultSetMetaData; +import java.sql.SQLException; +import java.sql.Statement; +import java.util.StringTokenizer; + +import org.junit.After; +import org.junit.Before; +import org.junit.BeforeClass; +import org.junit.Ignore; +import org.junit.Test; + +/** These tests are designed to stress PreparedStatements on memory dbs. */ +public class PrepStmtTest +{ + static byte[] b1 = new byte[] { 1, 2, 7, 4, 2, 6, 2, 8, 5, 2, 3, 1, 5, 3, 6, 3, 3, 6, 2, 5 }; + static byte[] b2 = "To be or not to be.".getBytes(); + static byte[] b3 = "Question!#$%".getBytes(); + static String utf01 = "\uD840\uDC40"; + static String utf02 = "\uD840\uDC47 "; + static String utf03 = " \uD840\uDC43"; + static String utf04 = " \uD840\uDC42 "; + static String utf05 = "\uD840\uDC40\uD840\uDC44"; + static String utf06 = "Hello World, \uD840\uDC40 \uD880\uDC99"; + static String utf07 = "\uD840\uDC41 testing \uD880\uDC99"; + static String utf08 = "\uD840\uDC40\uD840\uDC44 testing"; + + private Connection conn; + private Statement stat; + + @BeforeClass + public static void forName() throws Exception + { + Class.forName("org.sqlite.JDBC"); + } + + @Before + public void connect() throws Exception + { + conn = DriverManager.getConnection("jdbc:sqlite:"); + stat = conn.createStatement(); + } + + @After + public void close() throws SQLException + { + stat.close(); + conn.close(); + } + + @Test + public void update() throws SQLException + { + assertEquals(conn.prepareStatement("create table s1 (c1);").executeUpdate(), 0); + PreparedStatement prep = conn.prepareStatement("insert into s1 values (?);"); + prep.setInt(1, 3); + assertEquals(prep.executeUpdate(), 1); + prep.setInt(1, 5); + assertEquals(prep.executeUpdate(), 1); + prep.setInt(1, 7); + assertEquals(prep.executeUpdate(), 1); + prep.close(); + + // check results with normal statement + ResultSet rs = stat.executeQuery("select sum(c1) from s1;"); + assertTrue(rs.next()); + assertEquals(rs.getInt(1), 15); + rs.close(); + } + + @Test + public void multiUpdate() throws SQLException + { + stat.executeUpdate("create table test (c1);"); + PreparedStatement prep = conn.prepareStatement("insert into test values (?);"); + + for (int i = 0; i < 10; i++) + { + prep.setInt(1, i); + prep.executeUpdate(); + prep.execute(); + } + + prep.close(); + stat.executeUpdate("drop table test;"); + } + + @Test + public void emptyRS() throws SQLException + { + PreparedStatement prep = conn.prepareStatement("select null limit 0;"); + ResultSet rs = prep.executeQuery(); + assertFalse(rs.next()); + rs.close(); + prep.close(); + } + + @Test + public void singleRowRS() throws SQLException + { + PreparedStatement prep = conn.prepareStatement("select ?;"); + prep.setInt(1, Integer.MAX_VALUE); + ResultSet rs = prep.executeQuery(); + assertTrue(rs.next()); + assertEquals(rs.getInt(1), Integer.MAX_VALUE); + assertEquals(rs.getString(1), Integer.toString(Integer.MAX_VALUE)); + assertEquals(rs.getDouble(1), new Integer(Integer.MAX_VALUE).doubleValue()); + assertFalse(rs.next()); + rs.close(); + prep.close(); + } + + @Test + public void twoRowRS() throws SQLException + { + PreparedStatement prep = conn.prepareStatement("select ? union all select ?;"); + prep.setDouble(1, Double.MAX_VALUE); + prep.setDouble(2, Double.MIN_VALUE); + ResultSet rs = prep.executeQuery(); + assertTrue(rs.next()); + assertEquals(rs.getDouble(1), Double.MAX_VALUE); + assertTrue(rs.next()); + assertEquals(rs.getDouble(1), Double.MIN_VALUE); + assertFalse(rs.next()); + rs.close(); + } + + @Test + public void stringRS() throws SQLException + { + String name = "Gandhi"; + PreparedStatement prep = conn.prepareStatement("select ?;"); + prep.setString(1, name); + ResultSet rs = prep.executeQuery(); + assertTrue(rs.next()); + assertEquals(rs.getString(1), name); + assertFalse(rs.next()); + rs.close(); + } + + @Test + public void finalizePrep() throws SQLException + { + conn.prepareStatement("select null;"); + System.gc(); + } + + @Test + public void set() throws SQLException + { + ResultSet rs; + PreparedStatement prep = conn.prepareStatement("select ?, ?, ?;"); + + // integers + prep.setInt(1, Integer.MIN_VALUE); + prep.setInt(2, Integer.MAX_VALUE); + prep.setInt(3, 0); + rs = prep.executeQuery(); + assertTrue(rs.next()); + assertEquals(rs.getInt(1), Integer.MIN_VALUE); + assertEquals(rs.getInt(2), Integer.MAX_VALUE); + assertEquals(rs.getInt(3), 0); + + // strings + String name = "Winston Leonard Churchill"; + String fn = name.substring(0, 7), mn = name.substring(8, 15), sn = name.substring(16, 25); + prep.clearParameters(); + prep.setString(1, fn); + prep.setString(2, mn); + prep.setString(3, sn); + prep.executeQuery(); + assertTrue(rs.next()); + assertEquals(rs.getString(1), fn); + assertEquals(rs.getString(2), mn); + assertEquals(rs.getString(3), sn); + + // mixed + prep.setString(1, name); + prep.setString(2, null); + prep.setLong(3, Long.MAX_VALUE); + prep.executeQuery(); + assertTrue(rs.next()); + assertEquals(rs.getString(1), name); + assertNull(rs.getString(2)); + assertTrue(rs.wasNull()); + assertEquals(rs.getLong(3), Long.MAX_VALUE); + + // bytes + prep.setBytes(1, b1); + prep.setBytes(2, b2); + prep.setBytes(3, b3); + prep.executeQuery(); + assertTrue(rs.next()); + assertArrayEq(rs.getBytes(1), b1); + assertArrayEq(rs.getBytes(2), b2); + assertArrayEq(rs.getBytes(3), b3); + assertFalse(rs.next()); + rs.close(); + } + + @Test + public void colNameAccess() throws SQLException + { + PreparedStatement prep = conn.prepareStatement("select ? as col1, ? as col2, ? as bingo;"); + prep.setNull(1, 0); + prep.setFloat(2, Float.MIN_VALUE); + prep.setShort(3, Short.MIN_VALUE); + prep.executeQuery(); + ResultSet rs = prep.executeQuery(); + assertTrue(rs.next()); + assertNull(rs.getString("col1")); + assertTrue(rs.wasNull()); + assertEquals(rs.getFloat("col2"), Float.MIN_VALUE); + assertEquals(rs.getShort("bingo"), Short.MIN_VALUE); + rs.close(); + prep.close(); + } + + @Test + public void insert1000() throws SQLException + { + stat.executeUpdate("create table in1000 (a);"); + PreparedStatement prep = conn.prepareStatement("insert into in1000 values (?);"); + conn.setAutoCommit(false); + for (int i = 0; i < 1000; i++) + { + prep.setInt(1, i); + prep.executeUpdate(); + } + conn.commit(); + + ResultSet rs = stat.executeQuery("select count(a) from in1000;"); + assertTrue(rs.next()); + assertEquals(rs.getInt(1), 1000); + rs.close(); + } + + @Ignore + @Test + public void getObject() throws SQLException + { + stat.executeUpdate("create table testobj (" + "c1 integer, c2 float, c3, c4 varchar, c5 bit, c6, c7);"); + PreparedStatement prep = conn.prepareStatement("insert into testobj values (?,?,?,?,?,?,?);"); + + prep.setInt(1, Integer.MAX_VALUE); + prep.setFloat(2, Float.MAX_VALUE); + prep.setDouble(3, Double.MAX_VALUE); + prep.setLong(4, Long.MAX_VALUE); + prep.setBoolean(5, false); + prep.setByte(6, (byte) 7); + prep.setBytes(7, b1); + prep.executeUpdate(); + + ResultSet rs = stat.executeQuery("select c1,c2,c3,c4,c5,c6,c7 from testobj;"); + assertTrue(rs.next()); + + assertEquals(rs.getInt(1), Integer.MAX_VALUE); + assertEquals((int) rs.getLong(1), Integer.MAX_VALUE); + assertEquals(rs.getFloat(2), Float.MAX_VALUE); + assertEquals(rs.getDouble(3), Double.MAX_VALUE); + assertEquals(rs.getLong(4), Long.MAX_VALUE); + assertFalse(rs.getBoolean(5)); + assertEquals(rs.getByte(6), (byte) 7); + assertArrayEq(rs.getBytes(7), b1); + + assertNotNull(rs.getObject(1)); + assertNotNull(rs.getObject(2)); + assertNotNull(rs.getObject(3)); + assertNotNull(rs.getObject(4)); + assertNotNull(rs.getObject(5)); + assertNotNull(rs.getObject(6)); + assertNotNull(rs.getObject(7)); + assertTrue(rs.getObject(1) instanceof Integer); + assertTrue(rs.getObject(2) instanceof Double); + assertTrue(rs.getObject(3) instanceof Double); + assertTrue(rs.getObject(4) instanceof String); + assertTrue(rs.getObject(5) instanceof Integer); + assertTrue(rs.getObject(6) instanceof Integer); + assertTrue(rs.getObject(7) instanceof byte[]); + rs.close(); + } + + @Test + public void tokens() throws SQLException + { + /* + * checks for a bug where a substring is read by the driver as the full + * original string, caused by my idiocyin assuming the pascal-style + * string was null terminated. Thanks Oliver Randschau. + */ + StringTokenizer st = new StringTokenizer("one two three"); + st.nextToken(); + String substr = st.nextToken(); + + PreparedStatement prep = conn.prepareStatement("select ?;"); + prep.setString(1, substr); + ResultSet rs = prep.executeQuery(); + assertTrue(rs.next()); + assertEquals(rs.getString(1), substr); + } + + @Test + public void utf() throws SQLException + { + ResultSet rs = stat.executeQuery("select '" + utf01 + "','" + utf02 + "','" + utf03 + "','" + utf04 + "','" + + utf05 + "','" + utf06 + "','" + utf07 + "','" + utf08 + "';"); + assertEquals(rs.getString(1), utf01); + assertEquals(rs.getString(2), utf02); + assertEquals(rs.getString(3), utf03); + assertEquals(rs.getString(4), utf04); + assertEquals(rs.getString(5), utf05); + assertEquals(rs.getString(6), utf06); + assertEquals(rs.getString(7), utf07); + assertEquals(rs.getString(8), utf08); + rs.close(); + + PreparedStatement prep = conn.prepareStatement("select ?,?,?,?,?,?,?,?;"); + prep.setString(1, utf01); + prep.setString(2, utf02); + prep.setString(3, utf03); + prep.setString(4, utf04); + prep.setString(5, utf05); + prep.setString(6, utf06); + prep.setString(7, utf07); + prep.setString(8, utf08); + rs = prep.executeQuery(); + assertTrue(rs.next()); + assertEquals(rs.getString(1), utf01); + assertEquals(rs.getString(2), utf02); + assertEquals(rs.getString(3), utf03); + assertEquals(rs.getString(4), utf04); + assertEquals(rs.getString(5), utf05); + assertEquals(rs.getString(6), utf06); + assertEquals(rs.getString(7), utf07); + assertEquals(rs.getString(8), utf08); + rs.close(); + } + + @Test + public void batch() throws SQLException + { + ResultSet rs; + + stat.executeUpdate("create table test (c1, c2, c3, c4);"); + PreparedStatement prep = conn.prepareStatement("insert into test values (?,?,?,?);"); + for (int i = 0; i < 10; i++) + { + prep.setInt(1, Integer.MIN_VALUE + i); + prep.setFloat(2, Float.MIN_VALUE + i); + prep.setString(3, "Hello " + i); + prep.setDouble(4, Double.MAX_VALUE + i); + prep.addBatch(); + } + assertArrayEq(prep.executeBatch(), new int[] { 1, 1, 1, 1, 1, 1, 1, 1, 1, 1 }); + prep.close(); + + rs = stat.executeQuery("select * from test;"); + for (int i = 0; i < 10; i++) + { + assertTrue(rs.next()); + assertEquals(rs.getInt(1), Integer.MIN_VALUE + i); + assertEquals(rs.getFloat(2), Float.MIN_VALUE + i); + assertEquals(rs.getString(3), "Hello " + i); + assertEquals(rs.getDouble(4), Double.MAX_VALUE + i); + } + rs.close(); + stat.executeUpdate("drop table test;"); + } + + @Test + public void dblock() throws SQLException + { + stat.executeUpdate("create table test (c1);"); + stat.executeUpdate("insert into test values (1);"); + conn.prepareStatement("select * from test;").executeQuery().close(); + stat.executeUpdate("drop table test;"); + + } + + @Test + public void dbclose() throws SQLException + { + conn.prepareStatement("select ?;").setString(1, "Hello World"); + conn.prepareStatement("select null;").close(); + conn.prepareStatement("select null;").executeQuery().close(); + conn.prepareStatement("create table t (c);").executeUpdate(); + conn.prepareStatement("select null;"); + } + + @Test + public void batchOneParam() throws SQLException + { + stat.executeUpdate("create table test (c1);"); + PreparedStatement prep = conn.prepareStatement("insert into test values (?);"); + for (int i = 0; i < 10; i++) + { + prep.setInt(1, Integer.MIN_VALUE + i); + prep.addBatch(); + } + assertArrayEq(prep.executeBatch(), new int[] { 1, 1, 1, 1, 1, 1, 1, 1, 1, 1 }); + prep.close(); + ResultSet rs = stat.executeQuery("select count(*) from test;"); + assertTrue(rs.next()); + assertEquals(rs.getInt(1), 10); + rs.close(); + } + + @Test + public void paramMetaData() throws SQLException + { + PreparedStatement prep = conn.prepareStatement("select ?,?,?,?;"); + assertEquals(prep.getParameterMetaData().getParameterCount(), 4); + } + + @Test + public void metaData() throws SQLException + { + PreparedStatement prep = conn.prepareStatement("select ? as col1, ? as col2, ? as delta;"); + ResultSetMetaData meta = prep.getMetaData(); + assertEquals(meta.getColumnCount(), 3); + assertEquals(meta.getColumnName(1), "col1"); + assertEquals(meta.getColumnName(2), "col2"); + assertEquals(meta.getColumnName(3), "delta"); + /* + * assertEquals(meta.getColumnType(1), Types.INTEGER); + * assertEquals(meta.getColumnType(2), Types.INTEGER); + * assertEquals(meta.getColumnType(3), Types.INTEGER); + */ + + meta = prep.executeQuery().getMetaData(); + assertEquals(meta.getColumnCount(), 3); + prep.close(); + } + + @Test + public void date1() throws SQLException + { + Date d1 = new Date(987654321); + + stat.execute("create table t (c1);"); + PreparedStatement prep = conn.prepareStatement("insert into t values(?);"); + prep.setDate(1, d1); + prep.executeUpdate(); + + ResultSet rs = stat.executeQuery("select c1 from t;"); + assertTrue(rs.next()); + assertEquals(rs.getLong(1), d1.getTime()); + assertTrue(rs.getDate(1).equals(d1)); + rs.close(); + } + + @Test + public void date2() throws SQLException + { + Date d1 = new Date(1092941466000L); + stat.execute("create table t (c1);"); + PreparedStatement prep = conn.prepareStatement("insert into t values (datetime(?/1000, 'unixepoch'));"); + prep.setDate(1, d1); + prep.executeUpdate(); + + ResultSet rs = stat.executeQuery("select strftime('%s', c1) * 1000 from t;"); + assertTrue(rs.next()); + assertEquals(rs.getLong(1), d1.getTime()); + assertTrue(rs.getDate(1).equals(d1)); + } + + @Test + public void changeSchema() throws SQLException + { + stat.execute("create table t (c1);"); + PreparedStatement prep = conn.prepareStatement("insert into t values (?);"); + conn.createStatement().execute("create table t2 (c2);"); + prep.setInt(1, 1000); + prep.execute(); + prep.executeUpdate(); + } + + @Test(expected = SQLException.class) + public void noSuchTable() throws SQLException + { + PreparedStatement prep = conn.prepareStatement("select * from doesnotexist;"); + prep.executeQuery(); + } + + @Test(expected = SQLException.class) + public void noSuchCol() throws SQLException + { + PreparedStatement prep = conn.prepareStatement("select notacol from (select 1);"); + prep.executeQuery(); + } + + @Test(expected = SQLException.class) + public void noSuchColName() throws SQLException + { + ResultSet rs = conn.prepareStatement("select 1;").executeQuery(); + assertTrue(rs.next()); + rs.getInt("noSuchColName"); + } + + private void assertArrayEq(byte[] a, byte[] b) + { + assertNotNull(a); + assertNotNull(b); + assertEquals(a.length, b.length); + for (int i = 0; i < a.length; i++) + assertEquals(a[i], b[i]); + } + + private void assertArrayEq(int[] a, int[] b) + { + assertNotNull(a); + assertNotNull(b); + assertEquals(a.length, b.length); + for (int i = 0; i < a.length; i++) + assertEquals(a[i], b[i]); + } +} diff --git a/src/test/java/org/sqlite/RSMetaDataTest.java b/src/test/java/org/sqlite/RSMetaDataTest.java new file mode 100644 index 0000000..b035452 --- /dev/null +++ b/src/test/java/org/sqlite/RSMetaDataTest.java @@ -0,0 +1,104 @@ +package org.sqlite; + +import static org.junit.Assert.assertEquals; +import static org.junit.Assert.assertFalse; +import static org.junit.Assert.assertTrue; + +import java.sql.Connection; +import java.sql.DriverManager; +import java.sql.ResultSetMetaData; +import java.sql.SQLException; +import java.sql.Statement; +import java.sql.Types; + +import org.junit.After; +import org.junit.Before; +import org.junit.BeforeClass; +import org.junit.Test; + +public class RSMetaDataTest +{ + private Connection conn; + private Statement stat; + private ResultSetMetaData meta; + + @BeforeClass + public static void forName() throws Exception + { + Class.forName("org.sqlite.JDBC"); + } + + @Before + public void connect() throws Exception + { + conn = DriverManager.getConnection("jdbc:sqlite:"); + stat = conn.createStatement(); + stat.executeUpdate("create table People (pid integer primary key autoincrement, " + + " firstname string, surname string, dob date);"); + stat.executeUpdate("insert into people values (null, 'Mohandas', 'Gandhi', " + " '1869-10-02');"); + meta = stat.executeQuery("select pid, firstname, surname from people;").getMetaData(); + } + + @After + public void close() throws SQLException + { + stat.executeUpdate("drop table people;"); + stat.close(); + conn.close(); + } + + @Test + public void catalogName() throws SQLException + { + assertEquals(meta.getCatalogName(1), "People"); + } + + @Test + public void columns() throws SQLException + { + assertEquals(meta.getColumnCount(), 3); + assertEquals(meta.getColumnName(1), "pid"); + assertEquals(meta.getColumnName(2), "firstname"); + assertEquals(meta.getColumnName(3), "surname"); + assertEquals(meta.getColumnType(1), Types.INTEGER); + assertEquals(meta.getColumnType(2), Types.VARCHAR); + assertEquals(meta.getColumnType(3), Types.VARCHAR); + assertTrue(meta.isAutoIncrement(1)); + assertFalse(meta.isAutoIncrement(2)); + assertFalse(meta.isAutoIncrement(3)); + assertEquals(meta.isNullable(1), meta.columnNoNulls); + assertEquals(meta.isNullable(2), meta.columnNullable); + assertEquals(meta.isNullable(3), meta.columnNullable); + } + + @Test + public void differentRS() throws SQLException + { + meta = stat.executeQuery("select * from people;").getMetaData(); + assertEquals(meta.getColumnCount(), 4); + assertEquals(meta.getColumnName(1), "pid"); + assertEquals(meta.getColumnName(2), "firstname"); + assertEquals(meta.getColumnName(3), "surname"); + assertEquals(meta.getColumnName(4), "dob"); + } + + @Test + public void nullable() throws SQLException + { + meta = stat.executeQuery("select null;").getMetaData(); + assertEquals(meta.isNullable(1), ResultSetMetaData.columnNullable); + } + + @Test(expected = SQLException.class) + public void badCatalogIndex() throws SQLException + { + meta.getCatalogName(4); + } + + @Test(expected = SQLException.class) + public void badColumnIndex() throws SQLException + { + meta.getColumnName(4); + } + +} diff --git a/src/test/java/org/sqlite/StatementTest.java b/src/test/java/org/sqlite/StatementTest.java new file mode 100644 index 0000000..c62d10e --- /dev/null +++ b/src/test/java/org/sqlite/StatementTest.java @@ -0,0 +1,322 @@ +package org.sqlite; + +import static org.junit.Assert.assertEquals; +import static org.junit.Assert.assertFalse; +import static org.junit.Assert.assertNotNull; +import static org.junit.Assert.assertNull; +import static org.junit.Assert.assertTrue; + +import java.sql.BatchUpdateException; +import java.sql.Connection; +import java.sql.DriverManager; +import java.sql.ResultSet; +import java.sql.SQLException; +import java.sql.Statement; + +import org.junit.After; +import org.junit.Before; +import org.junit.BeforeClass; +import org.junit.Test; + +/** These tests are designed to stress Statements on memory databases. */ +public class StatementTest +{ + private Connection conn; + private Statement stat; + + @BeforeClass + public static void forName() throws Exception + { + Class.forName("org.sqlite.JDBC"); + } + + @Before + public void connect() throws Exception + { + conn = DriverManager.getConnection("jdbc:sqlite:"); + stat = conn.createStatement(); + } + + @After + public void close() throws SQLException + { + stat.close(); + conn.close(); + } + + @Test + public void stmtUpdate() throws SQLException + { + assertEquals(stat.executeUpdate("create table s1 (c1);"), 0); + assertEquals(stat.executeUpdate("insert into s1 values (0);"), 1); + assertEquals(stat.executeUpdate("insert into s1 values (1);"), 1); + assertEquals(stat.executeUpdate("insert into s1 values (2);"), 1); + assertEquals(stat.executeUpdate("update s1 set c1 = 5;"), 3); + assertEquals(stat.executeUpdate("delete from s1;"), 0); + assertEquals(stat.executeUpdate("drop table s1;"), 0); + } + + @Test + public void emptyRS() throws SQLException + { + ResultSet rs = stat.executeQuery("select null limit 0;"); + assertFalse(rs.next()); + rs.close(); + } + + @Test + public void singleRowRS() throws SQLException + { + ResultSet rs = stat.executeQuery("select " + Integer.MAX_VALUE + ";"); + assertTrue(rs.next()); + assertEquals(rs.getInt(1), Integer.MAX_VALUE); + assertEquals(rs.getString(1), Integer.toString(Integer.MAX_VALUE)); + assertEquals(rs.getDouble(1), new Integer(Integer.MAX_VALUE).doubleValue()); + assertFalse(rs.next()); + rs.close(); + } + + @Test + public void twoRowRS() throws SQLException + { + ResultSet rs = stat.executeQuery("select 9 union all select 7;"); + assertTrue(rs.next()); + assertEquals(rs.getInt(1), 9); + assertTrue(rs.next()); + assertEquals(rs.getInt(1), 7); + assertFalse(rs.next()); + rs.close(); + } + + @Test + public void autoClose() throws SQLException + { + conn.createStatement().executeQuery("select 1;"); + } + + @Test + public void stringRS() throws SQLException + { + ResultSet rs = stat.executeQuery("select \"Russell\";"); + assertTrue(rs.next()); + assertEquals(rs.getString(1), "Russell"); + assertFalse(rs.next()); + rs.close(); + } + + @Test + public void execute() throws SQLException + { + assertTrue(stat.execute("select null;")); + ResultSet rs = stat.getResultSet(); + assertNotNull(rs); + assertTrue(rs.next()); + assertNull(rs.getString(1)); + assertTrue(rs.wasNull()); + assertFalse(stat.getMoreResults()); + assertEquals(stat.getUpdateCount(), -1); + + assertTrue(stat.execute("select null;")); + assertFalse(stat.getMoreResults()); + assertEquals(stat.getUpdateCount(), -1); + + assertFalse(stat.execute("create table test (c1);")); + assertEquals(stat.getUpdateCount(), 0); + assertFalse(stat.getMoreResults()); + assertEquals(stat.getUpdateCount(), -1); + } + + @Test + public void colNameAccess() throws SQLException + { + assertEquals(stat.executeUpdate("create table tab (id, firstname, surname);"), 0); + assertEquals(stat.executeUpdate("insert into tab values (0, 'Bob', 'Builder');"), 1); + assertEquals(stat.executeUpdate("insert into tab values (1, 'Fred', 'Blogs');"), 1); + assertEquals(stat.executeUpdate("insert into tab values (2, 'John', 'Smith');"), 1); + ResultSet rs = stat.executeQuery("select * from tab;"); + assertTrue(rs.next()); + assertEquals(rs.getInt("id"), 0); + assertEquals(rs.getString("firstname"), "Bob"); + assertEquals(rs.getString("surname"), "Builder"); + assertTrue(rs.next()); + assertEquals(rs.getInt("id"), 1); + assertEquals(rs.getString("firstname"), "Fred"); + assertEquals(rs.getString("surname"), "Blogs"); + assertTrue(rs.next()); + assertEquals(rs.getInt("id"), 2); + assertEquals(rs.getString("id"), "2"); + assertEquals(rs.getString("firstname"), "John"); + assertEquals(rs.getString("surname"), "Smith"); + assertFalse(rs.next()); + rs.close(); + assertEquals(stat.executeUpdate("drop table tab;"), 1); + } + + @Test + public void nulls() throws SQLException + { + ResultSet rs = stat.executeQuery("select null union all select null;"); + assertTrue(rs.next()); + assertNull(rs.getString(1)); + assertTrue(rs.wasNull()); + assertTrue(rs.next()); + assertNull(rs.getString(1)); + assertTrue(rs.wasNull()); + assertFalse(rs.next()); + rs.close(); + } + + @Test + public void tempTable() throws SQLException + { + assertEquals(stat.executeUpdate("create temp table myTemp (a);"), 0); + assertEquals(stat.executeUpdate("insert into myTemp values (2);"), 1); + } + + @Test + public void insert1000() throws SQLException + { + assertEquals(stat.executeUpdate("create table in1000 (a);"), 0); + conn.setAutoCommit(false); + for (int i = 0; i < 1000; i++) + assertEquals(stat.executeUpdate("insert into in1000 values (" + i + ");"), 1); + conn.commit(); + + ResultSet rs = stat.executeQuery("select count(a) from in1000;"); + assertTrue(rs.next()); + assertEquals(rs.getInt(1), 1000); + rs.close(); + + assertEquals(stat.executeUpdate("drop table in1000;"), 1); + } + + private void assertArrayEq(int[] a, int[] b) + { + assertNotNull(a); + assertNotNull(b); + assertEquals(a.length, b.length); + for (int i = 0; i < a.length; i++) + assertEquals(a[i], b[i]); + } + + @Test + public void batch() throws SQLException + { + stat.addBatch("create table batch (c1);"); + stat.addBatch("insert into batch values (1);"); + stat.addBatch("insert into batch values (2);"); + stat.addBatch("insert into batch values (3);"); + stat.addBatch("insert into batch values (4);"); + assertArrayEq(new int[] { 0, 1, 1, 1, 1 }, stat.executeBatch()); + assertArrayEq(new int[] {}, stat.executeBatch()); + stat.clearBatch(); + stat.addBatch("insert into batch values (9);"); + assertArrayEq(new int[] { 1 }, stat.executeBatch()); + assertArrayEq(new int[] {}, stat.executeBatch()); + stat.clearBatch(); + stat.addBatch("insert into batch values (7);"); + stat.addBatch("insert into batch values (7);"); + assertArrayEq(new int[] { 1, 1 }, stat.executeBatch()); + stat.clearBatch(); + + ResultSet rs = stat.executeQuery("select count(*) from batch;"); + assertTrue(rs.next()); + assertEquals(7, rs.getInt(1)); + rs.close(); + } + + @Test + public void closeOnFalseNext() throws SQLException + { + stat.executeUpdate("create table t1 (c1);"); + conn.createStatement().executeQuery("select * from t1;").next(); + stat.executeUpdate("drop table t1;"); + } + + @Test + public void getGeneratedKeys() throws SQLException + { + ResultSet rs; + stat.executeUpdate("create table t1 (c1 integer primary key, v);"); + stat.executeUpdate("insert into t1 (v) values ('red');"); + rs = stat.getGeneratedKeys(); + assertTrue(rs.next()); + assertEquals(rs.getInt(1), 1); + rs.close(); + stat.executeUpdate("insert into t1 (v) values ('blue');"); + rs = stat.getGeneratedKeys(); + assertTrue(rs.next()); + assertEquals(rs.getInt(1), 2); + rs.close(); + } + + @Test + public void isBeforeFirst() throws SQLException + { + ResultSet rs = stat.executeQuery("select 1 union all select 2;"); + assertTrue(rs.isBeforeFirst()); + assertTrue(rs.next()); + assertTrue(rs.isFirst()); + assertEquals(rs.getInt(1), 1); + assertTrue(rs.next()); + assertFalse(rs.isBeforeFirst()); + assertFalse(rs.isFirst()); + assertEquals(rs.getInt(1), 2); + assertFalse(rs.next()); + assertFalse(rs.isBeforeFirst()); + rs.close(); + } + + @Test(expected = SQLException.class) + public void failToDropWhenRSOpen() throws SQLException + { + stat.executeUpdate("create table t1 (c1);"); + stat.executeUpdate("insert into t1 values (4);"); + stat.executeUpdate("insert into t1 values (4);"); + conn.createStatement().executeQuery("select * from t1;").next(); + stat.executeUpdate("drop table t1;"); + } + + @Test(expected = SQLException.class) + public void executeNoRS() throws SQLException + { + assertFalse(stat.execute("insert into test values (8);")); + stat.getResultSet(); + } + + @Test(expected = SQLException.class) + public void executeClearRS() throws SQLException + { + assertTrue(stat.execute("select null;")); + assertNotNull(stat.getResultSet()); + assertFalse(stat.getMoreResults()); + stat.getResultSet(); + } + + @Test(expected = BatchUpdateException.class) + public void batchReturnsResults() throws SQLException + { + stat.addBatch("select null;"); + stat.executeBatch(); + } + + @Test(expected = SQLException.class) + public void noSuchTable() throws SQLException + { + stat.executeQuery("select * from doesnotexist;"); + } + + @Test(expected = SQLException.class) + public void noSuchCol() throws SQLException + { + stat.executeQuery("select notacol from (select 1);"); + } + + @Test(expected = SQLException.class) + public void noSuchColName() throws SQLException + { + ResultSet rs = stat.executeQuery("select 1;"); + assertTrue(rs.next()); + rs.getInt("noSuchColName"); + } +} diff --git a/src/test/java/org/sqlite/TransactionTest.java b/src/test/java/org/sqlite/TransactionTest.java new file mode 100644 index 0000000..2618305 --- /dev/null +++ b/src/test/java/org/sqlite/TransactionTest.java @@ -0,0 +1,284 @@ +package org.sqlite; + +import static org.junit.Assert.assertEquals; +import static org.junit.Assert.assertFalse; +import static org.junit.Assert.assertTrue; + +import java.io.File; +import java.sql.Connection; +import java.sql.DriverManager; +import java.sql.PreparedStatement; +import java.sql.ResultSet; +import java.sql.SQLException; +import java.sql.Statement; + +import org.junit.After; +import org.junit.Before; +import org.junit.BeforeClass; +import org.junit.Test; + +/** + * These tests assume that Statements and PreparedStatements are working as per + * normal and test the interactions of commit(), rollback() and + * setAutoCommit(boolean) with multiple connections to the same db. + */ +public class TransactionTest +{ + private Connection conn1, conn2, conn3; + private Statement stat1, stat2, stat3; + + boolean done = false; + + @BeforeClass + public static void forName() throws Exception + { + Class.forName("org.sqlite.JDBC"); + } + + @Before + public void connect() throws Exception + { + new File("test-trans.db").delete(); + conn1 = DriverManager.getConnection("jdbc:sqlite:test-trans.db"); + conn2 = DriverManager.getConnection("jdbc:sqlite:test-trans.db"); + conn3 = DriverManager.getConnection("jdbc:sqlite:test-trans.db"); + stat1 = conn1.createStatement(); + stat2 = conn2.createStatement(); + stat3 = conn3.createStatement(); + } + + @After + public void close() throws Exception + { + stat1.close(); + stat2.close(); + stat3.close(); + conn1.close(); + conn2.close(); + conn3.close(); + new File("test-trans.db").delete(); + } + + @Test + public void multiConn() throws SQLException + { + stat1.executeUpdate("create table test (c1);"); + stat1.executeUpdate("insert into test values (1);"); + stat2.executeUpdate("insert into test values (2);"); + stat3.executeUpdate("insert into test values (3);"); + + ResultSet rs = stat1.executeQuery("select sum(c1) from test;"); + assertTrue(rs.next()); + assertEquals(rs.getInt(1), 6); + rs.close(); + + rs = stat3.executeQuery("select sum(c1) from test;"); + assertTrue(rs.next()); + assertEquals(rs.getInt(1), 6); + rs.close(); + } + + @Test + public void locking() throws SQLException + { + stat1.executeUpdate("create table test (c1);"); + stat1.executeUpdate("begin immediate;"); + stat2.executeUpdate("select * from test;"); + } + + @Test + public void insert() throws SQLException + { + ResultSet rs; + String countSql = "select count(*) from trans;"; + + stat1.executeUpdate("create table trans (c1);"); + conn1.setAutoCommit(false); + + assertEquals(1, stat1.executeUpdate("insert into trans values (4);")); + + // transaction not yet commited, conn1 can see, conn2 can not + rs = stat1.executeQuery(countSql); + assertTrue(rs.next()); + assertEquals(1, rs.getInt(1)); + rs.close(); + rs = stat2.executeQuery(countSql); + assertTrue(rs.next()); + assertEquals(0, rs.getInt(1)); + rs.close(); + + conn1.commit(); + + // all connects can see data + rs = stat2.executeQuery(countSql); + assertTrue(rs.next()); + assertEquals(1, rs.getInt(1)); + rs.close(); + } + + @Test + public void rollback() throws SQLException + { + String select = "select * from trans;"; + ResultSet rs; + + stat1.executeUpdate("create table trans (c1);"); + conn1.setAutoCommit(false); + stat1.executeUpdate("insert into trans values (3);"); + + rs = stat1.executeQuery(select); + assertTrue(rs.next()); + rs.close(); + + conn1.rollback(); + + rs = stat1.executeQuery(select); + assertFalse(rs.next()); + rs.close(); + } + + @Test + public void multiRollback() throws SQLException + { + ResultSet rs; + + stat1.executeUpdate("create table t (c1);"); + conn1.setAutoCommit(false); + stat1.executeUpdate("insert into t values (1);"); + conn1.commit(); + stat1.executeUpdate("insert into t values (1);"); + conn1.rollback(); + stat1.addBatch("insert into t values (2);"); + stat1.addBatch("insert into t values (3);"); + stat1.executeBatch(); + conn1.commit(); + stat1.addBatch("insert into t values (7);"); + stat1.executeBatch(); + conn1.rollback(); + stat1.executeUpdate("insert into t values (4);"); + conn1.setAutoCommit(true); + stat1.executeUpdate("insert into t values (5);"); + conn1.setAutoCommit(false); + PreparedStatement p = conn1.prepareStatement("insert into t values (?);"); + p.setInt(1, 6); + p.executeUpdate(); + p.setInt(1, 7); + p.executeUpdate(); + + // conn1 can see (1+...+7), conn2 can see (1+...+5) + rs = stat1.executeQuery("select sum(c1) from t;"); + assertTrue(rs.next()); + assertEquals(1 + 2 + 3 + 4 + 5 + 6 + 7, rs.getInt(1)); + rs.close(); + rs = stat2.executeQuery("select sum(c1) from t;"); + assertTrue(rs.next()); + assertEquals(1 + 2 + 3 + 4 + 5, rs.getInt(1)); + rs.close(); + } + + @Test + public void transactionsDontMindReads() throws SQLException + { + stat1.executeUpdate("create table t (c1);"); + stat1.executeUpdate("insert into t values (1);"); + stat1.executeUpdate("insert into t values (2);"); + ResultSet rs = stat1.executeQuery("select * from t;"); + assertTrue(rs.next()); // select is open + + conn2.setAutoCommit(false); + stat1.executeUpdate("insert into t values (2);"); + + rs.close(); + conn2.commit(); + } + + @Test + public void secondConnWillWait() throws Exception + { + stat1.executeUpdate("create table t (c1);"); + stat1.executeUpdate("insert into t values (1);"); + stat1.executeUpdate("insert into t values (2);"); + ResultSet rs = stat1.executeQuery("select * from t;"); + assertTrue(rs.next()); + + final TransactionTest lock = this; + lock.done = false; + new Thread() { + public void run() + { + try + { + stat2.executeUpdate("insert into t values (3);"); + } + catch (SQLException e) + { + e.printStackTrace(); + return; + } + + synchronized (lock) + { + lock.done = true; + } + } + }.start(); + + rs.close(); + + for (int i = 0; i < 40; i++) + { + boolean isDone = false; + synchronized (lock) + { + isDone = lock.done; + } + if (isDone) + return; + try + { + Thread.sleep(100); + } + catch (Exception e) + {} + } + + throw new Exception("should have caught done from second thread"); + } + + @Test(expected = SQLException.class) + public void secondConnMustTimeout() throws SQLException + { + stat1.executeUpdate("create table t (c1);"); + stat1.executeUpdate("insert into t values (1);"); + stat1.executeUpdate("insert into t values (2);"); + ResultSet rs = stat1.executeQuery("select * from t;"); + assertTrue(rs.next()); + + stat2.executeUpdate("insert into t values (3);"); // can't be done + } + + @Test(expected = SQLException.class) + public void cantUpdateWhileReading() throws SQLException + { + stat1.executeUpdate("create table t (c1);"); + stat1.executeUpdate("insert into t values (1);"); + stat1.executeUpdate("insert into t values (2);"); + ResultSet rs = conn1.createStatement().executeQuery("select * from t;"); + assertTrue(rs.next()); + + stat1.executeUpdate("insert into t values (3);"); // can't be done + } + + @Test(expected = SQLException.class) + public void cantCommit() throws SQLException + { + conn1.commit(); + } + + @Test(expected = SQLException.class) + public void cantRollback() throws SQLException + { + conn1.rollback(); + } + +} diff --git a/src/test/java/org/sqlite/UDFTest.java b/src/test/java/org/sqlite/UDFTest.java new file mode 100644 index 0000000..e39d0d6 --- /dev/null +++ b/src/test/java/org/sqlite/UDFTest.java @@ -0,0 +1,480 @@ +package org.sqlite; + +import static org.junit.Assert.assertEquals; +import static org.junit.Assert.assertNotNull; +import static org.junit.Assert.assertTrue; + +import java.sql.Connection; +import java.sql.DriverManager; +import java.sql.PreparedStatement; +import java.sql.ResultSet; +import java.sql.SQLException; +import java.sql.Statement; +import java.util.LinkedList; +import java.util.List; + +import org.junit.After; +import org.junit.Before; +import org.junit.BeforeClass; +import org.junit.Test; + +/** Tests User Defined Functions. */ +public class UDFTest +{ + private static int val = 0; + private static byte[] b1 = new byte[] { 2, 5, -4, 8, -1, 3, -5 }; + private static int gotTrigger = 0; + + private Connection conn; + private Statement stat; + + @BeforeClass + public static void forName() throws Exception + { + Class.forName("org.sqlite.JDBC"); + } + + @Before + public void connect() throws Exception + { + conn = DriverManager.getConnection("jdbc:sqlite:"); + stat = conn.createStatement(); + } + + @After + public void close() throws SQLException + { + stat.close(); + conn.close(); + } + + @Test + public void calling() throws SQLException + { + Function.create(conn, "f1", new Function() { + public void xFunc() throws SQLException + { + val = 4; + } + }); + stat.executeQuery("select f1();").close(); + assertEquals(val, 4); + } + + @Test + public void returning() throws SQLException + { + Function.create(conn, "f2", new Function() { + public void xFunc() throws SQLException + { + result(4); + } + }); + ResultSet rs = stat.executeQuery("select f2();"); + assertTrue(rs.next()); + assertEquals(rs.getInt(1), 4); + rs.close(); + + for (int i = 0; i < 20; i++) + { + rs = stat.executeQuery("select (f2() + " + i + ");"); + assertTrue(rs.next()); + assertEquals(rs.getInt(1), 4 + i); + rs.close(); + } + } + + @Test + public void accessArgs() throws SQLException + { + Function.create(conn, "f3", new Function() { + public void xFunc() throws SQLException + { + result(value_int(0)); + } + }); + for (int i = 0; i < 15; i++) + { + ResultSet rs = stat.executeQuery("select f3(" + i + ");"); + assertTrue(rs.next()); + assertEquals(rs.getInt(1), i); + rs.close(); + } + } + + @Test + public void multipleArgs() throws SQLException + { + Function.create(conn, "f4", new Function() { + public void xFunc() throws SQLException + { + int ret = 0; + for (int i = 0; i < args(); i++) + ret += value_int(i); + result(ret); + } + }); + ResultSet rs = stat.executeQuery("select f4(2, 3, 9, -5);"); + assertTrue(rs.next()); + assertEquals(rs.getInt(1), 9); + rs.close(); + rs = stat.executeQuery("select f4(2);"); + assertTrue(rs.next()); + assertEquals(rs.getInt(1), 2); + rs.close(); + rs = stat.executeQuery("select f4(-3, -4, -5);"); + assertTrue(rs.next()); + assertEquals(rs.getInt(1), -12); + } + + @Test + public void returnTypes() throws SQLException + { + Function.create(conn, "f5", new Function() { + public void xFunc() throws SQLException + { + result("Hello World"); + } + }); + ResultSet rs = stat.executeQuery("select f5();"); + assertTrue(rs.next()); + assertEquals(rs.getString(1), "Hello World"); + + Function.create(conn, "f6", new Function() { + public void xFunc() throws SQLException + { + result(Long.MAX_VALUE); + } + }); + rs.close(); + rs = stat.executeQuery("select f6();"); + assertTrue(rs.next()); + assertEquals(rs.getLong(1), Long.MAX_VALUE); + + Function.create(conn, "f7", new Function() { + public void xFunc() throws SQLException + { + result(Double.MAX_VALUE); + } + }); + rs.close(); + rs = stat.executeQuery("select f7();"); + assertTrue(rs.next()); + assertEquals(rs.getDouble(1), Double.MAX_VALUE); + + Function.create(conn, "f8", new Function() { + public void xFunc() throws SQLException + { + result(b1); + } + }); + rs.close(); + rs = stat.executeQuery("select f8();"); + assertTrue(rs.next()); + assertArrayEq(rs.getBytes(1), b1); + } + + @Test + public void returnArgInt() throws SQLException + { + Function.create(conn, "farg_int", new Function() { + public void xFunc() throws SQLException + { + result(value_int(0)); + } + }); + PreparedStatement prep = conn.prepareStatement("select farg_int(?);"); + prep.setInt(1, Integer.MAX_VALUE); + ResultSet rs = prep.executeQuery(); + assertTrue(rs.next()); + assertEquals(rs.getInt(1), Integer.MAX_VALUE); + prep.close(); + } + + @Test + public void returnArgLong() throws SQLException + { + Function.create(conn, "farg_long", new Function() { + public void xFunc() throws SQLException + { + result(value_long(0)); + } + }); + PreparedStatement prep = conn.prepareStatement("select farg_long(?);"); + prep.setLong(1, Long.MAX_VALUE); + ResultSet rs = prep.executeQuery(); + assertTrue(rs.next()); + assertEquals(rs.getLong(1), Long.MAX_VALUE); + prep.close(); + } + + @Test + public void returnArgDouble() throws SQLException + { + Function.create(conn, "farg_doub", new Function() { + public void xFunc() throws SQLException + { + result(value_double(0)); + } + }); + PreparedStatement prep = conn.prepareStatement("select farg_doub(?);"); + prep.setDouble(1, Double.MAX_VALUE); + ResultSet rs = prep.executeQuery(); + assertTrue(rs.next()); + assertEquals(rs.getDouble(1), Double.MAX_VALUE); + prep.close(); + } + + @Test + public void returnArgBlob() throws SQLException + { + Function.create(conn, "farg_blob", new Function() { + public void xFunc() throws SQLException + { + result(value_blob(0)); + } + }); + PreparedStatement prep = conn.prepareStatement("select farg_blob(?);"); + prep.setBytes(1, b1); + ResultSet rs = prep.executeQuery(); + assertTrue(rs.next()); + assertArrayEq(rs.getBytes(1), b1); + prep.close(); + } + + @Test + public void returnArgString() throws SQLException + { + Function.create(conn, "farg_str", new Function() { + public void xFunc() throws SQLException + { + result(value_text(0)); + } + }); + PreparedStatement prep = conn.prepareStatement("select farg_str(?);"); + prep.setString(1, "Hello"); + ResultSet rs = prep.executeQuery(); + assertTrue(rs.next()); + assertEquals(rs.getString(1), "Hello"); + prep.close(); + } + + @Test(expected = SQLException.class) + public void customErr() throws SQLException + { + Function.create(conn, "f9", new Function() { + public void xFunc() throws SQLException + { + throw new SQLException("myErr"); + } + }); + stat.executeQuery("select f9();"); + } + + @Test + public void trigger() throws SQLException + { + Function.create(conn, "inform", new Function() { + protected void xFunc() throws SQLException + { + gotTrigger = value_int(0); + } + }); + stat.executeUpdate("create table trigtest (c1);"); + stat.executeUpdate("create trigger trigt after insert on trigtest" + " begin select inform(new.c1); end;"); + stat.executeUpdate("insert into trigtest values (5);"); + assertEquals(gotTrigger, 5); + } + + @Test + public void aggregate() throws SQLException + { + Function.create(conn, "mySum", new Function.Aggregate() { + private int val = 0; + + protected void xStep() throws SQLException + { + for (int i = 0; i < args(); i++) + val += value_int(i); + } + + protected void xFinal() throws SQLException + { + result(val); + } + }); + stat.executeUpdate("create table t (c1);"); + stat.executeUpdate("insert into t values (5);"); + stat.executeUpdate("insert into t values (3);"); + stat.executeUpdate("insert into t values (8);"); + stat.executeUpdate("insert into t values (2);"); + stat.executeUpdate("insert into t values (7);"); + ResultSet rs = stat.executeQuery("select mySum(c1), sum(c1) from t;"); + assertTrue(rs.next()); + assertEquals(rs.getInt(1), rs.getInt(2)); + } + + @Test + public void destroy() throws SQLException + { + Function.create(conn, "f1", new Function() { + public void xFunc() throws SQLException + { + val = 9; + } + }); + stat.executeQuery("select f1();").close(); + assertEquals(val, 9); + + Function.destroy(conn, "f1"); + Function.destroy(conn, "f1"); + } + + @Test + public void manyfunctions() throws SQLException + { + Function.create(conn, "f1", new Function() { + public void xFunc() throws SQLException + { + result(1); + } + }); + Function.create(conn, "f2", new Function() { + public void xFunc() throws SQLException + { + result(2); + } + }); + Function.create(conn, "f3", new Function() { + public void xFunc() throws SQLException + { + result(3); + } + }); + Function.create(conn, "f4", new Function() { + public void xFunc() throws SQLException + { + result(4); + } + }); + Function.create(conn, "f5", new Function() { + public void xFunc() throws SQLException + { + result(5); + } + }); + Function.create(conn, "f6", new Function() { + public void xFunc() throws SQLException + { + result(6); + } + }); + Function.create(conn, "f7", new Function() { + public void xFunc() throws SQLException + { + result(7); + } + }); + Function.create(conn, "f8", new Function() { + public void xFunc() throws SQLException + { + result(8); + } + }); + Function.create(conn, "f9", new Function() { + public void xFunc() throws SQLException + { + result(9); + } + }); + Function.create(conn, "f10", new Function() { + public void xFunc() throws SQLException + { + result(10); + } + }); + Function.create(conn, "f11", new Function() { + public void xFunc() throws SQLException + { + result(11); + } + }); + + ResultSet rs = stat.executeQuery("select f1() + f2() + f3() + f4() + f5() + f6()" + + " + f7() + f8() + f9() + f10() + f11();"); + assertTrue(rs.next()); + assertEquals(rs.getInt(1), 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 + 10 + 11); + rs.close(); + } + + @Test + public void multipleThreads() throws Exception + { + Function func = new Function() { + int sum = 0; + + protected void xFunc() + { + try + { + sum += value_int(1); + } + catch (SQLException e) + { + e.printStackTrace(); + } + } + + public String toString() + { + return String.valueOf(sum); + } + }; + Function.create(conn, "func", func); + stat.executeUpdate("create table foo (col integer);"); + stat.executeUpdate("create trigger foo_trigger after insert on foo begin" + + " select func(new.rowid, new.col); end;"); + int times = 1000; + List threads = new LinkedList(); + for (int tn = 0; tn < times; tn++) + { + threads.add(new Thread("func thread " + tn) { + public void run() + { + try + { + Statement s = conn.createStatement(); + s.executeUpdate("insert into foo values (1);"); + s.close(); + } + catch (SQLException e) + { + e.printStackTrace(); + } + } + }); + } + for (Thread thread : threads) + thread.start(); + for (Thread thread : threads) + thread.join(); + + // check that all of the threads successfully executed + ResultSet rs = stat.executeQuery("select sum(col) from foo;"); + assertTrue(rs.next()); + assertEquals(rs.getInt(1), times); + rs.close(); + + // check that custom function was executed each time + assertEquals(Integer.parseInt(func.toString()), times); + } + + private void assertArrayEq(byte[] a, byte[] b) + { + assertNotNull(a); + assertNotNull(b); + assertEquals(a.length, b.length); + for (int i = 0; i < a.length; i++) + assertEquals(a[i], b[i]); + } +} diff --git a/src/test/java/org/xerial/db/sql/sqlite/SQLiteJDBCLoaderTest.java b/src/test/java/org/xerial/db/sql/sqlite/SQLiteJDBCLoaderTest.java index 3ff18ca..fae0647 100644 --- a/src/test/java/org/xerial/db/sql/sqlite/SQLiteJDBCLoaderTest.java +++ b/src/test/java/org/xerial/db/sql/sqlite/SQLiteJDBCLoaderTest.java @@ -24,6 +24,8 @@ //-------------------------------------- package org.xerial.db.sql.sqlite; +import static org.junit.Assert.assertEquals; +import static org.junit.Assert.assertTrue; import static org.junit.Assert.fail; import java.sql.Connection; @@ -35,33 +37,34 @@ import java.sql.Statement; import org.junit.After; import org.junit.Before; import org.junit.Test; +import org.sqlite.Function; public class SQLiteJDBCLoaderTest { + private Connection connection = null; + @Before public void setUp() throws Exception { - + connection = null; + Class.forName("org.sqlite.JDBC"); + // create a database connection + connection = DriverManager.getConnection("jdbc:sqlite::memory:"); } @After public void tearDown() throws Exception - {} + { + if (connection != null) + connection.close(); + } @Test public void query() throws ClassNotFoundException { - // SQLiteJDBCLoader.initialize(); - - // load the sqlite-JDBC driver into the current class loader - Class.forName("org.sqlite.JDBC"); - - Connection connection = null; try { - // create a database connection - connection = DriverManager.getConnection("jdbc:sqlite::memory:"); Statement statement = connection.createStatement(); statement.setQueryTimeout(30); // set timeout to 30 sec. @@ -83,18 +86,25 @@ public class SQLiteJDBCLoaderTest // database file is found fail(e.getMessage()); } - finally - { - try - { - if (connection != null) - connection.close(); - } - catch (SQLException e) + } + + @Test + public void function() throws SQLException + { + Function.create(connection, "total", new Function() { + @Override + protected void xFunc() throws SQLException { - // connection close failed. - fail(e.getMessage()); + int sum = 0; + for (int i = 0; i < args(); i++) + sum += value_int(i); + result(sum); } - } + }); + + ResultSet rs = connection.createStatement().executeQuery("select total(1, 2, 3, 4, 5)"); + assertTrue(rs.next()); + assertEquals(rs.getInt(1), 1 + 2 + 3 + 4 + 5); } + } -- 2.11.0