40 lines
1.2 KiB
SQL
40 lines
1.2 KiB
SQL
ALTER TABLE pbx.followme DROP FOREIGN KEY followme_ibfk_1;
|
|
ALTER TABLE pbx.queueMember DROP FOREIGN KEY queueMember_ibfk_2;
|
|
ALTER TABLE pbx.sipReg DROP FOREIGN KEY sipReg_ibfk_1;
|
|
|
|
CREATE OR REPLACE TEMPORARY TABLE tmp.tSipReg
|
|
SELECT * FROM pbx.sipReg;
|
|
|
|
CREATE OR REPLACE TEMPORARY TABLE tmp.tOldSip
|
|
SELECT * FROM pbx.sip;
|
|
|
|
TRUNCATE TABLE pbx.sipReg;
|
|
|
|
TRUNCATE TABLE pbx.sip;
|
|
|
|
INSERT INTO pbx.sip (user_id, extension)
|
|
SELECT w.id, w.id FROM vn.worker w;
|
|
|
|
REPLACE INTO pbx.sipReg
|
|
SELECT tsr.* FROM tmp.tSipReg tsr
|
|
JOIN vn.worker w ON tsr.userId = w.id;
|
|
|
|
UPDATE pbx.queueMember qm
|
|
JOIN tmp.tOldSip tos ON tos.extension = qm.extension
|
|
LEFT JOIN pbx.sip s ON s.user_id = tos.user_id
|
|
SET qm.extension = s.extension;
|
|
|
|
ALTER TABLE pbx.followme
|
|
ADD CONSTRAINT followme_ibfk_1
|
|
FOREIGN KEY (extension) REFERENCES pbx.sip(extension);
|
|
|
|
ALTER TABLE pbx.queueMember
|
|
ADD CONSTRAINT queueMember_ibfk_2
|
|
FOREIGN KEY (extension) REFERENCES pbx.sip(extension);
|
|
|
|
ALTER TABLE pbx.sipReg
|
|
ADD CONSTRAINT sipReg_ibfk_1
|
|
FOREIGN KEY (userId) REFERENCES pbx.sip(user_id);
|
|
|
|
DROP TEMPORARY TABLE IF EXISTS tmp.tSipReg;
|
|
DROP TEMPORARY TABLE IF EXISTS tmp.tOldSip; |