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