001    /**
002     * Copyright (c) 2000-2013 Liferay, Inc. All rights reserved.
003     *
004     * This library is free software; you can redistribute it and/or modify it under
005     * the terms of the GNU Lesser General Public License as published by the Free
006     * Software Foundation; either version 2.1 of the License, or (at your option)
007     * any later version.
008     *
009     * This library is distributed in the hope that it will be useful, but WITHOUT
010     * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
011     * FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public License for more
012     * details.
013     */
014    
015    package com.liferay.util.dao.orm;
016    
017    import com.liferay.portal.kernel.dao.jdbc.DataAccess;
018    import com.liferay.portal.kernel.io.unsync.UnsyncBufferedReader;
019    import com.liferay.portal.kernel.io.unsync.UnsyncStringReader;
020    import com.liferay.portal.kernel.log.Log;
021    import com.liferay.portal.kernel.log.LogFactoryUtil;
022    import com.liferay.portal.kernel.util.GetterUtil;
023    import com.liferay.portal.kernel.util.OrderByComparator;
024    import com.liferay.portal.kernel.util.PortalClassLoaderUtil;
025    import com.liferay.portal.kernel.util.StringBundler;
026    import com.liferay.portal.kernel.util.StringPool;
027    import com.liferay.portal.kernel.util.StringUtil;
028    import com.liferay.portal.kernel.util.Validator;
029    import com.liferay.portal.kernel.xml.Document;
030    import com.liferay.portal.kernel.xml.Element;
031    import com.liferay.portal.kernel.xml.SAXReaderUtil;
032    import com.liferay.portal.util.PortalUtil;
033    
034    import java.io.IOException;
035    import java.io.InputStream;
036    
037    import java.sql.Connection;
038    import java.sql.DatabaseMetaData;
039    import java.sql.SQLException;
040    
041    import java.util.HashMap;
042    import java.util.Map;
043    import java.util.Properties;
044    
045    /**
046     * @author Brian Wing Shun Chan
047     * @author Bruno Farache
048     * @author Raymond Aug??
049     */
050    public class CustomSQL {
051    
052            public static final String DB2_FUNCTION_IS_NOT_NULL =
053                    "CAST(? AS VARCHAR(32672)) IS NOT NULL";
054    
055            public static final String DB2_FUNCTION_IS_NULL =
056                    "CAST(? AS VARCHAR(32672)) IS NULL";
057    
058            public static final String INFORMIX_FUNCTION_IS_NOT_NULL =
059                    "NOT lportal.isnull(?)";
060    
061            public static final String INFORMIX_FUNCTION_IS_NULL = "lportal.isnull(?)";
062    
063            public static final String MYSQL_FUNCTION_IS_NOT_NULL =
064                    "IFNULL(?, '1') = '0'";
065    
066            public static final String MYSQL_FUNCTION_IS_NULL = "IFNULL(?, '1') = '1'";
067    
068            public static final String SYBASE_FUNCTION_IS_NOT_NULL =
069                    "CONVERT(VARCHAR,?) IS NOT NULL";
070    
071            public static final String SYBASE_FUNCTION_IS_NULL =
072                    "CONVERT(VARCHAR,?) IS NULL";
073    
074            public CustomSQL() throws SQLException {
075                    reloadCustomSQL();
076            }
077    
078            public String appendCriteria(String sql, String criteria) {
079                    if (Validator.isNull(criteria)) {
080                            return sql;
081                    }
082    
083                    if (!criteria.startsWith(StringPool.SPACE)) {
084                            criteria = StringPool.SPACE.concat(criteria);
085                    }
086    
087                    if (!criteria.endsWith(StringPool.SPACE)) {
088                            criteria = criteria.concat(StringPool.SPACE);
089                    }
090    
091                    int pos = sql.indexOf(_GROUP_BY_CLAUSE);
092    
093                    if (pos != -1) {
094                            return sql.substring(0, pos + 1).concat(criteria).concat(
095                                    sql.substring(pos + 1));
096                    }
097    
098                    pos = sql.indexOf(_ORDER_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                    return sql.concat(criteria);
106            }
107    
108            public String get(String id) {
109                    return _sqlPool.get(id);
110            }
111    
112            /**
113             * Returns <code>true</code> if Hibernate is connecting to a DB2 database.
114             *
115             * @return <code>true</code> if Hibernate is connecting to a DB2 database
116             */
117            public boolean isVendorDB2() {
118                    return _vendorDB2;
119            }
120    
121            /**
122             * Returns <code>true</code> if Hibernate is connecting to a Hypersonic
123             * database.
124             *
125             * @return <code>true</code> if Hibernate is connecting to a Hypersonic
126             *         database
127             */
128            public boolean isVendorHSQL() {
129                    return _vendorHSQL;
130            }
131    
132            /**
133             * Returns <code>true</code> if Hibernate is connecting to an Informix
134             * database.
135             *
136             * @return <code>true</code> if Hibernate is connecting to an Informix
137             *         database
138             */
139            public boolean isVendorInformix() {
140                    return _vendorInformix;
141            }
142    
143            /**
144             * Returns <code>true</code> if Hibernate is connecting to a MySQL database.
145             *
146             * @return <code>true</code> if Hibernate is connecting to a MySQL database
147             */
148            public boolean isVendorMySQL() {
149                    return _vendorMySQL;
150            }
151    
152            /**
153             * Returns <code>true</code> if Hibernate is connecting to an Oracle
154             * database. Oracle has a nasty bug where it treats '' as a
155             * <code>NULL</code> value. See
156             * http://thedailywtf.com/forums/thread/26879.aspx for more information on
157             * this nasty bug.
158             *
159             * @return <code>true</code> if Hibernate is connecting to an Oracle
160             *         database
161             */
162            public boolean isVendorOracle() {
163                    return _vendorOracle;
164            }
165    
166            /**
167             * Returns <code>true</code> if Hibernate is connecting to a PostgreSQL
168             * database.
169             *
170             * @return <code>true</code> if Hibernate is connecting to a PostgreSQL
171             *         database
172             */
173            public boolean isVendorPostgreSQL() {
174                    return _vendorPostgreSQL;
175            }
176    
177            /**
178             * Returns <code>true</code> if Hibernate is connecting to a Sybase
179             * database.
180             *
181             * @return <code>true</code> if Hibernate is connecting to a Sybase database
182             */
183            public boolean isVendorSybase() {
184                    return _vendorSybase;
185            }
186    
187            public String[] keywords(String keywords) {
188                    return keywords(keywords, true);
189            }
190    
191            public String[] keywords(String keywords, boolean lowerCase) {
192                    if (Validator.isNull(keywords)) {
193                            return new String[] {null};
194                    }
195    
196                    if (lowerCase) {
197                            keywords = keywords.toLowerCase();
198                    }
199    
200                    keywords = keywords.trim();
201    
202                    String[] keywordsArray = keywords.split("\\s+");
203    
204                    for (int i = 0; i < keywordsArray.length; i++) {
205                            String keyword = keywordsArray[i];
206    
207                            keywordsArray[i] =
208                                    StringPool.PERCENT + keyword + StringPool.PERCENT;
209                    }
210    
211                    return keywordsArray;
212            }
213    
214            public String[] keywords(String[] keywordsArray) {
215                    return keywords(keywordsArray, true);
216            }
217    
218            public String[] keywords(String[] keywordsArray, boolean lowerCase) {
219                    if ((keywordsArray == null) || (keywordsArray.length == 0)) {
220                            return new String[] {null};
221                    }
222    
223                    if (lowerCase) {
224                            for (int i = 0; i < keywordsArray.length; i++) {
225                                    keywordsArray[i] = StringUtil.lowerCase(keywordsArray[i]);
226                            }
227                    }
228    
229                    return keywordsArray;
230            }
231    
232            public void reloadCustomSQL() throws SQLException {
233                    PortalUtil.initCustomSQL();
234    
235                    Connection con = DataAccess.getConnection();
236    
237                    String functionIsNull = PortalUtil.getCustomSQLFunctionIsNull();
238                    String functionIsNotNull = PortalUtil.getCustomSQLFunctionIsNotNull();
239    
240                    try {
241                            if (Validator.isNotNull(functionIsNull) &&
242                                    Validator.isNotNull(functionIsNotNull)) {
243    
244                                    _functionIsNull = functionIsNull;
245                                    _functionIsNotNull = functionIsNotNull;
246    
247                                    if (_log.isDebugEnabled()) {
248                                            _log.debug(
249                                                    "functionIsNull is manually set to " + functionIsNull);
250                                            _log.debug(
251                                                    "functionIsNotNull is manually set to " +
252                                                            functionIsNotNull);
253                                    }
254                            }
255                            else if (con != null) {
256                                    DatabaseMetaData metaData = con.getMetaData();
257    
258                                    String dbName = GetterUtil.getString(
259                                            metaData.getDatabaseProductName());
260    
261                                    if (_log.isInfoEnabled()) {
262                                            _log.info("Database name " + dbName);
263                                    }
264    
265                                    if (dbName.startsWith("DB2")) {
266                                            _vendorDB2 = true;
267                                            _functionIsNull = DB2_FUNCTION_IS_NULL;
268                                            _functionIsNotNull = DB2_FUNCTION_IS_NOT_NULL;
269    
270                                            if (_log.isInfoEnabled()) {
271                                                    _log.info("Detected DB2 with database name " + dbName);
272                                            }
273                                    }
274                                    else if (dbName.startsWith("HSQL")) {
275                                            _vendorHSQL = true;
276    
277                                            if (_log.isInfoEnabled()) {
278                                                    _log.info("Detected HSQL with database name " + dbName);
279                                            }
280                                    }
281                                    else if (dbName.startsWith("Informix")) {
282                                            _vendorInformix = true;
283                                            _functionIsNull = INFORMIX_FUNCTION_IS_NULL;
284                                            _functionIsNotNull = INFORMIX_FUNCTION_IS_NOT_NULL;
285    
286                                            if (_log.isInfoEnabled()) {
287                                                    _log.info(
288                                                            "Detected Informix with database name " + dbName);
289                                            }
290                                    }
291                                    else if (dbName.startsWith("MySQL")) {
292                                            _vendorMySQL = true;
293                                            //_functionIsNull = MYSQL_FUNCTION_IS_NULL;
294                                            //_functionIsNotNull = MYSQL_FUNCTION_IS_NOT_NULL;
295    
296                                            if (_log.isInfoEnabled()) {
297                                                    _log.info(
298                                                            "Detected MySQL with database name " + dbName);
299                                            }
300                                    }
301                                    else if (dbName.startsWith("Sybase") || dbName.equals("ASE")) {
302                                            _vendorSybase = true;
303                                            _functionIsNull = SYBASE_FUNCTION_IS_NULL;
304                                            _functionIsNotNull = SYBASE_FUNCTION_IS_NOT_NULL;
305    
306                                            if (_log.isInfoEnabled()) {
307                                                    _log.info(
308                                                            "Detected Sybase with database name " + dbName);
309                                            }
310                                    }
311                                    else if (dbName.startsWith("Oracle")) {
312                                            _vendorOracle = true;
313    
314                                            if (_log.isInfoEnabled()) {
315                                                    _log.info(
316                                                            "Detected Oracle with database name " + dbName);
317                                            }
318                                    }
319                                    else if (dbName.startsWith("PostgreSQL")) {
320                                            _vendorPostgreSQL = true;
321    
322                                            if (_log.isInfoEnabled()) {
323                                                    _log.info(
324                                                            "Detected PostgreSQL with database name " + dbName);
325                                            }
326                                    }
327                                    else {
328                                            if (_log.isDebugEnabled()) {
329                                                    _log.debug(
330                                                            "Unable to detect database with name " + dbName);
331                                            }
332                                    }
333                            }
334                    }
335                    catch (Exception e) {
336                            _log.error(e, e);
337                    }
338                    finally {
339                            DataAccess.cleanUp(con);
340                    }
341    
342                    if (_sqlPool == null) {
343                            _sqlPool = new HashMap<String, String>();
344                    }
345                    else {
346                            _sqlPool.clear();
347                    }
348    
349                    try {
350                            Class<?> clazz = getClass();
351    
352                            ClassLoader classLoader = clazz.getClassLoader();
353    
354                            String[] configs = getConfigs();
355    
356                            for (String _config : configs) {
357                                    read(classLoader, _config);
358                            }
359                    }
360                    catch (Exception e) {
361                            _log.error(e, e);
362                    }
363            }
364    
365            public String removeGroupBy(String sql) {
366                    int x = sql.indexOf(_GROUP_BY_CLAUSE);
367    
368                    if (x != -1) {
369                            int y = sql.indexOf(_ORDER_BY_CLAUSE);
370    
371                            if (y == -1) {
372                                    sql = sql.substring(0, x);
373                            }
374                            else {
375                                    sql = sql.substring(0, x) + sql.substring(y);
376                            }
377                    }
378    
379                    return sql;
380            }
381    
382            public String removeOrderBy(String sql) {
383                    int pos = sql.indexOf(_ORDER_BY_CLAUSE);
384    
385                    if (pos != -1) {
386                            sql = sql.substring(0, pos);
387                    }
388    
389                    return sql;
390            }
391    
392            public String replaceAndOperator(String sql, boolean andOperator) {
393                    String andOrConnector = "OR";
394                    String andOrNullCheck = "AND ? IS NOT NULL";
395    
396                    if (andOperator) {
397                            andOrConnector = "AND";
398                            andOrNullCheck = "OR ? IS NULL";
399                    }
400    
401                    sql = StringUtil.replace(
402                            sql,
403                            new String[] {
404                                    "[$AND_OR_CONNECTOR$]", "[$AND_OR_NULL_CHECK$]"
405                            },
406                            new String[] {
407                                    andOrConnector, andOrNullCheck
408                            });
409    
410                    if (_vendorPostgreSQL) {
411                            sql = StringUtil.replace(
412                                    sql,
413                                    new String[] {
414                                            "Date >= ? AND ? IS NOT NULL",
415                                            "Date <= ? AND ? IS NOT NULL", "Date >= ? OR ? IS NULL",
416                                            "Date <= ? OR ? IS NULL"
417                                    },
418                                    new String[] {
419                                            "Date >= ? AND CAST(? AS TIMESTAMP) IS NOT NULL",
420                                            "Date <= ? AND CAST(? AS TIMESTAMP) IS NOT NULL",
421                                            "Date >= ? OR CAST(? AS TIMESTAMP) IS NULL",
422                                            "Date <= ? OR CAST(? AS TIMESTAMP) IS NULL"
423                                    });
424                    }
425    
426                    sql = replaceIsNull(sql);
427    
428                    return sql;
429            }
430    
431            public String replaceGroupBy(String sql, String groupBy) {
432                    if (groupBy == null) {
433                            return sql;
434                    }
435    
436                    int x = sql.indexOf(_GROUP_BY_CLAUSE);
437    
438                    if (x != -1) {
439                            int y = sql.indexOf(_ORDER_BY_CLAUSE);
440    
441                            if (y == -1) {
442                                    sql = sql.substring(0, x + _GROUP_BY_CLAUSE.length()).concat(
443                                            groupBy);
444                            }
445                            else {
446                                    sql = sql.substring(0, x + _GROUP_BY_CLAUSE.length()).concat(
447                                            groupBy).concat(sql.substring(y));
448                            }
449                    }
450                    else {
451                            int y = sql.indexOf(_ORDER_BY_CLAUSE);
452    
453                            if (y == -1) {
454                                    sql = sql.concat(_GROUP_BY_CLAUSE).concat(groupBy);
455                            }
456                            else {
457                                    StringBundler sb = new StringBundler();
458    
459                                    sb.append(sql.substring(0, y));
460                                    sb.append(_GROUP_BY_CLAUSE);
461                                    sb.append(groupBy);
462                                    sb.append(sql.substring(y));
463    
464                                    sql = sb.toString();
465                            }
466                    }
467    
468                    return sql;
469            }
470    
471            public String replaceIsNull(String sql) {
472                    if (Validator.isNotNull(_functionIsNull)) {
473                            sql = StringUtil.replace(
474                                    sql,
475                                    new String[] {
476                                            "? IS NULL", "? IS NOT NULL"
477                                    },
478                                    new String[] {
479                                            _functionIsNull, _functionIsNotNull
480                                    });
481                    }
482    
483                    return sql;
484            }
485    
486            public String replaceKeywords(
487                    String sql, String field, boolean last, int[] values) {
488    
489                    if ((values != null) && (values.length == 1)) {
490                            return sql;
491                    }
492    
493                    StringBundler oldSql = new StringBundler(4);
494    
495                    oldSql.append("(");
496                    oldSql.append(field);
497                    oldSql.append(" = ?)");
498    
499                    if (!last) {
500                            oldSql.append(" [$AND_OR_CONNECTOR$]");
501                    }
502    
503                    if ((values == null) || (values.length == 0)) {
504                            return StringUtil.replace(sql, oldSql.toString(), StringPool.BLANK);
505                    }
506    
507                    StringBundler newSql = new StringBundler(values.length * 4 + 3);
508    
509                    newSql.append("(");
510    
511                    for (int i = 0; i < values.length; i++) {
512                            if (i > 0) {
513                                    newSql.append(" OR ");
514                            }
515    
516                            newSql.append("(");
517                            newSql.append(field);
518                            newSql.append(" = ?)");
519                    }
520    
521                    newSql.append(")");
522    
523                    if (!last) {
524                            newSql.append(" [$AND_OR_CONNECTOR$]");
525                    }
526    
527                    return StringUtil.replace(sql, oldSql.toString(), newSql.toString());
528            }
529    
530            public String replaceKeywords(
531                    String sql, String field, boolean last, long[] values) {
532    
533                    if ((values != null) && (values.length == 1)) {
534                            return sql;
535                    }
536    
537                    StringBundler oldSql = new StringBundler(4);
538    
539                    oldSql.append("(");
540                    oldSql.append(field);
541                    oldSql.append(" = ?)");
542    
543                    if (!last) {
544                            oldSql.append(" [$AND_OR_CONNECTOR$]");
545                    }
546    
547                    if ((values == null) || (values.length == 0)) {
548                            return StringUtil.replace(sql, oldSql.toString(), StringPool.BLANK);
549                    }
550    
551                    StringBundler newSql = new StringBundler(values.length * 4 + 3);
552    
553                    newSql.append("(");
554    
555                    for (int i = 0; i < values.length; i++) {
556                            if (i > 0) {
557                                    newSql.append(" OR ");
558                            }
559    
560                            newSql.append("(");
561                            newSql.append(field);
562                            newSql.append(" = ?)");
563                    }
564    
565                    newSql.append(")");
566    
567                    if (!last) {
568                            newSql.append(" [$AND_OR_CONNECTOR$]");
569                    }
570    
571                    return StringUtil.replace(sql, oldSql.toString(), newSql.toString());
572            }
573    
574            public String replaceKeywords(
575                    String sql, String field, String operator, boolean last,
576                    String[] values) {
577    
578                    if ((values != null) && (values.length <= 1)) {
579                            return sql;
580                    }
581    
582                    StringBundler oldSql = new StringBundler(6);
583    
584                    oldSql.append("(");
585                    oldSql.append(field);
586                    oldSql.append(" ");
587                    oldSql.append(operator);
588                    oldSql.append(" ? [$AND_OR_NULL_CHECK$])");
589    
590                    if (!last) {
591                            oldSql.append(" [$AND_OR_CONNECTOR$]");
592                    }
593    
594                    StringBundler newSql = new StringBundler(values.length * 6 + 3);
595    
596                    newSql.append("(");
597    
598                    for (int i = 0; i < values.length; i++) {
599                            if (i > 0) {
600                                    newSql.append(" OR ");
601                            }
602    
603                            newSql.append("(");
604                            newSql.append(field);
605                            newSql.append(" ");
606                            newSql.append(operator);
607                            newSql.append(" ? [$AND_OR_NULL_CHECK$])");
608                    }
609    
610                    newSql.append(")");
611    
612                    if (!last) {
613                            newSql.append(" [$AND_OR_CONNECTOR$]");
614                    }
615    
616                    return StringUtil.replace(sql, oldSql.toString(), newSql.toString());
617            }
618    
619            public String replaceOrderBy(String sql, OrderByComparator obc) {
620                    if (obc == null) {
621                            return sql;
622                    }
623    
624                    String orderBy = obc.getOrderBy();
625    
626                    int pos = sql.indexOf(_ORDER_BY_CLAUSE);
627    
628                    if ((pos != -1) && (pos < sql.length())) {
629                            sql = sql.substring(0, pos + _ORDER_BY_CLAUSE.length()).concat(
630                                    orderBy);
631                    }
632                    else {
633                            sql = sql.concat(_ORDER_BY_CLAUSE).concat(orderBy);
634                    }
635    
636                    return sql;
637            }
638    
639            protected String[] getConfigs() {
640                    if (PortalClassLoaderUtil.getClassLoader() ==
641                                    CustomSQL.class.getClassLoader()) {
642    
643                            Properties propsUtil = PortalUtil.getPortalProperties();
644    
645                            return StringUtil.split(
646                                    propsUtil.getProperty("custom.sql.configs"));
647                    }
648                    else {
649                            return new String[] {"custom-sql/default.xml"};
650                    }
651            }
652    
653            protected void read(ClassLoader classLoader, String source)
654                    throws Exception {
655    
656                    InputStream is = classLoader.getResourceAsStream(source);
657    
658                    if (is == null) {
659                            return;
660                    }
661    
662                    if (_log.isDebugEnabled()) {
663                            _log.debug("Loading " + source);
664                    }
665    
666                    Document document = SAXReaderUtil.read(is);
667    
668                    Element rootElement = document.getRootElement();
669    
670                    for (Element sqlElement : rootElement.elements("sql")) {
671                            String file = sqlElement.attributeValue("file");
672    
673                            if (Validator.isNotNull(file)) {
674                                    read(classLoader, file);
675                            }
676                            else {
677                                    String id = sqlElement.attributeValue("id");
678                                    String content = transform(sqlElement.getText());
679    
680                                    content = replaceIsNull(content);
681    
682                                    _sqlPool.put(id, content);
683                            }
684                    }
685            }
686    
687            protected String transform(String sql) {
688                    sql = PortalUtil.transformCustomSQL(sql);
689    
690                    StringBundler sb = new StringBundler();
691    
692                    try {
693                            UnsyncBufferedReader unsyncBufferedReader =
694                                    new UnsyncBufferedReader(new UnsyncStringReader(sql));
695    
696                            String line = null;
697    
698                            while ((line = unsyncBufferedReader.readLine()) != null) {
699                                    sb.append(line.trim());
700                                    sb.append(StringPool.SPACE);
701                            }
702    
703                            unsyncBufferedReader.close();
704                    }
705                    catch (IOException ioe) {
706                            return sql;
707                    }
708    
709                    return sb.toString();
710            }
711    
712            private static final String _GROUP_BY_CLAUSE = " GROUP BY ";
713    
714            private static final String _ORDER_BY_CLAUSE = " ORDER BY ";
715    
716            private static Log _log = LogFactoryUtil.getLog(CustomSQL.class);
717    
718            private String _functionIsNotNull;
719            private String _functionIsNull;
720            private Map<String, String> _sqlPool;
721            private boolean _vendorDB2;
722            private boolean _vendorHSQL;
723            private boolean _vendorInformix;
724            private boolean _vendorMySQL;
725            private boolean _vendorOracle;
726            private boolean _vendorPostgreSQL;
727            private boolean _vendorSybase;
728    
729    }