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