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