[db] changes in db opmerken.

[Alarmnummer]

Voor een applicatie (searchengine platform) die ik ontwikkel is het voor db koppelingen noodzakelijk om te achterhalen welke veranderingen er zijn geweest in de db. Als bv een persoon is gewijzigd, toegevoegd of verwijderd, dan zal ik dat ook moeten verwerken in de index van de searchengine. Iedere keer alle records in de db afgaan is veel te duur qua tijd, dus er moet een andere oplossing komen waarbij je eenvoudig alle wijzigingen kunt achterhalen.

De minst opdringerige manier om dit te doen, lijkt mij om triggers te hangen aan tables. En de triggers wijzigingen naar een centrale table weg te laten schrijven. En dat je later deze wijziging table raadpleegd om de wijzigingen door te voeren.

Wij werken trouwens zelf veel met MsSql dus als die er faciliteiten voor heeft zou ik dit graag willen weten. En verder zullen we ook een db onafhankelijke oplossing nodig zijn (voor het geval we niet met MsSql kunnen werken) . Dus als iemand hier ervaring mee heeft zou ik dat graag willen weten..

[ Voor 19% gewijzigd door Alarmnummer op 13-04-2005 12:50 ]

[curry684]

Is het niet veel eenvoudiger om de table Person door een class PersonManager in je datalayer te laten beheren, en die class vervolgens OnPersonModified events etc. de wereld in te laten sturen waar geinteresseerde listeners zoals de index-updater zich op kunnen subscriben?

[Alarmnummer]

curry684 schreef op woensdag 13 april 2005 @ 12:50:
Is het niet veel eenvoudiger om de table Person door een class PersonManager in je datalayer te laten beheren, en die class vervolgens OnPersonModified events etc. de wereld in te laten sturen waar geinteresseerde listeners zoals de index-updater zich op kunnen subscriben?

Nope.. want ik heb geen/niet altijd controle over de applicaties die mutaties in de db doorvoeren. Het kan gerust een ASP/PHP webapp zijn die de db vult.

[ Voor 8% gewijzigd door Alarmnummer op 13-04-2005 12:52 ]

[curry684]

Dan kun je alsnog je database securitywise dichttimmeren en alle access verplicht via een SOAP-interface op je datalayer laten lopen

[Alarmnummer]

curry684 schreef op woensdag 13 april 2005 @ 12:54:
Dan kun je alsnog je database securitywise dichttimmeren en alle access verplicht via een SOAP-interface op je datalayer laten lopen

Wat moet ik hier nu op zeggen. Dacht je nou echt dat het mogelijk was om zomaar bestaande applicaties over hoop te gooien omwille van zoekfunctionaliteit? Het is dus geen optie om in de andere lagen iets aan te gaan passen. Ga hierover aub ook niet verder discussieeren.

[ Voor 5% gewijzigd door Alarmnummer op 13-04-2005 12:57 ]

[Michali]

Dan lijken triggers de enige optie. Ik weet alleen niet of MsSQL dat ondersteund.

Je zou anders ook een extra veld kunnen aanmaken en daarin bijhouden wanneer een record voor het laatst is bewerkt. Maar dan moet je dit ook doorvoeren in andere apps, wat dus niet mogelijk is.

[ Voor 10% gewijzigd door Michali op 13-04-2005 13:09 ]

[whoami]

Michali schreef op woensdag 13 april 2005 @ 13:08:
Dan lijken triggers de enige optie. Ik weet alleen niet of MsSQL dat ondersteund.

Tuurlijk ondersteunt MS SQL triggers.

[ Voor 71% gewijzigd door whoami op 13-04-2005 13:13 ]

[FendtVario]

Ja, maar zijn die ook in staat om een bericht naar je applicatie te sturen. De enige database die ik ken die dat doet is Interbase (Firebird).

@TS: alle wijzigingen bijhouden in een andere tabel; ga je dan met een interval steeds een query naar die tabel doen om wijzigingen te controleren?

