1########### ../t/crud_table_criteria_args.test ############# 2### # 3### Manipulating on valid rows by criteria with placeholders # 4### Test covers # 5### - FIND with different operators # 6### - UPDATE (SET) with different operators # 7### - DELETE with different operators # 8### # 9######################################################################## 10# 11 12--echo ============================================= 13--echo CRUD TABLE CRITERIA ARGS SCENARIOS 14--echo ============================================= 15--echo 16 17--echo ================================================================================ 18--echo PREAMBLE 19--echo ================================================================================ 20## Preamble 21--source ../include/xplugin_preamble.inc 22## Test starts here 23--echo ================================================================================ 24--echo TEST START 25--echo ================================================================================ 26--write_file $MYSQL_TMP_DIR/crud_criteria_args.tmp 27## Test data 28-->sql 29drop schema if exists xtest; 30create schema xtest; 31use xtest; 32create table mytable (id int primary key, name varchar(40), price decimal(5,2), info json); 33insert into mytable values (1, 'banana', 1.20, '{"color": "yellow"}'); 34insert into mytable values (2, 'apple', 0.25, '{"color":"red"}'); 35insert into mytable values (3, 'tomato', 1.80, '{"color":"red"}'); 36insert into mytable values (4, 'mango', 3.14, '{"color":"green"}'); 37insert into mytable values (5, 'orange', 0.90, '{"color":"orange"}'); 38insert into mytable values (6, 'berry', null, '{"color":"orange"}'); 39SELECT * FROM xtest.mytable ORDER BY price; 40-->endsql 41 42-->echo Find with == Operator and placeholder 43Mysqlx.Crud.Find { 44 collection { 45 name: "mytable" 46 schema: "xtest" 47 } 48 data_model: TABLE 49 criteria { 50 type: OPERATOR 51 operator { 52 name: "==" 53 param { 54 type: IDENT 55 identifier { 56 name: "name" 57 } 58 } 59 param { 60 type: PLACEHOLDER 61 position: 0 62 } 63 } 64 } 65 args { 66 type: V_STRING 67 v_string{ 68 value: "tomato" 69 } 70 } 71} 72 73## expect Mysqlx.Sql.StmtExecuteOk 74-->recvresult 75 76-->echo Find with != Operator. placeholder and projection 77Mysqlx.Crud.Find { 78 collection { 79 name: "mytable" 80 schema: "xtest" 81 } 82 data_model: TABLE 83 projection { 84 source { 85 type: IDENT 86 identifier { 87 name: "name" 88 } 89 } 90 } 91 criteria { 92 type: OPERATOR 93 operator { 94 name: "==" 95 param { 96 type: IDENT 97 identifier { 98 name: "name" 99 } 100 } 101 param { 102 type: PLACEHOLDER 103 position: 0 104 } 105 } 106 } 107 args { 108 type: V_STRING 109 v_string{ 110 value: "tomato" 111 } 112 } 113} 114 115## expect Mysqlx.Sql.StmtExecuteOk 116-->recvresult 117 118-->echo Find with != Operator and placeholder 119Mysqlx.Crud.Find { 120 collection { 121 name: "mytable" 122 schema: "xtest" 123 } 124 data_model: TABLE 125 criteria { 126 type: OPERATOR 127 operator { 128 name: "!=" 129 param { 130 type: IDENT 131 identifier { 132 name: "name" 133 } 134 } 135 param { 136 type: PLACEHOLDER 137 position: 0 138 } 139 } 140 } 141 args { 142 type: V_STRING 143 v_string{ 144 value: "tomato" 145 } 146 } 147} 148 149## expect Mysqlx.Sql.StmtExecuteOk 150-->recvresult 151 152-->echo Find with > Operator and placeholder 153Mysqlx.Crud.Find { 154 collection { 155 name: "mytable" 156 schema: "xtest" 157 } 158 data_model: TABLE 159 criteria { 160 type: OPERATOR 161 operator { 162 name: ">" 163 param { 164 type: IDENT 165 identifier { 166 name: "price" 167 } 168 } 169 param { 170 type: PLACEHOLDER 171 position: 0 172 } 173 } 174 } 175 args { 176 type: V_DOUBLE 177 v_double: 1.80 178 } 179} 180 181## expect Mysqlx.Sql.StmtExecuteOk 182-->recvresult 183 184-->echo Find with != Operator, placeholder and Order desc 185Mysqlx.Crud.Find { 186 collection { 187 name: "mytable" 188 schema: "xtest" 189 } 190 data_model: TABLE 191 criteria { 192 type: OPERATOR 193 operator { 194 name: "!=" 195 param { 196 type: IDENT 197 identifier { 198 name: "price" 199 } 200 } 201 param { 202 type: PLACEHOLDER 203 position: 0 204 } 205 } 206 } 207 args { 208 type: V_DOUBLE 209 v_double: 0 210 } 211 order { 212 expr { 213 type: IDENT 214 identifier { 215 name: "name" 216 } 217 } 218 direction: DESC 219 } 220} 221 222## expect Mysqlx.Sql.StmtExecuteOk 223-->recvresult 224 225-->echo Find with != Operator, placeholder and Order asc 226Mysqlx.Crud.Find { 227 collection { 228 name: "mytable" 229 schema: "xtest" 230 } 231 data_model: TABLE 232 criteria { 233 type: OPERATOR 234 operator { 235 name: "!=" 236 param { 237 type: IDENT 238 identifier { 239 name: "price" 240 } 241 } 242 param { 243 type: PLACEHOLDER 244 position: 0 245 } 246 } 247 } 248 args { 249 type: V_DOUBLE 250 v_double: 0 251 } 252 order { 253 expr { 254 type: IDENT 255 identifier { 256 name: "name" 257 } 258 } 259 direction: ASC 260 } 261} 262 263## expect Mysqlx.Sql.StmtExecuteOk 264-->recvresult 265 266 267-->echo Find with < Operator and placeholder 268Mysqlx.Crud.Find { 269 collection { 270 name: "mytable" 271 schema: "xtest" 272 } 273 data_model: TABLE 274 criteria { 275 type: OPERATOR 276 operator { 277 name: "<" 278 param { 279 type: IDENT 280 identifier { 281 name: "price" 282 } 283 } 284 param { 285 type: PLACEHOLDER 286 position: 0 287 } 288 } 289 } 290 args { 291 type: V_DOUBLE 292 v_double: 0.25 293 } 294} 295 296## expect Mysqlx.Sql.StmtExecuteOk 297-->recvresult 298 299-->echo Find with >= Operator and placeholder 300Mysqlx.Crud.Find { 301 collection { 302 name: "mytable" 303 schema: "xtest" 304 } 305 data_model: TABLE 306 criteria { 307 type: OPERATOR 308 operator { 309 name: ">=" 310 param { 311 type: IDENT 312 identifier { 313 name: "price" 314 } 315 } 316 param { 317 type: PLACEHOLDER 318 position: 0 319 } 320 } 321 } 322 args { 323 type: V_DOUBLE 324 v_double: 1.80 325 } 326} 327 328## expect Mysqlx.Sql.StmtExecuteOk 329-->recvresult 330 331-->echo Find with <= Operator and placeholder 332Mysqlx.Crud.Find { 333 collection { 334 name: "mytable" 335 schema: "xtest" 336 } 337 data_model: TABLE 338 criteria { 339 type: OPERATOR 340 operator { 341 name: "<=" 342 param { 343 type: IDENT 344 identifier { 345 name: "price" 346 } 347 } 348 param { 349 type: PLACEHOLDER 350 position: 0 351 } 352 } 353 } 354 args { 355 type: V_DOUBLE 356 v_double: 0.25 357 } 358} 359 360## expect Mysqlx.Sql.StmtExecuteOk 361-->recvresult 362 363-->echo Find with in Operator and placeholder 364Mysqlx.Crud.Find { 365 collection { 366 name: "mytable" 367 schema: "xtest" 368 } 369 data_model: TABLE 370 criteria { 371 type: OPERATOR 372 operator { 373 name: "in" 374 param { 375 type: IDENT 376 identifier { 377 name: "price" 378 } 379 } 380 param { 381 type: PLACEHOLDER 382 position: 0 383 } 384 } 385 } 386 args { 387 type: V_DOUBLE 388 v_double: 1.80 389 } 390} 391 392## expect Mysqlx.Sql.StmtExecuteOk 393-->recvresult 394 395-->echo Find with not in Operator and placeholder 396Mysqlx.Crud.Find { 397 collection { 398 name: "mytable" 399 schema: "xtest" 400 } 401 data_model: TABLE 402 criteria { 403 type: OPERATOR 404 operator { 405 name: "not_in" 406 param { 407 type: IDENT 408 identifier { 409 name: "price" 410 } 411 } 412 param { 413 type: PLACEHOLDER 414 position: 0 415 } 416 } 417 } 418 args { 419 type: V_DOUBLE 420 v_double: 1.80 421 } 422} 423 424## expect Mysqlx.Sql.StmtExecuteOk 425-->recvresult 426 427-->echo Find with == Operator, V_NULL and placeholder 428Mysqlx.Crud.Find { 429 collection { 430 name: "mytable" 431 schema: "xtest" 432 } 433 data_model: TABLE 434 criteria { 435 type: OPERATOR 436 operator { 437 name: "is" 438 param { 439 type: IDENT 440 identifier { 441 name: "price" 442 } 443 } 444 param { 445 type: PLACEHOLDER 446 position: 0 447 } 448 } 449 } 450 args { 451 type: V_NULL 452 } 453} 454## expect Mysqlx.Sql.StmtExecuteOk 455-->recvresult 456 457## CRUD UPDATE PLACEHOLDER SCENARIOS 458 459-->echo Update with == operator and placeholder 460Mysqlx.Crud.Update { 461 collection { 462 name: "mytable" 463 schema: "xtest" 464 } 465 data_model: TABLE 466 criteria { 467 type: OPERATOR 468 operator { 469 name: "==" 470 param { 471 type: IDENT 472 identifier { 473 name: "price" 474 } 475 } 476 param { 477 type: PLACEHOLDER 478 position: 0 479 } 480 } 481 } 482 operation { 483 source { 484 name: "price" 485 } 486 operation: SET 487 value { 488 type: LITERAL 489 literal { 490 type: V_DOUBLE 491 v_double: 18.00 492 } 493 } 494 } 495 args { 496 type: V_DOUBLE 497 v_double: 1.80 498 } 499} 500 501## expect Mysqlx.Sql.StmtExecuteOk 502-->recvresult 503 504-->sql 505SELECT * FROM xtest.mytable ORDER BY price; 506-->endsql 507 508-->echo Update with > operator and placeholder 509Mysqlx.Crud.Update { 510 collection { 511 name: "mytable" 512 schema: "xtest" 513 } 514 data_model: TABLE 515 criteria { 516 type: OPERATOR 517 operator { 518 name: ">" 519 param { 520 type: IDENT 521 identifier { 522 name: "price" 523 } 524 } 525 param { 526 type: PLACEHOLDER 527 position: 0 528 } 529 } 530 } 531 operation { 532 source { 533 name: "price" 534 } 535 operation: SET 536 value { 537 type: LITERAL 538 literal { 539 type: V_DOUBLE 540 v_double: 10 541 } 542 } 543 } 544 args { 545 type: V_DOUBLE 546 v_double: 1 547 } 548} 549 550## expect Mysqlx.Sql.StmtExecuteOk 551-->recvresult 552 553-->sql 554SELECT * FROM xtest.mytable ORDER BY price; 555-->endsql 556 557-->echo Update with >= operator and placeholder 558Mysqlx.Crud.Update { 559 collection { 560 name: "mytable" 561 schema: "xtest" 562 } 563 data_model: TABLE 564 criteria { 565 type: OPERATOR 566 operator { 567 name: ">=" 568 param { 569 type: IDENT 570 identifier { 571 name: "price" 572 } 573 } 574 param { 575 type: PLACEHOLDER 576 position: 0 577 } 578 } 579 } 580 operation { 581 source { 582 name: "price" 583 } 584 operation: SET 585 value { 586 type: LITERAL 587 literal { 588 type: V_DOUBLE 589 v_double: 895.63 590 } 591 } 592 } 593 args { 594 type: V_DOUBLE 595 v_double: 1.80 596 } 597} 598 599## expect Mysqlx.Sql.StmtExecuteOk 600-->recvresult 601 602-->sql 603SELECT * FROM xtest.mytable ORDER BY price; 604-->endsql 605 606-->echo Update with <= operator and placeholder 607Mysqlx.Crud.Update { 608 collection { 609 name: "mytable" 610 schema: "xtest" 611 } 612 data_model: TABLE 613 criteria { 614 type: OPERATOR 615 operator { 616 name: "<=" 617 param { 618 type: IDENT 619 identifier { 620 name: "price" 621 } 622 } 623 param { 624 type: PLACEHOLDER 625 position: 0 626 } 627 } 628 } 629 operation { 630 source { 631 name: "price" 632 } 633 operation: SET 634 value { 635 type: LITERAL 636 literal { 637 type: V_DOUBLE 638 v_double: 456.54 639 } 640 } 641 } 642 args { 643 type: V_DOUBLE 644 v_double: 0.90 645 } 646} 647 648## expect Mysqlx.Sql.StmtExecuteOk 649-->recvresult 650 651-->sql 652SELECT * FROM xtest.mytable ORDER BY price; 653-->endsql 654 655-->echo Update with Float value and placeholder 656Mysqlx.Crud.Update { 657 collection { 658 name: "mytable" 659 schema: "xtest" 660 } 661 data_model: TABLE 662 criteria { 663 type: OPERATOR 664 operator { 665 name: "==" 666 param { 667 type: IDENT 668 identifier { 669 name: "name" 670 } 671 } 672 param { 673 type: PLACEHOLDER 674 position: 0 675 } 676 } 677 } 678 operation { 679 source { 680 name: "price" 681 } 682 operation: SET 683 value { 684 type: LITERAL 685 literal { 686 type: V_FLOAT 687 v_float: 256.53 688 } 689 } 690 } 691 args { 692 type: V_STRING 693 v_string{ 694 value: "berry" 695 } 696 } 697} 698 699## expect Mysqlx.Sql.StmtExecuteOk 700-->recvresult 701 702-->sql 703SELECT * FROM xtest.mytable ORDER BY price; 704-->endsql 705 706-->echo Update with String value and placeholder 707Mysqlx.Crud.Update { 708 collection { 709 name: "mytable" 710 schema: "xtest" 711 } 712 data_model: TABLE 713 criteria { 714 type: OPERATOR 715 operator { 716 name: "==" 717 param { 718 type: IDENT 719 identifier { 720 name: "price" 721 } 722 } 723 param { 724 type: PLACEHOLDER 725 position: 0 726 } 727 } 728 } 729 operation { 730 source { 731 name: "name" 732 } 733 operation: SET 734 value { 735 type: LITERAL 736 literal { 737 type: V_STRING 738 v_string{ 739 value: "watermelon" 740 } 741 } 742 } 743 } 744 args { 745 type: V_DOUBLE 746 v_double: 256.53 747 } 748} 749 750## expect Mysqlx.Sql.StmtExecuteOk 751-->recvresult 752 753-->sql 754SELECT * FROM xtest.mytable ORDER BY price; 755-->endsql 756 757-->echo Update with Null value and placeholder 758Mysqlx.Crud.Update { 759 collection { 760 name: "mytable" 761 schema: "xtest" 762 } 763 data_model: TABLE 764 criteria { 765 type: OPERATOR 766 operator { 767 name: "==" 768 param { 769 type: IDENT 770 identifier { 771 name: "name" 772 } 773 } 774 param { 775 type: PLACEHOLDER 776 position: 0 777 } 778 } 779 } 780 operation { 781 source { 782 name: "price" 783 } 784 operation: SET 785 value { 786 type: LITERAL 787 literal { 788 type: V_NULL 789 } 790 } 791 } 792 args { 793 type: V_STRING 794 v_string{ 795 value: "banana" 796 } 797 } 798} 799 800## expect Mysqlx.Sql.StmtExecuteOk 801-->recvresult 802 803-->sql 804SELECT * FROM xtest.mytable ORDER BY price; 805-->endsql 806 807-->echo Update with ITEM_MERGE value and placeholder 808Mysqlx.Crud.Update { 809 collection { 810 name: "mytable" 811 schema: "xtest" 812 } 813 data_model: TABLE 814 criteria { 815 type: OPERATOR 816 operator { 817 name: "==" 818 param { 819 type: IDENT 820 identifier { 821 name: "name" 822 } 823 } 824 param { 825 type: PLACEHOLDER 826 position: 0 827 } 828 } 829 } 830 operation { 831 source { 832 name: 'info' 833 } 834 operation: ITEM_MERGE 835 value: { 836 type: LITERAL 837 literal { 838 type: V_OCTETS 839 v_octets {value:'{"third":3.0, "fourth": "four"}'} 840 } 841 } 842 } 843 args { 844 type: V_STRING 845 v_string{ 846 value: "banana" 847 } 848 } 849} 850 851-- Mysqlx.Sql.StmtExecuteOk 852-->recvresult 853 854-->sql 855SELECT * FROM xtest.mytable ORDER BY price; 856insert into xtest.mytable values (7, 'Grapes',null, '{"third": ["two"]}'); 857-->endsql 858 859-->echo Update with ARRAY_INSERT value and placeholder 860Mysqlx.Crud.Update { 861 collection { 862 name: "mytable" 863 schema: "xtest" 864 } 865 data_model: TABLE 866 criteria { 867 type: OPERATOR 868 operator { 869 name: "==" 870 param { 871 type: IDENT 872 identifier { 873 name: "name" 874 } 875 } 876 param { 877 type: PLACEHOLDER 878 position: 0 879 } 880 } 881 } 882 operation { 883 source { 884 name: 'info' 885 document_path {type: MEMBER value: 'third'} 886 document_path {type: ARRAY_INDEX index: 0} 887 } 888 operation: ARRAY_INSERT 889 value: { 890 type: LITERAL 891 literal { 892 type: V_OCTETS 893 v_octets {value:'two.1'} 894 } 895 } 896 } 897 args { 898 type: V_STRING 899 v_string{ 900 value: "Grapes" 901 } 902 } 903} 904-- Mysqlx.Sql.StmtExecuteOk 905-->recvresult 906 907-->sql 908SELECT * FROM xtest.mytable ORDER BY price; 909-->endsql 910 911## CRUD DELETE PLACEHOLDER SCENARIOS 912 913-->echo Delete with == Operator and placeholder 914Mysqlx.Crud.Delete { 915 collection { 916 name: "mytable" 917 schema: "xtest" 918 } 919 data_model: TABLE 920 criteria { 921 type: OPERATOR 922 operator { 923 name: "==" 924 param { 925 type: IDENT 926 identifier { 927 name: "price" 928 } 929 } 930 param { 931 type: PLACEHOLDER 932 position: 0 933 } 934 } 935 } 936 args { 937 type: V_DOUBLE 938 v_double: 256.53 939 } 940} 941 942## expect Mysqlx.Sql.StmtExecuteOk 943-->recvresult 944 945-->sql 946SELECT * FROM xtest.mytable; 947-->endsql 948 949-->echo Delete with != Operator and placeholder 950Mysqlx.Crud.Delete { 951 collection { 952 name: "mytable" 953 schema: "xtest" 954 } 955 data_model: TABLE 956 criteria { 957 type: OPERATOR 958 operator { 959 name: "!=" 960 param { 961 type: IDENT 962 identifier { 963 name: "price" 964 } 965 } 966 param { 967 type: PLACEHOLDER 968 position: 0 969 } 970 } 971 } 972 args { 973 type: V_DOUBLE 974 v_double: 256.53 975 } 976} 977 978## expect Mysqlx.Sql.StmtExecuteOk 979-->recvresult 980 981-->sql 982SELECT * FROM xtest.mytable; 983-->endsql 984 985## Wrong or missing placeholder 986 987-->echo Find Wrong placeholder 988Mysqlx.Crud.Find { 989 collection { 990 name: "mytable" 991 schema: "xtest" 992 } 993 data_model: TABLE 994 criteria { 995 type: OPERATOR 996 operator { 997 name: ">" 998 param { 999 type: IDENT 1000 identifier { 1001 name: "price" 1002 } 1003 } 1004 param { 1005 type: PLACEHOLDER 1006 position: 1 1007 } 1008 } 1009 } 1010 args { 1011 type: V_DOUBLE 1012 v_double: 0 1013 } 1014} 1015 1016-->expecterror 5154 1017-->recvresult 1018 1019-->echo Find Missing placeholder 1020Mysqlx.Crud.Find { 1021 collection { 1022 name: "mytable" 1023 schema: "xtest" 1024 } 1025 data_model: TABLE 1026 criteria { 1027 type: OPERATOR 1028 operator { 1029 name: ">" 1030 param { 1031 type: IDENT 1032 identifier { 1033 name: "price" 1034 } 1035 } 1036 param { 1037 type: PLACEHOLDER 1038 position: 0 1039 } 1040 } 1041 } 1042} 1043 1044-->expecterror 5154 1045-->recvresult 1046 1047 1048## Cleanup 1049-->echo ================================================================================ 1050-->echo CLEAN UP 1051-->echo ================================================================================ 1052-->sql 1053drop schema if exists xtest; 1054-->endsql 1055EOF 1056 1057--exec $MYSQLXTEST -uroot --password='' --file=$MYSQL_TMP_DIR/crud_criteria_args.tmp 2>&1 1058--remove_file $MYSQL_TMP_DIR/crud_criteria_args.tmp 1059 1060## Postamble 1061--echo ================================================================================ 1062--echo POSTAMBLE 1063--echo ================================================================================ 1064uninstall plugin mysqlx; 1065