ecto_sqlite3/test/ecto/adapters/sqlite3/connection/insert_test.exs at d033bda04d2edbece0d0628ee392190a2765f176 · elixir-sqlite/ecto_sqlite3 · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
defmodule Ecto.Adapters.SQLite3.Connection.InsertTest do
  use ExUnit.Case, async: true

  import Ecto.Query
  import Ecto.Adapters.SQLite3.TestHelpers

  test "insert" do
    query = insert(nil, "schema", [:x, :y], [[:x, :y]], {:raise, [], []}, [:id])
    assert query == ~s{INSERT INTO "schema" ("x","y") VALUES (?1,?2) RETURNING "id"}

    assert_raise ArgumentError, fn ->
      insert(nil, "schema", [:x, :y], [[:x, :y], [nil, :z]], {:raise, [], []}, [:id])
    end

    assert_raise ArgumentError, fn ->
      insert(nil, "schema", [:x, :y], [[:x, :y], [nil, :z]], {:raise, [], []}, [:id], [
        1,
        2
      ])
    end

    query = insert(nil, "schema", [], [[]], {:raise, [], []}, [:id])
    assert query == ~s{INSERT INTO "schema" DEFAULT VALUES RETURNING "id"}

    query = insert(nil, "schema", [], [[]], {:raise, [], []}, [])
    assert query == ~s{INSERT INTO "schema" DEFAULT VALUES}

    query = insert("prefix", "schema", [], [[]], {:raise, [], []}, [])
    assert query == ~s{INSERT INTO prefix.schema DEFAULT VALUES}

    query = insert(nil, "schema", [:x, :y], [[:x, :y]], {:raise, [], []}, [:id])
    assert query == ~s{INSERT INTO "schema" ("x","y") VALUES (?1,?2) RETURNING "id"}

    assert_raise(
      ArgumentError,
      "Cell-wise default values are not supported on INSERT statements by SQLite3",
      fn ->
        insert(nil, "schema", [:x, :y], [[:x, :y], [nil, :z]], {:raise, [], []}, [:id])
      end
    )
  end

  test "insert with on conflict" do
    # For :nothing
    query = insert(nil, "schema", [:x, :y], [[:x, :y]], {:nothing, [], []}, [])

    assert query ==
             ~s{INSERT INTO "schema" ("x","y") VALUES (?1,?2) ON CONFLICT DO NOTHING}

    query = insert(nil, "schema", [:x, :y], [[:x, :y]], {:nothing, [], [:x, :y]}, [])

    assert query ==
             ~s{INSERT INTO "schema" ("x","y") VALUES (?1,?2) ON CONFLICT ("x","y") DO NOTHING}

    # For :update
    update = from("schema", update: [set: [z: "foo"]]) |> plan(:update_all)
    query = insert(nil, "schema", [:x, :y], [[:x, :y]], {update, [], [:x, :y]}, [:z])

    assert query ==
             ~s{INSERT INTO "schema" AS s0 ("x","y") VALUES (?1,?2) ON CONFLICT ("x","y") DO UPDATE SET "z" = 'foo' RETURNING "z"}

    # For :unsafe_fragment
    update = from("schema", update: [set: [z: "foo"]]) |> plan(:update_all)

    query =
      insert(
        nil,
        "schema",
        [:x, :y],
        [[:x, :y]],
        {update, [], {:unsafe_fragment, "foobar"}},
        [:z]
      )

    assert query ==
             ~s{INSERT INTO "schema" AS s0 ("x","y") VALUES (?1,?2) ON CONFLICT foobar DO UPDATE SET "z" = 'foo' RETURNING "z"}

    assert_raise ArgumentError, "Upsert in SQLite3 requires :conflict_target", fn ->
      conflict_target = []

      insert(
        nil,
        "schema",
        [:x, :y],
        [[:x, :y]],
        {:replace_all, [], conflict_target},
        []
      )
    end

    assert_raise ArgumentError,
                 "Upsert in SQLite3 does not support ON CONSTRAINT",
                 fn ->
                   insert(
                     nil,
                     "schema",
                     [:x, :y],
                     [[:x, :y]],
                     {:replace_all, [], {:constraint, :foo}},
                     []
                   )
                 end

    query = insert(nil, "schema", [:x, :y], [[:x, :y]], {:replace_all, [], [:id]}, [])

    assert query ==
             """
             INSERT INTO "schema" ("x","y") \
             VALUES (?1,?2) \
             ON CONFLICT ("id") \
             DO UPDATE SET "x" = EXCLUDED."x","y" = EXCLUDED."y"\
             """
  end

  test "insert with query" do
    query = from("schema", select: [:id]) |> plan(:all)

    assert_raise ArgumentError, fn ->
      insert(
        nil,
        "schema",
        [:x, :y, :z],
        [[:x, {query, 3}, :z], [nil, {query, 2}, :z]],
        {:raise, [], []},
        [:id]
      )
    end
  end

  test "insert with query as rows" do
    query = from(s in "schema", select: %{foo: fragment("3"), bar: s.bar}) |> plan(:all)

    query = insert(nil, "schema", [:foo, :bar], query, {:raise, [], []}, [:foo])

    assert query ==
             ~s{INSERT INTO "schema" ("foo","bar") SELECT 3, s0."bar" FROM "schema" AS s0 RETURNING "foo"}

    query =
      from(s in "schema", select: %{foo: fragment("3"), bar: s.bar}, where: true)
      |> plan(:all)

    query = insert(nil, "schema", [:foo, :bar], query, {:raise, [], []}, [:foo])

    assert query ==
             ~s{INSERT INTO "schema" ("foo","bar") SELECT 3, s0."bar" FROM "schema" AS s0 WHERE (1) RETURNING "foo"}
  end
end