Ejemplo n.º 1
0
  public void testSplitJoinQuery() throws Exception {
    Connection con = getTestDbConnection();
    DataContext dc = new JdbcDataContext(con);
    Schema schema = dc.getDefaultSchema();
    Table productsTable = schema.getTableByName("PRODUCTS");
    Relationship[] relationships = productsTable.getRelationships();
    Relationship relationship = relationships[0];
    assertEquals(
        "Relationship[primaryTable=PRODUCTS,primaryColumns=[PRODUCTCODE],foreignTable=ORDERFACT,foreignColumns=[PRODUCTCODE]]",
        relationship.toString());

    Query q =
        new Query()
            .from(new FromItem(JoinType.LEFT, relationship))
            .select(relationship.getForeignColumns())
            .select(relationship.getPrimaryColumns());
    assertEquals(
        "SELECT _ORDERFACT_._PRODUCTCODE_, _PRODUCTS_._PRODUCTCODE_ FROM PUBLIC._PRODUCTS_ LEFT JOIN PUBLIC._ORDERFACT_ ON _PRODUCTS_._PRODUCTCODE_ = _ORDERFACT_._PRODUCTCODE_",
        q.toString().replace('\"', '_'));

    QuerySplitter qs = new QuerySplitter(dc, q);
    assertEquals(2997, qs.getRowCount());

    List<Query> splitQueries = qs.setMaxRows(1500).splitQuery();
    assertSameCount(dc, qs, splitQueries);
    assertEquals(3, splitQueries.size());
    assertEquals("[1415, 902, 680]", Arrays.toString(getCounts(dc, splitQueries)));
  }
  @Test
  public void testGroupByQuery() throws Exception {
    Table table = dataContext.getDefaultSchema().getTableByName(peopleIndexType);

    Query q = new Query();
    q.from(table);
    q.groupBy(table.getColumnByName("gender"));
    q.select(
        new SelectItem(table.getColumnByName("gender")),
        new SelectItem(FunctionType.MAX, table.getColumnByName("age")),
        new SelectItem(FunctionType.MIN, table.getColumnByName("age")),
        new SelectItem(FunctionType.COUNT, "*", "total"),
        new SelectItem(FunctionType.MIN, table.getColumnByName("id")).setAlias("firstId"));
    q.orderBy("gender");
    DataSet data = dataContext.executeQuery(q);
    assertEquals(
        "[peopletype.gender, MAX(peopletype.age), MIN(peopletype.age), COUNT(*) AS total, MIN(peopletype.id) AS firstId]",
        Arrays.toString(data.getSelectItems()));

    assertTrue(data.next());
    assertEquals("Row[values=[female, 20, 17, 5, 5]]", data.getRow().toString());
    assertTrue(data.next());
    assertEquals("Row[values=[male, 19, 17, 4, 1]]", data.getRow().toString());
    assertFalse(data.next());
  }
Ejemplo n.º 3
0
  public void testGroupByQuery() throws Exception {
    Connection con = getTestDbConnection();
    DataContext dc = new JdbcDataContext(con);
    Schema schema = dc.getDefaultSchema();
    Table employeesTable = schema.getTableByName("EMPLOYEES");
    Table orderDetailsTable = schema.getTableByName("ORDERDETAILS");
    Query q = new Query().from(employeesTable, "e").from(orderDetailsTable, "c");
    q.select(orderDetailsTable.getColumns()[0])
        .select(new SelectItem(FunctionType.MAX, employeesTable.getColumns()[0]));
    q.groupBy(orderDetailsTable.getColumns()[0]);
    assertEquals(
        "SELECT c._ORDERNUMBER_, MAX(e._EMPLOYEENUMBER_) FROM PUBLIC._EMPLOYEES_ e, PUBLIC._ORDERDETAILS_ c GROUP BY c._ORDERNUMBER_",
        q.toString().replace('\"', '_'));

    QuerySplitter qs = new QuerySplitter(dc, q);
    assertEquals(326, qs.getRowCount());

    List<Query> splitQueries = qs.setMaxRows(250).splitQuery();

    assertEquals(
        "[SELECT c._ORDERNUMBER_, MAX(e._EMPLOYEENUMBER_) FROM PUBLIC._EMPLOYEES_ e, PUBLIC._ORDERDETAILS_ c WHERE (c._ORDERNUMBER_ < 10262 OR c._ORDERNUMBER_ IS NULL) GROUP BY c._ORDERNUMBER_, SELECT c._ORDERNUMBER_, MAX(e._EMPLOYEENUMBER_) FROM PUBLIC._EMPLOYEES_ e, PUBLIC._ORDERDETAILS_ c WHERE (c._ORDERNUMBER_ > 10262 OR c._ORDERNUMBER_ = 10262) GROUP BY c._ORDERNUMBER_]",
        Arrays.toString(splitQueries.toArray()).replace('\"', '_'));
    assertSameCount(dc, qs, splitQueries);
    assertEquals(2, splitQueries.size());
    assertEquals("[162, 164]", Arrays.toString(getCounts(dc, splitQueries)));

    DataSet data = qs.executeQueries();
    int count = 0;
    while (data.next()) {
      if (count == 2) {
        assertEquals("Row[values=[10102, 1702]]", data.getRow().toString());
      }
      count++;
    }
    data.close();
    assertEquals(326, count);
  }