[curry684]

Alarmnummer schreef op woensdag 13 april 2005 @ 12:56:
[...]

Wat moet ik hier nu op zeggen. Dacht je nou echt dat het mogelijk was om zomaar bestaande applicaties over hoop te gooien omwille van zoekfunctionaliteit? Het is dus geen optie om in de andere lagen iets aan te gaan passen. Ga hierover aub ook niet verder discussieeren.

Chill, je had nergens gezegd dat er al bestaande applicaties waren, dus zo aggressief hoeft het niet

[frickY]

Je kunt toch gewoon een extra kolom aan je tabellen toevoegen waarin je bijhoud wanneer her record voor het laatst gewijzigd is?
In MySQL wordt een veld van het type TIMESTAMP automatisch aangepast naar de datum van aanpassing wanneer er een veld van het record wijzigt.

Bij een update van je zoekmachine-index selecteer je alleen alle records met een timestamp na de vorige indexering.

[Alarmnummer]

curry684 schreef op woensdag 13 april 2005 @ 13:21:
[...]
Chill, je had nergens gezegd dat er al bestaande applicaties waren, dus zo aggressief hoeft het niet

Hmmm niet zozeer agressief, maar discussies waar ik niet in ben geinteresseerd stop zetten. En zie verder:

Alarmnummer
Nope.. want ik heb geen/niet altijd controle over de applicaties die mutaties in de db doorvoeren. Het kan gerust een ASP/PHP webapp zijn die de db vult.

[Alarmnummer]

Ik zie tot zover dus 1 oplossing:
trigger aan de table hangen die de veranderingen wegschrijft.

De info kan naar 2 (misschien wel meer) plekken geschreven worden:
1) in de table zelf (extra kolom)
2) in een centrale table.

Het voordeel van de laatste aanpak lijkt me dat je sneller terug kunt vinden wat er gewijzigd is. En dat het net iets minder opdringerig is (in het db model) dan een extra kolom aanmaken.

Weet iemand of MsSql nog een eigen oplossing heeft?

[ Voor 9% gewijzigd door Alarmnummer op 13-04-2005 14:05 ]

[mulder]

Ik roep maar wat hoor: maar het transaction log? Daar worden toch alle veranderingen bijgehouden, of is dat niet betrouwbaar genoeg?

[Alarmnummer]

Don Facundo schreef op woensdag 13 april 2005 @ 14:06:
Ik roep maar wat hoor: maar het transaction log? Daar worden toch alle veranderingen bijgehouden, of is dat niet betrouwbaar genoeg?

Het is niet bedrijfs kritisch. Als er een change niet wordt opgemerkt dan zal een searchengine daar niet aan dood gaan. Verder is de index altijd verouderd tov de db, dus het is niet zo`n probleem.

Het zou een oplossing zijn, alhoewel ik niet weet in hoeverre zo`n transactionlog db specifiek is.

[ Voor 8% gewijzigd door Alarmnummer op 13-04-2005 14:09 ]

[RobIII]

Je kan de last modified date per record opvragen als ik me niet vergis (en anders knutsel je 'm er bij en werk je 'm bij met een trigger).
Dan kun je makkelijk een "select from tabel where lastchange > lastindexed" doen en de gewijzigde records dus ophalen. Of denk ik nu te simpel

[ Voor 8% gewijzigd door RobIII op 13-04-2005 14:27 ]

[Verwijderd]

Ik gebruik zelf inderdaad die 'ranzige' oplossing: triggers. Wel automatisch gegenereerd, maar dat spreekt vanzelf...
Die trigger doet een 'if update(<veldnaam>)' check en een check of de waarden uit deleted en inserted verschillen, en zo ja, dan roept 'ie een kleine stored proc aan die de changes_log tabel bijwerkt:

SQL:

