You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@empire-db.apache.org by do...@apache.org on 2022/03/01 09:59:52 UTC
[empire-db] branch master updated: EMPIREDB-362 getUpdateWithJoins for PostgreSQL
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 dd5f11f EMPIREDB-362 getUpdateWithJoins for PostgreSQL
new f1a8867 Merge branch 'master' of https://gitbox.apache.org/repos/asf/empire-db
dd5f11f is described below
commit dd5f11f4ee44b759a3b88561075ba0de59a8dfd1
Author: Rainer Döbele <do...@apache.org>
AuthorDate: Tue Mar 1 10:59:33 2022 +0100
EMPIREDB-362 getUpdateWithJoins for PostgreSQL
---
.../main/java/org/apache/empire/db/DBCommand.java | 74 ++++++++++++++--------
.../main/java/org/apache/empire/db/DBRowSet.java | 15 +++--
.../org/apache/empire/dbms/hsql/DBCommandHSql.java | 25 ++------
.../apache/empire/dbms/mysql/DBMSHandlerMySQL.java | 15 ++---
.../apache/empire/dbms/oracle/DBCommandOracle.java | 64 ++++++-------------
.../dbms/postgresql/DBMSHandlerPostgreSQL.java | 40 +++++++++++-
6 files changed, 124 insertions(+), 109 deletions(-)
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 7899c57..6a31fbd 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
@@ -1480,7 +1480,7 @@ public abstract class DBCommand extends DBCommandExpr
*
* @return an update SQL-Statement
*/
- public String getUpdate()
+ public final String getUpdate()
{
resetParamUsage();
if (set == null)
@@ -1489,30 +1489,40 @@ public abstract class DBCommand extends DBCommandExpr
DBRowSet table = set.get(0).getTable();
if (joins!=null && !joins.isEmpty())
{ // Join Update
- buf.append( table.getAlias() );
- long context = CTX_DEFAULT;
- // Set Expressions
- buf.append("\r\nSET ");
- addListExpr(buf, set, context, ", ");
- // From clause
- addFrom(buf);
- // Add Where
- addWhere(buf, context);
+ addUpdateWithJoins(buf, table);
}
else
{ // Simple Statement
- table.addSQL(buf, CTX_FULLNAME);
- long context = CTX_NAME | CTX_VALUE;
- // Set Expressions
- buf.append("\r\nSET ");
- addListExpr(buf, set, context, ", ");
- // Add Where
- addWhere(buf, context);
+ addUpdateForTable(buf, table);
}
// done
completeParamUsage();
return buf.toString();
}
+
+ protected void addUpdateForTable(StringBuilder buf, DBRowSet table)
+ { // Simple Statement
+ table.addSQL(buf, CTX_FULLNAME);
+ long context = CTX_NAME | CTX_VALUE;
+ // Set Expressions
+ buf.append("\r\nSET ");
+ addListExpr(buf, set, context, ", ");
+ // Add Where
+ addWhere(buf, context);
+ }
+
+ protected void addUpdateWithJoins(StringBuilder buf, DBRowSet table)
+ { // Join Update
+ buf.append( table.getAlias() );
+ long context = CTX_DEFAULT;
+ // Set Expressions
+ buf.append("\r\nSET ");
+ addListExpr(buf, set, context, ", ");
+ // From clause
+ addFrom(buf);
+ // Add Where
+ addWhere(buf, context);
+ }
/**
* Creates a delete SQL-Statement
@@ -1521,30 +1531,40 @@ public abstract class DBCommand extends DBCommandExpr
*
* @return a delete SQL-Statement
*/
- public String getDelete(DBTable table)
+ public final String getDelete(DBTable table)
{
resetParamUsage();
StringBuilder buf = new StringBuilder("DELETE ");
// joins or simple
if (joins!=null && !joins.isEmpty())
{ // delete with joins
- table.addSQL(buf, CTX_FULLNAME);
- // From clause
- addFrom(buf);
- // Add Where
- addWhere(buf, CTX_DEFAULT);
+ addDeleteWithJoins(buf, table);
}
else
{ // Simple Statement
- buf.append("FROM ");
- table.addSQL(buf, CTX_FULLNAME);
- // where
- addWhere(buf, CTX_NAME|CTX_VALUE);
+ addDeleteForTable(buf, table);
}
// done
completeParamUsage();
return buf.toString();
}
+
+ protected void addDeleteForTable(StringBuilder buf, DBRowSet table)
+ { // Simple Statement
+ buf.append("FROM ");
+ table.addSQL(buf, CTX_FULLNAME);
+ // where
+ addWhere(buf, CTX_NAME|CTX_VALUE);
+ }
+
+ protected void addDeleteWithJoins(StringBuilder buf, DBRowSet table)
+ { // delete with joins
+ table.addSQL(buf, CTX_FULLNAME);
+ // From clause
+ addFrom(buf);
+ // Add Where
+ addWhere(buf, CTX_DEFAULT);
+ }
// ------- Select Statement Parts -------
diff --git a/empire-db/src/main/java/org/apache/empire/db/DBRowSet.java b/empire-db/src/main/java/org/apache/empire/db/DBRowSet.java
index 2d96411..88359d7 100644
--- a/empire-db/src/main/java/org/apache/empire/db/DBRowSet.java
+++ b/empire-db/src/main/java/org/apache/empire/db/DBRowSet.java
@@ -302,6 +302,9 @@ public abstract class DBRowSet extends DBExpr implements EntityType
public abstract void deleteRecord(Object[] key, DBContext context);
+ @Override
+ public abstract DBColumn[] getKeyColumns();
+
/**
* Returns the full qualified name of the rowset.
* <P>
@@ -621,7 +624,7 @@ public abstract class DBRowSet extends DBExpr implements EntityType
// Init Key Values
if (key != null)
{ // Check Columns
- DBColumn[] keyColumns =(DBColumn[])getKeyColumns();
+ DBColumn[] keyColumns = getKeyColumns();
if (keyColumns==null)
throw new NoPrimaryKeyException(this);
if (key.length!=keyColumns.length)
@@ -667,7 +670,7 @@ public abstract class DBRowSet extends DBExpr implements EntityType
prepareInitRecord(record, false);
// Get Record Field Values
Object[] fields = record.getFields();
- DBColumn[] keyColumns =(DBColumn[])getKeyColumns();
+ DBColumn[] keyColumns = getKeyColumns();
for (int i = 0; i < fields.length; i++)
{ // Read a value
DBColumnExpr column = getColumnExprAt(i);
@@ -757,7 +760,7 @@ public abstract class DBRowSet extends DBExpr implements EntityType
protected DBCompareExpr getKeyConstraints(Object[] key)
{
// Check Primary key
- DBColumn[] keyColumns =(DBColumn[])getKeyColumns();
+ DBColumn[] keyColumns = getKeyColumns();
if (keyColumns==null || keyColumns.length==0)
throw new NoPrimaryKeyException(this); // Invalid Argument
// Check Columns
@@ -966,7 +969,7 @@ public abstract class DBRowSet extends DBExpr implements EntityType
String sql = null;
int setCount = 0;
// Perform action
- DBColumn[] keyColumns =(DBColumn[])getKeyColumns();
+ DBColumn[] keyColumns = getKeyColumns();
DBRecordBase.State recordState = record.getState();
if (recordState==DBRecordBase.State.New)
{ // Insert Record
@@ -1124,7 +1127,7 @@ public abstract class DBRowSet extends DBExpr implements EntityType
{
// Merge Sub-Records
List<DBRelation> relations = db.getRelations();
- DBColumn[] keyColumns =(DBColumn[])getKeyColumns();
+ DBColumn[] keyColumns = getKeyColumns();
if (keyColumns==null)
return; // No primary key - no references!
// Find all relations
@@ -1158,7 +1161,7 @@ public abstract class DBRowSet extends DBExpr implements EntityType
if (refs.length!=parentKey.length)
throw new InvalidArgumentException("refs", refs);
// Rowset
- DBColumn[] keyColumns =(DBColumn[])getKeyColumns();
+ DBColumn[] keyColumns = getKeyColumns();
if (keyColumns==null || keyColumns.length==0)
{ // No Primary Key
DBCommand cmd = createRecordCommand(context);
diff --git a/empire-db/src/main/java/org/apache/empire/dbms/hsql/DBCommandHSql.java b/empire-db/src/main/java/org/apache/empire/dbms/hsql/DBCommandHSql.java
index 97cace1..bc88c43 100644
--- a/empire-db/src/main/java/org/apache/empire/dbms/hsql/DBCommandHSql.java
+++ b/empire-db/src/main/java/org/apache/empire/dbms/hsql/DBCommandHSql.java
@@ -19,7 +19,6 @@
package org.apache.empire.dbms.hsql;
import java.util.ArrayList;
-// Imports
import java.util.HashSet;
import java.util.List;
import java.util.Set;
@@ -95,27 +94,13 @@ public class DBCommandHSql extends DBCommand
}
}
}
-
- /**
- * Creates an update statement.
- * If a join is required, this method creates a "MERGE INTO" expression
- */
+
@Override
- public synchronized String getUpdate()
- {
- // No Joins: Use Default
- if (joins==null || set==null)
- return super.getUpdate();
- else
- return getUpdateWithJoins();
- }
-
- protected String getUpdateWithJoins()
+ protected void addUpdateWithJoins(StringBuilder buf, DBRowSet table)
{
// Generate Merge expression
- resetParamUsage();
- StringBuilder buf = new StringBuilder("MERGE INTO ");
- DBRowSet table = set.get(0).getTable();
+ buf.setLength(0);
+ buf.append("MERGE INTO ");
table.addSQL(buf, CTX_FULLNAME|CTX_ALIAS);
// join (only one allowed yet)
DBColumnJoinExpr updateJoin = null;
@@ -217,8 +202,6 @@ public class DBCommandHSql extends DBCommand
buf.append(")\r\nWHEN MATCHED THEN UPDATE ");
buf.append("\r\nSET ");
addListExpr(buf, mergeSet, CTX_DEFAULT, ", ");
- // done
- return buf.toString();
}
protected boolean isSetColumn(DBColumn col)
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 d613fce..81bedd6 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
@@ -32,6 +32,7 @@ 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.DBTable;
import org.apache.empire.db.DBTableColumn;
@@ -112,21 +113,13 @@ public class DBMSHandlerMySQL extends DBMSHandlerBase
* @return the delete SQL-Command
*/
@Override
- public synchronized String getDelete(DBTable table)
+ protected void addDeleteWithJoins(StringBuilder buf, DBRowSet table)
{
- if (joins == null) {
- // Default
- return super.getDelete(table);
- }
-
- // DELETE with Multiple-Table Syntax
- // http://dev.mysql.com/doc/refman/5.7/en/delete.html
- resetParamUsage();
- StringBuilder buf = new StringBuilder("DELETE ");
+ // DELETE with Multiple-Table Syntax
+ // http://dev.mysql.com/doc/refman/5.7/en/delete.html
buf.append(table.getAlias());
addFrom(buf);
addWhere(buf);
- return buf.toString();
}
}
diff --git a/empire-db/src/main/java/org/apache/empire/dbms/oracle/DBCommandOracle.java b/empire-db/src/main/java/org/apache/empire/dbms/oracle/DBCommandOracle.java
index b7cf772..506f1f3 100644
--- a/empire-db/src/main/java/org/apache/empire/dbms/oracle/DBCommandOracle.java
+++ b/empire-db/src/main/java/org/apache/empire/dbms/oracle/DBCommandOracle.java
@@ -31,7 +31,6 @@ import org.apache.empire.db.DBColumnExpr;
import org.apache.empire.db.DBCommand;
import org.apache.empire.db.DBIndex;
import org.apache.empire.db.DBRowSet;
-import org.apache.empire.db.DBTable;
import org.apache.empire.db.expr.column.DBAliasExpr;
import org.apache.empire.db.expr.column.DBValueExpr;
import org.apache.empire.db.expr.compare.DBCompareColExpr;
@@ -221,6 +220,7 @@ public class DBCommandOracle extends DBCommand
buf.append(usePreparedStatements ? "?" : String.valueOf(skipRows));
}
}
+ completeParamUsage();
}
@Override
@@ -242,30 +242,12 @@ public class DBCommandOracle extends DBCommand
newParams[--newSize]=skipRows+limitRows;
return newParams;
}
-
- /**
- * Creates an Oracle specific update statement.
- * If a join is required, this method creates a "MERGE INTO" expression
- */
- @Override
- public synchronized String getUpdate()
- {
- // No Joins: Use Default
- if (joins==null || set==null)
- return getSimpleUpdate();
- else
- return getUpdateWithJoins();
- }
- protected String getSimpleUpdate()
+ @Override
+ protected void addUpdateForTable(StringBuilder buf, DBRowSet table)
{
- resetParamUsage();
- if (set == null)
- return null;
- StringBuilder buf = new StringBuilder("UPDATE ");
- DBRowSet table = set.get(0).getTable();
- long context = CTX_FULLNAME;
// Optimizer Hint
+ long context = CTX_FULLNAME;
if (StringUtils.isNotEmpty(optimizerHint))
{ // Append an optimizer hint to the select statement e.g. SELECT /*+ RULE */
buf.append("/*+ ").append(optimizerHint).append(" */ ");
@@ -282,16 +264,14 @@ public class DBCommandOracle extends DBCommand
addListExpr(buf, set, context, ", ");
// Add Where
addWhere(buf, context);
- // done
- return buf.toString();
}
- protected String getUpdateWithJoins()
+ @Override
+ protected void addUpdateWithJoins(StringBuilder buf, DBRowSet table)
{
// Generate Merge expression
- resetParamUsage();
- StringBuilder buf = new StringBuilder("MERGE INTO ");
- DBRowSet table = set.get(0).getTable();
+ buf.setLength(0);
+ buf.append("MERGE INTO ");
table.addSQL(buf, CTX_FULLNAME|CTX_ALIAS);
// join (only one allowed yet)
DBColumnJoinExpr updateJoin = null;
@@ -393,8 +373,6 @@ public class DBCommandOracle extends DBCommand
buf.append(")\r\nWHEN MATCHED THEN UPDATE ");
buf.append("\r\nSET ");
addListExpr(buf, mergeSet, CTX_DEFAULT, ", ");
- // done
- return buf.toString();
}
protected boolean isSetColumn(DBColumn col)
@@ -411,25 +389,25 @@ public class DBCommandOracle extends DBCommand
* Creates an Oracle specific delete statement.
* @return the delete SQL-Command
*/
+
@Override
- public synchronized String getDelete(DBTable table)
+ protected void addDeleteForTable(StringBuilder buf, DBRowSet table)
{
- resetParamUsage();
- StringBuilder buf = new StringBuilder("DELETE ");
- if (optimizerHint != null)
+ if (StringUtils.isNotEmpty(optimizerHint))
{ // Append an optimizer hint to the select statement e.g. SELECT /*+ RULE */
buf.append("/*+ ").append(optimizerHint).append(" */ ");
}
- buf.append("FROM ");
- table.addSQL(buf, CTX_FULLNAME);
- // Set Expressions
- if (where != null || having != null)
- { // add where condition
- buf.append("\r\nWHERE ");
- if (where != null)
- addListExpr(buf, where, CTX_NAME|CTX_VALUE, " AND ");
+ super.addDeleteForTable(buf, table);
+ }
+
+ @Override
+ protected void addDeleteWithJoins(StringBuilder buf, DBRowSet table)
+ {
+ if (StringUtils.isNotEmpty(optimizerHint))
+ { // Append an optimizer hint to the select statement e.g. SELECT /*+ RULE */
+ buf.append("/*+ ").append(optimizerHint).append(" */ ");
}
- return buf.toString();
+ super.addDeleteWithJoins(buf, table);
}
}
\ No newline at end of file
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 6aa5d67..ad010e3 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,20 +26,23 @@ import java.util.GregorianCalendar;
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;
import org.apache.empire.dbms.DBMSHandler;
import org.apache.empire.dbms.DBMSHandlerBase;
-import org.apache.empire.dbms.DBMSFeature;
import org.apache.empire.dbms.DBSqlPhrase;
import org.apache.empire.exceptions.InvalidArgumentException;
import org.slf4j.Logger;
@@ -127,6 +130,41 @@ public class DBMSHandlerPostgreSQL extends DBMSHandlerBase
}
}
}
+
+ @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;