# pointers

Ecto's missing universal foreign key

> One foreign key to rule them all and in the darkness, bind them.

-- Gandalf, paraphrased.

A means of foreign keying many tables in one field. Designed for
highly interlinked data in highly dynamic schemata where tracking all
the foreign keys is neither desired nor practical.

Note: a universal foreign key is actually a hard problem. Many
approaches are on offer with a variety of tradeoffs. You should
carefully consider a variety of approaches rather than just blindly
adopting the one that fitted our project's needs the best!

## Background

A `Table` is a record of a table that may be linked to by a pointer.
A `Pointer` is a pointer id and a table id.

With these two ingredients, we can construct a means of pointing to
any table that has a `Table` entry.

`Pointer` and `Table` IDs are both `Pointers.ULID`, a UUID-like type
that combines a millisecond-precision timestamp and some randomness to
reduce the likelihood of a clash. It naturally sorts both in binary
and text form by time and as far as postgres is concerned, it's a UUID.

## Installation

Aside from the hex dependency, you will also need to write a simple
migration to set up the database before you can start writing your
regular migrations:

defmodule MyApp.Repo.Migrations.InitPointers do
  use Ecto.Migration
  import Pointers.Migration

  def up(), do: inits(:up)
  def down(), do: inits(:down)

  defp inits(dir) do
    init_pointers_ulid_extra(dir) # this one is optional but recommended
    init_pointers(dir) # this one is not optional

## Defining a Pointable Type

Pointable tables require a unique sentinel ULID to identify
them. These must be 26 characters long and in the alphabet of
[Crockford's Base32]('s_Base32).
They should be easy to identify in a printout and might be silly.

There is a helper function, `synthesise!/1` in `Pointers.ULID` to
assist with this process - give it a 26-character long binary of ascii
alphanumerics and it will give you the closest ULID that matches back.

Let's look at a simple schema:

defmodule MyApp.Greeting do
  use Pointers.Pointable,
    otp_app: :my_app,
    source: "myapp_greeting",
    table_id: "GREET1NGSFR0MD0CEXAMP1E000"

  pointable_schema do
    field :greeting, :string

To declare a pointable schema, we start by using `Pointers.Pointable`,
providing the name of our otp application, the source table's name in
the database and our chosen sentinel ULID.

We then call `pointable_schema` and define any fields we wish to put
directly in the table. For the most part, `pointable_schema` is like
Ecto's `schema` macro, except you do not provide the table name and
let it handle the primary key.

If for some reason you wished to turn autogeneration off, you could
pass `autogenerate: false` to the options provided when using

Now let's define the migration for our schema:

defmodule MyApp.Repo.Migrations.Greeting do
  use Ecto.Migration
  import Pointers.Migration

  def up() do
    create_pointable_table(:greeting, "GREET1NGSFR0MD0CEXAMP1E000") do
      add :greeting, :text, null: false

  def down() do
    drop_pointable_table(:greeting, "GREET1NGSFR0MD0CEXAMP1E000")

As you can see, it's pretty similar to defining a regular migration,
except you use `create_pointable_table` and
`drop_pointable_table`. Notice that our sentinel ULID makes an
appearance again here. It's *very* important that these match what we
declared in the schema.

## Referencing Pointers

Ecto does not know anything about our scheme, so unless we
specifically want something to reference one of the pointed tables, we
typically `belongs_to` with `Pointers.Pointer`. The table in which we
do this does not itself need to be pointable.

defmodule MyApp.Foo do

  use Ecto.Schema
  alias Pointers.Pointer

  # regular ecto table, not pointable!
  schema "hello" do
    belongs_to :pointer, Pointer # who knows what it points to?

You may choose to reference a specific schema rather than Pointer if it
will only point to a single table. If you do this, you must ensure
that the referenced record exists in that table in the normal
way. There may be some performance benefit, we didn't benchmark it.

The migration is slightly more complex, we have to decide what type of
a pointer it is. Pointers come in three categories:

* A strong pointer is not nullable and is deleted when the object it
  points to is deleted.
* A weak pointer is nullable and is nilified when the object it points
  to is deleted.
* An unbreakable pointer will raise when you attempt to delete the
  object it points to.

| Type        | Nullable? | On Delete   |
| Strong      | No        | Cascade     |
| Weak        | Yes       | Set Null    |
| Unbreakable | No        | Raise       |

In this case we will use a strong pointer, because we want it to be
deleted if the pointed object is deleted.

defmodule MyApp.Repo.Migrations.Hello do
  use Ecto.Migration
  import Pointers.Migration

  def change() do
    create_if_not_exists table(:hello) do
      add :pointer, strong_pointer(), null: false
      add :greeting, :text, null: false

If you are pointing to a specific table instead of pointer,
`strong_pointer/1` allows you to pass the name of that module
(`strong_pointer/0` calls this with `Pointers.Pointer`).

