2018年10月22日

Phoenix_7_JavaScript

Watching Video

  • 修改 views 改成可以查看 videos
  • 建立一個新的 controller for watching video
  • 修改 router for new routes
  • 增加 JavaScript 以使用 YouTube API

修改 /web/templates/layout/app.html.eex 的 header

        <div class="header">
        <ol class="breadcrumb text-right">
          <%= if @current_user do %>
            <li><%= @current_user.username %></li>
            <li><%= link "My Videos", to: video_path(@conn, :index) %></li>
            <li>
              <%= link "Log out", to: session_path(@conn, :delete, @current_user),
                                  method: "delete" %>
            </li>
          <% else %>
            <li><%= link "Register", to: user_path(@conn, :new) %></li>
            <li><%= link "Log in", to: session_path(@conn, :new) %></li>
          <% end %>
        </ol>
        <span class="logo"></span>
      </div>

登入後,點擊 header 上面的 "My Videos" 進入 http://localhost:4000/manage/videos


新增 /web/controllers/watch_controller.ex

defmodule Rumbl.WatchController do
  use Rumbl.Web, :controller
  alias Rumbl.Video

  def show(conn, %{"id" => id}) do
    video = Repo.get!(Video, id)
    render conn, "show.html", video: video
  end
end

/web/templates/watch/show.html.eex

<h2><%= @video.title %></h2>
<div class="row">
  <div class="col-sm-7">
    <%= content_tag :div, id: "video",
          data: [id: @video.id, player_id: player_id(@video)] do %>
    <% end %>
  </div>
  <div class="col-sm-5">
    <div class="panel panel-default">
      <div class="panel-heading">
        <h3 class="panel-title">Annotations</h3>
      </div>
      <div id="msg-container" class="panel-body annotations">

      </div>
      <div class="panel-footer">
        <textarea id="msg-input"
                  rows="3"
                  class="form-control"
                  placeholder="Comment..."></textarea>
        <button id="msg-submit" class="btn btn-primary form-control"
type="submit">
          Post
        </button>
      </div>
    </div>
  </div>
</div>

/web/views/watch_view.ex

