Debian Codegraph @

mysql-test/suite/x/t/delete_crud_1.test

raw
1 ######### ../t/delete_sql_1.test #############
2 # #
3 # This test runs aims to run DELETE xmessage variant #
4 # with mysqlxtest client. #
5 # Test covers #
6 # - delete with transaction (rollback/commit) #
7 # - delete with WHERE,LIMIT,ORDER BY parameters #
8 # #
9 ######################################################################
10 #
11
12
13
14 ## =============================================
15 ##
16 ## CRUD DELETE SCENARIOS
17 ##
18 ## =============================================
19
20 --source include/xplugin_preamble.inc
21 --source include/xplugin_create_user.inc
22
23 ## TEST STARTS HERE
24
25 --write_file $MYSQL_TMP_DIR/mysqlx-delete_sql.xpl
26 -->sql
27 DROP SCHEMA if EXISTS dbmysqlxplugintest;
28 CREATE SCHEMA dbmysqlxplugintest DEFAULT CHARSET='utf8';
29 USE dbmysqlxplugintest;
30
31 CREATE TABLE Categories (
32 CategoryID int NOT NULL AUTO_INCREMENT,
33 CategoryName varchar(100),
34 CategoryDescription varchar(200),
35 CategoryIMEI tinyint,
36 CategoryDecimal decimal(5,2),
37 PRIMARY key (CategoryID)
38 );
39
40 INSERT INTO Categories(CategoryID, CategoryName, CategoryDescription, CategoryIMEI, CategoryDecimal)
41 VALUES
42 (1,'Sports','Sports related category',1,235.15),
43 (2,'Entertaiment','Entertaiment related category',2,235.15),
44 (3, 'Home','Home related category',3,235.15),
45 (4, 'Kitchen','Kitchen related category',4,235.15),
46 (5, 'Garden','Garden related category',5,535.15),
47 (6, 'Toys','Toys related category',6,635.15);
48
49 CREATE TABLE ExtraCategoryInfo (
50 ExtraCategoryInfoID int NOT NULL AUTO_INCREMENT,
51 CategoryID int NOT NULL,
52 MainParentCategoryName varchar(100),
53 PRIMARY key (ExtraCategoryInfoID)
54 );
55
56 INSERT INTO ExtraCategoryInfo(ExtraCategoryInfoID, CategoryID, MainParentCategoryName)
57 VALUES
58 (1,1,'People'),
59 (2,2,'People'),
60 (3,2,'House'),
61 (4,3,'House'),
62 (5,1,'People'),
63 (6,2,'People'),
64 (7,2,'House'),
65 (8,3,'House'),
66 (9,1,'People'),
67 (10,2,'House');
68
69 -->endsql
70
71 -->sql
72 SELECT * FROM dbmysqlxplugintest.ExtraCategoryInfo;
73
74 -->endsql
75
76 -->echo Delete with In, && operators
77 Mysqlx.Crud.Delete {
78 collection {
79 name: "ExtraCategoryInfo"
80 schema: "dbmysqlxplugintest"
81 }
82 data_model: TABLE
83 criteria {
84 type: OPERATOR
85 operator {
86 name: "&&"
87 param {
88 type: OPERATOR
89 operator {
90 name: "in"
91 param {
92 type: IDENT
93 identifier {
94 name: "ExtraCategoryInfoID"
95 }
96 }
97 param {
98 type: LITERAL
99 literal {
100 type: V_SINT
101 v_signed_int: 1
102 }
103 }
104 param {
105 type: LITERAL
106 literal {
107 type: V_SINT
108 v_signed_int: 3
109 }
110 }
111 }
112 }
113 param {
114 type: OPERATOR
115 operator {
116 name: "=="
117 param {
118 type: IDENT
119 identifier {
120 name: "CategoryID"
121 }
122 }
123 param {
124 type: LITERAL
125 literal {
126 type: V_SINT
127 v_signed_int: 2
128 }
129 }
130 }
131 }
132 }
133 }
134 }
135 #-- Mysqlx.Sql.StmtExecuteOk
136 -->recvresult
137
138 -->sql
139 SELECT * FROM dbmysqlxplugintest.ExtraCategoryInfo;
140
141 #-- "try xmessage within transaction"
142 START TRANSACTION;
143 -->endsql
144
145 -->echo Delete with == operator and rollback operation
146 Mysqlx.Crud.Delete {
147 collection {
148 name: "ExtraCategoryInfo"
149 schema: "dbmysqlxplugintest"
150 }
151 data_model: TABLE
152 criteria {
153 type: OPERATOR
154 operator {
155 name: "=="
156 param {
157 type: IDENT
158 identifier {
159 name: "ExtraCategoryInfoID"
160 }
161 }
162 param {
163 type: LITERAL
164 literal {
165 type: V_SINT
166 v_signed_int: 1
167 }
168 }
169 }
170 }
171 }
172 #-- Mysqlx.Sql.StmtExecuteOk
173 -->recvresult
174
175 -->sql
176 SELECT * FROM dbmysqlxplugintest.ExtraCategoryInfo;
177 ROLLBACK;
178 SELECT * FROM dbmysqlxplugintest.ExtraCategoryInfo;
179 -->endsql
180
181 -->sql
182 START TRANSACTION;
183 -->endsql
184 -->echo Delete with == operator and commit
185 Mysqlx.Crud.Delete {
186 collection {
187 name: "ExtraCategoryInfo"
188 schema: "dbmysqlxplugintest"
189 }
190 data_model: TABLE
191 criteria {
192 type: OPERATOR
193 operator {
194 name: "=="
195 param {
196 type: IDENT
197 identifier {
198 name: "ExtraCategoryInfoID"
199 }
200 }
201 param {
202 type: LITERAL
203 literal {
204 type: V_SINT
205 v_signed_int: 1
206 }
207 }
208 }
209 }
210 }
211 #-- Mysqlx.Sql.StmtExecuteOk
212 -->recvresult
213
214 -->sql
215 COMMIT;
216 SELECT * FROM dbmysqlxplugintest.ExtraCategoryInfo;
217 -->endsql
218 EOF
219
220 --exec $MYSQLXTEST -ux_root --password='' --file=$MYSQL_TMP_DIR/mysqlx-delete_sql.xpl 2>&1
221 --remove_file $MYSQL_TMP_DIR/mysqlx-delete_sql.xpl
222
223 # case 1 : delete with limit with offset
224 # Fix result file when bug is fixed
225 --write_file $MYSQL_TMP_DIR/mysqlx-delete_sql.xpl
226 -->sql
227 USE dbmysqlxplugintest;
228 SELECT * FROM dbmysqlxplugintest.ExtraCategoryInfo;
229 -->endsql
230 -->echo Use delete with limit and order parameters
231 Mysqlx.Crud.Delete {
232 collection {
233 name: "ExtraCategoryInfo"
234 schema: "dbmysqlxplugintest"
235 }
236 data_model: DOCUMENT
237 limit {
238 row_count:2
239 offset:5
240 }
241 }
242 # expect error (offset is not valid for Delete)
243 -->recv
244 -->sql
245 SELECT * FROM dbmysqlxplugintest.ExtraCategoryInfo;
246 -->endsql
247
248 -->echo Use delete with limit 0 and order parameters
249 Mysqlx.Crud.Delete {
250 collection {
251 name: "ExtraCategoryInfo"
252 schema: "dbmysqlxplugintest"
253 }
254 data_model: DOCUMENT
255 limit {
256 row_count:0
257 offset:0
258 }
259 }
260 # expect OK (offset 0 is OK for Delete)
261 -->recvresult
262
263 EOF
264
265 --exec $MYSQLXTEST -u x_root --password='' --file=$MYSQL_TMP_DIR/mysqlx-delete_sql.xpl 2>&1
266 --remove_file $MYSQL_TMP_DIR/mysqlx-delete_sql.xpl
267
268 # case 2 : delete with limit and order
269 # Uncomment $MYSQLXTEST call when order message is fixed
270 --write_file $MYSQL_TMP_DIR/mysqlx-delete_sql.xpl
271 -->sql
272 USE dbmysqlxplugintest;
273 SELECT * FROM dbmysqlxplugintest.ExtraCategoryInfo;
274 -->endsql
275 -->echo Use delete with limit and order parameters
276 Mysqlx.Crud.Delete {
277 collection {
278 name: "ExtraCategoryInfo"
279 schema: "dbmysqlxplugintest"
280 }
281 data_model: DOCUMENT
282 limit {
283 row_count:1
284 }
285 order {
286 expr {
287 type: IDENT
288 identifier {
289 name: "CategoryID"
290 }
291 }
292 direction:2
293 }
294
295 order {
296 expr {
297 type: IDENT
298 identifier {
299 name: "ExtraCategoryInfoID"
300 }
301 }
302 direction:2
303 }
304
305 }
306 -->recvresult
307 -->sql
308 SELECT * FROM dbmysqlxplugintest.ExtraCategoryInfo;
309 -->endsql
310 EOF
311
312 --exec $MYSQLXTEST -u x_root --password='' --file=$MYSQL_TMP_DIR/mysqlx-delete_sql.xpl 2>&1
313
314 # Cleanup
315 DROP DATABASE IF EXISTS dbmysqlxplugintest;
316 --source ../include/xplugin_cleanup.inc