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