Java > Open Source Codes > org > webdocwf > util > sql > SQLSplitter


1 /***********************************************
2  SQLSplitter.java
3  Date: 29.10.2002.
4  @version 1.4.
5  @author Sinisa Milosevic
6  *********************************************** *
7  * formatted with JxBeauty (c) johann.langhofer@nextra.at
8  */
9 package org.webdocwf.util.sql;
10
11 import java.io.File ;
12 import java.io.RandomAccessFile ;
13 import java.util.StringTokenizer ;
14 import java.util.Vector ;
15 import org.enhydra.dods.xslt.XSLTUtil;
16
17 /**
18  * Class SQLSplitter copies all SQL commands from all SQL files which are situated
19  * in the working directory and all its subdirectories into new SQL files.
20  * Original SQL files are created by DODS - Enhydra.
21  * All SQL commands are copied into first sql file except sql commands which reference to foreign and primary key columns.
22  * In the second sql file class puts ALTER TABLE sql commands with adding foreign key references.
23  * In the third sql file class puts ALTER TABLE sql commands with adding primary keys.
24  * In the forth sql file class puts DROP TABLE sql commands for all tables which were created by create table SQL statements in the first file.
25  * In the fifth sql file class puts CREATE INDEX sql commands for all tables which were created by create table SQL statements in the first file.
26  * In the sixth sql file class puts DROP foreign key sql commands for all tables which were created by create table SQL statements in the first file.
27  * In the seventh sql file class puts DROP primary sql commands for all tables which created by create table SQL statements in the first file.
28  * All others Sql commands class puts into separate file.
29  *
30  */
31 public class SQLSplitter {
32     Vector vecTableName = new Vector ();
33     Vector vecForeignTable = new Vector ();
34     Vector vecForeignKey = new Vector ();
35     Vector vecPrimaryKey = new Vector ();
36     Vector vecReference = new Vector ();
37     Vector vecConstraintName = new Vector ();
38     Vector vecPrimaryKeyTableName = new Vector ();
39     Vector vecPrimaryKeyColumn = new Vector ();
40     Vector vecDropTableTableName = new Vector ();
41     Vector vecDropForeignKeys = new Vector ();
42     Vector vecCreateIndex = new Vector ();
43     Vector vecConstraintPrimary = new Vector ();
44     Vector vecFKRest = new Vector ();
45     boolean splitSQLPrimary = true;
46
47     /**
48      * Constructor SQLSplitter without parameters.
49      */
50     public SQLSplitter() {}
51
52     /**
53      * Main method controls parameters, if they are OK, starts separateSqlCommands method, but if
54      * they aren't, makes alert.
55      *
56      * param argv[0] The URL of the 'target' directory.
57      * param argv[1] Basic name of output file.
58      *
59      */
60     public static void main(String argv[]) {
61         if ((argv.length != 2)&&(argv.length != 3)) {
62             System.out.println("Usage: SQLSplitter url outputFile\n splitPrimarySql");
63             System.out.println(" where url is the URL of working directory \n");
64             System.out.println(" outputFile is the path and name of SQL file which will be created \n");
65             System.out.println(" and splitPrimarySql force SQLSpliter split PRIMARY part from CREATE to separate file \n");
66             System.out.println(" values are true/false (default value is true). \n");
67             System.exit(1);
68         }
69         SQLSplitter SQLCommandsChange = new SQLSplitter();
70         SQLCommandsChange.setSplitSQLPrimary(true);
71         if ((argv.length == 3)){
72             try{
73                 SQLCommandsChange.setSplitSQLPrimary(new Boolean (argv[2]).booleanValue());
74             }catch(Exception e){
75                 System.out.println("Usage: SQLSplitter url outputFile\n splitPrimarySql");
76                 System.out.println(" where url is the URL of working directory \n");
77                 System.out.println(" outputFile is the path and name of SQL file which will be created \n");
78                 System.out.println(" and splitPrimarySql force SQLSpliter split PRIMARY part from CREATE to separate file \n");
79                 System.out.println(" values are true/false (default value is true). \n");
80                 System.exit(1);
81             }
82             
83         }
84         SQLCommandsChange.separateSqlCommands(argv[0], argv[1]);
85     }
86
87     
88
89     /**
90      * Method separateSqlCommands analyses all SQL files in a working directory and
91      * all its subdirecories and puts all create table SQL statements without Foreign references and Primary keys into separate file.
92      * File will be saved in the root of the strDirectory with adding "Create" at the end of output file name - strOutputFile.
93      * SQL statements which define foreign keys of tables this method puts into file strOutputFile+"Integrity".
94      * SQL statements which define primary keys of tables this method puts into file strOutputFile+"Primary".
95      * SQL statements which define indexes of tables this method puts into file strOutputFile+"Index".
96      * SQL statements which delete tables will be written into strOutputFile+"DropTable".
97      * SQL statements which delete foreign keys will be written into strOutputFile+"DropIntegrity".
98      * SQL statements which delete primary keys will be written into strOutputFile+"DropPrimary".
99      * Other SQL statements will be written into strOutputFile+"OtherSql".
100      *
101      * @param strDirectory The URL of the working directory.
102      * @param strOutputFile The Url and basic name of the output SQL files(example: c:\tmp\new_SQL.sql).
103      */
104     public void separateSqlCommands(String strDirectory, String strOutputFile) {
105         Vector vPathSQLFiles = new Vector ();
106         int iStart = 0;
107         String strOutputFileBegin = "";
108         String strOutputFileEnd = "";
109
110         if (System.getProperty("os.name").toLowerCase().startsWith("win")) {
111             if (!strDirectory.endsWith("\\")) {
112                 strDirectory += "\\";
113             }
114         } else {
115             if (!strDirectory.endsWith("/")) {
116                 strDirectory += "/";
117             }
118         }
119             
120         if ((iStart = strOutputFile.toLowerCase().indexOf(".sql")) != -1) {
121             strOutputFileBegin = strOutputFile.substring(0, iStart);
122             strOutputFileEnd = strOutputFile.substring(iStart);
123         } else {
124             strOutputFileBegin = strOutputFile;
125             strOutputFileEnd = ".sql";
126         }
127         File fWorkingDirectory = new File (strDirectory);
128         File fOutputCreateTable = new File (strOutputFileBegin + "Create"
129                 + strOutputFileEnd);
130         File fOutputAlterTable = new File (strOutputFileBegin + "Integrity"
131                 + strOutputFileEnd);
132         File fOutputDropTable = new File (strOutputFileBegin + "DropTable"
133                 + strOutputFileEnd);
134         File fOutputDropIntegrity = new File (strOutputFileBegin
135                 + "DropIntegrity" + strOutputFileEnd);
136         File fOutputDropPrimary = new File (strOutputFileBegin + "DropPrimary"
137                 + strOutputFileEnd);
138         File fOutputAlterTablePrimaryKey = new File (strOutputFileBegin
139                 + "Primary" + strOutputFileEnd);
140         File fOutputAlterTableIndex = new File (strOutputFileBegin + "Index"
141                 + strOutputFileEnd);
142           File fOutputDropTableIndex = new File (strOutputFileBegin + "DropIndex"
143                        + strOutputFileEnd);
144         File fOutputOtherSql = new File (strOutputFileBegin + "OtherSql"
145                 + strOutputFileEnd);
146
147         if (fOutputCreateTable.exists() || fOutputAlterTable.exists()
148                 || fOutputAlterTablePrimaryKey.exists()
149                 || fOutputDropTable.exists() || fOutputAlterTableIndex.exists()
150                 || fOutputDropIntegrity.exists() || fOutputDropPrimary.exists()
151                 || fOutputOtherSql.exists() || fOutputDropTableIndex.exists()) {
152             System.out.println("Output SQL file exists. Please choose different name!");
153             System.exit(1);
154         }
155         vPathSQLFiles = this.findSQLFiles(fWorkingDirectory);
156         for (int i = 0; i < vPathSQLFiles.size(); i++) {
157             System.out.println("File : " + vPathSQLFiles.get(i).toString()
158                     + " is analysing.");
159             this.createSQLFile(new File (vPathSQLFiles.get(i).toString()),
160                     fOutputCreateTable, fOutputOtherSql);
161         }
162         if (fOutputCreateTable.length() == 0) {
163             fOutputCreateTable.delete();
164         }
165         if (fOutputOtherSql.length() == 0) {
166             fOutputOtherSql.delete();
167         }
168         this.writeAlterTableForeignKey(fOutputAlterTable);
169         if (fOutputAlterTable.length() == 0) {
170             fOutputAlterTable.delete();
171         }
172         this.writeAlterTablePrimaryKey(fOutputAlterTablePrimaryKey);
173         if (fOutputAlterTablePrimaryKey.length() == 0) {
174             fOutputAlterTablePrimaryKey.delete();
175         }
176         this.writeDropTable(fOutputDropTable);
177         if (fOutputDropTable.length() == 0) {
178             fOutputDropTable.delete();
179         }
180         this.writeAlterTableIndex(fOutputAlterTableIndex);
181         if (fOutputAlterTableIndex.length() == 0) {
182             fOutputAlterTableIndex.delete();
183         }
184         this.writeDropIntegrity(fOutputDropIntegrity);
185         if (fOutputDropIntegrity.length() == 0) {
186             fOutputDropIntegrity.delete();
187         }
188           this.writeDropTableIndex(fOutputDropTableIndex);
189           if (fOutputDropTableIndex.length() == 0) {
190                fOutputDropTableIndex.delete();
191           }
192         
193         this.writeDropPrimary(fOutputDropPrimary);
194         if (fOutputDropPrimary.length() == 0) {
195             fOutputDropPrimary.delete();
196         }
197         if (vPathSQLFiles.size() == 0) {
198             System.out.println("\n No SQL files in target directory");
199         }
200     }
201
202     /**
203      * Method findSQLFiles finds all SQL files (all files which contain ".sql" in their name) in a working directory and
204      * all its subdirecories and puts their URL's into Vector.
205      * If there is an error Exception "Exception" is thrown.
206      *
207      * @param fDirectory The URL of the working directory.
208      *
209      * @return Vector The URLs of all output SQL files in working directory and in all its subdirectories.
210      */
211     public Vector findSQLFiles(File fDirectory) {
212         Vector vPathDirectory = new Vector ();
213         File [] arrFileSql;
214
215         try {
216             if ((arrFileSql = fDirectory.listFiles()) != null) {
217                 int i = 0;
218
219                 while (i < arrFileSql.length) {
220                     if (arrFileSql[i].isDirectory()) {
221                         Vector vTemp = findSQLFiles(arrFileSql[i]);
222
223                         for (int j = 0; j < vTemp.size(); j++) {
224                             vPathDirectory.addElement(vTemp.get(j));
225                         }
226                     } else if (arrFileSql[i].toString().toUpperCase().indexOf(".SQL")
227                             != -1) {
228                         vPathDirectory.addElement(arrFileSql[i]);
229                     }
230                     i++;
231                 }
232             } else {
233                 System.out.println("Directory " + fDirectory.toString()
234                         + " doesn't exist");
235                 System.exit(1);
236             }
237         } catch (Exception se) {
238             System.out.println("error " + se.getMessage());
239             System.exit(1);
240         }
241         return vPathDirectory;
242     }
243
244     /**
245      * Method createSQLFile prepares SQL statements for all output files and creates SQL file (fOutputSQLFile) with CREATE TABLE statements.
246      * Method ignores all comments, foreign keys references and primary keys SQL commands.
247      * If there is an error Exception "Exception" is thrown.
248      *
249      * @param fInputSQLFile The URL of the input SQL file.
250      * @param fCreateTable The URL of the output SQL file where we copy CREATE TABLE sql statements.
251      * @param fOther The URL of the output SQL file where we copy all other sql statements.
252      *
253      * @throws Exception.
254      */
255     public void createSQLFile(File fInputSQLFile, File fCreateTable, File fOther) {
256         String strInLine = "";
257         String strTableName = "";
258         String strRef = "";
259         String strForeignTable = "";
260         String strForeignKey = "";
261         String strPrimaryKey = "";
262         String strPrimaryColumn = "";
263         String strIndex = "";
264         boolean bNextIndexLine = false;
265         boolean bNextCreateTableLine = false;
266         int iStartPointer = 0;
267         int iEndPointer = 0;
268
269         try {
270             RandomAccessFile fWriteSQL = new RandomAccessFile (fCreateTable, "rw");
271             RandomAccessFile fWriteOtherSql = new RandomAccessFile (fOther, "rw");
272             long lWriteSql = fWriteSQL.length();
273             long lWriteOtherSql = fWriteOtherSql.length();
274
275             fWriteSQL.seek(lWriteSql);
276             fWriteOtherSql.seek(lWriteOtherSql);
277             String strWriteSQL = "";
278             String strWriteOtherSQL = "";
279             RandomAccessFile fReadSQL = new RandomAccessFile (fInputSQLFile, "r");
280
281             while ((strInLine = fReadSQL.readLine()) != null) {
282                 int brojac = 0;
283
284                 if ((((iStartPointer = strInLine.toUpperCase().indexOf("/*"))
285                                         != -1)
286                                 || (((iStartPointer = strInLine.toUpperCase().indexOf("--"))
287                                         != -1)))
288                         && !bNextIndexLine) {
289                     if (iStartPointer > 0) {
290                         strInLine = strInLine.substring(0, iStartPointer).trim();
291                     } else {
292                         strInLine = "";
293                     }
294                 }
295                 if (((iStartPointer = strInLine.toUpperCase().indexOf("CREATE TABLE"))
296                                 != -1)
297                         && !bNextIndexLine) {
298                     bNextCreateTableLine = true;
299                     if ((iEndPointer = strInLine.indexOf("(")) != -1) {
300                         strTableName = strInLine.substring(iStartPointer + 12, iEndPointer).trim();
301                     } else {
302                         strTableName = strInLine.substring(iStartPointer + 12).trim();
303                     }
304                     this.addTableName(strTableName);
305                 }
306                 int iStartConstraint = 0;
307
308                 if ((((iStartPointer = strInLine.toUpperCase().indexOf("REFERENCES"))
309                                         != -1)
310                                 && (strInLine.toUpperCase().indexOf("FOREIGN KEY")
311                                         == -1))
312                         && !bNextIndexLine) {
313                     if ((iStartConstraint = strInLine.toUpperCase().indexOf("CONSTRAINT"))
314                             != -1) {
315                         strForeignKey = strInLine.trim().substring(0,
316                                 strInLine.trim().indexOf(" "));
317                         String strTemp = strInLine.substring(iStartPointer + 10).trim();
318                         String strConstraint = strInLine.substring(iStartConstraint + 10).trim();
319
320                         strInLine = strInLine.substring(0, iStartConstraint - 1)
321                                 + ",";
322                         if ((iEndPointer = strTemp.indexOf(" (")) != -1) {
323                             strForeignTable = strTemp.substring(0, iEndPointer).trim();
324                         } else if ((iEndPointer = strTemp.indexOf("(")) != -1) {
325                             strForeignTable = strTemp.substring(0, iEndPointer).trim();
326                         }
327                         if ((iStartPointer = strTemp.indexOf("(")) != -1) {
328                             if ((iEndPointer = strTemp.indexOf(")")) != -1) {
329                                 strPrimaryKey = strTemp.substring(iStartPointer + 1, iEndPointer).trim();
330                             }
331                         }
332                         if ((iStartPointer = strTemp.indexOf(")")) != -1) {
333                             if ((iEndPointer = strTemp.indexOf(",")) != -1) {
334                                 strRef = strTemp.substring(iStartPointer + 1, iEndPointer).trim();
335                             } else {
336                                 strRef = strTemp.substring(iStartPointer + 1).trim();
337                             }
338                         }
339                         strConstraint = strConstraint.trim().substring(0,
340                                 strConstraint.trim().indexOf(" "));
341                         String rest = "";
342
343                         this.addForeignKeyValues(strTableName, strForeignTable,
344                                 strForeignKey, strPrimaryKey, strRef,
345                                 strConstraint, rest);
346                     } else {
347                         strForeignKey = strInLine.trim().substring(0,
348                                 strInLine.trim().indexOf(" "));
349                         String strTemp = strInLine.substring(iStartPointer + 10).trim();
350
351                         strInLine = strInLine.substring(0, iStartPointer - 1)
352                                 + ",";
353                         if ((iEndPointer = strTemp.indexOf(" (")) != -1) {
354                             strForeignTable = strTemp.substring(0, iEndPointer).trim();
355                         } else if ((iEndPointer = strTemp.indexOf("(")) != -1) {
356                             strForeignTable = strTemp.substring(0, iEndPointer).trim();
357                         }
358                         if ((iStartPointer = strTemp.indexOf("(")) != -1) {
359                             if ((iEndPointer = strTemp.indexOf(")")) != -1) {
360                                 strPrimaryKey = strTemp.substring(iStartPointer + 1, iEndPointer).trim();
361                             }
362                         }
363                         if ((iStartPointer = strTemp.indexOf(")")) != -1) {
364                             if ((iEndPointer = strTemp.indexOf(",")) != -1) {
365                                 strRef = strTemp.substring(iStartPointer + 1, iEndPointer).trim();
366                             } else {
367                                 strRef = strTemp.substring(iStartPointer + 1).trim();
368                             }
369                         }
370                         String strConstraint = strTableName + "_"
371                                 + strForeignKey;
372                         String rest = "";
373
374                         this.addForeignKeyValues(strTableName, strForeignTable,
375                                 strForeignKey, strPrimaryKey, strRef,
376                                 strConstraint, rest);
377                     }
378                 }
379                 iStartConstraint = 0;
380                 if (((iStartPointer = strInLine.toUpperCase().indexOf("PRIMARY KEY"))
381                                 != -1)
382                         && (!strInLine.toUpperCase().trim().startsWith("CONSTRAINT "))
383                         && !bNextIndexLine) {
384                     if ((iStartConstraint = strInLine.toUpperCase().indexOf("CONSTRAINT"))
385                             != -1) {
386                         strPrimaryColumn = strInLine.trim().substring(0,
387                                 strInLine.trim().indexOf(" "));
388                         String strConstraintPrimary = strInLine.substring(iStartConstraint + 10).trim();
389
390                         strConstraintPrimary = strConstraintPrimary.trim().substring(0,
391                                 strConstraintPrimary.trim().indexOf(" "));
392                         if(isSplitSQLPrimary()){
393                             strInLine = strInLine.substring(0, iStartConstraint - 1)+ ",";
394                         }
395                         
396                         this.addPrimaryKeyValues(strTableName, strPrimaryColumn,
397                                 strConstraintPrimary);
398                     } else {
399                         strPrimaryColumn = strInLine.trim().substring(0,
400                                 strInLine.trim().indexOf(" "));
401                         if(isSplitSQLPrimary()){
402                             strInLine = strInLine.substring(0, iStartPointer - 1)+ ",";
403                         }
404                         String strConstraintPrimary = XSLTUtil.returnFixedConstraintName(strTableName,strPrimaryColumn,"CREATE","Spliter_PK",strTableName,strPrimaryColumn,"");
405                         
406                         this.addPrimaryKeyValues(strTableName, strPrimaryColumn,
407                                 strConstraintPrimary);
408                     }
409                 }
410               
411                 if ((((iStartPointer = strInLine.toUpperCase().indexOf("CREATE "))
412                                         != -1)
413                                 && ((strInLine.toUpperCase().indexOf(" INDEX "))
414                                         != -1))
415                         || bNextIndexLine) {
416                     if ((iEndPointer = strInLine.trim().indexOf(";")) == -1) {
417                         bNextIndexLine = true;
418                         iEndPointer = strInLine.trim().length() - 1;
419                     } else {
420                         bNextIndexLine = false;
421                     }
422                     strIndex = strInLine.trim().substring(0, iEndPointer + 1);
423                     if (iStartPointer > 1) {
424                         strInLine = strInLine.substring(0, iStartPointer - 1);
425                     } else {
426                         if (iStartPointer != -1) {
427                             strInLine = "";
428                         }
429                     }
430                     this.addIndexValues(strIndex);
431                 }
432                 if ((((iStartPointer = strInLine.toUpperCase().indexOf("CONSTRAINT "))
433                                 != -1)
434                         && ((strInLine.toUpperCase().indexOf(" FOREIGN KEY"))
435                                 != -1))) {
436                     int iForeignKey = strInLine.toUpperCase().indexOf(" FOREIGN KEY");
437                     String strConstraint = strInLine.substring(iStartPointer + 10, iForeignKey).trim();
438                     int iReference = strInLine.toUpperCase().indexOf(" REFERENCES ");
439                     String strFK = strInLine.substring(iForeignKey + 12, iReference).trim();
440
441                     strFK = strFK.substring(1, strFK.length() - 1);
442                     String endLine = strInLine.substring(iReference);
443                     int iStartBracket = endLine.indexOf("(");
444                     int iEndBracket = endLine.indexOf(")");
445                     String strFT = endLine.substring(12, iStartBracket).trim();
446                     String strPK = endLine.substring(iStartBracket + 1, iEndBracket).trim();
447                     String rest = endLine.substring(iEndBracket + 1).trim();
448
449                     rest = rest.replaceAll(",", " ");
450                     strInLine = "";
451                     this.addForeignKeyValues(strTableName, strFT, strFK, strPK,
452                             strRef, strConstraint, rest);
453                 }
454                 if ((((iStartPointer = strInLine.toUpperCase().indexOf("CONSTRAINT "))
455                                 != -1)
456                         && ((strInLine.toUpperCase().indexOf(" PRIMARY KEY"))
457                                 != -1))) {
458                     int iPrimaryKey = strInLine.toUpperCase().indexOf(" PRIMARY KEY");
459                     String strConstraint = strInLine.substring(iStartPointer + 10, iPrimaryKey).trim();
460                     int iStartBracket = strInLine.indexOf("(");
461                     int iEndBracket = strInLine.indexOf(")");
462                     String strPK = strInLine.substring(iStartBracket + 1, iEndBracket).trim();
463                     if(isSplitSQLPrimary()){
464                         strInLine = "";
465                     }
466                     this.addPrimaryKeyValues(strTableName, strPK, strConstraint);
467                 }
468                 if ((strInLine != null) && !strInLine.trim().equals("")
469                         && bNextCreateTableLine) {
470                     strWriteSQL = strWriteSQL.concat(strInLine + "\n");
471                 } else if (!strInLine.equals("")) {
472                     strWriteOtherSQL = strWriteOtherSQL.concat(strInLine + "\n");
473                 }
474                 if (strInLine.indexOf(";") != -1 && bNextCreateTableLine) {
475                     bNextCreateTableLine = false;
476                 }
477             }
478             
479             if (!strWriteSQL.equals("")) {
480                 StringTokenizer tokenizerCreate = new StringTokenizer (strWriteSQL,
481                         ";");
482             
483                 while (tokenizerCreate.hasMoreTokens()) {
484                 
485                     String strToken = tokenizerCreate.nextToken().trim();
486
487                     if (!strToken.equals("")) {
488                             
489                         boolean isOK = true;
490                         int endComPos = 0;
491                         int endPos = strToken.lastIndexOf(")");
492
493                         if (endPos != -1) {
494                             endComPos = strToken.substring(0, endPos).lastIndexOf(",");
495                             if (endComPos != -1) {
496                                 String checkString = strToken.substring(endComPos
497                                         + 1,
498                                         endPos);
499
500                                 OK:
501                                 for (int i = 0; i < checkString.length(); i++) {
502                                     if (checkString.charAt(i) != ' '
503                                             && checkString.charAt(i) != '\n') {
504                                         isOK = true;
505                                         break OK;
506                                     } else {
507                                         isOK = false;
508                                     }
509                                 }
510                             }
511                         }
512                         if (!isOK) {
513                             strToken = strToken.substring(0, endComPos)
514                                     + strToken.substring(endComPos + 1);
515                         }
516                         fWriteSQL.writeBytes(strToken + ";\n\n");
517                     }
518                                             
519                 }
520                 if (!strWriteOtherSQL.equals("")) {
521                     fWriteSQL.writeBytes(strWriteOtherSQL + "\n\n");
522                 }
523                     
524             }
525             fReadSQL.close();
526             fWriteSQL.close();
527             fWriteOtherSql.close();
528         } catch (Exception e) {
529             e.printStackTrace();
530         }
531     }
532
533     /**
534      * Method addForeignKeyValues adds values to global variables - vectors.
535      * These variables are used for creating ALTER TABLE sql commands - foreign key references.
536      *
537      * @param strTableName Name of table where the foreign key SQL statement was found.
538      * @param strForeignTable 'Foreign table'.
539      * @param strForeignKey Column in the table 'strTablename' which is referenced by foreign key.
540      * @param strPrimaryKey Column in the table 'strForeignTable' - primary key.
541      * @param strReference Sql commands after foreign key columns (such as ON DELETE CASCADE).
542      * @param strConstraint Name of Foreign Key constraint.
543      * @param strRest ON DELETE ...
544      */
545     public void addForeignKeyValues(String strTableName, String strForeignTable,
546             String strForeignKey, String strPrimaryKey, String strReference,
547             String strConstraint, String strRest) {
548         this.vecTableName.addElement(strTableName);
549         this.vecForeignTable.addElement(strForeignTable);
550         this.vecForeignKey.addElement(strForeignKey);
551         this.vecPrimaryKey.addElement(strPrimaryKey);
552         this.vecReference.addElement(strReference);
553         this.vecConstraintName.addElement(strConstraint);
554         this.vecFKRest.addElement(strRest);
555     }
556
557     /**
558      * Method addPrimaryKeyValues adds values to global variables - vectors.
559      * These variables are used for creating ALTER TABLE sql commands - primary key.
560      *
561      * @param strTableName Name of table where the primary key SQL statement was found.
562      * @param strPrimaryColumn Name of column which is primary key.
563      * @param strConstraintPrimary Name of Primary key constraint.
564      */
565     public void addPrimaryKeyValues(String strTableName, String strPrimaryColumn,
566             String strConstraintPrimary) {
567         this.vecPrimaryKeyTableName.addElement(strTableName);
568         this.vecPrimaryKeyColumn.addElement(strPrimaryColumn);
569         this.vecConstraintPrimary.addElement(strConstraintPrimary);
570     }
571
572     /**
573      * Method addTableName adds values to global variables - vectors.
574      * These variables are used for creating DROP TABLE sql commands.
575      *
576      * @param strTableName Name of table which it will be deleted.
577      */
578     public void addTableName(String strTableName) {
579         boolean bEqual = false;
580
581         for (int i = 0; i < this.vecDropTableTableName.size(); i++) {
582             if (this.vecDropTableTableName.get(i).toString().equalsIgnoreCase(strTableName)) {
583                 bEqual = true;
584             }
585         }
586         if (!bEqual) {
587             this.vecDropTableTableName.addElement(strTableName);
588         }
589     }
590
591     /**
592      * Method addIndexValues adds values to global variables - vectors.
593      * These variables are used for creating CREATE INDEX sql commands.
594      *
595      * @param strIndex All create index SQL statements.
596      */
597     public void addIndexValues(String strIndex) {
598         this.vecCreateIndex.addElement(strIndex);
599     }
600
601     /**
602      * Method writeAlterTableForeignKey creates SQL file with ALTER TABLE SQL commands (foreign key references).
603      * If there is an error Exception "Exception" is thrown.
604      *
605      * @param fOutputAlterTable The URL of the output SQL file.
606      *
607      * @throws Exception.
608      */
609     public void writeAlterTableForeignKey(File fOutputAlterTable) {
610         try {
611             RandomAccessFile fWriteAlterTable = new RandomAccessFile (fOutputAlterTable,
612                     "rw");
613
614             fWriteAlterTable.seek(fWriteAlterTable.length());
615             for (int i = 0; i < this.vecTableName.size(); i++) {
616                 fWriteAlterTable.writeBytes("\nALTER TABLE "
617                         + this.vecTableName.get(i).toString()
618                         + " ADD CONSTRAINT "
619                         + this.vecConstraintName.get(i).toString()
620                         + " FOREIGN KEY ("
621                         + this.vecForeignKey.get(i).toString() + ") REFERENCES "
622                         + this.vecForeignTable.get(i).toString() + " ("
623                         + this.vecPrimaryKey.get(i).toString() + ") "
624                         + this.vecReference.get(i).toString() + " "
625                         + this.vecFKRest.get(i).toString() + " ;\n");
626             }
627             fWriteAlterTable.close();
628         } catch (Exception e) {
629             e.printStackTrace();
630         }
631     }
632
633     /**
634      * Method writeDropIntegrity creates SQL file with ALTER TABLE SQL commands (drop foreign key references).
635      * If there is an error Exception "Exception" is thrown.
636      *
637      * @param fOutputDropIntegrity The URL of the output SQL file.
638      *
639      * @throws Exception.
640      */
641     public void writeDropIntegrity(File fOutputDropIntegrity) {
642         try {
643             RandomAccessFile fWriteAlterTable = new RandomAccessFile (fOutputDropIntegrity,
644                     "rw");
645
646             fWriteAlterTable.seek(fWriteAlterTable.length());
647             for (int i = 0; i < this.vecTableName.size(); i++) {
648                 fWriteAlterTable.writeBytes("\nALTER TABLE "
649                         + this.vecTableName.get(i).toString()
650                         + " DROP CONSTRAINT "
651                         + this.vecConstraintName.get(i).toString() + " ;\n");
652             }
653             fWriteAlterTable.close();
654         } catch (Exception e) {
655             e.printStackTrace();
656         }
657     }
658
659     /**
660      * Method writeDropPrimary creates SQL file with ALTER TABLE SQL commands (drop primary keys).
661      * If there is an error Exception "Exception" is thrown.
662      *
663      * @param fOutputDropPrimary The URL of the output SQL file.
664      *
665      * @throws Exception.
666      */
667     public void writeDropPrimary(File fOutputDropPrimary) {
668         try {
669             RandomAccessFile fWriteAlterTable = new RandomAccessFile (fOutputDropPrimary,
670                     "rw");
671
672             fWriteAlterTable.seek(fWriteAlterTable.length());
673             for (int i = 0; i < this.vecPrimaryKeyTableName.size(); i++) {
674                 fWriteAlterTable.writeBytes("\nALTER TABLE "
675                         + this.vecPrimaryKeyTableName.get(i).toString()
676                         + " DROP CONSTRAINT "
677                         + this.vecConstraintPrimary.get(i).toString() + " ;\n");
678             }
679             fWriteAlterTable.close();
680         } catch (Exception e) {
681             e.printStackTrace();
682         }
683     }
684
685     /**
686      * Method writeAlterTablePrimaryKey createss SQL file with ALTER TABLE SQL commands (primary keys for all tables).
687      * If there is an error Exception "Exception" is thrown.
688      *
689      * @param fOutputAlterTable The URL of the output SQL file.
690      *
691      * @throws Exception.
692      */
693     public void writeAlterTablePrimaryKey(File fOutputAlterTable) {
694         try {
695             RandomAccessFile fWriteAlterTable = new RandomAccessFile (fOutputAlterTable,
696                     "rw");
697
698             fWriteAlterTable.seek(fWriteAlterTable.length());
699             for (int i = 0; i < this.vecPrimaryKeyTableName.size(); i++) {
700                 fWriteAlterTable.writeBytes("\nALTER TABLE "
701                         + this.vecPrimaryKeyTableName.get(i).toString()
702                         + " ADD CONSTRAINT "
703                         + this.vecConstraintPrimary.get(i).toString()
704                         + " PRIMARY KEY ("
705                         + this.vecPrimaryKeyColumn.get(i).toString() + ") ;\n");
706             }
707             fWriteAlterTable.close();
708         } catch (Exception e) {
709             e.printStackTrace();
710         }
711     }
712
713     /**
714      * Method writeDropTable creates SQL file with DROP TABLE SQL commands. All tables will be deleted.
715      * If there is an error Exception "Exception" is thrown.
716      *
717      * @param fOutputDropTable The URL of the output SQL file.
718      *
719      * @throws Exception.
720      */
721     public void writeDropTable(File fOutputDropTable) {
722         try {
723             RandomAccessFile fWriteDropTable = new RandomAccessFile (fOutputDropTable,
724                     "rw");
725
726             fWriteDropTable.seek(fWriteDropTable.length());
727             for (int i = 0; i < this.vecDropTableTableName.size(); i++) {
728                 fWriteDropTable.writeBytes("\nDROP TABLE "
729                         + this.vecDropTableTableName.get(i).toString() + " ;\n");
730             }
731             fWriteDropTable.close();
732         } catch (Exception e) {
733             e.printStackTrace();
734         }
735     }
736
737     /**
738      * Method writeAlterTableIndex creates SQL file with CREATE INDEX SQL commands.
739      * If there is an error Exception "Exception" is thrown.
740      *
741      * @param fOutputAlterTableIndex The URL of the output SQL file.
742      *
743      * @throws Exception.
744      */
745     public void writeAlterTableIndex(File fOutputAlterTableIndex) {
746         try {
747             RandomAccessFile fWriteIndex = new RandomAccessFile (fOutputAlterTableIndex,
748                     "rw");
749
750             fWriteIndex.seek(fWriteIndex.length());
751             for (int i = 0; i < this.vecCreateIndex.size(); i++) {
752                 fWriteIndex.writeBytes("\n"
753                         + this.vecCreateIndex.get(i).toString() + "\n");
754             }
755             fWriteIndex.close();
756         } catch (Exception e) {
757             e.printStackTrace();
758         }
759     }
760     
761     /**
762      * Method writeDropTableIndex creates SQL file with DROP INDEX SQL commands.
763      * If there is an error Exception "Exception" is thrown.
764      *
765      * @param fOutputDropTableIndex The URL of the output SQL file.
766      *
767      * @throws Exception.
768      */
769     public void writeDropTableIndex(File fOutputDropTableIndex) {
770         try {
771             RandomAccessFile fWriteIndex = new RandomAccessFile (fOutputDropTableIndex,
772                     "rw");
773
774             fWriteIndex.seek(fWriteIndex.length());
775             for (int i = 0; i < this.vecCreateIndex.size(); i++) {
776                 String temp = this.vecCreateIndex.get(i).toString();
777
778                 fWriteIndex.writeBytes("\n DROP "
779                         + temp.substring(temp.indexOf("INDEX"),temp.indexOf("ON")) + ";\n");
780             }
781             fWriteIndex.close();
782         } catch (Exception e) {
783             e.printStackTrace();
784         }
785
786     }
787     
788     /**
789      * @return value of splitSQLPrimary.
790      */
791     public boolean isSplitSQLPrimary() {
792         return splitSQLPrimary;
793     }
794     
795     /**
796      * @param b new value for setSplitSQLPrimary
797      */
798     public void setSplitSQLPrimary(boolean b) {
799         splitSQLPrimary = b;
800     }
801
802     
803 }
804

A to Z: JavaDoc & Examples

---

Daily Java News & Articles

---

Open Source Projects

---

Open Source Codes

---

Free Computer Books

---

Remove Frame

---

Popular Tags