1   /**
2    * Copyright (c) 2000-2009 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.dao.db;
24  
25  import com.liferay.portal.kernel.dao.db.DB;
26  import com.liferay.portal.kernel.dao.jdbc.DataAccess;
27  import com.liferay.portal.kernel.log.Log;
28  import com.liferay.portal.kernel.log.LogFactoryUtil;
29  import com.liferay.portal.kernel.util.FileUtil;
30  import com.liferay.portal.kernel.util.GetterUtil;
31  import com.liferay.portal.kernel.util.StringPool;
32  import com.liferay.portal.kernel.util.StringUtil;
33  import com.liferay.portal.velocity.VelocityUtil;
34  import com.liferay.util.SimpleCounter;
35  
36  import java.io.BufferedReader;
37  import java.io.File;
38  import java.io.FileReader;
39  import java.io.IOException;
40  import java.io.InputStream;
41  import java.io.StringReader;
42  
43  import java.sql.Connection;
44  import java.sql.SQLException;
45  import java.sql.Statement;
46  
47  import java.util.HashMap;
48  import java.util.Map;
49  
50  import javax.naming.NamingException;
51  
52  /**
53   * <a href="BaseDB.java.html"><b><i>View Source</i></b></a>
54   *
55   * @author Alexander Chow
56   * @author Ganesh Ram
57   * @author Brian Wing Shun Chan
58   */
59  public abstract class BaseDB implements DB {
60  
61      public void buildCreateFile(String databaseName) throws IOException {
62          buildCreateFile(databaseName, POPULATED);
63          buildCreateFile(databaseName, MINIMAL);
64          buildCreateFile(databaseName, SHARDED);
65      }
66  
67      public void buildCreateFile(String databaseName, int population)
68          throws IOException {
69  
70          String suffix = getSuffix(population);
71  
72          File file = new File(
73              "../sql/create" + suffix + "/create" + suffix + "-" +
74                  getServerName() + ".sql");
75  
76          if (population != SHARDED) {
77              String content = buildCreateFileContent(databaseName, population);
78  
79              if (content != null) {
80                  FileUtil.write(file, content);
81              }
82          }
83          else {
84              String content = buildCreateFileContent(databaseName, MINIMAL);
85  
86              if (content != null) {
87                  FileUtil.write(file, content);
88              }
89  
90              content = buildCreateFileContent(databaseName + "1", MINIMAL);
91  
92              if (content != null) {
93                  FileUtil.write(file, content, false, true);
94              }
95  
96              content = buildCreateFileContent(databaseName + "2", MINIMAL);
97  
98              if (content != null) {
99                  FileUtil.write(file, content, false, true);
100             }
101         }
102     }
103 
104     public abstract String buildSQL(String template) throws IOException;
105 
106     public void buildSQLFile(String fileName) throws IOException {
107         String template = buildTemplate(fileName);
108 
109         template = buildSQL(template);
110 
111         FileUtil.write(
112             "../sql/" + fileName + "/" + fileName + "-" + getServerName() +
113                 ".sql",
114             template);
115     }
116 
117     public String getTemplateFalse() {
118         return getTemplate()[2];
119     }
120 
121     public String getTemplateTrue() {
122         return getTemplate()[1];
123     }
124 
125     public String getType() {
126         return _type;
127     }
128 
129     public boolean isSupportsAlterColumnName() {
130         return _SUPPORTS_ALTER_COLUMN_NAME;
131     }
132 
133     public boolean isSupportsAlterColumnType() {
134         return _SUPPORTS_ALTER_COLUMN_TYPE;
135     }
136 
137     public boolean isSupportsDateMilliseconds() {
138         return _SUPPORTS_DATE_MILLISECONDS;
139     }
140 
141     public boolean isSupportsScrollableResults() {
142         return _SUPPORTS_SCROLLABLE_RESULTS;
143     }
144 
145     public boolean isSupportsStringCaseSensitiveQuery() {
146         return _supportsStringCaseSensitiveQuery;
147     }
148 
149     public boolean isSupportsUpdateWithInnerJoin() {
150         return _SUPPORTS_UPDATE_WITH_INNER_JOIN;
151     }
152 
153     public void runSQL(String sql) throws IOException, SQLException {
154         runSQL(new String[] {sql});
155     }
156 
157     public void runSQL(Connection con, String sql)
158         throws IOException, SQLException {
159 
160         runSQL(con, new String[] {sql});
161     }
162 
163     public void runSQL(String[] sqls) throws IOException, SQLException {
164         Connection con = DataAccess.getConnection();
165 
166         try {
167             runSQL(con, sqls);
168         }
169         finally {
170             DataAccess.cleanUp(con);
171         }
172     }
173 
174     public void runSQL(Connection con, String[] sqls)
175         throws IOException, SQLException {
176 
177         Statement s = null;
178 
179         try {
180             s = con.createStatement();
181 
182             for (int i = 0; i < sqls.length; i++) {
183                 String sql = buildSQL(sqls[i]);
184 
185                 sql = sql.trim();
186 
187                 if (sql.endsWith(";")) {
188                     sql = sql.substring(0, sql.length() - 1);
189                 }
190 
191                 if (sql.endsWith("go")) {
192                     sql = sql.substring(0, sql.length() - 2);
193                 }
194 
195                 if (_log.isDebugEnabled()) {
196                     _log.debug(sql);
197                 }
198 
199                 try {
200                     s.executeUpdate(sql);
201                 }
202                 catch (SQLException sqle) {
203                     throw sqle;
204                 }
205             }
206         }
207         finally {
208             DataAccess.cleanUp(s);
209         }
210     }
211 
212     public void runSQLTemplate(String path)
213         throws IOException, NamingException, SQLException {
214 
215         runSQLTemplate(path, true);
216     }
217 
218     public void runSQLTemplate(String path, boolean failOnError)
219         throws IOException, NamingException, SQLException {
220 
221         Thread currentThread = Thread.currentThread();
222 
223         ClassLoader classLoader = currentThread.getContextClassLoader();
224 
225         InputStream is = classLoader.getResourceAsStream(
226             "com/liferay/portal/tools/sql/dependencies/" + path);
227 
228         if (is == null) {
229             is = classLoader.getResourceAsStream(path);
230         }
231 
232         if (is == null) {
233             _log.error("Invalid path " + path);
234         }
235 
236         String template = StringUtil.read(is);
237 
238         is.close();
239 
240         boolean evaluate = path.endsWith(".vm");
241 
242         runSQLTemplateString(template, evaluate, failOnError);
243     }
244 
245     public void runSQLTemplateString(
246             String template, boolean evaluate, boolean failOnError)
247         throws IOException, NamingException, SQLException {
248 
249         if (evaluate) {
250             try {
251                 template = evaluateVM(template);
252             }
253             catch (Exception e) {
254                 _log.error(e, e);
255             }
256         }
257 
258         StringBuilder sb = new StringBuilder();
259 
260         BufferedReader br = new BufferedReader(new StringReader(template));
261 
262         String line = null;
263 
264         while ((line = br.readLine()) != null) {
265             if (!line.startsWith("##")) {
266                 if (line.startsWith("@include ")) {
267                     int pos = line.indexOf(" ");
268 
269                     String includeFileName = line.substring(pos + 1);
270 
271                     Thread currentThread = Thread.currentThread();
272 
273                     ClassLoader classLoader =
274                         currentThread.getContextClassLoader();
275 
276                     InputStream is = classLoader.getResourceAsStream(
277                         "com/liferay/portal/tools/sql/dependencies/" +
278                             includeFileName);
279 
280                     if (is == null) {
281                         is = classLoader.getResourceAsStream(includeFileName);
282                     }
283 
284                     String include = StringUtil.read(is);
285 
286                     is.close();
287 
288                     if (includeFileName.endsWith(".vm")) {
289                         try {
290                             include = evaluateVM(include);
291                         }
292                         catch (Exception e) {
293                             _log.error(e, e);
294                         }
295                     }
296 
297                     include = convertTimestamp(include);
298                     include = replaceTemplate(include, getTemplate());
299 
300                     runSQLTemplateString(include, false, true);
301                 }
302                 else{
303                     sb.append(line);
304 
305                     if (line.endsWith(";")) {
306                         String sql = sb.toString();
307 
308                         sb = new StringBuilder();
309 
310                         try {
311                             if (!sql.equals("COMMIT_TRANSACTION;")) {
312                                 runSQL(sql);
313                             }
314                             else {
315                                 if (_log.isDebugEnabled()) {
316                                     _log.debug("Skip commit sql");
317                                 }
318                             }
319                         }
320                         catch (IOException ioe) {
321                             if (failOnError) {
322                                 throw ioe;
323                             }
324                             else if (_log.isWarnEnabled()) {
325                                 _log.warn(ioe.getMessage());
326                             }
327                         }
328                         catch (SQLException sqle) {
329                             if (failOnError) {
330                                 throw sqle;
331                             }
332                             else if (_log.isWarnEnabled()) {
333                                 String message = GetterUtil.getString(
334                                     sqle.getMessage());
335 
336                                 if (!message.startsWith("Duplicate key name")) {
337                                     _log.warn(message + ": " + sql);
338                                 }
339 
340                                 if (message.startsWith("Duplicate entry") ||
341                                     message.startsWith(
342                                         "Specified key was too long")) {
343 
344                                     _log.error(line);
345                                 }
346                             }
347                         }
348                     }
349                 }
350             }
351         }
352 
353         br.close();
354     }
355 
356     public void setSupportsStringCaseSensitiveQuery(
357         boolean supportsStringCaseSensitiveQuery) {
358 
359         if (_log.isInfoEnabled()) {
360             if (supportsStringCaseSensitiveQuery) {
361                 _log.info("Database supports case sensitive queries");
362             }
363             else {
364                 _log.info("Database does not support case sensitive queries");
365             }
366         }
367 
368         _supportsStringCaseSensitiveQuery = supportsStringCaseSensitiveQuery;
369     }
370 
371     protected BaseDB(String type) {
372         _type = type;
373     }
374 
375     protected abstract String buildCreateFileContent(
376             String databaseName, int population)
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-5.0.1-5.1.0")) {
426 
427             BufferedReader br = new BufferedReader(new StringReader(template));
428 
429             StringBuilder sb = new StringBuilder();
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 = line.substring(pos + 1);
438 
439                     File includeFile = new File("../sql/" + includeFileName);
440 
441                     if (!includeFile.exists()) {
442                         continue;
443                     }
444 
445                     String include = FileUtil.read(includeFile);
446 
447                     if (includeFileName.endsWith(".vm")) {
448                         try {
449                             include = evaluateVM(include);
450                         }
451                         catch (Exception e) {
452                             _log.error(e, e);
453                         }
454                     }
455 
456                     include = convertTimestamp(include);
457                     include = replaceTemplate(include, getTemplate());
458 
459                     sb.append(include);
460                     sb.append("\n\n");
461                 }
462                 else {
463                     sb.append(line);
464                     sb.append("\n");
465                 }
466             }
467 
468             br.close();
469 
470             template = sb.toString();
471         }
472 
473         if (fileName.equals("indexes") && (this instanceof SybaseDB)) {
474             template = removeBooleanIndexes(template);
475         }
476 
477         return template;
478     }
479 
480     protected String convertTimestamp(String data) {
481         String s = null;
482 
483         if (this instanceof MySQLDB) {
484             s = StringUtil.replace(data, "SPECIFIC_TIMESTAMP_", "");
485         }
486         else {
487             s = data.replaceAll(
488                 "SPECIFIC_TIMESTAMP_" + "\\d+", "CURRENT_TIMESTAMP");
489         }
490 
491         return s;
492     }
493 
494     protected String evaluateVM(String template) throws Exception {
495         Map<String, Object> variables = new HashMap<String, Object>();
496 
497         variables.put("counter", new SimpleCounter());
498 
499         template = VelocityUtil.evaluate(template, variables);
500 
501         // Trim insert statements because it breaks MySQL Query Browser
502 
503         BufferedReader br = new BufferedReader(new StringReader(template));
504 
505         StringBuilder sb = new StringBuilder();
506 
507         String line = null;
508 
509         while ((line = br.readLine()) != null) {
510             line = line.trim();
511 
512             sb.append(line);
513             sb.append("\n");
514         }
515 
516         br.close();
517 
518         template = sb.toString();
519         template = StringUtil.replace(template, "\n\n\n", "\n\n");
520 
521         return template;
522     }
523 
524     protected abstract String getServerName();
525 
526     protected String getSuffix(int type) {
527         if (type == MINIMAL) {
528             return "-minimal";
529         }
530         else if (type == SHARDED) {
531             return "-sharded";
532         }
533         else {
534             return StringPool.BLANK;
535         }
536     }
537 
538     protected abstract String[] getTemplate();
539 
540     protected String readSQL(String fileName, String comments, String eol)
541         throws IOException {
542 
543         BufferedReader br = new BufferedReader(
544             new FileReader(new File(fileName)));
545 
546         StringBuilder sb = new StringBuilder();
547 
548         String line = null;
549 
550         while ((line = br.readLine()) != null) {
551             if (!line.startsWith(comments)) {
552                 line = StringUtil.replace(
553                     line,
554                     new String[] {"\n", "\t"},
555                     new String[] {"", ""});
556 
557                 if (line.endsWith(";")) {
558                     sb.append(line.substring(0, line.length() - 1));
559                     sb.append(eol);
560                 }
561                 else {
562                     sb.append(line);
563                 }
564             }
565         }
566 
567         br.close();
568 
569         return sb.toString();
570     }
571 
572     protected String removeBooleanIndexes(String data) throws IOException {
573         String portalData = FileUtil.read("../sql/portal-tables.sql");
574 
575         BufferedReader br = new BufferedReader(new StringReader(data));
576 
577         StringBuilder sb = new StringBuilder();
578 
579         String line = null;
580 
581         while ((line = br.readLine()) != null) {
582             boolean append = true;
583 
584             int x = line.indexOf(" on ");
585 
586             if (x != -1) {
587                 int y = line.indexOf(" (", x);
588 
589                 String table = line.substring(x + 4, y);
590 
591                 x = y + 2;
592                 y = line.indexOf(")", x);
593 
594                 String[] columns = StringUtil.split(line.substring(x, y));
595 
596                 x = portalData.indexOf("create table " + table + " (");
597                 y = portalData.indexOf(");", x);
598 
599                 String portalTableData = portalData.substring(x, y);
600 
601                 for (int i = 0; i < columns.length; i++) {
602                     if (portalTableData.indexOf(
603                             columns[i].trim() + " BOOLEAN") != -1) {
604 
605                         append = false;
606 
607                         break;
608                     }
609                 }
610             }
611 
612             if (append) {
613                 sb.append(line);
614                 sb.append("\n");
615             }
616         }
617 
618         br.close();
619 
620         return sb.toString();
621     }
622 
623     protected String removeInserts(String data) throws IOException {
624         BufferedReader br = new BufferedReader(new StringReader(data));
625 
626         StringBuilder sb = new StringBuilder();
627 
628         String line = null;
629 
630         while ((line = br.readLine()) != null) {
631             if (!line.startsWith("insert into ") &&
632                 !line.startsWith("update ")) {
633 
634                 sb.append(line);
635                 sb.append("\n");
636             }
637         }
638 
639         br.close();
640 
641         return sb.toString();
642     }
643 
644     protected String removeLongInserts(String data) throws IOException {
645         BufferedReader br = new BufferedReader(new StringReader(data));
646 
647         StringBuilder sb = new StringBuilder();
648 
649         String line = null;
650 
651         while ((line = br.readLine()) != null) {
652             if (!line.startsWith("insert into Image (") &&
653                 !line.startsWith("insert into JournalArticle (") &&
654                 !line.startsWith("insert into JournalStructure (") &&
655                 !line.startsWith("insert into JournalTemplate (")) {
656 
657                 sb.append(line);
658                 sb.append("\n");
659             }
660         }
661 
662         br.close();
663 
664         return sb.toString();
665     }
666 
667     protected String removeNull(String content) {
668         content = StringUtil.replace(content, " not null", " not_null");
669         content = StringUtil.replace(content, " null", "");
670         content = StringUtil.replace(content, " not_null", " not null");
671 
672         return content;
673     }
674 
675     protected String replaceTemplate(String template, String[] actual) {
676         if ((template == null) || (TEMPLATE == null) || (actual == null)) {
677             return null;
678         }
679 
680         if (TEMPLATE.length != actual.length) {
681             return template;
682         }
683 
684         for (int i = 0; i < TEMPLATE.length; i++) {
685             if (TEMPLATE[i].equals("##") ||
686                 TEMPLATE[i].equals("'01/01/1970'")) {
687 
688                 template = template.replaceAll(TEMPLATE[i], actual[i]);
689             }
690             else {
691                 template = template.replaceAll(
692                     "\\b" + TEMPLATE[i] + "\\b", actual[i]);
693             }
694         }
695 
696         return template;
697     }
698 
699     protected abstract String reword(String data) throws IOException;
700 
701     protected static String ALTER_COLUMN_TYPE = "alter_column_type ";
702 
703     protected static String ALTER_COLUMN_NAME = "alter_column_name ";
704 
705     protected static String DROP_PRIMARY_KEY = "drop primary key";
706 
707     protected static String[] REWORD_TEMPLATE = {
708         "@table@", "@old-column@", "@new-column@", "@type@", "@nullable@"
709     };
710 
711     protected static String[] TEMPLATE = {
712         "##", "TRUE", "FALSE",
713         "'01/01/1970'", "CURRENT_TIMESTAMP",
714         " BLOB", " BOOLEAN", " DATE",
715         " DOUBLE", " INTEGER", " LONG",
716         " STRING", " TEXT", " VARCHAR",
717         " IDENTITY", "COMMIT_TRANSACTION"
718     };
719 
720     private static boolean _SUPPORTS_ALTER_COLUMN_NAME = true;
721 
722     private static boolean _SUPPORTS_ALTER_COLUMN_TYPE = true;
723 
724     private static boolean _SUPPORTS_DATE_MILLISECONDS = true;
725 
726     private static boolean _SUPPORTS_SCROLLABLE_RESULTS = true;
727 
728     private static boolean _SUPPORTS_UPDATE_WITH_INNER_JOIN;
729 
730     private static Log _log = LogFactoryUtil.getLog(BaseDB.class);
731 
732     private String _type;
733     private boolean _supportsStringCaseSensitiveQuery;
734 
735 }