CREATE procedure ADD_CHANGES_LOG
  (
   @PRIMARY_KEY char(20),
   @USER char(8),
   @FIELD_NAME varchar(30),
   @OLD_VALUE varchar(255),
   @NEW_VALUE varchar(255),
   @RESULT int OUTPUT
  )
as
begin
  declare @num int,
          @log_date DATETIME,
          @log_time char(5)

  SET @log_date = getdate()
  SET @log_time = convert(char(5), @log_date, 8)
  SET @OLD_VALUE = substring(@OLD_VALUE, 1, 50)
  SET @NEW_VALUE = substring(@NEW_VALUE, 1, 50)
  exec GEN_ID 'GEN_CHANGES', 1, @num output
  insert into CHANGES_LOG
    (SEQUENCE, PRIMARY_KEY, HC_DATE, HC_TIME, HC_USER, FIELD_NAME, OLD_VALUE, NEW_VALUE)
  values
    (@num, @PRIMARY_KEY, @log_date, @log_time, @USER, @FIELD_NAME, @OLD_VALUE, @NEW_VALUE)
  SET @RESULT = @num
end

Werkt uitstekend, en performance is ook niet verkeerd. Zelfs niet bij 50+ users die druk aan het updaten zijn.

Die call naar die GEN_ID stored proc is overigens een soort van workaround voor databases die geen autoincrement velden kennen (Interbase bijvoorbeeld). Op deze manier hou ik 't zelf een beetje database onafhankelijk.

En nee, volgens mij heeft MSSQL hier zelf geen oplossing voor.

[Verwijderd]

Volgens mij kun je dit het beste oplossen door gebruik te maken van replicatie...

Laat alle mutaties repliceren naar een andere DB.
Bij voorkeur op een andere server, slecht idee om rapportage in je productieomgeving te doen...

Je hebt dan ook de mogelijkheid om efficiëntere indexen aan te leggen voor je zoek/rapportage functionaliteit.

edit:
Op deze manier hoef je dus geen triggers te gebruiken, alleen de replicatie mogelijkheden van SQL Server

[ Voor 15% gewijzigd door Verwijderd op 13-04-2005 14:35 ]

[lier]

Alarmnummer schreef op woensdag 13 april 2005 @ 12:46:
Voor een applicatie (searchengine platform) die ik ontwikkel is het voor db koppelingen noodzakelijk om te achterhalen welke veranderingen er zijn geweest in de db. Als bv een persoon is gewijzigd, toegevoegd of verwijderd, dan zal ik dat ook moeten verwerken in de index van de searchengine. Iedere keer alle records in de db afgaan is veel te duur qua tijd, dus er moet een andere oplossing komen waarbij je eenvoudig alle wijzigingen kunt achterhalen.

Misschien wil je dit voor mij nog toelichten ?
Je maakt gebruik van 1 (of meerdere) database(s), waarvoor je na insert/update/delete de index wil laten bijwerken. Wat voor soort index is dit ? Hebben jullie wel indexen op de database staan ? Zijn de database indexen niet toereikend ?

[Alarmnummer]

lier schreef op woensdag 13 april 2005 @ 14:38:
[...]


Misschien wil je dit voor mij nog toelichten ?
Je maakt gebruik van 1 (of meerdere) database(s), waarvoor je na insert/update/delete de index wil laten bijwerken. Wat voor soort index is dit ?

Lucene
Een lowlevel searchengine voor Java.

Hebben jullie wel indexen op de database staan ?

Dat is niet interessant. We gaan de db om verschillende redenen met Lucene integreren. Gebruikers kunnen aan documenten (word,pdf, html etc) die geindexeerd worden metadata toevoegen. Deze metadata wil ik op een veilige plek opslaan en dat is gewoon in een db. Een keer in de zoveel tijd moet in de db gekeken worden naar wijzigingen in de metadata (aangezien gebruikers ook metadata kunnen veranderen) en kan dit meegenomen worden in de index (van Lucene).

Een andere reden dat we het met Lucene gaan integreren is dat Lucene een echte searchengine is, en veel krachtiger dan wat indices in een db.

