PDO transaction prepared statements
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
<?php
$sql = '
INSERT
INTO ' . TABLE_PREFIX . 'profile
(
naam
)
VALUES
(
:naam
)
';
$stmt = $db->prepare($sql);
$stmt->bindParam(':naam', $_POST['gegevens']['naam'], PDO::PARAM_STR);
// Vanaf hier willen we alle wijzigingen kunnen terugrollen
$db->beginTransaction();
try {
$stmt->execute();
$sql = '
INSERT
INTO ' . TABLE_PREFIX . 'application
(
profile_id
)
VALUES
(
' . $db->lastInsertId() . '
)
';
$db->query($sql);
$db->commit();
} catch (PDOException $e) {
$db->rollBack();
}
?>
$sql = '
INSERT
INTO ' . TABLE_PREFIX . 'profile
(
naam
)
VALUES
(
:naam
)
';
$stmt = $db->prepare($sql);
$stmt->bindParam(':naam', $_POST['gegevens']['naam'], PDO::PARAM_STR);
// Vanaf hier willen we alle wijzigingen kunnen terugrollen
$db->beginTransaction();
try {
$stmt->execute();
$sql = '
INSERT
INTO ' . TABLE_PREFIX . 'application
(
profile_id
)
VALUES
(
' . $db->lastInsertId() . '
)
';
$db->query($sql);
$db->commit();
} catch (PDOException $e) {
$db->rollBack();
}
?>
Wat je je wel kunt afvragen, is of het mislukken van die tweede INSERT zo erg is. Je wilt kennelijk een profiel toevoegen en met dat nieuwe profiel een applicatie toevoegen. Dat kun je ook splitsen in PHP. Met andere woorden: je datamodel bevat een soort één-op-één-afhankelijkheid die misschien niet per se nodig is. Of misschien zelfs een één-op-één-afhankelijk die eigenlijk gewoon in één tabel thuishoort.
Wat bedoel je hiermee? De auto_increment wordt wel opgehoogd, dus het is eerder toevoegen->error->verwijderen, dan voorbereiden->error->herstellen.
Waarom de keuze om try pas vanaf regel 19 te doen en niet bovenaan?
Als de tweede of derde, vierde, etc insert mislukt heeft de eerste ook niet zoveel zin. Het gaat namelijk om één formulier dat wordt opgeslagen. Ik verspreid het over meerdere tabellen omdat ik niet weet hoeveel rijen van alles wordt toegevoegd. En het ook mogelijk is dat de zelfde naam nog een 'application' toevoegt.
Is het gebruik van de last insert id wel goed dan? Want ik ga nou twijfelen door jouw reacties. Hoe doe jij dit? En misschien dat het makkelijker is deze in een koppeltabel te zetten, maar dat is denk ik een heel andere vraag en dan zou je het op de zelfde manier moeten doen.
Of het hergebruik van de ID verspreid over meerdere tabellen wel helemaal juist is, is zo moeilijk te beoordelen. Dat is een andere vraag; misschien kun je het inderdaad beter met een koppeltabel oplossen. Kun je iets van het datamodel laten zien?
Ik zou een transactie alléén in zijn geheel laten lukken/mislukken als het echt niet anders kan. Maar als de INSERTs eventueel wel gesplitst mogen worden, dan heb je niet per se een transactie nodig. Een INSERT ... ON DUPLICATE KEY ... is soms bijvoorbeeld een betere oplossing.
Ja dat klopt wel. Daar had ik zelf nog niet zo aan gedacht. Aan de andere kant, als er geen verbinding kan er ook niks geinsert worden.
>> Of het hergebruik
Hier een schets van m'n database
Volgens mij was een koppel inderdaad beter geweest. Dan kan ik ook makkelijker koppelen wanneer er 2 zelfde profielen zijn. Dat kan nu niet. Het is nu alleen om de boel bij elkaar te houden.
>> Ik zou een transactie
True, maar wat heb je aan halve data? Als de eerste 3 tabellen wel worden geinsert, maar de 4e gaat fout, wordt de 5e toch ook niet geinsert? Dus dan is de data incompleet.
Wat doet ON DUPLICATE KEY (UPDATE?) precies en wanneer zou je dit toepassen?
In PHP wordt dat "dus" vooral vaak verkeerd aangepakt in mappers, bijvoorbeeld een UserMapper. Dan gaat de ene methode eerst controleren of iets al bestaat voordat één van twee andere methoden respectievelijk een INSERT of een UPDATE kan uitvoeren. Dat is vaak nergens goed voor.
Guilty :) Dus INSERT...ON DUPLICATE KEY UPDATE is een controle of bijvoorbeeld een naam al bestaat. Zo ja, updaten, Zo nee dan inserten. Had ik dat maar eerder geweten.
Maar ik snap nog niet helemaal de link met de rollback, want in mijn geval wordt er één formulier ingevuld en opgeslagen. Zou ik dan een controle moeten doen of ingevulde waardes al bestaan in 'profile', dan updaten, anders inserten? Waar zou ik dan op moeten controleren, en wat moet ik updaten, want misschien wil de persoon wel 2 verschillende dingen opslaan... Je maakt het er niet makkelijker op :)
Wat vond je van het datamodel? Koppeltabel gebruiken?
Je kunt een INSERT ... ON DUPLICATE KEY UPDATE ... gebruiken om de data die je al hebt alvast op te slaan, zelfs als de gebruiker nog een paar schermen met foutmeldingen en vragen nodig heeft om het geheel af te ronden. Sla de ID op in een sessie en laat die als de DUPLICATE KEY het werk doen.
Je kunt het dus zien als alternatief voor een transactie waarbij echt alles in één keer goed of in één keer fout moet gaan.
De ene oplossing is flexibel, de andere rigide, dus het hangt ervan af wat je precies wilt bereiken.
Dat klinkt wel mooi :) Gebruik je die methode zelf ook? Alleen je kunt dan niet eenvoudig een 'rollback' doen lijkt me. Dus als een gebruiker het formulier niet kan afronden door een niet-menselijke fout, blijft er onvolledige data in de database. Of dit een probleem is een tweede.
Ja, ik gebruik inderdaad databasetabellen met incomplete gegevens. Een goed voorbeeld zijn online bestellingen: die gaan vaker fout dan goed, maar je hebt de incomplete orders van "shopping cart abandonment" nodig om te zien waar en waarom kopers afhaken.
Wel moet je dus twee dingen elders doen: bij een SELECT moet je er rekening mee houden dat data kunnen ontbreken (in de view en dergelijke) en om de zoveel weken of maanden moet je de database eens opschonen.
Ik dacht dat ik dat al redelijk had gedaan eigenlijk...
Misschien toch beter om het idee van rollback voor dit maar te laten vallen. Zolang de lege data geen probleem vormt zou het ook niet veel uit moeten maken (inderdaad in de view en zo controleren of de data er werkelijk is, maar dat doe ik eigenlijk uit automatisme al wel). Als je incomplete data voorbij ziet komen weet je wel gelijk dat er wat fout gaat en eventueel waar.
Wat schoon je precies op dan? Je wilt toch wel een historie van je aankopen? Controleer je dan of alles na jouw idee compleet is, zo niet, dan verwijderen? En dan ben je die mislukte-aankopen-historie wel kwijt?
Met sitestatistieken doe ik dat ook: na circa 18 maanden zijn ze niets meer waard, omdat een site (en eigenlijk half internet) na anderhalf jaar te ingrijpend is veranderd. Jaar-op-jaar-verschillen zijn wel belangrijk, bijvoorbeeld bezoeken rond Koningsdag in april vorig jaar versus april dit jaar, maar daarna wordt het al gauw appels met peren vergelijken.
Eigenlijk zou je er een back-up van moeten maken, "want je weet maar nooit", maar statistisch gezien heb je er in de praktijk meestal niets aan.
Daar heb je eigenlijk helemaal gelijk in :) Vaak is de opslag van dat soort statistieken niet groot, dus dan is het meer van 'laat maar staan'.
Hoe zou jij het datamodel normaliseren?
Voor data-analyse gebruik ik Excel en soms gewoon MySQL. Bijvoorbeeld man/vrouw-verschillen vinden is een kwestie van een simpele GROUP BY.
profile_id is het uniek auto_increment id van de tabel 'profile'. Er kunnen nu dus in principe meerdere zelfde profielen worden toegevoegd met een eigen id.
In jouw ene voorbeeld kon het dus niet (alles op één moment executen) en mijn manier (in de eerste post) is niet helemaal goed?
Is jouw tweede voorbeeld wel goed dan? De eerste executen om het id te krijgen, om vervolgens de rest voor te bereiden en als laatste 2 tm .. de executen. Of is dat nou niet meer van toepassing als we de rollback laten vervallen.
Die beslissingsregel bepaalt namelijk hoe je initiële transactie eruit ziet. Díé eerste "minimalistische" INSERTs moeten in hun geheel slagen of falen. Dit levert je de "minimale dataset" op met gegevens die je nodig hebt om de boel niet als een kaartenhuis te laten instorten.
Daarna kun je veel flexibeler op bepaalde onderdelen data toevoegen, zolang je maar de juiste ID bij de hand hebt. Dat kan eventueel ook dagen, weken, maanden later nog — wat jij nodig hebt.
Vergelijk het met het opbouwen van een profiel bij een sociaal netwerk, bijvoorbeeld LinkedIn of Google+. Je moet minimaal x, y en z opgeven voor een account. Maar daarna werken de meeste onderdelen ook goed met een profiel dat maar 60% of 85% compleet is. Je kunt het gebruikers dus ook uitleggen.
Waar je voor een specifieke applicatie nog een specifiek gegeven nodig hebt, stel je een vraag: "Voor dit ene moet je wel zus-en-zo nog instellen." En dat kan dan eventueel nog een INSERT ... ON DUPLICATE KEY UPDATE ... zijn als de bijbehorende tabel in eerste instantie leeg kan zijn.
Dit zijn o.a. de persoonsgegevens. Ze hoeven allebei niet 100% gevuld te zijn, maar wel het grote deel en die moeten slagen anders heeft de rest ook geen zin. De rest van de tabellen kan erg uiteen lopen dus dat is niet direct verplicht.
Je kunt het niet vergelijken met een profiel, maar ik snap je punt. In dit geval gaat het om een formulier zonder login o.i.d. Ik zit dus nu ook te denken of ik nog een random key moet toevoegen aan 'profile' waar ik op terug kan vallen om bijvoorbeeld een linkje te sturen waarin men het formulier kan aanpassen (Dat kan dan weer mooi met ON DUPLICATE UPDATE).