dwyl / dwyl/ecto-postgres-pubsub-spike

SPIKE: Automatically insert records in address_history table

Open
#1 44 comments 0 reactions 1 assignee Claimed by @RobStallion View on GitHub
good first issue help wanted in-progress
Dominant language
Elixir
Stars
2
Forks
0
PR merge metrics
No merged PRs in 30d

Description

With reference to the Address Book example outlined in https://github.com/dwyl/phoenix-ecto-append-only-log-example/issues/27 we want to pursue **Option 3** which is having an `address_history` table where all `address` records get backed up when they are inserted or updated.

# Spike Tasks ✅

## Basic 👍

+ [x] Create a new phoenix application called `app`
+ [x] Create an `address` schema with HTML forms:
```sh
mix phx.gen.html Accounts Address addresses name:string address_line_1:string address_line_2:string city:string postcode:string tel:string
```
+ [x] _manually_ create the `address_history` table/schema using the `mix phx.gen.schema` command:
```sh
mix phx.gen.schema Address addresses name:string address_line_1:string address_line_2:string city:string postcode:string tel:string
```
+ [x] Manually insert a record into the `address` table using the browser-based form.
+ [x] Confirm the record was inserted. ✅
+ [x] Write a custom function that inserts the data into `address_history` each time a record is inserted into `address`. `insert_address_history()` needs to know the `id` of the address record so it needs to happen _after_ the `Repo.insert()`
+ [x] `insert_address_history()` should be invoked when an address record is _updated_ such that any _update_ to the record is saved to `address_history`

id| name | address_line_1 | address_line_2 | city | postcode | tel | inserted_at | updated_at |
--|-------|--------------|--------------|-----------|--------|---------|--------|----|
1 | Thor | 1 Sunset Blvd | 3rd Floor | LA | 90210 | 98765 | 2019-03-28 22:01:42 | 2019-03-28 22:01:42 |

`address_history` would be:

id | ref_id | name | address_line_1 | address_line_2 | city | postcode | tel | inserted_at |
--|--|-------|--------------|--------------|-----------|--------|---------|--------|
1 | 1 | Thor | The Hall | Valhalla | Asgard | AS1 3DG | 123123 | 2019-02-25 10:01:42 |
2 | 1 | Thor | 177A Bleecker Street | c/o Dr. Strange | New York | NY 10012 | 98765 | 2019-03-14 10:01:42 |
3 | 1 | Thor | 1 Sunset Blvd | 3rd Floor | LA | 90210 | 98765 | 2019-03-28 22:01:42 |

+ [x] Once this functionality is achieved **`commit`** the code with reference to this issue and **`git push`** so there is a *snapshot* of the working code.
+ [x] Write some tests for the insertion of the record into _both_ tables. ✅

## Advanced 🚀

+ [x] Create a PostgreSQL Pub/Sub `LISTEN` (Elixir) function for the `insert` and `update` events on the `address` table and log this event to std.out
+ [x] Create function to insert data received in the `LISTEN` event into the `address_history` table.
+ [x] Comment out the _manual_ `insert_address_history()` line in `address` insert/update function(s).
+ [ ] _Confirm_ that tests created above still pass. (_we may need to wait for the trigger to run ..._)

## Bonus Level! 🥇

+ [x] Create **`unique`** constraint on the `address.tel` (_telephone_) field to ensure that no two records can be inserted into `address` with the same `tel`.
+ [x] Write a test that attempts to insert two records with the _same_ `tel` number.
(_**Note**: you can write this test **`before`** adding the `unique` constraints_ ... `#TDD` ... 😉)
+ [x] There should be no **`unique`** constraints in the `address_history` because _by definition_ there will be "_duplicate_" records.

## Boss Level 👾

The purpose of this level/section is to _automatically_ update the `address_history` table whenever anything is updated in the `address` (_primary_) table. Nobody has time/patience to _manually_ update both tables and keep them in sync, so we need to figure out if we can automate it without any "_magic_".

+ [x] _Manually_ add `country` field to `address` schema using `mix ecto.gen.migration`
+ [x] Insert a `IO.inspect` into the `address > schema` definition to test if we can output to std.out in the context of a `schema` definition. e.g:
```elixir
defmodule Append.Address do
use Ecto.Schema

@timestamps_opts [type: :naive_datetime_usec]
schema "addresses" do
field(:address_line_1, :string)
field(:address_line_2, :string)
field(:city, :string)
field(:name, :string)
field(:postcode, :string)
field(:tel, :string)

timestamps()
IO.inspect("hello from address schema definition")
end
end
```
+ [x] If that `IO.inspect` works, try defining a function and executing the function from inside the `schema` create a function `hello` that only IO.inspects and invoke it from inside the `schema`

```elixir
defmodule Append.Address do
use Ecto.Schema

@timestamps_opts [type: :naive_datetime_usec]
schema "addresses" do
field(:address_line_1, :string)
field(:address_line_2, :string)
field(:city, :string)
field(:name, :string)
field(:postcode, :string)
field(:tel, :string)

timestamps()
hello()
end

def hello do
IO.inspect("hello from address schema definition")
end
end
```
+ [x] If that works, share your progress! 🎉

+ [ ] Now, modify the `hello` function to `IO.inspect` the `schema` (_if possible_)
+ [ ] If it's _not_ possible to "introspect" the `address.schema` comment to that effect ...
+ [ ] If you _are_ able to "introspect" `address.schema` share that progress!
+ [x] Our goal is to mirror the `country` field we just added to `address` into `address_history`.

@RobStallion this SPIKE should get us towards our mission of making `alog` more user-friendly.
Please log your progress on this and if you get stuck please share as much detail as possible so that we can help "unblock" ...
Thanks! 🌻

Contributor guide

No contributing guide indexed for this repository

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.