aeon_dashboard_back/document/db/20240508.sql

USE `data_center_aeon_admin`;

DROP procedure IF EXISTS `add_column`;

DELIMITER $$

CREATE PROCEDURE add_column()

BEGIN
    IF NOT EXISTS (
        SELECT * 
        FROM information_schema.columns 
        WHERE table_schema = 'data_center_aeon_admin'
        AND table_name = 'basic_company' 
        AND column_name = 'aurora_flag'
    ) THEN
        ALTER TABLE basic_company 
        ADD COLUMN `aurora_flag` int DEFAULT '0' COMMENT '0-未创建，1-创建中，2-创建成功，3-创建失败';
    END IF;
    
    IF NOT EXISTS (
        SELECT * 
        FROM information_schema.columns 
        WHERE table_schema = 'data_center_aeon_admin'
        AND table_name = 'basic_company' 
        AND column_name = 'aurora_url'
    ) THEN
        ALTER TABLE basic_company 
        ADD COLUMN `aurora_url` varchar(255) DEFAULT NULL;
    END IF;
    
    IF NOT EXISTS (
        SELECT * 
        FROM information_schema.columns 
        WHERE table_schema = 'data_center_aeon_admin'
        AND table_name = 'basic_company' 
        AND column_name = 'aurora_username'
    ) THEN
        ALTER TABLE basic_company 
        ADD COLUMN `aurora_username` varchar(255) DEFAULT NULL;
    END IF;
    
    IF NOT EXISTS (
        SELECT * 
        FROM information_schema.columns 
        WHERE table_schema = 'data_center_aeon_admin'
        AND table_name = 'basic_company' 
        AND column_name = 'aurora_password'
    ) THEN
        ALTER TABLE basic_company 
        ADD COLUMN `aurora_password` varchar(255) DEFAULT NULL;
    END IF;
    
    IF NOT EXISTS (
        SELECT * 
        FROM information_schema.columns 
        WHERE table_schema = 'data_center_aeon_admin'
        AND table_name = 'basic_company' 
        AND column_name = 'redis_db_id'
    ) THEN
        ALTER TABLE basic_company 
        ADD COLUMN `redis_db_id` int DEFAULT NULL COMMENT '使用的redis库id';
    END IF;
    
    IF NOT EXISTS (
        SELECT * 
        FROM information_schema.columns 
        WHERE table_schema = 'data_center_aeon_admin'
        AND table_name = 'basic_company' 
        AND column_name = 'bearer_token'
    ) THEN
        ALTER TABLE basic_company 
        ADD COLUMN `bearer_token` text;
    END IF;
    
    update basic_company set aurora_flag = 2 where aurora_url is not null;
    
END$$

DELIMITER ;


CALL add_column();

DROP procedure IF EXISTS `add_column`;