7 maggio 2012 · 7 min di lettura

ChronoModel: viaggiare nel tempo con PostgreSQL

• ruby
• postgresql
• rails
• open-source

Stiamo costruendo un CRM all’IFAD — un’agenzia specializzata delle Nazioni Unite a Roma — e uno dei requisiti chiave sono i dati temporali. Dobbiamo sapere come appariva un record in qualsiasi momento del passato. Qual era il budget di questo progetto il 15 marzo? Quando e’ cambiato l’indirizzo di questo beneficiario? Chi ha approvato cosa, e come appariva il record in quel momento?

Stavo prototipando un approccio basato sullo schema di PostgreSQL — viste, regole, table inheritance — e funzionava. Poi Amedeo, il mio capo, ci ha dato un’occhiata e ha detto: “Questa roba non deve vivere dentro il CRM. Fanne un framework riusabile.”

Aveva ragione. Il pattern temporale non ha niente a che fare con la logica del CRM. Va in una gem.

Cosi’ ho avuto cinque giorni di concentrazione totale, e oggi rilascio ChronoModel — un’estensione ActiveRecord che da’ ai tuoi modelli capacita’ temporali complete su PostgreSQL. Quello che Oracle ti vende come Flashback Queries facendoti pagare fior di quattrini, noi lo facciamo con SQL standard su Postgres 9.0+.

L’idea

La risposta da manuale per i dati temporali e’ una Slowly Changing Dimension Type 2 — mantieni uno storico di ogni riga con timestamp di validita’, e interroghi quelli. Ogni vendor di database enterprise ha una soluzione proprietaria. PostgreSQL no. Ma PostgreSQL ti da’ tutti i mattoncini — viste, regole, table inheritance, indici GiST — e nessuno li aveva ancora assemblati in un pacchetto Rails-friendly. Fino ad oggi.

La mia scommessa e’ stata renderlo completamente trasparente per l’applicazione. Nessun cambiamento di schema nei modelli, nessun metodo di salvataggio speciale, nessuna tabella di storico da gestire a mano. Aggiungi temporal: true nella migration e include ChronoModel::TimeMachine nel modello, e tutto il resto succede dietro le quinte. Il codice esistente non cambia — acquisisce semplicemente la capacita’ di guardare nel passato.

Il punto cruciale e’ che tutto questo succede nel database, non nell’applicazione. Le regole PostgreSQL intercettano ogni scrittura e mantengono lo storico atomicamente. Non esiste il bug “mi sono dimenticato di chiamare save_with_history!”. Non ci sono race condition tra la scrittura della riga corrente e dell’entry storica. L’integrita’ referenziale e’ garantita dal database stesso — se la transazione va in commit, lo storico e’ consistente. Punto.

Quella trasparenza e’ anche la parte piu’ rischiosa del design, perche’ renderlo invisibile ad ActiveRecord significa entrare molto in intimita’ con gli internals di ActiveRecord. Ma ci arriviamo dopo.

L’architettura

ChronoModel usa tre schemi PostgreSQL che lavorano insieme:

• temporal — contiene le tabelle “correnti” reali
• history — contiene tabelle di storico che ereditano da quelle temporali, aggiungendo colonne valid_from, valid_to e recorded_at
• public — contiene viste aggiornabili che l’applicazione vede come tabelle normali

I tuoi modelli Rails puntano alle viste in public. Appaiono e si comportano esattamente come tabelle normali. Dietro le quinte, le regole PostgreSQL su quelle viste intercettano ogni INSERT, UPDATE e DELETE e li instradano nel posto giusto:

• INSERT: crea una riga in temporal (dato corrente) e una riga in history (con valid_from = now())
• UPDATE: chiude l’entry storica corrente (imposta valid_to = now()), ne apre una nuova, e aggiorna la tabella temporale
• DELETE: chiude l’entry storica e rimuove la riga temporale

La parte bella e’ che il codice della tua applicazione non cambia per niente. Le query vanno sulle viste public, che mostrano i dati correnti da temporal. Lo storico si accumula silenziosamente in history.

Ecco la struttura SQL completa per una tabella countries (dal reference completo dello schema):

create schema temporal;  -- i dati correnti vivono qui
create schema history;   -- i dati storici vivono qui

-- La tabella reale, nello schema temporal
create table temporal.countries (
  id   serial primary key,
  name varchar
);

-- La tabella storica EREDITA da quella temporale — quindi ha
-- tutte le stesse colonne, piu' i campi di tracciamento validita'.
-- Nessuna duplicazione di schema, nessun drift tra le colonne.
create table history.countries (
  hid         serial primary key,
  valid_from  timestamp not null,
  valid_to    timestamp not null default '9999-12-31',
  recorded_at timestamp not null default now(),

  constraint from_before_to check (valid_from < valid_to),

  constraint overlapping_times exclude using gist (
    box(
      point( extract( epoch from valid_from), id ),
      point( extract( epoch from valid_to - interval '1 millisecond'), id )
    ) with &&
  )
) inherits ( temporal.countries );

