TIL : "select" queries with ETS
Tags: elixir
Published: 2022-11-30 08:44:31


The following notes are complementary to [this ETS lesson](https://elixirschool.com/en/lessons/storage/ets#deleting-data-10) with a focus on "ets.select / match_spec" and the [`Ex2ms`](https://github.com/ericmj/ex2ms) library to use with `ETS`, the Erlang built-in in-memory database.

Suppose we have an ETS table that saves messages exchanged between two users. The records will be in the form:

```elixir
{timestamp, user, receiver, msg}
```

We will use `match_spec` for `.select` based queries.

## Remove "old" messages

We want to run periodical cleanups to limit the size of the table. We use the Erlang-ETS function [`:ets.select_delete/2`](https://www.erlang.org/doc/man/ets.html#select_delete-2) that takes the table name (we named it in the options) and a `match_spec`.

A record to be deleted should have a "match_spec" that returns `true` whenever this record matches. A match here is when the first element of the record - the timestamp -  is lower than a given constant.
For example, if the constant is 1, a "match_spec" to be used in the function `:ets.select_delete` is:

```elixir
ms = 
[{
  {:"$1", :"$2", :"$3", :"$4"},
  [
   {:<, :"$1", {:const, 1}}
  ], 
  [true]
}]
```

and use it: `:ets.select_delete(:table, ms)`.

We can test this against the table with the function [`:ets.test_ms/2`](https://www.erlang.org/doc/man/ets.html#test_ms-2). It will detect errors if any.

We can  advantageously use the package [`Ex2ms`](https://github.com/ericmj/ex2ms) to rewrite this into a function form with `Ex2ms.fun`. It is more readable, but also allows you to use variables in the scope. Add the package to the `MixProject`.

With this package, the "match_spec" is built by passing a function to `Ex2ms.fun` that returns the same "match_spec":

```elixir
ms = Ex2ms.fun do
  {t, _e, _u, _r, _m} when t < 1 -> true
end
```

> you can use the built-in function [`fm2ms`](https://www.erlang.org/doc/man/ets.html#fun2ms-1) when you use constants, but when you want to use variables in scope, then you should use the `Ex2ms` package.
 
We can use a variable in scope and use this "match_spec" in the `:ets.select_delete` function:

```elixir
# module MyApp.ChatCache
import Ex2ms, only: [fun: 1]

def clean_up(delay) do
  some_time_ago = System.os_time(:second) - delay * 60

  ms =
    fun do
      {t, _e, _u, _r, _m} when t < ^some_time_ago -> true
    end

  :ets.select_delete(:chat, ms)
end
```

This function returns the number of records deleted.


## Another example

We want to [`:ets.select`](https://www.erlang.org/doc/man/ets.html#select-2) from our table all the messages exchanged between a couple of users. In other words, we want the records where the user and receiver - the second and third element (of the record) - are equal to some values, regardless of the order.
Given two constants  values `a` and `b` that represent our couple of users, our constraint is:

```
($2 == "a" and  $3 == "b") or ($2 == "b" and $3 == "a")
```

Such a "match_spec" would be written (carefully!) in Erlang for our table:

```elixir 
[{
  {:"$1", :"$2", :"$3", :"$4"},
  [{
    :orelse,
      {:andalso, 
        {:==, :"$2", {:const, "a"}},
        {:==, :"$3", {:const, "b"}}
      },
      {:andalso, 
        {:==, :"$2", {:const, "b"}},
        {:==, :"$3", {:const, "a"}}
      }
  }], 
  [{
    {:"$1", :"$2", :"$3", :"$4"}
  }]
}]
```

The list of Erlang's allowed function descriptions is [here](https://www.erlang.org/doc/apps/erts/match_spec.html#function-descriptions). Not so easy? `Ex2ms` makes it easy:

```elixir
import Ex2ms, only: [fun: 1]

def get_messages_by_channel(val_e, val_r) do

  ms = fun do {t, e, r, m} when
      (e == ^val_e and r == ^val_r) 
      or (e == ^val_r and r == ^val_e) 
    -> {t, e, r, m}

  :ets.select(:chat, ms)
end
```