Java > Open Source Codes > org > hsqldb > util > TransferSQLText


1 /* Copyright (c) 2001-2005, The HSQL Development Group
2  * All rights reserved.
3  *
4  * Redistribution and use in source and binary forms, with or without
5  * modification, are permitted provided that the following conditions are met:
6  *
7  * Redistributions of source code must retain the above copyright notice, this
8  * list of conditions and the following disclaimer.
9  *
10  * Redistributions in binary form must reproduce the above copyright notice,
11  * this list of conditions and the following disclaimer in the documentation
12  * and/or other materials provided with the distribution.
13  *
14  * Neither the name of the HSQL Development Group nor the names of its
15  * contributors may be used to endorse or promote products derived from this
16  * software without specific prior written permission.
17  *
18  * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
19  * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
20  * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
21  * ARE DISCLAIMED. IN NO EVENT SHALL HSQL DEVELOPMENT GROUP, HSQLDB.ORG,
22  * OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
23  * EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
24  * PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
25  * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
26  * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
27  * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
28  * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
29  */
30
31
32 package org.hsqldb.util;
33
34 import java.io.BufferedReader ;
35 import java.io.BufferedWriter ;
36 import java.io.FileReader ;
37 import java.io.FileWriter ;
38 import java.io.IOException ;
39 import java.sql.SQLException ;
40 import java.util.Enumeration ;
41 import java.util.Hashtable ;
42 import java.util.NoSuchElementException ;
43 import java.util.StringTokenizer ;
44 import java.util.Vector ;
45
46 /**
47  * @author Nicolas BAZIN, INGENICO
48  * @version 1.7.0
49  */
50 class TransferSQLText extends DataAccessPoint {
51
52     String sFileName = null;
53     BufferedWriter WTextWrite = null;
54     BufferedReader WTextRead = null;
55     protected boolean StructureAlreadyParsed = false;
56     Hashtable DbStmts = null;
57     protected JDBCTypes JDBCT = null;
58
59     TransferSQLText(String _FileName,
60                     Traceable t) throws DataAccessPointException {
61
62         super(t);
63
64         sFileName = _FileName;
65         JDBCT = new JDBCTypes();
66
67         if (sFileName == null) {
68             throw new DataAccessPointException("File name not initialized");
69         }
70     }
71
72     boolean execute(String statement) throws DataAccessPointException {
73
74         if (WTextWrite == null) {
75             try {
76                 WTextWrite = new BufferedWriter (new FileWriter (sFileName));
77             } catch (IOException e) {
78                 throw new DataAccessPointException(e.getMessage());
79             }
80         }
81
82         try {
83             WTextWrite.write(statement + "\n");
84             WTextWrite.flush();
85         } catch (IOException e) {
86             throw new DataAccessPointException(e.getMessage());
87         }
88
89         return true;
90     }
91
92     void putData(String statement, TransferResultSet r,
93                  int iMaxRows) throws DataAccessPointException {
94
95         int i = 0;
96
97         if (r == null) {
98             return;
99         }
100
101         if (WTextWrite == null) {
102             try {
103                 WTextWrite = new BufferedWriter (new FileWriter (sFileName));
104             } catch (IOException e) {
105                 throw new DataAccessPointException(e.getMessage());
106             }
107         }
108
109         try {
110             while (r.next()) {
111                 if (i == 0) {
112                     WTextWrite.write(statement + "\n");
113                     WTextWrite.flush();
114                 }
115
116                 transferRow(r);
117
118                 if (iMaxRows != 0 && i == iMaxRows) {
119                     break;
120                 }
121
122                 i++;
123
124                 if (iMaxRows != 0 || i % 100 == 0) {
125                     tracer.trace("Transfered " + i + " rows");
126                 }
127             }
128         } catch (Exception e) {
129             throw new DataAccessPointException(e.getMessage());
130         } finally {
131             try {
132                 if (i > 0) {
133                     WTextWrite.write("\tNumber of Rows=" + i + "\n\n");
134                     WTextWrite.flush();
135                 }
136             } catch (IOException e) {
137                 throw new DataAccessPointException(e.getMessage());
138             }
139         }
140     }
141
142     void close() throws DataAccessPointException {
143
144         if (WTextWrite != null) {
145             try {
146                 WTextWrite.flush();
147                 WTextWrite.close();
148             } catch (IOException e) {}
149         }
150     }
151
152     /**
153      * Method declaration
154      *
155      *
156      * @param type
157      * @param r
158      * @param p
159      *
160      * @throws SQLException
161      */
162     private void transferRow(TransferResultSet r) throws Exception {
163
164         String sLast = "";
165         int len = r.getColumnCount();
166
167         if (WTextWrite == null) {
168             try {
169                 WTextWrite = new BufferedWriter (new FileWriter (sFileName));
170             } catch (IOException e) {
171                 throw new DataAccessPointException(e.getMessage());
172             }
173         }
174
175         for (int i = 0; i < len; i++) {
176             int t = r.getColumnType(i + 1);
177
178             sLast = "column=" + r.getColumnName(i + 1) + " datatype="
179                     + (String ) helper.getSupportedTypes().get(new Integer (t));
180
181             Object o = r.getObject(i + 1);
182
183             if (o == null) {
184                 sLast += " value=<null>";
185             } else {
186                 o = helper.convertColumnValue(o, i + 1, t);
187                 sLast += " value=\'" + o.toString() + "\'";
188             }
189
190             WTextWrite.write("\t" + sLast + "\n");
191             WTextWrite.flush();
192         }
193
194         WTextWrite.write("\n");
195         WTextWrite.flush();
196
197         sLast = "";
198     }
199
200     class ColumnDef {
201
202         String columnName;
203         String columnType;
204         String options;
205         int start;
206         int len;
207
208         public ColumnDef() {
209
210             columnName = "";
211             columnType = "";
212             options = "";
213             start = 0;
214             len = 0;
215         }
216     }
217
218     ColumnDef getColumnDef(String ColumnsDesc, int curPos) {
219
220         int nextPos = 0;
221         ColumnDef columnDef = new TransferSQLText.ColumnDef();
222
223         columnDef.start = curPos;
224
225         if ((ColumnsDesc == null) || (ColumnsDesc.length() == 0)
226                 || (curPos >= ColumnsDesc.length())) {
227             return new TransferSQLText.ColumnDef();
228         }
229
230         String stbuff = ColumnsDesc.substring(curPos);
231
232         try {
233             int i = 0;
234
235             for (; i < stbuff.length(); i++) {
236                 int c = stbuff.charAt(i);
237
238                 if (c == ',' || c == ' ' || c == ')' || c == ';') {
239                     continue;
240                 } else {
241                     break;
242                 }
243             }
244
245             if (i == stbuff.length()) {
246                 return new TransferSQLText.ColumnDef();
247             }
248
249             columnDef.len += i;
250             stbuff = stbuff.substring(i);
251
252             while (stbuff.charAt(nextPos) != ' ') {
253                 nextPos++;
254             }
255
256             columnDef.columnName = stbuff.substring(0, nextPos);
257             stbuff = stbuff.substring(nextPos);
258             columnDef.len += nextPos;
259             nextPos = 0;
260
261             if (!columnDef.columnName.toUpperCase().equals("CONSTRAINT")) {
262                 i = 0;
263
264                 for (; i < stbuff.length() && stbuff.charAt(i) == ' '; i++) {}
265
266                 stbuff = stbuff.substring(i);
267                 columnDef.len += i;
268
269                 while ((stbuff.charAt(nextPos) != '(')
270                         && (stbuff.charAt(nextPos) != ',')
271                         && (stbuff.charAt(nextPos) != ')')
272                         && (stbuff.charAt(nextPos) != ';')
273                         && (stbuff.charAt(nextPos) != ' ')) {
274                     nextPos++;
275                 }
276
277                 columnDef.columnType = stbuff.substring(0,
278                         nextPos).toUpperCase();
279                 stbuff = stbuff.substring(nextPos);
280                 columnDef.len += nextPos;
281                 nextPos = 0;
282             }
283
284             while ((stbuff.charAt(nextPos) != ',')
285                     && (stbuff.charAt(nextPos) != ';')
286                     && (nextPos < stbuff.length())
287                     && (stbuff.charAt(nextPos) != ')')) {
288                 if (stbuff.charAt(nextPos) == '(') {
289                     while (stbuff.charAt(nextPos) != ')') {
290                         nextPos++;
291                     }
292                 }
293
294                 nextPos++;
295             }
296
297             columnDef.options = stbuff.substring(0, nextPos);
298             columnDef.len += nextPos;
299         } catch (Exception e) {
300             columnDef = new TransferSQLText.ColumnDef();
301         }
302
303         return columnDef;
304     }
305
306     String translateTypes(String CreateLine, TransferTable TTable,
307                           DataAccessPoint Dest)
308                           throws DataAccessPointException {
309
310         String translatedLine = "";
311         JDBCTypes JDBCT = new JDBCTypes();
312         int currentPos = 0;
313         String columnName = "";
314         String columnType = "";
315         int colnum = 0;
316         ColumnDef cDef;
317
318         currentPos = CreateLine.indexOf('(') + 1;
319         translatedLine = CreateLine.substring(0, currentPos);
320
321         do {
322             cDef = getColumnDef(CreateLine, currentPos);
323
324             if (cDef.len == 0) {
325                 break;
326             }
327
328             columnName = cDef.columnName;
329             columnType = cDef.columnType;
330
331             if (columnName.toUpperCase().indexOf("CONSTRAINT") >= 0) {
332                 translatedLine +=
333                     CreateLine.substring(currentPos, currentPos + cDef.len)
334                     + ",";
335                 currentPos += cDef.len + 1;
336
337                 colnum++;
338
339                 continue;
340             }
341
342             columnName = Dest.helper.formatIdentifier(columnName) + " ";
343
344             try {
345                 Integer inttype = new Integer (
346                     Dest.helper.convertToType(JDBCT.toInt(columnType)));
347
348                 columnType = (String ) TTable.hTypes.get(inttype);
349             } catch (Exception JDBCtypeEx) {}
350
351             if (cDef.options != null) {
352                 columnType += cDef.options;
353             }
354
355             try {
356                 columnType = Dest.helper.fixupColumnDefWrite(TTable, null,
357                         columnType, null, colnum);
358             } catch (SQLException SQLe) {
359                 return CreateLine;
360             }
361
362             translatedLine += columnName + " " + columnType + ",";
363             currentPos += cDef.len + 1;
364
365             colnum++;
366         } while (true);
367
368         return translatedLine.substring(0, translatedLine.length() - 1)
369                + ");";
370     }
371
372     void parseFileForTables() throws DataAccessPointException {
373
374         StringTokenizer Tokenizer;
375
376         if (WTextRead == null) {
377             try {
378                 WTextRead = new BufferedReader (new FileReader (sFileName));
379             } catch (IOException e) {
380                 throw new DataAccessPointException(e.getMessage());
381             }
382         }
383
384         String currentLine = "";
385         String Token = "";
386         String name = "";
387         TransferTable relatedTable = null;
388
389         try {
390             while ((currentLine = WTextRead.readLine()) != null) {
391                 currentLine = currentLine.trim() + ";";
392                 Tokenizer = new StringTokenizer (currentLine);
393
394                 try {
395                     Token = Tokenizer.nextToken();
396                 } catch (NoSuchElementException NSE) {
397                     continue;
398                 }
399
400                 if (Token == null) {
401                     continue;
402                 }
403
404                 if (!Token.toUpperCase().equals("CREATE")) {
405                     continue;
406                 }
407
408                 Token = Tokenizer.nextToken().toUpperCase();
409
410                 if (Token.equals("TABLE") || Token.equals("VIEW")) {
411                     try {
412                         name = Tokenizer.nextToken(" (;");
413                         relatedTable = new TransferTable(this, name, "",
414                                                          Token, tracer);
415                         relatedTable.Stmts.bCreate = false;
416                         relatedTable.Stmts.bDelete = false;
417                         relatedTable.Stmts.bDrop = false;
418                         relatedTable.Stmts.bCreateIndex = false;
419                         relatedTable.Stmts.bDropIndex = false;
420                         relatedTable.Stmts.bInsert = false;
421                         relatedTable.Stmts.bAlter = false;
422
423                         DbStmts.put(relatedTable.Stmts.sSourceTable,
424                                     relatedTable);
425                     } catch (NoSuchElementException NSE) {
426                         continue;
427                     }
428                 }
429             }
430         } catch (Exception IOe) {
431             throw new DataAccessPointException(IOe.getMessage());
432         }
433     }
434
435     void parseFileForTheRest(TransferTable TTable,
436                              DataAccessPoint Dest)
437                              throws DataAccessPointException {
438
439         StringTokenizer Tokenizer;
440
441         StructureAlreadyParsed = true;
442
443         if (WTextRead == null) {
444             try {
445                 WTextRead = new BufferedReader (new FileReader (sFileName));
446             } catch (IOException e) {
447                 throw new DataAccessPointException(e.getMessage());
448             }
449         }
450
451         String currentLine = "";
452         String Token = "";
453         String name = "";
454         TransferTable relatedTable = null;
455
456         try {
457             while ((currentLine = WTextRead.readLine()) != null) {
458                 currentLine = currentLine.trim() + ";";
459                 Tokenizer = new StringTokenizer (currentLine);
460
461                 try {
462                     Token = Tokenizer.nextToken();
463                 } catch (NoSuchElementException NSE) {
464                     continue;
465                 }
466
467                 if (Token == null) {
468                     continue;
469                 }
470
471                 if (Token.toUpperCase().equals("INSERT")) {
472                     try {
473                         if (!Tokenizer.nextToken().toUpperCase().equals(
474                                 "INTO")) {
475                             throw new DataAccessPointException(
476                                 "Error in INSERT statement: no INTO found");
477                         }
478
479                         Token = Tokenizer.nextToken();
480
481                         if ((relatedTable =
482                                 (TransferTable) DbStmts.get(Token)) != null) {
483                             relatedTable.Stmts.bDelete = true;
484                             relatedTable.Stmts.bInsert = true;
485                             relatedTable.Stmts.sDestInsert = currentLine;
486                             relatedTable.Stmts.sDestDelete =
487                                 "DELETE FROM "
488                                 + relatedTable.Stmts.sSourceTable + ";";
489                         }
490
491                         continue;
492                     } catch (NoSuchElementException NSE) {
493                         continue;
494                     }
495                 } else if (Token.toUpperCase().equals("ALTER")) {
496                     try {
497                         if (!Tokenizer.nextToken().toUpperCase().equals(
498                                 "TABLE")) {
499                             continue;
500                         }
501
502                         name = Tokenizer.nextToken();
503                         Token = Tokenizer.nextToken().toUpperCase();
504
505                         if (!Token.equals("ADD")) {
506                             continue;
507                         }
508
509                         do {
510                             Token = Tokenizer.nextToken().toUpperCase();
511                         } while (!Token.equals("CONSTRAINT"));
512
513                         if ((relatedTable = (TransferTable) DbStmts.get(name))
514                                 != null) {
515                             if (relatedTable.Stmts.sDestAlter == null) {
516                                 relatedTable.Stmts.sDestAlter = "";
517                             }
518
519                             relatedTable.Stmts.bAlter = true;
520                             relatedTable.Stmts.sDestAlter += currentLine;
521                         } else {
522                             throw new DataAccessPointException(
523                                 "table not found");
524                         }
525
526                         Token = Tokenizer.nextToken();
527
528                         if (relatedTable.Stmts.sDestDrop == null) {
529                             relatedTable.Stmts.sDestDrop = "";
530                         }
531
532                         relatedTable.Stmts.bDrop = true;
533                         relatedTable.Stmts.sDestDrop =
534                             "ALTER TABLE " + name + " DROP CONSTRAINT "
535                             + Token + ";" + relatedTable.Stmts.sDestDrop;
536
537                         continue;
538                     } catch (NoSuchElementException NSE) {
539                         continue;
540                     }
541                 } else if (!Token.toUpperCase().equals("CREATE")) {
542                     continue;
543                 }
544
545                 Token = Tokenizer.nextToken().toUpperCase();
546
547                 if (Token.equals("TABLE") || Token.equals("VIEW")) {
548                     try {
549                         name = Tokenizer.nextToken(" (;");
550
551                         if (!DbStmts.containsKey(name)) {
552                             throw new DataAccessPointException(
553                                 "error: index is created before the table");
554                         }
555
556                         relatedTable = (TransferTable) DbStmts.get(name);
557                         relatedTable.Stmts.bCreate = true;
558                         relatedTable.Stmts.bDrop = true;
559
560 // relatedTable.Stmts.sDestCreate = currentLine;
561 relatedTable.Stmts.sDestCreate =
562                             translateTypes(currentLine, TTable, Dest);
563                         relatedTable.Stmts.sDestDrop =
564                             "DROP " + relatedTable.Stmts.sType + " " + name
565                             + ";";
566
567                         DbStmts.put(relatedTable.Stmts.sSourceTable,
568                                     relatedTable);
569                     } catch (NoSuchElementException NSE) {
570                         continue;
571                     }
572                 }
573
574                 if (Token.equals("INDEX") || Token.equals("UNIQUE")) {
575                     try {
576                         while ((Token =
577                                 Tokenizer.nextToken()).toUpperCase().equals(
578                                     "INDEX")) {
579                             ;
580                         }
581
582                         String IndexdropCommand = "DROP INDEX " + Token
583                                                   + " ;";
584
585                         while ((Token = Tokenizer.nextToken(
586                                 " (")).toUpperCase().equals("ON")) {
587                             ;
588                         }
589
590                         name = Token;
591
592                         if (!DbStmts.containsKey(Token)) {
593                             throw new DataAccessPointException(
594                                 "error: index is created before the table");
595                         }
596
597                         relatedTable = (TransferTable) DbStmts.get(Token);
598
599                         if (relatedTable.Stmts.sDestCreateIndex == null) {
600                             relatedTable.Stmts.sDestCreateIndex = "";
601                         }
602
603                         if (relatedTable.Stmts.sDestDropIndex == null) {
604                             relatedTable.Stmts.sDestDropIndex = "";
605                         }
606
607                         relatedTable.Stmts.bCreateIndex = true;
608                         relatedTable.Stmts.bDropIndex = true;
609                         relatedTable.Stmts.sDestCreateIndex += currentLine;
610                         relatedTable.Stmts.sDestDropIndex += IndexdropCommand;
611                     } catch (NoSuchElementException NSE) {
612                         continue;
613                     }
614                 }
615             }
616         } catch (IOException IOe) {
617             throw new DataAccessPointException(IOe.getMessage());
618         }
619     }
620
621     Vector getTables(String sCatalog,
622                      String [] sSchemas) throws DataAccessPointException {
623
624         Vector AllTables = new Vector ();
625
626         if (DbStmts == null) {
627             DbStmts = new Hashtable ();
628         }
629
630         if (WTextRead != null) {
631             try {
632                 WTextRead.close();
633
634                 WTextRead = null;
635             } catch (IOException e) {}
636         }
637
638         this.parseFileForTables();
639
640         StructureAlreadyParsed = false;
641
642         Enumeration e = DbStmts.elements();
643
644         while (e.hasMoreElements()) {
645             AllTables.addElement(e.nextElement());
646         }
647
648         return AllTables;
649     }
650
651     void getTableStructure(TransferTable TTable,
652                            DataAccessPoint Dest)
653                            throws DataAccessPointException {
654
655         if (!StructureAlreadyParsed) {
656             if (WTextRead != null) {
657                 try {
658                     WTextRead.close();
659
660                     WTextRead = null;
661                 } catch (IOException e) {}
662             }
663
664             this.parseFileForTheRest(TTable, Dest);
665         }
666     }
667
668     TransferResultSet getData(String statement)
669     throws DataAccessPointException {
670
671         StringTokenizer Tokenizer;
672         String tableName = "";
673
674         try {
675             Tokenizer = new StringTokenizer (statement);
676
677             while (!Tokenizer.nextToken().toUpperCase().equals("FROM")) {
678                 ;
679             }
680
681             tableName = Tokenizer.nextToken(" ;");
682         } catch (NoSuchElementException NSE) {
683             throw new DataAccessPointException(
684                 "Table name not found in statement: " + statement);
685         }
686
687         if (WTextRead != null) {
688             try {
689                 WTextRead.close();
690
691                 WTextRead = null;
692             } catch (IOException e) {}
693         }
694
695         return (this.parseFileForData(tableName));
696     }
697
698     TransferResultSet parseFileForData(String tableName)
699     throws DataAccessPointException {
700
701         TransferResultSet trsData = new TransferResultSet();
702         StringTokenizer Tokenizer;
703
704         if (WTextRead == null) {
705             try {
706                 WTextRead = new BufferedReader (new FileReader (sFileName));
707             } catch (IOException e) {
708                 throw new DataAccessPointException(e.getMessage());
709             }
710         }
711
712         String currentLine = "";
713         String Token;
714
715         try {
716             while ((currentLine = WTextRead.readLine()) != null)