~ubuntu-branches/ubuntu/precise/mysql-5.1/precise

NULL db_datadict v_granted_glob /* ALGORITHM=UNDEFINED */ select `db_datadict`.`t1`.`f2` AS `f2`,`db_datadict`.`t1`.`f3` AS `f3` from `db_datadict`.`t1` NONE YES root@localhost DEFINER

NULL db_datadict v_granted_to_1 /* ALGORITHM=UNDEFINED */ select `db_datadict`.`t1`.`f1` AS `f1`,`db_datadict`.`t1`.`f2` AS `f2`,`db_datadict`.`t1`.`f3` AS `f3` from `db_datadict`.`t1` NONE YES root@localhost DEFINER

# Establish connection testuser1 (user=testuser1)

SELECT * FROM information_schema.views

WHERE table_schema = 'db_datadict' ORDER BY table_name;

TABLE_CATALOG TABLE_SCHEMA TABLE_NAME VIEW_DEFINITION CHECK_OPTION IS_UPDATABLE DEFINER SECURITY_TYPE

NULL db_datadict v_granted_glob NONE YES root@localhost DEFINER

NULL db_datadict v_granted_to_1 NONE YES root@localhost DEFINER

# Establish connection testuser2 (user=testuser2)

SELECT * FROM information_schema.views

WHERE table_schema = 'db_datadict' ORDER BY table_name;

TABLE_CATALOG TABLE_SCHEMA TABLE_NAME VIEW_DEFINITION CHECK_OPTION IS_UPDATABLE DEFINER SECURITY_TYPE

NULL db_datadict v_granted_glob NONE YES root@localhost DEFINER

NULL db_datadict v_granted_to_1 NONE YES root@localhost DEFINER

# Establish connection test_no_views (user=test_no_views)

100

SELECT * FROM information_schema.views

101

WHERE table_schema = 'db_datadict' ORDER BY table_name;

102

TABLE_CATALOG TABLE_SCHEMA TABLE_NAME VIEW_DEFINITION CHECK_OPTION IS_UPDATABLE DEFINER SECURITY_TYPE

103

NULL db_datadict v_granted_glob NONE YES root@localhost DEFINER

104

NULL db_datadict v_granted_to_1 NONE YES root@localhost DEFINER

105

# Switch to connection default and close all other connections

106

DROP USER 'testuser1'@'localhost';

107

DROP USER 'testuser2'@'localhost';

108

DROP USER 'test_no_views'@'localhost';

109

DROP DATABASE db_datadict;

110

#########################################################################

111

# 3.2.1.13+3.2.1.14+3.2.1.15: INFORMATION_SCHEMA.VIEWS modifications

112

#########################################################################

113

DROP TABLE IF EXISTS test.t1_my_table;

114

DROP DATABASE IF EXISTS db_datadict;

115

CREATE DATABASE db_datadict;

116

CREATE TABLE test.t1_table (f1 BIGINT, f2 CHAR(10))

117

DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci

118

ENGINE = <engine_type>;

119

DROP USER 'testuser1'@'localhost';

120

CREATE USER 'testuser1'@'localhost';

121

SELECT * FROM information_schema.views

122

WHERE table_name LIKE 't1_%';

123

TABLE_CATALOG TABLE_SCHEMA TABLE_NAME VIEW_DEFINITION CHECK_OPTION IS_UPDATABLE DEFINER SECURITY_TYPE

124

CREATE VIEW test.t1_view AS SELECT DISTINCT f1 FROM test.t1_table;

125

SELECT * FROM information_schema.views

126

WHERE table_name LIKE 't1_%';

127

TABLE_CATALOG TABLE_SCHEMA TABLE_NAME VIEW_DEFINITION CHECK_OPTION IS_UPDATABLE DEFINER SECURITY_TYPE

128

NULL test t1_view /* ALGORITHM=UNDEFINED */ select distinct `test`.`t1_table`.`f1` AS `f1` from `test`.`t1_table` NONE NO root@localhost DEFINER

129

SELECT table_name,definer FROM information_schema.views

130

