This is an automated email from the ASF dual-hosted git repository.
dahn pushed a commit to branch 4.19
in repository https://gitbox.apache.org/repos/asf/cloudstack.git
The following commit(s) were added to refs/heads/4.19 by this push:
new 6e6a2766637 add procedures procedure (#9385)
6e6a2766637 is described below
commit 6e6a2766637c604d4cc812d34c6a05b767904991
Author: dahn <[email protected]>
AuthorDate: Mon Aug 12 05:47:34 2024 -0700
add procedures procedure (#9385)
---
.../com/cloud/upgrade/DatabaseUpgradeChecker.java | 160 +++++++++++++--------
.../cloud.add_guest_os_and_hypervisor_mapping.sql | 49 +++++++
.../db/procedures/cloud.idempotent_add_column.sql | 27 ++++
.../db/procedures/cloud.idempotent_add_key.sql | 27 ++++
.../cloud.idempotent_add_unique_index.sql | 26 ++++
.../procedures/cloud.idempotent_add_unique_key.sql | 26 ++++
.../procedures/cloud.idempotent_change_column.sql | 27 ++++
.../cloud.idempotent_create_unique_index.sql | 27 ++++
.../cloud.idempotent_drop_foreign_key.sql | 25 ++++
.../db/procedures/cloud.idempotent_drop_index.sql | 27 ++++
...dempotent_insert_guestos_hypervisor_mapping.sql | 48 +++++++
.../db/procedures/usage.idempotent_add_column.sql | 26 ++++
.../procedures/usage.idempotent_change_column.sql | 27 ++++
.../db/procedures/usage.idempotent_drop_index.sql | 25 ++++
.../resources/META-INF/db/schema-41000to41100.sql | 45 ------
.../resources/META-INF/db/schema-41520to41600.sql | 47 ------
.../resources/META-INF/db/schema-41600to41610.sql | 54 -------
.../resources/META-INF/db/schema-41610to41700.sql | 45 ------
.../resources/META-INF/db/schema-41720to41800.sql | 59 --------
.../resources/META-INF/db/schema-4910to4920.sql | 38 -----
20 files changed, 486 insertions(+), 349 deletions(-)
diff --git
a/engine/schema/src/main/java/com/cloud/upgrade/DatabaseUpgradeChecker.java
b/engine/schema/src/main/java/com/cloud/upgrade/DatabaseUpgradeChecker.java
index 614e6058aa6..b908455c1fe 100644
--- a/engine/schema/src/main/java/com/cloud/upgrade/DatabaseUpgradeChecker.java
+++ b/engine/schema/src/main/java/com/cloud/upgrade/DatabaseUpgradeChecker.java
@@ -128,6 +128,7 @@ public class DatabaseUpgradeChecker implements
SystemIntegrityChecker {
private static final Logger s_logger =
Logger.getLogger(DatabaseUpgradeChecker.class);
private final DatabaseVersionHierarchy hierarchy;
private static final String VIEWS_DIRECTORY = Paths.get("META-INF", "db",
"views").toString();
+ private static final String PROCEDURES_DIRECTORY = Paths.get("META-INF",
"db", "procedures").toString();
@Inject
VersionDao _dao;
@@ -295,83 +296,120 @@ public class DatabaseUpgradeChecker implements
SystemIntegrityChecker {
}
protected void upgrade(CloudStackVersion dbVersion, CloudStackVersion
currentVersion) {
+ executeProcedureScripts();
+ final DbUpgrade[] upgrades = executeUpgrades(dbVersion,
currentVersion);
+
+ executeViewScripts();
+ updateSystemVmTemplates(upgrades);
+ }
+
+ protected void executeProcedureScripts() {
+ s_logger.info(String.format("Executing Stored Procedure scripts that
are under resource directory [%s].", PROCEDURES_DIRECTORY));
+ List<String> filesPathUnderViewsDirectory =
FileUtil.getFilesPathsUnderResourceDirectory(PROCEDURES_DIRECTORY);
+
+ try (TransactionLegacy txn =
TransactionLegacy.open("execute-procedure-scripts")) {
+ Connection conn = txn.getConnection();
+
+ for (String filePath : filesPathUnderViewsDirectory) {
+ s_logger.debug(String.format("Executing PROCEDURE script
[%s].", filePath));
+
+ InputStream viewScript =
Thread.currentThread().getContextClassLoader().getResourceAsStream(filePath);
+ runScript(conn, viewScript);
+ }
+
+ s_logger.info(String.format("Finished execution of PROCEDURE
scripts that are under resource directory [%s].", PROCEDURES_DIRECTORY));
+ } catch (SQLException e) {
+ String message = String.format("Unable to execute PROCEDURE
scripts due to [%s].", e.getMessage());
+ s_logger.error(message, e);
+ throw new CloudRuntimeException(message, e);
+ }
+ }
+
+ private DbUpgrade[] executeUpgrades(CloudStackVersion dbVersion,
CloudStackVersion currentVersion) {
s_logger.info("Database upgrade must be performed from " + dbVersion +
" to " + currentVersion);
final DbUpgrade[] upgrades = calculateUpgradePath(dbVersion,
currentVersion);
for (DbUpgrade upgrade : upgrades) {
- VersionVO version;
- s_logger.debug("Running upgrade " +
upgrade.getClass().getSimpleName() + " to upgrade from " +
upgrade.getUpgradableVersionRange()[0] + "-" + upgrade
- .getUpgradableVersionRange()[1] + " to " +
upgrade.getUpgradedVersion());
- TransactionLegacy txn = TransactionLegacy.open("Upgrade");
- txn.start();
- try {
- Connection conn;
- try {
- conn = txn.getConnection();
- } catch (SQLException e) {
- String errorMessage = "Unable to upgrade the database";
- s_logger.error(errorMessage, e);
- throw new CloudRuntimeException(errorMessage, e);
- }
- InputStream[] scripts = upgrade.getPrepareScripts();
- if (scripts != null) {
- for (InputStream script : scripts) {
- runScript(conn, script);
- }
- }
-
- upgrade.performDataMigration(conn);
-
- version = new VersionVO(upgrade.getUpgradedVersion());
- version = _dao.persist(version);
+ VersionVO version = executeUpgrade(upgrade);
+ executeUpgradeCleanup(upgrade, version);
+ }
+ return upgrades;
+ }
- txn.commit();
- } catch (CloudRuntimeException e) {
+ private VersionVO executeUpgrade(DbUpgrade upgrade) {
+ VersionVO version;
+ s_logger.debug("Running upgrade " + upgrade.getClass().getSimpleName()
+ " to upgrade from " + upgrade.getUpgradableVersionRange()[0] + "-" + upgrade
+ .getUpgradableVersionRange()[1] + " to " +
upgrade.getUpgradedVersion());
+ TransactionLegacy txn = TransactionLegacy.open("Upgrade");
+ txn.start();
+ try {
+ Connection conn;
+ try {
+ conn = txn.getConnection();
+ } catch (SQLException e) {
String errorMessage = "Unable to upgrade the database";
s_logger.error(errorMessage, e);
throw new CloudRuntimeException(errorMessage, e);
- } finally {
- txn.close();
+ }
+ InputStream[] scripts = upgrade.getPrepareScripts();
+ if (scripts != null) {
+ for (InputStream script : scripts) {
+ runScript(conn, script);
+ }
}
- // Run the corresponding '-cleanup.sql' script
- txn = TransactionLegacy.open("Cleanup");
- try {
- s_logger.info("Cleanup upgrade " +
upgrade.getClass().getSimpleName() + " to upgrade from " +
upgrade.getUpgradableVersionRange()[0] + "-" + upgrade
- .getUpgradableVersionRange()[1] + " to " +
upgrade.getUpgradedVersion());
+ upgrade.performDataMigration(conn);
- txn.start();
- Connection conn;
- try {
- conn = txn.getConnection();
- } catch (SQLException e) {
- s_logger.error("Unable to cleanup the database", e);
- throw new CloudRuntimeException("Unable to cleanup the
database", e);
- }
+ version = new VersionVO(upgrade.getUpgradedVersion());
+ version = _dao.persist(version);
- InputStream[] scripts = upgrade.getCleanupScripts();
- if (scripts != null) {
- for (InputStream script : scripts) {
- runScript(conn, script);
- s_logger.debug("Cleanup script " +
upgrade.getClass().getSimpleName() + " is executed successfully");
- }
- }
- txn.commit();
+ txn.commit();
+ } catch (CloudRuntimeException e) {
+ String errorMessage = "Unable to upgrade the database";
+ s_logger.error(errorMessage, e);
+ throw new CloudRuntimeException(errorMessage, e);
+ } finally {
+ txn.close();
+ }
+ return version;
+ }
- txn.start();
- version.setStep(Step.Complete);
- version.setUpdated(new Date());
- _dao.update(version.getId(), version);
- txn.commit();
- s_logger.debug("Upgrade completed for version " +
version.getVersion());
- } finally {
- txn.close();
+ private void executeUpgradeCleanup(DbUpgrade upgrade, VersionVO version) {
+ TransactionLegacy txn;
+ // Run the corresponding '-cleanup.sql' script
+ txn = TransactionLegacy.open("Cleanup");
+ try {
+ s_logger.info("Cleanup upgrade " +
upgrade.getClass().getSimpleName() + " to upgrade from " +
upgrade.getUpgradableVersionRange()[0] + "-" + upgrade
+ .getUpgradableVersionRange()[1] + " to " +
upgrade.getUpgradedVersion());
+
+ txn.start();
+ Connection conn;
+ try {
+ conn = txn.getConnection();
+ } catch (SQLException e) {
+ s_logger.error("Unable to cleanup the database", e);
+ throw new CloudRuntimeException("Unable to cleanup the
database", e);
}
- }
- executeViewScripts();
- updateSystemVmTemplates(upgrades);
+ InputStream[] scripts = upgrade.getCleanupScripts();
+ if (scripts != null) {
+ for (InputStream script : scripts) {
+ runScript(conn, script);
+ s_logger.debug("Cleanup script " +
upgrade.getClass().getSimpleName() + " is executed successfully");
+ }
+ }
+ txn.commit();
+
+ txn.start();
+ version.setStep(Step.Complete);
+ version.setUpdated(new Date());
+ _dao.update(version.getId(), version);
+ txn.commit();
+ s_logger.debug("Upgrade completed for version " +
version.getVersion());
+ } finally {
+ txn.close();
+ }
}
protected void executeViewScripts() {
diff --git
a/engine/schema/src/main/resources/META-INF/db/procedures/cloud.add_guest_os_and_hypervisor_mapping.sql
b/engine/schema/src/main/resources/META-INF/db/procedures/cloud.add_guest_os_and_hypervisor_mapping.sql
new file mode 100644
index 00000000000..efe56bccf2d
--- /dev/null
+++
b/engine/schema/src/main/resources/META-INF/db/procedures/cloud.add_guest_os_and_hypervisor_mapping.sql
@@ -0,0 +1,49 @@
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements. See the NOTICE file
+-- distributed with this work for additional information
+-- regarding copyright ownership. The ASF licenses this file
+-- to you 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.
+
+-- PR#4699 Drop the procedure `ADD_GUEST_OS_AND_HYPERVISOR_MAPPING` if it
already exist.
+DROP PROCEDURE IF EXISTS `cloud`.`ADD_GUEST_OS_AND_HYPERVISOR_MAPPING`;
+
+-- PR#4699 Create the procedure `ADD_GUEST_OS_AND_HYPERVISOR_MAPPING` to add
guest_os and guest_os_hypervisor mapping.
+CREATE PROCEDURE `cloud`.`ADD_GUEST_OS_AND_HYPERVISOR_MAPPING` (
+ IN guest_os_category_id bigint(20) unsigned,
+ IN guest_os_display_name VARCHAR(255),
+ IN guest_os_hypervisor_hypervisor_type VARCHAR(32),
+ IN guest_os_hypervisor_hypervisor_version VARCHAR(32),
+ IN guest_os_hypervisor_guest_os_name VARCHAR(255)
+ )
+BEGIN
+INSERT INTO cloud.guest_os (uuid, category_id, display_name, created)
+SELECT UUID(), guest_os_category_id, guest_os_display_name, now()
+FROM DUAL
+WHERE not exists( SELECT 1
+ FROM cloud.guest_os
+ WHERE cloud.guest_os.category_id = guest_os_category_id
+ AND cloud.guest_os.display_name =
guest_os_display_name)
+
+; INSERT INTO cloud.guest_os_hypervisor (uuid, hypervisor_type,
hypervisor_version, guest_os_name, guest_os_id, created)
+ SELECT UUID(), guest_os_hypervisor_hypervisor_type,
guest_os_hypervisor_hypervisor_version, guest_os_hypervisor_guest_os_name,
guest_os.id, now()
+ FROM cloud.guest_os
+ WHERE guest_os.category_id = guest_os_category_id
+ AND guest_os.display_name = guest_os_display_name
+ AND NOT EXISTS (SELECT 1
+ FROM cloud.guest_os_hypervisor as hypervisor
+ WHERE hypervisor_type =
guest_os_hypervisor_hypervisor_type
+ AND hypervisor_version =
guest_os_hypervisor_hypervisor_version
+ AND hypervisor.guest_os_id = guest_os.id
+ AND hypervisor.guest_os_name =
guest_os_hypervisor_guest_os_name)
+;END;
diff --git
a/engine/schema/src/main/resources/META-INF/db/procedures/cloud.idempotent_add_column.sql
b/engine/schema/src/main/resources/META-INF/db/procedures/cloud.idempotent_add_column.sql
new file mode 100644
index 00000000000..7872f60b2db
--- /dev/null
+++
b/engine/schema/src/main/resources/META-INF/db/procedures/cloud.idempotent_add_column.sql
@@ -0,0 +1,27 @@
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements. See the NOTICE file
+-- distributed with this work for additional information
+-- regarding copyright ownership. The ASF licenses this file
+-- to you 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.
+
+-- in cloud
+DROP PROCEDURE IF EXISTS `cloud`.`IDEMPOTENT_ADD_COLUMN`;
+CREATE PROCEDURE `cloud`.`IDEMPOTENT_ADD_COLUMN` (
+ IN in_table_name VARCHAR(200),
+ IN in_column_name VARCHAR(200),
+ IN in_column_definition VARCHAR(1000)
+)
+BEGIN
+
+ DECLARE CONTINUE HANDLER FOR 1060 BEGIN END; SET @ddl = CONCAT('ALTER
TABLE ', in_table_name); SET @ddl = CONCAT(@ddl, ' ', 'ADD COLUMN') ; SET @ddl
= CONCAT(@ddl, ' ', in_column_name); SET @ddl = CONCAT(@ddl, ' ',
in_column_definition); PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE
stmt; END;
diff --git
a/engine/schema/src/main/resources/META-INF/db/procedures/cloud.idempotent_add_key.sql
b/engine/schema/src/main/resources/META-INF/db/procedures/cloud.idempotent_add_key.sql
new file mode 100644
index 00000000000..8083080088e
--- /dev/null
+++
b/engine/schema/src/main/resources/META-INF/db/procedures/cloud.idempotent_add_key.sql
@@ -0,0 +1,27 @@
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements. See the NOTICE file
+-- distributed with this work for additional information
+-- regarding copyright ownership. The ASF licenses this file
+-- to you 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.
+
+DROP PROCEDURE IF EXISTS `cloud`.`IDEMPOTENT_ADD_KEY`;
+
+CREATE PROCEDURE `cloud`.`IDEMPOTENT_ADD_KEY` (
+ IN in_index_name VARCHAR(200)
+ , IN in_table_name VARCHAR(200)
+ , IN in_key_definition VARCHAR(1000)
+)
+BEGIN
+
+ DECLARE CONTINUE HANDLER FOR 1061 BEGIN END; SET @ddl = CONCAT('ALTER
TABLE ', in_table_name); SET @ddl = CONCAT(@ddl, ' ', ' ADD KEY ') ; SET @ddl =
CONCAT(@ddl, ' ', in_index_name); SET @ddl = CONCAT(@ddl, ' ',
in_key_definition); PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE
stmt; END;
diff --git
a/engine/schema/src/main/resources/META-INF/db/procedures/cloud.idempotent_add_unique_index.sql
b/engine/schema/src/main/resources/META-INF/db/procedures/cloud.idempotent_add_unique_index.sql
new file mode 100644
index 00000000000..22f490ad0fa
--- /dev/null
+++
b/engine/schema/src/main/resources/META-INF/db/procedures/cloud.idempotent_add_unique_index.sql
@@ -0,0 +1,26 @@
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements. See the NOTICE file
+-- distributed with this work for additional information
+-- regarding copyright ownership. The ASF licenses this file
+-- to you 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.
+
+-- Idempotent ADD UNIQUE INDEX
+DROP PROCEDURE IF EXISTS `cloud_usage`.`IDEMPOTENT_ADD_UNIQUE_INDEX`;
+CREATE PROCEDURE `cloud_usage`.`IDEMPOTENT_ADD_UNIQUE_INDEX` (
+ IN in_table_name VARCHAR(200)
+, IN in_index_name VARCHAR(200)
+, IN in_index_definition VARCHAR(1000)
+)
+BEGIN
+ DECLARE CONTINUE HANDLER FOR 1061 BEGIN END; SET @ddl = CONCAT('ALTER
TABLE ', in_table_name); SET @ddl = CONCAT(@ddl, ' ', 'ADD UNIQUE INDEX ',
in_index_name); SET @ddl = CONCAT(@ddl, ' ', in_index_definition); PREPARE stmt
FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt; END;
diff --git
a/engine/schema/src/main/resources/META-INF/db/procedures/cloud.idempotent_add_unique_key.sql
b/engine/schema/src/main/resources/META-INF/db/procedures/cloud.idempotent_add_unique_key.sql
new file mode 100644
index 00000000000..5d4cbf6c770
--- /dev/null
+++
b/engine/schema/src/main/resources/META-INF/db/procedures/cloud.idempotent_add_unique_key.sql
@@ -0,0 +1,26 @@
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements. See the NOTICE file
+-- distributed with this work for additional information
+-- regarding copyright ownership. The ASF licenses this file
+-- to you 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.
+
+-- Idempotent ADD UNIQUE KEY
+DROP PROCEDURE IF EXISTS `cloud`.`IDEMPOTENT_ADD_UNIQUE_KEY`;
+CREATE PROCEDURE `cloud`.`IDEMPOTENT_ADD_UNIQUE_KEY` (
+ IN in_table_name VARCHAR(200)
+, IN in_key_name VARCHAR(200)
+, IN in_key_definition VARCHAR(1000)
+)
+BEGIN
+ DECLARE CONTINUE HANDLER FOR 1061 BEGIN END; SET @ddl = CONCAT('ALTER
TABLE ', in_table_name); SET @ddl = CONCAT(@ddl, ' ', 'ADD UNIQUE KEY ',
in_key_name); SET @ddl = CONCAT(@ddl, ' ', in_key_definition); PREPARE stmt
FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt; END;
diff --git
a/engine/schema/src/main/resources/META-INF/db/procedures/cloud.idempotent_change_column.sql
b/engine/schema/src/main/resources/META-INF/db/procedures/cloud.idempotent_change_column.sql
new file mode 100644
index 00000000000..d63c92e6b81
--- /dev/null
+++
b/engine/schema/src/main/resources/META-INF/db/procedures/cloud.idempotent_change_column.sql
@@ -0,0 +1,27 @@
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements. See the NOTICE file
+-- distributed with this work for additional information
+-- regarding copyright ownership. The ASF licenses this file
+-- to you 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.
+
+-- in usage Idempotent CHANGE COLUMN
+DROP PROCEDURE IF EXISTS `cloud`.`IDEMPOTENT_CHANGE_COLUMN`;
+CREATE PROCEDURE `cloud`.`IDEMPOTENT_CHANGE_COLUMN` (
+ IN in_table_name VARCHAR(200)
+ , IN in_column_name VARCHAR(200)
+ , IN in_column_new_name VARCHAR(200)
+ , IN in_column_new_definition VARCHAR(1000)
+)
+BEGIN
+ DECLARE CONTINUE HANDLER FOR 1054 BEGIN END; SET @ddl = CONCAT('ALTER
TABLE ', in_table_name); SET @ddl = CONCAT(@ddl, ' ', 'CHANGE COLUMN') ; SET
@ddl = CONCAT(@ddl, ' ', in_column_name); SET @ddl = CONCAT(@ddl, ' ',
in_column_new_name); SET @ddl = CONCAT(@ddl, ' ', in_column_new_definition);
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt; END;
diff --git
a/engine/schema/src/main/resources/META-INF/db/procedures/cloud.idempotent_create_unique_index.sql
b/engine/schema/src/main/resources/META-INF/db/procedures/cloud.idempotent_create_unique_index.sql
new file mode 100644
index 00000000000..167b17412f1
--- /dev/null
+++
b/engine/schema/src/main/resources/META-INF/db/procedures/cloud.idempotent_create_unique_index.sql
@@ -0,0 +1,27 @@
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements. See the NOTICE file
+-- distributed with this work for additional information
+-- regarding copyright ownership. The ASF licenses this file
+-- to you 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.
+
+DROP PROCEDURE IF EXISTS `cloud`.`IDEMPOTENT_CREATE_UNIQUE_INDEX`;
+
+CREATE PROCEDURE `cloud`.`IDEMPOTENT_CREATE_UNIQUE_INDEX` (
+ IN in_index_name VARCHAR(200)
+ , IN in_table_name VARCHAR(200)
+ , IN in_index_definition VARCHAR(1000)
+)
+BEGIN
+
+ DECLARE CONTINUE HANDLER FOR 1061 BEGIN END; SET @ddl = CONCAT('CREATE
UNIQUE INDEX ', in_index_name); SET @ddl = CONCAT(@ddl, ' ', ' ON ') ; SET @ddl
= CONCAT(@ddl, ' ', in_table_name); SET @ddl = CONCAT(@ddl, ' ',
in_index_definition); PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE
stmt; END;
diff --git
a/engine/schema/src/main/resources/META-INF/db/procedures/cloud.idempotent_drop_foreign_key.sql
b/engine/schema/src/main/resources/META-INF/db/procedures/cloud.idempotent_drop_foreign_key.sql
new file mode 100644
index 00000000000..0ba0a411ca9
--- /dev/null
+++
b/engine/schema/src/main/resources/META-INF/db/procedures/cloud.idempotent_drop_foreign_key.sql
@@ -0,0 +1,25 @@
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements. See the NOTICE file
+-- distributed with this work for additional information
+-- regarding copyright ownership. The ASF licenses this file
+-- to you 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.
+
+DROP PROCEDURE IF EXISTS `cloud`.`IDEMPOTENT_DROP_FOREIGN_KEY`;
+
+CREATE PROCEDURE `cloud`.`IDEMPOTENT_DROP_FOREIGN_KEY` (
+ IN in_table_name VARCHAR(200),
+ IN in_foreign_key_name VARCHAR(200)
+)
+BEGIN
+ DECLARE CONTINUE HANDLER FOR 1091, 1025 BEGIN END; SET @ddl =
CONCAT('ALTER TABLE ', in_table_name); SET @ddl = CONCAT(@ddl, ' ', ' DROP
FOREIGN KEY '); SET @ddl = CONCAT(@ddl, ' ', in_foreign_key_name); PREPARE stmt
FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt; END;
diff --git
a/engine/schema/src/main/resources/META-INF/db/procedures/cloud.idempotent_drop_index.sql
b/engine/schema/src/main/resources/META-INF/db/procedures/cloud.idempotent_drop_index.sql
new file mode 100644
index 00000000000..1e1afd26597
--- /dev/null
+++
b/engine/schema/src/main/resources/META-INF/db/procedures/cloud.idempotent_drop_index.sql
@@ -0,0 +1,27 @@
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements. See the NOTICE file
+-- distributed with this work for additional information
+-- regarding copyright ownership. The ASF licenses this file
+-- to you 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.
+
+-- in cloud
+DROP PROCEDURE IF EXISTS `cloud`.`IDEMPOTENT_DROP_INDEX`;
+
+CREATE PROCEDURE `cloud`.`IDEMPOTENT_DROP_INDEX` (
+ IN in_index_name VARCHAR(200)
+ , IN in_table_name VARCHAR(200)
+)
+BEGIN
+
+ DECLARE CONTINUE HANDLER FOR 1091 BEGIN END; SET @ddl = CONCAT('DROP INDEX
', in_index_name); SET @ddl = CONCAT(@ddl, ' ', ' ON ') ; SET @ddl =
CONCAT(@ddl, ' ', in_table_name); PREPARE stmt FROM @ddl; EXECUTE stmt;
DEALLOCATE PREPARE stmt; END;
diff --git
a/engine/schema/src/main/resources/META-INF/db/procedures/cloud.idempotent_insert_guestos_hypervisor_mapping.sql
b/engine/schema/src/main/resources/META-INF/db/procedures/cloud.idempotent_insert_guestos_hypervisor_mapping.sql
new file mode 100644
index 00000000000..7af96f56145
--- /dev/null
+++
b/engine/schema/src/main/resources/META-INF/db/procedures/cloud.idempotent_insert_guestos_hypervisor_mapping.sql
@@ -0,0 +1,48 @@
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements. See the NOTICE file
+-- distributed with this work for additional information
+-- regarding copyright ownership. The ASF licenses this file
+-- to you 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.
+
+DROP PROCEDURE IF EXISTS
`cloud`.`IDEMPOTENT_INSERT_GUESTOS_HYPERVISOR_MAPPING`;
+
+CREATE PROCEDURE `cloud`.`IDEMPOTENT_INSERT_GUESTOS_HYPERVISOR_MAPPING`(
+ IN in_hypervisor_type VARCHAR(32),
+ IN in_hypervisor_version VARCHAR(32),
+ IN in_guest_os_name VARCHAR(255),
+ IN in_guest_os_id BIGINT(20) UNSIGNED,
+ IN is_user_defined int(1) UNSIGNED)
+BEGIN
+ IF NOT EXISTS ((SELECT * FROM `cloud`.`guest_os_hypervisor` WHERE
+ hypervisor_type=in_hypervisor_type AND
+ hypervisor_version=in_hypervisor_version AND
+ guest_os_id = in_guest_os_id))
+ THEN
+ INSERT INTO `cloud`.`guest_os_hypervisor` (
+ uuid,
+ hypervisor_type,
+ hypervisor_version,
+ guest_os_name,
+ guest_os_id,
+ created,
+ is_user_defined)
+ VALUES (
+ UUID(),
+ in_hypervisor_type,
+ in_hypervisor_version,
+ in_guest_os_name,
+ in_guest_os_id,
+ utc_timestamp(),
+ is_user_defined
+ ); END IF; END;;
diff --git
a/engine/schema/src/main/resources/META-INF/db/procedures/usage.idempotent_add_column.sql
b/engine/schema/src/main/resources/META-INF/db/procedures/usage.idempotent_add_column.sql
new file mode 100644
index 00000000000..e257b64bd8a
--- /dev/null
+++
b/engine/schema/src/main/resources/META-INF/db/procedures/usage.idempotent_add_column.sql
@@ -0,0 +1,26 @@
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements. See the NOTICE file
+-- distributed with this work for additional information
+-- regarding copyright ownership. The ASF licenses this file
+-- to you 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.
+
+-- in usage
+DROP PROCEDURE IF EXISTS `cloud_usage`.`IDEMPOTENT_ADD_COLUMN`;
+CREATE PROCEDURE `cloud_usage`.`IDEMPOTENT_ADD_COLUMN` (
+ IN in_table_name VARCHAR(200)
+, IN in_column_name VARCHAR(200)
+, IN in_column_definition VARCHAR(1000)
+)
+BEGIN
+ DECLARE CONTINUE HANDLER FOR 1060 BEGIN END; SET @ddl = CONCAT('ALTER
TABLE ', in_table_name); SET @ddl = CONCAT(@ddl, ' ', 'ADD COLUMN') ; SET @ddl
= CONCAT(@ddl, ' ', in_column_name); SET @ddl = CONCAT(@ddl, ' ',
in_column_definition); PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE
stmt; END;
diff --git
a/engine/schema/src/main/resources/META-INF/db/procedures/usage.idempotent_change_column.sql
b/engine/schema/src/main/resources/META-INF/db/procedures/usage.idempotent_change_column.sql
new file mode 100644
index 00000000000..a47b6a1fbf9
--- /dev/null
+++
b/engine/schema/src/main/resources/META-INF/db/procedures/usage.idempotent_change_column.sql
@@ -0,0 +1,27 @@
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements. See the NOTICE file
+-- distributed with this work for additional information
+-- regarding copyright ownership. The ASF licenses this file
+-- to you 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.
+
+-- in usage Idempotent CHANGE COLUMN
+DROP PROCEDURE IF EXISTS `cloud_usage`.`IDEMPOTENT_CHANGE_COLUMN`;
+CREATE PROCEDURE `cloud_usage`.`IDEMPOTENT_CHANGE_COLUMN` (
+ IN in_table_name VARCHAR(200)
+ , IN in_old_column_name VARCHAR(200)
+ , IN in_new_column_name VARCHAR(200)
+ , IN in_column_definition VARCHAR(1000)
+)
+BEGIN
+ DECLARE CONTINUE HANDLER FOR 1060 BEGIN END; SET @ddl = CONCAT('ALTER
TABLE ', in_table_name); SET @ddl = CONCAT(@ddl, ' ', ' CHANGE COLUMN') ; SET
@ddl = CONCAT(@ddl, ' ', in_old_column_name); SET @ddl = CONCAT(@ddl, ' ',
in_new_column_name); SET @ddl = CONCAT(@ddl, ' ', in_column_definition);
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt; END;
diff --git
a/engine/schema/src/main/resources/META-INF/db/procedures/usage.idempotent_drop_index.sql
b/engine/schema/src/main/resources/META-INF/db/procedures/usage.idempotent_drop_index.sql
new file mode 100644
index 00000000000..f824ebad98e
--- /dev/null
+++
b/engine/schema/src/main/resources/META-INF/db/procedures/usage.idempotent_drop_index.sql
@@ -0,0 +1,25 @@
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements. See the NOTICE file
+-- distributed with this work for additional information
+-- regarding copyright ownership. The ASF licenses this file
+-- to you 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.
+
+-- in usage
+DROP PROCEDURE IF EXISTS `cloud_usage`.`IDEMPOTENT_DROP_INDEX`;
+CREATE PROCEDURE `cloud_usage`.`IDEMPOTENT_DROP_INDEX` (
+ IN in_index_name VARCHAR(200)
+, IN in_table_name VARCHAR(200)
+)
+BEGIN
+ DECLARE CONTINUE HANDLER FOR 1091 BEGIN END; SET @ddl = CONCAT('DROP INDEX
', in_index_name); SET @ddl = CONCAT(@ddl, ' ', ' ON ') ; SET @ddl =
CONCAT(@ddl, ' ', in_table_name); PREPARE stmt FROM @ddl; EXECUTE stmt;
DEALLOCATE PREPARE stmt; END;
diff --git
a/engine/schema/src/main/resources/META-INF/db/schema-41000to41100.sql
b/engine/schema/src/main/resources/META-INF/db/schema-41000to41100.sql
index 6148ee11bcf..0b1779d7e2b 100644
--- a/engine/schema/src/main/resources/META-INF/db/schema-41000to41100.sql
+++ b/engine/schema/src/main/resources/META-INF/db/schema-41000to41100.sql
@@ -19,51 +19,6 @@
-- Schema upgrade from 4.10.0.0 to 4.11.0.0
--;
---;
--- Stored procedure to do idempotent column add;
---;
-DROP PROCEDURE IF EXISTS `cloud`.`IDEMPOTENT_ADD_COLUMN`;
-
-CREATE PROCEDURE `cloud`.`IDEMPOTENT_ADD_COLUMN` (
- IN in_table_name VARCHAR(200)
- , IN in_column_name VARCHAR(200)
- , IN in_column_definition VARCHAR(1000)
-)
-BEGIN
-
- DECLARE CONTINUE HANDLER FOR 1060 BEGIN END; SET @ddl = CONCAT('ALTER
TABLE ', in_table_name); SET @ddl = CONCAT(@ddl, ' ', 'ADD COLUMN') ; SET @ddl
= CONCAT(@ddl, ' ', in_column_name); SET @ddl = CONCAT(@ddl, ' ',
in_column_definition); PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE
stmt; END;
-
-DROP PROCEDURE IF EXISTS `cloud`.`IDEMPOTENT_DROP_FOREIGN_KEY`;
-
-CREATE PROCEDURE `cloud`.`IDEMPOTENT_DROP_FOREIGN_KEY` (
- IN in_table_name VARCHAR(200)
- , IN in_foreign_key_name VARCHAR(200)
-)
-BEGIN
-
- DECLARE CONTINUE HANDLER FOR 1091 BEGIN END; SET @ddl = CONCAT('ALTER
TABLE ', in_table_name); SET @ddl = CONCAT(@ddl, ' ', ' DROP FOREIGN KEY ');
SET @ddl = CONCAT(@ddl, ' ', in_foreign_key_name); PREPARE stmt FROM @ddl;
EXECUTE stmt; DEALLOCATE PREPARE stmt; END;
-
-DROP PROCEDURE IF EXISTS `cloud`.`IDEMPOTENT_DROP_INDEX`;
-
-CREATE PROCEDURE `cloud`.`IDEMPOTENT_DROP_INDEX` (
- IN in_index_name VARCHAR(200)
- , IN in_table_name VARCHAR(200)
-)
-BEGIN
-
- DECLARE CONTINUE HANDLER FOR 1091 BEGIN END; SET @ddl = CONCAT('DROP INDEX
', in_index_name); SET @ddl = CONCAT(@ddl, ' ', ' ON ') ; SET @ddl =
CONCAT(@ddl, ' ', in_table_name); PREPARE stmt FROM @ddl; EXECUTE stmt;
DEALLOCATE PREPARE stmt; END;
-
-DROP PROCEDURE IF EXISTS `cloud`.`IDEMPOTENT_CREATE_UNIQUE_INDEX`;
-
-CREATE PROCEDURE `cloud`.`IDEMPOTENT_CREATE_UNIQUE_INDEX` (
- IN in_index_name VARCHAR(200)
- , IN in_table_name VARCHAR(200)
- , IN in_index_definition VARCHAR(1000)
-)
-BEGIN
-
- DECLARE CONTINUE HANDLER FOR 1061 BEGIN END; SET @ddl = CONCAT('CREATE
UNIQUE INDEX ', in_index_name); SET @ddl = CONCAT(@ddl, ' ', ' ON ') ; SET @ddl
= CONCAT(@ddl, ' ', in_table_name); SET @ddl = CONCAT(@ddl, ' ',
in_index_definition); PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE
stmt; END;
-
-- Add For VPC flag
CALL `cloud`.`IDEMPOTENT_ADD_COLUMN`('cloud.network_offerings','for_vpc',
'INT(1) NOT NULL DEFAULT 0');
diff --git
a/engine/schema/src/main/resources/META-INF/db/schema-41520to41600.sql
b/engine/schema/src/main/resources/META-INF/db/schema-41520to41600.sql
index a414f244b08..2464a8a57ce 100644
--- a/engine/schema/src/main/resources/META-INF/db/schema-41520to41600.sql
+++ b/engine/schema/src/main/resources/META-INF/db/schema-41520to41600.sql
@@ -123,20 +123,6 @@ CREATE VIEW `cloud`.`service_offering_view` AS
GROUP BY
`service_offering`.`id`;
---;
--- Stored procedure to do idempotent column add;
--- This is copied from schema-41000to41100.sql
---;
-DROP PROCEDURE IF EXISTS `cloud`.`IDEMPOTENT_ADD_COLUMN`;
-
-CREATE PROCEDURE `cloud`.`IDEMPOTENT_ADD_COLUMN` (
- IN in_table_name VARCHAR(200),
- IN in_column_name VARCHAR(200),
- IN in_column_definition VARCHAR(1000)
-)
-BEGIN
-
- DECLARE CONTINUE HANDLER FOR 1060 BEGIN END; SET @ddl = CONCAT('ALTER
TABLE ', in_table_name); SET @ddl = CONCAT(@ddl, ' ', 'ADD COLUMN') ; SET @ddl
= CONCAT(@ddl, ' ', in_column_name); SET @ddl = CONCAT(@ddl, ' ',
in_column_definition); PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE
stmt; END;
CALL `cloud`.`IDEMPOTENT_ADD_COLUMN`('cloud.account','created', 'datetime
DEFAULT NULL COMMENT ''date created'' AFTER `state` ');
CALL `cloud`.`IDEMPOTENT_ADD_COLUMN`('cloud.domain','created', 'datetime
DEFAULT NULL COMMENT ''date created'' AFTER `next_child_seq` ');
@@ -730,39 +716,6 @@ ALTER TABLE `cloud`.`annotations` ADD COLUMN `admins_only`
tinyint(1) unsigned N
-- Add uuid for ssh keypairs
ALTER TABLE `cloud`.`ssh_keypairs` ADD COLUMN `uuid` varchar(40) AFTER `id`;
--- PR#4699 Drop the procedure `ADD_GUEST_OS_AND_HYPERVISOR_MAPPING` if it
already exist.
-DROP PROCEDURE IF EXISTS `cloud`.`ADD_GUEST_OS_AND_HYPERVISOR_MAPPING`;
-
--- PR#4699 Create the procedure `ADD_GUEST_OS_AND_HYPERVISOR_MAPPING` to add
guest_os and guest_os_hypervisor mapping.
-CREATE PROCEDURE `cloud`.`ADD_GUEST_OS_AND_HYPERVISOR_MAPPING` (
- IN guest_os_category_id bigint(20) unsigned,
- IN guest_os_display_name VARCHAR(255),
- IN guest_os_hypervisor_hypervisor_type VARCHAR(32),
- IN guest_os_hypervisor_hypervisor_version VARCHAR(32),
- IN guest_os_hypervisor_guest_os_name VARCHAR(255)
-)
-BEGIN
- INSERT INTO cloud.guest_os (uuid, category_id, display_name, created)
- SELECT UUID(), guest_os_category_id, guest_os_display_name, now()
- FROM DUAL
- WHERE not exists( SELECT 1
- FROM cloud.guest_os
- WHERE cloud.guest_os.category_id =
guest_os_category_id
- AND cloud.guest_os.display_name =
guest_os_display_name)
-
-; INSERT INTO cloud.guest_os_hypervisor (uuid, hypervisor_type,
hypervisor_version, guest_os_name, guest_os_id, created)
- SELECT UUID(), guest_os_hypervisor_hypervisor_type,
guest_os_hypervisor_hypervisor_version, guest_os_hypervisor_guest_os_name,
guest_os.id, now()
- FROM cloud.guest_os
- WHERE guest_os.category_id = guest_os_category_id
- AND guest_os.display_name = guest_os_display_name
- AND NOT EXISTS (SELECT 1
- FROM cloud.guest_os_hypervisor as hypervisor
- WHERE hypervisor_type =
guest_os_hypervisor_hypervisor_type
- AND hypervisor_version =
guest_os_hypervisor_hypervisor_version
- AND hypervisor.guest_os_id = guest_os.id
- AND hypervisor.guest_os_name =
guest_os_hypervisor_guest_os_name)
-;END;
-
-- PR#4699 Call procedure `ADD_GUEST_OS_AND_HYPERVISOR_MAPPING` to add new
data to guest_os and guest_os_hypervisor.
CALL ADD_GUEST_OS_AND_HYPERVISOR_MAPPING (10, 'Ubuntu 20.04 LTS', 'KVM',
'default', 'Ubuntu 20.04 LTS');
CALL ADD_GUEST_OS_AND_HYPERVISOR_MAPPING (10, 'Ubuntu 21.04', 'KVM',
'default', 'Ubuntu 21.04');
diff --git
a/engine/schema/src/main/resources/META-INF/db/schema-41600to41610.sql
b/engine/schema/src/main/resources/META-INF/db/schema-41600to41610.sql
index d53e2181cef..2a2ae668dae 100644
--- a/engine/schema/src/main/resources/META-INF/db/schema-41600to41610.sql
+++ b/engine/schema/src/main/resources/META-INF/db/schema-41600to41610.sql
@@ -21,60 +21,6 @@
ALTER TABLE `cloud`.`vm_work_job` ADD COLUMN `secondary_object` char(100)
COMMENT 'any additional item that must be checked during queueing' AFTER
`vm_instance_id`;
--- Stored procedures to handle cloud and cloud_schema changes
-
--- Idempotent ADD COLUMN
-DROP PROCEDURE IF EXISTS `cloud`.`IDEMPOTENT_ADD_COLUMN`;
-CREATE PROCEDURE `cloud`.`IDEMPOTENT_ADD_COLUMN` (
- IN in_table_name VARCHAR(200)
-, IN in_column_name VARCHAR(200)
-, IN in_column_definition VARCHAR(1000)
-)
-BEGIN
- DECLARE CONTINUE HANDLER FOR 1060 BEGIN END; SET @ddl = CONCAT('ALTER
TABLE ', in_table_name); SET @ddl = CONCAT(@ddl, ' ', 'ADD COLUMN') ; SET @ddl
= CONCAT(@ddl, ' ', in_column_name); SET @ddl = CONCAT(@ddl, ' ',
in_column_definition); PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE
stmt; END;
-
--- Idempotent ADD COLUMN
-DROP PROCEDURE IF EXISTS `cloud_usage`.`IDEMPOTENT_ADD_COLUMN`;
-CREATE PROCEDURE `cloud_usage`.`IDEMPOTENT_ADD_COLUMN` (
- IN in_table_name VARCHAR(200)
-, IN in_column_name VARCHAR(200)
-, IN in_column_definition VARCHAR(1000)
-)
-BEGIN
- DECLARE CONTINUE HANDLER FOR 1060 BEGIN END; SET @ddl = CONCAT('ALTER
TABLE ', in_table_name); SET @ddl = CONCAT(@ddl, ' ', 'ADD COLUMN') ; SET @ddl
= CONCAT(@ddl, ' ', in_column_name); SET @ddl = CONCAT(@ddl, ' ',
in_column_definition); PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE
stmt; END;
-
--- Idempotent DROP INDEX
-DROP PROCEDURE IF EXISTS `cloud_usage`.`IDEMPOTENT_DROP_INDEX`;
-CREATE PROCEDURE `cloud_usage`.`IDEMPOTENT_DROP_INDEX` (
- IN in_index_name VARCHAR(200)
-, IN in_table_name VARCHAR(200)
-)
-BEGIN
- DECLARE CONTINUE HANDLER FOR 1091 BEGIN END; SET @ddl = CONCAT('DROP INDEX
', in_index_name); SET @ddl = CONCAT(@ddl, ' ', ' ON ') ; SET @ddl =
CONCAT(@ddl, ' ', in_table_name); PREPARE stmt FROM @ddl; EXECUTE stmt;
DEALLOCATE PREPARE stmt; END;
-
--- Idempotent ADD UNIQUE INDEX
-DROP PROCEDURE IF EXISTS `cloud_usage`.`IDEMPOTENT_ADD_UNIQUE_INDEX`;
-CREATE PROCEDURE `cloud_usage`.`IDEMPOTENT_ADD_UNIQUE_INDEX` (
- IN in_table_name VARCHAR(200)
-, IN in_index_name VARCHAR(200)
-, IN in_index_definition VARCHAR(1000)
-)
-BEGIN
- DECLARE CONTINUE HANDLER FOR 1061 BEGIN END; SET @ddl = CONCAT('ALTER
TABLE ', in_table_name); SET @ddl = CONCAT(@ddl, ' ', 'ADD UNIQUE INDEX ',
in_index_name); SET @ddl = CONCAT(@ddl, ' ', in_index_definition); PREPARE stmt
FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt; END;
-
--- Idempotent CHANGE COLUMN
-DROP PROCEDURE IF EXISTS `cloud_usage`.`IDEMPOTENT_CHANGE_COLUMN`;
-CREATE PROCEDURE `cloud_usage`.`IDEMPOTENT_CHANGE_COLUMN` (
- IN in_table_name VARCHAR(200)
-, IN in_old_column_name VARCHAR(200)
-, IN in_new_column_name VARCHAR(200)
-, IN in_column_definition VARCHAR(1000)
-)
-BEGIN
- DECLARE CONTINUE HANDLER FOR 1060 BEGIN END; SET @ddl = CONCAT('ALTER
TABLE ', in_table_name); SET @ddl = CONCAT(@ddl, ' ', ' CHANGE COLUMN') ; SET
@ddl = CONCAT(@ddl, ' ', in_old_column_name); SET @ddl = CONCAT(@ddl, ' ',
in_new_column_name); SET @ddl = CONCAT(@ddl, ' ', in_column_definition);
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt; END;
-
--- Invoke stored procedures to add primary keys on missing tables
-
-- Add PK to cloud.op_user_stats_log
CALL `cloud`.`IDEMPOTENT_ADD_COLUMN`('cloud.op_user_stats_log', 'id',
'BIGINT(20) NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (`id`)');
diff --git
a/engine/schema/src/main/resources/META-INF/db/schema-41610to41700.sql
b/engine/schema/src/main/resources/META-INF/db/schema-41610to41700.sql
index 8417ec29640..ca07c25aaa5 100644
--- a/engine/schema/src/main/resources/META-INF/db/schema-41610to41700.sql
+++ b/engine/schema/src/main/resources/META-INF/db/schema-41610to41700.sql
@@ -219,21 +219,6 @@ CREATE VIEW `cloud`.`service_offering_view` AS
`service_offering`.`id`;
---;
--- Stored procedure to do idempotent column add;
--- This is copied from schema-41000to41100.sql
---;
-DROP PROCEDURE IF EXISTS `cloud`.`IDEMPOTENT_ADD_COLUMN`;
-
-CREATE PROCEDURE `cloud`.`IDEMPOTENT_ADD_COLUMN` (
- IN in_table_name VARCHAR(200),
- IN in_column_name VARCHAR(200),
- IN in_column_definition VARCHAR(1000)
-)
-BEGIN
-
- DECLARE CONTINUE HANDLER FOR 1060 BEGIN END; SET @ddl = CONCAT('ALTER
TABLE ', in_table_name); SET @ddl = CONCAT(@ddl, ' ', 'ADD COLUMN') ; SET @ddl
= CONCAT(@ddl, ' ', in_column_name); SET @ddl = CONCAT(@ddl, ' ',
in_column_definition); PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE
stmt; END;
-
CALL `cloud`.`IDEMPOTENT_ADD_COLUMN`('cloud.volumes','external_uuid',
'VARCHAR(40) DEFAULT null ');
DROP VIEW IF EXISTS `cloud`.`volume_view`;
@@ -937,35 +922,5 @@ INSERT IGNORE INTO `cloud`.`hypervisor_capabilities`(uuid,
hypervisor_type, hype
-- Copy XenServer 8.2.0 hypervisor guest OS mappings to XenServer 8.2.1
INSERT IGNORE INTO `cloud`.`guest_os_hypervisor` (uuid,hypervisor_type,
hypervisor_version, guest_os_name, guest_os_id, created, is_user_defined)
SELECT UUID(),'Xenserver', '8.2.1', guest_os_name, guest_os_id,
utc_timestamp(), 0 FROM `cloud`.`guest_os_hypervisor` WHERE
hypervisor_type='Xenserver' AND hypervisor_version='8.2.0';
-DROP PROCEDURE IF EXISTS `cloud`.`ADD_GUEST_OS_AND_HYPERVISOR_MAPPING`;
-CREATE PROCEDURE `cloud`.`ADD_GUEST_OS_AND_HYPERVISOR_MAPPING` (
- IN guest_os_category_id bigint(20) unsigned,
- IN guest_os_display_name VARCHAR(255),
- IN guest_os_hypervisor_hypervisor_type VARCHAR(32),
- IN guest_os_hypervisor_hypervisor_version VARCHAR(32),
- IN guest_os_hypervisor_guest_os_name VARCHAR(255)
- )
-BEGIN
-INSERT INTO cloud.guest_os (uuid, category_id, display_name, created)
-SELECT UUID(), guest_os_category_id, guest_os_display_name, now()
-FROM DUAL
-WHERE not exists( SELECT 1
- FROM cloud.guest_os
- WHERE cloud.guest_os.category_id = guest_os_category_id
- AND cloud.guest_os.display_name =
guest_os_display_name)
-
-; INSERT INTO cloud.guest_os_hypervisor (uuid, hypervisor_type,
hypervisor_version, guest_os_name, guest_os_id, created)
- SELECT UUID(), guest_os_hypervisor_hypervisor_type,
guest_os_hypervisor_hypervisor_version, guest_os_hypervisor_guest_os_name,
guest_os.id, now()
- FROM cloud.guest_os
- WHERE guest_os.category_id = guest_os_category_id
- AND guest_os.display_name = guest_os_display_name
- AND NOT EXISTS (SELECT 1
- FROM cloud.guest_os_hypervisor as hypervisor
- WHERE hypervisor_type =
guest_os_hypervisor_hypervisor_type
- AND hypervisor_version =
guest_os_hypervisor_hypervisor_version
- AND hypervisor.guest_os_id = guest_os.id
- AND hypervisor.guest_os_name =
guest_os_hypervisor_guest_os_name)
-;END;
-
CALL ADD_GUEST_OS_AND_HYPERVISOR_MAPPING (2, 'Debian GNU/Linux 11 (64-bit)',
'XenServer', '8.2.1', 'Debian Bullseye 11');
CALL ADD_GUEST_OS_AND_HYPERVISOR_MAPPING (2, 'Debian GNU/Linux 11 (32-bit)',
'XenServer', '8.2.1', 'Debian Bullseye 11');
diff --git
a/engine/schema/src/main/resources/META-INF/db/schema-41720to41800.sql
b/engine/schema/src/main/resources/META-INF/db/schema-41720to41800.sql
index c51d5a43045..9f38de11819 100644
--- a/engine/schema/src/main/resources/META-INF/db/schema-41720to41800.sql
+++ b/engine/schema/src/main/resources/META-INF/db/schema-41720to41800.sql
@@ -214,16 +214,6 @@ CREATE VIEW `cloud`.`domain_router_view` AS
and async_job.instance_type = 'DomainRouter'
and async_job.job_status = 0;
--- Idempotent ADD COLUMN
-DROP PROCEDURE IF EXISTS `cloud`.`IDEMPOTENT_ADD_COLUMN`;
-CREATE PROCEDURE `cloud`.`IDEMPOTENT_ADD_COLUMN` (
- IN in_table_name VARCHAR(200)
-, IN in_column_name VARCHAR(200)
-, IN in_column_definition VARCHAR(1000)
-)
-BEGIN
- DECLARE CONTINUE HANDLER FOR 1060 BEGIN END; SET @ddl = CONCAT('ALTER
TABLE ', in_table_name); SET @ddl = CONCAT(@ddl, ' ', 'ADD COLUMN') ; SET @ddl
= CONCAT(@ddl, ' ', in_column_name); SET @ddl = CONCAT(@ddl, ' ',
in_column_definition); PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE
stmt; END;
-
-- Add passphrase table
CREATE TABLE IF NOT EXISTS `cloud`.`passphrase` (
`id` bigint unsigned NOT NULL auto_increment,
@@ -433,45 +423,6 @@ WHERE roles.role_type != 'Admin' AND roles.is_default = 1
AND role_perm.rule = '
-- VM autoscaling
--- Idempotent ADD COLUMN
-DROP PROCEDURE IF EXISTS `cloud`.`IDEMPOTENT_ADD_COLUMN`;
-CREATE PROCEDURE `cloud`.`IDEMPOTENT_ADD_COLUMN` (
- IN in_table_name VARCHAR(200)
-, IN in_column_name VARCHAR(200)
-, IN in_column_definition VARCHAR(1000)
-)
-BEGIN
- DECLARE CONTINUE HANDLER FOR 1060 BEGIN END; SET @ddl = CONCAT('ALTER
TABLE ', in_table_name); SET @ddl = CONCAT(@ddl, ' ', 'ADD COLUMN') ; SET @ddl
= CONCAT(@ddl, ' ', in_column_name); SET @ddl = CONCAT(@ddl, ' ',
in_column_definition); PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE
stmt; END;
-
--- Idempotent RENAME COLUMN
-DROP PROCEDURE IF EXISTS `cloud`.`IDEMPOTENT_CHANGE_COLUMN`;
-CREATE PROCEDURE `cloud`.`IDEMPOTENT_CHANGE_COLUMN` (
- IN in_table_name VARCHAR(200)
-, IN in_column_name VARCHAR(200)
-, IN in_column_new_name VARCHAR(200)
-, IN in_column_new_definition VARCHAR(1000)
-)
-BEGIN
- DECLARE CONTINUE HANDLER FOR 1054 BEGIN END; SET @ddl = CONCAT('ALTER
TABLE ', in_table_name); SET @ddl = CONCAT(@ddl, ' ', 'CHANGE COLUMN') ; SET
@ddl = CONCAT(@ddl, ' ', in_column_name); SET @ddl = CONCAT(@ddl, ' ',
in_column_new_name); SET @ddl = CONCAT(@ddl, ' ', in_column_new_definition);
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt; END;
-
--- Idempotent ADD UNIQUE KEY
-DROP PROCEDURE IF EXISTS `cloud`.`IDEMPOTENT_ADD_UNIQUE_KEY`;
-CREATE PROCEDURE `cloud`.`IDEMPOTENT_ADD_UNIQUE_KEY` (
- IN in_table_name VARCHAR(200)
-, IN in_key_name VARCHAR(200)
-, IN in_key_definition VARCHAR(1000)
-)
-BEGIN
- DECLARE CONTINUE HANDLER FOR 1061 BEGIN END; SET @ddl = CONCAT('ALTER
TABLE ', in_table_name); SET @ddl = CONCAT(@ddl, ' ', 'ADD UNIQUE KEY ',
in_key_name); SET @ddl = CONCAT(@ddl, ' ', in_key_definition); PREPARE stmt
FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt; END;
-
--- Idempotent DROP FOREIGN KEY
-DROP PROCEDURE IF EXISTS `cloud`.`IDEMPOTENT_DROP_FOREIGN_KEY`;
-CREATE PROCEDURE `cloud`.`IDEMPOTENT_DROP_FOREIGN_KEY` (
- IN in_table_name VARCHAR(200)
-, IN in_foreign_key_name VARCHAR(200)
-)
-BEGIN
- DECLARE CONTINUE HANDLER FOR 1091, 1025 BEGIN END; SET @ddl =
CONCAT('ALTER TABLE ', in_table_name); SET @ddl = CONCAT(@ddl, ' ', ' DROP
FOREIGN KEY '); SET @ddl = CONCAT(@ddl, ' ', in_foreign_key_name); PREPARE stmt
FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt; END;
-- Add column 'supports_vm_autoscaling' to 'network_offerings' table
CALL `cloud`.`IDEMPOTENT_ADD_COLUMN`('cloud.network_offerings',
'supports_vm_autoscaling', 'boolean default false');
@@ -1186,16 +1137,6 @@ CREATE TABLE IF NOT EXISTS
`cloud`.`tungsten_lb_health_monitor` (
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--- #6888 add index to speed up querying IPs in the network-tab
-DROP PROCEDURE IF EXISTS `cloud`.`IDEMPOTENT_ADD_KEY`;
-
-CREATE PROCEDURE `cloud`.`IDEMPOTENT_ADD_KEY` (
- IN in_index_name VARCHAR(200)
- , IN in_table_name VARCHAR(200)
- , IN in_key_definition VARCHAR(1000)
-)
-BEGIN
-
- DECLARE CONTINUE HANDLER FOR 1061 BEGIN END; SET @ddl = CONCAT('ALTER
TABLE ', in_table_name); SET @ddl = CONCAT(@ddl, ' ', ' ADD KEY ') ; SET @ddl =
CONCAT(@ddl, ' ', in_index_name); SET @ddl = CONCAT(@ddl, ' ',
in_key_definition); PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE
stmt; END;
CALL `cloud`.`IDEMPOTENT_ADD_KEY`('i_user_ip_address_state','user_ip_address',
'(state)');
diff --git a/engine/schema/src/main/resources/META-INF/db/schema-4910to4920.sql
b/engine/schema/src/main/resources/META-INF/db/schema-4910to4920.sql
index a910a8b7799..1aa63020124 100644
--- a/engine/schema/src/main/resources/META-INF/db/schema-4910to4920.sql
+++ b/engine/schema/src/main/resources/META-INF/db/schema-4910to4920.sql
@@ -19,42 +19,6 @@
-- Schema upgrade from 4.9.1.0 to 4.9.2.0;
--;
---;
--- Stored procedure to do idempotent insert;
---;
-
-DROP PROCEDURE IF EXISTS
`cloud`.`IDEMPOTENT_INSERT_GUESTOS_HYPERVISOR_MAPPING`;
-
-CREATE PROCEDURE `cloud`.`IDEMPOTENT_INSERT_GUESTOS_HYPERVISOR_MAPPING`(
- IN in_hypervisor_type VARCHAR(32),
- IN in_hypervisor_version VARCHAR(32),
- IN in_guest_os_name VARCHAR(255),
- IN in_guest_os_id BIGINT(20) UNSIGNED,
- IN is_user_defined int(1) UNSIGNED)
-BEGIN
- IF NOT EXISTS ((SELECT * FROM `cloud`.`guest_os_hypervisor` WHERE
- hypervisor_type=in_hypervisor_type AND
- hypervisor_version=in_hypervisor_version AND
- guest_os_id = in_guest_os_id))
- THEN
- INSERT INTO `cloud`.`guest_os_hypervisor` (
- uuid,
- hypervisor_type,
- hypervisor_version,
- guest_os_name,
- guest_os_id,
- created,
- is_user_defined)
- VALUES (
- UUID(),
- in_hypervisor_type,
- in_hypervisor_version,
- in_guest_os_name,
- in_guest_os_id,
- utc_timestamp(),
- is_user_defined
- ); END IF; END;;
-
CALL `cloud`.`IDEMPOTENT_INSERT_GUESTOS_HYPERVISOR_MAPPING`('Xenserver',
'7.0.0', 'CentOS 4.5 (32-bit)', 1, 0);
CALL `cloud`.`IDEMPOTENT_INSERT_GUESTOS_HYPERVISOR_MAPPING`('Xenserver',
'7.0.0', 'CentOS 4.6 (32-bit)', 2, 0);
CALL `cloud`.`IDEMPOTENT_INSERT_GUESTOS_HYPERVISOR_MAPPING`('Xenserver',
'7.0.0', 'CentOS 4.7 (32-bit)', 3, 0);
@@ -234,5 +198,3 @@ CALL
`cloud`.`IDEMPOTENT_INSERT_GUESTOS_HYPERVISOR_MAPPING`('Xenserver', '7.0.0'
CALL `cloud`.`IDEMPOTENT_INSERT_GUESTOS_HYPERVISOR_MAPPING`('Xenserver',
'7.0.0', 'Ubuntu Trusty Tahr 14.04', 255, 0);
CALL `cloud`.`IDEMPOTENT_INSERT_GUESTOS_HYPERVISOR_MAPPING`('Xenserver',
'7.0.0', 'Ubuntu Trusty Tahr 14.04', 256, 0);
-
-DROP PROCEDURE `cloud`.`IDEMPOTENT_INSERT_GUESTOS_HYPERVISOR_MAPPING`
