@Test
public void testNumberIsHandledAsNumber() throws Exception {
Table table = dataContext.getDefaultSchema().getTableByName(peopleIndexType);
Column column = table.getColumnByName("age");
ColumnType type = column.getType();
assertEquals(ColumnType.BIGINT, type);
DataSet dataSet = dataContext.query().from(table).select(column).execute();
while (dataSet.next()) {
Object value = dataSet.getRow().getValue(column);
assertTrue(
"Got class: " + value.getClass() + ", expected Number (or subclass)",
value instanceof Number);
}
}
@Test
public void testDateIsHandledAsDate() throws Exception {
Table table = dataContext.getDefaultSchema().getTableByName("tweet1");
Column column = table.getColumnByName("postDate");
ColumnType type = column.getType();
assertEquals(ColumnType.DATE, type);
DataSet dataSet = dataContext.query().from(table).select(column).execute();
while (dataSet.next()) {
Object value = dataSet.getRow().getValue(column);
assertTrue(
"Got class: " + value.getClass() + ", expected Date (or subclass)",
value instanceof Date);
}
}
@Test
public void testCreateTableInsertQueryAndDrop() throws Exception {
final Schema schema = dataContext.getDefaultSchema();
final CreateTable createTable = new CreateTable(schema, "testCreateTable");
createTable.withColumn("foo").ofType(ColumnType.STRING);
createTable.withColumn("bar").ofType(ColumnType.NUMBER);
dataContext.executeUpdate(createTable);
final Table table = schema.getTableByName("testCreateTable");
assertEquals(
"[" + ElasticSearchDataContext.FIELD_ID + ", foo, bar]",
Arrays.toString(table.getColumnNames()));
final Column fooColumn = table.getColumnByName("foo");
final Column idColumn = table.getPrimaryKeys()[0];
assertEquals(
"Column[name=_id,columnNumber=0,type=STRING,nullable=null,nativeType=null,columnSize=null]",
idColumn.toString());
dataContext.executeUpdate(
new UpdateScript() {
@Override
public void run(UpdateCallback callback) {
callback.insertInto(table).value("foo", "hello").value("bar", 42).execute();
callback.insertInto(table).value("foo", "world").value("bar", 43).execute();
}
});
dataContext.refreshSchemas();
try (DataSet ds = dataContext.query().from(table).selectAll().orderBy("bar").execute()) {
assertTrue(ds.next());
assertEquals("hello", ds.getRow().getValue(fooColumn).toString());
assertNotNull(ds.getRow().getValue(idColumn));
assertTrue(ds.next());
assertEquals("world", ds.getRow().getValue(fooColumn).toString());
assertNotNull(ds.getRow().getValue(idColumn));
assertFalse(ds.next());
}
dataContext.executeUpdate(new DropTable(table));
dataContext.refreshSchemas();
assertNull(dataContext.getTableByQualifiedLabel(table.getName()));
}
private boolean nextInternal() {
if (_reader == null) {
return false;
}
final String[] csvValues;
try {
csvValues = _reader.readNext();
} catch (IOException e) {
throw new IllegalStateException("Exception reading from file", e);
}
if (csvValues == null) {
close();
return false;
}
final int size = getHeader().size();
final Object[] rowValues = new Object[size];
for (int i = 0; i < size; i++) {
Column column = getHeader().getSelectItem(i).getColumn();
int columnNumber = column.getColumnNumber();
if (columnNumber < csvValues.length) {
rowValues[i] = csvValues[columnNumber];
} else {
// Ticket #125: Missing values should be enterpreted as
// null.
rowValues[i] = null;
}
}
_row = new DefaultRow(getHeader(), rowValues);
if (_failOnInconsistentRowLength) {
_rowNumber++;
if (_columnsInTable != csvValues.length) {
throw new InconsistentRowLengthException(_columnsInTable, _row, csvValues, _rowNumber);
}
}
return true;
}
public void testProductTable() throws Exception {
Schema schema = dc.getDefaultSchema();
assertEquals("developers.mdb", schema.getName());
Table table = schema.getTableByName("product");
assertEquals("[id, name, version, founder_developer]", Arrays.toString(table.getColumnNames()));
Column idCol = table.getColumnByName("id");
assertEquals(
"Column[name=id,columnNumber=0,type=INTEGER,nullable=null,nativeType=LONG,columnSize=4]",
idCol.toString());
Column nameCol = table.getColumnByName("name");
assertEquals(
"Column[name=name,columnNumber=1,type=VARCHAR,nullable=null,nativeType=TEXT,columnSize=100]",
nameCol.toString());
Column versionCol = table.getColumnByName("version");
assertEquals(
"Column[name=version,columnNumber=2,type=INTEGER,nullable=null,nativeType=LONG,columnSize=4]",
versionCol.toString());
Column founderCol = table.getColumnByName("founder_developer");
assertEquals(
"Column[name=founder_developer,columnNumber=3,type=INTEGER,nullable=null,nativeType=LONG,columnSize=4]",
founderCol.toString());
DataSet ds;
ds = dc.executeQuery(new Query().select(nameCol, versionCol, founderCol).from(table));
assertTrue(ds.next());
assertEquals("Anthons Algorithms", ds.getRow().getValue(nameCol).toString());
assertEquals(11, ds.getRow().getValue(versionCol));
assertEquals(1, ds.getRow().getValue(founderCol));
assertTrue(ds.next());
assertEquals("Barbaras Basic Bundle", ds.getRow().getValue(nameCol).toString());
assertEquals(2, ds.getRow().getValue(versionCol));
assertEquals(2, ds.getRow().getValue(founderCol));
assertFalse(ds.next());
ds.close();
ds = dc.query().from(table).selectCount().execute();
assertTrue(ds.next());
assertEquals("Row[values=[2]]", ds.getRow().toString());
assertFalse(ds.next());
ds.close();
}
public void testDeveloperTable() throws Exception {
Schema schema = dc.getDefaultSchema();
assertEquals("developers.mdb", schema.getName());
assertEquals("[developer, product]", Arrays.toString(schema.getTableNames()));
Table table = schema.getTableByName("developer");
assertEquals(
"[id, name, email, male, developer_since]", Arrays.toString(table.getColumnNames()));
Column[] primaryKeys = table.getPrimaryKeys();
assertEquals(
"[Column[name=id,columnNumber=0,type=INTEGER,nullable=null,nativeType=LONG,columnSize=4]]",
Arrays.toString(primaryKeys));
Column nameCol = table.getColumnByName("name");
assertEquals(
"Column[name=name,columnNumber=1,type=VARCHAR,nullable=null,nativeType=TEXT,columnSize=100]",
nameCol.toString());
Column maleCol = table.getColumnByName("male");
assertEquals(
"Column[name=male,columnNumber=3,type=BOOLEAN,nullable=null,nativeType=BOOLEAN,columnSize=1]",
maleCol.toString());
Column developerSinceCol = table.getColumnByName("developer_since");
assertEquals(
"Column[name=developer_since,columnNumber=4,type=TIMESTAMP,nullable=null,nativeType=SHORT_DATE_TIME,columnSize=8]",
developerSinceCol.toString());
DataSet ds =
dc.executeQuery(new Query().select(nameCol, maleCol, developerSinceCol).from(table));
while (ds.next()) {
Row row = ds.getRow();
assertEquals(3, row.getValues().length);
Object value = row.getValue(0);
assertEquals(String.class, value.getClass());
value = row.getValue(1);
assertEquals(Boolean.class, value.getClass());
value = row.getValue(2);
assertTrue(value instanceof Date);
}
}
protected static void rewriteFilterItem(StringBuilder sb, FilterItem filterItem)
throws UnsupportedOperationException {
if (filterItem.isCompoundFilter()) {
FilterItem[] childrend = filterItem.getChildItems();
boolean firstChild = true;
sb.append('(');
for (FilterItem child : childrend) {
if (firstChild) {
firstChild = false;
} else {
sb.append(' ');
sb.append(filterItem.getLogicalOperator().toString());
sb.append(' ');
}
rewriteFilterItem(sb, child);
}
sb.append(')');
return;
}
final SelectItem selectItem = filterItem.getSelectItem();
validateSoqlSupportedSelectItem(selectItem);
final Column column = selectItem.getColumn();
sb.append(column.getName());
sb.append(' ');
final OperatorType operator = filterItem.getOperator();
if (OperatorType.IN.equals(operator)) {
throw new UnsupportedOperationException("IN operator not supported: " + filterItem);
}
sb.append(operator.toSql());
sb.append(' ');
final Object operand = filterItem.getOperand();
if (operand == null) {
sb.append("null");
} else if (operand instanceof String) {
sb.append('\'');
String str = operand.toString();
str = str.replaceAll("\'", "\\\\'");
str = str.replaceAll("\"", "\\\\\"");
str = str.replaceAll("\r", "\\\\r");
str = str.replaceAll("\n", "\\\\n");
str = str.replaceAll("\t", "\\\\t");
sb.append(str);
sb.append('\'');
} else if (operand instanceof Number) {
sb.append(operand);
} else if (operand instanceof Date) {
final SimpleDateFormat dateFormat;
ColumnType expectedColumnType = selectItem.getExpectedColumnType();
if (expectedColumnType == ColumnType.DATE) {
// note: we don't apply the timezone for DATE fields, since they
// don't contain time-of-day information.
dateFormat = new SimpleDateFormat(SOQL_DATE_FORMAT_OUT);
} else if (expectedColumnType == ColumnType.TIME) {
dateFormat = new SimpleDateFormat(SOQL_TIME_FORMAT_OUT, Locale.ENGLISH);
dateFormat.setTimeZone(SOQL_TIMEZONE);
} else {
dateFormat = new SimpleDateFormat(SOQL_DATE_TIME_FORMAT_OUT, Locale.ENGLISH);
dateFormat.setTimeZone(SOQL_TIMEZONE);
}
String str = dateFormat.format((Date) operand);
logger.debug("Date '{}' formatted as: {}", operand, str);
sb.append(str);
} else if (operand instanceof Column) {
sb.append(((Column) operand).getName());
} else if (operand instanceof SelectItem) {
SelectItem operandSelectItem = (SelectItem) operand;
validateSoqlSupportedSelectItem(operandSelectItem);
sb.append(operandSelectItem.getColumn().getName());
} else {
throw new UnsupportedOperationException("Unsupported operand: " + operand);
}
}
@Override
public DataSet executeQuery(Query query) {
final List<FromItem> fromItems = query.getFromClause().getItems();
if (fromItems.size() != 1) {
// not a simple SELECT ... FROM [table] ... query, we need to use
// query post processing.
return super.executeQuery(query);
}
final Table table = fromItems.get(0).getTable();
if (table == null) {
return super.executeQuery(query);
}
if (!query.getGroupByClause().isEmpty()) {
return super.executeQuery(query);
}
if (!query.getHavingClause().isEmpty()) {
return super.executeQuery(query);
}
final List<SelectItem> selectItems = query.getSelectClause().getItems();
final StringBuilder sb = new StringBuilder();
try {
sb.append("SELECT ");
int i = 0;
final Column[] columns = new Column[selectItems.size()];
for (SelectItem selectItem : selectItems) {
validateSoqlSupportedSelectItem(selectItem);
columns[i] = selectItem.getColumn();
if (i != 0) {
sb.append(", ");
}
sb.append(columns[i].getName());
i++;
}
sb.append(" FROM ");
sb.append(table.getName());
boolean firstWhere = true;
for (FilterItem filterItem : query.getWhereClause().getItems()) {
if (firstWhere) {
sb.append(" WHERE ");
firstWhere = false;
} else {
sb.append(" AND ");
}
rewriteFilterItem(sb, filterItem);
}
i = 0;
final List<OrderByItem> items = query.getOrderByClause().getItems();
for (OrderByItem orderByItem : items) {
if (i == 0) {
sb.append(" ORDER BY ");
} else {
sb.append(", ");
}
final SelectItem selectItem = orderByItem.getSelectItem();
validateSoqlSupportedSelectItem(selectItem);
final Column column = selectItem.getColumn();
sb.append(column.getName());
sb.append(' ');
sb.append(orderByItem.getDirection());
i++;
}
final Integer firstRow = query.getFirstRow();
final Integer maxRows = query.getMaxRows();
if (maxRows != null && maxRows > 0) {
if (firstRow != null) {
// add first row / offset to avoid missing some records.
sb.append(" LIMIT " + (maxRows + firstRow - 1));
} else {
sb.append(" LIMIT " + maxRows);
}
}
final QueryResult result = executeSoqlQuery(sb.toString());
DataSet dataSet = new SalesforceDataSet(columns, result, _connection);
if (firstRow != null) {
// OFFSET is still only a developer preview feature of SFDC. See
// http://www.salesforce.com/us/developer/docs/api/Content/sforce_api_calls_soql_select_offset.htm
dataSet = new FirstRowDataSet(dataSet, firstRow.intValue());
}
return dataSet;
} catch (UnsupportedOperationException e) {
logger.debug(
"Failed to rewrite query to SOQL, falling back to regular query post-processing", e);
return super.executeQuery(query);
}
}
