1
22
23 package com.liferay.util.dao.orm;
24
25 import com.liferay.portal.kernel.dao.jdbc.DataAccess;
26 import com.liferay.portal.kernel.util.GetterUtil;
27 import com.liferay.portal.kernel.util.OrderByComparator;
28 import com.liferay.portal.kernel.util.PortalClassLoaderUtil;
29 import com.liferay.portal.kernel.util.StringPool;
30 import com.liferay.portal.kernel.util.StringUtil;
31 import com.liferay.portal.kernel.util.Validator;
32 import com.liferay.portal.kernel.xml.Document;
33 import com.liferay.portal.kernel.xml.Element;
34 import com.liferay.portal.kernel.xml.SAXReaderUtil;
35 import com.liferay.portal.util.PortalUtil;
36
37 import java.io.BufferedReader;
38 import java.io.IOException;
39 import java.io.StringReader;
40
41 import java.sql.Connection;
42 import java.sql.DatabaseMetaData;
43 import java.sql.SQLException;
44
45 import java.util.HashMap;
46 import java.util.Iterator;
47 import java.util.Map;
48 import java.util.Properties;
49
50 import javax.naming.NamingException;
51
52 import org.apache.commons.logging.Log;
53 import org.apache.commons.logging.LogFactory;
54
55
62 public class CustomSQL {
63
64 public static final String DB2_FUNCTION_IS_NULL =
65 "CAST(? AS VARCHAR(32672)) IS NULL";
66
67 public static final String DB2_FUNCTION_IS_NOT_NULL =
68 "CAST(? AS VARCHAR(32672)) IS NOT NULL";
69
70 public static final String INFORMIX_FUNCTION_IS_NULL = "lportal.isnull(?)";
71
72 public static final String INFORMIX_FUNCTION_IS_NOT_NULL =
73 "NOT " + INFORMIX_FUNCTION_IS_NULL;
74
75 public static final String MYSQL_FUNCTION_IS_NULL = "IFNULL(?, '1') = '1'";
76
77 public static final String MYSQL_FUNCTION_IS_NOT_NULL =
78 "IFNULL(?, '1') = '0'";
79
80 public static final String SYBASE_FUNCTION_IS_NULL = "ISNULL(?, '1') = '1'";
81
82 public static final String SYBASE_FUNCTION_IS_NOT_NULL =
83 "ISNULL(?, '1') = '0'";
84
85 public CustomSQL() throws NamingException, SQLException {
86 Connection con = DataAccess.getConnection();
87
88 String functionIsNull = PortalUtil.getCustomSQLFunctionIsNull();
89 String functionIsNotNull = PortalUtil.getCustomSQLFunctionIsNotNull();
90
91 try {
92 if (Validator.isNotNull(functionIsNull) &&
93 Validator.isNotNull(functionIsNotNull)) {
94
95 _functionIsNull = functionIsNull;
96 _functionIsNotNull = functionIsNotNull;
97
98 if (_log.isDebugEnabled()) {
99 _log.info(
100 "functionIsNull is manually set to " + functionIsNull);
101 _log.info(
102 "functionIsNotNull is manually set to " +
103 functionIsNotNull);
104 }
105 }
106 else if (con != null) {
107 DatabaseMetaData metaData = con.getMetaData();
108
109 String dbName = GetterUtil.getString(
110 metaData.getDatabaseProductName());
111
112 if (_log.isInfoEnabled()) {
113 _log.info("Database name " + dbName);
114 }
115
116 if (dbName.startsWith("DB2")) {
117 _vendorDB2 = true;
118 _functionIsNull = DB2_FUNCTION_IS_NULL;
119 _functionIsNotNull = DB2_FUNCTION_IS_NOT_NULL;
120
121 if (_log.isInfoEnabled()) {
122 _log.info("Detected DB2 with database name " + dbName);
123 }
124 }
125 else if (dbName.startsWith("Informix")) {
126 _vendorInformix = true;
127 _functionIsNull = INFORMIX_FUNCTION_IS_NULL;
128 _functionIsNotNull = INFORMIX_FUNCTION_IS_NOT_NULL;
129
130 if (_log.isInfoEnabled()) {
131 _log.info(
132 "Detected Informix with database name " + dbName);
133 }
134 }
135 else if (dbName.startsWith("MySQL")) {
136 _vendorMySQL = true;
137
140 if (_log.isInfoEnabled()) {
141 _log.info(
142 "Detected MySQL with database name " + dbName);
143 }
144 }
145 else if (dbName.startsWith("Sybase") || dbName.equals("ASE")) {
146 _vendorSybase = true;
147 _functionIsNull = SYBASE_FUNCTION_IS_NULL;
148 _functionIsNotNull = SYBASE_FUNCTION_IS_NOT_NULL;
149
150 if (_log.isInfoEnabled()) {
151 _log.info(
152 "Detected Sybase with database name " + dbName);
153 }
154 }
155 else if (dbName.startsWith("Oracle")) {
156 _vendorOracle = true;
157
158 if (_log.isInfoEnabled()) {
159 _log.info(
160 "Detected Oracle with database name " + dbName);
161 }
162 }
163 else if (dbName.startsWith("PostgreSQL")) {
164 _vendorPostgreSQL = true;
165
166 if (_log.isInfoEnabled()) {
167 _log.info(
168 "Detected PostgreSQL with database name " + dbName);
169 }
170 }
171 else {
172 if (_log.isDebugEnabled()) {
173 _log.debug(
174 "Unable to detect database with name " + dbName);
175 }
176 }
177 }
178 }
179 catch (Exception e) {
180 _log.error(e, e);
181 }
182 finally {
183 DataAccess.cleanUp(con);
184 }
185
186 _sqlPool = new HashMap<String, String>();
187
188 try {
189 ClassLoader classLoader = getClass().getClassLoader();
190
191 String[] configs = getConfigs();
192
193 for (int i = 0; i < configs.length; i++) {
194 read(classLoader, configs[i]);
195 }
196 }
197 catch (Exception e) {
198 _log.error(e, e);
199 }
200 }
201
202 public String get(String id) {
203 return _sqlPool.get(id);
204 }
205
206
211 public boolean isVendorDB2() {
212 return _vendorDB2;
213 }
214
215
220 public boolean isVendorInformix() {
221 return _vendorInformix;
222 }
223
224
229 public boolean isVendorMySQL() {
230 return _vendorMySQL;
231 }
232
233
242 public boolean isVendorOracle() {
243 return _vendorOracle;
244 }
245
246
251 public boolean isVendorPostgreSQL() {
252 return _vendorPostgreSQL;
253 }
254
255
260 public boolean isVendorSybase() {
261 return _vendorSybase;
262 }
263
264 public String[] keywords(String keywords) {
265 return keywords(keywords, true);
266 }
267
268 public String[] keywords(String keywords, boolean lowerCase) {
269 if (lowerCase) {
270 keywords = keywords.toLowerCase();
271 }
272
273 keywords = keywords.trim();
274
275 String[] keywordsArray = StringUtil.split(keywords, StringPool.SPACE);
276
277 for (int i = 0; i < keywordsArray.length; i++) {
278 String keyword = keywordsArray[i];
279
280 keywordsArray[i] =
281 StringPool.PERCENT + keyword + StringPool.PERCENT;
282 }
283
284 return keywordsArray;
285 }
286
287 public String[] keywords(String[] keywordsArray) {
288 return keywords(keywordsArray, true);
289 }
290
291 public String[] keywords(String[] keywordsArray, boolean lowerCase) {
292 if ((keywordsArray == null) || (keywordsArray.length == 0)) {
293 keywordsArray = new String[] {null};
294 }
295
296 if (lowerCase) {
297 for (int i = 0; i < keywordsArray.length; i++) {
298 keywordsArray[i] = StringUtil.lowerCase(keywordsArray[i]);
299 }
300 }
301
302 return keywordsArray;
303 }
304
305 public String replaceAndOperator(String sql, boolean andOperator) {
306 String andOrConnector = "OR";
307 String andOrNullCheck = "AND ? IS NOT NULL";
308
309 if (andOperator) {
310 andOrConnector = "AND";
311 andOrNullCheck = "OR ? IS NULL";
312 }
313
314 sql = StringUtil.replace(
315 sql,
316 new String[] {
317 "[$AND_OR_CONNECTOR$]", "[$AND_OR_NULL_CHECK$]"
318 },
319 new String[] {
320 andOrConnector, andOrNullCheck
321 });
322
323 if (_vendorPostgreSQL) {
324 sql = StringUtil.replace(
325 sql,
326 new String[] {
327 "Date >= ? AND ? IS NOT NULL",
328 "Date <= ? AND ? IS NOT NULL",
329 "Date >= ? OR ? IS NULL",
330 "Date <= ? OR ? IS NULL"
331 },
332 new String[] {
333 "Date >= ? AND CAST(? AS TIMESTAMP) IS NOT NULL",
334 "Date <= ? AND CAST(? AS TIMESTAMP) IS NOT NULL",
335 "Date >= ? OR CAST(? AS TIMESTAMP) IS NULL",
336 "Date <= ? OR CAST(? AS TIMESTAMP) IS NULL"
337 });
338 }
339
340 sql = replaceIsNull(sql);
341
342 return sql;
343 }
344
345 public String replaceIsNull(String sql) {
346 if (Validator.isNotNull(_functionIsNull)) {
347 sql = StringUtil.replace(
348 sql,
349 new String[] {
350 "? IS NULL", "? IS NOT NULL"
351 },
352 new String[] {
353 _functionIsNull,
354 _functionIsNotNull
355 });
356 }
357
358 return sql;
359 }
360
361 public String replaceKeywords(
362 String sql, String field, String operator, boolean last,
363 String[] values) {
364
365 if (values.length == 0) {
366 return sql;
367 }
368
369 StringBuilder oldSql = new StringBuilder();
370
371 oldSql.append("(");
372 oldSql.append(field);
373 oldSql.append(" ");
374 oldSql.append(operator);
375 oldSql.append(" ? [$AND_OR_NULL_CHECK$])");
376
377 if (!last) {
378 oldSql.append(" [$AND_OR_CONNECTOR$]");
379 }
380
381 StringBuilder newSql = new StringBuilder();
382
383 newSql.append("(");
384
385 for (int i = 0; i < values.length; i++) {
386 if (i > 0) {
387 newSql.append(" OR ");
388 }
389
390 newSql.append("(");
391 newSql.append(field);
392 newSql.append(" ");
393 newSql.append(operator);
394 newSql.append(" ? [$AND_OR_NULL_CHECK$])");
395 }
396
397 newSql.append(")");
398
399 if (!last) {
400 newSql.append(" [$AND_OR_CONNECTOR$]");
401 }
402
403 return StringUtil.replace(sql, oldSql.toString(), newSql.toString());
404 }
405
406 public String removeOrderBy(String sql) {
407 int pos = sql.indexOf(" ORDER BY ");
408
409 if (pos != -1) {
410 sql = sql.substring(0, pos);
411 }
412
413 return sql;
414 }
415
416 public String replaceOrderBy(String sql, OrderByComparator obc) {
417 if (obc == null) {
418 return sql;
419 }
420
421 StringBuilder sb = new StringBuilder();
422
423 sb.append(removeOrderBy(sql));
424 sb.append(" ORDER BY ");
425 sb.append(obc.getOrderBy());
426
427 return sb.toString();
428 }
429
430 protected String[] getConfigs() {
431 if (PortalClassLoaderUtil.getClassLoader() ==
432 CustomSQL.class.getClassLoader()) {
433
434 Properties propsUtil = PortalUtil.getPortalProperties();
435
436 return StringUtil.split(
437 propsUtil.getProperty("custom.sql.configs"));
438 }
439 else {
440 return new String[] {"custom-sql/default.xml"};
441 }
442 }
443
444 protected void read(ClassLoader classLoader, String source)
445 throws Exception {
446
447 String xml = null;
448
449 try {
450 xml = StringUtil.read(classLoader, source);
451 }
452 catch (Exception e) {
453 _log.warn("Cannot load " + source);
454 }
455
456 if (xml == null) {
457 return;
458 }
459
460 if (_log.isDebugEnabled()) {
461 _log.debug("Loading " + source);
462 }
463
464 Document doc = SAXReaderUtil.read(new StringReader(xml));
465
466 Element root = doc.getRootElement();
467
468 Iterator<Element> itr = root.elements("sql").iterator();
469
470 while (itr.hasNext()) {
471 Element sql = itr.next();
472
473 String file = sql.attributeValue("file");
474
475 if (Validator.isNotNull(file)) {
476 read(classLoader, file);
477 }
478 else {
479 String id = sql.attributeValue("id");
480 String content = transform(sql.getText());
481
482 content = replaceIsNull(content);
483
484 _sqlPool.put(id, content);
485 }
486 }
487 }
488
489 protected String transform(String sql) {
490 sql = PortalUtil.transformCustomSQL(sql);
491
492 StringBuilder sb = new StringBuilder();
493
494 try {
495 BufferedReader br = new BufferedReader(new StringReader(sql));
496
497 String line = null;
498
499 while ((line = br.readLine()) != null) {
500 sb.append(line.trim());
501 sb.append(StringPool.SPACE);
502 }
503
504 br.close();
505 }
506 catch (IOException ioe) {
507 return sql;
508 }
509
510 return sb.toString();
511 }
512
513 private static Log _log = LogFactory.getLog(CustomSQL.class);
514
515 private boolean _vendorDB2;
516 private boolean _vendorInformix;
517 private boolean _vendorMySQL;
518 private boolean _vendorOracle;
519 private boolean _vendorPostgreSQL;
520 private boolean _vendorSybase;
521 private String _functionIsNull;
522 private String _functionIsNotNull;
523 private Map<String, String> _sqlPool;
524
525 }