This is an automated email from the ASF dual-hosted git repository.
doebele pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/empire-db.git
The following commit(s) were added to refs/heads/master by this push:
new 69ea173 EMPIREDB-314 SQL-format function support
69ea173 is described below
commit 69ea1739a4c24785f2334d79a52e58999eda7086
Author: Rainer Döbele <[email protected]>
AuthorDate: Wed Oct 23 10:07:39 2019 +0200
EMPIREDB-314
SQL-format function support
---
.../java/org/apache/empire/db/DBColumnExpr.java | 12 +++++++
.../main/java/org/apache/empire/db/DBCommand.java | 26 +++++++++++++--
.../org/apache/empire/db/DBDatabaseDriver.java | 1 +
.../empire/db/derby/DBDatabaseDriverDerby.java | 2 ++
.../empire/db/expr/column/DBAbstractFuncExpr.java | 38 +++++++++++++++++++---
.../apache/empire/db/h2/DBDatabaseDriverH2.java | 1 +
.../empire/db/hsql/DBDatabaseDriverHSql.java | 1 +
.../empire/db/mysql/DBDatabaseDriverMySQL.java | 1 +
.../empire/db/oracle/DBDatabaseDriverOracle.java | 1 +
.../db/postgresql/DBDatabaseDriverPostgreSQL.java | 1 +
.../empire/db/sqlite/DBDatabaseDriverSQLite.java | 1 +
.../empire/db/sqlserver/DBDatabaseDriverMSSQL.java | 1 +
12 files changed, 78 insertions(+), 8 deletions(-)
diff --git a/empire-db/src/main/java/org/apache/empire/db/DBColumnExpr.java
b/empire-db/src/main/java/org/apache/empire/db/DBColumnExpr.java
index 3c2b33e..61e6492 100644
--- a/empire-db/src/main/java/org/apache/empire/db/DBColumnExpr.java
+++ b/empire-db/src/main/java/org/apache/empire/db/DBColumnExpr.java
@@ -864,6 +864,18 @@ public abstract class DBColumnExpr extends DBExpr
}
/**
+ * Formats a column-expression using a format string
+ * This function is intended for formatting numbers.
+ * Formatting any other data types may not supported and be database
specific
+ * @param format the format string. Beware: This is passed to the database
"as is" and hence may be database specific.
+ * @return a string expression representing the formatted value
+ */
+ public DBColumnExpr format(String format)
+ {
+ return getExprFromPhrase(DBDatabaseDriver.SQL_FUNC_FORMAT, new
Object[] { format }, getUpdateColumn(), false, DataType.VARCHAR);
+ }
+
+ /**
* Creates and returns a sql-expression that returns the string length of
this expression.
* @return the new DBFuncExpr object
diff --git a/empire-db/src/main/java/org/apache/empire/db/DBCommand.java
b/empire-db/src/main/java/org/apache/empire/db/DBCommand.java
index 74778b3..caffa5a 100644
--- a/empire-db/src/main/java/org/apache/empire/db/DBCommand.java
+++ b/empire-db/src/main/java/org/apache/empire/db/DBCommand.java
@@ -778,11 +778,11 @@ public abstract class DBCommand extends DBCommandExpr
return;
removeConstraintOn(where, col);
}
-
+
/**
* Returns a copy of the defined joins.
*
- * @return vector of joins
+ * @return the list of joins
*/
public List<DBJoinExpr> getJoins()
{
@@ -813,9 +813,29 @@ public abstract class DBCommand extends DBCommandExpr
having = new ArrayList<DBCompareExpr>();
setConstraint(having, expr);
}
+
+ /**
+ * Returns true if the command has having-constraints or false if not.
+ *
+ * @return true if constraints have been set on the command
+ */
+ public boolean hasHavingConstraints()
+ {
+ return (having!=null && having.size()>0);
+ }
+
+ /**
+ * Returns a copy of the defined having clauses.
+ *
+ * @return list of having constraints
+ */
+ public List<DBCompareExpr> getHavingConstraints()
+ {
+ return (this.having!=null ? Collections.unmodifiableList(this.having)
: null);
+ }
/**
- * removes a constraint on a particular column from the where clause
+ * removes a constraint on a particular column from the having clause
* @param col the column expression for which to remove the constraint
*/
public void removeHavingConstraintOn(DBColumnExpr col)
diff --git a/empire-db/src/main/java/org/apache/empire/db/DBDatabaseDriver.java
b/empire-db/src/main/java/org/apache/empire/db/DBDatabaseDriver.java
index 44b09c9..4aacd7b 100644
--- a/empire-db/src/main/java/org/apache/empire/db/DBDatabaseDriver.java
+++ b/empire-db/src/main/java/org/apache/empire/db/DBDatabaseDriver.java
@@ -94,6 +94,7 @@ public abstract class DBDatabaseDriver implements Serializable
public static final int SQL_FUNC_FLOOR = 123; // Oracle: floor(?)
public static final int SQL_FUNC_CEILING = 124; // Oracle: ceil(?)
public static final int SQL_FUNC_MODULO = 125; // Oracle: mod(?)
+ public static final int SQL_FUNC_FORMAT = 126; // Oracle: TO_CHAR(?)
// Date
public static final int SQL_FUNC_DAY = 132; // MSSQL: month(?)
public static final int SQL_FUNC_MONTH = 133; // MSSQL: month(?)
diff --git
a/empire-db/src/main/java/org/apache/empire/db/derby/DBDatabaseDriverDerby.java
b/empire-db/src/main/java/org/apache/empire/db/derby/DBDatabaseDriverDerby.java
index e60b6a8..8eed386 100644
---
a/empire-db/src/main/java/org/apache/empire/db/derby/DBDatabaseDriverDerby.java
+++
b/empire-db/src/main/java/org/apache/empire/db/derby/DBDatabaseDriverDerby.java
@@ -220,6 +220,8 @@ public class DBDatabaseDriverDerby extends DBDatabaseDriver
case SQL_FUNC_TRUNC: return "truncate(?,{0})";
case SQL_FUNC_CEILING: return "ceiling(?)";
case SQL_FUNC_FLOOR: return "floor(?)";
+ case SQL_FUNC_MODULO: return "mod(?,{0})";
+ case SQL_FUNC_FORMAT: return "format(?, {0:VARCHAR})";
// Date
case SQL_FUNC_DAY: return "day(?)";
case SQL_FUNC_MONTH: return "month(?)";
diff --git
a/empire-db/src/main/java/org/apache/empire/db/expr/column/DBAbstractFuncExpr.java
b/empire-db/src/main/java/org/apache/empire/db/expr/column/DBAbstractFuncExpr.java
index ffff472..d89fd3d 100644
---
a/empire-db/src/main/java/org/apache/empire/db/expr/column/DBAbstractFuncExpr.java
+++
b/empire-db/src/main/java/org/apache/empire/db/expr/column/DBAbstractFuncExpr.java
@@ -27,7 +27,10 @@ import org.apache.empire.db.DBColumn;
import org.apache.empire.db.DBColumnExpr;
import org.apache.empire.db.DBDatabase;
import org.apache.empire.db.DBDatabaseDriver;
+import org.apache.empire.exceptions.InvalidArgumentException;
import org.apache.empire.xml.XMLUtil;
+import org.slf4j.Logger;
+import org.slf4j.LoggerFactory;
import org.w3c.dom.Element;
@@ -37,6 +40,7 @@ import org.w3c.dom.Element;
public abstract class DBAbstractFuncExpr extends DBColumnExpr
{
private final static long serialVersionUID = 1L;
+ private static final Logger log =
LoggerFactory.getLogger(DBAbstractFuncExpr.class);
protected final DBColumnExpr expr;
protected final DBColumn updateColumn; // optional
@@ -156,13 +160,37 @@ public abstract class DBAbstractFuncExpr extends
DBColumnExpr
{
// Get Template
if (params != null)
- { // Replace Params
- DataType dataType = expr.getDataType();
+ { // Replace Params
for (int i = 0; i < params.length; i++)
- { // String test =(params[i] != null) ? params[i].toString() :
"";
- String value = getObjectValue(dataType, params[i],
CTX_DEFAULT, ",");
+ { // Detect placeholder and data type
+ DataType paramDataType = expr.getDataType();
+ int idx;
+ String ph = "{"+String.valueOf(i);
+ if ((idx=template.indexOf(ph))>=0) {
+ // param found
+ idx += ph.length();
+ int end = template.indexOf('}', idx);
+ if (end<idx)
+ throw new InvalidArgumentException("template",
template);
+ // check if type is specified
+ if (template.charAt(idx)==':')
+ { // DataType is specified
+ String typeName = ((end>=idx) ?
template.substring(idx+1, end) : null);
+ DataType dataType = ((typeName!=null) ?
DataType.valueOf(typeName) : null);
+ if (dataType!=null)
+ paramDataType = dataType;
+ }
+ // complete placeholder
+ ph += template.substring(idx, end+1);
+
+ } else {
+ log.warn("No placeholder found in template {} for paramter
{}", template, i);
+ continue;
+ }
+ // get param and replace
+ String paramAsString = getObjectValue(paramDataType,
params[i], CTX_DEFAULT, ",");
// template = template.replaceAll("\\{" + String.valueOf(i) +
"\\}", value);
- template = StringUtils.replaceAll(template, "{"+
String.valueOf(i) + "}", value);
+ template = StringUtils.replaceAll(template, ph, paramAsString);
}
}
// Get Prefix and Postfix
diff --git
a/empire-db/src/main/java/org/apache/empire/db/h2/DBDatabaseDriverH2.java
b/empire-db/src/main/java/org/apache/empire/db/h2/DBDatabaseDriverH2.java
index 5b168ba..67289ec 100644
--- a/empire-db/src/main/java/org/apache/empire/db/h2/DBDatabaseDriverH2.java
+++ b/empire-db/src/main/java/org/apache/empire/db/h2/DBDatabaseDriverH2.java
@@ -268,6 +268,7 @@ public class DBDatabaseDriverH2 extends DBDatabaseDriver
case SQL_FUNC_CEILING: return "ceiling(?)";
case SQL_FUNC_FLOOR: return "floor(?)";
case SQL_FUNC_MODULO: return "mod(?,{0})";
+ case SQL_FUNC_FORMAT: return "format(?, {0:VARCHAR})";
// Date
case SQL_FUNC_DAY: return "day(?)";
case SQL_FUNC_MONTH: return "month(?)";
diff --git
a/empire-db/src/main/java/org/apache/empire/db/hsql/DBDatabaseDriverHSql.java
b/empire-db/src/main/java/org/apache/empire/db/hsql/DBDatabaseDriverHSql.java
index 1ae3104..72a2f03 100644
---
a/empire-db/src/main/java/org/apache/empire/db/hsql/DBDatabaseDriverHSql.java
+++
b/empire-db/src/main/java/org/apache/empire/db/hsql/DBDatabaseDriverHSql.java
@@ -160,6 +160,7 @@ public class DBDatabaseDriverHSql extends DBDatabaseDriver
case SQL_FUNC_CEILING: return "ceiling(?)";
case SQL_FUNC_FLOOR: return "floor(?)";
case SQL_FUNC_MODULO: return "mod(?,{0})";
+ case SQL_FUNC_FORMAT: return "TO_CHAR(?, {0:VARCHAR})";
// Date
case SQL_FUNC_DAY: return "day(?)";
case SQL_FUNC_MONTH: return "month(?)";
diff --git
a/empire-db/src/main/java/org/apache/empire/db/mysql/DBDatabaseDriverMySQL.java
b/empire-db/src/main/java/org/apache/empire/db/mysql/DBDatabaseDriverMySQL.java
index 7b57ba5..11c9859 100644
---
a/empire-db/src/main/java/org/apache/empire/db/mysql/DBDatabaseDriverMySQL.java
+++
b/empire-db/src/main/java/org/apache/empire/db/mysql/DBDatabaseDriverMySQL.java
@@ -1028,6 +1028,7 @@ public class DBDatabaseDriverMySQL extends
DBDatabaseDriver
case SQL_FUNC_CEILING: return "ceiling(?)";
case SQL_FUNC_FLOOR: return "floor(?)";
case SQL_FUNC_MODULO: return "mod(?,{0})";
+ case SQL_FUNC_FORMAT: return "format(?, {0:INTEGER})";
/* TODO: supports only decimal places. Add support for a format string */
// Date
case SQL_FUNC_DAY: return "day(?)";
case SQL_FUNC_MONTH: return "month(?)";
diff --git
a/empire-db/src/main/java/org/apache/empire/db/oracle/DBDatabaseDriverOracle.java
b/empire-db/src/main/java/org/apache/empire/db/oracle/DBDatabaseDriverOracle.java
index 32556df..c25e7b9 100644
---
a/empire-db/src/main/java/org/apache/empire/db/oracle/DBDatabaseDriverOracle.java
+++
b/empire-db/src/main/java/org/apache/empire/db/oracle/DBDatabaseDriverOracle.java
@@ -189,6 +189,7 @@ public class DBDatabaseDriverOracle extends DBDatabaseDriver
case SQL_FUNC_CEILING: return "ceil(?)";
case SQL_FUNC_FLOOR: return "floor(?)";
case SQL_FUNC_MODULO: return "mod(?,{0})";
+ case SQL_FUNC_FORMAT: return "TO_CHAR(?,
{0:VARCHAR})";
// Date
case SQL_FUNC_DAY: return oracle8Compatibilty ?
"to_number(to_char(?,'DD'))" : "extract(day from ?)";
case SQL_FUNC_MONTH: return oracle8Compatibilty ?
"to_number(to_char(?,'MM'))" : "extract(month from ?)";
diff --git
a/empire-db/src/main/java/org/apache/empire/db/postgresql/DBDatabaseDriverPostgreSQL.java
b/empire-db/src/main/java/org/apache/empire/db/postgresql/DBDatabaseDriverPostgreSQL.java
index ce279b5..39965e5 100644
---
a/empire-db/src/main/java/org/apache/empire/db/postgresql/DBDatabaseDriverPostgreSQL.java
+++
b/empire-db/src/main/java/org/apache/empire/db/postgresql/DBDatabaseDriverPostgreSQL.java
@@ -368,6 +368,7 @@ public class DBDatabaseDriverPostgreSQL extends
DBDatabaseDriver
case SQL_FUNC_CEILING: return "ceiling(?)";
case SQL_FUNC_FLOOR: return "floor(?)";
case SQL_FUNC_MODULO: return "mod(?,{0})";
+ case SQL_FUNC_FORMAT: return "format({0:VARCHAR}, ?)";
// Date
case SQL_FUNC_DAY: return "extract(day
from ?)";
case SQL_FUNC_MONTH: return "extract(month
from ?)";
diff --git
a/empire-db/src/main/java/org/apache/empire/db/sqlite/DBDatabaseDriverSQLite.java
b/empire-db/src/main/java/org/apache/empire/db/sqlite/DBDatabaseDriverSQLite.java
index a516c1e..d038427 100644
---
a/empire-db/src/main/java/org/apache/empire/db/sqlite/DBDatabaseDriverSQLite.java
+++
b/empire-db/src/main/java/org/apache/empire/db/sqlite/DBDatabaseDriverSQLite.java
@@ -349,6 +349,7 @@ public class DBDatabaseDriverSQLite extends DBDatabaseDriver
case SQL_FUNC_CEILING: return "ceiling(?)";
case SQL_FUNC_FLOOR: return "floor(?)";
case SQL_FUNC_MODULO: return "mod(?,{0})";
+ case SQL_FUNC_FORMAT: return "printf({0:VARCHAR}, ?)";
// Date
case SQL_FUNC_DAY: return "day(?)";
case SQL_FUNC_MONTH: return "month(?)";
diff --git
a/empire-db/src/main/java/org/apache/empire/db/sqlserver/DBDatabaseDriverMSSQL.java
b/empire-db/src/main/java/org/apache/empire/db/sqlserver/DBDatabaseDriverMSSQL.java
index 4804f5c..d013245 100644
---
a/empire-db/src/main/java/org/apache/empire/db/sqlserver/DBDatabaseDriverMSSQL.java
+++
b/empire-db/src/main/java/org/apache/empire/db/sqlserver/DBDatabaseDriverMSSQL.java
@@ -337,6 +337,7 @@ public class DBDatabaseDriverMSSQL extends DBDatabaseDriver
case SQL_FUNC_CEILING: return "ceiling(?)";
case SQL_FUNC_FLOOR: return "floor(?)";
case SQL_FUNC_MODULO: return "((?) % {0})";
+ case SQL_FUNC_FORMAT: return "format(?, {0:VARCHAR})";
// Date
case SQL_FUNC_DAY: return "day(?)";
case SQL_FUNC_MONTH: return "month(?)";
