removed fixed_ips virtual_interface_id foreignkey constraint from multi_nic migration, and added it as a standalone migration with special sqlite files

4 files changed, 157 insertions, 26 deletions

diff --git a/nova/db/sqlalchemy/migrate_repo/versions/023_multi_nic.py b/nova/db/sqlalchemy/migrate_repo/versions/023_multi_nic.py
index 86ef24b3f..12cd7621a 100644
--- a/nova/db/sqlalchemy/migrate_repo/versions/023_multi_nic.py
+++ b/nova/db/sqlalchemy/migrate_repo/versions/023_multi_nic.py
@@ -47,43 +47,21 @@ virtual_interfaces = Table('virtual_interfaces', meta,
         Column('port_id',
                String(length=255, convert_unicode=False, assert_unicode=None,
                       unicode_error=None, _warn_on_bytestring=False),
-               unique=True, nullable=True),
+               unique=True),
         )
 
-# Don't autoload this table since sqlite will have issues when
-# adding a column with a foreign key
-#TODO(tr3buchet)[wishful thinking]: remove support for sqlite
-fixed_ips = Table('fixed_ips', meta,
-        Column('created_at', DateTime(timezone=False),
-               default=utils.utcnow()),
-        Column('updated_at', DateTime(timezone=False),
-               onupdate=utils.utcnow()),
-        Column('deleted_at', DateTime(timezone=False)),
-        Column('deleted', Boolean(create_constraint=True, name=None)),
-        Column('id', Integer(), primary_key=True),
-        Column('address', String(255)),
-        Column('network_id', Integer(), ForeignKey('networks.id'),
-               nullable=True),
-        Column('instance_id', Integer(), ForeignKey('instances.id'),
-               nullable=True),
-        Column('allocated', Boolean(), default=False),
-        Column('leased', Boolean(), default=False),
-        Column('reserved', Boolean(), default=False),
-        )
 
 # bridge_interface column to add to networks table
 interface = Column('bridge_interface',
                    String(length=255, convert_unicode=False,
                           assert_unicode=None, unicode_error=None,
-                          _warn_on_bytestring=False),
-                          nullable=True)
+                          _warn_on_bytestring=False))
 
 
 # virtual interface id column to add to fixed_ips table
+# foreignkey added in next migration
 virtual_interface_id = Column('virtual_interface_id',
-                       Integer(),
-                       ForeignKey('virtual_interfaces.id'),
-                       nullable=True)
+                       Integer())
 
 
 def upgrade(migrate_engine):
@@ -92,6 +70,7 @@ def upgrade(migrate_engine):
     # grab tables and (column for dropping later)
     instances = Table('instances', meta, autoload=True)
     networks = Table('networks', meta, autoload=True)
+    fixed_ips = Table('fixed_ips', meta, autoload=True)
     c = instances.columns['mac_address']
 
     # add interface column to networks table
