W dniu 14.03.2013 03:32, LoLe napisał(a):
Skleciłem coś co może przydać się innym. Delety rekordów sierot zrobiłem wg własnych potrzeb. Być może u innych trzeba będzie gdzie inndziej czyścić. Oczywiście używacie tego na własną odpowiedzialność. Może też niech ktoś zerknie na to czy jest ok i sie wypowie.
Czy ktoś jeszcze testował tą poprawkę schematu bazy danych mysql u siebie? Planuję przygotować aktualizację schematu DB wykorzystującą poniższe polecenia i puścić ją do git.
DELETE FROM `assignments` WHERE customerid not in (select id from customers); ALTER TABLE `assignments` ADD FOREIGN KEY ( `customerid` ) REFERENCES `lms`.`customers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ; ALTER TABLE `assignments` ADD FOREIGN KEY ( `numberplanid` ) REFERENCES `lms`.`numberplans` (`id`) ON DELETE SET NULL ON UPDATE CASCADE ; ALTER TABLE `location_districts` ADD FOREIGN KEY ( `stateid` ) REFERENCES `lms`.`location_states` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ; ALTER TABLE `location_boroughs` ADD FOREIGN KEY ( `districtid` ) REFERENCES `lms`.`location_districts` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ; ALTER TABLE `location_cities` ADD FOREIGN KEY ( `boroughid` ) REFERENCES `lms`.`location_boroughs` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ; ALTER TABLE `location_streets` ADD FOREIGN KEY ( `cityid` ) REFERENCES `lms`.`location_cities` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ; ALTER TABLE `location_streets` ADD INDEX ( `typeid` ) ; ALTER TABLE `location_streets` ADD FOREIGN KEY ( `typeid` ) REFERENCES `lms`.`location_street_types` (`id`) ON DELETE SET NULL ON UPDATE CASCADE ; ALTER TABLE `netdevices` ADD FOREIGN KEY ( `location_city` ) REFERENCES `lms`.`location_cities` (`id`) ON DELETE SET NULL ON UPDATE CASCADE ; ALTER TABLE `netdevices` ADD FOREIGN KEY ( `location_street` ) REFERENCES `lms`.`location_streets` (`id`) ON DELETE SET NULL ON UPDATE CASCADE ; ALTER TABLE `netdevices` ADD FOREIGN KEY ( `channelid` ) REFERENCES `lms`.`ewx_channels` (`id`) ON DELETE SET NULL ON UPDATE CASCADE ; ALTER TABLE `nodes` ADD FOREIGN KEY ( `location_city` ) REFERENCES `lms`.`location_cities` (`id`) ON DELETE SET NULL ON UPDATE CASCADE ; ALTER TABLE `nodes` ADD FOREIGN KEY ( `location_street` ) REFERENCES `lms`.`location_streets` (`id`) ON DELETE SET NULL ON UPDATE CASCADE ; ALTER TABLE `nodelocks` ADD INDEX ( `nodeid` ); ALTER TABLE `nodelocks` ADD FOREIGN KEY ( `nodeid` ) REFERENCES `lms`.`nodes` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ; ALTER TABLE `rtattachments` ADD FOREIGN KEY ( `messageid` ) REFERENCES `lms`.`rtmessages` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ; DELETE FROM `rtmessages` WHERE ticketid NOT IN (SELECT id FROM rttickets); ALTER TABLE `rtmessages` ADD FOREIGN KEY ( `ticketid` ) REFERENCES `lms`.`rttickets` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ; DELETE from `rtnotes` WHERE ticketid NOT IN (SELECT id FROM rttickets); ALTER TABLE `rtnotes` ADD FOREIGN KEY ( `ticketid` ) REFERENCES `lms`.`rttickets` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ; ALTER TABLE `rtnotes` ADD FOREIGN KEY ( `userid` ) REFERENCES `lms`.`users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ; ALTER TABLE `rttickets` ADD FOREIGN KEY ( `queueid` ) REFERENCES `lms`.`rtqueues` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ; ALTER TABLE `rtrights` ADD INDEX ( `queueid` ); ALTER TABLE `rtrights` ADD FOREIGN KEY ( `userid` ) REFERENCES `lms`.`users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ; ALTER TABLE `rtrights` ADD FOREIGN KEY ( `queueid` ) REFERENCES `lms`.`rtqueues` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ; ALTER TABLE `rtcategoryusers` ADD INDEX ( `categoryid` ); ALTER TABLE `rtcategoryusers` ADD FOREIGN KEY ( `userid` ) REFERENCES `lms`.`users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ; ALTER TABLE `rtcategoryusers` ADD FOREIGN KEY ( `categoryid` ) REFERENCES `lms`.`rtcategories` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ; Delete FROM `rtticketcategories` where `ticketid` not in (select id from rttickets); ALTER TABLE `rtticketcategories` ADD INDEX ( `categoryid` ); ALTER TABLE `rtticketcategories` ADD FOREIGN KEY ( `ticketid` ) REFERENCES `lms`.`rttickets` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ; ALTER TABLE `rtticketcategories` ADD FOREIGN KEY ( `categoryid` ) REFERENCES `lms`.`rtcategories` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ; delete FROM `promotionschemas` where `promotionid` not in (select id from promotions); ALTER TABLE `promotionschemas` ADD FOREIGN KEY ( `promotionid` ) REFERENCES `lms`.`promotions` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ; ALTER TABLE `promotionschemas` ADD FOREIGN KEY ( `ctariffid` ) REFERENCES `lms`.`tariffs` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE ; delete FROM `promotionassignments` WHERE `promotionschemaid` not in (select id from promotionschemas); delete FROM `promotionassignments` WHERE `tariffid` not in (select id from tariffs); ALTER TABLE `promotionassignments` ADD FOREIGN KEY ( `promotionschemaid` ) REFERENCES `lms`.`promotionschemas` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
; ALTER TABLE `promotionassignments` ADD FOREIGN KEY ( `tariffid` ) REFERENCES `lms`.`tariffs` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ; ALTER TABLE `customerassignments` ADD FOREIGN KEY ( `customergroupid` ) REFERENCES `lms`.`customergroups` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ; ALTER TABLE `customerassignments` ADD FOREIGN KEY ( `customerid` ) REFERENCES `lms`.`customers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ; ALTER TABLE `sourcefiles` ADD FOREIGN KEY ( `userid` ) REFERENCES `lms`.`users` (`id`); DELETE FROM `cashimport` where `customerid` not in (select id from customers); ALTER TABLE `cashimport` ADD FOREIGN KEY ( `customerid` ) REFERENCES `lms`.`customers` (`id`) ON DELETE SET NULL ON UPDATE CASCADE ; ALTER TABLE `cashimport` ADD FOREIGN KEY ( `sourceid` ) REFERENCES `lms`.`cashsources` (`id`) ON DELETE SET NULL ON UPDATE CASCADE ; ALTER TABLE `cashimport` ADD FOREIGN KEY ( `sourcefileid` ) REFERENCES `lms`.`sourcefiles` (`id`) ON DELETE SET NULL ON UPDATE CASCADE ; ALTER TABLE `imessengers` ADD FOREIGN KEY ( `customerid` ) REFERENCES `lms`.`customers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ; ALTER TABLE `customercontacts` ADD FOREIGN KEY ( `customerid` ) REFERENCES `lms`.`customers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ; ALTER TABLE `excludedgroups` ADD FOREIGN KEY ( `customergroupid` ) REFERENCES `lms`.`customergroups` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ; ALTER TABLE `managementurls` ADD INDEX ( `netdevid` ); delete FROM `managementurls` WHERE `netdevid` not in (select id from netdevices); ALTER TABLE `managementurls` ADD FOREIGN KEY ( `netdevid` ) REFERENCES `lms`.`netdevices` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ;