public static void main(String[] args) throws IOException {
    DatabaseCatalog tableMap = DatabaseCatalog.getInstance();
    tableMap.autogetTableInfo();
    System.out.println(tableMap.getTableLocated("Sailors"));
    Schema schema = Schema.getInstance();
    schema.getSchemaInfor("/Users/hanwenwang/Desktop/samples/input/db/schema.txt");

    try {
      CCJSqlParser parser =
          new CCJSqlParser(new FileReader("/Users/hanwenwang/Desktop/queries 2.sql"));

      Statement statement;
      while ((statement = parser.Statement()) != null) {

        Select select = (Select) statement;
        System.out.println("Select body is " + select.getSelectBody());
        PlainSelect plainSelect = (PlainSelect) select.getSelectBody();
        System.out.println(plainSelect.getSelectItems().toString());

        ScanOperator scanOperator = new ScanOperator(plainSelect.getFromItem().toString());
        SelectOperator selectOperator = new SelectOperator(scanOperator, plainSelect.getWhere());

        ProjectOperator projectOperator =
            new ProjectOperator(selectOperator, plainSelect.getSelectItems());
        projectOperator.dump();
      }
    } catch (Exception e) {
      System.err.println("Exception occurred during parsing");
      e.printStackTrace();
    }
  }
示例#2
0
 @Override
 public void visit(PlainSelect plainSelect) {
   firstRun = true;
   counter = 0;
   aliases.clear();
   for (SelectItem item : plainSelect.getSelectItems()) {
     item.accept(this);
   }
   firstRun = false;
   for (SelectItem item : plainSelect.getSelectItems()) {
     item.accept(this);
   }
 }
示例#3
0
  public static void main(String[] args) throws IOException {
    // get the information of table address
    DatabaseCatalog databaseCatalog = DatabaseCatalog.getInstance();
    databaseCatalog.autogetTableInfo();
    Schema schema = Schema.getInstance();
    schema.getSchemaInfor("samples/input/db/schema.txt");
    Aliases aliases = Aliases.getInstance();
    //		aliases.addaliase("S", "Sailors");
    //		ScanOperator scanOperator = new ScanOperator("S");
    //		scanOperator.dump();

    try {
      CCJSqlParser parser =
          new CCJSqlParser(new FileReader("/Users/hanwenwang/Desktop/queries.sql"));
      Statement statement;
      while ((statement = parser.Statement()) != null) {
        Select select = (Select) statement;
        System.out.println("Select body is " + select.getSelectBody());
        PlainSelect plainSelect = (PlainSelect) select.getSelectBody();
        String aliasName = ((Table) plainSelect.getFromItem()).getAlias();
        String tableName = ((Table) plainSelect.getFromItem()).getName();
        aliases.addaliase(aliasName, tableName);
        ScanOperator scanOperator1 = new ScanOperator(aliasName);
        //				scanOperator1.dump();
        ProjectOperator projectOperator =
            new ProjectOperator(scanOperator1, plainSelect.getSelectItems());
        projectOperator.dump();
      }
    } catch (Exception e) {
      System.err.println("Exception occurred during parsing");
      e.printStackTrace();
    }
  }
  public void testSelectItems() throws JSQLParserException {
    String statement =
        "SELECT myid AS MYID, mycol, tab.*, schema.tab.*, mytab.mycol2, myschema.mytab.mycol, myschema.mytab.* FROM mytable WHERE mytable.col = 9";
    PlainSelect plainSelect =
        (PlainSelect) ((Select) parserManager.parse(new StringReader(statement))).getSelectBody();

    assertEquals("MYID", ((SelectExpressionItem) plainSelect.getSelectItems().get(0)).getAlias());
    assertEquals(
        "mycol",
        ((Column) ((SelectExpressionItem) plainSelect.getSelectItems().get(1)).getExpression())
            .getColumnName());
    assertEquals(
        "tab", ((AllTableColumns) plainSelect.getSelectItems().get(2)).getTable().getName());
    assertEquals(
        "schema",
        ((AllTableColumns) plainSelect.getSelectItems().get(3)).getTable().getSchemaName());
    assertEquals(
        "schema.tab",
        ((AllTableColumns) plainSelect.getSelectItems().get(3)).getTable().getWholeTableName());
    assertEquals(
        "mytab.mycol2",
        ((Column) ((SelectExpressionItem) plainSelect.getSelectItems().get(4)).getExpression())
            .getWholeColumnName());
    assertEquals(
        "myschema.mytab.mycol",
        ((Column) ((SelectExpressionItem) plainSelect.getSelectItems().get(5)).getExpression())
            .getWholeColumnName());
    assertEquals(
        "myschema.mytab",
        ((AllTableColumns) plainSelect.getSelectItems().get(6)).getTable().getWholeTableName());
    assertEquals(statement, "" + plainSelect);

    statement =
        "SELECT myid AS MYID, (SELECT MAX(ID) AS myid2 FROM mytable2) AS myalias FROM mytable WHERE mytable.col = 9";
    plainSelect =
        (PlainSelect) ((Select) parserManager.parse(new StringReader(statement))).getSelectBody();
    assertEquals(
        "myalias", ((SelectExpressionItem) plainSelect.getSelectItems().get(1)).getAlias());
    assertEquals(statement, "" + plainSelect);

    statement = "SELECT (myid + myid2) AS MYID FROM mytable WHERE mytable.col = 9";
    plainSelect =
        (PlainSelect) ((Select) parserManager.parse(new StringReader(statement))).getSelectBody();
    assertEquals("MYID", ((SelectExpressionItem) plainSelect.getSelectItems().get(0)).getAlias());
    assertEquals(statement, "" + plainSelect);
  }
