Back (Current repo: scraps)

copy_schema_struct.sql (2394B)

USE some_schema;

-- May need to add DEFINER 
DROP PROCEDURE IF EXISTS `copy_schema_with_config`;

CREATE PROCEDURE copy_schema_with_config(IN inOldDB VARCHAR(256), IN inNewDB VARCHAR(256), IN copyTables INT(1))
  BEGIN

    DECLARE v_finished INTEGER DEFAULT 0;
    DECLARE v_tname varchar(100) DEFAULT "";

    DECLARE currentTable CURSOR FOR
      SELECT `table_name`
      FROM INFORMATION_SCHEMA.TABLES
      WHERE TABLE_SCHEMA = inOldDB
            and TABLE_TYPE='BASE TABLE';

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_finished = 1;
    -- Should be able to delete tables even if they have foreign keys
    SET FOREIGN_KEY_CHECKS = 0;
    set @result=NULL;

    -- Create new schema if it doesn't yet exist
    SET @create = CONCAT('CREATE SCHEMA IF NOT EXISTS ',inNewDB,';');
    PREPARE create_schema FROM @create;
    EXECUTE create_schema;
    DEALLOCATE PREPARE create_schema;

    -- Loop over tables in the old schema
    OPEN currentTable;
    clone_tables: LOOP

      -- Get the next table name
      FETCH currentTable INTO v_tname;

      -- Leave the Loop if we're done
      IF v_finished = 1 THEN LEAVE clone_tables;
      END IF;

      -- Drop tables in target DB schema if they already exist
      SET @drop = CONCAT("DROP TABLE IF EXISTS `" , inNewDB, "`.`", v_tname,"`;");
      PREPARE stmt FROM @drop;
      EXECUTE stmt;
      DEALLOCATE PREPARE stmt;

      -- Clone the table from the origin
      SET @sql = CONCAT("CREATE TABLE `", inNewDB, "`.`", v_tname, "` LIKE `", inOldDB, "`.`", v_tname, "`;");
      PREPARE clone_table FROM @sql;
      EXECUTE clone_table;
      DEALLOCATE PREPARE clone_table;

      -- Optionally clone some data
      -- maybe change this to a for each in kind of thing besides config
      IF (copyTables > 0 && v_tname = 'config') THEN
        SET @data = CONCAT("INSERT INTO `", inNewDB, "`.`", v_tname, "` SELECT * FROM `", inOldDB, "`.`", v_tname, "`;");
        PREPARE clone_data FROM @data;
        EXECUTE clone_data;
        DEALLOCATE PREPARE clone_data;
      END IF;

      -- Give the user some result message
      SET @result = IFNULL(CONCAT(@result,',',v_tname),v_tname);

    END LOOP clone_tables;

    -- Close cursor

    CLOSE currentTable;
    SET FOREIGN_KEY_CHECKS = 1;
    -- Print result message
    SELECT CONCAT("Copied schema structure from ", inOldDB, " to ", inNewDB, ": ", @result);

  END;