← Back to branch summary

~ubuntu-branches/ubuntu/saucy/nova/saucy-proposed

« back to all changes in this revision

Viewing changes to nova/db/sqlalchemy/migrate_repo/versions/090_sqlite_upgrade.sql

  • Committer: Package Import Robot
  • Author(s): Chuck Short
  • Date: 2012-05-24 13:12:53 UTC
  • mfrom: (1.1.55)
  • Revision ID: package-import@ubuntu.com-20120524131253-ommql08fg1en06ut
Tags: 2012.2~f1-0ubuntu1
https://launchpad.net/bugs/907197
* New upstream release.
* Prepare for quantal:
  - Dropped debian/patches/upstream/0006-Use-project_id-in-ec2.cloud._format_image.patch
  - Dropped debian/patches/upstream/0005-Populate-image-properties-with-project_id-again.patch
  - Dropped debian/patches/upstream/0004-Fixed-bug-962840-added-a-test-case.patch
  - Dropped debian/patches/upstream/0003-Allow-unprivileged-RADOS-users-to-access-rbd-volumes.patch
  - Dropped debian/patches/upstream/0002-Stop-libvirt-test-from-deleting-instances-dir.patch
  - Dropped debian/patches/upstream/0001-fix-bug-where-nova-ignores-glance-host-in-imageref.patch 
  - Dropped debian/patches/0001-fix-useexisting-deprecation-warnings.patch
