SQL scripts should not manage transactions
Glance SQL scripts assume too much about the environment and the way in which they are executed by sqlalchemy-migrate. Particularly, they manage transactions on their own even though sqlalchemy-migrate do it itself, plus it assumes that scripts are executed in one go (even though standard Python DB-API 2.0 does not mention multi-statement SQL commands to be executed on DB cursors. Also fix comments to be actual SQL comments and not C comments https://blueprints.launchpad.net/oslo.db/+spec/enable-mysql-connector Change-Id: I10c58b3af75d3ab9153a8bbd2a539bf1577de328 Closes-Bug: 1368391 Closes-Bug: 1410494
This commit is contained in:
parent
a8b4b845a0
commit
92a7111e9e
|
@ -1,6 +1,4 @@
|
|||
BEGIN;
|
||||
|
||||
/* Make changes to the base images table */
|
||||
-- Make changes to the base images table
|
||||
CREATE TEMPORARY TABLE images_backup (
|
||||
id INTEGER NOT NULL,
|
||||
name VARCHAR(255),
|
||||
|
@ -46,13 +44,11 @@ FROM images_backup;
|
|||
|
||||
DROP TABLE images_backup;
|
||||
|
||||
/* Re-insert the type values from the temp table */
|
||||
-- Re-insert the type values from the temp table
|
||||
UPDATE images
|
||||
SET type = (SELECT value FROM image_properties WHERE image_id = images.id AND key = 'type')
|
||||
WHERE EXISTS (SELECT * FROM image_properties WHERE image_id = images.id AND key = 'type');
|
||||
|
||||
/* Remove the type properties from the image_properties table */
|
||||
-- Remove the type properties from the image_properties table
|
||||
DELETE FROM image_properties
|
||||
WHERE key = 'type';
|
||||
|
||||
COMMIT;
|
||||
|
|
|
@ -1,7 +1,5 @@
|
|||
BEGIN TRANSACTION;
|
||||
|
||||
/* Move type column from base images table
|
||||
* to be records in image_properties table */
|
||||
-- Move type column from base images table
|
||||
-- to be records in image_properties table
|
||||
CREATE TEMPORARY TABLE tmp_type_records (id INTEGER NOT NULL, type VARCHAR(30) NOT NULL);
|
||||
INSERT INTO tmp_type_records
|
||||
SELECT id, type
|
||||
|
@ -15,7 +13,7 @@ FROM tmp_type_records;
|
|||
|
||||
DROP TABLE tmp_type_records;
|
||||
|
||||
/* Make changes to the base images table */
|
||||
-- Make changes to the base images table
|
||||
CREATE TEMPORARY TABLE images_backup (
|
||||
id INTEGER NOT NULL,
|
||||
name VARCHAR(255),
|
||||
|
@ -61,4 +59,3 @@ SELECT id, name, size, status, is_public, location, created_at, updated_at, dele
|
|||
FROM images_backup;
|
||||
|
||||
DROP TABLE images_backup;
|
||||
COMMIT;
|
||||
|
|
|
@ -1,10 +1,10 @@
|
|||
/*
|
||||
* This file is necessary because MySQL does not support
|
||||
* renaming indexes.
|
||||
*/
|
||||
--
|
||||
-- This file is necessary because MySQL does not support
|
||||
-- renaming indexes.
|
||||
--
|
||||
DROP INDEX ix_image_properties_image_id_name ON image_properties;
|
||||
|
||||
/* Rename the `key` column to `name` */
|
||||
-- Rename the `key` column to `name`
|
||||
ALTER TABLE image_properties
|
||||
CHANGE COLUMN name `key` VARCHAR(255) NOT NULL;
|
||||
|
||||
|
|
|
@ -1,10 +1,10 @@
|
|||
/*
|
||||
* This file is necessary because MySQL does not support
|
||||
* renaming indexes.
|
||||
*/
|
||||
--
|
||||
-- This file is necessary because MySQL does not support
|
||||
-- renaming indexes.
|
||||
--
|
||||
DROP INDEX ix_image_properties_image_id_key ON image_properties;
|
||||
|
||||
/* Rename the `key` column to `name` */
|
||||
-- Rename the `key` column to `name`
|
||||
ALTER TABLE image_properties
|
||||
CHANGE COLUMN `key` name VARCHAR(255) NOT NULL;
|
||||
|
||||
|
|
|
@ -1,9 +1,7 @@
|
|||
/*
|
||||
* This is necessary because SQLite does not support
|
||||
* RENAME INDEX or ALTER TABLE CHANGE COLUMN.
|
||||
*/
|
||||
BEGIN TRANSACTION;
|
||||
|
||||
--
|
||||
-- This is necessary because SQLite does not support
|
||||
-- RENAME INDEX or ALTER TABLE CHANGE COLUMN.
|
||||
--
|
||||
CREATE TEMPORARY TABLE image_properties_backup (
|
||||
id INTEGER NOT NULL,
|
||||
image_id INTEGER NOT NULL,
|
||||
|
@ -43,4 +41,3 @@ SELECT id, image_id, key, value, created_at, updated_at, deleted_at, deleted
|
|||
FROM image_properties_backup;
|
||||
|
||||
DROP TABLE image_properties_backup;
|
||||
COMMIT;
|
||||
|
|
|
@ -1,9 +1,7 @@
|
|||
/*
|
||||
* This is necessary because SQLite does not support
|
||||
* RENAME INDEX or ALTER TABLE CHANGE COLUMN.
|
||||
*/
|
||||
BEGIN TRANSACTION;
|
||||
|
||||
--
|
||||
-- This is necessary because SQLite does not support
|
||||
-- RENAME INDEX or ALTER TABLE CHANGE COLUMN.
|
||||
--
|
||||
CREATE TEMPORARY TABLE image_properties_backup (
|
||||
id INTEGER NOT NULL,
|
||||
image_id INTEGER NOT NULL,
|
||||
|
@ -44,4 +42,3 @@ SELECT id, image_id, name, value, created_at, updated_at, deleted_at, deleted
|
|||
FROM image_properties_backup;
|
||||
|
||||
DROP TABLE image_properties_backup;
|
||||
COMMIT;
|
||||
|
|
|
@ -1,5 +1,3 @@
|
|||
BEGIN TRANSACTION;
|
||||
|
||||
CREATE TEMPORARY TABLE images_backup (
|
||||
id INTEGER NOT NULL,
|
||||
name VARCHAR(255),
|
||||
|
@ -58,4 +56,3 @@ SELECT id, name, size, status, is_public, location, created_at, updated_at,
|
|||
FROM images_backup;
|
||||
|
||||
DROP TABLE images_backup;
|
||||
COMMIT;
|
||||
|
|
|
@ -1,5 +1,3 @@
|
|||
BEGIN TRANSACTION;
|
||||
|
||||
CREATE TEMPORARY TABLE images_backup (
|
||||
id INTEGER NOT NULL,
|
||||
name VARCHAR(255),
|
||||
|
@ -59,4 +57,3 @@ SELECT id, name, size, status, is_public, location, created_at, updated_at,
|
|||
FROM images_backup;
|
||||
|
||||
DROP TABLE images_backup;
|
||||
COMMIT;
|
||||
|
|
|
@ -1,9 +1,7 @@
|
|||
/*
|
||||
* This is necessary because sqlalchemy has various bugs preventing
|
||||
* downgrades from working correctly.
|
||||
*/
|
||||
BEGIN TRANSACTION;
|
||||
|
||||
--
|
||||
-- This is necessary because sqlalchemy has various bugs preventing
|
||||
-- downgrades from working correctly.
|
||||
--
|
||||
CREATE TEMPORARY TABLE images_backup (
|
||||
id VARCHAR(36) NOT NULL,
|
||||
name VARCHAR(255),
|
||||
|
@ -62,4 +60,3 @@ SELECT id, name, size, status, is_public, location, created_at, updated_at, dele
|
|||
FROM images_backup;
|
||||
|
||||
DROP TABLE images_backup;
|
||||
COMMIT;
|
||||
|
|
|
@ -1,5 +1,3 @@
|
|||
BEGIN TRANSACTION;
|
||||
|
||||
CREATE TEMPORARY TABLE image_members_backup (
|
||||
id INTEGER NOT NULL,
|
||||
image_id VARCHAR(36) NOT NULL,
|
||||
|
@ -43,4 +41,3 @@ SELECT id, image_id, member, can_share, created_at, updated_at, deleted_at,
|
|||
FROM image_members_backup;
|
||||
|
||||
DROP TABLE image_members_backup;
|
||||
COMMIT;
|
||||
|
|
|
@ -1,5 +1,3 @@
|
|||
BEGIN TRANSACTION;
|
||||
|
||||
CREATE TEMPORARY TABLE images_backup (
|
||||
id VARCHAR(36) NOT NULL,
|
||||
name VARCHAR(255),
|
||||
|
@ -147,4 +145,3 @@ INSERT INTO image_properties (id, image_id, name, value, created_at, updated_at,
|
|||
FROM image_properties_backup;
|
||||
|
||||
DROP TABLE image_properties_backup;
|
||||
COMMIT;
|
|
@ -1,5 +1,3 @@
|
|||
BEGIN TRANSACTION;
|
||||
|
||||
UPDATE images SET protected = 0 WHERE protected is NULL;
|
||||
UPDATE image_members SET status = 'pending' WHERE status is NULL;
|
||||
|
||||
|
@ -160,4 +158,3 @@ INSERT INTO image_properties (id, image_id, name, value, created_at, updated_at,
|
|||
FROM image_properties_backup;
|
||||
|
||||
DROP TABLE image_properties_backup;
|
||||
COMMIT;
|
Loading…
Reference in New Issue