Debian Codegraph @

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

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