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