Changeset 6976

Timestamp:

Oct 8, 2015, 8:20:56 AM (7 years ago)

Author:

Nicklas Nordborg

Message:

References #1941: Store experimental factor values as part experiments

When updating a MySQL installation it is not possible to drop indexes without also dropping foreign keys that use the same columns. After dropping the index, the foreign keys need to be re-created. In schema version 124 we need to drop the unique index on annotationset_id+annotationtype_id in the Annotations table since cloning annotations means that there can be multiple annotations for the same annotation type.

When updating a MySQL installation it is not possible to drop a NOT NULL constraint on a column. Instead the column definition must be modified as if a new column was created without a NOT NULL constraint. This requires that we know the data type of the column. Since the SQL to do this is so different from how PostgreSQL does this, we put this in the mysql-queries.xml file.

Location:

branches/3.6-stable

Files:

• config/dist/mysql-queries.xml (modified) (1 diff)
• src/core/net/sf/basedb/core/Update.java (modified) (4 diffs)
• src/core/net/sf/basedb/core/dbengine/AbstractDbEngine.java (modified) (1 diff)
• src/core/net/sf/basedb/core/dbengine/DbEngine.java (modified) (1 diff)
• src/core/net/sf/basedb/core/dbengine/MySQLEngine.java (modified) (1 diff)

branches/3.6-stable/config/dist/mysql-queries.xml

@@ -28 +28 @@
 -->
 <predefined-queries>
+  <query id="DROP_NOT_NULL_CONSTRAINT" type="SQL">
+    <sql>
+      ALTER TABLE [{1}] MODIFY [{2}] {3} NULL
+    </sql>
+    <description>
+      An SQL query that drops a NOT NULL contraint from column (2) with data type (3)
+      in a table (1).
+    </description>
+  </query>
 
 </predefined-queries>

branches/3.6-stable/src/core/net/sf/basedb/core/Update.java

@@ -1337 +1337 @@
       
       // Drop NOT NULL on Annotations.value_id
-      dropNotNullConstraint(session, "Annotations", "value_id");
+      dropNotNullConstraint(session, "Annotations", "value_id", "int");
 
       // Drop UNIQUE constraint on Annotations.annotationset_id/annotationtype_id
@@ -1657 +1657 @@
       TableInfo info = getTableInfo(session, tableName);
       String indexName = info.findIndexName(null, new HashSet<String>(Arrays.asList(columnNames)));
+
       if (indexName != null)
       {
         DbEngine engine = HibernateUtil.getDbEngine();
+
+        // Drop foreign keys using any of the column names in the index
+        List<ForeignKeyInfo> dropped = new ArrayList<ForeignKeyInfo>();
+        if (engine.dropForeignKeysUsedInIndex())
+        {
+          for (ForeignKeyInfo fk : info.getForeignKeys())
+          {
+            for (String colName : columnNames)
+            {
+              if (fk.getFkColumns().contains(colName))
+              {
+                dropped.add(fk);
+                String fkSql = engine.getDropForeignKeySql(null, null, tableName, fk.getName());
+                query = HibernateUtil.createSqlQuery(session, fkSql);
+                query.executeUpdate();
+                break;
+              }
+            }
+          }
+        }
+        
         String sql = engine.getDropIndexSql(null, null, tableName, indexName, unique);
         query = HibernateUtil.createSqlQuery(session, sql);
         query.executeUpdate();
+        
+        // Re-created dropped foreign keys
+        for (ForeignKeyInfo fk : dropped)
+        {
+          String fkSql = engine.getCreateForeignKeySql(null, null, tableName, fk.getName(), fk.getFkColumns(), fk.getRefName(), fk.getRefColumns());
+          query = HibernateUtil.createSqlQuery(session, fkSql);
+          query.executeUpdate();
+        }
+
       }
       // Only commit if we started a new transaction
@@ -1696 +1727 @@
   }
 
-  private static void dropNotNullConstraint(org.hibernate.Session session, String tableName, String columnName)
+  private static void dropNotNullConstraint(org.hibernate.Session session, String tableName, String columnName, String mySqlDataType)
   {
     org.hibernate.Transaction tx = null;
@@ -1705 +1736 @@
       tx = session.getTransaction().isActive() ? null : HibernateUtil.newTransaction(session);
       
-      query = HibernateUtil.getPredefinedSQLQuery(session, "DROP_NOT_NULL_CONSTRAINT", tableName, columnName);
+      query = HibernateUtil.getPredefinedSQLQuery(session, "DROP_NOT_NULL_CONSTRAINT", tableName, columnName, mySqlDataType);
       query.executeUpdate();
       

branches/3.6-stable/src/core/net/sf/basedb/core/dbengine/AbstractDbEngine.java

@@ -75 +75 @@
   }
 
-  
+  /**
+    @return FALSE
+    @since 3.6
+  */
+  @Override
+  public boolean dropForeignKeysUsedInIndex()
+  {
+    return false;
+  }
+
   /**
     Return the SQL unmodified.

branches/3.6-stable/src/core/net/sf/basedb/core/dbengine/DbEngine.java

@@ -123 +123 @@
   */
   public String getDropIndexSql(String catalog, String schema, String table, String name, boolean unique);
+  
+  /**
+    When dropping an index, must foreign keys that uses the same columns also be
+    dropped (before dropping the index)? Default is FALSE, but MySQL need TRUE.
+    @since 3.6
+  */
+  public boolean dropForeignKeysUsedInIndex();
   
   /**

branches/3.6-stable/src/core/net/sf/basedb/core/dbengine/MySQLEngine.java

@@ -114 +114 @@
     sql.append(")");
     return sql.toString();
+  }
+  
+  /**
+    @return TRUE
+    @since 3.6
+  */
+  @Override
+  public boolean dropForeignKeysUsedInIndex()
+  {
+    return true;
   }