MySQL PK using ordered UUID V1 with Ecto

Questions / Help
1

Hey

I have a few tables that need to have a UUID as the PK, and I would like to use a ordered V1 UUID.
It seems that by default Ecto uses V4.

What would be the best way to go about this.

  1. Create a new type that generates the UUIDV1 and swaps the parts about
  2. Create a new type that just wraps around a UUIDV1 to call my UuidBin() function in MySQL?
  3. Other?

Or is there a setting I’ve missed somewhere that lets me set the value for a UUID field in Ecto?

Thanks!

2

None last I checked (I wished it used UUIDv1 or UUIDv2 too), I had to do it manually. However postgresql has excellent built-in support for those. :slight_smile:

3

So as I’m already using https://hex.pm/packages/uuid I just use that for creating a UUIDV1 and then just tweak the dump/load so that it does the ordering under the hood. I have a generated column for the tables that shows the original UUID as set by the application/user to aid debugging.

@OvermindDL1 Unfortunately switching everything to use PG is not an option at the moment :frowning:

defmodule App.Ecto.OrderedUuidType do
  @moduledoc """
  Custom type for handling ordered UUIDV1. Ecto.UUID is always V4.
  Because MySQL is not great at UUIDs, we store it as a BINARY(16)  - same as
  Ecto.UUID, but the difference is we also make sure it is a V1 UUID and move 
  some parts around so that it can be cached better.

  @see http://mysql.rjweb.org/doc.php/uuid for more info.
  """
  @behaviour Ecto.Type

  alias UUID

  @doc """
  The Ecto type.
  """
  def type, do: :uuid

  @doc """
  String UUID. Make sure it is a valid UUID V1
  """
  def cast(<< _::64, ?-, _::32, ?-, _::32, ?-, _::32, ?-, _::96 >> = uuid) do
    case is_valid_uuid_type?(uuid) do
      true ->
        {:ok, String.downcase(uuid)}

      _ ->
        :error
    end
  end

  @doc """
  binary representaiton of UUID
  """
  def cast(<< _::128 >> = binary) do
    UUID.binary_to_string!(binary) |> cast
  end

  # Everything else is error
  def cast(_), do: :error

  defp is_valid_uuid_type?(uuid) do
    case UUID.info(uuid) do
      {:ok, [uuid: _, binary: _, type: _, version: 1, variant: _]} ->
        true

      {_, _} ->
        false
    end
  end

  @doc """
  Load a binary UUId from DB and convert to string. Move parts back to unordered
  UUID so that it matches what the user put in.
  """
  def load(<< _::128 >> = binary) do
    << c1, c2, c3, c4, b1, b2, b3, b4, ?-,
       a1, a2, a3, a4, ?-,      
       a5, a6, a7, a8, ?-,
       d1, d2, d3, d4, ?-,
       e1, e2, e3, e4, e5, e6, e7, e8, e9, e10, e11, e12 >> = UUID.binary_to_string!(binary)

    {:ok, << a1, a2, a3, a4, a5, a6, a7, a8, ?-,
             b1, b2, b3, b4, ?-,
             c1, c2, c3, c4, ?-,
             d1, d2, d3, d4, ?-,
             e1, e2, e3, e4, e5, e6, e7, e8, e9, e10, e11, e12 >>}
  end

  # Everything else is error
  def load(_), do: :error

  @doc """
  Take a string representation of a UUID and re-order it into a time ordered
  UUID, then convert it to binary for putting in the DB.

  Convert this: ce093c9e-027f-11e8-ba89-0ed5f89f718b
  To this: 11e8027f-ce09-3c9e-ba89-0ed5f89f718b
  """
  def dump(<< a1, a2, a3, a4, a5, a6, a7, a8, ?-,
              b1, b2, b3, b4, ?-,
              c1, c2, c3, c4, ?-,
              d1, d2, d3, d4, ?-,
              e1, e2, e3, e4, e5, e6, e7, e8, e9, e10, e11, e12 >>) do
    
    ordered = << c1, c2, c3, c4, b1, b2, b3, b4, ?-,
                 a1, a2, a3, a4, ?-,
                 a5, a6, a7, a8, ?-,
                 d1, d2, d3, d4, ?-,
                 e1, e2, e3, e4, e5, e6, e7, e8, e9, e10, e11, e12 >>

    {:ok, UUID.string_to_binary!(ordered)}
  end

  # Everything else is error
  def dump(_), do: :error

  # Callback invoked by autogenerate fields.
  @doc false
  def autogenerate do
    UUID.uuid1()
  end
end

defmodule App.Ecto.OrderedUuidTypeTest do
  use ExUnit.Case

  alias App.Ecto.OrderedUuidType

  @test_uuid "ce093c9e-027f-11e8-ba89-0ed5f89f718b"
  @test_uuid_upper_case "CE093C9E-027F-11E8-BA89-0ED5F89F718B"
  @test_uuid_binary <<206, 9, 60, 158, 2, 127, 17, 232, 186, 137, 14, 213, 248, 159, 113, 139>>
  @test_uuid_ordered_binary <<17, 232, 2, 127, 206, 9, 60, 158, 186, 137, 14, 213, 248, 159, 113, 139>>

  @test_uuid_invalid_characters "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"
  @test_uuid_invalid_type "8eebc69b-b3f7-4ee8-a034-f9bcf69e30cc"

  test "cast" do
    assert OrderedUuidType.cast(@test_uuid) == {:ok, @test_uuid}
    assert OrderedUuidType.cast(@test_uuid_binary) == {:ok, @test_uuid}
    assert OrderedUuidType.cast(@test_uuid_upper_case) == {:ok, String.downcase(@test_uuid_upper_case)}
    assert OrderedUuidType.cast(@test_uuid_invalid_characters) == :error
    assert OrderedUuidType.cast(@test_uuid_invalid_type) == :error
    assert OrderedUuidType.cast(nil) == :error
  end

  test "load" do
    assert OrderedUuidType.load(@test_uuid_ordered_binary) == {:ok, @test_uuid}
    assert OrderedUuidType.load("") == :error
  end

  test "dump" do
    assert OrderedUuidType.dump(@test_uuid) == {:ok, @test_uuid_ordered_binary}
    assert OrderedUuidType.dump(@test_uuid_binary) == :error
  end

  test "autogenerate" do
    assert << _::64, ?-, _::32, ?-, _::32, ?-, _::32, ?-, _::96 >> = Ecto.UUID.generate
  end
end
2 Likes
4

@benperiton That’d be a good Ecto.Type to add to hex. :slight_smile: