|
1
17 package org.apache.ws.jaxme.sqls.junit;
18
19 import java.util.ArrayList  ;
20 import java.util.Collection ;
21 import java.util.Iterator ;
22 import java.util.List ;
23
24 import junit.framework.TestCase;
25 import org.apache.ws.jaxme.sqls.BooleanConstraint;
26 import org.apache.ws.jaxme.sqls.Column;
27 import org.apache.ws.jaxme.sqls.ColumnReference;
28 import org.apache.ws.jaxme.sqls.CombinedConstraint;
29 import org.apache.ws.jaxme.sqls.DeleteStatement;
30 import org.apache.ws.jaxme.sqls.Expression;
31 import org.apache.ws.jaxme.sqls.ForeignKey;
32 import org.apache.ws.jaxme.sqls.Index;
33 import org.apache.ws.jaxme.sqls.InsertStatement;
34 import org.apache.ws.jaxme.sqls.JoinReference;
35 import org.apache.ws.jaxme.sqls.SQLFactory;
36 import org.apache.ws.jaxme.sqls.SQLGenerator;
37 import org.apache.ws.jaxme.sqls.Schema;
38 import org.apache.ws.jaxme.sqls.SelectStatement;
39 import org.apache.ws.jaxme.sqls.SelectTableReference;
40 import org.apache.ws.jaxme.sqls.StringColumn;
41 import org.apache.ws.jaxme.sqls.Table;
42 import org.apache.ws.jaxme.sqls.TableReference;
43 import org.apache.ws.jaxme.sqls.UpdateStatement;
44 import org.apache.ws.jaxme.sqls.impl.SQLFactoryImpl;
45 import org.apache.ws.jaxme.sqls.impl.VirtualColumn;
46
47
48 public class CreateTest extends TestCase {
49 private SQLFactory sqlFactory;
50 private SQLGenerator sqlGenerator;
51 private Schema schema;
52
53 protected SQLFactory newSQLFactory() {
54 return new SQLFactoryImpl();
55 }
56
57 protected SQLGenerator newSQLGenerator() {
58 return sqlFactory.newSQLGenerator();
59 }
60
61 protected SQLFactory getSQLFactory() {
62 return sqlFactory;
63 }
64
65 protected SQLGenerator getSQLGenerator() {
66 return sqlGenerator;
67 }
68
69 public void setUp() {
70 sqlFactory = newSQLFactory();
71 sqlGenerator = newSQLGenerator();
72 schema = sqlFactory.newSchema("MySchema");
73 }
74
75
77 public CreateTest(String pName) { super(pName); }
78
79
81 protected Table getBasicTable() {
82 Table table = schema.newTable("MyTable");
83 Column myIndex = table.newColumn("MyIndex", Column.Type.INTEGER);
84 assertTrue(!myIndex.isStringColumn());
85 assertTrue(!myIndex.isBinaryColumn());
86 Column myName = table.newColumn("MyName", Column.Type.VARCHAR);
87 assertTrue(myName.isStringColumn()); assertTrue(!myName.isBinaryColumn());
89 ((StringColumn) myName).setLength(60);
90 Column myDate = table.newColumn("MyDate", Column.Type.DATE);
91 assertTrue(!myDate.isStringColumn());
92 assertTrue(!myDate.isBinaryColumn());
93 myDate.setNullable(true);
94 return table;
95 }
96
97
99 protected Table getPrimaryKeyTable() {
100 Table table = getBasicTable();
101 Index index = table.newPrimaryKey();
102 index.addColumn("MyIndex");
103 return table;
104 }
105
106 protected Table getForeignKeyTable(Table pTable) {
107 Table otherTable = pTable.getSchema().newTable("OtherTable");
108 Column otherIndex = otherTable.newColumn("MyIndex", Column.Type.INTEGER);
109 Column referenceColumn = otherTable.newColumn("RefIndex", Column.Type.INTEGER);
110 Column companyColumn = otherTable.newColumn("Company", Column.Type.VARCHAR);
111 ((StringColumn) companyColumn).setLength(60);
112 otherTable.newPrimaryKey().addColumn(otherIndex);
113
114 ForeignKey reference = otherTable.newForeignKey(pTable);
115 reference.addColumnLink(referenceColumn, pTable.getColumn("MyIndex"));
116 return otherTable;
117 }
118
119
122 public void testBasicCreate() {
123 Table table = getBasicTable();
124 SQLGenerator generator = getSQLGenerator();
125 generator.setLineTerminator("\n");
126 Collection statements = generator.getCreate(table.getSchema(), true);
127 Iterator iter = statements.iterator();
128 assertTrue(iter.hasNext());
129 assertEquals("CREATE SCHEMA MySchema", iter.next());
130 assertTrue(iter.hasNext());
131 assertEquals("CREATE TABLE MySchema.MyTable (\n" +
132 " MyIndex INT NOT NULL,\n" +
133 " MyName VARCHAR(60) NOT NULL,\n" +
134 " MyDate DATE\n" +
135 ")\n", iter.next());
136 assertTrue(!iter.hasNext());
137 }
138
139
141 public void testBasicInsert() {
142 Table table = getBasicTable();
143 InsertStatement insertStatement = table.getInsertStatement();
144 SQLGenerator generator = getSQLGenerator();
145 generator.setLineTerminator("\n");
146 String s = generator.getQuery(insertStatement);
147 assertEquals("INSERT INTO MySchema.MyTable (MyIndex, MyName, MyDate) VALUES (?, ?, ?)", s);
148 }
149
150 protected String getBulkInsertResult() {
151 return "INSERT INTO MySchema.MyTable (MyTable.MyIndex, MyTable.MyName, MyTable.MyDate) (SELECT MyTable0.MyIndex, MyTable0.MyName, MyTable0.MyDate FROM MySchema.MyTable AS MyTable0)";
152 }
153
154 public void testBulkInsert() {
155 Table table = getBasicTable();
156 InsertStatement insertStatement = getSQLFactory().newInsertStatement();
157 insertStatement.setTable(table);
158 SelectStatement st = table.getSelectStatement();
159 SQLGenerator generator = getSQLGenerator();
160 generator.setLineTerminator("\n");
161 insertStatement.setSubSelect(st);
162 String got = generator.getQuery(insertStatement);
163 String expect = getBulkInsertResult();
164 assertEquals(expect, got);
165 }
166
167
169 public void testBasicSelect() {
170 Table table = getBasicTable();
171 SelectStatement selectStatement = table.getSelectStatement();
172 SQLGenerator generator = getSQLGenerator();
173 generator.setLineTerminator("\n");
174 String s = generator.getQuery(selectStatement);
175 assertEquals("SELECT MyIndex, MyName, MyDate FROM MySchema.MyTable", s);
176 }
177
178
180 public void testBasicUpdate() {
181 Table table = getPrimaryKeyTable();
182 UpdateStatement updateStatement = table.getUpdateStatement();
183 SQLGenerator generator = getSQLGenerator();
184 generator.setLineTerminator("\n");
185 String s = generator.getQuery(updateStatement);
186 assertEquals("UPDATE MySchema.MyTable SET MyName=?, MyDate=? WHERE MyIndex=?", s);
187 }
188
189
191 public void testBasicDelete() {
192 Table table = getPrimaryKeyTable();
193 DeleteStatement deleteStatement = table.getDeleteStatement();
194 SQLGenerator generator = getSQLGenerator();
195 generator.setLineTerminator("\n");
196 String s = generator.getQuery(deleteStatement);
197 assertEquals("DELETE FROM MySchema.MyTable WHERE MyIndex=?", s);
198 }
199
200 protected String getCreateForeignKeyResult() {
201 return "CREATE TABLE MySchema.OtherTable ( MyIndex INT NOT NULL, RefIndex INT NOT NULL, Company VARCHAR(60) NOT NULL)";
202 }
203
204
206 public void testCreateForeignKey() {
207 Table table = getPrimaryKeyTable();
208 Table otherTable = getForeignKeyTable(table);
209 SQLGenerator generator = getSQLGenerator();
210 Collection c = generator.getCreate(otherTable);
211 assertEquals(1, c.size());
212 String expect = getCreateForeignKeyResult();
213 String got = (String ) c.iterator().next();
214 assertEquals(expect, got);
215 }
216
217 protected String getTestJoinResult() {
218 return "SELECT OtherTable.MyIndex, RefIndex, Company FROM MySchema.OtherTable JOIN MySchema.MyTable ON RefIndex=MyTable.MyIndex WHERE OtherTable.MyIndex=?";
219 }
220
221 protected SelectStatement getJoinStatement() {
222 Table table = getPrimaryKeyTable();
223 Table otherTable = getForeignKeyTable(table);
224 SelectStatement statement = otherTable.getSelectStatement();
225 SelectTableReference tableReference = statement.getSelectTableReference();
226 JoinReference joinReference = tableReference.join(table);
227
228 TableReference refLocal = tableReference;
229 TableReference refRef = tableReference.getRightJoinedTableReference();
230
231 joinReference.getOn().addJoin((ForeignKey) otherTable.getForeignKeys().next(),
232 refLocal, refRef);
233 CombinedConstraint cc = statement.getWhere();
234 BooleanConstraint bc = cc.createEQ();
235 bc.addPart(tableReference.newColumnReference("MyIndex"));
236 bc.addPlaceholder();
237 return statement;
238 }
239
240
242 public void testJoin() {
243 SelectStatement statement = getJoinStatement();
244 SQLGenerator generator = getSQLGenerator();
245 generator.setLineTerminator("\n");
246 String got = generator.getQuery(statement);
247 String expect = getTestJoinResult();
248 assertEquals(expect, got);
249 }
250
251 protected String getTestLeftOuterJoinResult() {
252 return "SELECT OtherTable.MyIndex, RefIndex, Company FROM MySchema.OtherTable LEFT OUTER JOIN MySchema.MyTable ON RefIndex=MyTable.MyIndex WHERE OtherTable.MyIndex=?";
253 }
254
255
257 public void testLeftOuterJoin() {
258 Table table = getPrimaryKeyTable();
259 Table otherTable = getForeignKeyTable(table);
260 SelectStatement statement = otherTable.getSelectStatement();
261 SelectTableReference tableReference = statement.getSelectTableReference();
262 JoinReference joinReference = tableReference.leftOuterJoin(table);
263
264 TableReference refLocal = tableReference;
265 TableReference refRef = tableReference.getRightJoinedTableReference();
266
267 joinReference.getOn().addJoin((ForeignKey) otherTable.getForeignKeys().next(),
268 refLocal, refRef);
269 CombinedConstraint cc = statement.getWhere();
270 BooleanConstraint bc = cc.createEQ();
271 bc.addPart(tableReference.newColumnReference("MyIndex"));
272 bc.addPlaceholder();
273
274 SQLGenerator generator = getSQLGenerator();
275 generator.setLineTerminator("\n");
276 String got = generator.getQuery(statement);
277 String expect = getTestLeftOuterJoinResult();
278 assertEquals(expect, got);
279 }
280
281
283 public void testExists() {
284 Table table = getPrimaryKeyTable();
285 Table otherTable = getForeignKeyTable(table);
286 SelectStatement statement = table.getSelectStatement();
287 SelectTableReference tableReference = statement.getSelectTableReference();
288 SelectStatement existsStatement = otherTable.getSelectStatement();
289 SelectTableReference existsTableReference = existsStatement.getSelectTableReference();
290 BooleanConstraint bc = existsStatement.getWhere().createEQ();
291 bc.addPart(existsTableReference.newColumnReference("RefIndex"));
292 bc.addPart(tableReference.newColumnReference("MyIndex"));
293 statement.getWhere().createEXISTS(existsStatement);
294
295 SQLGenerator generator = getSQLGenerator();
296 generator.setLineTerminator("\n");
297 String got = generator.getQuery(statement);
298 String expect = "SELECT MyTable.MyIndex, MyName, MyDate FROM MySchema.MyTable" +
299 " WHERE EXISTS(SELECT OtherTable.MyIndex, RefIndex, Company" +
300 " FROM MySchema.OtherTable WHERE RefIndex=MyTable.MyIndex)";
301 assertEquals(expect, got);
302 }
303
304
306 public void testBetween() {
307 Table table = getBasicTable();
308 SelectStatement statement = table.getSelectStatement();
309 BooleanConstraint between = statement.getWhere().createBETWEEN();
310 between.addPart(statement.getTableReference().newColumnReference("MyIndex"));
311 between.addPart(3);
312 between.addPart(5);
313
314 SQLGenerator generator = getSQLGenerator();
315 generator.setLineTerminator("\n");
316 String got = generator.getQuery(statement);
317 String expect = "SELECT MyIndex, MyName, MyDate FROM MySchema.MyTable WHERE MyIndex BETWEEN 3 AND 5";
318 assertEquals(expect, got);
319 }
320
321
323 protected Table getComposedKeyTable() {
324 Table table = getPrimaryKeyTable();
325 Column verNumColumn = table.newColumn("VerNum", Column.Type.INTEGER);
326 assertTrue(!verNumColumn.isStringColumn());
327 assertTrue(!verNumColumn.isBinaryColumn());
328 Index index = table.getPrimaryKey();
329 index.addColumn("VerNum");
330 return table;
331 }
332
333
335 public void testComposedPrimaryKey() {
336 Table table = getComposedKeyTable();
337
338 SelectStatement statement = table.getSelectStatement();
339 statement.getWhere().addColumnSetQuery(table.getPrimaryKey(), statement.getTableReference());
340 SQLGenerator generator = getSQLGenerator();
341 generator.setLineTerminator("\n");
342 String s = generator.getQuery(statement);
343 assertEquals("SELECT MyIndex, MyName, MyDate, VerNum FROM MySchema.MyTable WHERE (MyIndex=? AND VerNum=?)", s);
344 }
345
346
348 public void testIndexNames() {
349 SQLGenerator gen = getSQLGenerator();
350 Table table = getBasicTable();
351 for (int i = 0; i < 10; i++) {
352 Index index = table.newIndex();
353 index.addColumn("MyName");
354 String s = (String ) gen.getCreate(index).iterator().next();
355 assertEquals("CREATE INDEX MyTable_I" + i + " ON MySchema.MyTable (MyName)", s);
356 }
357
358 Collection coll = gen.getCreate(schema, true);
359 String [] cmds = (String []) coll.toArray(new String [coll.size()]);
360 assertEquals(12, cmds.length);
361 assertEquals("CREATE SCHEMA MySchema", cmds[0]);
362 assertEquals("CREATE TABLE MySchema.MyTable ( MyIndex INT NOT NULL, MyName VARCHAR(60) NOT NULL, MyDate DATE)", cmds[1]);
363 for (int i = 0; i < 10; i++) {
364 assertEquals("CREATE INDEX MyTable_I" + i + " ON MySchema.MyTable (MyName)", cmds[i+2]);
365 }
366 }
367
368
370 public void testSubSelect() {
371 SQLGenerator gen = getSQLGenerator();
372 Table table = getComposedKeyTable();
373
374 Table otherTable = table.getSchema().newTable("OtherTable");
375 Column otherIndex = otherTable.newColumn("MyIndex", Column.Type.INTEGER);
376 otherTable.newPrimaryKey().addColumn(otherIndex);
377 ForeignKey foreignKey = otherTable.newForeignKey(table);
378 SelectStatement selectStatement = sqlFactory.newSelectStatement();
379 selectStatement.setTable(otherTable);
380 DeleteStatement deleteStatement = sqlFactory.newDeleteStatement();
381 deleteStatement.setTable(table);
382 List columns = new ArrayList ();
383 for (Iterator iter = table.getColumns(); iter.hasNext(); ) {
384 Column column = (Column) iter.next();
385 Column refColumn = otherTable.newColumn("Ref" + column.getName(), column.getType());
386 foreignKey.addColumnLink(refColumn, column);
387 if (column.isPrimaryKeyPart()) {
388 selectStatement.addResultColumn(selectStatement.getTableReference().newColumnReference(refColumn));
389 columns.add(deleteStatement.getTableReference().newColumnReference(column));
390 }
391 }
392 BooleanConstraint eq = selectStatement.getWhere().createEQ();
393 eq.addPart(selectStatement.getTableReference().newColumnReference("RefMyName"));
394 eq.addPlaceholder();
395
396 BooleanConstraint bc = deleteStatement.getWhere().createIN();
397 bc.addPart((ColumnReference[]) columns.toArray(new ColumnReference[columns.size()]));
398 bc.addPart(selectStatement);
399 String expect = "DELETE FROM MySchema.MyTable WHERE (MyTable.MyIndex, VerNum) IN ((SELECT RefMyIndex, RefVerNum FROM MySchema.OtherTable WHERE RefMyName=?))";
400 String got = gen.getQuery(deleteStatement);
401 assertEquals(expect, got);
402 }
403
404 public void testVirtualColumn() {
405 Table table = getBasicTable();
406 SelectStatement selectStatement = table.getSelectStatement();
407 VirtualColumn col = new VirtualColumn("virtCol", Column.Type.VARCHAR);
408 selectStatement.addResultColumn(col);
409 col.setValue("null");
410 SQLGenerator gen = getSQLGenerator();
411 String query = gen.getQuery(selectStatement);
412 assertEquals("SELECT MyIndex, MyName, MyDate, null AS virtCol FROM MySchema.MyTable", query);
413 }
414
415 public void testNOT() {
416 Table table = getBasicTable();
417 SelectStatement selectStatement = table.getSelectStatement();
418 SelectTableReference ref = selectStatement.getSelectTableReference();
419 CombinedConstraint and = selectStatement.getWhere();
420 BooleanConstraint bc = and.createLIKE();
421 bc.addPart(ref.newColumnReference("MyName"));
422 bc.addPart("%a%");
423 CombinedConstraint or = and.createOrConstraint();
424 or.setNOT(true);
425 bc = or.createEQ();
426 bc.addPart(ref.newColumnReference("MyIndex"));
427 bc.addPart(1);
428 SQLGenerator gen = getSQLGenerator();
429 String query = gen.getQuery(selectStatement);
430 assertEquals("SELECT MyIndex, MyName, MyDate FROM MySchema.MyTable WHERE (MyName LIKE '%a%' AND NOT (MyIndex=1))", query);
431
432 bc = or.createEQ();
433 bc.addPart(ref.newColumnReference("MyIndex"));
434 bc.addPart(2);
435 query = gen.getQuery(selectStatement);
436 assertEquals("SELECT MyIndex, MyName, MyDate FROM MySchema.MyTable WHERE (MyName LIKE '%a%' AND (NOT (MyIndex=1 OR MyIndex=2)))", query);
437 }
438
439
441 public void testExpressions() {
442 Table t = getBasicTable();
443 SelectStatement st = t.getSelectStatement();
444 SelectTableReference ref = st.getSelectTableReference();
445 BooleanConstraint bc = st.getWhere().createGT();
446 Expression e1 = bc.createSUM();
447 e1.addPart(ref.newColumnReference("MyIndex"));
448 e1.addPart(3);
449 Expression e2 = bc.createSUM();
450 e2.addPart(5);
451 SQLGenerator gen = getSQLGenerator();
452 String got = gen.getQuery(st);
453 String expect = "SELECT MyIndex, MyName, MyDate FROM MySchema.MyTable WHERE (MyIndex+3)>5";
454 assertEquals(expect, got);
455 }
456 }
457 |
Popular Tags |
Java API By Example, From Geeks To Geeks.