Ejemplo n.º 4
0
  public void testSimpleQuerySplit() throws Exception {
    Connection con = getTestDbConnection();
    DataContext dc = new JdbcDataContext(con);
    Schema schema = dc.getSchemaByName("PUBLIC");
    Table employeesTable = schema.getTableByName("EMPLOYEES");
    Table customersTable = schema.getTableByName("CUSTOMERS");
    Query q = new Query().from(employeesTable, "e").from(customersTable, "c");
    q.select(employeesTable.getColumns()[0], customersTable.getColumns()[0]);
    assertEquals(
        "SELECT e._EMPLOYEENUMBER_, c._CUSTOMERNUMBER_ FROM PUBLIC._EMPLOYEES_ e, PUBLIC._CUSTOMERS_ c",
        q.toString().replace('\"', '_'));

    QuerySplitter qs = new QuerySplitter(dc, q);
    long rowCount = qs.getRowCount();
    assertEquals(2806, rowCount);

    qs.setMaxRows(1000);
    List<Query> splitQueries = qs.splitQuery();

    assertEquals("[793, 610, 366, 714, 323]", Arrays.toString(getCounts(dc, splitQueries)));

    assertEquals(
        "[SELECT e._EMPLOYEENUMBER_, c._CUSTOMERNUMBER_ FROM PUBLIC._EMPLOYEES_ e, PUBLIC._CUSTOMERS_ c WHERE (c._CUSTOMERNUMBER_ < 299 OR c._CUSTOMERNUMBER_ IS NULL) AND (e._EMPLOYEENUMBER_ < 1352 OR e._EMPLOYEENUMBER_ IS NULL), SELECT e._EMPLOYEENUMBER_, c._CUSTOMERNUMBER_ FROM PUBLIC._EMPLOYEES_ e, PUBLIC._CUSTOMERS_ c WHERE (c._CUSTOMERNUMBER_ < 299 OR c._CUSTOMERNUMBER_ IS NULL) AND (e._EMPLOYEENUMBER_ > 1352 OR e._EMPLOYEENUMBER_ = 1352), SELECT e._EMPLOYEENUMBER_, c._CUSTOMERNUMBER_ FROM PUBLIC._EMPLOYEES_ e, PUBLIC._CUSTOMERS_ c WHERE (c._CUSTOMERNUMBER_ > 299 OR c._CUSTOMERNUMBER_ = 299) AND (e._REPORTSTO_ < 1072 OR e._REPORTSTO_ IS NULL), SELECT e._EMPLOYEENUMBER_, c._CUSTOMERNUMBER_ FROM PUBLIC._EMPLOYEES_ e, PUBLIC._CUSTOMERS_ c WHERE (c._CUSTOMERNUMBER_ > 299 OR c._CUSTOMERNUMBER_ = 299) AND (e._REPORTSTO_ > 1072 OR e._REPORTSTO_ = 1072) AND (c._SALESREPEMPLOYEENUMBER_ < 1433 OR c._SALESREPEMPLOYEENUMBER_ IS NULL), SELECT e._EMPLOYEENUMBER_, c._CUSTOMERNUMBER_ FROM PUBLIC._EMPLOYEES_ e, PUBLIC._CUSTOMERS_ c WHERE (c._CUSTOMERNUMBER_ > 299 OR c._CUSTOMERNUMBER_ = 299) AND (e._REPORTSTO_ > 1072 OR e._REPORTSTO_ = 1072) AND (c._SALESREPEMPLOYEENUMBER_ > 1433 OR c._SALESREPEMPLOYEENUMBER_ = 1433)]",
        Arrays.toString(splitQueries.toArray()).replace('\"', '_'));
    assertSameCount(dc, qs, splitQueries);
    assertEquals(5, splitQueries.size());

    splitQueries = qs.setMaxRows(300).splitQuery();
    assertSameCount(dc, qs, splitQueries);
    assertEquals(
        "[299, 221, 170, 299, 276, 253, 102, 289, 253, 138, 368, 138]",
        Arrays.toString(getCounts(dc, splitQueries)));

    splitQueries = qs.setMaxRows(5000).splitQuery();
    assertEquals(1, splitQueries.size());
    assertSame(q, splitQueries.get(0));
  }
