[SQL]'Towers of Hanoi' probleempje...

[RetepV]

Ok, hypothetisch. Ik heb de volgende tabel:

code:

Tabel: Test

NR1 NR2 KL3
1   1   Een
1   2   Twee
1   3   Drie

Er is een primary key over beide velden gelegd, dwz. er mogen geen dubbele combinaties van NR1 en NR2 voorkomen.

Nou wil ik van de tweede en derde rij de cijfers van de tweede kolom omwisselen zodat de tabel het volgende gaat bevatten:

code:

Tabel: Test

NR1 NR2 KL3
1   1   Een
1   2   Drie
1   3   Twee

Dit stukje code geeft logisch een key violation omdat direkt na de eerste statement al de key's van Twee en Drie gelijk zijn:

code:

UPDATE Test SET NR2=3 WHERE (NR1=1) AND (NR2=2)
UPDATE Test SET NR2=2 WHERE (NR1=1) AND (NR3=3)

Ook dit in een Transaction doen levert een error op (wat ik overigens niet verwacht had, maar alla).

Enige idee hoe ik het voor elkaar kan krijgen zonder een tijdelijke waarde (b.v. -1) aan NR2 toe te kennen en zonder de andere velden om te wisselen i.p.v. NR2?

[Robtimus]

Wissel je KL3 om?

[Grijze Vos]

Als je zelf deze verwisseling maakt, en zorg draagt voor het intact blijven van je database, kun je die restraint natuurlijk ook weglaten...

Niet zo'n nette oplossing, maar et werkt wel

[.oisyn]

RetepV schreef op 11 november 2003 @ 19:24:
Ook dit in een Transaction doen levert een error op (wat ik overigens niet verwacht had, maar alla).

een transaction zorgt er niet voor dat dingen "tegelijk" worden uitgevoerd. Het zorgt er slechts voor dat, zodra er een error optreedt, de vorige al uitgevoerde queries terug worden gedraaid, zodat een consistente database behouden blijft

En idd, zoals IceManX al zei, waarom wissel je niet gewoon de waarden in de 3e kolom om ipv de 2e

[SuperRembo]

Zou dit kunnen werken?

code:

UPDATE Test 
SET NR2 = CASE WHEN NR2=2 THEN 3 ELSE 2 END
WHERE (NR1=1) AND ((NR2=2) OR (NR2=3))

TSQL kan ik hier niet testen. In MSAccess heb ik 't even geprobeerd met:

code:

UPDATE Test 
SET Test.NR2 = IIF(Test.NR2=2, 3, 2)
WHERE (((Test.NR1)=1) AND (((Test.NR2)=2) OR ((Test.NR2)=3)));

Maar die pikt het niet. Het zal wel row voor row moeten kloppen, wat me ook logisch lijkt.

edit:

Ik heb het net getest op MS SqlSever 2000, en het werkt Blijkbaar wordt na de update van alle rows gekeken of de constraints nog kloppen.

[ Voor 70% gewijzigd door SuperRembo op 12-11-2003 09:18 ]

[ACM]

RetepV schreef op 11 november 2003 @ 19:24:
Ook dit in een Transaction doen levert een error op (wat ik overigens niet verwacht had, maar alla).

Waarom had je dat niet verwacht?
Een transactie is niet iets dat "wacht met verwerken tot jij er klaar voor bent" of de volgordes aan zal passen, dus het zou gekker zijn als het dan ineens wel goed ging.

Enige idee hoe ik het voor elkaar kan krijgen zonder een tijdelijke waarde (b.v. -1) aan NR2 toe te kennen en zonder de andere velden om te wisselen i.p.v. NR2?

Ik betwijfel of zoiets kan zonder een of andere tussenstap, want zodra je probeert de ene te veranderen overtreed je de PK-restrictie, als je de PK-restrictie weglaat wordt het nog interessanter omdat je dan na de eerste update 2 dezelfde records hebt die daarna allebei foutief weer een nieuwe waarde krijgen

Je hoort de PK eigenlijk ook niet te wijzigen.

