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