使用Phoenix使用原生SQL查询MariaDB

2 年 ago
雅, 悟
3 minutes

凤凰框架中有一个名为Ecto的数据库操作库，它支持PostgreSQL、MySQL（MariaDB）和SQLite等。

使用Ecto可以轻松地进行CRUD操作（不需要编写SQL），只需按照定义的模型进行操作。但是这次我想有意地直接向MariaDB发送原始的SQL查询。

请参阅此处的Phoenix应用程序创建指南。

为什么选择原生 SQL？

只要定义好模型，Ecto可以为我们提供CRUD操作、表的创建，甚至生成JSON API和HTML，非常强大。但是，在开发涉及数据库操作的大型WebAPI时，根据我的经验，经常会遇到以下情况，不可避免地会想要编写原生SQL。

    • レコード数が増加するにつれパフォーマンスが低下し、SQL を細かくチューニングする必要がでてくる

 

    • データベースが提供するコアな関数が使いたくなってくる

 

    • テーブルのリレーションが複雑化し、フレームワークが提供する CRUD 操作では対応しきれなくなってくる

 

    句が複雑化するにつれ、DSL の DSL という存在にイラ立ちを覚え始める

当然，在框架的范围内设计表格是最好的选择，但有时无论如何也无法实现，这也是常态。
为了保险起见，也希望能够写生 SQL。

准备工作：安装MariaDB

这次我们将在本地启动 MariaDB 并使用它。
如果尚未安装，可以从官方网站下载或者使用 homebrew 在 Mac 上进行安装。
（顺便提一下，目前稳定版本是 10.0.21。）

完成安装后，启动MariaDB服务器并从终端进行操作。
首先，让我们创建一个名为phoenix_sample的数据库。

MariaDB [(none)]> CREATE DATABASE phoenix_sample;

下一步是创建表格。
这次我们尝试在名为ipa_t的表格中管理IPA（印度淡色艾尔）的种类。

MariaDB [(none)]> use phoenix_sample
Database changed
MariaDB [phoenix_sample]> CREATE TABLE ipa_t (
    ->   id INT AUTO_INCREMENT,
    ->   name varchar(256) NOT NULL,
    ->   abv FLOAT NOT NULL,
    ->   PRIMARY KEY (id)
    -> );

我已经完成了准备工作。

安装Phoenix应用程序。

我們將創建一個名為mariadb_sample的應用程序。
通過添加–database mysql選項，將使用的數據庫從默認的PostgreSQL更改為MySQL（MariaDB）。

$ mix phoenix.new mariadb_sample --database mysql

我可以确定 mix.exs 文件中已经添加了用于连接MariaDB的mariaex库。

defmodule MariadbSample.Mixfile do
  ...
  defp deps do
    [{:phoenix, "~> 0.17"},
     {:phoenix_ecto, "~> 1.1"},
     {:mariaex, ">= 0.0.0"},
     {:phoenix_html, "~> 2.1"},
     {:phoenix_live_reload, "~> 1.0", only: :dev},
     {:cowboy, "~> 1.0"}]
  end
end

DB连接信息在名为 config/dev.exs 的文件中进行记录。
让我们将数据库的名称从默认的 mariadb_sample_dev 更改为 phoenix_sample。

...
# Configure your database
config :mariadb_sample, MariadbSample.Repo,
  adapter: Ecto.Adapters.MySQL,
  username: "root",
  password: "",
#  database: "mariadb_sample_dev",
  database: "phoenix_sample",
  pool_size: 10

以上，应用程序的设置已完成。

创建Insert和Select的端点

我們將創建一個可以插入和選擇IPA信息的端口。
我們將在web/router.ex中添加以下範圍。

defmodule MariadbSample.Router do
  ...
  scope "/ipa", MariadbSample do
    pipe_through :api

    get "/", MariadbSample, :select_all
    get "/:id", MariadbSample, :select
    post "/", MariadbSample, :insert
  end
  ...
end

下面是在web/controllers/page_controller.ex中实现每个端点的步骤。

