<commit_before># Temporal database system on PostgreSQL using [updatable views][], [table inheritance][] and [INSTEAD OF triggers][]. [![Build Status][build-status-badge]][build-status] [![Dependency Status][deps-status-badge]][deps-status] [![Code Climate][code-analysis-badge]][code-analysis]

ChronoModel does what Oracle sells as "Flashback Queries", but with standard
SQL on free PostgreSQL. Academically speaking, ChronoModel implements a
[Type-2 Slowly-Changing Dimension][wp-scd-2].

All history keeping happens inside the database system, freeing application
code from having to deal with it. ChronoModel implements all the required
features to Ruby on Rails's ORM to leverage the database temporal structure
below.


## Design

The application model is backed by an updatable view in the default `public`
schema that behaves like a plain table to any database client.  When data in
manipulated on it, INSTEAD OF [triggers][] redirect the manipulations to
concrete tables.

Current data is hold in a table in the `temporal` [schema][], while history in
hold in a specular one in the `history` schema.  The latter [inherits][] from
the former, to get automated schema updates and for free and other benefits.

The current time is taken using [`current_timestamp`][], so that multiple data
manipulations in the same transaction on the same records always create a
single history entry (they are _squashed_ together).

[Partitioning][] of history is also possible: this design [fits the
requirements][partitioning-excl-constraints] but it's not implemented yet.

See [README.sql][] for a SQL example defining the machinery for a simple table.


## Active Record integration

All Active Record schema migration statements are decorated with code that
handles the temporal structure by e.g. keeping the triggers in sync or
dropping/recreating it when required by your migrations.

Data extraction at a single point in time and even `JOIN`s between temporal and
non-temporal data is implemented using sub-selects and a `WHERE` generated by
the provided `TimeMachine` module to be included in your models.

The `WHERE` is optimized using [GiST indexes][] on the `tsrange` defining
record validity. Overlapping history is prevented through [exclusion
constraints][] and the [btree_gist][] extension.

All timestamps are _forcibly_ stored in as UTC, bypassing the
`default_timezone` setting.


## Requirements

* Ruby >= 1.9.3
* Active Record >= 4.0
* PostgreSQL >= 9.3
* The `btree_gist` PostgreSQL extension


## Installation

Add this line to your application's Gemfile:

    gem 'chrono_model', github: 'ifad/chronomodel'

And then execute:

    $ bundle


## Configuration

Configure your `config/database.yml` to use the `chronomodel` adapter:

```yaml
development:
  adapter: chronomodel
  username: ...
```

## Schema creation

ChronoModel hooks all `ActiveRecord::Migration` methods to make them temporal
aware.

```ruby
create_table :countries, temporal: true do |t|
  t.string     :common_name
  t.references :currency
  # ...
end
```

This creates the _temporal_ table, its inherited _history_ one the _public_
view and all the trigger machinery. Every other housekeeping of the temporal
structure is handled behind the scenes by the other schema statements. E.g.:

 * `rename_table`  - renames tables, views, sequences, indexes and triggers
 * `drop_table`    - drops the temporal table and all dependant objects
 * `add_column`    - adds the column to the current table and updates triggers
 * `rename_column` - renames the current table column and updates the triggers
 * `remove_column` - removes the current table column and updates the triggers
 * `add_index`     - creates the index in both _temporal_ and _history_ tables
 * `remove_index`  - removes the index from both tables


## Adding Temporal extensions to an existing table

Use `change_table`:

```ruby
change_table :your_table, temporal: true
```

If you want to also set up the history from your current data:

```ruby
change_table :your_table, temporal: true, copy_data: true
```

This will create an history record for each record in your table, setting its
validity from midnight, January 1st, 1 CE. You can set a specific validity with
the `:validity` option:

```ruby
change_table :your_table, :temporal => true, :copy_data => true, :validity => '1977-01-01'
```

## Selective Journaling

By default UPDATEs only to the `updated_at` field are not recorded in the
history.

