Ecto 总括

ecto 简介

ecto 非凡给 elixir 的 ORM,不过得益于 elixir 语言,和传统的 ORM
相比较,更加从简与强硬。
ecto 重要分为 4 部分:

  1. Repo: 这是暨确实数据库交互的有
  2. Schema: 相当于是数据库中表的概念,但不光是概念
  3. Changeset:Schema 到实在数据库中的转换层
  4. Query:elixir 风格的数据查询情势

ecto 连接数据库(以 sqlite 为例)

连天至实在的数据库,需要相应的驱动,下面为 sqlite 为条例,配置数据库的连日

创工程

否即使是一般的 elixir 工程

mix new ecto_sample

sqlite,引入 ecto 和数据库让

引入 ecto 和 sqlite 相关的 packages (mix.exs)

defp deps do
  [
    {:sqlite_ecto2, "~> 2.2"},
    {:ecto, "~> 2.1"}
  ]
end

布好后透过命令执行安装:

mix deps.get

创制数据库

在 lib/ecto_sample 下成立文件 repo.ex,文件内容如下:

defmodule EctoSample.Repo do
  use Ecto.Repo,
    otp_app: :ecto_sample,
    adapter: Sqlite.Ecto2
end

配置数据库连接,config/config.esx

config :ecto_sample, ecto_repos: [EctoSample.Repo]  # 配置 repo

config :ecto_sample, EctoSample.Repo,   # 配置驱动和数据库位置,这里用的 sqlite 数据,比较简单
  adapter: Sqlite.Ecto2,
  database: "ecto_sample.db"

sqlite 数据库只要配置数据库文件的岗位即可,倘诺是 postgres
之类的关周到据库,需要安排主机,用户名/密码 等

config :ecto_sample, EctoSample.Repo,
  adapter: Ecto.Adapters.Postgres,
  database: "ecto_sample_repo",
  username: "user",
  password: "pass",
  hostname: "localhost"

布置好后,创立数据库

mix ecto.create

事业有成实践的话,能看在工程根目录下大半矣只 ecto_sample.db 文件

创建表

创建 schema users, lib/repo.ex 文件被扩展
中 changeset
是在改进数据时用来证实数据中或者更换数据用的,不是须的

defmodule EctoSample.User do
  use Ecto.Schema

  schema "users" do
    field :username, :string
    field :password, :string
    field :email,    :string
    field :age,      :integer
  end

  def changeset(user, params \\ %{}) do
    user
    |> cast(params, [:username, :password, :email, :age])
    |> validate_required([:username, :password])
  end
end

创造建表的 migration

$ mix ecto.gen.migration create_user
Compiling 2 files (.ex)
Generated ecto_sample app
* creating priv/repo/migrations
* creating priv/repo/migrations/20171123012930_create_user.exs

参照 users 的 schema 编辑
priv/repo/migrations/20171123012930_create_user.exs

defmodule EctoSample.Repo.Migrations.CreateUser do
  use Ecto.Migration

  def change do
    create table(:users) do
      add :username, :string
      add :password, :string
      add :email,    :string
      add :age,      :integer
    end
  end
end

创建表

$ mix ecto.migrate

09:33:40.257 [info]  == Running EctoSample.Repo.Migrations.CreateUser.change/0 forward

09:33:40.257 [info]  create table users

09:33:40.259 [info]  == Migrated in 0.0s

登入数据库验证

从而 sqlite3 的客户端登入数据库查看状况,上边拔取的凡命令执行措施

$ sqlite3 ecto_sample.db 
SQLite version 3.16.2 2017-01-06 16:32:41
Enter ".help" for usage hints.
sqlite> .fullschema
CREATE TABLE IF NOT EXISTS "schema_migrations" ("version" BIGINT PRIMARY KEY, "inserted_at" NAIVE_DATETIME);
CREATE TABLE IF NOT EXISTS "users" ("id" INTEGER PRIMARY KEY, "username" TEXT, "password" TEXT, "email" TEXT, "age" INTEGER);
/* No STAT tables available */
sqlite> .exit

可看看:

  1. 除却创造了 users 表,ecto 还创办了 schema_migrations 用来保管每一回的
    migration
  2. 默认创建了 id 主键,类型是 INTEGER,即便一旦反成为 uuid 制造时只要强烈指定

ecto 单表操作

示范示例

