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