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