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                 sb.append(line);
357 
358                 if (line.endsWith(";")) {
359                     String sql = sb.toString();
360 
361                     sb = new StringBuilder();
362 
363                     try {
364                         if (!sql.equals("COMMIT_TRANSACTION;")) {
365                             runSQL(sql);
366                         }
367                         else {
368                             if (_log.isDebugEnabled()) {
369                                 _log.debug("Skip commit sql");
370                             }
371                         }
372                     }
373                     catch (IOException ioe) {
374                         if (failOnError) {
375                             throw ioe;
376                         }
377                         else if (_log.isWarnEnabled()) {
378                             _log.warn(ioe.getMessage());
379                         }
380                     }
381                     catch (SQLException sqle) {
382                         if (failOnError) {
383                             throw sqle;
384                         }
385                         else if (_log.isWarnEnabled()) {
386                             _log.warn(sqle.getMessage());
387                         }
388                     }
389                 }
390             }
391         }
392 
393         br.close();
394     }
395 
396     protected abstract void buildCreateFile(
397             String databaseName, boolean minimal)
398         throws IOException;
399 
400     protected String[] buildColumnNameTokens(String line) {
401         String[] words = StringUtil.split(line, " ");
402 
403         if (words.length == 7) {
404             words[5] = "not null;";
405         }
406 
407         String[] template = {
408             words[1], words[2], words[3], words[4], words[5]
409         };
410 
411         return template;
412     }
413 
414     protected String[] buildColumnTypeTokens(String line) {
415         String[] words = StringUtil.split(line, " ");
416 
417         String nullable = "";
418 
419         if (words.length == 6) {
420             nullable = "not null;";
421         }
422         else if (words.length == 5) {
423             nullable = words[4];
424         }
425         else if (words.length == 4) {
426             nullable = "not null;";
427 
428             if (words[3].endsWith(";")) {
429                 words[3] = words[3].substring(0, words[3].length() - 1);
430             }
431         }
432 
433         String[] template = {
434             words[1], words[2], "", words[3], nullable
435         };
436 
437         return template;
438     }
439 
440     protected String buildTemplate(String fileName) throws IOException {
441         File file = new File("../sql/" + fileName + ".sql");
442 
443         String template = FileUtil.read(file);
444 
445         if (fileName.equals("portal") || fileName.equals("portal-minimal") ||
446             fileName.equals("update-5.0.1-5.1.0")) {
447 
448             BufferedReader br = new BufferedReader(new StringReader(template));
449 
450             StringBuilder sb = new StringBuilder();
451 
452             String line = null;
453 
454             while ((line = br.readLine()) != null) {
455                 if (line.startsWith("@include ")) {
456                     int pos = line.indexOf(" ");
457 
458                     String includeFileName =
459                         line.substring(pos + 1, line.length());
460 
461                     File includeFile = new File("../sql/" + includeFileName);
462 
463                     if (!includeFile.exists()) {
464                         continue;
465                     }
466 
467                     String include = FileUtil.read(includeFile);
468 
469                     if (includeFileName.endsWith(".vm")) {
470                         try {
471                             include = evaluateVM(include);
472                         }
473                         catch (Exception e) {
474                             e.printStackTrace();
475                         }
476                     }
477 
478                     include = convertTimestamp(include);
479                     include = StringUtil.replace(
480                         include, TEMPLATE, getTemplate());
481 
482                     sb.append(include);
483                     sb.append("\n\n");
484                 }
485                 else {
486                     sb.append(line);
487                     sb.append("\n");
488                 }
489             }
490 
491             br.close();
492 
493             template = sb.toString();
494         }
495 
496         if (fileName.equals("indexes") && (this instanceof SybaseUtil)) {
497             template = removeBooleanIndexes(template);
498         }
499 
500         return template;
501     }
502 
503     protected String convertTimestamp(String data) {
504         String s = null;
505 
506         if (this instanceof MySQLUtil) {
507             s = StringUtil.replace(data, "SPECIFIC_TIMESTAMP_", "");
508         }
509         else {
510             s = data.replaceAll(
511                 "SPECIFIC_TIMESTAMP_" + "\\d+", "CURRENT_TIMESTAMP");
512         }
513 
514         return s;
515     }
516 
517     protected String evaluateVM(String template) throws Exception {
518         Map<String, Object> variables = new HashMap<String, Object>();
519 
520         variables.put("counter", new SimpleCounter());
521 
522         template = VelocityUtil.evaluate(template, variables);
523 
524         // Trim insert statements because it breaks MySQL Query Browser
525 
526         BufferedReader br = new BufferedReader(new StringReader(template));
527 
528         StringBuilder sb = new StringBuilder();
529 
530         String line = null;
531 
532         while ((line = br.readLine()) != null) {
533             line = line.trim();
534 
535             sb.append(line);
536             sb.append("\n");
537         }
538 
539         br.close();
540 
541         template = sb.toString();
542         template = StringUtil.replace(template, "\n\n\n", "\n\n");
543 
544         return template;
545     }
546 
547     protected String getMinimalSuffix(boolean minimal) {
548         if (minimal) {
549             return "-minimal";
550         }
551         else {
552             return StringPool.BLANK;
553         }
554     }
555 
556     protected abstract String getServerName();
557 
558     protected abstract String[] getTemplate();
559 
560     protected String readSQL(String fileName, String comments, String eol)
561         throws IOException {
562 
563         BufferedReader br = new BufferedReader(
564             new FileReader(new File(fileName)));
565 
566         StringBuilder sb = new StringBuilder();
567 
568         String line = null;
569 
570         while ((line = br.readLine()) != null) {
571             if (!line.startsWith(comments)) {
572                 line = StringUtil.replace(
573                     line,
574                     new String[] {"\n", "\t"},
575                     new String[] {"", ""});
576 
577                 if (line.endsWith(";")) {
578                     sb.append(line.substring(0, line.length() - 1));
579                     sb.append(eol);
580                 }
581                 else {
582                     sb.append(line);
583                 }
584             }
585         }
586 
587         br.close();
588 
589         return sb.toString();
590     }
591 
592     protected String removeBooleanIndexes(String data) throws IOException {
593         String portalData = FileUtil.read("../sql/portal-tables.sql");
594 
595         BufferedReader br = new BufferedReader(new StringReader(data));
596 
597         StringBuilder sb = new StringBuilder();
598 
599         String line = null;
600 
601         while ((line = br.readLine()) != null) {
602             boolean append = true;
603 
604             int x = line.indexOf(" on ");
605 
606             if (x != -1) {
607                 int y = line.indexOf(" (", x);
608 
609                 String table = line.substring(x + 4, y);
610 
611                 x = y + 2;
612                 y = line.indexOf(")", x);
613 
614                 String[] columns = StringUtil.split(line.substring(x, y));
615 
616                 x = portalData.indexOf("create table " + table + " (");
617                 y = portalData.indexOf(");", x);
618 
619                 String portalTableData = portalData.substring(x, y);
620 
621                 for (int i = 0; i < columns.length; i++) {
622                     if (portalTableData.indexOf(
623                             columns[i].trim() + " BOOLEAN") != -1) {
624 
625                         append = false;
626 
627                         break;
628                     }
629                 }
630             }
631 
632             if (append) {
633                 sb.append(line);
634                 sb.append("\n");
635             }
636         }
637 
638         br.close();
639 
640         return sb.toString();
641     }
642 
643     protected String removeInserts(String data) throws IOException {
644         BufferedReader br = new BufferedReader(new StringReader(data));
645 
646         StringBuilder sb = new StringBuilder();
647 
648         String line = null;
649 
650         while ((line = br.readLine()) != null) {
651             if (!line.startsWith("insert into ") &&
652                 !line.startsWith("update ")) {
653 
654                 sb.append(line);
655                 sb.append("\n");
656             }
657         }
658 
659         br.close();
660 
661         return sb.toString();
662     }
663 
664     protected String removeLongInserts(String data) throws IOException {
665         BufferedReader br = new BufferedReader(new StringReader(data));
666 
667         StringBuilder sb = new StringBuilder();
668 
669         String line = null;
670 
671         while ((line = br.readLine()) != null) {
672             if (!line.startsWith("insert into Image (") &&
673                 !line.startsWith("insert into JournalArticle (") &&
674                 !line.startsWith("insert into JournalStructure (") &&
675                 !line.startsWith("insert into JournalTemplate (")) {
676 
677                 sb.append(line);
678                 sb.append("\n");
679             }
680         }
681 
682         br.close();
683 
684         return sb.toString();
685     }
686 
687     protected String removeNull(String content) {
688         content = StringUtil.replace(content, " not null", " not_null");
689         content = StringUtil.replace(content, " null", "");
690         content = StringUtil.replace(content, " not_null", " not null");
691 
692         return content;
693     }
694 
695     protected abstract String reword(String data) throws IOException;
696 
697     protected static String ALTER_COLUMN_TYPE = "alter_column_type ";
698 
699     protected static String ALTER_COLUMN_NAME = "alter_column_name ";
700 
701     protected static String DROP_PRIMARY_KEY = "drop primary key";
702 
703     protected static String[] REWORD_TEMPLATE = {
704         "@table@", "@old-column@", "@new-column@", "@type@", "@nullable@"
705     };
706 
707     protected static String[] TEMPLATE = {
708         "##", "TRUE", "FALSE",
709         "'01/01/1970'", "CURRENT_TIMESTAMP",
710         " BLOB", " BOOLEAN", " DATE",
711         " DOUBLE", " INTEGER", " LONG",
712         " STRING", " TEXT", " VARCHAR",
713         " IDENTITY", "COMMIT_TRANSACTION"
714     };
715 
716     private static Log _log = LogFactory.getLog(DBUtil.class);
717 
718     private static DBUtil _dbUtil;
719 
720 }