1
22
23 package com.liferay.portal.dao.db;
24
25 import com.liferay.portal.kernel.dao.db.DB;
26 import com.liferay.portal.kernel.dao.jdbc.DataAccess;
27 import com.liferay.portal.kernel.log.Log;
28 import com.liferay.portal.kernel.log.LogFactoryUtil;
29 import com.liferay.portal.kernel.util.FileUtil;
30 import com.liferay.portal.kernel.util.GetterUtil;
31 import com.liferay.portal.kernel.util.StringPool;
32 import com.liferay.portal.kernel.util.StringUtil;
33 import com.liferay.portal.velocity.VelocityUtil;
34 import com.liferay.util.SimpleCounter;
35
36 import java.io.BufferedReader;
37 import java.io.File;
38 import java.io.FileReader;
39 import java.io.IOException;
40 import java.io.InputStream;
41 import java.io.StringReader;
42
43 import java.sql.Connection;
44 import java.sql.SQLException;
45 import java.sql.Statement;
46
47 import java.util.HashMap;
48 import java.util.Map;
49
50 import javax.naming.NamingException;
51
52
59 public abstract class BaseDB implements DB {
60
61 public void buildCreateFile(String databaseName) throws IOException {
62 buildCreateFile(databaseName, POPULATED);
63 buildCreateFile(databaseName, MINIMAL);
64 buildCreateFile(databaseName, SHARDED);
65 }
66
67 public void buildCreateFile(String databaseName, int population)
68 throws IOException {
69
70 String suffix = getSuffix(population);
71
72 File file = new File(
73 "../sql/create" + suffix + "/create" + suffix + "-" +
74 getServerName() + ".sql");
75
76 if (population != SHARDED) {
77 String content = buildCreateFileContent(databaseName, population);
78
79 if (content != null) {
80 FileUtil.write(file, content);
81 }
82 }
83 else {
84 String content = buildCreateFileContent(databaseName, MINIMAL);
85
86 if (content != null) {
87 FileUtil.write(file, content);
88 }
89
90 content = buildCreateFileContent(databaseName + "1", MINIMAL);
91
92 if (content != null) {
93 FileUtil.write(file, content, false, true);
94 }
95
96 content = buildCreateFileContent(databaseName + "2", MINIMAL);
97
98 if (content != null) {
99 FileUtil.write(file, content, false, true);
100 }
101 }
102 }
103
104 public abstract String buildSQL(String template) throws IOException;
105
106 public void buildSQLFile(String fileName) throws IOException {
107 String template = buildTemplate(fileName);
108
109 template = buildSQL(template);
110
111 FileUtil.write(
112 "../sql/" + fileName + "/" + fileName + "-" + getServerName() +
113 ".sql",
114 template);
115 }
116
117 public String getTemplateFalse() {
118 return getTemplate()[2];
119 }
120
121 public String getTemplateTrue() {
122 return getTemplate()[1];
123 }
124
125 public String getType() {
126 return _type;
127 }
128
129 public boolean isSupportsAlterColumnName() {
130 return _SUPPORTS_ALTER_COLUMN_NAME;
131 }
132
133 public boolean isSupportsAlterColumnType() {
134 return _SUPPORTS_ALTER_COLUMN_TYPE;
135 }
136
137 public boolean isSupportsDateMilliseconds() {
138 return _SUPPORTS_DATE_MILLISECONDS;
139 }
140
141 public boolean isSupportsScrollableResults() {
142 return _SUPPORTS_SCROLLABLE_RESULTS;
143 }
144
145 public boolean isSupportsStringCaseSensitiveQuery() {
146 return _supportsStringCaseSensitiveQuery;
147 }
148
149 public boolean isSupportsUpdateWithInnerJoin() {
150 return _SUPPORTS_UPDATE_WITH_INNER_JOIN;
151 }
152
153 public void runSQL(String sql) throws IOException, SQLException {
154 runSQL(new String[] {sql});
155 }
156
157 public void runSQL(Connection con, String sql)
158 throws IOException, SQLException {
159
160 runSQL(con, new String[] {sql});
161 }
162
163 public void runSQL(String[] sqls) throws IOException, SQLException {
164 Connection con = DataAccess.getConnection();
165
166 try {
167 runSQL(con, sqls);
168 }
169 finally {
170 DataAccess.cleanUp(con);
171 }
172 }
173
174 public void runSQL(Connection con, String[] sqls)
175 throws IOException, SQLException {
176
177 Statement s = null;
178
179 try {
180 s = con.createStatement();
181
182 for (int i = 0; i < sqls.length; i++) {
183 String sql = buildSQL(sqls[i]);
184
185 sql = sql.trim();
186
187 if (sql.endsWith(";")) {
188 sql = sql.substring(0, sql.length() - 1);
189 }
190
191 if (sql.endsWith("go")) {
192 sql = sql.substring(0, sql.length() - 2);
193 }
194
195 if (_log.isDebugEnabled()) {
196 _log.debug(sql);
197 }
198
199 try {
200 s.executeUpdate(sql);
201 }
202 catch (SQLException sqle) {
203 throw sqle;
204 }
205 }
206 }
207 finally {
208 DataAccess.cleanUp(s);
209 }
210 }
211
212 public void runSQLTemplate(String path)
213 throws IOException, NamingException, SQLException {
214
215 runSQLTemplate(path, true);
216 }
217
218 public void runSQLTemplate(String path, boolean failOnError)
219 throws IOException, NamingException, SQLException {
220
221 Thread currentThread = Thread.currentThread();
222
223 ClassLoader classLoader = currentThread.getContextClassLoader();
224
225 InputStream is = classLoader.getResourceAsStream(
226 "com/liferay/portal/tools/sql/dependencies/" + path);
227
228 if (is == null) {
229 is = classLoader.getResourceAsStream(path);
230 }
231
232 if (is == null) {
233 _log.error("Invalid path " + path);
234 }
235
236 String template = StringUtil.read(is);
237
238 is.close();
239
240 boolean evaluate = path.endsWith(".vm");
241
242 runSQLTemplateString(template, evaluate, failOnError);
243 }
244
245 public void runSQLTemplateString(
246 String template, boolean evaluate, boolean failOnError)
247 throws IOException, NamingException, SQLException {
248
249 if (evaluate) {
250 try {
251 template = evaluateVM(template);
252 }
253 catch (Exception e) {
254 _log.error(e, e);
255 }
256 }
257
258 StringBuilder sb = new StringBuilder();
259
260 BufferedReader br = new BufferedReader(new StringReader(template));
261
262 String line = null;
263
264 while ((line = br.readLine()) != null) {
265 if (!line.startsWith("##")) {
266 if (line.startsWith("@include ")) {
267 int pos = line.indexOf(" ");
268
269 String includeFileName = line.substring(pos + 1);
270
271 Thread currentThread = Thread.currentThread();
272
273 ClassLoader classLoader =
274 currentThread.getContextClassLoader();
275
276 InputStream is = classLoader.getResourceAsStream(
277 "com/liferay/portal/tools/sql/dependencies/" +
278 includeFileName);
279
280 if (is == null) {
281 is = classLoader.getResourceAsStream(includeFileName);
282 }
283
284 String include = StringUtil.read(is);
285
286 is.close();
287
288 if (includeFileName.endsWith(".vm")) {
289 try {
290 include = evaluateVM(include);
291 }
292 catch (Exception e) {
293 _log.error(e, e);
294 }
295 }
296
297 include = convertTimestamp(include);
298 include = replaceTemplate(include, getTemplate());
299
300 runSQLTemplateString(include, false, true);
301 }
302 else{
303 sb.append(line);
304
305 if (line.endsWith(";")) {
306 String sql = sb.toString();
307
308 sb = new StringBuilder();
309
310 try {
311 if (!sql.equals("COMMIT_TRANSACTION;")) {
312 runSQL(sql);
313 }
314 else {
315 if (_log.isDebugEnabled()) {
316 _log.debug("Skip commit sql");
317 }
318 }
319 }
320 catch (IOException ioe) {
321 if (failOnError) {
322 throw ioe;
323 }
324 else if (_log.isWarnEnabled()) {
325 _log.warn(ioe.getMessage());
326 }
327 }
328 catch (SQLException sqle) {
329 if (failOnError) {
330 throw sqle;
331 }
332 else if (_log.isWarnEnabled()) {
333 String message = GetterUtil.getString(
334 sqle.getMessage());
335
336 if (!message.startsWith("Duplicate key name")) {
337 _log.warn(message + ": " + sql);
338 }
339
340 if (message.startsWith("Duplicate entry") ||
341 message.startsWith(
342 "Specified key was too long")) {
343
344 _log.error(line);
345 }
346 }
347 }
348 }
349 }
350 }
351 }
352
353 br.close();
354 }
355
356 public void setSupportsStringCaseSensitiveQuery(
357 boolean supportsStringCaseSensitiveQuery) {
358
359 if (_log.isInfoEnabled()) {
360 if (supportsStringCaseSensitiveQuery) {
361 _log.info("Database supports case sensitive queries");
362 }
363 else {
364 _log.info("Database does not support case sensitive queries");
365 }
366 }
367
368 _supportsStringCaseSensitiveQuery = supportsStringCaseSensitiveQuery;
369 }
370
371 protected BaseDB(String type) {
372 _type = type;
373 }
374
375 protected abstract String buildCreateFileContent(
376 String databaseName, int population)
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-5.0.1-5.1.0")) {
426
427 BufferedReader br = new BufferedReader(new StringReader(template));
428
429 StringBuilder sb = new StringBuilder();
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 = line.substring(pos + 1);
438
439 File includeFile = new File("../sql/" + includeFileName);
440
441 if (!includeFile.exists()) {
442 continue;
443 }
444
445 String include = FileUtil.read(includeFile);
446
447 if (includeFileName.endsWith(".vm")) {
448 try {
449 include = evaluateVM(include);
450 }
451 catch (Exception e) {
452 _log.error(e, e);
453 }
454 }
455
456 include = convertTimestamp(include);
457 include = replaceTemplate(include, getTemplate());
458
459 sb.append(include);
460 sb.append("\n\n");
461 }
462 else {
463 sb.append(line);
464 sb.append("\n");
465 }
466 }
467
468 br.close();
469
470 template = sb.toString();
471 }
472
473 if (fileName.equals("indexes") && (this instanceof SybaseDB)) {
474 template = removeBooleanIndexes(template);
475 }
476
477 return template;
478 }
479
480 protected String convertTimestamp(String data) {
481 String s = null;
482
483 if (this instanceof MySQLDB) {
484 s = StringUtil.replace(data, "SPECIFIC_TIMESTAMP_", "");
485 }
486 else {
487 s = data.replaceAll(
488 "SPECIFIC_TIMESTAMP_" + "\\d+", "CURRENT_TIMESTAMP");
489 }
490
491 return s;
492 }
493
494 protected String evaluateVM(String template) throws Exception {
495 Map<String, Object> variables = new HashMap<String, Object>();
496
497 variables.put("counter", new SimpleCounter());
498
499 template = VelocityUtil.evaluate(template, variables);
500
501
503 BufferedReader br = new BufferedReader(new StringReader(template));
504
505 StringBuilder sb = new StringBuilder();
506
507 String line = null;
508
509 while ((line = br.readLine()) != null) {
510 line = line.trim();
511
512 sb.append(line);
513 sb.append("\n");
514 }
515
516 br.close();
517
518 template = sb.toString();
519 template = StringUtil.replace(template, "\n\n\n", "\n\n");
520
521 return template;
522 }
523
524 protected abstract String getServerName();
525
526 protected String getSuffix(int type) {
527 if (type == MINIMAL) {
528 return "-minimal";
529 }
530 else if (type == SHARDED) {
531 return "-sharded";
532 }
533 else {
534 return StringPool.BLANK;
535 }
536 }
537
538 protected abstract String[] getTemplate();
539
540 protected String readSQL(String fileName, String comments, String eol)
541 throws IOException {
542
543 BufferedReader br = new BufferedReader(
544 new FileReader(new File(fileName)));
545
546 StringBuilder sb = new StringBuilder();
547
548 String line = null;
549
550 while ((line = br.readLine()) != null) {
551 if (!line.startsWith(comments)) {
552 line = StringUtil.replace(
553 line,
554 new String[] {"\n", "\t"},
555 new String[] {"", ""});
556
557 if (line.endsWith(";")) {
558 sb.append(line.substring(0, line.length() - 1));
559 sb.append(eol);
560 }
561 else {
562 sb.append(line);
563 }
564 }
565 }
566
567 br.close();
568
569 return sb.toString();
570 }
571
572 protected String removeBooleanIndexes(String data) throws IOException {
573 String portalData = FileUtil.read("../sql/portal-tables.sql");
574
575 BufferedReader br = new BufferedReader(new StringReader(data));
576
577 StringBuilder sb = new StringBuilder();
578
579 String line = null;
580
581 while ((line = br.readLine()) != null) {
582 boolean append = true;
583
584 int x = line.indexOf(" on ");
585
586 if (x != -1) {
587 int y = line.indexOf(" (", x);
588
589 String table = line.substring(x + 4, y);
590
591 x = y + 2;
592 y = line.indexOf(")", x);
593
594 String[] columns = StringUtil.split(line.substring(x, y));
595
596 x = portalData.indexOf("create table " + table + " (");
597 y = portalData.indexOf(");", x);
598
599 String portalTableData = portalData.substring(x, y);
600
601 for (int i = 0; i < columns.length; i++) {
602 if (portalTableData.indexOf(
603 columns[i].trim() + " BOOLEAN") != -1) {
604
605 append = false;
606
607 break;
608 }
609 }
610 }
611
612 if (append) {
613 sb.append(line);
614 sb.append("\n");
615 }
616 }
617
618 br.close();
619
620 return sb.toString();
621 }
622
623 protected String removeInserts(String data) throws IOException {
624 BufferedReader br = new BufferedReader(new StringReader(data));
625
626 StringBuilder sb = new StringBuilder();
627
628 String line = null;
629
630 while ((line = br.readLine()) != null) {
631 if (!line.startsWith("insert into ") &&
632 !line.startsWith("update ")) {
633
634 sb.append(line);
635 sb.append("\n");
636 }
637 }
638
639 br.close();
640
641 return sb.toString();
642 }
643
644 protected String removeLongInserts(String data) throws IOException {
645 BufferedReader br = new BufferedReader(new StringReader(data));
646
647 StringBuilder sb = new StringBuilder();
648
649 String line = null;
650
651 while ((line = br.readLine()) != null) {
652 if (!line.startsWith("insert into Image (") &&
653 !line.startsWith("insert into JournalArticle (") &&
654 !line.startsWith("insert into JournalStructure (") &&
655 !line.startsWith("insert into JournalTemplate (")) {
656
657 sb.append(line);
658 sb.append("\n");
659 }
660 }
661
662 br.close();
663
664 return sb.toString();
665 }
666
667 protected String removeNull(String content) {
668 content = StringUtil.replace(content, " not null", " not_null");
669 content = StringUtil.replace(content, " null", "");
670 content = StringUtil.replace(content, " not_null", " not null");
671
672 return content;
673 }
674
675 protected String replaceTemplate(String template, String[] actual) {
676 if ((template == null) || (TEMPLATE == null) || (actual == null)) {
677 return null;
678 }
679
680 if (TEMPLATE.length != actual.length) {
681 return template;
682 }
683
684 for (int i = 0; i < TEMPLATE.length; i++) {
685 if (TEMPLATE[i].equals("##") ||
686 TEMPLATE[i].equals("'01/01/1970'")) {
687
688 template = template.replaceAll(TEMPLATE[i], actual[i]);
689 }
690 else {
691 template = template.replaceAll(
692 "\\b" + TEMPLATE[i] + "\\b", actual[i]);
693 }
694 }
695
696 return template;
697 }
698
699 protected abstract String reword(String data) throws IOException;
700
701 protected static String ALTER_COLUMN_TYPE = "alter_column_type ";
702
703 protected static String ALTER_COLUMN_NAME = "alter_column_name ";
704
705 protected static String DROP_PRIMARY_KEY = "drop primary key";
706
707 protected static String[] REWORD_TEMPLATE = {
708 "@table@", "@old-column@", "@new-column@", "@type@", "@nullable@"
709 };
710
711 protected static String[] TEMPLATE = {
712 "##", "TRUE", "FALSE",
713 "'01/01/1970'", "CURRENT_TIMESTAMP",
714 " BLOB", " BOOLEAN", " DATE",
715 " DOUBLE", " INTEGER", " LONG",
716 " STRING", " TEXT", " VARCHAR",
717 " IDENTITY", "COMMIT_TRANSACTION"
718 };
719
720 private static boolean _SUPPORTS_ALTER_COLUMN_NAME = true;
721
722 private static boolean _SUPPORTS_ALTER_COLUMN_TYPE = true;
723
724 private static boolean _SUPPORTS_DATE_MILLISECONDS = true;
725
726 private static boolean _SUPPORTS_SCROLLABLE_RESULTS = true;
727
728 private static boolean _SUPPORTS_UPDATE_WITH_INNER_JOIN;
729
730 private static Log _log = LogFactoryUtil.getLog(BaseDB.class);
731
732 private String _type;
733 private boolean _supportsStringCaseSensitiveQuery;
734
735 }