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