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