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
259 String template = StringUtil.read(is);
260
261 is.close();
262
263 boolean evaluate = path.endsWith(".vm");
264
265 runSQLTemplateString(template, evaluate, failOnError);
266 }
267
268 public void runSQLTemplateString(
269 String template, boolean evaluate, boolean failOnError)
270 throws IOException, NamingException, SQLException {
271
272 if (evaluate) {
273 try {
274 template = evaluateVM(template);
275 }
276 catch (Exception e) {
277 _log.error(e, e);
278 }
279 }
280
281 StringBundler sb = new StringBundler();
282
283 UnsyncBufferedReader unsyncBufferedReader = new UnsyncBufferedReader(
284 new UnsyncStringReader(template));
285
286 String line = null;
287
288 while ((line = unsyncBufferedReader.readLine()) != null) {
289 if (!line.startsWith("##")) {
290 if (line.startsWith("@include ")) {
291 int pos = line.indexOf(" ");
292
293 String includeFileName = line.substring(pos + 1);
294
295 Thread currentThread = Thread.currentThread();
296
297 ClassLoader classLoader =
298 currentThread.getContextClassLoader();
299
300 InputStream is = classLoader.getResourceAsStream(
301 "com/liferay/portal/tools/sql/dependencies/" +
302 includeFileName);
303
304 if (is == null) {
305 is = classLoader.getResourceAsStream(includeFileName);
306 }
307
308 String include = StringUtil.read(is);
309
310 is.close();
311
312 if (includeFileName.endsWith(".vm")) {
313 try {
314 include = evaluateVM(include);
315 }
316 catch (Exception e) {
317 _log.error(e, e);
318 }
319 }
320
321 include = convertTimestamp(include);
322 include = replaceTemplate(include, getTemplate());
323
324 runSQLTemplateString(include, false, true);
325 }
326 else{
327 sb.append(line);
328
329 if (line.endsWith(";")) {
330 String sql = sb.toString();
331
332 sb.setIndex(0);
333
334 try {
335 if (!sql.equals("COMMIT_TRANSACTION;")) {
336 runSQL(sql);
337 }
338 else {
339 if (_log.isDebugEnabled()) {
340 _log.debug("Skip commit sql");
341 }
342 }
343 }
344 catch (IOException ioe) {
345 if (failOnError) {
346 throw ioe;
347 }
348 else if (_log.isWarnEnabled()) {
349 _log.warn(ioe.getMessage());
350 }
351 }
352 catch (SQLException sqle) {
353 if (failOnError) {
354 throw sqle;
355 }
356 else if (_log.isWarnEnabled()) {
357 String message = GetterUtil.getString(
358 sqle.getMessage());
359
360 if (!message.startsWith("Duplicate key name")) {
361 _log.warn(message + ": " + sql);
362 }
363
364 if (message.startsWith("Duplicate entry") ||
365 message.startsWith(
366 "Specified key was too long")) {
367
368 _log.error(line);
369 }
370 }
371 }
372 }
373 }
374 }
375 }
376
377 unsyncBufferedReader.close();
378 }
379
380 public void setSupportsStringCaseSensitiveQuery(
381 boolean supportsStringCaseSensitiveQuery) {
382
383 if (_log.isInfoEnabled()) {
384 if (supportsStringCaseSensitiveQuery) {
385 _log.info("Database supports case sensitive queries");
386 }
387 else {
388 _log.info("Database does not support case sensitive queries");
389 }
390 }
391
392 _supportsStringCaseSensitiveQuery = supportsStringCaseSensitiveQuery;
393 }
394
395 public void updateIndexes(
396 String tablesSQL, String indexesSQL, String indexesProperties,
397 boolean dropIndexes)
398 throws IOException, SQLException {
399
400 List<Index> indexes = getIndexes();
401
402 Set<String> validIndexNames = null;
403
404 if (dropIndexes) {
405 validIndexNames = dropIndexes(
406 tablesSQL, indexesSQL, indexesProperties, indexes);
407 }
408 else {
409 validIndexNames = new HashSet<String>();
410
411 for (Index index : indexes) {
412 String indexName = index.getIndexName().toUpperCase();
413
414 validIndexNames.add(indexName);
415 }
416 }
417
418 addIndexes(indexesSQL, validIndexNames);
419 }
420
421 protected BaseDB(String type) {
422 _type = type;
423 }
424
425 protected void addIndexes(String indexesSQL, Set<String> validIndexNames)
426 throws IOException {
427
428 if (_log.isInfoEnabled()) {
429 _log.info("Adding indexes");
430 }
431
432 DB db = DBFactoryUtil.getDB();
433
434 UnsyncBufferedReader bufferedReader = new UnsyncBufferedReader(
435 new UnsyncStringReader(indexesSQL));
436
437 String sql = null;
438
439 while ((sql = bufferedReader.readLine()) != null) {
440 if (Validator.isNull(sql)) {
441 continue;
442 }
443
444 int y = sql.indexOf(" on ");
445 int x = sql.lastIndexOf(" ", y - 1);
446
447 String indexName = sql.substring(x + 1, y);
448
449 if (validIndexNames.contains(indexName)) {
450 continue;
451 }
452
453 if (_log.isInfoEnabled()) {
454 _log.info(sql);
455 }
456
457 try {
458 db.runSQL(sql);
459 }
460 catch (Exception e) {
461 if (_log.isWarnEnabled()) {
462 _log.warn(e.getMessage());
463 }
464 }
465 }
466 }
467
468 protected abstract String buildCreateFileContent(
469 String sqlDir, String databaseName, int population)
470 throws IOException;
471
472 protected String[] buildColumnNameTokens(String line) {
473 String[] words = StringUtil.split(line, " ");
474
475 if (words.length == 7) {
476 words[5] = "not null;";
477 }
478
479 String[] template = {
480 words[1], words[2], words[3], words[4], words[5]
481 };
482
483 return template;
484 }
485
486 protected String[] buildColumnTypeTokens(String line) {
487 String[] words = StringUtil.split(line, " ");
488
489 String nullable = "";
490
491 if (words.length == 6) {
492 nullable = "not null;";
493 }
494 else if (words.length == 5) {
495 nullable = words[4];
496 }
497 else if (words.length == 4) {
498 nullable = "not null;";
499
500 if (words[3].endsWith(";")) {
501 words[3] = words[3].substring(0, words[3].length() - 1);
502 }
503 }
504
505 String[] template = {
506 words[1], words[2], "", words[3], nullable
507 };
508
509 return template;
510 }
511
512 protected String buildTemplate(String sqlDir, String fileName)
513 throws IOException {
514
515 File file = new File(sqlDir + "/" + fileName + ".sql");
516
517 String template = FileUtil.read(file);
518
519 if (fileName.equals("portal") || fileName.equals("portal-minimal") ||
520 fileName.equals("update-5.0.1-5.1.0")) {
521
522 UnsyncBufferedReader unsyncBufferedReader =
523 new UnsyncBufferedReader(new UnsyncStringReader(template));
524
525 StringBundler sb = new StringBundler();
526
527 String line = null;
528
529 while ((line = unsyncBufferedReader.readLine()) != null) {
530 if (line.startsWith("@include ")) {
531 int pos = line.indexOf(" ");
532
533 String includeFileName = line.substring(pos + 1);
534
535 File includeFile = new File(
536 sqlDir + "/" + includeFileName);
537
538 if (!includeFile.exists()) {
539 continue;
540 }
541
542 String include = FileUtil.read(includeFile);
543
544 if (includeFileName.endsWith(".vm")) {
545 try {
546 include = evaluateVM(include);
547 }
548 catch (Exception e) {
549 _log.error(e, e);
550 }
551 }
552
553 include = convertTimestamp(include);
554 include = replaceTemplate(include, getTemplate());
555
556 sb.append(include);
557 sb.append("\n\n");
558 }
559 else {
560 sb.append(line);
561 sb.append("\n");
562 }
563 }
564
565 unsyncBufferedReader.close();
566
567 template = sb.toString();
568 }
569
570 if (fileName.equals("indexes") && (this instanceof SybaseDB)) {
571 template = removeBooleanIndexes(sqlDir, template);
572 }
573
574 return template;
575 }
576
577 protected String convertTimestamp(String data) {
578 String s = null;
579
580 if (this instanceof MySQLDB) {
581 s = StringUtil.replace(data, "SPECIFIC_TIMESTAMP_", "");
582 }
583 else {
584 s = data.replaceAll(
585 "SPECIFIC_TIMESTAMP_" + "\\d+", "CURRENT_TIMESTAMP");
586 }
587
588 return s;
589 }
590
591 protected Set<String> dropIndexes(
592 String tablesSQL, String indexesSQL, String indexesProperties,
593 List<Index> indexes)
594 throws IOException, SQLException {
595
596 if (_log.isInfoEnabled()) {
597 _log.info("Dropping stale indexes");
598 }
599
600 Set<String> validIndexNames = new HashSet<String>();
601
602 if (indexes.isEmpty()) {
603 return validIndexNames;
604 }
605
606 DB db = DBFactoryUtil.getDB();
607
608 String tablesSQLLowerCase = tablesSQL.toLowerCase();
609 String indexesSQLLowerCase = indexesSQL.toLowerCase();
610
611 Properties indexesPropertiesObj = PropertiesUtil.load(
612 indexesProperties);
613
614 Enumeration<String> enu =
615 (Enumeration<String>)indexesPropertiesObj.propertyNames();
616
617 while (enu.hasMoreElements()) {
618 String key = enu.nextElement();
619
620 String value = indexesPropertiesObj.getProperty(key);
621
622 indexesPropertiesObj.setProperty(key.toLowerCase(), value);
623 }
624
625 for (Index index : indexes) {
626 String indexNameUpperCase = index.getIndexName().toUpperCase();
627 String indexNameLowerCase = indexNameUpperCase.toLowerCase();
628 String tableName = index.getTableName();
629 String tableNameLowerCase = tableName.toLowerCase();
630 boolean unique = index.isUnique();
631
632 validIndexNames.add(indexNameUpperCase);
633
634 if (indexesPropertiesObj.containsKey(indexNameLowerCase)) {
635 if (unique &&
636 indexesSQLLowerCase.contains(
637 "create unique index " + indexNameLowerCase + " ")) {
638
639 continue;
640 }
641
642 if (!unique &&
643 indexesSQLLowerCase.contains(
644 "create index " + indexNameLowerCase + " ")) {
645
646 continue;
647 }
648 }
649 else {
650 if (!tablesSQLLowerCase.contains(
651 "create table " + tableNameLowerCase + " (")) {
652
653 continue;
654 }
655 }
656
657 validIndexNames.remove(indexNameUpperCase);
658
659 db.runSQL("drop index " + indexNameUpperCase + " on " + tableName);
660 }
661
662 return validIndexNames;
663 }
664
665 protected String evaluateVM(String template) throws Exception {
666 Map<String, Object> variables = new HashMap<String, Object>();
667
668 variables.put("counter", new SimpleCounter());
669
670 template = VelocityUtil.evaluate(template, variables);
671
672
674 UnsyncBufferedReader unsyncBufferedReader = new UnsyncBufferedReader(
675 new UnsyncStringReader(template));
676
677 StringBundler sb = new StringBundler();
678
679 String line = null;
680
681 while ((line = unsyncBufferedReader.readLine()) != null) {
682 line = line.trim();
683
684 sb.append(line);
685 sb.append("\n");
686 }
687
688 unsyncBufferedReader.close();
689
690 template = sb.toString();
691 template = StringUtil.replace(template, "\n\n\n", "\n\n");
692
693 return template;
694 }
695
696 protected abstract String getServerName();
697
698 protected String getSuffix(int type) {
699 if (type == MINIMAL) {
700 return "-minimal";
701 }
702 else if (type == SHARDED) {
703 return "-sharded";
704 }
705 else {
706 return StringPool.BLANK;
707 }
708 }
709
710 protected abstract String[] getTemplate();
711
712 protected String readSQL(String fileName, String comments, String eol)
713 throws IOException {
714
715 UnsyncBufferedReader unsyncBufferedReader = new UnsyncBufferedReader(
716 new FileReader(new File(fileName)));
717
718 StringBundler sb = new StringBundler();
719
720 String line = null;
721
722 while ((line = unsyncBufferedReader.readLine()) != null) {
723 if (!line.startsWith(comments)) {
724 line = StringUtil.replace(
725 line,
726 new String[] {"\n", "\t"},
727 new String[] {"", ""});
728
729 if (line.endsWith(";")) {
730 sb.append(line.substring(0, line.length() - 1));
731 sb.append(eol);
732 }
733 else {
734 sb.append(line);
735 }
736 }
737 }
738
739 unsyncBufferedReader.close();
740
741 return sb.toString();
742 }
743
744 protected String removeBooleanIndexes(String sqlDir, String data)
745 throws IOException {
746
747 String portalData = FileUtil.read(sqlDir + "/portal-tables.sql");
748
749 UnsyncBufferedReader unsyncBufferedReader = new UnsyncBufferedReader(
750 new UnsyncStringReader(data));
751
752 StringBundler sb = new StringBundler();
753
754 String line = null;
755
756 while ((line = unsyncBufferedReader.readLine()) != null) {
757 boolean append = true;
758
759 int x = line.indexOf(" on ");
760
761 if (x != -1) {
762 int y = line.indexOf(" (", x);
763
764 String table = line.substring(x + 4, y);
765
766 x = y + 2;
767 y = line.indexOf(")", x);
768
769 String[] columns = StringUtil.split(line.substring(x, y));
770
771 x = portalData.indexOf("create table " + table + " (");
772 y = portalData.indexOf(");", x);
773
774 String portalTableData = portalData.substring(x, y);
775
776 for (int i = 0; i < columns.length; i++) {
777 if (portalTableData.indexOf(
778 columns[i].trim() + " BOOLEAN") != -1) {
779
780 append = false;
781
782 break;
783 }
784 }
785 }
786
787 if (append) {
788 sb.append(line);
789 sb.append("\n");
790 }
791 }
792
793 unsyncBufferedReader.close();
794
795 return sb.toString();
796 }
797
798 protected String removeInserts(String data) throws IOException {
799 UnsyncBufferedReader unsyncBufferedReader = new UnsyncBufferedReader(
800 new UnsyncStringReader(data));
801
802 StringBundler sb = new StringBundler();
803
804 String line = null;
805
806 while ((line = unsyncBufferedReader.readLine()) != null) {
807 if (!line.startsWith("insert into ") &&
808 !line.startsWith("update ")) {
809
810 sb.append(line);
811 sb.append("\n");
812 }
813 }
814
815 unsyncBufferedReader.close();
816
817 return sb.toString();
818 }
819
820 protected String removeLongInserts(String data) throws IOException {
821 UnsyncBufferedReader unsyncBufferedReader = new UnsyncBufferedReader(
822 new UnsyncStringReader(data));
823
824 StringBundler sb = new StringBundler();
825
826 String line = null;
827
828 while ((line = unsyncBufferedReader.readLine()) != null) {
829 if (!line.startsWith("insert into Image (") &&
830 !line.startsWith("insert into JournalArticle (") &&
831 !line.startsWith("insert into JournalStructure (") &&
832 !line.startsWith("insert into JournalTemplate (")) {
833
834 sb.append(line);
835 sb.append("\n");
836 }
837 }
838
839 unsyncBufferedReader.close();
840
841 return sb.toString();
842 }
843
844 protected String removeNull(String content) {
845 content = StringUtil.replace(content, " is null", " IS NULL");
846 content = StringUtil.replace(content, " not null", " not_null");
847 content = StringUtil.replace(content, " null", "");
848 content = StringUtil.replace(content, " not_null", " not null");
849
850 return content;
851 }
852
853 protected String replaceTemplate(String template, String[] actual) {
854 if ((template == null) || (TEMPLATE == null) || (actual == null)) {
855 return null;
856 }
857
858 if (TEMPLATE.length != actual.length) {
859 return template;
860 }
861
862 for (int i = 0; i < TEMPLATE.length; i++) {
863 if (TEMPLATE[i].equals("##") ||
864 TEMPLATE[i].equals("'01/01/1970'")) {
865
866 template = template.replaceAll(TEMPLATE[i], actual[i]);
867 }
868 else {
869 template = template.replaceAll(
870 "\\b" + TEMPLATE[i] + "\\b", actual[i]);
871 }
872 }
873
874 return template;
875 }
876
877 protected abstract String reword(String data) throws IOException;
878
879 protected static String ALTER_COLUMN_TYPE = "alter_column_type ";
880
881 protected static String ALTER_COLUMN_NAME = "alter_column_name ";
882
883 protected static String DROP_INDEX = "drop index";
884
885 protected static String DROP_PRIMARY_KEY = "drop primary key";
886
887 protected static String[] REWORD_TEMPLATE = {
888 "@table@", "@old-column@", "@new-column@", "@type@", "@nullable@"
889 };
890
891 protected static String[] TEMPLATE = {
892 "##", "TRUE", "FALSE",
893 "'01/01/1970'", "CURRENT_TIMESTAMP",
894 " BLOB", " BOOLEAN", " DATE",
895 " DOUBLE", " INTEGER", " LONG",
896 " STRING", " TEXT", " VARCHAR",
897 " IDENTITY", "COMMIT_TRANSACTION"
898 };
899
900 private static boolean _SUPPORTS_ALTER_COLUMN_NAME = true;
901
902 private static boolean _SUPPORTS_ALTER_COLUMN_TYPE = true;
903
904 private static boolean _SUPPORTS_DATE_MILLISECONDS = true;
905
906 private static boolean _SUPPORTS_SCROLLABLE_RESULTS = true;
907
908 private static boolean _SUPPORTS_UPDATE_WITH_INNER_JOIN;
909
910 private static Log _log = LogFactoryUtil.getLog(BaseDB.class);
911
912 private String _type;
913 private boolean _supportsStringCaseSensitiveQuery;
914
915 }