Debian Codegraph @

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

raw
1 ## Create view; insert, update, delete on view
2
3 ## Preamble
4 --source include/xplugin_preamble.inc
5 --source include/xplugin_create_user.inc
6
7 ## Test data
8 ## Test starts here
9 --let $xtest_file= $MYSQL_TMP_DIR/create_view.xpl
10 --write_file $xtest_file
11 -->quiet
12
13 -->title -Create view (bad select)
14 Mysqlx.Crud.CreateView {
15 collection { name: "xxx" schema: "xtest" }
16 stmt: {
17 collection { name: "xtable" schema: "xtest" }
18 projection {
19 source { type: IDENT identifier { name: "first" } }
20 }
21 data_model: DOCUMENT
22 }
23 }
24 -->recverror 5120
25
26
27 -->macro Create_view %NAME% %FIELD% %FIELD_ALIAS% %OPTION% %ERROR%
28 Mysqlx.Crud.CreateView {
29 collection { name: "%NAME%" schema: "xtest" }
30 %OPTION%
31 stmt: {
32 collection { name: "xtable" schema: "xtest" }
33 projection {
34 alias: "%FIELD_ALIAS%"
35 source { type: IDENT identifier { name: "%FIELD%" } }
36 }
37 data_model: TABLE
38 }
39 }
40 -->recvuntil %ERROR%
41 -->endmacro
42
43
44 -->macro Show_view %NAME%
45 -->sql
46 SHOW CREATE VIEW xtest.%NAME%
47 -->endsql
48 -->endmacro
49
50
51 -->title -Create view (default)
52 -->callmacro Create_view xview first one # Mysqlx.Ok
53 -->callmacro Show_view xview
54
55
56 -->title -Create view (collection)
57 -->callmacro Create_view xdoc second doc # Mysqlx.Ok
58 -->callmacro Show_view xdoc
59
60
61 -->title -List objects
62 -->stmtadmin list_objects {"schema":"xtest"}
63 -->recvresult
64
65
66 -->title -Create view (algorithm=undefined)
67 -->callmacro Create_view au second doc algorithm: UNDEFINED Mysqlx.Ok
68 -->callmacro Show_view au
69
70
71 -->title -Create view (algorithm=merge)
72 -->callmacro Create_view am second doc algorithm: MERGE Mysqlx.Ok
73 -->callmacro Show_view am
74
75
76 -->title -Create view (algorithm=temptable)
77 -->callmacro Create_view at second doc algorithm: TEMPTABLE Mysqlx.Ok
78 -->callmacro Show_view at
79
80
81 -->title -Create view (definer=xuser)
82 -->callmacro Create_view du second doc definer: "xuser" Mysqlx.Ok
83 -->callmacro Show_view du
84
85
86 -->title -Create view (security=invoker)
87 -->callmacro Create_view si second doc security: INVOKER Mysqlx.Ok
88 -->callmacro Show_view si
89
90
91 -->title -Create view (security=definer)
92 -->callmacro Create_view sd second doc security: DEFINER Mysqlx.Ok
93 -->callmacro Show_view sd
94
95
96 -->title -Create view (check=local)
97 -->callmacro Create_view cl second doc check: LOCAL Mysqlx.Ok
98 -->callmacro Show_view cl
99
100 -->title -Create view (check=cascaded)
101 -->callmacro Create_view cc second doc check: CASCADED Mysqlx.Ok
102 -->callmacro Show_view cc
103
104 -->title -Create view (column)
105 -->callmacro Create_view co second doc column: "third" Mysqlx.Ok
106 -->callmacro Show_view co
107
108 -->title -Create view (too many columns)
109 -->callmacro Create_view ct second doc column: "third" column: "fourth" Mysqlx.Error
110
111
112 -->title -Replace view (already exists)
113 -->callmacro Create_view co second fifth # Mysqlx.Error
114 -->callmacro Show_view co
115
116 -->title -Replace view
117 -->callmacro Create_view co second fifth replace_existing: true Mysqlx.Ok
118 -->callmacro Show_view co
119
120
121 -->title -Inserting data to collection_view
122 Mysqlx.Crud.Insert {
123 collection { name: "xdoc" schema: "xtest" }
124 data_model: DOCUMENT
125 row {
126 field {
127 type: LITERAL literal {
128 type: V_STRING v_string { value: '{"_id":"one", "name":"Adam"}' }
129 }
130 }
131 }
132 row {
133 field {
134 type: LITERAL literal {
135 type: V_STRING v_string { value: '{"_id":"two", "name":"Barnaba"}' }
136 }
137 }
138 }
139 }
140 -->recvresult
141
142
143 -->title -Selecting all data from collection_view
144 Mysqlx.Crud.Find {
145 collection { name: "xdoc" schema: "xtest"}
146 data_model: DOCUMENT
147 }
148 -->recvresult
149
150
151 -->title -Updating one member of collection_view
152 Mysqlx.Crud.Update {
153 collection { name: "xdoc" schema: "xtest" }
154 data_model: DOCUMENT
155 criteria {
156 type: OPERATOR operator {
157 name: "=="
158 param {
159 type: IDENT identifier { document_path { type: MEMBER value: "_id" } }
160 }
161 param {
162 type: LITERAL literal { type: V_OCTETS v_octets { value: "one" } }
163 }
164 }
165 }
166 operation {
167 source {
168 document_path { type: MEMBER value: "name" }
169 }
170 operation: ITEM_REPLACE
171 value {
172 type: LITERAL literal { type: V_STRING v_string { value: "Charles" } }
173 }
174 }
175 }
176 -->recvresult
177 -->stmtsql SELECT * FROM xtest.xdoc;
178 -->recvresult
179
180
181 -->title -Deleting one row of collection_view
182 Mysqlx.Crud.Delete {
183 collection { name: "xdoc" schema: "xtest"}
184 data_model: DOCUMENT
185 criteria {
186 type: OPERATOR operator {
187 name: "=="
188 param {
189 type: IDENT identifier { document_path { type: MEMBER value: "_id" } }
190 }
191 param {
192 type: LITERAL literal { type: V_OCTETS v_octets { value: "two" } }
193 }
194 }
195 }
196 }
197 -->recvresult
198 -->stmtsql SELECT * FROM xtest.xdoc;
199 -->recvresult
200
201
202 -->title -Updating whole doc of collection_view
203 Mysqlx.Crud.Update {
204 collection { name: "xdoc" schema: "xtest" }
205 data_model: DOCUMENT
206 operation {
207 source {
208 document_path { type: MEMBER value: "" }
209 }
210 operation: ITEM_REPLACE
211 value {
212 type: LITERAL literal { type: V_STRING v_string { value: '{"_id":"three", "name":"David"}' } }
213 }
214 }
215 }
216 -->recvresult
217 -->stmtsql SELECT * FROM xtest.xdoc;
218 -->recvresult
219
220 -->stmtsql SHOW STATUS LIKE 'Mysqlx_crud_create_view'
221 -->recvresult
222
223 EOF
224
225
226 CREATE SCHEMA xtest;
227 CREATE TABLE xtest.xtable (first INT, second JSON);
228 CREATE USER xuser;
229
230
231 --exec $MYSQLXTEST -ux_root --password='' --file=$xtest_file 2>&1
232
233 --let $assert_text= Global status of 'Mysqlx_crud_create_view'
234 --let $assert_cond= [SHOW GLOBAL STATUS LIKE \'Mysqlx_crud_create_view\', Value, 1] = 15
235 --source include/assert.inc
236
237 ## Cleanup
238 --source ../include/xplugin_cleanup.inc
239 # `xuser` is definer of the view dropped in xplugin_cleanup.inc (xtest schema is dropped).
240 DROP USER xuser;