1   /**
2    * Copyright (c) 2000-2007 Liferay, Inc. All rights reserved.
3    *
4    * Permission is hereby granted, free of charge, to any person obtaining a copy
5    * of this software and associated documentation files (the "Software"), to deal
6    * in the Software without restriction, including without limitation the rights
7    * to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
8    * copies of the Software, and to permit persons to whom the Software is
9    * furnished to do so, subject to the following conditions:
10   *
11   * The above copyright notice and this permission notice shall be included in
12   * all copies or substantial portions of the Software.
13   *
14   * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
15   * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
16   * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
17   * AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
18   * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
19   * OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
20   * SOFTWARE.
21   */
22  
23  package com.liferay.portal.tools.sql;
24  
25  import com.liferay.portal.kernel.util.StringMaker;
26  import com.liferay.portal.kernel.util.StringPool;
27  import com.liferay.portal.kernel.util.StringUtil;
28  import com.liferay.portal.spring.hibernate.HibernateUtil;
29  import com.liferay.portal.velocity.VelocityUtil;
30  import com.liferay.util.FileUtil;
31  import com.liferay.util.SimpleCounter;
32  import com.liferay.util.dao.DataAccess;
33  
34  import java.io.BufferedReader;
35  import java.io.File;
36  import java.io.FileReader;
37  import java.io.IOException;
38  import java.io.InputStream;
39  import java.io.StringReader;
40  
41  import java.sql.Connection;
42  import java.sql.SQLException;
43  import java.sql.Statement;
44  
45  import java.util.HashMap;
46  import java.util.Map;
47  
48  import org.apache.commons.logging.Log;
49  import org.apache.commons.logging.LogFactory;
50  
51  import org.hibernate.dialect.DB2Dialect;
52  import org.hibernate.dialect.DerbyDialect;
53  import org.hibernate.dialect.Dialect;
54  import org.hibernate.dialect.FirebirdDialect;
55  import org.hibernate.dialect.HSQLDialect;
56  import org.hibernate.dialect.InformixDialect;
57  import org.hibernate.dialect.InterbaseDialect;
58  import org.hibernate.dialect.JDataStoreDialect;
59  import org.hibernate.dialect.MySQLDialect;
60  import org.hibernate.dialect.Oracle10gDialect;
61  import org.hibernate.dialect.Oracle8iDialect;
62  import org.hibernate.dialect.Oracle9Dialect;
63  import org.hibernate.dialect.Oracle9iDialect;
64  import org.hibernate.dialect.OracleDialect;
65  import org.hibernate.dialect.PostgreSQLDialect;
66  import org.hibernate.dialect.SAPDBDialect;
67  import org.hibernate.dialect.SQLServerDialect;
68  import org.hibernate.dialect.SybaseDialect;
69  
70  /**
71   * <a href="DBUtil.java.html"><b><i>View Source</i></b></a>
72   *
73   * @author Alexander Chow
74   *
75   */
76  public abstract class DBUtil {
77  
78      public static final int DB_TYPE_DB2 = 1;
79  
80      public static final int DB_TYPE_DERBY = 2;
81  
82      public static final int DB_TYPE_FIREBIRD = 3;
83  
84      public static final int DB_TYPE_HYPERSONIC = 4;
85  
86      public static final int DB_TYPE_INFORMIX = 5;
87  
88      public static final int DB_TYPE_INTERBASE = 6;
89  
90      public static final int DB_TYPE_JDATASTORE = 7;
91  
92      public static final int DB_TYPE_MYSQL = 8;
93  
94      public static final int DB_TYPE_ORACLE = 9;
95  
96      public static final int DB_TYPE_POSTGRESQL = 10;
97  
98      public static final int DB_TYPE_SAP = 11;
99  
100     public static final int DB_TYPE_SQLSERVER = 12;
101 
102     public static final int DB_TYPE_SYBASE = 13;
103 
104     public static DBUtil getInstance() {
105         if (_dbUtil != null) {
106             return _dbUtil;
107         }
108 
109         Dialect dialect = HibernateUtil.getWrappedDialect();
110 
111         if (dialect instanceof DB2Dialect) {
112             if (dialect instanceof DerbyDialect) {
113                 _dbUtil = DerbyUtil.getInstance();
114             }
115             else {
116                 _dbUtil = DB2Util.getInstance();
117             }
118         }
119         else if (dialect instanceof HSQLDialect) {
120             _dbUtil = HypersonicUtil.getInstance();
121         }
122         else if (dialect instanceof InformixDialect) {
123             _dbUtil = InformixUtil.getInstance();
124         }
125         else if (dialect instanceof InterbaseDialect) {
126             if (dialect instanceof FirebirdDialect) {
127                 _dbUtil = FirebirdUtil.getInstance();
128             }
129             else {
130                 _dbUtil = InterBaseUtil.getInstance();
131             }
132         }
133         else if (dialect instanceof JDataStoreDialect) {
134             _dbUtil = JDataStoreUtil.getInstance();
135         }
136         else if (dialect instanceof MySQLDialect) {
137             _dbUtil = MySQLUtil.getInstance();
138         }
139         else if (dialect instanceof OracleDialect ||
140                  dialect instanceof Oracle8iDialect ||
141                  dialect instanceof Oracle9Dialect ||
142                  dialect instanceof Oracle9iDialect ||
143                  dialect instanceof Oracle10gDialect) {
144 
145             _dbUtil = OracleUtil.getInstance();
146         }
147         else if (dialect instanceof PostgreSQLDialect) {
148             _dbUtil = PostgreSQLUtil.getInstance();
149         }
150         else if (dialect instanceof SAPDBDialect) {
151             _dbUtil = SAPUtil.getInstance();
152         }
153         else if (dialect instanceof SybaseDialect) {
154             if (dialect instanceof SQLServerDialect) {
155                 _dbUtil = SQLServerUtil.getInstance();
156             }
157             else {
158                 _dbUtil = SybaseUtil.getInstance();
159             }
160         }
161 
162         return _dbUtil;
163     }
164 
165     public static DBUtil getInstance(int dbType) {
166         DBUtil dbUtil = null;
167 
168         if (dbType == DB_TYPE_DB2) {
169             dbUtil = DB2Util.getInstance();
170         }
171         else if (dbType == DB_TYPE_DERBY) {
172             dbUtil = DerbyUtil.getInstance();
173         }
174         else if (dbType == DB_TYPE_FIREBIRD) {
175             dbUtil = FirebirdUtil.getInstance();
176         }
177         else if (dbType == DB_TYPE_HYPERSONIC) {
178             dbUtil = HypersonicUtil.getInstance();
179         }
180         else if (dbType == DB_TYPE_INFORMIX) {
181             dbUtil = InformixUtil.getInstance();
182         }
183         else if (dbType == DB_TYPE_INTERBASE) {
184             dbUtil = InterBaseUtil.getInstance();
185         }
186         else if (dbType == DB_TYPE_JDATASTORE) {
187             dbUtil = JDataStoreUtil.getInstance();
188         }
189         else if (dbType == DB_TYPE_MYSQL) {
190             dbUtil = MySQLUtil.getInstance();
191         }
192         else if (dbType == DB_TYPE_ORACLE) {
193             dbUtil = OracleUtil.getInstance();
194         }
195         else if (dbType == DB_TYPE_POSTGRESQL) {
196             dbUtil = PostgreSQLUtil.getInstance();
197         }
198         else if (dbType == DB_TYPE_SAP) {
199             dbUtil = SAPUtil.getInstance();
200         }
201         else if (dbType == DB_TYPE_SQLSERVER) {
202             dbUtil = SQLServerUtil.getInstance();
203         }
204         else if (dbType == DB_TYPE_SYBASE) {
205             dbUtil = SybaseUtil.getInstance();
206         }
207 
208         return dbUtil;
209     }
210 
211     public void buildCreateFile(String databaseName) throws IOException {
212         buildCreateFile(databaseName, true);
213         buildCreateFile(databaseName, false);
214     }
215 
216     public abstract String buildSQL(String template) throws IOException;
217 
218     public void buildSQLFile(String fileName) throws IOException {
219         String template = buildTemplate(fileName);
220 
221         template = buildSQL(template);
222 
223         FileUtil.write(
224             "../sql/" + fileName + "/" + fileName + "-" + getServerName() +
225                 ".sql",
226             template);
227     }
228 
229     public void runSQL(String sql) throws IOException, SQLException {
230         runSQL(new String[] {sql});
231     }
232 
233     public void runSQL(String[] sqls)
234         throws IOException, SQLException {
235 
236         Connection con = null;
237         Statement stmt = null;
238 
239         try {
240             con = HibernateUtil.getConnection();
241 
242             stmt = con.createStatement();
243 
244             for (int i = 0; i < sqls.length; i++) {
245                 String sql = buildSQL(sqls[i]);
246 
247                 sql = sql.trim();
248 
249                 if (sql.endsWith(";")) {
250                     sql = sql.substring(0, sql.length() - 1);
251                 }
252 
253                 if (sql.endsWith("go")) {
254                     sql = sql.substring(0, sql.length() - 2);
255                 }
256 
257                 if (_log.isDebugEnabled()) {
258                     _log.debug(sql);
259                 }
260 
261                 try {
262                     stmt.executeUpdate(sql);
263                 }
264                 catch (SQLException sqle) {
265                     throw sqle;
266                 }
267             }
268         }
269         finally {
270             DataAccess.cleanUp(con, stmt);
271         }
272     }
273 
274     public void runSQLTemplate(String path) throws IOException, SQLException {
275         runSQLTemplate(path, true);
276     }
277 
278     public void runSQLTemplate(String path, boolean failOnError)
279         throws IOException, SQLException {
280 
281         ClassLoader classLoader = getClass().getClassLoader();
282 
283         InputStream is = classLoader.getResourceAsStream(
284             "com/liferay/portal/tools/sql/dependencies/" + path);
285 
286         if (is == null) {
287             is = classLoader.getResourceAsStream(path);
288         }
289 
290         String template = StringUtil.read(is);
291 
292         is.close();
293 
294         boolean evaluate = path.endsWith(".vm");
295 
296         runSQLTemplateString(template, evaluate, failOnError);
297     }
298 
299     public void runSQLTemplateString(
300             String template, boolean evaluate, boolean failOnError)
301         throws IOException, SQLException {
302 
303         if (evaluate) {
304             try {
305                 template = evaluateVM(template);
306             }
307             catch (Exception e) {
308                 _log.error(e, e);
309             }
310         }
311 
312         StringMaker sm = new StringMaker();
313 
314         BufferedReader br = new BufferedReader(new StringReader(template));
315 
316         String line = null;
317 
318         while ((line = br.readLine()) != null) {
319             if (!line.startsWith("##")) {
320                 sm.append(line);
321 
322                 if (line.endsWith(";")) {
323                     String sql = sm.toString();
324 
325                     sm = new StringMaker();
326 
327                     try {
328                         if (!sql.equals("COMMIT_TRANSACTION;")) {
329                             runSQL(sql);
330                         }
331                         else {
332                             if (_log.isDebugEnabled()) {
333                                 _log.debug("Skip commit sql");
334                             }
335                         }
336                     }
337                     catch (IOException ioe) {
338                         if (failOnError) {
339                             throw ioe;
340                         }
341                         else if (_log.isWarnEnabled()) {
342                             _log.warn(ioe.getMessage());
343                         }
344                     }
345                     catch (SQLException sqle) {
346                         if (failOnError) {
347                             throw sqle;
348                         }
349                         else if (_log.isWarnEnabled()) {
350                             _log.warn(sqle.getMessage());
351                         }
352                     }
353                 }
354             }
355         }
356 
357         br.close();
358     }
359 
360     protected abstract void buildCreateFile(
361             String databaseName, boolean minimal)
362         throws IOException;
363 
364     protected String[] buildColumnNameTokens(String line) {
365         String[] words = StringUtil.split(line, " ");
366 
367         if (words.length == 7) {
368             words[5] = "not null;";
369         }
370 
371         String[] template = {
372             words[1], words[2], words[3], words[4], words[5]
373         };
374 
375         return template;
376     }
377 
378     protected String[] buildColumnTypeTokens(String line) {
379         String[] words = StringUtil.split(line, " ");
380 
381         String nullable = "";
382 
383         if (words.length == 6) {
384             nullable = "not null;";
385         }
386         else if (words.length == 5) {
387             nullable = words[4];
388         }
389         else if (words.length == 4) {
390             nullable = "not null;";
391 
392             if (words[3].endsWith(";")) {
393                 words[3] = words[3].substring(0, words[3].length() - 1);
394             }
395         }
396 
397         String[] template = {
398             words[1], words[2], "", words[3], nullable
399         };
400 
401         return template;
402     }
403 
404     protected String buildTemplate(String fileName) throws IOException {
405         File file = new File("../sql/" + fileName + ".sql");
406 
407         String template = FileUtil.read(file);
408 
409         if (fileName.equals("portal") || fileName.equals("portal-minimal") ||
410             fileName.equals("update-3.6.0-4.0.0")) {
411 
412             BufferedReader br = new BufferedReader(new StringReader(template));
413 
414             StringMaker sm = new StringMaker();
415 
416             String line = null;
417 
418             while ((line = br.readLine()) != null) {
419                 if (line.startsWith("@include ")) {
420                     int pos = line.indexOf(" ");
421 
422                     String includeFileName =
423                         line.substring(pos + 1, line.length());
424 
425                     File includeFile = new File("../sql/" + includeFileName);
426 
427                     if (!includeFile.exists()) {
428                         continue;
429                     }
430 
431                     String include = FileUtil.read(includeFile);
432 
433                     if (includeFileName.endsWith(".vm")) {
434                         try {
435                             include = evaluateVM(include);
436                         }
437                         catch (Exception e) {
438                             e.printStackTrace();
439                         }
440                     }
441 
442                     include = convertTimestamp(include);
443                     include =
444                         StringUtil.replace(include, TEMPLATE, getTemplate());
445 
446                     sm.append(include);
447                     sm.append("\n\n");
448                 }
449                 else {
450                     sm.append(line);
451                     sm.append("\n");
452                 }
453             }
454 
455             br.close();
456 
457             template = sm.toString();
458         }
459 
460         if (fileName.equals("indexes") && (this instanceof SybaseUtil)) {
461             template = removeBooleanIndexes(template);
462         }
463 
464         return template;
465     }
466 
467     protected String convertTimestamp(String data) {
468         String s = null;
469 
470         if (this instanceof MySQLUtil) {
471             s = StringUtil.replace(data, "SPECIFIC_TIMESTAMP_", "");
472         }
473         else {
474             s = data.replaceAll(
475                 "SPECIFIC_TIMESTAMP_" + "\\d+", "CURRENT_TIMESTAMP");
476         }
477 
478         return s;
479     }
480 
481     protected String evaluateVM(String template) throws Exception {
482         Map variables = new HashMap();
483 
484         variables.put("counter", new SimpleCounter());
485 
486         template = VelocityUtil.evaluate(template, variables);
487 
488         // Trim insert statements because it breaks MySQL Query Browser
489 
490         BufferedReader br = new BufferedReader(new StringReader(template));
491 
492         StringMaker sm = new StringMaker();
493 
494         String line = null;
495 
496         while ((line = br.readLine()) != null) {
497             line = line.trim();
498 
499             sm.append(line);
500             sm.append("\n");
501         }
502 
503         br.close();
504 
505         template = sm.toString();
506         template = StringUtil.replace(template, "\n\n\n", "\n\n");
507 
508         return template;
509     }
510 
511     protected String getMinimalSuffix(boolean minimal) {
512         if (minimal) {
513             return "-minimal";
514         }
515         else {
516             return StringPool.BLANK;
517         }
518     }
519 
520     protected abstract String getServerName();
521 
522     protected abstract String[] getTemplate();
523 
524     protected String readSQL(String fileName, String comments, String eol)
525         throws IOException {
526 
527         BufferedReader br = new BufferedReader(
528             new FileReader(new File(fileName)));
529 
530         StringMaker sm = new StringMaker();
531 
532         String line = null;
533 
534         while ((line = br.readLine()) != null) {
535             if (!line.startsWith(comments)) {
536                 line = StringUtil.replace(
537                     line,
538                     new String[] {"\n", "\t"},
539                     new String[] {"", ""});
540 
541                 if (line.endsWith(";")) {
542                     sm.append(line.substring(0, line.length() - 1));
543                     sm.append(eol);
544                 }
545                 else {
546                     sm.append(line);
547                 }
548             }
549         }
550 
551         br.close();
552 
553         return sm.toString();
554     }
555 
556     protected String removeBooleanIndexes(String data) throws IOException {
557         String portalData = FileUtil.read("../sql/portal-tables.sql");
558 
559         BufferedReader br = new BufferedReader(new StringReader(data));
560 
561         StringMaker sm = new StringMaker();
562 
563         String line = null;
564 
565         while ((line = br.readLine()) != null) {
566             boolean append = true;
567 
568             int x = line.indexOf(" on ");
569 
570             if (x != -1) {
571                 int y = line.indexOf(" (", x);
572 
573                 String table = line.substring(x + 4, y);
574 
575                 x = y + 2;
576                 y = line.indexOf(")", x);
577 
578                 String[] columns = StringUtil.split(line.substring(x, y));
579 
580                 x = portalData.indexOf("create table " + table + " (");
581                 y = portalData.indexOf(");", x);
582 
583                 String portalTableData = portalData.substring(x, y);
584 
585                 for (int i = 0; i < columns.length; i++) {
586                     if (portalTableData.indexOf(
587                             columns[i].trim() + " BOOLEAN") != -1) {
588 
589                         append = false;
590 
591                         break;
592                     }
593                 }
594             }
595 
596             if (append) {
597                 sm.append(line);
598                 sm.append("\n");
599             }
600         }
601 
602         br.close();
603 
604         return sm.toString();
605     }
606 
607     protected String removeInserts(String data) throws IOException {
608         BufferedReader br = new BufferedReader(new StringReader(data));
609 
610         StringMaker sm = new StringMaker();
611 
612         String line = null;
613 
614         while ((line = br.readLine()) != null) {
615             if (!line.startsWith("insert into ") &&
616                 !line.startsWith("update ")) {
617 
618                 sm.append(line);
619                 sm.append("\n");
620             }
621         }
622 
623         br.close();
624 
625         return sm.toString();
626     }
627 
628     protected String removeLongInserts(String data) throws IOException {
629         BufferedReader br = new BufferedReader(new StringReader(data));
630 
631         StringMaker sm = new StringMaker();
632 
633         String line = null;
634 
635         while ((line = br.readLine()) != null) {
636             if (!line.startsWith("insert into Image (") &&
637                 !line.startsWith("insert into JournalArticle (") &&
638                 !line.startsWith("insert into JournalStructure (") &&
639                 !line.startsWith("insert into JournalTemplate (")) {
640 
641                 sm.append(line);
642                 sm.append("\n");
643             }
644         }
645 
646         br.close();
647 
648         return sm.toString();
649     }
650 
651     protected String removeNull(String content) {
652         content = StringUtil.replace(content, " not null", " not_null");
653         content = StringUtil.replace(content, " null", "");
654         content = StringUtil.replace(content, " not_null", " not null");
655 
656         return content;
657     }
658 
659     protected abstract String reword(String data) throws IOException;
660 
661     protected static String ALTER_COLUMN_TYPE = "alter_column_type ";
662 
663     protected static String ALTER_COLUMN_NAME = "alter_column_name ";
664 
665     protected static String DROP_PRIMARY_KEY = "drop primary key";
666 
667     protected static String[] REWORD_TEMPLATE = {
668         "@table@", "@old-column@", "@new-column@", "@type@", "@nullable@"
669     };
670 
671     protected static String[] TEMPLATE = {
672         "##", "TRUE", "FALSE",
673         "'01/01/1970'", "CURRENT_TIMESTAMP",
674         " BOOLEAN", " DATE", " DOUBLE",
675         " INTEGER", " LONG",
676         " STRING", " TEXT", " VARCHAR",
677         " IDENTITY", "COMMIT_TRANSACTION"
678     };
679 
680     private static Log _log = LogFactory.getLog(DBUtil.class);
681 
682     private static DBUtil _dbUtil;
683 
684 }