| /* |
| * Licensed to the Apache Software Foundation (ASF) under one |
| * or more contributor license agreements. See the NOTICE file |
| * distributed with this work for additional information |
| * regarding copyright ownership. The ASF licenses this file |
| * to you 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 org.ofbiz.sql.test; |
| |
| import java.io.StringReader; |
| import java.util.Collections; |
| import java.util.Iterator; |
| import java.util.List; |
| |
| import org.ofbiz.base.test.GenericTestCaseBase; |
| import org.ofbiz.sql.AggregateFunction; |
| import org.ofbiz.sql.BetweenCondition; |
| import org.ofbiz.sql.BooleanCondition; |
| import org.ofbiz.sql.Condition; |
| import org.ofbiz.sql.ConditionList; |
| import org.ofbiz.sql.ConstantValue; |
| import org.ofbiz.sql.FieldAll; |
| import org.ofbiz.sql.FieldDef; |
| import org.ofbiz.sql.FieldValue; |
| import org.ofbiz.sql.FunctionCall; |
| import org.ofbiz.sql.InsertRow; |
| import org.ofbiz.sql.InsertValues; |
| import org.ofbiz.sql.Joined; |
| import org.ofbiz.sql.Joiner; |
| import org.ofbiz.sql.KeyMap; |
| import org.ofbiz.sql.ListCondition; |
| import org.ofbiz.sql.MathValue; |
| import org.ofbiz.sql.NumberValue; |
| import org.ofbiz.sql.OrderByItem; |
| import org.ofbiz.sql.ParameterValue; |
| import org.ofbiz.sql.Parser; |
| import org.ofbiz.sql.Relation; |
| import org.ofbiz.sql.SQLDelete; |
| import org.ofbiz.sql.SQLIndex; |
| import org.ofbiz.sql.SQLInsert; |
| import org.ofbiz.sql.SQLSelect; |
| import org.ofbiz.sql.SQLStatement; |
| import org.ofbiz.sql.SQLUpdate; |
| import org.ofbiz.sql.SQLView; |
| import org.ofbiz.sql.SetField; |
| import org.ofbiz.sql.StringValue; |
| import org.ofbiz.sql.Table; |
| import org.ofbiz.sql.TableName; |
| import org.ofbiz.sql.Value; |
| |
| public class SQLTest extends GenericTestCaseBase { |
| public SQLTest(String name) { |
| super(name); |
| } |
| |
| private static Parser parser(SQLStatement<?> s) throws Exception { |
| return new Parser(new StringReader(s.toString())); |
| } |
| |
| public void testParse() throws Exception { |
| List<SQLStatement<?>> statements = new Parser(getClass().getResourceAsStream("GoodParseAll.sql")).SQLFile(); |
| for (SQLStatement<?> statement: statements) { |
| System.err.println(statement); |
| } |
| Iterator<SQLStatement<?>> stmtIt = statements.iterator(); |
| assertTrue("has more statements", stmtIt.hasNext()); |
| |
| { |
| SQLSelect select = new SQLSelect( |
| false, |
| list( |
| new FieldAll("a", Collections.<String>emptySet()), |
| new FieldAll("b", set("partyId")), |
| new FieldAll("c", set("partyId")) |
| ), |
| GenericTestCaseBase.<String, FieldDef>map( |
| "roleTypeId", new FieldDef(new FieldValue("d", "roleTypeId"), null), |
| "roleDescription", new FieldDef(new FieldValue("d", "description"), "roleDescription"), |
| "SUM", new FieldDef(new AggregateFunction("SUM", false, new FieldValue("a", "partyId")), null), |
| "baz", new FieldDef(new FunctionCall("FOO", GenericTestCaseBase.<Value>list(new FieldValue("a", "partyId"), new NumberValue<Integer>(Integer.valueOf(1)))), "baz"), |
| "one", new FieldDef(new MathValue("||", list(new FieldValue("a", "partyId"), new StringValue("-"), new FieldValue("a", "partyTypeId"))), "one"), |
| "cnt1", new FieldDef(new AggregateFunction("COUNT", false, new FieldValue("a", "partyId")), "cnt1"), |
| "cnt2", new FieldDef(new AggregateFunction("COUNT", false, new FieldValue(null, "partyId")), "cnt2"), |
| "cnt3", new FieldDef(new AggregateFunction("COUNT", true, new FieldValue("a", "partyId")), "cnt3") |
| ), |
| new Table( |
| new TableName("Party", "a"), |
| new Joined(true, new TableName("Person", "b"), list(new KeyMap("partyId", "partyId")), |
| new Joined(true, new TableName("PartyGroup", "c"), list(new KeyMap("partyId", "partyId")), |
| new Joined(false, new TableName("PartyRole", "d"), list(new KeyMap("partyId", "partyId"), new KeyMap("partyId", "partyId"))) |
| ) |
| ) |
| ), |
| GenericTestCaseBase.<String, Relation>map( |
| "MainAPerson", new Relation("one", "MainA", "Person", list(new KeyMap("partyId", "partyId"))), |
| "MainBPerson", new Relation(null, "MainB", "Person", list(new KeyMap("partyId", "partyId"))), |
| "Person", new Relation("one", null, "Person", list(new KeyMap("partyId", "partyId"))), |
| "PartyGroup", new Relation(null, null, "PartyGroup", list(new KeyMap("partyId", "partyId"))) |
| ), |
| new ConditionList( |
| Joiner.OR, |
| GenericTestCaseBase.<Condition>list( |
| new ConditionList( |
| Joiner.AND, |
| list( |
| new BooleanCondition(new FieldValue("a", "partyTypeId"), "=", new StringValue("PERSON")), |
| new BooleanCondition(new FieldValue("b", "lastName"), "LIKE", new ParameterValue("lastName")), |
| new BetweenCondition(new FieldValue("b", "birthDate"), new StringValue("1974-12-01"), new StringValue("1974-12-31")) |
| ) |
| ), |
| new ConditionList( |
| Joiner.AND, |
| list( |
| new ListCondition(new FieldValue("b", "partyId"), "IN", GenericTestCaseBase.<Value>list( |
| new StringValue("1"), |
| new StringValue("2"), |
| new StringValue("3"), |
| new StringValue("4") |
| )), |
| new BooleanCondition(new FieldValue("b", "gender"), "=", new StringValue("M")) |
| ) |
| ) |
| ) |
| ), |
| new BooleanCondition(new FieldValue("b", "firstName"), "LIKE", new StringValue("%foo%")), |
| null, |
| list( |
| new OrderByItem(OrderByItem.Order.DEFAULT, OrderByItem.Nulls.DEFAULT, new FunctionCall("LOWER", GenericTestCaseBase.<Value>list(new FieldValue(null, "lastName")))), |
| new OrderByItem(OrderByItem.Order.DEFAULT, OrderByItem.Nulls.DEFAULT, new FieldValue(null, "firstName")), |
| new OrderByItem(OrderByItem.Order.DESCENDING, OrderByItem.Nulls.DEFAULT, new FieldValue(null, "birthDate")) |
| ), |
| 5, |
| 10 |
| ); |
| SQLStatement<?> stmt = stmtIt.next(); |
| assertEquals("firstSelect", select, stmt); |
| assertEquals("firstSelect:parse", parser(select).SelectStatement(), parser(stmt).SelectStatement()); |
| } |
| { |
| SQLInsert insert = new SQLInsert( |
| new TableName("Party", null), |
| new InsertValues( |
| list( |
| new InsertRow(GenericTestCaseBase.<Value>list(new StringValue("a"), new StringValue("PERSON"), new StringValue("PARTY_DISABLED"))), |
| new InsertRow(list(new NumberValue<Integer>(Integer.valueOf(5)), new StringValue("PARTY_GROUP"), new ParameterValue("name"))) |
| ) |
| ), |
| list("partyId", "partyTypeId", "statusId") |
| ); |
| SQLStatement<?> stmt = stmtIt.next(); |
| assertEquals("firstInsert", insert, stmt); |
| assertEquals("firstInsert:parse", parser(insert).InsertStatement(), parser(stmt).InsertStatement()); |
| } |
| { |
| SQLInsert insert = new SQLInsert( |
| new TableName("Person", null), |
| new SQLSelect( |
| false, |
| null, |
| GenericTestCaseBase.<String, FieldDef>map( |
| "partyId", new FieldDef(new FieldValue(null, "partyId"), null), |
| "firstName", new FieldDef(new MathValue("||", list(new FieldValue(null, "partyId"), new StringValue("-auto"))), "firstName") |
| ), |
| new Table(new TableName("Party", null), null), |
| null, |
| new ListCondition(new FieldValue(null, "partyId"), "IN", GenericTestCaseBase.<Value>list(new StringValue("a"), new StringValue("b"))), |
| null, |
| null, |
| null, |
| -1, |
| -1 |
| ), |
| list("partyId", "firstName") |
| ); |
| SQLStatement<?> stmt = stmtIt.next(); |
| assertEquals("secondInsert", insert, stmt); |
| assertEquals("secondInsert:parse", parser(insert).InsertStatement(), parser(stmt).InsertStatement()); |
| } |
| { |
| SQLUpdate update = new SQLUpdate( |
| new Table(new TableName("Person", null), null), |
| list( |
| new SetField("lastName", new MathValue("||", list(new StringValue("auto-"), new FieldValue(null, "partyId")))) |
| ), |
| new ListCondition(new FieldValue(null, "partyId"), "IN", GenericTestCaseBase.<Value>list(new StringValue("a"), new StringValue("b"))) |
| ); |
| SQLStatement<?> stmt = stmtIt.next(); |
| assertEquals("firstUpdate", update, stmt); |
| assertEquals("firstUpdate:parse", parser(update).UpdateStatement(), parser(stmt).UpdateStatement()); |
| } |
| { |
| SQLUpdate update = new SQLUpdate( |
| new Table(new TableName("Person", null), null), |
| list( |
| new SetField("lastName", new MathValue("||", list(new StringValue("auto-"), new FieldValue(null, "partyId")))), |
| new SetField("height", new NumberValue<Integer>(Integer.valueOf(5))), |
| new SetField("width", new NumberValue<Integer>(Integer.valueOf(7))) |
| ), |
| new ListCondition(new FieldValue(null, "partyId"), "IN", GenericTestCaseBase.<Value>list(new StringValue("a"), new StringValue("b"))) |
| ); |
| SQLStatement<?> stmt = stmtIt.next(); |
| assertEquals("secondUpdate", update, stmt); |
| assertEquals("secondUpdate:parse", parser(update).UpdateStatement(), parser(stmt).UpdateStatement()); |
| } |
| { |
| SQLUpdate update = new SQLUpdate( |
| new Table(new TableName("Person", null), null), |
| list( |
| new SetField("lastName", new MathValue("||", list(new StringValue("auto-"), new FieldValue(null, "partyId")))), |
| new SetField("height", new NumberValue<Integer>(Integer.valueOf(6))), |
| new SetField("width", new NumberValue<Integer>(Integer.valueOf(5))), |
| new SetField("nickname", new StringValue("a")) |
| ), |
| new ListCondition(new FieldValue(null, "partyId"), "IN", GenericTestCaseBase.<Value>list(new StringValue("a"), new StringValue("b"))) |
| ); |
| SQLStatement<?> stmt = stmtIt.next(); |
| assertEquals("thirdUpdate", update, stmt); |
| assertEquals("thirdUpdate:parse", parser(update).UpdateStatement(), parser(stmt).UpdateStatement()); |
| } |
| { |
| SQLDelete delete = new SQLDelete( |
| new Table(new TableName("Person", null), null), |
| new ListCondition(new FieldValue(null, "partyId"), "IN", GenericTestCaseBase.<Value>list(new StringValue("a"), new StringValue("b"))) |
| ); |
| SQLStatement<?> stmt = stmtIt.next(); |
| assertEquals("firstDelete", delete, stmt); |
| assertEquals("firstDelete:parse", parser(delete).DeleteStatement(), parser(stmt).DeleteStatement()); |
| } |
| { |
| SQLDelete delete = new SQLDelete( |
| new Table(new TableName("Party", null), null), |
| new ListCondition(new FieldValue(null, "partyId"), "IN", GenericTestCaseBase.<Value>list(new StringValue("a"), new StringValue("b"))) |
| ); |
| SQLStatement<?> stmt = stmtIt.next(); |
| assertEquals("secondDelete", delete, stmt); |
| assertEquals("secondDelete:parse", parser(delete).DeleteStatement(), parser(stmt).DeleteStatement()); |
| } |
| { |
| SQLView view = new SQLView( |
| "viewOne", |
| new SQLSelect( |
| false, |
| list(new FieldAll("a", Collections.<String>emptySet())), |
| null, |
| new Table(new TableName("Party", "a"), null), |
| null, |
| null, |
| null, |
| null, |
| null, |
| -1, |
| -1 |
| ) |
| ); |
| SQLStatement<?> stmt = stmtIt.next(); |
| assertEquals("firstView", view, stmt); |
| assertEquals("firstView:parse", parser(view).ViewStatement(), parser(stmt).ViewStatement()); |
| } |
| { |
| SQLIndex index = new SQLIndex( |
| false, |
| "testIndex", |
| "Party", |
| "btree", |
| GenericTestCaseBase.<ConstantValue>list( |
| new FieldValue(null, "partyId") |
| ) |
| ); |
| SQLStatement<?> stmt = stmtIt.next(); |
| assertEquals("firstIndex", index, stmt); |
| } |
| assertFalse("has no more statements", stmtIt.hasNext()); |
| } |
| /* |
| CREATE VIEW viewOne AS SELECT a.* FROM Party a; |
| */ |
| } |