all

[RobStallion]

Having an issue "alog-ifying" the all query.

Currently in alog the all query is done like so...

sub =
  from(m in __MODULE__,
    distinct: m.entry_id,
    order_by: [desc: :updated_at],
    select: m
  )

query = from(m in subquery(sub), where: not m.deleted, select: m)

As mentioned in this comment on alog, if the query is done all in one, there is no guarantee that distinct will return the latest entry.

If done in all in one (without using subquery) the where would discard the deleted values, then distinct would pick all remaining unique values.
When done the above way it the subquery returns all the latest entries, then the deleted entries are removed.

The reason this is a problem is because trying to do a subquery in the adapter is causing an error.

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

So this shows that we can update a query that is passed into the adapter. However if we try to use subquery the following way...

  def all(query) do
    query = from(u in query, distinct: u.name)
    query = from(u in subquery(query), where: u.name == "1")
    Ecto.Adapters.Postgres.Connection.all(query)
  end

[error] #PID<0.483.0> running UsingAlogAdapterWeb.Endpoint (connection #PID<0.482.0>, stream id 1) terminated
Server: localhost:4000 (http)
Request: GET /
** (exit) an exception was raised:
    ** (ArgumentError) argument error
        :erlang.tuple_size(nil)
        (ecto_sql) lib/ecto/adapters/postgres/connection.ex:655: Ecto.Adapters.Postgres.Connection.create_names/1
        (ecto_sql) lib/ecto/adapters/postgres/connection.ex:107: Ecto.Adapters.Postgres.Connection.all/1
        (alog_adapter) lib/alog_adapter.ex:3: AlogAdapter.prepare/2
        (ecto) lib/ecto/query/planner.ex:188: Ecto.Query.Planner.query_without_cache/4
        (ecto) lib/ecto/query/planner.ex:158: Ecto.Query.Planner.query_prepare/6
        (ecto) lib/ecto/query/planner.ex:133: Ecto.Query.Planner.query_with_cache/7
        (ecto) lib/ecto/repo/queryable.ex:132: Ecto.Repo.Queryable.execute/4
        (ecto) lib/ecto/repo/queryable.ex:18: Ecto.Repo.Queryable.all/3
        (using_alog_adapter) lib/using_alog_adapter_web/controllers/page_controller.ex:6: UsingAlogAdapterWeb.PageController.index/2
        (using_alog_adapter) lib/using_alog_adapter_web/controllers/page_controller.ex:1: UsingAlogAdapterWeb.PageController.action/2
        (using_alog_adapter) lib/using_alog_adapter_web/controllers/page_controller.ex:1: UsingAlogAdapterWeb.PageController.phoenix_controller_pipeline/2
        (using_alog_adapter) lib/using_alog_adapter_web/endpoint.ex:1: UsingAlogAdapterWeb.Endpoint.instrument/4
        (phoenix) lib/phoenix/router.ex:275: Phoenix.Router.__call__/1
        (using_alog_adapter) lib/using_alog_adapter_web/endpoint.ex:1: UsingAlogAdapterWeb.Endpoint.plug_builder_call/2
        (using_alog_adapter) lib/plug/debugger.ex:122: UsingAlogAdapterWeb.Endpoint."call (overridable 3)"/2
        (using_alog_adapter) lib/using_alog_adapter_web/endpoint.ex:1: UsingAlogAdapterWeb.Endpoint.call/2
        (phoenix) lib/phoenix/endpoint/cowboy2_handler.ex:34: Phoenix.Endpoint.Cowboy2Handler.init/2
        (cowboy) /Users/robertfrancis/Code/spike/using_alog_adapter/deps/cowboy/src/cowboy_handler.erl:41: :cowboy_handler.execute/2
        (cowboy) /Users/robertfrancis/Code/spike/using_alog_adapter/deps/cowboy/src/cowboy_stream_h.erl:296: :cowboy_stream_h.execute/3

@SimonLab @Danwhy Can you see if I am doing something wrong with this subquery?

I think that the error is being caused by the adapter trying to create a query as apposed to the adapter just updating the query that is being passed to it (but I am not sure about this).

[RobStallion]

I just got the following error when trying to run a rollback command on the module where I have been testing this dummy adapter...

