Debian Codegraph @

mysql-test/suite/x/t/crud_insert_upsert.test

raw
1 ## Inserting with upsert
2
3 ## Preamble
4 --source include/xplugin_preamble.inc
5 --source include/xplugin_create_user.inc
6 ## Test starts here
7
8 --write_file $MYSQL_TMP_DIR/crud_insert_upsert.xpl
9 ## Test data
10 -->quiet
11 -->import crud_insert.macro
12 -->import crud_find.macro
13
14 -->varlet %DOC1_FOOBARCOM_FOO% type: LITERAL literal{type: V_OCTETS v_octets{value:'{"_id": "1", "email": "[email protected]", "name":"foo"}' content_type:2}}
15 -->varlet %DOC2_BLABARCOM_BLA% type: LITERAL literal{type: V_OCTETS v_octets{value:'{"_id": "2", "email": "[email protected]", "name":"bla"}' content_type:2}}
16 -->varlet %DOC2_FOO% type: LITERAL literal{type: V_OCTETS v_octets{value:'{"_id": "2", "name":"foo"}' content_type:2}}
17 -->varlet %DOC3_NULL_BLA% type: LITERAL literal{type: V_OCTETS v_octets{value:'{"_id": "3", "email": null, "name":"bla"}' content_type:2}}
18 -->varlet %DOC3_BLA% type: LITERAL literal{type: V_OCTETS v_octets{value:'{"_id": "3", "name":"bla"}' content_type:2}}
19 -->varlet %DOC3_FOOORACOM_FOO% type: LITERAL literal{type: V_OCTETS v_octets{value:'{"_id": "3", "email": "[email protected]", "name":"foo"}' content_type:2}}
20 -->varlet %DOC1_FOOORACOM_FOO% type: LITERAL literal{type: V_OCTETS v_octets{value:'{"_id": "1", "email": "[email protected]", "name":"foo"}' content_type:2}}
21 -->varlet %DOC1_FOOBARCOM_BLA% type: LITERAL literal{type: V_OCTETS v_octets{value:'{"_id": "1", "email": "[email protected]", "name":"bla"}' content_type:2}}
22 -->varlet %DOC3_FOOBARCOM_BLA% type: LITERAL literal{type: V_OCTETS v_octets{value:'{"_id": "3", "email": "[email protected]", "name":"bla"}' content_type:2}}
23 -->varlet %DOC1_BLA% type: LITERAL literal{type: V_OCTETS v_octets{value:'{"_id": "1", "name":"bla"}' content_type:2}}
24 -->varlet %DOC1_NULL_BLA% type: LITERAL literal{type: V_OCTETS v_octets{value:'{"_id": "1", "name":"bla", "email": null}' content_type:2}}
25 -->varlet %DOC1_BLABARCOM_FOO% type: LITERAL literal{type: V_OCTETS v_octets{value:'{"_id": "1", "email": "[email protected]", "name":"foo"}' content_type:2}}
26 -->varlet %DOC4_BLABLACOM_BLA% type: LITERAL literal{type: V_OCTETS v_octets{value:'{"_id": "4", "email": "[email protected]", "name":"bla"}' content_type:2}}
27 -->varlet %DOC4_FOO% type: LITERAL literal{type: V_OCTETS v_octets{value:'{"_id": "4", "name":"foo"}' content_type:2}}
28
29
30 -->title =Data model: TABLE
31
32 -->sql
33 CREATE TABLE xtest.xtable (doc JSON);
34 -->endsql
35 -->title -Insert two docs
36 -->callmacro Insert_two TABLE xtable %DOC1_FOOBARCOM_FOO% %DOC2_BLABARCOM_BLA% ER_SUCCESS
37 -->callmacro Show xtable
38 -->title -Upsert one doc
39 -->callmacro Upsert_one TABLE xtable %DOC3_FOOORACOM_FOO% ER_X_BAD_INSERT_DATA
40
41
42
43 -->title =Data model: DOCUMENT
44
45 -->macro Setup %NAME%
46 -->stmtadmin create_collection {"schema":"xtest","name":"%NAME%"}
47 -->recvresult
48 -->stmtadmin create_collection_index {"schema":"xtest", "collection":"%NAME%", "name":"secondary", "unique":true, "fields":{"field":"$.email", "type":"TEXT(20)","required":false}}
49 -->recvresult
50 -->callmacro Insert_two DOCUMENT %NAME% %DOC1_FOOBARCOM_FOO% %DOC2_BLABARCOM_BLA% ER_SUCCESS
51 -->callmacro Show %NAME%
52 -->endmacro
53
54 -->macro Setup_without_data %NAME%
55 -->stmtadmin create_collection {"schema":"xtest","name":"%NAME%"}
56 -->recvresult
57 -->stmtadmin create_collection_index {"schema":"xtest", "collection":"%NAME%", "name":"secondary", "unique":true, "fields":{"field":"$.email", "type":"TEXT(20)","required":false}}
58 -->recvresult
59 -->endmacro
60
61
62 -->macro Setup_drop_id %NAME%
63 -->callmacro Setup %NAME%
64 -->stmtsql ALTER TABLE xtest.%NAME% DROP COLUMN `_id`;
65 -->recvresult
66 -->endmacro
67
68
69 -->title -Test0 - No matches
70 -->callmacro Setup col0
71 -->callmacro Upsert_one DOCUMENT col0 %DOC3_FOOORACOM_FOO% ER_SUCCESS
72 -->callmacro Show col0
73
74 -->title -Test1 - Match on _id
75 -->callmacro Setup col1
76 -->callmacro Upsert_one DOCUMENT col1 %DOC1_FOOORACOM_FOO% ER_SUCCESS
77 -->callmacro Show col1
78
79 -->title -Test2 - Match on secondary key only
80 -->callmacro Setup col2
81 -->callmacro Upsert_one DOCUMENT col2 %DOC3_FOOBARCOM_BLA% ER_X_DUPLICATE_ENTRY
82 -->callmacro Show col2
83
84 -->title -Test3 - Match on _id only, missing secondary index
85 -->callmacro Setup col3
86 -->callmacro Upsert_one DOCUMENT col3 %DOC1_BLA% ER_SUCCESS
87 -->callmacro Show col3
88
89 -->title -Test4 - Match on _id and secondary, but from different rows
90 -->callmacro Setup col4
91 -->callmacro Upsert_one DOCUMENT col4 %DOC1_BLABARCOM_FOO% ER_X_DUPLICATE_ENTRY
92 -->callmacro Show col4
93
94 -->title -Test5 - Match on _id and secondary key (other field value updated)
95 -->callmacro Setup col5
96 -->callmacro Upsert_one DOCUMENT col5 %DOC1_FOOBARCOM_BLA% ER_SUCCESS
97 -->callmacro Show col5
98
99 -->title -Test6 - New unique document, with null on secondary key
100 -->callmacro Setup col6
101 -->callmacro Upsert_one DOCUMENT col5 %DOC3_NULL_BLA% ER_SUCCESS
102 -->callmacro Show col5
103
104 -->title -Test7 - New unique document, with missing field on secondary key
105 -->callmacro Setup col7
106 -->callmacro Upsert_one DOCUMENT col7 %DOC3_BLA% ER_SUCCESS
107 -->callmacro Show col7
108
109 -->title -Test8 - Match on _id only, null at secondary index
110 -->callmacro Setup col8
111 -->callmacro Upsert_one DOCUMENT col8 %DOC1_NULL_BLA% ER_SUCCESS
112 -->callmacro Show col8
113
114 -->title -Test9 - Match on _id and null value at secondary index
115 -->callmacro Setup_without_data col9
116 -->callmacro Insert_one DOCUMENT col9 %DOC1_BLA% ER_SUCCESS
117 -->callmacro Insert_one DOCUMENT col9 %DOC2_BLABARCOM_BLA% ER_SUCCESS
118 -->callmacro Insert_one DOCUMENT col9 %DOC3_BLA% ER_SUCCESS
119 -->callmacro Show col9
120 -->callmacro Upsert_one DOCUMENT col9 %DOC2_FOO% ER_SUCCESS
121 -->callmacro Show col9
122
123 -->title -Test10 - No match, null value at secondary index
124 -->callmacro Setup_without_data col10
125 -->callmacro Insert_one DOCUMENT col10 %DOC1_BLA% ER_SUCCESS
126 -->callmacro Insert_one DOCUMENT col10 %DOC2_BLABARCOM_BLA% ER_SUCCESS
127 -->callmacro Insert_one DOCUMENT col10 %DOC3_BLA% ER_SUCCESS
128 -->callmacro Show col10
129 -->callmacro Upsert_one DOCUMENT col10 %DOC4_FOO% ER_SUCCESS
130 -->callmacro Show col10
131
132 -->title -Test1-2 - Match on _id and secondary key (upsert two rows)
133 -->callmacro Setup col21
134 -->callmacro Upsert_two DOCUMENT col21 %DOC4_BLABLACOM_BLA% %DOC1_FOOORACOM_FOO% ER_SUCCESS
135 -->callmacro Show col21
136
137 -->title -Test2-2 - Match on secondary key only (upsert two rows)
138 -->callmacro Setup col22
139 -->callmacro Upsert_two DOCUMENT col22 %DOC4_BLABLACOM_BLA% %DOC3_FOOBARCOM_BLA% ER_X_DUPLICATE_ENTRY
140 -->callmacro Show col22
141
142 -->title -Test3-2 - Match on _id only (upsert two rows)
143 -->callmacro Setup col23
144 -->callmacro Upsert_two DOCUMENT col23 %DOC4_BLABLACOM_BLA% %DOC1_BLA% ER_SUCCESS
145 -->callmacro Show col23
146
147 -->title -Test4-2 - Match on _id and secondary, but from different rows (upsert two rows)
148 -->callmacro Setup col24
149 -->callmacro Upsert_two DOCUMENT col24 %DOC4_BLABLACOM_BLA% %DOC1_BLABARCOM_FOO% ER_X_DUPLICATE_ENTRY
150 -->callmacro Show col24
151
152
153 -->title -Test0-3 - No matches (insert instead upsert)
154 -->callmacro Setup col30
155 -->callmacro Insert_one DOCUMENT col30 %DOC3_FOOORACOM_FOO% ER_SUCCESS
156 -->callmacro Show col30
157
158 -->title -Test1-3 - Match on _id and secondary key (insert instead upsert)
159 -->callmacro Setup col31
160 -->callmacro Insert_one DOCUMENT col31 %DOC1_FOOORACOM_FOO% ER_X_DUPLICATE_ENTRY
161 -->callmacro Show col31
162
163 -->title -Test2-3 - Match on secondary key only (insert instead upsert)
164 -->callmacro Setup col32
165 -->callmacro Insert_one DOCUMENT col32 %DOC3_FOOBARCOM_BLA% ER_X_DUPLICATE_ENTRY
166 -->callmacro Show col32
167
168 -->title -Test3-3 - Match on _id only (insert instead upsert)
169 -->callmacro Setup col33
170 -->callmacro Insert_one DOCUMENT col33 %DOC1_BLA% ER_X_DUPLICATE_ENTRY
171 -->callmacro Show col33
172
173 -->title -Test4-3 - Match on _id and secondary, but from different rows (insert instead upsert)
174 -->callmacro Setup col34
175 -->callmacro Insert_one DOCUMENT col34 %DOC1_BLABARCOM_FOO% ER_X_DUPLICATE_ENTRY
176 -->callmacro Show col34
177
178
179 -->title -Test0-4 - No matches (drop _id column)
180 -->callmacro Setup_drop_id col40
181 -->callmacro Upsert_one DOCUMENT col40 %DOC3_FOOORACOM_FOO% ER_SUCCESS
182 -->callmacro Show col40
183
184 -->title -Test1-4 - Match on _id and secondary key (drop _id coulmn)
185 -->callmacro Setup_drop_id col41
186 -->callmacro Upsert_one DOCUMENT col41 %DOC1_FOOORACOM_FOO% ER_SUCCESS
187 -->callmacro Show col41
188
189 -->title -Test2-4 - Match on secondary key only (drop _id coulmn)
190 -->callmacro Setup_drop_id col42
191 -->callmacro Upsert_one DOCUMENT col42 %DOC3_FOOBARCOM_BLA% ER_X_DUPLICATE_ENTRY
192 -->callmacro Show col42
193
194 -->title -Test3-4 - Match on _id only (drop _id coulmn)
195 -->callmacro Setup_drop_id col43
196 -->callmacro Upsert_one DOCUMENT col43 %DOC1_BLA% ER_SUCCESS
197 -->callmacro Show col43
198
199 -->title -Test4-4 - Match on _id and secondary, but from different rows (drop _id coulmn)
200 -->callmacro Setup_drop_id col44
201 -->callmacro Upsert_one DOCUMENT col44 %DOC1_BLABARCOM_FOO% ER_X_DUPLICATE_ENTRY
202 -->callmacro Show col44
203
204 EOF
205
206 --write_file $MYSQL_TMP_DIR/crud_insert_upsert_dual_index.xpl
207 ## Test data
208 -->quiet
209 -->import crud_insert.macro
210 -->import crud_find.macro
211
212 -->stmtadmin create_collection {"schema":"xtest","name":"add_or_replace"}
213 -->recvresult
214 -->stmtadmin create_collection_index {"schema":"xtest", "collection":"add_or_replace", "name":"_name", "unique":true, "fields":{"field":"$.name", "type":"TEXT(50)","required":true}}
215 -->recvresult
216
217 -->varlet %DOC1_BASIC% type: LITERAL literal{type: V_OCTETS v_octets{value:'{"_id":"document_001", "name":"basic"}' content_type:2}}
218 -->varlet %DOC2_TO_UPDATE% type: LITERAL literal{type: V_OCTETS v_octets{value:'{"_id":"document_002", "name":"to update"}' content_type:2}}
219 -->varlet %DOC3_BASIC% type: LITERAL literal{type: V_OCTETS v_octets{value:'{"_id":"document_003", "name":"basic"}' content_type:2}}
220 -->varlet %DOC2_BASIC% type: LITERAL literal{type: V_OCTETS v_octets{value:'{"_id":"document_002", "name":"basic"}' content_type:2}}
221 -->varlet %DOC2_ADVENCED% type: LITERAL literal{type: V_OCTETS v_octets{value:'{"_id":"document_002", "name":"advenced"}' content_type:2}}
222
223 -->callmacro Upsert_one DOCUMENT add_or_replace %DOC1_BASIC% ER_SUCCESS
224 -->callmacro Upsert_one DOCUMENT add_or_replace %DOC2_TO_UPDATE% ER_SUCCESS
225 -->callmacro Upsert_one DOCUMENT add_or_replace %DOC3_BASIC% ER_X_DUPLICATE_ENTRY
226 -->callmacro Upsert_one DOCUMENT add_or_replace %DOC2_BASIC% ER_X_DUPLICATE_ENTRY
227 -->callmacro Upsert_one DOCUMENT add_or_replace %DOC2_ADVENCED% ER_SUCCESS
228 -->callmacro Show add_or_replace
229
230 EOF
231
232 --write_file $MYSQL_TMP_DIR/crud_insert_upsert_multi_index.xpl
233 ## Test data
234 -->quiet
235 -->import crud_insert.macro
236 -->import crud_find.macro
237
238 -->stmtadmin create_collection {"schema":"xtest","name":"multi"}
239 -->recvresult
240 -->stmtadmin create_collection_index {"schema":"xtest", "collection":"multi", "name":"_first", "unique":true, "fields":{"field":"$.first", "type":"TEXT(20)","required":true}}
241 -->recvresult
242 -->stmtadmin create_collection_index {"schema":"xtest", "collection":"multi", "name":"_second", "unique":true, "fields":{"field":"$.second", "type":"TEXT(20)","required":true}}
243 -->recvresult
244
245 -->varlet %DOC1_A_A% type: LITERAL literal{type: V_OCTETS v_octets{value:'{"_id":"001", "first":"A", "second":"A"}' content_type:2}}
246 -->varlet %DOC2_B_B% type: LITERAL literal{type: V_OCTETS v_octets{value:'{"_id":"002", "first":"B", "second":"B"}' content_type:2}}
247 -->varlet %DOC3_A_B% type: LITERAL literal{type: V_OCTETS v_octets{value:'{"_id":"003", "first":"A", "second":"B"}' content_type:2}}
248 -->varlet %DOC1_A_B% type: LITERAL literal{type: V_OCTETS v_octets{value:'{"_id":"001", "first":"A", "second":"B"}' content_type:2}}
249 -->varlet %DOC2_A_B% type: LITERAL literal{type: V_OCTETS v_octets{value:'{"_id":"002", "first":"A", "second":"B"}' content_type:2}}
250 -->varlet %DOC3_B_A% type: LITERAL literal{type: V_OCTETS v_octets{value:'{"_id":"003", "first":"B", "second":"A"}' content_type:2}}
251 -->varlet %DOC1_B_A% type: LITERAL literal{type: V_OCTETS v_octets{value:'{"_id":"001", "first":"B", "second":"A"}' content_type:2}}
252 -->varlet %DOC2_B_A% type: LITERAL literal{type: V_OCTETS v_octets{value:'{"_id":"002", "first":"B", "second":"A"}' content_type:2}}
253
254 -->callmacro Upsert_one DOCUMENT multi %DOC1_A_A% ER_SUCCESS
255 -->callmacro Upsert_one DOCUMENT multi %DOC2_B_B% ER_SUCCESS
256 -->callmacro Upsert_one DOCUMENT multi %DOC3_A_B% ER_X_DUPLICATE_ENTRY
257 -->callmacro Upsert_one DOCUMENT multi %DOC1_A_B% ER_X_DUPLICATE_ENTRY
258 -->callmacro Upsert_one DOCUMENT multi %DOC2_A_B% ER_X_DUPLICATE_ENTRY
259 -->callmacro Upsert_one DOCUMENT multi %DOC3_B_A% ER_X_DUPLICATE_ENTRY
260 -->callmacro Upsert_one DOCUMENT multi %DOC1_B_A% ER_X_DUPLICATE_ENTRY
261 -->callmacro Upsert_one DOCUMENT multi %DOC2_B_A% ER_X_DUPLICATE_ENTRY
262 -->callmacro Show multi
263
264 EOF
265
266 --write_file $MYSQL_TMP_DIR/crud_insert_upsert_numerical_id.xpl
267 ## Test data
268 -->quiet
269 -->import crud_insert.macro
270 -->import crud_find.macro
271
272 -->stmtadmin create_collection {"schema":"xtest","name":"num"}
273 -->recvresult
274
275 -->varlet %DOC123_A_A% type: LITERAL literal{type: V_OCTETS v_octets{value:'{"_id":"123", "first":"A", "second":"A"}' content_type:2}}
276 -->varlet %DOC123_B_B% type: LITERAL literal{type: V_OCTETS v_octets{value:'{"_id":123, "first":"B", "second":"B"}' content_type:2}}
277
278 -->title -Numerical _id
279 -->callmacro Insert_one DOCUMENT num %DOC123_A_A% ER_SUCCESS
280 -->callmacro Show num
281 -->callmacro Upsert_one DOCUMENT num %DOC123_B_B% ER_SUCCESS
282 -->callmacro Show num
283
284 EOF
285
286 CREATE SCHEMA xtest;
287
288 exec $MYSQLXTEST
289 -ux_root --password=''
290 --file=$MYSQL_TMP_DIR/crud_insert_upsert.xpl 2>&1;
291
292 exec $MYSQLXTEST
293 -ux_root --password=''
294 --file=$MYSQL_TMP_DIR/crud_insert_upsert_dual_index.xpl 2>&1;
295
296 exec $MYSQLXTEST
297 -ux_root --password=''
298 --file=$MYSQL_TMP_DIR/crud_insert_upsert_multi_index.xpl 2>&1;
299
300 exec $MYSQLXTEST
301 -ux_root --password=''
302 --file=$MYSQL_TMP_DIR/crud_insert_upsert_numerical_id.xpl 2>&1;
303
304 ## Cleanup
305 --source ../include/xplugin_cleanup.inc