Unique username and some first update routine (still needs testing) #4102

diff --git a/.gitattributes b/.gitattributes
index e013819c7d459dde349b8924c6ab5586649e9c59..f908b6c7d23666bedc9c98870c0fd19bdf8cffdf 100644 (file)
--- a/.gitattributes
+++ b/.gitattributes
@@ -1307,6 +1307,7 @@ cdmlib-persistence/src/main/java/eu/etaxonomy/cdm/database/update/v31_33/TermUpd
 cdmlib-persistence/src/main/java/eu/etaxonomy/cdm/database/update/v31_33/TermVocabularyRepresentationUpdater.java -text
 cdmlib-persistence/src/main/java/eu/etaxonomy/cdm/database/update/v33_34/SchemaUpdater_331_34.java -text
 cdmlib-persistence/src/main/java/eu/etaxonomy/cdm/database/update/v33_34/TermUpdater_33_34.java -text
+cdmlib-persistence/src/main/java/eu/etaxonomy/cdm/database/update/v33_34/UsernameConstraintUpdater.java -text
 cdmlib-persistence/src/main/java/eu/etaxonomy/cdm/persistence/dao/IAlternativeSpellingSuggestionParser.java -text
 cdmlib-persistence/src/main/java/eu/etaxonomy/cdm/persistence/dao/IMethodCache.java -text
 cdmlib-persistence/src/main/java/eu/etaxonomy/cdm/persistence/dao/MethodCacheImpl.java -text

diff --git a/cdmlib-model/src/main/java/eu/etaxonomy/cdm/model/common/User.java b/cdmlib-model/src/main/java/eu/etaxonomy/cdm/model/common/User.java
index 332f2012576b75a77f41ea001f5a6319a2b653d5..2e6ad25107d377bb31ef752a39eaa62e766a7524 100644 (file)
--- a/cdmlib-model/src/main/java/eu/etaxonomy/cdm/model/common/User.java
+++ b/cdmlib-model/src/main/java/eu/etaxonomy/cdm/model/common/User.java
@@ -13,6 +13,7 @@ import java.util.Collection;
 import java.util.HashSet;\r
 import java.util.Set;\r
 \r
+import javax.persistence.Column;\r
 import javax.persistence.Entity;\r
 import javax.persistence.FetchType;\r
 import javax.persistence.ManyToMany;\r
@@ -32,7 +33,6 @@ import javax.xml.bind.annotation.XmlType;
 import org.apache.log4j.Logger;\r
 import org.hibernate.annotations.Cascade;\r
 import org.hibernate.annotations.CascadeType;\r
-import org.hibernate.annotations.NaturalId;\r
 import org.hibernate.envers.Audited;\r
 import org.hibernate.envers.NotAudited;\r
 import org.hibernate.search.annotations.Analyze;\r
