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