ecto_sql_log_to_query.ex

defmodule SQL do
  defmodule LogParser do
    import NimbleParsec

    defcombinatorp(
      :string,
      ascii_char([?"])
      |> ignore()
      |> repeat_while(
        choice([
          ~S(\") |> string() |> replace(?"),
          utf8_char([])
        ]),
        {:not_quote, []}
      )
      |> ignore(ascii_char([?"]))
      |> reduce({List, :to_string, []})
    )

    defcombinatorp(
      :bitstring,
      empty()
      |> ignore(string("<<"))
      |> repeat(integer(min: 1, max: 3) |> ignore(optional(string(", "))))
      |> ignore(string(">>"))
      |> reduce(:reduce_to_bitstring)
    )

    defcombinatorp(
      :integer,
      empty()
      |> optional(string("-") |> replace(:negative))
      |> integer(min: 1)
      |> reduce(:reduce_to_integer)
    )

    defcombinatorp(
      :float,
      empty()
      |> optional(string("-") |> replace(:negative))
      |> integer(min: 1)
      |> ignore(string("."))
      |> integer(min: 1)
      |> reduce(:reduce_to_float)
    )

    defcombinatorp(
      :decimal,
      empty()
      |> ignore(string("#Decimal<"))
      |> choice([parsec(:float), parsec(:integer)])
      |> ignore(string(">"))
      |> reduce(:reduce_to_decimal)
    )

    defcombinatorp(
      :list,
      empty()
      |> ignore(string("["))
      |> repeat(
        [
          parsec(:float),
          parsec(:bitstring),
          parsec(:integer),
          parsec(:list),
          parsec(:decimal),
          parsec(:string)
        ]
        |> choice()
        |> ignore(optional(string(", ")))
      )
      |> ignore(string("]"))
      |> wrap()
    )

    defparsecp(:bindings, parsec(:list) |> eos())

    defparsec(
      :split,
      empty()
      |> repeat_while(utf8_char([]), {:not_bindings, []})
      |> reduce(:to_string)
      |> parsec(:bindings)
    )

    defp reduce_to_bitstring(list) do
      Enum.into(list, <<>>, &<<&1>>)
    end

    defp reduce_to_integer([:negative, number]) do
      -1 * number
    end

    defp reduce_to_integer([number]) do
      number
    end

    defp reduce_to_float([:negative, a, b]) do
      {float, ""} = Float.parse("-#{a}.#{b}")
      float
    end

    defp reduce_to_float([a, b]) do
      {float, ""} = Float.parse("#{a}.#{b}")
      float
    end

    defp reduce_to_decimal([integer]) when is_integer(integer), do: Decimal.new(integer)
    defp reduce_to_decimal([float]) when is_float(float), do: Decimal.from_float(float)

    defp not_quote(<<?", _::binary>>, context, _, _), do: {:halt, context}
    defp not_quote(_, context, _, _), do: {:cont, context}

    defp not_bindings(binary, context, _, _) do
      case bindings(binary) do
        {:ok, _, _, _, _, _} -> {:halt, context}
        {:error, _, _, _, _, _} -> {:cont, context}
      end
    end
  end

  def explain_analyze(query, repo) do
    result = query |> repo.query!()
    query = "EXPLAIN ANALYZE " <> query

    query
    |> repo.query!()
    |> Map.get(:rows)
    |> Enum.intersperse("\n")
    |> IO.puts()

    IO.puts("RESULT: #{inspect(result)}")

    query
  end

  def normalize_log(log) do
    {:ok, [query, bindings], _, _, _, _} = __MODULE__.LogParser.split(log)

    bindings
    |> Enum.with_index(1)
    |> Enum.reduce(query, fn {binding, index}, query ->
      String.replace(query, "$#{index}", stringify(binding, :root))
    end)
    |> format()
  end

  def format(query) do
    path = Plug.Upload.random_file!("SQL")
    File.write!(path, query)

    "sqlfmt --use-spaces --print-width=80 < #{path}"
    |> String.to_charlist()
    |> :os.cmd()
    |> to_string()
  end

  def stringify(binding, _level) when is_float(binding) or is_integer(binding), do: to_string(binding)
  def stringify(%Decimal{} = binding, _level), do: to_string(binding)

  def stringify(binding, level) when is_binary(binding) do
    string =
      cond do
        is_binary_uuid(binding) -> UUID.binary_to_string!(binding)
        true -> binding
      end

    case level do
      :root -> "'#{string}'"
      :child -> string
    end
  end

  def stringify(binding, :root) when is_list(binding) do
    "'{" <> Enum.join(Enum.map(binding, &stringify(&1, :child)), ",") <> "}'"
  end

  defp is_binary_uuid(binary) when is_binary(binary) do
    is_binary(UUID.binary_to_string!(binary))
  rescue
    ArgumentError ->
      false
  end

  defp is_binary_uuid(_), do: false
end

"SELECT TRUE FROM "users" AS u0 WHERE (u0."id" = $1) LIMIT 1 [<<155, 195, 79, 130, 225, 190, 65, 28, 171, 159, 59, 40, 165, 9, 160, 175>>]"
|> SQL.normalize_log()
|> IO.puts()