← Back to branch summary

~drizzle-trunk/drizzle/development

« back to all changes in this revision

Viewing changes to tests/t/randgen_queries.test

  • Committer: Jay Pipes
  • Date: 2009-09-15 21:01:42 UTC
  • mto: (1126.2.5 merge)
  • mto: This revision was merged to the branch mainline in revision 1128.
  • Revision ID: jpipes@serialcoder-20090915210142-x8mwiqn1q0vzjspp
Moves Alter_info out into its own header and source file, cleans up some related include mess in sql_lex.h, and renames Alter_info to AlterInfo.

Show diffs side-by-side

added added

removed removed

Lines of Context:
tests/t/randgen_queries.test
1
 
--disable_warnings
2
 
drop table if exists t1;
3
 
--enable_warnings
4
 
 
5
 
# execute all the queries that randgen uses to setup and run its tests
6
 
--replace_column 2 ####.##.####
7
 
show variables like 'version';
8
 
--replace_column 1 ####.##.####
9
 
select version();
10
 
select database();
11
 
 
12
 
CREATE TABLE `t1` (
13
 
`col_bigint` bigint,
14
 
`col_text` text,
15
 
`col_char` char (1),
16
 
`col_enum` enum ('a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z'),
17
 
`col_int` int,
18
 
`col_char_not_null` char (1) not null,
19
 
`col_int_not_null_key` int not null,
20
 
`col_text_not_null` text not null,
21
 
`col_enum_not_null_key` enum ('a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z') not null,
22
 
`col_int_key` int,
23
 
`col_char_key` char (1),
24
 
`col_enum_not_null` enum ('a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z') not null,
25
 
`col_text_not_null_key` text not null,
26
 
pk integer auto_increment,
27
 
`col_bigint_key` bigint,
28
 
`col_int_not_null` int not null,
29
 
`col_bigint_not_null` bigint not null,
30
 
`col_bigint_not_null_key` bigint not null,
31
 
`col_char_not_null_key` char (1) not null,
32
 
`col_text_key` text,
33
 
`col_enum_key` enum ('a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z'),
34
 
/*Indices*/
35
 
key (`col_int_not_null_key` ),
36
 
key (`col_enum_not_null_key` ),
37
 
key (`col_int_key` ),
38
 
key (`col_char_key` ),
39
 
key (`col_text_not_null_key`  (255)),
40
 
primary key (pk),
41
 
key (`col_bigint_key` ),
42
 
key (`col_bigint_not_null_key` ),
43
 
key (`col_char_not_null_key` ),
44
 
key (`col_text_key`  (255)),
45
 
key (`col_enum_key` )) ENGINE=innodb;
46
 
set AUTOCOMMIT=OFF;
47
 
 
48
 
# bookkeeping query in Executor/Drizzle.pm in randgen
49
 
select SQL_BIG_RESULT table_schema, 
50
 
       table_name, 
51
 
       CASE WHEN table_type = 'STANDARD' THEN 'table' 
52
 
            WHEN table_type = 'FUNCTION' then 'function' 
53
 
       ELSE 'misc' END, 
54
 
       column_name,
55
 
       CASE WHEN IS_USED_IN_PRIMARY = 'YES' THEN 'primary' 
56
 
             WHEN IS_INDEXED = 'YES' THEN 'indexed' 
57
 
       ELSE 'indexed' END 
58
 
       FROM data_dictionary.tables INNER JOIN
59
 
            data_dictionary.columns USING(table_schema, table_name) ORDER BY table_schema, table_name limit 20;
60
 
# end bookkeeping test
61
 
 
62
 
# bookkeeping query in Executor/Drizzle.pm in randgen - variant 1
63
 
# test of non-DATA_DICTIONARY / INFORMATION_SCHEMA tables
64
 
COMMIT;
65
 
CREATE SCHEMA randgen_query_test;
66
 
USE randgen_query_test;
67
 
CREATE TABLE t1 (a INT);
68
 
CREATE TABLE t2 LIKE t1;
69
 
CREATE TABLE t3 LIKE t1;
70
 
CREATE TABLE t4 (b CHAR(500));
71
 
CREATE TABLE t5 LIKE t4;
72
 
 
73
 
select SQL_BIG_RESULT table_schema,
74
 
       table_name,
75
 
       CASE WHEN table_type = 'STANDARD' THEN 'table'
76
 
            WHEN table_type = 'FUNCTION' then 'function'
77
 
       ELSE 'misc' END,
78
 
       column_name,
79
 
       CASE WHEN IS_USED_IN_PRIMARY = 'YES' THEN 'primary'
80
 
             WHEN IS_INDEXED = 'YES' THEN 'indexed'
81
 
       ELSE 'indexed' END
82
 
       FROM data_dictionary.tables INNER JOIN
83
 
            data_dictionary.columns USING(table_schema, table_name) WHERE table_schema NOT IN ('DATA_DICTIONARY','INFORMATION_SCHEMA')
84
 
       ORDER BY table_schema, table_name limit 20;
85
 
 
86
 
COMMIT;
87
 
DROP SCHEMA randgen_query_test;
88
 
USE test;
89
 
# end bookkeeping test
90
 
 
91
 
 
92
 
select AVG(`col_int_key`) + AVG(`col_int`) AS average1,
93
 
                        (SUM(`col_int_key`) + SUM(`col_int`)) / COUNT(*) AS average2,
94
 
                        COUNT(*) AS count FROM t1;
95
 
 
96
 
--sorted_result
97
 
select collation_name,character_set_name FROM data_dictionary.collations;
98
 
--replace_column 1 ####.##.####
99
 
--sorted_result
100
 
select COUNT(*) from data_dictionary.tables;
101
 
COMMIT;
102
 
DROP TABLE t1;