Repository: phoenix Updated Branches: refs/heads/master 1666e932d -> 2cb617f35
http://git-wip-us.apache.org/repos/asf/phoenix/blob/2cb617f3/phoenix-core/src/main/java/org/apache/phoenix/util/CursorUtil.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/main/java/org/apache/phoenix/util/CursorUtil.java b/phoenix-core/src/main/java/org/apache/phoenix/util/CursorUtil.java new file mode 100644 index 0000000..877c436 --- /dev/null +++ b/phoenix-core/src/main/java/org/apache/phoenix/util/CursorUtil.java @@ -0,0 +1,189 @@ +/* + * 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.apache.phoenix.util; + +import java.sql.Connection; +import java.sql.SQLException; +import java.util.HashMap; +import java.util.Map; + +import org.apache.hadoop.hbase.client.Scan; +import org.apache.hadoop.hbase.io.ImmutableBytesWritable; +import org.apache.phoenix.compile.QueryPlan; +import org.apache.phoenix.compile.OrderByCompiler.OrderBy; +import org.apache.phoenix.execute.CursorFetchPlan; +import org.apache.phoenix.iterate.CursorResultIterator; +import org.apache.phoenix.parse.CloseStatement; +import org.apache.phoenix.parse.DeclareCursorStatement; +import org.apache.phoenix.parse.OpenStatement; +import org.apache.phoenix.schema.tuple.Tuple; + +public final class CursorUtil { + + private static class CursorWrapper { + private final String cursorName; + private final String selectSQL; + private boolean isOpen = false; + QueryPlan queryPlan; + ImmutableBytesWritable row; + ImmutableBytesWritable previousRow; + private Scan scan; + private boolean moreValues=true; + private boolean isReversed; + private boolean islastCallNext; + private CursorFetchPlan fetchPlan; + private int offset = -1; + private boolean isAggregate; + + private CursorWrapper(String cursorName, String selectSQL, QueryPlan queryPlan){ + this.cursorName = cursorName; + this.selectSQL = selectSQL; + this.queryPlan = queryPlan; + this.islastCallNext = true; + this.fetchPlan = new CursorFetchPlan(queryPlan,cursorName); + isAggregate = fetchPlan.isAggregate(); + } + + private synchronized void openCursor(Connection conn) throws SQLException { + if(isOpen){ + return; + } + this.scan = this.queryPlan.getContext().getScan(); + isReversed=OrderBy.REV_ROW_KEY_ORDER_BY.equals(this.queryPlan.getOrderBy()); + isOpen = true; + } + + private void closeCursor() throws SQLException { + isOpen = false; + ((CursorResultIterator) fetchPlan.iterator()).closeCursor(); + //TODO: Determine if the cursor should be removed from the HashMap at this point. + //Semantically it makes sense that something which is 'Closed' one should be able to 'Open' again. + mapCursorIDQuery.remove(this.cursorName); + } + + private QueryPlan getFetchPlan(boolean isNext, int fetchSize) throws SQLException { + if (!isOpen) + throw new SQLException("Fetch call on closed cursor '" + this.cursorName + "'!"); + ((CursorResultIterator)fetchPlan.iterator()).setFetchSize(fetchSize); + if (!isAggregate) { + if (row!=null){ + scan.setStartRow(row.get()); + } + } + return this.fetchPlan; + } + + public void updateLastScanRow(Tuple rowValues,Tuple nextRowValues) { + + this.moreValues = !isReversed ? nextRowValues != null : rowValues != null; + if(!moreValues()){ + return; + } + if (row == null) { + row = new ImmutableBytesWritable(); + } + if (previousRow == null) { + previousRow = new ImmutableBytesWritable(); + } + if (nextRowValues != null) { + nextRowValues.getKey(row); + } + if (rowValues != null) { + rowValues.getKey(previousRow); + } + offset++; + } + + public boolean moreValues() { + return moreValues; + } + + public String getFetchSQL() throws SQLException { + if (!isOpen) + throw new SQLException("Fetch call on closed cursor '" + this.cursorName + "'!"); + return selectSQL; + } + } + + private static Map<String, CursorWrapper> mapCursorIDQuery = new HashMap<String,CursorWrapper>(); + + /** + * Private constructor + */ + private CursorUtil() { + } + + /** + * + * @param stmt DeclareCursorStatement instance intending to declare a new cursor. + * @return Returns true if the new cursor was successfully declared. False if a cursor with the same + * identifier already exists. + */ + public static boolean declareCursor(DeclareCursorStatement stmt, QueryPlan queryPlan) throws SQLException { + if(mapCursorIDQuery.containsKey(stmt.getCursorName())){ + throw new SQLException("Can't declare cursor " + stmt.getCursorName() + ", cursor identifier already in use."); + } else { + mapCursorIDQuery.put(stmt.getCursorName(), new CursorWrapper(stmt.getCursorName(), stmt.getQuerySQL(), queryPlan)); + return true; + } + } + + public static boolean openCursor(OpenStatement stmt, Connection conn) throws SQLException { + if(mapCursorIDQuery.containsKey(stmt.getCursorName())){ + mapCursorIDQuery.get(stmt.getCursorName()).openCursor(conn); + return true; + } else{ + throw new SQLException("Cursor " + stmt.getCursorName() + " not declared."); + } + } + + public static void closeCursor(CloseStatement stmt) throws SQLException { + if(mapCursorIDQuery.containsKey(stmt.getCursorName())){ + mapCursorIDQuery.get(stmt.getCursorName()).closeCursor(); + } + } + + public static QueryPlan getFetchPlan(String cursorName, boolean isNext, int fetchSize) throws SQLException { + if(mapCursorIDQuery.containsKey(cursorName)){ + return mapCursorIDQuery.get(cursorName).getFetchPlan(isNext, fetchSize); + } else { + throw new SQLException("Cursor " + cursorName + " not declared."); + } + } + + public static String getFetchSQL(String cursorName) throws SQLException { + if (mapCursorIDQuery.containsKey(cursorName)) { + return mapCursorIDQuery.get(cursorName).getFetchSQL(); + } else { + throw new SQLException("Cursor " + cursorName + " not declared."); + } + } + + public static void updateCursor(String cursorName, Tuple rowValues, Tuple nextRowValues) throws SQLException { + mapCursorIDQuery.get(cursorName).updateLastScanRow(rowValues,nextRowValues); + } + + public static boolean cursorDeclared(String cursorName){ + return mapCursorIDQuery.containsKey(cursorName); + } + + public static boolean moreValues(String cursorName) { + return mapCursorIDQuery.get(cursorName).moreValues(); + } +} http://git-wip-us.apache.org/repos/asf/phoenix/blob/2cb617f3/phoenix-core/src/main/java/org/apache/phoenix/util/ScanUtil.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/main/java/org/apache/phoenix/util/ScanUtil.java b/phoenix-core/src/main/java/org/apache/phoenix/util/ScanUtil.java index 1fdc73b..9794a2a 100644 --- a/phoenix-core/src/main/java/org/apache/phoenix/util/ScanUtil.java +++ b/phoenix-core/src/main/java/org/apache/phoenix/util/ScanUtil.java @@ -613,6 +613,10 @@ public class ScanUtil { scan.setAttribute(BaseScannerRegionObserver.REVERSE_SCAN, PDataType.TRUE_BYTES); } + public static void unsetReversed(Scan scan) { + scan.setAttribute(BaseScannerRegionObserver.REVERSE_SCAN, PDataType.FALSE_BYTES); + } + private static byte[] getReversedRow(byte[] startRow) { /* * Must get previous key because this is going from an inclusive start key to an exclusive stop key, and we need @@ -926,4 +930,4 @@ public class ScanUtil { return scan.getAttribute((BaseScannerRegionObserver.REBUILD_INDEXES)) != null; } -} \ No newline at end of file +} http://git-wip-us.apache.org/repos/asf/phoenix/blob/2cb617f3/phoenix-core/src/test/java/org/apache/phoenix/compile/CursorCompilerTest.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/test/java/org/apache/phoenix/compile/CursorCompilerTest.java b/phoenix-core/src/test/java/org/apache/phoenix/compile/CursorCompilerTest.java new file mode 100644 index 0000000..a8f37f0 --- /dev/null +++ b/phoenix-core/src/test/java/org/apache/phoenix/compile/CursorCompilerTest.java @@ -0,0 +1,87 @@ +/* + * 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.apache.phoenix.compile; + +import org.apache.hadoop.hbase.client.Scan; +import org.apache.hadoop.hbase.filter.Filter; +import org.apache.hadoop.hbase.filter.FirstKeyOnlyFilter; +import org.apache.hadoop.hbase.util.Bytes; +import org.apache.phoenix.compile.OrderByCompiler.OrderBy; +import org.apache.phoenix.coprocessor.BaseScannerRegionObserver; +import org.apache.phoenix.exception.SQLExceptionCode; +import org.apache.phoenix.expression.Expression; +import org.apache.phoenix.expression.LiteralExpression; +import org.apache.phoenix.expression.aggregator.Aggregator; +import org.apache.phoenix.expression.aggregator.CountAggregator; +import org.apache.phoenix.expression.aggregator.ServerAggregators; +import org.apache.phoenix.expression.function.TimeUnit; +import org.apache.phoenix.filter.ColumnProjectionFilter; +import org.apache.phoenix.jdbc.PhoenixConnection; +import org.apache.phoenix.jdbc.PhoenixDatabaseMetaData; +import org.apache.phoenix.jdbc.PhoenixPreparedStatement; +import org.apache.phoenix.jdbc.PhoenixStatement; +import org.apache.phoenix.query.BaseConnectionlessQueryTest; +import org.apache.phoenix.query.QueryConstants; +import org.apache.phoenix.query.QueryServices; +import org.apache.phoenix.schema.*; +import org.apache.phoenix.util.*; +import org.junit.Test; + +import java.math.BigDecimal; +import java.sql.*; +import java.util.*; + +import static org.apache.phoenix.util.TestUtil.TEST_PROPERTIES; +import static org.apache.phoenix.util.TestUtil.assertDegenerate; +import static org.junit.Assert.*; + + +/** + * + * Test for compiling the various cursor related statements + * + * + * @since 0.1 + */ +@edu.umd.cs.findbugs.annotations.SuppressWarnings( + value="RV_RETURN_VALUE_IGNORED", + justification="Test code.") +public class CursorCompilerTest extends BaseConnectionlessQueryTest { + + @Test + public void testCursorLifecycleCompile() throws SQLException { + String query = "SELECT a_string, b_string FROM atable"; + String sql = "DECLARE testCursor CURSOR FOR " + query; + Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); + Connection conn = DriverManager.getConnection(getUrl(), props); + //Test declare cursor compile + PreparedStatement statement = conn.prepareStatement(sql); + //Test declare cursor execution + statement.execute(); + assertTrue(CursorUtil.cursorDeclared("testCursor")); + //Test open cursor compile + sql = "OPEN testCursor"; + statement = conn.prepareStatement(sql); + //Test open cursor execution + statement.execute(); + //Test fetch cursor compile + sql = "FETCH NEXT FROM testCursor"; + statement = conn.prepareStatement(sql); + statement.executeQuery(); + } +} \ No newline at end of file http://git-wip-us.apache.org/repos/asf/phoenix/blob/2cb617f3/phoenix-core/src/test/java/org/apache/phoenix/parse/CursorParserTest.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/test/java/org/apache/phoenix/parse/CursorParserTest.java b/phoenix-core/src/test/java/org/apache/phoenix/parse/CursorParserTest.java new file mode 100644 index 0000000..247ee44 --- /dev/null +++ b/phoenix-core/src/test/java/org/apache/phoenix/parse/CursorParserTest.java @@ -0,0 +1,367 @@ + /* + * 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.apache.phoenix.parse; + +import org.apache.hadoop.hbase.util.Pair; +import org.apache.phoenix.exception.SQLExceptionCode; +import org.apache.phoenix.jdbc.PhoenixStatement.Operation; +import org.apache.phoenix.schema.SortOrder; +import org.junit.Test; + +import java.io.IOException; +import java.io.StringReader; +import java.sql.SQLException; +import java.sql.SQLFeatureNotSupportedException; +import java.util.List; + +import static org.junit.Assert.*; + + +public class CursorParserTest { + + private void parseCursor(String sql) throws IOException, SQLException { + SQLParser parser = new SQLParser(new StringReader(sql)); + BindableStatement stmt = null; + try{ + stmt = parser.parseDeclareCursor(); + } catch (SQLException e){ + fail("Unable to parse:\n" + sql); + } + } + + private void parseFetch(String sql) throws IOException, SQLException { + SQLParser parser = new SQLParser(new StringReader(sql)); + BindableStatement stmt = null; + try{ + stmt = parser.parseFetch(); + } catch (SQLException e){ + fail("Unable to parse:\n" + sql); + } + } + + private void parseOpen(String sql) throws IOException, SQLException { + SQLParser parser = new SQLParser(new StringReader(sql)); + BindableStatement stmt = null; + try{ + stmt = parser.parseOpen(); + } catch (SQLException e){ + fail("Unable to parse:\n" + sql); + } + } + + @Test + public void testParseCursor0() throws Exception { + String expectedNameToken = "testCursor"; + String expectedSelectStatement = "select a from b\n" + + "where ((ind.name = 'X')" + + "and rownum <= (1000 + 1000))\n"; + + String sql = "DECLARE " + expectedNameToken + " CURSOR FOR " + expectedSelectStatement; + parseCursor(sql); + } + + @Test + public void testParseCursor1() throws Exception { + String expectedNameToken = "testCursor"; + String expectedSelectStatement = "select /*gatherSlowStats*/ count(1) from core.search_name_lookup ind\n" + + "where( (ind.name = 'X'\n" + + "and rownum <= 1 + 2)\n" + + "and (ind.organization_id = '000000000000000')\n" + + "and (ind.key_prefix = '00T')\n" + + "and (ind.name_type = 't'))"; + + + String sql = "DECLARE " + expectedNameToken + " CURSOR FOR " + expectedSelectStatement; + parseCursor(sql); + } + + @Test + public void testParseCursor2() throws Exception { + String expectedNameToken = "testCursor"; + String expectedSelectStatement = "select /*gatherSlowStats*/ count(1) from core.custom_index_value ind\n" + + "where (ind.string_value in ('a', 'b', 'c', 'd'))\n" + + "and rownum <= ( 3 + 1 )\n" + + "and (ind.organization_id = '000000000000000')\n" + + "and (ind.key_prefix = '00T')\n" + + "and (ind.deleted = '0')\n" + + "and (ind.index_num = 1)"; + + + String sql = "DECLARE " + expectedNameToken + " CURSOR FOR " + expectedSelectStatement; + parseCursor(sql); + + } + + @Test + public void testParseCursor3() throws Exception { + String expectedNameToken = "testCursor"; + String expectedSelectStatement = "select /*gatherSlowStats*/ count(1) from core.custom_index_value ind\n" + + "where (ind.number_value > 3)\n" + + "and rownum <= 1000\n" + + "and (ind.organization_id = '000000000000000')\n" + + "and (ind.key_prefix = '001'\n" + + "and (ind.deleted = '0'))\n" + + "and (ind.index_num = 2)"; + + + String sql = "DECLARE " + expectedNameToken + " CURSOR FOR " + expectedSelectStatement; + parseCursor(sql); + + } + + @Test + public void testParseCursor4() throws Exception { + String expectedNameToken = "testCursor"; + String expectedSelectStatement = "select /*+ index(t iecustom_entity_data_created) */ /*gatherSlowStats*/ count(1) from core.custom_entity_data t\n" + + "where (t.created_date > to_date('01/01/2001'))\n" + + "and rownum <= 4500\n" + + "and (t.organization_id = '000000000000000')\n" + + "and (t.key_prefix = '001')"; + + + String sql = "DECLARE " + expectedNameToken + " CURSOR FOR " + expectedSelectStatement; + parseCursor(sql); + + } + + @Test + public void testCountDistinctCursor() throws Exception { + String expectedNameToken = "testCursor"; + String expectedSelectStatement = "select count(distinct foo) from core.custom_entity_data t\n" + + "where (t.created_date > to_date('01/01/2001'))\n" + + "and (t.organization_id = '000000000000000')\n" + + "and (t.key_prefix = '001')\n" + "limit 4500"; + + String sql = "DECLARE " + expectedNameToken + " CURSOR FOR " + expectedSelectStatement; + parseCursor(sql); + + } + + @Test + public void testIsNullCursor() throws Exception { + String expectedNameToken = "testCursor"; + String expectedSelectStatement = "select count(foo) from core.custom_entity_data t\n" + + "where (t.created_date is null)\n" + + "and (t.organization_id is not null)\n"; + + String sql = "DECLARE " + expectedNameToken + " CURSOR FOR " + expectedSelectStatement; + parseCursor(sql); + + } + + @Test + public void testAsInColumnAlias() throws Exception { + String expectedNameToken = "testCursor"; + String expectedSelectStatement = "select count(foo) AS c from core.custom_entity_data t\n" + + "where (t.created_date is null)\n" + + "and (t.organization_id is not null)\n"; + + String sql = "DECLARE " + expectedNameToken + " CURSOR FOR " + expectedSelectStatement; + parseCursor(sql); + + } + + @Test + public void testParseJoin1() throws Exception { + String expectedNameToken = "testCursor"; + String expectedSelectStatement = "select /*SOQL*/ \"Id\"\n" + + "from (select /*+ ordered index(cft) */\n" + + "cft.val188 \"Marketing_Offer_Code__c\",\n" + + "t.account_id \"Id\"\n" + + "from sales.account_cfdata cft,\n" + + "sales.account t\n" + + "where (cft.account_cfdata_id = t.account_id)\n" + + "and (cft.organization_id = '00D300000000XHP')\n" + + "and (t.organization_id = '00D300000000XHP')\n" + + "and (t.deleted = '0')\n" + + "and (t.account_id != '000000000000000'))\n" + + "where (\"Marketing_Offer_Code__c\" = 'FSCR')"; + + String sql = "DECLARE " + expectedNameToken + " CURSOR FOR " + expectedSelectStatement; + parseCursor(sql); + + } + + @Test + public void testParseJoin2() throws Exception { + String expectedNameToken = "testCursor"; + String expectedSelectStatement = "select /*rptacctlist 00O40000002C3of*/ \"00N40000001M8VK\",\n" + + "\"00N40000001M8VK.ID\",\n" + + "\"00N30000000r0K2\",\n" + + "\"00N30000000jgjo\"\n" + + "from (select /*+ ordered use_hash(aval368) index(cfa) */\n" + + "a.record_type_id \"RECORDTYPE\",\n" + + "aval368.last_name,aval368.first_name || ' ' || aval368.last_name,aval368.name \"00N40000001M8VK\",\n" + + "a.last_update \"LAST_UPDATE\",\n" + + "cfa.val368 \"00N40000001M8VK.ID\",\n" + + "TO_DATE(cfa.val282) \"00N30000000r0K2\",\n" + + "cfa.val252 \"00N30000000jgjo\"\n" + + "from sales.account a,\n" + + "sales.account_cfdata cfa,\n" + + "core.name_denorm aval368\n" + + "where (cfa.account_cfdata_id = a.account_id)\n" + + "and (aval368.entity_id = cfa.val368)\n" + + "and (a.deleted = '0')\n" + + "and (a.organization_id = '00D300000000EaE')\n" + + "and (a.account_id <> '000000000000000')\n" + + "and (cfa.organization_id = '00D300000000EaE')\n" + + "and (aval368.organization_id = '00D300000000EaE')\n" + + "and (aval368.entity_id like '005%'))\n" + + "where (\"RECORDTYPE\" = '0123000000002Gv')\n" + + "AND (\"00N40000001M8VK\" is null or \"00N40000001M8VK\" in ('BRIAN IRWIN', 'BRIAN MILLER', 'COLLEEN HORNYAK', 'ERNIE ZAVORAL JR', 'JAMIE TRIMBUR', 'JOE ANTESBERGER', 'MICHAEL HYTLA', 'NATHAN DELSIGNORE', 'SANJAY GANDHI', 'TOM BASHIOUM'))\n" + + "AND (\"LAST_UPDATE\" >= to_date('2009-08-01 07:00:00'))"; + + String sql = "DECLARE " + expectedNameToken + " CURSOR FOR " + expectedSelectStatement; + parseCursor(sql); + + } + + @Test + public void testCommentCursor() throws Exception { + String expectedNameToken = "testCursor"; + String expectedSelectStatement = "select a from b -- here we come\n" + + "where ((ind.name = 'X') // to save the day\n" + + "and rownum /* won't run */ <= (1000 + 1000))\n"; + + String sql = "DECLARE " + expectedNameToken + " CURSOR FOR " + expectedSelectStatement; + parseCursor(sql); + + } + + @Test + public void testQuoteEscapeCursor() throws Exception { + String expectedNameToken = "testCursor"; + String expectedSelectStatement = "select a from b\n" + + "where ind.name = 'X''Y'\n"; + + String sql = "DECLARE " + expectedNameToken + " CURSOR FOR " + expectedSelectStatement; + parseCursor(sql); + + } + + @Test + public void testSubtractionInSelect() throws Exception { + String expectedNameToken = "testCursor"; + String expectedSelectStatement = "select a, 3-1-2, -4- -1-1 from b\n" + + "where d = c - 1\n"; + + String sql = "DECLARE " + expectedNameToken + " CURSOR FOR " + expectedSelectStatement; + parseCursor(sql); + + } + + @Test + public void testNextValueForSelect() throws Exception { + String expectedNameToken = "testCursor"; + String expectedSelectStatement = "select next value for foo.bar \n" + + "from core.custom_entity_data\n"; + + String sql = "DECLARE " + expectedNameToken + " CURSOR FOR " + expectedSelectStatement; + parseCursor(sql); + + } + + @Test + public void testPercentileQuery1() throws Exception { + String expectedNameToken = "testCursor"; + String expectedSelectStatement = "select PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY salary DESC) from core.custom_index_value ind"; + + String sql = "DECLARE " + expectedNameToken + " CURSOR FOR " + expectedSelectStatement; + parseCursor(sql); + + } + + @Test + public void testPercentileQuery2() throws Exception { + String expectedNameToken = "testCursor"; + String expectedSelectStatement = "select PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY mark ASC) from core.custom_index_value ind"; + + String sql = "DECLARE " + expectedNameToken + " CURSOR FOR " + expectedSelectStatement; + parseCursor(sql); + + } + + @Test + public void testRowValueConstructorQuery() throws Exception { + String expectedNameToken = "testCursor"; + String expectedSelectStatement = "select a_integer FROM aTable where (x_integer, y_integer) > (3, 4)"; + + String sql = "DECLARE " + expectedNameToken + " CURSOR FOR " + expectedSelectStatement; + parseCursor(sql); + + } + + @Test + public void testSingleTopLevelNot() throws Exception { + String expectedNameToken = "testCursor"; + String expectedSelectStatement = "select * from t where not c = 5"; + + String sql = "DECLARE " + expectedNameToken + " CURSOR FOR " + expectedSelectStatement; + parseCursor(sql); + } + + @Test + public void testHavingWithNot() throws Exception { + String expectedNameToken = "testCursor"; + String expectedSelectStatement = "select\n" + + "\"WEB_STAT_ALIAS\".\"DOMAIN\" as \"c0\"\n" + + "from \"WEB_STAT\" \"WEB_STAT_ALIAS\"\n" + + "group by \"WEB_STAT_ALIAS\".\"DOMAIN\" having\n" + + "(\n" + + "(\n" + + "NOT\n" + + "(\n" + + "(sum(\"WEB_STAT_ALIAS\".\"ACTIVE_VISITOR\") is null)\n" + + ")\n" + + "OR NOT((sum(\"WEB_STAT_ALIAS\".\"ACTIVE_VISITOR\") is null))\n" + + ")\n" + + "OR NOT((sum(\"WEB_STAT_ALIAS\".\"ACTIVE_VISITOR\") is null))\n" + + ")\n" + + "order by CASE WHEN \"WEB_STAT_ALIAS\".\"DOMAIN\" IS NULL THEN 1 ELSE 0 END,\n" + + "\"WEB_STAT_ALIAS\".\"DOMAIN\" ASC"; + + String sql = "DECLARE " + expectedNameToken + " CURSOR FOR " + expectedSelectStatement; + parseCursor(sql); + + } + + @Test + public void testDoubleBackslash() throws Exception { + String expectedNameToken = "testCursor"; + String expectedSelectStatement = "SELECT * FROM T WHERE A LIKE 'a\\(d'"; + + String sql = "DECLARE " + expectedNameToken + " CURSOR FOR " + expectedSelectStatement; + parseCursor(sql); + + } + + @Test + public void testOpenCursor() throws Exception { + String expectedNameToken = "testCursor"; + String sql = "OPEN " + expectedNameToken; + parseOpen(sql); + } + + @Test + public void testFetchNext() throws Exception { + String expectedNameToken = "testCursor"; + String sql = "FETCH NEXT FROM " + expectedNameToken; + parseFetch(sql); + } + +}
