#setup dummy table for latter
CREATE TABLE vps_private_network (
NETWORKID varchar(16) CHARACTER SET latin1 NOT NULL,
type varchar(255),
PRIMARY KEY(NETWORKID)
);
INSERT INTO vps_private_network (NETWORKID, type) values ('a','dev'),('b','qa'),('c','prod');
CREATE TABLE account_subscription_dbaas (
SUBID int NOT NULL AUTO_INCREMENT,
DBAASACCOUNTPROJECTID int NOT NULL,
service_name varchar(255),
dbname varchar(255),
host varchar(255),
user varchar(255),
port varchar(255),
password varchar(255),
PRIMARY KEY (SUBID)
);
INSERT INTO account_subscription_dbaas (DBAASACCOUNTPROJECTID, service_name, host, port)
VALUES
(1,'mydb','dbhost','80'),
(1,'yourdb','ghost','20'),
(1,'ourdb','with the most','130')
;
ALTER TABLE account_subscription_dbaas ADD COLUMN NETWORKID varchar(16) CHARACTER SET utf8 AFTER DBAASACCOUNTPROJECTID;
ALTER TABLE `account_subscription_dbaas` MODIFY COLUMN `NETWORKID` varchar(16) CHARACTER SET latin1 NULL DEFAULT NULL;
ALTER TABLE `account_subscription_dbaas` ADD CONSTRAINT `account_subscription_dbaas_ibfk_NETWORKID` FOREIGN KEY (`NETWORKID`) REFERENCES `vps_private_network`(`NETWORKID`) ON DELETE SET NULL ON UPDATE CASCADE;
INSERT INTO account_subscription_dbaas (DBAASACCOUNTPROJECTID, user, password, NETWORKID)
VALUES
(2,'tim','pass', NULL),
(2,'mike','123', NULL),
(2,'jess','test', NULL),
(2,'lizzy','secret', 'a'),
(2,'tom','pwrd', 'b'),
(2,'katie','mybirthday', NULL),
(2,'brian','unicorn','c'),
(2,'aruna','jdifonqwejfin3242noqwf', NULL)
;
#Create new table and move user and password over to new table
CREATE TABLE dbaas_service_users (
USERID int NOT NULL AUTO_INCREMENT,
DBAASSUBID int NOT NULL,
username varchar(255) NOT NULL,
password varchar(255) NOT NULL,
encryption_method varchar(255) NOT NULL,
PRIMARY KEY (USERID),
FOREIGN KEY (DBAASSUBID) REFERENCES account_subscription_dbaas(SUBID)
);
INSERT INTO dbaas_service_users (DBAASSUBID, username, password, encryption_method)
SELECT SUBID, user, password, 'default'
FROM account_subscription_dbaas
Where user IS NOT NULL AND password IS NOT NULL;
ALTER TABLE account_subscription_dbaas
DROP COLUMN user,
DROP COLUMN password;
# add another manualy
INSERT INTO dbaas_service_users (DBAASSUBID, username, password, encryption_method)
VALUES (10,'bob','opensaysame','encrypt2');
SHOW FULL COLUMNS FROM account_subscription_dbaas;
#tests
#UPDATE account_subscription_dbaas SET NETWORKID = 'a';
Telegram: DB developers channel