Writing an Ecto Adapter: Introducing MyXQL

In this talk, we will present MyXQL, a new Elixir driver for the MySQL database.

From connecting to TCP socket, through encoding and decoding a binary protocol, all the way to integrating with DBConnection and Ecto, this talk is a whirlwind tour of building a database driver with Elixir. After watching this talk, you should know about the significant parts of the driver, how to make one (or contribute to one!) and lessons learned from working on MyXQL.

Transcript

1. Writing an Ecto Adapter: Introducing MyXQL @wojtekmach @wojtekmach Wojtek Mach
2. What is MyXQL?
3. What is MyXQL? • Added as built-in MySQL adapter in ecto_sql v3.1.0
4. What is MyXQL? • Added as built-in MySQL adapter in ecto_sql v3.1.0 • (almost) drop-in replacement for the Mariaex- based adapter
5. What is MyXQL? • Added as built-in MySQL adapter in ecto_sql v3.1.0 • (almost) drop-in replacement for the Mariaex- based adapter • Mariaex-based adapter is deprecated in v3.1.5 and removed in upcoming v3.2.
6. Why MyXQL?
7. Why MyXQL? • Why not use Mariaex? • History. Mariaex started around the time of Elixir 1.0, Ecto 0.3, and MySQL 5.6.
 A lot have changed since! • Expertise • Maintenance
