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