protected static final String [] DB_SQL_QUOTEDS = {"\"", "[", "`", "\"", "\"", "\""};
protected static final String [] DB_SQL_QUOTEDE = {"\"", "]", "`", "\"", "\"", "\""};
protected static final String [] SQL_TERMINATOR = {";", System.getProperty("line.separator") + "GO", ";", ";", ";", ";"};
- protected static final String [] SQL_PREFIX = {"@", ":r ", "", "", "", ""};
+ protected static final String [] SQL_PREFIX = {"@", ":r ", "source ", "", "", ""};
protected static final String [] SQL_COMMENT_MARK = {"--", "--", "--", "--", "--", "--"};
protected static final int MASK_PTN_ALL = 0;
protected static final int MASK_PTN_ALT = 1;
+ " inner join sys.schemas as b "
+ " on a.schema_id = b.schema_id "
+ "order by synonym_name,table_owner,table_name",
- "select "
- + " c.name as table_name, "
- + " b.index_id, "
- + " d.name as index_name , "
- + " a.type_desc as allocation_type, "
- + " a.data_pages, "
- + " partition_number "
- + "from "
- + " sys.allocation_units as a "
- + " inner join sys.partitions as b "
- + " on a.container_id = b.partition_id "
- + " inner join sys.objects as c "
- + " on b.object_id = c.object_id and c.type = 'U' "
- + " inner join sys.indexes as d "
- + " on b.index_id = d.index_id and d.object_id = b.object_id "
- + "order by c.name, b.index_id",
+ "",
"select distinct "
+ " c.name as table_name, "
+ " a.name as constraint_name, "
+ " inner join sys.key_constraints as g "
+ " on a.referenced_object_id = g.parent_object_id "
+ "order by table_name,constraint_name, position"
+ },
+ { //MYSQL
+ "select "
+ + " a.table_name,"
+ + " replace(replace(a.table_comment, char(13), ''), char(10), '') as table_comments, "
+ + " b.column_name,"
+ + " replace(replace(b.column_comment, char(13), ''), char(10), '') as column_comment, "
+ + " b.column_type,"
+ + " b.character_maximum_length as data_precision,"
+ + " b.numeric_precision as data_precision,"
+ + " b.numeric_scale as data_scale,"
+ + " case b.is_nullable"
+ + " when 'NO' then '\81Z'"
+ + " else null "
+ + " end as nullable,"
+ + " column_default as data_default "
+ + "from "
+ + " information_schema.tables a,"
+ + " information_schema.columns b "
+ + "where "
+ + " a.table_schema = database()"
+ + " and a.table_type = 'BASE TABLE'"
+ + " and a.table_schema = b.table_schema "
+ + " and a.table_name = b.table_name "
+ + "order by a.table_name,b.ordinal_position",
+ "select "
+ + " a.table_name, "
+ + " b.constraint_name as index_name,"
+ + " '' as index_type, "
+ + " case b.constraint_type "
+ + " when 'PRIMARY KEY' then 'P' "
+ + " when 'UNIQUE' then 'U' "
+ + " end as constraint_type,"
+ + " c.column_name,"
+ + " c.ordinal_position as column_position"
+ + " from"
+ + " information_schema.tables a,"
+ + " information_schema.table_constraints b,"
+ + " information_schema.key_column_usage c"
+ + " where"
+ + " a.table_schema = database()"
+ + " and a.table_type = 'BASE TABLE'"
+ + " and a.table_schema = b.table_schema"
+ + " and a.table_name = b.table_name"
+ + " and b.table_schema = c.table_schema"
+ + " and b.table_name = c.table_name"
+ + " and b.constraint_name = c.constraint_name"
+ + " and b.constraint_type in('PRIMARY KEY','UNIQUE')"
+ + " union select"
+ + " a.table_name,"
+ + " a.index_name,"
+ + " b.index_type as index_type, "
+ + " '' as constraint_type,"
+ + " a.column_name,"
+ + " a.seq_in_index as column_position"
+ + " from"
+ + " information_schema.statistics a,"
+ + " information_schema.statistics b"
+ + " where"
+ + " a.table_schema = database()"
+ + " and a.table_schema = b.table_schema"
+ + " and a.table_name = b.table_name"
+ + " and a.index_name = b.index_name"
+ + " and a.non_unique = 1"
+ + " order by table_name,index_name,column_position",
+ "select "
+ + " a.table_name as view_name, "
+ + " '' as comments "
+ + "from "
+ + " information_schema.views a "
+ + " where"
+ + " table_schema = database()"
+ + " order by "
+ + " a.table_name",
+ "select "
+ + " routine_name as object_name, "
+ + " routine_type as object_type, "
+ + " '' as action_timing, "
+ + " '' as event_action, "
+ + " '' as event_object "
+ + " from "
+ + " information_schema.routines "
+ + " where"
+ + " routine_schema = database()"
+ + " and (routine_type = 'PROCEDURE' or routine_type = 'FUNCTION') "
+ + " union select "
+ + " trigger_name as object_name, "
+ + " 'TRIGGER' as object_type, "
+ + " action_timing as action_timing, "
+ + " event_manipulation as event_action, "
+ + " event_object_table as event_object "
+ + " from "
+ + " information_schema.triggers "
+ + " where"
+ + " trigger_schema = database()"
+ + " order by object_name,object_type ",
+ "",
+ "",
+ "",
+ "select distinct "
+ + " b.table_name, "
+ + " b.constraint_name, "
+ + " b.column_name, "
+ + " b.ordinal_position as position, "
+ + " b.referenced_table_name as r_table_name, "
+ + " b.referenced_column_name as r_column_name, "
+ + " c.ordinal_position as r_position "
+ + " from "
+ + " information_schema.referential_constraints a, "
+ + " information_schema.key_column_usage b, "
+ + " information_schema.columns c "
+ + " where "
+ + " a.constraint_schema = database() "
+ + " and a.constraint_schema = b.constraint_schema "
+ + " and a.constraint_name = b.constraint_name "
+ + " and b.referenced_table_name = c.table_name "
+ + " and b.referenced_column_name = c.column_name "
}
};
protected static String sql_view_text[] = {
+ "on b.object_id = a.object_id "
+ "inner join sys.sql_modules c "
+ "on c.object_id = b.object_id "
- + "where a.name = ?"
+ + "where a.name = ?",
+ "select view_definition from information_schema.views where table_name = ?"
};
protected static String sql_view_column_and_comment[] = {
"select "
+ " inner join sys.types c on b.system_type_id = c.system_type_id "
+ " where a.type = 'V' "
+ " and c.user_type_id <> 256 "
- + " order by b.column_id"
+ + " order by b.column_id",
+ " select "
+ + " column_name,"
+ + " replace(replace(column_comment, char(13), ''), char(10), '') as column_comment "
+ + " from"
+ + " information_schema.columns "
+ + " where"
+ + " table_name = ? "
+ + " order by ordinal_position"
};
protected static String sql_proc[] = {
"select text from user_source where type = ? and name = ? order by line",
+ "on b.object_id = a.object_id "
+ "inner join sys.sql_modules c "
+ "on c.object_id = b.object_id) d "
- + "where d.type = ? and d.name = ?"
+ + "where d.type = ? and d.name = ?",
+ "select a.definition "
+ + "from "
+ + "( "
+ + "select "
+ + " routine_name as object_name, "
+ + " routine_type as object_type, "
+ + " routine_definition as definition "
+ + "from "
+ + " information_schema.routines "
+ + "where "
+ + " routine_schema = database() "
+ + "union select "
+ + " trigger_name as object_name, "
+ + " 'TRIGGER' as object_type, "
+ + " action_statement as definition "
+ + "from "
+ + " information_schema.triggers "
+ + "where "
+ + " trigger_schema = database() "
+ + ") a "
+ + "where "
+ + " a.object_type = ? "
+ + " and a.object_name = ? "
};
protected String user = null;
cp = new CmnProps();
cp.setProperty(database);
- if (cp.dbType > cp.DB_TYPE_SQLSERVER || cp.dbType <0){
+ if (cp.dbType > cp.DB_TYPE_MYSQL || cp.dbType <0){
throw new Exception("\83f\81[\83^\83x\81[\83X\82ª\91Î\8fÛ\8aO\82Å\82·\81B[" + cp.DB_TYPE_NAME[cp.dbType] + "]");
}
if(cp.outFolder != null){
conn = dao.connect();
// Def tsv Writing start
for(int i=0;i<cp.meta_sql[cp.dbType].length;i++){
- dao.select(cp.meta_sql[cp.dbType][i]);
- CmnUtils.writeSeparator(folder + "/" + cp.WK_FILE_NAME[i] + "_" + database +"." + cp.fileExtension, dao.getColumnCount(), dao.getArrayList(), cp.delimiter);
+ if(!cp.meta_sql[cp.dbType][i].equals("")){
+ dao.select(cp.meta_sql[cp.dbType][i]);
+ CmnUtils.writeSeparator(folder + "/" + cp.WK_FILE_NAME[i] + "_" + database +"." + cp.fileExtension, dao.getColumnCount(), dao.getArrayList(), cp.delimiter);
+ } else {
+ CmnUtils.emptyFile(folder + "/" + cp.WK_FILE_NAME[i] + "_" + database +"." + cp.fileExtension);
+ }
}
// Def tsv Writing end
if(!colCmnt.equals(""))sbTbCmnt.append(cp.lineSeparator);
if(CmnUtils.isColPrec(colType, colPrec)){
sbTbCnts.append("(" + colPrec);
- if (!colScale.equals("") && !colScale.equals("0"))sbTbCnts.append("," + colScale);
+ if (!CmnUtils.isEmpty(colScale) && !colScale.equals("0"))sbTbCnts.append("," + colScale);
sbTbCnts.append(")");
} else if (CmnUtils.isColLength(colType)){
if(colLen.equals("-1")){
sbTbCnts.append("(" + CmnUtils.getColLength(colType, colLen) + ")");
}
}
- if (!colDflt.equals("")){
+ if (!CmnUtils.isEmpty(colDflt)){
if(CmnUtils.split(strLine, cp.SQL_COMMENT_MARK[cp.dbType]).length > 0){
String [] colDfltAry = CmnUtils.split(colDflt, cp.SQL_COMMENT_MARK[cp.dbType]);
- sbTbCnts.append(" DEFAULT " + colDfltAry[0].trim());
+ if (cp.dbType == cp.DB_TYPE_MYSQL && !CmnUtils.isReserved(colDfltAry[0].trim())){
+ sbTbCnts.append(" DEFAULT '" + colDfltAry[0].trim() + "'");
+ } else {
+ sbTbCnts.append(" DEFAULT " + colDfltAry[0].trim());
+ }
} else {
- sbTbCnts.append(" DEFAULT " + colDflt.trim());
+ if (cp.dbType == cp.DB_TYPE_MYSQL && !CmnUtils.isReserved(colDflt.trim())){
+ sbTbCnts.append(" DEFAULT " + colDflt.trim() + "'");
+ } else {
+ sbTbCnts.append(" DEFAULT " + colDflt.trim());
+ }
}
}
- if (!colNoN.equals("")){
+ if (!CmnUtils.isEmpty(colNoN)){
sbTbCnts.append(" NOT NULL");
} else {
sbTbCnts.append(" NULL");
sbTbCnts.append(cp.lineSeparator);
sbTbCnts.append("(");
sbTbCnts.append(cp.lineSeparator);
- if(!strSplit[LINE][COL02].equals(""))sbTbCmnt.append(commentOnTable(cp, strSplit[LINE][COL01], strSplit[LINE][COL02], cp.TYPE_TABLE));
- if(!strSplit[LINE][COL02].equals(""))sbTbCmnt.append(cp.lineSeparator);
+ if(!CmnUtils.isEmpty(strSplit[LINE][COL02]))sbTbCmnt.append(commentOnTable(cp, strSplit[LINE][COL01], strSplit[LINE][COL02], cp.TYPE_TABLE));
+ if(!CmnUtils.isEmpty(strSplit[LINE][COL02]))sbTbCmnt.append(cp.lineSeparator);
} else {
sbTbCnts.append(",");
sbTbCnts.append(cp.lineSeparator);
sbTbCnts.append(cp.lineSeparator);
sbTbCnts.append("(");
sbTbCnts.append(cp.lineSeparator);
- if(!strSplit[LINE][COL02].equals(""))sbTbCmnt.append(commentOnTable(cp, strSplit[LINE][COL01], strSplit[LINE][COL02], cp.TYPE_TABLE));
- if(!strSplit[LINE][COL02].equals(""))sbTbCmnt.append(cp.lineSeparator);
+ if(!CmnUtils.isEmpty(strSplit[LINE][COL02]))sbTbCmnt.append(commentOnTable(cp, strSplit[LINE][COL01], strSplit[LINE][COL02], cp.TYPE_TABLE));
+ if(!CmnUtils.isEmpty(strSplit[LINE][COL02]))sbTbCmnt.append(cp.lineSeparator);
}
for(int i=0;i<strSplit[LINE].length;i++)CmnUtils.debugPrint("'" + strSplit[LINE][i] + "'");
}
if(CmnUtils.isColPrec(colType, colPrec)){
sbTbCnts.append("(" + colPrec);
- if (!colScale.equals("") && !colScale.equals("0"))sbTbCnts.append("," + colScale);
+ if (!CmnUtils.isEmpty(colScale) && !CmnUtils.isEmpty(colScale))sbTbCnts.append("," + colScale);
sbTbCnts.append(")");
} else if (CmnUtils.isColLength(colType)){
if(colLen.equals("-1")){
sbTbCnts.append("(" + CmnUtils.getColLength(colType, colLen) + ")");
}
}
- if (!colDflt.equals(""))sbTbCnts.append(" DEFAULT " + colDflt.trim());
- if (!colNoN.equals("")){
+ if (!CmnUtils.isEmpty(colDflt)){
+ if (cp.dbType == cp.DB_TYPE_MYSQL && !CmnUtils.isReserved(colDflt.trim())){
+ sbTbCnts.append(" DEFAULT '" + colDflt.trim() + "'");
+ } else {
+ sbTbCnts.append(" DEFAULT " + colDflt.trim());
+ }
+ }
+ if (!CmnUtils.isEmpty(colNoN)){
sbTbCnts.append(" NOT NULL");
} else {
sbTbCnts.append(" NULL");
strSplit[LINE] = CmnUtils.split(strLine, cp.delimiter);
//Not 1st Line Process
if (!indexName.equals("")){
- if (!strSplit[LINE][COL02].equals(indexName)){
+ if (!strSplit[LINE][COL02].equals(indexName) || !strSplit[LINE][COL01].equals(tableName)){
//Post Process
if (colPos.equals("1")){
sbIxCnts.append(cp.DB_SQL_QUOTEDS[cp.dbType]+ colName + cp.DB_SQL_QUOTEDE[cp.dbType]);
for(int i=0;i<strSplit[IDX_CONTENTS_1].length;i++)if(!strSplit[IDX_CONTENTS_1][i].trim().equals(""))pw[W_CREATE_UKEY].println(strSplit[IDX_CONTENTS_1][i]);
strSplit[IDX_CONTENTS_2] = CmnUtils.split(sbIx2Cnts.toString(), cp.lineSeparator);
for(int i=0;i<strSplit[IDX_CONTENTS_2].length;i++)if(!strSplit[IDX_CONTENTS_2][i].trim().equals(""))pw[W_DROP_UKEY].println(strSplit[IDX_CONTENTS_2][i]);
- } else if (constraintType.equals("")){
+ } else {
strSplit[IDX_CONTENTS_1] = CmnUtils.split(sbIxCnts.toString(), cp.lineSeparator);
for(int i=0;i<strSplit[IDX_CONTENTS_1].length;i++)if(!strSplit[IDX_CONTENTS_1][i].trim().equals(""))pw[W_CREATE_INDEX].println(strSplit[IDX_CONTENTS_1][i]);
}
//Index Changed Process
if (strSplit[LINE][COL04].equals("P")){
- sbIxCnts.append("ALTER TABLE " + cp.DB_SQL_QUOTEDS[cp.dbType]+ strSplit[LINE][COL01] + cp.DB_SQL_QUOTEDE[cp.dbType]+ " ADD CONSTRAINT " + cp.DB_SQL_QUOTEDS[cp.dbType]+ strSplit[LINE][COL02] + cp.DB_SQL_QUOTEDE[cp.dbType]+ " PRIMARY KEY(");
- sbIx2Cnts.append("ALTER TABLE " + cp.DB_SQL_QUOTEDS[cp.dbType]+ strSplit[LINE][COL01] + cp.DB_SQL_QUOTEDE[cp.dbType]+ " DROP CONSTRAINT " + cp.DB_SQL_QUOTEDS[cp.dbType]+ strSplit[LINE][COL02] + cp.DB_SQL_QUOTEDE[cp.dbType]+ cp.SQL_TERMINATOR[cp.dbType]);
+ if (cp.dbType == cp.DB_TYPE_MYSQL){
+ sbIxCnts.append("ALTER TABLE " + cp.DB_SQL_QUOTEDS[cp.dbType]+ strSplit[LINE][COL01] + cp.DB_SQL_QUOTEDE[cp.dbType]+ " ADD CONSTRAINT PRIMARY KEY(");
+ sbIx2Cnts.append("ALTER TABLE " + cp.DB_SQL_QUOTEDS[cp.dbType]+ strSplit[LINE][COL01] + cp.DB_SQL_QUOTEDE[cp.dbType]+ " DROP PRIMARY KEY" + cp.SQL_TERMINATOR[cp.dbType]);
+ } else {
+ sbIxCnts.append("ALTER TABLE " + cp.DB_SQL_QUOTEDS[cp.dbType]+ strSplit[LINE][COL01] + cp.DB_SQL_QUOTEDE[cp.dbType]+ " ADD CONSTRAINT " + cp.DB_SQL_QUOTEDS[cp.dbType]+ strSplit[LINE][COL02] + cp.DB_SQL_QUOTEDE[cp.dbType]+ " PRIMARY KEY(");
+ sbIx2Cnts.append("ALTER TABLE " + cp.DB_SQL_QUOTEDS[cp.dbType]+ strSplit[LINE][COL01] + cp.DB_SQL_QUOTEDE[cp.dbType]+ " DROP CONSTRAINT " + cp.DB_SQL_QUOTEDS[cp.dbType]+ strSplit[LINE][COL02] + cp.DB_SQL_QUOTEDE[cp.dbType]+ cp.SQL_TERMINATOR[cp.dbType]);
+ }
} else if (strSplit[LINE][COL04].equals("U")){
sbIxCnts.append("ALTER TABLE " + cp.DB_SQL_QUOTEDS[cp.dbType]+ strSplit[LINE][COL01] + cp.DB_SQL_QUOTEDE[cp.dbType]+ " ADD CONSTRAINT " + cp.DB_SQL_QUOTEDS[cp.dbType]+ strSplit[LINE][COL02] + cp.DB_SQL_QUOTEDE[cp.dbType]+ " UNIQUE(");
sbIx2Cnts.append("ALTER TABLE " + cp.DB_SQL_QUOTEDS[cp.dbType]+ strSplit[LINE][COL01] + cp.DB_SQL_QUOTEDE[cp.dbType]+ " DROP UNIQUE(");
- } else if (strSplit[LINE][COL04].equals("")){
- sbIxCnts.append("CREATE INDEX " + cp.DB_SQL_QUOTEDS[cp.dbType]+ strSplit[LINE][COL02] + cp.DB_SQL_QUOTEDE[cp.dbType]+ " ON " + strSplit[LINE][COL01] + "(");
+ } else {
+ if (cp.dbType == cp.DB_TYPE_MYSQL && !strSplit[LINE][COL03].equals("") && !strSplit[LINE][COL03].equals("BTREE")){
+ sbIxCnts.append("CREATE " + strSplit[LINE][COL03] + " INDEX " + cp.DB_SQL_QUOTEDS[cp.dbType]+ strSplit[LINE][COL02] + cp.DB_SQL_QUOTEDE[cp.dbType]+ " ON " + strSplit[LINE][COL01] + "(");
+ } else {
+ sbIxCnts.append("CREATE INDEX " + cp.DB_SQL_QUOTEDS[cp.dbType]+ strSplit[LINE][COL02] + cp.DB_SQL_QUOTEDE[cp.dbType]+ " ON " + strSplit[LINE][COL01] + "(");
+ }
}
sbIx3Cnts.append("DROP INDEX " + cp.DB_SQL_QUOTEDS[cp.dbType]+ strSplit[LINE][COL02] + cp.DB_SQL_QUOTEDE[cp.dbType]+ cp.SQL_TERMINATOR[cp.dbType]);
- } else if (strSplit[LINE][COL02].equals(indexName)){
+ } else if (strSplit[LINE][COL02].equals(indexName) && strSplit[LINE][COL01].equals(tableName)){
if (colPos.equals("1")){
sbIxCnts.append(cp.DB_SQL_QUOTEDS[cp.dbType]+ colName + cp.DB_SQL_QUOTEDE[cp.dbType]);
if (constraintType.equals("U"))sbIx2Cnts.append(cp.DB_SQL_QUOTEDS[cp.dbType]+ colName + cp.DB_SQL_QUOTEDE[cp.dbType]);
//1st Line Process
else {
if (strSplit[LINE][COL04].equals("P")){
- sbIxCnts.append("ALTER TABLE " + cp.DB_SQL_QUOTEDS[cp.dbType]+ strSplit[LINE][COL01] + cp.DB_SQL_QUOTEDE[cp.dbType]+ " ADD CONSTRAINT " + cp.DB_SQL_QUOTEDS[cp.dbType]+ strSplit[LINE][COL02] + cp.DB_SQL_QUOTEDE[cp.dbType]+ " PRIMARY KEY(");
- sbIx2Cnts.append("ALTER TABLE " + cp.DB_SQL_QUOTEDS[cp.dbType]+ strSplit[LINE][COL01] + cp.DB_SQL_QUOTEDE[cp.dbType]+ " DROP CONSTRAINT " + cp.DB_SQL_QUOTEDS[cp.dbType]+ strSplit[LINE][COL02] + cp.DB_SQL_QUOTEDE[cp.dbType]+ cp.SQL_TERMINATOR[cp.dbType]);
+ if (cp.dbType == cp.DB_TYPE_MYSQL){
+ sbIxCnts.append("ALTER TABLE " + cp.DB_SQL_QUOTEDS[cp.dbType]+ strSplit[LINE][COL01] + cp.DB_SQL_QUOTEDE[cp.dbType]+ " ADD CONSTRAINT PRIMARY KEY(");
+ sbIx2Cnts.append("ALTER TABLE " + cp.DB_SQL_QUOTEDS[cp.dbType]+ strSplit[LINE][COL01] + cp.DB_SQL_QUOTEDE[cp.dbType]+ " DROP PRIMARY KEY" + cp.SQL_TERMINATOR[cp.dbType]);
+ } else {
+ sbIxCnts.append("ALTER TABLE " + cp.DB_SQL_QUOTEDS[cp.dbType]+ strSplit[LINE][COL01] + cp.DB_SQL_QUOTEDE[cp.dbType]+ " ADD CONSTRAINT " + cp.DB_SQL_QUOTEDS[cp.dbType]+ strSplit[LINE][COL02] + cp.DB_SQL_QUOTEDE[cp.dbType]+ " PRIMARY KEY(");
+ sbIx2Cnts.append("ALTER TABLE " + cp.DB_SQL_QUOTEDS[cp.dbType]+ strSplit[LINE][COL01] + cp.DB_SQL_QUOTEDE[cp.dbType]+ " DROP CONSTRAINT " + cp.DB_SQL_QUOTEDS[cp.dbType]+ strSplit[LINE][COL02] + cp.DB_SQL_QUOTEDE[cp.dbType]+ cp.SQL_TERMINATOR[cp.dbType]);
+ }
} else if (strSplit[LINE][COL04].equals("U")){
sbIxCnts.append("ALTER TABLE " + cp.DB_SQL_QUOTEDS[cp.dbType]+ strSplit[LINE][COL01] + cp.DB_SQL_QUOTEDE[cp.dbType]+ " ADD CONSTRAINT " + cp.DB_SQL_QUOTEDS[cp.dbType]+ strSplit[LINE][COL02] + cp.DB_SQL_QUOTEDE[cp.dbType]+ " UNIQUE(");
sbIx2Cnts.append("ALTER TABLE " + cp.DB_SQL_QUOTEDS[cp.dbType]+ strSplit[LINE][COL01] + cp.DB_SQL_QUOTEDE[cp.dbType]+ " DROP UNIQUE(");
- } else if (strSplit[LINE][COL04].equals("")){
- sbIxCnts.append("CREATE INDEX " + cp.DB_SQL_QUOTEDS[cp.dbType]+ strSplit[LINE][COL02] + cp.DB_SQL_QUOTEDE[cp.dbType]+ " ON " + cp.DB_SQL_QUOTEDS[cp.dbType]+ strSplit[LINE][COL01] + cp.DB_SQL_QUOTEDE[cp.dbType]+ "(");
+ } else {
+ if (cp.dbType == cp.DB_TYPE_MYSQL && !strSplit[LINE][COL03].equals("") && !strSplit[LINE][COL03].equals("BTREE")){
+ sbIxCnts.append("CREATE " + strSplit[LINE][COL03] + " INDEX " + cp.DB_SQL_QUOTEDS[cp.dbType]+ strSplit[LINE][COL02] + cp.DB_SQL_QUOTEDE[cp.dbType]+ " ON " + cp.DB_SQL_QUOTEDS[cp.dbType]+ strSplit[LINE][COL01] + cp.DB_SQL_QUOTEDE[cp.dbType]+ "(");
+ } else {
+ sbIxCnts.append("CREATE INDEX " + cp.DB_SQL_QUOTEDS[cp.dbType]+ strSplit[LINE][COL02] + cp.DB_SQL_QUOTEDE[cp.dbType]+ " ON " + cp.DB_SQL_QUOTEDS[cp.dbType]+ strSplit[LINE][COL01] + cp.DB_SQL_QUOTEDE[cp.dbType]+ "(");
+ }
}
sbIx3Cnts.append("DROP INDEX " + cp.DB_SQL_QUOTEDS[cp.dbType]+ strSplit[LINE][COL02] + cp.DB_SQL_QUOTEDE[cp.dbType]+ cp.SQL_TERMINATOR[cp.dbType]);
}
for(int i=0;i<strSplit[IDX_CONTENTS_1].length;i++)if(!strSplit[IDX_CONTENTS_1][i].trim().equals(""))pw[W_CREATE_UKEY].println(strSplit[IDX_CONTENTS_1][i]);
strSplit[IDX_CONTENTS_2] = CmnUtils.split(sbIx2Cnts.toString(), cp.lineSeparator);
for(int i=0;i<strSplit[IDX_CONTENTS_2].length;i++)if(!strSplit[IDX_CONTENTS_2][i].trim().equals(""))pw[W_DROP_UKEY].println(strSplit[IDX_CONTENTS_2][i]);
- } else if (constraintType.equals("")){
+ } else {
strSplit[IDX_CONTENTS_1] = CmnUtils.split(sbIxCnts.toString(), cp.lineSeparator);
for(int i=0;i<strSplit[IDX_CONTENTS_1].length;i++)if(!strSplit[IDX_CONTENTS_1][i].trim().equals(""))pw[W_CREATE_INDEX].println(strSplit[IDX_CONTENTS_1][i]);
}
dao.executeSql();
alData = dao.getArrayList();
columnCount = dao.getColumnCount();
- if (cp.dbType == cp.DB_TYPE_ORACLE){
+ if (cp.dbType == cp.DB_TYPE_ORACLE || cp.dbType == cp.DB_TYPE_MYSQL){
pw[CONTENTS].println("/* " + strSplit[LINE][COL01] + " */");
- pw[CONTENTS].println("CREATE OR REPLACE VIEW " + cp.DB_SQL_QUOTEDS[cp.dbType]+ strSplit[LINE][COL01] + cp.DB_SQL_QUOTEDE[cp.dbType]);
+ if (cp.dbType == cp.DB_TYPE_ORACLE){
+ pw[CONTENTS].println("CREATE OR REPLACE VIEW " + cp.DB_SQL_QUOTEDS[cp.dbType]+ strSplit[LINE][COL01] + cp.DB_SQL_QUOTEDE[cp.dbType]);
+ } else {
+ pw[CONTENTS].println("CREATE VIEW " + cp.DB_SQL_QUOTEDS[cp.dbType]+ strSplit[LINE][COL01] + cp.DB_SQL_QUOTEDE[cp.dbType]);
+ }
pw[CONTENTS].println("(");
for(int i=0;i<alData.size();i++){
if ((i+1) % columnCount == 1){
strSplit[LINE] = CmnUtils.split(strLine, cp.delimiter);
CmnUtils.debugPrint("'" + strSplit[LINE][COL01] + "'");
pw[CREATE].println(cp.SQL_PREFIX[cp.dbType] + "PROCEDURES/" + strSplit[LINE][COL01] + ".sql");
- pw[DROP].println("DROP " + strSplit[LINE][COL02] + " " + strSplit[LINE][COL01] + cp.SQL_TERMINATOR[cp.dbType]);
+ pw[DROP].println("DROP " + strSplit[LINE][COL02] + " " + cp.DB_SQL_QUOTEDS[cp.dbType] + strSplit[LINE][COL01] + cp.DB_SQL_QUOTEDE[cp.dbType] + cp.SQL_TERMINATOR[cp.dbType]);
if (strSplit[LINE].length>1)CmnUtils.debugPrint("'" + strSplit[LINE][COL02] + "'");
pw[CONTENTS] = new PrintWriter(new BufferedWriter(new FileWriter(new File(subFolder + "/" + strSplit[LINE][COL01] + ".sql"))));
if (cp.dbType == cp.DB_TYPE_ORACLE){
pw[CONTENTS].println("/* " + strSplit[LINE][COL01] + " */");
pw[CONTENTS].print("CREATE OR REPLACE ");
+ } else if (cp.dbType == cp.DB_TYPE_MYSQL){
+ if(strSplit[LINE][COL02].equals("TRIGGER")){
+ pw[CONTENTS].println("CREATE " + strSplit[LINE][COL02] + " " + cp.DB_SQL_QUOTEDS[cp.dbType] + strSplit[LINE][COL01] + cp.DB_SQL_QUOTEDE[cp.dbType] + " " + strSplit[LINE][COL03] + " " + strSplit[LINE][COL04] + " ON " + cp.DB_SQL_QUOTEDS[cp.dbType] + strSplit[LINE][COL05] + cp.DB_SQL_QUOTEDE[cp.dbType] + " FOR EACH ROW");
+ } else {
+ pw[CONTENTS].println("CREATE " + strSplit[LINE][COL02] + " " + cp.DB_SQL_QUOTEDS[cp.dbType] + strSplit[LINE][COL01] + cp.DB_SQL_QUOTEDE[cp.dbType]);
+ }
}
stmt = dao.prepareSql(cp.sql_proc[cp.dbType]);
stmt.setString(1,strSplit[LINE][COL02]);
strSplit[LINE] = CmnUtils.split(strLine, cp.delimiter);
if(!fkName.equals(strSplit[LINE][COL02]) && !fkName.equals("")){
pw[CREATE].println("ALTER TABLE " + cp.DB_SQL_QUOTEDS[cp.dbType]+ tbName + cp.DB_SQL_QUOTEDE[cp.dbType]+ " ADD CONSTRAINT " + cp.DB_SQL_QUOTEDS[cp.dbType]+ fkName + cp.DB_SQL_QUOTEDE[cp.dbType]+ " FOREIGN KEY (" + cp.DB_SQL_QUOTEDS[cp.dbType]+ fkCol + cp.DB_SQL_QUOTEDE[cp.dbType]+ ") REFERENCES " + cp.DB_SQL_QUOTEDS[cp.dbType]+ rtbName + cp.DB_SQL_QUOTEDE[cp.dbType]+ " (" + cp.DB_SQL_QUOTEDS[cp.dbType]+ rfkCol + cp.DB_SQL_QUOTEDE[cp.dbType]+ ")" + cp.SQL_TERMINATOR[cp.dbType]);
- pw[DROP].println("ALTER TABLE " + cp.DB_SQL_QUOTEDS[cp.dbType]+ tbName + cp.DB_SQL_QUOTEDE[cp.dbType]+ " DROP CONSTRAINT " + cp.DB_SQL_QUOTEDS[cp.dbType]+ fkName + cp.DB_SQL_QUOTEDE[cp.dbType]+ cp.SQL_TERMINATOR[cp.dbType]);
+ if(cp.dbType == cp.DB_TYPE_MYSQL){
+ pw[DROP].println("ALTER TABLE " + cp.DB_SQL_QUOTEDS[cp.dbType]+ tbName + cp.DB_SQL_QUOTEDE[cp.dbType]+ " DROP FOREIGN KEY " + cp.DB_SQL_QUOTEDS[cp.dbType]+ fkName + cp.DB_SQL_QUOTEDE[cp.dbType]+ cp.SQL_TERMINATOR[cp.dbType]);
+ } else {
+ pw[DROP].println("ALTER TABLE " + cp.DB_SQL_QUOTEDS[cp.dbType]+ tbName + cp.DB_SQL_QUOTEDE[cp.dbType]+ " DROP CONSTRAINT " + cp.DB_SQL_QUOTEDS[cp.dbType]+ fkName + cp.DB_SQL_QUOTEDE[cp.dbType]+ cp.SQL_TERMINATOR[cp.dbType]);
+ }
for(int i=0;i<strSplit[LINE].length;i++)CmnUtils.debugPrint("'" + strSplit[LINE][i] + "'");
tbName = strSplit[LINE][COL01];
fkName = strSplit[LINE][COL02];
}
pw[CREATE].println("ALTER TABLE " + cp.DB_SQL_QUOTEDS[cp.dbType]+ tbName + cp.DB_SQL_QUOTEDE[cp.dbType]+ " ADD CONSTRAINT " + cp.DB_SQL_QUOTEDS[cp.dbType]+ fkName + cp.DB_SQL_QUOTEDE[cp.dbType]+ " FOREIGN KEY (" + cp.DB_SQL_QUOTEDS[cp.dbType]+ fkCol + cp.DB_SQL_QUOTEDE[cp.dbType]+ ") REFERENCES " + cp.DB_SQL_QUOTEDS[cp.dbType]+ rtbName + cp.DB_SQL_QUOTEDE[cp.dbType]+ " (" + cp.DB_SQL_QUOTEDS[cp.dbType]+ rfkCol + cp.DB_SQL_QUOTEDE[cp.dbType]+ ")" + cp.SQL_TERMINATOR[cp.dbType]);
- pw[DROP].println("ALTER TABLE " + cp.DB_SQL_QUOTEDS[cp.dbType]+ tbName + cp.DB_SQL_QUOTEDE[cp.dbType]+ " DROP CONSTRAINT " + cp.DB_SQL_QUOTEDS[cp.dbType]+ fkName + cp.DB_SQL_QUOTEDE[cp.dbType]+ cp.SQL_TERMINATOR[cp.dbType]);
- for(int i=0;i<pw.length;i++){
+ if(cp.dbType == cp.DB_TYPE_MYSQL){
+ pw[DROP].println("ALTER TABLE " + cp.DB_SQL_QUOTEDS[cp.dbType]+ tbName + cp.DB_SQL_QUOTEDE[cp.dbType]+ " DROP FOREIGN KEY " + cp.DB_SQL_QUOTEDS[cp.dbType]+ fkName + cp.DB_SQL_QUOTEDE[cp.dbType]+ cp.SQL_TERMINATOR[cp.dbType]);
+ } else {
+ pw[DROP].println("ALTER TABLE " + cp.DB_SQL_QUOTEDS[cp.dbType]+ tbName + cp.DB_SQL_QUOTEDE[cp.dbType]+ " DROP CONSTRAINT " + cp.DB_SQL_QUOTEDS[cp.dbType]+ fkName + cp.DB_SQL_QUOTEDE[cp.dbType]+ cp.SQL_TERMINATOR[cp.dbType]);
+ }
+ for(int i=0;i<pw.length;i++){
if(pw[i]!=null){
pw[i].close();
pw[i]=null;
OSDN Git Service
