• R/O
  • HTTP
  • SSH
  • HTTPS

rapideact: Commit


Commit MetaInfo

Revisionb685826f51307319f9bfe47c2333fcf76b13a394 (tree)
Time2017-06-15 21:04:00
Authorkuboryu <kuboryu@yaho...>
Commiterkuboryu

Log Message

20170615

Change Summary

Incremental Difference

--- a/com/rapide_act/CmnProps.java
+++ b/com/rapide_act/CmnProps.java
@@ -40,7 +40,7 @@ public class CmnProps{
4040 protected static final String [] DB_SQL_QUOTEDS = {"\"", "[", "`", "\"", "\"", "\""};
4141 protected static final String [] DB_SQL_QUOTEDE = {"\"", "]", "`", "\"", "\"", "\""};
4242 protected static final String [] SQL_TERMINATOR = {";", System.getProperty("line.separator") + "GO", ";", ";", ";", ";"};
43- protected static final String [] SQL_PREFIX = {"@", ":r ", "", "", "", ""};
43+ protected static final String [] SQL_PREFIX = {"@", ":r ", "source ", "", "", ""};
4444 protected static final String [] SQL_COMMENT_MARK = {"--", "--", "--", "--", "--", "--"};
4545 protected static final int MASK_PTN_ALL = 0;
4646 protected static final int MASK_PTN_ALT = 1;
@@ -594,22 +594,7 @@ public class CmnProps{
594594 + " inner join sys.schemas as b "
595595 + " on a.schema_id = b.schema_id "
596596 + "order by synonym_name,table_owner,table_name",
597- "select "
598- + " c.name as table_name, "
599- + " b.index_id, "
600- + " d.name as index_name , "
601- + " a.type_desc as allocation_type, "
602- + " a.data_pages, "
603- + " partition_number "
604- + "from "
605- + " sys.allocation_units as a "
606- + " inner join sys.partitions as b "
607- + " on a.container_id = b.partition_id "
608- + " inner join sys.objects as c "
609- + " on b.object_id = c.object_id and c.type = 'U' "
610- + " inner join sys.indexes as d "
611- + " on b.index_id = d.index_id and d.object_id = b.object_id "
612- + "order by c.name, b.index_id",
597+ "",
613598 "select distinct "
614599 + " c.name as table_name, "
615600 + " a.name as constraint_name, "
@@ -634,6 +619,123 @@ public class CmnProps{
634619 + " inner join sys.key_constraints as g "
635620 + " on a.referenced_object_id = g.parent_object_id "
636621 + "order by table_name,constraint_name, position"
622+ },
623+ { //MYSQL
624+ "select "
625+ + " a.table_name,"
626+ + " replace(replace(a.table_comment, char(13), ''), char(10), '') as table_comments, "
627+ + " b.column_name,"
628+ + " replace(replace(b.column_comment, char(13), ''), char(10), '') as column_comment, "
629+ + " b.column_type,"
630+ + " b.character_maximum_length as data_precision,"
631+ + " b.numeric_precision as data_precision,"
632+ + " b.numeric_scale as data_scale,"
633+ + " case b.is_nullable"
634+ + " when 'NO' then '〇'"
635+ + " else null "
636+ + " end as nullable,"
637+ + " column_default as data_default "
638+ + "from "
639+ + " information_schema.tables a,"
640+ + " information_schema.columns b "
641+ + "where "
642+ + " a.table_schema = database()"
643+ + " and a.table_type = 'BASE TABLE'"
644+ + " and a.table_schema = b.table_schema "
645+ + " and a.table_name = b.table_name "
646+ + "order by a.table_name,b.ordinal_position",
647+ "select "
648+ + " a.table_name, "
649+ + " b.constraint_name as index_name,"
650+ + " '' as index_type, "
651+ + " case b.constraint_type "
652+ + " when 'PRIMARY KEY' then 'P' "
653+ + " when 'UNIQUE' then 'U' "
654+ + " end as constraint_type,"
655+ + " c.column_name,"
656+ + " c.ordinal_position as column_position"
657+ + " from"
658+ + " information_schema.tables a,"
659+ + " information_schema.table_constraints b,"
660+ + " information_schema.key_column_usage c"
661+ + " where"
662+ + " a.table_schema = database()"
663+ + " and a.table_type = 'BASE TABLE'"
664+ + " and a.table_schema = b.table_schema"
665+ + " and a.table_name = b.table_name"
666+ + " and b.table_schema = c.table_schema"
667+ + " and b.table_name = c.table_name"
668+ + " and b.constraint_name = c.constraint_name"
669+ + " and b.constraint_type in('PRIMARY KEY','UNIQUE')"
670+ + " union select"
671+ + " a.table_name,"
672+ + " a.index_name,"
673+ + " b.index_type as index_type, "
674+ + " '' as constraint_type,"
675+ + " a.column_name,"
676+ + " a.seq_in_index as column_position"
677+ + " from"
678+ + " information_schema.statistics a,"
679+ + " information_schema.statistics b"
680+ + " where"
681+ + " a.table_schema = database()"
682+ + " and a.table_schema = b.table_schema"
683+ + " and a.table_name = b.table_name"
684+ + " and a.index_name = b.index_name"
685+ + " and a.non_unique = 1"
686+ + " order by table_name,index_name,column_position",
687+ "select "
688+ + " a.table_name as view_name, "
689+ + " '' as comments "
690+ + "from "
691+ + " information_schema.views a "
692+ + " where"
693+ + " table_schema = database()"
694+ + " order by "
695+ + " a.table_name",
696+ "select "
697+ + " routine_name as object_name, "
698+ + " routine_type as object_type, "
699+ + " '' as action_timing, "
700+ + " '' as event_action, "
701+ + " '' as event_object "
702+ + " from "
703+ + " information_schema.routines "
704+ + " where"
705+ + " routine_schema = database()"
706+ + " and (routine_type = 'PROCEDURE' or routine_type = 'FUNCTION') "
707+ + " union select "
708+ + " trigger_name as object_name, "
709+ + " 'TRIGGER' as object_type, "
710+ + " action_timing as action_timing, "
711+ + " event_manipulation as event_action, "
712+ + " event_object_table as event_object "
713+ + " from "
714+ + " information_schema.triggers "
715+ + " where"
716+ + " trigger_schema = database()"
717+ + " order by object_name,object_type ",
718+ "",
719+ "",
720+ "",
721+ "select distinct "
722+ + " b.table_name, "
723+ + " b.constraint_name, "
724+ + " b.column_name, "
725+ + " b.ordinal_position as position, "
726+ + " b.referenced_table_name as r_table_name, "
727+ + " b.referenced_column_name as r_column_name, "
728+ + " c.ordinal_position as r_position "
729+ + " from "
730+ + " information_schema.referential_constraints a, "
731+ + " information_schema.key_column_usage b, "
732+ + " information_schema.columns c "
733+ + " where "
734+ + " a.constraint_schema = database() "
735+ + " and a.constraint_schema = b.constraint_schema "
736+ + " and a.constraint_name = b.constraint_name "
737+ + " and b.referenced_table_name = c.table_name "
738+ + " and b.referenced_column_name = c.column_name "
637739 }
638740 };
639741 protected static String sql_view_text[] = {
@@ -644,7 +746,8 @@ public class CmnProps{
644746 + "on b.object_id = a.object_id "
645747 + "inner join sys.sql_modules c "
646748 + "on c.object_id = b.object_id "
647- + "where a.name = ?"
749+ + "where a.name = ?",
750+ "select view_definition from information_schema.views where table_name = ?"
648751 };
649752 protected static String sql_view_column_and_comment[] = {
650753 "select "
@@ -668,7 +771,15 @@ public class CmnProps{
668771 + " inner join sys.types c on b.system_type_id = c.system_type_id "
669772 + " where a.type = 'V' "
670773 + " and c.user_type_id <> 256 "
671- + " order by b.column_id"
774+ + " order by b.column_id",
775+ " select "
776+ + " column_name,"
777+ + " replace(replace(column_comment, char(13), ''), char(10), '') as column_comment "
778+ + " from"
779+ + " information_schema.columns "
780+ + " where"
781+ + " table_name = ? "
782+ + " order by ordinal_position"
672783 };
673784 protected static String sql_proc[] = {
674785 "select text from user_source where type = ? and name = ? order by line",
@@ -694,7 +805,30 @@ public class CmnProps{
694805 + "on b.object_id = a.object_id "
695806 + "inner join sys.sql_modules c "
696807 + "on c.object_id = b.object_id) d "
697- + "where d.type = ? and d.name = ?"
808+ + "where d.type = ? and d.name = ?",
809+ "select a.definition "
810+ + "from "
811+ + "( "
812+ + "select "
813+ + " routine_name as object_name, "
814+ + " routine_type as object_type, "
815+ + " routine_definition as definition "
816+ + "from "
817+ + " information_schema.routines "
818+ + "where "
819+ + " routine_schema = database() "
820+ + "union select "
821+ + " trigger_name as object_name, "
822+ + " 'TRIGGER' as object_type, "
823+ + " action_statement as definition "
824+ + "from "
825+ + " information_schema.triggers "
826+ + "where "
827+ + " trigger_schema = database() "
828+ + ") a "
829+ + "where "
830+ + " a.object_type = ? "
831+ + " and a.object_name = ? "
698832 };
699833
700834 protected String user = null;
--- a/com/rapide_act/CmnUtils.java
+++ b/com/rapide_act/CmnUtils.java
@@ -35,6 +35,17 @@ import java.nio.charset.Charset;
3535
3636 public class CmnUtils{
3737
38+ protected static void emptyFile(String _file) throws Exception{
39+ PrintWriter pw = null;
40+ try {
41+ pw = new PrintWriter(new BufferedWriter(new FileWriter(new File(_file))));
42+ pw.close();
43+ }
44+ catch (Exception e){
45+ throw e;
46+ }
47+ }
48+
3849 protected static void writeCsv(String _file, int _columnCount, ArrayList<String> _alData) throws Exception{
3950 PrintWriter pw = null;
4051 try {
@@ -573,6 +584,21 @@ public class CmnUtils{
573584 return retVal;
574585 }
575586
587+ protected static boolean isReserved(String _str) throws Exception{
588+ if(
589+ _str.toUpperCase().equals("CURRENT_DATE") ||
590+ _str.toUpperCase().equals("CURRENT_TIME") ||
591+ _str.toUpperCase().equals("CURRENT_TIMESTAMP") ||
592+ _str.toUpperCase().equals("CURRENT_USER") ||
593+ _str.toUpperCase().equals("LOCALTIME") ||
594+ _str.toUpperCase().equals("LOCALTIMESTAMP") ||
595+ _str.toUpperCase().equals("NULL")){
596+ return true;
597+ } else {
598+ return false;
599+ }
600+ }
601+
576602 protected static String[] getSystemProperty(String _str) throws Exception{
577603 String [] retValue = null;
578604 if(System.getProperty(_str)!=null){
--- a/com/rapide_act/RapideMetaUnloader.java
+++ b/com/rapide_act/RapideMetaUnloader.java
@@ -169,7 +169,7 @@ public class RapideMetaUnloader{
169169 cp = new CmnProps();
170170 cp.setProperty(database);
171171
172- if (cp.dbType > cp.DB_TYPE_SQLSERVER || cp.dbType <0){
172+ if (cp.dbType > cp.DB_TYPE_MYSQL || cp.dbType <0){
173173 throw new Exception("データベースが対象外です。[" + cp.DB_TYPE_NAME[cp.dbType] + "]");
174174 }
175175 if(cp.outFolder != null){
@@ -191,8 +191,12 @@ public class RapideMetaUnloader{
191191 conn = dao.connect();
192192 // Def tsv Writing start
193193 for(int i=0;i<cp.meta_sql[cp.dbType].length;i++){
194- dao.select(cp.meta_sql[cp.dbType][i]);
195- CmnUtils.writeSeparator(folder + "/" + cp.WK_FILE_NAME[i] + "_" + database +"." + cp.fileExtension, dao.getColumnCount(), dao.getArrayList(), cp.delimiter);
194+ if(!cp.meta_sql[cp.dbType][i].equals("")){
195+ dao.select(cp.meta_sql[cp.dbType][i]);
196+ CmnUtils.writeSeparator(folder + "/" + cp.WK_FILE_NAME[i] + "_" + database +"." + cp.fileExtension, dao.getColumnCount(), dao.getArrayList(), cp.delimiter);
197+ } else {
198+ CmnUtils.emptyFile(folder + "/" + cp.WK_FILE_NAME[i] + "_" + database +"." + cp.fileExtension);
199+ }
196200 }
197201 // Def tsv Writing end
198202
@@ -227,7 +231,7 @@ public class RapideMetaUnloader{
227231 if(!colCmnt.equals(""))sbTbCmnt.append(cp.lineSeparator);
228232 if(CmnUtils.isColPrec(colType, colPrec)){
229233 sbTbCnts.append("(" + colPrec);
230- if (!colScale.equals("") && !colScale.equals("0"))sbTbCnts.append("," + colScale);
234+ if (!CmnUtils.isEmpty(colScale) && !colScale.equals("0"))sbTbCnts.append("," + colScale);
231235 sbTbCnts.append(")");
232236 } else if (CmnUtils.isColLength(colType)){
233237 if(colLen.equals("-1")){
@@ -236,15 +240,23 @@ public class RapideMetaUnloader{
236240 sbTbCnts.append("(" + CmnUtils.getColLength(colType, colLen) + ")");
237241 }
238242 }
239- if (!colDflt.equals("")){
243+ if (!CmnUtils.isEmpty(colDflt)){
240244 if(CmnUtils.split(strLine, cp.SQL_COMMENT_MARK[cp.dbType]).length > 0){
241245 String [] colDfltAry = CmnUtils.split(colDflt, cp.SQL_COMMENT_MARK[cp.dbType]);
242- sbTbCnts.append(" DEFAULT " + colDfltAry[0].trim());
246+ if (cp.dbType == cp.DB_TYPE_MYSQL && !CmnUtils.isReserved(colDfltAry[0].trim())){
247+ sbTbCnts.append(" DEFAULT '" + colDfltAry[0].trim() + "'");
248+ } else {
249+ sbTbCnts.append(" DEFAULT " + colDfltAry[0].trim());
250+ }
243251 } else {
244- sbTbCnts.append(" DEFAULT " + colDflt.trim());
252+ if (cp.dbType == cp.DB_TYPE_MYSQL && !CmnUtils.isReserved(colDflt.trim())){
253+ sbTbCnts.append(" DEFAULT " + colDflt.trim() + "'");
254+ } else {
255+ sbTbCnts.append(" DEFAULT " + colDflt.trim());
256+ }
245257 }
246258 }
247- if (!colNoN.equals("")){
259+ if (!CmnUtils.isEmpty(colNoN)){
248260 sbTbCnts.append(" NOT NULL");
249261 } else {
250262 sbTbCnts.append(" NULL");
@@ -270,8 +282,8 @@ public class RapideMetaUnloader{
270282 sbTbCnts.append(cp.lineSeparator);
271283 sbTbCnts.append("(");
272284 sbTbCnts.append(cp.lineSeparator);
273- if(!strSplit[LINE][COL02].equals(""))sbTbCmnt.append(commentOnTable(cp, strSplit[LINE][COL01], strSplit[LINE][COL02], cp.TYPE_TABLE));
274- if(!strSplit[LINE][COL02].equals(""))sbTbCmnt.append(cp.lineSeparator);
285+ if(!CmnUtils.isEmpty(strSplit[LINE][COL02]))sbTbCmnt.append(commentOnTable(cp, strSplit[LINE][COL01], strSplit[LINE][COL02], cp.TYPE_TABLE));
286+ if(!CmnUtils.isEmpty(strSplit[LINE][COL02]))sbTbCmnt.append(cp.lineSeparator);
275287 } else {
276288 sbTbCnts.append(",");
277289 sbTbCnts.append(cp.lineSeparator);
@@ -282,8 +294,8 @@ public class RapideMetaUnloader{
282294 sbTbCnts.append(cp.lineSeparator);
283295 sbTbCnts.append("(");
284296 sbTbCnts.append(cp.lineSeparator);
285- if(!strSplit[LINE][COL02].equals(""))sbTbCmnt.append(commentOnTable(cp, strSplit[LINE][COL01], strSplit[LINE][COL02], cp.TYPE_TABLE));
286- if(!strSplit[LINE][COL02].equals(""))sbTbCmnt.append(cp.lineSeparator);
297+ if(!CmnUtils.isEmpty(strSplit[LINE][COL02]))sbTbCmnt.append(commentOnTable(cp, strSplit[LINE][COL01], strSplit[LINE][COL02], cp.TYPE_TABLE));
298+ if(!CmnUtils.isEmpty(strSplit[LINE][COL02]))sbTbCmnt.append(cp.lineSeparator);
287299 }
288300 for(int i=0;i<strSplit[LINE].length;i++)CmnUtils.debugPrint("'" + strSplit[LINE][i] + "'");
289301
@@ -309,7 +321,7 @@ public class RapideMetaUnloader{
309321 }
310322 if(CmnUtils.isColPrec(colType, colPrec)){
311323 sbTbCnts.append("(" + colPrec);
312- if (!colScale.equals("") && !colScale.equals("0"))sbTbCnts.append("," + colScale);
324+ if (!CmnUtils.isEmpty(colScale) && !CmnUtils.isEmpty(colScale))sbTbCnts.append("," + colScale);
313325 sbTbCnts.append(")");
314326 } else if (CmnUtils.isColLength(colType)){
315327 if(colLen.equals("-1")){
@@ -318,8 +330,14 @@ public class RapideMetaUnloader{
318330 sbTbCnts.append("(" + CmnUtils.getColLength(colType, colLen) + ")");
319331 }
320332 }
321- if (!colDflt.equals(""))sbTbCnts.append(" DEFAULT " + colDflt.trim());
322- if (!colNoN.equals("")){
333+ if (!CmnUtils.isEmpty(colDflt)){
334+ if (cp.dbType == cp.DB_TYPE_MYSQL && !CmnUtils.isReserved(colDflt.trim())){
335+ sbTbCnts.append(" DEFAULT '" + colDflt.trim() + "'");
336+ } else {
337+ sbTbCnts.append(" DEFAULT " + colDflt.trim());
338+ }
339+ }
340+ if (!CmnUtils.isEmpty(colNoN)){
323341 sbTbCnts.append(" NOT NULL");
324342 } else {
325343 sbTbCnts.append(" NULL");
@@ -375,7 +393,7 @@ public class RapideMetaUnloader{
375393 strSplit[LINE] = CmnUtils.split(strLine, cp.delimiter);
376394 //Not 1st Line Process
377395 if (!indexName.equals("")){
378- if (!strSplit[LINE][COL02].equals(indexName)){
396+ if (!strSplit[LINE][COL02].equals(indexName) || !strSplit[LINE][COL01].equals(tableName)){
379397 //Post Process
380398 if (colPos.equals("1")){
381399 sbIxCnts.append(cp.DB_SQL_QUOTEDS[cp.dbType]+ colName + cp.DB_SQL_QUOTEDE[cp.dbType]);
@@ -401,7 +419,7 @@ public class RapideMetaUnloader{
401419 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]);
402420 strSplit[IDX_CONTENTS_2] = CmnUtils.split(sbIx2Cnts.toString(), cp.lineSeparator);
403421 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]);
404- } else if (constraintType.equals("")){
422+ } else {
405423 strSplit[IDX_CONTENTS_1] = CmnUtils.split(sbIxCnts.toString(), cp.lineSeparator);
406424 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]);
407425 }
@@ -415,16 +433,25 @@ public class RapideMetaUnloader{
415433
416434 //Index Changed Process
417435 if (strSplit[LINE][COL04].equals("P")){
418- 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(");
419- 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]);
436+ if (cp.dbType == cp.DB_TYPE_MYSQL){
437+ sbIxCnts.append("ALTER TABLE " + cp.DB_SQL_QUOTEDS[cp.dbType]+ strSplit[LINE][COL01] + cp.DB_SQL_QUOTEDE[cp.dbType]+ " ADD CONSTRAINT PRIMARY KEY(");
438+ 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]);
439+ } else {
440+ 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(");
441+ 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]);
442+ }
420443 } else if (strSplit[LINE][COL04].equals("U")){
421444 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(");
422445 sbIx2Cnts.append("ALTER TABLE " + cp.DB_SQL_QUOTEDS[cp.dbType]+ strSplit[LINE][COL01] + cp.DB_SQL_QUOTEDE[cp.dbType]+ " DROP UNIQUE(");
423- } else if (strSplit[LINE][COL04].equals("")){
424- sbIxCnts.append("CREATE INDEX " + cp.DB_SQL_QUOTEDS[cp.dbType]+ strSplit[LINE][COL02] + cp.DB_SQL_QUOTEDE[cp.dbType]+ " ON " + strSplit[LINE][COL01] + "(");
446+ } else {
447+ if (cp.dbType == cp.DB_TYPE_MYSQL && !strSplit[LINE][COL03].equals("") && !strSplit[LINE][COL03].equals("BTREE")){
448+ 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] + "(");
449+ } else {
450+ sbIxCnts.append("CREATE INDEX " + cp.DB_SQL_QUOTEDS[cp.dbType]+ strSplit[LINE][COL02] + cp.DB_SQL_QUOTEDE[cp.dbType]+ " ON " + strSplit[LINE][COL01] + "(");
451+ }
425452 }
426453 sbIx3Cnts.append("DROP INDEX " + cp.DB_SQL_QUOTEDS[cp.dbType]+ strSplit[LINE][COL02] + cp.DB_SQL_QUOTEDE[cp.dbType]+ cp.SQL_TERMINATOR[cp.dbType]);
427- } else if (strSplit[LINE][COL02].equals(indexName)){
454+ } else if (strSplit[LINE][COL02].equals(indexName) && strSplit[LINE][COL01].equals(tableName)){
428455 if (colPos.equals("1")){
429456 sbIxCnts.append(cp.DB_SQL_QUOTEDS[cp.dbType]+ colName + cp.DB_SQL_QUOTEDE[cp.dbType]);
430457 if (constraintType.equals("U"))sbIx2Cnts.append(cp.DB_SQL_QUOTEDS[cp.dbType]+ colName + cp.DB_SQL_QUOTEDE[cp.dbType]);
@@ -438,13 +465,22 @@ public class RapideMetaUnloader{
438465 //1st Line Process
439466 else {
440467 if (strSplit[LINE][COL04].equals("P")){
441- 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(");
442- 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]);
468+ if (cp.dbType == cp.DB_TYPE_MYSQL){
469+ sbIxCnts.append("ALTER TABLE " + cp.DB_SQL_QUOTEDS[cp.dbType]+ strSplit[LINE][COL01] + cp.DB_SQL_QUOTEDE[cp.dbType]+ " ADD CONSTRAINT PRIMARY KEY(");
470+ 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]);
471+ } else {
472+ 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(");
473+ 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]);
474+ }
443475 } else if (strSplit[LINE][COL04].equals("U")){
444476 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(");
445477 sbIx2Cnts.append("ALTER TABLE " + cp.DB_SQL_QUOTEDS[cp.dbType]+ strSplit[LINE][COL01] + cp.DB_SQL_QUOTEDE[cp.dbType]+ " DROP UNIQUE(");
446- } else if (strSplit[LINE][COL04].equals("")){
447- 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]+ "(");
478+ } else {
479+ if (cp.dbType == cp.DB_TYPE_MYSQL && !strSplit[LINE][COL03].equals("") && !strSplit[LINE][COL03].equals("BTREE")){
480+ 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]+ "(");
481+ } else {
482+ 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]+ "(");
483+ }
448484 }
449485 sbIx3Cnts.append("DROP INDEX " + cp.DB_SQL_QUOTEDS[cp.dbType]+ strSplit[LINE][COL02] + cp.DB_SQL_QUOTEDE[cp.dbType]+ cp.SQL_TERMINATOR[cp.dbType]);
450486 }
@@ -484,7 +520,7 @@ public class RapideMetaUnloader{
484520 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]);
485521 strSplit[IDX_CONTENTS_2] = CmnUtils.split(sbIx2Cnts.toString(), cp.lineSeparator);
486522 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]);
487- } else if (constraintType.equals("")){
523+ } else {
488524 strSplit[IDX_CONTENTS_1] = CmnUtils.split(sbIxCnts.toString(), cp.lineSeparator);
489525 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]);
490526 }
@@ -522,9 +558,13 @@ public class RapideMetaUnloader{
522558 dao.executeSql();
523559 alData = dao.getArrayList();
524560 columnCount = dao.getColumnCount();
525- if (cp.dbType == cp.DB_TYPE_ORACLE){
561+ if (cp.dbType == cp.DB_TYPE_ORACLE || cp.dbType == cp.DB_TYPE_MYSQL){
526562 pw[CONTENTS].println("/* " + strSplit[LINE][COL01] + " */");
527- pw[CONTENTS].println("CREATE OR REPLACE VIEW " + cp.DB_SQL_QUOTEDS[cp.dbType]+ strSplit[LINE][COL01] + cp.DB_SQL_QUOTEDE[cp.dbType]);
563+ if (cp.dbType == cp.DB_TYPE_ORACLE){
564+ pw[CONTENTS].println("CREATE OR REPLACE VIEW " + cp.DB_SQL_QUOTEDS[cp.dbType]+ strSplit[LINE][COL01] + cp.DB_SQL_QUOTEDE[cp.dbType]);
565+ } else {
566+ pw[CONTENTS].println("CREATE VIEW " + cp.DB_SQL_QUOTEDS[cp.dbType]+ strSplit[LINE][COL01] + cp.DB_SQL_QUOTEDE[cp.dbType]);
567+ }
528568 pw[CONTENTS].println("(");
529569 for(int i=0;i<alData.size();i++){
530570 if ((i+1) % columnCount == 1){
@@ -585,12 +625,18 @@ public class RapideMetaUnloader{
585625 strSplit[LINE] = CmnUtils.split(strLine, cp.delimiter);
586626 CmnUtils.debugPrint("'" + strSplit[LINE][COL01] + "'");
587627 pw[CREATE].println(cp.SQL_PREFIX[cp.dbType] + "PROCEDURES/" + strSplit[LINE][COL01] + ".sql");
588- pw[DROP].println("DROP " + strSplit[LINE][COL02] + " " + strSplit[LINE][COL01] + cp.SQL_TERMINATOR[cp.dbType]);
628+ 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]);
589629 if (strSplit[LINE].length>1)CmnUtils.debugPrint("'" + strSplit[LINE][COL02] + "'");
590630 pw[CONTENTS] = new PrintWriter(new BufferedWriter(new FileWriter(new File(subFolder + "/" + strSplit[LINE][COL01] + ".sql"))));
591631 if (cp.dbType == cp.DB_TYPE_ORACLE){
592632 pw[CONTENTS].println("/* " + strSplit[LINE][COL01] + " */");
593633 pw[CONTENTS].print("CREATE OR REPLACE ");
634+ } else if (cp.dbType == cp.DB_TYPE_MYSQL){
635+ if(strSplit[LINE][COL02].equals("TRIGGER")){
636+ 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");
637+ } else {
638+ pw[CONTENTS].println("CREATE " + strSplit[LINE][COL02] + " " + cp.DB_SQL_QUOTEDS[cp.dbType] + strSplit[LINE][COL01] + cp.DB_SQL_QUOTEDE[cp.dbType]);
639+ }
594640 }
595641 stmt = dao.prepareSql(cp.sql_proc[cp.dbType]);
596642 stmt.setString(1,strSplit[LINE][COL02]);
@@ -729,7 +775,11 @@ public class RapideMetaUnloader{
729775 strSplit[LINE] = CmnUtils.split(strLine, cp.delimiter);
730776 if(!fkName.equals(strSplit[LINE][COL02]) && !fkName.equals("")){
731777 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]);
732- 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]);
778+ if(cp.dbType == cp.DB_TYPE_MYSQL){
779+ 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]);
780+ } else {
781+ 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]);
782+ }
733783 for(int i=0;i<strSplit[LINE].length;i++)CmnUtils.debugPrint("'" + strSplit[LINE][i] + "'");
734784 tbName = strSplit[LINE][COL01];
735785 fkName = strSplit[LINE][COL02];
@@ -760,8 +810,12 @@ public class RapideMetaUnloader{
760810
761811 }
762812 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]);
763- 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]);
764- for(int i=0;i<pw.length;i++){
813+ if(cp.dbType == cp.DB_TYPE_MYSQL){
814+ 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]);
815+ } else {
816+ 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]);
817+ }
818+ for(int i=0;i<pw.length;i++){
765819 if(pw[i]!=null){
766820 pw[i].close();
767821 pw[i]=null;
Show on old repository browser