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