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 sb.append(line);
357
358 if (line.endsWith(";")) {
359 String sql = sb.toString();
360
361 sb = new StringBuilder();
362
363 try {
364 if (!sql.equals("COMMIT_TRANSACTION;")) {
365 runSQL(sql);
366 }
367 else {
368 if (_log.isDebugEnabled()) {
369 _log.debug("Skip commit sql");
370 }
371 }
372 }
373 catch (IOException ioe) {
374 if (failOnError) {
375 throw ioe;
376 }
377 else if (_log.isWarnEnabled()) {
378 _log.warn(ioe.getMessage());
379 }
380 }
381 catch (SQLException sqle) {
382 if (failOnError) {
383 throw sqle;
384 }
385 else if (_log.isWarnEnabled()) {
386 _log.warn(sqle.getMessage());
387 }
388 }
389 }
390 }
391 }
392
393 br.close();
394 }
395
396 protected abstract void buildCreateFile(
397 String databaseName, boolean minimal)
398 throws IOException;
399
400 protected String[] buildColumnNameTokens(String line) {
401 String[] words = StringUtil.split(line, " ");
402
403 if (words.length == 7) {
404 words[5] = "not null;";
405 }
406
407 String[] template = {
408 words[1], words[2], words[3], words[4], words[5]
409 };
410
411 return template;
412 }
413
414 protected String[] buildColumnTypeTokens(String line) {
415 String[] words = StringUtil.split(line, " ");
416
417 String nullable = "";
418
419 if (words.length == 6) {
420 nullable = "not null;";
421 }
422 else if (words.length == 5) {
423 nullable = words[4];
424 }
425 else if (words.length == 4) {
426 nullable = "not null;";
427
428 if (words[3].endsWith(";")) {
429 words[3] = words[3].substring(0, words[3].length() - 1);
430 }
431 }
432
433 String[] template = {
434 words[1], words[2], "", words[3], nullable
435 };
436
437 return template;
438 }
439
440 protected String buildTemplate(String fileName) throws IOException {
441 File file = new File("../sql/" + fileName + ".sql");
442
443 String template = FileUtil.read(file);
444
445 if (fileName.equals("portal") || fileName.equals("portal-minimal") ||
446 fileName.equals("update-5.0.1-5.1.0")) {
447
448 BufferedReader br = new BufferedReader(new StringReader(template));
449
450 StringBuilder sb = new StringBuilder();
451
452 String line = null;
453
454 while ((line = br.readLine()) != null) {
455 if (line.startsWith("@include ")) {
456 int pos = line.indexOf(" ");
457
458 String includeFileName =
459 line.substring(pos + 1, line.length());
460
461 File includeFile = new File("../sql/" + includeFileName);
462
463 if (!includeFile.exists()) {
464 continue;
465 }
466
467 String include = FileUtil.read(includeFile);
468
469 if (includeFileName.endsWith(".vm")) {
470 try {
471 include = evaluateVM(include);
472 }
473 catch (Exception e) {
474 e.printStackTrace();
475 }
476 }
477
478 include = convertTimestamp(include);
479 include = StringUtil.replace(
480 include, TEMPLATE, getTemplate());
481
482 sb.append(include);
483 sb.append("\n\n");
484 }
485 else {
486 sb.append(line);
487 sb.append("\n");
488 }
489 }
490
491 br.close();
492
493 template = sb.toString();
494 }
495
496 if (fileName.equals("indexes") && (this instanceof SybaseUtil)) {
497 template = removeBooleanIndexes(template);
498 }
499
500 return template;
501 }
502
503 protected String convertTimestamp(String data) {
504 String s = null;
505
506 if (this instanceof MySQLUtil) {
507 s = StringUtil.replace(data, "SPECIFIC_TIMESTAMP_", "");
508 }
509 else {
510 s = data.replaceAll(
511 "SPECIFIC_TIMESTAMP_" + "\\d+", "CURRENT_TIMESTAMP");
512 }
513
514 return s;
515 }
516
517 protected String evaluateVM(String template) throws Exception {
518 Map<String, Object> variables = new HashMap<String, Object>();
519
520 variables.put("counter", new SimpleCounter());
521
522 template = VelocityUtil.evaluate(template, variables);
523
524
526 BufferedReader br = new BufferedReader(new StringReader(template));
527
528 StringBuilder sb = new StringBuilder();
529
530 String line = null;
531
532 while ((line = br.readLine()) != null) {
533 line = line.trim();
534
535 sb.append(line);
536 sb.append("\n");
537 }
538
539 br.close();
540
541 template = sb.toString();
542 template = StringUtil.replace(template, "\n\n\n", "\n\n");
543
544 return template;
545 }
546
547 protected String getMinimalSuffix(boolean minimal) {
548 if (minimal) {
549 return "-minimal";
550 }
551 else {
552 return StringPool.BLANK;
553 }
554 }
555
556 protected abstract String getServerName();
557
558 protected abstract String[] getTemplate();
559
560 protected String readSQL(String fileName, String comments, String eol)
561 throws IOException {
562
563 BufferedReader br = new BufferedReader(
564 new FileReader(new File(fileName)));
565
566 StringBuilder sb = new StringBuilder();
567
568 String line = null;
569
570 while ((line = br.readLine()) != null) {
571 if (!line.startsWith(comments)) {
572 line = StringUtil.replace(
573 line,
574 new String[] {"\n", "\t"},
575 new String[] {"", ""});
576
577 if (line.endsWith(";")) {
578 sb.append(line.substring(0, line.length() - 1));
579 sb.append(eol);
580 }
581 else {
582 sb.append(line);
583 }
584 }
585 }
586
587 br.close();
588
589 return sb.toString();
590 }
591
592 protected String removeBooleanIndexes(String data) throws IOException {
593 String portalData = FileUtil.read("../sql/portal-tables.sql");
594
595 BufferedReader br = new BufferedReader(new StringReader(data));
596
597 StringBuilder sb = new StringBuilder();
598
599 String line = null;
600
601 while ((line = br.readLine()) != null) {
602 boolean append = true;
603
604 int x = line.indexOf(" on ");
605
606 if (x != -1) {
607 int y = line.indexOf(" (", x);
608
609 String table = line.substring(x + 4, y);
610
611 x = y + 2;
612 y = line.indexOf(")", x);
613
614 String[] columns = StringUtil.split(line.substring(x, y));
615
616 x = portalData.indexOf("create table " + table + " (");
617 y = portalData.indexOf(");", x);
618
619 String portalTableData = portalData.substring(x, y);
620
621 for (int i = 0; i < columns.length; i++) {
622 if (portalTableData.indexOf(
623 columns[i].trim() + " BOOLEAN") != -1) {
624
625 append = false;
626
627 break;
628 }
629 }
630 }
631
632 if (append) {
633 sb.append(line);
634 sb.append("\n");
635 }
636 }
637
638 br.close();
639
640 return sb.toString();
641 }
642
643 protected String removeInserts(String data) throws IOException {
644 BufferedReader br = new BufferedReader(new StringReader(data));
645
646 StringBuilder sb = new StringBuilder();
647
648 String line = null;
649
650 while ((line = br.readLine()) != null) {
651 if (!line.startsWith("insert into ") &&
652 !line.startsWith("update ")) {
653
654 sb.append(line);
655 sb.append("\n");
656 }
657 }
658
659 br.close();
660
661 return sb.toString();
662 }
663
664 protected String removeLongInserts(String data) throws IOException {
665 BufferedReader br = new BufferedReader(new StringReader(data));
666
667 StringBuilder sb = new StringBuilder();
668
669 String line = null;
670
671 while ((line = br.readLine()) != null) {
672 if (!line.startsWith("insert into Image (") &&
673 !line.startsWith("insert into JournalArticle (") &&
674 !line.startsWith("insert into JournalStructure (") &&
675 !line.startsWith("insert into JournalTemplate (")) {
676
677 sb.append(line);
678 sb.append("\n");
679 }
680 }
681
682 br.close();
683
684 return sb.toString();
685 }
686
687 protected String removeNull(String content) {
688 content = StringUtil.replace(content, " not null", " not_null");
689 content = StringUtil.replace(content, " null", "");
690 content = StringUtil.replace(content, " not_null", " not null");
691
692 return content;
693 }
694
695 protected abstract String reword(String data) throws IOException;
696
697 protected static String ALTER_COLUMN_TYPE = "alter_column_type ";
698
699 protected static String ALTER_COLUMN_NAME = "alter_column_name ";
700
701 protected static String DROP_PRIMARY_KEY = "drop primary key";
702
703 protected static String[] REWORD_TEMPLATE = {
704 "@table@", "@old-column@", "@new-column@", "@type@", "@nullable@"
705 };
706
707 protected static String[] TEMPLATE = {
708 "##", "TRUE", "FALSE",
709 "'01/01/1970'", "CURRENT_TIMESTAMP",
710 " BLOB", " BOOLEAN", " DATE",
711 " DOUBLE", " INTEGER", " LONG",
712 " STRING", " TEXT", " VARCHAR",
713 " IDENTITY", "COMMIT_TRANSACTION"
714 };
715
716 private static Log _log = LogFactory.getLog(DBUtil.class);
717
718 private static DBUtil _dbUtil;
719
720 }