View Javadoc
1   /*
2   Copyright (c) 2008 Health Market Science, Inc.
3   
4   Licensed under the Apache License, Version 2.0 (the "License");
5   you may not use this file except in compliance with the License.
6   You may obtain a copy of the License at
7   
8       http://www.apache.org/licenses/LICENSE-2.0
9   
10  Unless required by applicable law or agreed to in writing, software
11  distributed under the License is distributed on an "AS IS" BASIS,
12  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13  See the License for the specific language governing permissions and
14  limitations under the License.
15  */
16  
17  package com.healthmarketscience.sqlbuilder;
18  
19  import java.io.IOException;
20  import java.sql.Types;
21  import java.text.SimpleDateFormat;
22  import java.util.Arrays;
23  import java.util.Date;
24  
25  import com.healthmarketscience.common.util.AppendableExt;
26  import com.healthmarketscience.sqlbuilder.dbspec.Column;
27  import com.healthmarketscience.sqlbuilder.dbspec.RejoinTable;
28  import com.healthmarketscience.sqlbuilder.dbspec.basic.DbColumn;
29  import com.healthmarketscience.sqlbuilder.dbspec.basic.DbFunction;
30  import com.healthmarketscience.sqlbuilder.dbspec.basic.DbFunctionPackage;
31  import com.healthmarketscience.sqlbuilder.dbspec.basic.DbIndex;
32  import com.healthmarketscience.sqlbuilder.dbspec.basic.DbSpec;
33  import com.healthmarketscience.sqlbuilder.dbspec.basic.DbTable;
34  
35  
36  /**
37   * @author James Ahlborn
38   */
39  public class SqlBuilderTest extends BaseSqlTestCase
40  {
41    
42    public SqlBuilderTest(String name) {
43      super(name);
44    }
45  
46    public void testCreateTable()
47    {
48      String createStr1 = new CreateTableQuery(_table1)
49        .addColumns(_table1_col1, _table1_col3).validate().toString();
50      checkResult(createStr1,
51                  "CREATE TABLE Schema1.Table1 (col1 VARCHAR(213),col3 DECIMAL(4,8))");
52      
53      String createStr2 = new CreateTableQuery(_table1, true)
54        .validate().toString();
55      checkResult(createStr2,
56                  "CREATE TABLE Schema1.Table1 (col1 VARCHAR(213),col2 NUMBER(7),col3 DECIMAL(4,8),col4 VARCHAR(255))");
57  
58      String createStr3 = new CreateTableQuery(_defTable1, true)
59        .validate().toString();
60      checkResult(createStr3,
61                  "CREATE TABLE Table1 (col_id NUMBER,col2 VARCHAR(64) DEFAULT 'blah',col3 DATE,altCol4 VARCHAR(255))");
62  
63      @SuppressWarnings("deprecation")
64      String createStr4 = new CreateTableQuery(_defTable1, false)
65        .addColumns(_defTable1_col_id, _defTable1_col2)
66        .setColumnConstraint(_defTable1_col_id,
67                             CreateTableQuery.ColumnConstraint.PRIMARY_KEY)
68        .addColumn(_defTable1_col3, CreateTableQuery.ColumnConstraint.NOT_NULL)
69        .addColumnConstraint(_defTable1_col3, 
70                             ConstraintClause.foreignKey("col3_fk", _table1)
71                             .addRefColumns(_table1_col3))
72        .addCustomColumn("col4 NUMBER", CreateTableQuery.ColumnConstraint.NOT_NULL)
73        .addColumnConstraint(_table1_col1, CreateTableQuery.ColumnConstraint.UNIQUE)
74        .validate().toString();
75      checkResult(createStr4,
76                  "CREATE TABLE Table1 (col_id NUMBER PRIMARY KEY,col2 VARCHAR(64) DEFAULT 'blah',col3 DATE NOT NULL CONSTRAINT col3_fk REFERENCES Schema1.Table1 (col3),col4 NUMBER NOT NULL)");
77  
78      String createStr5 = new CreateTableQuery(_defTable1, true)
79        .addColumnConstraint(_defTable1_col_id,
80                             ConstraintClause.notNull())
81        .addColumnConstraint(_defTable1_col_id,
82                             ConstraintClause.primaryKey("id_pk"))
83        .setColumnTypeName(_defTable1_col_id, "BIGINT")
84        .addColumnConstraint(_defTable1_col_id, ConstraintClause.checkCondition(
85                                 BinaryCondition.greaterThan(_defTable1_col_id, 10, false)))
86        .addColumnConstraint(_defTable1_col3,
87                             ConstraintClause.notNull())
88        .setColumnDefaultValue(_defTable1_col3, new CustomSql("CURRENT_DATE"))
89        .addCustomConstraints(ConstraintClause.unique() 
90                              .addColumns(_defTable1_col2, _defTable1_col3))
91        .validate().toString();
92      checkResult(createStr5,
93                  "CREATE TABLE Table1 (col_id BIGINT NOT NULL CONSTRAINT id_pk PRIMARY KEY CHECK (col_id > 10),col2 VARCHAR(64) DEFAULT 'blah',col3 DATE DEFAULT CURRENT_DATE NOT NULL,altCol4 VARCHAR(255),UNIQUE (col2,col3))");
94  
95      String createStr6 = new CreateTableQuery(_defTable2, true)
96        .setTableType(CreateTableQuery.TableType.TEMPORARY)
97        .validate().toString();
98      checkResult(createStr6,
99                  "CREATE TEMPORARY TABLE Table2 (col_id NUMBER NOT NULL CONSTRAINT col_id_pk PRIMARY KEY,col4 VARCHAR(64),col5 DATE,CONSTRAINT t2_fk FOREIGN KEY (col4,col5) REFERENCES Table1 (col2,col3),CONSTRAINT neq_cond CHECK (col4 <> col5))");
100 
101     try {
102       new CreateTableQuery(_table1).validate();
103       fail("ValidationException should have been thrown");
104     } catch(ValidationException e) {}
105   }
106 
107   public void testIndex()
108   {
109     DbIndex index = _schema1.addIndex("Index1", "Table1",
110                                       "col1", "col2");
111     CreateIndexQuery query = new CreateIndexQuery(index);
112     String createStr1 = query.validate().toString();
113     checkResult(createStr1,
114                 "CREATE INDEX Schema1.Index1 ON Schema1.Table1 (col1,col2)");
115 
116     
117     String createStr2 = query.setIndexType(CreateIndexQuery.IndexType.UNIQUE)
118       .validate().toString();
119     checkResult(createStr2,
120                 "CREATE UNIQUE INDEX Schema1.Index1 ON Schema1.Table1 (col1,col2)");
121 
122     String dropStr1 = query.getDropQuery().validate().toString();
123     checkResult(dropStr1,
124                 "DROP INDEX Schema1.Index1");
125   }
126   
127   public void testDropTable()
128   {
129     String dropStr1 = DropQuery.dropTable(_table1).validate().toString();
130     checkResult(dropStr1, "DROP TABLE Schema1.Table1");
131 
132     String dropStr2 = DropQuery.dropTable(_defTable1)
133       .setBehavior(DropQuery.Behavior.CASCADE).validate().toString();
134     checkResult(dropStr2, "DROP TABLE Table1 CASCADE");
135 
136     String dropStr3 = new CreateTableQuery(_table1)
137       .addColumns(_table1_col1, _table1_col3)
138       .getDropQuery().validate().toString();
139     checkResult(dropStr3, "DROP TABLE Schema1.Table1");
140     
141     String dropStr4 = new CreateTableQuery(_defTable1, true)
142       .getDropQuery().validate().toString();
143     checkResult(dropStr4, "DROP TABLE Table1");
144   }
145 
146   public void testInsert()
147   {
148     String insertStr1 = new InsertQuery(_table1)
149       .addColumns(new DbColumn[]{_table1_col1, _table1_col3, _table1_col2},
150                   new Object[]{13, "feed me seymor", true})
151       .validate().toString();
152     checkResult(insertStr1,
153                 "INSERT INTO Schema1.Table1 (col1,col3,col2) VALUES (13,'feed me seymor',1)");
154     
155     String insertStr2 = new InsertQuery(_table1)
156       .addColumns(new DbColumn[]{_table1_col1},
157                   new Object[]{"13"})
158       .addPreparedColumns(_table1_col2, _table1_col3)
159       .validate().toString();
160     checkResult(insertStr2,
161                 "INSERT INTO Schema1.Table1 (col1,col2,col3) VALUES ('13',?,?)");
162 
163     String insertStr3 = new InsertQuery(_defTable1)
164       .addColumns(new DbColumn[]{_defTable1_col_id},
165                   new Object[]{13})
166       .addPreparedColumns(_defTable1_col2, _defTable1_col3)
167       .validate().toString();
168     checkResult(insertStr3,
169                 "INSERT INTO Table1 (col_id,col2,col3) VALUES (13,?,?)");
170     
171     try {
172       new InsertQuery(_table1)
173         .addColumns(new DbColumn[]{_table1_col1, _table1_col3},
174                     new Object[]{13})
175         .validate();
176       fail("ValidationException should have been thrown");
177     } catch(ValidationException e) {}
178   }
179 
180   public void testInsertSelect()
181   {
182     SelectQuery selectQuery = new SelectQuery()
183       .addColumns(_table1_col1, _table1_col2, _table1_col3).validate();
184 
185     String insertStr1 = new InsertSelectQuery(_defTable1)
186       .addColumns(_defTable1_col_id, _defTable1_col2,
187                   _defTable1_col3)
188       .setSelectQuery(selectQuery)
189       .validate().toString();
190     checkResult(insertStr1,
191                 "INSERT INTO Table1 (col_id,col2,col3) SELECT t0.col1,t0.col2,t0.col3 FROM Schema1.Table1 t0");
192 
193     try {
194       new InsertSelectQuery(_defTable1)
195         .addColumns(_defTable1_col_id, _defTable1_col2,
196                     _defTable1_col3)
197         .validate();
198       fail("ValidationException should have been thrown");
199     } catch(ValidationException e) {}
200 
201     try {
202       new InsertSelectQuery(_defTable1)
203         .addColumns(_defTable1_col_id, _defTable1_col2)
204         .setSelectQuery(selectQuery)
205         .validate();
206       fail("ValidationException should have been thrown");
207     } catch(ValidationException e) {}
208   }
209 
210   public void testSelect()
211   {
212     {
213       SelectQuery selectQuery1 = new SelectQuery()
214         .addColumns(_table1_col1, _defTable1_col2, _defTable2_col5);
215 
216       String selectStr1 = selectQuery1.validate().toString();
217       checkResult(selectStr1,
218                 "SELECT t0.col1,t1.col2,t2.col5 FROM Schema1.Table1 t0,Table1 t1,Table2 t2");
219 
220       String selectStr2 = selectQuery1.setIsDistinct(true)
221         .validate().toString();
222       checkResult(selectStr2,
223                   "SELECT DISTINCT t0.col1,t1.col2,t2.col5 FROM Schema1.Table1 t0,Table1 t1,Table2 t2");
224 
225       String selectStr3 = selectQuery1.addJoins(SelectQuery.JoinType.INNER,
226                                                 _col4Join)
227         .addJoins(SelectQuery.JoinType.LEFT_OUTER, _idJoin)
228         .validate().toString();
229       checkResult(selectStr3,
230                   "SELECT DISTINCT t0.col1,t1.col2,t2.col5 FROM Schema1.Table1 t0 INNER JOIN Table1 t1 ON (t0.col4 = t1.altCol4) LEFT OUTER JOIN Table2 t2 ON (t1.col_id = t2.col_id)");
231 
232       String selectStr4 = selectQuery1.addOrderings(_defTable1_col2)
233         .validate().toString();
234       checkResult(selectStr4,
235                   "SELECT DISTINCT t0.col1,t1.col2,t2.col5 FROM Schema1.Table1 t0 INNER JOIN Table1 t1 ON (t0.col4 = t1.altCol4) LEFT OUTER JOIN Table2 t2 ON (t1.col_id = t2.col_id) ORDER BY t1.col2");
236 
237       String selectStr5 = selectQuery1.addCondition(
238         BinaryCondition.greaterThan(_defTable2_col4, 42, true))
239         .validate().toString();
240       checkResult(selectStr5,
241                   "SELECT DISTINCT t0.col1,t1.col2,t2.col5 FROM Schema1.Table1 t0 INNER JOIN Table1 t1 ON (t0.col4 = t1.altCol4) LEFT OUTER JOIN Table2 t2 ON (t1.col_id = t2.col_id) WHERE (t2.col4 >= 42) ORDER BY t1.col2");
242 
243       String selectStr6 = selectQuery1.addOrdering(_defTable2_col5,
244                                                    OrderObject.Dir.DESCENDING)
245         .validate().toString();
246       checkResult(selectStr6,
247                   "SELECT DISTINCT t0.col1,t1.col2,t2.col5 FROM Schema1.Table1 t0 INNER JOIN Table1 t1 ON (t0.col4 = t1.altCol4) LEFT OUTER JOIN Table2 t2 ON (t1.col_id = t2.col_id) WHERE (t2.col4 >= 42) ORDER BY t1.col2,t2.col5 DESC");
248 
249       String selectStr7 = selectQuery1.addHaving(BinaryCondition.greaterThan(_defTable1_col2, new NumberValueObject(1), false)).toString();
250       checkResult(selectStr7,
251                   "SELECT DISTINCT t0.col1,t1.col2,t2.col5 FROM Schema1.Table1 t0 INNER JOIN Table1 t1 ON (t0.col4 = t1.altCol4) LEFT OUTER JOIN Table2 t2 ON (t1.col_id = t2.col_id) WHERE (t2.col4 >= 42) ORDER BY t1.col2,t2.col5 DESC");
252       
253       String selectStr8 = selectQuery1.addGroupings(_defTable1_col2,
254                                                     _defTable2_col5)
255         .validate().toString();
256       checkResult(selectStr8,
257                   "SELECT DISTINCT t0.col1,t1.col2,t2.col5 FROM Schema1.Table1 t0 INNER JOIN Table1 t1 ON (t0.col4 = t1.altCol4) LEFT OUTER JOIN Table2 t2 ON (t1.col_id = t2.col_id) WHERE (t2.col4 >= 42) GROUP BY t1.col2,t2.col5 HAVING (t1.col2 > 1) ORDER BY t1.col2,t2.col5 DESC");
258     }
259 
260     String selectStr6 = new SelectQuery()
261       .addAllTableColumns(_table1)
262       .validate().toString();
263     checkResult(selectStr6,
264                 "SELECT t0.* FROM Schema1.Table1 t0");
265 
266     String selectStr7 = new SelectQuery()
267       .addAllColumns()
268       .addFromTable(_defTable1)
269       .addFromTable(_defTable2)
270       .validate().toString();
271     checkResult(selectStr7,
272                 "SELECT * FROM Table1 t1, Table2 t2");
273 
274     String selectStr8 = new SelectQuery()
275       .setForUpdate(true)
276       .addAllColumns()
277       .addFromTable(_defTable1)
278       .addFromTable(_defTable2)
279       .validate().toString();
280     checkResult(selectStr8,
281                 "SELECT * FROM Table1 t1, Table2 t2 FOR UPDATE");
282 
283     String selectStr9 = new SelectQuery()
284       .addColumns(_table1_col1, _defTable1_col2)
285       .addCustomColumns(Converter.toColumnSqlObject(
286                             _defTable2_col5, "MyCol"))
287       .addAliasedColumn(_defTable2_col4, "SomeCol")
288       .validate().toString();
289     checkResult(selectStr9,
290                 "SELECT t0.col1,t1.col2,t2.col5 AS MyCol,t2.col4 AS SomeCol FROM Schema1.Table1 t0,Table1 t1,Table2 t2");
291 
292     String selectStr10 = new SelectQuery()
293       .addColumns(_table1_col1, _defTable1_col2)
294       .addCustomJoin(
295           SqlObjectList.create(" CROSS JOIN ")
296           .addObjects(Converter.CUSTOM_TABLE_DEF_TO_OBJ, _table1, _defTable1))
297       .validate().toString();
298     checkResult(selectStr10,
299                 "SELECT t0.col1,t1.col2 FROM Schema1.Table1 t0 CROSS JOIN Table1 t1");
300     
301     String selectStr11 = new SelectQuery()
302       .addAllTableColumns(_table1)
303       .setOffset(0)
304       .validate().toString();
305     checkResult(selectStr11,
306                 "SELECT t0.* FROM Schema1.Table1 t0 OFFSET 0 ROWS");
307 
308     String selectStr12 = new SelectQuery()
309       .addAllTableColumns(_table1)
310       .addOrdering(_table1_col1, OrderObject.Dir.DESCENDING)
311       .setOffset(0)
312       .setFetchNext(25)
313       .validate().toString();
314     checkResult(selectStr12,
315                 "SELECT t0.* FROM Schema1.Table1 t0 ORDER BY t0.col1 DESC OFFSET 0 ROWS FETCH NEXT 25 ROWS ONLY");
316     
317     try {
318       new SelectQuery()
319         .addColumns(_table1_col1, _defTable1_col2, _defTable2_col5)
320         .addFromTable(_defTable1).validate();
321       fail("ValidationException should have been thrown");
322     } catch(ValidationException e) {}
323       
324     try {
325       new SelectQuery()
326         .addColumns(_defTable1_col2)
327         .addFromTable(_defTable1)
328         .addOrderings(_table1_col1)
329         .validate();
330       fail("ValidationException should have been thrown");
331     } catch(ValidationException e) {}
332 
333     try {
334       new SelectQuery()
335         .addColumns(_defTable1_col2)
336         .addFromTable(_defTable1)
337         .addIndexedOrderings(2)
338         .validate();
339       fail("ValidationException should have been thrown");
340     } catch(ValidationException e) {}
341 
342     try {
343       new SelectQuery()
344         .addColumns(_defTable1_col_id, _defTable1_col2)
345         .addFromTable(_defTable1)
346         .addCustomOrderings(1.5d)
347         .validate();
348       fail("ValidationException should have been thrown");
349     } catch(ValidationException e) {}
350 
351     try {
352       DbTable table3 = _schema1.addTable("Table3");
353       
354       new SelectQuery()
355         .addColumns(_table1_col1)
356         .addJoin(SelectQuery.JoinType.INNER, _table1, _defTable1,
357                  Arrays.asList(_table1_col1),
358                  Arrays.asList(_defTable1_col_id))
359         .addJoin(SelectQuery.JoinType.INNER, table3, _defTable2,
360                  Arrays.asList(_defTable1_col_id),
361                  Arrays.asList(_defTable2_col_id))
362         .validate();
363       fail("ValidationException should have been thrown");
364     } catch(ValidationException e) {}
365 
366     try {
367       new SelectQuery()
368         .addAllTableColumns(_table1)
369         .setOffset(-1)
370         .validate();
371       fail("ValidationException should have been thrown");
372     } catch(ValidationException e) {}
373     
374   }
375 
376   public void testSelectNoAlias()
377   {
378     RejoinTable noAliasTable = new RejoinTable(_table1, null);
379     Column col1 = noAliasTable.findColumnByName("col1");
380     
381     SelectQuery selectQuery1 = new SelectQuery()
382       .addColumns(col1, _defTable1_col2, _defTable2_col5);
383 
384     String selectStr1 = selectQuery1.addJoin(
385         SelectQuery.JoinType.INNER,
386         noAliasTable, _defTable1,
387         col1, _defTable1_col2)
388         .addJoins(SelectQuery.JoinType.LEFT_OUTER, _idJoin)
389         .validate().toString();
390       checkResult(selectStr1,
391                   "SELECT col1,t1.col2,t2.col5 FROM Schema1.Table1 INNER JOIN Table1 t1 ON (col1 = t1.col2) LEFT OUTER JOIN Table2 t2 ON (t1.col_id = t2.col_id)");
392 
393       String selectStr2 = selectQuery1
394         .addCondition(BinaryCondition.greaterThan(_defTable2_col4, 42, true))
395         .addCondition(BinaryCondition.equalTo(col1, "foo"))
396         .validate().toString();
397       checkResult(selectStr2,
398                   "SELECT col1,t1.col2,t2.col5 FROM Schema1.Table1 INNER JOIN Table1 t1 ON (col1 = t1.col2) LEFT OUTER JOIN Table2 t2 ON (t1.col_id = t2.col_id) WHERE ((t2.col4 >= 42) AND (col1 = 'foo'))");
399   }
400   
401   public void testCondition()
402   {
403     SelectQuery sq = new SelectQuery().addColumns(_table1_col1);
404 
405     String reallyComplicatedConditionStr = ComboCondition.and(
406       BinaryCondition.lessThan(_table1_col1, "FOO", false),
407       ComboCondition.or(),
408       UnaryCondition.isNotNull(_defTable1_col_id),
409       new ComboCondition(ComboCondition.Op.OR,
410                          new CustomCondition("IM REALLY SNAZZY"),
411                          new NotCondition(
412                            BinaryCondition.like(_defTable2_col5,
413                                                 "BUZ%")),
414                          new BinaryCondition(BinaryCondition.Op.EQUAL_TO,
415                                              new CustomSql("YOU"),
416                                              "ME")),
417       ComboCondition.or(
418         new UnaryCondition(UnaryCondition.Op.IS_NULL,
419                            _table1_col2)),
420       new InCondition(_defTable2_col4,
421                       "this string",
422                       new NumberValueObject(37))
423       .addObject(new NumberValueObject(42)),
424       BinaryCondition.notLike(_table1_col2, "\\_%").setLikeEscapeChar('\\'),
425       UnaryCondition.exists(sq))
426       .toString();
427     checkResult(reallyComplicatedConditionStr,
428                 "((t0.col1 < 'FOO') AND (t1.col_id IS NOT NULL) AND ((IM REALLY SNAZZY) OR (NOT (t2.col5 LIKE 'BUZ%')) OR (YOU = 'ME')) AND (t0.col2 IS NULL) AND (t2.col4 IN ('this string',37,42) ) AND (t0.col2 NOT LIKE '\\_%' ESCAPE '\\') AND (EXISTS (SELECT t0.col1 FROM Schema1.Table1 t0)))");
429 
430     checkResult(new InCondition(_defTable2_col4,
431                                 new NumberValueObject(37)).toString(),
432                 "(t2.col4 IN (37) )");
433 
434     try {
435       BinaryCondition.equalTo(_table1_col2, "\\37").setLikeEscapeChar('\\');
436       fail("IllegalArgumentException should have been thrown");
437     } catch(IllegalArgumentException e) {}
438   }
439 
440   public void testConditionAlterParens()
441   {
442     SelectQuery sq = new SelectQuery().addColumns(_table1_col1);
443 
444     String reallyComplicatedConditionStr = ComboCondition.and(
445       BinaryCondition.lessThan(_table1_col1, "FOO", false),
446       ComboCondition.or(),
447       UnaryCondition.isNotNull(_defTable1_col_id),
448       new ComboCondition(ComboCondition.Op.OR,
449                          new CustomCondition("IM REALLY SNAZZY")
450                            .setDisableParens(true),
451                          new NotCondition(
452                            BinaryCondition.like(_defTable2_col5,
453                                                 "BUZ%")),
454                          new BinaryCondition(BinaryCondition.Op.EQUAL_TO,
455                                              new CustomSql("YOU"),
456                                              "ME"))
457         .setDisableParens(true),
458       ComboCondition.or(
459         new UnaryCondition(UnaryCondition.Op.IS_NULL,
460                            _table1_col2)),
461       new InCondition(_defTable2_col4,
462                       "this string",
463                       new NumberValueObject(37))
464       .addObject(new NumberValueObject(42))
465       .setDisableParens(true),
466       BinaryCondition.notLike(_table1_col2, "\\_%").setLikeEscapeChar('\\')
467         .setDisableParens(true),
468       UnaryCondition.exists(sq))
469       .toString();
470     checkResult(reallyComplicatedConditionStr,
471                 "((t0.col1 < 'FOO') AND (t1.col_id IS NOT NULL) AND IM REALLY SNAZZY OR (NOT (t2.col5 LIKE 'BUZ%')) OR (YOU = 'ME') AND (t0.col2 IS NULL) AND t2.col4 IN ('this string',37,42) AND t0.col2 NOT LIKE '\\_%' ESCAPE '\\' AND (EXISTS (SELECT t0.col1 FROM Schema1.Table1 t0)))");
472   }
473   
474   public void testExpression()
475   {
476     Expression expr = ComboExpression.add(
477         37, _defTable2_col5,
478         new NegateExpression(
479             ComboExpression.multiply(_table1_col1, 4.7f)),
480         ComboExpression.subtract(),
481         new NegateExpression((Object)Expression.EMPTY),
482         "PI", new CustomSql("8 - 3"));
483     String reallyComplicatedExpression = expr.toString();
484     checkResult(reallyComplicatedExpression,
485                 "(37 + t2.col5 + (- (t0.col1 * 4.7)) + 'PI' + (8 - 3))");
486 
487     String exprQuery = new SelectQuery()
488       .addCustomColumns(expr, _table1_col2)
489       .validate().toString();
490     checkResult(exprQuery, "SELECT (37 + t2.col5 + (- (t0.col1 * 4.7)) + 'PI' + (8 - 3)),t0.col2 FROM Table2 t2,Schema1.Table1 t0");
491     
492     String concatExpression = ComboExpression.concatenate(
493         "The answer is ", ComboExpression.add(40, 2), ".")
494       .toString();
495     checkResult(concatExpression,
496                 "('The answer is ' || (40 + 2) || '.')");
497   }
498 
499   public void testFunction()
500   {
501     // add some functions to play with
502     DbFunctionPackage funcPack1 = _schema1.addFunctionPackage("fpkg");
503     DbFunction func1 = funcPack1.addFunction("func1");
504     DbFunctionPackage funcPack2 = _schema1.addFunctionPackage((String)null);
505     DbFunction func2 = funcPack2.addFunction("Func2");
506     DbFunctionPackage funcPack3 = _defSchema.addDefaultFunctionPackage();
507     DbFunction func3 = funcPack3.addFunction("func3");
508     
509     String funcStr1 = new FunctionCall(func1).toString();
510     checkResult(funcStr1, "Schema1.fpkg.func1()");
511 
512     String funcStr2 = new FunctionCall(func2)
513       .addColumnParams(_table1_col1)
514       .toString();
515     checkResult(funcStr2, "Schema1.Func2(t0.col1)");
516     
517     String funcStr3 = new FunctionCall(func2)
518       .setIsDistinct(true)
519       .addColumnParams(_table1_col1)
520       .toString();
521     checkResult(funcStr3, "Schema1.Func2(DISTINCT t0.col1)");
522     
523     String funcStr4 = new FunctionCall(func3)
524       .addColumnParams(_table1_col1)
525       .addCustomParams("42")
526       .toString();
527     checkResult(funcStr4, "func3(t0.col1,'42')");
528     
529     String funcStr5 = new FunctionCall(func3)
530       .addCustomParams(new String("HAPPY"), _table1_col1)
531       .toString();
532     checkResult(funcStr5, "func3('HAPPY',t0.col1)");
533 
534     String funcStr6 = FunctionCall.sum()
535       .addColumnParams(_table1_col3)
536       .toString();
537     checkResult(funcStr6, "SUM(t0.col3)");
538 
539     String funcStr7 = FunctionCall.countAll()
540       .toString();
541     checkResult(funcStr7, "COUNT(*)");
542 
543     String funcStr8 = new FunctionCall(func3)
544       .addColumnParams(_table1_col1)
545       .addNumericValueParam(42)
546       .toString();
547     checkResult(funcStr8, "func3(t0.col1,42)");
548     
549   }
550 
551   public void testCustom()
552   {
553     String customStr1 = new SelectQuery()
554       .addColumns(_defTable1_col_id)
555       .addFromTable(_defTable1)
556       .addCustomFromTable(new CustomSql("otherTable"))
557       .addCustomColumns(new CustomSql("fooCol"), new CustomSql("BazzCol"))
558       .addCondition(ComboCondition.and(
559                       new BinaryCondition(BinaryCondition.Op.LESS_THAN,
560                                           new CustomSql("fooCol"),
561                                           new ValueObject(37)),
562                       new CustomCondition("bazzCol IS FUNKY")))
563       .addCondition(new NotCondition((Object)Condition.EMPTY))
564       .validate().toString();
565     checkResult(customStr1,
566                 "SELECT t1.col_id,fooCol,BazzCol FROM Table1 t1, otherTable WHERE ((fooCol < '37') AND (bazzCol IS FUNKY))");
567   }
568 
569   public void testCaseStatement() {
570     String caseClause1 = new SimpleCaseStatement(_table1_col1)
571       .addNumericWhen(1, "one")
572       .addNumericWhen(2, "two")
573       .addElse("three").validate().toString();
574 
575     checkResult(caseClause1,
576                 "(CASE t0.col1 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'three' END)");
577 
578     String caseClause2 = new CaseStatement()
579       .addWhen(BinaryCondition.equalTo(_table1_col2, "13"), _table1_col3)
580       .addWhen(BinaryCondition.equalTo(_table1_col2, "14"), "14")
581       .addElseNull().validate().toString();
582     
583     checkResult(caseClause2,
584                 "(CASE WHEN (t0.col2 = '13') THEN t0.col3 WHEN (t0.col2 = '14') THEN '14' ELSE NULL END)");
585 
586     String caseClause3 = new SimpleCaseStatement(_table1_col2).validate()
587       .toString();
588     checkResult(caseClause3, "");
589 
590     try {
591       new SimpleCaseStatement(_table1_col1)
592         .addNumericWhen(1, "one")
593         .addNumericWhen(2, "two")
594         .addElse("three")
595         .addElseNull().validate();
596       fail("ValidationException should have been thrown");
597     } catch(ValidationException e) {}
598 
599     
600     SimpleCaseStatement invalidCase = new SimpleCaseStatement(_table1_col1)
601       .addNumericWhen(1, "one")
602       .addElse("three")
603       .addNumericWhen(2, "two");
604     
605     try {
606       invalidCase.validate();
607       fail("ValidationException should have been thrown");
608     } catch(ValidationException e) {}
609     
610     try {
611       new SelectQuery()
612         .addCustomColumns(invalidCase)
613         .validate();
614       fail("ValidationException should have been thrown");
615     } catch(ValidationException e) {}
616     
617   }
618 
619   public void testDelete()
620   {
621     String deleteQuery1 = new DeleteQuery(_table1)
622       .addCondition(BinaryCondition.equalTo(_table1_col2, "13"))
623       .validate().toString();
624 
625     checkResult(deleteQuery1,
626                 "DELETE FROM Schema1.Table1 WHERE (col2 = '13')");
627     
628   }
629 
630   public void testUpdate()
631   {
632     String updateQuery1 = new UpdateQuery(_table1)
633       .addSetClause(_table1_col1, 47)
634       .addSetClause(_table1_col3, "foo")
635       .addCondition(BinaryCondition.equalTo(_table1_col2, "13"))
636       .validate().toString();
637 
638     checkResult(updateQuery1,
639                 "UPDATE Schema1.Table1 SET col1 = 47,col3 = 'foo' WHERE (col2 = '13')");
640     
641   }
642 
643   public void testUnion()
644   {
645     SelectQuery q1 = new SelectQuery()
646       .addColumns(_table1_col1, _table1_col2, _table1_col3);
647     SelectQuery q2 = new SelectQuery()
648       .addColumns(_defTable2_col_id, _defTable2_col4, _defTable2_col5);
649 
650     UnionQuery unionQuery = UnionQuery.unionAll(q1, q2);
651 
652     String unionQuery1 = unionQuery.validate().toString();
653     checkResult(unionQuery1,
654                 "SELECT t0.col1,t0.col2,t0.col3 FROM Schema1.Table1 t0 UNION ALL SELECT t2.col_id,t2.col4,t2.col5 FROM Table2 t2");
655 
656     q1.addColumns(_defTable1_col3);
657     try {
658       unionQuery.validate();
659       fail("ValidationException should have been thrown");
660     } catch(ValidationException e) {}
661 
662     q1 = new SelectQuery()
663       .addColumns(_table1_col1, _table1_col2, _table1_col3);
664     q2 = new SelectQuery()
665       .addColumns(_defTable2_col_id, _defTable2_col4, _defTable2_col5);
666 
667     unionQuery = UnionQuery.unionAll(q1, q2)
668       .addIndexedOrderings(1)
669       .addOrderings(_table1_col1);
670 
671     String unionQuery2 = unionQuery.validate().toString();
672     checkResult(unionQuery2,
673                 "SELECT t0.col1,t0.col2,t0.col3 FROM Schema1.Table1 t0 UNION ALL SELECT t2.col_id,t2.col4,t2.col5 FROM Table2 t2 ORDER BY 1,col1");
674 
675     q1.addOrderings(_table1_col2);
676     try {
677       unionQuery.validate();
678       fail("ValidationException should have been thrown");
679     } catch(ValidationException e) {}
680 
681     q1 = new SelectQuery()
682       .addAllTableColumns(_table1);
683     q2 = new SelectQuery()
684       .addFromTable(_defTable2)
685       .addAllColumns();
686 
687     unionQuery = UnionQuery.union(q1, q2)
688       .addIndexedOrdering(1, OrderObject.Dir.DESCENDING)
689       .addCustomOrderings(
690           new OrderObject(OrderObject.Dir.ASCENDING, _table1_col1)
691           .setNullOrder(OrderObject.NullOrder.FIRST));
692     
693     String unionQuery3 = unionQuery.validate().toString();
694     checkResult(unionQuery3,
695                 "SELECT t0.* FROM Schema1.Table1 t0 UNION SELECT * FROM Table2 t2 ORDER BY 1 DESC,col1 ASC NULLS FIRST");
696   }
697 
698   public void testSetOperationQueries()
699   {
700     SelectQuery q1 = new SelectQuery()
701       .addAllTableColumns(_table1);
702     SelectQuery q2 = new SelectQuery()
703       .addFromTable(_defTable2)
704       .addAllColumns();
705     SelectQuery q3 = new SelectQuery()
706       .addFromTable(_defTable1)
707       .addAllColumns();
708     
709     SetOperationQuery<?> setOpQuery = SetOperationQuery.except(q1, q2)
710       .addQueries(SetOperationQuery.Type.INTERSECT_ALL, q3);
711 
712     String setOpQueryStr = setOpQuery.validate().toString();
713     checkResult(setOpQueryStr,
714                 "SELECT t0.* FROM Schema1.Table1 t0 EXCEPT SELECT * FROM Table2 t2 INTERSECT ALL SELECT * FROM Table1 t1");
715 
716   }
717 
718   public void testSqlContext()
719   {
720     SqlContext context = new SqlContext();
721     Condition cond = ComboCondition.and(
722         BinaryCondition.equalTo(_defTable1_col3, "foo"),
723         BinaryCondition.lessThan(_table1_col1, 13, true));
724 
725     String condStr1 = cond.toString(32, context);
726     checkResult(condStr1,
727                 "((t1.col3 = 'foo') AND (t0.col1 <= 13))");
728 
729     context.setUseTableAliases(false);
730     String condStr2 = cond.toString(32, context);
731     checkResult(condStr2,
732                 "((col3 = 'foo') AND (col1 <= 13))");
733     
734   }
735 
736   public void testRejoinTable()
737   {
738     RejoinTable rejoinTable1 = _table1.rejoin("t5");
739     assertSame(_table1, rejoinTable1.getOriginalTable());
740     assertSame(_table1_col1,
741                rejoinTable1.getColumns().get(0).getOriginalColumn());
742     assertSame(_table1.getConstraints(), rejoinTable1.getConstraints());
743     assertNull(rejoinTable1.findColumnByName("bogus"));
744 
745     RejoinTable.RejoinColumn rejoinCol2 = rejoinTable1.findColumnByName("col2");
746     assertSame(_table1_col2, rejoinCol2.getOriginalColumn());
747     assertSame(rejoinCol2, rejoinTable1.findColumn(_table1_col2));
748     assertSame(_table1_col2.getTypeNameSQL(), rejoinCol2.getTypeNameSQL());
749     assertSame(_table1_col2.getTypeLength(), rejoinCol2.getTypeLength());
750     assertSame(_table1_col2.getConstraints(), rejoinCol2.getConstraints());
751     
752     String rejoinQuery = (new SelectQuery())
753       .addFromTable(_table1)
754       .addColumns(_table1_col1, _table1_col2)
755       .addFromTable(rejoinTable1)
756       .addColumns(rejoinTable1.getColumns().get(0),
757                   rejoinTable1.findColumn(_table1_col2))
758       .validate().toString();
759 
760     checkResult(rejoinQuery,
761                 "SELECT t0.col1,t0.col2,t5.col1,t5.col2 FROM Schema1.Table1 t0, Schema1.Table1 t5");
762   }
763   
764   public void testJdbcEscape()
765   {
766     String escapeStr1 = new InsertQuery(_table1)
767       .addColumns(new DbColumn[]{_table1_col1, _table1_col3},
768                   new Object[]{13, JdbcScalarFunction.NOW})
769       .validate().toString();
770     checkResult(escapeStr1,
771                 "INSERT INTO Schema1.Table1 (col1,col3) VALUES (13,{fn NOW()})");
772     
773     Date d = new Date(1204909500692L);
774     String dateStr = JdbcEscape.date(d).toString();
775     String date = new SimpleDateFormat("yyyy-MM-dd").format(d);
776     checkResult(dateStr, "{d '"+date+"'}");
777     String timeStr = JdbcEscape.time(d).toString();
778     String time = new SimpleDateFormat("HH:mm:ss").format(d);
779     checkResult(timeStr, "{t '"+time+"'}");
780     String timestampStr = JdbcEscape.timestamp(d).toString();
781     String timestamp = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS").format(d);
782     checkResult(timestampStr, "{ts '"+timestamp+"'}");
783   }
784   
785   public void testGrantRevoke()
786   {
787     String grantStr1 = new GrantQuery()
788       .setTarget(GrantQuery.targetTable(_table1))
789       .addPrivileges(GrantQuery.privilegeInsert(_table1_col1),
790                      GrantQuery.privilegeUsage())
791       .addGrantees("bob", "Mark")
792       .validate().toString();
793     checkResult(grantStr1, "GRANT INSERT(col1),USAGE ON TABLE Schema1.Table1 TO bob,Mark");
794 
795     String revokeStr1 = new RevokeQuery()
796       .setTarget(GrantQuery.targetTable(_table1))
797       .addPrivileges(GrantQuery.privilegeInsert(_table1_col1),
798                      GrantQuery.privilegeUsage())
799       .addCustomGrantees(RevokeQuery.PUBLIC_GRANTEE)
800       .validate().toString();
801     checkResult(revokeStr1, "REVOKE INSERT(col1),USAGE ON TABLE Schema1.Table1 FROM PUBLIC");
802 
803     try {
804       new GrantQuery()
805         .setTarget(GrantQuery.targetTable(_table1))
806         .addPrivileges(GrantQuery.privilegeInsert(_defTable1_col3))
807         .addGrantees("bob")
808         .validate();
809       fail("ValidationException should have been thrown");
810     } catch(ValidationException e) {}      
811   }
812 
813   public void testSubquery()
814   {
815     String queryStr1 =
816       new SelectQuery()
817       .addColumns(_table1_col1, _table1_col2)
818       .addCondition(new InCondition(
819                         _table1_col1, new Subquery(
820                             new SelectQuery()
821                             .addColumns(_defTable1_col3)
822                             .validate())))
823       .validate().toString();
824     checkResult(queryStr1, "SELECT t0.col1,t0.col2 FROM Schema1.Table1 t0 WHERE (t0.col1 IN (SELECT t1.col3 FROM Table1 t1) )");
825 
826     SelectQuery innerSelect = new SelectQuery()
827       .addCustomColumns(_defTable2_col4)
828       .addCondition(BinaryCondition.equalTo(_table1_col1, _defTable2_col_id));
829     innerSelect.validate();
830     String queryStr2 = innerSelect.toString();
831     checkResult(queryStr2, "SELECT t2.col4 FROM Table2 t2,Schema1.Table1 t0 WHERE (t0.col1 = t2.col_id)");    
832     SelectQuery outerSelect = new SelectQuery()
833       .addCustomColumns(_table1_col1, _table1_col2)
834       .addJoin(SelectQuery.JoinType.INNER, _table1, _defTable1,
835                BinaryCondition.equalTo(_table1_col1, _defTable1_col_id))
836       .addCondition(new InCondition(_table1_col1, new Subquery(innerSelect)));
837     outerSelect.validate();
838     String queryStr3 = outerSelect.toString();
839     checkResult(queryStr3, "SELECT t0.col1,t0.col2 FROM Schema1.Table1 t0 INNER JOIN Table1 t1 ON (t0.col1 = t1.col_id) WHERE (t0.col1 IN (SELECT t2.col4 FROM Table2 t2 WHERE (t0.col1 = t2.col_id)) )");
840 
841     innerSelect.addCustomColumns()
842       .addJoin(SelectQuery.JoinType.INNER, _table1, _defTable1, _table1_col1,
843                _defTable1_col_id);
844     try {
845       innerSelect.validate();
846       fail("ValidationException should have been thrown");
847     } catch(ValidationException e) {}      
848       
849     try {
850       outerSelect.validate();
851       fail("ValidationException should have been thrown");
852     } catch(ValidationException e) {}      
853 
854     innerSelect = new SelectQuery()
855       .addCustomColumns(_defTable2_col4)
856       .addJoin(SelectQuery.JoinType.INNER, _table1, _defTable2, _table1_col1,
857                _defTable2_col_id)
858       .addCondition(BinaryCondition.equalTo(_table1_col1, _defTable1_col_id));
859     String queryStr4 = innerSelect.toString();
860     checkResult(queryStr4, "SELECT t2.col4 FROM Schema1.Table1 t0 INNER JOIN Table2 t2 ON (t0.col1 = t2.col_id) WHERE (t0.col1 = t1.col_id)");
861 
862     try {
863       innerSelect.validate();
864       fail("ValidationException should have been thrown");
865     } catch(ValidationException e) {}      
866 
867     outerSelect = new SelectQuery()
868       .addCustomColumns(_table1_col1, _table1_col2)
869       .addJoin(SelectQuery.JoinType.INNER, _table1, _defTable1, _table1_col1,
870                _defTable1_col_id)
871       .addCondition(new InCondition(_table1_col1, new Subquery(innerSelect)));
872     outerSelect.validate();
873     String queryStr5 = outerSelect.toString();
874     checkResult(queryStr5, "SELECT t0.col1,t0.col2 FROM Schema1.Table1 t0 INNER JOIN Table1 t1 ON (t0.col1 = t1.col_id) WHERE (t0.col1 IN (SELECT t2.col4 FROM Schema1.Table1 t0 INNER JOIN Table2 t2 ON (t0.col1 = t2.col_id) WHERE (t0.col1 = t1.col_id)) )");
875   }
876 
877   public void testAlterTable()
878   {
879     @SuppressWarnings("deprecation")
880     String queryStr1 =
881       new AlterTableQuery(_table1)
882       .setAction(new AlterTableQuery.AddUniqueConstraintAction()
883                  .addColumns(_table1_col2))
884       .validate().toString();
885     checkResult(queryStr1, "ALTER TABLE Schema1.Table1 ADD UNIQUE (col2)");
886 
887     @SuppressWarnings("deprecation")
888     String queryStr2 =
889       new AlterTableQuery(_defTable1)
890       .setAction(new AlterTableQuery.AddPrimaryConstraintAction()
891                  .addColumns(_defTable1_col_id))
892       .validate().toString();
893     checkResult(queryStr2, "ALTER TABLE Table1 ADD PRIMARY KEY (col_id)");
894 
895     @SuppressWarnings("deprecation")
896     String queryStr3 =
897       new AlterTableQuery(_defTable1)
898       .setAction(new AlterTableQuery.AddForeignConstraintAction(_defTable2)
899                  .addPrimaryKeyReference(_defTable1_col_id))
900       .validate().toString();
901     checkResult(queryStr3, 
902                 "ALTER TABLE Table1 ADD FOREIGN KEY (col_id) REFERENCES Table2");
903 
904     @SuppressWarnings("deprecation")
905     String queryStr4 =
906       new AlterTableQuery(_defTable1)
907       .setAction(new AlterTableQuery.AddForeignConstraintAction(_defTable2)
908                  .addReference(_defTable1_col_id, _defTable2_col4)
909                  .addReference(_defTable1_col2, _defTable2_col5))
910       .validate().toString();
911     checkResult(queryStr4, 
912                 "ALTER TABLE Table1 ADD FOREIGN KEY (col_id,col2) " +
913                 "REFERENCES Table2 (col4,col5)");
914 
915     String queryStr5 =
916       new AlterTableQuery(_defTable2)
917       .setAddConstraint(_defTable2.getConstraints().get(0))
918       .validate().toString();
919     checkResult(queryStr5, 
920                 "ALTER TABLE Table2 ADD CONSTRAINT t2_fk FOREIGN KEY (col4,col5) REFERENCES Table1 (col2,col3)");
921 
922     DbColumn toAdd = _defTable1.addColumn("col5", Types.VARCHAR, 255);
923     toAdd.notNull();
924     String queryStr6 =
925       new AlterTableQuery(_defTable1)
926       .setAction(new AlterTableQuery.AddColumnAction(toAdd)
927                  .addConstraint(new ConstraintClause(ConstraintClause.Type.UNIQUE, null)))
928       .validate().toString();
929     checkResult(queryStr6,
930                 "ALTER TABLE Table1 ADD col5 VARCHAR(255) NOT NULL UNIQUE");
931 
932     toAdd.setDefaultValue("someValue");
933     String queryStr7 =
934       new AlterTableQuery(_defTable1)
935       .setAddColumn(toAdd)
936       .validate().toString();
937     checkResult(queryStr7,
938                 "ALTER TABLE Table1 ADD col5 VARCHAR(255) DEFAULT 'someValue' NOT NULL");
939 
940     String queryStr8 =
941       new AlterTableQuery(_defTable1)
942       .setAction(new AlterTableQuery.AddColumnAction(toAdd)
943                  .setTypeName("NVARCHAR"))
944       .validate().toString();
945     checkResult(queryStr8,
946                 "ALTER TABLE Table1 ADD col5 NVARCHAR(255) DEFAULT 'someValue' NOT NULL");
947 
948   }
949 
950   public void testComment()
951   {
952     String queryStr1 = new SelectQuery()
953       .addCustomColumns(_table1_col1, _table1_col2, new Comment("foo bar"))
954                         .validate().toString();
955     checkResult(queryStr1, "SELECT t0.col1,t0.col2, -- foo bar\n FROM Schema1.Table1 t0");
956 
957     String queryStr2 = new SelectQuery()
958       .addCustomColumns(_table1_col1, _table1_col2)
959                         .validate().toString() +
960       new Comment("My coolest query ever");
961     checkResult(queryStr2, "SELECT t0.col1,t0.col2 FROM Schema1.Table1 t0 -- My coolest query ever\n");
962     
963   }
964 
965   public void testEscapeLiteral()
966   {
967     String orig = "/this%is_a ' literal/pattern";
968 
969     assertEquals("//this/%is/_a ' literal//pattern",
970                  BinaryCondition.escapeLikeLiteral(orig, '/'));
971     assertEquals("/this\\%is\\_a ' literal/pattern",
972                  BinaryCondition.escapeLikeLiteral(orig, '\\'));
973   }
974 
975   public void testValidationException() {
976     SelectQuery select = new SelectQuery()
977       .addCustomColumns(_defTable1_col_id)
978       .addFromTable(_table1);
979 
980     ValidationException ve = null;
981     try {
982       select.validate();
983     } catch(ValidationException tmp) {
984       ve = tmp;
985     }
986 
987     assertNotNull(ve);
988     assertNotNull(ve.getFailedVerifiable());
989     assertSame(select, ve.getFailedVerifiable().get1());
990 
991     String msg = ve.getMessage();
992     checkResult(msg, "Columns used for unreferenced tables [Failed clause: SELECT t1.col_id FROM Schema1.Table1 t0]");
993 
994     
995     select.addCustomColumns(new SqlObject() {
996         @Override
997         public void appendTo(AppendableExt app) throws IOException {
998           throw new NullPointerException("BOO");
999         }
1000         @Override
1001         protected void collectSchemaObjects(ValidationContext vContext) {}
1002       });
1003     try {
1004       select.validate();
1005     } catch(ValidationException tmp) {
1006       ve = tmp;
1007     }
1008 
1009     assertNotNull(ve);
1010     
1011     msg = ve.getMessage();
1012     assertTrue(msg.matches("Columns used for unreferenced tables \\[Verifiable: com.healthmarketscience.sqlbuilder.SelectQuery@[0-9a-f]+\\]"));
1013 
1014   }
1015 
1016   public void testCreateView()
1017   {
1018     SelectQuery query1 = new SelectQuery()
1019       .addColumns(_defTable1_col_id, _defTable1_col2)
1020       .addCondition(UnaryCondition.isNotNull(_defTable1_col_id));
1021     SelectQuery query2 = new SelectQuery().addAllTableColumns(_defTable2);
1022     
1023     String createStr1 = new CreateViewQuery(_table1)
1024       .setSelectQuery(query1)
1025       .validate().toString();
1026     checkResult(createStr1,
1027                 "CREATE VIEW Schema1.Table1 AS SELECT t1.col_id,t1.col2 FROM Table1 t1 WHERE (t1.col_id IS NOT NULL)");
1028 
1029     
1030     String createStr2 = new CreateViewQuery(_table1)
1031       .addColumns(_table1_col1, _table1_col3)
1032       .setSelectQuery(query1)
1033       .setWithCheckOption(true)
1034       .validate().toString();
1035     checkResult(createStr2,
1036                 "CREATE VIEW Schema1.Table1 (col1,col3) AS SELECT t1.col_id,t1.col2 FROM Table1 t1 WHERE (t1.col_id IS NOT NULL) WITH CHECK OPTION");
1037 
1038     String createStr3 = new CreateViewQuery(_table1)
1039       .addCustomColumns(_table1_col1, _table1_col3)
1040       .setSelectQuery(query2)
1041                     .validate().toString();
1042     checkResult(createStr3,
1043                 "CREATE VIEW Schema1.Table1 (col1,col3) AS SELECT t2.* FROM Table2 t2");
1044 
1045     CreateViewQuery viewQuery = new CreateViewQuery(_table1)
1046       .setSelectQuery(query2);
1047     
1048     String createStr4 = viewQuery.validate().toString();
1049     checkResult(createStr4,
1050                 "CREATE VIEW Schema1.Table1 AS SELECT t2.* FROM Table2 t2");
1051 
1052     String dropStr1 = viewQuery.getDropQuery().validate().toString();
1053     checkResult(dropStr1,
1054                 "DROP VIEW Schema1.Table1");
1055     
1056     try {
1057       new CreateViewQuery(_table1).validate();
1058       fail("ValidationException should have been thrown");
1059     } catch(ValidationException e) {}
1060     
1061     try {
1062       new CreateViewQuery(_table1)
1063         .addColumns(_table1_col1)
1064         .setSelectQuery(query1)
1065         .validate();
1066       fail("ValidationException should have been thrown");
1067     } catch(ValidationException e) {}
1068     
1069     try {
1070       new CreateViewQuery(_table1)
1071         .addCustomColumns(SqlObject.ALL_SYMBOL)
1072         .setSelectQuery(query2)
1073         .validate();
1074       fail("ValidationException should have been thrown");
1075     } catch(ValidationException e) {}
1076 
1077   }
1078 
1079   public void testCrossSchemaFK() throws Exception
1080   {
1081     String createStr = new CreateTableQuery(_defTable3, true)
1082       .validate().toString();
1083 
1084     checkResult(createStr, "CREATE TABLE DefTable3 (col_id NUMBER CONSTRAINT t2_id_fk REFERENCES Schema1.Table1 (col2))");
1085   }
1086 
1087   public void testCustomPrefix() throws Exception
1088   {
1089     DbTable table = new DbSpec("pre_").addDefaultSchema().addTable("NewTable");
1090     table.addColumn("col1");
1091     table.addColumn("col2");
1092 
1093     String sqlStr = new SelectQuery().addAllTableColumns(table)
1094       .validate().toString();
1095     checkResult(sqlStr, "SELECT pre_0.* FROM NewTable pre_0");
1096   }
1097 
1098   public void testBetweenCondition()
1099   {
1100     String conditionStr = ComboCondition.and(
1101       BinaryCondition.lessThan(_table1_col1, "FOO", false),
1102       ComboCondition.or(),
1103       new BetweenCondition(_defTable2_col4,
1104                       "this string",
1105                       new NumberValueObject(37)))
1106       .toString();
1107     checkResult(conditionStr,
1108                 "((t0.col1 < 'FOO') AND (t2.col4 BETWEEN 'this string' AND 37))");
1109   }
1110 
1111   public void testExtractExpression()
1112   {
1113     String exprStr = BinaryCondition.equalTo(
1114         "2016",
1115         new ExtractExpression(ExtractExpression.DatePart.YEAR, "2016-01-01"))
1116       .toString();
1117     checkResult(exprStr, "('2016' = EXTRACT(YEAR FROM '2016-01-01'))");
1118   }
1119 
1120   public void testWindowFunctions()
1121   {
1122     String exprStr = FunctionCall.avg()
1123       .setWindow(new WindowDefinitionClause())
1124       .toString();
1125     
1126     checkResult(exprStr, "AVG() OVER ()");
1127 
1128     exprStr = FunctionCall.avg()
1129       .setWindow(new WindowDefinitionClause()
1130                  .addPartitionColumns(_defTable1_col_id))
1131       .toString();
1132     
1133     checkResult(exprStr, "AVG() OVER (PARTITION BY t1.col_id)");
1134 
1135     exprStr = FunctionCall.avg()
1136       .setWindow(new WindowDefinitionClause()
1137                  .addPartitionColumns(_defTable1_col_id)
1138                  .addOrderings(_defTable1_col2))
1139       .toString();
1140     
1141     checkResult(exprStr, "AVG() OVER (PARTITION BY t1.col_id ORDER BY t1.col2)");
1142 
1143     exprStr = FunctionCall.avg()
1144       .setWindow(new WindowDefinitionClause()
1145                  .addPartitionColumns(_defTable1_col_id)
1146                  .addOrderings(_defTable1_col2)
1147                  .setFrame(
1148                      WindowDefinitionClause.FrameUnits.ROWS,
1149                      WindowDefinitionClause.FrameBound.CURRENT_ROW))
1150       .toString();
1151     
1152     checkResult(exprStr, "AVG() OVER (PARTITION BY t1.col_id ORDER BY t1.col2 ROWS CURRENT ROW)");
1153 
1154 
1155     exprStr = FunctionCall.avg()
1156       .setWindow(new WindowDefinitionClause()
1157                  .addPartitionColumns(_defTable1_col_id)
1158                  .addOrderings(_defTable1_col2)
1159                  .setFrameBetween(
1160                      WindowDefinitionClause.FrameUnits.ROWS,
1161                      WindowDefinitionClause.FrameBound.UNBOUNDED_PRECEDING,
1162                      WindowDefinitionClause.FrameBound.boundedFollowing(5)))
1163       .toString();
1164     
1165     checkResult(exprStr, "AVG() OVER (PARTITION BY t1.col_id ORDER BY t1.col2 ROWS BETWEEN UNBOUNDED PRECEDING AND 5 FOLLOWING)");
1166 
1167     String queryStr = new SelectQuery()
1168       .addColumns(_table1_col1, _table1_col2) 
1169       .addAliasedColumn(FunctionCall.avg().setWindowByName("w"), "average")
1170       .addWindowDefinition(
1171           "w", new WindowDefinitionClause()
1172           .addPartitionColumns(_defTable1_col_id)
1173           .addOrderings(_defTable1_col2)
1174           .setFrameBetween(
1175               WindowDefinitionClause.FrameUnits.ROWS,
1176               WindowDefinitionClause.FrameBound.UNBOUNDED_PRECEDING,
1177               WindowDefinitionClause.FrameBound.boundedFollowing(5),
1178               WindowDefinitionClause.FrameExclusion.CURRENT_ROW))
1179       .validate()
1180       .toString();
1181 
1182     checkResult(queryStr, "SELECT t0.col1,t0.col2,AVG() OVER w AS average FROM Schema1.Table1 t0,Table1 t1 WINDOW w AS (PARTITION BY t1.col_id ORDER BY t1.col2 ROWS BETWEEN UNBOUNDED PRECEDING AND 5 FOLLOWING EXCLUDE CURRENT ROW)");
1183   }
1184 }