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

glance/db/sqlalchemy/migrate_repo/versions/003_sqlite_downgrade.sql

@@ -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;

glance/db/sqlalchemy/migrate_repo/versions/003_sqlite_upgrade.sql

@@ -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;

glance/db/sqlalchemy/migrate_repo/versions/006_mysql_downgrade.sql

@@ -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;
 

glance/db/sqlalchemy/migrate_repo/versions/006_mysql_upgrade.sql

@@ -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;
 

glance/db/sqlalchemy/migrate_repo/versions/006_sqlite_downgrade.sql

@@ -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;

glance/db/sqlalchemy/migrate_repo/versions/006_sqlite_upgrade.sql

@@ -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;

glance/db/sqlalchemy/migrate_repo/versions/011_sqlite_downgrade.sql

@@ -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;

glance/db/sqlalchemy/migrate_repo/versions/011_sqlite_upgrade.sql

@@ -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;

glance/db/sqlalchemy/migrate_repo/versions/013_sqlite_downgrade.sql

@@ -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;

glance/db/sqlalchemy/migrate_repo/versions/016_sqlite_downgrade.sql

@@ -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;

glance/db/sqlalchemy/migrate_repo/versions/037_sqlite_downgrade.sql

@@ -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;

glance/db/sqlalchemy/migrate_repo/versions/037_sqlite_upgrade.sql

@@ -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;