-
Notifications
You must be signed in to change notification settings - Fork 437
/
mydumper_testing_db_template.sql
159 lines (140 loc) · 6.69 KB
/
mydumper_testing_db_template.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
-- Tables and views
CREATE table IF NOT EXISTS mydumper_aipk_uuid (id int primary key auto_increment, val varchar(36));
INSERT into mydumper_aipk_uuid (val) values (uuid());
INSERT into mydumper_aipk_uuid (val) SELECT uuid() from mydumper_aipk_uuid;
INSERT into mydumper_aipk_uuid (val) SELECT uuid() from mydumper_aipk_uuid;
INSERT into mydumper_aipk_uuid (val) SELECT uuid() from mydumper_aipk_uuid;
INSERT into mydumper_aipk_uuid (val) SELECT uuid() from mydumper_aipk_uuid;
INSERT into mydumper_aipk_uuid (val) SELECT uuid() from mydumper_aipk_uuid;
INSERT into mydumper_aipk_uuid (val) SELECT uuid() from mydumper_aipk_uuid;
INSERT into mydumper_aipk_uuid (val) SELECT uuid() from mydumper_aipk_uuid;
INSERT into mydumper_aipk_uuid (val) SELECT uuid() from mydumper_aipk_uuid;
INSERT into mydumper_aipk_uuid (val) SELECT uuid() from mydumper_aipk_uuid;
INSERT into mydumper_aipk_uuid (val) SELECT uuid() from mydumper_aipk_uuid;
INSERT into mydumper_aipk_uuid (val) SELECT uuid() from mydumper_aipk_uuid;
INSERT into mydumper_aipk_uuid (val) SELECT uuid() from mydumper_aipk_uuid;
INSERT into mydumper_aipk_uuid (val) SELECT uuid() from mydumper_aipk_uuid;
INSERT into mydumper_aipk_uuid (val) SELECT uuid() from mydumper_aipk_uuid;
INSERT into mydumper_aipk_uuid (val) SELECT uuid() from mydumper_aipk_uuid;
-- INSERT into mydumper_aipk_uuid (val) SELECT uuid() from mydumper_aipk_uuid;
-- INSERT into mydumper_aipk_uuid (val) SELECT uuid() from mydumper_aipk_uuid;
-- INSERT into mydumper_aipk_uuid (val) SELECT uuid() from mydumper_aipk_uuid;
-- INSERT into mydumper_aipk_uuid (val) SELECT uuid() from mydumper_aipk_uuid;
CREATE TABLE `char_hex_test` ( `id` binary(16) NOT NULL, `val` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `val` (`val`));
INSERT INTO char_hex_test values (unhex(replace(uuid(),'-','')), rand()*1000);
INSERT INTO char_hex_test SELECT unhex(replace(uuid(),'-','')), rand()*1000 from char_hex_test;
INSERT INTO char_hex_test SELECT unhex(replace(uuid(),'-','')), rand()*1000 from char_hex_test;
INSERT INTO char_hex_test SELECT unhex(replace(uuid(),'-','')), rand()*1000 from char_hex_test;
INSERT INTO char_hex_test SELECT unhex(replace(uuid(),'-','')), rand()*1000 from char_hex_test;
INSERT INTO char_hex_test SELECT unhex(replace(uuid(),'-','')), rand()*1000 from char_hex_test;
INSERT INTO char_hex_test SELECT unhex(replace(uuid(),'-','')), rand()*1000 from char_hex_test;
INSERT INTO char_hex_test SELECT unhex(replace(uuid(),'-','')), rand()*1000 from char_hex_test;
INSERT INTO `char_hex_test` VALUES("ÈCY e1í´\0\'_÷M",539);
CREATE TABLE t (qty INT, price INT);
INSERT INTO t VALUES(3, 50);
CREATE VIEW v AS SELECT qty, price, qty*price AS value FROM t;
CREATE TABLE t_w_g ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `val` VARCHAR(30) NOT NULL, `short_val` VARCHAR(10) GENERATED ALWAYS AS (left(val,10)) STORED NOT NULL, PRIMARY KEY (`id`) );
INSERT into t_w_g (val) values ("asdfljlkjgsadklfjewtjgasd");
CREATE TABLE `table_varchar_pk` (
`id` char(5) NOT NULL COLLATE latin1_bin,
`val` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `table_varchar_pk` VALUES(binary "csdfg",3);
INSERT INTO `table_varchar_pk` VALUES("bsdfg",3);
INSERT INTO `table_varchar_pk` VALUES("asdfg",3);
INSERT INTO `table_varchar_pk` VALUES("Öasdf",3);
INSERT INTO `table_varchar_pk` VALUES(binary "Asdfg",3);
INSERT INTO `table_varchar_pk` VALUES("Btdfg",3);
INSERT INTO `table_varchar_pk` VALUES("Csdfg",3);
-- Tables with special characters
CREATE table IF NOT EXISTS `mydumper_aipk_uuid_%` (id int primary key auto_increment, val varchar(36));
INSERT into `mydumper_aipk_uuid_%` (val) values (uuid());
CREATE table IF NOT EXISTS `mydumper_aipk_uuid_*` (id int primary key auto_increment, val varchar(36));
INSERT into `mydumper_aipk_uuid_*` (val) values (uuid());
CREATE table IF NOT EXISTS `mydumper_aipk_uuid_?` (id int primary key auto_increment, val varchar(36));
INSERT into `mydumper_aipk_uuid_?` (val) values (uuid());
CREATE table IF NOT EXISTS `mydumper_aipk_uuid_.` (id int primary key auto_increment, val varchar(36));
INSERT into `mydumper_aipk_uuid_.` (val) values (uuid());
CREATE table IF NOT EXISTS `mydumper.aipk_uuid` (id int primary key auto_increment, val varchar(36));
INSERT into `mydumper.aipk_uuid` (val) values (uuid());
CREATE table IF NOT EXISTS `mydumper/aipk_uuid` (id int primary key auto_increment, val varchar(36));
INSERT into `mydumper/aipk_uuid` (val) values (uuid());
DROP TABLE IF EXISTS `perftest`;
CREATE TABLE `perftest` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`val` varchar(108) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `val` (`val`(2)),
KEY `val_2` (`val`(4)),
KEY `val_3` (`val`(8))
) ENGINE=InnoDB;
DROP TABLE IF EXISTS `perftest_2`;
CREATE TABLE `perftest_2` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`val` varchar(108) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `val` (`val`(2)),
KEY `val_2` (`val`(4)),
KEY `val_3` (`val`(8))
) ENGINE=InnoDB;
DROP TABLE IF EXISTS `pertest_child`;
CREATE TABLE `pertest_child` (
`id` int NOT NULL,
PRIMARY KEY (`id`),
CONSTRAINT `pertest_child_ibfk_1` FOREIGN KEY (`id`) REFERENCES `perftest` (`id`)
) ENGINE=InnoDB;
DROP TABLE IF EXISTS `table_view_1_a`;
CREATE TABLE `table_view_1_a` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`val` varchar(108) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `val_1` (`val`(8))
) ENGINE=InnoDB;
DROP TABLE IF EXISTS `table_view_1_b`;
CREATE VIEW `table_view_1_b` AS SELECT
1 AS `id`,
1 AS `val` FROM table_view_1_a LIMIT 1;
DROP TABLE IF EXISTS `table_view_2_b`;
CREATE TABLE `table_view_2_b` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`val` varchar(108) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `val_1` (`val`(8))
) ENGINE=InnoDB;
DROP TABLE IF EXISTS `table_view_2_a`;
CREATE VIEW `table_view_2_a` AS SELECT
1 AS `id`,
1 AS `val` FROM table_view_2_b LIMIT 1;
-- Store procedures and functions
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` TRIGGER perftest_trigger AFTER UPDATE ON perftest FOR EACH ROW INSERT INTO perftest_2 VALUES (old.id,old.val) ;;
DELIMITER ;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` FUNCTION `count_perftest_higher_than`(
val INT
) RETURNS INT
DETERMINISTIC
BEGIN
DECLARE my_count INT;
SELECT count(*) into my_count FROM perftest where id > val;
RETURN (my_count);
END ;;
DELIMITER ;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `get_all_perftest`()
BEGIN
SELECT * FROM perftest;
END ;;
DELIMITER ;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `get_all_table_view_function`()
BEGIN
SELECT * FROM table_view_function;
END ;;
DELIMITER ;
DROP TABLE IF EXISTS `table_view_function`;
CREATE VIEW `table_view_function` AS SELECT
1 AS `id`,
1 AS `val`,
count_perftest_higher_than(2) FROM table_view_2_b LIMIT 1;