[ Voor 3% gewijzigd door ACM op 11-11-2003 21:43 ]

[RetepV]

IceManX schreef op 11 november 2003 @ 19:26:
Wissel je KL3 om?

In mijn werkelijke situatie zijn het natuurlijk heel wat meer velden dan één. Als ik dat ga doen, dan wordt het al gauw niet goed onderhoudbaar meer.

Ik zou voor het omwisselen natuurlijk een USF of SP kunnen maken, maar ook daarmee boet je in aan onderhoudbaarheid. Over een jaar is iedereen vergeten hoe het ook al weer werkt, en als er dan velden toegevoegd worden, worden die niet mee gekopieerd. En daar kom je natuurlijk pas een aantal maanden later achter omdat de nieuwe functionaliteit niet direkt zo veel gebruikt wordt. En dan is het kwaad geschiedt en zit je met data die niet meer klopt. Da's een wordt-case situatie die je ab-so-luut niet wilt hebben...

[curry684]

Allereerst wil ik ACM's statement onderschrijven: als je PK ooit veranderd moet worden is het geen goede primary key. Ergo je demonstreert hier een slecht DB-model

Daarnaast:

SuperRembo schreef op 11 november 2003 @ 21:01:
Ik heb het net getest op MS SqlSever 2000, en het werkt Blijkbaar wordt na de update van alle rows gekeken of de constraints nog kloppen.

Jah dit is logisch. Binnen programmeertheorie bestaat de term 'invariant'. Het 'invariant' zijn van een situatie houdt in dat de data technisch en referentieel intact is. Het behouden van de invariant-status is niet constant mogelijk, echter hij hoort *voor* en *na* iedere operatie te bestaan.

Een transactie groepeert enkel acties voor een centrale 'commit', hij maakt niet van meerdere acties een atomaire actie. Ergo tussen afzonderlijke onderdelen van een transactie dienen alle referenties (dus ook foreign key constraints) intact te zijn. Tijdens een enkele actie/query wordt de status alleen vooraf en naderhand afgedwongen.

Het gedrag van SqlServer klopt dus (zoals je van een Enterprise DBMS ook wel mag verwachten )

[The Eagle]

Als je dit wilt doen, waarom maak je dan geen tussentabel aan? Schrijf je gewoon een stukje SQL script, waarin je eerst een tussentabel aanmaakt, vervolgende de goede waarden op de juiste plaats zet, en dan commit. 2e stukje van het script stript eerst de 1e tabel leeg en kopieert dan de waarden van de tussentabel in de 1e tabel. Vervolgens commit, drop tussentabel en weer commit.
Voila, c'est ca

[RetepV]

ACM schreef op 11 november 2003 @ 21:40:
[...]

Waarom had je dat niet verwacht?
Een transactie is niet iets dat "wacht met verwerken tot jij er klaar voor bent" of de volgordes aan zal passen, dus het zou gekker zijn als het dan ineens wel goed ging.

Ik had eigenlijk verwacht dat in een transactie de constraints pas gechecked worden als de transactie gecommit wordt. In feite kan dat prima en ik zie er de problemen niet echt van in.

Maar een transactie is natuurlijk niet zo gedefinieerd, ik zat daar fout. Inderdaad wordt er een log bijgehouden waarmee de veranderingen opgeslagen worden en terug te draaien zijn als dat nodig is. Als je een transactie commit, dan is het enige wat nog gebeurt dat er een denkbeeldig vlaggetje in de transactie-log wordt gezet dat de transactie successvol afgesloten is.

Het zou leuk zijn als er iets was waarmee je een ondeelbare reeks instructies kunt uitvoeren... Dwz. dat je van alles kunt doen, of het nou goed of fout is, als de uiteindelijke uitkomst maar klopt met de constraints. In feite past dit prima in het beeld van het Detached verwerken van data.

Maar goed, er is dus blijkbaar niet zoiets, dus zal het anders moeten. Ik denk dat ik dan maar weer terug moet grijpen naar een slimme SP. Of een extra veld introduceren.

