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