Examples with Column com.alibaba.druid.stat.TableStat.Column used on opensource projects

Example 86 with Column

use of com.alibaba.druid.stat.TableStat.Column in project druid by alibaba.

the class MySqlCreateViewTest method test_0.

public void test_0() throws Exception {
    String sql = //
    "CREATE OR REPLACE VIEW view_name AS\n" + //
    "SELECT fname\n" + //
    "FROM table_name\n" + "WHERE fid = ?";
    MySqlStatementParser parser = new MySqlStatementParser(sql);
    List<SQLStatement> statementList = parser.parseStatementList();
    SQLCreateViewStatement stmt = (SQLCreateViewStatement) statementList.get(0);
    //        print(statementList);
    Assert.assertEquals(1, statementList.size());
    MySqlSchemaStatVisitor visitor = new MySqlSchemaStatVisitor();
    stmt.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(2, visitor.getColumns().size());
    Assert.assertEquals(1, visitor.getConditions().size());
    Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("table_name")));
    Assert.assertTrue(visitor.getColumns().contains(new Column("table_name", "fid")));
    Assert.assertTrue(visitor.getColumns().contains(new Column("table_name", "fname")));
}

Example 87 with Column

use of com.alibaba.druid.stat.TableStat.Column in project druid by alibaba.

the class MySqlCreateViewTest1 method test_0.

public void test_0() throws Exception {
    String sql = "CREATE ALGORITHM=UNDEFINED DEFINER=root@localhost SQL SECURITY DEFINER VIEW view_audit_enroll AS " + "SELECT a.enroll_id AS 'enrollId', case when ((SELECT audit FROM actvty_audit WHERE enroll_id = a.enroll_id AND rankjurisdiction = 1) > 0) then \"县站已审核\" else NULL end AS 'countyAudit', case when ((SELECT audit FROM actvty_audit WHERE enroll_id = a.enroll_id AND rankjurisdiction = 2) > 0) then \"市馆已审核\" else NULL end AS 'cityAudit', case when ((SELECT audit FROM actvty_audit WHERE enroll_id = a.enroll_id AND rankjurisdiction = 3) > 0) then \"省馆已审核\" else NULL end AS 'provinceAudit' FROM actvty_audit a GROUP BY a.enroll_id";
    MySqlStatementParser parser = new MySqlStatementParser(sql);
    List<SQLStatement> statementList = parser.parseStatementList();
    SQLCreateViewStatement stmt = (SQLCreateViewStatement) statementList.get(0);
    //        print(statementList);
    Assert.assertEquals(1, statementList.size());
    MySqlSchemaStatVisitor visitor = new MySqlSchemaStatVisitor();
    stmt.accept(visitor);
    Assert.assertEquals("CREATE ALGORITHM = UNDEFINED\n" + "\tDEFINER = root\n" + "\tSQL SECURITY = DEFINER\n" + "\tVIEW view_audit_enroll\n" + "AS\n" + "SELECT a.enroll_id AS 'enrollId', CASE WHEN (\n" + "\t\tSELECT audit\n" + "\t\tFROM actvty_audit\n" + "\t\tWHERE enroll_id = a.enroll_id\n" + "\t\t\tAND rankjurisdiction = 1\n" + "\t\t) > 0 THEN '县站已审核' ELSE NULL END AS 'countyAudit', CASE WHEN (\n" + "\t\tSELECT audit\n" + "\t\tFROM actvty_audit\n" + "\t\tWHERE enroll_id = a.enroll_id\n" + "\t\t\tAND rankjurisdiction = 2\n" + "\t\t) > 0 THEN '市馆已审核' ELSE NULL END AS 'cityAudit', CASE WHEN (\n" + "\t\tSELECT audit\n" + "\t\tFROM actvty_audit\n" + "\t\tWHERE enroll_id = a.enroll_id\n" + "\t\t\tAND rankjurisdiction = 3\n" + "\t\t) > 0 THEN '省馆已审核' ELSE NULL END AS 'provinceAudit'\n" + "FROM actvty_audit a\n" + //
    "GROUP BY a.enroll_id", SQLUtils.toMySqlString(stmt));
    Assert.assertEquals("create algorithm = UNDEFINED\n" + "\tdefiner = root\n" + "\tsql security = DEFINER\n" + "\tview view_audit_enroll\n" + "as\n" + "select a.enroll_id as 'enrollId', case when (\n" + "\t\tselect audit\n" + "\t\tfrom actvty_audit\n" + "\t\twhere enroll_id = a.enroll_id\n" + "\t\t\tand rankjurisdiction = 1\n" + "\t\t) > 0 then '县站已审核' else null end as 'countyAudit', case when (\n" + "\t\tselect audit\n" + "\t\tfrom actvty_audit\n" + "\t\twhere enroll_id = a.enroll_id\n" + "\t\t\tand rankjurisdiction = 2\n" + "\t\t) > 0 then '市馆已审核' else null end as 'cityAudit', case when (\n" + "\t\tselect audit\n" + "\t\tfrom actvty_audit\n" + "\t\twhere enroll_id = a.enroll_id\n" + "\t\t\tand rankjurisdiction = 3\n" + "\t\t) > 0 then '省馆已审核' else null end as 'provinceAudit'\n" + "from actvty_audit a\n" + //
    "group by a.enroll_id", SQLUtils.toMySqlString(stmt, SQLUtils.DEFAULT_LCASE_FORMAT_OPTION));
    //        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(2, visitor.getConditions().size());
    Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("actvty_audit")));
    Assert.assertTrue(visitor.getColumns().contains(new Column("actvty_audit", "audit")));
    Assert.assertTrue(visitor.getColumns().contains(new Column("actvty_audit", "enroll_id")));
}

