1
14
15 package com.liferay.portal.dao.db;
16
17 import com.liferay.counter.service.CounterLocalServiceUtil;
18 import com.liferay.portal.kernel.dao.db.DB;
19 import com.liferay.portal.kernel.dao.db.DBFactoryUtil;
20 import com.liferay.portal.kernel.dao.db.Index;
21 import com.liferay.portal.kernel.dao.jdbc.DataAccess;
22 import com.liferay.portal.kernel.exception.SystemException;
23 import com.liferay.portal.kernel.io.unsync.UnsyncBufferedReader;
24 import com.liferay.portal.kernel.io.unsync.UnsyncStringReader;
25 import com.liferay.portal.kernel.log.Log;
26 import com.liferay.portal.kernel.log.LogFactoryUtil;
27 import com.liferay.portal.kernel.util.FileUtil;
28 import com.liferay.portal.kernel.util.GetterUtil;
29 import com.liferay.portal.kernel.util.PropertiesUtil;
30 import com.liferay.portal.kernel.util.StringBundler;
31 import com.liferay.portal.kernel.util.StringPool;
32 import com.liferay.portal.kernel.util.StringUtil;
33 import com.liferay.portal.kernel.util.Validator;
34 import com.liferay.portal.velocity.VelocityUtil;
35 import com.liferay.util.SimpleCounter;
36
37 import java.io.File;
38 import java.io.FileReader;
39 import java.io.IOException;
40 import java.io.InputStream;
41
42 import java.sql.Connection;
43 import java.sql.SQLException;
44 import java.sql.Statement;
45
46 import java.util.Collections;
47 import java.util.Enumeration;
48 import java.util.HashMap;
49 import java.util.HashSet;
50 import java.util.List;
51 import java.util.Map;
52 import java.util.Properties;
53 import java.util.Set;
54
55 import javax.naming.NamingException;
56
57
64 public abstract class BaseDB implements DB {
65
66 public void buildCreateFile(String sqlDir, String databaseName)
67 throws IOException {
68
69 buildCreateFile(sqlDir, databaseName, POPULATED);
70 buildCreateFile(sqlDir, databaseName, MINIMAL);
71 buildCreateFile(sqlDir, databaseName, SHARDED);
72 }
73
74 public void buildCreateFile(
75 String sqlDir, String databaseName, int population)
76 throws IOException {
77
78 String suffix = getSuffix(population);
79
80 File file = new File(
81 sqlDir + "/create" + suffix + "/create" + suffix + "-" +
82 getServerName() + ".sql");
83
84 if (population != SHARDED) {
85 String content = buildCreateFileContent(
86 sqlDir, databaseName, population);
87
88 if (content != null) {
89 FileUtil.write(file, content);
90 }
91 }
92 else {
93 String content = buildCreateFileContent(
94 sqlDir, databaseName, MINIMAL);
95
96 if (content != null) {
97 FileUtil.write(file, content);
98 }
99
100 content = buildCreateFileContent(
101 sqlDir, databaseName + "1", MINIMAL);
102
103 if (content != null) {
104 FileUtil.write(file, content, false, true);
105 }
106
107 content = buildCreateFileContent(
108 sqlDir, databaseName + "2", MINIMAL);
109
110 if (content != null) {
111 FileUtil.write(file, content, false, true);
112 }
113 }
114 }
115
116 public abstract String buildSQL(String template) throws IOException;
117
118 public void buildSQLFile(String sqlDir, String fileName)
119 throws IOException {
120
121 String template = buildTemplate(sqlDir, fileName);
122
123 template = buildSQL(template);
124
125 FileUtil.write(
126 sqlDir + "/" + fileName + "/" + fileName + "-" + getServerName() +
127 ".sql",
128 template);
129 }
130
131 @SuppressWarnings("unused")
132 public List<Index> getIndexes() throws SQLException {
133 return Collections.EMPTY_LIST;
134 }
135
136 public String getTemplateFalse() {
137 return getTemplate()[2];
138 }
139
140 public String getTemplateTrue() {
141 return getTemplate()[1];
142 }
143
144 public String getType() {
145 return _type;
146 }
147
148 public long increment() throws SystemException {
149 return CounterLocalServiceUtil.increment();
150 }
151
152 public boolean isSupportsAlterColumnName() {
153 return _SUPPORTS_ALTER_COLUMN_NAME;
154 }
155
156 public boolean isSupportsAlterColumnType() {
157 return _SUPPORTS_ALTER_COLUMN_TYPE;
158 }
159
160 public boolean isSupportsDateMilliseconds() {
161 return _SUPPORTS_DATE_MILLISECONDS;
162 }
163
164 public boolean isSupportsScrollableResults() {
165 return _SUPPORTS_SCROLLABLE_RESULTS;
166 }
167
168 public boolean isSupportsStringCaseSensitiveQuery() {
169 return _supportsStringCaseSensitiveQuery;
170 }
171
172 public boolean isSupportsUpdateWithInnerJoin() {
173 return _SUPPORTS_UPDATE_WITH_INNER_JOIN;
174 }
175
176 public void runSQL(String sql) throws IOException, SQLException {
177 runSQL(new String[] {sql});
178 }
179
180 public void runSQL(Connection con, String sql)
181 throws IOException, SQLException {
182
183 runSQL(con, new String[] {sql});
184 }
185
186 public void runSQL(String[] sqls) throws IOException, SQLException {
187 Connection con = DataAccess.getConnection();
188
189 try {
190 runSQL(con, sqls);
191 }
192 finally {
193 DataAccess.cleanUp(con);
194 }
195 }
196
197 public void runSQL(Connection con, String[] sqls)
198 throws IOException, SQLException {
199
200 Statement s = null;
201
202 try {
203 s = con.createStatement();
204
205 for (int i = 0; i < sqls.length; i++) {
206 String sql = buildSQL(sqls[i]);
207
208 sql = sql.trim();
209
210 if (sql.endsWith(";")) {
211 sql = sql.substring(0, sql.length() - 1);
212 }
213
214 if (sql.endsWith("go")) {
215 sql = sql.substring(0, sql.length() - 2);
216 }
217
218 if (_log.isDebugEnabled()) {
219 _log.debug(sql);
220 }
221
222 try {
223 s.executeUpdate(sql);
224 }
225 catch (SQLException sqle) {
226 throw sqle;
227 }
228 }
229 }
230 finally {
231 DataAccess.cleanUp(s);
232 }
233 }
234
235 public void runSQLTemplate(String path)
236 throws IOException, NamingException, SQLException {
237
238 runSQLTemplate(path, true);
239 }
240
241 public void runSQLTemplate(String path, boolean failOnError)
242 throws IOException, NamingException, SQLException {
243
244 Thread currentThread = Thread.currentThread();
245
246 ClassLoader classLoader = currentThread.getContextClassLoader();
247
248 InputStream is = classLoader.getResourceAsStream(
249 "com/liferay/portal/tools/sql/dependencies/" + path);
250
251 if (is == null) {
252 is = classLoader.getResourceAsStream(path);
253 }
254
255 if (is == null) {
256 _log.error("Invalid path " + path);
257
258 if (failOnError) {
259 throw new IOException("Invalid path " + path);
260 }
261 else {
262 return;
263 }
264 }
265
266 String template = StringUtil.read(is);
267
268 is.close();
269
270 boolean evaluate = path.endsWith(".vm");
271
272 runSQLTemplateString(template, evaluate, failOnError);
273 }
274
275 public void runSQLTemplateString(
276 String template, boolean evaluate, boolean failOnError)
277 throws IOException, NamingException, SQLException {
278
279 if (evaluate) {
280 try {
281 template = evaluateVM(template);
282 }
283 catch (Exception e) {
284 _log.error(e, e);
285 }
286 }
287
288 StringBundler sb = new StringBundler();
289
290 UnsyncBufferedReader unsyncBufferedReader = new UnsyncBufferedReader(
291 new UnsyncStringReader(template));
292
293 String line = null;
294
295 while ((line = unsyncBufferedReader.readLine()) != null) {
296 if (!line.startsWith("##")) {
297 if (line.startsWith("@include ")) {
298 int pos = line.indexOf(" ");
299
300 String includeFileName = line.substring(pos + 1);
301
302 Thread currentThread = Thread.currentThread();
303
304 ClassLoader classLoader =
305 currentThread.getContextClassLoader();
306
307 InputStream is = classLoader.getResourceAsStream(
308 "com/liferay/portal/tools/sql/dependencies/" +
309 includeFileName);
310
311 if (is == null) {
312 is = classLoader.getResourceAsStream(includeFileName);
313 }
314
315 String include = StringUtil.read(is);
316
317 is.close();
318
319 if (includeFileName.endsWith(".vm")) {
320 try {
321 include = evaluateVM(include);
322 }
323 catch (Exception e) {
324 _log.error(e, e);
325 }
326 }
327
328 include = convertTimestamp(include);
329 include = replaceTemplate(include, getTemplate());
330
331 runSQLTemplateString(include, false, true);
332 }
333 else{
334 sb.append(line);
335
336 if (line.endsWith(";")) {
337 String sql = sb.toString();
338
339 sb.setIndex(0);
340
341 try {
342 if (!sql.equals("COMMIT_TRANSACTION;")) {
343 runSQL(sql);
344 }
345 else {
346 if (_log.isDebugEnabled()) {
347 _log.debug("Skip commit sql");
348 }
349 }
350 }
351 catch (IOException ioe) {
352 if (failOnError) {
353 throw ioe;
354 }
355 else if (_log.isWarnEnabled()) {
356 _log.warn(ioe.getMessage());
357 }
358 }
359 catch (SQLException sqle) {
360 if (failOnError) {
361 throw sqle;
362 }
363 else if (_log.isWarnEnabled()) {
364 String message = GetterUtil.getString(
365 sqle.getMessage());
366
367 if (!message.startsWith("Duplicate key name")) {
368 _log.warn(message + ": " + sql);
369 }
370
371 if (message.startsWith("Duplicate entry") ||
372 message.startsWith(
373 "Specified key was too long")) {
374
375 _log.error(line);
376 }
377 }
378 }
379 }
380 }
381 }
382 }
383
384 unsyncBufferedReader.close();
385 }
386
387 public void setSupportsStringCaseSensitiveQuery(
388 boolean supportsStringCaseSensitiveQuery) {
389
390 if (_log.isInfoEnabled()) {
391 if (supportsStringCaseSensitiveQuery) {
392 _log.info("Database supports case sensitive queries");
393 }
394 else {
395 _log.info("Database does not support case sensitive queries");
396 }
397 }
398
399 _supportsStringCaseSensitiveQuery = supportsStringCaseSensitiveQuery;
400 }
401
402 public void updateIndexes(
403 String tablesSQL, String indexesSQL, String indexesProperties,
404 boolean dropIndexes)
405 throws IOException, SQLException {
406
407 List<Index> indexes = getIndexes();
408
409 Set<String> validIndexNames = null;
410
411 if (dropIndexes) {
412 validIndexNames = dropIndexes(
413 tablesSQL, indexesSQL, indexesProperties, indexes);
414 }
415 else {
416 validIndexNames = new HashSet<String>();
417
418 for (Index index : indexes) {
419 String indexName = index.getIndexName().toUpperCase();
420
421 validIndexNames.add(indexName);
422 }
423 }
424
425 addIndexes(indexesSQL, validIndexNames);
426 }
427
428 protected BaseDB(String type) {
429 _type = type;
430 }
431
432 protected void addIndexes(String indexesSQL, Set<String> validIndexNames)
433 throws IOException {
434
435 if (_log.isInfoEnabled()) {
436 _log.info("Adding indexes");
437 }
438
439 DB db = DBFactoryUtil.getDB();
440
441 UnsyncBufferedReader bufferedReader = new UnsyncBufferedReader(
442 new UnsyncStringReader(indexesSQL));
443
444 String sql = null;
445
446 while ((sql = bufferedReader.readLine()) != null) {
447 if (Validator.isNull(sql)) {
448 continue;
449 }
450
451 int y = sql.indexOf(" on ");
452 int x = sql.lastIndexOf(" ", y - 1);
453
454 String indexName = sql.substring(x + 1, y);
455
456 if (validIndexNames.contains(indexName)) {
457 continue;
458 }
459
460 if (_log.isInfoEnabled()) {
461 _log.info(sql);
462 }
463
464 try {
465 db.runSQL(sql);
466 }
467 catch (Exception e) {
468 if (_log.isWarnEnabled()) {
469 _log.warn(e.getMessage());
470 }
471 }
472 }
473 }
474
475 protected abstract String buildCreateFileContent(
476 String sqlDir, String databaseName, int population)
477 throws IOException;
478
479 protected String[] buildColumnNameTokens(String line) {
480 String[] words = StringUtil.split(line, " ");
481
482 if (words.length == 7) {
483 words[5] = "not null;";
484 }
485
486 String[] template = {
487 words[1], words[2], words[3], words[4], words[5]
488 };
489
490 return template;
491 }
492
493 protected String[] buildColumnTypeTokens(String line) {
494 String[] words = StringUtil.split(line, " ");
495
496 String nullable = "";
497
498 if (words.length == 6) {
499 nullable = "not null;";
500 }
501 else if (words.length == 5) {
502 nullable = words[4];
503 }
504 else if (words.length == 4) {
505 nullable = "not null;";
506
507 if (words[3].endsWith(";")) {
508 words[3] = words[3].substring(0, words[3].length() - 1);
509 }
510 }
511
512 String[] template = {
513 words[1], words[2], "", words[3], nullable
514 };
515
516 return template;
517 }
518
519 protected String buildTemplate(String sqlDir, String fileName)
520 throws IOException {
521
522 File file = new File(sqlDir + "/" + fileName + ".sql");
523
524 String template = FileUtil.read(file);
525
526 if (fileName.equals("portal") || fileName.equals("portal-minimal") ||
527 fileName.equals("update-5.0.1-5.1.0")) {
528
529 UnsyncBufferedReader unsyncBufferedReader =
530 new UnsyncBufferedReader(new UnsyncStringReader(template));
531
532 StringBundler sb = new StringBundler();
533
534 String line = null;
535
536 while ((line = unsyncBufferedReader.readLine()) != null) {
537 if (line.startsWith("@include ")) {
538 int pos = line.indexOf(" ");
539
540 String includeFileName = line.substring(pos + 1);
541
542 File includeFile = new File(
543 sqlDir + "/" + includeFileName);
544
545 if (!includeFile.exists()) {
546 continue;
547 }
548
549 String include = FileUtil.read(includeFile);
550
551 if (includeFileName.endsWith(".vm")) {
552 try {
553 include = evaluateVM(include);
554 }
555 catch (Exception e) {
556 _log.error(e, e);
557 }
558 }
559
560 include = convertTimestamp(include);
561 include = replaceTemplate(include, getTemplate());
562
563 sb.append(include);
564 sb.append("\n\n");
565 }
566 else {
567 sb.append(line);
568 sb.append("\n");
569 }
570 }
571
572 unsyncBufferedReader.close();
573
574 template = sb.toString();
575 }
576
577 if (fileName.equals("indexes") && (this instanceof SybaseDB)) {
578 template = removeBooleanIndexes(sqlDir, template);
579 }
580
581 return template;
582 }
583
584 protected String convertTimestamp(String data) {
585 String s = null;
586
587 if (this instanceof MySQLDB) {
588 s = StringUtil.replace(data, "SPECIFIC_TIMESTAMP_", "");
589 }
590 else {
591 s = data.replaceAll(
592 "SPECIFIC_TIMESTAMP_" + "\\d+", "CURRENT_TIMESTAMP");
593 }
594
595 return s;
596 }
597
598 protected Set<String> dropIndexes(
599 String tablesSQL, String indexesSQL, String indexesProperties,
600 List<Index> indexes)
601 throws IOException, SQLException {
602
603 if (_log.isInfoEnabled()) {
604 _log.info("Dropping stale indexes");
605 }
606
607 Set<String> validIndexNames = new HashSet<String>();
608
609 if (indexes.isEmpty()) {
610 return validIndexNames;
611 }
612
613 DB db = DBFactoryUtil.getDB();
614
615 String tablesSQLLowerCase = tablesSQL.toLowerCase();
616 String indexesSQLLowerCase = indexesSQL.toLowerCase();
617
618 Properties indexesPropertiesObj = PropertiesUtil.load(
619 indexesProperties);
620
621 Enumeration<String> enu =
622 (Enumeration<String>)indexesPropertiesObj.propertyNames();
623
624 while (enu.hasMoreElements()) {
625 String key = enu.nextElement();
626
627 String value = indexesPropertiesObj.getProperty(key);
628
629 indexesPropertiesObj.setProperty(key.toLowerCase(), value);
630 }
631
632 for (Index index : indexes) {
633 String indexNameUpperCase = index.getIndexName().toUpperCase();
634 String indexNameLowerCase = indexNameUpperCase.toLowerCase();
635 String tableName = index.getTableName();
636 String tableNameLowerCase = tableName.toLowerCase();
637 boolean unique = index.isUnique();
638
639 validIndexNames.add(indexNameUpperCase);
640
641 if (indexesPropertiesObj.containsKey(indexNameLowerCase)) {
642 if (unique &&
643 indexesSQLLowerCase.contains(
644 "create unique index " + indexNameLowerCase + " ")) {
645
646 continue;
647 }
648
649 if (!unique &&
650 indexesSQLLowerCase.contains(
651 "create index " + indexNameLowerCase + " ")) {
652
653 continue;
654 }
655 }
656 else {
657 if (!tablesSQLLowerCase.contains(
658 "create table " + tableNameLowerCase + " (")) {
659
660 continue;
661 }
662 }
663
664 validIndexNames.remove(indexNameUpperCase);
665
666 db.runSQL("drop index " + indexNameUpperCase + " on " + tableName);
667 }
668
669 return validIndexNames;
670 }
671
672 protected String evaluateVM(String template) throws Exception {
673 Map<String, Object> variables = new HashMap<String, Object>();
674
675 variables.put("counter", new SimpleCounter());
676
677 template = VelocityUtil.evaluate(template, variables);
678
679
681 UnsyncBufferedReader unsyncBufferedReader = new UnsyncBufferedReader(
682 new UnsyncStringReader(template));
683
684 StringBundler sb = new StringBundler();
685
686 String line = null;
687
688 while ((line = unsyncBufferedReader.readLine()) != null) {
689 line = line.trim();
690
691 sb.append(line);
692 sb.append("\n");
693 }
694
695 unsyncBufferedReader.close();
696
697 template = sb.toString();
698 template = StringUtil.replace(template, "\n\n\n", "\n\n");
699
700 return template;
701 }
702
703 protected abstract String getServerName();
704
705 protected String getSuffix(int type) {
706 if (type == MINIMAL) {
707 return "-minimal";
708 }
709 else if (type == SHARDED) {
710 return "-sharded";
711 }
712 else {
713 return StringPool.BLANK;
714 }
715 }
716
717 protected abstract String[] getTemplate();
718
719 protected String readSQL(String fileName, String comments, String eol)
720 throws IOException {
721
722 UnsyncBufferedReader unsyncBufferedReader = new UnsyncBufferedReader(
723 new FileReader(new File(fileName)));
724
725 StringBundler sb = new StringBundler();
726
727 String line = null;
728
729 while ((line = unsyncBufferedReader.readLine()) != null) {
730 if (!line.startsWith(comments)) {
731 line = StringUtil.replace(
732 line,
733 new String[] {"\n", "\t"},
734 new String[] {"", ""});
735
736 if (line.endsWith(";")) {
737 sb.append(line.substring(0, line.length() - 1));
738 sb.append(eol);
739 }
740 else {
741 sb.append(line);
742 }
743 }
744 }
745
746 unsyncBufferedReader.close();
747
748 return sb.toString();
749 }
750
751 protected String removeBooleanIndexes(String sqlDir, String data)
752 throws IOException {
753
754 String portalData = FileUtil.read(sqlDir + "/portal-tables.sql");
755
756 UnsyncBufferedReader unsyncBufferedReader = new UnsyncBufferedReader(
757 new UnsyncStringReader(data));
758
759 StringBundler sb = new StringBundler();
760
761 String line = null;
762
763 while ((line = unsyncBufferedReader.readLine()) != null) {
764 boolean append = true;
765
766 int x = line.indexOf(" on ");
767
768 if (x != -1) {
769 int y = line.indexOf(" (", x);
770
771 String table = line.substring(x + 4, y);
772
773 x = y + 2;
774 y = line.indexOf(")", x);
775
776 String[] columns = StringUtil.split(line.substring(x, y));
777
778 x = portalData.indexOf("create table " + table + " (");
779 y = portalData.indexOf(");", x);
780
781 String portalTableData = portalData.substring(x, y);
782
783 for (int i = 0; i < columns.length; i++) {
784 if (portalTableData.indexOf(
785 columns[i].trim() + " BOOLEAN") != -1) {
786
787 append = false;
788
789 break;
790 }
791 }
792 }
793
794 if (append) {
795 sb.append(line);
796 sb.append("\n");
797 }
798 }
799
800 unsyncBufferedReader.close();
801
802 return sb.toString();
803 }
804
805 protected String removeInserts(String data) throws IOException {
806 UnsyncBufferedReader unsyncBufferedReader = new UnsyncBufferedReader(
807 new UnsyncStringReader(data));
808
809 StringBundler sb = new StringBundler();
810
811 String line = null;
812
813 while ((line = unsyncBufferedReader.readLine()) != null) {
814 if (!line.startsWith("insert into ") &&
815 !line.startsWith("update ")) {
816
817 sb.append(line);
818 sb.append("\n");
819 }
820 }
821
822 unsyncBufferedReader.close();
823
824 return sb.toString();
825 }
826
827 protected String removeLongInserts(String data) throws IOException {
828 UnsyncBufferedReader unsyncBufferedReader = new UnsyncBufferedReader(
829 new UnsyncStringReader(data));
830
831 StringBundler sb = new StringBundler();
832
833 String line = null;
834
835 while ((line = unsyncBufferedReader.readLine()) != null) {
836 if (!line.startsWith("insert into Image (") &&
837 !line.startsWith("insert into JournalArticle (") &&
838 !line.startsWith("insert into JournalStructure (") &&
839 !line.startsWith("insert into JournalTemplate (")) {
840
841 sb.append(line);
842 sb.append("\n");
843 }
844 }
845
846 unsyncBufferedReader.close();
847
848 return sb.toString();
849 }
850
851 protected String removeNull(String content) {
852 content = StringUtil.replace(content, " is null", " IS NULL");
853 content = StringUtil.replace(content, " not null", " not_null");
854 content = StringUtil.replace(content, " null", "");
855 content = StringUtil.replace(content, " not_null", " not null");
856
857 return content;
858 }
859
860 protected String replaceTemplate(String template, String[] actual) {
861 if ((template == null) || (TEMPLATE == null) || (actual == null)) {
862 return null;
863 }
864
865 if (TEMPLATE.length != actual.length) {
866 return template;
867 }
868
869 for (int i = 0; i < TEMPLATE.length; i++) {
870 if (TEMPLATE[i].equals("##") ||
871 TEMPLATE[i].equals("'01/01/1970'")) {
872
873 template = template.replaceAll(TEMPLATE[i], actual[i]);
874 }
875 else {
876 template = template.replaceAll(
877 "\\b" + TEMPLATE[i] + "\\b", actual[i]);
878 }
879 }
880
881 return template;
882 }
883
884 protected abstract String reword(String data) throws IOException;
885
886 protected static String ALTER_COLUMN_TYPE = "alter_column_type ";
887
888 protected static String ALTER_COLUMN_NAME = "alter_column_name ";
889
890 protected static String DROP_INDEX = "drop index";
891
892 protected static String DROP_PRIMARY_KEY = "drop primary key";
893
894 protected static String[] REWORD_TEMPLATE = {
895 "@table@", "@old-column@", "@new-column@", "@type@", "@nullable@"
896 };
897
898 protected static String[] TEMPLATE = {
899 "##", "TRUE", "FALSE",
900 "'01/01/1970'", "CURRENT_TIMESTAMP",
901 " BLOB", " BOOLEAN", " DATE",
902 " DOUBLE", " INTEGER", " LONG",
903 " STRING", " TEXT", " VARCHAR",
904 " IDENTITY", "COMMIT_TRANSACTION"
905 };
906
907 private static boolean _SUPPORTS_ALTER_COLUMN_NAME = true;
908
909 private static boolean _SUPPORTS_ALTER_COLUMN_TYPE = true;
910
911 private static boolean _SUPPORTS_DATE_MILLISECONDS = true;
912
913 private static boolean _SUPPORTS_SCROLLABLE_RESULTS = true;
914
915 private static boolean _SUPPORTS_UPDATE_WITH_INNER_JOIN;
916
917 private static Log _log = LogFactoryUtil.getLog(BaseDB.class);
918
919 private String _type;
920 private boolean _supportsStringCaseSensitiveQuery;
921
922 }