Ejemplo n.º 5
0
  public void testSplitSubQuery() throws Exception {
    Connection con = getTestDbConnection();
    DataContext dc = new JdbcDataContext(con);
    Schema schema = dc.getDefaultSchema();
    Table employeesTable = schema.getTableByName("EMPLOYEES");
    Table customersTable = schema.getTableByName("CUSTOMERS");
    Query sq = new Query().from(employeesTable, "e").from(customersTable, "c");
    SelectItem empSelectItem = new SelectItem(employeesTable.getColumns()[0]);
    SelectItem custSelectItem = new SelectItem(customersTable.getColumns()[0]);
    sq.select(empSelectItem, custSelectItem);
    assertEquals(
        "SELECT e._EMPLOYEENUMBER_, c._CUSTOMERNUMBER_ FROM PUBLIC._EMPLOYEES_ e, PUBLIC._CUSTOMERS_ c",
        sq.toString().replace('\"', '_'));
    Query q = new Query();
    FromItem sqItem = new FromItem(sq).setAlias("sq");

    custSelectItem.setAlias("c_num");
    empSelectItem.setAlias("e_num");

    q.from(sqItem);
    q.select(new SelectItem(custSelectItem, sqItem), new SelectItem(empSelectItem, sqItem));
    assertEquals(
        "SELECT sq.c_num, sq.e_num FROM (SELECT e._EMPLOYEENUMBER_ AS e_num, c._CUSTOMERNUMBER_ AS c_num FROM PUBLIC._EMPLOYEES_ e, PUBLIC._CUSTOMERS_ c) sq",
        q.toString().replace('\"', '_'));

    QuerySplitter qs = new QuerySplitter(dc, q);
    assertEquals(2806, qs.getRowCount());

    List<Query> splitQueries = qs.setMaxRows(1000).splitQuery();
    assertSameCount(dc, qs, splitQueries);
    assertEquals(5, splitQueries.size());
    assertEquals("[793, 610, 366, 714, 323]", Arrays.toString(getCounts(dc, splitQueries)));

    splitQueries = qs.setMaxRows(2000).splitQuery();
    assertSameCount(dc, qs, splitQueries);
    assertEquals(2, splitQueries.size());
    assertEquals("[1403, 1403]", Arrays.toString(getCounts(dc, splitQueries)));

    DataSet data = qs.executeQueries();
    int count = 0;
    while (data.next()) {
      if (count == 2) {
        assertEquals("Row[values=[114, 1002]]", data.getRow().toString());
      }
      count++;
    }
    data.close();
    assertEquals(2806, count);
  }
  public String rewriteQuery(Query query) {
    query = beforeRewrite(query);

    final StringBuilder sb = new StringBuilder();
    sb.append(rewriteSelectClause(query, query.getSelectClause()));
    sb.append(rewriteFromClause(query, query.getFromClause()));
    sb.append(rewriteWhereClause(query, query.getWhereClause()));
    sb.append(rewriteGroupByClause(query, query.getGroupByClause()));
    sb.append(rewriteHavingClause(query, query.getHavingClause()));
    sb.append(rewriteOrderByClause(query, query.getOrderByClause()));
    return sb.toString();
  }
Ejemplo n.º 7
0
  @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);
    }
  }
 public SatisfiedOrderByBuilderImpl(
     FunctionType function, Column column, Query query, GroupedQueryBuilder queryBuilder) {
   super(queryBuilder);
   orderByitem = new OrderByItem(new SelectItem(function, column));
   query.orderBy(orderByitem);
 }