/**
* Test that a correct query is returned when a column that is being aggregated is removed from
* the query.
*
* @throws Exception
*/
public void testUnselectColumnWithAggregate() throws Exception {
Query q = new QueryImpl(new StubDatabaseMapping());
Item col1 = new StringItem("Col 1");
Item col2 = new StringItem("Col 2");
Container table = new TestingItemContainer("Test container");
table.addItem(col1);
table.addItem(col2);
q.addTable(table);
q.selectItem(col1);
q.selectItem(col2);
String selectRegex =
"select(.|\n)*"
+ col1.getName().toLowerCase()
+ "(.|\n)*"
+ col2.getName().toLowerCase()
+ "(.|\n)*from(.|\n)*";
String query = q.generateQuery().toLowerCase();
assertTrue(query.matches(selectRegex));
String groupingRegex =
"select(.|\n)*"
+ col1.getName().toLowerCase()
+ "(.|\n)*"
+ col2.getName().toLowerCase()
+ "(.|\n)*from(.|\n)*group by(.|\n)*"
+ "(.|\n)*"
+ col1.getName().toLowerCase()
+ "(.|\n)*";
String summingRegex =
"select(.|\n)*sum(.|\n)*"
+ col1.getName().toLowerCase()
+ "(.|\n)*"
+ col2.getName().toLowerCase()
+ "(.|\n)*from(.|\n)*";
q.setGroupingEnabled(true);
col1.setGroupBy(SQLGroupFunction.SUM);
query = q.generateQuery().toLowerCase();
assertFalse(query.matches(groupingRegex));
assertTrue(query.matches(summingRegex));
String failRegex =
"select(.|\n)*"
+ col1.getName().toLowerCase()
+ "(.|\n)*"
+ col2.getName().toLowerCase()
+ "(.|\n)*from(.|\n)*";
String col2Regex = "select(.|\n)*" + col2.getName().toLowerCase() + "(.|\n)*";
q.unselectItem(col1);
query = q.generateQuery().toLowerCase();
System.out.println(query);
assertFalse(query.matches(failRegex));
assertTrue(query.matches(col2Regex));
}
/** Tests adding an item with its order by set will return a query with the order by in it. */
public void testAddingItemWithOrder() throws Exception {
Query q = new QueryImpl(new StubDatabaseMapping());
Item col1 = new StringItem("Col 1");
Item col2 = new StringItem("Col 2");
Container table = new TestingItemContainer("Test container");
table.addItem(col1);
table.addItem(col2);
q.addTable(table);
q.selectItem(col1);
q.selectItem(col2);
q.orderColumn(col1, OrderByArgument.ASC);
String query = q.generateQuery().toLowerCase();
System.out.println(query);
String selectAndSortRegex =
"select(.|\n)*"
+ col1.getName().toLowerCase()
+ "(.|\n)*"
+ col2.getName().toLowerCase()
+ "(.|\n)*order by(.|\n)*"
+ col1.getName().toLowerCase()
+ "(.|\n)*";
assertTrue(query.matches(selectAndSortRegex));
assertEquals(0, col1.getOrderByOrdering().intValue());
Item col3 = new StringItem("Col 3");
q.selectItem(col3);
q.orderColumn(col3, OrderByArgument.DESC);
table.addItem(col3);
query = q.generateQuery().toLowerCase();
String colAddedWithSort =
"select(.|\n)*"
+ col1.getName().toLowerCase()
+ "(.|\n)*"
+ col2.getName().toLowerCase()
+ "(.|\n)*"
+ col3.getName().toLowerCase()
+ "(.|\n)*order by(.|\n)*"
+ col1.getName().toLowerCase()
+ "(.|\n)*"
+ col3.getName().toLowerCase()
+ "(.|\n)*";
query = q.generateQuery().toLowerCase();
System.out.println(query);
assertTrue(query.matches(colAddedWithSort));
assertEquals(0, col1.getOrderByOrdering().intValue());
assertEquals(1, col3.getOrderByOrdering().intValue());
assertEquals(col1, q.getOrderByList().get(0));
assertEquals(col3, q.getOrderByList().get(1));
}
/**
* Tests that removing a column that had a sort order from the query by un-selecting it removes it
* from the generated query and then selecting it adds it back in.
*/
public void testReselectColWithSorting() throws Exception {
Query q = new QueryImpl(new StubDatabaseMapping());
Item col1 = new StringItem("Col 1");
Item col2 = new StringItem("Col 2");
Container table = new TestingItemContainer("Test container");
table.addItem(col1);
table.addItem(col2);
q.addTable(table);
q.selectItem(col1);
q.selectItem(col2);
q.orderColumn(col1, OrderByArgument.ASC);
String query = q.generateQuery().toLowerCase();
System.out.println(query);
String selectAndSortRegex =
"select(.|\n)*"
+ col1.getName().toLowerCase()
+ "(.|\n)*"
+ col2.getName().toLowerCase()
+ "(.|\n)*order by(.|\n)*"
+ col1.getName().toLowerCase()
+ "(.|\n)*";
String selectNoSortRegex =
"select(.|\n)*"
+ col1.getName().toLowerCase()
+ "(.|\n)*"
+ col2.getName().toLowerCase()
+ "(.|\n)*";
assertTrue(query.matches(selectAndSortRegex));
q.unselectItem(col1);
query = q.generateQuery().toLowerCase();
assertFalse(query.matches(selectAndSortRegex));
assertFalse(query.matches(selectNoSortRegex));
System.out.println(query);
assertTrue(query.matches("select(.|\n)*" + col2.getName().toLowerCase() + "(.|\n)*"));
String reselectWithSortRegex =
"select(.|\n)*"
+ col2.getName().toLowerCase()
+ "(.|\n)*"
+ col1.getName().toLowerCase()
+ "(.|\n)*order by(.|\n)*"
+ col1.getName().toLowerCase()
+ "(.|\n)*";
q.selectItem(col1);
query = q.generateQuery().toLowerCase();
assertTrue(query.matches(reselectWithSortRegex));
}
/**
* Test to ensure a column that is selected is added to the query and if it is un-selected it is
* removed.
*/
public void testSelectColumn() throws Exception {
Query q = new QueryImpl(new StubDatabaseMapping());
Item col1 = new StringItem("Col 1");
Item col2 = new StringItem("Col 2");
Container table = new TestingItemContainer("Test container");
table.addItem(col1);
table.addItem(col2);
q.addTable(table);
q.selectItem(col1);
q.selectItem(col2);
assertEquals(2, q.getSelectedColumns().size());
assertTrue(q.getSelectedColumns().contains(col1));
assertTrue(q.getSelectedColumns().contains(col2));
String selectRegex =
"select(.|\n)*"
+ col1.getName().toLowerCase()
+ "(.|\n)*"
+ col2.getName().toLowerCase()
+ "(.|\n)*from(.|\n)*";
String query = q.generateQuery().toLowerCase();
assertTrue(query.matches(selectRegex));
String unselectRegex = "select(.|\n)*" + col2.getName().toLowerCase() + "(.|\n)*from(.|\n)*";
q.unselectItem(col1);
query = q.generateQuery().toLowerCase();
assertFalse(query.matches(selectRegex));
assertTrue(query.matches(unselectRegex));
String reselectRegex =
"select(.|\n)*"
+ col2.getName().toLowerCase()
+ "(.|\n)*"
+ col1.getName().toLowerCase()
+ "(.|\n)*from(.|\n)*";
q.selectItem(col1);
query = q.generateQuery().toLowerCase();
assertFalse(query.matches(selectRegex));
assertTrue(query.matches(reselectRegex));
}
/**
* This is a test to confirm that a query string can be created from a Query object if the
* database is missing. This is for cases where the database may not be able to be connected to.
*/
public void testQueryConstructionWithMissingDB() throws Exception {
Query query = new QueryImpl(new StubDatabaseMapping());
Container container = new ItemContainer("Test_Table");
Item item = new StringItem("column");
container.addItem(item);
query.addTable(container);
query.selectItem(item);
assertEquals(1, query.getSelectedColumns().size());
assertTrue(query.getSelectedColumns().contains(item));
String queryString = query.generateQuery();
queryString = queryString.toLowerCase();
System.out.println(queryString);
assertTrue(
queryString
.toLowerCase()
.matches(
"select(.|\r|\n)*test_table.column(.|\r|\n)*from(.|\r|\n)*test_table(.|\r|\n)*test_table(.|\r|\n)*"));
}
