001
014
015 package com.liferay.util.dao.orm;
016
017 import com.liferay.portal.kernel.dao.jdbc.DataAccess;
018 import com.liferay.portal.kernel.dao.orm.QueryDefinition;
019 import com.liferay.portal.kernel.io.unsync.UnsyncBufferedReader;
020 import com.liferay.portal.kernel.io.unsync.UnsyncStringReader;
021 import com.liferay.portal.kernel.log.Log;
022 import com.liferay.portal.kernel.log.LogFactoryUtil;
023 import com.liferay.portal.kernel.util.CharPool;
024 import com.liferay.portal.kernel.util.GetterUtil;
025 import com.liferay.portal.kernel.util.OrderByComparator;
026 import com.liferay.portal.kernel.util.PortalClassLoaderUtil;
027 import com.liferay.portal.kernel.util.StringBundler;
028 import com.liferay.portal.kernel.util.StringPool;
029 import com.liferay.portal.kernel.util.StringUtil;
030 import com.liferay.portal.kernel.util.Validator;
031 import com.liferay.portal.kernel.workflow.WorkflowConstants;
032 import com.liferay.portal.kernel.xml.Document;
033 import com.liferay.portal.kernel.xml.Element;
034 import com.liferay.portal.kernel.xml.SAXReaderUtil;
035 import com.liferay.portal.util.PortalUtil;
036
037 import java.io.IOException;
038 import java.io.InputStream;
039
040 import java.sql.Connection;
041 import java.sql.DatabaseMetaData;
042 import java.sql.SQLException;
043
044 import java.util.ArrayList;
045 import java.util.HashMap;
046 import java.util.List;
047 import java.util.Map;
048 import java.util.Properties;
049
050
055 public class CustomSQL {
056
057 public static final String DB2_FUNCTION_IS_NOT_NULL =
058 "CAST(? AS VARCHAR(32672)) IS NOT NULL";
059
060 public static final String DB2_FUNCTION_IS_NULL =
061 "CAST(? AS VARCHAR(32672)) IS NULL";
062
063 public static final String INFORMIX_FUNCTION_IS_NOT_NULL =
064 "NOT lportal.isnull(?)";
065
066 public static final String INFORMIX_FUNCTION_IS_NULL = "lportal.isnull(?)";
067
068 public static final String MYSQL_FUNCTION_IS_NOT_NULL =
069 "IFNULL(?, '1') = '0'";
070
071 public static final String MYSQL_FUNCTION_IS_NULL = "IFNULL(?, '1') = '1'";
072
073 public static final String SYBASE_FUNCTION_IS_NOT_NULL =
074 "CONVERT(VARCHAR,?) IS NOT NULL";
075
076 public static final String SYBASE_FUNCTION_IS_NULL =
077 "CONVERT(VARCHAR,?) IS NULL";
078
079 public CustomSQL() throws SQLException {
080 reloadCustomSQL();
081 }
082
083 public String appendCriteria(String sql, String criteria) {
084 if (Validator.isNull(criteria)) {
085 return sql;
086 }
087
088 if (!criteria.startsWith(StringPool.SPACE)) {
089 criteria = StringPool.SPACE.concat(criteria);
090 }
091
092 if (!criteria.endsWith(StringPool.SPACE)) {
093 criteria = criteria.concat(StringPool.SPACE);
094 }
095
096 int pos = sql.indexOf(_GROUP_BY_CLAUSE);
097
098 if (pos != -1) {
099 return sql.substring(0, pos + 1).concat(criteria).concat(
100 sql.substring(pos + 1));
101 }
102
103 pos = sql.indexOf(_ORDER_BY_CLAUSE);
104
105 if (pos != -1) {
106 return sql.substring(0, pos + 1).concat(criteria).concat(
107 sql.substring(pos + 1));
108 }
109
110 return sql.concat(criteria);
111 }
112
113 public String get(String id) {
114 return _sqlPool.get(id);
115 }
116
117 public String get(String id, QueryDefinition queryDefinition) {
118 return get(id, queryDefinition, StringPool.BLANK);
119 }
120
121 public String get(
122 String id, QueryDefinition queryDefinition, String tableName) {
123
124 String sql = get(id);
125
126 if (!Validator.isBlank(tableName) &&
127 !tableName.endsWith(StringPool.PERIOD)) {
128
129 tableName = tableName.concat(StringPool.PERIOD);
130 }
131
132 if (queryDefinition.getStatus() == WorkflowConstants.STATUS_ANY) {
133 sql = sql.replace(_STATUS_KEYWORD, _STATUS_CONDITION_EMPTY);
134 }
135 else {
136 if (queryDefinition.isExcludeStatus()) {
137 sql = sql.replace(
138 _STATUS_KEYWORD,
139 tableName.concat(_STATUS_CONDITION_INVERSE));
140 }
141 else {
142 sql = sql.replace(
143 _STATUS_KEYWORD,
144 tableName.concat(_STATUS_CONDITION_DEFAULT));
145 }
146 }
147
148 return sql;
149 }
150
151
156 public boolean isVendorDB2() {
157 return _vendorDB2;
158 }
159
160
167 public boolean isVendorInformix() {
168 return _vendorInformix;
169 }
170
171
176 public boolean isVendorMySQL() {
177 return _vendorMySQL;
178 }
179
180
190 public boolean isVendorOracle() {
191 return _vendorOracle;
192 }
193
194
201 public boolean isVendorPostgreSQL() {
202 return _vendorPostgreSQL;
203 }
204
205
211 public boolean isVendorSybase() {
212 return _vendorSybase;
213 }
214
215 public String[] keywords(String keywords) {
216 return keywords(keywords, true);
217 }
218
219 public String[] keywords(String keywords, boolean lowerCase) {
220 if (Validator.isNull(keywords)) {
221 return new String[] {null};
222 }
223
224 if (lowerCase) {
225 keywords = keywords.toLowerCase();
226 }
227
228 keywords = keywords.trim();
229
230 List<String> keywordsList = new ArrayList<String>();
231
232 for (int i = 0; i < keywords.length(); i++) {
233 char c = keywords.charAt(i);
234
235 if (c == CharPool.QUOTE) {
236 int pos = i + 1;
237
238 i = keywords.indexOf(CharPool.QUOTE, pos);
239
240 if (i == -1) {
241 i = keywords.length();
242 }
243
244 if (i > pos) {
245 String keyword = keywords.substring(pos, i);
246
247 keywordsList.add(
248 StringUtil.quote(keyword, StringPool.PERCENT));
249 }
250 }
251 else {
252 while (Character.isWhitespace(c)) {
253 i++;
254
255 c = keywords.charAt(i);
256 }
257
258 int pos = i;
259
260 while (!Character.isWhitespace(c)) {
261 i++;
262
263 if (i == keywords.length()) {
264 break;
265 }
266
267 c = keywords.charAt(i);
268 }
269
270 String keyword = keywords.substring(pos, i);
271
272 keywordsList.add(StringUtil.quote(keyword, StringPool.PERCENT));
273 }
274 }
275
276 return keywordsList.toArray(new String[keywordsList.size()]);
277 }
278
279 public String[] keywords(String[] keywordsArray) {
280 return keywords(keywordsArray, true);
281 }
282
283 public String[] keywords(String[] keywordsArray, boolean lowerCase) {
284 if ((keywordsArray == null) || (keywordsArray.length == 0)) {
285 return new String[] {null};
286 }
287
288 if (lowerCase) {
289 for (int i = 0; i < keywordsArray.length; i++) {
290 keywordsArray[i] = StringUtil.lowerCase(keywordsArray[i]);
291 }
292 }
293
294 return keywordsArray;
295 }
296
297 public void reloadCustomSQL() throws SQLException {
298 PortalUtil.initCustomSQL();
299
300 Connection con = DataAccess.getConnection();
301
302 String functionIsNull = PortalUtil.getCustomSQLFunctionIsNull();
303 String functionIsNotNull = PortalUtil.getCustomSQLFunctionIsNotNull();
304
305 try {
306 if (Validator.isNotNull(functionIsNull) &&
307 Validator.isNotNull(functionIsNotNull)) {
308
309 _functionIsNull = functionIsNull;
310 _functionIsNotNull = functionIsNotNull;
311
312 if (_log.isDebugEnabled()) {
313 _log.info(
314 "functionIsNull is manually set to " + functionIsNull);
315 _log.info(
316 "functionIsNotNull is manually set to " +
317 functionIsNotNull);
318 }
319 }
320 else if (con != null) {
321 DatabaseMetaData metaData = con.getMetaData();
322
323 String dbName = GetterUtil.getString(
324 metaData.getDatabaseProductName());
325
326 if (_log.isInfoEnabled()) {
327 _log.info("Database name " + dbName);
328 }
329
330 if (dbName.startsWith("DB2")) {
331 _vendorDB2 = true;
332 _functionIsNull = DB2_FUNCTION_IS_NULL;
333 _functionIsNotNull = DB2_FUNCTION_IS_NOT_NULL;
334
335 if (_log.isInfoEnabled()) {
336 _log.info("Detected DB2 with database name " + dbName);
337 }
338 }
339 else if (dbName.startsWith("Informix")) {
340 _vendorInformix = true;
341 _functionIsNull = INFORMIX_FUNCTION_IS_NULL;
342 _functionIsNotNull = INFORMIX_FUNCTION_IS_NOT_NULL;
343
344 if (_log.isInfoEnabled()) {
345 _log.info(
346 "Detected Informix with database name " + dbName);
347 }
348 }
349 else if (dbName.startsWith("MySQL")) {
350 _vendorMySQL = true;
351
352
353
354 if (_log.isInfoEnabled()) {
355 _log.info(
356 "Detected MySQL with database name " + dbName);
357 }
358 }
359 else if (dbName.startsWith("Sybase") || dbName.equals("ASE")) {
360 _vendorSybase = true;
361 _functionIsNull = SYBASE_FUNCTION_IS_NULL;
362 _functionIsNotNull = SYBASE_FUNCTION_IS_NOT_NULL;
363
364 if (_log.isInfoEnabled()) {
365 _log.info(
366 "Detected Sybase with database name " + dbName);
367 }
368 }
369 else if (dbName.startsWith("Oracle")) {
370 _vendorOracle = true;
371
372 if (_log.isInfoEnabled()) {
373 _log.info(
374 "Detected Oracle with database name " + dbName);
375 }
376 }
377 else if (dbName.startsWith("PostgreSQL")) {
378 _vendorPostgreSQL = true;
379
380 if (_log.isInfoEnabled()) {
381 _log.info(
382 "Detected PostgreSQL with database name " + dbName);
383 }
384 }
385 else {
386 if (_log.isDebugEnabled()) {
387 _log.debug(
388 "Unable to detect database with name " + dbName);
389 }
390 }
391 }
392 }
393 catch (Exception e) {
394 _log.error(e, e);
395 }
396 finally {
397 DataAccess.cleanUp(con);
398 }
399
400 if (_sqlPool == null) {
401 _sqlPool = new HashMap<String, String>();
402 }
403 else {
404 _sqlPool.clear();
405 }
406
407 try {
408 Class<?> clazz = getClass();
409
410 ClassLoader classLoader = clazz.getClassLoader();
411
412 String[] configs = getConfigs();
413
414 for (String _config : configs) {
415 read(classLoader, _config);
416 }
417 }
418 catch (Exception e) {
419 _log.error(e, e);
420 }
421 }
422
423 public String removeGroupBy(String sql) {
424 int x = sql.indexOf(_GROUP_BY_CLAUSE);
425
426 if (x != -1) {
427 int y = sql.indexOf(_ORDER_BY_CLAUSE);
428
429 if (y == -1) {
430 sql = sql.substring(0, x);
431 }
432 else {
433 sql = sql.substring(0, x) + sql.substring(y);
434 }
435 }
436
437 return sql;
438 }
439
440 public String removeOrderBy(String sql) {
441 int pos = sql.indexOf(_ORDER_BY_CLAUSE);
442
443 if (pos != -1) {
444 sql = sql.substring(0, pos);
445 }
446
447 return sql;
448 }
449
450 public String replaceAndOperator(String sql, boolean andOperator) {
451 String andOrConnector = "OR";
452 String andOrNullCheck = "AND ? IS NOT NULL";
453
454 if (andOperator) {
455 andOrConnector = "AND";
456 andOrNullCheck = "OR ? IS NULL";
457 }
458
459 sql = StringUtil.replace(
460 sql,
461 new String[] {
462 "[$AND_OR_CONNECTOR$]", "[$AND_OR_NULL_CHECK$]"
463 },
464 new String[] {
465 andOrConnector, andOrNullCheck
466 });
467
468 if (_vendorPostgreSQL) {
469 sql = StringUtil.replace(
470 sql,
471 new String[] {
472 "Date >= ? AND ? IS NOT NULL",
473 "Date <= ? AND ? IS NOT NULL", "Date >= ? OR ? IS NULL",
474 "Date <= ? OR ? IS NULL"
475 },
476 new String[] {
477 "Date >= ? AND CAST(? AS TIMESTAMP) IS NOT NULL",
478 "Date <= ? AND CAST(? AS TIMESTAMP) IS NOT NULL",
479 "Date >= ? OR CAST(? AS TIMESTAMP) IS NULL",
480 "Date <= ? OR CAST(? AS TIMESTAMP) IS NULL"
481 });
482 }
483
484 sql = replaceIsNull(sql);
485
486 return sql;
487 }
488
489 public String replaceGroupBy(String sql, String groupBy) {
490 if (groupBy == null) {
491 return sql;
492 }
493
494 int x = sql.indexOf(_GROUP_BY_CLAUSE);
495
496 if (x != -1) {
497 int y = sql.indexOf(_ORDER_BY_CLAUSE);
498
499 if (y == -1) {
500 sql = sql.substring(0, x + _GROUP_BY_CLAUSE.length()).concat(
501 groupBy);
502 }
503 else {
504 sql = sql.substring(0, x + _GROUP_BY_CLAUSE.length()).concat(
505 groupBy).concat(sql.substring(y));
506 }
507 }
508 else {
509 int y = sql.indexOf(_ORDER_BY_CLAUSE);
510
511 if (y == -1) {
512 sql = sql.concat(_GROUP_BY_CLAUSE).concat(groupBy);
513 }
514 else {
515 StringBundler sb = new StringBundler();
516
517 sb.append(sql.substring(0, y));
518 sb.append(_GROUP_BY_CLAUSE);
519 sb.append(groupBy);
520 sb.append(sql.substring(y));
521
522 sql = sb.toString();
523 }
524 }
525
526 return sql;
527 }
528
529 public String replaceIsNull(String sql) {
530 if (Validator.isNotNull(_functionIsNull)) {
531 sql = StringUtil.replace(
532 sql,
533 new String[] {
534 "? IS NULL", "? IS NOT NULL"
535 },
536 new String[] {
537 _functionIsNull, _functionIsNotNull
538 });
539 }
540
541 return sql;
542 }
543
544 public String replaceKeywords(
545 String sql, String field, boolean last, int[] values) {
546
547 if ((values != null) && (values.length == 1)) {
548 return sql;
549 }
550
551 StringBundler oldSql = new StringBundler(4);
552
553 oldSql.append(StringPool.OPEN_PARENTHESIS);
554 oldSql.append(field);
555 oldSql.append(" = ?)");
556
557 if (!last) {
558 oldSql.append(" [$AND_OR_CONNECTOR$]");
559 }
560
561 if ((values == null) || (values.length == 0)) {
562 return StringUtil.replace(sql, oldSql.toString(), StringPool.BLANK);
563 }
564
565 StringBundler newSql = new StringBundler(values.length * 4 + 3);
566
567 newSql.append(StringPool.OPEN_PARENTHESIS);
568
569 for (int i = 0; i < values.length; i++) {
570 if (i > 0) {
571 newSql.append(" OR ");
572 }
573
574 newSql.append(StringPool.OPEN_PARENTHESIS);
575 newSql.append(field);
576 newSql.append(" = ?)");
577 }
578
579 newSql.append(StringPool.CLOSE_PARENTHESIS);
580
581 if (!last) {
582 newSql.append(" [$AND_OR_CONNECTOR$]");
583 }
584
585 return StringUtil.replace(sql, oldSql.toString(), newSql.toString());
586 }
587
588 public String replaceKeywords(
589 String sql, String field, boolean last, long[] values) {
590
591 if ((values != null) && (values.length == 1)) {
592 return sql;
593 }
594
595 StringBundler oldSql = new StringBundler(4);
596
597 oldSql.append(StringPool.OPEN_PARENTHESIS);
598 oldSql.append(field);
599 oldSql.append(" = ?)");
600
601 if (!last) {
602 oldSql.append(" [$AND_OR_CONNECTOR$]");
603 }
604
605 if ((values == null) || (values.length == 0)) {
606 return StringUtil.replace(sql, oldSql.toString(), StringPool.BLANK);
607 }
608
609 StringBundler newSql = new StringBundler(values.length * 4 + 3);
610
611 newSql.append(StringPool.OPEN_PARENTHESIS);
612
613 for (int i = 0; i < values.length; i++) {
614 if (i > 0) {
615 newSql.append(" OR ");
616 }
617
618 newSql.append(StringPool.OPEN_PARENTHESIS);
619 newSql.append(field);
620 newSql.append(" = ?)");
621 }
622
623 newSql.append(StringPool.CLOSE_PARENTHESIS);
624
625 if (!last) {
626 newSql.append(" [$AND_OR_CONNECTOR$]");
627 }
628
629 return StringUtil.replace(sql, oldSql.toString(), newSql.toString());
630 }
631
632 public String replaceKeywords(
633 String sql, String field, String operator, boolean last,
634 String[] values) {
635
636 if ((values != null) && (values.length <= 1)) {
637 return sql;
638 }
639
640 StringBundler oldSql = new StringBundler(6);
641
642 oldSql.append(StringPool.OPEN_PARENTHESIS);
643 oldSql.append(field);
644 oldSql.append(" ");
645 oldSql.append(operator);
646 oldSql.append(" ? [$AND_OR_NULL_CHECK$])");
647
648 if (!last) {
649 oldSql.append(" [$AND_OR_CONNECTOR$]");
650 }
651
652 StringBundler newSql = new StringBundler(values.length * 6 + 3);
653
654 newSql.append(StringPool.OPEN_PARENTHESIS);
655
656 for (int i = 0; i < values.length; i++) {
657 if (i > 0) {
658 newSql.append(" OR ");
659 }
660
661 newSql.append(StringPool.OPEN_PARENTHESIS);
662 newSql.append(field);
663 newSql.append(" ");
664 newSql.append(operator);
665 newSql.append(" ? [$AND_OR_NULL_CHECK$])");
666 }
667
668 newSql.append(StringPool.CLOSE_PARENTHESIS);
669
670 if (!last) {
671 newSql.append(" [$AND_OR_CONNECTOR$]");
672 }
673
674 return StringUtil.replace(sql, oldSql.toString(), newSql.toString());
675 }
676
677 public String replaceOrderBy(String sql, OrderByComparator obc) {
678 if (obc == null) {
679 return sql;
680 }
681
682 String orderBy = obc.getOrderBy();
683
684 int pos = sql.indexOf(_ORDER_BY_CLAUSE);
685
686 if ((pos != -1) && (pos < sql.length())) {
687 sql = sql.substring(0, pos + _ORDER_BY_CLAUSE.length()).concat(
688 orderBy);
689 }
690 else {
691 sql = sql.concat(_ORDER_BY_CLAUSE).concat(orderBy);
692 }
693
694 return sql;
695 }
696
697 protected String[] getConfigs() {
698 if (PortalClassLoaderUtil.getClassLoader() ==
699 CustomSQL.class.getClassLoader()) {
700
701 Properties propsUtil = PortalUtil.getPortalProperties();
702
703 return StringUtil.split(
704 propsUtil.getProperty("custom.sql.configs"));
705 }
706 else {
707 return new String[] {"custom-sql/default.xml"};
708 }
709 }
710
711 protected void read(ClassLoader classLoader, String source)
712 throws Exception {
713
714 InputStream is = classLoader.getResourceAsStream(source);
715
716 if (is == null) {
717 return;
718 }
719
720 if (_log.isDebugEnabled()) {
721 _log.debug("Loading " + source);
722 }
723
724 Document document = SAXReaderUtil.read(is);
725
726 Element rootElement = document.getRootElement();
727
728 for (Element sqlElement : rootElement.elements("sql")) {
729 String file = sqlElement.attributeValue("file");
730
731 if (Validator.isNotNull(file)) {
732 read(classLoader, file);
733 }
734 else {
735 String id = sqlElement.attributeValue("id");
736 String content = transform(sqlElement.getText());
737
738 content = replaceIsNull(content);
739
740 _sqlPool.put(id, content);
741 }
742 }
743 }
744
745 protected String transform(String sql) {
746 sql = PortalUtil.transformCustomSQL(sql);
747
748 StringBundler sb = new StringBundler();
749
750 try {
751 UnsyncBufferedReader unsyncBufferedReader =
752 new UnsyncBufferedReader(new UnsyncStringReader(sql));
753
754 String line = null;
755
756 while ((line = unsyncBufferedReader.readLine()) != null) {
757 sb.append(line.trim());
758 sb.append(StringPool.SPACE);
759 }
760
761 unsyncBufferedReader.close();
762 }
763 catch (IOException ioe) {
764 return sql;
765 }
766
767 return sb.toString();
768 }
769
770 private static final String _GROUP_BY_CLAUSE = " GROUP BY ";
771
772 private static final String _ORDER_BY_CLAUSE = " ORDER BY ";
773
774 private static final String _STATUS_CONDITION_DEFAULT = "status = ?";
775
776 private static final String _STATUS_CONDITION_EMPTY =
777 WorkflowConstants.STATUS_ANY + " = ?";
778
779 private static final String _STATUS_CONDITION_INVERSE = "status != ?";
780
781 private static final String _STATUS_KEYWORD = "[$STATUS$]";
782
783 private static Log _log = LogFactoryUtil.getLog(CustomSQL.class);
784
785 private String _functionIsNotNull;
786 private String _functionIsNull;
787 private Map<String, String> _sqlPool;
788 private boolean _vendorDB2;
789 private boolean _vendorInformix;
790 private boolean _vendorMySQL;
791 private boolean _vendorOracle;
792 private boolean _vendorPostgreSQL;
793 private boolean _vendorSybase;
794
795 }