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