8. • Mariax team, thank you!
9. Building the Adapter: Hello World!
10. iex> {:ok, sock} = :gen_tcp.connect('localhost', 3306, [:binary, active: false]) {:ok, #Port<0.7>}
11. iex> {:ok, sock} = :gen_tcp.connect('localhost', 3306, [:binary, active: false]) {:ok, #Port<0.7>} iex> {:ok, data} = :gen_tcp.recv(sock, 0) {:ok, <<74, 0, 0, 0, 10, 56, 46, 48, 46, 49, 54, 0, 41, 3, 0, 0, 109, 12, 72, 1, 75, 19, 110, 65, 0, 255, 255, 45, 2, 0, 255, 195, 21, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 37, 8, 53, 100, 1, ...>>}
12. iex> {:ok, sock} = :gen_tcp.connect('localhost', 3306, [:binary, active: false]) {:ok, #Port<0.7>} iex> {:ok, data} = :gen_tcp.recv(sock, 0) {:ok, <<74, 0, 0, 0, 10, 56, 46, 48, 46, 49, 54, 0, 41, 3, 0, 0, 109, 12, 72, 1, 75, 19, 110, 65, 0, 255, 255, 45, 2, 0, 255, 195, 21, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 37, 8, 53, 100, 1, ...>>} iex> :binpp.pprint(data) 0000 4A 00 00 00 0A 38 2E 30 2E 31 36 00 2D 03 00 00 J....8.0.16.-... 0001 76 13 44 5E 31 4D 3B 35 00 FF FF 2D 02 00 FF C3 v.D^1M;5.ÿÿ-..ÿÃ 0002 15 00 00 00 00 00 00 00 00 00 00 60 5E 27 32 08 ...........`^'2. 0003 70 11 14 5F 3C 23 08 00 63 61 63 68 69 6E 67 5F p.._<#..caching_ 0004 73 68 61 32 5F 70 61 73 73 77 6F 72 64 00 sha2_password. https://hex.pm/packages/binpp
13. iex> {:ok, sock} = :gen_tcp.connect('localhost', 3306, [:binary, active: false]) {:ok, #Port<0.7>} iex> {:ok, data} = :gen_tcp.recv(sock, 0) {:ok, <<74, 0, 0, 0, 10, 56, 46, 48, 46, 49, 54, 0, 41, 3, 0, 0, 109, 12, 72, 1, 75, 19, 110, 65, 0, 255, 255, 45, 2, 0, 255, 195, 21, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 37, 8, 53, 100, 1, ...>>} iex> :binpp.pprint(data) 0000 4A 00 00 00 0A 38 2E 30 2E 31 36 00 2D 03 00 00 J....8.0.16.-... 0001 76 13 44 5E 31 4D 3B 35 00 FF FF 2D 02 00 FF C3 v.D^1M;5.ÿÿ-..ÿÃ 0002 15 00 00 00 00 00 00 00 00 00 00 60 5E 27 32 08 ...........`^'2. 0003 70 11 14 5F 3C 23 08 00 63 61 63 68 69 6E 67 5F p.._<#..caching_ 0004 73 68 61 32 5F 70 61 73 73 77 6F 72 64 00 sha2_password. https://hex.pm/packages/binpp
14. https://www.wireshark.org
15. https://www.wireshark.org
16. https://www.wireshark.org
17. https://www.wireshark.org
18. MySQL Packet https://dev.mysql.com/doc/internals/en/mysql-packet.html
19. MySQL Packet https://dev.mysql.com/doc/internals/en/mysql-packet.html
20. MySQL Packet iex> << payload_length, # 3-byte/24-bit integer with little endianness sequence_id, # 1-byte/24-bit integer with little endianness payload # n-byte binary >> = data
21. MySQL Packet iex> << payload_length::size(24)-little-integer, # 3-byte/24-bit integer with little endianness sequence_id, # 1-byte/24-bit integer with little endianness payload # n-byte binary >> = data
22. MySQL Packet iex> << payload_length::size(24)-little-integer, # 3-byte/24-bit integer with little endianness sequence_id::size(8)-little-integer, # 1-byte/24-bit integer with little endianness payload # n-byte binary >> = data
23. MySQL Packet iex> << payload_length::size(24)-little-integer, # 3-byte/24-bit integer with little endianness sequence_id::size(8)-little-integer, # 1-byte/24-bit integer with little endianness payload::size(payload_length)-binary # n-byte binary >> = data
24. MySQL Packet iex> << payload_length::size(3)-unit(8)—little, # 3-byte/24-bit integer with little endianness sequence_id::size(1)-unit(8)—little, # 1-byte/24-bit integer with little endianness payload::size(payload_length)-binary # n-byte binary >> = data
25. MySQL Packet iex> << payload_length::size(24)-little-integer, # 3-byte/24-bit integer with little endianness sequence_id::size(8)-little-integer, # 1-byte/24-bit integer with little endianness payload::size(payload_length)-binary # n-byte binary >> = data iex> payload_length 74
26. MySQL Packet iex> << payload_length::size(24)-little-integer, # 3-byte/24-bit integer with little endianness sequence_id::size(8)-little-integer, # 1-byte/24-bit integer with little endianness payload::size(payload_length)-binary # n-byte binary >> = data iex> payload_length 74 iex> sequence_id 0
27. MySQL Packet iex> << payload_length::size(24)-little-integer, # 3-byte/24-bit integer with little endianness sequence_id::size(8)-little-integer, # 1-byte/24-bit integer with little endianness payload::size(payload_length)-binary # n-byte binary >> = data iex> payload_length 74 iex> sequence_id 0 iex> payload <<10, 56, 46, 48, 46, 49, 54, 0, 53, 3, 0, 0, 88, 101, 53, 110, 39, 101, 117, 71, 0, 255, 255, 45, 2, 0, 255, 195, 21, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 70, 51, 58, 121, 78, 3, 31, 40, 15, 56, 116, ...>>
28. Initial Handshake Packet
29. Initial Handshake Packet https://dev.mysql.com/doc/internals/en/connection-phase-packets.html#packet- Protocol::Handshake
30. Initial Handshake Packet https://dev.mysql.com/doc/internals/en/connection-phase-packets.html#packet- Protocol::Handshake
31. Initial Handshake Packet iex> <<0x0A, rest::binary>> = payload
32. Initial Handshake Packet iex> <<0x0A, rest::binary>> = payload iex> [server_version, rest] = :binary.split(rest, <<0x00>>)
33. Initial Handshake Packet iex> <<0x0A, rest::binary>> = payload iex> [server_version, rest] = :binary.split(rest, <<0x00>>) iex> <<connection_id::size(32)-little, rest::binary>> = rest
34. Handshake Response
35. Handshake Response https://dev.mysql.com/doc/internals/en/connection-phase-packets.html#packet- Protocol::HandshakeResponse
36. Handshake Response iex> use Bitwise iex> capability_flags = 0x00000200 ||| 0x00008000 ||| 0x00080000 iex> max_packet_size = 65535 iex> charset = 0x21 iex> username = "myxql_test" iex> auth_response = <<0x00>> iex> client_auth_plugin = "caching_sha2_password" iex> payload = << capability_flags::size(32)-little, max_packet_size::size(32)-little, charset, 0::8*23, username::binary, 0x00, auth_response::binary, client_auth_plugin::binary, 0x00 >> iex> sequence_id = 1 iex> data = <<byte_size(payload)::24-little, sequence_id, payload::binary>>
37. Handshake Response iex> use Bitwise iex> capability_flags = 0x00000200 ||| 0x00008000 ||| 0x00080000 iex> max_packet_size = 65535 iex> charset = 0x21 iex> username = "myxql_test" iex> auth_response = <<0x00>> iex> client_auth_plugin = "caching_sha2_password" iex> payload = << capability_flags::size(32)-little, max_packet_size::size(32)-little, charset, 0::8*23, username::binary, 0x00, auth_response::binary, client_auth_plugin::binary, 0x00 >> iex> sequence_id = 1 iex> data = <<byte_size(payload)::24-little, sequence_id, payload::binary>>
38. Handshake Response iex> use Bitwise iex> capability_flags = 0x00000200 ||| 0x00008000 ||| 0x00080000 iex> max_packet_size = 65535 iex> charset = 0x21 iex> username = "myxql_test" iex> auth_response = <<0x00>> iex> client_auth_plugin = "caching_sha2_password" iex> payload = << capability_flags::32-little, max_packet_size::32-little, charset, 0::8*23, username::binary, 0x00, auth_response::binary, client_auth_plugin::binary, 0x00 >> iex> sequence_id = 1 iex> data = <<byte_size(payload)::24-little, sequence_id, payload::binary>>
39. Handshake Response iex> use Bitwise iex> capability_flags = 0x00000200 ||| 0x00008000 ||| 0x00080000 iex> max_packet_size = 65535 iex> charset = 0x21 iex> username = "myxql_test" iex> auth_response = <<0x00>> iex> client_auth_plugin = "caching_sha2_password" iex> payload = << capability_flags::32-little, max_packet_size::32-little, charset, 0::8*23, username::binary, 0x00, auth_response::binary, client_auth_plugin::binary, 0x00 >> iex> sequence_id = 1 iex> data = <<byte_size(payload)::24-little, sequence_id, payload::binary>>
40. Handshake Response iex> :ok = :gen_tcp.send(sock, data)
41. Handshake Response iex> :ok = :gen_tcp.send(sock, data) iex> {:ok, data} = :gen_tcp.recv(sock, 0)
42. Handshake Response iex> :ok = :gen_tcp.send(sock, data) iex> {:ok, data} = :gen_tcp.recv(sock, 0) iex> <<payload_length::24-little, sequence_id::8, payload::binary>> = data
43. Handshake Response iex> :ok = :gen_tcp.send(sock, data) iex> {:ok, data} = :gen_tcp.recv(sock, 0) iex> <<payload_length::24-little, sequence_id::8, payload::binary>> = data iex> :binpp.pprint(payload) 0000 00 00 00 02 00 00 00
44. Building the Adapter: Encoding/Decoding
45. MySQL Data Types https://dev.mysql.com/doc/internals/en/describing-packets.html
46. int<1>
47. int<1> defmodule MyXQL.Types do def take_int1(data) do {value, rest} end end
48. int<1> defmodule MyXQL.Types do def take_int1(data) do <<value::8-little-integer, rest::binary>> = data {value, rest} end end
49. int<1> defmodule MyXQL.Types do def take_int1(data) do <<value::8-little-integer, rest::binary>> = data {value, rest} end end iex> MyXQL.Types.take_int1(<<1, 2, 3>>) {1, <<2, 3>>}
50. int<n> defmodule MyXQL.Types do def take_fixed_length_integer(data, size) do <<value::little-integer-size(size)-unit(8), rest::binary>> = data {value, rest} end end
51. int<n> defmodule MyXQL.Types do def take_fixed_length_integer(data, size) do <<value::little-integer-size(size)-unit(8), rest::binary>> = data {value, rest} end end iex> MyXQL.Types.take_fixed_length_integer(<<1, 2, 3>>, 2) {513, <<3>>}
52. int<1> (again) iex> <<value::8-little-integer, rest::binary>> = <<1, 2, 3>> iex> {value, rest} {1, <<2, 3>>}
53. int<1> (again) iex> <<value::8-little-integer, rest::binary>> = <<1, 2, 3>>
54. int<1> (again) iex> <<value::int1, rest::binary>> = <<1, 2, 3>>
55. int<1> (again) defmodule MyXQL.Types do defmacro int1() do quote do size(8)-little-integer end end end iex> import MyXQL.Types iex> <<value::int1, rest::binary>> = <<1, 2, 3>>
56. int<n> (again) defmodule MyXQL.Types do defmacro int(size) do quote do size(unquote(size))-unit(8)-little-integer end end end iex> import MyXQL.Types iex> <<value::int(1), rest::binary>> = <<1, 2, 3>>
57. OK_Packet https://dev.mysql.com/doc/internals/en/packet-OK_Packet.html
58. OK_Packet defrecord :ok_packet, [:affected_rows, :last_insert_id, :status_flags, :warning_count]
59. OK_Packet defrecord :ok_packet, [:affected_rows, :last_insert_id, :status_flags, :warning_count] def decode_ok_packet(data) do end
60. OK_Packet defrecord :ok_packet, [:affected_rows, :last_insert_id, :status_flags, :warning_count] def decode_ok_packet(data) do <<0x00, rest::binary>> = data end
61. OK_Packet defrecord :ok_packet, [:affected_rows, :last_insert_id, :status_flags, :warning_count] def decode_ok_packet(data) do <<0x00, rest::binary>> = data {affected_rows, rest} = take_length_encoded_integer(rest) {last_insert_id, rest} = take_length_encoded_integer(rest) end
62. OK_Packet defrecord :ok_packet, [:affected_rows, :last_insert_id, :status_flags, :warning_count] def decode_ok_packet(data) do <<0x00, rest::binary>> = data {affected_rows, rest} = take_length_encoded_integer(rest) {last_insert_id, rest} = take_length_encoded_integer(rest) << status_flags::int(2), warning_count::int(2) >> = rest end
63. OK_Packet defrecord :ok_packet, [:affected_rows, :last_insert_id, :status_flags, :warning_count] def decode_ok_packet(data) do <<0x00, rest::binary>> = data {affected_rows, rest} = take_length_encoded_integer(rest) {last_insert_id, rest} = take_length_encoded_integer(rest) << status_flags::int(2), warning_count::int(2) >> = rest ok_packet( affected_rows: affected_rows, last_insert_id: last_insert_id status_flags: status_flags, warning_count: warning_count ) end
64. Building the Adapter: Encoding/Decoding Summary
65. Building the Adapter: DBConnection Integration
66. Production-quality driver • maintaining a connection pool to talk to the DB efficiently from multiple processes • not overloading the DB • attempting to re-connect to the DB if connection is lost • supporting common DB features like prepared statements, transactions, streaming etc • Fast!
68. defmodule MyXQL.Connection do use DBConnection @impl true def connect(opts) do end end
69. defmodule MyXQL.Connection do use DBConnection @impl true def connect(opts) do sock_opts = [:binary, active: false] case :gen_tcp.connect(opts[:host], opts[:port], sock_opts) do {:ok, sock} -> {:error, reason} -> end end end
70. defmodule MyXQL.Connection do use DBConnection defstruct [:sock] @impl true def connect(opts) do sock_opts = [:binary, active: false] case :gen_tcp.connect(opts[:host], opts[:port], sock_opts) do {:ok, sock} -> handshake(username, sock) {:error, reason} -> {:error, %MyXQL.Error{message: "error when connecting: #{inspect(reason)}"}} end end defp handshake(username, sock) do end end
71. defmodule MyXQL.Connection do use DBConnection import MyXQL.Protocol defstruct [:sock] @impl true def connect(opts) do sock_opts = [:binary, active: false] case :gen_tcp.connect(opts[:host], opts[:port], sock_opts) do {:ok, sock} -> handshake(username, sock) {:error, reason} -> {:error, %MyXQL.Error{message: "error when connecting: #{inspect(reason)}"}} end end defp handshake(username, sock) do with {:ok, data} <- :gen_tcp.recv(sock, 0), end end end
72. defmodule MyXQL.Connection do use DBConnection import MyXQL.Protocol defstruct [:sock] @impl true def connect(opts) do sock_opts = [:binary, active: false] case :gen_tcp.connect(opts[:host], opts[:port], sock_opts) do {:ok, sock} -> handshake(username, sock) {:error, reason} -> {:error, %MyXQL.Error{message: "error when connecting: #{inspect(reason)}"}} end end defp handshake(username, sock) do with {:ok, data} <- :gen_tcp.recv(sock, 0), initial_handshake_packet() = decode_initial_handshake_packet(data), end end end
73. defmodule MyXQL.Connection do use DBConnection import MyXQL.Protocol defstruct [:sock] @impl true def connect(opts) do sock_opts = [:binary, active: false] case :gen_tcp.connect(opts[:host], opts[:port], sock_opts) do {:ok, sock} -> handshake(username, sock) {:error, reason} -> {:error, %MyXQL.Error{message: "error when connecting: #{inspect(reason)}"}} end end defp handshake(username, sock) do with {:ok, data} <- :gen_tcp.recv(sock, 0), initial_handshake_packet() = decode_initial_handshake_packet(data), data = encode_handshake_response_packet(username), end end end
74. defmodule MyXQL.Connection do use DBConnection import MyXQL.Protocol defstruct [:sock] @impl true def connect(opts) do sock_opts = [:binary, active: false] case :gen_tcp.connect(opts[:host], opts[:port], sock_opts) do {:ok, sock} -> handshake(username, sock) {:error, reason} -> {:error, %MyXQL.Error{message: "error when connecting: #{inspect(reason)}"}} end end defp handshake(username, sock) do with {:ok, data} <- :gen_tcp.recv(sock, 0), initial_handshake_packet() = decode_initial_handshake_packet(data), data = encode_handshake_response_packet(username), :ok <- :gen_tcp.send(sock, data), end end end
75. defmodule MyXQL.Connection do use DBConnection import MyXQL.Protocol defstruct [:sock] @impl true def connect(opts) do sock_opts = [:binary, active: false] case :gen_tcp.connect(opts[:host], opts[:port], sock_opts) do {:ok, sock} -> handshake(username, sock) {:error, reason} -> {:error, %MyXQL.Error{message: "error when connecting: #{inspect(reason)}"}} end end defp handshake(username, sock) do with {:ok, data} <- :gen_tcp.recv(sock, 0), initial_handshake_packet() = decode_initial_handshake_packet(data), data = encode_handshake_response_packet(username), :ok <- :gen_tcp.send(sock, data), {:ok, data} <- :gen_tcp.recv(sock, 0), end end end
76. defmodule MyXQL.Connection do use DBConnection import MyXQL.Protocol defstruct [:sock] @impl true def connect(opts) do sock_opts = [:binary, active: false] case :gen_tcp.connect(opts[:host], opts[:port], sock_opts) do {:ok, sock} -> handshake(username, sock) {:error, reason} -> {:error, %MyXQL.Error{message: "error when connecting: #{inspect(reason)}"}} end end defp handshake(username, sock) do with {:ok, data} <- :gen_tcp.recv(sock, 0), initial_handshake_packet() = decode_initial_handshake_packet(data), data = encode_handshake_response_packet(username), :ok <- :gen_tcp.send(sock, data), {:ok, data} <- :gen_tcp.recv(sock, 0), ok_packet() <- decode_handshake_response_packet(data) do end end end
77. defmodule MyXQL.Connection do use DBConnection import MyXQL.Protocol defstruct [:sock] @impl true def connect(opts) do sock_opts = [:binary, active: false] case :gen_tcp.connect(opts[:host], opts[:port], sock_opts) do {:ok, sock} -> handshake(username, sock) {:error, reason} -> {:error, %MyXQL.Error{message: "error when connecting: #{inspect(reason)}"}} end end defp handshake(username, sock) do with {:ok, data} <- :gen_tcp.recv(sock, 0), initial_handshake_packet() = decode_initial_handshake_packet(data), data = encode_handshake_response_packet(username), :ok <- :gen_tcp.send(sock, data), {:ok, data} <- :gen_tcp.recv(sock, 0), ok_packet() <- decode_handshake_response_packet(data) do {:ok, %__MODULE__{sock: sock}} end end end
78. defmodule MyXQL do def start_link(opts) do␠ DBConnection.start_link(MyXQL.Connection, opts) end end
79. defmodule MyXQL.Result do defstruct [:columns, :rows] end defmodule MyXQL.Query do defstruct [:statement, :statement_id] defimpl DBConnection.Query do def parse(query, _opts), do: query def describe(query, _opts), do: query def encode(_query, params, _opts), do: params def decode(_query, result, _opts), do: result end end
80. defmodule MyXQL do def prepare(conn, name, statement, opts) do query = %MyXQL.Query{name: name, statement: statement} DBConnection.prepare(conn, query, opts) end end
81. defmodule MyXQL.Connection do # ... @impl true def handle_prepare(%MyXQL.Query{statement: statement}, _opts, state) do end end
82. defmodule MyXQL.Connection do # ... @impl true def handle_prepare(%MyXQL.Query{statement: statement}, _opts, state) do data = encode_com_stmt_prepare(query.statement) end end
83. defmodule MyXQL.Connection do # ... @impl true def handle_prepare(%MyXQL.Query{statement: statement}, _opts, state) do data = encode_com_stmt_prepare(query.statement) with :ok <- sock_send(data, state), end end end
84. defmodule MyXQL.Connection do # ... @impl true def handle_prepare(%MyXQL.Query{statement: statement}, _opts, state) do data = encode_com_stmt_prepare(query.statement) with :ok <- sock_send(data, state), {:ok, data} <- sock_recv(state), end end end
85. defmodule MyXQL.Connection do # ... @impl true def handle_prepare(%MyXQL.Query{statement: statement}, _opts, state) do data = encode_com_stmt_prepare(query.statement) with :ok <- sock_send(data, state), {:ok, data} <- sock_recv(state), com_stmt_prepare_ok(statement_id: statement_id) <- decode_com_stmt_prepare_response(data) do end end end
86. defmodule MyXQL.Connection do # ... @impl true def handle_prepare(%MyXQL.Query{statement: statement}, _opts, state) do data = encode_com_stmt_prepare(query.statement) with :ok <- sock_send(data, state), {:ok, data} <- sock_recv(state), com_stmt_prepare_ok(statement_id: statement_id) <- decode_com_stmt_prepare_response(data) do query = %{query | statement_id: statement_id} end end end
87. defmodule MyXQL.Connection do # ... @impl true def handle_prepare(%MyXQL.Query{statement: statement}, _opts, state) do data = encode_com_stmt_prepare(query.statement) with :ok <- sock_send(data, state), {:ok, data} <- sock_recv(state), com_stmt_prepare_ok(statement_id: statement_id) <- decode_com_stmt_prepare_response(data) do query = %{query | statement_id: statement_id} {:ok, query, state} end end end
88. defmodule MyXQL.Connection do # ... @impl true def handle_prepare(%MyXQL.Query{statement: statement}, _opts, state) do data = encode_com_stmt_prepare(query.statement) with :ok <- sock_send(data, state), {:ok, data} <- sock_recv(state), com_stmt_prepare_ok(statement_id: statement_id) <- decode_com_stmt_prepare_response(data) do query = %{query | statement_id: statement_id} {:ok, query, state} else end end end
89. defmodule MyXQL.Connection do # ... @impl true def handle_prepare(%MyXQL.Query{statement: statement}, _opts, state) do data = encode_com_stmt_prepare(query.statement) with :ok <- sock_send(data, state), {:ok, data} <- sock_recv(state), com_stmt_prepare_ok(statement_id: statement_id) <- decode_com_stmt_prepare_response(data) do query = %{query | statement_id: statement_id} {:ok, query, state} else err_packet(message: message) -> {:error, %MyXQL.Error{message: "error when preparing query: #{message}"}, state} end end end
90. defmodule MyXQL.Connection do # ... @impl true def handle_prepare(%MyXQL.Query{statement: statement}, _opts, state) do data = encode_com_stmt_prepare(query.statement) with :ok <- sock_send(data, state), {:ok, data} <- sock_recv(state), com_stmt_prepare_ok(statement_id: statement_id) <- decode_com_stmt_prepare_response(data) do query = %{query | statement_id: statement_id} {:ok, query, state} else err_packet(message: message) -> {:error, %MyXQL.Error{message: "error when preparing query: #{message}"}, state} {:error, reason} -> {:disconnect, %MyXQL.Error{message: "error when preparing query: #{inspect(reason)}"}, state} end end end
91. defmodule MyXQL.Connection do # ... @impl true def handle_prepare(%MyXQL.Query{statement: statement}, _opts, state) do data = encode_com_stmt_prepare(query.statement) with :ok <- sock_send(data, state), {:ok, data} <- sock_recv(state), com_stmt_prepare_ok(statement_id: statement_id) <- decode_com_stmt_prepare_response(data) do query = %{query | statement_id: statement_id} {:ok, query, state} else err_packet(message: message) -> {:error, %MyXQL.Error{message: "error when preparing query: #{message}"}, state} {:error, reason} -> {:disconnect, %MyXQL.Error{message: "error when preparing query: #{inspect(reason)}"}, state} end end defp sock_send(data, state), do: :gen_tcp.recv(state.sock, data, :infinity) defp sock_recv(state), do: :gen_tcp.send(state.sock, data) end
92. defmodule MyXQL.Connection do use DBConnection @impl true def connect(opts) @impl true def disconnect(exception, state) end
93. defmodule MyXQL.Connection do use DBConnection @impl true def connect(opts) @impl true def disconnect(exception, state) @impl true def checkout(state) @impl true def checkin(state) end
94. defmodule MyXQL.Connection do use DBConnection @impl true def connect(opts) @impl true def disconnect(exception, state) @impl true def checkout(state) @impl true def checkin(state) @impl true def ping(state) end
95. defmodule MyXQL.Connection do use DBConnection @impl true def connect(opts) @impl true def disconnect(exception, state) @impl true def checkout(state) @impl true def checkin(state) @impl true def ping(state) @impl true def handle_prepare(query, opts, state) @impl true def handle_execute(query, params, opts, state) end
96. defmodule MyXQL.Connection do use DBConnection @impl true def connect(opts) @impl true def disconnect(exception, state) @impl true def checkout(state) @impl true def checkin(state) @impl true def ping(state) @impl true def handle_prepare(query, opts, state) @impl true def handle_execute(query, params, opts, state) # more callbacks: transactions! end
97. defmodule MyXQL.Connection do use DBConnection @impl true def connect(opts) @impl true def disconnect(exception, state) @impl true def checkout(state) @impl true def checkin(state) @impl true def ping(state) @impl true def handle_prepare(query, opts, state) @impl true def handle_execute(query, params, opts, state) # more callbacks: transactions! # even more callabacks: cursors! end
98. defmodule MyXQL.Client do def connect(opts) do end def disconnect(exception, state) do end def com_stmt_prepare(statement, state) do end def com_stmt_execute(statement_id, params, state) do end # ... end
99. defmodule MyXQL.Client do def connect(opts) do end def disconnect(exception, state) do end def com_stmt_prepare(statement, state) do end def com_stmt_execute(statement_id, params, state) do end # ... end defmodule MyXQL.Connection do @impl true def connect(opts) do case MyXQL.Client.connect(opts) do # ... end end # ... end
100. Building the Adapter: Ecto Integration
101. (some of) Ecto Features • Changesets to cast and validate data before persisting into the DB • Composing Queries instead of concatenating strings • Defining Schemas that map DB data into Elixir structs • Using mix ecto.create, mix ecto.migrate etc • Using SQL Sandbox • Tap into greater Ecto ecosystem: integration with Phoenix, pagination libraries, admin builders, etc • And more!
102. defmodule MyApp.Repo do use Ecto.Repo, adapter: Ecto.Adapters.MySQL, otp_app: :my_app end
103. Adapter Specifications • Ecto.Adapter – minimal API required from adapters • Ecto.Adapter.Queryable – plan, prepare, and execute queries leveraging query cache • Ecto.Adapter.Schema – insert, update, and delete structs as well as autogenerate IDs • Ecto.Adapter.Storage – storage API used by e.g. mix ecto.create and mix ecto.drop • Ecto.Adapter.Transaction – transactions API
104. Writing SQL Adapter defmodule MyXQL.EctoAdapter do use Ecto.Adapters.SQL, driver: :myxql, migration_lock: "FOR UPDATE" end
105. Example: Constraints
106. Example: Constraints $ mysql -u root myxql_test mysql> CREATE TABLE users (email VARCHAR(255) UNIQUE); Query OK, 0 rows affected (0.17 sec)
107. Example: Constraints $ mysql -u root myxql_test mysql> CREATE TABLE users (email VARCHAR(255) UNIQUE); Query OK, 0 rows affected (0.17 sec) mysql> INSERT INTO users VALUES ('alice@example.com'); Query OK, 1 row affected (0.08 sec) mysql> INSERT INTO users VALUES ('alice@example.com'); ERROR 1062 (23000): Duplicate entry 'alice@example.com' for key 'email'
108. Example: Constraints defmodule MyXQL.EctoAdapter.Connection do # ... @impl true def to_constraints(%MyXQL.Error{mysql: %{code: 1062}, message: message}) do case :binary.split(message, " for key ") do [_, quoted] -> [unique: strip_quotes(quoted)] _ -> [] end end end
109. Example: Constraints defmodule MyXQL.EctoAdapter.Connection do # ... @impl true def to_constraints(%MyXQL.Error{mysql: %{code: 1062}, message: message}) do case :binary.split(message, " for key ") do [_, quoted] -> [unique: strip_quotes(quoted)] _ -> [] end end end
110. Example: Constraints defmodule MyXQL.EctoAdapter.Connection do # ... @impl true def to_constraints(%MyXQL.Error{mysql: %{code: 1062}, message: message}) do case :binary.split(message, " for key ") do [_, quoted] -> [unique: strip_quotes(quoted)] _ -> [] end end end
111. Example: Normalizing Data
112. Example: Normalizing Data defmodule MyXQL.EctoAdapter do # ... @impl true def loaders(:boolean, type), do: [&bool_decode/1, type] # ... def loaders(_, type), do: [type] end
113. Example: Normalizing Data defmodule MyXQL.EctoAdapter do # ... @impl true def loaders(:boolean, type), do: [&bool_decode/1, type] # ... def loaders(_, type), do: [type] defp bool_decode(<<0>>), do: {:ok, false} defp bool_decode(<<1>>), do: {:ok, true} defp bool_decode(0), do: {:ok, false} defp bool_decode(1), do: {:ok, true} defp bool_decode(other), do: {:ok, other} end
114. Integration Tests
115. Integration Tests ecto = Mix.Project.deps_paths[:ecto] Code.require_file "#{ecto}/integration_test/cases/assoc.exs", __DIR__ Code.require_file "#{ecto}/integration_test/cases/interval.exs", __DIR__ # ...
116. Testing
117. .travis.yml
118. docker-compose.yml
119. Tags & Excludes # test/myxql/client_test.exs defmodule MyXQL.ClientTest do @tag ssl: true test "default auth plugin (ssl)" do end @tag ssl: false test "client requires ssl but server does not support it" do end end
120. Tags & Excludes # test/test_helper.exs defmodule TestHelper do def supports_ssl?() do end def excludes() do exclude = [] exclude = [{:ssl, not supports_ssl?()} | exclude] # ... exclude end end ExUnit.start(exclude: TestHelper.excludes())
121. Tags & Excludes # test/test_helper.exs defmodule TestHelper do def supports_ssl?() do mysql!("SELECT @@have_ssl") == "@@have_ssl\nYES\n" end def excludes() do exclude = [] exclude = [{:ssl, not supports_ssl?()} | exclude] # ... exclude end end ExUnit.start(exclude: TestHelper.excludes())
122. Tags & Excludes
123. Summary
124. Links • https://github.com/elixir-ecto/myxql • https://hexdocs.pm/myxql • “Building a new MySQL adapter for Ecto” blog series • Part I: Hello World • Part II: Encoding/Decoding • Part III: DBConnection Integration • Part IV: Ecto Integration
126. Thank you!