Insert - Ecto.Adapters.SQL.Connection

[Danwhy]

Part of #38

https://hexdocs.pm/ecto_sql/Ecto.Adapters.SQL.Connection.html#c:insert/6

For this function, we need to generate a cid based on the given data, as well as create an "entry id" for ease of lookup.

[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. 🎉😄

[RobStallion]

For more info on how I got to this point see RobStallion/alog_adapter#2

[RobStallion]

Next steps

• Add insert/6 to the Alog module.
• Replace params = params ++ [show: true] with a function that adds the cid and entry_id values to the list of params.
  • add CID into project deps so we can create CID from params
  • Create a map from the params, excluding inserted_at & updated_at, to be used to create the CID.
  • Take the first 3 characters of the CID and use as the entry_id.
  • Make sure the CID is unique (needs to be unique for insert function)
  • Make sure the entry_id is unique (needs to be unique for insert function)
  • If entry_id is not unique, and another character and check for uniqueness again (repeat this step as many times as needed until we get a unique entry id)

Make sure the entry_id is unique

I think that this will be the most tricky part as it means that we will somehow need to query the database. I think that this will be tricky as we will need to be able to create a query struct that will allow us to query the db from the adapter (something that we can normally do in an app with Repo.all(User) for example will not work as it creates the struct incorrect. I think it will be a similar issue as the one we encountered with all and subquery)

[Danwhy]

We should be able to make sure the CID is unique using a unique index. In the execute_ddl functions when we create a table we define the cid column, we can also add a unique index for it.

All we need to do then is make sure the entry id is unique, which might require some querying

[Danwhy]

@RobStallion Another thing to note is that we need to make sure the timestamp fields are present in the params when we insert.

I automatically create the columns during the migration, but they will just be null if timestamps is not in the schema. We just need to check the params, and insert them if the user hasn't.

[RobStallion]

@Danwhy Thanks for responding on this. I realised that I made a typo sorry 😞

I was meant to say "Make sure the entry_id" is unique". I will update my earlier comment.
You mentioned what I thought was the issue (regardless of me being an egg 🥚)

All we need to do then is make sure the entry id is unique, which might require some querying

@Danwhy @SimonLab Do you have any thoughts on how we can do the querying to check if the entry_id is unique?

[RobStallion]

@Danwhy Regarding this comment, good thinking 🧠

I'll make sure I add a check for that. Should be a fairly simple step 👍

[Danwhy]

The query should be quite simple, so we might be able to just create a sql string: SELECT entry_id where entry_id = 'f' for example, then pass it to the execute function.

We can do this recursively until we find a non existing entry id: SELECT entry_id where entry_id = 'fy',
SELECT entry_id where entry_id = 'fyZ' etc.