[...]

Aan de PK zou je inderdaad niet moeten komen, maar ja, het gaat hier om iets als orderregels in een order systeem. De regels moeten uniek zijn op OrderNR en OrderRegelNR. Maar in mijn specifieke geval is de volgorde van de regels belangrijk en die wordt gegeven door het regelnummer. Regelnummers mogen dus niet twee keer voorkomen en dus een PK of OrderNR en OrderRegelNR.

De beste oplossing die ik dan kan bedenken is een extra veld OrderRegelVolgorde ofzo toe te voegen, waarmee ik snel met de volgorde kan goochelen. Helaas redundante informatie, maar de andere alternatieven lijken erger te zijn .

[RetepV]

curry684 schreef op 12 november 2003 @ 10:19:
Een transactie groepeert enkel acties voor een centrale 'commit', hij maakt niet van meerdere acties een atomaire actie.

'Atomaire actie', dat was de term die ik zocht .

Eigenlijk zou dat dus prima in het model van Detached werken met een database passen...

[Verwijderd]

Ik zou inderdaad ook van de PK afblijven. Een ontwerp waarbij je constant de PK's aan het aanpassen bent is geen goed ontwerp.

Zoals hierboven al eerder is voorgesteld: waarom wissel je niet gewoon de waarde van kolom KL3 om? Het uiteindelijk resultaat is precies hetzelfde. Twee updates achter elkaar binnen 1 transactie... gaat altijd goed.

Als je nu toch echt de PK's wil aanpassen zou je het volgende kunnen doen (binnen 1 transactie)
- delete de twee records met de oude PK's en de oude KL3 waarde.
- insert twee nieuwe records met de nieuwe PK's en de nieuwe KL3 waarde.
(dit is uiteindelijk hetzelfde als alleen de waarde van KL3 omwisselen maar goed... wat je zelf wil!)

[ Voor 4% gewijzigd door Verwijderd op 12-11-2003 10:49 ]

[RetepV]

RetepV schreef op 12 november 2003 @ 10:33:
[...]


Ik had eigenlijk verwacht dat in een transactie de constraints pas gechecked worden als de transactie gecommit wordt. In feite kan dat prima en ik zie er de problemen niet echt van in.

Maar een transactie is natuurlijk niet zo gedefinieerd, ik zat daar fout. Inderdaad wordt er een log bijgehouden waarmee de veranderingen opgeslagen worden en terug te draaien zijn als dat nodig is. Als je een transactie commit, dan is het enige wat nog gebeurt dat er een denkbeeldig vlaggetje in de transactie-log wordt gezet dat de transactie successvol afgesloten is.

Het zou leuk zijn als er iets was waarmee je een ondeelbare reeks instructies kunt uitvoeren... Dwz. dat je van alles kunt doen, of het nou goed of fout is, als de uiteindelijke uitkomst maar klopt met de constraints. In feite past dit prima in het beeld van het Detached verwerken van data.

Maar goed, er is dus blijkbaar niet zoiets, dus zal het anders moeten. Ik denk dat ik dan maar weer terug moet grijpen naar een slimme SP. Of een extra veld introduceren.

[...]

Aan de PK zou je inderdaad niet moeten komen, maar ja, het gaat hier om iets als orderregels in een order systeem. De regels moeten uniek zijn op OrderNR en OrderRegelNR. Maar in mijn specifieke geval is de volgorde van de regels belangrijk en die wordt gegeven door het regelnummer. Regelnummers mogen dus niet twee keer voorkomen en dus een PK of OrderNR en OrderRegelNR.

De beste oplossing die ik dan kan bedenken is een extra veld OrderRegelVolgorde ofzo toe te voegen, waarmee ik snel met de volgorde kan goochelen. Helaas redundante informatie, maar de andere alternatieven lijken erger te zijn .

[tussentabel]

Tussentabellen maken het langzamer. En ze fragmenteren de database file of het geheugen (maar ik weet niet hoe erg de gevolgen van fragmenteren zijn bij databases).

[drm]

