-
Notifications
You must be signed in to change notification settings - Fork 77
Expand file tree
/
Copy pathtestCreateTable.java
More file actions
79 lines (65 loc) · 4.06 KB
/
Copy pathtestCreateTable.java
File metadata and controls
79 lines (65 loc) · 4.06 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
package test;
import gudusoft.gsqlparser.EDbVendor;
import gudusoft.gsqlparser.ETableKind;
import gudusoft.gsqlparser.TGSqlParser;
import gudusoft.gsqlparser.stmt.TCreateTableSqlStatement;
import gudusoft.gsqlparser.stmt.TSelectSqlStatement;
import junit.framework.TestCase;
public class testCreateTable extends TestCase {
public void testDuplicateColumn(){
TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvoracle);
sqlparser.sqltext = "create table all_data_types ( \n" +
" col1 GOOGLE,\n" +
" col1 CRICBUZZ,\n" +
" col1 GOIBIBO,\n" +
" col1 AIRINDIA\n" +
");";
assertTrue(sqlparser.parse() != 0);
}
public void testTemporaryTable(){
TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvnetezza);
sqlparser.sqltext = "create temporary table vt (id int, name varchar(1)) distribute on(id)";
assertTrue(sqlparser.parse() == 0);
TCreateTableSqlStatement createTable = (TCreateTableSqlStatement)sqlparser.sqlstatements.get(0);
assertTrue(createTable.getTargetTable().toString().equalsIgnoreCase("vt"));
assertTrue(createTable.getTableKinds().contains(ETableKind.etkTemporary));
sqlparser.sqltext = "CREATE TEMP TABLE cows2 AS SELECT cname, cbreed FROM cows DISTRIBUTE ON RANDOM;";
assertTrue(sqlparser.parse() == 0);
createTable = (TCreateTableSqlStatement)sqlparser.sqlstatements.get(0);
assertTrue(createTable.getTargetTable().toString().equalsIgnoreCase("cows2"));
// System.out.println(createTable.getTableKinds());
assertTrue(createTable.getTableKinds().contains(ETableKind.etkTemp));
}
public void testSAMEAS() {
TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvnetezza);
sqlparser.sqltext = "CREATE EXTERNAL TABLE demo_ext SAMEAS t1 USING (dataobject ('/tmp/demo.out' ) DELIMITER '|');";
assertTrue(sqlparser.parse() == 0);
TCreateTableSqlStatement createTable = (TCreateTableSqlStatement) sqlparser.sqlstatements.get(0);
assertTrue(createTable.getAsTable().toString().equalsIgnoreCase("t1"));
}
public void testAsSubquery() {
TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvnetezza);
sqlparser.sqltext = "CREATE TEMP TABLE PROD_ETL.PHDM.TEMP_FEP_CONTRACT_ENROLLMENT_COVERAGE AS\n" +
"SELECT DISTINCT \n" +
" CAST('CONTRACTENROLLMENT' AS VARCHAR(25)) AS SUBSCR_CVRG_DATA_SRC_CD,\n" +
" CES.CONTRACT_R_NUMBER, \n" +
" CES.PRODUCT_CODE,\n" +
" CES.FAMILY_STATUS_CODE,\n" +
" GREATEST(CES.CONTRACT_ENROLLMENT_EFFECTIVE_DATE,CE.BLUE_CROSS_PLAN_CODE_EFFECTIVE_DATE) AS BEGIN_DATE, \n" +
" LEAST(CES.CONTRACT_ENROLLMENT_TERMINATION_DATE,CE.BLUE_CROSS_PLAN_CODE_TERMINATION_DATE) AS END_DATE\n" +
"FROM PROD_STG.FEP_IDEA.CONTRACTENROLLMENTEXTRACT_STG01 CES\n" +
"INNER JOIN PROD_STG.FEP_IDEA.CONTRACTPLANEXTRACT_STG01 CE\n" +
"ON CES.CONTRACT_R_NUMBER = CE.CONTRACT_ID_R_NUMBER\n" +
"WHERE (CES.CONTRACT_ENROLLMENT_EFFECTIVE_DATE, CES.CONTRACT_ENROLLMENT_TERMINATION_DATE)\n" +
" OVERLAPS\n" +
" (CE.BLUE_CROSS_PLAN_CODE_EFFECTIVE_DATE, CE.BLUE_CROSS_PLAN_CODE_TERMINATION_DATE)\n" +
"AND CES.CONTRACT_ACTIVE_FLAG = 'Y'\n" +
"AND CES.CONTRACT_DELETE_FLAG = 'N'\n" +
"DISTRIBUTE ON (CONTRACT_R_NUMBER);";
assertTrue(sqlparser.parse() == 0);
TCreateTableSqlStatement createTable = (TCreateTableSqlStatement) sqlparser.sqlstatements.get(0);
assertTrue(createTable.getTableName().toString().equalsIgnoreCase("PROD_ETL.PHDM.TEMP_FEP_CONTRACT_ENROLLMENT_COVERAGE"));
TSelectSqlStatement subquery = createTable.getSubQuery();
assertTrue(subquery.getTables().getTable(0).toString().equalsIgnoreCase("PROD_STG.FEP_IDEA.CONTRACTENROLLMENTEXTRACT_STG01"));
}
}