通过一个例子来演示对 users 表的 CURD
演示流程: 增添一个记录 -> 查询这长长的记下 -> 修改就漫长记下 ->
查询新的记录 -> 删除这条记下 -> 再一次询问也空

  1. 增产记录

    def add_user(username, password, email \\ "", age \\ 0) do
      user = EctoSample.User.changeset(%EctoSample.User{}, %{:username => username, :password => password,
                                                             :email => email, :age => age})
    
      case EctoSample.Repo.insert(user) do
        {:ok, _} -> Logger.info "insert successfully"
        {:error, _} -> Logger.error "insert failed"
      end
    end
    
  2. 查询记录

    import Ecto.Query, only: [from: 2]
    
    q = from u in EctoSample.User,
      where: u.username == ^username
    
    EctoSample.Repo.all(q) |> Enum.map(fn (u) ->
      Logger.info "==============================="
      Logger.info "username: " <> u.username
      Logger.info "password: " <> u.password
      if u.email do
        Logger.info "email:    " <> u.email
      end
      Logger.info "age:      " <> Integer.to_string u.age
      Logger.info "==============================="
    end)
    
  3. 改记录

    def change_user(id, params \\ %{}) do
      u = EctoSample.Repo.get!(EctoSample.User, id)
      changeset = EctoSample.User.changeset(u, params)
    
      EctoSample.Repo.update(changeset)
    end
    
  4. 剔除记录

    def delete_user(id) do
      u = EctoSample.Repo.get!(EctoSample.User, id)
      EctoSample.Repo.delete(u)
      |> case  do
           {:ok, _} -> Logger.info "delete successfully"
           {:error, _} -> Logger.error "delete failed"
         end
    end
    

测试步骤

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

  Interactive Elixir (1.5.2) - press Ctrl+C to exit (type h() ENTER for help)