Example 88 with Column

use of com.alibaba.druid.stat.TableStat.Column in project druid by alibaba.

the class MySqlCreateTableTest7 method test_0.

public void test_0() throws Exception {
    String sql = //
    "CREATE TABLE Orders\n" + //
    "(\n" + //
    "O_Id int NOT NULL,\n" + //
    "OrderNo int NOT NULL,\n" + //
    "Id_P int,\n" + //
    "PRIMARY KEY (O_Id),\n" + //
    "FOREIGN KEY (Id_P) REFERENCES Persons(Id_P)\n" + ")";
    MySqlStatementParser parser = new MySqlStatementParser(sql);
    List<SQLStatement> statementList = parser.parseStatementList();
    MySqlCreateTableStatement stmt = (MySqlCreateTableStatement) statementList.get(0);
    //        print(statementList);
    Assert.assertEquals(1, statementList.size());
    MySqlSchemaStatVisitor visitor = new MySqlSchemaStatVisitor();
    stmt.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(2, visitor.getTables().size());
    Assert.assertEquals(4, visitor.getColumns().size());
    Assert.assertEquals(0, visitor.getConditions().size());
    Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("Orders")));
    Assert.assertTrue(visitor.getColumns().contains(new Column("Orders", "O_Id")));
    Assert.assertTrue(visitor.getColumns().contains(new Column("Orders", "OrderNo")));
    Assert.assertTrue(visitor.getColumns().contains(new Column("Orders", "Id_P")));
}

Example 89 with Column

use of com.alibaba.druid.stat.TableStat.Column in project druid by alibaba.

the class MySqlCreateTableTest70 method test_one.

