Examples with SchemaRepository com.alibaba.druid.sql.repository.SchemaRepository used on opensource projects

Example 31 with SchemaRepository

use of com.alibaba.druid.sql.repository.SchemaRepository in project druid by alibaba.

the class Resolve_AllColumn_Test method test_resolve.

public void test_resolve() throws Exception {
    SchemaRepository repository = new SchemaRepository(DbType.mysql);
    repository.acceptDDL("create table t_emp(emp_id bigint, name varchar(20));");
    SQLStatement stmt = SQLUtils.parseSingleMysqlStatement("select * from t_emp");
    repository.resolve(stmt, SchemaResolveVisitor.Option.ResolveAllColumn);
    assertEquals("SELECT emp_id, name\n" + "FROM t_emp", stmt.toString());
    SQLSelectQueryBlock queryBlock = ((SQLSelectStatement) stmt).getSelect().getQueryBlock();
    SQLIdentifierExpr expr = (SQLIdentifierExpr) queryBlock.getSelectList().get(0).getExpr();
    assertNotNull(expr.getResolvedColumn());
    new SQLASTVisitorAdapter() {

        public boolean visit(SQLSelectQueryBlock queryBlock) {
            final List<SQLSelectItem> selectList = queryBlock.getSelectList();
            for (int i = 0; i < selectList.size(); i++) {
                final SQLSelectItem selectItem = selectList.get(i);
                final SQLExpr expr = selectItem.getExpr();
                if (expr instanceof SQLAllColumnExpr) {
                } else if (expr instanceof SQLPropertyExpr && ((SQLPropertyExpr) expr).getName().equals("*")) {
                }
            }
            return true;
        }
    };
}

Example 32 with SchemaRepository

use of com.alibaba.druid.sql.repository.SchemaRepository in project druid by alibaba.

the class CTERewriteTest method test_for_rewrite.

public void test_for_rewrite() throws Exception {
    String sql = "WITH x AS (\n" + "        SELECT o.open_id AS open_id\n" + "        FROM purchases_order o\n" + "        WHERE DATE(o.order_start_date) >= IFNULL(NULL, current_date())\n" + "            AND DATE(o.order_start_date) <= IFNULL(NULL, current_date())\n" + "            AND o.order_status <= 10\n" + "        ORDER BY 1\n" + "    ),\n" + "    c AS (\n" + "        SELECT COUNT(*) AS orders\n" + "        FROM x\n" + "        GROUP BY x.open_id\n" + "        ORDER BY 1\n" + "    ),\n" + "    y AS (\n" + "        SELECT SUM(c.orders) AS total\n" + "        FROM c\n" + "        ORDER BY 1\n" + "    ),\n" + "    z AS (\n" + "        SELECT COUNT(*) AS single\n" + "        FROM x\n" + "        WHERE NOT EXISTS (\n" + "            SELECT 1\n" + "            FROM purchases_order o, c\n" + "            WHERE c.orders <= 1\n" + "                AND o.open_id = x.open_id\n" + "                AND DATE(o.order_start_date) < IFNULL(NULL, current_date())\n" + "            ORDER BY 1\n" + "        )\n" + "        ORDER BY 1\n" + "    )\n" + "SELECT ROUND((y.total - z.single) / y.total, 2) AS value\n" + "FROM y, z\n" + "ORDER BY 1";
    SQLSelectStatement stmt = (SQLSelectStatement) SQLUtils.parseSingleMysqlStatement(sql);
    SchemaRepository repository = new SchemaRepository(DbType.mysql);
    repository.resolve(stmt);
    MyVisitor v = new MyVisitor();
    stmt.accept(v);
    stmt.getSelect().setWithSubQuery(null);
    assertEquals("SELECT ROUND((y.total - z.single) / y.total, 2) AS value\n" + "FROM (\n" + "\tSELECT SUM(c.orders) AS total\n" + "\tFROM (\n" + "\t\tSELECT COUNT(*) AS orders\n" + "\t\tFROM (\n" + "\t\t\tSELECT o.open_id AS open_id\n" + "\t\t\tFROM purchases_order o\n" + "\t\t\tWHERE DATE(o.order_start_date) >= IFNULL(NULL, current_date())\n" + "\t\t\t\tAND DATE(o.order_start_date) <= IFNULL(NULL, current_date())\n" + "\t\t\t\tAND o.order_status <= 10\n" + "\t\t\tORDER BY 1\n" + "\t\t) x\n" + "\t\tGROUP BY x.open_id\n" + "\t\tORDER BY 1\n" + "\t) c\n" + "\tORDER BY 1\n" + ") y, (\n" + "\t\tSELECT COUNT(*) AS single\n" + "\t\tFROM (\n" + "\t\t\tSELECT o.open_id AS open_id\n" + "\t\t\tFROM purchases_order o\n" + "\t\t\tWHERE DATE(o.order_start_date) >= IFNULL(NULL, current_date())\n" + "\t\t\t\tAND DATE(o.order_start_date) <= IFNULL(NULL, current_date())\n" + "\t\t\t\tAND o.order_status <= 10\n" + "\t\t\tORDER BY 1\n" + "\t\t) x\n" + "\t\tWHERE NOT EXISTS (\n" + "\t\t\tSELECT 1\n" + "\t\t\tFROM purchases_order o, (\n" + "\t\t\t\t\tSELECT COUNT(*) AS orders\n" + "\t\t\t\t\tFROM (\n" + "\t\t\t\t\t\tSELECT o.open_id AS open_id\n" + "\t\t\t\t\t\tFROM purchases_order o\n" + "\t\t\t\t\t\tWHERE DATE(o.order_start_date) >= IFNULL(NULL, current_date())\n" + "\t\t\t\t\t\t\tAND DATE(o.order_start_date) <= IFNULL(NULL, current_date())\n" + "\t\t\t\t\t\t\tAND o.order_status <= 10\n" + "\t\t\t\t\t\tORDER BY 1\n" + "\t\t\t\t\t) x\n" + "\t\t\t\t\tGROUP BY x.open_id\n" + "\t\t\t\t\tORDER BY 1\n" + "\t\t\t\t) c\n" + "\t\t\tWHERE c.orders <= 1\n" + "\t\t\t\tAND o.open_id = x.open_id\n" + "\t\t\t\tAND DATE(o.order_start_date) < IFNULL(NULL, current_date())\n" + "\t\t\tORDER BY 1\n" + "\t\t)\n" + "\t\tORDER BY 1\n" + "\t) z\n" + "ORDER BY 1", stmt.toString());
}

