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