defmodule Rumbl.WatchView do
  use Rumbl.Web, :view

  def player_id(video) do
    ~r{^.*(?:youtu\.be/|\w+/|v=)(?<id>[^#&?]*)}
    |> Regex.named_captures(video.url)
    |> get_in(["id"])
  end
end

修改 /web/router.ex

  scope "/", Rumbl do
    pipe_through :browser # Use the default browser stack

    get "/", PageController, :index
    resources "/users", UserController, only: [:index, :show, :new, :create]

    resources "/sessions", SessionController, only: [:new, :create, :delete]

    get "/watch/:id", WatchController, :show
  end

修改 /web/templates/video/index.html.eex

<h2>Listing videos</h2>

<table class="table">
  <thead>
    <tr>
      <th>User</th>
      <th>Url</th>
      <th>Title</th>
      <th>Description</th>

      <th></th>
    </tr>
  </thead>
  <tbody>
<%= for video <- @videos do %>
    <tr>
      <td><%= video.user_id %></td>
      <td><%= video.url %></td>
      <td><%= video.title %></td>
      <td><%= video.description %></td>

      <td class="text-right">
        <%= link "Watch", to: watch_path(@conn, :show, video),
                          class: "btn btn-default btn-xs" %>

        <%= link "Edit", to: video_path(@conn, :edit, video),
                         class: "btn btn-default btn-xs" %>

        <%= link "Delete", to: video_path(@conn, :delete, video),
                           method: :delete,
                           data: [confirm: "Are you sure?"],
                           class: "btn btn-danger btn-xs" %>
      </td>
    </tr>
<% end %>
  </tbody>
</table>

<%= link "New video", to: video_path(@conn, :new) %>

Adding JavaScript

Brunch 是用 Node.js 撰寫的 build tool,Phoenix 使用 Brunch 去 build, transform, minify JS code,也能處理 css 及 assets。

Brunch 資料夾結構

web/static
  - assets
  - css
  - js
  - vendor

放在 assets 目錄是不需要 Brunch 轉換的資源,只會被複製到 priv/static,這個目錄是由 Phoenix.Static 作為 endpoint。

vendor 目錄是放 3rd party tools 例如 jQuery,external dependencies 不需要 import。

Brunch 是使用 ECMAScript6 (ES6) version,有支援 import 功能。每個 file 是一個 function,除非 import 到 app.js,否則不會自動被 browser 執行。

/web/static/js/app.js 裡面有一行

import "phoenix_html"

這就是 /web/templates/layout/app.html.eex 最後面 include 的 js

<script src="<%= static_path(@conn, "/js/app.js") %>"></script>

可在 brunch-config.js 填寫 Brunch 的設定

brunch 有三個指令

  1. brunch build

    build 所有 static files,compiling & copy 結果到 /priv/static

  2. brunch build --production

    build & minifies

  3. brunch watch

    開發時使用,brunch 會自動 recompile files。通常不需要執行,因為 Phoenix 已經有啟動了。

    /config/dev.exs 裡面有一行 watchers 設定

    watchers: [node: ["node_modules/brunch/bin/brunch", "watch", "--stdin",
                    cd: Path.expand("../", __DIR__)]]

新增 /web/static/js/player.js

let Player = {
  player: null,

  init(domId, playerId, onReady){
    window.onYouTubeIframeAPIReady = () => {
      this.onIframeReady(domId, playerId, onReady)
    }
    let youtubeScriptTag = document.createElement("script")
    youtubeScriptTag.src = "//www.youtube.com/iframe_api"
    document.head.appendChild(youtubeScriptTag)
  },

  onIframeReady(domId, playerId, onReady){
    this.player = new YT.Player(domId, {
      height: "360",
      width: "420",
      videoId: playerId,
      events: {
        "onReady":  (event => onReady(event) ),
        "onStateChange": (event => this.onPlayerStateChange(event) )
      }
    })
  },

  onPlayerStateChange(event){ },
  getCurrentTime(){ return Math.floor(this.player.getCurrentTime() * 1000) },
  seekTo(millsec){ return this.player.seekTo(millsec / 1000) }
}
export default Player

修改 /web/static/js/app.js,這樣才會編譯 player.js

import "phoenix_html"

import Player from "./player"
let video = document.getElementById("video")

if(video) {
    Player.init(video.id, video.getAttribute("data-player-id"), () => {
        console.log("player ready!")
    })
}

新增 /web/static/css/video.css

#msg-container {
  min-height: 190px;
}

Creating Slugs

如希望 videos 有一個唯一的 URL-friendly identified,稱為 slug,就需要一個 table 欄位,記錄給 search engine 使用的 unique URL。ex: 1-elixir

add a slug column to table videos

mix ecto.gen.migration add_slug_to_video

修改 migration

defmodule Rumbl.Repo.Migrations.AddSlugToVideo do
  use Ecto.Migration

  def change do
    alter table(:videos) do
      add :slug, :string
    end
  end
end

升級 DB

$ mix ecto.migrate
[info] == Running Rumbl.Repo.Migrations.AddSlugToVideo.change/0 forward
[info] alter table videos
[info] == Migrated in 0.0s

修改 /web/models/video.ex,增加 slug 欄位,並在 changeset 加上 slugify_title()


defmodule Rumbl.Video do
  use Rumbl.Web, :model

  schema "videos" do
    field :url, :string
    field :title, :string
    field :description, :string
    field :slug, :string
    belongs_to :user, Rumbl.User
    belongs_to :category, Rumbl.Category

    timestamps
  end

  @required_fields ~w(url title description)
  @optional_fields ~w(category_id)

  def changeset(model, params \\ %{}) do
    model
    |> cast(params, @required_fields, @optional_fields)
    |> slugify_title()
    |> assoc_constraint(:category)
  end

  defp slugify_title(changeset) do
    if title = get_change(changeset, :title) do
      put_change(changeset, :slug, slugify(title))
    else
      changeset
    end
  end

  defp slugify(str) do
    str
    |> String.downcase()
    |> String.replace(~r/[^\w-]+/u, "-")
  end
end
  • 因 Ecto 區隔了 changeset 及 record 定義,可將 change policy 分開,也能在 create video 的 JSON API 加上 slug

  • changeset 會 filter and cast 新資料,確保一些敏感資料不會從系統外面進來

  • changeset 可以 validate 資料

  • changeset 讓程式碼更容易閱讀及實作


Extending Phoenix with Protocols

查看 /web/templates/video/index.html.eex 產生 link 的部分

<%= link "Watch", to: watch_path(@conn, :show, video),
class: "btn btn-default btn-xs" %>

為了改用 slug,就修改為

watch_path(@conn, :show, "#{video.id}-#{video.slug}")

Phoenix.Param 是 Elixir Protocol,可謂任意一個 data type 自訂此參數。

修改 /web/models/video.ex 增加 defimpl Phoenix.Param, for: Rumbl.Video

  defimpl Phoenix.Param, for: Rumbl.Video do
    def to_param(%{slug: slug, id: id}) do
      "#{id}-#{slug}"
    end
  end

IEx 測試

iex(1)> video = %Rumbl.Video{id: 1, slug: "hello"}
%Rumbl.Video{__meta__: #Ecto.Schema.Metadata<:built, "videos">,
 category: #Ecto.Association.NotLoaded<association :category is not loaded>,
 category_id: nil, description: nil, id: 1, inserted_at: nil, slug: "hello",
 title: nil, updated_at: nil, url: nil,
 user: #Ecto.Association.NotLoaded<association :user is not loaded>,
 user_id: nil}


iex(2)> Rumbl.Router.Helpers.watch_path(%URI{}, :show, video)
"/watch/1-hello"
iex(4)> url = URI.parse("http://example.com/prefix")
%URI{authority: "example.com", fragment: nil, host: "example.com",
 path: "/prefix", port: 80, query: nil, scheme: "http", userinfo: nil}
iex(5)> Rumbl.Router.Helpers.watch_path(url, :show, video)
"/prefix/watch/1-hello"
iex(6)> Rumbl.Router.Helpers.watch_url(url, :show, video)
"http://example.com/prefix/watch/1-hello"

可使用 Rumbl.Endpoint.struct_url

iex(8)> url = Rumbl.Endpoint.struct_url
%URI{authority: nil, fragment: nil, host: "localhost", path: nil, port: 4000,
 query: nil, scheme: "http", userinfo: nil}
iex(9)> Rumbl.Router.Helpers.watch_url(url, :show, video)
"http://localhost:4000/watch/1-hello"

Extending Schemas with Ecto Types

新增 /lib/rumbl/permalink.ex


defmodule Rumbl.Permalink do
  @behaviour Ecto.Type

  def type, do: :id

  def cast(binary) when is_binary(binary) do
    case Integer.parse(binary) do
      {int, _} when int > 0 -> {:ok, int}
      _ -> :error
    end
  end

  def cast(integer) when is_integer(integer) do
    {:ok, integer}
  end

  def cast(_) do
    :error
  end

  def dump(integer) when is_integer(integer) do
    {:ok, integer}
  end

  def load(integer) when is_integer(integer) do
    {:ok, integer}
  end
end

Rumbl.Permalink 是根據 Ecto.Type behavior 定義的 custom type,需要定義四個 functions

  1. type

    回傳 underlying Ecto type,目前是以 :id 來建構

  2. cast

    當 external data 傳入 Ecto 時會呼叫,在 values in queries 被 interpolated 或是在 changeset 的 cast 被呼叫

  3. dump

    當 data 發送給 database 時被呼叫

  4. load

    由 DB 載入資料時被呼叫

iex(1)> alias Rumbl.Permalink, as: P
Rumbl.Permalink
iex(2)> P.cast "1"
{:ok, 1}
iex(3)> P.cast 1
{:ok, 1}
iex(4)> P.cast "13-hello-world"
{:ok, 13}
iex(5)> P.cast "hello-world-13"
:error

web/models/video.ex 增加 @primary_key

  @primary_key {:id, Rumbl.Permalink, autogenerate: true}
  schema "videos" do

就可以使用 http://localhost:4000/watch/2-elixir 這樣的 URL

References

Programming Phoenix

2018年10月15日

Phoenix_6_TestingMVC

ExUnit

ExUnit 有三個主要的 macros

  1. setup

    setup code that runs once before each test

  2. test

    單一 isolated test,每次執行 test 前,都會先執行 setup

  3. assert

    驗證結果

defmodule MyTest do
    use ExUnit.Case, async: true

    setup do
        # run some tedious setup code
        :ok
    end

    test "pass" do
        assert true
    end
    
    test "fail" do
        assert false
    end
end

以 Mix 執行 Phoenix Tests

Phoenix 會產生幾個 test,ex: test/controllers/videocontrollertest.exs,要先修改 /config/test.exs 的 DB 設定,再用 mix test 執行測試

mix test

/test/support/conn_case.ex

defmodule Rumbl.ConnCase do
  use ExUnit.CaseTemplate

  using do
    quote do
      # Import conveniences for testing with connections
      use Phoenix.ConnTest

      alias Rumbl.Repo
      import Ecto
      import Ecto.Changeset
      import Ecto.Query

      import Rumbl.Router.Helpers

      # The default endpoint for testing
      @endpoint Rumbl.Endpoint
    end
  end

  setup tags do
    :ok = Ecto.Adapters.SQL.Sandbox.checkout(Rumbl.Repo)

    unless tags[:async] do
      Ecto.Adapters.SQL.Sandbox.mode(Rumbl.Repo, {:shared, self()})
    end

    {:ok, conn: Phoenix.ConnTest.build_conn()}
  end
end

注意,他是使用 @endpoint Rumbl.Endpoint


test/controllers/pagecontrollertest.ex

defmodule Rumbl.PageControllerTest do
  use Rumbl.ConnCase

  test "GET /", %{conn: conn} do
    conn = get conn, "/"
    assert html_response(conn, 200) =~ "Welcome to Phoenix!"
  end
end

這個測試會驗證三個部分

  • 檢查 conn 的 response 是否為 200
  • 檢查 response content-type 是否為 text/html
  • 回傳 response body

如果是 json 就改成 assert %{user_id: user.id} = json_response(conn, 200)

修改剛剛的測試

assert html_response(conn, 200) =~ "Welcome to Rumbl.io"

可得到 pass 結果正確

$ mix test test/controllers/page_controller_test.exs
.

Finished in 0.1 seconds
1 test, 0 failures

Randomized with seed 354626

Integration Tests

Creating Test Data

新增 test/support/test_helpers.ex ,新增 user 及 video

defmodule Rumbl.TestHelpers do
  alias Rumbl.Repo

  def insert_user(attrs \\ %{}) do
    changes = Dict.merge(%{
      name: "Some User",
      username: "user#{Base.encode16(:crypto.rand_bytes(8))}",
      password: "supersecret",
    }, attrs)

    %Rumbl.User{}
    |> Rumbl.User.registration_changeset(changes)
    |> Repo.insert!()
  end

  def insert_video(user, attrs \\ %{}) do
    user
    |> Ecto.build_assoc(:videos, attrs)
    |> Repo.insert!()
  end
end
Testing Logged-Out Users

修改 /test/support/conn_case.ex

  using do
    quote do
      # Import conveniences for testing with connections
      use Phoenix.ConnTest

      alias Rumbl.Repo
      import Ecto
      import Ecto.Changeset
      import Ecto.Query, only: [from: 1, from: 2]

      import Rumbl.Router.Helpers
      
      # 增加 TestHelpers
      import Rumbl.TestHelpers 

      # The default endpoint for testing
      @endpoint Rumbl.Endpoint
    end
  end

新增 /test/controllers/videocontrollertest.ex,因為沒有登入,測試所有的連線都是以 302 為結果

defmodule Rumbl.VideoControllerTest do
  use Rumbl.ConnCase

  test "requires user authentication on all actions", %{conn: conn} do
    Enum.each([
      get(conn, video_path(conn, :new)),
      get(conn, video_path(conn, :index)),
      get(conn, video_path(conn, :show, "123")),
      get(conn, video_path(conn, :edit, "123")),
      put(conn, video_path(conn, :update, "123", %{})),
      post(conn, video_path(conn, :create, %{})),
      delete(conn, video_path(conn, :delete, "123")),
    ], fn conn ->
      assert html_response(conn, 302)
      assert conn.halted
    end)
  end
end
Preparing for Logged-In Users

修改 /web/controllers/auth.ex, 使用 cond 檢查多個條件

  def call(conn, repo) do
    user_id = get_session(conn, :user_id)

    cond do
      user = conn.assigns[:current_user] ->
        conn
      user = user_id && repo.get(Rumbl.User, user_id) ->
        assign(conn, :current_user, user)
      true ->
        assign(conn, :current_user, nil)
    end
  end

舊版本為

  # 收到 init 的 repository
  def call(conn, repo) do
    # 檢查 session 是否有存在 :user_id
    user_id = get_session(conn, :user_id)
    # 如果有 user_id 且 User DB 有這個 user_id
    # 利用 assign 把這個 user 資料存放在 conn.assigns
    user    = user_id && repo.get(Rumbl.User, user_id)
    # 後面可以用 :current_user 取得 User 資料
    assign(conn, :current_user, user)
  end
Testing Logged-In Users

/test/controllers/videocontrollertest.ex

  setup do
    user = insert_user(username: "max")
    conn = assign(conn(), :current_user, user)
    {:ok, conn: conn, user: user}
  end

  test "lists all user's videos on index", %{conn: conn, user: user} do
    user_video  = insert_video(user, title: "funny cats")
    other_video = insert_video(insert_user(username: "other"), title: "another video")

    conn = get conn, video_path(conn, :index)
    assert html_response(conn, 200) =~ ~r/Listing videos/
    assert String.contains?(conn.resp_body, user_video.title)
    refute String.contains?(conn.resp_body, other_video.title)
  end
Controlling Duplication with Tagging

因有些測試需要登入,有些不要,setup 要區分這兩種狀況。可使用 ExUnit tags 解決此問題

  setup %{conn: conn} = config do
    if username = config[:login_as] do
      user = insert_user(username: username)
      conn = assign(conn, :current_user, user)
      {:ok, conn: conn, user: user}
    else
      :ok
    end
  end

  @tag login_as: "max" 
  test "lists all user's videos on index", %{conn: conn, user: user} do
    user_video  = insert_video(user, title: "funny cats")
    other_video = insert_video(insert_user(username: "other"), title: "another video")

    conn = get conn, video_path(conn, :index)
    assert html_response(conn, 200) =~ ~r/Listing videos/
    assert String.contains?(conn.resp_body, user_video.title)
    refute String.contains?(conn.resp_body, other_video.title)
  end

測試

$ mix test test/controllers --only login_as
Compiling 13 files (.ex)
warning: function authenticate/2 is unused
  web/controllers/user_controller.ex:40

Including tags: [:login_as]
Excluding tags: [:test]

.

Finished in 0.8 seconds
3 tests, 0 failures, 2 skipped

Randomized with seed 158230

增加 create a video 的測試

  alias Rumbl.Video
  @valid_attrs %{url: "http://youtu.be", title: "vid", description: "a vid"}
  @invalid_attrs %{title: "invalid"}

  defp video_count(query), do: Repo.one(from v in query, select: count(v.id))

  @tag login_as: "max"
  test "creates user video and redirects", %{conn: conn, user: user} do
    conn = post conn, video_path(conn, :create), video: @valid_attrs
    assert redirected_to(conn) == video_path(conn, :index)
    assert Repo.get_by!(Video, @valid_attrs).user_id == user.id
  end

  @tag login_as: "max"
  test "does not create video and renders errors when invalid", %{conn: conn} do
    count_before = video_count(Video)
    conn = post conn, video_path(conn, :create), video: @invalid_attrs
    assert html_response(conn, 302) =~ "redirected"
#    assert video_count(Video) == count_before
  end
  
    @tag login_as: "max"
  test "authorizes actions against access by other users",
       %{user: owner, conn: conn} do

    video = insert_video(owner, @valid_attrs)
    non_owner = insert_user(username: "sneaky")
    conn = assign(conn, :current_user, non_owner)

    assert_error_sent :not_found, fn ->
      get(conn, video_path(conn, :show, video))
    end
    assert_error_sent :not_found, fn ->
      get(conn, video_path(conn, :edit, video))
    end
    assert_error_sent :not_found, fn ->
      put(conn, video_path(conn, :update, video, video: @valid_attrs))
    end
    assert_error_sent :not_found, fn ->
      delete(conn, video_path(conn, :delete, video))
    end
  end
Unit-Testing Plugs

/test/controllers/auth_test.exs


defmodule Rumbl.AuthTest do
  use Rumbl.ConnCase
  alias Rumbl.Auth

  setup %{conn: conn} do
    conn =
      conn
      |> bypass_through(Rumbl.Router, :browser)
      |> get("/")

    {:ok, %{conn: conn}}
  end

  test "authenticate_user halts when no current_user exists",
       %{conn: conn} do

    conn = Auth.authenticate_user(conn, [])
    assert conn.halted
  end

  test "authenticate_user continues when the current_user exists",
       %{conn: conn} do

    conn =
      conn
      |> assign(:current_user, %Rumbl.User{})
      |> Auth.authenticate_user([])

    refute conn.halted
  end
end

測試 login logout

  test "login puts the user in the session", %{conn: conn} do
     # 新的 connection
    login_conn =
      conn
      |> Auth.login(%Rumbl.User{id: 123})
      |> send_resp(:ok, "")

    next_conn = get(login_conn, "/")
    # 檢查是否在 session 裡面
    assert get_session(next_conn, :user_id) == 123
  end

  test "logout drops the session", %{conn: conn} do
    logout_conn =
      conn
      |> put_session(:user_id, 123)
      |> Auth.logout()
      |> send_resp(:ok, "")

    next_conn = get(logout_conn, "/")
    refute get_session(next_conn, :user_id)
  end

檢查 assigns 裡面的 current_user

  test "call places user from session into assigns", %{conn: conn} do
    user = insert_user()
    conn =
      conn
      |> put_session(:user_id, user.id)
      |> Auth.call(Repo)

    assert conn.assigns.current_user.id == user.id
  end

  test "call with no session sets current_user assign to nil", %{conn: conn} do
    conn = Auth.call(conn, Repo)
    assert conn.assigns.current_user == nil
  end
  test "login with a valid username and pass", %{conn: conn} do 
    user = insert_user(username: "me", password: "secret")
    {:ok, conn} =
      Auth.login_by_username_and_pass(conn, "me", "secret", repo: Repo)

    assert conn.assigns.current_user.id == user.id
  end

  test "login with a not found user", %{conn: conn} do 
    assert {:error, :not_found, _conn} =
      Auth.login_by_username_and_pass(conn, "me", "secret", repo: Repo)
  end

  test "login with password mismatch", %{conn: conn} do 
    _ = insert_user(username: "me", password: "secret")
    assert {:error, :unauthorized, _conn} =
      Auth.login_by_username_and_pass(conn, "me", "wrong", repo: Repo)
  end

Testing Views and Templates

defmodule Rumbl.VideoViewTest do
  use Rumbl.ConnCase, async: true
  import Phoenix.View

  test "renders index.html", %{conn: conn} do
    # 以 videos render 頁面
    videos = [%Rumbl.Video{id: "1", title: "dogs"},
      %Rumbl.Video{id: "2", title: "cats"}]
    content = render_to_string(Rumbl.VideoView, "index.html",
      conn: conn, videos: videos)

    assert String.contains?(content, "Listing videos")
    for video <- videos do
      assert String.contains?(content, video.title)
    end
  end


  test "renders new.html", %{conn: conn} do
    # 以 changeset 及 categories assigns 去 render 頁面
    changeset = Rumbl.Video.changeset(%Rumbl.Video{})
    categories = [{"cats", 123}]

    content = render_to_string(Rumbl.VideoView, "new.html",
      conn: conn, changeset: changeset, categories: categories)

    assert String.contains?(content, "New video")
  end
end

Spliting Side Effects in Model Tests

Testing Side Effect-Free Model Code

在 test/support/modelcase.ex 中 import TestHelpers,修改 errorson function

    using do
    quote do
      alias Rumbl.Repo

      import Ecto
      import Ecto.Changeset
      import Ecto.Query, only: [from: 1, from: 2]
      import Rumbl.TestHelpers 
      import Rumbl.ModelCase
    end
  end
  
  
  def errors_on(model, data) do
    model.__struct__.changeset(model, data).errors
  end

新增 /test/models/user_test.exs

defmodule Rumbl.UserTest do

  # 設定為 async,因為目標是要區隔每個 test,可平行處理
  use Rumbl.ModelCase, async: true
  alias Rumbl.User

  @valid_attrs %{name: "A User", username: "eva", password: "secret"}
  @invalid_attrs %{}

  test "changeset with valid attributes" do
    changeset = User.changeset(%User{}, @valid_attrs)
    assert changeset.valid?
  end

  test "changeset with invalid attributes" do
    changeset = User.changeset(%User{}, @invalid_attrs)
#    refute changeset.valid?
    assert changeset.valid?
  end

  test "changeset does not accept long usernames" do
    attrs = Map.put(@valid_attrs, :username, String.duplicate("a", 30))
    # 使用 ModelCase 定義的 error_on function,快速取得 changeset 裡面的 errors
#    assert {:username, {"should be at most %{count} character(s)", [count: 20]}} in
#             errors_on(%User{}, attrs)

    assert [username: {"should be at most %{count} character(s)", [count: 20, validation: :length, max: 20]}] ==
             errors_on(%User{}, attrs)
  end
  
  
  test "registration_changeset password must be at least 6 chars long" do
    attrs = Map.put(@valid_attrs, :password, "12345")
    changeset = User.registration_changeset(%User{}, attrs)
#    assert {:password, {"should be at least %{count} character(s)", count: 6}}
#           in changeset.errors

    assert [password: {"should be at least %{count} character(s)",
              [count: 6, validation: :length, min: 6]}]
           == changeset.errors
  end

  test "registration_changeset with valid attributes hashes password" do
    attrs = Map.put(@valid_attrs, :password, "123456")
    changeset = User.registration_changeset(%User{}, attrs)
    %{password: pass, password_hash: pass_hash} = changeset.changes

    assert changeset.valid?
    assert pass_hash
    assert Comeonin.Bcrypt.checkpw(pass, pass_hash)
  end
end
Testing Code with Side Effect

新增 /test/models/userrepotest.exs


defmodule Rumbl.UserRepoTest do
  use Rumbl.ModelCase
  alias Rumbl.User

  @valid_attrs %{name: "A User", username: "eva"}

  test "converts unique_constraint on username to error" do
    insert_user(username: "eric")
    attrs = Map.put(@valid_attrs, :username, "eric")
    changeset = User.changeset(%User{}, attrs)

    assert {:error, changeset} = Repo.insert(changeset) 
#    assert {:username, "has already been taken"} in changeset.errors
    assert [username: {"has already been taken", []}] == changeset.errors
  end
end
$ mix test test/models/user_repo_test.exs
.

Finished in 0.4 seconds
1 test, 0 failures

defmodule Rumbl.CategoryRepoTest do
  use Rumbl.ModelCase
  alias Rumbl.Category

  test "alphabetical/1 orders by name" do
    Repo.insert!(%Category{name: "c"})
    Repo.insert!(%Category{name: "a"})
    Repo.insert!(%Category{name: "b"})

    query = Category |> Category.alphabetical()
    query = from c in query, select: c.name
#    assert ~w(a b c) == Repo.all(query)
    assert ~w(a Action b c Comedy Drama Romance Sci-fi) == Repo.all(query)
  end
end
$ mix test test/models/category_repo_test.exs
.

Finished in 0.08 seconds
1 test, 0 failures

References

Programming Phoenix

2018年10月8日

Phoenix_5_DeeperEcto

更深入了解 Ecto

Generators

瞭解如何定義 table 的關聯,User 會選擇 video,video裡有多個 comments(annotations),comments 是由 User 建立的。

可使用 generators 產生 skeleton: 包含 migration, controllers, templates

video 相關的欄位為

  1. an associated User
  2. A creation time for the video
  3. A URL of the video location
  4. A title
  5. type of the video

使用 phoenix.gen.html Mix task

mix phoenix.gen.html Video videos user_id:references:users url:string title:string description:text
$ mix phoenix.gen.html Video videos user_id:references:users url:string title:string description:text
* creating web/controllers/video_controller.ex
* creating web/templates/video/edit.html.eex
* creating web/templates/video/form.html.eex
* creating web/templates/video/index.html.eex
* creating web/templates/video/new.html.eex
* creating web/templates/video/show.html.eex
* creating web/views/video_view.ex
* creating test/controllers/video_controller_test.exs
* creating web/models/video.ex
* creating test/models/video_test.exs
* creating priv/repo/migrations/20170905083531_create_video.exs

Add the resource to your browser scope in web/router.ex:

    resources "/videos", VideoController

Remember to update your repository by running migrations:

    $ mix ecto.migrate

執行以下這個指令,DB 就會產生 table: videos

mix ecto.migrate

執行後會得到

  1. 定義 model 的 module name
  2. model name
  3. 每個欄位及 type information

如果要限制 /videos 只能讓已登入的使用者使用,前面已經寫過 authentication 的 functions

/web/controller/user_controller.ex

  defp authenticate(conn, _opts) do
    if conn.assigns.current_user do
      conn
    else
      conn
      |> put_flash(:error, "You must be logged in to access that page")
      |> redirect(to: page_path(conn, :index))
      |> halt()
    end
  end

把這個部分的 code 移到 /web/controllers/auth.ex

  import Phoenix.Controller
  alias Rumbl.Router.Helpers

  def authenticate_user(conn, _opts) do
    if conn.assigns.current_user do
      conn
    else
      conn
      |> put_flash(:error, "You must be logged in to access that page")
      |> redirect(to: Helpers.page_path(conn, :index))
      |> halt()
    end
  end

修改 /web/web.ex ,增加 import Rumbl.Auth, only: [authenticate_user: 2],把 authenticate_user 提供給 controller 及 router 使用

  def controller do
    quote do
      use Phoenix.Controller

      alias Rumbl.Repo
      import Ecto
      import Ecto.Query

      import Rumbl.Router.Helpers
      import Rumbl.Gettext

      import Rumbl.Auth, only: [authenticate_user: 2] # New import
    end
  end
  
  def router do
    quote do
      use Phoenix.Router

      import Rumbl.Auth, only: [authenticate_user: 2] # New import
    end
  end

修改 /web/controllers/usercontroller.ex ,將 :authenticate plug 改成 :authenticateuser

plug :authenticate_user when action in [:index, :show]

回到 router,定義新 scope /manage 包含 /videos resources

  scope "/manage", Rumbl do
    pipe_through [:browser, :authenticate_user]

    resources "/videos", VideoController
  end

測試一下 new, update, delete, read video 資料,全部都有了


VideoController 也有 pipeline,可使用 scrub_params

plug :scrub_params, "video" when action in [:create, :update]

因為 HTML form 沒有 nil 的概念,所以 blank input 都會被轉成 empty string,scrub_params 可將 form 參數裡面的 empty string 轉換為 nil


產生 DB Migrations

打開 /priv/repo/migrations/20170905083531_create_video.exs,增加 down function 處理 DB rollback

defmodule Rumbl.Repo.Migrations.CreateVideo do
  use Ecto.Migration

  def change do
    create table(:videos) do
      add :url, :string
      add :title, :string
      add :description, :text
      add :user_id, references(:users, on_delete: :nothing)

      timestamps()
    end
    create index(:videos, [:user_id])

  end

  def down do
#    drop constraint(:videos, "videos_user_id_fkey")
    execute "ALTER TABLE videos DROP FOREIGN KEY videos_user_id_fkey"
    alter table(:videos) do
      modify :user_id, references(:users, on_delete: :nothing)
    end

    drop_if_exists table("videos")
  end
end

可將 ecto videos table rollback 回去

$ mix ecto.rollback
[info] == Running Rumbl.Repo.Migrations.CreateVideo.down/0 forward
[info] execute "ALTER TABLE videos DROP FOREIGN KEY videos_user_id_fkey"
[info] alter table videos
[info] drop table if exists videos
[info] == Migrated in 0.0s


$ mix ecto.migrate
[info] == Running Rumbl.Repo.Migrations.CreateVideo.change/0 forward
[info] create table videos
[info] create index videos_user_id_index
[info] == Migrated in 0.0s

Building Relationships

/web/models/video.ex

defmodule Rumbl.Video do
  use Rumbl.Web, :model

  schema "videos" do
    field :url, :string
    field :title, :string
    field :description, :string

    # 定義 :user_id 欄位, 為 User.id 的 foreign key
    belongs_to :user, Rumbl.User

    timestamps()
  end

  @required_fields ~w(url title description)
  @optional_fields ~w()

  def changeset(model, params \\ %{}) do
    model
    |> cast(params, @required_fields, @optional_fields)
  end
end

/web/models/user.ex 加上 has_many :videos, Rumbl.Video

  use Rumbl.Web, :model
  schema "users" do
    field :name, :string
    field :username, :string
    field :password, :string, virtual: true
    field :password_hash, :string
    has_many :videos, Rumbl.Video

    timestamps()
  end

測試

$  iex -S mix

iex(1)> alias Rumbl.Repo
Rumbl.Repo
iex(2)> alias Rumbl.User
Rumbl.User
iex(3)> import Ecto.Query
Ecto.Query

iex(4)> user = Repo.get_by!(User, username: "josie")
[debug] QUERY OK source="users" db=4.2ms decode=2.6ms
SELECT u0.`id`, u0.`name`, u0.`username`, u0.`password_hash`, u0.`inserted_at`, u0.`updated_at` FROM `users` AS u0 WHERE (u0.`username` = ?) ["josie"]
%Rumbl.User{__meta__: #Ecto.Schema.Metadata<:loaded, "users">, id: 1,
 inserted_at: ~N[2017-09-03 13:09:55.000000], name: "Jose", password: nil,
 password_hash: "$2b$12$whoMkt3Va91Mk6yAmaM0sO/3dgh3nhNCem0M6xMB5UIMXo7vERw6O",
 updated_at: ~N[2017-09-05 01:22:52.000000], username: "josie",
 videos: #Ecto.Association.NotLoaded<association :videos is not loaded>}
 
iex(5)> user.videos
#Ecto.Association.NotLoaded<association :videos is not loaded>


# Repo.preload 接受 one / collection of names,取得所有相關的資料。
iex(6)> user = Repo.preload(user, :videos)
[debug] QUERY OK source="videos" db=1.6ms
SELECT v0.`id`, v0.`url`, v0.`title`, v0.`description`, v0.`user_id`, v0.`inserted_at`, v0.`updated_at`, v0.`user_id` FROM `videos` AS v0 WHERE (v0.`user_id` = ?) ORDER BY v0.`user_id` [1]
%Rumbl.User{__meta__: #Ecto.Schema.Metadata<:loaded, "users">, id: 1,
 inserted_at: ~N[2017-09-03 13:09:55.000000], name: "Jose", password: nil,
 password_hash: "$2b$12$whoMkt3Va91Mk6yAmaM0sO/3dgh3nhNCem0M6xMB5UIMXo7vERw6O",
 updated_at: ~N[2017-09-05 01:22:52.000000], username: "josie", videos: []}

iex(7)> user.videos
[]
iex(14)> user = Repo.get_by!(User, username: "josie")
[debug] QUERY OK source="users" db=0.8ms
SELECT u0.`id`, u0.`name`, u0.`username`, u0.`password_hash`, u0.`inserted_at`, u0.`updated_at` FROM `users` AS u0 WHERE (u0.`username` = ?) ["josie"]
%Rumbl.User{__meta__: #Ecto.Schema.Metadata<:loaded, "users">, id: 1,
 inserted_at: ~N[2017-09-03 13:09:55.000000], name: "Jose", password: nil,
 password_hash: "$2b$12$whoMkt3Va91Mk6yAmaM0sO/3dgh3nhNCem0M6xMB5UIMXo7vERw6O",
 updated_at: ~N[2017-09-05 01:22:52.000000], username: "josie",
 videos: #Ecto.Association.NotLoaded<association :videos is not loaded>}
 
 
iex(15)> attrs = %{title: "hi", description: "says hi", url: "example.com"}
%{description: "says hi", title: "hi", url: "example.com"}


iex(16)>  video = Ecto.build_assoc(user, :videos, attrs)
%Rumbl.Video{__meta__: #Ecto.Schema.Metadata<:built, "videos">,
 description: "says hi", id: nil, inserted_at: nil, title: "hi",
 updated_at: nil, url: "example.com",
 user: #Ecto.Association.NotLoaded<association :user is not loaded>, user_id: 1}
 
 
iex(17)> video = Repo.insert!(video)
[debug] QUERY OK db=0.2ms
begin []
[debug] QUERY OK db=6.2ms
INSERT INTO `videos` (`description`,`title`,`url`,`user_id`,`inserted_at`,`updated_at`) VALUES (?,?,?,?,?,?) ["says hi", "hi", "example.com", 1, {{2017, 9, 5}, {13, 37, 58, 291187}}, {{2017, 9, 5}, {13, 37, 58, 291202}}]
[debug] QUERY OK db=1.0ms
commit []
%Rumbl.Video{__meta__: #Ecto.Schema.Metadata<:loaded, "videos">,
 description: "says hi", id: 1, inserted_at: ~N[2017-09-05 13:37:58.291187],
 title: "hi", updated_at: ~N[2017-09-05 13:37:58.291202], url: "example.com",
 user: #Ecto.Association.NotLoaded<association :user is not loaded>, user_id: 1}

Ecto.buildassoc 可產生 struct,並有所有有關聯的欄位,呼叫 buildassoc 等同於呼叫

%Rumbl.Video{user_id: user.id, title: "hi", description: "says hi", url: "example.com"}

再重新測試一次

iex(1)> alias Rumbl.Repo
Rumbl.Repo
iex(2)> alias Rumbl.User
Rumbl.User
iex(3)> import Ecto.Query
Ecto.Query

iex(4)> user = Repo.get_by!(User, username: "josie")
[debug] QUERY OK source="users" db=2.2ms decode=2.5ms queue=0.1ms
SELECT u0.`id`, u0.`name`, u0.`username`, u0.`password_hash`, u0.`inserted_at`, u0.`updated_at` FROM `users` AS u0 WHERE (u0.`username` = ?) ["josie"]
%Rumbl.User{__meta__: #Ecto.Schema.Metadata<:loaded, "users">, id: 1,
 inserted_at: ~N[2017-09-03 13:09:55.000000], name: "Jose", password: nil,
 password_hash: "$2b$12$whoMkt3Va91Mk6yAmaM0sO/3dgh3nhNCem0M6xMB5UIMXo7vERw6O",
 updated_at: ~N[2017-09-05 01:22:52.000000], username: "josie",
 videos: #Ecto.Association.NotLoaded<association :videos is not loaded>}
 
 
iex(5)> user = Repo.preload(user, :videos)
[debug] QUERY OK source="videos" db=0.7ms
SELECT v0.`id`, v0.`url`, v0.`title`, v0.`description`, v0.`user_id`, v0.`inserted_at`, v0.`updated_at`, v0.`user_id` FROM `videos` AS v0 WHERE (v0.`user_id` = ?) ORDER BY v0.`user_id` [1]
%Rumbl.User{__meta__: #Ecto.Schema.Metadata<:loaded, "users">, id: 1,
 inserted_at: ~N[2017-09-03 13:09:55.000000], name: "Jose", password: nil,
 password_hash: "$2b$12$whoMkt3Va91Mk6yAmaM0sO/3dgh3nhNCem0M6xMB5UIMXo7vERw6O",
 updated_at: ~N[2017-09-05 01:22:52.000000], username: "josie",
 videos: [%Rumbl.Video{__meta__: #Ecto.Schema.Metadata<:loaded, "videos">,
   description: "says hi", id: 1, inserted_at: ~N[2017-09-05 13:37:58.000000],
   title: "hi", updated_at: ~N[2017-09-05 13:37:58.000000], url: "example.com",
   user: #Ecto.Association.NotLoaded<association :user is not loaded>,
   user_id: 1}]}

iex(6)> user.videos
[%Rumbl.Video{__meta__: #Ecto.Schema.Metadata<:loaded, "videos">,
  description: "says hi", id: 1, inserted_at: ~N[2017-09-05 13:37:58.000000],
  title: "hi", updated_at: ~N[2017-09-05 13:37:58.000000], url: "example.com",
  user: #Ecto.Association.NotLoaded<association :user is not loaded>,
  user_id: 1}]
  
  
  
iex(7)> query = Ecto.assoc(user, :videos)
#Ecto.Query<from v in Rumbl.Video, where: v.user_id == ^1>

iex(8)> Repo.all(query)
[debug] QUERY OK source="videos" db=0.7ms
SELECT v0.`id`, v0.`url`, v0.`title`, v0.`description`, v0.`user_id`, v0.`inserted_at`, v0.`updated_at` FROM `videos` AS v0 WHERE (v0.`user_id` = ?) [1]
[%Rumbl.Video{__meta__: #Ecto.Schema.Metadata<:loaded, "videos">,
  description: "says hi", id: 1, inserted_at: ~N[2017-09-05 13:37:58.000000],
  title: "hi", updated_at: ~N[2017-09-05 13:37:58.000000], url: "example.com",
  user: #Ecto.Association.NotLoaded<association :user is not loaded>,
  user_id: 1}]

Managing Related Data

video controller 可對 videos 進行 CRUD,但我們要將 videos 跟 users 連結在一起

可修改 /web/controllers/video_controller.ex

  # 跟現在登入的 user 連結在一起,用該 user create video
  def new(conn, _params) do
    changeset =
      conn.assigns.current_user
      |> build_assoc(:videos)
      |> Video.changeset()

    render(conn, "new.html", changeset: changeset)
  end

更進一步用另一種寫法,在 /web/controllers/video_controller.ex 加上 action(conn, _) ,同時修改 new, create 加上 user 參數

  def action(conn, _) do
    apply(__MODULE__, action_name(conn),
      [conn, conn.params, conn.assigns.current_user])
  end
  
  def new(conn, _params, user) do
    changeset =
      user
      |> build_assoc(:videos)
      |> Video.changeset()

    render(conn, "new.html", changeset: changeset)
  end

  def create(conn, %{"video" => video_params}, user) do
    changeset =
      user
      |> build_assoc(:videos)
      |> Video.changeset(video_params)

    case Repo.insert(changeset) do
      {:ok, _video} ->
        conn
        |> put_flash(:info, "Video created successfully.")
        |> redirect(to: video_path(conn, :index))
      {:error, changeset} ->
        render(conn, "new.html", changeset: changeset)
    end
  end

另外再增加 user_videos,取得某個 user 所有 videos

  defp user_videos(user) do
    assoc(user, :videos)
  end

再修改 index, show

  def index(conn, _params, user) do
    videos = Repo.all(user_videos(user))
    render(conn, "index.html", videos: videos)
  end
  
  def show(conn, %{"id" => id}, user) do
    video = Repo.get!(user_videos(user), id)
    render(conn, "show.html", video: video)
  end

update, edit, delete 也加上 user 參數

def edit(conn, %{"id" => id}, user) do
    video = Repo.get!(user_videos(user), id)
    changeset = Video.changeset(video)
    render(conn, "edit.html", video: video, changeset: changeset)
  end

  def update(conn, %{"id" => id, "video" => video_params}, user) do
    video = Repo.get!(user_videos(user), id)
    changeset = Video.changeset(video, video_params)

    case Repo.update(changeset) do
      {:ok, video} ->
        conn
        |> put_flash(:info, "Video updated successfully.")
        |> redirect(to: video_path(conn, :show, video))
      {:error, changeset} ->
        render(conn, "edit.html", video: video, changeset: changeset)
    end
  end

  def delete(conn, %{"id" => id}, user) do
    video = Repo.get!(user_videos(user), id)
    Repo.delete!(video)

    conn
    |> put_flash(:info, "Video deleted successfully.")
    |> redirect(to: video_path(conn, :index))
  end

Adding Categories

$ mix phoenix.gen.model Category categories name:string* creating web/models/category.ex
* creating test/models/category_test.exs
* creating priv/repo/migrations/20170905140814_create_category.exs

Remember to update your repository by running migrations:

    $ mix ecto.migrate

修改 migration,加上 null: false,以及 index

defmodule Rumbl.Repo.Migrations.CreateCategory do
  use Ecto.Migration

  def change do
    create table(:categories) do
      add :name, :string, null: false

      timestamps()
    end

    create unique_index(:categories, [:name])

  end
end

修改 /web/models/video.ex belongs_to :category, Rumbl.Category

  schema "videos" do
    field :url, :string
    field :title, :string
    field :description, :string

    # 定義 :user_id 欄位, 為 User.id 的 foreign key
    belongs_to :user, Rumbl.User
    belongs_to :category, Rumbl.Category

    timestamps()
  end

adds category_id to video

$ mix ecto.gen.migration add_category_id_to_video
Compiling 14 files (.ex)
warning: function authenticate/2 is unused
  web/controllers/user_controller.ex:40

Generated rumbl app
* creating priv/repo/migrations
* creating priv/repo/migrations/20170905141843_add_category_id_to_video.exs

修改 priv/repo/migrations/20170905141843addcategoryidto_video.exs

defmodule Rumbl.Repo.Migrations.AddCategoryIdToVideo do
  use Ecto.Migration

  def change do
     ## enforce a constraint between
videos and categories
    alter table(:videos) do
      add :category_id, references(:categories)
    end
  end
end

migrate database

$ mix ecto.migrate
[info] == Running Rumbl.Repo.Migrations.CreateCategory.change/0 forward
[info] create table categories
[info] create index categories_name_index
[info] == Migrated in 0.0s
[info] == Running Rumbl.Repo.Migrations.AddCategoryIdToVideo.change/0 forward
[info] alter table videos
[info] == Migrated in 0.0s

Setup Category Seed Data

如果要讓 categories 固定,但不想產生 contoller, view, templates,只需要在啟動時,在 database 塞入資料。

Phoenix 已經有個 seeding data 的功能,在 /priv/repo/seeds.exs,填寫以下 code

alias Rumbl.Repo
alias Rumbl.Category

for category <- ~w(Action Drama Romance Comedy Sci-fi) do
  Repo.insert!(%Category{name: category})
end
$ mix run priv/repo/seeds.exs
[debug] QUERY OK db=5.1ms
INSERT INTO `categories` (`name`,`inserted_at`,`updated_at`) VALUES (?,?,?) ["Action", {{2017, 9, 5}, {14, 24, 45, 713209}}, {{2017, 9, 5}, {14, 24, 45, 715805}}]
[debug] QUERY OK db=2.2ms queue=0.1ms
INSERT INTO `categories` (`name`,`inserted_at`,`updated_at`) VALUES (?,?,?) ["Drama", {{2017, 9, 5}, {14, 24, 45, 749923}}, {{2017, 9, 5}, {14, 24, 45, 749940}}]
[debug] QUERY OK db=1.8ms queue=0.1ms
INSERT INTO `categories` (`name`,`inserted_at`,`updated_at`) VALUES (?,?,?) ["Romance", {{2017, 9, 5}, {14, 24, 45, 752539}}, {{2017, 9, 5}, {14, 24, 45, 752546}}]
[debug] QUERY OK db=0.6ms queue=0.1ms
INSERT INTO `categories` (`name`,`inserted_at`,`updated_at`) VALUES (?,?,?) ["Comedy", {{2017, 9, 5}, {14, 24, 45, 754714}}, {{2017, 9, 5}, {14, 24, 45, 754719}}]
[debug] QUERY OK db=0.7ms
INSERT INTO `categories` (`name`,`inserted_at`,`updated_at`) VALUES (?,?,?) ["Sci-fi", {{2017, 9, 5}, {14, 24, 45, 755721}}, {{2017, 9, 5}, {14, 24, 45, 755730}}]

如果執行兩次會發生 error,因為 DB 有設定 unique index 的關係

$ mix run priv/repo/seeds.exs
[debug] QUERY ERROR db=6.1ms
INSERT INTO `categories` (`name`,`inserted_at`,`updated_at`) VALUES (?,?,?) ["Action", {{2017, 9, 5}, {14, 25, 5, 952360}}, {{2017, 9, 5}, {14, 25, 5, 954959}}]
** (Ecto.ConstraintError) constraint error when attempting to insert struct:

    * unique: categories_name_index

再修改 seeds.exs,這樣就不會出現 error

alias Rumbl.Repo
alias Rumbl.Category

for category <- ~w(Action Drama Romance Comedy Sci-fi) do
  Repo.get_by(Category, name: category) ||
    Repo.insert!(%Category{name: category})
end

Associating Videos and Categories

  1. 由 DB 取得所有 categories names and IDs
  2. 以 name 排序
  3. 傳入 view 並放在 select input 裡面

首先測試 Query 功能

$ iex -S mix
Erlang/OTP 20 [erts-9.0] [source] [64-bit] [smp:4:4] [ds:4:4:10] [async-threads:10] [hipe] [kernel-poll:false]

Interactive Elixir (1.5.1) - press Ctrl+C to exit (type h() ENTER for help)
iex(1)> import Ecto.Query
Ecto.Query
iex(2)> alias Rumbl.
Auth                 Category             Endpoint
ErrorHelpers         ErrorView            Gettext
LayoutView           Mixfile              PageController
PageView             Repo                 Router
SessionController    SessionView          User
UserController       UserSocket           UserView
Video                VideoController      VideoView
Web                  config_change/3      start/2

iex(2)> alias Rumbl.Repo
Rumbl.Repo
iex(3)> alias Rumbl.Category
Rumbl.Category

iex(4)> Repo.all from c in Category, select: c.name
[debug] QUERY OK source="categories" db=2.3ms
SELECT c0.`name` FROM `categories` AS c0 []
["Action", "Comedy", "Drama", "Romance", "Sci-fi"]
  • Repo.all 回傳所有 rows
  • from 是產生 query 的 macro
  • c in Category 表示取得 category 所有 rows
  • select: c.name 表示只需要 name 欄位
  • order_by 是排序
iex(6)> Repo.all from c in Category, order_by: c.name, select: {c.name, c.id}
[debug] QUERY OK source="categories" db=0.6ms
SELECT c0.`name`, c0.`id` FROM `categories` AS c0 ORDER BY c0.`name` []
[{"Action", 1}, {"Comedy", 4}, {"Drama", 2}, {"Romance", 3}, {"Sci-fi", 5}]

Ecto 的 query 是 composable

Ecto 定義了 Ecto.Queryable queryable protocol, from 會接受 queryable,可使用任何一個 queryable 當作新的 queryable 的基礎

iex(8)> query = Category
Rumbl.Category
iex(9)> query = from c in query, order_by: c.name
#Ecto.Query<from c in Rumbl.Category, order_by: [asc: c.name]>
iex(10)> query = from c in query, select: {c.name, c.id}
#Ecto.Query<from c in Rumbl.Category, order_by: [asc: c.name],
 select: {c.name, c.id}>
iex(11)> Repo.all query
[debug] QUERY OK source="categories" db=0.6ms
SELECT c0.`name`, c0.`id` FROM `categories` AS c0 ORDER BY c0.`name` []
[{"Action", 1}, {"Comedy", 4}, {"Drama", 2}, {"Romance", 3}, {"Sci-fi", 5}]

在 /web/models/category.ex 新增兩個 function

  # 以 queryable 為參數,且 return queryable
  def alphabetical(query) do
    from c in query, order_by: c.name
  end

  def names_and_ids(query) do
    from c in query, select: {c.name, c.id}
  end

/web/controllers/video_controller.ex 新增

  alias Rumbl.Category

  plug :load_categories when action in [:new, :create, :edit, :update]

  defp load_categories(conn, _) do
    query =
      Category
      |> Category.alphabetical
      |> Category.names_and_ids
    categories = Repo.all query
    assign(conn, :categories, categories)
  end

/web/templates/video/form.html.eex 新增
 <div class="form-group"> <%= label f, :category_id, "Category", class: "control-label" %> <%= select f, :category_id, @categories, class: "form-control", prompt: "Choose a category" %> </div>

修改 new.html.eex

<h2>New video</h2>

<%= render "form.html", changeset: @changeset, categories: @categories,
                        action: video_path(@conn, :create) %>

<%= link "Back", to: video_path(@conn, :index) %>

edit.html.eex

<h2>Edit video</h2>

<%= render "form.html", changeset: @changeset, categories: @categories,
                        action: video_path(@conn, :update, @video) %>

<%= link "Back", to: video_path(@conn, :index) %>

Deeper into Ecto Queries

$ iex -S mix
iex(1)> import Ecto.Query
Ecto.Query
iex(2)> alias Rumbl.Repo
Rumbl.Repo
iex(3)> alias Rumbl.User
Rumbl.User

iex(4)> username = "josie"
"josie"

iex(5)> Repo.one(from u in User, where: u.username == ^username)
[debug] QUERY OK source="users" db=2.2ms decode=2.7ms
SELECT u0.`id`, u0.`name`, u0.`username`, u0.`password_hash`, u0.`inserted_at`, u0.`updated_at` FROM `users` AS u0 WHERE (u0.`username` = ?) ["josie"]
%Rumbl.User{__meta__: #Ecto.Schema.Metadata<:loaded, "users">, id: 1,
 inserted_at: ~N[2017-09-03 13:09:55.000000], name: "Jose", password: nil,
 password_hash: "$2b$12$DtmZgYW2J.jxy3I0kNDAI.ooOsdcOXc6bezZuBzaTwaYSxbF1oBIS",
 updated_at: ~N[2017-09-05 01:22:52.000000], username: "josie",
 videos: #Ecto.Association.NotLoaded<association :videos is not loaded>}
  • Repo.one 只要回傳 one row,並不表示只有一個結果,而是只需要一個結果
  • from u in User 表示要讀取 User schema
  • where: u.username == ^username ,使用 ^ 可保證 username 永遠不會異動,^ 也可保證不會發生 sql injection
  • select 可省略,表示回傳整個 struct
  • 型別錯誤會發生 error
iex(7)> username = 123
123
iex(8)> Repo.one(from u in User, where: u.username == ^username)
** (Ecto.Query.CastError) iex:8: value `123` in `where` cannot be cast to type :string in query:

from u in Rumbl.User,
  where: u.username == ^123,
  select: u

Phoenix 的 MVC 如下

  1. controller 由 socket 讀取資料
  2. parse 資料 into data structures (ex: params map)
  3. 傳送到 Model,轉換參數為 changesets/queries
  4. Elixir structs, changesets, queries 都只是資料,可以在 function 中一個傳給一個,一步一步修改資料
  5. 透過 Repo 修改到 DB/發送 email 給系統負責人
  6. 最後呼叫 view,將 model 轉成 view data (JSON/HTML)

Query API

可在 query 中使用的

  1. Comparison operators: ==, !=, <=, >=, <, >
  2. Boolean operators: and, or, not
  3. Inclusion operator: in
  4. Search functions: like and ilike
  5. Null check functions: is_nil
  6. Aggregates: count, avg, sum, min, max
  7. Date/time intervals: datetimeadd, dateadd
  8. General: fragment, field, and type

可使用 keyword syntax 或是 pipe syntax


以 Keyword syntax 撰寫 Queries

Repo.one from u in User,
    select: count(u.id),
    where: like(u.username, ^"j%")
        or like(u.username, ^"c%")

iex(9)> Repo.one from u in User, select: count(u.id), where: like(u.username, ^"j%") or like(u.username, ^"c%")
[debug] QUERY OK source="users" db=6.2ms
SELECT count(u0.`id`) FROM `users` AS u0 WHERE ((u0.`username` LIKE ?) OR (u0.`username` LIKE ?)) ["j%", "c%"]
2
iex(12)> users_count = from u in User, select: count(u.id)
#Ecto.Query<from u in Rumbl.User, select: count(u.id)>

iex(13)> j_users = from u in users_count, where: like(u.username, ^"%j%")
#Ecto.Query<from u in Rumbl.User, where: like(u.username, ^"%j%"),
 select: count(u.id)>

iex(14)> j_users = from q in users_count, where: like(q.username, ^"%j%")
#Ecto.Query<from u in Rumbl.User, where: like(u.username, ^"%j%"),
 select: count(u.id)>

以 Pipe syntax 撰寫 Queries

User |>
select([u], count(u.id)) |>
where([u], like(u.username, ^"j%") or like(u.username, ^"c%")) |>
Repo.one()


iex(17)> User |>
...(17)> select([u], count(u.id)) |>
...(17)> where([u], like(u.username, ^"j%") or like(u.username, ^"c%")) |>
...(17)> Repo.one()
[debug] QUERY OK source="users" db=0.8ms
SELECT count(u0.`id`) FROM `users` AS u0 WHERE ((u0.`username` LIKE ?) OR (u0.`username` LIKE ?)) ["j%", "c%"]
2

Fragments

Ecto query fragment 會發送 DB 部分的 query,並能以更安全的方式產生 query string

from(u in User,
    where: fragment("lower(username) = ?",
    ^String.downcase(uname)))

Ecto 也可以直接執行 SQL

Ecto.Adapters.SQL.query(Rumbl.Repo, "SELECT power(?, ?)", [2, 10])

# note: PostgreSQL 要改為
Ecto.Adapters.SQL.query(Rumbl.Repo, "SELECT power($1, $2)", [2, 10])
iex(24)> Ecto.Adapters.SQL.query(Rumbl.Repo, "SELECT power(?, ?)", [2, 10])
[debug] QUERY OK db=6.4ms
SELECT power(?, ?) [2, 10]
{:ok,
 %Mariaex.Result{columns: ["power(?, ?)"], connection_id: nil,
  last_insert_id: nil, num_rows: 1, rows: [[1024.0]]}}

Querying Relationships

Ecto 支援 association relationship,可用 Repo.preload 取得 associated data

iex(1)> import Ecto.Query
Ecto.Query
iex(2)> alias Rumbl.Repo
Rumbl.Repo
iex(3)> alias Rumbl.User
Rumbl.User

# 取得一個 user
iex(4)> user = Repo.one from(u in User, limit: 1)
[debug] QUERY OK source="users" db=3.4ms decode=5.8ms
SELECT u0.`id`, u0.`name`, u0.`username`, u0.`password_hash`, u0.`inserted_at`, u0.`updated_at` FROM `users` AS u0 LIMIT 1 []
%Rumbl.User{__meta__: #Ecto.Schema.Metadata<:loaded, "users">, id: 1,
 inserted_at: ~N[2017-09-03 13:09:55.000000], name: "Jose", password: nil,
 password_hash: "$2b$12$DtmZgYW2J.jxy3I0kNDAI.ooOsdcOXc6bezZuBzaTwaYSxbF1oBIS",
 updated_at: ~N[2017-09-05 01:22:52.000000], username: "josie",
 videos: #Ecto.Association.NotLoaded<association :videos is not loaded>}
 
# user.videos 尚未 preload
iex(5)> user.videos
#Ecto.Association.NotLoaded<association :videos is not loaded>

# preload :videos
iex(6)> user = Repo.preload(user, :videos)
[debug] QUERY OK source="videos" db=0.8ms
SELECT v0.`id`, v0.`url`, v0.`title`, v0.`description`, v0.`user_id`, v0.`category_id`, v0.`inserted_at`, v0.`updated_at`, v0.`user_id` FROM `videos` AS v0 WHERE (v0.`user_id` = ?) ORDER BY v0.`user_id` [1]
%Rumbl.User{__meta__: #Ecto.Schema.Metadata<:loaded, "users">, id: 1,
 inserted_at: ~N[2017-09-03 13:09:55.000000], name: "Jose", password: nil,
 password_hash: "$2b$12$DtmZgYW2J.jxy3I0kNDAI.ooOsdcOXc6bezZuBzaTwaYSxbF1oBIS",
 updated_at: ~N[2017-09-05 01:22:52.000000], username: "josie",
 videos: [%Rumbl.Video{__meta__: #Ecto.Schema.Metadata<:loaded, "videos">,
   category: #Ecto.Association.NotLoaded<association :category is not loaded>,
   category_id: nil, description: "test", id: 2,
   inserted_at: ~N[2017-09-05 14:06:33.000000], title: "test",
   updated_at: ~N[2017-09-05 14:06:33.000000], url: "test",
   user: #Ecto.Association.NotLoaded<association :user is not loaded>,
   user_id: 1},
  %Rumbl.Video{__meta__: #Ecto.Schema.Metadata<:loaded, "videos">,
   category: #Ecto.Association.NotLoaded<association :category is not loaded>,
   category_id: nil, description: "t2", id: 3,
   inserted_at: ~N[2017-09-05 16:11:55.000000], title: "t2",
   updated_at: ~N[2017-09-05 16:11:55.000000], url: "t2",
   user: #Ecto.Association.NotLoaded<association :user is not loaded>,
   user_id: 1}]}

iex(7)> user.videos

# 也可以直接在 query 時 preload
iex(8)> user = Repo.one from(u in User, limit: 1, preload: [:videos])
[debug] QUERY OK source="users" db=0.9ms
SELECT u0.`id`, u0.`name`, u0.`username`, u0.`password_hash`, u0.`inserted_at`, u0.`updated_at` FROM `users` AS u0 LIMIT 1 []
[debug] QUERY OK source="videos" db=0.8ms
SELECT v0.`id`, v0.`url`, v0.`title`, v0.`description`, v0.`user_id`, v0.`category_id`, v0.`inserted_at`, v0.`updated_at`, v0.`user_id` FROM `videos` AS v0 WHERE (v0.`user_id` = ?) ORDER BY v0.`user_id` [1]
%Rumbl.User{__meta__: #Ecto.Schema.Metadata<:loaded, "users">, id: 1,
 inserted_at: ~N[2017-09-03 13:09:55.000000], name: "Jose", password: nil,
 password_hash: "$2b$12$DtmZgYW2J.jxy3I0kNDAI.ooOsdcOXc6bezZuBzaTwaYSxbF1oBIS",
 updated_at: ~N[2017-09-05 01:22:52.000000], username: "josie",
 videos: [%Rumbl.Video{__meta__: #Ecto.Schema.Metadata<:loaded, "videos">,
   category: #Ecto.Association.NotLoaded<association :category is not loaded>,
   category_id: nil, description: "test", id: 2,
   inserted_at: ~N[2017-09-05 14:06:33.000000], title: "test",
   updated_at: ~N[2017-09-05 14:06:33.000000], url: "test",
   user: #Ecto.Association.NotLoaded<association :user is not loaded>,
   user_id: 1},
  %Rumbl.Video{__meta__: #Ecto.Schema.Metadata<:loaded, "videos">,
   category: #Ecto.Association.NotLoaded<association :category is not loaded>,
   category_id: nil, description: "t2", id: 3,
   inserted_at: ~N[2017-09-05 16:11:55.000000], title: "t2",
   updated_at: ~N[2017-09-05 16:11:55.000000], url: "t2",
   user: #Ecto.Association.NotLoaded<association :user is not loaded>,
   user_id: 1}]}

加上過濾條件

Repo.all from u in User,
join: v in assoc(u, :videos),
join: c in assoc(v, :category),
where: c.name == "Comedy",
select: {u, v}

iex(9)> Repo.all from u in User,
...(9)> join: v in assoc(u, :videos),
...(9)> join: c in assoc(v, :category),
...(9)> where: c.name == "Comedy",
...(9)> select: {u, v}
[debug] QUERY OK source="users" db=5.4ms
SELECT u0.`id`, u0.`name`, u0.`username`, u0.`password_hash`, u0.`inserted_at`, u0.`updated_at`, v1.`id`, v1.`url`, v1.`title`, v1.`description`, v1.`user_id`, v1.`category_id`, v1.`inserted_at`, v1.`updated_at` FROM `users` AS u0 INNER JOIN `videos` AS v1 ON v1.`user_id` = u0.`id` INNER JOIN `categories` AS c2 ON c2.`id` = v1.`category_id` WHERE (c2.`name` = 'Comedy') []
[]

Constraints

  • constraint

    explicit database constraint 可能是 unique constraint on an index,或是 integrity constant between primary and foreign keys

  • constraint error

    Ecto.ConstraintError (如果 add 一樣的 category 兩次)

  • changeset constraint

    附加到 changeset 的 a constraint annotation,可讓 Ecto 轉換 constrant error 為 changeset error messages

  • changeset error message


Validating Unique Data (Table Primary Key)

在 DB migration 中加上 unique index

create unique_index(:users, [:username])

/web/models/user.ex,changeset 加上 unique_constraint

  def changeset(model, params \\ %{}) do
    model
    |> cast(params, ~w(name username), [])
    |> validate_length(:username, min: 1, max: 20)
    |> unique_constraint(:username)
  end

Validating Foreign Keys

在 /web/models/video.ex 加上 category constraint

notes: 書本是寫成 @optionalfields ~w(categoryid),但是把 categoryid 放在 requiredfields,changeset 資料才會處理 category_id 欄位,這裡是比較奇怪的地方...

  @required_fields ~w(url title description category_id)
  @optional_fields ~w()

  def changeset(model, params \\ %{}) do
    Logger.debug "params: #{inspect(params)}"
    model
    |> cast(params, @required_fields, @optional_fields)
    |> assoc_constraint(:category)
  end
$ iex -S mix

iex(1)> alias Rumbl.Category
Rumbl.Category
iex(2)> alias Rumbl.Video
Rumbl.Video
iex(3)> alias Rumbl.Repo
Rumbl.Repo
iex(4)> import Ecto.Query
Ecto.Query

# 取得 Drama category
iex(5)> category = Repo.get_by Category, name: "Drama"
[debug] QUERY OK source="categories" db=2.9ms
SELECT c0.`id`, c0.`name`, c0.`inserted_at`, c0.`updated_at` FROM `categories` AS c0 WHERE (c0.`name` = ?) ["Drama"]
%Rumbl.Category{__meta__: #Ecto.Schema.Metadata<:loaded, "categories">, id: 2,
 inserted_at: ~N[2017-09-05 14:24:45.000000], name: "Drama",
 updated_at: ~N[2017-09-05 14:24:45.000000]}

# 取得一個video
iex(8)> video = Repo.one(from v in Video, limit: 1)
[debug] QUERY OK source="videos" db=0.5ms
SELECT v0.`id`, v0.`url`, v0.`title`, v0.`description`, v0.`user_id`, v0.`category_id`, v0.`inserted_at`, v0.`updated_at` FROM `videos` AS v0 LIMIT 1 []
%Rumbl.Video{__meta__: #Ecto.Schema.Metadata<:loaded, "videos">,
 category: #Ecto.Association.NotLoaded<association :category is not loaded>,
 category_id: 2, description: "test", id: 2,
 inserted_at: ~N[2017-09-05 14:06:33.000000], title: "test",
 updated_at: ~N[2017-09-06 02:09:40.000000], url: "test",
 user: #Ecto.Association.NotLoaded<association :user is not loaded>, user_id: 1}


# 修改 category_id
iex(10)> changeset = Video.changeset(video, %{category_id: category.id})
[debug] params: %{category_id: 2}
#Ecto.Changeset<action: nil, changes: %{}, errors: [], data: #Rumbl.Video<>,
 valid?: true>

# 寫入 DB
iex(12)> Repo.update(changeset)
{:ok,
 %Rumbl.Video{__meta__: #Ecto.Schema.Metadata<:loaded, "videos">,
  category: #Ecto.Association.NotLoaded<association :category is not loaded>,
  category_id: 2, description: "test", id: 2,
  inserted_at: ~N[2017-09-05 14:06:33.000000], title: "test",
  updated_at: ~N[2017-09-06 02:09:40.000000], url: "test",
  user: #Ecto.Association.NotLoaded<association :user is not loaded>,
  user_id: 1}}
  
# 將 category_id 設定為 12345
iex(13)> changeset = Video.changeset(video, %{category_id: 12345})
[debug] params: %{category_id: 12345}
#Ecto.Changeset<action: nil, changes: %{category_id: 12345}, errors: [],
 data: #Rumbl.Video<>, valid?: true>
 
# update 時發生 error
iex(14)> Repo.update(changeset)
[debug] QUERY OK db=0.1ms
begin []
[debug] QUERY ERROR db=201.6ms
UPDATE `videos` SET `category_id` = ?, `updated_at` = ? WHERE `id` = ? [12345, {{2017, 9, 6}, {2, 12, 11, 984209}}, 2]
[debug] QUERY OK db=0.4ms
rollback []
{:error,
 #Ecto.Changeset<action: :update, changes: %{category_id: 12345},
  errors: [category: {"does not exist", []}], data: #Rumbl.Video<>,
  valid?: false>}

Delete

$ iex -S mix
iex(1)> alias Rumbl.Repo
Rumbl.Repo
iex(2)> category = Repo.get_by Rumbl.Category, name: "Drama"
[debug] QUERY OK source="categories" db=2.5ms decode=3.3ms
SELECT c0.`id`, c0.`name`, c0.`inserted_at`, c0.`updated_at` FROM `categories` AS c0 WHERE (c0.`name` = ?) ["Drama"]
%Rumbl.Category{__meta__: #Ecto.Schema.Metadata<:loaded, "categories">, id: 2,
 inserted_at: ~N[2017-09-05 14:24:45.000000], name: "Drama",
 updated_at: ~N[2017-09-05 14:24:45.000000]}
iex(3)> Repo.delete category
[debug] QUERY ERROR db=1.9ms
DELETE FROM `categories` WHERE `id` = ? [2]
** (Ecto.ConstraintError) constraint error when attempting to delete struct:

    * foreign_key: videos_category_id_fkey

If you would like to convert this constraint into an error, please
call foreign_key_constraint/3 in your changeset and define the proper
constraint name. The changeset has not defined any constraint.

    (ecto) lib/ecto/repo/schema.ex:570: anonymous fn/4 in Ecto.Repo.Schema.constraints_to_errors/3
    (elixir) lib/enum.ex:1255: Enum."-map/2-lists^map/1-0-"/2
    (ecto) lib/ecto/repo/schema.ex:555: Ecto.Repo.Schema.constraints_to_errors/3
    (ecto) lib/ecto/repo/schema.ex:382: anonymous fn/9 in Ecto.Repo.Schema.do_delete/4

delete 也可以用 changeset 當參數

iex(3)> import Ecto.Changeset
Ecto.Changeset
iex(4)> changeset = Ecto.Changeset.change(category)
#Ecto.Changeset<action: nil, changes: %{}, errors: [], data: #Rumbl.Category<>,
 valid?: true>
iex(5)> changeset = foreign_key_constraint(changeset, :videos, name: :videos_category_id_fkey, message: "still exist")
#Ecto.Changeset<action: nil, changes: %{}, errors: [], data: #Rumbl.Category<>,
 valid?: true>

iex(6)> Repo.delete changeset
[debug] QUERY ERROR db=5.1ms
DELETE FROM `categories` WHERE `id` = ? [2]
{:error,
 #Ecto.Changeset<action: :delete, changes: %{},
  errors: [videos: {"still exist", []}], data: #Rumbl.Category<>,
  valid?: false>}

在 DB migration 裡,references 裡面可加上 :on_delete option

  • :nothing

    預設值

  • :delete_all

    刪除某個 category 時,所有這個 category 的 videos 都會被刪除

  • :nilify_all

    刪除某個 cateogry 時,所有這個 category 的 videos 的 category_id 會被設定為 null

ex:

add :category_id, references(:categories, on_delete: :nothing)

References

Programming Phoenix