How to express composite primary key? And how to populate virtual field on Repo.get?

Questions / Help
,
1

Hello all,
I am casually working on, as a hobbyist project, every now and then, a WordPress read-only Elixir viewer. I am working my way through several dumped WP databases and making Ecto schema modules out of their tables.

I stumbled upon this one today:

mysql> desc wp_wfBlockedIPLog;
+-------------+------------------+------+-----+------------------+-------+
| Field       | Type             | Null | Key | Default          | Extra |
+-------------+------------------+------+-----+------------------+-------+
| IP          | binary(16)       | NO   | PRI |                  |       |
| countryCode | varchar(2)       | NO   |     | NULL             |       |
| blockCount  | int(10) unsigned | NO   |     | 0                |       |
| unixday     | int(10) unsigned | NO   | PRI | NULL             |       |
+-------------+------------------+------+-----+------------------+-------+
4 rows in set (0.01 sec)

Primary question

How can I express a primary key – through the @primary_key attribute, I imagine – for this module?

So far my first try (without the double primary key) is:

defmodule WP.WfBlockedIpLog do
  use Ecto.Schema

  @primary_key {:id, :binary_id, autogenerate: false, source: :IP}
  schema "wp_wfBlockedIPLog" do
    field :block_count, :integer, source: :blockCount
    field :country_code, :string, source: :countryCode
    field :unixday, :integer
    field :date, :date, virtual: true # <- Note this for the secondary question.
  end
end

Example of a loaded Ecto.Schema:

  %WP.WfBlockedIpLog{
    __meta__: #Ecto.Schema.Metadata<:loaded, "wp_wfBlockedIPLog">,
    block_count: 5,
    country_code: "UA",
    date: nil,
    id: "00000000-0000-0000-0000-ffff2573bf43",
    unixday: 17030
  }

The above module gives me a string UUID which I am not very happy with. Is there a way to make the ID a true binary?

Secondary question

I realized after inspecting the dumped WP databases that unixday above is basically a date:

17_030
|> Kernel.*(24*60*60)
|> DateTime.from_unix!()
|> DateTime.to_date()

Is there a way to have the date virtual field above be filled at Repo.get, transparently? Or is there a way to do it at all? What alternative approaches would you recommend?

Thank you.

2

Hello,

  1. try just string instead of binary_id
  2. obvious solution for me is to have simple helper that will fill virtual fields on demand
def load_virtual_fields(query) do
  select_merge(query, [log], %{date: type(fragment("to_timestamp(? * 24*60*60)", log.unixday), :date)})
end
# ...
WfBlockedIpLog
|> load_virtual_fields()
|> ...
|> Repo.get()
2 Likes
3

Solved by using :binary instead of :binary_id:

WfBlockedIpLog |> Repo.get(<<0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 255, 255, 37, 115, 191, 67>>)
# finds the actual record

Yep, I realized Ecto wouldn’t ever introduce implied behaviour so my question was kind of dumb. I just made a function as you described (even simpler, just overwrites the date field with the %{object | date: ...} syntax) and it works quite well. Basically like a Phoenix context – as opposed to directly calling Repo.get.

Do you have any ideas on how to describe a composite primary key on a legacy MySQL table that can’t be changed?

4

ah, yes, forgot about binary :slight_smile:

there are some instructions here Custom Primary Keys – Phoenix v1.3.0-rc.3 (Composite primary keys section)
have you tried them?

5

Yes. Check above – the source of my module. I already know how to use another field as a primary key. What interests me is if it’s possible to use a combination of several fields as a primary key. And these are legacy DBs; they mostly cannot be changed (except for adding an index here and there since that doesn’t change the shape of the tables and the data).

6

You can set multiple fields as primary key.

1 Like
7

It’s not possible to define multiple primary key. But you can set @primary_key to false and then define the option primary_key to true on your fields IP and unixday. Everything is well explains in the doc @fuelen sent, at the bottom.

In your case it will be


defmodule WP.WfBlockedIpLog do
  use Ecto.Schema

  @primary_key false
  schema "wp_wfBlockedIPLog" do
    field :ip, :binary, primary_key: true
    field :unixday, :integer, primary_key: true

    field :block_count, :integer, source: :blockCount
    field :country_code, :string, source: :countryCode
   
    field :date, :date, virtual: true # <- Note this for the secondary question.
  end
end

(Write with my phone so may contains some errors).

2 Likes
8

Thanks @Matsa59 and @akashv. I made it work as you guys pointed out but found the lookup to be too troublesome so I stuck with a single primary key (the IP) and added a few accessor functions.

Marked @fuelen’s answer the solution for future visibility.

9

What do you mean?

You can use Ë‹get_byand for preloads define your own function that use customjoin`.

1 Like
10

I meant that I wanted an easier lookup by default. I still added complex lookup functions.

11

One thing I have done previously whenever I needed easier lookups for composite primary keys, is to add another column that has both the fields and make it the primary key.
Like, say in your case IP and unixday(time) are primary keys. So I would have a column like InsKey which stores IP and unixday with a separator (usually !) so, values would be IP!UnixDay. Then I still get faster lookups and I don’t have to keep a composite primary key, the new column is the primary key.
Sure it is a waste of space, but it’s not something I have needed to worry about.

12

Crossed my mind but I have a hard requirement to not change the DB / tables beyond adding some indexes or triggers – in general, anything that changes the schema is a “no”.

So I just made a big fat comment about how the table is really structured and made several lookup functions so the future devs could pick how they want to find those records in particular.