Example 33 with SchemaRepository

use of com.alibaba.druid.sql.repository.SchemaRepository in project druid by alibaba.

the class MySqlSelectTest_23 method test_0.

public void test_0() throws Exception {
    SchemaRepository repository = new SchemaRepository(DbType.ads);
    repository.acceptDDL("CREATE TABLE linxi_test.test_realtime1 (\n" + "  id bigint NOT NULL COMMENT '',\n" + "  int_test1 bigint NOT NULL COMMENT '',\n" + "  boolean_test boolean COMMENT '',\n" + "  byte_test tinyint COMMENT '',\n" + "  short_test smallint COMMENT '',\n" + "  int_test2 int COMMENT '',\n" + "  float_test float COMMENT '',\n" + "  string_test varchar COMMENT '',\n" + "  date_test date COMMENT '',\n" + "  time_test time COMMENT '',\n" + "  timestamp_test timestamp COMMENT '',\n" + "  double_test double COMMENT '',\n" + "  INDEX id_index HASH (string_test),\n" + "  PRIMARY KEY (id,int_test1,int_test2)\n" + ")\n" + "PARTITION BY HASH KEY (id) PARTITION NUM 10\n" + "TABLEGROUP group2\n" + "OPTIONS (UPDATETYPE='realtime')\n" + "COMMENT ''");
    // String sql = "INSERT INTO test_realtime1(id, int_test1, int_test2, string_test, date_test, time_test, timestamp_test)\n"
    // + "VALUES (2, 2, 2, 'string', '2017-1-3', '12:00:00', '2017-1-3 12:00:00');";
    // String sql = "delete test_realtime1 where id ='cailijun'";
    String sql = "SELECT  date_test, time_test, timestamp_test from test_realtime1 where timestamp_test = \"2017-01-02 12:00:00\"";
    AdsStatementParser parser = new AdsStatementParser(sql);
    List<SQLStatement> statementList = parser.parseStatementList();
    SQLStatement statemen = statementList.get(0);
    repository.resolve(statemen);
    Assert.assertEquals(1, statementList.size());
    MySqlSchemaStatVisitor visitor = new MySqlSchemaStatVisitor();
    statemen.accept(visitor);
    // System.out.println("Tables : " + visitor.getTables());
    System.out.println("fields : " + visitor.getColumns());
    // System.out.println("coditions : " + visitor.getConditions());
    // System.out.println("orderBy : " + visitor.getOrderByColumns());
    Assert.assertEquals(1, visitor.getTables().size());
    Assert.assertEquals(3, visitor.getColumns().size());
    Assert.assertEquals(1, visitor.getConditions().size());
    Assert.assertEquals(0, visitor.getOrderByColumns().size());
}

Example 34 with SchemaRepository

use of com.alibaba.druid.sql.repository.SchemaRepository in project druid by alibaba.

the class MysqlResolveTest method test_1.

@Test
public void test_1() {
    SchemaRepository repository = new SchemaRepository(DbType.mysql);
    repository.console("create table t_emp(emp_id bigint, name varchar(20));");
    repository.console("create table t_org(org_id bigint, name varchar(20));");
    String sql = "delete from t_emp where name = '12'";
    List<SQLStatement> stmtList = SQLUtils.parseStatements(sql, DbType.mysql);
    assertEquals(1, stmtList.size());
    SQLDeleteStatement stmt = (SQLDeleteStatement) stmtList.get(0);
    repository.resolve(stmt);
}