示例#5
0
  @Override
  public void visit(PlainSelect plainSelect) {
    log.debug("visit(PlainSelect) " + plainSelect);
    enterPlainSelect(plainSelect);

    this.visitingPart = VisitingPart.SELECT_ITEM;
    List<SelectItem> selectItems = plainSelect.getSelectItems();
    if (selectItems != null) for (SelectItem s : selectItems) s.accept(this.expressionNavigator);

    log.debug("visit(PlainSelect) done");
    leavePlainSelect();
  }
  public static SeQueryInfo parse(ISession session, PlainSelect select)
      throws SeException, IOException {
    String[] columns = null;
    String[] tables = null;
    String where = null;
    String orderAndOrGroupByClause = null;

    if (LOGGER.isLoggable(Level.FINER)) {
      LOGGER.finer("building SeQueryInfo to reflect " + select);
    }

    // obtain needed SeQueryInfo components

    columns = getColumns(session, select.getSelectItems());
    tables = getTables(select.getFromItems());

    Expression whereClause = select.getWhere();
    if (whereClause != null) {
      where = whereClause.toString();
    }

    if (select.getGroupByColumnReferences() != null
        && select.getGroupByColumnReferences().size() > 0) {
      String gb = PlainSelect.getFormatedList(select.getGroupByColumnReferences(), " GROUP BY ");
      orderAndOrGroupByClause = gb;
    }
    if (select.getOrderByElements() != null && select.getOrderByElements().size() > 0) {
      String ob = PlainSelect.orderByToString(select.getOrderByElements());
      if (orderAndOrGroupByClause == null) {
        orderAndOrGroupByClause = "";
      }
      orderAndOrGroupByClause += " " + ob;
    }

    // build SeQueryInfo
    SeQueryInfo qinfo = new SeQueryInfo();
    qinfo.setColumns(columns);

    SeSqlConstruct sqlConstruct = new SeSqlConstruct();
    sqlConstruct.setTables(tables);
    if (where != null) {
      sqlConstruct.setWhere(where);
    }

    qinfo.setConstruct(sqlConstruct);

    if (orderAndOrGroupByClause != null) {
      qinfo.setByClause(orderAndOrGroupByClause);
    }

    return qinfo;
  }
 @SuppressWarnings("unchecked")
 @Override
 public void visit(PlainSelect select) {
   if (!CollectionUtils.isEmpty(select.getGroupByColumnReferences())
       || !CollectionUtils.isEmpty(select.getJoins())) {
     isSimple = false;
   } else {
     select.getFromItem().accept(this);
     for (SelectItem item : (List<SelectItem>) select.getSelectItems()) {
       item.accept(this);
     }
   }
 }
  public void testFunctions() throws JSQLParserException {
    String statement = "SELECT MAX(id) as max FROM mytable WHERE mytable.col = 9";
    PlainSelect select =
        (PlainSelect) ((Select) parserManager.parse(new StringReader(statement))).getSelectBody();
    assertEquals("max", ((SelectExpressionItem) select.getSelectItems().get(0)).getAlias());
    assertEquals(statement.toUpperCase(), select.toString().toUpperCase());

    statement = "SELECT MAX(id), AVG(pro) as myavg FROM mytable WHERE mytable.col = 9 GROUP BY pro";
    select =
        (PlainSelect) ((Select) parserManager.parse(new StringReader(statement))).getSelectBody();
    assertEquals("myavg", ((SelectExpressionItem) select.getSelectItems().get(1)).getAlias());
    assertEquals(statement.toUpperCase(), select.toString().toUpperCase());

    statement = "SELECT MAX(a, b, c), COUNT(*), D FROM tab1 GROUP BY D";
    PlainSelect plainSelect =
        (PlainSelect) ((Select) parserManager.parse(new StringReader(statement))).getSelectBody();
    Function fun =
        (Function) ((SelectExpressionItem) plainSelect.getSelectItems().get(0)).getExpression();
    assertEquals("MAX", fun.getName());
    assertEquals("b", ((Column) fun.getParameters().getExpressions().get(1)).getWholeColumnName());
    assertTrue(
        ((Function) ((SelectExpressionItem) plainSelect.getSelectItems().get(1)).getExpression())
            .isAllColumns());
    assertEquals(statement.toUpperCase(), plainSelect.toString().toUpperCase());

    statement = "SELECT {fn MAX(a, b, c)}, COUNT(*), D FROM tab1 GROUP BY D";
    plainSelect =
        (PlainSelect) ((Select) parserManager.parse(new StringReader(statement))).getSelectBody();
    fun = (Function) ((SelectExpressionItem) plainSelect.getSelectItems().get(0)).getExpression();
    assertTrue(fun.isEscaped());
    assertEquals("MAX", fun.getName());
    assertEquals("b", ((Column) fun.getParameters().getExpressions().get(1)).getWholeColumnName());
    assertTrue(
        ((Function) ((SelectExpressionItem) plainSelect.getSelectItems().get(1)).getExpression())
            .isAllColumns());
    assertEquals(statement.toUpperCase(), plainSelect.toString().toUpperCase());

    statement = "SELECT ab.MAX(a, b, c), cd.COUNT(*), D FROM tab1 GROUP BY D";
    plainSelect =
        (PlainSelect) ((Select) parserManager.parse(new StringReader(statement))).getSelectBody();
    fun = (Function) ((SelectExpressionItem) plainSelect.getSelectItems().get(0)).getExpression();
    assertEquals("ab.MAX", fun.getName());
    assertEquals("b", ((Column) fun.getParameters().getExpressions().get(1)).getWholeColumnName());
    fun = (Function) ((SelectExpressionItem) plainSelect.getSelectItems().get(1)).getExpression();
    assertEquals("cd.COUNT", fun.getName());
    assertTrue(fun.isAllColumns());
    assertEquals(statement.toUpperCase(), plainSelect.toString().toUpperCase());
  }
 public void testDistinct() throws JSQLParserException {
   String statement = "SELECT DISTINCT ON (myid) myid, mycol FROM mytable WHERE mytable.col = 9";
   PlainSelect plainSelect =
       (PlainSelect) ((Select) parserManager.parse(new StringReader(statement))).getSelectBody();
   assertEquals(
       "myid",
       ((Column)
               ((SelectExpressionItem) plainSelect.getDistinct().getOnSelectItems().get(0))
                   .getExpression())
           .getColumnName());
   assertEquals(
       "mycol",
       ((Column) ((SelectExpressionItem) plainSelect.getSelectItems().get(1)).getExpression())
           .getColumnName());
   assertEquals(statement.toUpperCase(), plainSelect.toString().toUpperCase());
 }
