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