Changeset 6976


Ignore:
Timestamp:
Oct 8, 2015, 8:20:56 AM (6 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:
5 edited

Legend:

Unmodified
Added
Removed
  • branches/3.6-stable/config/dist/mysql-queries.xml

    r6330 r6976  
    2828-->
    2929<predefined-queries>
     30  <query id="DROP_NOT_NULL_CONSTRAINT" type="SQL">
     31    <sql>
     32      ALTER TABLE [{1}] MODIFY [{2}] {3} NULL
     33    </sql>
     34    <description>
     35      An SQL query that drops a NOT NULL contraint from column (2) with data type (3)
     36      in a table (1).
     37    </description>
     38  </query>
    3039
    3140</predefined-queries>
  • branches/3.6-stable/src/core/net/sf/basedb/core/Update.java

    r6941 r6976  
    13371337     
    13381338      // Drop NOT NULL on Annotations.value_id
    1339       dropNotNullConstraint(session, "Annotations", "value_id");
     1339      dropNotNullConstraint(session, "Annotations", "value_id", "int");
    13401340
    13411341      // Drop UNIQUE constraint on Annotations.annotationset_id/annotationtype_id
     
    16571657      TableInfo info = getTableInfo(session, tableName);
    16581658      String indexName = info.findIndexName(null, new HashSet<String>(Arrays.asList(columnNames)));
     1659
    16591660      if (indexName != null)
    16601661      {
    16611662        DbEngine engine = HibernateUtil.getDbEngine();
     1663
     1664        // Drop foreign keys using any of the column names in the index
     1665        List<ForeignKeyInfo> dropped = new ArrayList<ForeignKeyInfo>();
     1666        if (engine.dropForeignKeysUsedInIndex())
     1667        {
     1668          for (ForeignKeyInfo fk : info.getForeignKeys())
     1669          {
     1670            for (String colName : columnNames)
     1671            {
     1672              if (fk.getFkColumns().contains(colName))
     1673              {
     1674                dropped.add(fk);
     1675                String fkSql = engine.getDropForeignKeySql(null, null, tableName, fk.getName());
     1676                query = HibernateUtil.createSqlQuery(session, fkSql);
     1677                query.executeUpdate();
     1678                break;
     1679              }
     1680            }
     1681          }
     1682        }
     1683       
    16621684        String sql = engine.getDropIndexSql(null, null, tableName, indexName, unique);
    16631685        query = HibernateUtil.createSqlQuery(session, sql);
    16641686        query.executeUpdate();
     1687       
     1688        // Re-created dropped foreign keys
     1689        for (ForeignKeyInfo fk : dropped)
     1690        {
     1691          String fkSql = engine.getCreateForeignKeySql(null, null, tableName, fk.getName(), fk.getFkColumns(), fk.getRefName(), fk.getRefColumns());
     1692          query = HibernateUtil.createSqlQuery(session, fkSql);
     1693          query.executeUpdate();
     1694        }
     1695
    16651696      }
    16661697      // Only commit if we started a new transaction
     
    16961727  }
    16971728
    1698   private static void dropNotNullConstraint(org.hibernate.Session session, String tableName, String columnName)
     1729  private static void dropNotNullConstraint(org.hibernate.Session session, String tableName, String columnName, String mySqlDataType)
    16991730  {
    17001731    org.hibernate.Transaction tx = null;
     
    17051736      tx = session.getTransaction().isActive() ? null : HibernateUtil.newTransaction(session);
    17061737     
    1707       query = HibernateUtil.getPredefinedSQLQuery(session, "DROP_NOT_NULL_CONSTRAINT", tableName, columnName);
     1738      query = HibernateUtil.getPredefinedSQLQuery(session, "DROP_NOT_NULL_CONSTRAINT", tableName, columnName, mySqlDataType);
    17081739      query.executeUpdate();
    17091740     
  • branches/3.6-stable/src/core/net/sf/basedb/core/dbengine/AbstractDbEngine.java

    r6721 r6976  
    7575  }
    7676
    77  
     77  /**
     78    @return FALSE
     79    @since 3.6
     80  */
     81  @Override
     82  public boolean dropForeignKeysUsedInIndex()
     83  {
     84    return false;
     85  }
     86
    7887  /**
    7988    Return the SQL unmodified.
  • branches/3.6-stable/src/core/net/sf/basedb/core/dbengine/DbEngine.java

    r6880 r6976  
    123123  */
    124124  public String getDropIndexSql(String catalog, String schema, String table, String name, boolean unique);
     125 
     126  /**
     127    When dropping an index, must foreign keys that uses the same columns also be
     128    dropped (before dropping the index)? Default is FALSE, but MySQL need TRUE.
     129    @since 3.6
     130  */
     131  public boolean dropForeignKeysUsedInIndex();
    125132 
    126133  /**
  • branches/3.6-stable/src/core/net/sf/basedb/core/dbengine/MySQLEngine.java

    r6684 r6976  
    114114    sql.append(")");
    115115    return sql.toString();
     116  }
     117 
     118  /**
     119    @return TRUE
     120    @since 3.6
     121  */
     122  @Override
     123  public boolean dropForeignKeysUsedInIndex()
     124  {
     125    return true;
    116126  }
    117127 
Note: See TracChangeset for help on using the changeset viewer.