WHERE table_name = 't1_view';

131

table_name definer

132

t1_view root@localhost

133

ALTER DEFINER = 'testuser1'@'localhost' VIEW test.t1_view AS

134

SELECT DISTINCT f1 FROM test.t1_table;

135

SELECT table_name,definer,security_type FROM information_schema.views

136

WHERE table_name LIKE 't1_%';

137

table_name definer security_type

138

t1_view testuser1@localhost DEFINER

139

ALTER DEFINER = 'root'@'localhost' SQL SECURITY INVOKER VIEW test.t1_view AS

140

SELECT f1 FROM test.t1_table WITH LOCAL CHECK OPTION;

141

SELECT table_name,definer,security_type FROM information_schema.views

142

WHERE table_name LIKE 't1_%';

143

table_name definer security_type

144

t1_view root@localhost INVOKER

145

SELECT table_schema,table_name FROM information_schema.views

146

WHERE table_name LIKE 't1_%'

147

ORDER BY table_schema,table_name;

148

table_schema table_name

149

test t1_view

150

RENAME TABLE test.t1_view TO db_datadict.t1_view;

151

ERROR HY000: Changing schema from 'test' to 'db_datadict' is not allowed.

152

DROP VIEW test.t1_view;

153

CREATE VIEW db_datadict.t1_view AS SELECT * FROM test.t1_table;

154

SELECT table_schema,table_name FROM information_schema.views

155

WHERE table_name LIKE 't1_%'

156

ORDER BY table_schema,table_name;

157

table_schema table_name

158

db_datadict t1_view

159

SELECT table_name FROM information_schema.views

160

WHERE table_name LIKE 't1_%'

161

ORDER BY table_name;

162

table_name

163

t1_view

164

RENAME TABLE db_datadict.t1_view TO db_datadict.t1_viewx;

165

SELECT table_name FROM information_schema.views

166

WHERE table_name LIKE 't1_%'

167

ORDER BY table_name;

168

table_name

169

t1_viewx

170

SELECT table_name FROM information_schema.views

171

WHERE table_name LIKE 't1_%'

172

ORDER BY table_name;

173

table_name

174

t1_viewx

175

DROP VIEW db_datadict.t1_viewx;

176

SELECT table_name FROM information_schema.views

177

WHERE table_name LIKE 't1_%'

178

ORDER BY table_name;

179

table_name

180

CREATE VIEW db_datadict.t1_view AS SELECT * FROM test.t1_table;

181

SELECT table_name FROM information_schema.views

182

WHERE table_name LIKE 't1_%'

183

ORDER BY table_name;

184

table_name

185

t1_view

186

DROP TABLE test.t1_table;

187

SELECT table_name FROM information_schema.views

188

WHERE table_name LIKE 't1_%'

189

ORDER BY table_name;

190

table_name

191

t1_view

192

Warnings:

193

Warning 1356 View 'db_datadict.t1_view' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

194

CREATE TABLE test.t1_table (f1 BIGINT, f2 CHAR(10))

195

DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci COMMENT = 'Initial Comment'

196

ENGINE = <engine_type>;

197

SELECT table_name FROM information_schema.views

198

WHERE table_name LIKE 't1_%'

199

ORDER BY table_name;

200

table_name

201

t1_view

202

DROP DATABASE db_datadict;

203

SELECT table_name FROM information_schema.views

204

WHERE table_name LIKE 't1_%'

205

ORDER BY table_name;

206

table_name

207

DROP USER 'testuser1'@'localhost';

208

DROP TABLE test.t1_table;

209

########################################################################

210

# Testcases 3.2.1.3-3.2.1.5 + 3.2.1.8-3.2.1.12: INSERT/UPDATE/DELETE and

211

# DDL on INFORMATION_SCHEMA table are not supported

212

########################################################################

213

DROP DATABASE IF EXISTS db_datadict;

214

CREATE DATABASE db_datadict;

215

CREATE VIEW db_datadict.v1 AS SELECT 1;

216

INSERT INTO information_schema.views

217

SELECT * FROM information_schema.views;

218