diff --git a/nova/db/sqlalchemy/migrate_repo/versions/024_fk_fixed_ips_virtual_interface_id.py b/nova/db/sqlalchemy/migrate_repo/versions/024_fk_fixed_ips_virtual_interface_id.py
new file mode 100644
index 000000000..56e927717
--- /dev/null
+++ b/nova/db/sqlalchemy/migrate_repo/versions/024_fk_fixed_ips_virtual_interface_id.py
@@ -0,0 +1,56 @@
+# Copyright 2011 OpenStack LLC.
+# All Rights Reserved.
+#
+#    Licensed under the Apache License, Version 2.0 (the "License"); you may
+#    not use this file except in compliance with the License. You may obtain
+#    a copy of the License at
+#
+#         http://www.apache.org/licenses/LICENSE-2.0
+#
+#    Unless required by applicable law or agreed to in writing, software
+#    distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
+#    WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the
+#    License for the specific language governing permissions and limitations
+#    under the License.
+
+import datetime
+
+from sqlalchemy import *
+from migrate import *
+
+from nova import log as logging
+from nova import utils
+
+meta = MetaData()
+
+
+def upgrade(migrate_engine):
+    meta.bind = migrate_engine
+    dialect = migrate_engine.url.get_dialect().name
+
+    # grab tables
+    fixed_ips = Table('fixed_ips', meta, autoload=True)
+    virtual_interfaces = Table('virtual_interfaces', meta, autoload=True)
+
+    # add foreignkey if not sqlite
+    try:
+        if not dialect.startswith('sqlite'):
+            ForeignKeyConstraint(columns=[fixed_ips.c.virtual_interface_id],
+                                 refcolumns=[virtual_interfaces.c.id]).create()
+    except Exception:
+        logging.error(_("foreign key constraint couldn't be added"))
+        raise
+
+
+def downgrade(migrate_engine):
+    meta.bind = migrate_engine
+    dialect = migrate_engine.url.get_dialect().name
+
+    # drop foreignkey if not sqlite
+    try:
+        if not dialect.startswith('sqlite'):
+            ForeignKeyConstraint(columns=[fixed_ips.c.virtual_interface_id],
+                                 refcolumns=[virtual_interfaces.c.id]).drop()
+    except Exception:
+        logging.error(_("foreign key constraint couldn't be dropped"))
+        raise
diff --git a/nova/db/sqlalchemy/migrate_repo/versions/024_sqlite_downgrade.sql b/nova/db/sqlalchemy/migrate_repo/versions/024_sqlite_downgrade.sql
new file mode 100644
index 000000000..c1d26b180
--- /dev/null
+++ b/nova/db/sqlalchemy/migrate_repo/versions/024_sqlite_downgrade.sql
@@ -0,0 +1,48 @@
+BEGIN TRANSACTION;
+
+    CREATE TEMPORARY TABLE fixed_ips_backup (
+        id INTEGER NOT NULL,
+        address VARCHAR(255),
+        virtual_interface_id INTEGER,
+        network_id INTEGER,
+        instance_id INTEGER,
+        allocated BOOLEAN default FALSE,
+        leased BOOLEAN default FALSE,
+        reserved BOOLEAN default FALSE,
+        created_at DATETIME NOT NULL,
+        updated_at DATETIME,
+        deleted_at DATETIME,
+        deleted BOOLEAN NOT NULL,
+        PRIMARY KEY (id),
+        FOREIGN KEY(virtual_interface_id) REFERENCES virtual_interfaces (id)
+    );
+
+    INSERT INTO fixed_ips_backup
+    SELECT id, address, virtual_interface_id, network_id, instance_id, allocated, leased, reserved, created_at, updated_at, deleted_at, deleted
+    FROM fixed_ips;
+
+    DROP TABLE fixed_ips;
+
+    CREATE TABLE fixed_ips (
+        id INTEGER NOT NULL,
+        address VARCHAR(255),
+        virtual_interface_id INTEGER,
+        network_id INTEGER,
+        instance_id INTEGER,
+        allocated BOOLEAN default FALSE,
+        leased BOOLEAN default FALSE,
+        reserved BOOLEAN default FALSE,
+        created_at DATETIME NOT NULL,
+        updated_at DATETIME,
+        deleted_at DATETIME,
+        deleted BOOLEAN NOT NULL,
+        PRIMARY KEY (id)
+    );
+
+    INSERT INTO fixed_ips
+    SELECT id, address, virtual_interface_id, network_id, instance_id, allocated, leased, reserved, created_at, updated_at, deleted_at, deleted
+    FROM fixed_ips;
+
+    DROP TABLE fixed_ips_backup;
+
+COMMIT;
diff --git a/nova/db/sqlalchemy/migrate_repo/versions/024_sqlite_upgrade.sql b/nova/db/sqlalchemy/migrate_repo/versions/024_sqlite_upgrade.sql
new file mode 100644
index 000000000..2a9362545
--- /dev/null
+++ b/nova/db/sqlalchemy/migrate_repo/versions/024_sqlite_upgrade.sql
@@ -0,0 +1,48 @@
+BEGIN TRANSACTION;
+
+    CREATE TEMPORARY TABLE fixed_ips_backup (
+        id INTEGER NOT NULL,
+        address VARCHAR(255),
+        virtual_interface_id INTEGER,
+        network_id INTEGER,
+        instance_id INTEGER,
+        allocated BOOLEAN default FALSE,
+        leased BOOLEAN default FALSE,
+        reserved BOOLEAN default FALSE,
+        created_at DATETIME NOT NULL,
+        updated_at DATETIME,
+        deleted_at DATETIME,
+        deleted BOOLEAN NOT NULL,
+        PRIMARY KEY (id)
+    );
+
+    INSERT INTO fixed_ips_backup
+    SELECT id, address, virtual_interface_id, network_id, instance_id, allocated, leased, reserved, created_at, updated_at, deleted_at, deleted
+    FROM fixed_ips;
+
+    DROP TABLE fixed_ips;
+
+    CREATE TABLE fixed_ips (
+        id INTEGER NOT NULL,
+        address VARCHAR(255),
+        virtual_interface_id INTEGER,
+        network_id INTEGER,
+        instance_id INTEGER,
+        allocated BOOLEAN default FALSE,
+        leased BOOLEAN default FALSE,
+        reserved BOOLEAN default FALSE,
+        created_at DATETIME NOT NULL,
+        updated_at DATETIME,
+        deleted_at DATETIME,
+        deleted BOOLEAN NOT NULL,
+        PRIMARY KEY (id),
+        FOREIGN KEY(virtual_interface_id) REFERENCES virtual_interfaces (id)
+    );
+
+    INSERT INTO fixed_ips
+    SELECT id, address, virtual_interface_id, network_id, instance_id, allocated, leased, reserved, created_at, updated_at, deleted_at, deleted
+    FROM fixed_ips;
+
+    DROP TABLE fixed_ips_backup;
+
+COMMIT;