com/rapide_act/RapideMetaUnloader.java

   1 package com.rapide_act;
   2
   3 import java.io.BufferedInputStream;
   4 import java.io.FileInputStream;
   5 import java.io.IOException;
   6 import java.io.InputStream;
   7 import java.io.File;
   8 import java.io.FileReader;
   9 import java.io.FileWriter;
  10 import java.io.BufferedWriter;
  11 import java.io.BufferedReader;
  12 import java.io.PrintWriter;
  13 import java.io.PrintWriter;
  14 import java.util.Date;
  15 import java.util.ArrayList;
  16 import java.util.Iterator;
  17 import java.util.Properties;
  18 import java.sql.Connection;
  19 import java.sql.DriverManager;
  20 import java.sql.ResultSet;
  21 import java.sql.ResultSetMetaData;
  22 import java.sql.SQLException;
  23 import java.sql.PreparedStatement;
  24 import java.sql.Timestamp;
  25 import java.sql.Blob;
  26 import java.sql.Clob;
  27
  28 public class RapideMetaUnloader{
  29
  30         private static final int FL_TABLE = 0;
  31         private static final int FL_INDEX = 1;
  32         private static final int FL_VIEW = 2;
  33         private static final int FL_PROCEDURE = 3;
  34         private static final int FL_SEQ = 4;
  35         private static final int FL_SYNONYM = 5;
  36         private static final int FL_OBJ = 6;
  37         private static final int FL_FK = 7;
  38         private static final String [] FILE_NAME = {
  39                 "TABLEDEF",
  40                 "INDEXDEF",
  41                 "VIEWDEF",
  42                 "PROCDEF",
  43                 "SEQDEF",
  44                 "SYNMDEF",
  45                 "OBJDEF",
  46                 "FKDEF"
  47         };
  48         private static final String [] sql = {
  49         "select "
  50              + "a.table_name as table_name,"
  51              + "replace(replace(b.comments, chr(13), ''), chr(10), '') as table_comments,"
  52              + "c.column_name as column_name, "
  53              + "replace(replace(d.comments, chr(13), ''), chr(10), '') as column_comments, "
  54              + "decode(c.data_type, 'TIMESTAMP(6)', 'TIMESTAMP',c.data_type) as data_type,"
  55              + "decode(c.data_type, 'NUMBER', c.data_precision, 'TIMESTAMP(6)', null, c.data_length) as data_length,"
  56              + "c.data_scale as data_scale,"
  57              + "decode(c.nullable, 'N', '\81\9b', null) as nullable,"
  58              + "c.data_default as data_default "
  59              + "from user_tables a "
  60              + "inner join user_tab_comments b "
  61              + "on a.table_name = b.table_name "
  62              + "inner join user_tab_columns c "
  63              + "on a.table_name = c.table_name "
  64              + "inner join user_col_comments d "
  65              + "on c.table_name = d.table_name "
  66              + " and c.column_name = d.column_name "
  67              + "order by a.table_name,c.column_id",
  68         "select "
  69              + " a.table_name,"
  70              + " a.index_name,"
  71              + " a.index_type,"
  72              + " c.constraint_type,"
  73              + " b.column_name,"
  74              + " b.column_position "
  75              + " from"
  76              + " user_indexes a,"
  77              + " user_ind_columns b,"
  78              + " user_constraints c "
  79              + " where"
  80              + "  a.table_name = b.table_name and"
  81              + "  a.index_name = b.index_name and"
  82              + "  a.table_name = c.table_name(+) and"
  83              + "  a.index_name = c.constraint_name(+) "
  84              + " order by"
  85              + " a.table_name,"
  86              + " a.index_name,"
  87              + " b.column_position",
  88         "select "
  89              + " a.view_name, "
  90              + " replace(replace(c.comments, chr(13), ''), chr(10), '') as comments "
  91              + " from"
  92              + " user_views a, "
  93              + " user_tab_comments c "
  94              + " where"
  95                 + "  a.view_name = c.table_name(+) "
  96              + " order by"
  97              + " a.view_name",
  98         "select "
  99              + " object_name, "
 100              + " object_type "
 101              + "from"
 102              + " user_objects"
 103              + " where object_type in('PACKAGE','PROCEDURE','FUNCTION','TRIGGER','TYPE')"
 104              + " group by object_name,object_type"
 105              + " order by object_name,object_type",
 106         "select "
 107              + "  sequence_name,"
 108              + "  to_char(min_value) as min_value,"
 109              + "  to_char(max_value) as max_value,"
 110              + "  to_char(increment_by) as increment_by,"
 111              + "  cycle_flag,"
 112              + "  order_flag,"
 113              + "  to_char(cache_size) as cache_size,"
 114              + "  to_char(last_number) as last_number "
 115              + " from user_sequences "
 116              + " order by sequence_name",
 117         "select "
 118              + "  synonym_name,"
 119              + "  table_owner,"
 120              + "  table_name,"
 121              + "  db_link "
 122              + "from user_synonyms "
 123              + "order by synonym_name,table_owner,table_name",
 124         "select "
 125              + " a.table_name,"
 126              + " a.object_name,"
 127              + " a.object_type,"
 128              + " a.pct_free,"
 129              + " a.pct_used,"
 130              + " a.ini_trans,"
 131              + " a.freelists,"
 132              + " a.freelist_groups,"
 133              + " a.buffer_pool,"
 134              + " a.tablespace_name, "
 135              + " a.logging, "
 136              + " b.bytes "
 137              + "from "
 138              + "(select "
 139              + " table_name,"
 140              + " table_name as object_name,"
 141              + " 'TABLE' as object_type,"
 142              + " pct_free,"
 143              + " pct_used,"
 144              + " ini_trans,"
 145              + " freelists,"
 146              + " freelist_groups,"
 147              + " buffer_pool,"
 148              + " tablespace_name, "
 149              + " logging "
 150              + "from "
 151              + " user_tables "
 152              + " union select "
 153              + " table_name,"
 154              + " index_name as object_name,"
 155              + " 'INDEX' as object_type,"
 156              + " pct_free,"
 157              + " null as pct_used,"
 158              + " ini_trans,"
 159              + " freelists,"
 160              + " freelist_groups,"
 161              + " buffer_pool,"
 162              + " tablespace_name, "
 163              + " logging "
 164              + " from "
 165              + " user_indexes "
 166              + ") a, "
 167              + "(select "
 168              + " segment_name,"
 169              + " segment_type,"
 170              + " bytes "
 171              + " from "
 172              + " user_segments "
 173              + ") b "
 174              + " where a.object_name = b.segment_name "
 175              + " and a.object_type = b.segment_type "
 176              + " order by"
 177              + " a.table_name,"
 178              + " a.object_name",
 179         "select "
 180              + " a.table_name as table_name, "
 181              + " a.constraint_name as constraint_name, "
 182              + " b.column_name as column_name, "
 183              + " b.position as position,"
 184              + " c.table_name as r_table_name, "
 185              + " c.constraint_name as r_constraint_name, "
 186              + " d.column_name as r_column_name, "
 187              + " d.position as r_position "
 188              + " from "
 189              + "   user_constraints a, "
 190              + "   user_cons_columns b,"
 191              + "   user_constraints c, "
 192              + "   user_cons_columns d "
 193              + " where "
 194              + "   a.constraint_type = 'R'"
 195              + "   and a.owner = b.owner"
 196              + "   and a.table_name = b.table_name"
 197              + "   and a.constraint_name = b.constraint_name"
 198              + "   and c.constraint_type in('P','U')"
 199              + "   and c.owner = b.owner"
 200              + "   and c.table_name = d.table_name"
 201              + "   and c.constraint_name = d.constraint_name"
 202              + "   and a.r_owner = c.owner"
 203              + "   and a.r_constraint_name = c.constraint_name"
 204              + " order by"
 205              + "   a.table_name,a.constraint_name,b.position,d.position"
 206         };
 207         private static String sql_view_text = "select text from user_views where view_name = ?";
 208         private static String sql_view_column_and_comment = "select "
 209              + " b.column_name,"
 210              + " replace(replace(d.comments, chr(13), ''), chr(10), '') as comments "
 211              + " from"
 212              + " user_tab_columns b,"
 213              + " user_col_comments d "
 214              + " where"
 215              + "  b.table_name = ? and"
 216              + "  b.table_name = d.table_name(+) and"
 217              + "  b.column_name = d.column_name(+) "
 218              + " order by"
 219              + " b.column_id";
 220         private static String sql_proc = "select text from user_source where name = ? order by line";
 221
 222         private String database = null;
 223
 224         public static void main(String args[]){
 225                 try {
 226                         if (args.length > 0){
 227                                 RapideMetaUnloader RapideMetaUnloader = new RapideMetaUnloader(args[0]);
 228                                 RapideMetaUnloader.metaUnload();
 229                         } else {
 230                                 RapideMetaUnloader RapideMetaUnloader = new RapideMetaUnloader(null);
 231                                 RapideMetaUnloader.metaUnload();
 232                         }
 233                 } catch (Exception e) {
 234                         e.printStackTrace();
 235                 }
 236         }
 237
 238         RapideMetaUnloader(String _database) {
 239                 super();
 240                 database = _database;
 241         }
 242
 243
 244         private void metaUnload(){
 245                 Connection conn = null;
 246                 PreparedStatement stmt = null;
 247                 BufferedReader [] br = new BufferedReader[4];
 248                 PrintWriter [] pw = new PrintWriter[6];
 249                 File folder = null;
 250                 File subFolder = null;
 251                 String strLine = null;
 252                 String strLine2 = null;
 253                 String [] strSplit = null;
 254                 String [] strSplit2 = null;
 255                 String [] strSplit3 = null;
 256                 String [] strSplit4 = null;
 257                 String strContents = null;
 258                 ArrayList<String> alData = null;
 259                 ArrayList<String> alData2 = null;
 260                 int columnCount = 0;
 261                 String tableName = null;
 262                 String tableCmnt = null;
 263                 String colName = null;
 264                 String colCmnt = null;
 265                 String colType = null;
 266                 String colLen = null;
 267                 String colDec = null;
 268                 String colNoN = null;
 269                 String colDflt = null;
 270                 String indexName = null;
 271                 String indexType = null;
 272                 String constraintType = null;
 273                 String colPos = null;
 274                 String seqName = null;
 275                 String minVal = null;
 276                 String maxVal = null;
 277                 String incSz = null;
 278                 String cycFg = null;
 279                 String odFg = null;
 280                 String cacheSz = null;
 281                 String lstNo = null;
 282                 String synmOwner = null;
 283                 String synmName = null;
 284                 String tbOwner = null;
 285                 String tbName = null;
 286                 String dbLnk = null;
 287                 String userName = null;
 288                 String passWord = null;
 289                 String host = null;
 290                 String created = null;
 291                 String acStat = null;
 292                 String defTbsp = null;
 293                 String tmpTbsp = null;
 294                 String grntee = null;
 295                 String grntor = null;
 296                 String grntrl = null;
 297                 String prvs = null;
 298                 String grntabl = null;
 299                 String hrchy = null;
 300
 301                 String objName = null;
 302                 String objTyp = null;
 303                 String pctFree = null;
 304                 String pctUsed = null;
 305                 String iniTrns = null;
 306                 String freeLst = null;
 307                 String flstGrp = null;
 308                 String bufPool = null;
 309                 String tblSpace = null;
 310                 String logging = null;
 311                 String segByts = null;
 312
 313                 String fkName = null;
 314                 String fkCol = null;
 315                 String fkColPos = null;
 316                 String rtbName = null;
 317                 String rfkName = null;
 318                 String rfkCol = null;
 319                 String rfkColPos = null;
 320
 321                 CmnProperty cp = null;
 322                 DataAccessObjects dao = null;
 323
 324                 try {
 325                         cp = new CmnProperty();
 326                         cp.setProperty(database);
 327
 328                         if (cp.dbType != cp.DB_TYPE_ORACLE){
 329                                 throw new Exception("\83f\81[\83^\83x\81[\83X\82ª\91Î\8fÛ\8aO\82Å\82·\81B[" + cp.DB_TYPE_NAME[cp.dbType] + "]");
 330                         }
 331                         if(cp.outFolder != null){
 332                                 folder = new File(cp.outFolder);
 333                         } else {
 334                                 if(database != null){
 335                                         folder = new File(cp.DEFAULT_OUT_FOLDER + "/DDL_" + database.toUpperCase() + "_" + CmnUtils.getYmdhm());
 336                                 } else {
 337                                         folder = new File(cp.DEFAULT_OUT_FOLDER + "/DDL_" + cp.DB_TYPE_NAME[cp.dbType].toUpperCase() + "_" + CmnUtils.getYmdhm());
 338                                 }
 339                                 folder.mkdir();
 340                         }
 341
 342                         CmnUtils.infoPrint("-->\8fo\97Í\90æ\83t\83H\83\8b\83_='" + folder + "'");
 343                         CmnUtils.infoPrint("\83\81\83^\83f\81[\83^\83A\83\93\83\8d\81[\83h\82ð\8aJ\8en\82µ\82Ü\82µ\82½\81B");
 344
 345                         // Connect
 346                         dao = new DataAccessObjects(cp);
 347                         conn = dao.connect();
 348                         // Def Csv Writing start
 349                         for(int i=0;i<sql.length;i++){
 350                                 dao.select(sql[i]);
 351                                 CmnUtils.writeCsv(folder + "/" + FILE_NAME[i] + "_" + database +".csv", dao.getColumnCount(), dao.getArrayList());
 352                         }
 353                         // Def Csv Writing end
 354
 355                         // Table Contents start
 356                         br[0] = new BufferedReader(new FileReader(folder + "/" + FILE_NAME[FL_TABLE] + "_" + database +".csv"));
 357                         subFolder = new File(folder + "/TABLES");
 358                         subFolder.mkdir();
 359                         pw[1] = new PrintWriter(new BufferedWriter(new FileWriter(new File(folder + "/" + cp.SQL_FILE_NAME[cp.CREATE_TABLE]))));
 360                         pw[2] = new PrintWriter(new BufferedWriter(new FileWriter(new File(folder + "/" + cp.SQL_FILE_NAME[cp.DROP_TABLE]))));
 361                         pw[3] = new PrintWriter(new BufferedWriter(new FileWriter(new File(folder + "/" + cp.SQL_FILE_NAME[cp.TRUNCATE_TABLE]))));
 362
 363                         tableName = "";
 364                         tableCmnt = "";
 365                         colName = "";
 366                         colCmnt = "";
 367                         colType = "";
 368                         colLen = "";
 369                         colDec = "";
 370                         colNoN = "";
 371                         colDflt = "";
 372
 373                         StringBuffer sbTbCnts = new StringBuffer();
 374                         StringBuffer sbTbCmnt = new StringBuffer();
 375                         while((strLine=br[0].readLine()) != null){
 376                                 if(!strLine.equals("")){
 377                                         strSplit = CmnUtils.splitCsv(strLine);
 378                                         if (!tableName.equals("")){
 379                                                 sbTbCnts.append("    " + cp.sqlQuoted + colName + cp.sqlQuoted);
 380                                                 sbTbCnts.append(" " + colType);
 381                                                 if(!colCmnt.equals(""))sbTbCmnt.append("COMMENT ON COLUMN " + cp.sqlQuoted + tableName + cp.sqlQuoted + "." + cp.sqlQuoted + colName + cp.sqlQuoted + " IS '" + colCmnt + "';");
 382                                                 if(!colCmnt.equals(""))sbTbCmnt.append(cp.lineSeparator);
 383                                                 if (!colType.equals("BLOB") && !colType.equals("CLOB") && !colType.equals("TIMESTAMP") && !colType.equals("DATE") && !colType.equals("LONG") && !colType.equals("LONG RAW")){
 384                                                         if (!colLen.equals(""))sbTbCnts.append("(" + colLen);
 385                                                         if (!colLen.equals("") && !colDec.equals(""))sbTbCnts.append("," + colDec);
 386                                                         if (!colLen.equals(""))sbTbCnts.append(")");
 387                                                 }
 388                                                 if (!colDflt.equals(""))sbTbCnts.append(" DEFAULT " + colDflt.trim());
 389                                                 if (colNoN.equals("\81\9b"))sbTbCnts.append(" NOT NULL");
 390                                                 if (!strSplit[0].equals(tableName)){
 391                                                         pw[1].println("@TABLES/" + tableName + ".sql");
 392                                                         pw[2].println("DROP TABLE " + cp.sqlQuoted + tableName + cp.sqlQuoted + " PURGE;");
 393                                                         pw[3].println("TRUNCATE TABLE " + cp.sqlQuoted + tableName + cp.sqlQuoted + ";");
 394                                                         sbTbCnts.append(cp.lineSeparator);
 395                                                         sbTbCnts.append(");");
 396                                                         sbTbCnts.append(cp.lineSeparator);
 397                                                         pw[0] = new PrintWriter(new BufferedWriter(new FileWriter(new File(subFolder + "/" + tableName + ".sql"))));
 398                                                         strSplit2 = CmnUtils.splitCrLf(sbTbCnts.toString());
 399                                                         for(int i=0;i<strSplit2.length;i++)if(!strSplit2[i].trim().equals(""))pw[0].println(strSplit2[i]);
 400                                                         strSplit3 = CmnUtils.splitCrLf(sbTbCmnt.toString());
 401                                                         for(int i=0;i<strSplit3.length;i++)if(!strSplit3[i].trim().equals(""))pw[0].println(strSplit3[i]);
 402                                                         pw[0].close();
 403                                                         pw[0] = null;
 404                                                         sbTbCnts = new StringBuffer();
 405                                                         sbTbCmnt = new StringBuffer();
 406                                                         sbTbCnts.append("CREATE TABLE " + cp.sqlQuoted + strSplit[0] + cp.sqlQuoted);
 407                                                         sbTbCnts.append(cp.lineSeparator);
 408                                                         sbTbCnts.append("(");
 409                                                         sbTbCnts.append(cp.lineSeparator);
 410                                                         if(!strSplit[1].equals(""))sbTbCmnt.append("COMMENT ON TABLE " + cp.sqlQuoted + strSplit[0] + cp.sqlQuoted + " IS '" + strSplit[1] +"';");
 411                                                         if(!strSplit[1].equals(""))sbTbCmnt.append(cp.lineSeparator);
 412                                                 } else {
 413                                                         sbTbCnts.append(",");
 414                                                         sbTbCnts.append(cp.lineSeparator);
 415                                                 }
 416                                         }
 417                                         else {
 418                                                 sbTbCnts.append("CREATE TABLE " + cp.sqlQuoted + strSplit[0] + cp.sqlQuoted);
 419                                                 sbTbCnts.append(cp.lineSeparator);
 420                                                 sbTbCnts.append("(");
 421                                                 sbTbCnts.append(cp.lineSeparator);
 422                                                 if(!strSplit[1].equals(""))sbTbCmnt.append("COMMENT ON TABLE " + cp.sqlQuoted + strSplit[0] + cp.sqlQuoted + " IS '" + strSplit[1] +"';");
 423                                                 if(!strSplit[1].equals(""))sbTbCmnt.append(cp.lineSeparator);
 424                                         }
 425                                         for(int i=0;i<strSplit.length;i++)CmnUtils.debugPrint("'" + strSplit[i] + "'");
 426
 427                                         tableName = strSplit[0];
 428                                         tableCmnt = strSplit[1];
 429                                         colName = strSplit[2];
 430                                         colCmnt = strSplit[3];
 431                                         colType = strSplit[4];
 432                                         colLen = strSplit[5];
 433                                         colDec = strSplit[6];
 434                                         colNoN = strSplit[7];
 435                                         colDflt = strSplit[8];
 436                                 }
 437                         }
 438
 439                         if (!tableName.equals("")){
 440                                 sbTbCnts.append("    " + cp.sqlQuoted + colName + cp.sqlQuoted);
 441                                 sbTbCnts.append(" " + colType);
 442                                 if(!colCmnt.equals(""))sbTbCmnt.append("COMMENT ON COLUMN " + cp.sqlQuoted + tableName + cp.sqlQuoted + "." + cp.sqlQuoted + colName + cp.sqlQuoted + " IS '" + colCmnt + "';");
 443                                 if(!colCmnt.equals(""))sbTbCmnt.append(cp.lineSeparator);
 444                                 if (!colType.equals("BLOB") && !colType.equals("TIMESTAMP") && !colType.equals("DATE") && !colType.equals("LONG") && !colType.equals("LONG RAW")){
 445                                         if (!colLen.equals(""))sbTbCnts.append("(" + colLen);
 446                                         if (!colDec.equals(""))sbTbCnts.append("," + colDec);
 447                                         if (!colLen.equals(""))sbTbCnts.append(")");
 448                                 }
 449                                 if (!colDflt.equals(""))sbTbCnts.append(" DEFAULT " + colDflt.trim());
 450                                 if (colNoN.equals("\81\9b"))sbTbCnts.append(" NOT NULL");
 451                                 pw[1].println("@TABLES/" + tableName + ".sql");
 452                                 pw[2].println("DROP TABLE " + cp.sqlQuoted + tableName + cp.sqlQuoted + " PURGE;");
 453                                 pw[3].println("TRUNCATE TABLE " + cp.sqlQuoted + tableName + cp.sqlQuoted + ";");
 454                                 sbTbCnts.append(cp.lineSeparator);
 455                                 sbTbCnts.append(");");
 456                                 sbTbCnts.append(cp.lineSeparator);
 457                                 pw[0] = new PrintWriter(new BufferedWriter(new FileWriter(new File(subFolder + "/" + tableName + ".sql"))));
 458                                 strSplit2 = CmnUtils.splitCrLf(sbTbCnts.toString());
 459                                 for(int i=0;i<strSplit2.length;i++)if(!strSplit2[i].trim().equals(""))pw[0].println(strSplit2[i]);
 460                                 strSplit3 = CmnUtils.splitCrLf(sbTbCmnt.toString());
 461                                 for(int i=0;i<strSplit3.length;i++)if(!strSplit3[i].trim().equals(""))pw[0].println(strSplit3[i]);
 462                                 pw[0].close();
 463                                 pw[0] = null;
 464                         }
 465                         for(int i=1;i<4;i++)pw[i].close();
 466                         for(int i=1;i<4;i++)pw[i]=null;
 467                         br[0].close();
 468                         br[0] = null;
 469                         // Table Contents end
 470
 471                         // Index Contents start
 472                         br[0] = new BufferedReader(new FileReader(folder + "/" + FILE_NAME[FL_INDEX] + "_" + database +".csv"));
 473                         pw[0] = new PrintWriter(new BufferedWriter(new FileWriter(new File(folder + "/" + cp.SQL_FILE_NAME[cp.CREATE_PKEY]))));
 474                         pw[1] = new PrintWriter(new BufferedWriter(new FileWriter(new File(folder + "/" + cp.SQL_FILE_NAME[cp.CREATE_UKEY]))));
 475                         pw[2] = new PrintWriter(new BufferedWriter(new FileWriter(new File(folder + "/" + cp.SQL_FILE_NAME[cp.CREATE_INDEX]))));
 476                         pw[3] = new PrintWriter(new BufferedWriter(new FileWriter(new File(folder + "/" + cp.SQL_FILE_NAME[cp.DROP_PKEY]))));
 477                         pw[4] = new PrintWriter(new BufferedWriter(new FileWriter(new File(folder + "/" + cp.SQL_FILE_NAME[cp.DROP_UKEY]))));
 478                         pw[5] = new PrintWriter(new BufferedWriter(new FileWriter(new File(folder + "/" + cp.SQL_FILE_NAME[cp.DROP_INDEX]))));
 479
 480                         //Initial Process
 481                         tableName = "";
 482                         indexName = "";
 483                         indexType = "";
 484                         constraintType = "";
 485                         colName = "";
 486                         colPos = "";
 487                         StringBuffer sbIxCnts = new StringBuffer();
 488                         StringBuffer sbIx2Cnts = new StringBuffer();
 489                         StringBuffer sbIx3Cnts = new StringBuffer();
 490
 491                         //Loop Process
 492                         while((strLine=br[0].readLine()) != null){
 493                                 if(!strLine.equals("")){
 494                                         strSplit = CmnUtils.splitCsv(strLine);
 495                                         //Not 1st Line Process
 496                                         if (!indexName.equals("")){
 497                                                 if (!strSplit[1].equals(indexName)){
 498                                                         //Post Process
 499                                                         if (colPos.equals("1")){
 500                                                                 sbIxCnts.append(cp.sqlQuoted + colName + cp.sqlQuoted);
 501                                                                 if (constraintType.equals("U"))sbIx2Cnts.append(cp.sqlQuoted + colName + cp.sqlQuoted);
 502                                                         } else {
 503                                                                 sbIxCnts.append(", " + cp.sqlQuoted + colName + cp.sqlQuoted);
 504                                                                 if (constraintType.equals("U"))sbIx2Cnts.append(", " + cp.sqlQuoted + colName + cp.sqlQuoted);
 505                                                         }
 506                                                         sbIxCnts.append(");");
 507                                                         sbIxCnts.append(cp.lineSeparator);
 508                                                         if (constraintType.equals("U"))sbIx2Cnts.append(");");
 509                                                         sbIx2Cnts.append(cp.lineSeparator);
 510                                                         sbIx3Cnts.append(cp.lineSeparator);
 511
 512                                                         //Write Buffer
 513                                                         if (constraintType.equals("P")){
 514                                                                 strSplit2 = CmnUtils.splitCrLf(sbIxCnts.toString());
 515                                                                 for(int i=0;i<strSplit2.length;i++)if(!strSplit2[i].trim().equals(""))pw[0].println(strSplit2[i]);
 516                                                                 strSplit3 = CmnUtils.splitCrLf(sbIx2Cnts.toString());
 517                                                                 for(int i=0;i<strSplit3.length;i++)if(!strSplit3[i].trim().equals(""))pw[3].println(strSplit3[i]);
 518                                                         } else if (constraintType.equals("U")){
 519                                                                 strSplit2 = CmnUtils.splitCrLf(sbIxCnts.toString());
 520                                                                 for(int i=0;i<strSplit2.length;i++)if(!strSplit2[i].trim().equals(""))pw[1].println(strSplit2[i]);
 521                                                                 strSplit3 = CmnUtils.splitCrLf(sbIx2Cnts.toString());
 522                                                                 for(int i=0;i<strSplit3.length;i++)if(!strSplit3[i].trim().equals(""))pw[4].println(strSplit3[i]);
 523                                                         } else if (constraintType.equals("")){
 524                                                                 strSplit2 = CmnUtils.splitCrLf(sbIxCnts.toString());
 525                                                                 for(int i=0;i<strSplit2.length;i++)if(!strSplit2[i].trim().equals(""))pw[2].println(strSplit2[i]);
 526                                                         }
 527                                                         strSplit4 = CmnUtils.splitCrLf(sbIx3Cnts.toString());
 528                                                         for(int i=0;i<strSplit4.length;i++)if(!strSplit4[i].trim().equals(""))pw[5].println(strSplit4[i]);
 529
 530                                                         //Initial Process
 531                                                         sbIxCnts = new StringBuffer();
 532                                                         sbIx2Cnts = new StringBuffer();
 533                                                         sbIx3Cnts = new StringBuffer();
 534
 535                                                         //Index Changed Process
 536                                                         if (strSplit[3].equals("P")){
 537                                                                 sbIxCnts.append("ALTER TABLE " + cp.sqlQuoted + strSplit[0] + cp.sqlQuoted + " ADD CONSTRAINT " + cp.sqlQuoted + strSplit[1] + cp.sqlQuoted + " PRIMARY KEY(");
 538                                                                 sbIx2Cnts.append("ALTER TABLE " + cp.sqlQuoted + strSplit[0] + cp.sqlQuoted + " DROP CONSTRAINT " + cp.sqlQuoted + strSplit[1] + cp.sqlQuoted + ";");
 539                                                         } else if (strSplit[3].equals("U")){
 540                                                                 sbIxCnts.append("ALTER TABLE " + cp.sqlQuoted + strSplit[0] + cp.sqlQuoted + " ADD CONSTRAINT " + cp.sqlQuoted + strSplit[1] + cp.sqlQuoted + " UNIQUE(");
 541                                                                 sbIx2Cnts.append("ALTER TABLE " + cp.sqlQuoted + strSplit[0] + cp.sqlQuoted + " DROP UNIQUE(");
 542                                                         } else if (strSplit[3].equals("")){
 543                                                                 sbIxCnts.append("CREATE INDEX " + cp.sqlQuoted + strSplit[1] + cp.sqlQuoted + " ON " + strSplit[0] + "(");
 544                                                         }
 545                                                         sbIx3Cnts.append("DROP INDEX " + cp.sqlQuoted + strSplit[1] + cp.sqlQuoted + ";");
 546                                                 } else if (strSplit[1].equals(indexName)){
 547                                                         if (colPos.equals("1")){
 548                                                                 sbIxCnts.append(cp.sqlQuoted + colName + cp.sqlQuoted);
 549                                                                 if (constraintType.equals("U"))sbIx2Cnts.append(cp.sqlQuoted + colName + cp.sqlQuoted);
 550                                                         } else {
 551                                                                 sbIxCnts.append(", " + cp.sqlQuoted + colName + cp.sqlQuoted);
 552                                                                 if (constraintType.equals("U"))sbIx2Cnts.append(", " + cp.sqlQuoted + colName + cp.sqlQuoted);
 553                                                         }
 554                                                 }
 555                                         }
 556
 557                                         //1st Line Process
 558                                         else {
 559                                                 if (strSplit[3].equals("P")){
 560                                                         sbIxCnts.append("ALTER TABLE " + cp.sqlQuoted + strSplit[0] + cp.sqlQuoted + " ADD CONSTRAINT " + cp.sqlQuoted + strSplit[1] + cp.sqlQuoted + " PRIMARY KEY(");
 561                                                         sbIx2Cnts.append("ALTER TABLE " + cp.sqlQuoted + strSplit[0] + cp.sqlQuoted + " DROP CONSTRAINT " + cp.sqlQuoted + strSplit[1] + cp.sqlQuoted + ";");
 562                                                 } else if (strSplit[3].equals("U")){
 563                                                         sbIxCnts.append("ALTER TABLE " + cp.sqlQuoted + strSplit[0] + cp.sqlQuoted + " ADD CONSTRAINT " + cp.sqlQuoted + strSplit[1] + cp.sqlQuoted + " UNIQUE(");
 564                                                         sbIx2Cnts.append("ALTER TABLE " + cp.sqlQuoted + strSplit[0] + cp.sqlQuoted + " DROP UNIQUE(");
 565                                                 } else if (strSplit[3].equals("")){
 566                                                         sbIxCnts.append("CREATE INDEX " + cp.sqlQuoted + strSplit[1] + cp.sqlQuoted + " ON " + cp.sqlQuoted + strSplit[0] + cp.sqlQuoted + "(");
 567                                                 }
 568                                                 sbIx3Cnts.append("DROP INDEX " + cp.sqlQuoted + strSplit[1] + cp.sqlQuoted + ";");
 569                                         }
 570                                         for(int i=0;i<strSplit.length;i++)CmnUtils.debugPrint("'" + strSplit[i] + "'");
 571
 572                                         tableName = strSplit[0];
 573                                         indexName = strSplit[1];
 574                                         indexType = strSplit[2];
 575                                         constraintType = strSplit[3];
 576                                         colName = strSplit[4];
 577                                         colPos = strSplit[5];
 578                                 }
 579                         }
 580                         if (!indexName.equals("")){
 581                                 //Post Process
 582                                 if (colPos.equals("1")){
 583                                         sbIxCnts.append(cp.sqlQuoted + colName + cp.sqlQuoted);
 584                                         if (constraintType.equals("U"))sbIx2Cnts.append(cp.sqlQuoted + colName + cp.sqlQuoted);
 585                                 } else {
 586                                         sbIxCnts.append(", " + cp.sqlQuoted + colName + cp.sqlQuoted);
 587                                         if (constraintType.equals("U"))sbIx2Cnts.append(", " + cp.sqlQuoted + colName + cp.sqlQuoted);
 588                                 }
 589                                 sbIxCnts.append(");");
 590                                 sbIxCnts.append(cp.lineSeparator);
 591                                 if (constraintType.equals("U"))sbIx2Cnts.append(");");
 592                                 sbIx2Cnts.append(cp.lineSeparator);
 593                                 sbIx3Cnts.append(cp.lineSeparator);
 594
 595                                 //Write Buffer
 596                                 if (constraintType.equals("P")){
 597                                         strSplit2 = CmnUtils.splitCrLf(sbIxCnts.toString());
 598                                         for(int i=0;i<strSplit2.length;i++)if(!strSplit2[i].trim().equals(""))pw[0].println(strSplit2[i]);
 599                                         strSplit3 = CmnUtils.splitCrLf(sbIx2Cnts.toString());
 600                                         for(int i=0;i<strSplit3.length;i++)if(!strSplit3[i].trim().equals(""))pw[3].println(strSplit3[i]);
 601                                 } else if (constraintType.equals("U")){
 602                                         strSplit2 = CmnUtils.splitCrLf(sbIxCnts.toString());
 603                                         for(int i=0;i<strSplit2.length;i++)if(!strSplit2[i].trim().equals(""))pw[1].println(strSplit2[i]);
 604                                         strSplit3 = CmnUtils.splitCrLf(sbIx2Cnts.toString());
 605                                         for(int i=0;i<strSplit3.length;i++)if(!strSplit3[i].trim().equals(""))pw[4].println(strSplit3[i]);
 606                                 } else if (constraintType.equals("")){
 607                                         strSplit2 = CmnUtils.splitCrLf(sbIxCnts.toString());
 608                                         for(int i=0;i<strSplit2.length;i++)if(!strSplit2[i].trim().equals(""))pw[2].println(strSplit2[i]);
 609                                 }
 610                                 strSplit4 = CmnUtils.splitCrLf(sbIx3Cnts.toString());
 611                                 for(int i=0;i<strSplit4.length;i++)if(!strSplit4[i].trim().equals(""))pw[5].println(strSplit4[i]);
 612                         }
 613
 614                         //Close Buffer
 615                         for(int i=0;i<6;i++)pw[i].close();
 616                         for(int i=0;i<6;i++)pw[i]=null;
 617                         br[0].close();
 618                         br[0] = null;
 619                         // Index Contents end
 620
 621                         // View Contents start
 622                         br[0] = new BufferedReader(new FileReader(folder + "/" + FILE_NAME[FL_VIEW] + "_" + database +".csv"));
 623                         subFolder = new File(folder + "/VIEWS");
 624                         subFolder.mkdir();
 625                         pw[1] = new PrintWriter(new BufferedWriter(new FileWriter(new File(folder + "/" + cp.SQL_FILE_NAME[cp.CREATE_VIEW]))));
 626                         pw[2] = new PrintWriter(new BufferedWriter(new FileWriter(new File(folder + "/" + cp.SQL_FILE_NAME[cp.DROP_VIEW]))));
 627                         while((strLine=br[0].readLine()) != null){
 628                                 strSplit = CmnUtils.splitCsv(strLine);
 629                                 CmnUtils.debugPrint("'" + strSplit[0] + "'");
 630                                 pw[1].println("@VIEWS/" + strSplit[0] + ".sql");
 631                                 pw[2].println("DROP VIEW \"" + strSplit[0] + "\";");
 632                                 if (strSplit.length>1)CmnUtils.debugPrint("'" + strSplit[1] + "'");
 633                                 pw[0] = new PrintWriter(new BufferedWriter(new FileWriter(new File(subFolder + "/" + strSplit[0] + ".sql"))));
 634                                 stmt = dao.prepareSql(sql_view_column_and_comment);
 635                                 stmt.setString(1,strSplit[0]);
 636                                 dao.executeSql();
 637                                 alData = dao.getArrayList();
 638                                 columnCount = dao.getColumnCount();
 639                                 pw[0].println("/* " + strSplit[0] + " */");
 640                                 pw[0].println("CREATE OR REPLACE VIEW " + strSplit[0]);
 641                                 pw[0].println("(");
 642                                 for(int i=0;i<alData.size();i++){
 643                                         if ((i+1) % columnCount == 1){
 644                                                 if (i == 0){
 645                                                         if (!alData.get(i+1).equals("")){
 646                                                                 pw[0].println("    " + alData.get(i) + "    -- " + alData.get(i+1));
 647                                                         } else {
 648                                                                 pw[0].println("    " + alData.get(i));
 649                                                         }
 650                                                 } else {
 651                                                         if (!alData.get(i+1).equals("")){
 652                                                                 pw[0].println("    ," + alData.get(i) + "    -- " + alData.get(i+1));
 653                                                         } else {
 654                                                                 pw[0].println("    ," + alData.get(i));
 655                                                         }
 656                                                 }
 657                                         }
 658                                 }
 659                                 pw[0].println(")");
 660                                 pw[0].println("AS");
 661                                 stmt = dao.prepareSql(sql_view_text);
 662                                 stmt.setString(1,strSplit[0]);
 663                                 dao.executeSql();
 664                                 alData2 = dao.getArrayList();
 665                                 strContents = alData2.get(0).trim().replaceAll("\n",cp.lineSeparator);
 666                                 strSplit2 = CmnUtils.splitCrLf(strContents);
 667                                 for(int i=0;i<strSplit2.length;i++)if(!strSplit2[i].trim().equals(""))pw[0].println(strSplit2[i]);
 668                                 pw[0].println(";");
 669                                 if (!strSplit[1].equals(""))pw[0].println("COMMENT ON TABLE " + cp.sqlQuoted + strSplit[0] + cp.sqlQuoted + " IS '" + strSplit[1] + "';");
 670                                 for(int i=0;i<alData.size();i++){
 671                                         if ((i+1) % columnCount == 0){
 672                                                 if (!alData.get(i).equals(""))pw[0].println("COMMENT ON COLUMN " + cp.sqlQuoted + strSplit[0] + cp.sqlQuoted + "." + alData.get(i-1) + " IS '" + alData.get(i) + "';");
 673                                         }
 674                                 }
 675                                 pw[0].close();
 676                                 pw[0] = null;
 677                         }
 678                         for(int i=1;i<3;i++)pw[i].close();
 679                         for(int i=1;i<3;i++)pw[i]=null;
 680                         br[0].close();
 681                         br[0] = null;
 682                         // View Contents end
 683
 684                         // Procedure Contents start
 685                         br[0] = new BufferedReader(new FileReader(folder + "/" + FILE_NAME[FL_PROCEDURE] + "_" + database +".csv"));
 686                         subFolder = new File(folder + "/PROCEDURES");
 687                         subFolder.mkdir();
 688                         pw[1] = new PrintWriter(new BufferedWriter(new FileWriter(new File(folder + "/" + cp.SQL_FILE_NAME[cp.CREATE_PROCEDURE]))));
 689                         pw[2] = new PrintWriter(new BufferedWriter(new FileWriter(new File(folder + "/" + cp.SQL_FILE_NAME[cp.DROP_PROCEDURE]))));
 690                         while((strLine=br[0].readLine()) != null){
 691                                 strSplit = CmnUtils.splitCsv(strLine);
 692                                 CmnUtils.debugPrint("'" + strSplit[0] + "'");
 693                                 pw[1].println("@PROCEDURES/" + strSplit[0] + ".sql");
 694                                 pw[2].println("DROP " + strSplit[1] + " " + strSplit[0] + ";");
 695                                 if (strSplit.length>1)CmnUtils.debugPrint("'" + strSplit[1] + "'");
 696                                 pw[0] = new PrintWriter(new BufferedWriter(new FileWriter(new File(subFolder + "/" + strSplit[0] + ".sql"))));
 697                                 pw[0].println("/* " + strSplit[0] + " */");
 698                                 pw[0].print("CREATE OR REPLACE ");
 699                                 stmt = dao.prepareSql(sql_proc);
 700                                 stmt.setString(1,strSplit[0]);
 701                                 dao.executeSql();
 702                                 alData = dao.getArrayList();
 703                                 for(int i=0;i<alData.size();i++){
 704                                         strContents = alData.get(i).replaceAll("\n","");
 705                                         if(!strContents.equals(""))pw[0].println(strContents);
 706                                 }
 707                                 pw[0].println("/");
 708                                 pw[0].close();
 709                                 pw[0] = null;
 710                         }
 711                         for(int i=1;i<3;i++)pw[i].close();
 712                         for(int i=1;i<3;i++)pw[i]=null;
 713                         br[0].close();
 714                         br[0] = null;
 715                         // Procedure Contents end
 716
 717                         // Sequence Contents start
 718                         br[0] = new BufferedReader(new FileReader(folder + "/" + FILE_NAME[FL_SEQ] + "_" + database +".csv"));
 719                         pw[1] = new PrintWriter(new BufferedWriter(new FileWriter(new File(folder + "/" + cp.SQL_FILE_NAME[cp.CREATE_SEQUENCE]))));
 720                         pw[2] = new PrintWriter(new BufferedWriter(new FileWriter(new File(folder + "/" + cp.SQL_FILE_NAME[cp.DROP_SEQUENCE]))));
 721
 722                         while((strLine=br[0].readLine()) != null){
 723                                 strSplit = CmnUtils.splitCsv(strLine);
 724                                 seqName = strSplit[0];
 725                                 minVal = strSplit[1];
 726                                 maxVal = strSplit[2];
 727                                 incSz = strSplit[3];
 728                                 cycFg = strSplit[4];
 729                                 odFg = strSplit[5];
 730                                 cacheSz = strSplit[6];
 731                                 lstNo = strSplit[7];
 732                                 pw[1].print("CREATE SEQUENCE \"" + cp.sqlQuoted + seqName + cp.sqlQuoted + "\" MINVALUE " + minVal + " MAXVALUE " + maxVal + " INCREMENT BY " + incSz + " START WITH " + lstNo + " CACHE " + cacheSz);
 733                                 if (odFg.equals("N")){
 734                                         pw[1].print(" NOORDER");
 735                                 } else {
 736                                         pw[1].print(" ORDER");
 737                                 }
 738                                 if (cycFg.equals("N")){
 739                                         pw[1].print(" NOCYCLE");
 740                                 } else {
 741                                         pw[1].print(" CYCLE");
 742                                 }
 743                                 pw[1].println(";");
 744                                 pw[2].println("DROP SEQUENCE \"" + cp.sqlQuoted + seqName + cp.sqlQuoted + "\";");
 745                                 for(int i=0;i<strSplit.length;i++)CmnUtils.debugPrint("'" + strSplit[i] + "'");
 746                         }
 747                         for(int i=1;i<3;i++)pw[i].close();
 748                         for(int i=1;i<3;i++)pw[i]=null;
 749                         br[0].close();
 750                         br[0] = null;
 751                         // Sequence Contents end
 752
 753                         // Synonym Contents start
 754                         br[0] = new BufferedReader(new FileReader(folder + "/" + FILE_NAME[FL_SYNONYM] + "_" + database +".csv"));
 755                         pw[1] = new PrintWriter(new BufferedWriter(new FileWriter(new File(folder + "/" + cp.SQL_FILE_NAME[cp.CREATE_SYNONYM]))));
 756                         pw[2] = new PrintWriter(new BufferedWriter(new FileWriter(new File(folder + "/" + cp.SQL_FILE_NAME[cp.DROP_SYNONYM]))));
 757
 758                         while((strLine=br[0].readLine()) != null){
 759                                 strSplit = CmnUtils.splitCsv(strLine);
 760                                 synmName = strSplit[0];
 761                                 tbOwner = strSplit[1];
 762                                 tbName = strSplit[2];
 763                                 dbLnk = strSplit[3];
 764
 765                                 pw[1].print("CREATE SYNONYM " + cp.sqlQuoted + synmName + cp.sqlQuoted + " FOR ");
 766                                 if(dbLnk.equals("")){
 767                                         pw[1].print(cp.sqlQuoted + tbOwner + cp.sqlQuoted + "." + cp.sqlQuoted + tbName + cp.sqlQuoted );
 768                                 } else {
 769                                         pw[1].print(cp.sqlQuoted + tbName + cp.sqlQuoted + "@" + cp.sqlQuoted + dbLnk + cp.sqlQuoted );
 770                                 }
 771                                 pw[1].println(";");
 772                                 pw[2].println("DROP SYNONYM " + cp.sqlQuoted + synmName + cp.sqlQuoted + ";");
 773                                 for(int i=0;i<strSplit.length;i++)CmnUtils.debugPrint("'" + strSplit[i] + "'");
 774                         }
 775                         for(int i=1;i<3;i++)pw[i].close();
 776                         for(int i=1;i<3;i++)pw[i]=null;
 777                         br[0].close();
 778                         br[0] = null;
 779                         // Synonym Contents end
 780
 781                         // Fk Contents start
 782                         File csvFile = new File(folder + "/" + FILE_NAME[FL_FK] + "_" + database +".csv");
 783                         if (csvFile.length() > 0) {
 784                                 br[0] = new BufferedReader(new FileReader(folder + "/" + FILE_NAME[FL_FK] + "_" + database +".csv"));
 785                                 pw[1] = new PrintWriter(new BufferedWriter(new FileWriter(new File(folder + "/" + cp.SQL_FILE_NAME[cp.CREATE_FK]))));
 786                                 pw[2] = new PrintWriter(new BufferedWriter(new FileWriter(new File(folder + "/" + cp.SQL_FILE_NAME[cp.DROP_FK]))));
 787                                 tbName = "";
 788                                 fkName = "";
 789                                 fkCol = "";
 790                                 fkColPos = "";
 791                                 rtbName = "";
 792                                 rfkName = "";
 793                                 rfkCol = "";
 794                                 rfkColPos = "";
 795
 796                                 while((strLine=br[0].readLine()) != null){
 797                                         strSplit = CmnUtils.splitCsv(strLine);
 798                                         if(!fkName.equals(strSplit[1]) && !fkName.equals("")){
 799                                                 pw[1].println("ALTER TABLE " + cp.sqlQuoted + tbName + cp.sqlQuoted + " ADD CONSTRAINT " + cp.sqlQuoted + fkName + " FOREIGN KEY (" + cp.sqlQuoted + fkCol + cp.sqlQuoted + ") REFERENCES " + cp.sqlQuoted + rtbName + cp.sqlQuoted + " (" + cp.sqlQuoted + rfkCol + cp.sqlQuoted + ");");
 800                                                 pw[2].println("ALTER TABLE " + cp.sqlQuoted + tbName + cp.sqlQuoted + " DROP CONSTRAINT " + cp.sqlQuoted + fkName + ";");
 801                                                 for(int i=0;i<strSplit.length;i++)CmnUtils.debugPrint("'" + strSplit[i] + "'");
 802                                                 tbName = strSplit[0];
 803                                                 fkName = strSplit[1];
 804                                                 fkCol = strSplit[2];
 805                                                 fkColPos = strSplit[3];
 806                                                 rtbName = strSplit[4];
 807                                                 rfkName = strSplit[5];
 808                                                 rfkCol = strSplit[6];
 809                                                 rfkColPos = strSplit[7];
 810                                         } else {
 811                                                 if(fkName.equals("")){
 812                                                         tbName = strSplit[0];
 813                                                         fkName = strSplit[1];
 814                                                         fkCol = strSplit[2];
 815                                                         fkColPos = strSplit[3];
 816                                                         rtbName = strSplit[4];
 817                                                         rfkName = strSplit[5];
 818                                                         rfkCol = strSplit[6];
 819                                                         rfkColPos = strSplit[7];
 820                                                 } else {
 821                                                         if (strSplit[3].equals(strSplit[7])) {
 822                                                                 fkCol = fkCol + cp.sqlQuoted + ", " + cp.sqlQuoted + strSplit[2] + cp.sqlQuoted;
 823                                                                 rfkCol = rfkCol + ", " + cp.sqlQuoted + strSplit[6] + cp.sqlQuoted;
 824                                                         }
 825                                                 }
 826                                         }
 827
 828                                 }
 829                                 pw[1].println("ALTER TABLE " + cp.sqlQuoted + tbName + cp.sqlQuoted + " ADD CONSTRAINT " + cp.sqlQuoted + fkName + cp.sqlQuoted + " FOREIGN KEY (" + cp.sqlQuoted + fkCol + cp.sqlQuoted + ") REFERENCES " + cp.sqlQuoted + rtbName + cp.sqlQuoted + " (" + cp.sqlQuoted + rfkCol + cp.sqlQuoted + ");");
 830                                 pw[2].println("ALTER TABLE " + cp.sqlQuoted + cp.sqlQuoted + tbName + cp.sqlQuoted + " DROP CONSTRAINT " + cp.sqlQuoted + fkName + cp.sqlQuoted + ";");
 831                                 for(int i=1;i<3;i++)pw[i].close();
 832                                 for(int i=1;i<3;i++)pw[i]=null;
 833                                 br[0].close();
 834                                 br[0] = null;
 835                         }
 836                         // Fk Contents end
 837
 838                         // Rhysical Contents start
 839                         if(cp.isPhysical){
 840                                 // physical index setting
 841                                 br[0] = new BufferedReader(new FileReader(folder + "/" + FILE_NAME[FL_OBJ] + "_" + database +".csv"));
 842                                 br[1] = new BufferedReader(new FileReader(folder + "/" + cp.SQL_FILE_NAME[cp.CREATE_PKEY]));
 843                                 br[2] = new BufferedReader(new FileReader(folder + "/" + cp.SQL_FILE_NAME[cp.CREATE_UKEY]));
 844                                 br[3] = new BufferedReader(new FileReader(folder + "/" + cp.SQL_FILE_NAME[cp.CREATE_INDEX]));
 845                                 pw[1] = new PrintWriter(new BufferedWriter(new FileWriter(new File(folder + "/" + cp.SQL_FILE_NAME[cp.CREATE_PKEY] + ".phy"))));
 846                                 pw[2] = new PrintWriter(new BufferedWriter(new FileWriter(new File(folder + "/" + cp.SQL_FILE_NAME[cp.CREATE_UKEY] + ".phy"))));
 847                                 pw[3] = new PrintWriter(new BufferedWriter(new FileWriter(new File(folder + "/" + cp.SQL_FILE_NAME[cp.CREATE_INDEX] + ".phy"))));
 848                                 alData = new ArrayList<String>();
 849                                 int rowNo;
 850                                 String [] rowData;
 851                                 while((strLine=br[0].readLine()) != null){
 852                                         strSplit = CmnUtils.splitCsv(strLine);
 853                                         for(int i=0;i<strSplit.length;i++)alData.add(strSplit[i]);
 854                                 }
 855                                 while((strLine=br[1].readLine()) != null){
 856                                         strSplit = CmnUtils.splitSpace(strLine);
 857                                         rowNo = CmnUtils.getArrayRowNo(alData, 12, 1, strSplit[5].replaceAll(cp.sqlQuoted,""));
 858                                         if (rowNo != -1){
 859                                                 rowData = rowData = CmnUtils.getArrayRowData(alData, 12, rowNo);
 860                                                 CmnUtils.debugPrint(strSplit[5] + ":" + rowNo);
 861                                                 for(int i=0;i<rowData.length;i++)CmnUtils.debugPrint(rowData[i]);
 862                                                 tbName = rowData[0];
 863                                                 objName = rowData[1];
 864                                                 objTyp = rowData[2];
 865                                                 pctFree = rowData[3];
 866                                                 pctUsed = rowData[4];
 867                                                 iniTrns = rowData[5];
 868                                                 freeLst = rowData[6];
 869                                                 flstGrp = rowData[7];
 870                                                 bufPool = rowData[8];
 871                                                 tblSpace = rowData[9];
 872                                                 logging = rowData[10];
 873                                                 segByts = rowData[11];
 874                                                 pw[1].print(strLine.replace(';',' '));
 875                                                 pw[1].print("USING INDEX PCTFREE " + pctFree);
 876                                                 pw[1].print(" INITRANS " + iniTrns + " STORAGE(");
 877                                                 if(!freeLst.equals(""))pw[1].print(" FREELISTS " + freeLst);
 878                                                 if(!flstGrp.equals(""))pw[1].print(" FREELIST GROUPS " + flstGrp);
 879                                                 pw[1].print(" BUFFER_POOL " + bufPool + ")");
 880                                                 pw[1].print(" TABLESPACE " + cp.sqlQuoted + tblSpace + cp.sqlQuoted);
 881                                                 if(logging.equals("NO")){
 882                                                         pw[1].println(" NOLOGGING;");
 883                                                 } else {
 884                                                         pw[1].println(";");
 885                                                 }
 886                                         } else {
 887                                                 pw[1].println(strLine);
 888                                                 CmnUtils.errorPrint(strSplit[5] + ",UNUSABLE INDEX");
 889                                         }
 890                                 }
 891                                 while((strLine=br[2].readLine()) != null){
 892                                         strSplit = CmnUtils.splitSpace(strLine);
 893                                         rowNo = CmnUtils.getArrayRowNo(alData, 12, 1, strSplit[5].replaceAll(cp.sqlQuoted,""));
 894                                         if (rowNo != -1){
 895                                                 rowData = rowData = CmnUtils.getArrayRowData(alData, 12, rowNo);
 896                                                 CmnUtils.debugPrint(strSplit[5] + ":" + rowNo);
 897                                                 for(int i=0;i<rowData.length;i++)CmnUtils.debugPrint(rowData[i]);
 898                                                 tbName = rowData[0];
 899                                                 objName = rowData[1];
 900                                                 objTyp = rowData[2];
 901                                                 pctFree = rowData[3];
 902                                                 pctUsed = rowData[4];
 903                                                 iniTrns = rowData[5];
 904                                                 freeLst = rowData[6];
 905                                                 flstGrp = rowData[7];
 906                                                 bufPool = rowData[8];
 907                                                 tblSpace = rowData[9];
 908                                                 logging = rowData[10];
 909                                                 segByts = rowData[11];
 910                                                 pw[2].print(strLine.replace(';',' '));
 911                                                 pw[2].print("USING INDEX PCTFREE " + pctFree);
 912                                                 pw[2].print(" INITRANS " + iniTrns + " STORAGE(");
 913                                                 if(!freeLst.equals(""))pw[2].print(" FREELISTS " + freeLst);
 914                                                 if(!flstGrp.equals(""))pw[2].print(" FREELIST GROUPS " + flstGrp);
 915                                                 pw[2].print(" BUFFER_POOL " + bufPool + ")");
 916                                                 pw[2].print(" TABLESPACE " + cp.sqlQuoted + tblSpace + cp.sqlQuoted);
 917                                                 if(logging.equals("NO")){
 918                                                         pw[2].println(" NOLOGGING;");
 919                                                 } else {
 920                                                         pw[2].println(";");
 921                                                 }
 922                                         } else {
 923                                                 pw[2].println(strLine);
 924                                                 CmnUtils.errorPrint(strSplit[5] + ",UNUSABLE INDEX");
 925                                         }
 926                                 }
 927                                 while((strLine=br[3].readLine()) != null){
 928                                         strSplit = CmnUtils.splitSpace(strLine);
 929                                         rowNo = CmnUtils.getArrayRowNo(alData, 12, 1, strSplit[2].replaceAll(cp.sqlQuoted,""));
 930                                         CmnUtils.debugPrint("rowNo=" + rowNo);
 931                                         if (rowNo != -1){
 932                                                 rowData = CmnUtils.getArrayRowData(alData, 12, rowNo);
 933                                                 for(int i=0;i<rowData.length;i++)CmnUtils.debugPrint(rowData[i]);
 934                                                 tbName = rowData[0];
 935                                                 objName = rowData[1];
 936                                                 objTyp = rowData[2];
 937                                                 pctFree = rowData[3];
 938                                                 pctUsed = rowData[4];
 939                                                 iniTrns = rowData[5];
 940                                                 freeLst = rowData[6];
 941                                                 flstGrp = rowData[7];
 942                                                 bufPool = rowData[8];
 943                                                 tblSpace = rowData[9];
 944                                                 logging = rowData[10];
 945                                                 segByts = rowData[11];
 946                                                 pw[3].print(strLine.replace(';',' '));
 947                                                 pw[3].print("PCTFREE " + pctFree);
 948                                                 pw[3].print(" INITRANS " + iniTrns + " STORAGE(");
 949                                                 if(!freeLst.equals(""))pw[3].print(" FREELISTS " + freeLst);
 950                                                 if(!flstGrp.equals(""))pw[3].print(" FREELIST GROUPS " + flstGrp);
 951                                                 pw[3].print(" BUFFER_POOL " + bufPool + ")");
 952                                                 pw[3].print(" TABLESPACE " + cp.sqlQuoted + tblSpace + cp.sqlQuoted);
 953                                                 if(logging.equals("NO")){
 954                                                         pw[3].println(" NOLOGGING;");
 955                                                 } else {
 956                                                         pw[3].println(";");
 957                                                 }
 958                                         } else {
 959                                                 pw[3].println(strLine);
 960                                                 CmnUtils.errorPrint(strSplit[2] + ",UNUSABLE INDEX");
 961                                         }
 962                                 }
 963                                 for(int i=1;i<4;i++)pw[i].close();
 964                                 for(int i=1;i<4;i++)pw[i]=null;
 965                                 for(int i=0;i<4;i++)br[i].close();
 966                                 for(int i=0;i<4;i++)br[i]=null;
 967
 968                                 File [] fmFl = new File[6];
 969                                 File [] toFl = new File[6];
 970
 971                                 fmFl[0] = new File(folder + "/" + cp.SQL_FILE_NAME[cp.CREATE_PKEY] + ".phy");
 972                                 fmFl[1] = new File(folder + "/" + cp.SQL_FILE_NAME[cp.CREATE_UKEY] + ".phy");
 973                                 fmFl[2] = new File(folder + "/" + cp.SQL_FILE_NAME[cp.CREATE_INDEX] + ".phy");
 974                                 toFl[0] = new File(folder + "/" + cp.SQL_FILE_NAME[cp.CREATE_PKEY]);
 975                                 toFl[1] = new File(folder + "/" + cp.SQL_FILE_NAME[cp.CREATE_UKEY]);
 976                                 toFl[2] = new File(folder + "/" + cp.SQL_FILE_NAME[cp.CREATE_INDEX]);
 977                                 for(int i=0;i<3;i++){
 978                                         CmnUtils.debugPrint(fmFl[i].toString() + ">" + toFl[i].toString());
 979                                         toFl[i].delete();
 980                                         fmFl[i].renameTo(toFl[i]);
 981                                 }
 982
 983                                 // physical table setting
 984                                 File tbDir = new File(folder + "/TABLES");
 985                                 File [] tbFiles = tbDir.listFiles();
 986                                 File tb = null;
 987                                 File oTb = null;
 988                                 for(int i=0;i<tbFiles.length;i++){
 989                                         tb = tbFiles[i];
 990                                         tbName = tb.getName().replaceAll(".sql","");
 991                                         oTb = new File(tb + ".phy");
 992                                         CmnUtils.debugPrint(tbName);
 993                                         br[0] = new BufferedReader(new FileReader(tb));
 994                                         pw[0] = new PrintWriter(new BufferedWriter(new FileWriter(oTb)));
 995                                         while((strLine=br[0].readLine()) != null){
 996                                                 if(strLine.equals(");")){
 997                                                         rowNo = CmnUtils.getArrayRowNo(alData, 12, 1, tbName);
 998                                                         if (rowNo != -1){
 999                                                                 rowData = CmnUtils.getArrayRowData(alData, 12, rowNo);
1000                                                                 CmnUtils.debugPrint(tbName + ":" + rowNo);
1001                                                                 //for(int j=0;j<rowData.length;i++)CmnUtils.debugPrint(rowData[j]);
1002                                                                 tbName = rowData[0];
1003                                                                 objName = rowData[1];
1004                                                                 objTyp = rowData[2];
1005                                                                 pctFree = rowData[3];
1006                                                                 pctUsed = rowData[4];
1007                                                                 iniTrns = rowData[5];
1008                                                                 freeLst = rowData[6];
1009                                                                 flstGrp = rowData[7];
1010                                                                 bufPool = rowData[8];
1011                                                                 tblSpace = rowData[9];
1012                                                                 logging = rowData[10];
1013                                                                 segByts = rowData[11];
1014                                                                 pw[0].print(") PCTFREE " + pctFree);
1015                                                                 if(!pctUsed.equals(""))pw[0].print(" PCTUSED " + pctUsed);
1016                                                                 pw[0].print(" INITRANS " + iniTrns + " STORAGE(");
1017                                                                 if(!freeLst.equals(""))pw[0].print(" FREELISTS " + freeLst);
1018                                                                 if(!flstGrp.equals(""))pw[0].print(" FREELIST GROUPS " + flstGrp);
1019                                                                 pw[0].print(" BUFFER_POOL " + bufPool + ")");
1020                                                                 pw[0].print(" TABLESPACE " + cp.sqlQuoted + tblSpace + cp.sqlQuoted);
1021                                                                 if(logging.equals("NO")){
1022                                                                         pw[0].println(" NOLOGGING;");
1023                                                                 } else {
1024                                                                         pw[0].println(";");
1025                                                                 }
1026                                                         } else {
1027                                                                 pw[0].println(");");
1028                                                         }
1029                                                 } else {
1030                                                         pw[0].println(strLine);
1031                                                 }
1032                                         }
1033                                         pw[0].close();
1034                                         pw[0]=null;
1035                                         br[0].close();
1036                                         br[0]=null;
1037                                         tb.delete();
1038                                         oTb.renameTo(tb);
1039                                 }
1040                         }
1041                         // Rhysical Contents end
1042
1043                         // Remove work file
1044                         File fl= null;
1045                         for(int i=0;i<sql.length;i++){
1046                                 fl= new File(folder + "/" + FILE_NAME[i] + "_" + database +".csv");
1047                                 CmnUtils.debugPrint(fl.toString());
1048                                 fl.delete();
1049                         }
1050
1051                         // disconnect database
1052                         dao.disconnect();
1053                         CmnUtils.infoPrint("\83\81\83^\83f\81[\83^\83A\83\93\83\8d\81[\83h\82ð\8fI\97¹\82µ\82Ü\82µ\82½\81B");
1054
1055                 } catch(SQLException se) {
1056                         try{
1057                                 CmnUtils.errorPrint(se.toString());
1058                                 se.printStackTrace();
1059                                 se = se.getNextException();
1060                                 if(se != null) {
1061                                         System.out.println(se.getMessage());
1062                                 }
1063                                 dao.rollback();
1064                         } catch (Exception see) {}
1065                 } catch (Exception e) {
1066                         try{
1067                                 CmnUtils.errorPrint(e.toString());
1068                                 e.printStackTrace();
1069                                 dao.rollback();
1070                         } catch (Exception ee) {}
1071                 } finally{
1072                         try{
1073                                 for(int i=0;i<6;i++){
1074                                         if(pw[i]!=null){
1075                                                 pw[i].close();
1076                                                 pw[i]=null;
1077                                         }
1078                                 }
1079                                 for(int i=0;i<4;i++){
1080                                         if(br[i]!=null){
1081                                                 br[i].close();
1082                                                 br[i]=null;
1083                                         }
1084                                 }
1085                         } catch (Exception e) {}
1086                 }
1087         }
1088
1089
1090 }
1091