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 String getTemplateFalse() {
237 return getTemplate()[2];
238 }
239
240 public String getTemplateTrue() {
241 return getTemplate()[1];
242 }
243
244 public void runSQL(String sql) throws IOException, SQLException {
245 runSQL(new String[] {sql});
246 }
247
248 public void runSQL(String[] sqls)
249 throws IOException, SQLException {
250
251 Connection con = null;
252 Statement stmt = null;
253
254 try {
255 con = HibernateUtil.getConnection();
256
257 stmt = con.createStatement();
258
259 for (int i = 0; i < sqls.length; i++) {
260 String sql = buildSQL(sqls[i]);
261
262 sql = sql.trim();
263
264 if (sql.endsWith(";")) {
265 sql = sql.substring(0, sql.length() - 1);
266 }
267
268 if (sql.endsWith("go")) {
269 sql = sql.substring(0, sql.length() - 2);
270 }
271
272 if (_log.isDebugEnabled()) {
273 _log.debug(sql);
274 }
275
276 try {
277 stmt.executeUpdate(sql);
278 }
279 catch (SQLException sqle) {
280 throw sqle;
281 }
282 }
283 }
284 finally {
285 DataAccess.cleanUp(con, stmt);
286 }
287 }
288
289 public void runSQLTemplate(String path) throws IOException, SQLException {
290 runSQLTemplate(path, true);
291 }
292
293 public void runSQLTemplate(String path, boolean failOnError)
294 throws IOException, SQLException {
295
296 ClassLoader classLoader = getClass().getClassLoader();
297
298 InputStream is = classLoader.getResourceAsStream(
299 "com/liferay/portal/tools/sql/dependencies/" + path);
300
301 if (is == null) {
302 is = classLoader.getResourceAsStream(path);
303 }
304
305 String template = StringUtil.read(is);
306
307 is.close();
308
309 boolean evaluate = path.endsWith(".vm");
310
311 runSQLTemplateString(template, evaluate, failOnError);
312 }
313
314 public void runSQLTemplateString(
315 String template, boolean evaluate, boolean failOnError)
316 throws IOException, SQLException {
317
318 if (evaluate) {
319 try {
320 template = evaluateVM(template);
321 }
322 catch (Exception e) {
323 _log.error(e, e);
324 }
325 }
326
327 StringMaker sm = new StringMaker();
328
329 BufferedReader br = new BufferedReader(new StringReader(template));
330
331 String line = null;
332
333 while ((line = br.readLine()) != null) {
334 if (!line.startsWith("##")) {
335 sm.append(line);
336
337 if (line.endsWith(";")) {
338 String sql = sm.toString();
339
340 sm = new StringMaker();
341
342 try {
343 if (!sql.equals("COMMIT_TRANSACTION;")) {
344 runSQL(sql);
345 }
346 else {
347 if (_log.isDebugEnabled()) {
348 _log.debug("Skip commit sql");
349 }
350 }
351 }
352 catch (IOException ioe) {
353 if (failOnError) {
354 throw ioe;
355 }
356 else if (_log.isWarnEnabled()) {
357 _log.warn(ioe.getMessage());
358 }
359 }
360 catch (SQLException sqle) {
361 if (failOnError) {
362 throw sqle;
363 }
364 else if (_log.isWarnEnabled()) {
365 _log.warn(sqle.getMessage());
366 }
367 }
368 }
369 }
370 }
371
372 br.close();
373 }
374
375 protected abstract void buildCreateFile(
376 String databaseName, boolean minimal)
377 throws IOException;
378
379 protected String[] buildColumnNameTokens(String line) {
380 String[] words = StringUtil.split(line, " ");
381
382 if (words.length == 7) {
383 words[5] = "not null;";
384 }
385
386 String[] template = {
387 words[1], words[2], words[3], words[4], words[5]
388 };
389
390 return template;
391 }
392
393 protected String[] buildColumnTypeTokens(String line) {
394 String[] words = StringUtil.split(line, " ");
395
396 String nullable = "";
397
398 if (words.length == 6) {
399 nullable = "not null;";
400 }
401 else if (words.length == 5) {
402 nullable = words[4];
403 }
404 else if (words.length == 4) {
405 nullable = "not null;";
406
407 if (words[3].endsWith(";")) {
408 words[3] = words[3].substring(0, words[3].length() - 1);
409 }
410 }
411
412 String[] template = {
413 words[1], words[2], "", words[3], nullable
414 };
415
416 return template;
417 }
418
419 protected String buildTemplate(String fileName) throws IOException {
420 File file = new File("../sql/" + fileName + ".sql");
421
422 String template = FileUtil.read(file);
423
424 if (fileName.equals("portal") || fileName.equals("portal-minimal") ||
425 fileName.equals("update-3.6.0-4.0.0")) {
426
427 BufferedReader br = new BufferedReader(new StringReader(template));
428
429 StringMaker sm = new StringMaker();
430
431 String line = null;
432
433 while ((line = br.readLine()) != null) {
434 if (line.startsWith("@include ")) {
435 int pos = line.indexOf(" ");
436
437 String includeFileName =
438 line.substring(pos + 1, line.length());
439
440 File includeFile = new File("../sql/" + includeFileName);
441
442 if (!includeFile.exists()) {
443 continue;
444 }
445
446 String include = FileUtil.read(includeFile);
447
448 if (includeFileName.endsWith(".vm")) {
449 try {
450 include = evaluateVM(include);
451 }
452 catch (Exception e) {
453 e.printStackTrace();
454 }
455 }
456
457 include = convertTimestamp(include);
458 include =
459 StringUtil.replace(include, TEMPLATE, getTemplate());
460
461 sm.append(include);
462 sm.append("\n\n");
463 }
464 else {
465 sm.append(line);
466 sm.append("\n");
467 }
468 }
469
470 br.close();
471
472 template = sm.toString();
473 }
474
475 if (fileName.equals("indexes") && (this instanceof SybaseUtil)) {
476 template = removeBooleanIndexes(template);
477 }
478
479 return template;
480 }
481
482 protected String convertTimestamp(String data) {
483 String s = null;
484
485 if (this instanceof MySQLUtil) {
486 s = StringUtil.replace(data, "SPECIFIC_TIMESTAMP_", "");
487 }
488 else {
489 s = data.replaceAll(
490 "SPECIFIC_TIMESTAMP_" + "\\d+", "CURRENT_TIMESTAMP");
491 }
492
493 return s;
494 }
495
496 protected String evaluateVM(String template) throws Exception {
497 Map<String, Object> variables = new HashMap<String, Object>();
498
499 variables.put("counter", new SimpleCounter());
500
501 template = VelocityUtil.evaluate(template, variables);
502
503
505 BufferedReader br = new BufferedReader(new StringReader(template));
506
507 StringMaker sm = new StringMaker();
508
509 String line = null;
510
511 while ((line = br.readLine()) != null) {
512 line = line.trim();
513
514 sm.append(line);
515 sm.append("\n");
516 }
517
518 br.close();
519
520 template = sm.toString();
521 template = StringUtil.replace(template, "\n\n\n", "\n\n");
522
523 return template;
524 }
525
526 protected String getMinimalSuffix(boolean minimal) {
527 if (minimal) {
528 return "-minimal";
529 }
530 else {
531 return StringPool.BLANK;
532 }
533 }
534
535 protected abstract String getServerName();
536
537 protected abstract String[] getTemplate();
538
539 protected String readSQL(String fileName, String comments, String eol)
540 throws IOException {
541
542 BufferedReader br = new BufferedReader(
543 new FileReader(new File(fileName)));
544
545 StringMaker sm = new StringMaker();
546
547 String line = null;
548
549 while ((line = br.readLine()) != null) {
550 if (!line.startsWith(comments)) {
551 line = StringUtil.replace(
552 line,
553 new String[] {"\n", "\t"},
554 new String[] {"", ""});
555
556 if (line.endsWith(";")) {
557 sm.append(line.substring(0, line.length() - 1));
558 sm.append(eol);
559 }
560 else {
561 sm.append(line);
562 }
563 }
564 }
565
566 br.close();
567
568 return sm.toString();
569 }
570
571 protected String removeBooleanIndexes(String data) throws IOException {
572 String portalData = FileUtil.read("../sql/portal-tables.sql");
573
574 BufferedReader br = new BufferedReader(new StringReader(data));
575
576 StringMaker sm = new StringMaker();
577
578 String line = null;
579
580 while ((line = br.readLine()) != null) {
581 boolean append = true;
582
583 int x = line.indexOf(" on ");
584
585 if (x != -1) {
586 int y = line.indexOf(" (", x);
587
588 String table = line.substring(x + 4, y);
589
590 x = y + 2;
591 y = line.indexOf(")", x);
592
593 String[] columns = StringUtil.split(line.substring(x, y));
594
595 x = portalData.indexOf("create table " + table + " (");
596 y = portalData.indexOf(");", x);
597
598 String portalTableData = portalData.substring(x, y);
599
600 for (int i = 0; i < columns.length; i++) {
601 if (portalTableData.indexOf(
602 columns[i].trim() + " BOOLEAN") != -1) {
603
604 append = false;
605
606 break;
607 }
608 }
609 }
610
611 if (append) {
612 sm.append(line);
613 sm.append("\n");
614 }
615 }
616
617 br.close();
618
619 return sm.toString();
620 }
621
622 protected String removeInserts(String data) throws IOException {
623 BufferedReader br = new BufferedReader(new StringReader(data));
624
625 StringMaker sm = new StringMaker();
626
627 String line = null;
628
629 while ((line = br.readLine()) != null) {
630 if (!line.startsWith("insert into ") &&
631 !line.startsWith("update ")) {
632
633 sm.append(line);
634 sm.append("\n");
635 }
636 }
637
638 br.close();
639
640 return sm.toString();
641 }
642
643 protected String removeLongInserts(String data) throws IOException {
644 BufferedReader br = new BufferedReader(new StringReader(data));
645
646 StringMaker sm = new StringMaker();
647
648 String line = null;
649
650 while ((line = br.readLine()) != null) {
651 if (!line.startsWith("insert into Image (") &&
652 !line.startsWith("insert into JournalArticle (") &&
653 !line.startsWith("insert into JournalStructure (") &&
654 !line.startsWith("insert into JournalTemplate (")) {
655
656 sm.append(line);
657 sm.append("\n");
658 }
659 }
660
661 br.close();
662
663 return sm.toString();
664 }
665
666 protected String removeNull(String content) {
667 content = StringUtil.replace(content, " not null", " not_null");
668 content = StringUtil.replace(content, " null", "");
669 content = StringUtil.replace(content, " not_null", " not null");
670
671 return content;
672 }
673
674 protected abstract String reword(String data) throws IOException;
675
676 protected static String ALTER_COLUMN_TYPE = "alter_column_type ";
677
678 protected static String ALTER_COLUMN_NAME = "alter_column_name ";
679
680 protected static String DROP_PRIMARY_KEY = "drop primary key";
681
682 protected static String[] REWORD_TEMPLATE = {
683 "@table@", "@old-column@", "@new-column@", "@type@", "@nullable@"
684 };
685
686 protected static String[] TEMPLATE = {
687 "##", "TRUE", "FALSE",
688 "'01/01/1970'", "CURRENT_TIMESTAMP",
689 " BOOLEAN", " DATE", " DOUBLE",
690 " INTEGER", " LONG",
691 " STRING", " TEXT", " VARCHAR",
692 " IDENTITY", "COMMIT_TRANSACTION"
693 };
694
695 private static Log _log = LogFactory.getLog(DBUtil.class);
696
697 private static DBUtil _dbUtil;
698
699 }