iex(1)> EctoSample.start
  {:ok, #PID<0.182.0>}
iex(2)> EctoSample.add_user("a", "b", "c", 10)

  22:45:22.570 [info]  insert successfully
iex(3)> EctoSample.query_user("a")

  22:45:29.370 [info]  ===============================
  [:ok]

  22:45:29.370 [info]  username: a

  22:45:29.370 [info]  password: b

  22:45:29.370 [info]  email:    c

  22:45:29.370 [info]  age:      10

  22:45:29.370 [info]  ===============================

iex(4)> EctoSample.change_user(1, %{:username => "change", :age => 20})
  {:ok,
   %EctoSample.User{__meta__: #Ecto.Schema.Metadata<:loaded, "users">, age: 20,
    email: "c", id: 1, password: "b", username: "change"}}
iex(5)> EctoSample.query_user("change")

  22:48:47.644 [info]  ===============================

  22:48:47.644 [info]  username: change

  22:48:47.644 [info]  password: b

  22:48:47.644 [info]  email:    c

  22:48:47.644 [info]  age:      20

  22:48:47.644 [info]  ===============================
iex(6)> EctoSample.delete_user(1)

  22:50:17.848 [info]  delete successfully
iex(7)> EctoSample.query_user("change")

  []

ecto 表关系操作

发明关系才生 3 种植,1:1,1:N,M:N。 先在现有的表基础上加 3 张表

充实及 User 关联的发明

  1. table 定义

    defmodule EctoSample.Schema.User do
      use Ecto.Schema
      import Ecto.Changeset
    
      schema "users" do
        field :username, :string
        field :password, :string
        field :email,    :string
        field :age,      :integer
    
        has_one :computer, EctoSample.Schema.Computer
        belongs_to :company, EctoSample.Schema.Company
        many_to_many :friends, EctoSample.Schema.Friend, join_through: "users_friends"
      end
    
      def changeset(user, params \\ %{}) do
        user
        |> cast(params, [:username, :password, :email, :age])
        |> validate_required([:username, :password])
      end
    end
    
    # 1 : 1  computer - user
    defmodule EctoSample.Schema.Computer do
      use Ecto.Schema
      import Ecto.Changeset
    
      schema "computers" do
        field :hostname, :string
        field :ip,       :string
    
        belongs_to :user, EctoSample.Schema.User
      end
    
      def changeset(computer, params \\ %{}) do
        computer
        |> cast(params, [:hostname, :ip])
        |> validate_required([:hostname, :ip])
      end
    end
    
    # 1 : N  company - user
    defmodule EctoSample.Schema.Company do
      use Ecto.Schema
      import Ecto.Changeset
    
      schema "companys" do
        field :comp_name, :string
        field :addr,      :string
    
        has_many :users, EctoSample.Schema.User
      end
    
      def changeset(company, params \\ %{}) do
        company
        |> cast(params, [:comp_name, :addr])
        |> validate_required([:comp_name, :addr])
      end
    end
    
    # M : N  friend - user
    defmodule EctoSample.Schema.Friend do
      use Ecto.Schema
      import Ecto.Changeset
    
      schema "friends" do
        field :frient_name, :string
        field :phone,       :string
    
        many_to_many :users, EctoSample.Schema.User, join_through: "users_friends"
      end
    
      def changeset(friend, params \\ %{}) do
        friend
        |> cast(params, [:friend_name, :phone])
        |> validate_required([:friend_name, :phone])
      end
    end
    

    本来的 user 表做了一部分改动,扩大了一些干属性,另外扩张了 3
    张表,和 user 表的涉分别是:

    • 1:1 user : computer
    • 1:N company : user
    • M:N friend : user
  2. table migration
    成立各种表底 migration

    mix ecto.gen.migration create_company
    mix ecto.gen.migration create_computer
    mix ecto.gen.migration create_friend
    mix ecto.gen.migration create_users_friends
    

    migration 的代码参见:https://gitee.com/wangyubin/ecto_sample.git
    整个示例工程的代码都于里头

1:1 示例

def one_to_one() do
  import Ecto.Changeset
  alias EctoSample.Schema.User
  alias EctoSample.Schema.Computer

  # insert
  computer = %Computer{}
  |> Computer.changeset(%{:hostname => "debian", :ip => "192.168.0.100"})
  |> EctoSample.Repo.insert!

  user = %User{}
  |> User.changeset(%{:username => "wyb", :password => "123"})
  |> put_assoc(:computer, computer)
  |> EctoSample.Repo.insert!

  # query
  u = EctoSample.Repo.get!(User, user.id) |> EctoSample.Repo.preload(:computer)
  Logger.info "==============================="
  Logger.info "id:       " <> Integer.to_string(u.id)
  Logger.info "username: " <> u.username
  Logger.info "password: " <> u.password
  Logger.info "computer: *********"
  Logger.info "hostname: " <> u.computer.hostname
  Logger.info "ip:       " <> u.computer.ip
  Logger.info "==============================="
end

1:N 示例

def one_to_many() do
  alias EctoSample.Schema.User
  alias EctoSample.Schema.Company

  # insert
  user1 = EctoSample.Repo.insert!(%User{:username => "wyb001", :password => "123"})
  user2 = EctoSample.Repo.insert!(%User{:username => "wyb002", :password => "321"})
  company = EctoSample.Repo.insert!(%Company{:comp_name => "yunbim", :addr => "D216", :users => [user1, user2]})

  # TODO 这里是根据 user 来新建 company,也可以 根据已有的 company 来创建 user

  # query
  c = EctoSample.Repo.get!(Company, company.id) |> EctoSample.Repo.preload(:users)
  Logger.info "==============================="
  Logger.info "id:       " <> Integer.to_string(c.id)
  Logger.info "comp_name:" <> c.comp_name
  Logger.info "addr    : " <> c.addr
  Logger.info "users: *********"
  c.users |> Enum.map(fn (u) ->
    Logger.info "id:       " <> Integer.to_string(u.id)
    Logger.info "username: " <> u.username
    Logger.info "password: " <> u.password
  end)
  Logger.info "==============================="
end

M:N 示例

def many_to_many() do
  alias EctoSample.Schema.User
  alias EctoSample.Schema.Friend
  import Ecto.Changeset

  # insert
  user1 = EctoSample.Repo.insert!(%User{:username => "wyb001", :password => "123"})
  user2 = EctoSample.Repo.insert!(%User{:username => "wyb002", :password => "321"})
  friend1 = EctoSample.Repo.insert!(%Friend{:friend_name => "f001", :phone => "123456789"})
  friend2 = EctoSample.Repo.insert!(%Friend{:friend_name => "f002", :phone => "987654321"})

  EctoSample.Repo.get!(User, user1.id)
  |> EctoSample.Repo.preload(:friends)
  |> change
  |> put_assoc(:friends, [friend1, friend2])
  |> EctoSample.Repo.update!()

  EctoSample.Repo.get!(User, user2.id)
  |> EctoSample.Repo.preload(:friends)
  |> change
  |> put_assoc(:friends, [friend1])
  |> EctoSample.Repo.update!()

  # query
  f1 = EctoSample.Repo.get!(Friend, friend1.id) |> EctoSample.Repo.preload(:users)
  Logger.info "==============================="
  Logger.info "id:         " <> Integer.to_string(f1.id)
  Logger.info "friend_name:" <> f1.friend_name
  Logger.info "phone:      " <> f1.phone
  Logger.info "users: *********"
  f1.users |> Enum.map(fn (u) ->
    Logger.info "id:       " <> Integer.to_string(u.id)
    Logger.info "username: " <> u.username
    Logger.info "password: " <> u.password
  end)
  Logger.info "==============================="

  f2 = EctoSample.Repo.get!(Friend, friend2.id) |> EctoSample.Repo.preload(:users)
  Logger.info "==============================="
  Logger.info "id:         " <> Integer.to_string(f2.id)
  Logger.info "friend_name:" <> f2.friend_name
  Logger.info "phone:      " <> f2.phone
  Logger.info "users: *********"
  f2.users |> Enum.map(fn (u) ->
    Logger.info "id:       " <> Integer.to_string(u.id)
    Logger.info "username: " <> u.username
    Logger.info "password: " <> u.password
  end)
  Logger.info "==============================="
end

运行测试

$ mix ecto.drop
$ mix ecto.create
$ mix ecto.migrate

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

Interactive Elixir (1.5.2) - press Ctrl+C to exit (type h() ENTER for help)
iex(1)> EctoSample.start
{:ok, #PID<0.182.0>}

iex(2)> EctoSample.one_to_one
11:00:27.800 [info]  ===============================

11:00:27.800 [info]  id:       1

11:00:27.800 [info]  username: wyb

11:00:27.800 [info]  password: 123

11:00:27.800 [info]  computer: *********

11:00:27.800 [info]  hostname: debian

11:00:27.800 [info]  ip:       192.168.0.100

11:00:27.800 [info]  ===============================

iex(3)> EctoSample.one_to_many
11:01:32.327 [info]  ===============================

11:01:32.327 [info]  id:       1

11:01:32.327 [info]  comp_name:yunbim

11:01:32.327 [info]  addr    : D216

11:01:32.327 [info]  users: *********

11:01:32.327 [info]  id:       2

11:01:32.327 [info]  username: wyb001

11:01:32.327 [info]  password: 123

11:01:32.327 [info]  id:       3

11:01:32.327 [info]  username: wyb002

11:01:32.327 [info]  password: 321

11:01:32.327 [info]  ===============================

iex(4)> EctoSample.many_to_many
11:02:22.086 [info]  ===============================

11:02:22.086 [info]  id:         1   

11:02:22.086 [info]  friend_name:f001     

11:02:22.086 [info]  phone:      123456789

11:02:22.086 [info]  users: *********                                                     

11:02:22.086 [info]  id:       4                                 

11:02:22.086 [info]  username: wyb001                                                            

11:02:22.086 [info]  password: 123                  

11:02:22.086 [info]  id:       5

11:02:22.086 [info]  username: wyb002

11:02:22.086 [info]  password: 321

11:02:22.086 [info]  ===============================

11:02:22.087 [info]  ===============================

11:02:22.087 [info]  id:         2

11:02:22.087 [info]  friend_name:f002

11:02:22.087 [info]  phone:      987654321

11:02:22.087 [info]  users: *********

11:02:22.087 [info]  id:       4

11:02:22.087 [info]  username: wyb001

11:02:22.087 [info]  password: 123

11:02:22.087 [info]  ===============================

ecto 中之作业

ecto 中之事务,首先通过 Multi 来组装需要展开的数据库操作,然后经
Repo.transaction 来执行

def trans() do
  alias EctoSample.Schema.User
  alias EctoSample.Schema.Computer
  import Ecto.Query, only: [from: 2]
  alias Ecto.Multi

  # insert user and computer in one transaction, insert all success
  Logger.info "=========== before transaction==============="
  EctoSample.Repo.one(from u in User, select: count(u.id)) |> Logger.info
  EctoSample.Repo.one(from c in Computer, select: count(c.id)) |> Logger.info

  Multi.new()
  |> Multi.insert(:user, %User{username: "m-user", password: "m-password"})
  |> Multi.insert(:computer, %Computer{hostname: "host-name", ip: "0.0.0.0"})
  |> EctoSample.Repo.transaction
  |> case do
       {:ok, _} -> Logger.info "multi success"
       {:error, _} -> Logger.error "multi error"
     end


  Logger.info "=========== after  transaction==============="
  EctoSample.Repo.one(from u in User, select: count(u.id)) |> Logger.info
  EctoSample.Repo.one(from c in Computer, select: count(c.id)) |> Logger.info

end

插成功后,User 和 Computer 表的数还汇合增多

其他

除外上述内容外,Ecto 还生外的 API 帮忙查询与各样数码操作,具体参见
Ecto 文档

网站地图xml地图