In mijn werkelijke situatie zijn het natuurlijk heel wat meer velden dan één. Als ik dat ga doen, dan wordt het al gauw niet goed onderhoudbaar meer.

Ik ben benieuwd of je dit nog gaat onderbouwen, want volgens mij is het een non-argument

[Verwijderd]

RetepV schreef op 12 november 2003 @ 10:06:
[...]
Ik zou voor het omwisselen natuurlijk een USF of SP kunnen maken, maar ook daarmee boet je in aan onderhoudbaarheid. Over een jaar is iedereen vergeten hoe het ook al weer werkt, en als er dan velden toegevoegd worden, worden die niet mee gekopieerd. En daar kom je natuurlijk pas een aantal maanden later achter omdat de nieuwe functionaliteit niet direkt zo veel gebruikt wordt. En dan is het kwaad geschiedt en zit je met data die niet meer klopt. Da's een wordt-case situatie die je ab-so-luut niet wilt hebben...

[Wel off-topic maar goed...]
Dit zou ik ook graag willen tegenspreken! de onderhoudbaarheid wordt namelijk juist veel groter! Als je het netjes opzet en je zorgt dat ALLE mutaties (insert, delete en update) ALTIJD via SP's gaan dan is dat zeer goed te onderhouden. Als je de SP's een duidelijke naam geeft dan zijn ze makkelijk terug te vinden.
Als je de tabel gaat uitbreiden met een aantal velden dan is het slechts een kwestie van een paar duidelijk gedefinieerde SP's aanpassen en voila... klaar! Kan niet fout gaan!
Je moet dan natuurlijk ook de code aanpassen die de SP's aanroept maar die plekken zijn ook makkelijk terug te vinden (zoek in al je code naar de SP naam).

Als je dit consequent doet dan wordt je applicatie zeer eenvoudig te onderhouden (in ieder geval de data access layer), door iedereen die een beetje verstand heeft van databases. Ook na een aantal jaren.

[ Voor 9% gewijzigd door Verwijderd op 12-11-2003 11:46 . Reden: typo's ]

[RetepV]

drm schreef op 12 november 2003 @ 11:23:
[...]
Ik ben benieuwd of je dit nog gaat onderbouwen, want volgens mij is het een non-argument

Zo... Ik ben er effe tussenuit geweest omdat ik aan mijn been geopereerd moest worden. Vorig jaar been gebroken, plaat+schroeven erin en die moesten er weer uit .

[onderhoudbaar]

Als ik simpelweg twee velden omwissel met twee (ofzo) queries, dan is dat toch veel onderhoudbaarder dan wanneer ik een stored procedure schrijf waarin ik (in mijn geval) zo'n 20 velden moet gaan zitten omwisselen?

[reactie op rmk]

Je hebt helemaal gelijk, maar als je dat gaat doen, dan kun je geen gebruik meer maken van de leuke grappen en grollen van je data access libraries van je programmeertaal (VB.NET bij mij). Daardoor verleng je de ontwikkeltijd, wat in dit geval (en neem van mij aan: in veruit de meeste gevallen, het gaat om geld namelijk ) zwaar ongewenst was.

[oplossing]

Uiteindelijk heb ik er gewoon maar voor gekozen om een extra veld 'regelnummer' toe te voegen, waarmee ik de volgorde van de records kan wijzigen. Wat jullie allemaal namelijk probeerden te zeggen is dat semantisch gezien een PK alleen maar voor identificatiedoeleinden gebruikt moet worden, terwijl in mijn geval de PK identificatie was EN de recordvolgorde aangaf. Dat moet je inderdaad gescheiden houden .

[algemeen]

Het Towers Of Hanoi probleem is eigenlijk al zo oud, dat ik had verwacht dat er tegenwoordig wel standaardoplossingen voor ingebouwd zouden worden. Maar helaas, SQL Server is blijkbaar niet slimmer dan mijn 8K Crystal Basic uit 1978. Dat is vooruitgang .

[ Voor 10% gewijzigd door RetepV op 28-11-2003 15:00 ]