[MySQL] Recursief verwijderen - kom er niet uit

[Verwijderd]

Hoi,

Ik heb ondermeer volgende tabellen.

persoon


creatie


persoon_creatie


Zoals je ziet verbindt persoon_creatie de tabellen persoon en creatie. Nu kan het zo zijn dat een persoon geen lid meer wil zijn, en z'n account verwijdert. Geen probleem, maar dan moeten zijn creaties ook verwijderd. Het moeilijke zit 'em in het gegeven dat hij tijdens z'n verblijf op de site kan aangeduid hebben dat een andere persoon ook aan die creatie heeft meegewerkt. In dit geval hebben personen met id's 1 en 2 (Toon en Kay) samen aan de creatie met id 2 (R&B Feelings gewerkt). Natuurlijk blijft die creatie van Kay, dus die mag dan niet uit de tabel creatie verdwijnen. De verbinding met persoon 1 (Toon) en zijn creaties verwijder ik met deze automatisch opgebouwde query:

code:

DELETE FROM persoon_creatie WHERE creatie_id = 1 OR creatie_id = 2 OR creatie_id = 3;

Hoe controleer ik binnen één query of die creatie dan nog wegmag? Iets in me zegt dat ik het met INNER JOIN moet aanpakken, maar ik zie even niet hoe. Alvast bedankt!

[user109731]

Stel Toon met id = 1 wil z'n account verwijderen, dan kun je toch gewoon zo doen:

SQL:

DELETE FROM persoon WHERE id = 1
DELETE FROM persoon_creatie WHERE persoon_id = 1
DELETE FROM creatie WHERE NOT id IN (SELECT creatie_id FROM persoon_creatie)

Door een Foreign Key te leggen kun je nog zonder de tweede query, maar FR's werken alleen met InnoDB.

[Orion84]

Dan zou ik wel de volgorde omdraaien, dus eerst alle creaties weggooien die volgens persoon_creatie bij id 1 horen, daarna de relaties en daarna de persoon zelf. Dat lijkt me efficienter dan met die subquery.
Eerst de TS lezen voor ik reageer op replies

Neemt niet weg dat die oplossing tamelijk inefficiënt gaat worden als je flink wat records in die tabellen hebt staan. Het is ook niet voor niets dat veel sites niet zomaar dingen verwijderen uit hun DB, maar gewoon een extra veld "deleted" of iets dergelijks opnemen waarmee een soft-delete ondersteund wordt.

[ Voor 87% gewijzigd door Orion84 op 09-01-2008 19:46 . Reden: Niet zo nuttige toevoeging verwijderd ]

[Gomez12]

Gewoon in je persoonstabel en creatietabel een veldje verwijderd erbij zetten, bij het tonen van creaties alleen de personen die niet verwijderd zijn tonen en als een creatie geen personen meer heeft, dan krijgt deze ook de status verwijderd.

Dan blijven je statistieken etc ook correct. Dingen verwijderen in een relationele database is vrij ingewikkeld omdat je al snel relaties breekt.
Wil je het echt verwijderd hebben ( vanwege ruimtegebrek etc ) dan zou ik ervoor kiezen om 1 maintenance script te maken wat 's nachts alles kan controleren en echt verwijderen.

[SyphOn]

JanDM schreef op woensdag 09 januari 2008 @ 19:20:
Stel Toon met id = 1 wil z'n account verwijderen, dan kun je toch gewoon zo doen:

SQL:

DELETE FROM persoon WHERE id = 1
DELETE FROM persoon_creatie WHERE persoon_id = 1
DELETE FROM creatie WHERE NOT id IN (SELECT creatie_id FROM persoon_creatie)

Door een Foreign Key te leggen kun je nog zonder de tweede query, maar FR's werken alleen met InnoDB.

is toch genoeg?

[ Voor 70% gewijzigd door SyphOn op 09-01-2008 19:43 ]

[BCC]

Even out of the box: Waarom maak je geen enabled veld bij de person tabel? Dan blijven al je relaties heel.

[Verwijderd]

Bedankt voor de reacties... Zo'n verwijderveldje is niet echt een optie, omdat ik dan meer werk zou hebben alle andere tabellen en scripts aan te passen. Dat komt nu een beetje ongelukkig laat. Verder zijn de tabellen van het type MyISAM, en om dat nu in InnoDB om te zetten, is het ook wat laat

Over die controle-inefficiëntie struikel ik voorlopig niet. Ben dus nog steeds op zoek naar die ene query, of als het niet in één query kan... Twee is ook nog goed. De eerste zou dan een array kunnen opbouwen met de rijen die echt weg mogen. Of redeneer ik hier fout?

[BCC]

Verwijderd schreef op woensdag 09 januari 2008 @ 20:17:
Bedankt voor de reacties... Zo'n verwijderveldje is niet echt een optie, omdat ik dan meer werk zou hebben alle andere tabellen en scripts aan te passen.

Als je geen object abstractie laag hebt, dan is het waarschijnlijk aardig wat werk idd....

[djiwie]

Verwijderd schreef op woensdag 09 januari 2008 @ 20:17:
Verder zijn de tabellen van het type MyISAM, en om dat nu in InnoDB om te zetten, is het ook wat laat

Waarom zou dat veel werk zijn? Twee muisklikken ofzo met phpmyadmin (wat je zo te zien toch al gebruikt), en daarna even je relaties vastleggen.

[curry684]