You can also choose which fields are to be journaled, passing the following
options to `create_table`:

  * `:journal => %w( fld1 fld2 .. .. )` - record changes in the history only when changing specified fields
  * `:no_journal => %w( fld1 fld2 .. )` - do not record changes to the specified fields
  * `:full_journal => true`             - record changes to *all* fields, including `updated_at`.<commit_msg>Add a README note about `change_table` and `change` migrations incompatibility (closes #14) [skip ci]<commit_after># Temporal database system on PostgreSQL using [updatable views][], [table inheritance][] and [INSTEAD OF triggers][]. [![Build Status][build-status-badge]][build-status] [![Dependency Status][deps-status-badge]][deps-status] [![Code Climate][code-analysis-badge]][code-analysis]

ChronoModel does what Oracle sells as "Flashback Queries", but with standard
SQL on free PostgreSQL. Academically speaking, ChronoModel implements a
[Type-2 Slowly-Changing Dimension][wp-scd-2].

All history keeping happens inside the database system, freeing application
code from having to deal with it. ChronoModel implements all the required
features to Ruby on Rails's ORM to leverage the database temporal structure
below.


## Design

The application model is backed by an updatable view in the default `public`
schema that behaves like a plain table to any database client.  When data in
manipulated on it, INSTEAD OF [triggers][] redirect the manipulations to
concrete tables.

Current data is hold in a table in the `temporal` [schema][], while history in
hold in a specular one in the `history` schema.  The latter [inherits][] from
the former, to get automated schema updates and for free and other benefits.

The current time is taken using [`current_timestamp`][], so that multiple data
manipulations in the same transaction on the same records always create a
single history entry (they are _squashed_ together).

[Partitioning][] of history is also possible: this design [fits the
requirements][partitioning-excl-constraints] but it's not implemented yet.

See [README.sql][] for a SQL example defining the machinery for a simple table.


## Active Record integration

All Active Record schema migration statements are decorated with code that
handles the temporal structure by e.g. keeping the triggers in sync or
dropping/recreating it when required by your migrations.

Data extraction at a single point in time and even `JOIN`s between temporal and
non-temporal data is implemented using sub-selects and a `WHERE` generated by
the provided `TimeMachine` module to be included in your models.

The `WHERE` is optimized using [GiST indexes][] on the `tsrange` defining
record validity. Overlapping history is prevented through [exclusion
constraints][] and the [btree_gist][] extension.

All timestamps are _forcibly_ stored in as UTC, bypassing the
`default_timezone` setting.


## Requirements

* Ruby >= 1.9.3
* Active Record >= 4.0
* PostgreSQL >= 9.3
* The `btree_gist` PostgreSQL extension


## Installation

Add this line to your application's Gemfile:

    gem 'chrono_model', github: 'ifad/chronomodel'

And then execute:

    $ bundle


## Configuration

Configure your `config/database.yml` to use the `chronomodel` adapter:

```yaml
development:
  adapter: chronomodel
  username: ...
```

## Schema creation

ChronoModel hooks all `ActiveRecord::Migration` methods to make them temporal
aware.

```ruby
create_table :countries, temporal: true do |t|
  t.string     :common_name
  t.references :currency
  # ...
end
```

This creates the _temporal_ table, its inherited _history_ one the _public_
view and all the trigger machinery. Every other housekeeping of the temporal
structure is handled behind the scenes by the other schema statements. E.g.:

 * `rename_table`  - renames tables, views, sequences, indexes and triggers
 * `drop_table`    - drops the temporal table and all dependant objects
 * `add_column`    - adds the column to the current table and updates triggers
 * `rename_column` - renames the current table column and updates the triggers
 * `remove_column` - removes the current table column and updates the triggers
 * `add_index`     - creates the index in both _temporal_ and _history_ tables
 * `remove_index`  - removes the index from both tables


## Adding Temporal extensions to an existing table

Use `change_table`:

```ruby
change_table :your_table, temporal: true
```

If you want to also set up the history from your current data:

```ruby
change_table :your_table, temporal: true, copy_data: true
```

This will create an history record for each record in your table, setting its
validity from midnight, January 1st, 1 CE. You can set a specific validity with
the `:validity` option:

```ruby
change_table :your_table, :temporal => true, :copy_data => true, :validity => '1977-01-01'
```

Please note that `change_table` requires you to use *old_style* `up` and
`down` migrations. It cannot work with Rails 3-style `change` migrations.

## Selective Journaling

By default UPDATEs only to the `updated_at` field are not recorded in the
history.

You can also choose which fields are to be journaled, passing the following
options to `create_table`:

  * `:journal => %w( fld1 fld2 .. .. )` - record changes in the history only when changing specified fields
  * `:no_journal => %w( fld1 fld2 .. )` - do not record changes to the specified fields
  * `:full_journal => true`             - record changes to *all* fields, including `updated_at`.
{'fraction_non_alphanumeric': 0.0794383149448345, 'fraction_numerical': 0.004914744232698095, 'mean_word_length': 4.617464788732394, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 21, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '1389083', 'n_tokens_mistral': 2875, 'n_tokens_neox': 2698, 'n_words': 1292}