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 5de5bfb EMPIREDB-303 Add support for Unique-Indexes which allow null
values Implementation provided for SQL Server only.
5de5bfb is described below
commit 5de5bfb2b15822e54342f8b790badf73f866f061
Author: Rainer Döbele <[email protected]>
AuthorDate: Tue Sep 10 14:20:16 2019 +0200
EMPIREDB-303
Add support for Unique-Indexes which allow null values
Implementation provided for SQL Server only.
---
.../java/org/apache/empire/db/DBDDLGenerator.java | 5 +--
.../org/apache/empire/db/DBExpressionIndex.java | 16 ++++-----
.../main/java/org/apache/empire/db/DBIndex.java | 40 ++++++++++++----------
.../main/java/org/apache/empire/db/DBTable.java | 22 +++++++++---
.../src/main/java/org/apache/empire/db/DBView.java | 3 +-
.../empire/db/sqlserver/MSSqlDDLGenerator.java | 27 +++++++++++++++
6 files changed, 78 insertions(+), 35 deletions(-)
diff --git a/empire-db/src/main/java/org/apache/empire/db/DBDDLGenerator.java
b/empire-db/src/main/java/org/apache/empire/db/DBDDLGenerator.java
index c30d241..51d9b69 100644
--- a/empire-db/src/main/java/org/apache/empire/db/DBDDLGenerator.java
+++ b/empire-db/src/main/java/org/apache/empire/db/DBDDLGenerator.java
@@ -20,6 +20,7 @@ package org.apache.empire.db;
import org.apache.empire.commons.StringUtils;
import org.apache.empire.data.DataType;
+import org.apache.empire.db.DBIndex.DBIndexType;
import org.apache.empire.exceptions.InvalidArgumentException;
import org.apache.empire.exceptions.MiscellaneousErrorException;
import org.apache.empire.exceptions.NotImplementedException;
@@ -401,7 +402,7 @@ public abstract class DBDDLGenerator<T extends
DBDatabaseDriver>
// Create other Indexes (except primary key)
for (DBIndex idx : t.getIndexes())
{
- if (idx == pk || idx.getType() == DBIndex.PRIMARYKEY)
+ if (idx == pk || idx.getType() == DBIndexType.PRIMARY_KEY)
continue;
// Create Index
@@ -420,7 +421,7 @@ public abstract class DBDDLGenerator<T extends
DBDatabaseDriver>
StringBuilder sql = new StringBuilder();
// Create Index
- sql.append((idx.getType() == DBIndex.UNIQUE) ? "CREATE UNIQUE INDEX "
: "CREATE INDEX ");
+ sql.append((idx.getType().isUnique()) ? "CREATE UNIQUE INDEX " :
"CREATE INDEX ");
appendElementName(sql, idx.getName());
sql.append(" ON ");
t.addSQL(sql, DBExpr.CTX_FULLNAME);
diff --git
a/empire-db/src/main/java/org/apache/empire/db/DBExpressionIndex.java
b/empire-db/src/main/java/org/apache/empire/db/DBExpressionIndex.java
index 81721d4..99870eb 100644
--- a/empire-db/src/main/java/org/apache/empire/db/DBExpressionIndex.java
+++ b/empire-db/src/main/java/org/apache/empire/db/DBExpressionIndex.java
@@ -30,7 +30,7 @@ public class DBExpressionIndex extends DBIndex
private final static long serialVersionUID = 1L;
private DBExpr[] columnExpressions;
-
+
/**
* Constructs a DBExpresionIndex
*
@@ -38,9 +38,9 @@ public class DBExpressionIndex extends DBIndex
* @param unique true if the index has only unique values or false
otherwise
* @param columnExpressions an array of one or more column expressions of
the index
*/
- public DBExpressionIndex(String name, boolean unique, DBExpr...
columnExpressions)
+ public DBExpressionIndex(String name, DBIndexType type, DBExpr...
columnExpressions)
{
- super(name, (unique ? UNIQUE : STANDARD), null);
+ super(name, type, null);
// columnExpressions
if (columnExpressions==null || columnExpressions.length==0)
throw new InvalidArgumentException("columnExpressions",
columnExpressions);
@@ -50,15 +50,13 @@ public class DBExpressionIndex extends DBIndex
/**
* Constructs a DBExpresionIndex
- *
- * @param name the index name
- * @param unique true if the index has only unique values or false
otherwise
- * @param expr an column expressions that defines the key values for the
index
+ * Overload for convenience
*/
- public DBExpressionIndex(String name, boolean unique, DBColumnExpr expr)
+ public DBExpressionIndex(String name, boolean unique, DBExpr...
columnExpressions)
{
- this(name, unique, new DBColumnExpr[] { expr });
+ this(name, (unique ? DBIndexType.UNIQUE : DBIndexType.STANDARD),
columnExpressions);
}
+
@Override
public DBDatabase getDatabase()
diff --git a/empire-db/src/main/java/org/apache/empire/db/DBIndex.java
b/empire-db/src/main/java/org/apache/empire/db/DBIndex.java
index be6a6d9..de8b910 100644
--- a/empire-db/src/main/java/org/apache/empire/db/DBIndex.java
+++ b/empire-db/src/main/java/org/apache/empire/db/DBIndex.java
@@ -29,24 +29,28 @@ public class DBIndex extends DBObject
{
private final static long serialVersionUID = 1L;
- // Index Types
- /**
- * SQL Standard index
- */
- public static final int STANDARD = 0;
-
- /**
- * SQL Unique index
- */
- public static final int UNIQUE = 1;
-
- /**
- * SQL Primary key index
- */
- public static final int PRIMARYKEY = 2;
+ public enum DBIndexType
+ {
+ STANDARD(false),
+ UNIQUE(true),
+ UNIQUE_ALLOW_NULL(true),
+ PRIMARY_KEY(true);
+
+ private final boolean unique;
+
+ DBIndexType(boolean unique)
+ {
+ this.unique = unique;
+ }
+
+ public boolean isUnique()
+ {
+ return unique;
+ }
+ }
private String name;
- private int type;
+ private DBIndexType type;
private DBColumn[] columns;
private DBTable table;
@@ -57,7 +61,7 @@ public class DBIndex extends DBObject
* @param type the primary key type (only PRIMARYKEY)
* @param columns an array of one or more columns of the primary key
*/
- public DBIndex(String name, int type, DBColumn[] columns)
+ public DBIndex(String name, DBIndexType type, DBColumn[] columns)
{
this.name = name;
this.type = type;
@@ -154,7 +158,7 @@ public class DBIndex extends DBObject
*
* @return the type of this index ({@link #PRIMARYKEY}, {@link #UNIQUE},
{@link #STANDARD})
*/
- public int getType()
+ public DBIndexType getType()
{
return type;
}
diff --git a/empire-db/src/main/java/org/apache/empire/db/DBTable.java
b/empire-db/src/main/java/org/apache/empire/db/DBTable.java
index aab8283..45f5962 100644
--- a/empire-db/src/main/java/org/apache/empire/db/DBTable.java
+++ b/empire-db/src/main/java/org/apache/empire/db/DBTable.java
@@ -29,6 +29,7 @@ import java.util.concurrent.atomic.AtomicInteger;
import org.apache.empire.commons.Options;
import org.apache.empire.data.DataMode;
import org.apache.empire.data.DataType;
+import org.apache.empire.db.DBIndex.DBIndexType;
import org.apache.empire.db.DBRelation.DBCascadeAction;
import org.apache.empire.db.exceptions.NoPrimaryKeyException;
import org.apache.empire.db.exceptions.RecordDeleteFailedException;
@@ -430,7 +431,7 @@ public class DBTable extends DBRowSet implements Cloneable
// Set primary Key now
if (columns.length>0)
{ // create primary key
- primaryKey = new DBIndex(name + "_PK", DBIndex.PRIMARYKEY,
columns);
+ primaryKey = new DBIndex(name + "_PK", DBIndexType.PRIMARY_KEY,
columns);
addIndex(primaryKey);
}
else
@@ -472,15 +473,26 @@ public class DBTable extends DBRowSet implements Cloneable
*
* @return the Index object
*/
- public final DBIndex addIndex(String name, boolean unique, DBColumn...
columns)
+ public final DBIndex addIndex(String name, DBIndexType type, DBColumn...
columns)
{
if (name==null || columns==null || columns.length==0)
throw new InvalidArgumentException("name|columns", null);
+ if (type==DBIndexType.PRIMARY_KEY && this.primaryKey!=null)
+ throw new InvalidArgumentException("type",
DBIndexType.PRIMARY_KEY.name());
// add Index now
- DBIndex index = new DBIndex(name, (unique) ? DBIndex.UNIQUE :
DBIndex.STANDARD, columns);
+ DBIndex index = new DBIndex(name, type, columns);
addIndex(index);
return index;
}
+
+ /**
+ * Adds an index.
+ * Overload for convenience
+ */
+ public final DBIndex addIndex(String name, boolean unique, DBColumn...
columns)
+ {
+ return addIndex(name, (unique) ? DBIndexType.UNIQUE :
DBIndexType.STANDARD, columns);
+ }
/**
* removes an index.
@@ -572,12 +584,12 @@ public class DBTable extends DBRowSet implements Cloneable
{
for (DBIndex idx : getIndexes())
{
- if (idx.getType()==DBIndex.PRIMARYKEY)
+ if (idx.getType()==DBIndexType.PRIMARY_KEY)
{ // Only for new records
if (!rec.isNew())
continue; // not new
}
- else if (idx.getType()==DBIndex.UNIQUE)
+ else if (idx.getType().isUnique())
{ // check if any of the fields were actually changed
if (!rec.isNew() && !rec.wasAnyModified(idx.getColumns()))
continue; // not modified
diff --git a/empire-db/src/main/java/org/apache/empire/db/DBView.java
b/empire-db/src/main/java/org/apache/empire/db/DBView.java
index 76775bf..49f3275 100644
--- a/empire-db/src/main/java/org/apache/empire/db/DBView.java
+++ b/empire-db/src/main/java/org/apache/empire/db/DBView.java
@@ -23,6 +23,7 @@ import java.util.concurrent.atomic.AtomicInteger;
import org.apache.empire.commons.Options;
import org.apache.empire.data.DataType;
+import org.apache.empire.db.DBIndex.DBIndexType;
import org.apache.empire.db.expr.column.DBValueExpr;
import org.apache.empire.exceptions.InvalidArgumentException;
import org.apache.empire.exceptions.ItemExistsException;
@@ -219,7 +220,7 @@ public abstract class DBView extends DBRowSet
protected void setKeyColumns(DBViewColumn... keyColumns)
{ // Set Key Columns
if (keyColumns != null)
- primaryKey = new DBIndex(null, DBIndex.PRIMARYKEY, keyColumns);
+ primaryKey = new DBIndex(null, DBIndexType.PRIMARY_KEY,
keyColumns);
}
/**
diff --git
a/empire-db/src/main/java/org/apache/empire/db/sqlserver/MSSqlDDLGenerator.java
b/empire-db/src/main/java/org/apache/empire/db/sqlserver/MSSqlDDLGenerator.java
index 222a274..7109d59 100644
---
a/empire-db/src/main/java/org/apache/empire/db/sqlserver/MSSqlDDLGenerator.java
+++
b/empire-db/src/main/java/org/apache/empire/db/sqlserver/MSSqlDDLGenerator.java
@@ -22,9 +22,13 @@ import org.apache.empire.commons.ObjectUtils;
import org.apache.empire.commons.StringUtils;
import org.apache.empire.data.Column;
import org.apache.empire.data.DataType;
+import org.apache.empire.db.DBColumn;
import org.apache.empire.db.DBDDLGenerator;
import org.apache.empire.db.DBDatabase;
import org.apache.empire.db.DBDatabaseDriver.DBSeqTable;
+import org.apache.empire.db.DBExpr;
+import org.apache.empire.db.DBIndex;
+import org.apache.empire.db.DBIndex.DBIndexType;
import org.apache.empire.db.DBSQLScript;
import org.apache.empire.db.DBTableColumn;
@@ -125,4 +129,27 @@ public class MSSqlDDLGenerator extends
DBDDLGenerator<DBDatabaseDriverMSSQL>
super.createDatabase(db, script);
}
+ @Override
+ protected void addCreateIndexStmt(DBIndex index, StringBuilder sql,
DBSQLScript script)
+ {
+ // Check type
+ if (index.getType()==DBIndexType.UNIQUE_ALLOW_NULL)
+ { // Add WHERE constraint for ALLOW_NULL
+ boolean first = true;
+ for (DBColumn col : index.getColumns())
+ {
+ // Check whether columns is nullable
+ if (col.isRequired())
+ continue;
+ // append
+ sql.append((first) ? " WHERE " : " AND ");
+ col.addSQL(sql, DBExpr.CTX_NAME);
+ sql.append(" IS NOT NULL");
+ first = false;
+ }
+ }
+ // done
+ super.addCreateIndexStmt(index, sql, script);
+ }
+
}
