Upgrade to Pro — share decks privately, control downloads, hide ads and more …

An overview of bitemporality

Akos Krivachy
February 05, 2016

An overview of bitemporality

In this talk we'll quickly look through, first unitemporal and then bitemporal database versioning and auditing mechanism that allows active querying of the audit logs.

Akos Krivachy

February 05, 2016
Tweet

More Decks by Akos Krivachy

Other Decks in Technology

Transcript

  1. Guns don't kill people -- a lack of audit logs

    kills people A presentation with a click-bait title about temporality by Akos Krivachy
  2. A story about Bob ☻/ This is bob. Copy and

    paste him so he can take over YouTube. /▌ /\
  3. Bob is a secret agent from YouTube and gets paid

    after every 1M views he’s a top comment
  4. Let’s improve this id balance valid_from valid_to 1 1000 01/01

    01/02 2 2000 01/02 01/03 3 3000 01/03 ∞ Unitemporal Our latest “open” record The valid timeline can be thought as the “business” timeline as in what happened in the real world
  5. Report emailed to Stewie from time-to-time id balance valid_from valid_to

    1 1000 01/01 01/02 2 2000 01/02 01/03 3 3000 01/03 ∞ SELECT * FROM money WHERE ‘01/02’ BETWEEN valid_from AND valid_to; To query this row we filter on the valid timeline
  6. With fake views id balance valid_from valid_to 1 1000 01/01

    01/02 2 2000 01/02 01/03 3 3000 01/03 ∞
  7. Without fake views id balance valid_from valid_to 1 500 01/01

    01/02 2 600 01/02 01/03 3 700 01/03 ∞ With only a valid timeline we are forced to overwrite the balances: a destructive edit
  8. Reports are still sent to Stewie id balance valid_from valid_to

    1 500 01/01 01/02 2 600 01/02 01/03 3 700 01/03 01/04 4 800 01/04 ∞ Bob sends off the next numbers to Stewie, he only reviews them later
  9. At this point, due to the lack of audit logs,

    we can’t answer Stewie’s question with data, only memories :)
  10. Let’s go back and fix this id balance valid_from valid_to

    transact_from transact_to 1 1000 01/01 01/02 01/01 ∞ 2 2000 01/02 01/03 01/02 ∞ 3 3000 01/03 ∞ 01/03 ∞ VALID TIMELINE == TRANSACT TIMELINE in “normal” cases Let’s add a second timeline, the transact or “system” timeline. This is what the IT systems see of the world. On inserts they always use NOW() for transact_from on closing out records they always use NOW() for transact_to.
  11. Queries now need to filter on valid AND transact id

    balance valid_from valid_to transact_from transact_to 1 1000 01/01 01/02 01/01 ∞ 2 2000 01/02 01/03 01/02 ∞ 3 3000 01/03 ∞ 01/03 ∞ SELECT * FROM money WHERE ‘01/02’ BETWEEN valid_from AND valid_to AND ‘01/02’ BETWEEN transact_from AND transact_to;
  12. Amendments to the past (happening on 01/04) id balance valid_from

    valid_to transact_from transact_to 1 1000 01/01 01/02 01/01 01/04 2 2000 01/02 01/03 01/02 01/04 3 3000 01/03 ∞ 01/03 01/04 4 500 01/01 01/02 01/04 ∞ 5 600 01/02 01/03 01/04 ∞ 6 700 01/03 ∞ 01/04 ∞
  13. Close out records: essentially delete id balance valid_from valid_to transact_from

    transact_to 1 1000 01/01 01/02 01/01 01/04 2 2000 01/02 01/03 01/02 01/04 3 3000 01/03 ∞ 01/03 01/04 4 500 01/01 01/02 01/04 ∞ 5 600 01/02 01/03 01/04 ∞ 6 700 01/03 ∞ 01/04 ∞ First we close out records by setting their transact_from to NOW()
  14. Insert new records id balance valid_from valid_to transact_from transact_to 1

    1000 01/01 01/02 01/01 01/04 2 2000 01/02 01/03 01/02 01/04 3 3000 01/03 ∞ 01/03 01/04 4 500 01/01 01/02 01/04 ∞ 5 600 01/02 01/03 01/04 ∞ 6 700 01/03 ∞ 01/04 ∞ Insert new records with the amended data. These will be considered the open records “as of” 01/04
  15. Use old valid time id balance valid_from valid_to transact_from transact_to

    1 1000 01/01 01/02 01/01 01/04 2 2000 01/02 01/03 01/02 01/04 3 3000 01/03 ∞ 01/03 01/04 4 500 01/01 01/02 01/04 ∞ 5 600 01/02 01/03 01/04 ∞ 6 700 01/03 ∞ 01/04 ∞ Very important that we use the old valid times, therefore looking like an ammendment.
  16. Amendments to the past (happening on 01/04) id balance valid_from

    valid_to transact_from transact_to 1 1000 01/01 01/02 01/01 01/04 2 2000 01/02 01/03 01/02 01/04 3 3000 01/03 ∞ 01/03 01/04 4 500 01/01 01/02 01/04 ∞ 5 600 01/02 01/03 01/04 ∞ 6 700 01/03 ∞ 01/04 ∞
  17. 01 02 03 04 01 02 03 04 1000 2000

    3000 valid transact New records are at this point
  18. 01 02 03 04 01 02 03 04 3000 valid

    transact 1000 2000 <= valid is 2.5 & transact is 2.5 <= valid is 2.5 & transact is 1.5 Querying for data with different transact constraints
  19. 01 02 03 04 01 02 03 04 1000 2000

    valid transact 3000
  20. 01 02 03 04 01 02 03 04 1000 2000

    3000 valid transact 500 600 700 Amendments can be visualized like this
  21. 01 02 03 04 01 02 03 04 1000 2000

    3000 valid transact 500 600 700 Queries with different transact constraints: we can actively query for the “old” data and the “new” data.
  22. Morale of the story: Make sure you know that the

    data you destroy will never be needed again. If it needs to be actively queried consider temporality.
  23. Some specifics This was just an intro to temporality to

    understand the concept. Lots of databases support temporal columns/auditing out of the box: MSSQL, Oracle, DB2, Teradata, Postgres SQL:2011 standard has defined features for temporal support There are other application level solutions as well: Hibernate Envers, event sourcing, CQRS, etc. Home-rolling bitemporality can become complicated and should be considered carefully.