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