Zijn de database indexen niet toereikend ?

Precies.

[ Voor 4% gewijzigd door Alarmnummer op 13-04-2005 14:45 ]

[Verwijderd]

Verwijderd schreef op woensdag 13 april 2005 @ 14:34:
Op deze manier hoef je dus geen triggers te gebruiken, alleen de replicatie mogelijkheden van SQL Server

MSSQL's replicatie is vele malen zwaarder dan een paar simpele triggertjes waarmee je een changes log tabelletje bijwerkt...

[Verwijderd]

Verwijderd schreef op woensdag 13 april 2005 @ 14:54:
[...]

MSSQL's replicatie is vele malen zwaarder dan een paar simpele triggertjes waarmee je een changes log tabelletje bijwerkt...

Inderdaad, maar je verkracht je DB tenminste niet.

TS wil nu z'n DB gaan aanpassen om functionaliteit te verkrijgen die je beter helemaal kunt scheiden van je productie omgeving...

[Verwijderd]

Om dat nou meteen "verkrachten" te noemen...
Je voegt een klein stukje functionaliteit toe aan je productie omgeving ten behoeve van in dit geval een search engine. Wat is daar mis mee?

[Alarmnummer]

Verwijderd schreef op woensdag 13 april 2005 @ 14:57:
[...]
Inderdaad, maar je verkracht je DB tenminste niet.

Ik zou het niet 1 2 3 verkrachten willen noemen. De structuur en werking van de db blijft hetzelfde. Alleen zit er een trigger aan die wijzigingen wegschrijft.

Ik wil trouwens niet zeggen dat ik nu meteen een bepaalde oplossing ga kiezen. Ik wil een aantal alternatieven naast elkaar kunnen leggen en afhankelijk van de situatie daar het beste alternatief bij kiezen.

[whoami]

Mjah, je moet wel weten dat, als je replicatie opzet op een database in SQL Server, je niet zomaar wijzigingen kunt aanbrengen in de structuur van die DB (mocht dat al nodig zijn).
Dat moet je dan speciale SP's doen, ofwel moet je dan de replicatie afbreken, wijzigingen doen aan de structuur, en daarna de replicatie opnieuw opzetten.

Een trigger toevoegen vind ik nu ook niet het 'verkrachten van een DB'.

[P_de_B]

whoami schreef op woensdag 13 april 2005 @ 15:40:

Een trigger toevoegen vind ik nu ook niet het 'verkrachten van een DB'.

Zeker niet voor genoemde functionaliteit, die m.i. gewoon in een trigger thuishoort. Een trigger is niet per definitie slecht.

offtopic:
@ eFBe hieronder: Ulrumse

dank je

[ Voor 13% gewijzigd door P_de_B op 13-04-2005 16:07 ]

[EfBe]

Je kunt ook nog met een datetime field werken in iedere table. Een trigger update dan dat field en je houdt zelf in de search engine bij wanneer de laatste re-scan gedaan is. Daarna doe je een select * from table where lastupdatedfield > @lastupdated en klaar ben je.

offtopic:
Heee, P_de_B: gefeliciteerd met die nieuwe zoutkampster!

[ Voor 17% gewijzigd door EfBe op 13-04-2005 15:57 ]

[Verwijderd]

Ok ok, sorry "verkrachten" is misschien wat te sterk uitgedrukt...

Ik vind het zelf niet niet echt lekker om triggers te gebruiken omdat de database daardoor complexer wordt en er dus meer mis kan gaan.

Een trigger gebruiken om data bij te werken die gebruikt word door een search-engine vind ik al helemaal niet netjes. In die situatie zou ik er dan eerder voor kiezen om de data te repliceren naar een andere server en daar lekker op te gaan zoeken.

Replicatie heeft ook nadelen en ik kan me voorstellen dat TS hier kiest voor de Quick and Dirty oplossing, maar helemaal ideaal vind ik het niet...