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