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