content: package com.alibaba.druid.bvt.sql.starrocks.issues;
  
  import com.alibaba.druid.sql.ast.statement.SQLCreateTableStatement;
  import com.alibaba.druid.sql.dialect.starrocks.parser.StarRocksStatementParser;
  import com.alibaba.druid.sql.parser.SQLCreateTableParser;
  import org.junit.Test;
  
  /**
   * @Author: Ekko
   * @Date: 2024-07-12 17:29
   * @see <a href="https://github.com/alibaba/druid/issues/6029>Issue来源</a>
   * @see <a href="https://docs.starrocks.io/zh/docs/sql-reference/sql-statements/data-definition/CREATE_TABLE/">CREATE TABLE</a>
   */
  public class Issue6029 {
      static final String[] caseList = new String[]{
              // 1.普通建表语句
              "CREATE TABLE IF NOT EXISTS `detailDemo` (\n" +
                      "\t`recruit_date` DATE NOT NULL COMMENT 'YYYY-MM-DD',\n" +
                      "\t`region_num` TINYINT COMMENT 'range [-128, 127]',\n" +
                      "\t`num_plate` SMALLINT COMMENT 'range [-32768, 32767] ',\n" +
                      "\t`tel` INT COMMENT 'range [-2147483648, 2147483647]',\n" +
                      "\t`id` BIGINT COMMENT 'range [-2^63 + 1 ~ 2^63 - 1]',\n" +
                      "\t`password` LARGEINT COMMENT 'range [-2^127 + 1 ~ 2^127 - 1]',\n" +
                      "\t`name` CHAR(20) NOT NULL COMMENT 'range char(m),m in (1-255)',\n" +
                      "\t`profile` VARCHAR(500) NOT NULL COMMENT 'upper limit value 1048576 bytes',\n" +
                      "\t`hobby` STRING NOT NULL COMMENT 'upper limit value 65533 bytes',\n" +
                      "\t`leave_time` DATETIME COMMENT 'YYYY-MM-DD HH:MM:SS',\n" +
                      "\t`channel` FLOAT COMMENT '4 bytes',\n" +
                      "\t`income` DOUBLE COMMENT '8 bytes',\n" +
                      "\t`account` DECIMAL(12, 4) COMMENT '\"\"',\n" +
                      "\t`ispass` BOOLEAN COMMENT 'true/false'\n" +
                      ")\n" +
                      "DISTRIBUTED BY HASH(`recruit_date`, `region_num`) BUCKETS 8\n",
  
              // 2.指定引擎，数据模型，PARTITION 分区为 LESS THAN，properties参数的建表语句
              "CREATE TABLE IF NOT EXISTS `detailDemo` (\n" +
                      "\t`recruit_date` DATE NOT NULL COMMENT 'YYYY-MM-DD',\n" +
                      "\t`region_num` TINYINT COMMENT 'range [-128, 127]',\n" +
                      "\t`num_plate` SMALLINT COMMENT 'range [-32768, 32767] ',\n" +
                      "\t`tel` INT COMMENT 'range [-2147483648, 2147483647]',\n" +
                      "\t`id` BIGINT COMMENT 'range [-2^63 + 1 ~ 2^63 - 1]',\n" +
                      "\t`password` LARGEINT COMMENT 'range [-2^127 + 1 ~ 2^127 - 1]',\n" +
                      "\t`name` CHAR(20) NOT NULL COMMENT 'range char(m),m in (1-255)',\n" +
                      "\t`profile` VARCHAR(500) NOT NULL COMMENT 'upper limit value 1048576 bytes',\n" +
                      "\t`hobby` STRING NOT NULL COMMENT 'upper limit value 65533 bytes',\n" +
                      "\t`leave_time` DATETIME COMMENT 'YYYY-MM-DD HH:MM:SS',\n" +
                      "\t`channel` FLOAT COMMENT '4 bytes',\n" +
                      "\t`income` DOUBLE COMMENT '8 bytes',\n" +
                      "\t`account` DECIMAL(12, 4) COMMENT '\"\"',\n" +
                      "\t`ispass` BOOLEAN COMMENT 'true/false'\n" +
                      ") ENGINE = OLAP\n" +
                      "DUPLICATE KEY (`recruit_date`, `region_num`)\n" +
                      "COMMENT 'detailDemo detailDemo '\n" +
                      "PARTITION BY RANGE(`recruit_date`, `recruit_date2`)\n" +
                      "(\n" +
                      "  PARTITION p1 VALUES LESS THAN (\"2021-01-02\"), \n" +
                      "  PARTITION p2 VALUES LESS THAN (\"2021-01-03\"), \n" +
                      "  PARTITION p3 VALUES LESS THAN MAXVALUE\n" +
                      ")\n" +
                      "DISTRIBUTED BY HASH(`recruit_date`, `region_num`) BUCKETS 8\n" +
                      "PROPERTIES (\n" +
                      "\t\"replication_num\" = \"1\"\n" +
                      ")",
  
              // 3.分区类型为 Fixed Range的建表语句
              "CREATE TABLE IF NOT EXISTS `detailDemo` (\n" +
                      "\t`recruit_date` DATE NOT NULL COMMENT 'YYYY-MM-DD',\n" +
                      "\t`region_num` TINYINT COMMENT 'range [-128, 127]',\n" +
                      "\t`num_plate` SMALLINT COMMENT 'range [-32768, 32767] ',\n" +
                      "\t`tel` INT COMMENT 'range [-2147483648, 2147483647]',\n" +
                      "\t`id` BIGINT COMMENT 'range [-2^63 + 1 ~ 2^63 - 1]',\n" +
                      "\t`password` LARGEINT COMMENT 'range [-2^127 + 1 ~ 2^127 - 1]',\n" +
                      "\t`name` CHAR(20) NOT NULL COMMENT 'range char(m),m in (1-255)',\n" +
                      "\t`profile` VARCHAR(500) NOT NULL COMMENT 'upper limit value 1048576 bytes',\n" +
                      "\t`hobby` STRING NOT NULL COMMENT 'upper limit value 65533 bytes',\n" +
                      "\t`leave_time` DATETIME COMMENT 'YYYY-MM-DD HH:MM:SS',\n" +
                      "\t`channel` FLOAT COMMENT '4 bytes',\n" +
                      "\t`income` DOUBLE COMMENT '8 bytes',\n" +
                      "\t`account` DECIMAL(12, 4) COMMENT '\"\"',\n" +
                      "\t`ispass` BOOLEAN COMMENT 'true/false'\n" +
                      ") ENGINE = OLAP\n" +
                      "DUPLICATE KEY (`recruit_date`, `region_num`)\n" +
                      "PARTITION BY RANGE(`recruit_date`)\n" +
                      "(\n" +
                      "  PARTITION p202101 VALUES [(\"20210101\"),(\"20210201\")),\n" +
                      "  PARTITION p202102 VALUES [(\"20210201\"),(\"20210301\")),\n" +
                      "  PARTITION p202103 VALUES [(\"20210301\"),(MAXVALUE))\n" +
                      ")\n" +
                      "DISTRIBUTED BY HASH(`recruit_date`, `region_num`) BUCKETS 8\n" +
                      "PROPERTIES (\n" +
                      "\t\"replication_num\" = \"1\"\n" +
                      ")",
  
              // 4.分区类型为 Fixed Range, 多分段的建表语句
              "CREATE TABLE IF NOT EXISTS `detailDemo` (\n" +
                      "\t`recruit_date` DATE NOT NULL COMMENT 'YYYY-MM-DD',\n" +
                      "\t`region_num` TINYINT COMMENT 'range [-128, 127]',\n" +
                      "\t`num_plate` SMALLINT COMMENT 'range [-32768, 32767] ',\n" +
                      "\t`tel` INT COMMENT 'range [-2147483648, 2147483647]',\n" +
                      "\t`id` BIGINT COMMENT 'range [-2^63 + 1 ~ 2^63 - 1]',\n" +
                      "\t`password` LARGEINT COMMENT 'range [-2^127 + 1 ~ 2^127 - 1]',\n" +
                      "\t`name` CHAR(20) NOT NULL COMMENT 'range char(m),m in (1-255)',\n" +
                      "\t`profile` VARCHAR(500) NOT NULL COMMENT 'upper limit value 1048576 bytes',\n" +
                      "\t`hobby` STRING NOT NULL COMMENT 'upper limit value 65533 bytes',\n" +
                      "\t`leave_time` DATETIME COMMENT 'YYYY-MM-DD HH:MM:SS',\n" +
                      "\t`channel` FLOAT COMMENT '4 bytes',\n" +
                      "\t`income` DOUBLE COMMENT '8 bytes',\n" +
                      "\t`account` DECIMAL(12, 4) COMMENT '\"\"',\n" +
                      "\t`ispass` BOOLEAN COMMENT 'true/false'\n" +
                      ") ENGINE = OLAP\n" +
                      "DUPLICATE KEY (`recruit_date`, `region_num`)\n" +
                      "PARTITION BY RANGE(`recruit_date`, `region_num`, `num_plate`)\n" +
                      "(\n" +
                      "  PARTITION partition_name1 VALUES [(\"k1-lower1\", \"k2-lower1\", \"k3-lower1\"),(\"k1-upper1\", \"k2-upper1\", \"k3-upper1\")),\n" +
                      "  PARTITION partition_name2 VALUES [(\"k1-lower1-2\", \"k2-lower1-2\"),(\"k1-upper1-2\", \"k2-upper1-2\", \"k3-upper1-2\"))\n" +
                      ")\n" +
                      "DISTRIBUTED BY HASH(`recruit_date`, `region_num`) BUCKETS 8\n" +
                      "PROPERTIES (\n" +
                      "\t\"replication_num\" = \"1\"\n" +
                      ")",
  
              // 5. 多种 PROPERTIES 参数类型的建表语句
              "CREATE TABLE IF NOT EXISTS `detailDemo` (\n" +
                      "\t`recruit_date` DATE NOT NULL COMMENT 'YYYY-MM-DD',\n" +
                      "\t`region_num` TINYINT COMMENT 'range [-128, 127]',\n" +
                      "\t`num_plate` SMALLINT COMMENT 'range [-32768, 32767] ',\n" +
                      "\t`tel` INT COMMENT 'range [-2147483648, 2147483647]',\n" +
                      "\t`id` BIGINT COMMENT 'range [-2^63 + 1 ~ 2^63 - 1]',\n" +
                      "\t`password` LARGEINT COMMENT 'range [-2^127 + 1 ~ 2^127 - 1]',\n" +
                      "\t`name` CHAR(20) NOT NULL COMMENT 'range char(m),m in (1-255)',\n" +
                      "\t`profile` VARCHAR(500) NOT NULL COMMENT 'upper limit value 1048576 bytes',\n" +
                      "\t`hobby` STRING NOT NULL COMMENT 'upper limit value 65533 bytes',\n" +
                      "\t`leave_time` DATETIME COMMENT 'YYYY-MM-DD HH:MM:SS',\n" +
                      "\t`channel` FLOAT COMMENT '4 bytes',\n" +
                      "\t`income` DOUBLE COMMENT '8 bytes',\n" +
                      "\t`account` DECIMAL(12, 4) COMMENT '\"\"',\n" +
                      "\t`ispass` BOOLEAN COMMENT 'true/false'\n" +
                      ") ENGINE = OLAP\n" +
                      "DUPLICATE KEY (`recruit_date`, `region_num`)\n" +
                      "PARTITION BY RANGE(`recruit_date`)\n" +
                      "(\n" +
                      "  PARTITION p202101 VALUES [(\"20210101\"),(\"20210201\")),\n" +
                      "  PARTITION p202102 VALUES [(\"20210201\"),(\"20210301\")),\n" +
                      "  PARTITION p202103 VALUES [(\"20210301\"),(MAXVALUE))\n" +
                      ")\n" +
                      "DISTRIBUTED BY HASH(`recruit_date`, `region_num`) BUCKETS 8\n" +
                      "PROPERTIES (\n" +
                      "\t\"storage_medium\" = \"[SSD|HDD]\",\n" +
                      "\t\"dynamic_partition.enable\" = \"true|false\",\n" +
                      "\t\"dynamic_partition.time_unit\" = \"DAY|WEEK|MONTH\",\n" +
                      "\t\"dynamic_partition.start\" = \"${integer_value}\",\n" +
                      "\t\"storage_cooldown_time\" = \"yyyy-MM-dd HH:mm:ss\",\n" +
                      "\t\"replication_num\" = \"3\"\n" +
                      ")",
  
              // 6.含有 Bitmap 索引和聚合函数的建表语句
              "CREATE TABLE d0.table_hash (\n" +
                      "\tk1 TINYINT,\n" +
                      "\tk2 DECIMAL(10, 2) DEFAULT \"10.5\",\n" +
                      "\tv1 CHAR(10) REPLACE,\n" +
                      "\tv2 INT SUM,\n" +
                      "\tINDEX index_name(column_name) USING BITMAP COMMENT '22'\n" +
                      ") ENGINE = olap\n" +
                      "AGGREGATE KEY (k1, k2)\n" +
                      "DISTRIBUTED BY HASH(k1) BUCKETS 10\n" +
                      "PROPERTIES (\n" +
                      "\t\"storage_type\" = \"column\"\n" +
                      ")",
  
              // 7. 外部表
              "CREATE EXTERNAL TABLE example_db.table_mysql (\n" +
                      "\tk1 DATE,\n" +
                      "\tk2 INT,\n" +
                      "\tk3 SMALLINT,\n" +
                      "\tk4 VARCHAR(2048),\n" +
                      "\tk5 DATETIME\n" +
                      ") ENGINE = mysql\n" +
                      "\n" +
                      "PROPERTIES (\n" +
                      "\t\"odbc_catalog_resource\" = \"mysql_resource\",\n" +
                      "\t\"database\" = \"mysql_db_test\",\n" +
                      "\t\"table\" = \"mysql_table_test\"\n" +
                      ")",
  
              // 8. 数据模型列只有一列
              "CREATE TABLE `olap_5e61d03d605641ebafd100c809dbf15c` (\n" +
                      "\t`a` int(11) NULL,\n" +
                      "\t`b` text NULL,\n" +
                      "\t`c` text NULL\n" +
                      ") ENGINE = OLAP\n" +
                      "DUPLICATE KEY (`a`)\n" +
                      "COMMENT 'OLAP'\n" +
                      "DISTRIBUTED BY RANDOM BUCKETS 10\n" +
                      "PROPERTIES (\n" +
                      "\t\"replication_allocation\" = \"tag.location.default: 1\",\n" +
                      "\t\"in_memory\" = \"false\",\n" +
                      "\t\"storage_format\" = \"V2\",\n" +
                      "\t\"disable_auto_compaction\" = \"false\"\n" +
                      ")"
      };
  
      @Test
      public void test_parse_create() {
          for (int i = 0; i < caseList.length; i++) {
              final String sql = caseList[i];
              final StarRocksStatementParser starRocksStatementParser = new StarRocksStatementParser(sql);
              final SQLCreateTableParser sqlCreateTableParser = starRocksStatementParser.getSQLCreateTableParser();
              final SQLCreateTableStatement parsed = sqlCreateTableParser.parseCreateTable();
              final String result = parsed.toString();
              System.out.println(result);
          }
      }
  }
  