Debian Codegraph @

mysql-test/suite/x/r/flow_resultset_crud_table.result

raw
1 CREATE SCHEMA IF NOT EXISTS xtest;
2 USE xtest;
3 CREATE PROCEDURE recreate_tables()
4 BEGIN
5 CREATE TABLE IF NOT EXISTS xtable (phrase VARCHAR(30), prio INTEGER);
6 TRUNCATE TABLE xtable;
7 INSERT INTO xtable (phrase, prio) VALUES ('Alice has a cat', 1);
8 INSERT INTO xtable (phrase, prio) VALUES ('Bob has a dog 1', 2);
9 INSERT INTO xtable (phrase, prio) VALUES ('Peppa has a teddy', 1);
10 INSERT INTO xtable (phrase, prio) VALUES ('Bob has a dog 2', 3);
11 INSERT INTO xtable (phrase, prio) VALUES ('Row to delete', 4);
12 CREATE TABLE IF NOT EXISTS xtable_with_pk (p_id INTEGER AUTO_INCREMENT PRIMARY KEY, description varchar(40));
13 TRUNCATE TABLE xtable_with_pk;
14 INSERT INTO xtable_with_pk (description) VALUES ('MyRow');
15 END //
16 CALL recreate_tables();
17
18
19 ## Test setup
20
21
22 ## I. Validate simple resultsets for CRUD messages
23 #
24 # 1. Assert resultset for CRUD-delete message
25 # 2. Assert resultset for CRUD-update message
26 # 3. Assert resultset for CRUD-create message (table with auto_inc column)
27 # 4. Assert resultset for CRUD-create message (table without auto_inc column)
28 # 5. Assert resultset for CRUD-read message
29
30 ## II. Validate simple resultset with warnings
31 #
32 # 1. Assert one resultsets for select queries
33
34 #
35 # I.1
36 send Mysqlx.Crud.Delete {
37 collection {
38 name: "xtable"
39 }
40 data_model: TABLE
41 criteria {
42 type: OPERATOR
43 operator {
44 name: "=="
45 param {
46 type: IDENT
47 identifier {
48 name: "prio"
49 }
50 }
51 param {
52 type: LITERAL
53 literal {
54 type: V_SINT
55 v_signed_int: 3
56 }
57 }
58 }
59 }
60 }
61
62 [ASSERT notice session state change - ROWS_AFFECTED, v_unsigned_int:1]
63 [ASSERT Mysqlx.Sql.StmtExecuteOk]
64
65 #
66 # I.2
67 send Mysqlx.Crud.Update {
68 collection {
69 name: "xtable"
70 }
71 data_model: TABLE
72 operation {
73 source {
74 name: "prio"
75 }
76 operation: SET
77 value {
78 type: OPERATOR
79 operator {
80 name: "-"
81 param {
82 type: IDENT
83 identifier {
84 name: "prio"
85 }
86 }
87 param {
88 type: LITERAL
89 literal {
90 type: V_SINT
91 v_signed_int: 1
92 }
93 }
94 }
95 }
96 }
97 }
98
99 [ASSERT notice session state change - PRODUCED_MESSAGE, type: V_STRING]
100 [ASSERT notice session state change - ROWS_AFFECTED, v_unsigned_int:4]
101 [ASSERT Mysqlx.Sql.StmtExecuteOk]
102
103 #
104 # I.3
105 send Mysqlx.Crud.Insert {
106 collection {
107 name: "xtable"
108 }
109 data_model: TABLE
110 row {
111 field {
112 type: LITERAL
113 literal {
114 type: V_STRING
115 v_string {
116 value: "sixth"
117 }
118 }
119 }
120 field {
121 type: LITERAL
122 literal {
123 type: V_SINT
124 v_signed_int: 3
125 }
126 }
127 }
128 upsert: false
129 }
130
131 [ASSERT notice session state change - ROWS_AFFECTED, v_unsigned_int:1]
132 [ASSERT Mysqlx.Sql.StmtExecuteOk]
133
134 #
135 # I.4
136 send Mysqlx.Crud.Insert {
137 collection {
138 name: "xtable_with_pk"
139 }
140 data_model: TABLE
141 projection {
142 name: "description"
143 }
144 row {
145 field {
146 type: LITERAL
147 literal {
148 type: V_STRING
149 v_string {
150 value: "test"
151 }
152 }
153 }
154 }
155 upsert: false
156 }
157
158 [ASSERT notice session state change - ROWS_AFFECTED, v_unsigned_int:1]
159 [ASSERT notice session state change - GENERATED_INSERT_ID, v_unsigned_int: 2]
160 [ASSERT Mysqlx.Sql.StmtExecuteOk]
161
162 #
163 # I.5
164 send Mysqlx.Crud.Find {
165 collection {
166 name: "xtable"
167 }
168 data_model: TABLE
169 projection {
170 source {
171 type: IDENT
172 identifier {
173 name: "prio"
174 }
175 }
176 alias: "prio"
177 }
178 }
179
180 [ASSERT Mysqlx.Resultset.ColumnMetaData]
181 [ASSERT Mysqlx.Resultset.Row]
182 [ASSERT Mysqlx.Resultset.Row]
183 [ASSERT Mysqlx.Resultset.Row]
184 [ASSERT Mysqlx.Resultset.Row]
185 [ASSERT Mysqlx.Resultset.Row]
186 [ASSERT Mysqlx.Resultset.FetchDone]
187 [ASSERT Mysqlx.Sql.StmtExecuteOk]
188
189 #
190 # II.1
191 send Mysqlx.Crud.Find {
192 collection {
193 name: "xtable"
194 }
195 data_model: TABLE
196 projection {
197 source {
198 type: OPERATOR
199 operator {
200 name: "/"
201 param {
202 type: LITERAL
203 literal {
204 type: V_SINT
205 v_signed_int: 1
206 }
207 }
208 param {
209 type: IDENT
210 identifier {
211 name: "prio"
212 }
213 }
214 }
215 }
216 alias: "name"
217 }
218 }
219
220 [ASSERT Mysqlx.Resultset.ColumnMetaData]
221 [ASSERT Mysqlx.Resultset.Row]
222 [ASSERT Mysqlx.Resultset.Row]
223 [ASSERT Mysqlx.Resultset.Row]
224 [ASSERT Mysqlx.Resultset.Row]
225 [ASSERT Mysqlx.Resultset.Row]
226 [ASSERT Mysqlx.Resultset.FetchDone]
227 [ASSERT notice warning - WARNING, 1365]
228 [ASSERT notice warning - WARNING, 1365]
229 [ASSERT Mysqlx.Sql.StmtExecuteOk]
230 Mysqlx.Ok {
231 msg: "bye!"
232 }
233 ok