1   /**
2    * Copyright (c) 2000-2008 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.dao.jdbc.DataAccess;
26  import com.liferay.portal.kernel.util.FileUtil;
27  import com.liferay.portal.kernel.util.GetterUtil;
28  import com.liferay.portal.kernel.util.StringPool;
29  import com.liferay.portal.kernel.util.StringUtil;
30  import com.liferay.portal.util.PropsValues;
31  import com.liferay.portal.velocity.VelocityUtil;
32  import com.liferay.util.SimpleCounter;
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 javax.naming.NamingException;
49  
50  import org.apache.commons.logging.Log;
51  import org.apache.commons.logging.LogFactory;
52  
53  import org.hibernate.dialect.DB2Dialect;
54  import org.hibernate.dialect.DerbyDialect;
55  import org.hibernate.dialect.Dialect;
56  import org.hibernate.dialect.FirebirdDialect;
57  import org.hibernate.dialect.HSQLDialect;
58  import org.hibernate.dialect.InformixDialect;
59  import org.hibernate.dialect.InterbaseDialect;
60  import org.hibernate.dialect.JDataStoreDialect;
61  import org.hibernate.dialect.MySQLDialect;
62  import org.hibernate.dialect.Oracle10gDialect;
63  import org.hibernate.dialect.Oracle8iDialect;
64  import org.hibernate.dialect.Oracle9Dialect;
65  import org.hibernate.dialect.Oracle9iDialect;
66  import org.hibernate.dialect.OracleDialect;
67  import org.hibernate.dialect.PostgreSQLDialect;
68  import org.hibernate.dialect.SAPDBDialect;
69  import org.hibernate.dialect.SQLServerDialect;
70  import org.hibernate.dialect.SybaseDialect;
71  
72  /**
73   * <a href="DBUtil.java.html"><b><i>View Source</i></b></a>
74   *
75   * @author Alexander Chow
76   *
77   */
78  public abstract class DBUtil {
79  
80      public static final String DB_TYPE_DB2 = "db2";
81  
82      public static final String DB_TYPE_DERBY = "derby";
83  
84      public static final String DB_TYPE_FIREBIRD = "firebird";
85  
86      public static final String DB_TYPE_HYPERSONIC = "hypersonic";
87  
88      public static final String DB_TYPE_INFORMIX = "informix";
89  
90      public static final String DB_TYPE_INTERBASE = "interbase";
91  
92      public static final String DB_TYPE_JDATASTORE = "jdatastore";
93  
94      public static final String DB_TYPE_MYSQL = "mysql";
95  
96      public static final String DB_TYPE_ORACLE = "oracle";
97  
98      public static final String DB_TYPE_POSTGRESQL = "postgresql";
99  
100     public static final String DB_TYPE_SAP = "sap";
101 
102     public static final String DB_TYPE_SQLSERVER = "sqlserver";
103 
104     public static final String DB_TYPE_SYBASE = "sybase";
105 
106     public static final String[] DB_TYPE_ALL = {
107         DB_TYPE_DB2, DB_TYPE_DERBY, DB_TYPE_FIREBIRD, DB_TYPE_HYPERSONIC,
108         DB_TYPE_INFORMIX, DB_TYPE_INTERBASE, DB_TYPE_JDATASTORE, DB_TYPE_MYSQL,
109         DB_TYPE_ORACLE, DB_TYPE_POSTGRESQL, DB_TYPE_SAP, DB_TYPE_SQLSERVER,
110         DB_TYPE_SYBASE
111     };
112 
113     public static DBUtil getInstance() {
114         if (_dbUtil == null) {
115             try {
116                 if (_log.isInfoEnabled()) {
117                     _log.info("Using dialect " + PropsValues.HIBERNATE_DIALECT);
118                 }
119 
120                 Dialect dialect = (Dialect)Class.forName(
121                     PropsValues.HIBERNATE_DIALECT).newInstance();
122 
123                 setInstance(dialect);
124             }
125             catch (Exception e) {
126                 _log.error(e, e);
127             }
128         }
129 
130         return _dbUtil;
131     }
132 
133     public static DBUtil getInstance(String dbType) {
134         DBUtil dbUtil = null;
135 
136         if (dbType.equals(DB_TYPE_DB2)) {
137             dbUtil = DB2Util.getInstance();
138         }
139         else if (dbType.equals(DB_TYPE_DERBY)) {
140             dbUtil = DerbyUtil.getInstance();
141         }
142         else if (dbType.equals(DB_TYPE_FIREBIRD)) {
143             dbUtil = FirebirdUtil.getInstance();
144         }
145         else if (dbType.equals(DB_TYPE_HYPERSONIC)) {
146             dbUtil = HypersonicUtil.getInstance();
147         }
148         else if (dbType.equals(DB_TYPE_INFORMIX)) {
149             dbUtil = InformixUtil.getInstance();
150         }
151         else if (dbType.equals(DB_TYPE_INTERBASE)) {
152             dbUtil = InterBaseUtil.getInstance();
153         }
154         else if (dbType.equals(DB_TYPE_JDATASTORE)) {
155             dbUtil = JDataStoreUtil.getInstance();
156         }
157         else if (dbType.equals(DB_TYPE_MYSQL)) {
158             dbUtil = MySQLUtil.getInstance();
159         }
160         else if (dbType.equals(DB_TYPE_ORACLE)) {
161             dbUtil = OracleUtil.getInstance();
162         }
163         else if (dbType.equals(DB_TYPE_POSTGRESQL)) {
164             dbUtil = PostgreSQLUtil.getInstance();
165         }
166         else if (dbType.equals(DB_TYPE_SAP)) {
167             dbUtil = SAPUtil.getInstance();
168         }
169         else if (dbType.equals(DB_TYPE_SQLSERVER)) {
170             dbUtil = SQLServerUtil.getInstance();
171         }
172         else if (dbType.equals(DB_TYPE_SYBASE)) {
173             dbUtil = SybaseUtil.getInstance();
174         }
175 
176         return dbUtil;
177     }
178 
179     public static void setInstance(Dialect dialect) {
180         if (_dbUtil != null) {
181             return;
182         }
183 
184         if (dialect instanceof DB2Dialect) {
185             if (dialect instanceof DerbyDialect) {
186                 _dbUtil = DerbyUtil.getInstance();
187             }
188             else {
189                 _dbUtil = DB2Util.getInstance();
190             }
191         }
192         else if (dialect instanceof HSQLDialect) {
193             _dbUtil = HypersonicUtil.getInstance();
194         }
195         else if (dialect instanceof InformixDialect) {
196             _dbUtil = InformixUtil.getInstance();
197         }
198         else if (dialect instanceof InterbaseDialect) {
199             if (dialect instanceof FirebirdDialect) {
200                 _dbUtil = FirebirdUtil.getInstance();
201             }
202             else {
203                 _dbUtil = InterBaseUtil.getInstance();
204             }
205         }
206         else if (dialect instanceof JDataStoreDialect) {
207             _dbUtil = JDataStoreUtil.getInstance();
208         }
209         else if (dialect instanceof MySQLDialect) {
210             _dbUtil = MySQLUtil.getInstance();
211         }
212         else if (dialect instanceof OracleDialect ||
213                  dialect instanceof Oracle8iDialect ||
214                  dialect instanceof Oracle9Dialect ||
215                  dialect instanceof Oracle9iDialect ||
216                  dialect instanceof Oracle10gDialect) {
217 
218             _dbUtil = OracleUtil.getInstance();
219         }
220         else if (dialect instanceof PostgreSQLDialect) {
221             _dbUtil = PostgreSQLUtil.getInstance();
222         }
223         else if (dialect instanceof SAPDBDialect) {
224             _dbUtil = SAPUtil.getInstance();
225         }
226         else if (dialect instanceof SybaseDialect) {
227             if (dialect instanceof SQLServerDialect) {
228                 _dbUtil = SQLServerUtil.getInstance();
229             }
230             else {
231                 _dbUtil = SybaseUtil.getInstance();
232             }
233         }
234     }
235 
236     public void buildCreateFile(String databaseName) throws IOException {
237         buildCreateFile(databaseName, true);
238         buildCreateFile(databaseName, false);
239     }
240 
241     public abstract String buildSQL(String template) throws IOException;
242 
243     public void buildSQLFile(String fileName) throws IOException {
244         String template = buildTemplate(fileName);
245 
246         template = buildSQL(template);
247 
248         FileUtil.write(
249             "../sql/" + fileName + "/" + fileName + "-" + getServerName() +
250                 ".sql",
251             template);
252     }
253 
254     public String getTemplateFalse() {
255         return getTemplate()[2];
256     }
257 
258     public String getTemplateTrue() {
259         return getTemplate()[1];
260     }
261 
262     public void runSQL(String sql)
263         throws IOException, NamingException, SQLException {
264 
265         runSQL(new String[] {sql});
266     }
267 
268     public void runSQL(String[] sqls)
269         throws IOException, NamingException, SQLException {
270 
271         Connection con = null;
272         Statement stmt = null;
273 
274         try {
275             con = DataAccess.getConnection();
276 
277             stmt = con.createStatement();
278 
279             for (int i = 0; i < sqls.length; i++) {
280                 String sql = buildSQL(sqls[i]);
281 
282                 sql = sql.trim();
283 
284                 if (sql.endsWith(";")) {
285                     sql = sql.substring(0, sql.length() - 1);
286                 }
287 
288                 if (sql.endsWith("go")) {
289                     sql = sql.substring(0, sql.length() - 2);
290                 }
291 
292                 if (_log.isDebugEnabled()) {
293                     _log.debug(sql);
294                 }
295 
296                 try {
297                     stmt.executeUpdate(sql);
298                 }
299                 catch (SQLException sqle) {
300                     throw sqle;
301                 }
302             }
303         }
304         finally {
305             DataAccess.cleanUp(con, stmt);
306         }
307     }
308 
309     public void runSQLTemplate(String path)
310         throws IOException, NamingException, SQLException {
311 
312         runSQLTemplate(path, true);
313     }
314 
315     public void runSQLTemplate(String path, boolean failOnError)
316         throws IOException, NamingException, SQLException {
317 
318         ClassLoader classLoader = getClass().getClassLoader();
319 
320         InputStream is = classLoader.getResourceAsStream(
321             "com/liferay/portal/tools/sql/dependencies/" + path);
322 
323         if (is == null) {
324             is = classLoader.getResourceAsStream(path);
325         }
326 
327         String template = StringUtil.read(is);
328 
329         is.close();
330 
331         boolean evaluate = path.endsWith(".vm");
332 
333         runSQLTemplateString(template, evaluate, failOnError);
334     }
335 
336     public void runSQLTemplateString(
337             String template, boolean evaluate, boolean failOnError)
338         throws IOException, NamingException, SQLException {
339 
340         if (evaluate) {
341             try {
342                 template = evaluateVM(template);
343             }
344             catch (Exception e) {
345                 _log.error(e, e);
346             }
347         }
348 
349         StringBuilder sb = new StringBuilder();
350 
351         BufferedReader br = new BufferedReader(new StringReader(template));
352 
353         String line = null;
354 
355         while ((line = br.readLine()) != null) {
356             if (!line.startsWith("##")) {
357                 if (line.startsWith("@include ")) {
358                     int pos = line.indexOf(" ");
359 
360                     String includeFileName = line.substring(pos + 1);
361 
362                     ClassLoader classLoader = getClass().getClassLoader();
363 
364                     InputStream is = classLoader.getResourceAsStream(
365                         "com/liferay/portal/tools/sql/dependencies/" +
366                             includeFileName);
367 
368                     if (is == null) {
369                         is = classLoader.getResourceAsStream(includeFileName);
370                     }
371 
372                     String include = StringUtil.read(is);
373 
374                     is.close();
375 
376                     if (includeFileName.endsWith(".vm")) {
377                         try {
378                             include = evaluateVM(include);
379                         }
380                         catch (Exception e) {
381                             _log.error(e, e);
382                         }
383                     }
384 
385                     include = convertTimestamp(include);
386                     include = replaceTemplate(include, getTemplate());
387 
388                     runSQLTemplateString(include, false, true);
389                 }
390                 else{
391                     sb.append(line);
392 
393                     if (line.endsWith(";")) {
394                         String sql = sb.toString();
395 
396                         sb = new StringBuilder();
397 
398                         try {
399                             if (!sql.equals("COMMIT_TRANSACTION;")) {
400                                 runSQL(sql);
401                             }
402                             else {
403                                 if (_log.isDebugEnabled()) {
404                                     _log.debug("Skip commit sql");
405                                 }
406                             }
407                         }
408                         catch (IOException ioe) {
409                             if (failOnError) {
410                                 throw ioe;
411                             }
412                             else if (_log.isWarnEnabled()) {
413                                 _log.warn(ioe.getMessage());
414                             }
415                         }
416                         catch (SQLException sqle) {
417                             if (failOnError) {
418                                 throw sqle;
419                             }
420                             else if (_log.isWarnEnabled()) {
421                                 String message = GetterUtil.getString(
422                                     sqle.getMessage());
423 
424                                 if (!message.startsWith("Duplicate key name")) {
425                                     _log.warn(sqle.getMessage());
426                                 }
427                             }
428                         }
429                     }
430                 }
431             }
432         }
433 
434         br.close();
435     }
436 
437     protected abstract void buildCreateFile(
438             String databaseName, boolean minimal)
439         throws IOException;
440 
441     protected String[] buildColumnNameTokens(String line) {
442         String[] words = StringUtil.split(line, " ");
443 
444         if (words.length == 7) {
445             words[5] = "not null;";
446         }
447 
448         String[] template = {
449             words[1], words[2], words[3], words[4], words[5]
450         };
451 
452         return template;
453     }
454 
455     protected String[] buildColumnTypeTokens(String line) {
456         String[] words = StringUtil.split(line, " ");
457 
458         String nullable = "";
459 
460         if (words.length == 6) {
461             nullable = "not null;";
462         }
463         else if (words.length == 5) {
464             nullable = words[4];
465         }
466         else if (words.length == 4) {
467             nullable = "not null;";
468 
469             if (words[3].endsWith(";")) {
470                 words[3] = words[3].substring(0, words[3].length() - 1);
471             }
472         }
473 
474         String[] template = {
475             words[1], words[2], "", words[3], nullable
476         };
477 
478         return template;
479     }
480 
481     protected String buildTemplate(String fileName) throws IOException {
482         File file = new File("../sql/" + fileName + ".sql");
483 
484         String template = FileUtil.read(file);
485 
486         if (fileName.equals("portal") || fileName.equals("portal-minimal") ||
487             fileName.equals("update-5.0.1-5.1.0")) {
488 
489             BufferedReader br = new BufferedReader(new StringReader(template));
490 
491             StringBuilder sb = new StringBuilder();
492 
493             String line = null;
494 
495             while ((line = br.readLine()) != null) {
496                 if (line.startsWith("@include ")) {
497                     int pos = line.indexOf(" ");
498 
499                     String includeFileName = line.substring(pos + 1);
500 
501                     File includeFile = new File("../sql/" + includeFileName);
502 
503                     if (!includeFile.exists()) {
504                         continue;
505                     }
506 
507                     String include = FileUtil.read(includeFile);
508 
509                     if (includeFileName.endsWith(".vm")) {
510                         try {
511                             include = evaluateVM(include);
512                         }
513                         catch (Exception e) {
514                             _log.error(e, e);
515                         }
516                     }
517 
518                     include = convertTimestamp(include);
519                     include = replaceTemplate(include, getTemplate());
520 
521                     sb.append(include);
522                     sb.append("\n\n");
523                 }
524                 else {
525                     sb.append(line);
526                     sb.append("\n");
527                 }
528             }
529 
530             br.close();
531 
532             template = sb.toString();
533         }
534 
535         if (fileName.equals("indexes") && (this instanceof SybaseUtil)) {
536             template = removeBooleanIndexes(template);
537         }
538 
539         return template;
540     }
541 
542     protected String convertTimestamp(String data) {
543         String s = null;
544 
545         if (this instanceof MySQLUtil) {
546             s = StringUtil.replace(data, "SPECIFIC_TIMESTAMP_", "");
547         }
548         else {
549             s = data.replaceAll(
550                 "SPECIFIC_TIMESTAMP_" + "\\d+", "CURRENT_TIMESTAMP");
551         }
552 
553         return s;
554     }
555 
556     protected String evaluateVM(String template) throws Exception {
557         Map<String, Object> variables = new HashMap<String, Object>();
558 
559         variables.put("counter", new SimpleCounter());
560 
561         template = VelocityUtil.evaluate(template, variables);
562 
563         // Trim insert statements because it breaks MySQL Query Browser
564 
565         BufferedReader br = new BufferedReader(new StringReader(template));
566 
567         StringBuilder sb = new StringBuilder();
568 
569         String line = null;
570 
571         while ((line = br.readLine()) != null) {
572             line = line.trim();
573 
574             sb.append(line);
575             sb.append("\n");
576         }
577 
578         br.close();
579 
580         template = sb.toString();
581         template = StringUtil.replace(template, "\n\n\n", "\n\n");
582 
583         return template;
584     }
585 
586     protected String getMinimalSuffix(boolean minimal) {
587         if (minimal) {
588             return "-minimal";
589         }
590         else {
591             return StringPool.BLANK;
592         }
593     }
594 
595     protected abstract String getServerName();
596 
597     protected abstract String[] getTemplate();
598 
599     protected String readSQL(String fileName, String comments, String eol)
600         throws IOException {
601 
602         BufferedReader br = new BufferedReader(
603             new FileReader(new File(fileName)));
604 
605         StringBuilder sb = new StringBuilder();
606 
607         String line = null;
608 
609         while ((line = br.readLine()) != null) {
610             if (!line.startsWith(comments)) {
611                 line = StringUtil.replace(
612                     line,
613                     new String[] {"\n", "\t"},
614                     new String[] {"", ""});
615 
616                 if (line.endsWith(";")) {
617                     sb.append(line.substring(0, line.length() - 1));
618                     sb.append(eol);
619                 }
620                 else {
621                     sb.append(line);
622                 }
623             }
624         }
625 
626         br.close();
627 
628         return sb.toString();
629     }
630 
631     protected String removeBooleanIndexes(String data) throws IOException {
632         String portalData = FileUtil.read("../sql/portal-tables.sql");
633 
634         BufferedReader br = new BufferedReader(new StringReader(data));
635 
636         StringBuilder sb = new StringBuilder();
637 
638         String line = null;
639 
640         while ((line = br.readLine()) != null) {
641             boolean append = true;
642 
643             int x = line.indexOf(" on ");
644 
645             if (x != -1) {
646                 int y = line.indexOf(" (", x);
647 
648                 String table = line.substring(x + 4, y);
649 
650                 x = y + 2;
651                 y = line.indexOf(")", x);
652 
653                 String[] columns = StringUtil.split(line.substring(x, y));
654 
655                 x = portalData.indexOf("create table " + table + " (");
656                 y = portalData.indexOf(");", x);
657 
658                 String portalTableData = portalData.substring(x, y);
659 
660                 for (int i = 0; i < columns.length; i++) {
661                     if (portalTableData.indexOf(
662                             columns[i].trim() + " BOOLEAN") != -1) {
663 
664                         append = false;
665 
666                         break;
667                     }
668                 }
669             }
670 
671             if (append) {
672                 sb.append(line);
673                 sb.append("\n");
674             }
675         }
676 
677         br.close();
678 
679         return sb.toString();
680     }
681 
682     protected String removeInserts(String data) throws IOException {
683         BufferedReader br = new BufferedReader(new StringReader(data));
684 
685         StringBuilder sb = new StringBuilder();
686 
687         String line = null;
688 
689         while ((line = br.readLine()) != null) {
690             if (!line.startsWith("insert into ") &&
691                 !line.startsWith("update ")) {
692 
693                 sb.append(line);
694                 sb.append("\n");
695             }
696         }
697 
698         br.close();
699 
700         return sb.toString();
701     }
702 
703     protected String removeLongInserts(String data) throws IOException {
704         BufferedReader br = new BufferedReader(new StringReader(data));
705 
706         StringBuilder sb = new StringBuilder();
707 
708         String line = null;
709 
710         while ((line = br.readLine()) != null) {
711             if (!line.startsWith("insert into Image (") &&
712                 !line.startsWith("insert into JournalArticle (") &&
713                 !line.startsWith("insert into JournalStructure (") &&
714                 !line.startsWith("insert into JournalTemplate (")) {
715 
716                 sb.append(line);
717                 sb.append("\n");
718             }
719         }
720 
721         br.close();
722 
723         return sb.toString();
724     }
725 
726     protected String removeNull(String content) {
727         content = StringUtil.replace(content, " not null", " not_null");
728         content = StringUtil.replace(content, " null", "");
729         content = StringUtil.replace(content, " not_null", " not null");
730 
731         return content;
732     }
733 
734     protected String replaceTemplate(String template, String[] actual) {
735         if ((template == null) || (TEMPLATE == null) || (actual == null)) {
736             return null;
737         }
738 
739         if (TEMPLATE.length != actual.length) {
740             return template;
741         }
742 
743         for (int i = 0; i < TEMPLATE.length; i++) {
744             if (TEMPLATE[i].equals("##") ||
745                 TEMPLATE[i].equals("'01/01/1970'")){
746 
747                 template = template.replaceAll(TEMPLATE[i], actual[i]);
748             }
749             else {
750                 template = template.replaceAll(
751                     "\\b" + TEMPLATE[i] + "\\b", actual[i]);
752             }
753         }
754 
755         return template;
756     }
757 
758     protected abstract String reword(String data) throws IOException;
759 
760     protected static String ALTER_COLUMN_TYPE = "alter_column_type ";
761 
762     protected static String ALTER_COLUMN_NAME = "alter_column_name ";
763 
764     protected static String DROP_PRIMARY_KEY = "drop primary key";
765 
766     protected static String[] REWORD_TEMPLATE = {
767         "@table@", "@old-column@", "@new-column@", "@type@", "@nullable@"
768     };
769 
770     protected static String[] TEMPLATE = {
771         "##", "TRUE", "FALSE",
772         "'01/01/1970'", "CURRENT_TIMESTAMP",
773         " BLOB", " BOOLEAN", " DATE",
774         " DOUBLE", " INTEGER", " LONG",
775         " STRING", " TEXT", " VARCHAR",
776         " IDENTITY", "COMMIT_TRANSACTION"
777     };
778 
779     private static Log _log = LogFactory.getLog(DBUtil.class);
780 
781     private static DBUtil _dbUtil;
782 
783 }