@Override protected Number executeCountQuery( Table table, List<FilterItem> whereItems, boolean functionApproximationAllowed) { final String query; try { final StringBuilder sb = new StringBuilder(); sb.append("SELECT COUNT() FROM "); sb.append(table.getName()); boolean firstWhere = true; for (FilterItem filterItem : whereItems) { if (firstWhere) { sb.append(" WHERE "); firstWhere = false; } else { sb.append(" AND "); } rewriteFilterItem(sb, filterItem); } query = sb.toString(); } catch (UnsupportedOperationException e) { logger.debug("Failed to rewrite count query, falling back to client side counting", e); // unable to rewrite to SOQL, counting will be done client side. return null; } final QueryResult queryResult = executeSoqlQuery(query); assert queryResult.isDone(); return queryResult.getSize(); }
@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())); }
@Override protected DataSet materializeMainSchemaTable(Table table, Column[] columns, int maxRows) { final StringBuilder sb = new StringBuilder(); sb.append("SELECT "); for (int i = 0; i < columns.length; i++) { if (i != 0) { sb.append(','); } sb.append(columns[i].getName()); } sb.append(" FROM "); sb.append(table.getName()); if (maxRows > 0) { sb.append(" LIMIT " + maxRows); } final QueryResult queryResult = executeSoqlQuery(sb.toString()); return new SalesforceDataSet(columns, queryResult, _connection); }
@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); } }