Revert IP Address column length to 39

In change I35d4565389d03fce86103289cf6447f824efeba0 we changed the cidr/ip
address column size to 43 to be able to support ipv6 cidr values, although ips
doesn't need that much space so we're reverting it to keep it consistent.

Change-Id: Icd2d727fd0733ee0db23c1fa431df4b982cae7b4

4 files changed, 274 insertions, 0 deletions

diff --git a/nova/db/sqlalchemy/migrate_repo/versions/159_revert_ip_column_length.py b/nova/db/sqlalchemy/migrate_repo/versions/159_revert_ip_column_length.py
new file mode 100644
index 000000000..65e11c42c
--- /dev/null
+++ b/nova/db/sqlalchemy/migrate_repo/versions/159_revert_ip_column_length.py
@@ -0,0 +1,63 @@
+# vim: tabstop=4 shiftwidth=4 softtabstop=4
+
+# Copyright 2013 IBM Corp.
+# 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.
+
+from sqlalchemy import MetaData, String, Table
+from sqlalchemy.dialects import postgresql
+
+TABLE_COLUMNS = [
+    # table name, column name
+    ('instances', 'access_ip_v4'),
+    ('instances', 'access_ip_v6'),
+    ('networks', 'gateway'),
+    ('networks', 'gateway_v6'),
+    ('networks', 'netmask'),
+    ('networks', 'netmask_v6'),
+    ('networks', 'broadcast'),
+    ('networks', 'dns1'),
+    ('networks', 'dns2'),
+    ('networks', 'vpn_public_address'),
+    ('networks', 'vpn_private_address'),
+    ('networks', 'dhcp_start'),
+    ('fixed_ips', 'address'),
+    ('floating_ips', 'address'),
+    ('console_pools', 'address')]
+
+
+def upgrade(migrate_engine):
+    dialect = migrate_engine.url.get_dialect()
+
+    # NOTE(maurosr): this just affects mysql; postgresql uses INET
+    # type and sqlite doesn't goes fine with alter tables, so it should be done
+    # manually. This way we'll be able to keep UCs like the one inserted on
+    # version 158 which would get lost cause sqlite is not migrated as mysql or
+    # pgsql, it copies the column and data instead of execute an alter table
+    # command.
+    if dialect is not postgresql.dialect:
+        meta = MetaData(bind=migrate_engine)
+        for table, column in TABLE_COLUMNS:
+            t = Table(table, meta, autoload=True)
+            getattr(t.c, column).alter(type=String(39))
+
+
+def downgrade(migrate_engine):
+    """Convert columns back to the larger String(43) defined in version 149."""
+    dialect = migrate_engine.url.get_dialect()
+    if dialect is not postgresql.dialect:
+        meta = MetaData(bind=migrate_engine)
+        for table, column in TABLE_COLUMNS:
+            t = Table(table, meta, autoload=True)
+            getattr(t.c, column).alter(type=String(43))
diff --git a/nova/db/sqlalchemy/migrate_repo/versions/159_sqlite_downgrade.sql b/nova/db/sqlalchemy/migrate_repo/versions/159_sqlite_downgrade.sql
new file mode 100644
index 000000000..381d8f26b
--- /dev/null
+++ b/nova/db/sqlalchemy/migrate_repo/versions/159_sqlite_downgrade.sql
@@ -0,0 +1,81 @@
+BEGIN TRANSACTION;
+    /* create networks_backup table with the fields like networks was before
+    the upgrade */
+    CREATE TABLE networks_backup(
+        created_at DATETIME,
+        updated_at DATETIME,
+        deleted_at DATETIME,
+        deleted INTEGER,
+        id INTEGER NOT NULL,
+        injected BOOLEAN,
+        cidr VARCHAR(43),
+        netmask VARCHAR(43),
+        bridge VARCHAR(255),
+        gateway VARCHAR(43),
+        broadcast VARCHAR(43),
+        dns1 VARCHAR(43),
+        vlan INTEGER,
+        vpn_public_address VARCHAR(43),
+        vpn_public_port INTEGER,
+        vpn_private_address VARCHAR(43),
+        dhcp_start VARCHAR(43),
+        project_id VARCHAR(255),
+        host VARCHAR(255),
+        cidr_v6 VARCHAR(43),
+        gateway_v6 VARCHAR(43),
+        label VARCHAR(255),
+        netmask_v6 VARCHAR(43),
+        bridge_interface VARCHAR(255),
+        multi_host BOOLEAN,
+        dns2 VARCHAR(43),
+        uuid VARCHAR(36),
+        priority INTEGER,
+        rxtx_base INTEGER,
+        PRIMARY KEY (id)
+    );
+
+    /* copy data currently on networks to the backup table and drop networks
+    table */
+    INSERT INTO networks_backup SELECT * FROM networks;
+    DROP TABLE networks;
+
+    CREATE TABLE networks (
+        created_at DATETIME,
+        updated_at DATETIME,
+        deleted_at DATETIME,
+        deleted INTEGER,
+        id INTEGER NOT NULL,
+        injected BOOLEAN,
+        cidr VARCHAR(43),
+        netmask VARCHAR(43),
+        bridge VARCHAR(255),
+        gateway VARCHAR(43),
+        broadcast VARCHAR(43),
+        dns1 VARCHAR(43),
+        vlan INTEGER,
+        vpn_public_address VARCHAR(43),
+        vpn_public_port INTEGER,
+        vpn_private_address VARCHAR(43),
+        dhcp_start VARCHAR(43),
+        project_id VARCHAR(255),
+        host VARCHAR(255),
+        cidr_v6 VARCHAR(43),
+        gateway_v6 VARCHAR(43),
+        label VARCHAR(255),
+        netmask_v6 VARCHAR(43),
+        bridge_interface VARCHAR(255),
+        multi_host BOOLEAN,
+        dns2 VARCHAR(43),
+        uuid VARCHAR(36),
+        priority INTEGER,
+        rxtx_base INTEGER,
+        PRIMARY KEY (id),
+        CHECK (injected IN (0, 1)),
+        CHECK (multi_host IN (0, 1)),
+        CONSTRAINT uniq_vlan_x_deleted UNIQUE (vlan, deleted)
+);
+
+    /* Get  data from backup table and drop it next */
+    INSERT INTO networks SELECT * FROM networks_backup;
+    DROP TABLE networks_backup;
+COMMIT;
diff --git a/nova/db/sqlalchemy/migrate_repo/versions/159_sqlite_upgrade.sql b/nova/db/sqlalchemy/migrate_repo/versions/159_sqlite_upgrade.sql
new file mode 100644
index 000000000..206155baa
--- /dev/null
+++ b/nova/db/sqlalchemy/migrate_repo/versions/159_sqlite_upgrade.sql
@@ -0,0 +1,79 @@
+BEGIN TRANSACTION;
+    /* Create a backup table with the new fields size */
+    CREATE TABLE networks_backup(
+        created_at DATETIME,
+        updated_at DATETIME,
+        deleted_at DATETIME,
+        deleted INTEGER,
+        id INTEGER NOT NULL,
+        injected BOOLEAN,
+        cidr VARCHAR(43),
+        netmask VARCHAR(39),
+        bridge VARCHAR(255),
+        gateway VARCHAR(39),
+        broadcast VARCHAR(39),
+        dns1 VARCHAR(39),
+        vlan INTEGER,
+        vpn_public_address VARCHAR(39),
+        vpn_public_port INTEGER,
+        vpn_private_address VARCHAR(39),
+        dhcp_start VARCHAR(39),
+        project_id VARCHAR(255),
+        host VARCHAR(255),
+        cidr_v6 VARCHAR(43),
+        gateway_v6 VARCHAR(39),
+        label VARCHAR(255),
+        netmask_v6 VARCHAR(39),
+        bridge_interface VARCHAR(255),
+        multi_host BOOLEAN,
+        dns2 VARCHAR(39),
+        uuid VARCHAR(36),
+        priority INTEGER,
+        rxtx_base INTEGER,
+        PRIMARY KEY (id)
+    );
+
+    /* get data from networks and the drop it */
+    INSERT INTO networks_backup SELECT * FROM networks;
+    DROP TABLE networks;
+
+    CREATE TABLE networks (
+        created_at DATETIME,
+        updated_at DATETIME,
+        deleted_at DATETIME,
+        deleted INTEGER,
+        id INTEGER NOT NULL,
+        injected BOOLEAN,
+        cidr VARCHAR(43),
+        netmask VARCHAR(39),
+        bridge VARCHAR(255),
+        gateway VARCHAR(39),
+        broadcast VARCHAR(39),
+        dns1 VARCHAR(39),
+        vlan INTEGER,
+        vpn_public_address VARCHAR(39),
+        vpn_public_port INTEGER,
+        vpn_private_address VARCHAR(39),
+        dhcp_start VARCHAR(39),
+        project_id VARCHAR(255),
+        host VARCHAR(255),
+        cidr_v6 VARCHAR(43),
+        gateway_v6 VARCHAR(39),
+        label VARCHAR(255),
+        netmask_v6 VARCHAR(39),
+        bridge_interface VARCHAR(255),
+        multi_host BOOLEAN,
+        dns2 VARCHAR(39),
+        uuid VARCHAR(36),
+        priority INTEGER,
+        rxtx_base INTEGER,
+        PRIMARY KEY (id),
+        CHECK (injected IN (0, 1)),
+        CHECK (multi_host IN (0, 1)),
+        CONSTRAINT uniq_vlan_x_deleted UNIQUE (vlan, deleted)
+    );
+
+    /* get data from networks_backup back and drop it */
+    INSERT INTO networks SELECT * FROM networks_backup;
+    DROP TABLE networks_backup;
+COMMIT;
diff --git a/nova/tests/test_migrations.py b/nova/tests/test_migrations.py
index ef25ca726..d0e8e5035 100644
--- a/nova/tests/test_migrations.py
+++ b/nova/tests/test_migrations.py
@@ -47,6 +47,8 @@ import datetime
 import netaddr
 import os
 import sqlalchemy
