-------------------------------------------------- From: "Tomasz Chiliński" tomasz.chilinski@chilan.com Sent: Wednesday, March 13, 2013 11:31 PM To: "LoLe" lole11@o2.pl Subject: Re: [lms] Helpdesk a usuwanie zgłoszeń
W dniu 13.03.2013 23:18, LoLe napisał(a):
W dniu 13.03.2013 04:34, LoLe napisał(a):
Mam wersję LMS git z około 08.12.2012 i ostatnio zauważyłem że kiedy dodam zgłoszenie a później je usunę to w tabelach rtmessages i rtticketcategories zostają wpisy dot tego złgoszenia. Czy to u mnie tak jest czy to jest błąd który nie został jeszcze wykryty?
To u Ciebie błąd - prawdopodobnie nie w pełni aktualnych schemat bazy danych. Rekordy ze wspomnianych tabel usuwane są automatycznie przy usuwaniu rekordów z tabeli rttickets, gdyż wiąże je relacja.
No masz rację. Tylko teraz zastanawiam się gdzie popełniłem błąd. Po przebadaniu okazuje się że klucze obce mam założone tylko na tabelach nodeassignments, macs oraz records.
Patrząc w plik ze strukturami lms.mysql z katalogu doc widzę że tylko te tabele miały definiowane klucze obce w postaci FOREIGN KEY ....:
Zastanawiałem się jak to możliwe, że mysql przyjął składnię opisu relacji niezgodną ze swoją dokumentacją: [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name, ...) REFERENCES tbl_name (index_col_name,...) [ON DELETE reference_option] [ON UPDATE reference_option]
a że było pisane jak w postgresql to pewnie to spowodowało problem ;-)
Fani mysql pewnie teraz wykażą wyższość mysql nad innymi bazami danych polegającą na iluzorycznej "auto korekcie" definicji tabel ;-)
CREATE TABLE nodeassignments ( id int(11) NOT NULL auto_increment, nodeid int(11) NOT NULL, assignmentid int(11) NOT NULL, PRIMARY KEY (id), FOREIGN KEY (nodeid) REFERENCES nodes (id) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY (assignmentid) REFERENCES assignments (id) ON DELETE CASCADE ON UPDATE CASCADE, UNIQUE KEY nodeid (nodeid, assignmentid), INDEX assignmentid (assignmentid) ) ENGINE=InnoDB;
a te na których klucze obce nie pozakładały się są zapisane jako REFERENCES ... tak:
CREATE TABLE rtattachments ( messageid int(11) NOT NULL REFERENCES rtmessages (id) ON DELETE CASCADE ON UPDATE CASCADE, filename varchar(255) NOT NULL DEFAULT '', contenttype varchar(255) NOT NULL DEFAULT '', INDEX messageid (messageid) ) ENGINE=InnoDB;
Czy ja coś skopałem przy instalacji?
-- Pozdrawiam Tomasz Chiliński, Chilan
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. 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 ;
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 ;
On Thu, 14 Mar 2013 10:13:01 +0100, Tomasz Chiliński tomasz.chilinski@chilan.com wrote:
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.
Może nie na temat ... ale nie lepiej zonaczać je statusem deleted tak jak klientów a nie kasować ...
pozdrawiam
-------------------------------------------------- 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.
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;
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;
uczestnicy (3)
-
Dariusz Kowalczyk -
LoLe -
Tomasz Chiliński