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