* debian/control: Add python-keystone as a dependency. (LP: #907197)
* debian/patches/kombu_tests_timeout.patch: Refreshed.
* debian/nova.conf, debian/nova-common.postinst: Convert to new ini
  file configuration
* debian/patches/nova-manage_flagfile_location.patch: Refreshed

Show diffs side-by-side

added added

removed removed

Lines of Context:
nova/db/sqlalchemy/migrate_repo/versions/090_sqlite_upgrade.sql
 
1
BEGIN TRANSACTION;
 
2
 
 
3
    -- change id and snapshot_id datatypes in volumes table
 
4
    CREATE TABLE volumes_backup(
 
5
        created_at DATETIME,
 
6
        updated_at DATETIME,
 
7
        deleted_at DATETIME,
 
8
        deleted BOOLEAN,
 
9
        id VARCHAR(36) NOT NULL,
 
10
        ec2_id INTEGER,
 
11
        user_id VARCHAR(255),
 
12
        project_id VARCHAR(255),
 
13
        snapshot_id VARCHAR(36),
 
14
        host VARCHAR(255),
 
15
        size INTEGER,
 
16
        availability_zone VARCHAR(255),
 
17
        instance_id INTEGER,
 
18
        mountpoint VARCHAR(255),
 
19
        attach_time VARCHAR(255),
 
20
        status VARCHAR(255),
 
21
        attach_status VARCHAR(255),
 
22
        scheduled_at DATETIME,
 
23
        launched_at DATETIME,
 
24
        terminated_at DATETIME,
 
25
        display_name VARCHAR(255),
 
26
        display_description VARCHAR(255),
 
27
        provider_location VARCHAR(255),
 
28
        provider_auth VARCHAR(255),
 
29
        volume_type_id INTEGER,
 
30
        PRIMARY KEY (id),
 
31
        FOREIGN KEY(instance_id) REFERENCES instances (id),
 
32
        UNIQUE (id),
 
33
        CHECK (deleted IN (0, 1))
 
34
    );
 
35
 
 
36
    INSERT INTO volumes_backup SELECT
 
37
        created_at,
 
38
        updated_at,
 
39
        deleted_at,
 
40
        deleted,
 
41
        id,
 
42
        ec2_id,
 
43
        user_id,
 
44
        project_id,
 
45
        snapshot_id,
 
46
        host,
 
47
        size,
 
48
        availability_zone,
 
49
        instance_id,
 
50
        mountpoint,
 
51
        attach_time,
 
52
        status,
 
53
        attach_status,
 
54
        scheduled_at,
 
55
        launched_at,
 
56
        terminated_at,
 
57
        display_name,
 
58
        display_description,
 
59
        provider_location,
 
60
        provider_auth,
 
61
        volume_type_id
 
62
    FROM volumes;
 
63
    DROP TABLE volumes;
 
64
    ALTER TABLE volumes_backup RENAME TO volumes;
 
65
 
 
66
    -- change id and volume_id datatypes in snapshots table
 
67
    CREATE TABLE snapshots_backup (
 
68
        created_at DATETIME,
 
69
        updated_at DATETIME,
 
70
        deleted_at DATETIME,
 
71
        deleted BOOLEAN,
 
72
        id VARCHAR(36) NOT NULL,
 
73
        user_id VARCHAR(255),
 
74
        project_id VARCHAR(255),
 
75
        volume_id VARCHAR(36),
 
76
        status VARCHAR(255),
 
77
        progress VARCHAR(255),
 
78
        volume_size INTEGER,
 
79
        display_name VARCHAR(255),
 
80
        display_description VARCHAR(255),
 
81
        PRIMARY KEY (id),
 
82
        UNIQUE (id),
 
83
        CHECK (deleted IN (0, 1))
 
84
    );
 
85
    INSERT INTO snapshots_backup SELECT
 
86
        created_at,
 
87
        updated_at,
 
88
        deleted_at,
 
89
        deleted,
 
90
        id,
 
91
        user_id,
 
92
        project_id,
 
93
        volume_id,
 
94
        status,
 
95
        progress,
 
96
        volume_size,
 
97
        display_name,
 
98
        display_description
 
99
    FROM snapshots;
 
100
    DROP TABLE snapshots;
 
101
    ALTER TABLE snapshots_backup RENAME TO snapshots;
 
102
 
 
103
    -- change id and volume_id datatypes in iscsi_targets table
 
104
    CREATE TABLE iscsi_targets_backup (
 
105
        created_at DATETIME,
 
106
        updated_at DATETIME,
 
107
        deleted_at DATETIME,
 
108
        deleted BOOLEAN,
 
109
        id INTEGER NOT NULL,
 
110
        target_num INTEGER,
 
111
        host VARCHAR(255),
 
112
        volume_id VARCHAR(36),
 
113
        PRIMARY KEY (id),
 
114
        FOREIGN KEY(volume_id) REFERENCES volumes(id),
 
115
        UNIQUE (id),
 
116
        CHECK (deleted IN (0, 1))
 
117
    );
 
118
    INSERT INTO iscsi_targets_backup SELECT
 
119
        created_at,
 
120
        updated_at,
 
121
        deleted_at,
 
122
        deleted,
 
123
        id,
 
124
        target_num,
 
125
        host,
 
126
        volume_id
 
127
    FROM iscsi_targets;
 
128
    DROP TABLE iscsi_targets;
 
129
    ALTER TABLE iscsi_targets_backup RENAME TO iscsi_targets;
 
130
 
 
131
    CREATE TABLE volume_metadata_backup (
 
132
        created_at DATETIME,
 
133
        updated_at DATETIME,
 
134
        deleted_at DATETIME,
 
135
        deleted BOOLEAN,
 
136
        id INTEGER NOT NULL,
 
137
        key VARCHAR(255),
 
138
        value VARCHAR(255),
 
139
        volume_id VARCHAR(36),
 
140
        PRIMARY KEY (id),
 
141
        FOREIGN KEY(volume_id) REFERENCES volumes(id),
 
142
        UNIQUE (id),
 
143
        CHECK (deleted IN (0, 1))
 
144
    );
 
145
    INSERT INTO volume_metadata_backup SELECT
 
146
        created_at,
 
147
        updated_at,
 
148
        deleted_at,
 
149
        deleted,
 
150
        id,
 
151
        key,
 
152
        value,
 
153
        volume_id
 
154
    FROM volume_metadata;
 
155
    DROP TABLE volume_metadata;
 
156
    ALTER TABLE volume_metadata_backup RENAME TO volume_metadata;
 
157
 
 
158
    -- change volume_id and snapshot_id datatypes in bdm table
 
159
    CREATE TABLE block_device_mapping_backup (
 
160
        created_at DATETIME,
 
161
        updated_at DATETIME,
 
162
        deleted_at DATETIME,
 
163
        deleted BOOLEAN,
 
164
        id INTEGER NOT NULL,
 
165
        instance_uuid VARCHAR(36) NOT NULL,
 
166
        device_name VARCHAR(255),
 
167
        delete_on_termination BOOLEAN,
 
168
        virtual_name VARCHAR(255),
 
169
        snapshot_id VARCHAR(36),
 
170
        volume_id VARCHAR(36),
 
171
        volume_size INTEGER,
 
172
        no_device BOOLEAN,
 
173
        connection_info VARCHAR(255),
 
174
        FOREIGN KEY(instance_uuid) REFERENCES instances(id),
 
175
        FOREIGN KEY(volume_id) REFERENCES volumes(id),
 
176
        FOREIGN KEY(snapshot_id) REFERENCES snapshots(id),
 
177
        PRIMARY KEY (id),
 
178
        UNIQUE (id),
 
179
        CHECK (deleted IN (0, 1))
 
180
    );
 
181
    INSERT INTO block_device_mapping_backup SELECT
 
182
        created_at,
 
183
        updated_at,
 
184
        deleted_at,
 
185
        deleted,
 
186
        id,
 
187
        instance_uuid,
 
188
        device_name,
 
189
        delete_on_termination,
 
190
        virtual_name,
 
191
        snapshot_id,
 
192
        volume_id,
 
193
        volume_size,
 
194
        no_device,
 
195
        connection_info
 
196
    FROM block_device_mapping;
 
197
    DROP TABLE block_device_mapping;
 
198
    ALTER TABLE block_device_mapping_backup RENAME TO block_device_mapping;
 
199
 
 
200
    -- change volume_id and sm_volume_table
 
201
    CREATE TABLE sm_volume_backup (
 
202
        created_at DATETIME,
 
203
        updated_at DATETIME,
 
204
        deleted_at DATETIME,
 
205
        deleted BOOLEAN,
 
206
        id VARCHAR(36) NOT NULL,
 
207
        backend_id INTEGER NOT NULL,
 
208
        vdi_uuid VARCHAR(255),
 
209
        PRIMARY KEY (id),
 
210
        FOREIGN KEY(id) REFERENCES volumes(id),
 
211
        UNIQUE (id),
 
212
        CHECK (deleted IN (0,1))
 
213
    );
 
214
    INSERT INTO sm_volume_backup SELECT
 
215
        created_at,
 
216
        updated_at,
 
217
        deleted_at,
 
218
        deleted,
 
219
        id,
 
220
        backend_id,
 
221
        vdi_uuid
 
222
    FROM sm_volume;
 
223
    DROP TABLE sm_volume;
 
224
    ALTER TABLE sm_volume_backup RENAME TO sm_volume;
 
225
 
 
226
COMMIT;