Debian Codegraph @

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

raw
1 ## Crud views sundries
2
3 ## Preamble
4 --source include/xplugin_preamble.inc
5 --source include/xplugin_create_user.inc
6
7 ## Test data
8 --let $xtest_file= $MYSQL_TMP_DIR/crud_view_sundries.xpl
9 --write_file $xtest_file
10 -->quiet
11
12 -->title -Create view on document table created using "Mysqlx.Sql.StmtExecute"
13 -->stmtadmin create_collection {"schema":"xtest", "name":"coll_a"}
14 -->recvresult
15 -->sql
16 CREATE VIEW xtest.view_a AS SELECT * FROM xtest.coll_a;
17 CREATE VIEW xtest.view_b AS SELECT doc FROM xtest.coll_a;
18 -->endsql
19 -->stmtadmin list_objects {"schema":"xtest"}
20 -->recvresult
21 -->sql
22 SHOW CREATE VIEW xtest.view_a;
23 SHOW CREATE VIEW xtest.view_b;
24 -->endsql
25
26
27 -->title -Create view based on another view
28 Mysqlx.Crud.CreateView {
29 collection { name: "view_c" schema: "xtest" }
30 algorithm: TEMPTABLE
31 stmt: {
32 collection { name: "view_a" schema: "xtest" }
33 data_model: TABLE
34 projection {
35 source { type: IDENT identifier { name: "doc" } }
36 }
37 }
38 }
39 -->recvuntil Mysqlx.Ok
40
41 -->sql
42 SHOW CREATE VIEW xtest.view_c;
43 -->endsql
44
45
46 -->title -Error when try modify view which is not updatable.
47 Mysqlx.Crud.ModifyView {
48 collection { name: "view_c" schema: "xtest" }
49 check: CASCADED
50 stmt: {
51 collection { name: "view_a" schema: "xtest"}
52 data_model: TABLE
53 projection {
54 source { type: IDENT identifier { name: "doc" } }
55 }
56 }
57 }
58 -->recverror ER_VIEW_NONUPD_CHECK
59
60
61 -->title -Run select query on view.
62 Mysqlx.Crud.CreateView {
63 collection { name: "view_d" schema: "xtest" }
64 stmt: {
65 collection { name: "tab_a" schema: "xtest" }
66 data_model: TABLE
67 projection {
68 alias: "doc"
69 source { type: IDENT identifier { name: "second" } }
70 }
71 }
72 }
73 -->recvuntil Mysqlx.Ok
74 -->sql
75 SELECT * FROM xtest.view_d;
76 -->endsql
77 Mysqlx.Crud.Find {
78 collection { name: "view_d" schema: "xtest"}
79 data_model: DOCUMENT
80 }
81 -->recvresult
82
83
84 -->title -Error on find if base table alteration invalidates the view.
85 -->sql
86 ALTER TABLE xtest.tab_a DROP second;
87 -->endsql
88 Mysqlx.Crud.Find {
89 collection { name: "view_d" schema: "xtest"}
90 data_model: DOCUMENT
91 }
92 -->recverror ER_VIEW_INVALID
93
94
95 -->title -Error on create view for non privilege user
96 -->newsession xuser_session xuser
97 Mysqlx.Crud.CreateView {
98 collection { name: "view_e" schema: "xtest" }
99 stmt: {
100 collection { name: "tab_a" schema: "xtest" }
101 data_model: TABLE
102 }
103 }
104 -->recverror ER_TABLEACCESS_DENIED_ERROR
105 -->closesession
106
107
108 -->title -Check view details added in information_schema.views
109 -->stmtadmin list_objects {"schema":"xtest"}
110 -->recvresult
111 -->sql
112 SELECT table_name,check_option,is_updatable,definer,security_type,view_definition
113 FROM information_schema.views WHERE table_schema = 'xtest';
114 -->endsql
115 EOF
116
117
118 CREATE SCHEMA xtest;
119 CREATE TABLE xtest.tab_a (first INT, second JSON);
120 INSERT INTO xtest.tab_a (first, second) VALUES (1, '{"_id":"one", "name":"Adam"}' );
121 CREATE USER xuser;
122
123 --exec $MYSQLXTEST -ux_root --password='' --file=$xtest_file 2>&1
124
125 ## Cleanup
126 DROP USER xuser;
127 --source ../include/xplugin_cleanup.inc