insert

[RobStallion]

Relates to dwyl/alog#45

Update the insert function so that it...

• creates a CID of the data passed in
• creates an entry_id that is a substring of the CID. See here
  • make sure entry_id is unique
  • if entry_id is not unique, take an extra character from cid and check db for new entry id.
  • repeat steps until entry_id is unique (at time of insertion).

Look into having a clause that checks if this is the first insert or if this is an update (as update will call insert). We do NOT want to create a unique entry_id when inserting an update.

[RobStallion]

Using this module as the adapter, I wrote this query...

app

Repo.insert(%UsingAlogAdapter.Comments{comment: "hi", comment_id_no: "1"})

adapter

  def insert(prefix, table, header, rows, on_conflict, returning) do
    IO.inspect(prefix, label: "prefix")
    IO.inspect(table, label: "table")
    IO.inspect(header, label: "header")
    IO.inspect(rows, label: "rows")
    IO.inspect(on_conflict, label: "on_conflict")
    IO.inspect(returning, label: "returning")

    values =
      if header == [] do
        [" VALUES " | intersperse_map(rows, ?,, fn _ -> "(DEFAULT)" end)]
      else
        [?\s, ?(, intersperse_map(header, ?,, &quote_name/1), ") VALUES " | insert_all(rows, 1)]
      end

    IO.inspect(values, label: "values")

    ["INSERT INTO ", quote_table(prefix, table), insert_as(on_conflict),
     values, on_conflict(on_conflict, header) | returning(returning)]
     |> IO.inspect(label: "-----> ")
  end

The result of all the logs I added to the insert function...

prefix: nil
table: "comments"
header: [:comment, :comment_id_no, :inserted_at, :updated_at]
rows: [[:comment, :comment_id_no, :inserted_at, :updated_at]]
on_conflict: {:raise, [], []}
returning: [:id]
values: [
  32,
  40,
  [
    [
      [[[], [34, "comment", 34], 44], [34, "comment_id_no", 34], 44],
      [34, "inserted_at", 34],
      44
    ],
    34,
    "updated_at",
    34
  ],
  ") VALUES ",
  [],
  40,
  [[[[[], [36 | "1"], 44], [36 | "2"], 44], [36 | "3"], 44], 36 | "4"],
  41
]
-----> : [
  "INSERT INTO ",
  [34, "comments", 34],
  [],
  [
    32,
    40,
    [
      [
        [[[], [34, "comment", 34], 44], [34, "comment_id_no", 34], 44],
        [34, "inserted_at", 34],
        44
      ],
      34,
      "updated_at",
      34
    ],
    ") VALUES ",
    [],
    40,
    [[[[[], [36 | "1"], 44], [36 | "2"], 44], [36 | "3"], 44], 36 | "4"],
    41
  ],
  [],
  " RETURNING ",
  [],
  34,
  "id",
  34
]

There is no clear log of the arguments that are passed into the function. At first glance it doesn't look like the arguments are there at all.

Going to change the arguments passed into the query and see if the resulting logs change.

[RobStallion]

updated the query but got the same result...

app

    Repo.insert(%UsingAlogAdapter.Comments{comment: "fjkdlfjdskflsjfkslfjsfklfjkflsfksfjsklf", comment_id_no: "3213283972893789273921"})

adapter

[
  "INSERT INTO ",
  [34, "comments", 34],
  [],
  [
    32,
    40,
    [
      [
        [[[], [34, "comment", 34], 44], [34, "comment_id_no", 34], 44],
        [34, "inserted_at", 34],
        44
      ],
      34,
      "updated_at",
      34
    ],
    ") VALUES ",
    [],
    40,
    [[[[[], [36 | "1"], 44], [36 | "2"], 44], [36 | "3"], 44], 36 | "4"],
    41
  ],
  [],
  " RETURNING ",
  [],
  34,
  "id",
  34
]

We can see that this is the same as the previous one. This means that the arguments being passed in are not actually being used.

The query that is logged by phoenix is ...

INSERT INTO "comments" ("comment","comment_id_no","inserted_at","updated_at") VALUES ($1,$2,$3,$4) RETURNING "id" ["fjkdlfjdskflsjfkslfjsfklfjkflsfksfjsklf", "3213283972893789273921", ~N[2019-02-18 12:46:24], ~N[2019-02-18 12:46:24]]

I think that the "RETURNING..." part is what is dealing wit the arguments we pass in. I think that the inert function is creating the first part of the query string. Need to look into this some more.

[RobStallion]

I looked in the part creating the "RETURNING....". It is this line.

However it is only returning the following...

[" RETURNING ", [], 34, "id", 34]

This leads me to believe that what this function is returning is translated into the following part of the query...

INSERT INTO "comments" ("comment","comment_id_no","inserted_at","updated_at") VALUES ($1,$2,$3,$4) RETURNING "id"

and the latter half of the query

["hi", "1", ~N[2019-02-18 12:46:24], ~N[2019-02-18 12:46:24]]

comes from another function.

Next step

Find the other function being called as this is the function that appears to decide the values that are given to the query.

Also need to look into how to manually pass in an id to the database

[RobStallion]

Also need to look into how to manually pass in an id to the database

Just had a thought on this point. We should be able to set primary key to false when creating the table and also create our own row as the new primary key (like this). This should allow us to pass in the cid as an argument, the same way I am passing in comment as an argument in the example above.

The step to create the new primary key should be done here so I'll leave that step for now.

[RobStallion]

1 Any Module in App

Repo.insert(%UsingAlogAdapter.Comments{comment: "hi", comment_id_no: "1"})

2 AppName.Repo

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

3 Ecto.Repo

https://github.com/elixir-ecto/ecto/blob/v3.0.7/lib/ecto/repo.ex#L189

def insert(struct, opts \\ []) do
  Ecto.Repo.Schema.insert(__MODULE__, struct, opts)
end

4 Ecto.Repo.Schema

https://github.com/elixir-ecto/ecto/blob/master/lib/ecto/repo/schema.ex#L204-L210

# if a changeset was passed in
def insert(name, %Changeset{} = changeset, opts) when is_list(opts) do
  do_insert(name, changeset, opts)
end

# if a struct was passed in
# This will be called in this example
def insert(name, %{__struct__: _} = struct, opts) when is_list(opts) do
  do_insert(name, Ecto.Changeset.change(struct), opts)
end

Ecto.Changeset.change(struct)
https://github.com/elixir-ecto/ecto/blob/master/lib/ecto/changeset.ex#L387-L392
turns struct into a changeset.

do_insert(name, Ecto.Changeset.change(struct), opts)

https://github.com/elixir-ecto/ecto/blob/master/lib/ecto/repo/schema.ex#L212-L282
This are the lines that define do_insert. Not pasting whole function as it is very long.

Function that seems to be the "next step" in the chain for actually making an insert happen (not including small manipulations in data) is the apply function...
https://github.com/elixir-ecto/ecto/blob/master/lib/ecto/repo/schema.ex#L264

case apply(changeset, adapter, :insert, args) do

defined here...
https://github.com/elixir-ecto/ecto/blob/master/lib/ecto/repo/schema.ex#L644-L670

defp apply(changeset, adapter, action, args) do # <---- apply/4
  case apply(adapter, action, args) do # <---- apply/3
    {:ok, values} ->
      {:ok, values}
    {:invalid, _} = constraints ->
      constraints
    {:error, :stale} ->
      opts = List.last(args)

      case Keyword.fetch(opts, :stale_error_field) do
        {:ok, stale_error_field} when is_atom(stale_error_field) ->
          stale_message = Keyword.get(opts, :stale_error_message, "is stale")
          changeset = Changeset.add_error(changeset, stale_error_field, stale_message, [stale: true])

          {:error, changeset}

        _other ->
          raise Ecto.StaleEntryError, struct: changeset.data, action: action
      end
  end
end

The arguments that were passed into apply...

**changeset** ===>: #Ecto.Changeset<
  action: :insert,
  changes: %{comment: "hi", comment_id_no: "1"},
  errors: [],
  data: #UsingAlogAdapter.Comments<>,
  valid?: true
>
**adapter** ===>: AlogAdapter
**action** ===>: :insert
**args** ===>: [
  %{
    cache: #Reference<0.2235452241.2265055237.133764>,
    opts: [timeout: 15000, pool_size: 10, pool: DBConnection.ConnectionPool],
    pid: #PID<0.2808.0>,
    sql: AlogAdapter.Connection,
    telemetry: {UsingAlogAdapter.Repo, :debug, [],
     [:using_alog_adapter, :repo, :query]}
  },
  %{
    autogenerate_id: {:id, :id, :id},
    context: nil,
    prefix: nil,
    schema: UsingAlogAdapter.Comments,
    source: "comments"
  },
  [
    comment: "hi",
    comment_id_no: "1",
    inserted_at: ~N[2019-02-19 15:29:26],
    updated_at: ~N[2019-02-19 15:29:26]
  ],
  {:raise, [], []},
  [:id],
  [skip_transaction: true]
]

apply/4 calls apply/3, which is Kernel.apply/3
and is defined as follows...

apply(module, function_name, args) == AlogAdapter.insert(args)

with the adapter, action and args.

apply(module, function_name, args) == AlogAdapter.insert(args)

[RobStallion]

5 AlogAdapter

The apply function call above takes us to our created adapter (this module).

AlogAdapter.insert goes here

defmodule AlogAdapter do
  # Inherit all behaviour from Ecto.Adapters.SQL
  use Ecto.Adapters.SQL,
    driver: :postgrex,
    migration_lock: "FOR UPDATE"

...
end

There is no insert function defined in this module but as it is 'using' Ecto.Adapters.SQL let's look at this module next.

[RobStallion]

6 Ecto.Adapters.SQL

defmodule Ecto.Adapters.SQL do

...

      @conn __MODULE__.Connection

...

      @impl true
      def insert(adapter_meta, %{source: source, prefix: prefix}, params,
                 {kind, conflict_params, _} = on_conflict, returning, opts) do
        {fields, values} = :lists.unzip(params)
        sql = @conn.insert(prefix, source, fields, [fields], on_conflict, returning)
        Ecto.Adapters.SQL.struct(adapter_meta, @conn, sql, :insert, source, [], values ++ conflict_params, kind, returning, opts)
      end

...
end

@conn is defined as a module attribute and is just the current calling module (MODULE) <> .Connection.

The calling module, as discussed in point 5 is AlogAdapter

That means in the insert function, the following line...

@conn.insert(prefix, source, fields, [fields], on_conflict, returning)

is the same as

AlogAdapter.Connection.insert(prefix, source, fields, [fields], on_conflict, returning)

[RobStallion]

7 AlogAdapter.Connection

https://github.com/RobStallion/alog_adapter/blob/master/lib/connection.ex#L46-L47

  @impl true
  defdelegate insert(prefix, table, header, rows, on_conflict, returning), to: PC

This is where our adapter comes into play. STEP 7!!!!!!!!!!!!!!!!!!!!!

[RobStallion]

Our Connection module is currently just delegating the insert task to PC.insert (PC is an alias for Ecto.Adapters.Postgres.Connection).

We will not focus on Connection.insert for now though as the params to insert are not actually passed to Connection.insert (despite the name of the function 🙄)

Connection.insert as it is may be all we need if we can update the params to be inserted in a previous step

We want to make sure that the logic that update the params is in the adapter so that the user of our adapter does not need to do any extra work to create this CID.

This means that we have to make the changes in step 5 or 6.

As we mention, step 7 does not get passed the params so it is too late.
Steps before 5 are not happening in the adapter so we cannot change any of these.

[RobStallion]

In order to get the behaviour that we want we need to define our own version of Ecto.Adapters.SQL.insert/6 in AlogAdapter so that it uses our insert/6 and not the one use Ecto.Adapters.SQL tells it to.

We can do this because insert: 6 is defined with defoverridable in the Ecto.Adapters.SQL module.

[RobStallion]

Comment schema in app

  schema "comments" do
    field :comment, :string
    field :comment_id_no, :string
    field :show, :boolean

    timestamps()
  end

Calling Repo.insert in app (purposefully leaving nil empty)

Repo.insert(%UsingAlogAdapter.Comments{comment: "hi", comment_id_no: "1"})

Newly defined insert/6 in AlogAdapter module

  def insert(adapter_meta, %{source: source, prefix: prefix}, params, on_conflict, returning, opts) do
    params = params ++ [show: true]  # <---- Adding :show as :true in the adapter

    {kind, conflict_params, _} = on_conflict
    {fields, values} = :lists.unzip(params)
    sql = @conn.insert(prefix, source, fields, [fields], on_conflict, returning)
    Ecto.Adapters.SQL.struct(adapter_meta, @conn, sql, :insert, source, [], values ++ conflict_params, kind, returning, opts)
  end

Logs from the terminal

INSERT INTO "comments" ("comment","comment_id_no","inserted_at","updated_at","show") VALUES ($1,$2,$3,$4,$5) RETURNING "id" ["hi", "1", ~N[2019-02-19 20:01:40], ~N[2019-02-19 20:01:40], true]

Changeset returned from Repo.insert

{:ok,
 %UsingAlogAdapter.Comments{
   __meta__: #Ecto.Schema.Metadata<:loaded, "comments">,
   comment: "hi",
   comment_id_no: "1",
   id: 51, # <----- ID no. entered into the db
   inserted_at: ~N[2019-02-19 20:01:40],
   show: nil,
   updated_at: ~N[2019-02-19 20:01:40]
 }}

# notice that the changeset says show is nil. I think this is actually the behaviour we
# will want in the adapter. If we are going to use the adapter to manually add the field
# entry_id (what I am doing with show here), then it will not be part of the users schema,
# meaning that they do not need to see it in their changeset

Log of Repo.get(Comments, 51)

iex()> Repo.get(Comments, 51)
[debug] QUERY OK source="comments" db=3.8ms queue=1.4ms
SELECT DISTINCT ON (c0."comment_id_no") c0."id", c0."comment", c0."comment_id_no", c0."show", c0."inserted_at", c0."updated_at" FROM "comments" AS c0 WHERE (c0."id" = $1) [51]
%UsingAlogAdapter.Comments{
  __meta__: #Ecto.Schema.Metadata<:loaded, "comments">,
  comment: "hi",
  comment_id_no: "1",
  id: 51,
  inserted_at: ~N[2019-02-19 20:01:40],
  show: true,  # <--------- Now showing true
  updated_at: ~N[2019-02-19 20:01:40]
}

We can see that when we select the new entry from the db is has show as true.

The shows that we can add/manipulate the values before they get entered into the db using an adapter. 🎉😄