EMPIREDB-381 Postgres improvement No 1
diff --git a/empire-db/src/main/java/org/apache/empire/db/DBColumn.java b/empire-db/src/main/java/org/apache/empire/db/DBColumn.java
index 849a767..c8e68d4 100644
--- a/empire-db/src/main/java/org/apache/empire/db/DBColumn.java
+++ b/empire-db/src/main/java/org/apache/empire/db/DBColumn.java
@@ -25,7 +25,9 @@
import org.apache.empire.commons.Options;
import org.apache.empire.commons.StringUtils;
import org.apache.empire.data.Column;
+import org.apache.empire.data.DataType;
import org.apache.empire.db.exceptions.DatabaseNotOpenException;
+import org.apache.empire.db.expr.column.DBValueExpr;
import org.apache.empire.db.expr.set.DBSetExpr;
import org.apache.empire.dbms.DBMSHandler;
import org.apache.empire.exceptions.ObjectNotValidException;
@@ -309,6 +311,16 @@
}
/**
+ * Returns the name of the column as a value expression
+ * This may be used to reference a parent column in a subquery
+ * @return the column value expression
+ */
+ public DBValueExpr value()
+ {
+ return new DBValueExpr(getDatabase(), this, DataType.UNKNOWN);
+ }
+
+ /**
* Returns the full qualified column name.
*
* @return the full qualified column name
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 63b0270..26d5284 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
@@ -730,7 +730,7 @@
*/
public DBColumnExpr modulo(Object divisor)
{
- return getExprFromPhrase(DBSqlPhrase.SQL_FUNC_MODULO, new Object[] { divisor });
+ return getExprFromPhrase(DBSqlPhrase.SQL_FUNC_MOD, new Object[] { divisor });
}
/**
@@ -1094,9 +1094,19 @@
* @param separator the separator between string
* @return the new DBFuncExpr object
*/
- public DBColumnExpr strAgg(String separator)
+ public DBColumnExpr stringAgg(String separator, DBOrderByExpr orderBy)
{
- return getExprFromPhrase(DBSqlPhrase.SQL_FUNC_STRAGG, new Object[] { separator });
+ return getExprFromPhrase(DBSqlPhrase.SQL_FUNC_STRAGG, new Object[] { separator, orderBy });
+ }
+
+ /**
+ * Creates and returns string aggregation expression
+ * @param separator the separator between string
+ * @return the new DBFuncExpr object
+ */
+ public DBColumnExpr stringAgg(String separator)
+ {
+ return stringAgg(separator, this.asc());
}
/**
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 c76ba31..a8927db 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
@@ -239,9 +239,6 @@
{
if (cmdParams==null)
return; // Nothing to do
- // unwrap
- if (cmpExpr instanceof Unwrappable<?>)
- cmpExpr = (DBCompareExpr)((Unwrappable<?>)cmpExpr).unwrap();
// check type
if (cmpExpr instanceof DBCompareColExpr)
{ // DBCompareColExpr
@@ -258,6 +255,10 @@
{ // DBCompareNotExpr
removeCommandParams(((DBCompareNotExpr)cmpExpr).getExpr());
}
+ else if ((cmpExpr instanceof Unwrappable<?>) && ((Unwrappable<?>)cmpExpr).isWrapper())
+ { // unwrap
+ removeCommandParams((DBCompareExpr)((Unwrappable<?>)cmpExpr).unwrap());
+ }
}
/**
@@ -1384,7 +1385,7 @@
if (!(cmp instanceof DBCompareColExpr))
continue;
// Compare columns
- DBColumnExpr cmpCol = ((DBCompareColExpr)cmp).getColumn();
+ DBColumnExpr cmpCol = ((DBCompareColExpr)cmp).getColumnExpr();
if (ObjectUtils.compareEqual(cmpCol, colExpr))
{ // Check if we replace a DBCommandParam
removeCommandParams(cmp);
@@ -1559,15 +1560,12 @@
return buf.toString();
}
+ @SuppressWarnings("unchecked")
protected void appendCompareColExprs(DBRowSet table, DBCompareExpr expr, List<DBCompareColExpr> list)
{
- // unwrap
- if (expr instanceof Unwrappable<?>)
- expr = (DBCompareExpr)((Unwrappable<?>)expr).unwrap();
- // check type
if (expr instanceof DBCompareColExpr)
{ // DBCompareColExpr
- DBColumn column = ((DBCompareColExpr)expr).getColumn().getUpdateColumn();
+ DBColumn column = ((DBCompareColExpr)expr).getColumnExpr().getUpdateColumn();
if (column!=null && column.getRowSet().equals(table) && !hasSetExprOn(column))
list.add((DBCompareColExpr)expr);
}
@@ -1580,6 +1578,10 @@
{ // DBCompareNotExpr
appendCompareColExprs(table, ((DBCompareNotExpr)expr).getExpr(), list);
}
+ else if ((expr instanceof Unwrappable<?>) && ((Unwrappable<?>)expr).isWrapper())
+ { // unwrap
+ appendCompareColExprs(table, ((Unwrappable<DBCompareExpr>)expr).unwrap(), list);
+ }
}
/**
diff --git a/empire-db/src/main/java/org/apache/empire/db/DBCommandExpr.java b/empire-db/src/main/java/org/apache/empire/db/DBCommandExpr.java
index 934e3f4..9a8beb7 100644
--- a/empire-db/src/main/java/org/apache/empire/db/DBCommandExpr.java
+++ b/empire-db/src/main/java/org/apache/empire/db/DBCommandExpr.java
@@ -25,6 +25,7 @@
import org.apache.empire.commons.Options;
import org.apache.empire.data.DataType;
+import org.apache.empire.db.expr.column.DBCmdResultExpr;
import org.apache.empire.db.expr.compare.DBCompareExpr;
import org.apache.empire.db.expr.order.DBOrderByExpr;
import org.apache.empire.dbms.DBMSHandler;
@@ -367,6 +368,19 @@
public abstract DataType getDataType();
/**
+ * Returns a ColumnExpr for the result of the query
+ * If the command must have exactly one select column otherwise a NotSupportedException is thrown;
+ * @return the result expression
+ */
+ public DBColumnExpr result()
+ { try {
+ return new DBCmdResultExpr(this);
+ } catch(InvalidArgumentException e) {
+ throw new NotSupportedException(this, "result");
+ }
+ }
+
+ /**
* Creates the SQL-Command.
*
* @param buf the SQL-Command
diff --git a/empire-db/src/main/java/org/apache/empire/db/DBQuery.java b/empire-db/src/main/java/org/apache/empire/db/DBQuery.java
index 553ff8e..5547767 100644
--- a/empire-db/src/main/java/org/apache/empire/db/DBQuery.java
+++ b/empire-db/src/main/java/org/apache/empire/db/DBQuery.java
@@ -496,12 +496,12 @@
if (cmp instanceof DBCompareColExpr)
{ // Check whether constraint belongs to update table
DBCompareColExpr cmpExpr = (DBCompareColExpr) cmp;
- DBColumn col = cmpExpr.getColumn().getUpdateColumn();
+ DBColumn col = cmpExpr.getColumnExpr().getUpdateColumn();
if (col!=null && col.getRowSet() == table)
{ // add the constraint
if (cmpExpr.getValue() instanceof DBCmdParam)
{ // Create a new command param
- DBColumnExpr colExpr = cmpExpr.getColumn();
+ DBColumnExpr colExpr = cmpExpr.getColumnExpr();
DBCmdParam param =(DBCmdParam)cmpExpr.getValue();
DBCmdParam value = upd.addParam(colExpr, param.getValue());
cmp = new DBCompareColExpr(colExpr, cmpExpr.getCmpOperator(), value);
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 b2cf4a5..5ff1cd4 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
@@ -26,6 +26,7 @@
import org.apache.empire.db.DBColumn;
import org.apache.empire.db.DBColumnExpr;
import org.apache.empire.db.DBDatabase;
+import org.apache.empire.db.exceptions.DatabaseNotOpenException;
import org.apache.empire.dbms.DBMSHandler;
import org.apache.empire.exceptions.InvalidArgumentException;
import org.apache.empire.xml.XMLUtil;
@@ -72,10 +73,15 @@
* returns the Database dbms or null if the Expression is not attached to an open database<BR>
* This function is intended for convenience only.
*/
- protected final DBMSHandler getDbms()
+ protected DBMSHandler getDbms()
{
- DBDatabase db = getDatabase();
- return (db!=null) ? db.getDbms() : null;
+ DBDatabase db = expr.getDatabase();
+ if (db==null)
+ throw new InvalidArgumentException("expr", expr);
+ DBMSHandler dbms = db.getDbms();
+ if (dbms==null)
+ throw new DatabaseNotOpenException(db);
+ return dbms;
}
/**
@@ -227,7 +233,7 @@
ph += template.substring(idx, end+1);
} else {
- log.warn("No placeholder found in template {} for paramter {}", template, i);
+ log.info("No placeholder found in template {} for paramter {}", template, i);
continue;
}
// get param and replace
diff --git a/empire-db/src/main/java/org/apache/empire/db/expr/column/DBCmdResultExpr.java b/empire-db/src/main/java/org/apache/empire/db/expr/column/DBCmdResultExpr.java
new file mode 100644
index 0000000..862ee17
--- /dev/null
+++ b/empire-db/src/main/java/org/apache/empire/db/expr/column/DBCmdResultExpr.java
@@ -0,0 +1,118 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+package org.apache.empire.db.expr.column;
+
+import java.util.Set;
+
+import org.apache.empire.data.DataType;
+import org.apache.empire.db.DBColumn;
+import org.apache.empire.db.DBColumnExpr;
+import org.apache.empire.db.DBCommandExpr;
+import org.apache.empire.db.DBDatabase;
+import org.apache.empire.exceptions.InvalidArgumentException;
+import org.apache.empire.xml.XMLUtil;
+import org.w3c.dom.Element;
+
+public class DBCmdResultExpr extends DBColumnExpr
+{
+ private final DBCommandExpr cmdExpr;
+
+ private final DBColumnExpr result;
+
+ public DBCmdResultExpr(DBCommandExpr cmdExpr)
+ {
+ this.cmdExpr = cmdExpr;
+ // get the result column
+ DBColumnExpr[] sel = cmdExpr.getSelectExprList();
+ if (sel.length!=1)
+ throw new InvalidArgumentException("cmdExpr", cmdExpr);
+ // result
+ this.result = sel[0];
+ }
+
+ @Override
+ public <T extends DBDatabase> T getDatabase()
+ {
+ return cmdExpr.getDatabase();
+ }
+
+ @Override
+ public Class<Enum<?>> getEnumType()
+ {
+ return result.getEnumType();
+ }
+
+ @Override
+ public DataType getDataType()
+ {
+ return result.getDataType();
+ }
+
+ @Override
+ public String getName()
+ {
+ return "SEL_"+result.getName();
+ }
+
+ @Override
+ public boolean isAggregate()
+ {
+ /* does not need a GROUP_BY */
+ return false;
+ }
+
+ @Override
+ public DBColumn getSourceColumn()
+ {
+ return null;
+ }
+
+ @Override
+ public DBColumn getUpdateColumn()
+ {
+ return null;
+ }
+
+ @Override
+ public void addReferencedColumns(Set<DBColumn> list)
+ {
+ /* NONE */
+ }
+
+ @Override
+ public void addSQL(StringBuilder buf, long context)
+ {
+ // simply forward
+ cmdExpr.addSQL(buf, context);
+ }
+
+ @Override
+ public Element addXml(Element parent, long flags)
+ {
+ // Add a column expression for this function
+ Element elem = XMLUtil.addElement(parent, "column");
+ elem.setAttribute("name", getName());
+ elem.setAttribute("function", "cmd");
+ elem.setAttribute("dataType", getDataType().name());
+ elem.setAttribute("aggregate", "true");
+ // Done
+ return elem;
+ }
+
+}
diff --git a/empire-db/src/main/java/org/apache/empire/db/expr/column/DBFuncExpr.java b/empire-db/src/main/java/org/apache/empire/db/expr/column/DBFuncExpr.java
index 0bbd93c..4feaf9d 100644
--- a/empire-db/src/main/java/org/apache/empire/db/expr/column/DBFuncExpr.java
+++ b/empire-db/src/main/java/org/apache/empire/db/expr/column/DBFuncExpr.java
@@ -20,12 +20,14 @@
import java.util.Set;
+import org.apache.empire.commons.StringUtils;
// Java
import org.apache.empire.data.DataType;
import org.apache.empire.db.DBColumn;
import org.apache.empire.db.DBColumnExpr;
import org.apache.empire.db.DBExpr;
import org.apache.empire.dbms.DBSqlPhrase;
+import org.apache.empire.exceptions.NotSupportedException;
/**
@@ -46,7 +48,7 @@
protected final DBSqlPhrase phrase;
protected final Object[] params;
- protected String template;
+ protected final String template;
/**
* Constructs a new DBFuncExpr object set the specified parameters to this object.
@@ -58,8 +60,6 @@
* @param expr the DBColumnExpr object
* @param phrase the SQL-phrase
* @param params an array of params which will be replaced in the template
- * @param updateColumn optional update column if any. This parameter may be null
- * @param isAggregate indicates whether the function is an aggregate function (sum, min, max, avg, ...)
* @param dataType indicates the data type of the function result
*/
public DBFuncExpr(DBColumnExpr expr, DBSqlPhrase phrase, Object[] params, DataType dataType)
@@ -68,7 +68,9 @@
// Set Phrase and Params
this.phrase = phrase;
this.params = params;
- this.template = null;
+ this.template = getDbms().getSQLPhrase(phrase);
+ if (StringUtils.isEmpty(template))
+ throw new NotSupportedException(getDbms(), phrase.name());
// check
if (phrase==DBSqlPhrase.SQL_FUNC_COALESCE)
log.warn("DBFuncExpr should not be used for SQL_FUNC_COALESCE. Use DBCoalesceExpr instead.");
@@ -102,11 +104,7 @@
// Get the template
if (phrase!=null)
{ // from phrase
- int end = phrase.name().lastIndexOf('_');
- if (end>0)
- return phrase.name().substring(end+1);
- // the phrase
- return phrase.name();
+ return phrase.getFuncName();
}
// Get the first word
if (template!=null)
@@ -157,12 +155,7 @@
*/
@Override
public void addSQL(StringBuilder sql, long context)
- {
- // Get the template
- if (template==null)
- template = getDbms().getSQLPhrase(phrase);
- // Add SQL
+ { // Add SQL
super.addSQL(sql, template, params, context);
}
-
}
\ No newline at end of file
diff --git a/empire-db/src/main/java/org/apache/empire/db/expr/compare/DBCompareAndOrExpr.java b/empire-db/src/main/java/org/apache/empire/db/expr/compare/DBCompareAndOrExpr.java
index 10a8c78..e052054 100644
--- a/empire-db/src/main/java/org/apache/empire/db/expr/compare/DBCompareAndOrExpr.java
+++ b/empire-db/src/main/java/org/apache/empire/db/expr/compare/DBCompareAndOrExpr.java
@@ -169,12 +169,12 @@
@Override
public boolean isMutuallyExclusive(DBCompareExpr other)
{
- if (other instanceof Unwrappable<?>)
- {
+ if ((other instanceof Unwrappable<?>) && ((Unwrappable<?>)other).isWrapper())
+ { // unwrap
other = ((Unwrappable<DBCompareExpr>)other).unwrap();
}
if (other instanceof DBCompareAndOrExpr)
- {
+ { // check other
DBCompareAndOrExpr otherExpr = (DBCompareAndOrExpr)other;
if (left .isMutuallyExclusive(otherExpr.left) &&
right.isMutuallyExclusive(otherExpr.right))
diff --git a/empire-db/src/main/java/org/apache/empire/db/expr/compare/DBCompareColExpr.java b/empire-db/src/main/java/org/apache/empire/db/expr/compare/DBCompareColExpr.java
index 8f5816e..39c5ccf 100644
--- a/empire-db/src/main/java/org/apache/empire/db/expr/compare/DBCompareColExpr.java
+++ b/empire-db/src/main/java/org/apache/empire/db/expr/compare/DBCompareColExpr.java
@@ -80,7 +80,7 @@
* Gets the DBColumnExpr object
* @return the DBColumnExpr object
*/
- public DBColumnExpr getColumn()
+ public DBColumnExpr getColumnExpr()
{
return expr;
}
@@ -301,7 +301,7 @@
texpr = texpr.unwrap();
// other
DBCompareColExpr o = (DBCompareColExpr)other;
- DBColumnExpr oexpr = o.getColumn();
+ DBColumnExpr oexpr = o.getColumnExpr();
if (oexpr.isWrapper())
oexpr = oexpr.unwrap();
// Compare
diff --git a/empire-db/src/main/java/org/apache/empire/db/expr/compare/DBCompareNotExpr.java b/empire-db/src/main/java/org/apache/empire/db/expr/compare/DBCompareNotExpr.java
index 4d19867..04ddfed 100644
--- a/empire-db/src/main/java/org/apache/empire/db/expr/compare/DBCompareNotExpr.java
+++ b/empire-db/src/main/java/org/apache/empire/db/expr/compare/DBCompareNotExpr.java
@@ -132,12 +132,12 @@
@Override
public boolean isMutuallyExclusive(DBCompareExpr other)
{
- if (other instanceof Unwrappable<?>)
- {
+ if ((other instanceof Unwrappable<?>) && ((Unwrappable<?>)other).isWrapper())
+ { // unwrap
other = ((Unwrappable<DBCompareExpr>)other).unwrap();
}
if (other instanceof DBCompareNotExpr)
- {
+ { // compare
DBCompareNotExpr otherNot = (DBCompareNotExpr)other;
return expr.isMutuallyExclusive(otherNot.expr);
}
diff --git a/empire-db/src/main/java/org/apache/empire/db/expr/join/DBCompareJoinExpr.java b/empire-db/src/main/java/org/apache/empire/db/expr/join/DBCompareJoinExpr.java
index 97c91d1..3cb7a03 100644
--- a/empire-db/src/main/java/org/apache/empire/db/expr/join/DBCompareJoinExpr.java
+++ b/empire-db/src/main/java/org/apache/empire/db/expr/join/DBCompareJoinExpr.java
@@ -58,7 +58,7 @@
return findFirstColumn(((DBCompareNotExpr)expr).getExpr());
// Get Column Expr
if (expr instanceof DBCompareColExpr)
- return ((DBCompareColExpr)expr).getColumn();
+ return ((DBCompareColExpr)expr).getColumnExpr();
// Error
log.error("Unknown class found for building a valid JOIN Expression");
return null;
diff --git a/empire-db/src/main/java/org/apache/empire/dbms/DBSqlPhrase.java b/empire-db/src/main/java/org/apache/empire/dbms/DBSqlPhrase.java
index b2d2c21..4f7f9e2 100644
--- a/empire-db/src/main/java/org/apache/empire/dbms/DBSqlPhrase.java
+++ b/empire-db/src/main/java/org/apache/empire/dbms/DBSqlPhrase.java
@@ -34,7 +34,7 @@
// functions
SQL_FUNC_COALESCE ("coalesce(?, {0})"), // Oracle: nvl(?, {0})
- SQL_FUNC_SUBSTRING ("substring(?,{0})"), // Oracle: substr(?,{0})
+ SQL_FUNC_SUBSTRING ("substring(?, {0})"), // Oracle: substr(?,{0})
SQL_FUNC_SUBSTRINGEX ("substring(?, {0}, {1})"), // Oracle: substr(?,{0},{1})
SQL_FUNC_REPLACE ("replace(?, {0}, {1})"), // Oracle: replace(?,{0},{1})
SQL_FUNC_REVERSE ("reverse(?)"), // Oracle: reverse(?)
@@ -55,7 +55,7 @@
SQL_FUNC_TRUNC ("trunc(?, {0})"),
SQL_FUNC_FLOOR ("floor(?)"),
SQL_FUNC_CEILING ("ceiling(?)"), // Oracle: ceil(?)
- SQL_FUNC_MODULO ("((?) % {0})"), // Oracle: mod(?)
+ SQL_FUNC_MOD ("((?) % {0})"), // Oracle: mod(?)
SQL_FUNC_FORMAT ("format(?, {0:VARCHAR})"), // Oracle: TO_CHAR(?, {0:VARCHAR})
// Date
@@ -68,7 +68,7 @@
SQL_FUNC_MAX ("max(?)", true),
SQL_FUNC_MIN ("min(?)", true),
SQL_FUNC_AVG ("avg(?)", true),
- SQL_FUNC_STRAGG ("string_agg(?,{0})", true), // string_agg, LISTAGG
+ SQL_FUNC_STRAGG (null), // Not supported by default
// Decode
SQL_FUNC_DECODE ("case ? {0} end"), // Oracle: decode(? {0})
@@ -76,6 +76,11 @@
SQL_FUNC_DECODE_PART ("when {0} then {1}"), // Oracle: {0}, {1}
SQL_FUNC_DECODE_ELSE ("else {0}"); // Oracle: {0}
+
+ private static final String PREFIX_SQL = "SQL_";
+ private static final String PREFIX_FUNC = "FUNC_";
+
+ private final String funcName;
private final String sqlDefault;
private final boolean aggregate;
@@ -83,6 +88,13 @@
{
this.sqlDefault = sqlDefault;
this.aggregate = aggregate;
+ // get the function name
+ String name = name();
+ if (name.startsWith(PREFIX_SQL))
+ name = name.substring(PREFIX_SQL.length());
+ if (name.startsWith(PREFIX_FUNC))
+ name = name.substring(PREFIX_FUNC.length());
+ this.funcName = name;
}
private DBSqlPhrase(String sqlDefault)
@@ -90,6 +102,11 @@
this(sqlDefault, false);
}
+ public String getFuncName()
+ {
+ return funcName;
+ }
+
public String getSqlDefault()
{
return sqlDefault;
diff --git a/empire-db/src/main/java/org/apache/empire/dbms/derby/DBMSHandlerDerby.java b/empire-db/src/main/java/org/apache/empire/dbms/derby/DBMSHandlerDerby.java
index ad099f7..ae37160 100644
--- a/empire-db/src/main/java/org/apache/empire/dbms/derby/DBMSHandlerDerby.java
+++ b/empire-db/src/main/java/org/apache/empire/dbms/derby/DBMSHandlerDerby.java
@@ -197,7 +197,7 @@
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_MOD: return "mod(?,{0})";
case SQL_FUNC_FORMAT: return "format(?, {0:VARCHAR})";
// Date
case SQL_FUNC_DAY: return "day(?)";
diff --git a/empire-db/src/main/java/org/apache/empire/dbms/h2/DBMSHandlerH2.java b/empire-db/src/main/java/org/apache/empire/dbms/h2/DBMSHandlerH2.java
index f09a6f3..79fed13 100644
--- a/empire-db/src/main/java/org/apache/empire/dbms/h2/DBMSHandlerH2.java
+++ b/empire-db/src/main/java/org/apache/empire/dbms/h2/DBMSHandlerH2.java
@@ -272,7 +272,7 @@
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_MOD: return "mod(?,{0})";
case SQL_FUNC_FORMAT: return "format(?, {0:VARCHAR})";
// Date
case SQL_FUNC_DAY: return "day(?)";
diff --git a/empire-db/src/main/java/org/apache/empire/dbms/hsql/DBMSHandlerHSql.java b/empire-db/src/main/java/org/apache/empire/dbms/hsql/DBMSHandlerHSql.java
index aafefc9..3160a23 100644
--- a/empire-db/src/main/java/org/apache/empire/dbms/hsql/DBMSHandlerHSql.java
+++ b/empire-db/src/main/java/org/apache/empire/dbms/hsql/DBMSHandlerHSql.java
@@ -147,7 +147,7 @@
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_MOD: return "mod(?,{0})";
case SQL_FUNC_FORMAT: return "TO_CHAR(?, {0:VARCHAR})";
// Date
case SQL_FUNC_DAY: return "day(?)";
diff --git a/empire-db/src/main/java/org/apache/empire/dbms/mysql/DBMSHandlerMySQL.java b/empire-db/src/main/java/org/apache/empire/dbms/mysql/DBMSHandlerMySQL.java
index 6dde1af..553beb1 100644
--- a/empire-db/src/main/java/org/apache/empire/dbms/mysql/DBMSHandlerMySQL.java
+++ b/empire-db/src/main/java/org/apache/empire/dbms/mysql/DBMSHandlerMySQL.java
@@ -1028,7 +1028,7 @@
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_MOD: 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(?)";
diff --git a/empire-db/src/main/java/org/apache/empire/dbms/oracle/DBMSHandlerOracle.java b/empire-db/src/main/java/org/apache/empire/dbms/oracle/DBMSHandlerOracle.java
index 43f8ed8..2fc2d0c 100644
--- a/empire-db/src/main/java/org/apache/empire/dbms/oracle/DBMSHandlerOracle.java
+++ b/empire-db/src/main/java/org/apache/empire/dbms/oracle/DBMSHandlerOracle.java
@@ -213,7 +213,7 @@
case SQL_FUNC_TRUNC: return "trunc(?,{0})";
case SQL_FUNC_CEILING: return "ceil(?)";
case SQL_FUNC_FLOOR: return "floor(?)";
- case SQL_FUNC_MODULO: return "mod(?,{0})";
+ case SQL_FUNC_MOD: 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 ?)";
@@ -224,7 +224,7 @@
case SQL_FUNC_MAX: return "max(?)";
case SQL_FUNC_MIN: return "min(?)";
case SQL_FUNC_AVG: return "avg(?)";
- case SQL_FUNC_STRAGG: return "listagg(? {0})";
+ case SQL_FUNC_STRAGG: return "listagg(? {0}) WITHIN GROUP (ORDER BY {1})";
// Others
case SQL_FUNC_DECODE: return "decode(? {0})";
case SQL_FUNC_DECODE_SEP: return ",";
diff --git a/empire-db/src/main/java/org/apache/empire/dbms/postgresql/DBCommandPostgres.java b/empire-db/src/main/java/org/apache/empire/dbms/postgresql/DBCommandPostgres.java
new file mode 100644
index 0000000..df1f45e
--- /dev/null
+++ b/empire-db/src/main/java/org/apache/empire/dbms/postgresql/DBCommandPostgres.java
@@ -0,0 +1,157 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+package org.apache.empire.dbms.postgresql;
+
+import org.apache.empire.data.DataType;
+import org.apache.empire.db.DBColumn;
+import org.apache.empire.db.DBColumnExpr;
+import org.apache.empire.db.DBCommand;
+import org.apache.empire.db.DBRowSet;
+import org.apache.empire.db.exceptions.NoPrimaryKeyException;
+import org.apache.empire.db.expr.column.DBValueExpr;
+import org.apache.empire.db.expr.compare.DBCompareExpr;
+
+/**
+ * Defines the PostgreSQL command type.
+ */
+public class DBCommandPostgres extends DBCommand
+{
+ // *Deprecated* private static final long serialVersionUID = 1L;
+
+ protected int limit = -1;
+ protected int skip = -1;
+
+ public DBCommandPostgres(boolean autoPrepareStmt)
+ {
+ super(autoPrepareStmt);
+ }
+
+ public DBColumnExpr funcAge(DBColumnExpr expr)
+ {
+ return new PostgresFuncExpr(expr, PostgresSqlPhrase.AGE, null, DataType.INTEGER);
+ }
+
+ public DBColumnExpr funcAge(DBColumnExpr expr1, DBColumnExpr expr2)
+ {
+ return new PostgresFuncExpr(expr1, PostgresSqlPhrase.AGE_TWO, new Object[] { expr2 }, DataType.INTEGER);
+ }
+
+ public DBColumnExpr funcExtract(PostgresExtractField field, DBColumnExpr expr)
+ {
+ DBValueExpr fieldExpr = new DBValueExpr(expr.getDatabase(), field.name(), DataType.UNKNOWN);
+ return new PostgresFuncExpr(expr, PostgresSqlPhrase.EXTRACT, new Object[] { fieldExpr }, DataType.INTEGER);
+ }
+
+ public DBColumnExpr funcToTsquery(DBColumnExpr expr)
+ {
+ return new PostgresFuncExpr(expr, PostgresSqlPhrase.TO_TSQUERY, null, DataType.UNKNOWN);
+ }
+
+ public DBColumnExpr funcToTsvector(DBColumnExpr expr)
+ {
+ return new PostgresFuncExpr(expr, PostgresSqlPhrase.TO_TSVECTOR, null, DataType.UNKNOWN);
+ }
+
+ public DBColumnExpr funcPlaintoTsquery(DBColumnExpr expr)
+ {
+ return new PostgresFuncExpr(expr, PostgresSqlPhrase.PLAINTO_TSQUERY, null, DataType.UNKNOWN);
+ }
+
+ public DBColumnExpr funcBoolAnd(DBCompareExpr cmpExpr)
+ {
+ return new PostgresBoolAndOrExpr(cmpExpr, false);
+ }
+
+ public DBColumnExpr funcBoolOr(DBCompareExpr cmpExpr)
+ {
+ return new PostgresBoolAndOrExpr(cmpExpr, true);
+ }
+
+ @Override
+ public DBCommand limitRows(int numRows)
+ {
+ limit = numRows;
+ return this;
+ }
+
+ @Override
+ public DBCommand skipRows(int numRows)
+ {
+ skip = numRows;
+ return this;
+ }
+
+ @Override
+ public void clearLimit()
+ {
+ limit = -1;
+ skip = -1;
+ }
+
+ @Override
+ public void getSelect(StringBuilder buf)
+ { // call base class
+ super.getSelect(buf);
+ // add limit and offset
+ if (limit>=0)
+ { buf.append("\r\nLIMIT ");
+ buf.append(String.valueOf(limit));
+ // Offset
+ if (skip>=0)
+ { buf.append(" OFFSET ");
+ buf.append(String.valueOf(skip));
+ }
+ }
+ }
+
+ @Override
+ protected void addUpdateWithJoins(StringBuilder buf, DBRowSet table)
+ {
+ DBColumn[] keyColumns = table.getKeyColumns();
+ if (keyColumns==null || keyColumns.length==0)
+ throw new NoPrimaryKeyException(table);
+ // Join Update
+ table.addSQL(buf, CTX_NAME);
+ buf.append(" t0");
+ long context = CTX_DEFAULT;
+ // Set Expressions
+ buf.append("\r\nSET ");
+ addListExpr(buf, set, context, ", ");
+ // From clause
+ addFrom(buf);
+ // Add Where
+ buf.append("\r\nWHERE");
+ // key columns
+ for (DBColumn col : keyColumns)
+ { // compare
+ buf.append(" t0.");
+ col.addSQL(buf, CTX_NAME);
+ buf.append("=");
+ buf.append(table.getAlias());
+ buf.append(".");
+ col.addSQL(buf, CTX_NAME);
+ }
+ // more constraints
+ if (where!=null && !where.isEmpty())
+ { // add where expression
+ buf.append("\r\n AND ");
+ addListExpr(buf, where, context, " AND ");
+ }
+ }
+}
diff --git a/empire-db/src/main/java/org/apache/empire/dbms/postgresql/DBMSHandlerPostgreSQL.java b/empire-db/src/main/java/org/apache/empire/dbms/postgresql/DBMSHandlerPostgreSQL.java
index d798160..f731708 100644
--- a/empire-db/src/main/java/org/apache/empire/dbms/postgresql/DBMSHandlerPostgreSQL.java
+++ b/empire-db/src/main/java/org/apache/empire/dbms/postgresql/DBMSHandlerPostgreSQL.java
@@ -26,18 +26,14 @@
import org.apache.empire.commons.ObjectUtils;
import org.apache.empire.commons.StringUtils;
import org.apache.empire.data.DataType;
-import org.apache.empire.db.DBColumn;
import org.apache.empire.db.DBColumnExpr;
-import org.apache.empire.db.DBCommand;
import org.apache.empire.db.DBDDLGenerator;
import org.apache.empire.db.DBDDLGenerator.DDLActionType;
import org.apache.empire.db.DBDatabase;
import org.apache.empire.db.DBObject;
-import org.apache.empire.db.DBRowSet;
import org.apache.empire.db.DBSQLScript;
import org.apache.empire.db.DBTableColumn;
import org.apache.empire.db.exceptions.EmpireSQLException;
-import org.apache.empire.db.exceptions.NoPrimaryKeyException;
import org.apache.empire.db.exceptions.QueryNoResultException;
import org.apache.empire.db.expr.column.DBValueExpr;
import org.apache.empire.dbms.DBMSFeature;
@@ -79,93 +75,6 @@
"END\n" +
"$$ LANGUAGE plpgsql IMMUTABLE RETURNS NULL ON NULL INPUT";
- /**
- * Defines the PostgreSQL command type.
- */
- public static class DBCommandPostreSQL extends DBCommand
- {
- // *Deprecated* private static final long serialVersionUID = 1L;
-
- protected int limit = -1;
- protected int skip = -1;
-
- public DBCommandPostreSQL(boolean autoPrepareStmt)
- {
- super(autoPrepareStmt);
- }
-
- @Override
- public DBCommand limitRows(int numRows)
- {
- limit = numRows;
- return this;
- }
-
- @Override
- public DBCommand skipRows(int numRows)
- {
- skip = numRows;
- return this;
- }
-
- @Override
- public void clearLimit()
- {
- limit = -1;
- skip = -1;
- }
-
- @Override
- public void getSelect(StringBuilder buf)
- { // call base class
- super.getSelect(buf);
- // add limit and offset
- if (limit>=0)
- { buf.append("\r\nLIMIT ");
- buf.append(String.valueOf(limit));
- // Offset
- if (skip>=0)
- { buf.append(" OFFSET ");
- buf.append(String.valueOf(skip));
- }
- }
- }
-
- @Override
- protected void addUpdateWithJoins(StringBuilder buf, DBRowSet table)
- {
- DBColumn[] keyColumns = table.getKeyColumns();
- if (keyColumns==null || keyColumns.length==0)
- throw new NoPrimaryKeyException(table);
- // Join Update
- table.addSQL(buf, CTX_NAME);
- buf.append(" t0");
- long context = CTX_DEFAULT;
- // Set Expressions
- buf.append("\r\nSET ");
- addListExpr(buf, set, context, ", ");
- // From clause
- addFrom(buf);
- // Add Where
- buf.append("\r\nWHERE");
- // key columns
- for (DBColumn col : keyColumns)
- { // compare
- buf.append(" t0.");
- col.addSQL(buf, CTX_NAME);
- buf.append("=");
- buf.append(table.getAlias());
- buf.append(".");
- col.addSQL(buf, CTX_NAME);
- }
- // more constraints
- if (where!=null && !where.isEmpty())
- { // add where expression
- buf.append("\r\n AND ");
- addListExpr(buf, where, context, " AND ");
- }
- }
- }
private String databaseName;
@@ -331,10 +240,9 @@
* @return the new DBCommandPostgreSQL object
*/
@Override
- public DBCommand createCommand(boolean autoPrepareStmt)
- {
- // create command object
- return new DBCommandPostreSQL(autoPrepareStmt);
+ public DBCommandPostgres createCommand(boolean autoPrepareStmt)
+ { // create command object
+ return new DBCommandPostgres(autoPrepareStmt);
}
/**
@@ -412,7 +320,7 @@
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_MOD: return "mod(?,{0})";
case SQL_FUNC_FORMAT: return "format({0:VARCHAR}, ?)";
// Date
case SQL_FUNC_DAY: return "extract(day from ?)";
@@ -423,6 +331,7 @@
case SQL_FUNC_MAX: return "max(?)";
case SQL_FUNC_MIN: return "min(?)";
case SQL_FUNC_AVG: return "avg(?)";
+ case SQL_FUNC_STRAGG: return "STRING_AGG(DISTINCT ? {0} ORDER BY {1})";
// Others
case SQL_FUNC_DECODE: return "case ? {0} end";
case SQL_FUNC_DECODE_SEP: return " ";
@@ -525,7 +434,7 @@
public void getDDLScript(DDLActionType type, DBObject dbo, DBSQLScript script)
{
if (ddlGenerator==null)
- ddlGenerator = new PostgreSQLDDLGenerator(this);
+ ddlGenerator = new PostgresDDLGenerator(this);
// forward request
ddlGenerator.getDDLScript(type, dbo, script);
}
diff --git a/empire-db/src/main/java/org/apache/empire/dbms/postgresql/PostgresBoolAndOrExpr.java b/empire-db/src/main/java/org/apache/empire/dbms/postgresql/PostgresBoolAndOrExpr.java
new file mode 100644
index 0000000..db59da9
--- /dev/null
+++ b/empire-db/src/main/java/org/apache/empire/dbms/postgresql/PostgresBoolAndOrExpr.java
@@ -0,0 +1,148 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+package org.apache.empire.dbms.postgresql;
+
+import java.util.Set;
+
+import org.apache.empire.commons.Unwrappable;
+import org.apache.empire.data.DataType;
+import org.apache.empire.db.DBColumn;
+import org.apache.empire.db.DBColumnExpr;
+import org.apache.empire.db.DBDatabase;
+import org.apache.empire.db.expr.compare.DBCompareAndOrExpr;
+import org.apache.empire.db.expr.compare.DBCompareColExpr;
+import org.apache.empire.db.expr.compare.DBCompareExpr;
+import org.apache.empire.db.expr.compare.DBCompareNotExpr;
+import org.apache.empire.xml.XMLUtil;
+import org.w3c.dom.Element;
+
+public class PostgresBoolAndOrExpr extends DBColumnExpr
+{
+ private static final String BOOL_AND = "BOOL_AND";
+ private static final String BOOL_OR = "BOOL_OR";
+
+ private final DBCompareExpr cmpExpr;
+ private final boolean or;
+ private String name;
+
+ public PostgresBoolAndOrExpr(DBCompareExpr cmpExpr, boolean or)
+ {
+ this.cmpExpr = cmpExpr;
+ this.or = or;
+ }
+
+ @SuppressWarnings("unchecked")
+ @Override
+ public DBDatabase getDatabase()
+ {
+ return cmpExpr.getDatabase();
+ }
+
+ @Override
+ public DataType getDataType()
+ {
+ return DataType.BOOL;
+ }
+
+ @Override
+ public Class<Enum<?>> getEnumType()
+ {
+ return null;
+ }
+
+ @Override
+ public DBColumn getSourceColumn()
+ {
+ return null;
+ }
+
+ @Override
+ public String getName()
+ {
+ if (name==null)
+ { // Build name
+ StringBuilder buf = new StringBuilder();
+ appendName(buf, this.cmpExpr);
+ buf.append("_");
+ buf.append(or ? BOOL_OR : BOOL_AND);
+ name = buf.toString();
+ }
+ return name;
+ }
+
+ @Override
+ public boolean isAggregate()
+ {
+ return true;
+ }
+
+ @Override
+ public DBColumn getUpdateColumn()
+ {
+ return null;
+ }
+
+ @Override
+ public void addReferencedColumns(Set<DBColumn> list)
+ {
+ cmpExpr.addReferencedColumns(list);
+ }
+
+ @Override
+ public void addSQL(StringBuilder buf, long context)
+ {
+
+ buf.append(or ? BOOL_OR : BOOL_AND);
+ buf.append("(");
+ cmpExpr.addSQL(buf, context);
+ buf.append(")");
+ }
+
+ @Override
+ public Element addXml(Element parent, long flags)
+ {
+ // Add a column expression for this function
+ Element elem = XMLUtil.addElement(parent, "column");
+ elem.setAttribute("name", getName());
+ elem.setAttribute("function", (or ? BOOL_OR : BOOL_AND));
+ elem.setAttribute("dataType", getDataType().name());
+ elem.setAttribute("aggregate", "true");
+ return elem;
+ }
+
+ @SuppressWarnings("unchecked")
+ protected void appendName(StringBuilder buf, DBCompareExpr expr)
+ {
+ if ((expr instanceof Unwrappable<?>) && ((Unwrappable<?>)expr).isWrapper())
+ appendName(buf, ((Unwrappable<DBCompareExpr>)expr).unwrap());
+ else if (expr instanceof DBCompareNotExpr)
+ appendName(buf, ((DBCompareNotExpr)expr).getExpr());
+ else if (expr instanceof DBCompareAndOrExpr) {
+ appendName(buf, ((DBCompareAndOrExpr)expr).getLeft());
+ appendName(buf, ((DBCompareAndOrExpr)expr).getRight());
+ }
+ else if (expr instanceof DBCompareColExpr) {
+ DBColumnExpr colExpr = ((DBCompareColExpr)expr).getColumnExpr();
+ if (buf.length()>0)
+ buf.append("_");
+ buf.append(colExpr.getName());
+ }
+ }
+
+}
diff --git a/empire-db/src/main/java/org/apache/empire/dbms/postgresql/PostgreSQLDDLGenerator.java b/empire-db/src/main/java/org/apache/empire/dbms/postgresql/PostgresDDLGenerator.java
similarity index 96%
rename from empire-db/src/main/java/org/apache/empire/dbms/postgresql/PostgreSQLDDLGenerator.java
rename to empire-db/src/main/java/org/apache/empire/dbms/postgresql/PostgresDDLGenerator.java
index 25a686f..edfda3a 100644
--- a/empire-db/src/main/java/org/apache/empire/dbms/postgresql/PostgreSQLDDLGenerator.java
+++ b/empire-db/src/main/java/org/apache/empire/dbms/postgresql/PostgresDDLGenerator.java
@@ -27,9 +27,9 @@
import org.apache.empire.db.DBTable;
import org.apache.empire.db.DBTableColumn;
-public class PostgreSQLDDLGenerator extends DBDDLGenerator<DBMSHandlerPostgreSQL>
+public class PostgresDDLGenerator extends DBDDLGenerator<DBMSHandlerPostgreSQL>
{
- public PostgreSQLDDLGenerator(DBMSHandlerPostgreSQL dbms)
+ public PostgresDDLGenerator(DBMSHandlerPostgreSQL dbms)
{
super(dbms);
// set Oracle specific data types
diff --git a/empire-db/src/main/java/org/apache/empire/dbms/postgresql/PostgresExtractField.java b/empire-db/src/main/java/org/apache/empire/dbms/postgresql/PostgresExtractField.java
new file mode 100644
index 0000000..54059f0
--- /dev/null
+++ b/empire-db/src/main/java/org/apache/empire/dbms/postgresql/PostgresExtractField.java
@@ -0,0 +1,44 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+package org.apache.empire.dbms.postgresql;
+
+public enum PostgresExtractField
+{
+ CENTURY,
+ DAY,
+ DECADE,
+ DOW,
+ DOY,
+ EPOCH,
+ HOUR,
+ ISODOW,
+ ISOYEAR,
+ MICROSECONDS,
+ MILLENNIUM,
+ MILLISECONDS,
+ MINUTE,
+ MONTH,
+ QUARTER,
+ SECOND,
+ TIMEZONE,
+ TIMEZONE_HOUR,
+ TIMEZONE_MINUTE,
+ WEEK,
+ YEAR;
+}
diff --git a/empire-db/src/main/java/org/apache/empire/dbms/postgresql/PostgresFuncExpr.java b/empire-db/src/main/java/org/apache/empire/dbms/postgresql/PostgresFuncExpr.java
new file mode 100644
index 0000000..7522247
--- /dev/null
+++ b/empire-db/src/main/java/org/apache/empire/dbms/postgresql/PostgresFuncExpr.java
@@ -0,0 +1,86 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+package org.apache.empire.dbms.postgresql;
+
+import java.util.Set;
+
+import org.apache.empire.data.DataType;
+import org.apache.empire.db.DBColumn;
+import org.apache.empire.db.DBColumnExpr;
+import org.apache.empire.db.DBExpr;
+import org.apache.empire.db.expr.column.DBAbstractFuncExpr;
+
+public class PostgresFuncExpr extends DBAbstractFuncExpr
+{
+ // *Deprecated* private static final long serialVersionUID = 1L;
+
+ protected final PostgresSqlPhrase phrase;
+ protected final Object[] params;
+
+ /**
+ * @param expr the DBColumnExpr object
+ * @param phrase the SQL-phrase
+ * @param params an array of params which will be replaced in the template
+ * @param dataType indicates the data type of the function result
+ */
+ public PostgresFuncExpr(DBColumnExpr expr, PostgresSqlPhrase phrase, Object[] params, DataType dataType)
+ {
+ super(expr, phrase.isAggregate(), dataType);
+ // Set Phrase and Params
+ this.phrase = phrase;
+ this.params = params;
+ }
+
+ @Override
+ protected String getFunctionName()
+ {
+ return phrase.name();
+ }
+
+ /**
+ * @see org.apache.empire.db.DBExpr#addReferencedColumns(Set)
+ */
+ @Override
+ public void addReferencedColumns(Set<DBColumn> list)
+ {
+ super.addReferencedColumns(list);
+ if (this.params==null)
+ return;
+ // Check params
+ for (int i=0; i<this.params.length; i++)
+ { // add referenced columns
+ if (params[i] instanceof DBExpr)
+ ((DBExpr)params[i]).addReferencedColumns(list);
+ }
+ }
+
+ /**
+ * Creates the SQL-Command adds a function to the SQL-Command.
+ *
+ * @param sql the SQL-Command
+ * @param context the current SQL-Command context
+ */
+ @Override
+ public void addSQL(StringBuilder sql, long context)
+ {
+ // Add SQL
+ super.addSQL(sql, phrase.getSQL(), params, context);
+ }
+
+}
diff --git a/empire-db/src/main/java/org/apache/empire/dbms/postgresql/PostgresSqlPhrase.java b/empire-db/src/main/java/org/apache/empire/dbms/postgresql/PostgresSqlPhrase.java
new file mode 100644
index 0000000..f9c70c0
--- /dev/null
+++ b/empire-db/src/main/java/org/apache/empire/dbms/postgresql/PostgresSqlPhrase.java
@@ -0,0 +1,58 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+package org.apache.empire.dbms.postgresql;
+
+/**
+ * Enum for all SQL phrases that may be supplied by the dbms
+ * @author rainer
+ */
+public enum PostgresSqlPhrase
+{
+ // functions
+ AGE ("AGE(?)"),
+ AGE_TWO ("AGE(?, {0})"),
+ EXTRACT ("EXTRACT({0} from ?)"),
+ TO_TSQUERY ("to_tsquery(?)"),
+ TO_TSVECTOR ("to_tsvector(?)"),
+ PLAINTO_TSQUERY ("plainto_tsquery(?)");
+
+ private final String functionSQL;
+ private final boolean aggregate;
+
+ private PostgresSqlPhrase(String phrase, boolean aggregate)
+ {
+ this.functionSQL = phrase;
+ this.aggregate = aggregate;
+ }
+
+ private PostgresSqlPhrase(String sqlDefault)
+ {
+ this(sqlDefault, false);
+ }
+
+ public String getSQL()
+ {
+ return functionSQL;
+ }
+
+ public boolean isAggregate()
+ {
+ return aggregate;
+ }
+}
diff --git a/empire-db/src/main/java/org/apache/empire/dbms/sqlite/DBMSHandlerSQLite.java b/empire-db/src/main/java/org/apache/empire/dbms/sqlite/DBMSHandlerSQLite.java
index 964605d..932eac8 100644
--- a/empire-db/src/main/java/org/apache/empire/dbms/sqlite/DBMSHandlerSQLite.java
+++ b/empire-db/src/main/java/org/apache/empire/dbms/sqlite/DBMSHandlerSQLite.java
@@ -350,7 +350,7 @@
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_MOD: return "mod(?,{0})";
case SQL_FUNC_FORMAT: return "printf({0:VARCHAR}, ?)";
// Date
case SQL_FUNC_DAY: return "day(?)";
diff --git a/empire-db/src/main/java/org/apache/empire/dbms/sqlserver/DBMSHandlerMSSQL.java b/empire-db/src/main/java/org/apache/empire/dbms/sqlserver/DBMSHandlerMSSQL.java
index 4e08b35..11f4337 100644
--- a/empire-db/src/main/java/org/apache/empire/dbms/sqlserver/DBMSHandlerMSSQL.java
+++ b/empire-db/src/main/java/org/apache/empire/dbms/sqlserver/DBMSHandlerMSSQL.java
@@ -361,7 +361,7 @@
case SQL_FUNC_TRUNC: return "trunc(?,{0})";
case SQL_FUNC_CEILING: return "ceiling(?)";
case SQL_FUNC_FLOOR: return "floor(?)";
- case SQL_FUNC_MODULO: return "((?) % {0})";
+ case SQL_FUNC_MOD: return "((?) % {0})";
case SQL_FUNC_FORMAT: return "format(?, {0:VARCHAR})";
// Date
case SQL_FUNC_DAY: return "day(?)";
@@ -372,6 +372,7 @@
case SQL_FUNC_MAX: return "max(?)";
case SQL_FUNC_MIN: return "min(?)";
case SQL_FUNC_AVG: return "avg(?)";
+ case SQL_FUNC_STRAGG: return "string_agg(?,{0}) WITHIN GROUP (ORDER BY {1})";
// Others
case SQL_FUNC_DECODE: return "case ? {0} end";
case SQL_FUNC_DECODE_SEP: return " ";
