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 String getType() {
263         return _type;
264     }
265 
266     public void runSQL(String sql)
267         throws IOException, NamingException, SQLException {
268 
269         runSQL(new String[] {sql});
270     }
271 
272     public void runSQL(String[] sqls)
273         throws IOException, NamingException, SQLException {
274 
275         Connection con = null;
276         Statement stmt = null;
277 
278         try {
279             con = DataAccess.getConnection();
280 
281             stmt = con.createStatement();
282 
283             for (int i = 0; i < sqls.length; i++) {
284                 String sql = buildSQL(sqls[i]);
285 
286                 sql = sql.trim();
287 
288                 if (sql.endsWith(";")) {
289                     sql = sql.substring(0, sql.length() - 1);
290                 }
291 
292                 if (sql.endsWith("go")) {
293                     sql = sql.substring(0, sql.length() - 2);
294                 }
295 
296                 if (_log.isDebugEnabled()) {
297                     _log.debug(sql);
298                 }
299 
300                 try {
301                     stmt.executeUpdate(sql);
302                 }
303                 catch (SQLException sqle) {
304                     throw sqle;
305                 }
306             }
307         }
308         finally {
309             DataAccess.cleanUp(con, stmt);
310         }
311     }
312 
313     public void runSQLTemplate(String path)
314         throws IOException, NamingException, SQLException {
315 
316         runSQLTemplate(path, true);
317     }
318 
319     public void runSQLTemplate(String path, boolean failOnError)
320         throws IOException, NamingException, SQLException {
321 
322         Thread currentThread = Thread.currentThread();
323 
324         ClassLoader classLoader = currentThread.getContextClassLoader();
325 
326         InputStream is = classLoader.getResourceAsStream(
327             "com/liferay/portal/tools/sql/dependencies/" + path);
328 
329         if (is == null) {
330             is = classLoader.getResourceAsStream(path);
331         }
332 
333         String template = StringUtil.read(is);
334 
335         is.close();
336 
337         boolean evaluate = path.endsWith(".vm");
338 
339         runSQLTemplateString(template, evaluate, failOnError);
340     }
341 
342     public void runSQLTemplateString(
343             String template, boolean evaluate, boolean failOnError)
344         throws IOException, NamingException, SQLException {
345 
346         if (evaluate) {
347             try {
348                 template = evaluateVM(template);
349             }
350             catch (Exception e) {
351                 _log.error(e, e);
352             }
353         }
354 
355         StringBuilder sb = new StringBuilder();
356 
357         BufferedReader br = new BufferedReader(new StringReader(template));
358 
359         String line = null;
360 
361         while ((line = br.readLine()) != null) {
362             if (!line.startsWith("##")) {
363                 if (line.startsWith("@include ")) {
364                     int pos = line.indexOf(" ");
365 
366                     String includeFileName = line.substring(pos + 1);
367 
368                     Thread currentThread = Thread.currentThread();
369 
370                     ClassLoader classLoader =
371                         currentThread.getContextClassLoader();
372 
373                     InputStream is = classLoader.getResourceAsStream(
374                         "com/liferay/portal/tools/sql/dependencies/" +
375                             includeFileName);
376 
377                     if (is == null) {
378                         is = classLoader.getResourceAsStream(includeFileName);
379                     }
380 
381                     String include = StringUtil.read(is);
382 
383                     is.close();
384 
385                     if (includeFileName.endsWith(".vm")) {
386                         try {
387                             include = evaluateVM(include);
388                         }
389                         catch (Exception e) {
390                             _log.error(e, e);
391                         }
392                     }
393 
394                     include = convertTimestamp(include);
395                     include = replaceTemplate(include, getTemplate());
396 
397                     runSQLTemplateString(include, false, true);
398                 }
399                 else{
400                     sb.append(line);
401 
402                     if (line.endsWith(";")) {
403                         String sql = sb.toString();
404 
405                         sb = new StringBuilder();
406 
407                         try {
408                             if (!sql.equals("COMMIT_TRANSACTION;")) {
409                                 runSQL(sql);
410                             }
411                             else {
412                                 if (_log.isDebugEnabled()) {
413                                     _log.debug("Skip commit sql");
414                                 }
415                             }
416                         }
417                         catch (IOException ioe) {
418                             if (failOnError) {
419                                 throw ioe;
420                             }
421                             else if (_log.isWarnEnabled()) {
422                                 _log.warn(ioe.getMessage());
423                             }
424                         }
425                         catch (SQLException sqle) {
426                             if (failOnError) {
427                                 throw sqle;
428                             }
429                             else if (_log.isWarnEnabled()) {
430                                 String message = GetterUtil.getString(
431                                     sqle.getMessage());
432 
433                                 if (!message.startsWith("Duplicate key name")) {
434                                     _log.warn(sqle.getMessage());
435                                 }
436                             }
437                         }
438                     }
439                 }
440             }
441         }
442 
443         br.close();
444     }
445 
446     protected DBUtil(String type) {
447         _type = type;
448     }
449 
450     protected abstract void buildCreateFile(
451             String databaseName, boolean minimal)
452         throws IOException;
453 
454     protected String[] buildColumnNameTokens(String line) {
455         String[] words = StringUtil.split(line, " ");
456 
457         if (words.length == 7) {
458             words[5] = "not null;";
459         }
460 
461         String[] template = {
462             words[1], words[2], words[3], words[4], words[5]
463         };
464 
465         return template;
466     }
467 
468     protected String[] buildColumnTypeTokens(String line) {
469         String[] words = StringUtil.split(line, " ");
470 
471         String nullable = "";
472 
473         if (words.length == 6) {
474             nullable = "not null;";
475         }
476         else if (words.length == 5) {
477             nullable = words[4];
478         }
479         else if (words.length == 4) {
480             nullable = "not null;";
481 
482             if (words[3].endsWith(";")) {
483                 words[3] = words[3].substring(0, words[3].length() - 1);
484             }
485         }
486 
487         String[] template = {
488             words[1], words[2], "", words[3], nullable
489         };
490 
491         return template;
492     }
493 
494     protected String buildTemplate(String fileName) throws IOException {
495         File file = new File("../sql/" + fileName + ".sql");
496 
497         String template = FileUtil.read(file);
498 
499         if (fileName.equals("portal") || fileName.equals("portal-minimal") ||
500             fileName.equals("update-5.0.1-5.1.0")) {
501 
502             BufferedReader br = new BufferedReader(new StringReader(template));
503 
504             StringBuilder sb = new StringBuilder();
505 
506             String line = null;
507 
508             while ((line = br.readLine()) != null) {
509                 if (line.startsWith("@include ")) {
510                     int pos = line.indexOf(" ");
511 
512                     String includeFileName = line.substring(pos + 1);
513 
514                     File includeFile = new File("../sql/" + includeFileName);
515 
516                     if (!includeFile.exists()) {
517                         continue;
518                     }
519 
520                     String include = FileUtil.read(includeFile);
521 
522                     if (includeFileName.endsWith(".vm")) {
523                         try {
524                             include = evaluateVM(include);
525                         }
526                         catch (Exception e) {
527                             _log.error(e, e);
528                         }
529                     }
530 
531                     include = convertTimestamp(include);
532                     include = replaceTemplate(include, getTemplate());
533 
534                     sb.append(include);
535                     sb.append("\n\n");
536                 }
537                 else {
538                     sb.append(line);
539                     sb.append("\n");
540                 }
541             }
542 
543             br.close();
544 
545             template = sb.toString();
546         }
547 
548         if (fileName.equals("indexes") && (this instanceof SybaseUtil)) {
549             template = removeBooleanIndexes(template);
550         }
551 
552         return template;
553     }
554 
555     protected String convertTimestamp(String data) {
556         String s = null;
557 
558         if (this instanceof MySQLUtil) {
559             s = StringUtil.replace(data, "SPECIFIC_TIMESTAMP_", "");
560         }
561         else {
562             s = data.replaceAll(
563                 "SPECIFIC_TIMESTAMP_" + "\\d+", "CURRENT_TIMESTAMP");
564         }
565 
566         return s;
567     }
568 
569     protected String evaluateVM(String template) throws Exception {
570         Map<String, Object> variables = new HashMap<String, Object>();
571 
572         variables.put("counter", new SimpleCounter());
573 
574         template = VelocityUtil.evaluate(template, variables);
575 
576         // Trim insert statements because it breaks MySQL Query Browser
577 
578         BufferedReader br = new BufferedReader(new StringReader(template));
579 
580         StringBuilder sb = new StringBuilder();
581 
582         String line = null;
583 
584         while ((line = br.readLine()) != null) {
585             line = line.trim();
586 
587             sb.append(line);
588             sb.append("\n");
589         }
590 
591         br.close();
592 
593         template = sb.toString();
594         template = StringUtil.replace(template, "\n\n\n", "\n\n");
595 
596         return template;
597     }
598 
599     protected String getMinimalSuffix(boolean minimal) {
600         if (minimal) {
601             return "-minimal";
602         }
603         else {
604             return StringPool.BLANK;
605         }
606     }
607 
608     protected abstract String getServerName();
609 
610     protected abstract String[] getTemplate();
611 
612     protected String readSQL(String fileName, String comments, String eol)
613         throws IOException {
614 
615         BufferedReader br = new BufferedReader(
616             new FileReader(new File(fileName)));
617 
618         StringBuilder sb = new StringBuilder();
619 
620         String line = null;
621 
622         while ((line = br.readLine()) != null) {
623             if (!line.startsWith(comments)) {
624                 line = StringUtil.replace(
625                     line,
626                     new String[] {"\n", "\t"},
627                     new String[] {"", ""});
628 
629                 if (line.endsWith(";")) {
630                     sb.append(line.substring(0, line.length() - 1));
631                     sb.append(eol);
632                 }
633                 else {
634                     sb.append(line);
635                 }
636             }
637         }
638 
639         br.close();
640 
641         return sb.toString();
642     }
643 
644     protected String removeBooleanIndexes(String data) throws IOException {
645         String portalData = FileUtil.read("../sql/portal-tables.sql");
646 
647         BufferedReader br = new BufferedReader(new StringReader(data));
648 
649         StringBuilder sb = new StringBuilder();
650 
651         String line = null;
652 
653         while ((line = br.readLine()) != null) {
654             boolean append = true;
655 
656             int x = line.indexOf(" on ");
657 
658             if (x != -1) {
659                 int y = line.indexOf(" (", x);
660 
661                 String table = line.substring(x + 4, y);
662 
663                 x = y + 2;
664                 y = line.indexOf(")", x);
665 
666                 String[] columns = StringUtil.split(line.substring(x, y));
667 
668                 x = portalData.indexOf("create table " + table + " (");
669                 y = portalData.indexOf(");", x);
670 
671                 String portalTableData = portalData.substring(x, y);
672 
673                 for (int i = 0; i < columns.length; i++) {
674                     if (portalTableData.indexOf(
675                             columns[i].trim() + " BOOLEAN") != -1) {
676 
677                         append = false;
678 
679                         break;
680                     }
681                 }
682             }
683 
684             if (append) {
685                 sb.append(line);
686                 sb.append("\n");
687             }
688         }
689 
690         br.close();
691 
692         return sb.toString();
693     }
694 
695     protected String removeInserts(String data) throws IOException {
696         BufferedReader br = new BufferedReader(new StringReader(data));
697 
698         StringBuilder sb = new StringBuilder();
699 
700         String line = null;
701 
702         while ((line = br.readLine()) != null) {
703             if (!line.startsWith("insert into ") &&
704                 !line.startsWith("update ")) {
705 
706                 sb.append(line);
707                 sb.append("\n");
708             }
709         }
710 
711         br.close();
712 
713         return sb.toString();
714     }
715 
716     protected String removeLongInserts(String data) throws IOException {
717         BufferedReader br = new BufferedReader(new StringReader(data));
718 
719         StringBuilder sb = new StringBuilder();
720 
721         String line = null;
722 
723         while ((line = br.readLine()) != null) {
724             if (!line.startsWith("insert into Image (") &&
725                 !line.startsWith("insert into JournalArticle (") &&
726                 !line.startsWith("insert into JournalStructure (") &&
727                 !line.startsWith("insert into JournalTemplate (")) {
728 
729                 sb.append(line);
730                 sb.append("\n");
731             }
732         }
733 
734         br.close();
735 
736         return sb.toString();
737     }
738 
739     protected String removeNull(String content) {
740         content = StringUtil.replace(content, " not null", " not_null");
741         content = StringUtil.replace(content, " null", "");
742         content = StringUtil.replace(content, " not_null", " not null");
743 
744         return content;
745     }
746 
747     protected String replaceTemplate(String template, String[] actual) {
748         if ((template == null) || (TEMPLATE == null) || (actual == null)) {
749             return null;
750         }
751 
752         if (TEMPLATE.length != actual.length) {
753             return template;
754         }
755 
756         for (int i = 0; i < TEMPLATE.length; i++) {
757             if (TEMPLATE[i].equals("##") ||
758                 TEMPLATE[i].equals("'01/01/1970'")){
759 
760                 template = template.replaceAll(TEMPLATE[i], actual[i]);
761             }
762             else {
763                 template = template.replaceAll(
764                     "\\b" + TEMPLATE[i] + "\\b", actual[i]);
765             }
766         }
767 
768         return template;
769     }
770 
771     protected abstract String reword(String data) throws IOException;
772 
773     protected static String ALTER_COLUMN_TYPE = "alter_column_type ";
774 
775     protected static String ALTER_COLUMN_NAME = "alter_column_name ";
776 
777     protected static String DROP_PRIMARY_KEY = "drop primary key";
778 
779     protected static String[] REWORD_TEMPLATE = {
780         "@table@", "@old-column@", "@new-column@", "@type@", "@nullable@"
781     };
782 
783     protected static String[] TEMPLATE = {
784         "##", "TRUE", "FALSE",
785         "'01/01/1970'", "CURRENT_TIMESTAMP",
786         " BLOB", " BOOLEAN", " DATE",
787         " DOUBLE", " INTEGER", " LONG",
788         " STRING", " TEXT", " VARCHAR",
789         " IDENTITY", "COMMIT_TRANSACTION"
790     };
791 
792     private static Log _log = LogFactory.getLog(DBUtil.class);
793 
794     private static DBUtil _dbUtil;
795 
796     private String _type;
797 
798 }