Hi List.
Below is tests/view.pl for testing CREATE VIEW options.
It tests several thousand permutations and was used to find MDEV-6709
Thanks, James Briggs.
--
Available for MySQL/NoSQL DBA/Programming in San Jose area or remote.
tests/view.pl:
#!/usr/bin/perl
# Program: view.pl
# Licence: GPL version 2
# Author: James Briggs
# Date: 2014 09 07
# Env: Perl 5
# Note: requires CREATE [[NO] FORCE] option
#
# Testing of views.
#
use strict;
use warnings;
use DBI;
use Getopt::Long;
$|=1;
use vars qw($opt_help $opt_Information $opt_force $opt_debug
$opt_verbose $opt_root_user $opt_root_password $opt_user $opt_password
$opt_database $opt_host $opt_silent);
$opt_help = $opt_Information = $opt_force = $opt_debug = $opt_verbose =
$opt_silent = 0;
$opt_host = localhost,
$opt_root_user = root;
$opt_root_password = ;
$opt_user = view_user;
$opt_password = view_user;
$opt_database = view_test;
$opt_force = 1;
my $version = 1.0;
my $opt_table=table1;
my $opt_user2 = $opt_user . '2';
GetOptions(Information,help,server=s,root-user=s,root-password=s,user,password=s,database=s,force,host=s,debug,verbose,silent)
|| usage();
usage() if ($opt_help || $opt_Information);
# magic constants
use constant N_TEST_RECORDS = 3;
use constant HAS_VIEW_GRANTS = 0;
use constant NO_VIEW_GRANTS = 1;
my @cols = qw[id col1 col2 col3 col4];
my $tmp_table=/tmp/mysql-view.test;
unlink($tmp_table);
# %roles array offets
use constant U_DBH= 0;
use constant U_PRIV_LEVEL = 1;
use constant U_USER = 2;
use constant U_HOST = 3;
use constant U_PASSWORD = 4;
# run tests with multiple user accounts from SUPER to very basic privs
my %roles = (
# [ U_DBH, U_PRIV_LEVEL,U_USER, U_HOST, U_PASSWORD ]
root = [ undef, HAS_VIEW_GRANTS, $opt_root_user, $opt_host,
$opt_root_password ],
power = [ undef, HAS_VIEW_GRANTS, $opt_user, $opt_host,
$opt_password ],
crud = [ undef, NO_VIEW_GRANTS, $opt_user2, $opt_host,
$opt_password], # use the non-root user account again, this time with
less privs
);
if (!$opt_force) {
print_info();
}
#
# setup test database
#
my $dbh = user_connect($opt_root_user,$opt_root_password, 0, 'test'); #
$opt_database may not exist yet, so connect with 'test'
$roles{'root'}-[U_DBH] = $dbh;
test_query('root', drop database if exists $opt_database); # drop database
to quickly drop any tables and views
test_query('root', create database $opt_database);
test_query('root', use $opt_database);
#
# setup test table
#
test_query('root', create table $opt_table (id int primary key
auto_increment, col1 int, col2 int, col3 int, col4 int));
my @c = @cols; # column names for test database
shift @c; # remove first column (id)
my $cols = join ',', @c; # squash array into a string
for my $i (1..N_TEST_RECORDS) {
test_query('root', insert into $opt_table ($cols) values (2, 3, 4, 5));
}
test_query('root', grant select, insert, update, delete, create, drop,
create view, show view on $opt_database.* to '$opt_user'\@'$opt_host'
identified by '$opt_password');
my $dbh_power = user_connect($opt_user, $opt_password, 0);
test_query('root', grant select, insert, update, delete on
$opt_database.* to '$opt_user2'\@'$opt_host' identified by
'$opt_password');
my $dbh_crud = user_connect($opt_user2, $opt_password, 0);
$roles{'power'}-[0] = $dbh_power;
$roles{'crud'}-[0] = $dbh_crud;
#
# test views
#
# query array offsets
use constant Q_QRY = 0;
use constant Q_HI_PRIV = 1;
use constant Q_LO_PRIV = 2;
use constant Q_OUTPUT = 3;
use constant Q_COMMENT = 4;
my @t0 = (
# [ query, ignore_failure_hi_priv,
ignore_failure_lo_priv, result, comment ]
# [
Q_QRY,Q_HI_PRIV, Q_LO_PRIV,
Q_OUTPUT, Q_COMMENT ]
[ create view $opt_table as select * from $opt_table, 1, 1,
undef, should fail - duplicate object name ],
[ create view view1 as select * from ${opt_table}2, 1, 1,
undef, should fail - no base table found ],
[ create view view1 as select * from $opt_table, 0,
1, undef, ],
[ select count(*) from view1, 0, 1,
N_TEST_RECORDS, ],
[ create definer = current_user() sql security invoker view v1 as select
1, 0, 1, undef, ],
[ drop view view1,0, 1,
undef, ],
[ drop view
v1, 0, 1, undef, ],
);
test_driver('original create view commands', \%roles, \@t0);
#
# test new CREATE NO FORCE VIEW view options in 10.1.x
#
# CREATE FORCE VIEW should work the same as default (omitted)
my @t1 = (
[ create no force view $opt_table as select * from $opt_table, 1, 1,
undef,
