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