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