From 1e4fc9cc867b925d82410f5ab3d5091987d60c4a Mon Sep 17 00:00:00 2001 From: James Moger Date: Wed, 22 Oct 2014 20:40:52 -0400 Subject: Implement execution tests of nested conditions and documentation --- releases.moxie | 4 +- src/site/model_classes.mkd | 4 +- src/site/usage.mkd | 40 +++- src/test/java/com/iciql/test/IciqlSuite.java | 2 +- .../java/com/iciql/test/NestedConditionsTest.java | 222 +++++++++++++++++++++ src/test/java/com/iciql/test/OneOfTest.java | 32 ++- .../com/iciql/test/StackableConditionsTest.java | 197 ------------------ src/test/java/com/iciql/test/models/Customer.java | 12 +- 8 files changed, 305 insertions(+), 208 deletions(-) create mode 100644 src/test/java/com/iciql/test/NestedConditionsTest.java delete mode 100644 src/test/java/com/iciql/test/StackableConditionsTest.java diff --git a/releases.moxie b/releases.moxie index b81afc4..47e06d3 100644 --- a/releases.moxie +++ b/releases.moxie @@ -12,9 +12,9 @@ r21: { fixes: - Return null NPE in selectFirst() if list is empty (pr-5) - Fix Moxie toolkit download URL (pr-6) - - Be more careful with primitive numeric type rollovers + - Be more careful with primitive numeric type rollovers (pr-6) changes: - - Revised EnumId interface to support generic types + - Revised EnumId interface to support generic types (pr-6) additions: - Add syntax for IN and NOT IN (pr-7) - Add support for nested AND/OR conditions (pr-8) diff --git a/src/site/model_classes.mkd b/src/site/model_classes.mkd index ea91bb0..cb21dcd 100644 --- a/src/site/model_classes.mkd +++ b/src/site/model_classes.mkd @@ -67,7 +67,7 @@ can be used for all iciql expressions INT
EnumType.ORDINAL
can only declare and explicitly reference one instance of each enum type per model
multiple instances of an enum type within a model is allowed if not using where/set/on/and/or/groupBy/orderBy(enum) java.lang.Enum implements
com.iciql.Iciql.EnumId.enumId() -INT
EnumType.ENUMID
can only declare and explicitly reference one instance of each enum type per model
multiple instances of an enum type within a model is allowed if not using where/set/on/and/or/groupBy/orderBy(enum) +variable
EnumType.ENUMID
can only declare and explicitly reference one instance of each enum type per model
multiple instances of an enum type within a model is allowed if not using where/set/on/and/or/groupBy/orderBy(enum) Partially Supported Types
can not be directly referenced in an expression @@ -383,4 +383,4 @@ public class Product { public Product() { } } ----JAVA--- \ No newline at end of file +---JAVA--- diff --git a/src/site/usage.mkd b/src/site/usage.mkd index b1f4c48..8b54dce 100644 --- a/src/site/usage.mkd +++ b/src/site/usage.mkd @@ -69,6 +69,44 @@ List allProducts = db.buildObjects(Product.class, rs); JdbcUtils.closeSilently(rs, true); ---JAVA--- +### Compound Conditions + +It is possible to specify type-safe compound where clauses using nested `And` and `Or` statements. + +---JAVA--- +final Customer model = new Customer(); + +List regionals = + db.from(model).where(model.customerId).isNotNull() + .and(model.region).isNotNull() + .and(new Or(db, model) {{ + or(model.region).is("LA"); + or(model.region).is("CA"); + }}); + +List regionalType1s = + db.from(model).where(new And(db, model) {{ + and(model.type).is(1); + and(new Or(db, model) {{ + or(model.region).is("CA"); + or(model.region).is("LA"); + }}); + }}); + +---JAVA--- + +### Finding Matches for a List of Values + +You can use SQL's *IN* WHERE clause with the `oneOf` or `noneOf` conditions. + +---JAVA--- +final Customer model = new Customer(); + +List regionals = db.from(model).where(model.region).oneOf("CA", "LA"); +List others = db.from(model).where(model.region).noneOf("CA", "LA"); + +---JAVA--- + ### Read-only Views View model classes can inherit their field definitions from a parent table model class. @@ -232,4 +270,4 @@ for (int i = 0; i < 5; i++) { ---JAVA--- - \ No newline at end of file + diff --git a/src/test/java/com/iciql/test/IciqlSuite.java b/src/test/java/com/iciql/test/IciqlSuite.java index 0273399..c5d7ce8 100644 --- a/src/test/java/com/iciql/test/IciqlSuite.java +++ b/src/test/java/com/iciql/test/IciqlSuite.java @@ -93,7 +93,7 @@ import com.iciql.util.Utils; @SuiteClasses({ AliasMapTest.class, AnnotationsTest.class, BooleanModelTest.class, ClobTest.class, ConcurrencyTest.class, EnumsTest.class, ModelsTest.class, PrimitivesTest.class, OneOfTest.class, RuntimeQueryTest.class, SamplesTest.class, UpdateTest.class, UpgradesTest.class, JoinTest.class, - UUIDTest.class, ViewsTest.class, ForeignKeyTest.class, TransactionTest.class, StackableConditionsTest.class }) + UUIDTest.class, ViewsTest.class, ForeignKeyTest.class, TransactionTest.class, NestedConditionsTest.class }) public class IciqlSuite { private static final TestDb[] TEST_DBS = { diff --git a/src/test/java/com/iciql/test/NestedConditionsTest.java b/src/test/java/com/iciql/test/NestedConditionsTest.java new file mode 100644 index 0000000..48bcfb3 --- /dev/null +++ b/src/test/java/com/iciql/test/NestedConditionsTest.java @@ -0,0 +1,222 @@ +/* + * Copyright (c) 2009-2014, Architector Inc., Japan + * All rights reserved. + * + * Licensed under the Apache License, Version 2.0 (the "License"); + * you may not use this file except in compliance with the License. + * You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ + +package com.iciql.test; + +import static org.junit.Assert.assertEquals; +import static org.junit.Assert.assertTrue; + +import java.util.List; +import java.util.Set; +import java.util.TreeSet; + +import org.junit.After; +import org.junit.Before; +import org.junit.Test; + +import com.iciql.Conditions.And; +import com.iciql.Conditions.Or; +import com.iciql.Db; +import com.iciql.IciqlException; +import com.iciql.QueryWhere; +import com.iciql.test.models.Customer; + +public class NestedConditionsTest { + + enum Region { + JP, FR + } + + private Db db; + + @Before + public void setUp() { + db = IciqlSuite.openNewDb(); + db.insertAll(Customer.getList()); + } + + @After + public void tearDown() { + db.close(); + } + + private String search(Region region, String... customerIds) { + Customer model; + QueryWhere query; + + model = new Customer(); + query = db.from(model).whereTrue(); + if (customerIds != null) { + query.andOpenFalse(); + for (String value : customerIds) { + query.or(model.customerId).is(value); + } + query.close(); + } + if (region != null) { + query.and(model.region).is(region.name()); + } + return query.toSQL(); + } + + @Test + public void andOrSyntaxTest() { + String Customer = db.getDialect().prepareTableName(null, "Customer"); + String customerId = db.getDialect().prepareColumnName("customerId"); + String region = db.getDialect().prepareColumnName("region"); + + assertEquals( + search(null, (String[]) null), + String.format("SELECT * FROM %s WHERE (true)", + Customer)); + assertEquals( + search(null, new String[0]), + String.format("SELECT * FROM %s WHERE (true) AND ( (false) )", + Customer)); + assertEquals( + search(null, "0001"), + String.format("SELECT * FROM %s WHERE (true) AND ( (false) OR %s = '0001' )", + Customer, customerId)); + assertEquals( + search(null, "0001", "0002"), + String.format("SELECT * FROM %s WHERE (true) AND ( (false) OR %s = '0001' OR %s = '0002' )", + Customer, customerId, customerId)); + assertEquals( + search(Region.JP, (String[]) null), + String.format("SELECT * FROM %s WHERE (true) AND %s = 'JP'", + Customer, region)); + assertEquals( + search(Region.JP, new String[0]), + String.format("SELECT * FROM %s WHERE (true) AND ( (false) ) AND %s = 'JP'", + Customer, region)); + assertEquals( + search(Region.JP, "0001"), + String.format("SELECT * FROM %s WHERE (true) AND ( (false) OR %s = '0001' ) AND %s = 'JP'", + Customer, customerId, region)); + assertEquals( + search(Region.JP, "0001", "0002"), + String.format("SELECT * FROM %s WHERE (true) AND ( (false) OR %s = '0001' OR %s = '0002' ) AND %s = 'JP'", + Customer, customerId, customerId, region)); + } + + @Test + public void errorTest() { + Customer model; + + model = new Customer(); + try { + db.from(model) + .where(model.customerId).is("0001") + .andOpenFalse() + .or(model.region).is("FR") + .or(model.region).is("JP") + .close() + .toSQL(); + assertTrue(true); + } + catch (IciqlException error) { + assertTrue(false); + } + try { + db.from(model) + .where(model.customerId).is("0001") + .andOpenFalse() + .or(model.region).is("FR") + .or(model.region).is("JP") + .toSQL(); + assertTrue(false); + } + catch (IciqlException error) { + assertTrue(true); + } + try { + db.from(model) + .where(model.customerId).is("0001") + .andOpenFalse() + .or(model.region).is("FR") + .or(model.region).is("JP") + .close() + .close(); + assertTrue(false); + } + catch (IciqlException error) { + assertTrue(true); + } + } + + @Test + public void fluentSyntaxTest() { + String Customer = db.getDialect().prepareTableName(null, "Customer"); + String customerId = db.getDialect().prepareColumnName("customerId"); + String region = db.getDialect().prepareColumnName("region"); + + final Customer model = new Customer(); + assertEquals( + db.from(model).where(new And(db, model) {{ + and(model.customerId).is("0001"); + and(new Or(db, model) {{ + or(model.region).is("CA"); + or(model.region).is("LA"); + }}); + }}).toSQL(), + String.format("SELECT * FROM %s WHERE (true) AND %s = '0001' AND ( (false) OR %s = 'CA' OR %s = 'LA' )", + Customer, customerId, region, region)); + assertEquals( + db.from(model).where(new Or(db, model) {{ + or(model.customerId).is("0001"); + or(new And(db, model) {{ + and(model.customerId).is("0002"); + and(model.region).is("LA"); + }}); + }}).toSQL(), + String.format("SELECT * FROM %s WHERE (false) OR %s = '0001' OR ( (true) AND %s = '0002' AND %s = 'LA' )", + Customer, customerId, customerId, region)); + assertEquals( + db.from(model) + .where(model.customerId).isNotNull() + .and(new Or(db, model) {{ + or(model.region).is("LA"); + or(model.region).is("CA"); + }}) + .and(model.region).isNotNull() + .toSQL(), + String.format("SELECT * FROM %s WHERE %s IS NOT NULL AND ( (false) OR %s = 'LA' OR %s = 'CA' ) AND %s IS NOT NULL", + Customer, customerId, region, region, region)); + } + + @Test + public void compoundConditionsTest() { + final Customer c = new Customer(); + List matches = db.from(c) + .where(c.customerId).like("A%") + .and(c.region).isNotNull() + .and(new Or(db, c) {{ + or(c.region).is("LA"); + or(c.region).is("CA"); + }}).select(); + + assertEquals(2, matches.size()); + + Set ids = new TreeSet(); + for (Customer customer : matches) { + ids.add(customer.customerId); + } + assertEquals("[ANTON, ASLAN]", ids.toString()); + + } + +} diff --git a/src/test/java/com/iciql/test/OneOfTest.java b/src/test/java/com/iciql/test/OneOfTest.java index e534262..c4aa90b 100644 --- a/src/test/java/com/iciql/test/OneOfTest.java +++ b/src/test/java/com/iciql/test/OneOfTest.java @@ -20,6 +20,9 @@ package com.iciql.test; import static org.junit.Assert.assertEquals; import java.util.ArrayList; +import java.util.List; +import java.util.Set; +import java.util.TreeSet; import org.junit.After; import org.junit.Before; @@ -36,6 +39,7 @@ public class OneOfTest { @Before public void setUp() { db = IciqlSuite.openNewDb(); + db.insertAll(Customer.getList()); } @After @@ -45,7 +49,7 @@ public class OneOfTest { @SuppressWarnings("serial") @Test - public void oneOfTest() { + public void oneOfSyntaxTest() { String PrimitivesTest = db.getDialect().prepareTableName(null, "PrimitivesTest"); String Customer = db.getDialect().prepareTableName(null, "Customer"); String myInteger = db.getDialect().prepareColumnName("myInteger"); @@ -82,7 +86,7 @@ public class OneOfTest { @SuppressWarnings("serial") @Test - public void noneOfTest() { + public void noneOfSyntaxTest() { String PrimitivesTest = db.getDialect().prepareTableName(null, "PrimitivesTest"); String Customer = db.getDialect().prepareTableName(null, "Customer"); String myInteger = db.getDialect().prepareColumnName("myInteger"); @@ -117,4 +121,28 @@ public class OneOfTest { .toSQL()); } + public void noneOfTest() { + Customer c = new Customer(); + List meAndny = db.from(c).where(c.region).noneOf("WA", "CA", "LA").select(); + assertEquals(2, meAndny.size()); + + Set regions = new TreeSet(); + for (Customer customer : meAndny) { + regions.add(customer.region); + } + assertEquals("[ME, NY]", regions.toString()); + } + + public void oneOfTest() { + Customer c = new Customer(); + List meAndny = db.from(c).where(c.region).oneOf("ME", "NY").select(); + assertEquals(2, meAndny.size()); + + Set regions = new TreeSet(); + for (Customer customer : meAndny) { + regions.add(customer.region); + } + assertEquals("[ME, NY]", regions.toString()); + } + } diff --git a/src/test/java/com/iciql/test/StackableConditionsTest.java b/src/test/java/com/iciql/test/StackableConditionsTest.java deleted file mode 100644 index f812339..0000000 --- a/src/test/java/com/iciql/test/StackableConditionsTest.java +++ /dev/null @@ -1,197 +0,0 @@ -/* - * Copyright (c) 2009-2014, Architector Inc., Japan - * All rights reserved. - * - * Licensed under the Apache License, Version 2.0 (the "License"); - * you may not use this file except in compliance with the License. - * You may obtain a copy of the License at - * - * http://www.apache.org/licenses/LICENSE-2.0 - * - * Unless required by applicable law or agreed to in writing, software - * distributed under the License is distributed on an "AS IS" BASIS, - * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - * See the License for the specific language governing permissions and - * limitations under the License. - */ - -package com.iciql.test; - -import static org.junit.Assert.assertEquals; -import static org.junit.Assert.assertTrue; - -import org.junit.After; -import org.junit.Before; -import org.junit.Test; - -import com.iciql.Conditions.And; -import com.iciql.Conditions.Or; -import com.iciql.Db; -import com.iciql.IciqlException; -import com.iciql.QueryWhere; -import com.iciql.test.models.Customer; - -public class StackableConditionsTest { - - enum Region { - JP, FR - } - - private Db db; - - @Before - public void setUp() { - db = IciqlSuite.openNewDb(); - } - - @After - public void tearDown() { - db.close(); - } - - private String search(Region region, String... customerIds) { - Customer model; - QueryWhere query; - - model = new Customer(); - query = db.from(model).whereTrue(); - if (customerIds != null) { - query.andOpenFalse(); - for (String value : customerIds) { - query.or(model.customerId).is(value); - } - query.close(); - } - if (region != null) { - query.and(model.region).is(region.name()); - } - return query.toSQL(); - } - - @SuppressWarnings("serial") - @Test - public void andOrTest() { - String Customer = db.getDialect().prepareTableName(null, "Customer"); - String customerId = db.getDialect().prepareColumnName("customerId"); - String region = db.getDialect().prepareColumnName("region"); - - assertEquals( - search(null, (String[]) null), - String.format("SELECT * FROM %s WHERE (true)", - Customer)); - assertEquals( - search(null, new String[0]), - String.format("SELECT * FROM %s WHERE (true) AND ( (false) )", - Customer)); - assertEquals( - search(null, "0001"), - String.format("SELECT * FROM %s WHERE (true) AND ( (false) OR %s = '0001' )", - Customer, customerId)); - assertEquals( - search(null, "0001", "0002"), - String.format("SELECT * FROM %s WHERE (true) AND ( (false) OR %s = '0001' OR %s = '0002' )", - Customer, customerId, customerId)); - assertEquals( - search(Region.JP, (String[]) null), - String.format("SELECT * FROM %s WHERE (true) AND %s = 'JP'", - Customer, region)); - assertEquals( - search(Region.JP, new String[0]), - String.format("SELECT * FROM %s WHERE (true) AND ( (false) ) AND %s = 'JP'", - Customer, region)); - assertEquals( - search(Region.JP, "0001"), - String.format("SELECT * FROM %s WHERE (true) AND ( (false) OR %s = '0001' ) AND %s = 'JP'", - Customer, customerId, region)); - assertEquals( - search(Region.JP, "0001", "0002"), - String.format("SELECT * FROM %s WHERE (true) AND ( (false) OR %s = '0001' OR %s = '0002' ) AND %s = 'JP'", - Customer, customerId, customerId, region)); - } - - @Test - public void errorTest() { - Customer model; - - model = new Customer(); - try { - db.from(model) - .where(model.customerId).is("0001") - .andOpenFalse() - .or(model.region).is("FR") - .or(model.region).is("JP") - .close() - .toSQL(); - assertTrue(true); - } - catch (IciqlException error) { - assertTrue(false); - } - try { - db.from(model) - .where(model.customerId).is("0001") - .andOpenFalse() - .or(model.region).is("FR") - .or(model.region).is("JP") - .toSQL(); - assertTrue(false); - } - catch (IciqlException error) { - assertTrue(true); - } - try { - db.from(model) - .where(model.customerId).is("0001") - .andOpenFalse() - .or(model.region).is("FR") - .or(model.region).is("JP") - .close() - .close(); - assertTrue(false); - } - catch (IciqlException error) { - assertTrue(true); - } - } - - @Test - public void fluentTest() { - String Customer = db.getDialect().prepareTableName(null, "Customer"); - String customerId = db.getDialect().prepareColumnName("customerId"); - String region = db.getDialect().prepareColumnName("region"); - - final Customer model = new Customer(); - assertEquals( - db.from(model).where(new And(db, model) {{ - and(model.customerId).is("0001"); - and(new Or(db, model) {{ - or(model.region).is("CA"); - or(model.region).is("LA"); - }}); - }}).toSQL(), - String.format("SELECT * FROM %s WHERE (true) AND %s = '0001' AND ( (false) OR %s = 'CA' OR %s = 'LA' )", - Customer, customerId, region, region)); - assertEquals( - db.from(model).where(new Or(db, model) {{ - or(model.customerId).is("0001"); - or(new And(db, model) {{ - and(model.customerId).is("0002"); - and(model.region).is("LA"); - }}); - }}).toSQL(), - String.format("SELECT * FROM %s WHERE (false) OR %s = '0001' OR ( (true) AND %s = '0002' AND %s = 'LA' )", - Customer, customerId, customerId, region)); - assertEquals( - db.from(model) - .where(model.customerId).isNotNull() - .and(new Or(db, model) {{ - or(model.region).is("LA"); - or(model.region).is("CA"); - }}) - .and(model.region).isNotNull() - .toSQL(), - String.format("SELECT * FROM %s WHERE %s IS NOT NULL AND ( (false) OR %s = 'LA' OR %s = 'CA' ) AND %s IS NOT NULL", - Customer, customerId, region, region, region)); - } - -} diff --git a/src/test/java/com/iciql/test/models/Customer.java b/src/test/java/com/iciql/test/models/Customer.java index ae8e40d..4270623 100644 --- a/src/test/java/com/iciql/test/models/Customer.java +++ b/src/test/java/com/iciql/test/models/Customer.java @@ -44,14 +44,20 @@ public class Customer { this.region = region; } + @Override public String toString() { return customerId; } public static List getList() { - Customer[] list = { new Customer("ALFKI", "WA"), new Customer("ANATR", "WA"), - new Customer("ANTON", "CA") }; - return Arrays.asList(list); + return Arrays.asList( + new Customer("ALFKI", "WA"), + new Customer("ANATR", "WA"), + new Customer("ASLAN", "CA"), + new Customer("ANTON", "CA"), + new Customer("BROWN", "LA"), + new Customer("SMITH", "NY"), + new Customer("JONES", "ME")); } } -- cgit v1.2.3