[SQL] Welke velden zijn geüpdatet, verwijdert of toegevoegd
Het zou leuk zijn om weer te kunnen geven welke velden zijn geüpdatet, verwijdert of nieuw zijn toegevoegd.
Ik heb een script wat draait in crontab en nieuwe informatie ophaalt. In deze data kunnen gegevens verdwenen zijn, toegevoegd zijn of geüpdatet zijn. Het script verwijdert alle gegevens uit de tabel en voegt deze opnieuw toe.
Nou stuur ik me zelf wel een mail wanneer deze taak gedaan is en hoeveel rijen er zijn verwijdert en zijn toegevoegd, maar dat zegt verder niks.
Hoe zou ik kunnen achterhalen welke nieuw, verdwenen of bijgewerkt zijn? Het zou dan ook nog leuk zijn om te zien welke velden bijgewerkt zijn. Iets met een tijdelijke tabel misschien?
Edit:
Zoiets als dit maar dat is me niet heel duidelijk
Zoiets als dit maar dat is me niet heel duidelijk
Bvd!
Gewijzigd op 28/10/2016 21:35:59 door Michael -
Maar: wat is de reden dat je dit wilt kunnen zien? Vanuit daar kun je verder kijken naar een geschikte oplossing.
CREATE TABLE log_tabel (row_id INT, date_created TIMESTAMP, row_data HSTORE);
INSERT INTO log_tabel (row_id, date_created, row_data) SELECT CURRENT_TIMESTAMP, HSTORE(jouw_tabel.*) FROM jouw_tabel;
en om de nieuwe data te vergelijken met de oude kun je botweg de HSTORE van de nieuwe rij vergelijken met die van de oude:
SELECT HSTORE(jouw_tabel) - row_data FROM jouw_tabel INNER JOIN log_tabel ON jouw_tabel.id = log_tabel.row_id;
En hop, daar is je verschil.
Het leuke van HSTORE is dat het gewoon een rij accpeteert, dus als je een kolom toevoegt aan "jouw_tabel" dan verandert er niets aan de code die de hstore opslaat of aan de code die het verschil oplepelt.
Zelf gebruik ik de HSTORE voor auditing zodat ik van elke mutatie weet wanner hij gemaakt is en wat de inhoud van het record op dat moment geworden is. Dat is vooral belangrijk bij webshops waar mensen achteraf komen klagen dat ze "echt maar 3 stuks in het mandje hebben gelegd" of "hij gaf aan dat het 10 euro was, maar nu betaal ik 15".
Wat ik hiermee wil zeggen is wat Ben al stelt: Wat wil je waarom weten/bewaren? Breng je functionele eis in beeld en bouw dat in met de geschikte engine. Ook MySQL (inmiddels ook van Oracle) kent triggers en procedures en ook grotere databases in MySQL kunnen een uitstekende performance leveren. Probleem is wel vaak dat in het webwereldje meer kennis is van html en php en beduidend veel minder van SQL. Ik kom regelmatig tegen dat hele bakken data uit MySQL worden opgehaald en in php arrays worden verwerkt, gesorteerd en gevalideerd om 2 records te vinden.
Quote:
In de grootzakelijke markt is "ijzer" meestal geen probleem
Absoluut, maar zijn hebben ook DBA's in dienst om de databases te beheren en ze hebben Terabytes aan werkgeheugen en verwerken duizenden mutaties per seconde en foo en bar... kortom ze lijken in de verste verte niet op het bedrijf waar de gemiddelde poster op PHPHulp voor werkt.
De gemiddelde webtoepassing heeft veel meer last van de overhead die een grote tabel ze geeft dan van een insert/update trigger.
Maar, laten we wachten op een omschrijving van wat de TS precies nodig heeft, en wat hij *kan* implementeren. Als hij een script van derven heeft dan zal het b.v. niet eens mogelijk zijn om versiebeheer in de tabel zelf te doen zonder triggers en views... daar ontkom je toch niet aan.
Quote:
Maar: wat is de reden dat je dit wilt kunnen zien? Vanuit daar kun je verder kijken naar een geschikte oplossing.
Nieuwsgierigheid ;-) Het is niet nodig, maar leuk om uit te proberen en te weten hoe zoiets werkt. Daarnaast is het altijd leuk om statistieken te krijgen van wat er gebeurt is na een insert/update.
Zo kun je natuurlijk meer statussen mee geven. Je kan dus de status op 3 zetten als deze bewerkt is, al zie je dan niet wat er precies bewerkt is. Voor dat zou je echt voor de update de oude uitlezen en die in een aparte tabel zetten. (Ik werk niet met PostgreSQL etc, daar mijn mening op gebaseerd).
Wesley - op 04/11/2016 09:25:32:
Wat ik altijd doe is een soort status mee geven. 1 is actief, 2 is verlopen (als je iets met tijd hebt) en 9 is inactief (verwijderd). Deze records worden ook nooit verwijderd.
Dat doe ik inderdaad vaak ook, maar dan met aparte DATETIME-kolommen. Bijvoorbeeld een aparte kolom last_modified is sowieso wel handig als je HTTP-headers goed wilt zetten voor caching.
Wat je daarnaast kunt doen, is opvallende of kritieke databasemutaties loggen naar een apart logbestand. Denk bijvoorbeeld aan het automatisch importeren van productfeeds: daarbij kan het handig zijn als je ergens een alert kunt triggeren bij opvallende prijswijzingen.
Aan data alleen heb je niets; het wordt pas informatie als je het ergens voor gebruikt. De hamvraag voor de TS is dus vooral: wat wil je weten?
Quote:
Aan data alleen heb je niets; het wordt pas informatie als je het ergens voor gebruikt. De hamvraag voor de TS is dus vooral: wat wil je weten?
Ik wil graag weten hoe een tijdelijke tabel werkt. Hoe kan ik deze aanmaken, hoe kan ik de huidige tabel kopiëren naar de tijdelijke. Meestal voeg ik er ook wel een datum aan toe, maar in dit geval gaat het mij vooral om hoe ik een tijdelijke tabel kan gebruiken. Gewoon nieuwsgierigheid dus.
In het eerste voorbeeld heb ik een verjaardagskalender die elke maand wordt bijgewerkt. Alle records worden op inactief gezet (1 naar 0) en wordt dan d.m.v. 'ON DUPLICATE KEY UPDATE' weer op 1 gezet of er wordt een nieuwe record toegevoegd. Het zou leuk zijn om te weten welke dan nieuw zijn, welke verwijdert zijn (blijven op 0, maar er zijn dus meer records die 0 zijn, dus moet alleen de laatste hebben) en welke zijn geüpdatet.
Hoe zie ik het verschil of alleen het veld 'active' is geüpdatet of dat er ook andere velden zijn geüpdatet?
Een tweede voorbeeld zou kunnen zijn; Ik gebruik veel arrays en soms wordt het best lastig om al deze arrays nog aan elkaar te koppelen en te sorteren en te doorzoeken etc. Het zou dus ook wel een uitkomst zijn om de arrays in een tijdelijke tabel te gooien om deze makkelijker te kunnen presenteren?