4493d40a-92aa-4162-9dfc-bfc4ec0996e6
/
polardbxengine
2
0
Fork 0
Code Issues Pull Requests Packages Projects Releases Wiki Activity
polardbxengine/mysql-test/suite/x/t/update_crud_remove_o.test

760 lines
14 KiB
Plaintext
Raw Permalink Blame History

########### ../t/update_crud_remove_o.test #############
### #
### This test runs aims to run UPDATE REMOVE operation statement #
### variant with mysqlxtest client. #
### Test covers #
### - update with different operators #
### - update on multiple rows #
### - update (ITEM_REMOVE) with data model as document #
### - update with transaction (rollback/commit) #
### #
########################################################################
#
--echo =============================================
--echo CRUD UPDATE ITEM_REMOVE SCENARIOS
--echo =============================================
--echo
## Preamble
--echo ================================================================================
--echo PREAMBLE
--echo ================================================================================
--source include/xplugin_preamble.inc
create user updateitemremove@localhost identified by 'updateitemremove';
grant all on *.* to updateitemremove@localhost;
CREATE SCHEMA mysqlxcoll;
## TEST STARTS HERE
--echo ================================================================================
--echo TEST START
--echo ================================================================================
--write_file $MYSQL_TMP_DIR/mysqlx-update_itemremove.tmp
-->stmtadmin create_collection {"schema":"mysqlxcoll","name":"maincoll"}
-->recvresult
-->sql
SET SQL_SAFE_UPDATES = 0;
USE mysqlxcoll;
INSERT INTO maincoll (doc) values ('{"_id": "1", "name": "Victor", "last_name": "Otero","amount": 4.99}');
INSERT INTO maincoll (doc) values ('{"_id": "2", "name": "Gonzalo", "last_name": "Chrystens","amount": 120.57}');
INSERT INTO maincoll (doc) values ('{"_id": "3", "name": "Abraham", "last_name": "Vega","amount": 74.56}');
INSERT INTO maincoll (doc) values ('{"_id": "4", "name": "Jennifer", "last_name": "Leon","amount": 387.14}');
INSERT INTO maincoll (doc) values ('{"_id": "5", "name": "Jhonny", "last_name": "Test","amount": 125.45}');
-->endsql
-->echo Update a valid collection with == operator
Mysqlx.Crud.Update {
collection {
name: "maincoll"
schema: "mysqlxcoll"
}
data_model: DOCUMENT
criteria {
type: OPERATOR
operator {
name: "=="
param {
type: IDENT
identifier {
document_path {
type: MEMBER
value: "_id"
}
}
}
param {
type: LITERAL
literal {
type: V_OCTETS
v_octets {value:"5"}
}
}
}
}
operation {
source {
document_path {
type: MEMBER
value: "name"
}
}
operation: ITEM_REMOVE
}
}
#-- Mysqlx.Sql.StmtExecuteOk
-->recvresult
-->sql
select * from maincoll;
-->endsql
-->echo Update a valid collection with string value and > operator for multiple docs
Mysqlx.Crud.Update {
collection {
name: "maincoll"
schema: "mysqlxcoll"
}
data_model: DOCUMENT
criteria {
type: OPERATOR
operator {
name: ">"
param {
type: IDENT
identifier {
document_path {
type: MEMBER
value: "_id"
}
}
}
param {
type: LITERAL
literal {
type: V_OCTETS
v_octets {value:"3"}
}
}
}
}
operation {
source {
document_path {
type: MEMBER
value: "name"
}
}
operation: ITEM_REMOVE
}
}
#-- Mysqlx.Sql.StmtExecuteOk
-->recvresult
-->sql
select * from maincoll;
-->endsql
-->echo Update a valid collection with string value and < operator for multiple docs
Mysqlx.Crud.Update {
collection {
name: "maincoll"
schema: "mysqlxcoll"
}
data_model: DOCUMENT
criteria {
type: OPERATOR
operator {
name: "<"
param {
type: IDENT
identifier {
document_path {
type: MEMBER
value: "_id"
}
}
}
param {
type: LITERAL
literal {
type: V_OCTETS
v_octets {value:"3"}
}
}
}
}
operation {
source {
document_path {
type: MEMBER
value: "name"
}
}
operation: ITEM_REMOVE
}
}
#-- Mysqlx.Sql.StmtExecuteOk
-->recvresult
-->sql
select * from maincoll;
-->endsql
-->echo Update a valid collection with string value and <= operator for multiple docs
Mysqlx.Crud.Update {
collection {
name: "maincoll"
schema: "mysqlxcoll"
}
data_model: DOCUMENT
criteria {
type: OPERATOR
operator {
name: "<="
param {
type: IDENT
identifier {
document_path {
type: MEMBER
value: "_id"
}
}
}
param {
type: LITERAL
literal {
type: V_OCTETS
v_octets {value:"3"}
}
}
}
}
operation {
source {
document_path {
type: MEMBER
value: "last_name"
}
}
operation: ITEM_REMOVE
}
}
#-- Mysqlx.Sql.StmtExecuteOk
-->recvresult
-->sql
select * from maincoll;
-->endsql
-->echo Update a valid collection with string value and >= operator for multiple docs
Mysqlx.Crud.Update {
collection {
name: "maincoll"
schema: "mysqlxcoll"
}
data_model: DOCUMENT
criteria {
type: OPERATOR
operator {
name: ">="
param {
type: IDENT
identifier {
document_path {
type: MEMBER
value: "_id"
}
}
}
param {
type: LITERAL
literal {
type: V_OCTETS
v_octets {value:"4"}
}
}
}
}
operation {
source {
document_path {
type: MEMBER
value: "last_name"
}
}
operation: ITEM_REMOVE
}
}
#-- Mysqlx.Sql.StmtExecuteOk
-->recvresult
-->sql
select * from maincoll;
-->endsql
-->echo Update with order clause update last _id in desc order (_id = 5)
Mysqlx.Crud.Update {
collection {
name: "maincoll"
schema: "mysqlxcoll"
}
data_model: DOCUMENT
criteria {
type: OPERATOR
operator {
name: "!="
param {
type: IDENT
identifier {
document_path {
type: MEMBER
value: "_id"
}
}
}
param {
type: LITERAL
literal {
type: V_OCTETS
v_octets {value:"0"}
}
}
}
}
limit {
row_count: 1
}
order {
expr {
type: IDENT
identifier {
name: "_id"
}
}
direction: DESC
}
operation {
source {
document_path {
type: MEMBER
value: "amount"
}
}
operation: ITEM_REMOVE
}
}
#-- Mysqlx.Sql.StmtExecuteOk
-->recvresult
-->sql
select * from maincoll;
-->endsql
-->echo Update with order clause update first two _id in asc order (_id = 1,2)
Mysqlx.Crud.Update {
collection {
name: "maincoll"
schema: "mysqlxcoll"
}
data_model: DOCUMENT
criteria {
type: OPERATOR
operator {
name: "!="
param {
type: IDENT
identifier {
document_path {
type: MEMBER
value: "_id"
}
}
}
param {
type: LITERAL
literal {
type: V_OCTETS
v_octets {value:"0"}
}
}
}
}
limit {
row_count: 2
}
order {
expr {
type: IDENT
identifier {
name: "_id"
}
}
direction: ASC
}
operation {
source {
document_path {
type: MEMBER
value: "amount"
}
}
operation: ITEM_REMOVE
}
}
#-- Mysqlx.Sql.StmtExecuteOk
-->recvresult
-->sql
select * from maincoll;
-->endsql
-->echo Update a valid collection with string value and != operator for multiple docs
Mysqlx.Crud.Update {
collection {
name: "maincoll"
schema: "mysqlxcoll"
}
data_model: DOCUMENT
criteria {
type: OPERATOR
operator {
name: "!="
param {
type: IDENT
identifier {
document_path {
type: MEMBER
value: "_id"
}
}
}
param {
type: LITERAL
literal {
type: V_OCTETS
v_octets {value:"2"}
}
}
}
}
operation {
source {
document_path {
type: MEMBER
value: "amount"
}
}
operation: ITEM_REMOVE
}
}
#-- Mysqlx.Sql.StmtExecuteOk
-->recvresult
-->sql
select * from maincoll;
START TRANSACTION;
-->endsql
-->echo Update transaction rollback
Mysqlx.Crud.Update {
collection {
name: "maincoll"
schema: "mysqlxcoll"
}
data_model: DOCUMENT
criteria {
type: OPERATOR
operator {
name: "=="
param {
type: IDENT
identifier {
document_path {
type: MEMBER
value: "_id"
}
}
}
param {
type: LITERAL
literal {
type: V_OCTETS
v_octets {value:"3"}
}
}
}
}
operation {
source {
document_path {
type: MEMBER
value: "name"
}
}
operation: ITEM_REMOVE
}
}
#-- Mysqlx.Sql.StmtExecuteOk
-->recvresult
-->sql
select * from maincoll;
ROLLBACK;
select * from maincoll;
-->endsql
-->sql
START TRANSACTION;
-->endsql
-->echo Update transaction commit
Mysqlx.Crud.Update {
collection {
name: "maincoll"
schema: "mysqlxcoll"
}
data_model: DOCUMENT
criteria {
type: OPERATOR
operator {
name: "=="
param {
type: IDENT
identifier {
document_path {
type: MEMBER
value: "_id"
}
}
}
param {
type: LITERAL
literal {
type: V_OCTETS
v_octets {value:"3"}
}
}
}
}
operation {
source {
document_path {
type: MEMBER
value: "name"
}
}
operation: ITEM_REMOVE
}
}
#-- Mysqlx.Sql.StmtExecuteOk
-->recvresult
-->sql
COMMIT;
select * from maincoll;
-->endsql
-->echo Update with non existing doc member
Mysqlx.Crud.Update {
collection {
name: "maincoll"
schema: "mysqlxcoll"
}
data_model: DOCUMENT
criteria {
type: OPERATOR
operator {
name: "!="
param {
type: IDENT
identifier {
document_path {
type: MEMBER
value: "_id"
}
}
}
param {
type: LITERAL
literal {
type: V_OCTETS
v_octets {value:"2"}
}
}
}
}
operation {
source {
document_path {
type: MEMBER
value: "notamember"
}
}
operation: ITEM_REMOVE
}
}
#-- Mysqlx.Sql.StmtExecuteOk
-->recvresult
-->sql
select * from maincoll;
-->endsql
-->echo Invalid type of update for Document
Mysqlx.Crud.Update {
collection {
name: "maincoll"
schema: "mysqlxcoll"
}
data_model: DOCUMENT
criteria {
type: OPERATOR
operator {
name: ">"
param {
type: IDENT
identifier {
document_path {
type: MEMBER
value: "_id"
}
}
}
param {
type: LITERAL
literal {
type: V_OCTETS
v_octets {value:"3"}
}
}
}
}
operation {
source {
document_path {
type: MEMBER
value: "name"
}
}
operation: SET
value {
type: LITERAL
literal {
type: V_STRING
v_string {
value: "xtest"
}
}
}
}
}
-->expecterror 5051
-->recvresult
-->echo Invalid operator for Criteria
Mysqlx.Crud.Update {
collection {
name: "maincoll"
schema: "mysqlxcoll"
}
data_model: DOCUMENT
criteria {
type: OPERATOR
operator {
name: "not an operator"
param {
type: IDENT
identifier {
document_path {
type: MEMBER
value: "_id"
}
}
}
param {
type: LITERAL
literal {
type: V_OCTETS
v_octets {value:"3"}
}
}
}
}
operation {
source {
document_path {
type: MEMBER
value: "name"
}
}
operation: ITEM_REMOVE
}
}
-->expecterror 5150
-->recvresult
-->echo # Remove already removed item
-->sql
select * from mysqlxcoll.maincoll;
-->endsql
Mysqlx.Crud.Update {
collection {
name: "maincoll"
schema: "mysqlxcoll"
}
data_model: DOCUMENT
operation {
source {
document_path {
type: MEMBER
value: "amount"
}
}
operation: ITEM_REMOVE
}
}
-->recvresult
-->sql
select * from mysqlxcoll.maincoll;
-->endsql
Mysqlx.Crud.Update {
collection {
name: "maincoll"
schema: "mysqlxcoll"
}
data_model: DOCUMENT
operation {
source {
document_path {
type: MEMBER
value: "amount"
}
}
operation: ITEM_REMOVE
}
}
-->recvresult
-->sql
select * from mysqlxcoll.maincoll;
-->endsql
-->echo # Can not remove immutable document member _id
Mysqlx.Crud.Update {
collection {
name: "maincoll"
schema: "mysqlxcoll"
}
data_model: DOCUMENT
operation {
source {
document_path {
type: MEMBER
value: "_id"
}
}
operation: ITEM_REMOVE
}
}
-->recverror 5053
-->sql
select * from mysqlxcoll.maincoll;
-->endsql
## Cleanup
-->echo ================================================================================
-->echo CLEAN UP
-->echo ================================================================================
-->sql
DROP USER updateitemremove@localhost;
SET SQL_SAFE_UPDATES = 1;
-->endsql
EOF
--exec $MYSQLXTEST -u updateitemremove --password='updateitemremove' --file=$MYSQL_TMP_DIR/mysqlx-update_itemremove.tmp 2>&1
## Cleanup
DROP SCHEMA if EXISTS mysqlxcoll;
--remove_file $MYSQL_TMP_DIR/mysqlx-update_itemremove.tmp
Reference in New Issue View Git Blame Copy Permalink