@Test
public void test_one() throws Exception {
    String sql = "CREATE TABLE `app_customer_license` (" + "  `id`  bigint(20) NOT NULL AUTO_INCREMENT ," + "  `created_by`  varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ," + "  `created_date`  datetime NOT NULL ," + "  `last_modified_by`  varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ," + "  `last_modified_date`  datetime NULL DEFAULT NULL ," + "  `version`  bigint(20) NOT NULL ," + "  `device_id`  varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ," + "  `customer_info`  bigint(20) NULL DEFAULT NULL ," + "  PRIMARY KEY (`id`)," + "  FOREIGN KEY (`customer_info`) REFERENCES `app_customer_info` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT," + "  INDEX `fk_app_customer_info_id` (`customer_info`) USING BTREE," + "  UNIQUE `idx_app_customer_license_deviceId` (`device_id`) USING BTREE" + ")" + "ENGINE=InnoDB " + "DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci " + "AUTO_INCREMENT=1 " + "ROW_FORMAT=DYNAMIC " + ";;";
    MySqlStatementParser parser = new MySqlStatementParser(sql);
    SQLStatement stmt = parser.parseCreateTable();
    MySqlSchemaStatVisitor visitor = new MySqlSchemaStatVisitor();
    stmt.accept(visitor);
    Column column = visitor.getColumn("app_customer_license", "version");
    Assert.assertNotNull(column);
    Assert.assertEquals("bigint", column.getDataType());
    {
        String output = SQLUtils.toMySqlString(stmt);
        Assert.assertEquals("CREATE TABLE `app_customer_license` (" + "\n\t`id` bigint(20) NOT NULL AUTO_INCREMENT, " + "\n\t`created_by` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, " + "\n\t`created_date` datetime NOT NULL, " + "\n\t`last_modified_by` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, " + "\n\t`last_modified_date` datetime NULL DEFAULT NULL, " + "\n\t`version` bigint(20) NOT NULL, " + "\n\t`device_id` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, " + "\n\t`customer_info` bigint(20) NULL DEFAULT NULL, " + "\n\tPRIMARY KEY (`id`), " + "\n\tFOREIGN KEY (`customer_info`) REFERENCES `app_customer_info` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT, " + "\n\tINDEX `fk_app_customer_info_id` USING BTREE(`customer_info`), " + "\n\tUNIQUE `idx_app_customer_license_deviceId` USING BTREE (`device_id`)" + "\n) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE utf8_general_ci AUTO_INCREMENT = 1 ROW_FORMAT = DYNAMIC", output);
    }
    {
        String output = SQLUtils.toMySqlString(stmt, SQLUtils.DEFAULT_LCASE_FORMAT_OPTION);
        Assert.assertEquals("create table `app_customer_license` (" + "\n\t`id` bigint(20) not null auto_increment, " + "\n\t`created_by` varchar(50) character set utf8 collate utf8_general_ci not null, " + "\n\t`created_date` datetime not null, " + "\n\t`last_modified_by` varchar(50) character set utf8 collate utf8_general_ci null default null, " + "\n\t`last_modified_date` datetime null default null, " + "\n\t`version` bigint(20) not null, " + "\n\t`device_id` varchar(20) character set utf8 collate utf8_general_ci not null, " + "\n\t`customer_info` bigint(20) null default null, " + "\n\tprimary key (`id`), " + "\n\tforeign key (`customer_info`) references `app_customer_info` (`id`) on delete restrict on update restrict, " + "\n\tindex `fk_app_customer_info_id` using BTREE(`customer_info`), " + "\n\tunique `idx_app_customer_license_deviceId` using BTREE (`device_id`)" + "\n) engine = InnoDB character set = utf8 collate utf8_general_ci auto_increment = 1 row_format = DYNAMIC", output);
    }
}

Example 90 with Column

use of com.alibaba.druid.stat.TableStat.Column in project druid by alibaba.

the class MySqlCreateTableTest74 method test_one.

@Test
public void test_one() throws Exception {
    String sql = "create table test.simple_test (\n" + "col_key bigint(20) not null auto_increment,\n" + "col1 varchar(45) null,\n" + "col2 tinyint(4) null,\n" + "col3 datetime null,\n" + "col4 timestamp null default current_timestamp on update current_timestamp,\n" + "primary key (col_key),\n" + "unique index v1_unique (col_key asc))";
    MySqlStatementParser parser = new MySqlStatementParser(sql);
    SQLStatement stmt = parser.parseCreateTable();
    MySqlSchemaStatVisitor visitor = new MySqlSchemaStatVisitor();
    stmt.accept(visitor);
    Column column = visitor.getColumn("test.simple_test", "col_key");
    Assert.assertNotNull(column);
    Assert.assertEquals("bigint", column.getDataType());
    {
        String output = SQLUtils.toMySqlString(stmt);
        Assert.assertEquals("CREATE TABLE test.simple_test (\n" + "\tcol_key bigint(20) NOT NULL AUTO_INCREMENT, \n" + "\tcol1 varchar(45) NULL, \n" + "\tcol2 tinyint(4) NULL, \n" + "\tcol3 datetime NULL, \n" + "\tcol4 timestamp NULL DEFAULT current_timestamp ON UPDATE current_timestamp, \n" + "\tPRIMARY KEY (col_key), \n" + "\tUNIQUE v1_unique (col_key ASC)\n" + ")", output);
    }
    {
        String output = SQLUtils.toMySqlString(stmt, SQLUtils.DEFAULT_LCASE_FORMAT_OPTION);
        Assert.assertEquals("create table test.simple_test (\n" + "\tcol_key bigint(20) not null auto_increment, \n" + "\tcol1 varchar(45) null, \n" + "\tcol2 tinyint(4) null, \n" + "\tcol3 datetime null, \n" + "\tcol4 timestamp null default current_timestamp on update current_timestamp, \n" + "\tprimary key (col_key), \n" + "\tunique v1_unique (col_key asc)\n" + ")", output);
    }
}