Orion84 schreef op woensdag 09 januari 2008 @ 19:24:
Neemt niet weg dat die oplossing tamelijk inefficiënt gaat worden als je flink wat records in die tabellen hebt staan. Het is ook niet voor niets dat veel sites niet zomaar dingen verwijderen uit hun DB, maar gewoon een extra veld "deleted" of iets dergelijks opnemen waarmee een soft-delete ondersteund wordt.

De hoofdreden voor soft-deletes is zelden performance, vaker security, backup, consistency en gemak.

Verwijderd schreef op woensdag 09 januari 2008 @ 20:17:
Over die controle-inefficiëntie struikel ik voorlopig niet. Ben dus nog steeds op zoek naar die ene query, of als het niet in één query kan... Twee is ook nog goed. De eerste zou dan een array kunnen opbouwen met de rijen die echt weg mogen. Of redeneer ik hier fout?

De enige manier waarop je hier met minder dan {aantal_tabellen} queries rond gaat komen is met cascading deletes, en daarvoor heb je toch echt MySQL 5.0, InnoDB tables en consequente DRI nodig. Anders ben je per definitie 1 query per tabel kwijt, omdat een delete-query nu eenmaal per definitie maar uit 1 tabel kan deleten. En cascading deletes is toch wel echt een van de enorme do-not-go-there's van database design om verschrikkelijk veel redenen.

[ Voor 43% gewijzigd door curry684 op 10-01-2008 00:53 ]

[Gomez12]

curry684 schreef op donderdag 10 januari 2008 @ 00:48:
[...]

En cascading deletes is toch wel echt een van de enorme do-not-go-there's van database design om verschrikkelijk veel redenen.

Maar ja, daarmee kan hij het wel met 1 query doen

[Duroth]

Gomez12 schreef op zaterdag 12 januari 2008 @ 03:39:
[...]

Maar ja, daarmee kan hij het wel met 1 query doen

Ja, maar DROP DATABASE; werkt in dezelfde denkwijze dus ook... Of je er op zit te wachten is een tweede

[CH4OS]

Verwijderd schreef op woensdag 09 januari 2008 @ 20:17:
Bedankt voor de reacties... Zo'n verwijderveldje is niet echt een optie, omdat ik dan meer werk zou hebben alle andere tabellen en scripts aan te passen. Dat komt nu een beetje ongelukkig laat. Verder zijn de tabellen van het type MyISAM, en om dat nu in InnoDB om te zetten, is het ook wat laat

Waarom zou dat veel werk moeten zijn dan? Je maakt er een int(1) van met een default waarde 0 (uit, niet verwijderd)... Alleen bij je login query moet je je where clause aanpassen en deleted = 0 toevoegen, that's it lijkt me... Lijkt mij overigens dat alleen tabel persoon dan een extra rij krijgt... De rest van de items kunnen altijd nog gebruikt worden, mits rechten...

[ Voor 10% gewijzigd door CH4OS op 12-01-2008 17:02 ]

[Gomez12]

GJ-tje schreef op zaterdag 12 januari 2008 @ 16:59:
[...]
Waarom zou dat veel werk moeten zijn dan? Je maakt er een int(1) van met een default waarde 0 (uit, niet verwijderd)... Alleen bij je login query moet je je where clause aanpassen en deleted = 0 toevoegen, that's it lijkt me... Lijkt mij overigens dat alleen tabel persoon dan een extra rij krijgt... De rest van de items kunnen altijd nog gebruikt worden, mits rechten...

Ehm, een creatie die alleen nog maar aan verwijderde personen toebehoort mag niet meer getoond worden lijkt mij? En om nu elke keer bij een creatie tonen te kijken of alle personen wel of niet verwijderd zijn. Dat kan een performance probleem veroorzaken als het om veel creaties en veel personen per creatie gaat.

[CH4OS]

Gomez12 schreef op zaterdag 12 januari 2008 @ 17:26:
Ehm, een creatie die alleen nog maar aan verwijderde personen toebehoort mag niet meer getoond worden lijkt mij? En om nu elke keer bij een creatie tonen te kijken of alle personen wel of niet verwijderd zijn. Dat kan een performance probleem veroorzaken als het om veel creaties en veel personen per creatie gaat.

Hangt er vanaf wat voor creatie het is en of anderen er gebruik van mogen maken ja of nee, is maar net wat de machtigingen zijn dus...

Het is lijkt mij gewoon de simpelste oplossing, geheel verwijderen lijkt mij nou ook niet wenselijk bijv. ivm clones, maar goed, ik weet daar de policy niet van...

[ Voor 11% gewijzigd door CH4OS op 12-01-2008 18:07 ]

[Gomez12]

GJ-tje schreef op zaterdag 12 januari 2008 @ 18:04:
[...]
Hangt er vanaf wat voor creatie het is en of anderen er gebruik van mogen maken ja of nee, is maar net wat de machtigingen zijn dus...

Het is lijkt mij gewoon de simpelste oplossing, geheel verwijderen lijkt mij nou ook niet wenselijk bijv. ivm clones, maar goed, ik weet daar de policy niet van...

Gaat mij ook niet meer om geheel verwijderen ja/nee, verwijder kolom is voor zowieso the way to go.
Maar dat betekent dus wel dat hij iets meer query's moet aanpassen dan die enkele die jij noemt.
Kan op zich nog best redelijk ingrijpend zijn ( alhoewel met een goede scheiding tussen query's en output is het enkel je query's aanpassen, je output krijgt minder data maar wel op dezelfde manier )