+from sqlalchemy.dialects import postgresql
+from sqlalchemy.dialects import sqlite
 import sqlalchemy.exc
 import urlparse
 import uuid
@@ -902,6 +904,55 @@ class TestNovaMigrations(BaseMigrationTestCase, CommonTestsMixIn):
                     fetchall()
         self.assertEqual(len(rows), 1)
 
+    def _pre_upgrade_159(self, engine):
+        data = {
+            'provider_fw_rules':
+                [
+                {'protocol': 'tcp', 'from_port': 1234,
+                 'to_port': 1234, 'cidr': "127.0.0.1/30"},
+                {'protocol': 'tcp', 'from_port': 1234,
+                 'to_port': 1234, 'cidr': "128.128.128.128/16"},
+                {'protocol': 'tcp', 'from_port': 1234,
+                 'to_port': 1234, 'cidr': "128.128.128.128/32"},
+                {'protocol': 'tcp', 'from_port': 1234,
+                 'to_port': 1234, 'cidr': "2001:db8::1:2/48"},
+                {'protocol': 'tcp', 'from_port': 1234,
+                 'to_port': 1234, 'cidr': "::1/64"},
+                {'protocol': 'tcp', 'from_port': 1234, 'to_port': 1234,
+                 'cidr': "0000:0000:0000:2013:0000:6535:abcd:ef11/64"},
+                {'protocol': 'tcp', 'from_port': 1234, 'to_port': 1234,
+                 'cidr': "0000:1020:0000:2013:0000:6535:abcd:ef11/128"},
+                ],
+            'console_pools':
+                [
+                {'address': '10.10.10.10'},
+                {'address': '128.100.100.100'},
+                {'address': '2002:2002:2002:2002:2002:2002:2002:2002'},
+                {'address': '::1'},
+                {'address': '0000:0000:0000:2013:0000:6535:abcd:ef11'}
+                ]
+            }
+        return data
+
+    # migration 159 - revert ip column size
+    def _check_159(self, engine, data):
+        dialect = engine.url.get_dialect()
+        # NOTE(maurosr): check if column length is 39 again (it currently makes
+        # sense only for mysql)
+        if dialect not in [postgresql.dialect, sqlite.dialect]:
+            console_pools = get_table(engine, 'console_pools')
+            self.assertEqual(console_pools.columns['address'].type.length, 39)
+        # recheck the 149 data
+        self._check_149(engine, data)
+
+    def _post_downgrade_159(self, engine):
+        dialect = engine.url.get_dialect()
+        # NOTE(maurosr): check if column length is 43 again (it currently makes
+        # sense only for mysql)
+        if dialect not in [postgresql.dialect, sqlite.dialect]:
+            console_pools = get_table(engine, 'console_pools')
+            self.assertEqual(console_pools.columns['address'].type.length, 43)
+
 
 class TestBaremetalMigrations(BaseMigrationTestCase, CommonTestsMixIn):
     """Test sqlalchemy-migrate migrations."""