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