From 2cae3d155177e3e2b012ff2cc5053a837ebf93c1 Mon Sep 17 00:00:00 2001 From: James Ahlborn Date: Wed, 12 Nov 2008 04:02:48 +0000 Subject: [PATCH] add some more query tests git-svn-id: https://svn.code.sf.net/p/jackcess/code/jackcess/trunk@389 f203690c-595d-4dc9-a70b-905162fa7fd2 --- pom.xml | 7 +- .../jackcess/query/Query.java | 3 +- .../jackcess/query/QueryTest.java | 283 +++++++++++++++++- test/src/resources/log4j_test.properties | 6 + 4 files changed, 291 insertions(+), 8 deletions(-) create mode 100644 test/src/resources/log4j_test.properties diff --git a/pom.xml b/pom.xml index 2fa3d92..abcc0ca 100644 --- a/pom.xml +++ b/pom.xml @@ -53,6 +53,11 @@ src/resources + + + test/src/resources + + @@ -64,7 +69,7 @@ log4j.configuration - com/healthmarketscience/jackcess/log4j.properties + log4j_test.properties com.healthmarketscience.jackcess.bigIndex diff --git a/src/java/com/healthmarketscience/jackcess/query/Query.java b/src/java/com/healthmarketscience/jackcess/query/Query.java index 5ad8dbc..7853859 100644 --- a/src/java/com/healthmarketscience/jackcess/query/Query.java +++ b/src/java/com/healthmarketscience/jackcess/query/Query.java @@ -189,13 +189,12 @@ public abstract class Query { return (new RowFormatter(getParameterRows()) { @Override protected void format(StringBuilder builder, Row row) { - String name = row.name1; String typeName = PARAM_TYPE_MAP.get(row.flag); if(typeName == null) { throw new IllegalStateException("Unknown param type " + row.flag); } - builder.append(name).append(' ').append(typeName); + builder.append(row.name1).append(' ').append(typeName); if((TEXT_FLAG.equals(row.flag)) && (getIntValue(row.extra, 0) > 0)) { builder.append('(').append(row.extra).append(')'); } diff --git a/test/src/java/com/healthmarketscience/jackcess/query/QueryTest.java b/test/src/java/com/healthmarketscience/jackcess/query/QueryTest.java index bdba1d2..10d976c 100644 --- a/test/src/java/com/healthmarketscience/jackcess/query/QueryTest.java +++ b/test/src/java/com/healthmarketscience/jackcess/query/QueryTest.java @@ -32,6 +32,7 @@ import java.util.Arrays; import java.util.Iterator; import java.util.List; +import com.healthmarketscience.jackcess.DataType; import com.healthmarketscience.jackcess.query.Query.Row; import junit.framework.TestCase; import org.apache.commons.lang.StringUtils; @@ -71,7 +72,7 @@ public class QueryTest extends TestCase "UNION ALL Select * from Table2;"), query.toSQLString()); - query.getRows().add(newRow(ORDERBY_ATTRIBUTE, "Table1.id", + addRows(query, newRow(ORDERBY_ATTRIBUTE, "Table1.id", null, null)); assertEquals(multiline("Select * from Table1", @@ -79,6 +80,15 @@ public class QueryTest extends TestCase "ORDER BY Table1.id;"), query.toSQLString()); + removeRows(query, TABLE_ATTRIBUTE); + + try { + query.toSQLString(); + fail("IllegalStateException should have been thrown"); + } catch(IllegalStateException e) { + // success + } + } public void testPassthroughQuery() throws Exception @@ -116,7 +126,7 @@ public class QueryTest extends TestCase "SET Table1.id = \"some string\", Table1.col1 = 42;"), query.toSQLString()); - query.getRows().add(newRow(WHERE_ATTRIBUTE, "(Table1.col2 < 13)", + addRows(query, newRow(WHERE_ATTRIBUTE, "(Table1.col2 < 13)", null, null)); assertEquals( @@ -126,6 +136,252 @@ public class QueryTest extends TestCase query.toSQLString()); } + public void testSelectQuery() throws Exception + { + SelectQuery query = (SelectQuery)newQuery( + Query.Type.SELECT, + newRow(TABLE_ATTRIBUTE, null, "Table1", null)); + setFlag(query, 1); + + assertEquals(multiline("SELECT *", + "FROM Table1;"), + query.toSQLString()); + + doTestColumns(query); + doTestSelectFlags(query); + doTestParameters(query); + doTestTables(query); + doTestRemoteDb(query); + doTestJoins(query); + doTestWhereExpression(query); + doTestGroupings(query); + doTestHavingExpression(query); + doTestOrderings(query); + } + + public void testBadQueries() throws Exception + { + List rowList = new ArrayList(); + rowList.add(newRow(TYPE_ATTRIBUTE, null, -1, null, null)); + Query query = Query.create(-1, "TestQuery", rowList, 13); + try { + query.toSQLString(); + fail("UnsupportedOperationException should have been thrown"); + } catch(UnsupportedOperationException e) { + // success + } + + addRows(query, newRow(TYPE_ATTRIBUTE, null, -1, null, null)); + + try { + query.getTypeRow(); + fail("IllegalStateException should have been thrown"); + } catch(IllegalStateException e) { + // success + } + + try { + new Query("TestQuery", rowList, 13, Query.Type.UNION) { + @Override protected void toSQLString(StringBuilder builder) { + throw new UnsupportedOperationException(); + }}; + fail("IllegalStateException should have been thrown"); + } catch(IllegalStateException e) { + // success + } + + } + + private void doTestColumns(SelectQuery query) throws Exception + { + addRows(query, newRow(COLUMN_ATTRIBUTE, "Table1.id", null, null)); + addRows(query, newRow(COLUMN_ATTRIBUTE, "Table1.col", "Some.Alias", null)); + + assertEquals(multiline("SELECT Table1.id, Table1.col AS [Some.Alias], *", + "FROM Table1;"), + query.toSQLString()); + } + + private void doTestSelectFlags(SelectQuery query) throws Exception + { + setFlag(query, 3); + + assertEquals(multiline("SELECT DISTINCT Table1.id, Table1.col AS [Some.Alias], *", + "FROM Table1;"), + query.toSQLString()); + + setFlag(query, 9); + + assertEquals(multiline("SELECT DISTINCTROW Table1.id, Table1.col AS [Some.Alias], *", + "FROM Table1;"), + query.toSQLString()); + + setFlag(query, 7); + + assertEquals(multiline("SELECT DISTINCT Table1.id, Table1.col AS [Some.Alias], *", + "FROM Table1", + "WITH OWNERACCESS OPTION;"), + query.toSQLString()); + + replaceRows(query, + newRow(FLAG_ATTRIBUTE, null, 49, null, "5", null)); + + assertEquals(multiline("SELECT TOP 5 PERCENT Table1.id, Table1.col AS [Some.Alias], *", + "FROM Table1;"), + query.toSQLString()); + + setFlag(query, 0); + } + + private void doTestParameters(SelectQuery query) throws Exception + { + addRows(query, newRow(PARAMETER_ATTRIBUTE, null, DataType.INT.getValue(), "INT_VAL", null)); + + assertEquals(multiline("PARAMETERS INT_VAL Short;", + "SELECT Table1.id, Table1.col AS [Some.Alias]", + "FROM Table1;"), + query.toSQLString()); + + addRows(query, newRow(PARAMETER_ATTRIBUTE, null, DataType.TEXT.getValue(), 50, "TextVal", null), + newRow(PARAMETER_ATTRIBUTE, null, 0, 50, "[Some Value]", null)); + + assertEquals(multiline("PARAMETERS INT_VAL Short, TextVal Text(50), [Some Value] Value;", + "SELECT Table1.id, Table1.col AS [Some.Alias]", + "FROM Table1;"), + query.toSQLString()); + + addRows(query, newRow(PARAMETER_ATTRIBUTE, null, -1, "BadVal", null)); + try { + query.toSQLString(); + fail("IllegalStateException should have been thrown"); + } catch(IllegalStateException e) { + // success + } + + removeRows(query, PARAMETER_ATTRIBUTE); + } + + private void doTestTables(SelectQuery query) throws Exception + { + addRows(query, newRow(TABLE_ATTRIBUTE, null, "Table2", "Another Table")); + addRows(query, newRow(TABLE_ATTRIBUTE, "Select val from Table3", "val", "Table3Val")); + + assertEquals(multiline("SELECT Table1.id, Table1.col AS [Some.Alias]", + "FROM Table1, Table2 AS [Another Table], [Select val from Table3].val AS Table3Val;"), + query.toSQLString()); + } + + private void doTestRemoteDb(SelectQuery query) throws Exception + { + addRows(query, newRow(REMOTEDB_ATTRIBUTE, null, 2, "other_db.mdb", null)); + + assertEquals(multiline("SELECT Table1.id, Table1.col AS [Some.Alias]", + "FROM Table1, Table2 AS [Another Table], [Select val from Table3].val AS Table3Val IN 'other_db.mdb';"), + query.toSQLString()); + + replaceRows(query, newRow(REMOTEDB_ATTRIBUTE, "MDB_FILE;", 2, "other_db.mdb", null)); + + assertEquals(multiline("SELECT Table1.id, Table1.col AS [Some.Alias]", + "FROM Table1, Table2 AS [Another Table], [Select val from Table3].val AS Table3Val IN 'other_db.mdb' [MDB_FILE;];"), + query.toSQLString()); + + replaceRows(query, newRow(REMOTEDB_ATTRIBUTE, "MDB_FILE;", 2, null, null)); + + assertEquals(multiline("SELECT Table1.id, Table1.col AS [Some.Alias]", + "FROM Table1, Table2 AS [Another Table], [Select val from Table3].val AS Table3Val IN '' [MDB_FILE;];"), + query.toSQLString()); + + removeRows(query, REMOTEDB_ATTRIBUTE); + } + + private void doTestJoins(SelectQuery query) throws Exception + { + addRows(query, newRow(JOIN_ATTRIBUTE, "(Table1.id = [Another Table].id)", 1, "Table1", "Another Table")); + + assertEquals(multiline("SELECT Table1.id, Table1.col AS [Some.Alias]", + "FROM [Select val from Table3].val AS Table3Val, Table1 INNER JOIN Table2 AS [Another Table] ON (Table1.id = [Another Table].id);"), + query.toSQLString()); + + addRows(query, newRow(JOIN_ATTRIBUTE, "(Table1.id = Table3Val.id)", 2, "Table1", "Table3Val")); + + assertEquals(multiline("SELECT Table1.id, Table1.col AS [Some.Alias]", + "FROM (Table1 INNER JOIN Table2 AS [Another Table] ON (Table1.id = [Another Table].id)) LEFT OUTER JOIN [Select val from Table3].val AS Table3Val ON (Table1.id = Table3Val.id);"), + query.toSQLString()); + + addRows(query, newRow(JOIN_ATTRIBUTE, "(Table1.id = Table3Val.id)", 5, "Table1", "Table3Val")); + + try { + query.toSQLString(); + fail("IllegalStateException should have been thrown"); + } catch(IllegalStateException e) { + // success + } + + removeLastRows(query, 1); + query.toSQLString(); + + addRows(query, newRow(JOIN_ATTRIBUTE, "(Table1.id = Table3Val.id)", 1, "BogusTable", "Table3Val")); + + try { + query.toSQLString(); + fail("IllegalStateException should have been thrown"); + } catch(IllegalStateException e) { + // success + } + + removeRows(query, JOIN_ATTRIBUTE); + } + + private void doTestWhereExpression(SelectQuery query) throws Exception + { + addRows(query, newRow(WHERE_ATTRIBUTE, "(Table1.col2 < 13)", + null, null)); + + assertEquals(multiline("SELECT Table1.id, Table1.col AS [Some.Alias]", + "FROM Table1, Table2 AS [Another Table], [Select val from Table3].val AS Table3Val", + "WHERE (Table1.col2 < 13);"), + query.toSQLString()); + } + + private void doTestGroupings(SelectQuery query) throws Exception + { + addRows(query, newRow(GROUPBY_ATTRIBUTE, "Table1.id", null, null), + newRow(GROUPBY_ATTRIBUTE, "SUM(Table1.val)", null, null)); + + assertEquals(multiline("SELECT Table1.id, Table1.col AS [Some.Alias]", + "FROM Table1, Table2 AS [Another Table], [Select val from Table3].val AS Table3Val", + "WHERE (Table1.col2 < 13)", + "GROUP BY Table1.id, SUM(Table1.val);"), + query.toSQLString()); + } + + private void doTestHavingExpression(SelectQuery query) throws Exception + { + addRows(query, newRow(HAVING_ATTRIBUTE, "(SUM(Table1.val) = 500)", null, null)); + + assertEquals(multiline("SELECT Table1.id, Table1.col AS [Some.Alias]", + "FROM Table1, Table2 AS [Another Table], [Select val from Table3].val AS Table3Val", + "WHERE (Table1.col2 < 13)", + "GROUP BY Table1.id, SUM(Table1.val)", + "HAVING (SUM(Table1.val) = 500);"), + query.toSQLString()); + } + + private void doTestOrderings(SelectQuery query) throws Exception + { + addRows(query, newRow(ORDERBY_ATTRIBUTE, "Table1.id", null, null), + newRow(ORDERBY_ATTRIBUTE, "Table2.val", "D", null)); + + assertEquals(multiline("SELECT Table1.id, Table1.col AS [Some.Alias]", + "FROM Table1, Table2 AS [Another Table], [Select val from Table3].val AS Table3Val", + "WHERE (Table1.col2 < 13)", + "GROUP BY Table1.id, SUM(Table1.val)", + "HAVING (SUM(Table1.val) = 500)", + "ORDER BY Table1.id, Table2.val DESC;"), + query.toSQLString()); + } + + private static Query newQuery(Query.Type type, Row... rows) { return newQuery(type, null, null, rows); @@ -162,9 +418,19 @@ public class QueryTest extends TestCase private static void setFlag(Query query, Number newFlagNum) { - removeRows(query, FLAG_ATTRIBUTE); - query.getRows().add( - newRow(FLAG_ATTRIBUTE, null, newFlagNum, null, null, null)); + replaceRows(query, + newRow(FLAG_ATTRIBUTE, null, newFlagNum, null, null, null)); + } + + private static void addRows(Query query, Row... rows) + { + query.getRows().addAll(Arrays.asList(rows)); + } + + private static void replaceRows(Query query, Row... rows) + { + removeRows(query, rows[0].attribute); + addRows(query, rows); } private static void removeRows(Query query, Byte attr) @@ -176,6 +442,13 @@ public class QueryTest extends TestCase } } + private static void removeLastRows(Query query, int num) + { + List rows = query.getRows(); + int size = rows.size(); + rows.subList(size - num, size).clear(); + } + private static String multiline(String... strs) { return StringUtils.join(strs, LINE_SEPARATOR); diff --git a/test/src/resources/log4j_test.properties b/test/src/resources/log4j_test.properties new file mode 100644 index 0000000..6647228 --- /dev/null +++ b/test/src/resources/log4j_test.properties @@ -0,0 +1,6 @@ +log4j.rootCategory=INFO, stdout +log4j.appender.stdout=org.apache.log4j.ConsoleAppender +log4j.appender.stdout.layout=org.apache.log4j.PatternLayout +log4j.appender.stdout.layout.ConversionPattern=**** %-5p %d{MMM d HH:mm:ss} [%F] - %m%n + +log4j.category.com.healthmarketscience.jackcess=ERROR -- 2.39.5