in tajo-catalog/tajo-catalog-server/src/main/java/org/apache/tajo/catalog/store/DerbyStore.java [50:334]
protected void createBaseTable() throws CatalogException {
Connection conn = null;
Statement stmt = null;
try {
conn = getConnection();
stmt = conn.createStatement();
StringBuilder sql = new StringBuilder();
//META
if (!baseTableMaps.get(TB_META)) {
sql.append("CREATE TABLE ");
sql.append(TB_META);
sql.append(" (version int NOT NULL)");
if (LOG.isDebugEnabled()) {
LOG.debug(sql.toString());
}
stmt.executeUpdate(sql.toString());
LOG.info("Table '" + TB_META + " is created.");
baseTableMaps.put(TB_META, true);
}
// TABLES
if (!baseTableMaps.get(TB_TABLES)) {
sql.delete(0, sql.length());
sql.append("CREATE TABLE ");
sql.append(TB_TABLES);
sql.append(" (");
sql.append("TID int NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),");
sql.append(C_TABLE_ID);
sql.append(" VARCHAR(255) NOT NULL CONSTRAINT TABLE_ID_UNIQ UNIQUE, ");
sql.append("path VARCHAR(1024), ");
sql.append("store_type CHAR(16), ");
sql.append("CONSTRAINT TABLES_PK PRIMARY KEY (TID)");
sql.append( ")");
if (LOG.isDebugEnabled()) {
LOG.debug(sql.toString());
}
stmt.addBatch(sql.toString());
sql.delete(0, sql.length());
sql.append("CREATE UNIQUE INDEX idx_tables_tid on ");
sql.append(TB_TABLES);
sql.append(" (TID)");
if (LOG.isDebugEnabled()) {
LOG.debug(sql.toString());
}
stmt.addBatch(sql.toString());
sql.delete(0, sql.length());
sql.append("CREATE UNIQUE INDEX idx_tables_name on ");
sql.append(TB_TABLES);
sql.append("(");
sql.append(C_TABLE_ID);
sql.append(")");
if (LOG.isDebugEnabled()) {
LOG.debug(sql.toString());
}
stmt.addBatch(sql.toString());
stmt.executeBatch();
LOG.info("Table '" + TB_TABLES + "' is created.");
baseTableMaps.put(TB_TABLES, true);
}
// COLUMNS
if (!baseTableMaps.get(TB_COLUMNS)) {
sql.delete(0, sql.length());
sql.append("CREATE TABLE ");
sql.append(TB_COLUMNS);
sql.append(" (");
sql.append("TID INT NOT NULL REFERENCES ");
sql.append(TB_TABLES);
sql.append(" (TID) ON DELETE CASCADE, ");
sql.append(C_TABLE_ID);
sql.append( " VARCHAR(255) NOT NULL REFERENCES ");
sql.append(TB_TABLES);
sql.append("(");
sql.append(C_TABLE_ID);
sql.append(") ON DELETE CASCADE, ");
sql.append("column_id INT NOT NULL,");
sql.append("column_name VARCHAR(255) NOT NULL, ");
sql.append("data_type CHAR(16), type_length INTEGER, ");
sql.append("CONSTRAINT C_COLUMN_ID UNIQUE (");
sql.append(C_TABLE_ID);
sql.append(", column_name))");
if (LOG.isDebugEnabled()) {
LOG.debug(sql.toString());
}
stmt.addBatch(sql.toString());
sql.delete(0, sql.length());
sql.append( "CREATE UNIQUE INDEX idx_fk_columns_table_name on ");
sql.append(TB_COLUMNS);
sql.append("(");
sql.append(C_TABLE_ID);
sql.append(", column_name)");
if (LOG.isDebugEnabled()) {
LOG.debug(sql.toString());
}
stmt.addBatch(sql.toString());
stmt.executeBatch();
LOG.info("Table '" + TB_COLUMNS + " is created.");
baseTableMaps.put(TB_COLUMNS, true);
}
// OPTIONS
if (!baseTableMaps.get(TB_OPTIONS)) {
sql.delete(0, sql.length());
sql.append( "CREATE TABLE ");
sql.append(TB_OPTIONS);
sql.append(" (").append(C_TABLE_ID);
sql.append(" VARCHAR(255) NOT NULL REFERENCES TABLES (");
sql.append(C_TABLE_ID).append(") ON DELETE CASCADE, ");
sql.append("key_ VARCHAR(255) NOT NULL, value_ VARCHAR(255) NOT NULL)");
if (LOG.isDebugEnabled()) {
LOG.debug(sql.toString());
}
stmt.addBatch(sql.toString());
sql.delete(0, sql.length());
sql.append("CREATE INDEX idx_options_key on ");
sql.append(TB_OPTIONS).append( " (").append(C_TABLE_ID).append(")");
if (LOG.isDebugEnabled()) {
LOG.debug(sql.toString());
}
stmt.addBatch(sql.toString());
sql.delete(0, sql.length());
sql.append("CREATE INDEX idx_options_table_name on ").append(TB_OPTIONS);
sql.append("(" ).append(C_TABLE_ID).append(")");
if (LOG.isDebugEnabled()) {
LOG.debug(sql.toString());
}
stmt.addBatch(sql.toString());
stmt.executeBatch();
LOG.info("Table '" + TB_OPTIONS + " is created.");
baseTableMaps.put(TB_OPTIONS, true);
}
// INDEXES
if (!baseTableMaps.get(TB_INDEXES)) {
sql.delete(0, sql.length());
sql.append("CREATE TABLE ").append(TB_INDEXES).append("(");
sql.append( "index_name VARCHAR(255) NOT NULL PRIMARY KEY, ");
sql.append(C_TABLE_ID).append(" VARCHAR(255) NOT NULL REFERENCES TABLES (");
sql.append(C_TABLE_ID).append(") ");
sql.append("ON DELETE CASCADE, ");
sql.append("column_name VARCHAR(255) NOT NULL, ");
sql.append("data_type VARCHAR(255) NOT NULL, ");
sql.append("index_type CHAR(32) NOT NULL, ");
sql.append("is_unique BOOLEAN NOT NULL, ");
sql.append("is_clustered BOOLEAN NOT NULL, ");
sql.append("is_ascending BOOLEAN NOT NULL)");
if (LOG.isDebugEnabled()) {
LOG.debug(sql.toString());
}
stmt.addBatch(sql.toString());
sql.delete(0, sql.length());
sql.append("CREATE UNIQUE INDEX idx_indexes_key ON ");
sql.append(TB_INDEXES).append(" (index_name)");
if (LOG.isDebugEnabled()) {
LOG.debug(sql.toString());
}
stmt.addBatch(sql.toString());
sql.delete(0, sql.length());
sql.append("CREATE INDEX idx_indexes_columns ON ");
sql.append(TB_INDEXES).append(" (").append(C_TABLE_ID).append(", column_name)");
if (LOG.isDebugEnabled()) {
LOG.debug(sql.toString());
}
stmt.addBatch(sql.toString());
stmt.executeBatch();
LOG.info("Table '" + TB_INDEXES + "' is created.");
baseTableMaps.put(TB_INDEXES, true);
}
if (!baseTableMaps.get(TB_STATISTICS)) {
sql.delete(0, sql.length());
sql.append("CREATE TABLE ").append(TB_STATISTICS).append( "(");
sql.append(C_TABLE_ID).append(" VARCHAR(255) NOT NULL REFERENCES TABLES (");
sql.append(C_TABLE_ID).append(") ");
sql.append("ON DELETE CASCADE, ");
sql.append("num_rows BIGINT, ");
sql.append("num_bytes BIGINT)");
if (LOG.isDebugEnabled()) {
LOG.debug(sql.toString());
}
stmt.addBatch(sql.toString());
sql.delete(0, sql.length());
sql.append("CREATE INDEX idx_stats_table_name ON ");
sql.append(TB_STATISTICS).append(" (").append(C_TABLE_ID).append(")");
if (LOG.isDebugEnabled()) {
LOG.debug(sql.toString());
}
stmt.addBatch(sql.toString());
stmt.executeBatch();
LOG.info("Table '" + TB_STATISTICS + "' is created.");
baseTableMaps.put(TB_STATISTICS, true);
}
// PARTITION_METHODS
if (!baseTableMaps.get(TB_PARTITION_METHODS)) {
sql.delete(0, sql.length());
sql.append("CREATE TABLE ").append(TB_PARTITION_METHODS).append(" (");
sql.append(C_TABLE_ID).append(" VARCHAR(255) NOT NULL REFERENCES TABLES (");
sql.append(C_TABLE_ID).append(") ");
sql.append("ON DELETE CASCADE, ");
sql.append("partition_type VARCHAR(10) NOT NULL,");
sql.append("expression VARCHAR(1024) NOT NULL,");
sql.append("expression_schema VARCHAR(1024) FOR BIT DATA NOT NULL)");
if (LOG.isDebugEnabled()) {
LOG.debug(sql.toString());
}
stmt.addBatch(sql.toString());
sql.delete(0, sql.length());
sql.append("CREATE INDEX idx_partition_methods_table_name ON ");
sql.append(TB_PARTITION_METHODS).append(" (").append(C_TABLE_ID).append(")");
if (LOG.isDebugEnabled()) {
LOG.debug(sql.toString());
}
stmt.addBatch(sql.toString());
stmt.executeBatch();
LOG.info("Table '" + TB_PARTITION_METHODS + "' is created.");
baseTableMaps.put(TB_PARTITION_METHODS, true);
}
// PARTITIONS
if (!baseTableMaps.get(TB_PARTTIONS)) {
sql.delete(0, sql.length());
sql.append("CREATE TABLE ").append(TB_PARTTIONS).append("(");
sql.append("PID INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),");
sql.append(C_TABLE_ID).append(" VARCHAR(255) NOT NULL REFERENCES TABLES (");
sql.append(C_TABLE_ID).append(")");
sql.append("ON DELETE CASCADE, ");
sql.append("partition_name VARCHAR(255), ");
sql.append("ordinal_position INT NOT NULL,");
sql.append("partition_value VARCHAR(1024),");
sql.append("path VARCHAR(1024),");
sql.append("cache_nodes VARCHAR(255), ");
sql.append(" CONSTRAINT PARTITION_PK PRIMARY KEY (PID))");
if (LOG.isDebugEnabled()) {
LOG.debug(sql.toString());
}
stmt.addBatch(sql.toString());
sql.delete(0, sql.length());
sql.append("CREATE INDEX idx_partitions_table_name ON ");
sql.append(TB_PARTTIONS).append(" (").append(C_TABLE_ID).append(")");
if (LOG.isDebugEnabled()) {
LOG.debug(sql.toString());
}
stmt.addBatch(sql.toString());
stmt.executeBatch();
LOG.info("Table '" + TB_PARTTIONS + "' is created.");
baseTableMaps.put(TB_PARTTIONS, true);
}
} catch (SQLException se) {
throw new CatalogException(se);
} finally {
CatalogUtil.closeQuietly(conn, stmt);
}
}