-- Quello che l'applicazione vede: una semplice vista sui dati correnti
create view public.countries as select * from only temporal.countries;

Tre cose da notare:

1. inherits ( temporal.countries ) — la tabella storica eredita lo schema dalla tabella corrente. Aggiungi una colonna a temporal.countries, appare automaticamente in history.countries. Nessun drift nelle migration, mai.

2. select * from only temporal.countries — la keyword ONLY e’ cruciale. Senza, l’ereditarieta’ di PostgreSQL farebbe si’ che la vista restituisca righe sia dalla tabella temporale che da quella storica. ONLY la limita ai dati correnti.

3. La exclusion constraint — abuso degli indici geometrici GiST per prevenire entry storiche sovrapposte per lo stesso record. Ogni periodo di validita’ diventa un box nello spazio 2D (asse tempo x ID record). Due box si sovrappongono (&&) solo se condividono sia lo stesso ID che un intervallo temporale sovrapposto. Se qualcuno prova a inserire un’entry contraddittoria, PostgreSQL la rifiuta a livello di vincolo. Integrita’ temporale blindata usando indici spaziali. Sono irragionevolmente orgoglioso di questo hack.

Poi le regole rendono la vista scrivibile. Ecco la UPDATE (la piu’ interessante):

create rule countries_upd as on update to countries do instead (
  -- Chiudi l'entry storica corrente
  update history.countries
    set   valid_to = now()
    where id       = old.id and valid_to = '9999-12-31';

  -- Apri una nuova entry storica con i dati aggiornati
  insert into history.countries ( id, name, valid_from )
  values ( old.id, new.name, now() );

  -- Aggiorna la tabella corrente
  update only temporal.countries
    set name = new.name
    where id = old.id
);

Una regola, tre operazioni, una transazione. La sentinella '9999-12-31' marca l’entry attualmente valida. La gem genera tutto questo — gli schemi, le tabelle con ereditarieta’, la vista, le regole, gli indici, i vincoli — da una singola opzione temporal: true. Non scrivi mai questo SQL a mano.

L’integrazione Rails

Far funzionare tutto questo in modo trasparente con ActiveRecord ha richiesto… creativita’. L’adapter estende PostgreSQLAdapter e sovrascrive ogni metodo DDL — create_table, drop_table, rename_table, add_column, rename_column, change_column, remove_column, add_index, remove_index, e altri. Tutti controllano se la tabella e’ temporale e instradano le operazioni a entrambi gli schemi.

Dalla tua migration, e’ una sola opzione:

create_table :countries, temporal: true do |t|
  t.string :name
  t.string :code
  t.timestamps
end

Quel singolo temporal: true crea la tabella temporale, la tabella storica con ereditarieta’, la vista pubblica, tutte le regole, l’indice GiST e la exclusion constraint. Plug and play.

Poi nel tuo modello:

class Country < ActiveRecord::Base
  include ChronoModel::TimeMachine
end

E hai il viaggio nel tempo:

# Dati correnti — funziona esattamente come prima
Country.where(code: 'IT')

# Come appariva l'Italia il 1 gennaio 2010?
Country.as_of(Time.utc(2010, 1, 1)).find_by(code: 'IT')

# Storico completo di un record
italy = Country.find_by(code: 'IT')
italy.history  # => tutte le versioni, con valid_from/valid_to

# Le associazioni temporali si propagano automaticamente
italy.as_of(1.year.ago).projects  # caricati anche loro a quella data

Ho anche aggiunto il supporto CTE (Common Table Expressions) al query builder di ActiveRecord, perche’ Rails 3 non ha le clausole WITH e le query as_of ne hanno bisogno. Questo ha richiesto una patch al visitor PostgreSQL di Arel per emettere SQL corretto.

La verita’ scomoda

Diciamocelo chiaramente: l’hack che fa funzionare tutto. Per iniettare l’adapter, faccio cosi':

silence_warnings do
  ActiveRecord::ConnectionAdapters::PostgreSQLAdapter = ChronoModel::Adapter
end

Si’, sostituisco l’intera costante dell’adapter PostgreSQL. E patcho ActiveRecord::Associations::Association per propagare l’as_of_time attraverso le associazioni temporali.

Funziona. E’ brutto. Andra’ ripulito prima di arrivare a una 1.0. Ma funziona, e funziona in modo trasparente — il tuo codice esistente non cambia.

Cinque giorni

Trentasei commit dal README iniziale a questo rilascio. Nessun test per ora — arriveranno, promesso. L’SQL e’ solido (ho testato l’approccio a livello di schema manualmente per settimane prima di scrivere la gem), ma il lato Ruby ha bisogno di spec fatte come si deve.

Se lavori con PostgreSQL e Rails e hai mai avuto bisogno di query storiche, audit trail, o reportistica temporale: gem install chrono_model e prova. Il sorgente e’ su GitHub, e la documentazione API copre ogni metodo pubblico. Issue, PR e lamentele sono benvenute.

Viaggiare nel tempo non dovrebbe costare una licenza Oracle.