Repository: hive Updated Branches: refs/heads/master 8f930e588 -> 9763c9dd3
Patch HIVE-11536 Project: http://git-wip-us.apache.org/repos/asf/hive/repo Commit: http://git-wip-us.apache.org/repos/asf/hive/commit/da95f633 Tree: http://git-wip-us.apache.org/repos/asf/hive/tree/da95f633 Diff: http://git-wip-us.apache.org/repos/asf/hive/diff/da95f633 Branch: refs/heads/master Commit: da95f6336acf9ab6a660f17c2a7555587f903038 Parents: ab03dc9 Author: Dmitry Tolpeko <dmtolp...@gmail.com> Authored: Thu Aug 20 09:30:19 2015 -0700 Committer: Dmitry Tolpeko <dmtolp...@gmail.com> Committed: Thu Aug 20 09:30:19 2015 -0700 ---------------------------------------------------------------------- .../antlr4/org/apache/hive/hplsql/Hplsql.g4 | 85 +++++++++-- .../java/org/apache/hive/hplsql/Column.java | 65 +++++++++ .../main/java/org/apache/hive/hplsql/Exec.java | 142 +++++++++++++++---- .../java/org/apache/hive/hplsql/Expression.java | 6 + .../main/java/org/apache/hive/hplsql/Meta.java | 118 +++++++++++++++ .../main/java/org/apache/hive/hplsql/Row.java | 97 +++++++++++++ .../java/org/apache/hive/hplsql/Select.java | 16 ++- .../main/java/org/apache/hive/hplsql/Stmt.java | 73 ++++++---- .../main/java/org/apache/hive/hplsql/Var.java | 37 ++++- .../apache/hive/hplsql/functions/Function.java | 13 ++ .../org/apache/hive/hplsql/TestHplsqlLocal.java | 7 +- .../apache/hive/hplsql/TestHplsqlOffline.java | 2 +- .../src/test/queries/db/rowtype_attribute.sql | 22 +++ hplsql/src/test/queries/db/type_attribute.sql | 8 ++ .../local/create_procedure_no_params.sql | 19 +++ .../test/queries/offline/create_table_ora.sql | 55 ++++++- .../test/results/db/rowtype_attribute.out.txt | 42 ++++++ .../src/test/results/db/type_attribute.out.txt | 15 ++ .../local/create_procedure_no_params.out.txt | 26 ++++ .../results/offline/create_table_ora.out.txt | 38 +++++ 20 files changed, 802 insertions(+), 84 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/hive/blob/da95f633/hplsql/src/main/antlr4/org/apache/hive/hplsql/Hplsql.g4 ---------------------------------------------------------------------- diff --git a/hplsql/src/main/antlr4/org/apache/hive/hplsql/Hplsql.g4 b/hplsql/src/main/antlr4/org/apache/hive/hplsql/Hplsql.g4 index 6027206..ff772fe 100644 --- a/hplsql/src/main/antlr4/org/apache/hive/hplsql/Hplsql.g4 +++ b/hplsql/src/main/antlr4/org/apache/hive/hplsql/Hplsql.g4 @@ -138,7 +138,7 @@ break_stmt : ; call_stmt : - T_CALL ident (T_OPEN_P expr_func_params T_CLOSE_P | expr_func_params)? + T_CALL ident (T_OPEN_P expr_func_params? T_CLOSE_P | expr_func_params)? ; declare_stmt : // Declaration statement @@ -211,11 +211,18 @@ create_table_column_inline_cons : dtype_default | T_NOT? T_NULL | T_PRIMARY T_KEY + | T_UNIQUE + | T_REFERENCES table_name T_OPEN_P ident T_CLOSE_P create_table_fk_action* | T_IDENTITY T_OPEN_P L_INT (T_COMMA L_INT)* T_CLOSE_P ; create_table_column_cons : - T_PRIMARY T_KEY T_CLUSTERED? T_OPEN_P ident (T_ASC | T_DESC)? (T_COMMA ident (T_ASC | T_DESC)?)* T_CLOSE_P index_storage_clause? + T_PRIMARY T_KEY T_CLUSTERED? T_OPEN_P ident (T_ASC | T_DESC)? (T_COMMA ident (T_ASC | T_DESC)?)* T_CLOSE_P index_storage_clause? + | T_FOREIGN T_KEY T_OPEN_P ident (T_COMMA ident)* T_CLOSE_P T_REFERENCES table_name T_OPEN_P ident (T_COMMA ident)* T_CLOSE_P create_table_fk_action* + ; + +create_table_fk_action : + T_ON (T_UPDATE | T_DELETE) (T_NO T_ACTION | T_RESTRICT | T_SET T_NULL | T_SET T_DEFAULT | T_CASCADE) ; create_table_options : @@ -224,11 +231,21 @@ create_table_options : create_table_options_item : T_ON T_COMMIT (T_DELETE | T_PRESERVE) T_ROWS + | create_table_options_ora_item | create_table_options_db2_item | create_table_options_hive_item | create_table_options_mssql_item ; +create_table_options_ora_item : + T_SEGMENT T_CREATION (T_IMMEDIATE | T_DEFERRED) + | (T_PCTFREE | T_PCTUSED | T_INITRANS | T_MAXTRANS) L_INT + | T_NOCOMPRESS + | (T_LOGGING | T_NOLOGGING) + | T_STORAGE T_OPEN_P (ident | L_INT)+ T_CLOSE_P + | T_TABLESPACE ident + ; + create_table_options_db2_item : T_IN ident | T_WITH T_REPLACE @@ -282,11 +299,11 @@ dtype : // Data types | T_TINYINT | T_VARCHAR | T_VARCHAR2 - | L_ID // User-defined data type + | L_ID ('%' (T_TYPE | T_ROWTYPE))? // User-defined or derived data type ; dtype_len : // Data type length or size specification - T_OPEN_P (L_INT | T_MAX) (T_COMMA L_INT)? T_CLOSE_P + T_OPEN_P (L_INT | T_MAX) (T_CHAR | T_BYTE)? (T_COMMA L_INT)? T_CLOSE_P ; dtype_attr : @@ -300,7 +317,7 @@ dtype_default : // Default clause in variable declaration ; create_function_stmt : - (T_ALTER | T_CREATE (T_OR T_REPLACE)? | T_REPLACE) T_FUNCTION ident create_routine_params? create_function_return (T_AS | T_IS)? single_block_stmt + (T_ALTER | T_CREATE (T_OR T_REPLACE)? | T_REPLACE)? T_FUNCTION ident create_routine_params? create_function_return (T_AS | T_IS)? single_block_stmt ; create_function_return : @@ -308,7 +325,7 @@ create_function_return : ; create_procedure_stmt : - (T_ALTER | T_CREATE (T_OR T_REPLACE)? | T_REPLACE) (T_PROCEDURE | T_PROC) ident create_routine_params? create_routine_options? (T_AS | T_IS)? label? proc_block (ident T_SEMICOLON)? + (T_ALTER | T_CREATE (T_OR T_REPLACE)? | T_REPLACE)? (T_PROCEDURE | T_PROC) ident create_routine_params? create_routine_options? (T_AS | T_IS)? label? proc_block (ident T_SEMICOLON)? ; create_routine_params : @@ -598,7 +615,7 @@ select_list_item : ; select_list_alias : - T_AS? L_ID + {!_input.LT(1).getText().equalsIgnoreCase("FROM")}? T_AS? ident | T_OPEN_P T_TITLE L_S_STRING T_CLOSE_P ; @@ -899,7 +916,7 @@ expr_func_params : ; func_param : - (ident T_EQUAL T_GREATER?)? expr + {!_input.LT(1).getText().equalsIgnoreCase("INTO")}? (ident T_EQUAL T_GREATER?)? expr ; hive : @@ -963,7 +980,8 @@ null_const : // NULL constant ; non_reserved_words : // Tokens that are not reserved words and can be used as identifiers - T_ACTIVITY_COUNT + T_ACTION + | T_ACTIVITY_COUNT | T_ALL | T_ALLOCATE | T_ALTER @@ -982,8 +1000,10 @@ non_reserved_words : // Tokens that are not reserved words | T_BIT | T_BREAK | T_BY + | T_BYTE | T_CALL - | T_CALLER + | T_CALLER + | T_CASCADE | T_CASE | T_CASESPECIFIC | T_CAST @@ -1002,6 +1022,7 @@ non_reserved_words : // Tokens that are not reserved words | T_COUNT | T_COUNT_BIG | T_CREATE + | T_CREATION | T_CREATOR | T_CS | T_CUME_DIST @@ -1019,6 +1040,7 @@ non_reserved_words : // Tokens that are not reserved words | T_DECIMAL | T_DECLARE | T_DEFAULT + | T_DEFERRED | T_DEFINED | T_DEFINER | T_DELETE @@ -1049,6 +1071,7 @@ non_reserved_words : // Tokens that are not reserved words | T_FIRST_VALUE | T_FLOAT | T_FOR + | T_FOREIGN | T_FORMAT | T_FOUND | T_FROM @@ -1071,6 +1094,7 @@ non_reserved_words : // Tokens that are not reserved words | T_IN | T_INCLUDE | T_INDEX + | T_INITRANS | T_INNER | T_INOUT | T_INSERT @@ -1097,11 +1121,13 @@ non_reserved_words : // Tokens that are not reserved words | T_LOCAL | T_LOCATOR | T_LOCATORS - | T_LOGGED + | T_LOGGED + | T_LOGGING | T_LOOP | T_MAP | T_MATCHED - | T_MAX + | T_MAX + | T_MAXTRANS | T_MERGE | T_MESSAGE_TEXT | T_MICROSECOND @@ -1110,7 +1136,10 @@ non_reserved_words : // Tokens that are not reserved words | T_MULTISET | T_NCHAR | T_NVARCHAR + | T_NO + | T_NOCOMPRESS | T_NOCOUNT + | T_NOLOGGING | T_NOT | T_NOTFOUND // T_NULL reserved word @@ -1129,18 +1158,22 @@ non_reserved_words : // Tokens that are not reserved words | T_OVERWRITE | T_OWNER | T_PART_LOC - | T_PARTITION + | T_PARTITION + | T_PCTFREE + | T_PCTUSED | T_PRESERVE | T_PRIMARY | T_PRINT | T_PROC | T_PROCEDURE | T_QUOTED_IDENTIFIER - | T_RANK + | T_RANK + | T_REFERENCES | T_REGEXP | T_RR | T_REPLACE | T_RESIGNAL + | T_RESTRICT | T_RESULT | T_RESULT_SET_LOCATOR | T_RETURN @@ -1156,6 +1189,7 @@ non_reserved_words : // Tokens that are not reserved words | T_ROW_NUMBER | T_SCHEMA | T_SECURITY + | T_SEGMENT | T_SEL | T_SELECT | T_SET @@ -1170,12 +1204,14 @@ non_reserved_words : // Tokens that are not reserved words | T_SQLWARNING | T_STEP | T_STDEV + | T_STORAGE | T_STRING | T_SUBSTRING | T_SUM | T_SYSDATE | T_SYS_REFCURSOR | T_TABLE + | T_TABLESPACE | T_TEMPORARY | T_TERMINATED | T_TEXTIMAGE_ON @@ -1210,6 +1246,7 @@ non_reserved_words : // Tokens that are not reserved words ; // Lexer rules +T_ACTION : A C T I O N ; T_ALL : A L L ; T_ALLOCATE : A L L O C A T E ; T_ALTER : A L T E R ; @@ -1228,8 +1265,10 @@ T_BIGINT : B I G I N T ; T_BIT : B I T ; T_BREAK : B R E A K ; T_BY : B Y ; +T_BYTE : B Y T E ; T_CALL : C A L L ; T_CALLER : C A L L E R ; +T_CASCADE : C A S C A D E ; T_CASE : C A S E ; T_CASESPECIFIC : C A S E S P E C I F I C ; T_CAST : C A S T ; @@ -1248,6 +1287,7 @@ T_CONTINUE : C O N T I N U E ; T_COUNT : C O U N T ; T_COUNT_BIG : C O U N T '_' B I G; T_CREATE : C R E A T E ; +T_CREATION : C R E A T I O N ; T_CREATOR : C R E A T O R ; T_CS : C S; T_CURRENT : C U R R E N T ; @@ -1261,6 +1301,7 @@ T_DEC : D E C ; T_DECIMAL : D E C I M A L ; T_DECLARE : D E C L A R E ; T_DEFAULT : D E F A U L T ; +T_DEFERRED : D E F E R R E D ; T_DEFINED : D E F I N E D ; T_DEFINER : D E F I N E R ; T_DELETE : D E L E T E ; @@ -1289,6 +1330,7 @@ T_FIELDS : F I E L D S ; T_FILE : F I L E ; T_FLOAT : F L O A T ; T_FOR : F O R ; +T_FOREIGN : F O R E I G N ; T_FORMAT : F O R M A T ; T_FOUND : F O U N D ; T_FROM : F R O M ; @@ -1311,6 +1353,7 @@ T_IMMEDIATE : I M M E D I A T E ; T_IN : I N ; T_INCLUDE : I N C L U D E ; T_INDEX : I N D E X ; +T_INITRANS : I N I T R A N S ; T_INNER : I N N E R ; T_INOUT : I N O U T; T_INSERT : I N S E R T ; @@ -1335,10 +1378,12 @@ T_LOCAL : L O C A L ; T_LOCATOR : L O C A T O R ; T_LOCATORS : L O C A T O R S ; T_LOGGED : L O G G E D ; +T_LOGGING : L O G G I N G ; T_LOOP : L O O P ; T_MAP : M A P ; T_MATCHED : M A T C H E D ; T_MAX : M A X ; +T_MAXTRANS : M A X T R A N S ; T_MERGE : M E R G E ; T_MESSAGE_TEXT : M E S S A G E '_' T E X T ; T_MICROSECOND : M I C R O S E C O N D ; @@ -1347,7 +1392,10 @@ T_MIN : M I N ; T_MULTISET : M U L T I S E T ; T_NCHAR : N C H A R ; T_NVARCHAR : N V A R C H A R ; +T_NO : N O ; T_NOCOUNT : N O C O U N T ; +T_NOCOMPRESS : N O C O M P R E S S ; +T_NOLOGGING : N O L O G G I N G ; T_NOT : N O T ; T_NOTFOUND : N O T F O U N D ; T_NULL : N U L L ; @@ -1366,15 +1414,19 @@ T_OVER : O V E R ; T_OVERWRITE : O V E R W R I T E ; T_OWNER : O W N E R ; T_PARTITION : P A R T I T I O N ; +T_PCTFREE : P C T F R E E ; +T_PCTUSED : P C T U S E D ; T_PRESERVE : P R E S E R V E ; T_PRIMARY : P R I M A R Y ; T_PRINT : P R I N T ; T_PROC : P R O C ; T_PROCEDURE : P R O C E D U R E; T_QUOTED_IDENTIFIER : Q U O T E D '_' I D E N T I F I E R ; +T_REFERENCES : R E F E R E N C E S ; T_REGEXP : R E G E X P ; T_REPLACE : R E P L A C E ; T_RESIGNAL : R E S I G N A L ; +T_RESTRICT : R E S T R I C T ; T_RESULT : R E S U L T ; T_RESULT_SET_LOCATOR : R E S U L T '_' S E T '_' L O C A T O R ; T_RETURN : R E T U R N ; @@ -1385,12 +1437,14 @@ T_RLIKE : R L I K E ; T_ROLLBACK : R O L L B A C K ; T_ROW : R O W ; T_ROWS : R O W S ; +T_ROWTYPE : R O W T Y P E ; T_ROW_COUNT : R O W '_' C O U N T ; T_RR : R R; T_RS : R S ; T_TRIM : T R I M ; T_SCHEMA : S C H E M A ; T_SECURITY : S E C U R I T Y ; +T_SEGMENT : S E G M E N T ; T_SEL : S E L ; T_SELECT : S E L E C T ; T_SET : S E T ; @@ -1404,11 +1458,13 @@ T_SQLINSERT : S Q L I N S E R T ; T_SQLSTATE : S Q L S T A T E ; T_SQLWARNING : S Q L W A R N I N G ; T_STEP : S T E P ; +T_STORAGE : S T O R A G E ; T_STRING : S T R I N G ; T_SUBSTRING : S U B S T R I N G ; T_SUM : S U M ; T_SYS_REFCURSOR : S Y S '_' R E F C U R S O R ; T_TABLE : T A B L E ; +T_TABLESPACE : T A B L E S P A C E ; T_TEMPORARY : T E M P O R A R Y ; T_TERMINATED : T E R M I N A T E D ; T_TEXTIMAGE_ON : T E X T I M A G E '_' O N ; @@ -1418,6 +1474,7 @@ T_TINYINT : T I N Y I N T ; T_TITLE : T I T L E ; T_TO : T O ; T_TOP : T O P ; +T_TYPE : T Y P E ; T_UNION : U N I O N ; T_UNIQUE : U N I Q U E ; T_UPDATE : U P D A T E ; http://git-wip-us.apache.org/repos/asf/hive/blob/da95f633/hplsql/src/main/java/org/apache/hive/hplsql/Column.java ---------------------------------------------------------------------- diff --git a/hplsql/src/main/java/org/apache/hive/hplsql/Column.java b/hplsql/src/main/java/org/apache/hive/hplsql/Column.java new file mode 100644 index 0000000..252a870 --- /dev/null +++ b/hplsql/src/main/java/org/apache/hive/hplsql/Column.java @@ -0,0 +1,65 @@ +/** + * 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.hive.hplsql; + +/** + * Table column + */ +public class Column { + + String name; + String type; + Var value; + + Column(String name, String type) { + this.name = name; + this.type = type; + } + + /** + * Set the column value + */ + void setValue(Var value) { + this.value = value; + } + + /** + * Get the column name + */ + String getName() { + return name; + } + + /** + * Get the column type + */ + String getType() { + return type; + } + + /** + * Get the column value + */ + Var getValue() { + return value; + } +} + + + http://git-wip-us.apache.org/repos/asf/hive/blob/da95f633/hplsql/src/main/java/org/apache/hive/hplsql/Exec.java ---------------------------------------------------------------------- diff --git a/hplsql/src/main/java/org/apache/hive/hplsql/Exec.java b/hplsql/src/main/java/org/apache/hive/hplsql/Exec.java index 268c218..f5592e1 100644 --- a/hplsql/src/main/java/org/apache/hive/hplsql/Exec.java +++ b/hplsql/src/main/java/org/apache/hive/hplsql/Exec.java @@ -30,6 +30,8 @@ import java.util.Map.Entry; import java.util.Stack; import java.util.Iterator; import java.sql.Connection; +import java.sql.ResultSet; +import java.sql.ResultSetMetaData; import java.sql.SQLException; import org.antlr.v4.runtime.ANTLRInputStream; @@ -48,7 +50,7 @@ import org.apache.hive.hplsql.functions.*; */ public class Exec extends HplsqlBaseVisitor<Integer> { - public static final String VERSION = "HPL/SQL 0.3.11"; + public static final String VERSION = "HPL/SQL 0.3.13"; public static final String SQLCODE = "SQLCODE"; public static final String SQLSTATE = "SQLSTATE"; public static final String HOSTCODE = "HOSTCODE"; @@ -96,6 +98,7 @@ public class Exec extends HplsqlBaseVisitor<Integer> { StringBuilder localUdf = new StringBuilder(); boolean initRoutines = false; public boolean buildSql = false; + public boolean inCallStmt = false; boolean udfRegistered = false; boolean udfRun = false; @@ -285,16 +288,29 @@ public class Exec extends HplsqlBaseVisitor<Integer> { * Find an existing variable by name */ public Var findVariable(String name) { - Scope cur = exec.currentScope; + String name1 = name; + Scope cur = exec.currentScope; + ArrayList<String> qualified = exec.meta.splitIdentifier(name); + if (qualified != null) { + name1 = qualified.get(0); + } String name2 = null; if (name.startsWith(":")) { name2 = name.substring(1); - } + } while (cur != null) { for (Var v : cur.vars) { - if (name.equalsIgnoreCase(v.getName()) || + if (name1.equalsIgnoreCase(v.getName()) || (name2 != null && name2.equalsIgnoreCase(v.getName()))) { - return v; + if (qualified != null) { + if (v.type == Var.Type.ROW && v.value != null) { + Row row = (Row)v.value; + return row.getValue(qualified.get(1)); + } + } + else { + return v; + } } } cur = cur.parent; @@ -675,7 +691,7 @@ public class Exec extends HplsqlBaseVisitor<Integer> { conf = new Conf(); conf.init(); conn = new Conn(this); - meta = new Meta(); + meta = new Meta(this); initOptions(); expr = new Expression(this); @@ -1024,37 +1040,72 @@ public class Exec extends HplsqlBaseVisitor<Integer> { */ @Override public Integer visitDeclare_var_item(HplsqlParser.Declare_var_itemContext ctx) { - String type = getFormattedText(ctx.dtype()); + String type = null; + Row row = null; String len = null; String scale = null; Var default_ = null; - if (ctx.dtype_len() != null) { - len = ctx.dtype_len().L_INT(0).getText(); - if (ctx.dtype_len().L_INT(1) != null) { - scale = ctx.dtype_len().L_INT(1).getText(); + if (ctx.dtype().T_ROWTYPE() != null) { + row = meta.getRowDataType(ctx, exec.conf.defaultConnection, ctx.dtype().L_ID().getText()); + if (row == null) { + type = Var.DERIVED_ROWTYPE; + } + } + else { + type = getDataType(ctx); + if (ctx.dtype_len() != null) { + len = ctx.dtype_len().L_INT(0).getText(); + if (ctx.dtype_len().L_INT(1) != null) { + scale = ctx.dtype_len().L_INT(1).getText(); + } + } + if (ctx.dtype_default() != null) { + default_ = evalPop(ctx.dtype_default()); } - } - if (ctx.dtype_default() != null) { - default_ = evalPop(ctx.dtype_default()); } int cnt = ctx.ident().size(); // Number of variables declared with the same data type and default for (int i = 0; i < cnt; i++) { String name = ctx.ident(i).getText(); - Var var = new Var(name, type, len, scale, default_); - addVariable(var); - if (trace) { - if (default_ != null) { - trace(ctx, "DECLARE " + name + " " + type + " = " + var.toSqlString()); - } - else { - trace(ctx, "DECLARE " + name + " " + type); + if (row == null) { + Var var = new Var(name, type, len, scale, default_); + addVariable(var); + if (trace) { + if (default_ != null) { + trace(ctx, "DECLARE " + name + " " + type + " = " + var.toSqlString()); + } + else { + trace(ctx, "DECLARE " + name + " " + type); + } } } + else { + addVariable(new Var(name, row)); + if (trace) { + trace(ctx, "DECLARE " + name + " " + ctx.dtype().getText()); + } + } } return 0; } /** + * Get the variable data type + */ + String getDataType(HplsqlParser.Declare_var_itemContext ctx) { + String type = null; + if (ctx.dtype().T_TYPE() != null) { + type = meta.getDataType(ctx, exec.conf.defaultConnection, ctx.dtype().L_ID().getText()); + if (type == null) { + type = Var.DERIVED_TYPE; + } + } + else { + type = getFormattedText(ctx.dtype()); + } + return type; + } + + /** * ALLOCATE CURSOR statement */ @Override @@ -1179,6 +1230,11 @@ public class Exec extends HplsqlBaseVisitor<Integer> { } @Override + public Integer visitCreate_table_options_ora_item(HplsqlParser.Create_table_options_ora_itemContext ctx) { + return 0; + } + + @Override public Integer visitCreate_table_options_mssql_item(HplsqlParser.Create_table_options_mssql_itemContext ctx) { return 0; } @@ -1457,16 +1513,24 @@ public class Exec extends HplsqlBaseVisitor<Integer> { */ @Override public Integer visitExec_stmt(HplsqlParser.Exec_stmtContext ctx) { - return exec.stmt.exec(ctx); + exec.inCallStmt = true; + Integer rc = exec.stmt.exec(ctx); + exec.inCallStmt = false; + return rc; } /** * CALL statement */ @Override - public Integer visitCall_stmt(HplsqlParser.Call_stmtContext ctx) { - if (exec.function.execProc(ctx.expr_func_params(), ctx.ident().getText())) { - return 0; + public Integer visitCall_stmt(HplsqlParser.Call_stmtContext ctx) { + try { + exec.inCallStmt = true; + if (exec.function.execProc(ctx.expr_func_params(), ctx.ident().getText())) { + return 0; + } + } finally { + exec.inCallStmt = false; } return -1; } @@ -1795,7 +1859,12 @@ public class Exec extends HplsqlBaseVisitor<Integer> { } } else { - exec.stackPush(new Var(Var.Type.IDENT, ident)); + if (!exec.buildSql && !exec.inCallStmt && exec.function.isProc(ident) && exec.function.execProc(null, ident)) { + return 0; + } + else { + exec.stackPush(new Var(Var.Type.IDENT, ident)); + } } return 0; } @@ -2041,6 +2110,25 @@ public class Exec extends HplsqlBaseVisitor<Integer> { } /** + * Trace values retrived from the database + */ + public void trace(ParserRuleContext ctx, Var var, ResultSet rs, ResultSetMetaData rm, int idx) throws SQLException { + if (var.type != Var.Type.ROW) { + trace(ctx, "COLUMN: " + rm.getColumnName(idx) + ", " + rm.getColumnTypeName(idx)); + trace(ctx, "SET " + var.getName() + " = " + var.toString()); + } + else { + Row row = (Row)var.value; + int cnt = row.size(); + for (int j = 1; j <= cnt; j++) { + Var v = row.getValue(j - 1); + trace(ctx, "COLUMN: " + rm.getColumnName(j) + ", " + rm.getColumnTypeName(j)); + trace(ctx, "SET " + v.getName() + " = " + v.toString()); + } + } + } + + /** * Informational messages */ public void info(ParserRuleContext ctx, String message) { http://git-wip-us.apache.org/repos/asf/hive/blob/da95f633/hplsql/src/main/java/org/apache/hive/hplsql/Expression.java ---------------------------------------------------------------------- diff --git a/hplsql/src/main/java/org/apache/hive/hplsql/Expression.java b/hplsql/src/main/java/org/apache/hive/hplsql/Expression.java index f811626..7269798 100644 --- a/hplsql/src/main/java/org/apache/hive/hplsql/Expression.java +++ b/hplsql/src/main/java/org/apache/hive/hplsql/Expression.java @@ -327,6 +327,9 @@ public class Expression { else if (v1.type == Type.TIMESTAMP && v2.type == Type.INTERVAL) { exec.stackPush(new Var(((Interval)v2.value).timestampChange((Timestamp)v1.value, true /*add*/), v1.scale)); } + else { + evalNull(); + } } /** @@ -350,6 +353,9 @@ public class Expression { else if (v1.type == Type.TIMESTAMP && v2.type == Type.INTERVAL) { exec.stackPush(new Var(((Interval)v2.value).timestampChange((Timestamp)v1.value, false /*subtract*/), v1.scale)); } + else { + evalNull(); + } } /** http://git-wip-us.apache.org/repos/asf/hive/blob/da95f633/hplsql/src/main/java/org/apache/hive/hplsql/Meta.java ---------------------------------------------------------------------- diff --git a/hplsql/src/main/java/org/apache/hive/hplsql/Meta.java b/hplsql/src/main/java/org/apache/hive/hplsql/Meta.java index 97d0fd2..485bcdf 100644 --- a/hplsql/src/main/java/org/apache/hive/hplsql/Meta.java +++ b/hplsql/src/main/java/org/apache/hive/hplsql/Meta.java @@ -18,12 +18,106 @@ package org.apache.hive.hplsql; +import java.sql.ResultSet; import java.util.ArrayList; +import java.util.HashMap; + +import org.antlr.v4.runtime.ParserRuleContext; /** * Metadata */ public class Meta { + + HashMap<String, HashMap<String, Row>> dataTypes = new HashMap<String, HashMap<String, Row>>(); + + Exec exec; + boolean trace = false; + boolean info = false; + + Meta(Exec e) { + exec = e; + trace = exec.getTrace(); + info = exec.getInfo(); + } + + /** + * Get the data type of column (column name is qualified i.e. schema.table.column) + */ + String getDataType(ParserRuleContext ctx, String conn, String column) { + String type = null; + HashMap<String, Row> map = dataTypes.get(conn); + if (map == null) { + map = new HashMap<String, Row>(); + dataTypes.put(conn, map); + } + ArrayList<String> twoparts = splitIdentifierToTwoParts(column); + if (twoparts != null) { + String tab = twoparts.get(0); + String col = twoparts.get(1).toUpperCase(); + Row row = map.get(tab); + if (row != null) { + type = row.getType(col); + } + else { + row = readColumns(ctx, conn, tab, map); + if (row != null) { + type = row.getType(col); + } + } + } + return type; + } + + /** + * Get data types for all columns of the table + */ + Row getRowDataType(ParserRuleContext ctx, String conn, String table) { + HashMap<String, Row> map = dataTypes.get(conn); + if (map == null) { + map = new HashMap<String, Row>(); + dataTypes.put(conn, map); + } + Row row = map.get(table); + if (row == null) { + row = readColumns(ctx, conn, table, map); + } + return row; + } + + /** + * Read the column data from the database and cache it + */ + Row readColumns(ParserRuleContext ctx, String conn, String table, HashMap<String, Row> map) { + Row row = null; + String sql = null; + Conn.Type connType = exec.getConnectionType(conn); + if (connType == Conn.Type.HIVE) { + sql = "DESCRIBE " + table; + } + if (sql != null) { + Query query = new Query(sql); + exec.executeQuery(ctx, query, conn); + if (!query.error()) { + ResultSet rs = query.getResultSet(); + try { + while (rs.next()) { + String col = rs.getString(1); + String typ = rs.getString(2); + if (row == null) { + row = new Row(); + } + row.addColumn(col.toUpperCase(), typ); + } + map.put(table, row); + } + catch (Exception e) {} + } + exec.closeQuery(query, conn); + } + return row; + } + /** * Normalize identifier name (convert "" [] to `` i.e.) */ @@ -55,6 +149,30 @@ public class Meta { } /** + * Split qualified object to 2 parts: schema.tab.col -> schema.tab|col; tab.col -> tab|col + */ + public ArrayList<String> splitIdentifierToTwoParts(String name) { + ArrayList<String> parts = splitIdentifier(name); + ArrayList<String> twoparts = null; + if (parts != null) { + StringBuilder id = new StringBuilder(); + int i = 0; + for (; i < parts.size() - 1; i++) { + id.append(parts.get(i)); + if (i + 1 < parts.size() - 1) { + id.append("."); + } + } + twoparts = new ArrayList<String>(); + twoparts.add(id.toString()); + id.setLength(0); + id.append(parts.get(i)); + twoparts.add(id.toString()); + } + return twoparts; + } + + /** * Split identifier to parts (schema, table, colum name etc.) * @return null if identifier contains single part */ http://git-wip-us.apache.org/repos/asf/hive/blob/da95f633/hplsql/src/main/java/org/apache/hive/hplsql/Row.java ---------------------------------------------------------------------- diff --git a/hplsql/src/main/java/org/apache/hive/hplsql/Row.java b/hplsql/src/main/java/org/apache/hive/hplsql/Row.java new file mode 100644 index 0000000..deeacaf --- /dev/null +++ b/hplsql/src/main/java/org/apache/hive/hplsql/Row.java @@ -0,0 +1,97 @@ +/** + * 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.hive.hplsql; + +import java.util.ArrayList; +import java.util.HashMap; + +/** + * Table row (all columns) + */ +public class Row { + + ArrayList<Column> columns = new ArrayList<Column>(); + HashMap<String, Column> columnMap = new HashMap<String, Column>(); + + /** + * Constructors + */ + Row() {} + + Row(Row row) { + for (Column c : row.columns) { + addColumn(c.name, c.type); + } + } + + /** + * Add a column with specified data type + */ + void addColumn(String name, String type) { + Column column = new Column(name, type); + columns.add(column); + columnMap.put(name, column); + } + + /** + * Get the data type by column name + */ + String getType(String name) { + Column column = columnMap.get(name); + if (column != null) { + return column.getType(); + } + return null; + } + + /** + * Get value by index + */ + Var getValue(int i) { + return columns.get(i).getValue(); + } + + /** + * Get value by column name + */ + Var getValue(String name) { + Column column = columnMap.get(name.toUpperCase()); + if (column != null) { + return column.getValue(); + } + return null; + } + + /** + * Get columns + */ + ArrayList<Column> getColumns() { + return columns; + } + + /** + * Get the number of columns + */ + int size() { + return columns.size(); + } +} + + + http://git-wip-us.apache.org/repos/asf/hive/blob/da95f633/hplsql/src/main/java/org/apache/hive/hplsql/Select.java ---------------------------------------------------------------------- diff --git a/hplsql/src/main/java/org/apache/hive/hplsql/Select.java b/hplsql/src/main/java/org/apache/hive/hplsql/Select.java index 32ca74a..71ca848 100644 --- a/hplsql/src/main/java/org/apache/hive/hplsql/Select.java +++ b/hplsql/src/main/java/org/apache/hive/hplsql/Select.java @@ -92,11 +92,15 @@ public class Select { String into_name = getIntoVariable(ctx, i - 1); Var var = exec.findVariable(into_name); if (var != null) { - var.setValue(rs, rm, i); + if (var.type != Var.Type.ROW) { + var.setValue(rs, rm, i); + } + else { + var.setValues(rs, rm); + } if (trace) { - trace(ctx, "COLUMN: " + rm.getColumnName(i) + ", " + rm.getColumnTypeName(i)); - trace(ctx, "SET " + var.getName() + " = " + var.toString()); - } + trace(ctx, var, rs, rm, i); + } } else { trace(ctx, "Variable not found: " + into_name); @@ -439,4 +443,8 @@ public class Select { void trace(ParserRuleContext ctx, String message) { exec.trace(ctx, message); } + + void trace(ParserRuleContext ctx, Var var, ResultSet rs, ResultSetMetaData rm, int idx) throws SQLException { + exec.trace(ctx, var, rs, rm, idx); + } } \ No newline at end of file http://git-wip-us.apache.org/repos/asf/hive/blob/da95f633/hplsql/src/main/java/org/apache/hive/hplsql/Stmt.java ---------------------------------------------------------------------- diff --git a/hplsql/src/main/java/org/apache/hive/hplsql/Stmt.java b/hplsql/src/main/java/org/apache/hive/hplsql/Stmt.java index 77c3c4f..6193f49 100644 --- a/hplsql/src/main/java/org/apache/hive/hplsql/Stmt.java +++ b/hplsql/src/main/java/org/apache/hive/hplsql/Stmt.java @@ -381,11 +381,15 @@ public class Stmt { for(int i=1; i <= cols; i++) { Var var = exec.findVariable(ctx.L_ID(i).getText()); if(var != null) { - var.setValue(rs, rsm, i); - if(trace) { - trace(ctx, "COLUMN: " + rsm.getColumnName(i) + ", " + rsm.getColumnTypeName(i)); - trace(ctx, "SET " + var.getName() + " = " + var.toString()); - } + if (var.type != Var.Type.ROW) { + var.setValue(rs, rsm, i); + } + else { + var.setValues(rs, rsm); + } + if (trace) { + trace(ctx, var, rs, rsm, i); + } } else if(trace) { trace(ctx, "Variable not found: " + ctx.L_ID(i).getText()); @@ -738,19 +742,16 @@ public class Stmt { if (rs != null) { ResultSetMetaData rm = rs.getMetaData(); int cols = rm.getColumnCount(); - Var[] vars = new Var[cols]; - for (int i = 0; i < cols; i++) { - vars[i] = new Var(); - vars[i].setName(cursor + "." + rm.getColumnName(i + 1)); - vars[i].setType(rm.getColumnType(i + 1)); - exec.addVariable(vars[i]); - if (trace) { - trace(ctx, "Column: " + vars[i].getName() + " " + rm.getColumnTypeName(i + 1)); - } - } + Row row = new Row(); + for (int i = 1; i <= cols; i++) { + row.addColumn(rm.getColumnName(i), rm.getColumnTypeName(i)); + } + Var var = new Var(cursor, row); + exec.addVariable(var); while (rs.next()) { - for (int i = 0; i < cols; i++) { - vars[i].setValue(rs, rm, i + 1); + var.setValues(rs, rm); + if (trace) { + trace(ctx, var, rs, rm, 0); } visit(ctx.block()); exec.incRowCount(); @@ -817,21 +818,24 @@ public class Stmt { ResultSet rs = query.getResultSet(); if (rs != null) { try { - ResultSetMetaData rsm = rs.getMetaData(); - // Assign to variables - if(ctx.T_INTO() != null) { + ResultSetMetaData rm = rs.getMetaData(); + if (ctx.T_INTO() != null) { int cols = ctx.L_ID().size(); - if(rs.next()) { - for(int i=0; i < cols; i++) { + if (rs.next()) { + for (int i = 0; i < cols; i++) { Var var = exec.findVariable(ctx.L_ID(i).getText()); - if(var != null) { - var.setValue(rs, rsm, i+1); - if(trace) { - trace(ctx, "COLUMN: " + rsm.getColumnName(i+1) + ", " + rsm.getColumnTypeName(i+1)); - trace(ctx, "SET " + var.getName() + " = " + var.toString()); + if (var != null) { + if (var.type != Var.Type.ROW) { + var.setValue(rs, rm, i + 1); + } + else { + var.setValues(rs, rm); + } + if (trace) { + trace(ctx, var, rs, rm, i + 1); } } - else if(trace) { + else if (trace) { trace(ctx, "Variable not found: " + ctx.L_ID(i).getText()); } } @@ -840,7 +844,7 @@ public class Stmt { } // Print the results else { - int cols = rsm.getColumnCount(); + int cols = rm.getColumnCount(); while(rs.next()) { for(int i = 1; i <= cols; i++) { if(i > 1) { @@ -864,8 +868,11 @@ public class Stmt { * EXEC to execute a stored procedure */ public Boolean execProc(HplsqlParser.Exec_stmtContext ctx) { - if (exec.function.execProc(ctx.expr_func_params(), evalPop(ctx.expr()).toString())) { - return true; + String name = evalPop(ctx.expr()).toString(); + if (exec.function.isProc(name)) { + if (exec.function.execProc(ctx.expr_func_params(), name)) { + return true; + } } return false; } @@ -1118,4 +1125,8 @@ public class Stmt { void trace(ParserRuleContext ctx, String message) { exec.trace(ctx, message); } + + void trace(ParserRuleContext ctx, Var var, ResultSet rs, ResultSetMetaData rm, int idx) throws SQLException { + exec.trace(ctx, var, rs, rm, idx); + } } http://git-wip-us.apache.org/repos/asf/hive/blob/da95f633/hplsql/src/main/java/org/apache/hive/hplsql/Var.java ---------------------------------------------------------------------- diff --git a/hplsql/src/main/java/org/apache/hive/hplsql/Var.java b/hplsql/src/main/java/org/apache/hive/hplsql/Var.java index 95eb526..b31a14d 100644 --- a/hplsql/src/main/java/org/apache/hive/hplsql/Var.java +++ b/hplsql/src/main/java/org/apache/hive/hplsql/Var.java @@ -32,7 +32,10 @@ import java.sql.Timestamp; public class Var { // Data types - public enum Type {BOOL, CURSOR, DATE, DECIMAL, FILE, IDENT, BIGINT, INTERVAL, RS_LOCATOR, STRING, STRINGLIST, TIMESTAMP, NULL}; + public enum Type {BOOL, CURSOR, DATE, DECIMAL, DERIVED_TYPE, DERIVED_ROWTYPE, FILE, IDENT, BIGINT, INTERVAL, ROW, + RS_LOCATOR, STRING, STRINGLIST, TIMESTAMP, NULL}; + public static final String DERIVED_TYPE = "DERIVED%TYPE"; + public static final String DERIVED_ROWTYPE = "DERIVED%ROWTYPE"; public static Var Empty = new Var(); public static Var Null = new Var(Type.NULL); @@ -102,6 +105,12 @@ public class Var { value = b; } + public Var(String name, Row row) { + this.name = name; + this.type = Type.ROW; + this.value = new Row(row); + } + public Var(Type type, String name) { this.type = type; this.name = name; @@ -140,9 +149,13 @@ public class Var { * Cast a new value to the variable */ public Var cast(Var val) { - if (val == null || val.value == null) { + if (val == null || val.value == null) { value = null; } + else if (type == Type.DERIVED_TYPE) { + type = val.type; + value = val.value; + } else if (type == val.type && type == Type.STRING) { cast((String)val.value); } @@ -211,7 +224,7 @@ public class Var { } /** - * Set the new value from a result set + * Set the new value from the result set */ public Var setValue(ResultSet rs, ResultSetMetaData rsm, int idx) throws SQLException { int type = rsm.getColumnType(idx); @@ -227,6 +240,21 @@ public class Var { } return this; } + + /** + * Set ROW values from the result set + */ + public Var setValues(ResultSet rs, ResultSetMetaData rsm) throws SQLException { + Row row = (Row)this.value; + int idx = 1; + for (Column column : row.getColumns()) { + Var var = new Var(column.getName(), column.getType(), null, null, null); + var.setValue(rs, rsm, idx); + column.setValue(var); + idx++; + } + return this; + } /** * Set the data type from string representation @@ -274,6 +302,9 @@ public class Var { else if (type.toUpperCase().startsWith("RESULT_SET_LOCATOR")) { return Type.RS_LOCATOR; } + else if (type.equalsIgnoreCase(Var.DERIVED_TYPE)) { + return Type.DERIVED_TYPE; + } return Type.NULL; } http://git-wip-us.apache.org/repos/asf/hive/blob/da95f633/hplsql/src/main/java/org/apache/hive/hplsql/functions/Function.java ---------------------------------------------------------------------- diff --git a/hplsql/src/main/java/org/apache/hive/hplsql/functions/Function.java b/hplsql/src/main/java/org/apache/hive/hplsql/functions/Function.java index ab0814d..ae7acae 100644 --- a/hplsql/src/main/java/org/apache/hive/hplsql/functions/Function.java +++ b/hplsql/src/main/java/org/apache/hive/hplsql/functions/Function.java @@ -71,6 +71,9 @@ public class Function { if (execUser(ctx, name)) { return; } + else if (isProc(name) && execProc(ctx, name)) { + return; + } if (name.indexOf(".") != -1) { // Name can be qualified and spaces are allowed between parts String[] parts = name.split("\\."); StringBuilder str = new StringBuilder(); @@ -201,6 +204,16 @@ public class Function { } /** + * Check if the stored procedure with the specified name is defined + */ + public boolean isProc(String name) { + if (procMap.get(name.toUpperCase()) != null) { + return true; + } + return false; + } + + /** * Execute a stored procedure using CALL or EXEC statement passing parameters */ public boolean execProc(HplsqlParser.Expr_func_paramsContext ctx, String name) { http://git-wip-us.apache.org/repos/asf/hive/blob/da95f633/hplsql/src/test/java/org/apache/hive/hplsql/TestHplsqlLocal.java ---------------------------------------------------------------------- diff --git a/hplsql/src/test/java/org/apache/hive/hplsql/TestHplsqlLocal.java b/hplsql/src/test/java/org/apache/hive/hplsql/TestHplsqlLocal.java index 4a08a18..6a67cd0 100644 --- a/hplsql/src/test/java/org/apache/hive/hplsql/TestHplsqlLocal.java +++ b/hplsql/src/test/java/org/apache/hive/hplsql/TestHplsqlLocal.java @@ -92,6 +92,11 @@ public class TestHplsqlLocal { public void testCreateProcedure() throws Exception { run("create_procedure"); } + + @Test + public void testCreateProcedureNoParams() throws Exception { + run("create_procedure_no_params"); + } @Test public void testDate() throws Exception { @@ -301,7 +306,7 @@ public class TestHplsqlLocal { BufferedReader reader = new BufferedReader(new StringReader(s)); String line = null; while ((line = reader.readLine()) != null) { - if (!line.startsWith("log4j:")) { + if (!line.startsWith("log4j:") && !line.contains("INFO Log4j")) { sb.append(line); sb.append("\n"); } http://git-wip-us.apache.org/repos/asf/hive/blob/da95f633/hplsql/src/test/java/org/apache/hive/hplsql/TestHplsqlOffline.java ---------------------------------------------------------------------- diff --git a/hplsql/src/test/java/org/apache/hive/hplsql/TestHplsqlOffline.java b/hplsql/src/test/java/org/apache/hive/hplsql/TestHplsqlOffline.java index 0063cac..eeaa395 100644 --- a/hplsql/src/test/java/org/apache/hive/hplsql/TestHplsqlOffline.java +++ b/hplsql/src/test/java/org/apache/hive/hplsql/TestHplsqlOffline.java @@ -66,7 +66,7 @@ public class TestHplsqlOffline { BufferedReader reader = new BufferedReader(new StringReader(s)); String line = null; while ((line = reader.readLine()) != null) { - if (!line.startsWith("log4j:")) { + if (!line.startsWith("log4j:") && !line.contains("INFO Log4j")) { sb.append(line); sb.append("\n"); } http://git-wip-us.apache.org/repos/asf/hive/blob/da95f633/hplsql/src/test/queries/db/rowtype_attribute.sql ---------------------------------------------------------------------- diff --git a/hplsql/src/test/queries/db/rowtype_attribute.sql b/hplsql/src/test/queries/db/rowtype_attribute.sql new file mode 100644 index 0000000..6a84b57 --- /dev/null +++ b/hplsql/src/test/queries/db/rowtype_attribute.sql @@ -0,0 +1,22 @@ +DECLARE + v1 default.src%ROWTYPE; + v2 src %ROWTYPE; + v3 src % ROWTYPE; + CURSOR c1 IS SELECT 'A' AS key, 'B' AS value FROM src LIMIT 1; +BEGIN + SELECT 'A' AS key, 'B' AS value INTO v1 FROM src LIMIT 1; + PRINT v1.key || v1.value; + + OPEN c1; + FETCH c1 INTO v2; + PRINT v2.key || v2.value; + CLOSE c1; + + FOR rec IN (SELECT 'A' AS key, 'B' AS value FROM src LIMIT 1) + LOOP + PRINT rec.key || rec.value; + END LOOP; + + EXECUTE IMMEDIATE 'SELECT ''A'' AS key, ''B'' AS value FROM src LIMIT 1' INTO v3; + PRINT v3.key || v3.value; +END \ No newline at end of file http://git-wip-us.apache.org/repos/asf/hive/blob/da95f633/hplsql/src/test/queries/db/type_attribute.sql ---------------------------------------------------------------------- diff --git a/hplsql/src/test/queries/db/type_attribute.sql b/hplsql/src/test/queries/db/type_attribute.sql new file mode 100644 index 0000000..2d93bfd --- /dev/null +++ b/hplsql/src/test/queries/db/type_attribute.sql @@ -0,0 +1,8 @@ +DECLARE + v1 default.src.key%TYPE; + v2 src.Key %TYPE; + v3 src.key3 % TYPE; +BEGIN + SELECT 'A', 'B', 1 INTO v1, v2, v3 FROM src LIMIT 1; + PRINT v1 || v2 || v3; +END \ No newline at end of file http://git-wip-us.apache.org/repos/asf/hive/blob/da95f633/hplsql/src/test/queries/local/create_procedure_no_params.sql ---------------------------------------------------------------------- diff --git a/hplsql/src/test/queries/local/create_procedure_no_params.sql b/hplsql/src/test/queries/local/create_procedure_no_params.sql new file mode 100644 index 0000000..535ba98 --- /dev/null +++ b/hplsql/src/test/queries/local/create_procedure_no_params.sql @@ -0,0 +1,19 @@ +create procedure sp1 +begin + print 'a'; +end; + +create procedure sp2() +begin + print 'b'; +end; + +call sp1; +call sp1(); +sp1; +sp1(); + +call sp2; +call sp2(); +sp2; +sp2(); \ No newline at end of file http://git-wip-us.apache.org/repos/asf/hive/blob/da95f633/hplsql/src/test/queries/offline/create_table_ora.sql ---------------------------------------------------------------------- diff --git a/hplsql/src/test/queries/offline/create_table_ora.sql b/hplsql/src/test/queries/offline/create_table_ora.sql index c47c75c..bb1d9c7 100644 --- a/hplsql/src/test/queries/offline/create_table_ora.sql +++ b/hplsql/src/test/queries/offline/create_table_ora.sql @@ -1,4 +1,53 @@ -CREATE TABLE ora_t1 ( - n1 NUMBER(3,0), - v1 VARCHAR2(10) +CREATE TABLE ora_t1 ( + n1 NUMBER(3,0), + v1 VARCHAR2(10) +); + +CREATE TABLE "USER"."EMP" + ( "EMPNO" NUMBER(4,0), + "ENAME" VARCHAR2(10 BYTE), + "JOB" VARCHAR2(9 BYTE), + "MGR" NUMBER(4,0), + "HIREDATE" DATE, + "SAL" NUMBER(7,2), + "COMM" NUMBER(7,2), + "DEPTNO" NUMBER(2,0) + ) SEGMENT CREATION IMMEDIATE + PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING + STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 + PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) + TABLESPACE "USERS" ; + +CREATE TABLE language ( + id NUMBER(7) NOT NULL PRIMARY KEY, + cd CHAR(2) NOT NULL, + description VARCHAR2(50) +); +CREATE TABLE author ( + id NUMBER(7) NOT NULL PRIMARY KEY, + first_name VARCHAR2(50), + last_name VARCHAR2(50) NOT NULL, + date_of_birth DATE, + year_of_birth NUMBER(7), + distinguished NUMBER(1) +); +CREATE TABLE book ( + id NUMBER(7) NOT NULL PRIMARY KEY, + author_id NUMBER(7) NOT NULL, + title VARCHAR2(400) NOT NULL, + published_in NUMBER(7) NOT NULL, + language_id NUMBER(7) NOT NULL, + CONSTRAINT fk_book_author FOREIGN KEY (author_id) REFERENCES author(id), + CONSTRAINT fk_book_language FOREIGN KEY (language_id) REFERENCES language(id) +); +CREATE TABLE book_store ( + name VARCHAR2(400) NOT NULL UNIQUE +); +CREATE TABLE book_to_book_store ( + name VARCHAR2(400) NOT NULL, + book_id INTEGER NOT NULL, + stock INTEGER, + PRIMARY KEY(name, book_id), + CONSTRAINT fk_b2bs_book_store FOREIGN KEY (name) REFERENCES book_store (name) ON DELETE CASCADE, + CONSTRAINT fk_b2bs_book FOREIGN KEY (book_id) REFERENCES book (id) ON DELETE CASCADE ); \ No newline at end of file http://git-wip-us.apache.org/repos/asf/hive/blob/da95f633/hplsql/src/test/results/db/rowtype_attribute.out.txt ---------------------------------------------------------------------- diff --git a/hplsql/src/test/results/db/rowtype_attribute.out.txt b/hplsql/src/test/results/db/rowtype_attribute.out.txt new file mode 100644 index 0000000..fc22370 --- /dev/null +++ b/hplsql/src/test/results/db/rowtype_attribute.out.txt @@ -0,0 +1,42 @@ +Ln:2 DECLARE v1 default.src%ROWTYPE +Ln:3 DECLARE v2 src%ROWTYPE +Ln:4 DECLARE v3 src%ROWTYPE +Ln:5 DECLARE CURSOR c1 +Ln:7 SELECT +Ln:7 SELECT 'A' AS key, 'B' AS value FROM src LIMIT 1 +Ln:7 SELECT completed successfully +Ln:7 SELECT INTO statement executed +Ln:7 COLUMN: key, string +Ln:7 SET KEY = A +Ln:7 COLUMN: value, string +Ln:7 SET VALUE = B +Ln:8 PRINT +AB +Ln:10 OPEN +Ln:10 c1: SELECT 'A' AS key, 'B' AS value FROM src LIMIT 1 +Ln:11 FETCH +Ln:11 COLUMN: key, string +Ln:11 SET KEY = A +Ln:11 COLUMN: value, string +Ln:11 SET VALUE = B +Ln:12 PRINT +AB +Ln:13 CLOSE +Ln:15 FOR CURSOR - ENTERED +Ln:15 SELECT 'A' AS key, 'B' AS value FROM src LIMIT 1 +Ln:15 SELECT completed successfully +Ln:15 COLUMN: key, string +Ln:15 SET key = A +Ln:15 COLUMN: value, string +Ln:15 SET value = B +Ln:17 PRINT +null +Ln:15 FOR CURSOR - LEFT +Ln:20 EXECUTE +Ln:20 SQL statement: SELECT 'A' AS key, 'B' AS value FROM src LIMIT 1 +Ln:20 COLUMN: key, string +Ln:20 SET KEY = A +Ln:20 COLUMN: value, string +Ln:20 SET VALUE = B +Ln:21 PRINT +AB \ No newline at end of file http://git-wip-us.apache.org/repos/asf/hive/blob/da95f633/hplsql/src/test/results/db/type_attribute.out.txt ---------------------------------------------------------------------- diff --git a/hplsql/src/test/results/db/type_attribute.out.txt b/hplsql/src/test/results/db/type_attribute.out.txt new file mode 100644 index 0000000..e236f5b --- /dev/null +++ b/hplsql/src/test/results/db/type_attribute.out.txt @@ -0,0 +1,15 @@ +Ln:2 DECLARE v1 string +Ln:3 DECLARE v2 string +Ln:4 DECLARE v3 DERIVED%TYPE +Ln:6 SELECT +Ln:6 SELECT 'A', 'B', 1 FROM src LIMIT 1 +Ln:6 SELECT completed successfully +Ln:6 SELECT INTO statement executed +Ln:6 COLUMN: _c0, string +Ln:6 SET v1 = A +Ln:6 COLUMN: _c1, string +Ln:6 SET v2 = B +Ln:6 COLUMN: _c2, int +Ln:6 SET v3 = 1 +Ln:7 PRINT +AB1 \ No newline at end of file http://git-wip-us.apache.org/repos/asf/hive/blob/da95f633/hplsql/src/test/results/local/create_procedure_no_params.out.txt ---------------------------------------------------------------------- diff --git a/hplsql/src/test/results/local/create_procedure_no_params.out.txt b/hplsql/src/test/results/local/create_procedure_no_params.out.txt new file mode 100644 index 0000000..9e3bde9 --- /dev/null +++ b/hplsql/src/test/results/local/create_procedure_no_params.out.txt @@ -0,0 +1,26 @@ +Ln:1 CREATE PROCEDURE sp1 +Ln:6 CREATE PROCEDURE sp2 +EXEC PROCEDURE sp1 +Ln:3 PRINT +a +EXEC PROCEDURE sp1 +Ln:3 PRINT +a +EXEC PROCEDURE sp1 +Ln:3 PRINT +a +EXEC PROCEDURE sp1 +Ln:3 PRINT +a +EXEC PROCEDURE sp2 +Ln:8 PRINT +b +EXEC PROCEDURE sp2 +Ln:8 PRINT +b +EXEC PROCEDURE sp2 +Ln:8 PRINT +b +EXEC PROCEDURE sp2 +Ln:8 PRINT +b \ No newline at end of file http://git-wip-us.apache.org/repos/asf/hive/blob/da95f633/hplsql/src/test/results/offline/create_table_ora.out.txt ---------------------------------------------------------------------- diff --git a/hplsql/src/test/results/offline/create_table_ora.out.txt b/hplsql/src/test/results/offline/create_table_ora.out.txt index 33af104..cf30c0f 100644 --- a/hplsql/src/test/results/offline/create_table_ora.out.txt +++ b/hplsql/src/test/results/offline/create_table_ora.out.txt @@ -1,4 +1,42 @@ Ln:1 CREATE TABLE Ln:1 CREATE TABLE ora_t1 (n1 DECIMAL(3,0), v1 STRING +) +Ln:6 CREATE TABLE +Ln:6 CREATE TABLE `USER`.`EMP` (`EMPNO` DECIMAL(4,0), +`ENAME` STRING, +`JOB` STRING, +`MGR` DECIMAL(4,0), +`HIREDATE` DATE, +`SAL` DECIMAL(7,2), +`COMM` DECIMAL(7,2), +`DEPTNO` DECIMAL(2,0) +) +Ln:21 CREATE TABLE +Ln:21 CREATE TABLE language (id DECIMAL(7), +cd CHAR(2), +description STRING +) +Ln:26 CREATE TABLE +Ln:26 CREATE TABLE author (id DECIMAL(7), +first_name STRING, +last_name STRING, +date_of_birth DATE, +year_of_birth DECIMAL(7), +distinguished DECIMAL(1) +) +Ln:34 CREATE TABLE +Ln:34 CREATE TABLE book (id DECIMAL(7), +author_id DECIMAL(7), +title STRING, +published_in DECIMAL(7), +language_id DECIMAL(7) +) +Ln:43 CREATE TABLE +Ln:43 CREATE TABLE book_store (name STRING +) +Ln:46 CREATE TABLE +Ln:46 CREATE TABLE book_to_book_store (name STRING, +book_id INTEGER, +stock INTEGER ) \ No newline at end of file