Debian Codegraph @

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

raw
1 ## Using Mysqlx::Expr::OBJECT in collection
2
3 ########### ../t/crud_doc_expr_array.test #############
4 ### #
5 ### This test runs aims to run ARRAY expresion statement #
6 ### variant with mysqlxtest client. #
7 ### Test covers #
8 ### - INSERT statements #
9 ### - FIND statements #
10 ### - UPDATE statements #
11 ### #
12 ##################################################################
13 #
14
15 --echo =============================================
16 --echo CRUD DOC ARRAY EXPRESIONS SCENARIOS
17 --echo =============================================
18 --echo
19
20 --echo ================================================================================
21 --echo PREAMBLE
22 --echo ================================================================================
23 --source include/xplugin_preamble.inc
24 --source include/xplugin_create_user.inc
25
26 ## Test starts here
27 --echo ================================================================================
28 --echo TEST START
29 --echo ================================================================================
30 --write_file $MYSQL_TMP_DIR/crud_doc_expr_array.xpl
31 ## Test data
32 -->sql
33 DROP SCHEMA IF EXISTS xtest;
34 CREATE SCHEMA xtest;
35 -->endsql
36
37 -->stmtadmin create_collection {"schema":"xtest", "name":"xcoll"}
38 -->recvresult
39
40 -->echo Inserting array into collection
41 Mysqlx.Crud.Insert {
42 collection {
43 name: "xcoll"
44 schema: "xtest"
45 }
46 data_model: DOCUMENT
47 row {
48 field {
49 type: OBJECT object {
50 fld {
51 key: "_id"
52 value {
53 type: LITERAL literal { type: V_OCTETS v_octets {value:"1"} }
54 }
55 }
56 fld {
57 key: "first"
58 value { type: LITERAL literal { type: V_OCTETS v_octets {value:"ten"} } }
59 }
60 fld {
61 key: "second"
62 value { type: LITERAL literal { type: V_OCTETS v_octets {value:"twenty"} } }
63 }
64 fld {
65 key: "third"
66 value { type: LITERAL literal { type: V_OCTETS v_octets {value:"thirty"} } }
67 }
68 fld {
69 key: "units"
70 value {
71 type: ARRAY array {
72 value {type: LITERAL literal { type: V_SINT v_signed_int: 1 } }
73 value {type: LITERAL literal { type: V_SINT v_signed_int: 2 } }
74 value {type: LITERAL literal { type: V_SINT v_signed_int: 3 } }
75 value {type: LITERAL literal { type: V_SINT v_signed_int: 4 } }
76 value {type: LITERAL literal { type: V_SINT v_signed_int: 5 } }
77 }
78 }
79 }
80 }
81 }
82 }
83 }
84 #-- Mysqlx.Sql.StmtExecuteOk
85 -->recvresult
86
87 -->sql
88 SELECT doc FROM xtest.xcoll;
89 -->endsql
90
91 -->echo Inserting array into collection with all the datatypes
92 Mysqlx.Crud.Insert {
93 collection {
94 name: "xcoll"
95 schema: "xtest"
96 }
97 data_model: DOCUMENT
98 row {
99 field {
100 type: OBJECT object {
101 fld {
102 key: "_id"
103 value {
104 type: LITERAL literal { type: V_OCTETS v_octets {value:"2"} }
105 }
106 }
107 fld {
108 key: "first"
109 value { type: LITERAL literal { type: V_STRING v_string {value: "ten again" } } }
110 }
111 fld {
112 key: "second"
113 value { type: LITERAL literal { type: V_STRING v_string {value: "twenty again" } } }
114 }
115 fld {
116 key: "third"
117 value { type: LITERAL literal { type: V_STRING v_string {value: "thirty again" } } }
118 }
119 fld {
120 key: "units"
121 value {
122 type: ARRAY array {
123 value {type: LITERAL literal { type: V_SINT v_signed_int: 1 } }
124 value {type: LITERAL literal { type: V_UINT v_unsigned_int: 2 } }
125 value {type: LITERAL literal { type: V_DOUBLE v_double: 3.13 } }
126 value {type: LITERAL literal { type: V_FLOAT v_float: 4.34 } }
127 value {type: LITERAL literal { type: V_BOOL v_bool: true } }
128 value {type: LITERAL literal { type: V_NULL } }
129 value {type: LITERAL literal { type: V_STRING v_string{ value: "extrainfo" } } }
130 }
131 }
132 }
133 }
134 }
135 }
136 }
137 #-- Mysqlx.Sql.StmtExecuteOk
138 -->recvresult
139
140 -->sql
141 SELECT doc FROM xtest.xcoll;
142 -->endsql
143
144 -->echo Selecting array from collection
145 Mysqlx.Crud.Find {
146 collection {
147 name: "xcoll"
148 schema: "xtest"
149 }
150 data_model: DOCUMENT
151 projection {
152 source {
153 type: OBJECT object {
154 fld {
155 key: "dozens"
156 value {
157 type: ARRAY array {
158 value { type: IDENT identifier { document_path { type: MEMBER value: "first" } } }
159 value { type: IDENT identifier { document_path { type: MEMBER value: "second" } } }
160 value { type: IDENT identifier { document_path { type: MEMBER value: "third" } } }
161 }
162 }
163 }
164 }
165 }
166 }
167 }
168 #-- Mysqlx.Sql.StmtExecuteOk
169 -->recvresult
170
171 -->echo Selecting array from collection
172 Mysqlx.Crud.Find {
173 collection {
174 name: "xcoll"
175 schema: "xtest"
176 }
177 data_model: DOCUMENT
178 projection {
179 source {
180 type: OBJECT object {
181 fld {
182 key: "UnitArray"
183 value {
184 type: ARRAY array {
185 value { type: IDENT identifier { document_path { type: MEMBER value: "units" } } }
186 }
187 }
188 }
189 }
190 }
191 }
192 }
193 #-- Mysqlx.Sql.StmtExecuteOk
194 -->recvresult
195
196 -->echo Selecting a value not available from collection to get null values
197 Mysqlx.Crud.Find {
198 collection {
199 name: "xcoll"
200 schema: "xtest"
201 }
202 data_model: DOCUMENT
203 projection {
204 source {
205 type: OBJECT object {
206 fld {
207 key: "UnitArray"
208 value {
209 type: ARRAY array {
210 value { type: IDENT identifier { document_path { type: MEMBER value: "notavailable" } } }
211 }
212 }
213 }
214 }
215 }
216 }
217 }
218 #-- Mysqlx.Sql.StmtExecuteOk
219 -->recvresult
220
221 -->echo Selecting array from collection with alias
222 Mysqlx.Crud.Find {
223 collection {
224 name: "xcoll"
225 schema: "xtest"
226 }
227 data_model: DOCUMENT
228 projection {
229 alias: "dozens"
230 source {
231 type: ARRAY array {
232 value { type: IDENT identifier { document_path { type: MEMBER value: "first" } } }
233 value { type: IDENT identifier { document_path { type: MEMBER value: "second" } } }
234 value { type: IDENT identifier { document_path { type: MEMBER value: "third" } } }
235 }
236 }
237 }
238 }
239 #-- Mysqlx.Sql.StmtExecuteOk
240 -->recvresult
241
242 -->echo Selecting array from collection with alias
243 Mysqlx.Crud.Find {
244 collection {
245 name: "xcoll"
246 schema: "xtest"
247 }
248 data_model: DOCUMENT
249 projection {
250 alias: "UnitArray"
251 source {
252 type: ARRAY array {
253 value { type: IDENT identifier { document_path { type: MEMBER value: "units" } } }
254 }
255 }
256 }
257 }
258 #-- Mysqlx.Sql.StmtExecuteOk
259 -->recvresult
260
261 -->echo Selecting a value not available from collection to get null values with alias
262 Mysqlx.Crud.Find {
263 collection {
264 name: "xcoll"
265 schema: "xtest"
266 }
267 data_model: DOCUMENT
268 projection {
269 alias: "UnitArray_Null"
270 source {
271 type: ARRAY array {
272 value { type: IDENT identifier { document_path { type: MEMBER value: "notavailable" } } }
273 }
274 }
275 }
276 }
277 #-- Mysqlx.Sql.StmtExecuteOk
278 -->recvresult
279 -->sql
280 use xtest;
281 select doc from xcoll;
282 -->endsql
283 -->echo Selecting array with sub-array from collection with alias
284 Mysqlx.Crud.Find {
285 collection {
286 name: "xcoll"
287 schema: "xtest"
288 }
289 data_model: DOCUMENT
290 projection {
291 alias: "numbers"
292 source {
293 type: ARRAY array {
294 value { type: IDENT identifier { document_path { type: MEMBER value: "first" } } }
295 value {
296 type: ARRAY array {
297 value {
298 type: IDENT identifier {
299 document_path { type: MEMBER value: "units" }
300 document_path { type: ARRAY_INDEX index: 0 }
301 }
302 }
303 value {
304 type: IDENT identifier {
305 document_path { type: MEMBER value: "units" }
306 document_path { type: ARRAY_INDEX index: 2 }
307 }
308 }
309 }
310 }
311 value { type: IDENT identifier { document_path { type: MEMBER value: "second" } } }
312 }
313 }
314 }
315 }
316
317 #-- Mysqlx.Sql.StmtExecuteOk
318 -->recvresult
319
320 -->echo Selecting array with sub-array including null values from collection with alias
321 Mysqlx.Crud.Find {
322 collection {
323 name: "xcoll"
324 schema: "xtest"
325 }
326 data_model: DOCUMENT
327 projection {
328 alias: "Array_SubArray_Null"
329 source {
330 type: ARRAY array {
331 value {
332 type: ARRAY array {
333 value {
334 type: IDENT identifier {
335 document_path { type: MEMBER value: "units" }
336 document_path { type: ARRAY_INDEX index: 1 }
337 }
338 }
339 value {
340 type: IDENT identifier {
341 document_path { type: MEMBER value: "units" }
342 document_path { type: ARRAY_INDEX index: 5 }
343 }
344 }
345 }
346 }
347 value {
348 type: ARRAY array {
349 value {
350 type: IDENT identifier {
351 document_path { type: MEMBER value: "units" }
352 document_path { type: ARRAY_INDEX index: 2 }
353 }
354 }
355 value {
356 type: IDENT identifier {
357 document_path { type: MEMBER value: "units" }
358 document_path { type: ARRAY_INDEX index: 5 }
359 }
360 }
361 }
362 }
363 value {
364 type: ARRAY array {
365 value {
366 type: IDENT identifier {
367 document_path { type: MEMBER value: "units" }
368 document_path { type: ARRAY_INDEX index: 3 }
369 }
370 }
371 value {
372 type: IDENT identifier {
373 document_path { type: MEMBER value: "units" }
374 document_path { type: ARRAY_INDEX index: 5 }
375 }
376 }
377 }
378 }
379 }
380 }
381 }
382 }
383 #-- Mysqlx.Sql.StmtExecuteOk
384 -->recvresult
385
386 -->echo Updating collection by array
387 Mysqlx.Crud.Update {
388 collection {
389 name: "xcoll"
390 schema: "xtest"
391 }
392 data_model: DOCUMENT
393 operation {
394 source {
395 document_path { type: MEMBER value: "dozens" }
396 }
397 operation: ITEM_SET
398 value {
399 type: ARRAY array {
400 value { type: IDENT identifier { document_path { type: MEMBER value: "first" } } }
401 value { type: IDENT identifier { document_path { type: MEMBER value: "second" } } }
402 value { type: IDENT identifier { document_path { type: MEMBER value: "third" } } }
403 }
404 }
405 }
406 }
407 #-- Mysqlx.Sql.StmtExecuteOk
408 -->recvresult
409
410 -->sql
411 SELECT doc FROM xtest.xcoll;
412 -->endsql
413
414 -->echo Updating collection by array with null values
415 Mysqlx.Crud.Update {
416 collection {
417 name: "xcoll"
418 schema: "xtest"
419 }
420 data_model: DOCUMENT
421 operation {
422 source {
423 document_path { type: MEMBER value: "NewArray" }
424 }
425 operation: ITEM_SET
426 value {
427 type: ARRAY array {
428 value { type: IDENT identifier { document_path { type: MEMBER value: "units" } } }
429 value { type: IDENT identifier { document_path { type: MEMBER value: "dozens" } } }
430 value { type: IDENT identifier { document_path { type: MEMBER value: "first" } } }
431 }
432 }
433 }
434 }
435 #-- Mysqlx.Sql.StmtExecuteOk
436 -->recvresult
437
438 -->sql
439 SELECT doc FROM xtest.xcoll;
440 -->endsql
441
442 -->echo Updating collection by array in a current member value
443 Mysqlx.Crud.Update {
444 collection {
445 name: "xcoll"
446 schema: "xtest"
447 }
448 data_model: DOCUMENT
449 operation {
450 source {
451 document_path { type: MEMBER value: "second" }
452 }
453 operation: ITEM_REPLACE
454 value {
455 type: ARRAY array {
456 value { type: IDENT identifier { document_path { type: MEMBER value: "units" } } }
457 value { type: IDENT identifier { document_path { type: MEMBER value: "dozens" } } }
458 value { type: IDENT identifier { document_path { type: MEMBER value: "first" } } }
459 }
460 }
461 }
462 }
463 #-- Mysqlx.Sql.StmtExecuteOk
464 -->recvresult
465
466 -->sql
467 SELECT doc FROM xtest.xcoll;
468 -->endsql
469
470 -->echo Updating collection by appending an array in a current array member value
471 Mysqlx.Crud.Update {
472 collection {
473 name: "xcoll"
474 schema: "xtest"
475 }
476 data_model: DOCUMENT
477 operation {
478 source {
479 document_path { type: MEMBER value: "second" }
480 }
481 operation: ARRAY_APPEND
482 value {
483 type: ARRAY array {
484 value { type: IDENT identifier { document_path { type: MEMBER value: "units" } } }
485 }
486 }
487 }
488 }
489 #-- Mysqlx.Sql.StmtExecuteOk
490 -->recvresult
491
492 -->sql
493 SELECT doc FROM xtest.xcoll;
494 -->endsql
495
496 -->echo Updating collection by inserting an array in a current array member value using == criteria
497 Mysqlx.Crud.Update {
498 collection {
499 name: "xcoll"
500 schema: "xtest"
501 }
502 data_model: DOCUMENT
503 criteria {
504 type: OPERATOR
505 operator {
506 name: "=="
507 param {
508 type: IDENT
509 identifier {
510 name: "_id"
511 }
512 }
513 param {
514 type: LITERAL
515 literal {
516 type: V_OCTETS
517 v_octets {value:"2"}
518 }
519 }
520 }
521 }
522 operation {
523 source {
524 document_path { type: MEMBER value: "second" }
525 document_path {type: ARRAY_INDEX index: 0}
526 }
527 operation: ARRAY_INSERT
528 value {
529 type: ARRAY array {
530 value { type: IDENT identifier { document_path { type: MEMBER value: "units" } } }
531 }
532 }
533 }
534 }
535 #-- Mysqlx.Sql.StmtExecuteOk
536 -->recvresult
537
538 -->sql
539 SELECT doc FROM xtest.xcoll;
540 -->endsql
541
542 -->echo Error: empty array
543 Mysqlx.Crud.Find {
544 collection {
545 name: "xcoll"
546 schema: "xtest"
547 }
548 data_model: DOCUMENT
549 projection {
550 source {
551 type: ARRAY array { }
552 }
553 }
554 }
555
556 -->recverror 5120
557
558
559 ## Cleanup
560 -->echo ================================================================================
561 -->echo CLEAN UP
562 -->echo ================================================================================
563 -->sql
564 drop schema if exists xtest;
565 -->endsql
566 EOF
567
568 --exec $MYSQLXTEST -ux_root --password='' --file=$MYSQL_TMP_DIR/crud_doc_expr_array.xpl 2>&1
569
570 ## Cleanup
571 --source ../include/xplugin_cleanup.inc
572