** (Postgrex.Error) ERROR 42703 (undefined_column) column s0.comment_id_no does not exist

    query: SELECT DISTINCT ON (s0."comment_id_no") s0."version"::bigint FROM "schema_migrations" AS s0 FOR UPDATE
    (ecto_sql) lib/ecto/adapters/sql.ex:624: Ecto.Adapters.SQL.raise_sql_call_error/1
    (ecto_sql) lib/ecto/adapters/sql.ex:557: Ecto.Adapters.SQL.execute/5
    (ecto) lib/ecto/repo/queryable.ex:147: Ecto.Repo.Queryable.execute/4
    (ecto) lib/ecto/repo/queryable.ex:18: Ecto.Repo.Queryable.all/3
    (ecto_sql) lib/ecto/migrator.ex:316: anonymous fn/3 in Ecto.Migrator.lock_for_migrations/3
    (ecto_sql) lib/ecto/adapters/sql.ex:820: anonymous fn/3 in Ecto.Adapters.SQL.checkout_or_transaction/4
    (db_connection) lib/db_connection.ex:1355: DBConnection.run_transaction/4
    (ecto_sql) lib/ecto/adapters/sql.ex:727: Ecto.Adapters.SQL.lock_for_migrations/5
    (ecto_sql) lib/ecto/migrator.ex:318: Ecto.Migrator.lock_for_migrations/3
    (ecto_sql) lib/mix/tasks/ecto.rollback.ex:106: anonymous fn/4 in Mix.Tasks.Ecto.Rollback.run/2
    (elixir) lib/enum.ex:765: Enum."-each/2-lists^foreach/1-0-"/2
    (elixir) lib/enum.ex:765: Enum.each/2
    (mix) lib/mix/task.ex:316: Mix.Task.run_task/3
    (mix) lib/mix/cli.ex:79: Mix.CLI.run_task/2
    (elixir) lib/code.ex:767: Code.require_file/2

This is the line that is causing the issue - https://github.com/RobStallion/alog_adapter/blob/master/lib/connection.ex#L36

This appears to be because not all the tables created in the module contain the row "comment_id_no" (equivalent to entry_id).

This could be a potential issue in trying to create an ecto adapter.

[RobStallion]

As we discovered on the call yesterday, the reason that the subquery is not working in the adapter is because something is being done the the query struct before it reaches the all function.

I did not realise that this was the case.

In order to better understand what happens to the query struct I am going to attempt to "follow" it before it gets to out all.

My hope is that by doing this I will gain a better understanding of how to update any query that is passed into our adapter.

[RobStallion]

[
  adapter_meta: %{
    cache: #Reference<0.3974704928.2992242689.138205>,
    opts: [timeout: 15000, pool_size: 10, pool: DBConnection.ConnectionPool],
    pid: #PID<0.2848.0>,
    sql: AlogAdapter.Connection,
    telemetry: {UsingAlogAdapter.Repo, :debug, [],
     [:using_alog_adapter, :repo, :query]}
  },
  opts: [],
  params: [],
  prepared: {:cache,
   #Function<29.104601620/1 in Ecto.Query.Planner.query_with_cache/7>,
   {134695,
    "SELECT c0.\"id\", c0.\"comment\", c0.\"comment_id_no\", c0.\"show\", c0.\"cid\", c0.\"entry_id\", c0.\"inserted_at\", c0.\"updated_at\" FROM \"comments\" AS c0"}},
  query_meta: %{
    preloads: [],
    select: %{
      assocs: [],
      from: {:any,
       {:source, {"comments", UsingAlogAdapter.Comments}, nil,
        [
          id: :id,
          comment: :string,
          comment_id_no: :string,
          show: :boolean,
          cid: :string,
          entry_id: :string,
          inserted_at: :naive_datetime,
          updated_at: :naive_datetime
        ]}},
      postprocess: {:source, :from},
      preprocess: [source: :from],
      take: []
    },
    sources: {{"comments", UsingAlogAdapter.Comments, nil}}
  }
]

Arguments that are passed into Ecto.Adapters.SQL.execute

 def execute(adapter_meta, query_meta, prepared, params, opts)