113 lines
3.3 KiB
Plaintext
113 lines
3.3 KiB
Plaintext
CREATE SCHEMA xtest;
|
|
CREATE TABLE xtest.tab_a (first INT, second JSON);
|
|
INSERT INTO xtest.tab_a (first, second) VALUES (1, '{"_id":"one", "name":"Adam"}' );
|
|
CREATE USER xuser;
|
|
|
|
Create view on document table created using "Mysqlx.Sql.StmtExecute"
|
|
--------------------------------------------------------------------
|
|
|
|
command ok
|
|
|
|
0 rows affected
|
|
|
|
0 rows affected
|
|
name type
|
|
coll_a COLLECTION
|
|
tab_a TABLE
|
|
view_a VIEW
|
|
view_b COLLECTION_VIEW
|
|
command ok
|
|
View Create View character_set_client collation_connection
|
|
view_a CREATE ALGORITHM=UNDEFINED DEFINER=`x_root`@`localhost` SQL SECURITY DEFINER VIEW `xtest`.`view_a` AS select `xtest`.`coll_a`.`doc` AS `doc`,`xtest`.`coll_a`.`_id` AS `_id` from `xtest`.`coll_a` utf8 utf8_general_ci
|
|
0 rows affected
|
|
View Create View character_set_client collation_connection
|
|
view_b CREATE ALGORITHM=UNDEFINED DEFINER=`x_root`@`localhost` SQL SECURITY DEFINER VIEW `xtest`.`view_b` AS select `xtest`.`coll_a`.`doc` AS `doc` from `xtest`.`coll_a` utf8 utf8_general_ci
|
|
0 rows affected
|
|
|
|
Create view based on another view
|
|
---------------------------------
|
|
Mysqlx.Ok {
|
|
}
|
|
|
|
View Create View character_set_client collation_connection
|
|
view_c CREATE ALGORITHM=TEMPTABLE DEFINER=`x_root`@`localhost` SQL SECURITY DEFINER VIEW `xtest`.`view_c` AS select `view_a`.`doc` AS `doc` from `xtest`.`view_a` utf8 utf8_general_ci
|
|
0 rows affected
|
|
|
|
Error when try modify view which is not updatable.
|
|
--------------------------------------------------
|
|
Got expected error:
|
|
Mysqlx.Error {
|
|
severity: ERROR
|
|
code: 1368
|
|
msg: "CHECK OPTION on non-updatable view \'xtest.view_c\'"
|
|
sql_state: "HY000"
|
|
}
|
|
|
|
|
|
Run select query on view.
|
|
-------------------------
|
|
Mysqlx.Ok {
|
|
}
|
|
|
|
doc
|
|
{"_id": "one", "name": "Adam"}
|
|
0 rows affected
|
|
doc
|
|
{"_id": "one", "name": "Adam"}
|
|
command ok
|
|
|
|
Error on find if base table alteration invalidates the view.
|
|
------------------------------------------------------------
|
|
|
|
0 rows affected
|
|
Records: 0 Duplicates: 0 Warnings: 0
|
|
Got expected error:
|
|
Mysqlx.Error {
|
|
severity: ERROR
|
|
code: 1356
|
|
msg: "View \'xtest.view_d\' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them"
|
|
sql_state: "HY000"
|
|
}
|
|
|
|
|
|
Error on create view for non privilege user
|
|
-------------------------------------------
|
|
connecting...
|
|
active session is now 'xuser_session'
|
|
Got expected error:
|
|
Mysqlx.Error {
|
|
severity: ERROR
|
|
code: 1142
|
|
msg: "CREATE VIEW command denied to user \'xuser\'@\'localhost\' for table \'view_e\'"
|
|
sql_state: "42000"
|
|
}
|
|
|
|
closing session xuser_session
|
|
Mysqlx.Ok {
|
|
msg: "bye!"
|
|
}
|
|
switched to session default
|
|
|
|
Check view details added in information_schema.views
|
|
----------------------------------------------------
|
|
name type
|
|
coll_a COLLECTION
|
|
tab_a TABLE
|
|
view_a VIEW
|
|
view_b COLLECTION_VIEW
|
|
view_c COLLECTION_VIEW
|
|
view_d VIEW
|
|
command ok
|
|
TABLE_NAME CHECK_OPTION IS_UPDATABLE DEFINER SECURITY_TYPE VIEW_DEFINITION
|
|
view_a NONE YES x_root@localhost DEFINER select `xtest`.`coll_a`.`doc` AS `doc`,`xtest`.`coll_a`.`_id` AS `_id` from `xtest`.`coll_a`
|
|
view_b NONE YES x_root@localhost DEFINER select `xtest`.`coll_a`.`doc` AS `doc` from `xtest`.`coll_a`
|
|
view_c NONE NO x_root@localhost DEFINER select `view_a`.`doc` AS `doc` from `xtest`.`view_a`
|
|
view_d NONE YES x_root@localhost DEFINER select `xtest`.`tab_a`.`second` AS `doc` from `xtest`.`tab_a`
|
|
0 rows affected
|
|
Mysqlx.Ok {
|
|
msg: "bye!"
|
|
}
|
|
ok
|
|
DROP SCHEMA IF EXISTS xtest;
|
|
DROP USER xuser;
|