1
22
23 package com.liferay.portal.dao.db;
24
25 import com.liferay.counter.service.CounterLocalServiceUtil;
26 import com.liferay.portal.SystemException;
27 import com.liferay.portal.kernel.dao.db.DB;
28 import com.liferay.portal.kernel.dao.db.DBFactoryUtil;
29 import com.liferay.portal.kernel.dao.db.Index;
30 import com.liferay.portal.kernel.dao.jdbc.DataAccess;
31 import com.liferay.portal.kernel.io.unsync.UnsyncBufferedReader;
32 import com.liferay.portal.kernel.io.unsync.UnsyncStringReader;
33 import com.liferay.portal.kernel.log.Log;
34 import com.liferay.portal.kernel.log.LogFactoryUtil;
35 import com.liferay.portal.kernel.util.FileUtil;
36 import com.liferay.portal.kernel.util.GetterUtil;
37 import com.liferay.portal.kernel.util.PropertiesUtil;
38 import com.liferay.portal.kernel.util.StringPool;
39 import com.liferay.portal.kernel.util.StringUtil;
40 import com.liferay.portal.kernel.util.Validator;
41 import com.liferay.portal.velocity.VelocityUtil;
42 import com.liferay.util.SimpleCounter;
43
44 import java.io.File;
45 import java.io.FileReader;
46 import java.io.IOException;
47 import java.io.InputStream;
48
49 import java.sql.Connection;
50 import java.sql.SQLException;
51 import java.sql.Statement;
52
53 import java.util.Collections;
54 import java.util.Enumeration;
55 import java.util.HashMap;
56 import java.util.HashSet;
57 import java.util.List;
58 import java.util.Map;
59 import java.util.Properties;
60 import java.util.Set;
61
62 import javax.naming.NamingException;
63
64
71 public abstract class BaseDB implements DB {
72
73 public void buildCreateFile(String databaseName) throws IOException {
74 buildCreateFile(databaseName, POPULATED);
75 buildCreateFile(databaseName, MINIMAL);
76 buildCreateFile(databaseName, SHARDED);
77 }
78
79 public void buildCreateFile(String databaseName, int population)
80 throws IOException {
81
82 String suffix = getSuffix(population);
83
84 File file = new File(
85 "../sql/create" + suffix + "/create" + suffix + "-" +
86 getServerName() + ".sql");
87
88 if (population != SHARDED) {
89 String content = buildCreateFileContent(databaseName, population);
90
91 if (content != null) {
92 FileUtil.write(file, content);
93 }
94 }
95 else {
96 String content = buildCreateFileContent(databaseName, MINIMAL);
97
98 if (content != null) {
99 FileUtil.write(file, content);
100 }
101
102 content = buildCreateFileContent(databaseName + "1", MINIMAL);
103
104 if (content != null) {
105 FileUtil.write(file, content, false, true);
106 }
107
108 content = buildCreateFileContent(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 fileName) throws IOException {
119 String template = buildTemplate(fileName);
120
121 template = buildSQL(template);
122
123 FileUtil.write(
124 "../sql/" + 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
257 String template = StringUtil.read(is);
258
259 is.close();
260
261 boolean evaluate = path.endsWith(".vm");
262
263 runSQLTemplateString(template, evaluate, failOnError);
264 }
265
266 public void runSQLTemplateString(
267 String template, boolean evaluate, boolean failOnError)
268 throws IOException, NamingException, SQLException {
269
270 if (evaluate) {
271 try {
272 template = evaluateVM(template);
273 }
274 catch (Exception e) {
275 _log.error(e, e);
276 }
277 }
278
279 StringBuilder sb = new StringBuilder();
280
281 UnsyncBufferedReader unsyncBufferedReader = new UnsyncBufferedReader(
282 new UnsyncStringReader(template));
283
284 String line = null;
285
286 while ((line = unsyncBufferedReader.readLine()) != null) {
287 if (!line.startsWith("##")) {
288 if (line.startsWith("@include ")) {
289 int pos = line.indexOf(" ");
290
291 String includeFileName = line.substring(pos + 1);
292
293 Thread currentThread = Thread.currentThread();
294
295 ClassLoader classLoader =
296 currentThread.getContextClassLoader();
297
298 InputStream is = classLoader.getResourceAsStream(
299 "com/liferay/portal/tools/sql/dependencies/" +
300 includeFileName);
301
302 if (is == null) {
303 is = classLoader.getResourceAsStream(includeFileName);
304 }
305
306 String include = StringUtil.read(is);
307
308 is.close();
309
310 if (includeFileName.endsWith(".vm")) {
311 try {
312 include = evaluateVM(include);
313 }
314 catch (Exception e) {
315 _log.error(e, e);
316 }
317 }
318
319 include = convertTimestamp(include);
320 include = replaceTemplate(include, getTemplate());
321
322 runSQLTemplateString(include, false, true);
323 }
324 else{
325 sb.append(line);
326
327 if (line.endsWith(";")) {
328 String sql = sb.toString();
329
330 sb = new StringBuilder();
331
332 try {
333 if (!sql.equals("COMMIT_TRANSACTION;")) {
334 runSQL(sql);
335 }
336 else {
337 if (_log.isDebugEnabled()) {
338 _log.debug("Skip commit sql");
339 }
340 }
341 }
342 catch (IOException ioe) {
343 if (failOnError) {
344 throw ioe;
345 }
346 else if (_log.isWarnEnabled()) {
347 _log.warn(ioe.getMessage());
348 }
349 }
350 catch (SQLException sqle) {
351 if (failOnError) {
352 throw sqle;
353 }
354 else if (_log.isWarnEnabled()) {
355 String message = GetterUtil.getString(
356 sqle.getMessage());
357
358 if (!message.startsWith("Duplicate key name")) {
359 _log.warn(message + ": " + sql);
360 }
361
362 if (message.startsWith("Duplicate entry") ||
363 message.startsWith(
364 "Specified key was too long")) {
365
366 _log.error(line);
367 }
368 }
369 }
370 }
371 }
372 }
373 }
374
375 unsyncBufferedReader.close();
376 }
377
378 public void setSupportsStringCaseSensitiveQuery(
379 boolean supportsStringCaseSensitiveQuery) {
380
381 if (_log.isInfoEnabled()) {
382 if (supportsStringCaseSensitiveQuery) {
383 _log.info("Database supports case sensitive queries");
384 }
385 else {
386 _log.info("Database does not support case sensitive queries");
387 }
388 }
389
390 _supportsStringCaseSensitiveQuery = supportsStringCaseSensitiveQuery;
391 }
392
393 public void updateIndexes(
394 String tablesSQL, String indexesSQL, String indexesProperties,
395 boolean dropIndexes)
396 throws IOException, SQLException {
397
398 List<Index> indexes = getIndexes();
399
400 Set<String> validIndexNames = null;
401
402 if (dropIndexes) {
403 validIndexNames = dropIndexes(
404 tablesSQL, indexesSQL, indexesProperties, indexes);
405 }
406 else {
407 validIndexNames = new HashSet<String>();
408
409 for (Index index : indexes) {
410 String indexName = index.getIndexName().toUpperCase();
411
412 validIndexNames.add(indexName);
413 }
414 }
415
416 addIndexes(indexesSQL, validIndexNames);
417 }
418
419 protected BaseDB(String type) {
420 _type = type;
421 }
422
423 protected void addIndexes(String indexesSQL, Set<String> validIndexNames)
424 throws IOException {
425
426 if (_log.isInfoEnabled()) {
427 _log.info("Adding indexes");
428 }
429
430 DB db = DBFactoryUtil.getDB();
431
432 UnsyncBufferedReader bufferedReader = new UnsyncBufferedReader(
433 new UnsyncStringReader(indexesSQL));
434
435 String sql = null;
436
437 while ((sql = bufferedReader.readLine()) != null) {
438 if (Validator.isNull(sql)) {
439 continue;
440 }
441
442 int y = sql.indexOf(" on ");
443 int x = sql.lastIndexOf(" ", y - 1);
444
445 String indexName = sql.substring(x + 1, y);
446
447 if (validIndexNames.contains(indexName)) {
448 continue;
449 }
450
451 if (_log.isInfoEnabled()) {
452 _log.info(sql);
453 }
454
455 try {
456 db.runSQL(sql);
457 }
458 catch (Exception e) {
459 if (_log.isWarnEnabled()) {
460 _log.warn(e.getMessage());
461 }
462 }
463 }
464 }
465
466 protected abstract String buildCreateFileContent(
467 String databaseName, int population)
468 throws IOException;
469
470 protected String[] buildColumnNameTokens(String line) {
471 String[] words = StringUtil.split(line, " ");
472
473 if (words.length == 7) {
474 words[5] = "not null;";
475 }
476
477 String[] template = {
478 words[1], words[2], words[3], words[4], words[5]
479 };
480
481 return template;
482 }
483
484 protected String[] buildColumnTypeTokens(String line) {
485 String[] words = StringUtil.split(line, " ");
486
487 String nullable = "";
488
489 if (words.length == 6) {
490 nullable = "not null;";
491 }
492 else if (words.length == 5) {
493 nullable = words[4];
494 }
495 else if (words.length == 4) {
496 nullable = "not null;";
497
498 if (words[3].endsWith(";")) {
499 words[3] = words[3].substring(0, words[3].length() - 1);
500 }
501 }
502
503 String[] template = {
504 words[1], words[2], "", words[3], nullable
505 };
506
507 return template;
508 }
509
510 protected String buildTemplate(String fileName) throws IOException {
511 File file = new File("../sql/" + fileName + ".sql");
512
513 String template = FileUtil.read(file);
514
515 if (fileName.equals("portal") || fileName.equals("portal-minimal") ||
516 fileName.equals("update-5.0.1-5.1.0")) {
517
518 UnsyncBufferedReader unsyncBufferedReader =
519 new UnsyncBufferedReader(new UnsyncStringReader(template));
520
521 StringBuilder sb = new StringBuilder();
522
523 String line = null;
524
525 while ((line = unsyncBufferedReader.readLine()) != null) {
526 if (line.startsWith("@include ")) {
527 int pos = line.indexOf(" ");
528
529 String includeFileName = line.substring(pos + 1);
530
531 File includeFile = new File("../sql/" + includeFileName);
532
533 if (!includeFile.exists()) {
534 continue;
535 }
536
537 String include = FileUtil.read(includeFile);
538
539 if (includeFileName.endsWith(".vm")) {
540 try {
541 include = evaluateVM(include);
542 }
543 catch (Exception e) {
544 _log.error(e, e);
545 }
546 }
547
548 include = convertTimestamp(include);
549 include = replaceTemplate(include, getTemplate());
550
551 sb.append(include);
552 sb.append("\n\n");
553 }
554 else {
555 sb.append(line);
556 sb.append("\n");
557 }
558 }
559
560 unsyncBufferedReader.close();
561
562 template = sb.toString();
563 }
564
565 if (fileName.equals("indexes") && (this instanceof SybaseDB)) {
566 template = removeBooleanIndexes(template);
567 }
568
569 return template;
570 }
571
572 protected String convertTimestamp(String data) {
573 String s = null;
574
575 if (this instanceof MySQLDB) {
576 s = StringUtil.replace(data, "SPECIFIC_TIMESTAMP_", "");
577 }
578 else {
579 s = data.replaceAll(
580 "SPECIFIC_TIMESTAMP_" + "\\d+", "CURRENT_TIMESTAMP");
581 }
582
583 return s;
584 }
585
586 protected Set<String> dropIndexes(
587 String tablesSQL, String indexesSQL, String indexesProperties,
588 List<Index> indexes)
589 throws IOException, SQLException {
590
591 if (_log.isInfoEnabled()) {
592 _log.info("Dropping stale indexes");
593 }
594
595 Set<String> validIndexNames = new HashSet<String>();
596
597 if (indexes.isEmpty()) {
598 return validIndexNames;
599 }
600
601 DB db = DBFactoryUtil.getDB();
602
603 String type = db.getType();
604
605 String tablesSQLLowerCase = tablesSQL.toLowerCase();
606 String indexesSQLLowerCase = indexesSQL.toLowerCase();
607
608 Properties indexesPropertiesObj = PropertiesUtil.load(
609 indexesProperties);
610
611 Enumeration<String> enu =
612 (Enumeration<String>)indexesPropertiesObj.propertyNames();
613
614 while (enu.hasMoreElements()) {
615 String key = enu.nextElement();
616
617 String value = indexesPropertiesObj.getProperty(key);
618
619 indexesPropertiesObj.setProperty(key.toLowerCase(), value);
620 }
621
622 for (Index index : indexes) {
623 String indexNameUpperCase = index.getIndexName().toUpperCase();
624 String indexNameLowerCase = indexNameUpperCase.toLowerCase();
625 String tableName = index.getTableName();
626 String tableNameLowerCase = tableName.toLowerCase();
627 boolean unique = index.isUnique();
628
629 validIndexNames.add(indexNameUpperCase);
630
631 if (indexesPropertiesObj.containsKey(indexNameLowerCase)) {
632 if (unique &&
633 indexesSQLLowerCase.contains(
634 "create unique index " + indexNameLowerCase + " ")) {
635
636 continue;
637 }
638
639 if (!unique &&
640 indexesSQLLowerCase.contains(
641 "create index " + indexNameLowerCase + " ")) {
642
643 continue;
644 }
645 }
646 else {
647 if (!tablesSQLLowerCase.contains(
648 "create table " + tableNameLowerCase + " (")) {
649
650 continue;
651 }
652 }
653
654 validIndexNames.remove(indexNameUpperCase);
655
656 String sql = "drop index " + indexNameUpperCase;
657
658 if (type.equals(DB.TYPE_MYSQL) || type.equals(DB.TYPE_SQLSERVER)) {
659 sql += " on " + tableName;
660 }
661
662 if (_log.isInfoEnabled()) {
663 _log.info(sql);
664 }
665
666 db.runSQL(sql);
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 StringBuilder sb = new StringBuilder();
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 StringBuilder sb = new StringBuilder();
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 data) throws IOException {
752 String portalData = FileUtil.read("../sql/portal-tables.sql");
753
754 UnsyncBufferedReader unsyncBufferedReader = new UnsyncBufferedReader(
755 new UnsyncStringReader(data));
756
757 StringBuilder sb = new StringBuilder();
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 StringBuilder sb = new StringBuilder();
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 StringBuilder sb = new StringBuilder();
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_PRIMARY_KEY = "drop primary key";
889
890 protected static String[] REWORD_TEMPLATE = {
891 "@table@", "@old-column@", "@new-column@", "@type@", "@nullable@"
892 };
893
894 protected static String[] TEMPLATE = {
895 "##", "TRUE", "FALSE",
896 "'01/01/1970'", "CURRENT_TIMESTAMP",
897 " BLOB", " BOOLEAN", " DATE",
898 " DOUBLE", " INTEGER", " LONG",
899 " STRING", " TEXT", " VARCHAR",
900 " IDENTITY", "COMMIT_TRANSACTION"
901 };
902
903 private static boolean _SUPPORTS_ALTER_COLUMN_NAME = true;
904
905 private static boolean _SUPPORTS_ALTER_COLUMN_TYPE = true;
906
907 private static boolean _SUPPORTS_DATE_MILLISECONDS = true;
908
909 private static boolean _SUPPORTS_SCROLLABLE_RESULTS = true;
910
911 private static boolean _SUPPORTS_UPDATE_WITH_INNER_JOIN;
912
913 private static Log _log = LogFactoryUtil.getLog(BaseDB.class);
914
915 private String _type;
916 private boolean _supportsStringCaseSensitiveQuery;
917
918 }