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       .addCustomOrdering(_table1_col1, OrderObject.Dir.ASCENDING);
690     
691     String unionQuery3 = unionQuery.validate().toString();
692     checkResult(unionQuery3,
693                 "SELECT t0.* FROM Schema1.Table1 t0 UNION SELECT * FROM Table2 t2 ORDER BY 1 DESC,col1 ASC");
694   }
695 
696   public void testSetOperationQueries()
697   {
698     SelectQuery q1 = new SelectQuery()
699       .addAllTableColumns(_table1);
700     SelectQuery q2 = new SelectQuery()
701       .addFromTable(_defTable2)
702       .addAllColumns();
703     SelectQuery q3 = new SelectQuery()
704       .addFromTable(_defTable1)
705       .addAllColumns();
706     
707     SetOperationQuery<?> setOpQuery = SetOperationQuery.except(q1, q2)
708       .addQueries(SetOperationQuery.Type.INTERSECT_ALL, q3);
709 
710     String setOpQueryStr = setOpQuery.validate().toString();
711     checkResult(setOpQueryStr,
712                 "SELECT t0.* FROM Schema1.Table1 t0 EXCEPT SELECT * FROM Table2 t2 INTERSECT ALL SELECT * FROM Table1 t1");
713 
714   }
715 
716   public void testSqlContext()
717   {
718     SqlContext context = new SqlContext();
719     Condition cond = ComboCondition.and(
720         BinaryCondition.equalTo(_defTable1_col3, "foo"),
721         BinaryCondition.lessThan(_table1_col1, 13, true));
722 
723     String condStr1 = cond.toString(32, context);
724     checkResult(condStr1,
725                 "((t1.col3 = 'foo') AND (t0.col1 <= 13))");
726 
727     context.setUseTableAliases(false);
728     String condStr2 = cond.toString(32, context);
729     checkResult(condStr2,
730                 "((col3 = 'foo') AND (col1 <= 13))");
731     
732   }
733 
734   public void testRejoinTable()
735   {
736     RejoinTable rejoinTable1 = _table1.rejoin("t5");
737     assertSame(_table1, rejoinTable1.getOriginalTable());
738     assertSame(_table1_col1,
739                rejoinTable1.getColumns().get(0).getOriginalColumn());
740     assertSame(_table1.getConstraints(), rejoinTable1.getConstraints());
741     assertNull(rejoinTable1.findColumnByName("bogus"));
742 
743     RejoinTable.RejoinColumn rejoinCol2 = rejoinTable1.findColumnByName("col2");
744     assertSame(_table1_col2, rejoinCol2.getOriginalColumn());
745     assertSame(rejoinCol2, rejoinTable1.findColumn(_table1_col2));
746     assertSame(_table1_col2.getTypeNameSQL(), rejoinCol2.getTypeNameSQL());
747     assertSame(_table1_col2.getTypeLength(), rejoinCol2.getTypeLength());
748     assertSame(_table1_col2.getConstraints(), rejoinCol2.getConstraints());
749     
750     String rejoinQuery = (new SelectQuery())
751       .addFromTable(_table1)
752       .addColumns(_table1_col1, _table1_col2)
753       .addFromTable(rejoinTable1)
754       .addColumns(rejoinTable1.getColumns().get(0),
755                   rejoinTable1.findColumn(_table1_col2))
756       .validate().toString();
757 
758     checkResult(rejoinQuery,
759                 "SELECT t0.col1,t0.col2,t5.col1,t5.col2 FROM Schema1.Table1 t0, Schema1.Table1 t5");
760   }
761   
762   public void testJdbcEscape()
763   {
764     String escapeStr1 = new InsertQuery(_table1)
765       .addColumns(new DbColumn[]{_table1_col1, _table1_col3},
766                   new Object[]{13, JdbcScalarFunction.NOW})
767       .validate().toString();
768     checkResult(escapeStr1,
769                 "INSERT INTO Schema1.Table1 (col1,col3) VALUES (13,{fn NOW()})");
770     
771     Date d = new Date(1204909500692L);
772     String dateStr = JdbcEscape.date(d).toString();
773     String date = new SimpleDateFormat("yyyy-MM-dd").format(d);
774     checkResult(dateStr, "{d '"+date+"'}");
775     String timeStr = JdbcEscape.time(d).toString();
776     String time = new SimpleDateFormat("HH:mm:ss").format(d);
777     checkResult(timeStr, "{t '"+time+"'}");
778     String timestampStr = JdbcEscape.timestamp(d).toString();
779     String timestamp = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS").format(d);
780     checkResult(timestampStr, "{ts '"+timestamp+"'}");
781   }
782   
783   public void testGrantRevoke()
784   {
785     String grantStr1 = new GrantQuery()
786       .setTarget(GrantQuery.targetTable(_table1))
787       .addPrivileges(GrantQuery.privilegeInsert(_table1_col1),
788                      GrantQuery.privilegeUsage())
789       .addGrantees("bob", "Mark")
790       .validate().toString();
791     checkResult(grantStr1, "GRANT INSERT(col1),USAGE ON TABLE Schema1.Table1 TO bob,Mark");
792 
793     String revokeStr1 = new RevokeQuery()
794       .setTarget(GrantQuery.targetTable(_table1))
795       .addPrivileges(GrantQuery.privilegeInsert(_table1_col1),
796                      GrantQuery.privilegeUsage())
797       .addCustomGrantees(RevokeQuery.PUBLIC_GRANTEE)
798       .validate().toString();
799     checkResult(revokeStr1, "REVOKE INSERT(col1),USAGE ON TABLE Schema1.Table1 FROM PUBLIC");
800 
801     try {
802       new GrantQuery()
803         .setTarget(GrantQuery.targetTable(_table1))
804         .addPrivileges(GrantQuery.privilegeInsert(_defTable1_col3))
805         .addGrantees("bob")
806         .validate();
807       fail("ValidationException should have been thrown");
808     } catch(ValidationException e) {}      
809   }
810 
811   public void testSubquery()
812   {
813     String queryStr1 =
814       new SelectQuery()
815       .addColumns(_table1_col1, _table1_col2)
816       .addCondition(new InCondition(
817                         _table1_col1, new Subquery(
818                             new SelectQuery()
819                             .addColumns(_defTable1_col3)
820                             .validate())))
821       .validate().toString();
822     checkResult(queryStr1, "SELECT t0.col1,t0.col2 FROM Schema1.Table1 t0 WHERE (t0.col1 IN (SELECT t1.col3 FROM Table1 t1) )");
823 
824     SelectQuery innerSelect = new SelectQuery()
825       .addCustomColumns(_defTable2_col4)
826       .addCondition(BinaryCondition.equalTo(_table1_col1, _defTable2_col_id));
827     innerSelect.validate();
828     String queryStr2 = innerSelect.toString();
829     checkResult(queryStr2, "SELECT t2.col4 FROM Table2 t2,Schema1.Table1 t0 WHERE (t0.col1 = t2.col_id)");    
830     SelectQuery outerSelect = new SelectQuery()
831       .addCustomColumns(_table1_col1, _table1_col2)
832       .addJoin(SelectQuery.JoinType.INNER, _table1, _defTable1,
833                BinaryCondition.equalTo(_table1_col1, _defTable1_col_id))
834       .addCondition(new InCondition(_table1_col1, new Subquery(innerSelect)));
835     outerSelect.validate();
836     String queryStr3 = outerSelect.toString();
837     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)) )");
838 
839     innerSelect.addCustomColumns()
840       .addJoin(SelectQuery.JoinType.INNER, _table1, _defTable1, _table1_col1,
841                _defTable1_col_id);
842     try {
843       innerSelect.validate();
844       fail("ValidationException should have been thrown");
845     } catch(ValidationException e) {}      
846       
847     try {
848       outerSelect.validate();
849       fail("ValidationException should have been thrown");
850     } catch(ValidationException e) {}      
851 
852     innerSelect = new SelectQuery()
853       .addCustomColumns(_defTable2_col4)
854       .addJoin(SelectQuery.JoinType.INNER, _table1, _defTable2, _table1_col1,
855                _defTable2_col_id)
856       .addCondition(BinaryCondition.equalTo(_table1_col1, _defTable1_col_id));
857     String queryStr4 = innerSelect.toString();
858     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)");
859 
860     try {
861       innerSelect.validate();
862       fail("ValidationException should have been thrown");
863     } catch(ValidationException e) {}      
864 
865     outerSelect = new SelectQuery()
866       .addCustomColumns(_table1_col1, _table1_col2)
867       .addJoin(SelectQuery.JoinType.INNER, _table1, _defTable1, _table1_col1,
868                _defTable1_col_id)
869       .addCondition(new InCondition(_table1_col1, new Subquery(innerSelect)));
870     outerSelect.validate();
871     String queryStr5 = outerSelect.toString();
872     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)) )");
873   }
874 
875   public void testAlterTable()
876   {
877     @SuppressWarnings("deprecation")
878     String queryStr1 =
879       new AlterTableQuery(_table1)
880       .setAction(new AlterTableQuery.AddUniqueConstraintAction()
881                  .addColumns(_table1_col2))
882       .validate().toString();
883     checkResult(queryStr1, "ALTER TABLE Schema1.Table1 ADD UNIQUE (col2)");
884 
885     @SuppressWarnings("deprecation")
886     String queryStr2 =
887       new AlterTableQuery(_defTable1)
888       .setAction(new AlterTableQuery.AddPrimaryConstraintAction()
889                  .addColumns(_defTable1_col_id))
890       .validate().toString();
891     checkResult(queryStr2, "ALTER TABLE Table1 ADD PRIMARY KEY (col_id)");
892 
893     @SuppressWarnings("deprecation")
894     String queryStr3 =
895       new AlterTableQuery(_defTable1)
896       .setAction(new AlterTableQuery.AddForeignConstraintAction(_defTable2)
897                  .addPrimaryKeyReference(_defTable1_col_id))
898       .validate().toString();
899     checkResult(queryStr3, 
900                 "ALTER TABLE Table1 ADD FOREIGN KEY (col_id) REFERENCES Table2");
901 
902     @SuppressWarnings("deprecation")
903     String queryStr4 =
904       new AlterTableQuery(_defTable1)
905       .setAction(new AlterTableQuery.AddForeignConstraintAction(_defTable2)
906                  .addReference(_defTable1_col_id, _defTable2_col4)
907                  .addReference(_defTable1_col2, _defTable2_col5))
908       .validate().toString();
909     checkResult(queryStr4, 
910                 "ALTER TABLE Table1 ADD FOREIGN KEY (col_id,col2) " +
911                 "REFERENCES Table2 (col4,col5)");
912 
913     String queryStr5 =
914       new AlterTableQuery(_defTable2)
915       .setAddConstraint(_defTable2.getConstraints().get(0))
916       .validate().toString();
917     checkResult(queryStr5, 
918                 "ALTER TABLE Table2 ADD CONSTRAINT t2_fk FOREIGN KEY (col4,col5) REFERENCES Table1 (col2,col3)");
919 
920     DbColumn toAdd = _defTable1.addColumn("col5", Types.VARCHAR, 255);
921     toAdd.notNull();
922     String queryStr6 =
923       new AlterTableQuery(_defTable1)
924       .setAction(new AlterTableQuery.AddColumnAction(toAdd)
925                  .addConstraint(new ConstraintClause(ConstraintClause.Type.UNIQUE, null)))
926       .validate().toString();
927     checkResult(queryStr6,
928                 "ALTER TABLE Table1 ADD col5 VARCHAR(255) NOT NULL UNIQUE");
929 
930     toAdd.setDefaultValue("someValue");
931     String queryStr7 =
932       new AlterTableQuery(_defTable1)
933       .setAddColumn(toAdd)
934       .validate().toString();
935     checkResult(queryStr7,
936                 "ALTER TABLE Table1 ADD col5 VARCHAR(255) DEFAULT 'someValue' NOT NULL");
937 
938     String queryStr8 =
939       new AlterTableQuery(_defTable1)
940       .setAction(new AlterTableQuery.AddColumnAction(toAdd)
941                  .setTypeName("NVARCHAR"))
942       .validate().toString();
943     checkResult(queryStr8,
944                 "ALTER TABLE Table1 ADD col5 NVARCHAR(255) DEFAULT 'someValue' NOT NULL");
945 
946   }
947 
948   public void testComment()
949   {
950     String queryStr1 = new SelectQuery()
951       .addCustomColumns(_table1_col1, _table1_col2, new Comment("foo bar"))
952                         .validate().toString();
953     checkResult(queryStr1, "SELECT t0.col1,t0.col2, -- foo bar\n FROM Schema1.Table1 t0");
954 
955     String queryStr2 = new SelectQuery()
956       .addCustomColumns(_table1_col1, _table1_col2)
957                         .validate().toString() +
958       new Comment("My coolest query ever");
959     checkResult(queryStr2, "SELECT t0.col1,t0.col2 FROM Schema1.Table1 t0 -- My coolest query ever\n");
960     
961   }
962 
963   public void testEscapeLiteral()
964   {
965     String orig = "/this%is_a ' literal/pattern";
966 
967     assertEquals("//this/%is/_a ' literal//pattern",
968                  BinaryCondition.escapeLikeLiteral(orig, '/'));
969     assertEquals("/this\\%is\\_a ' literal/pattern",
970                  BinaryCondition.escapeLikeLiteral(orig, '\\'));
971   }
972 
973   public void testValidationException() {
974     SelectQuery select = new SelectQuery()
975       .addCustomColumns(_defTable1_col_id)
976       .addFromTable(_table1);
977 
978     ValidationException ve = null;
979     try {
980       select.validate();
981     } catch(ValidationException tmp) {
982       ve = tmp;
983     }
984 
985     assertNotNull(ve);
986     assertNotNull(ve.getFailedVerifiable());
987     assertSame(select, ve.getFailedVerifiable().get1());
988 
989     String msg = ve.getMessage();
990     checkResult(msg, "Columns used for unreferenced tables [Failed clause: SELECT t1.col_id FROM Schema1.Table1 t0]");
991 
992     
993     select.addCustomColumns(new SqlObject() {
994         @Override
995         public void appendTo(AppendableExt app) throws IOException {
996           throw new NullPointerException("BOO");
997         }
998         @Override
999         protected void collectSchemaObjects(ValidationContext vContext) {}
1000       });
1001     try {
1002       select.validate();
1003     } catch(ValidationException tmp) {
1004       ve = tmp;
1005     }
1006 
1007     assertNotNull(ve);
1008     
1009     msg = ve.getMessage();
1010     assertTrue(msg.matches("Columns used for unreferenced tables \\[Verifiable: com.healthmarketscience.sqlbuilder.SelectQuery@[0-9a-f]+\\]"));
1011 
1012   }
1013 
1014   public void testCreateView()
1015   {
1016     SelectQuery query1 = new SelectQuery()
1017       .addColumns(_defTable1_col_id, _defTable1_col2)
1018       .addCondition(UnaryCondition.isNotNull(_defTable1_col_id));
1019     SelectQuery query2 = new SelectQuery().addAllTableColumns(_defTable2);
1020     
1021     String createStr1 = new CreateViewQuery(_table1)
1022       .setSelectQuery(query1)
1023       .validate().toString();
1024     checkResult(createStr1,
1025                 "CREATE VIEW Schema1.Table1 AS SELECT t1.col_id,t1.col2 FROM Table1 t1 WHERE (t1.col_id IS NOT NULL)");
1026 
1027     
1028     String createStr2 = new CreateViewQuery(_table1)
1029       .addColumns(_table1_col1, _table1_col3)
1030       .setSelectQuery(query1)
1031       .setWithCheckOption(true)
1032       .validate().toString();
1033     checkResult(createStr2,
1034                 "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");
1035 
1036     String createStr3 = new CreateViewQuery(_table1)
1037       .addCustomColumns(_table1_col1, _table1_col3)
1038       .setSelectQuery(query2)
1039                     .validate().toString();
1040     checkResult(createStr3,
1041                 "CREATE VIEW Schema1.Table1 (col1,col3) AS SELECT t2.* FROM Table2 t2");
1042 
1043     CreateViewQuery viewQuery = new CreateViewQuery(_table1)
1044       .setSelectQuery(query2);
1045     
1046     String createStr4 = viewQuery.validate().toString();
1047     checkResult(createStr4,
1048                 "CREATE VIEW Schema1.Table1 AS SELECT t2.* FROM Table2 t2");
1049 
1050     String dropStr1 = viewQuery.getDropQuery().validate().toString();
1051     checkResult(dropStr1,
1052                 "DROP VIEW Schema1.Table1");
1053     
1054     try {
1055       new CreateViewQuery(_table1).validate();
1056       fail("ValidationException should have been thrown");
1057     } catch(ValidationException e) {}
1058     
1059     try {
1060       new CreateViewQuery(_table1)
1061         .addColumns(_table1_col1)
1062         .setSelectQuery(query1)
1063         .validate();
1064       fail("ValidationException should have been thrown");
1065     } catch(ValidationException e) {}
1066     
1067     try {
1068       new CreateViewQuery(_table1)
1069         .addCustomColumns(SqlObject.ALL_SYMBOL)
1070         .setSelectQuery(query2)
1071         .validate();
1072       fail("ValidationException should have been thrown");
1073     } catch(ValidationException e) {}
1074 
1075   }
1076 
1077   public void testCrossSchemaFK() throws Exception
1078   {
1079     String createStr = new CreateTableQuery(_defTable3, true)
1080       .validate().toString();
1081 
1082     checkResult(createStr, "CREATE TABLE DefTable3 (col_id NUMBER CONSTRAINT t2_id_fk REFERENCES Schema1.Table1 (col2))");
1083   }
1084 
1085   public void testCustomPrefix() throws Exception
1086   {
1087     DbTable table = new DbSpec("pre_").addDefaultSchema().addTable("NewTable");
1088     table.addColumn("col1");
1089     table.addColumn("col2");
1090 
1091     String sqlStr = new SelectQuery().addAllTableColumns(table)
1092       .validate().toString();
1093     checkResult(sqlStr, "SELECT pre_0.* FROM NewTable pre_0");
1094   }
1095 
1096   public void testBetweenCondition()
1097   {
1098     String conditionStr = ComboCondition.and(
1099       BinaryCondition.lessThan(_table1_col1, "FOO", false),
1100       ComboCondition.or(),
1101       new BetweenCondition(_defTable2_col4,
1102                       "this string",
1103                       new NumberValueObject(37)))
1104       .toString();
1105     checkResult(conditionStr,
1106                 "((t0.col1 < 'FOO') AND (t2.col4 BETWEEN 'this string' AND 37))");
1107   }
1108 
1109 }