W dniu 18.03.2013 18:36, LoLe napisał(a):
From: "Tomasz Chiliński" tomasz.chilinski@chilan.com Sent: Thursday, March 14, 2013 10:13 AM To: lms@lists.lms.org.pl Subject: Re: [lms] Helpdesk a usuwanie zgłoszeń
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.
Generalnie wprowadziłem kosmetykę i uwzględniłem tabelę PNA. Odpalałem na dwóch bazach produkcyjnych i szło bez błędów.
Dzięki za info. Nikt nie zgłasza sprzeciwu to pójdzie dziś wieczorem uaktualnienie schematu mysql 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 `pna` ADD INDEX (`streetid`); ALTER TABLE `pna` ADD INDEX (`cityid`); ALTER TABLE `pna` ADD FOREIGN KEY (`cityid`) REFERENCES `lms`.`location_cities` (`id`) ON DELETE CASCADE ON UPDATE CASCADE; ALTER TABLE `pna` ADD FOREIGN KEY (`streetid`) REFERENCES `lms`.`location_streets` (`id`) ON DELETE CASCADE 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`) ON DELETE SET NULL ON UPDATE CASCADE; 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; DELETE FROM `managementurls` WHERE `netdevid` not in (select id from netdevices); ALTER TABLE `managementurls` ADD INDEX (`netdevid`); ALTER TABLE `managementurls` ADD FOREIGN KEY (`netdevid`) REFERENCES `lms`.`netdevices` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;