defmodule MariadbSample.PageController do
  ...
  def to_column_map(columns, rows) do
    Enum.map(rows, fn row -> Enum.into(List.zip([columns, row]), %{}) end)
  end

  def select_all(conn, _params) do
    case Ecto.Adapters.SQL.query(MariadbSample.Repo, "SELECT id, name, abv from ipa_t", []) do
      {:ok, result} -> json conn, to_column_map(result[:columns], result[:rows])
      {:error, e} -> json conn, %{error: e}
    end
  end

  def select(conn, %{"id" => id}) do
    case Ecto.Adapters.SQL.query(MariadbSample.Repo, "SELECT id, name, abv from ipa_t where id = ?", [id]) do
      {:ok, result} -> json conn, to_column_map(result[:columns], result[:rows])
      {:error, e} -> json conn, %{error: e}
    end
  end

  def insert(conn, _params) do
    {:ok, data, _conn_details} = Plug.Conn.read_body(conn)
    posted = Poison.Parser.parse!(data)
    case Ecto.Adapters.SQL.query(MariadbSample.Repo, "INSERT INTO ipa_t(name, abv) VALUE (?, ?)", [posted["name"], posted["abv"]]) do
      {:ok, result} -> json conn, %{succeed: 1}
      {:error, e} -> json conn, %{error: e}
    end
    json conn, nil
  end
end

在选择的终端节点上，发出的 SQL 查询非常简单。select_all 会获取全部数据，而 select 则会根据指定的 id 进行搜索。

Ecto.Adapters.SQL.query/3 是一个用于执行原始SQL查询的函数，它的第一个参数是 Repo，第二个参数是 SQL（预准备语句），第三个参数是要绑定的参数列表。

返回值可以是 {:ok, …}，表示成功；或者是 {:error, …}，表示失败。根据不同的返回值，我们可以使用 case 语句来进行处理。

在成功时调用的函数to_column_map/2被定义为将”列名列表”和”值列表”这两个列表合并为一个映射的函数。
每个被内部调用的函数的作用如下所示。

iex(31)> keys = ["a", "b", "c"]
["a", "b", "c"]
iex(32)> values = ["1", "2,", "3"]
["1", "2,", "3"]
iex(33)> zipped = List.zip([keys, values])
[{"a", "1"}, {"b", "2,"}, {"c", "3"}]
iex(34)> Enum.into(zipped, %{})
%{"a" => "1", "b" => "2,", "c" => "3"}

用内置函数来实现这些功能，确实体现了现代编程语言的特点。

在这种情况下，发出的 SQL 语句与以前并没有什么特别的改变。唯一的变化是我们现在解析了 POST 请求中的 JSON 数据，并创建了用于插入的数据。在 Phoenix（虽然这可能不是官方的写法），只需使用 {:ok, data, _conn_details} = Plug.Conn.read_body(conn) 便可以轻松获取到请求体字符串，然后再使用 Poison 进行解析。

在完全忽略异常处理的情况下，使用Ecto进行原生SQL查询的实现如下：

尝试调用 API

让我们立即调用已经创建好的 API。
首先，我们将尝试使用 DHC 发送数据进行 POST。

スクリーンショット 2015-09-07 2.39.20.png

如果按下发送按钮后返回 {“succeed”: 1}，则表示成功。
根据前十位国际音标协会（IPA）的信息，随意注册2-3种IPA。

等数据输入完毕后，我会尝试进行搜索。
进行全面搜索时，会显示如下结果。

$ curl -s "http://localhost:4000/ipa/" | jq .
[
  {
    "abv": 5,
    "id": 1,
    "name": "Samuel Smith's India Ale"
  },
  {
    "abv": 5.599999904632568,
    "id": 2,
    "name": "Toasted Oak India Pale Ale"
  },
  {
    "abv": 8.699999809265137,
    "id": 3,
    "name": "DirtWolf Double IPA"
  }
]

小数值似乎有些可疑，但基本上得到了预期的结果。
接下来，进行ID提取会是这样的情况。

$ curl -s "http://localhost:4000/ipa/2" | jq .
[
  {
    "abv": 5.599999904632568,
    "id": 2,
    "name": "Toasted Oak India Pale Ale"
  }
]

这里除了小数值之外，其它都是按预期的行为。

想法

    • Ecto にも（一応）生 SQL を実行する API があって、ちゃんと使えた

 

    • ただし、小数値の挙動をみる限り型の考慮がアヤシイ

このままだと日付とかの扱いが面倒なことになりそう
なんとか手をうったほうがよい

SQL と関係ないけど Request Body を取得するスニペットの挙動もアヤシイ

curl コマンドで JSON 食わせるとうまく動かなかった

✖
广告
将在 10 秒后关闭
bannerAds