@@ -66,10 +66,8 @@ public class User extends CdmBase implements UserDetails {
     private static final long serialVersionUID = 6582191171369439163L;\r
     private static final Logger logger = Logger.getLogger(User.class);\r
 \r
-    protected User(){\r
-        super();\r
-    }\r
-\r
+ // **************************** FACTORY *****************************************/   \r
+    \r
     public static User NewInstance(String username, String pwd){\r
         User user = new User();\r
         user.setUsername(username);\r
@@ -98,8 +96,10 @@ public class User extends CdmBase implements UserDetails {
         return user;\r
     }\r
 \r
+//***************************** Fields *********************** /\r
+    \r
     @XmlElement(name = "Username")\r
-    @NaturalId\r
+    @Column(unique = true)\r
     @Field(analyze = Analyze.NO)\r
     protected String username;\r
 \r
@@ -156,6 +156,14 @@ public class User extends CdmBase implements UserDetails {
     @Transient\r
     private Set<GrantedAuthority> authorities;  //authorities of this user and of all groups the user belongs to\r
 \r
+//***************************** Constructor *********************** /\r
+    \r
+    protected User(){\r
+        super();\r
+    }\r
+    \r
+// ***************************** METHODS ******************************/\r
+    \r
     /**\r
      * Initializes or refreshes the collection of authorities, See\r
      * {@link #getAuthorities()}\r

diff --git a/cdmlib-persistence/src/main/java/eu/etaxonomy/cdm/database/update/v33_34/SchemaUpdater_331_34.java b/cdmlib-persistence/src/main/java/eu/etaxonomy/cdm/database/update/v33_34/SchemaUpdater_331_34.java
index 474a6d7a5c42f94dc5b33c0e46b2b0cf86a4cf07..e33a9a1090616b2a3ff6cbfda1bd5df4292237cd 100644 (file)
--- a/cdmlib-persistence/src/main/java/eu/etaxonomy/cdm/database/update/v33_34/SchemaUpdater_331_34.java
+++ b/cdmlib-persistence/src/main/java/eu/etaxonomy/cdm/database/update/v33_34/SchemaUpdater_331_34.java
@@ -20,8 +20,7 @@ import eu.etaxonomy.cdm.database.update.ISchemaUpdater;
 import eu.etaxonomy.cdm.database.update.ISchemaUpdaterStep;
 import eu.etaxonomy.cdm.database.update.SchemaUpdaterBase;
 import eu.etaxonomy.cdm.database.update.SimpleSchemaUpdaterStep;
-import eu.etaxonomy.cdm.database.update.TableDroper;
-import eu.etaxonomy.cdm.database.update.TreeIndexUpdater;
+import eu.etaxonomy.cdm.database.update.UniqueIndexDropper;
 import eu.etaxonomy.cdm.database.update.v31_33.SchemaUpdater_33_331;
 
 /**
@@ -60,8 +59,17 @@ public class SchemaUpdater_331_34 extends SchemaUpdaterBase {
 
                List<ISchemaUpdaterStep> stepList = new ArrayList<ISchemaUpdaterStep>();
 
-               stepName = "Add label column to derived unit";
+               //TODO test
+               stepName = "Remove (username,uuid) - unique index";
+               step = UsernameConstraintUpdater.NewInstance(stepName);
+               stepList.add(step);
                
+               //TODO test
+               stepName = "Add label column to derived unit";
+               tableName = "SpecimenOrObservationBase";
+               columnName = "originalLabelInfo";
+               step = ColumnAdder.NewClobInstance(stepName, tableName, columnName, INCLUDE_AUDIT);
+               stepList.add(step);
                
                        
                //TODO test

diff --git a/cdmlib-persistence/src/main/java/eu/etaxonomy/cdm/database/update/v33_34/UsernameConstraintUpdater.java b/cdmlib-persistence/src/main/java/eu/etaxonomy/cdm/database/update/v33_34/UsernameConstraintUpdater.java
new file mode 100644 (file)
index 0000000..c11d539
--- /dev/null
+++ b/cdmlib-persistence/src/main/java/eu/etaxonomy/cdm/database/update/v33_34/UsernameConstraintUpdater.java
@@ -0,0 +1,198 @@
+// $Id$\r
+/**\r
+* Copyright (C) 2009 EDIT\r
+* European Distributed Institute of Taxonomy \r
+* http://www.e-taxonomy.eu\r
+* \r
+* The contents of this file are subject to the Mozilla Public License Version 1.1\r
+* See LICENSE.TXT at the top of this package for the full license terms.\r
+*/\r
+package eu.etaxonomy.cdm.database.update.v33_34;\r
+\r
+import java.sql.SQLException;\r
+\r
+import org.apache.log4j.Logger;\r
+\r
+import eu.etaxonomy.cdm.common.monitor.IProgressMonitor;\r
+import eu.etaxonomy.cdm.database.DatabaseTypeEnum;\r
+import eu.etaxonomy.cdm.database.ICdmDataSource;\r
+import eu.etaxonomy.cdm.database.update.CaseType;\r
+import eu.etaxonomy.cdm.database.update.ISchemaUpdaterStep;\r
+import eu.etaxonomy.cdm.database.update.SchemaUpdaterStepBase;\r
+\r
+/**\r
+ * @author a.mueller\r
+ * @date 16.09.2010\r
+ *\r
+ */\r
+public class UsernameConstraintUpdater extends SchemaUpdaterStepBase<UsernameConstraintUpdater> implements ISchemaUpdaterStep {\r
+       private static final Logger logger = Logger.getLogger(UsernameConstraintUpdater.class);\r
+       \r
+       public static final UsernameConstraintUpdater NewInstance(String stepName){\r
+               return new UsernameConstraintUpdater(stepName);\r
+       }\r
+\r
+       \r
+       protected UsernameConstraintUpdater(String stepName) {\r
+               super(stepName);\r
+       }\r
+       \r
+\r
+       @Override\r
+       public Integer invoke(ICdmDataSource datasource, IProgressMonitor monitor, CaseType caseType) throws SQLException {\r
+               //remove 2-fold constraint\r
+               removeExistingConstraint(datasource, caseType);\r
+               createUsernameConstraint(datasource, caseType);\r
+               createUuidConstraint(datasource, caseType);\r
+               return null;\r
+       }\r
+       \r
+       private void createUuidConstraint(ICdmDataSource datasource,\r
+                       CaseType caseType) {\r
+               try {\r
+                       String updateQuery = getCreateQuery(datasource, caseType, "@@UserAccount@@", "username_", "username");\r
+                       datasource.executeUpdate(updateQuery);\r
+               } catch (SQLException e) {\r
+                       logger.warn("Unique index for UserAccount.uuid could not be created");\r
+               }\r
+       }\r
+       \r
+       private void createUsernameConstraint(ICdmDataSource datasource,\r
+                       CaseType caseType) {\r
+               try {\r
+                       String updateQuery = getCreateQuery(datasource, caseType, "@@UserAccount@@", "username_", "username");\r
+                       datasource.executeUpdate(updateQuery);\r
+               } catch (SQLException e) {\r
+                       logger.warn("Unique index for username could not be created");\r
+               }\r
+       }\r
+       \r
+       private String getCreateQuery(ICdmDataSource datasource, CaseType caseType, String tableName, String constraintName, String columnName) {\r
+                       DatabaseTypeEnum type = datasource.getDatabaseType();\r
+                       String indexName = "_UniqueKey";\r
+                       String updateQuery;\r
+                       if (type.equals(DatabaseTypeEnum.MySQL)){\r
+                               updateQuery = "ALTER TABLE @@"+ tableName + "@@ ADD UNIQUE INDEX " + constraintName + " ("+columnName+");";\r
+                       }else if (type.equals(DatabaseTypeEnum.H2) || type.equals(DatabaseTypeEnum.PostgreSQL) || type.equals(DatabaseTypeEnum.SqlServer2005)){\r
+                               updateQuery = "CREATE UNIQUE INDEX " + constraintName + " ON "+tableName+"(" + columnName + ")";\r
+                       }else{\r
+                               throw new IllegalArgumentException("Datasource type not supported: " + type.getName());\r
+                       }\r
+                       updateQuery = updateQuery.replace("@indexName", indexName);\r
+                       caseType.replaceTableNames("@@UserAccount@@");\r
+                       return updateQuery;\r
+       }\r
+\r
+\r
+       private void removeExistingConstraint(ICdmDataSource datasource, CaseType caseType) {\r
+               try {\r
+                       DatabaseTypeEnum type = datasource.getDatabaseType();\r
+                       String indexName = "_UniqueKey";\r
+                       String updateQuery;\r
+                       if (type.equals(DatabaseTypeEnum.MySQL)){\r
+                               updateQuery = "ALTER TABLE @@UserAccount@@ DROP INDEX @indexName";\r
+                       }else if (type.equals(DatabaseTypeEnum.H2)){\r
+                               updateQuery = "ALTER TABLE @@UserAccount@@ DROP CONSTRAINT IF EXISTS @indexName";\r
+                       }else if (type.equals(DatabaseTypeEnum.PostgreSQL)){\r
+                               updateQuery = "ALTER TABLE @@UserAccount@@ DROP CONSTRAINT @indexName";\r
+                       }else if (type.equals(DatabaseTypeEnum.SqlServer2005)){\r
+                               //TODO\r
+                               throw new RuntimeException("Remove index not yet supported for SQLServer");\r
+                       }else{\r
+                               throw new IllegalArgumentException("Datasource type not supported: " + type.getName());\r
+                       }\r
+                       updateQuery = updateQuery.replace("@indexName", indexName);\r
+                       updateQuery = caseType.replaceTableNames("@@UserAccount@@");\r
+                       datasource.executeUpdate(updateQuery);\r
+               } catch (SQLException e) {\r
+                       logger.warn("Old index could not be removed");\r
+               }\r
+       }\r
+\r
+//     private boolean checkExists(ICdmDataSource datasource) throws SQLException, DatabaseTypeNotSupportedException {\r
+//             DatabaseTypeEnum type = datasource.getDatabaseType();\r
+//             if (type.equals(DatabaseTypeEnum.MySQL)){\r
+//                     String sql = "SELECT count(*)   FROM information_schema.TABLE_CONSTRAINTS " + \r
+//                                     " WHERE table_name ='@tableName' AND CONSTRAINT_SCHEMA = '@dbName' AND CONSTRAINT_TYPE = 'UNIQUE' ";\r
+//                     sql = sql.replace("@tableName", tableName);\r
+//                     sql = sql.replace("@columnName", indexColumn);\r
+//                     sql = sql.replace("@dbName", datasource.getDatabase());\r
+//                     long count = (Long)datasource.getSingleValue(sql);\r
+//                     return count > 0;\r
+//             }else if (type.equals(DatabaseTypeEnum.PostgreSQL)){\r
+//                     logger.warn("checkExists not yet implemented for PostGreSQL" );\r
+//                     return true;\r
+//             }else if (type.equals(DatabaseTypeEnum.H2)){\r
+//                     String indexName = getIndexName(datasource);\r
+//                     return indexName != null;\r
+//             }else{\r
+//                     // not needed\r
+//                     return true;\r
+//             }\r
+//     }\r
+\r
+\r
+//     public String getUpdateQueryString(String tableName, ICdmDataSource datasource, IProgressMonitor monitor) throws DatabaseTypeNotSupportedException, SQLException {\r
+//             //NOTE: no caseType required here\r
+//             String updateQuery;\r
+//             DatabaseTypeEnum type = datasource.getDatabaseType();\r
+//             String indexName = getIndexName(datasource);\r
+//             \r
+////           if (type.equals(DatabaseTypeEnum.SqlServer2005)){\r
+//                     //MySQL allows both syntaxes\r
+////                   updateQuery = "ALTER TABLE @tableName ADD @columnName @columnType";\r
+////           }else\r
+//                     if (type.equals(DatabaseTypeEnum.H2)){\r
+//                     updateQuery = "ALTER TABLE @tableName DROP CONSTRAINT IF EXISTS @indexName";\r
+//             }else if (type.equals(DatabaseTypeEnum.PostgreSQL)){\r
+////                   updateQuery = "DROP INDEX IF EXISTS @indexName";  // does not work because index is used in the constraint\r
+////                   updateQuery = "ALTER TABLE @tableName DROP CONSTRAINT IF EXISTS @indexName"; //"if exists" does not work (version 8.4) \r
+//                     updateQuery = "ALTER TABLE @tableName DROP CONSTRAINT @indexName";\r
+//             }else if (type.equals(DatabaseTypeEnum.MySQL)){\r
+//                     updateQuery = "ALTER TABLE @tableName DROP INDEX @indexName";\r
+//             }else{\r
+//                     updateQuery = null;\r
+//                     String warning = "Update step '" + this.getStepName() + "' is not supported by " + type.getName();\r
+//                     monitor.warning(warning);\r
+//                     throw new DatabaseTypeNotSupportedException(warning);\r
+//             }\r
+//             updateQuery = updateQuery.replace("@tableName", tableName);\r
+//             updateQuery = updateQuery.replace("@indexName", indexName);\r
+//             \r
+//             return updateQuery;\r
+//     }\r
+//\r
+//\r
+//     private String getIndexName(ICdmDataSource datasource) throws DatabaseTypeNotSupportedException, SQLException {\r
+//             String result = this.indexColumn;\r
+//             DatabaseTypeEnum type = datasource.getDatabaseType();\r
+//             if (type.equals(DatabaseTypeEnum.SqlServer2005)){\r
+//                     throw new DatabaseTypeNotSupportedException(type.toString());\r
+//             }else if (type.equals(DatabaseTypeEnum.MySQL)){\r
+//                     result = this.indexColumn;\r
+//             }else if (type.equals(DatabaseTypeEnum.H2) ){\r
+////                   String sql = "SELECT INDEX_NAME FROM INFORMATION_SCHEMA.INDEXES WHERE TABLE_NAME = @tableName AND INDEX_TYPE_NAME = 'UNIQUE INDEX'"; \r
+//                     String sql = "SELECT CONSTRAINT_NAME " + \r
+//                                     " FROM INFORMATION_SCHEMA.CONSTRAINTS "+\r
+//                                     " WHERE CONSTRAINT_CATALOG = '@dbName' AND "+\r
+//                                     " TABLE_NAME = '@tableName' AND CONSTRAINT_TYPE = 'UNIQUE' AND "+ \r
+//                                     " COLUMN_LIST = '@columnName'"; \r
+//                     sql = sql.replace("@tableName", tableName.toUpperCase());\r
+//                     sql = sql.replace("@columnName", indexColumn.toUpperCase());\r
+//                     sql = sql.replace("@dbName", datasource.getDatabase().toUpperCase());\r
+//                     String constraintName = (String)datasource.getSingleValue(sql);\r
+//                     result = constraintName;\r
+//             }else if (type.equals(DatabaseTypeEnum.PostgreSQL)){\r
+//                     //TODO do we need this cased?\r
+//                     result = this.tableName + "_" + this.indexColumn + "_key";\r
+//             }else{\r
+//                     throw new DatabaseTypeNotSupportedException(type.toString());\r
+//             }\r
+//             return result;\r
+//             \r
+//     }\r
+\r
+\r
+\r
+\r
+}\r