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