METAMODEL-1165: Fixed - added default_table alias table
[metamodel.git] / core / src / test / java / org / apache / metamodel / QueryPostprocessDataContextTest.java
1 /**
2 * Licensed to the Apache Software Foundation (ASF) under one
3 * or more contributor license agreements. See the NOTICE file
4 * distributed with this work for additional information
5 * regarding copyright ownership. The ASF licenses this file
6 * to you under the Apache License, Version 2.0 (the
7 * "License"); you may not use this file except in compliance
8 * with the License. You may obtain a copy of the License at
9 *
10 * http://www.apache.org/licenses/LICENSE-2.0
11 *
12 * Unless required by applicable law or agreed to in writing,
13 * software distributed under the License is distributed on an
14 * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
15 * KIND, either express or implied. See the License for the
16 * specific language governing permissions and limitations
17 * under the License.
18 */
19 package org.apache.metamodel;
20
21 import java.nio.channels.UnsupportedAddressTypeException;
22 import java.util.ArrayList;
23 import java.util.Arrays;
24 import java.util.List;
25 import java.util.stream.Collectors;
26
27 import javax.swing.table.TableModel;
28
29 import org.apache.metamodel.data.DataSet;
30 import org.apache.metamodel.data.DataSetHeader;
31 import org.apache.metamodel.data.DataSetTableModel;
32 import org.apache.metamodel.data.DefaultRow;
33 import org.apache.metamodel.data.EmptyDataSet;
34 import org.apache.metamodel.data.InMemoryDataSet;
35 import org.apache.metamodel.data.Row;
36 import org.apache.metamodel.data.SimpleDataSetHeader;
37 import org.apache.metamodel.query.CompiledQuery;
38 import org.apache.metamodel.query.FilterItem;
39 import org.apache.metamodel.query.FromItem;
40 import org.apache.metamodel.query.FunctionType;
41 import org.apache.metamodel.query.GroupByItem;
42 import org.apache.metamodel.query.JoinType;
43 import org.apache.metamodel.query.OperatorType;
44 import org.apache.metamodel.query.OrderByItem;
45 import org.apache.metamodel.query.OrderByItem.Direction;
46 import org.apache.metamodel.query.Query;
47 import org.apache.metamodel.query.QueryParameter;
48 import org.apache.metamodel.query.SelectItem;
49 import org.apache.metamodel.schema.Column;
50 import org.apache.metamodel.schema.MutableColumn;
51 import org.apache.metamodel.schema.MutableSchema;
52 import org.apache.metamodel.schema.MutableTable;
53 import org.apache.metamodel.schema.Relationship;
54 import org.apache.metamodel.schema.Schema;
55 import org.apache.metamodel.schema.Table;
56
57 public class QueryPostprocessDataContextTest extends MetaModelTestCase {
58
59 private final Schema schema = getExampleSchema();
60 private final Table table1 = schema.getTableByName(TABLE_CONTRIBUTOR);
61 private final Table table2 = schema.getTableByName(TABLE_ROLE);
62
63 public void testSchemaTraversalWithAliasTable() {
64 final MockUpdateableDataContext dc = new MockUpdateableDataContext();
65
66 final Column column = dc.getColumnByQualifiedLabel("foo");
67 assertEquals("table", column.getTable().getName());
68 }
69
70 public void testNoAliasTableWhenSystemPropertySet() {
71 System.setProperty(QueryPostprocessDataContext.SYSTEM_PROPERTY_CREATE_DEFAULT_TABLE_ALIAS, "false");
72 try {
73 final MockUpdateableDataContext dc = new MockUpdateableDataContext();
74 final List<Table> tables = dc.getDefaultSchema().getTables();
75 assertEquals(1, tables.size());
76
77 assertEquals("table", tables.get(0).getName());
78 } finally {
79 System.clearProperty(QueryPostprocessDataContext.SYSTEM_PROPERTY_CREATE_DEFAULT_TABLE_ALIAS);
80 }
81 }
82
83 public void testNoAliasTableWhenConstructorArgSet() {
84 final MockUpdateableDataContext dc = new MockUpdateableDataContext(false);
85 final List<Table> tables = dc.getDefaultSchema().getTables();
86 assertEquals(1, tables.size());
87
88 assertEquals("table", tables.get(0).getName());
89 }
90
91 public void testAliasTableQueries() {
92 final MockUpdateableDataContext dc = new MockUpdateableDataContext();
93 final List<Table> tables = dc.getDefaultSchema().getTables();
94 assertEquals(2, tables.size());
95
96 final Query q0 = dc.query().from(tables.get(0)).selectAll().toQuery();
97 assertEquals("SELECT table.foo, table.bar FROM schema.table", q0.toSql());
98
99 final Query q1 = dc.parseQuery("SELECT * FROM default_table d");
100 assertEquals("SELECT d.foo, d.bar FROM schema.default_table d", q1.toSql());
101
102 final Query q2 = dc.parseQuery("SELECT * FROM default_table");
103 assertEquals("SELECT default_table.foo, default_table.bar FROM schema.default_table", q2.toSql());
104
105 final DataSet dataSet0 = dc.executeQuery(q0);
106 final DataSet dataSet1 = dc.executeQuery(q1);
107 final DataSet dataSet2 = dc.executeQuery(q2);
108
109 Arrays.asList(dataSet0, dataSet1, dataSet2).forEach(ds -> {
110 assertTrue(ds.next());
111 assertEquals("Row[values=[1, hello]]", ds.getRow().toString());
112 assertTrue(ds.next());
113 assertEquals("Row[values=[2, there]]", ds.getRow().toString());
114 assertTrue(ds.next());
115 assertFalse(ds.next());
116 ds.close();
117 });
118
119 assertEquals("Row[values=[3]]", MetaModelHelper
120 .executeSingleRowQuery(dc, dc.parseQuery("SELECT COUNT(*) FROM default_table")).toString());
121 assertEquals("Row[values=[1]]",
122 MetaModelHelper
123 .executeSingleRowQuery(dc, dc.parseQuery("SELECT COUNT(*) FROM default_table WHERE foo = '2'"))
124 .toString());
125 }
126
127 public void testQueryMaxRows0() throws Exception {
128 final MockDataContext dc = new MockDataContext("sch", "tab", "1");
129 final Table table = dc.getDefaultSchema().getTable(0);
130 final DataSet dataSet = dc.query().from(table).selectAll().limit(0).execute();
131 assertTrue(dataSet instanceof EmptyDataSet);
132 assertFalse(dataSet.next());
133 dataSet.close();
134 }
135
136 // see issue METAMODEL-100
137 public void testSelectFromColumnsWithSameName() throws Exception {
138 final MutableTable table = new MutableTable("table");
139 table.addColumn(new MutableColumn("foo", table).setColumnNumber(0));
140 table.addColumn(new MutableColumn("foo", table).setColumnNumber(1));
141 table.addColumn(new MutableColumn("bar", table).setColumnNumber(2));
142
143 final QueryPostprocessDataContext dc = new QueryPostprocessDataContext() {
144 @Override
145 protected DataSet materializeMainSchemaTable(Table table, List<Column> columns, int maxRows) {
146 Object[] values = new Object[columns.size()];
147 for (int i = 0; i < columns.size(); i++) {
148 values[i] = columns.get(i).getColumnNumber();
149 }
150 DataSetHeader header =
151 new SimpleDataSetHeader(columns.stream().map(SelectItem::new).collect(Collectors.toList()));
152 DefaultRow row = new DefaultRow(header, values);
153 return new InMemoryDataSet(row);
154 }
155
156 @Override
157 protected String getMainSchemaName() throws MetaModelException {
158 return "sch";
159 }
160
161 @Override
162 protected Schema getMainSchema() throws MetaModelException {
163 MutableSchema schema = new MutableSchema(getMainSchemaName());
164 schema.addTable(table);
165 table.setSchema(schema);
166 return schema;
167 }
168 };
169
170 DataSet ds = dc.query().from(table).selectAll().execute();
171 assertTrue(ds.next());
172 assertEquals("Row[values=[0, 1, 2]]", ds.getRow().toString());
173 assertFalse(ds.next());
174 ds.close();
175 }
176
177 public void testAggregateQueryNoWhereClause() throws Exception {
178 MockDataContext dc = new MockDataContext("sch", "tab", "1");
179 Table table = dc.getDefaultSchema().getTable(0);
180 assertSingleRowResult("Row[values=[4]]", dc.query().from(table).selectCount().execute());
181 }
182
183 public void testAggregateQueryRegularWhereClause() throws Exception {
184 MockDataContext dc = new MockDataContext("sch", "tab", "1");
185 Table table = dc.getDefaultSchema().getTable(0);
186 assertSingleRowResult("Row[values=[3]]",
187 dc.query().from(table).selectCount().where("baz").eq("world").execute());
188 }
189
190 public void testApplyFunctionToNullValues() throws Exception {
191 QueryPostprocessDataContext dataContext = new QueryPostprocessDataContext() {
192 @Override
193 public DataSet materializeMainSchemaTable(Table table, List<Column> columns, int maxRows) {
194 if (table == table1) {
195 List<Column> columns1 = table1.getColumns();
196 List<SelectItem> selectItems = columns1.stream().map(SelectItem::new).collect(Collectors.toList());
197 List<Object[]> data = new ArrayList<Object[]>();
198 data.add(new Object[] { 1, "no nulls", 1 });
199 data.add(new Object[] { 2, "onlynull", null });
200 data.add(new Object[] { 3, "mixed", null });
201 data.add(new Object[] { 4, "mixed", "" });
202 data.add(new Object[] { 5, "mixed", 2 });
203 data.add(new Object[] { 6, "mixed", " \n \t " });
204 if (maxRows != -1) {
205 for (int i = data.size() - 1; i >= maxRows; i--) {
206 data.remove(i);
207 }
208 }
209 return createDataSet(selectItems, data);
210 }
211 throw new IllegalArgumentException("This test only accepts table1 and table2");
212 }
213
214 @Override
215 protected String getMainSchemaName() throws MetaModelException {
216 return schema.getName();
217 }
218
219 @Override
220 protected Schema getMainSchema() throws MetaModelException {
221 return schema;
222 }
223 };
224
225 DataSet dataSet = dataContext.query().from(TABLE_CONTRIBUTOR)
226 .select(FunctionType.SUM, COLUMN_CONTRIBUTOR_COUNTRY).select(COLUMN_CONTRIBUTOR_NAME)
227 .groupBy(COLUMN_CONTRIBUTOR_NAME).orderBy(COLUMN_CONTRIBUTOR_NAME).execute();
228 assertTrue(dataSet.next());
229 assertEquals("Row[values=[2.0, mixed]]", dataSet.getRow().toString());
230 assertTrue(dataSet.next());
231 assertEquals("Row[values=[1.0, no nulls]]", dataSet.getRow().toString());
232 assertTrue(dataSet.next());
233 assertEquals("Row[values=[0.0, onlynull]]", dataSet.getRow().toString());
234 assertFalse(dataSet.next());
235 dataSet.close();
236 }
237
238 public void testGroupByNulls() throws Exception {
239 MockDataContext dc = new MockDataContext("sch", "tab", null);
240 Table table = dc.getDefaultSchema().getTable(0);
241 DataSet dataSet = dc.query().from(table).select(FunctionType.SUM, "foo").select("baz").groupBy("baz").execute();
242 assertTrue(dataSet.next());
243 assertEquals("Row[values=[7.0, world]]", dataSet.getRow().toString());
244 assertTrue(dataSet.next());
245 assertEquals("Row[values=[3.0, null]]", dataSet.getRow().toString());
246 assertFalse(dataSet.next());
247 dataSet.close();
248 }
249
250 public void testNewAggregateFunctions() throws Exception {
251 MockDataContext dc = new MockDataContext("sch", "tab", null);
252 Table table = dc.getDefaultSchema().getTable(0);
253 DataSet dataSet = dc.query().from(table).select(FunctionType.FIRST, "foo").select(FunctionType.LAST, "foo")
254 .select(FunctionType.RANDOM, "foo").execute();
255 assertTrue(dataSet.next());
256
257 final Row row = dataSet.getRow();
258 assertEquals("1", row.getValue(0));
259 assertEquals("4", row.getValue(1));
260
261 final Object randomValue = row.getValue(2);
262 assertTrue(Arrays.asList("1", "2", "3", "4").contains(randomValue));
263
264 assertFalse(dataSet.next());
265 dataSet.close();
266 }
267
268 public void testAggregateQueryWhereClauseExcludingAll() throws Exception {
269 MockDataContext dc = new MockDataContext("sch", "tab", "1");
270 assertSingleRowResult("Row[values=[0]]",
271 dc.query().from("tab").selectCount().where("baz").eq("non_existing_value").execute());
272 }
273
274 public void testMixedAggregateAndRawQueryOnEmptyTable() throws Exception {
275 MockDataContext dc = new MockDataContext("sch", "tab", "1");
276 Table emptyTable = dc.getTableByQualifiedLabel("an_empty_table");
277
278 assertSingleRowResult("Row[values=[0, null]]", dc.query().from(emptyTable).selectCount().and("foo").execute());
279 }
280
281 private void assertSingleRowResult(String rowStr, DataSet ds) {
282 assertTrue("DataSet had no rows", ds.next());
283 Row row = ds.getRow();
284 assertEquals(rowStr, row.toString());
285 assertFalse("DataSet had more than a single row!", ds.next());
286 ds.close();
287 }
288
289 public void testMixedAggregateAndRawQuery() throws Exception {
290 MockDataContext dc = new MockDataContext("sch", "tab", "1");
291 Table table = dc.getDefaultSchema().getTable(0);
292 List<Column> columns = table.getColumns();
293
294 Query query = dc.query().from(table).select(FunctionType.MAX, columns.get(0)).and(columns.get(1)).toQuery();
295 assertEquals("SELECT MAX(tab.foo), tab.bar FROM sch.tab", query.toSql());
296
297 DataSet ds = dc.executeQuery(query);
298 assertTrue(ds.next());
299 assertEquals("Row[values=[4, hello]]", ds.getRow().toString());
300 assertTrue(ds.next());
301 assertEquals("Row[values=[4, 1]]", ds.getRow().toString());
302 assertTrue(ds.next());
303 assertEquals("Row[values=[4, hi]]", ds.getRow().toString());
304 assertTrue(ds.next());
305 assertEquals("Row[values=[4, yo]]", ds.getRow().toString());
306 assertFalse(ds.next());
307 }
308
309 public void testScalarFunctionSelect() throws Exception {
310 MockDataContext dc = new MockDataContext("sch", "tab", "1");
311 Table table = dc.getDefaultSchema().getTable(0);
312
313 Query query = dc.query().from(table).select("foo").select(FunctionType.TO_NUMBER, "foo").select("bar")
314 .select(FunctionType.TO_STRING, "bar").select(FunctionType.TO_NUMBER, "bar").toQuery();
315 assertEquals("SELECT tab.foo, TO_NUMBER(tab.foo), tab.bar, TO_STRING(tab.bar), TO_NUMBER(tab.bar) FROM sch.tab",
316 query.toSql());
317
318 DataSet ds = dc.executeQuery(query);
319 assertTrue(ds.next());
320 Row row;
321
322 row = ds.getRow();
323 assertEquals("Row[values=[1, 1, hello, hello, null]]", row.toString());
324 Object value1 = row.getValue(1);
325 assertEquals(Integer.class, value1.getClass());
326
327 assertTrue(ds.next());
328
329 row = ds.getRow();
330 assertEquals("Row[values=[2, 2, 1, 1, 1]]", row.toString());
331 Object value2 = row.getValue(1);
332 assertEquals(Integer.class, value2.getClass());
333 Object value3 = row.getValue(4);
334 assertEquals(Integer.class, value3.getClass());
335
336 assertTrue(ds.next());
337 ds.close();
338 }
339
340 public void testScalarFunctionWhere() throws Exception {
341 MockDataContext dc = new MockDataContext("sch", "tab", "1");
342 Table table = dc.getDefaultSchema().getTable(0);
343
344 Query query = dc.query().from(table).select("foo").where(FunctionType.TO_NUMBER, "bar").eq(1).toQuery();
345 assertEquals("SELECT tab.foo FROM sch.tab WHERE TO_NUMBER(tab.bar) = 1", query.toSql());
346
347 DataSet ds = dc.executeQuery(query);
348 assertTrue(ds.next());
349 Row row;
350
351 row = ds.getRow();
352 assertEquals("Row[values=[2]]", row.toString());
353
354 assertFalse(ds.next());
355 ds.close();
356 }
357
358 public void testSelectItemReferencesToFromItems() throws Exception {
359 MockDataContext dc = new MockDataContext("sch", "tab", "1");
360
361 Table table = dc.getDefaultSchema().getTable(0);
362
363 Query q = new Query();
364 FromItem fromItem1 = q.from(table, "t1").getFromClause().getItem(0);
365 FromItem fromItem2 = q.from(table, "t2").getFromClause().getItem(1);
366 q.select(table.getColumnByName("foo"), fromItem1);
367 q.select(table.getColumnByName("foo"), fromItem2);
368 q.where(q.getSelectClause().getItem(0), OperatorType.EQUALS_TO, "2");
369 assertEquals("SELECT t1.foo, t2.foo FROM sch.tab t1, sch.tab t2 WHERE t1.foo = '2'", q.toSql());
370
371 DataSet ds = dc.executeQuery(q);
372 List<SelectItem> selectItems = ds.getSelectItems();
373 assertEquals(2, selectItems.size());
374 assertEquals("t1.foo", selectItems.get(0).toSql());
375 assertEquals("t2.foo", selectItems.get(1).toSql());
376 assertTrue(ds.next());
377 assertEquals("Row[values=[2, 1]]", ds.getRow().toString());
378 assertTrue(ds.next());
379 assertEquals("Row[values=[2, 2]]", ds.getRow().toString());
380 assertTrue(ds.next());
381 assertEquals("Row[values=[2, 3]]", ds.getRow().toString());
382 assertTrue(ds.next());
383 assertEquals("Row[values=[2, 4]]", ds.getRow().toString());
384 assertFalse(ds.next());
385 ds.close();
386 }
387
388 private DataContext getDataContext() {
389 QueryPostprocessDataContext dataContext = new QueryPostprocessDataContext() {
390
391 @Override
392 public DataSet materializeMainSchemaTable(Table table, List<Column> columns, int maxRows) {
393 if (table == table1) {
394 List<Column> columns1 = table1.getColumns();
395 List<SelectItem> selectItems = columns1.stream().map(SelectItem::new).collect(Collectors.toList());
396 List<Object[]> data = new ArrayList<Object[]>();
397 data.add(new Object[] { 1, "kasper", "denmark" });
398 data.add(new Object[] { 2, "asbjorn", "denmark" });
399 data.add(new Object[] { 3, "johny", "israel" });
400 data.add(new Object[] { 4, "daniel", "canada" });
401 data.add(new Object[] { 5, "sasidhar", "unknown" });
402 data.add(new Object[] { 6, "jesper", "denmark" });
403 if (maxRows != -1) {
404 for (int i = data.size() - 1; i >= maxRows; i--) {
405 data.remove(i);
406 }
407 }
408 return createDataSet(selectItems, data);
409 } else if (table == table2) {
410 List<SelectItem> selectItems =
411 table2.getColumns().stream().map(SelectItem::new).collect(Collectors.toList());
412 List<Object[]> data = new ArrayList<Object[]>();
413 data.add(new Object[] { 1, 1, "founder" });
414 data.add(new Object[] { 1, 1, "developer" });
415 data.add(new Object[] { 1, 2, "developer" });
416 data.add(new Object[] { 2, 1, "developer" });
417 data.add(new Object[] { 2, 3, "developer" });
418 data.add(new Object[] { 4, 1, "advisor" });
419 data.add(new Object[] { 5, 2, "developer" });
420 data.add(new Object[] { 6, 1, "founder" });
421 if (maxRows != -1) {
422 for (int i = data.size() - 1; i >= maxRows; i--) {
423 data.remove(i);
424 }
425 }
426 return createDataSet(selectItems, data);
427 }
428 throw new IllegalArgumentException("This test only accepts table1 and table2");
429 }
430
431 @Override
432 protected String getMainSchemaName() throws MetaModelException {
433 return schema.getName();
434 }
435
436 @Override
437 protected Schema getMainSchema() throws MetaModelException {
438 return schema;
439 }
440 };
441 return dataContext;
442 }
443
444 public void testDistinct() throws Exception {
445
446 Column roleColumn = table2.getColumnByName(COLUMN_ROLE_ROLE_NAME);
447
448 Query q = new Query().select(roleColumn).from(table2).orderBy(roleColumn);
449 q.getSelectClause().setDistinct(true);
450
451 DataContext dc = getDataContext();
452 DataSet data = dc.executeQuery(q);
453 assertTrue(data.next());
454 assertEquals("advisor", data.getRow().getValue(roleColumn));
455 assertTrue(data.next());
456 assertEquals("developer", data.getRow().getValue(roleColumn));
457 assertTrue(data.next());
458 assertEquals("founder", data.getRow().getValue(roleColumn));
459 assertFalse(data.next());
460 }
461
462 public void testInformationSchema() throws Exception {
463 DataContext dc = getDataContext();
464 assertEquals("[information_schema, MetaModelSchema]", Arrays.toString(dc.getSchemaNames().toArray()));
465 Schema informationSchema = dc.getSchemaByName("information_schema");
466 assertEquals(
467 "[Table[name=tables,type=TABLE,remarks=null], Table[name=columns,type=TABLE,remarks=null], Table[name=relationships,type=TABLE,remarks=null]]",
468 Arrays.toString(informationSchema.getTables().toArray()));
469 assertEquals(
470 "[Relationship[primaryTable=tables,primaryColumns=[name],foreignTable=columns,foreignColumns=[table]], "
471 + "Relationship[primaryTable=tables,primaryColumns=[name],foreignTable=relationships,foreignColumns=[primary_table]], "
472 + "Relationship[primaryTable=tables,primaryColumns=[name],foreignTable=relationships,foreignColumns=[foreign_table]], "
473 + "Relationship[primaryTable=columns,primaryColumns=[name],foreignTable=relationships,foreignColumns=[primary_column]], "
474 + "Relationship[primaryTable=columns,primaryColumns=[name],foreignTable=relationships,foreignColumns=[foreign_column]]]",
475 Arrays.toString(informationSchema.getRelationships().toArray()));
476 Table tablesTable = informationSchema.getTableByName("tables");
477 assertEquals("[Column[name=name,columnNumber=0,type=VARCHAR,nullable=false,nativeType=null,columnSize=null], "
478 + "Column[name=type,columnNumber=1,type=VARCHAR,nullable=true,nativeType=null,columnSize=null], "
479 + "Column[name=num_columns,columnNumber=2,type=INTEGER,nullable=true,nativeType=null,columnSize=null], "
480 + "Column[name=remarks,columnNumber=3,type=VARCHAR,nullable=true,nativeType=null,columnSize=null]]",
481 Arrays.toString(tablesTable.getColumns().toArray()));
482 Table columnsTable = informationSchema.getTableByName("columns");
483 assertEquals("[Column[name=name,columnNumber=0,type=VARCHAR,nullable=false,nativeType=null,columnSize=null], "
484 + "Column[name=type,columnNumber=1,type=VARCHAR,nullable=true,nativeType=null,columnSize=null], "
485 + "Column[name=native_type,columnNumber=2,type=VARCHAR,nullable=true,nativeType=null,columnSize=null], "
486 + "Column[name=size,columnNumber=3,type=INTEGER,nullable=true,nativeType=null,columnSize=null], "
487 + "Column[name=nullable,columnNumber=4,type=BOOLEAN,nullable=true,nativeType=null,columnSize=null], "
488 + "Column[name=indexed,columnNumber=5,type=BOOLEAN,nullable=true,nativeType=null,columnSize=null], "
489 + "Column[name=table,columnNumber=6,type=VARCHAR,nullable=false,nativeType=null,columnSize=null], "
490 + "Column[name=remarks,columnNumber=7,type=VARCHAR,nullable=true,nativeType=null,columnSize=null]]",
491 Arrays.toString(columnsTable.getColumns().toArray()));
492 Table relationshipsTable = informationSchema.getTableByName("relationships");
493 assertEquals(
494 "[Column[name=primary_table,columnNumber=0,type=VARCHAR,nullable=false,nativeType=null,columnSize=null], "
495 + "Column[name=primary_column,columnNumber=1,type=VARCHAR,nullable=false,nativeType=null,columnSize=null], "
496 + "Column[name=foreign_table,columnNumber=2,type=VARCHAR,nullable=false,nativeType=null,columnSize=null], "
497 + "Column[name=foreign_column,columnNumber=3,type=VARCHAR,nullable=false,nativeType=null,columnSize=null]]",
498 Arrays.toString(relationshipsTable.getColumns().toArray()));
499
500 DataSet dataSet = dc.query().from(tablesTable).select(tablesTable.getColumns()).execute();
501 assertTrue(dataSet.next());
502 assertEquals("Row[values=[contributor, TABLE, 3, null]]", dataSet.getRow().toString());
503 assertTrue(dataSet.next());
504 assertEquals("Row[values=[project, TABLE, 4, null]]", dataSet.getRow().toString());
505 assertTrue(dataSet.next());
506 assertEquals("Row[values=[role, TABLE, 3, null]]", dataSet.getRow().toString());
507 assertTrue(dataSet.next());
508 assertEquals("Row[values=[project_contributor, VIEW, 3, null]]", dataSet.getRow().toString());
509 assertFalse(dataSet.next());
510 dataSet.close();
511
512 Relationship relationship = tablesTable.getRelationships(columnsTable).iterator().next();
513 FromItem joinFromItem = new FromItem(JoinType.INNER, relationship);
514 Query q = new Query().select(tablesTable.getColumnByName("name")).select(columnsTable.getColumnByName("name"))
515 .select(columnsTable.getBooleanColumns()).from(joinFromItem);
516
517 assertEquals("SELECT tables.name, columns.name, columns.nullable, columns.indexed "
518 + "FROM information_schema.tables INNER JOIN information_schema.columns "
519 + "ON tables.name = columns.table", q.toString());
520
521 dataSet = dc.executeQuery(q);
522 assertTrue(dataSet.next());
523 assertEquals("Row[values=[contributor, contributor_id, false, true]]", dataSet.getRow().toString());
524 assertTrue(dataSet.next());
525 assertEquals("Row[values=[contributor, name, false, false]]", dataSet.getRow().toString());
526 assertTrue(dataSet.next());
527 assertEquals("Row[values=[contributor, country, true, false]]", dataSet.getRow().toString());
528 assertTrue(dataSet.next());
529 assertEquals("Row[values=[project, project_id, false, false]]", dataSet.getRow().toString());
530 assertTrue(dataSet.next());
531 assertEquals("Row[values=[project, name, false, false]]", dataSet.getRow().toString());
532 dataSet.close();
533 }
534
535 public void testOrderByWithoutSelecting() throws Exception {
536 Query q = new Query();
537 q.from(new FromItem(table2).setAlias("r"));
538 Column roleColumn = table2.getColumnByName(COLUMN_ROLE_ROLE_NAME);
539 Column projectIdColumn = table2.getColumnByName(COLUMN_ROLE_PROJECT_ID);
540 q.select(new SelectItem(projectIdColumn));
541 q.orderBy(roleColumn);
542 assertEquals("SELECT r.project_id FROM MetaModelSchema.role r ORDER BY r.name ASC", q.toString());
543
544 DataContext dc = getDataContext();
545 DataSet data = dc.executeQuery(q);
546 assertEquals(1, data.getSelectItems().size());
547
548 TableModel tableModel = new DataSetTableModel(data);
549
550 // should correspond to these lines:
551
552 // data.add(new Object[] { 4, 1, "advisor" });
553 // data.add(new Object[] { 1, 1, "developer" });
554 // data.add(new Object[] { 1, 2, "developer" });
555 // data.add(new Object[] { 2, 1, "developer" });
556 // data.add(new Object[] { 2, 3, "developer" });
557 // data.add(new Object[] { 5, 2, "developer" });
558 // data.add(new Object[] { 1, 1, "founder" });
559 // data.add(new Object[] { 6, 1, "founder" });
560
561 assertEquals(8, tableModel.getRowCount());
562 assertEquals(1, tableModel.getColumnCount());
563 assertEquals(1, tableModel.getValueAt(0, 0));
564 assertEquals(1, tableModel.getValueAt(1, 0));
565 assertEquals(2, tableModel.getValueAt(2, 0));
566 assertEquals(1, tableModel.getValueAt(3, 0));
567 assertEquals(3, tableModel.getValueAt(4, 0));
568 assertEquals(2, tableModel.getValueAt(5, 0));
569 assertEquals(1, tableModel.getValueAt(6, 0));
570 assertEquals(1, tableModel.getValueAt(7, 0));
571 }
572
573 public void testGroupByWithoutSelecting() throws Exception {
574 Query q = new Query();
575 q.from(new FromItem(table2).setAlias("r"));
576 Column roleColumn = table2.getColumnByName(COLUMN_ROLE_ROLE_NAME);
577 Column projectIdColumn = table2.getColumnByName(COLUMN_ROLE_PROJECT_ID);
578 q.select(new SelectItem(FunctionType.SUM, projectIdColumn));
579 q.groupBy(new GroupByItem(new SelectItem(roleColumn)));
580 q.orderBy(roleColumn);
581 assertEquals("SELECT SUM(r.project_id) FROM MetaModelSchema.role r GROUP BY r.name ORDER BY r.name ASC",
582 q.toString());
583
584 DataContext dc = getDataContext();
585
586 DataSet data = dc.executeQuery(q);
587 assertEquals(1, data.getSelectItems().size());
588 assertEquals("SUM(r.project_id)", data.getSelectItems().get(0).toString());
589
590 TableModel tableModel = new DataSetTableModel(data);
591 assertEquals(3, tableModel.getRowCount());
592 assertEquals(1, tableModel.getColumnCount());
593 assertEquals(1.0, tableModel.getValueAt(0, 0));
594 assertEquals(9.0, tableModel.getValueAt(1, 0));
595 assertEquals(2.0, tableModel.getValueAt(2, 0));
596
597 q = dc.query().from(table2).select("name").orderBy("name").toQuery();
598 q.getSelectClause().setDistinct(true);
599
600 tableModel = new DataSetTableModel(dc.executeQuery(q));
601 assertEquals(3, tableModel.getRowCount());
602 assertEquals(1, tableModel.getColumnCount());
603 assertEquals("advisor", tableModel.getValueAt(0, 0));
604 assertEquals("developer", tableModel.getValueAt(1, 0));
605 assertEquals("founder", tableModel.getValueAt(2, 0));
606 }
607
608 public void testSimpleGroupBy() throws Exception {
609 Query q = new Query();
610 q.from(new FromItem(table2).setAlias("r"));
611 Column roleColumn = table2.getColumnByName(COLUMN_ROLE_ROLE_NAME);
612 q.select(new SelectItem(roleColumn));
613 q.groupBy(new GroupByItem(new SelectItem(roleColumn)));
614 assertEquals("SELECT r.name FROM MetaModelSchema.role r GROUP BY r.name", q.toString());
615
616 DataContext dc = getDataContext();
617 DataSet data = dc.executeQuery(q);
618 assertEquals(1, data.getSelectItems().size());
619 assertEquals("r.name", data.getSelectItems().get(0).toString());
620 TableModel tableModel = new DataSetTableModel(data);
621 assertEquals(3, tableModel.getRowCount());
622
623 q.select(new SelectItem(FunctionType.COUNT, "*", "c"));
624 q.where(new FilterItem(new SelectItem(roleColumn), OperatorType.EQUALS_TO, "founder"));
625 data = dc.executeQuery(q);
626 assertEquals(2, data.getSelectItems().size());
627 assertEquals("r.name", data.getSelectItems().get(0).toString());
628 assertEquals("COUNT(*) AS c", data.getSelectItems().get(1).toString());
629 tableModel = new DataSetTableModel(data);
630 assertEquals(1, tableModel.getRowCount());
631 assertEquals("founder", tableModel.getValueAt(0, 0));
632 assertEquals(2l, tableModel.getValueAt(0, 1));
633
634 q.select(new SelectItem(FunctionType.SUM, table2.getColumn(0)));
635 assertEquals(
636 "SELECT r.name, COUNT(*) AS c, SUM(r.contributor_id) FROM MetaModelSchema.role r WHERE r.name = 'founder' GROUP BY r.name",
637 q.toString());
638 data = dc.executeQuery(q);
639 assertEquals(3, data.getSelectItems().size());
640 assertEquals("r.name", data.getSelectItems().get(0).toString());
641 assertEquals("COUNT(*) AS c", data.getSelectItems().get(1).toString());
642 assertEquals("SUM(r.contributor_id)", data.getSelectItems().get(2).toString());
643 tableModel = new DataSetTableModel(data);
644 assertEquals(1, tableModel.getRowCount());
645 assertEquals("founder", tableModel.getValueAt(0, 0));
646 assertEquals(2l, tableModel.getValueAt(0, 1));
647 assertEquals(7.0, tableModel.getValueAt(0, 2));
648 }
649
650 public void testSimpleHaving() throws Exception {
651 Query q = new Query();
652 q.from(table2, "c");
653 Column roleColumn = table2.getColumnByName(COLUMN_ROLE_ROLE_NAME);
654 Column contributorIdColumn = table2.getColumnByName(COLUMN_ROLE_CONTRIBUTOR_ID);
655
656 q.groupBy(roleColumn);
657 SelectItem countSelectItem = new SelectItem(FunctionType.COUNT, contributorIdColumn).setAlias("my_count");
658 q.select(new SelectItem(roleColumn), countSelectItem);
659 q.having(new FilterItem(countSelectItem, OperatorType.GREATER_THAN, 1));
660 q.orderBy(new OrderByItem(countSelectItem));
661 assertEquals(
662 "SELECT c.name, COUNT(c.contributor_id) AS my_count FROM MetaModelSchema.role c GROUP BY c.name HAVING COUNT(c.contributor_id) > 1 ORDER BY COUNT(c.contributor_id) ASC",
663 q.toString());
664
665 DataSet data = getDataContext().executeQuery(q);
666 assertTrue(data.next());
667 assertEquals("Row[values=[founder, 2]]", data.getRow().toString());
668 assertTrue(data.next());
669 assertEquals("Row[values=[developer, 5]]", data.getRow().toString());
670 assertFalse(data.next());
671 }
672
673 public void testHavingFunctionNotSelected() throws Exception {
674 Query q = new Query();
675 q.from(table2, "c");
676 Column roleColumn = table2.getColumnByName(COLUMN_ROLE_ROLE_NAME);
677 Column contributorIdColumn = table2.getColumnByName(COLUMN_ROLE_CONTRIBUTOR_ID);
678
679 q.groupBy(roleColumn);
680 SelectItem countSelectItem = new SelectItem(FunctionType.COUNT, contributorIdColumn).setAlias("my_count");
681 q.select(new SelectItem(roleColumn));
682 q.having(new FilterItem(countSelectItem, OperatorType.GREATER_THAN, 3));
683 assertEquals("SELECT c.name FROM MetaModelSchema.role c GROUP BY c.name HAVING COUNT(c.contributor_id) > 3",
684 q.toString());
685
686 DataSet data = getDataContext().executeQuery(q);
687 assertTrue(data.next());
688 assertEquals("Row[values=[developer]]", data.getRow().toString());
689 assertFalse(data.next());
690 data.close();
691
692 q.getHavingClause().removeItems();
693 q.having(new FilterItem(SelectItem.getCountAllItem(), OperatorType.GREATER_THAN, 3));
694 assertEquals("SELECT c.name FROM MetaModelSchema.role c GROUP BY c.name HAVING COUNT(*) > 3", q.toString());
695 data = getDataContext().executeQuery(q);
696 assertTrue(data.next());
697 assertEquals("Row[values=[developer]]", data.getRow().toString());
698 assertFalse(data.next());
699 data.close();
700 }
701
702 public void testCompiledQueryParameterInWhereClause() throws Exception {
703 DataContext dc = getDataContext();
704 QueryParameter param1 = new QueryParameter();
705 CompiledQuery compiledQuery =
706 dc.query().from(table1).select("name").where(COLUMN_CONTRIBUTOR_COUNTRY).eq(param1).compile();
707 try {
708 assertEquals(1, compiledQuery.getParameters().size());
709 assertSame(param1, compiledQuery.getParameters().get(0));
710
711 DataSet ds = dc.executeQuery(compiledQuery, "denmark");
712 try {
713 assertTrue(ds.next());
714 assertEquals("Row[values=[kasper]]", ds.getRow().toString());
715 assertTrue(ds.next());
716 assertEquals("Row[values=[asbjorn]]", ds.getRow().toString());
717 assertTrue(ds.next());
718 assertEquals("Row[values=[jesper]]", ds.getRow().toString());
719 assertFalse(ds.next());
720 } finally {
721 ds.close();
722 }
723
724 try {
725 ds = dc.executeQuery(compiledQuery, "canada");
726 assertTrue(ds.next());
727 assertEquals("Row[values=[daniel]]", ds.getRow().toString());
728 assertFalse(ds.next());
729 } finally {
730 ds.close();
731 }
732 } finally {
733 compiledQuery.close();
734 }
735 }
736
737 public void testCompiledQueryParameterInSubQuery() throws Exception {
738 final DataContext dc = getDataContext();
739
740 final QueryParameter param1 = new QueryParameter();
741 final Query subQuery =
742 dc.query().from(table1).select("name").where(COLUMN_CONTRIBUTOR_COUNTRY).eq(param1).toQuery();
743
744 final FromItem subQueryFromItem = new FromItem(subQuery);
745 final Query query = new Query().select(new SelectItem(subQuery.getSelectClause().getItem(0), subQueryFromItem))
746 .from(subQueryFromItem);
747
748 final CompiledQuery compiledQuery = dc.compileQuery(query);
749
750 try {
751 assertEquals(1, compiledQuery.getParameters().size());
752 assertSame(param1, compiledQuery.getParameters().get(0));
753
754 DataSet ds = dc.executeQuery(compiledQuery, "denmark");
755 List<Object[]> objectArrays = ds.toObjectArrays();
756 assertEquals(3, objectArrays.size());
757
758 } finally {
759 compiledQuery.close();
760 }
761 }
762
763 public void testSelectCount() throws Exception {
764 DataContext dc = getDataContext();
765 Query q = new Query();
766 q.from(table1);
767 q.selectCount();
768
769 Row row = MetaModelHelper.executeSingleRowQuery(dc, q);
770 assertEquals("6", row.getValue(0).toString());
771 }
772
773 public void testSimpleSelect() throws Exception {
774 DataContext dc = getDataContext();
775 Query q = new Query();
776 q.from(table1);
777 q.select(table1.getColumns());
778 DataSet dataSet = dc.executeQuery(q);
779 assertTrue(dataSet.next());
780 Row row = dataSet.getRow();
781 assertEquals("Row[values=[1, kasper, denmark]]", row.toString());
782 assertTrue(dataSet.next());
783 assertTrue(dataSet.next());
784 assertTrue(dataSet.next());
785 assertTrue(dataSet.next());
786 assertTrue(dataSet.next());
787 assertFalse(dataSet.next());
788 }
789
790 public void testCarthesianProduct() throws Exception {
791 DataContext dc = getDataContext();
792 Query q = new Query();
793 q.from(table1);
794 q.from(table2);
795 q.select(table1.getColumns());
796 q.select(table2.getColumns());
797 DataSet data = dc.executeQuery(q);
798 assertEquals(table1.getColumnCount() + table2.getColumnCount(), data.getSelectItems().size());
799 for (int i = 0; i < 6 * 8; i++) {
800 assertTrue(data.next());
801 if (i == 0) {
802 assertEquals("Row[values=[1, kasper, denmark, 1, 1, founder]]", data.getRow().toString());
803 } else if (i == 1) {
804 assertEquals("Row[values=[1, kasper, denmark, 1, 1, developer]]", data.getRow().toString());
805 }
806 }
807 assertFalse(data.next());
808 }
809
810 public void testJoinAndFirstRow() throws Exception {
811 DataSet data;
812
813 DataContext dc = getDataContext();
814 Query q = new Query();
815 q.from(table1);
816 q.from(table2);
817 q.select(table1.getColumns());
818 q.select(table2.getColumns());
819 data = dc.executeQuery(q);
820 assertEquals(48, data.toObjectArrays().size());
821
822 q.setFirstRow(3);
823 data = dc.executeQuery(q);
824 assertEquals(46, data.toObjectArrays().size());
825 }
826
827 public void testSimpleWhere() throws Exception {
828 DataContext dc = getDataContext();
829 Query q = new Query();
830 q.from(table1);
831 q.select(table1.getColumns());
832 SelectItem countrySelectItem =
833 q.getSelectClause().getSelectItem(table1.getColumnByName(COLUMN_CONTRIBUTOR_COUNTRY));
834 q.where(new FilterItem(countrySelectItem, OperatorType.EQUALS_TO, "denmark"));
835
836 DataSet data = dc.executeQuery(q);
837 for (int i = 0; i < 3; i++) {
838 assertTrue("Assertion failed at i=" + i, data.next());
839 }
840 assertFalse(data.next());
841 }
842
843 public void testMaxRows() throws Exception {
844 DataContext dc = getDataContext();
845 Query q = new Query();
846 q.from(table1);
847 q.select(table1.getColumns());
848 q.setMaxRows(3);
849 DataSet data1 = dc.executeQuery(q);
850
851 assertTrue(data1.next());
852 assertEquals("Row[values=[1, kasper, denmark]]", data1.getRow().toString());
853 assertTrue(data1.next());
854 assertEquals("Row[values=[2, asbjorn, denmark]]", data1.getRow().toString());
855 assertTrue(data1.next());
856 assertEquals("Row[values=[3, johny, israel]]", data1.getRow().toString());
857
858 assertFalse(data1.next());
859 data1.close();
860
861 q = new Query();
862 q.from(table1);
863 q.select(table1.getColumns());
864 q.setFirstRow(2);
865 q.setMaxRows(2);
866 DataSet data2 = dc.executeQuery(q);
867 assertTrue(data2.next());
868 assertEquals("Row[values=[2, asbjorn, denmark]]", data2.getRow().toString());
869 assertTrue(data2.next());
870 assertEquals("Row[values=[3, johny, israel]]", data2.getRow().toString());
871
872 assertFalse(data2.next());
873 data2.close();
874 }
875
876 public void testCarthesianProductWithWhere() throws Exception {
877 DataContext dc = getDataContext();
878
879 SelectItem s1 = new SelectItem(table1.getColumnByName(COLUMN_CONTRIBUTOR_NAME));
880 SelectItem s2 = new SelectItem(table2.getColumnByName(COLUMN_ROLE_ROLE_NAME));
881 FromItem f1 = new FromItem(table1);
882 FromItem f2 = new FromItem(table2);
883
884 Query q = new Query();
885 q.select(s1);
886 q.select(s2);
887 q.from(f1);
888 q.from(f2);
889 SelectItem s3 = new SelectItem(table1.getColumnByName(COLUMN_CONTRIBUTOR_CONTRIBUTOR_ID));
890 SelectItem s4 = new SelectItem(table2.getColumnByName(COLUMN_ROLE_CONTRIBUTOR_ID));
891 q.where(new FilterItem(s3, OperatorType.EQUALS_TO, s4));
892 assertEquals(
893 "SELECT contributor.name, role.name FROM MetaModelSchema.contributor, MetaModelSchema.role WHERE contributor.contributor_id = role.contributor_id",
894 q.toString());
895
896 DataSet data = dc.executeQuery(q);
897 assertEquals(2, data.getSelectItems().size());
898 assertTrue(data.next());
899 assertEquals("Row[values=[kasper, founder]]", data.getRow().toString());
900 assertTrue(data.next());
901 assertEquals("Row[values=[kasper, developer]]", data.getRow().toString());
902 assertTrue(data.next());
903 assertEquals("Row[values=[kasper, developer]]", data.getRow().toString());
904 assertTrue(data.next());
905 assertEquals("Row[values=[asbjorn, developer]]", data.getRow().toString());
906 assertTrue(data.next());
907 assertEquals("Row[values=[asbjorn, developer]]", data.getRow().toString());
908 assertTrue(data.next());
909 assertEquals("Row[values=[daniel, advisor]]", data.getRow().toString());
910 assertTrue(data.next());
911 assertEquals("Row[values=[sasidhar, developer]]", data.getRow().toString());
912 assertTrue(data.next());
913 assertEquals("Row[values=[jesper, founder]]", data.getRow().toString());
914 assertFalse(data.next());
915 }
916
917 public void testSelectDistinct() throws Exception {
918 // there will be three distinct values in bar column: hello (x2), hi,
919 // howdy
920 MockDataContext dc = new MockDataContext("sch", "tab", "hello");
921
922 Table table = dc.getTableByQualifiedLabel("sch.tab");
923 Query q = dc.query().from(table).select("bar").toQuery();
924 q.getSelectClause().setDistinct(true);
925 q.orderBy(table.getColumnByName("bar"));
926
927 DataSet ds = dc.executeQuery(q);
928 assertTrue(ds.next());
929 assertEquals("Row[values=[hello]]", ds.getRow().toString());
930 assertTrue(ds.next());
931 assertEquals("Row[values=[hi]]", ds.getRow().toString());
932 assertTrue(ds.next());
933 assertEquals("Row[values=[yo]]", ds.getRow().toString());
934 assertFalse(ds.next());
935 }
936
937 public void testSubSelectionAndInnerJoin() throws Exception {
938 DataContext dc = getDataContext();
939
940 SelectItem s1 = new SelectItem(table1.getColumnByName(COLUMN_CONTRIBUTOR_NAME));
941 SelectItem s2 = new SelectItem(table2.getColumnByName(COLUMN_ROLE_ROLE_NAME));
942 FromItem fromItem = new FromItem(JoinType.INNER, table1.getRelationships(table2).iterator().next());
943
944 Query q = new Query();
945 q.select(s1);
946 q.select(s2);
947 q.from(fromItem);
948 assertEquals(
949 "SELECT contributor.name, role.name FROM MetaModelSchema.contributor INNER JOIN MetaModelSchema.role ON contributor.contributor_id = role.contributor_id",
950 q.toString());
951
952 DataSet data = dc.executeQuery(q);
953 assertEquals(2, data.getSelectItems().size());
954 assertTrue(data.next());
955 assertEquals("Row[values=[kasper, founder]]", data.getRow().toString());
956 assertTrue(data.next());
957 assertEquals("Row[values=[kasper, developer]]", data.getRow().toString());
958 assertTrue(data.next());
959 assertEquals("Row[values=[kasper, developer]]", data.getRow().toString());
960 assertTrue(data.next());
961 assertEquals("Row[values=[asbjorn, developer]]", data.getRow().toString());
962 assertTrue(data.next());
963 assertEquals("Row[values=[asbjorn, developer]]", data.getRow().toString());
964 assertTrue(data.next());
965 assertEquals("Row[values=[daniel, advisor]]", data.getRow().toString());
966 assertTrue(data.next());
967 assertEquals("Row[values=[sasidhar, developer]]", data.getRow().toString());
968 assertTrue(data.next());
969 assertEquals("Row[values=[jesper, founder]]", data.getRow().toString());
970 assertFalse(data.next());
971 }
972
973 public void testSubquery() throws Exception {
974 Query q1 = new Query();
975 q1.from(table1);
976 q1.select(table1.getColumns());
977
978 Query q2 = new Query();
979 FromItem fromItem = new FromItem(q1);
980 q2.from(fromItem);
981 SelectItem selectItem = new SelectItem(q1.getSelectClause().getItems().get(1), fromItem);
982 selectItem.setAlias("e");
983 q2.select(selectItem);
984 assertEquals(
985 "SELECT name AS e FROM (SELECT contributor.contributor_id, contributor.name, contributor.country FROM MetaModelSchema.contributor)",
986 q2.toString());
987
988 fromItem.setAlias("c");
989 assertEquals(
990 "SELECT c.name AS e FROM (SELECT contributor.contributor_id, contributor.name, contributor.country FROM MetaModelSchema.contributor) c",
991 q2.toString());
992
993 DataContext dc = getDataContext();
994 DataSet data = dc.executeQuery(q2);
995 assertEquals(1, data.getSelectItems().size());
996 assertTrue(data.next());
997 assertEquals("Row[values=[kasper]]", data.getRow().toString());
998 assertTrue(data.next());
999 assertEquals("Row[values=[asbjorn]]", data.getRow().toString());
1000 assertTrue(data.next());
1001 assertEquals("Row[values=[johny]]", data.getRow().toString());
1002 assertTrue(data.next());
1003 assertEquals("Row[values=[daniel]]", data.getRow().toString());
1004 assertTrue(data.next());
1005 assertEquals("Row[values=[sasidhar]]", data.getRow().toString());
1006 assertTrue(data.next());
1007 assertEquals("Row[values=[jesper]]", data.getRow().toString());
1008 assertFalse(data.next());
1009
1010 // Create a sub-query for a sub-query
1011 Query q3 = new Query();
1012 fromItem = new FromItem(q2);
1013 q3.from(fromItem);
1014 selectItem = new SelectItem(q2.getSelectClause().getItems().get(0), fromItem);
1015 selectItem.setAlias("f");
1016 q3.select(selectItem);
1017 fromItem.setAlias("d");
1018 assertEquals(
1019 "SELECT d.e AS f FROM (SELECT c.name AS e FROM (SELECT contributor.contributor_id, contributor.name, contributor.country FROM MetaModelSchema.contributor) c) d",
1020 q3.toString());
1021 data = dc.executeQuery(q3);
1022 assertEquals(1, data.getSelectItems().size());
1023 assertTrue(data.next());
1024 assertEquals("Row[values=[kasper]]", data.getRow().toString());
1025 assertTrue(data.next());
1026 assertEquals("Row[values=[asbjorn]]", data.getRow().toString());
1027 assertTrue(data.next());
1028 assertEquals("Row[values=[johny]]", data.getRow().toString());
1029 assertTrue(data.next());
1030 assertEquals("Row[values=[daniel]]", data.getRow().toString());
1031 assertTrue(data.next());
1032 assertEquals("Row[values=[sasidhar]]", data.getRow().toString());
1033 assertTrue(data.next());
1034 assertEquals("Row[values=[jesper]]", data.getRow().toString());
1035 assertFalse(data.next());
1036 }
1037
1038 public void testOrderBy() throws Exception {
1039 Query q = new Query();
1040 q.from(new FromItem(table1).setAlias("c"));
1041 q.select(table1.getColumns());
1042 OrderByItem countryOrderBy = new OrderByItem(q.getSelectClause().getItem(2), Direction.DESC);
1043 OrderByItem nameOrderBy = new OrderByItem(q.getSelectClause().getItem(1));
1044 q.orderBy(countryOrderBy, nameOrderBy);
1045
1046 assertEquals(
1047 "SELECT c.contributor_id, c.name, c.country FROM MetaModelSchema.contributor c ORDER BY c.country DESC, c.name ASC",
1048 q.toString());
1049
1050 DataSet data = getDataContext().executeQuery(q);
1051 assertTrue(data.next());
1052 assertEquals("Row[values=[5, sasidhar, unknown]]", data.getRow().toString());
1053 assertTrue(data.next());
1054 assertEquals("Row[values=[3, johny, israel]]", data.getRow().toString());
1055 assertTrue(data.next());
1056 assertEquals("Row[values=[2, asbjorn, denmark]]", data.getRow().toString());
1057 assertTrue(data.next());
1058 assertEquals("Row[values=[6, jesper, denmark]]", data.getRow().toString());
1059 assertTrue(data.next());
1060 assertEquals("Row[values=[1, kasper, denmark]]", data.getRow().toString());
1061 assertTrue(data.next());
1062 assertEquals("Row[values=[4, daniel, canada]]", data.getRow().toString());
1063 assertFalse(data.next());
1064 }
1065
1066 public void testExecuteCount() throws Exception {
1067 QueryPostprocessDataContext dc = new QueryPostprocessDataContext() {
1068 @Override
1069 protected DataSet materializeMainSchemaTable(Table table, List<Column> columns, int maxRows) {
1070 throw new UnsupportedOperationException();
1071 }
1072
1073 @Override
1074 protected Number executeCountQuery(Table table, List<FilterItem> whereItems,
1075 boolean functionApproximationAllowed) {
1076 return 1337;
1077 }
1078
1079 @Override
1080 protected String getMainSchemaName() throws MetaModelException {
1081 return "sch";
1082 }
1083
1084 @Override
1085 protected MutableSchema getMainSchema() throws MetaModelException {
1086 MutableSchema schema = new MutableSchema(getMainSchemaName());
1087 MutableTable table = new MutableTable("tabl").setSchema(schema);
1088 return schema.addTable(table.addColumn(new MutableColumn("col").setTable(table)));
1089 }
1090 };
1091
1092 DataSet ds = dc.query().from("sch.tabl").selectCount().execute();
1093 assertTrue(ds.next());
1094 assertEquals("Row[values=[1337]]", ds.getRow().toString());
1095 assertFalse(ds.next());
1096 }
1097
1098 public void testExecutePrimaryKeyLookupQuery() throws Exception {
1099 QueryPostprocessDataContext dc = new QueryPostprocessDataContext() {
1100 @Override
1101 protected DataSet materializeMainSchemaTable(Table table, List<Column> columns, int maxRows) {
1102 throw new UnsupportedAddressTypeException();
1103 }
1104
1105 @Override
1106 protected Number executeCountQuery(Table table, List<FilterItem> whereItems,
1107 boolean functionApproximationAllowed) {
1108 return null;
1109 }
1110
1111 @Override
1112 protected String getMainSchemaName() throws MetaModelException {
1113 return "sch";
1114 }
1115
1116 @Override
1117 protected Row executePrimaryKeyLookupQuery(Table table, List<SelectItem> selectItems,
1118 Column primaryKeyColumn, Object keyValue) {
1119 assertEquals("foo", keyValue);
1120 return new DefaultRow(new SimpleDataSetHeader(selectItems), new Object[] { "hello world" });
1121 }
1122
1123 @Override
1124 protected MutableSchema getMainSchema() throws MetaModelException {
1125 MutableSchema schema = new MutableSchema(getMainSchemaName());
1126 MutableTable table = new MutableTable("tabl").setSchema(schema);
1127 table.addColumn(new MutableColumn("col1").setTable(table).setPrimaryKey(true));
1128 table.addColumn(new MutableColumn("col2").setTable(table));
1129 return schema.addTable(table);
1130 }
1131 };
1132
1133 DataSet result = dc.query().from("tabl").select("col2").where("col1").eq("foo").execute();
1134 assertTrue(result.next());
1135 assertEquals("Row[values=[hello world]]", result.getRow().toString());
1136 assertFalse(result.next());
1137 }
1138
1139 public void testQueryWithDotInTableName() throws Exception {
1140 MockDataContext dc = new MockDataContext("folder", "file.csv", "foo");
1141
1142 Table table = dc.getDefaultSchema().getTableByName("file.csv");
1143 assertNotNull(table);
1144
1145 Query q = dc.parseQuery("SELECT foo FROM file.csv WHERE \r\nfoo='bar'");
1146 assertNotNull(q);
1147
1148 FilterItem item = q.getWhereClause().getItem(0);
1149 assertNull(item.getExpression());
1150
1151 assertEquals("file.csv.foo = 'bar'", item.toSql());
1152 }
1153
1154 public void testQueryWithMultipleColumnsInExpression() {
1155 Query query1 = new Query().from(table1).select("contributor_id,name");
1156 DataSet set = getDataContext().executeQuery(query1);
1157 assertEquals(true, set.next());
1158 assertEquals("Row[values=[1, kasper]]", set.getRow().toString());
1159 Query query2 = new Query().from(table1).select("Greatest(1,2,3),max(contributer_id)", true);
1160 assertEquals("SELECT Greatest(1,2,3), MAX(contributer_id) FROM MetaModelSchema.contributor", query2.toString());
1161 Query query3 = new Query().from(table1).select("*,count(*)");
1162 assertEquals("SELECT contributor.contributor_id, contributor.name, contributor.country, COUNT(*)"
1163 + " FROM MetaModelSchema.contributor", query3.toString());
1164 }
1165
1166 public void testOrderOnAggregationValue() throws Exception {
1167 MockDataContext dc = new MockDataContext("sch", "tab", "hello");
1168
1169 Query query = dc.parseQuery("SELECT MAX(baz) AS X FROM tab GROUP BY baz ORDER BY X");
1170
1171 DataSet ds = dc.executeQuery(query);
1172
1173 List<String> values = new ArrayList<String>();
1174
1175 while (ds.next()) {
1176 final String value = (String) ds.getRow().getValue(0);
1177 values.add(value);
1178 }
1179
1180 ds.close();
1181
1182 // this should be alphabetically sorted
1183 assertEquals("[hello, world]", values.toString());
1184 }
1185
1186 }