SQLize Online / PHPize Online  /  SQLtest Online

#setup dummy table for latter CREATE TABLE vps_private_network ( NETWORKID varchar(16) NOT NULL COLLATE utf8mb4_general_ci, 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) COLLATE utf8mb4_general_ci AFTER DBAASACCOUNTPROJECTID; ALTER TABLE account_subscription_dbaas ADD CONSTRAINT FOREIGN KEY (NETWORKID) REFERENCES vps_private_network(NETWORKID) ON UPDATE CASCADE ON DELETE RESTRICT; 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'); #tests #UPDATE account_subscription_dbaas SET NETWORKID = 'a';