## Dereferencing Pointers

It is common that even though you have a universal foreign key, you
will want to issue different queries based upon the type that is being
pointed to. For this reason, it is up to you to decide how to perform
an onward query.

`Pointers.schema/1` turns a `Pointer` into an Ecto schema module name
you can switch against. `Pointers.plan` breaks down a list of Pointers
into a map of ids keyed by schema module. It is handy to define some
functions in your (non-library) application that can load any type of
pointer in given contexts.

## Querying Pointers

Since `Pointer` has a table, you can use its `table_id` field to
filter by pointed type. `!/1` (or `ids!/1` for a
list) can be used to obtain the IDs for a table or tables.

Then you run into another problem, that even though you know all of
the tables you're working with will have a certain field, you need to
know which table they are to work with them! The solution to this is
what we are calling 'mixin tables' for convenience.

A mixin table has a `Pointer` primary key along with any other fields
you wish to store in this mixin. By moving fields out to mixin tables,
you gain knowledge of the table name to which you need to join.

An example mixin schema:

defmodule My.Creator
  use Pointers.Mixin,
    otp_app: :my_app,
    source: "creator"

  mixin_schema do
    belongs_to :creator, My.User

Mixin tables are not themselves pointable, so there is no need to
specify a table id as when defining a pointable schema.

The migration for this is slightly more complicated:

defmodule My.Creator.Migration do

  import Ecto.Migration
  import Pointers.Migration

  defp creator_table(), do: My.Creator.__schema__(:source)
  defp user_table(), do: My.User.__schema__(:source)

  def migrate_creator(index_opts \\ []),
    do: migrate_creator(index_opts, direction())

  defp migrate_creator(index_opts, :up) do
    create_mixin_table(creator_table()) do
      add :creator_id, strong_pointer(user_table()), null: false 
    create_if_not_exists(unique_index(creator_table(), [:creator_id], index_opts))

  defp migrate_creator(index_opts, :down) do
    drop_if_exists(unique_index(creator_table(), [:creator_id], index_opts))

## Elixir-based logic

The practical result of pointers is that it pushes a certain amount of
validation and consistency logic back into elixir land. It is
therefore your elixir code's responsibility to ensure that data is
inserted into the appropriate mixin tables when inserting a pointable
object and to manage deletions as appropriate.

When assembling queries with mixin tables, pay careful attention to
the type of join you are performing. An inner join is explicitly
asking not to be shown objects that do not have a record for that
mixin. You quite possibly wanted to left join.

## Configuration and overrides

Every pointable or mixin schema is overrideable with configuration
during compilation (this is why using them requires an `:otp_app` to
be specified). For example, we could override `Pointers.Table` (which
is a pointable table) thus:

config :pointers, Pointers.Table, source: "my_pointers_table"

The `table_id` is also configurable, but we don't recommend you change it.

In addition, all pointable and mixin schemas permit extension with
[Flexto]( See the [Flexto
docs]( for more information about how to
extend schemas via configuration. You will probably at the very least
want to insert some `has_one` for mixins off your pointables.

## Tradeoffs

All solutions to the universal foreign key problem have tradeofs. Here
are what we see as the deficiencies in our approach:

1. It forces a ULID on you. This is great for us, but not
   everyone. ULID exposes a timestamp with millisecond precision. If
   the time of creation of a resource is sensitive information for
   your purposes, ULIDs are not going to be suitable for you.
2. Ecto has no knowledge of the specialty of `Pointer`,
   e.g. `Repo.preload` does not work and you need to specify a join
   condition to join through a pointer. Use our functions.
3. Dereferencing a list of pointers requires a select query per table
   type that occurs in the input set.
4. Reliance on user attention. You have to follow the instructions
   correctly to make the system work at all.
5. There is likely some performance impact from postgres not
   understanding the relationships between the various tables
   properly. It's hard to gauge and we haven't even tried.

These are not likely to change. If you're going to pick
this library, do so in the full knowledge of the tradeoffs it makes.

Alternatives include (I'm sure you can think of others):

* Storing the table name in a second column alongside every foreign key.
* A compound datatype of id and table name.
* Byte/String manipulation tricks.
* Evil SQL hacks based upon compile time configuration.

While we have our gripes with this approach, once you've gotten the
hang of using it, it works out pretty well for most purposes and it's
one of the simpler options to work with.


* Docs!
* Tests!
* `mix pointers.gen.migration.init` task to generate an init migration.
* `mix pointers.gen.schema.pointable` task to generate a pointable schema.
* `mix pointers.gen.schema.mixin` task to generate a mixin schema.

## Copyright and License

Copyright (c) 2020 pointers Contributors

Licensed under the Apache License, Version 2.0 (the "License");
you may not use this file except in compliance with the License.
You may obtain a copy of the License at

Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
See the License for the specific language governing permissions and
limitations under the License.