示例#10
0
  /** @brief The following functions override functions of the interfaces. */
  public void visit(PlainSelect plainSelect) throws Exception {
    if (plainSelect.getFromItem() != null) {
      if (plainSelect.getFromItem().getAlias() != null) {
        this.aliasTableNameList.add(plainSelect.getFromItem().getAlias().getName());
      }
      plainSelect.getFromItem().accept(this);
    }

    if (plainSelect.getJoins() != null) {
      for (Iterator joinsIt = plainSelect.getJoins().iterator(); joinsIt.hasNext(); ) {
        Join join = (Join) joinsIt.next();
        if (join.getRightItem().getAlias() != null) {
          this.aliasTableNameList.add(join.getRightItem().getAlias().getName());
        }
        if (join.getOnExpression() != null) {
          join.getOnExpression().accept(this);
        }
        join.getRightItem().accept(this);
      }
    }

    // Select selectItem From fromItem, joinItem Where whereClause.
    if (plainSelect.getSelectItems() != null) {
      for (SelectItem selectItem : plainSelect.getSelectItems()) {
        selectItem.accept(this);
      }
    }

    if (plainSelect.getWhere() != null) {
      plainSelect.getWhere().accept(this);
    }

    if (plainSelect.getGroupByColumnReferences() != null) {
      for (Iterator groupByIt = plainSelect.getGroupByColumnReferences().iterator();
          groupByIt.hasNext(); ) {
        Expression groupBy = (Expression) groupByIt.next();
        groupBy.accept(this);
      }
    }

    if (plainSelect.getClusterByElements() != null) {
      for (Iterator clusterByit = plainSelect.getClusterByElements().iterator();
          clusterByit.hasNext(); ) {
        ClusterByElement clusterByElement = (ClusterByElement) clusterByit.next();
        visit(clusterByElement);
      }
    }

    if (plainSelect.getDistributeByElements() != null) {
      for (Iterator distributeByIt = plainSelect.getDistributeByElements().iterator();
          distributeByIt.hasNext(); ) {
        DistributeByElement distributeByElement = (DistributeByElement) distributeByIt.next();
        visit(distributeByElement);
      }
    }

    if (plainSelect.getOrderByElements() != null) {
      for (Iterator orderByIt = plainSelect.getOrderByElements().iterator();
          orderByIt.hasNext(); ) {
        OrderByElement orderByElement = (OrderByElement) orderByIt.next();
        orderByElement.accept(this);
      }
    }

    if (plainSelect.getSortByElements() != null) {
      for (Iterator sortByIt = plainSelect.getSortByElements().iterator(); sortByIt.hasNext(); ) {
        SortByElement sortByElement = (SortByElement) sortByIt.next();
        visit(sortByElement);
      }
    }

    if (plainSelect.getHaving() != null) {
      plainSelect.getHaving().accept(this);
    }
  }