Extend ecto_sql postgres adapter

[RobStallion]

Relates to https://github.com/dwyl/alog/issues/38#issuecomment-462499527

[RobStallion]

I have created a new app locally called alog_adapter and set it up in the same way as the https://github.com/dwyl/ecto-adapter-example, but I have copied the code from https://github.com/elixir-ecto/ecto_sql postgres files (adapter, connection).

Next created a new phx project, also locally for now, called using_alog_adapter.

I have added alog_adapter to the list of deps...

{:alog_adapter, path: "/path/to/alog_adapter"}

Then updated the Repo module...

defmodule UsingAlogAdapter.Repo do
  use Ecto.Repo,
    otp_app: :using_alog_adapter,
    adapter: AlogAdapter
end

Now all the Repo functions I call in this app use the alog_adapter module. This means that we can edit any and all functions users are used to using with ecto to "alog-ify" them.

[RobStallion]

Added 50 fake users to using_alog_adapter twice (as in there is a double of each user).

Running the query...

    query = from(u in User, select: u)
    users = Repo.all(query)
    IO.inspect(length users)

returns 100 users as expected.

Next step

Update my adapter all/1 function to ensure that it only selects distinct values when given the above query.

[RobStallion]

Query

IO.inspect(query, structs: false)
%{
  __struct__: Ecto.Query,
  aliases: %{},
  assocs: [],
  combinations: [],
  distinct: nil,
  from: %{
    __struct__: Ecto.Query.FromExpr,
    as: nil,
    hints: [],
    prefix: nil,
    source: {"users", UsingAlogAdapter.User}
  },
  group_bys: [],
  havings: [],
  joins: [],
  limit: nil,
  lock: nil,
  offset: nil,
  order_bys: [],
  prefix: nil,
  preloads: [],
  select: %{
    __struct__: Ecto.Query.SelectExpr,
    expr: {:&, [], [0]},
    fields: [
      {{:., [], [{:&, [], [0]}, :id]}, [], []},
      {{:., [], [{:&, [], [0]}, :email]}, [], []},
      {{:., [], [{:&, [], [0]}, :name]}, [], []},
      {{:., [], [{:&, [], [0]}, :inserted_at]}, [], []},
      {{:., [], [{:&, [], [0]}, :updated_at]}, [], []}
    ],
    file: "/using_alog_adapter_web/controllers/page_controller.ex",
    line: 9,
    params: nil,
    take: %{}
  },
  sources: {{"users", UsingAlogAdapter.User, nil}},
  updates: [],
  wheres: [],
  windows: []
}

query =
  from(
    u in User,
    select: u,
    distinct: u.name
  )
IO.inspect(query, structs: false)
%{
  __struct__: Ecto.Query,
  aliases: %{},
  assocs: [],
  combinations: [],
  distinct: %{
    __struct__: Ecto.Query.QueryExpr,
    expr: [asc: {{:., [], [{:&, [], [0]}, :name]}, [], []}],
    file: "/using_alog_adapter_web/controllers/page_controller.ex",
    line: 10,
    params: nil
  },
  from: %{
    __struct__: Ecto.Query.FromExpr,
    as: nil,
    hints: [],
    prefix: nil,
    source: {"users", UsingAlogAdapter.User}
  },
  group_bys: [],
  havings: [],
  joins: [],
  limit: nil,
  lock: nil,
  offset: nil,
  order_bys: [],
  prefix: nil,
  preloads: [],
  select: %{
    __struct__: Ecto.Query.SelectExpr,
    expr: {:&, [], [0]},
    fields: [
      {{:., [], [{:&, [], [0]}, :id]}, [], []},
      {{:., [], [{:&, [], [0]}, :email]}, [], []},
      {{:., [], [{:&, [], [0]}, :name]}, [], []},
      {{:., [], [{:&, [], [0]}, :inserted_at]}, [], []},
      {{:., [], [{:&, [], [0]}, :updated_at]}, [], []}
    ],
    file: "/using_alog_adapter_web/controllers/page_controller.ex",
    line: 10,
    params: nil,
    take: %{}
  },
  sources: {{"users", UsingAlogAdapter.User, nil}},
  updates: [],
  wheres: [],
  windows: []
}

First is the log of the query from the above, second is the query with distinct added. At first glance it appears that the distinct: field in the struct is the only one that has changed.

Second query returned only 50 as expected.

My first proof of concept will be to copy and paste the struct from the second query into the original query when it is in the adapter.

e.g.

distinct = %{
    __struct__: Ecto.Query.QueryExpr,
    expr: [asc: {{:., [], [{:&, [], [0]}, :name]}, [], []}],
    file: "/using_alog_adapter_web/controllers/page_controller.ex",
    line: 10,
    params: nil
  }

Map.put(query, :distinct, distinct)

[RobStallion]

Adding distinct in adapter

  @impl true
  def all(query) do
    distinct = %{
      __struct__: Ecto.Query.QueryExpr,
      expr: [asc: {{:., [], [{:&, [], [0]}, :name]}, [], []}],
      file: "/using_alog_adapter_web/controllers/page_controller.ex",
      line: 10,
      params: nil
    }

    query = Map.put(query, :distinct, distinct)

    sources = create_names(query)
    {select_distinct, order_by_distinct} = distinct(query.distinct, sources, query)

    from = from(query, sources)
    select = select(query, select_distinct, sources)
    join = join(query, sources)
    where = where(query, sources)
    group_by = group_by(query, sources)
    having = having(query, sources)
    window = window(query, sources)
    combinations = combinations(query)
    order_by = order_by(query, order_by_distinct, sources)
    limit = limit(query, sources)
    offset = offset(query, sources)
    lock = lock(query.lock)

    [select, from, join, where, group_by, having, window, combinations, order_by, limit, offset | lock]
  end

Calling a regular query in using_alog_adapter...

Repo.all(User) |> length |> IO.inspect()
50

Based on the above we can see that the adapter has been updated and only returned the distinct users. This shows that it is possible to "alog-ify" a query in the adapter.

Next step

Look into how to make the "alog-ification" dynamic. e.g. no matter what table or query is passed in, we will be able to update the query so that it is distinct on :name (this will be changed to entry_id in future)

[RobStallion]

Just to be clear, this also works with more complex queries...

Repo.all(from(u in User, select: u.name, where: u.name == "1")) |> length |> IO.inspect()
1

[RobStallion]

Opened a SO question about this and got an answer.

I have implemented that answer in my example adapters all function. The code now looks like this...

  @impl true
  def all(query) do
    query = from(u in query, distinct: u.name)
    Ecto.Adapters.Postgres.Connection.all(query)
  end

This takes the query that the user passes in and adds distinct to it (in a much nicer way than updating the struct was). Then calls the postgres adapters all function with the updated query.

[RobStallion]

The above does not fully account for how we will handle more complex queries but I feel that this is a good direction to head in.

The code is clear and concise. It takes a query, transforms that query, then calls the default query function.

[RobStallion]

I think that I have gone far enough with this spike now. I have been able to create an adapter that makes use of all the existing logic in the postges adapter (the default adapter used in phoenix apps).

Next steps

• Create a alog style application (something that has the correct db setup e.g. entry_id etc...) and use my newly created adapter with it.
• Recreate the alog query functions so that they work as an adapter, but with the same functionality that alog currently adds.