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