hMfQcE5Q

1DROP PROCEDURE IF EXISTS create_dbs;
2
3DELIMITER $$
4
5CREATE PROCEDURE create_dbs (
6    IN in_db_prefix VARCHAR(12),
7    IN in_schemas INT,
8    IN in_tables INT,
9    IN in_columns INT,
10    IN in_indexes INT,
11    IN in_engine VARCHAR(10)
12)
13BEGIN
14    DECLARE dbs_created INT DEFAULT 0;
15    DECLARE tbls_created INT DEFAULT 0;
16    DECLARE cols_created INT DEFAULT 0;
17    DECLARE idxs_created INT DEFAULT 0;
18
19    DECLARE cols TEXT;
20
21    createDatabasesLoop: LOOP
22
23        SET dbs_created = dbs_created + 1;
24        SET @sql = CONCAT('CREATE DATABASE IF NOT EXISTS ', in_db_prefix, '__', dbs_created);
25
26        -- SELECT @sql;
27
28        PREPARE create_db FROM @sql;
29        EXECUTE create_db;
30        DEALLOCATE PREPARE create_db;
31
32        SET tbls_created = 0;
33
34        createTablesLoop: LOOP
35
36            SET tbls_created = tbls_created + 1;
37
38            SET @sql = CONCAT('CREATE TABLE IF NOT EXISTS ', in_db_prefix, '__', dbs_created, '.tbl_', tbls_created, ' (');
39
40            SET cols_created = 0;
41            SET cols = '';
42
43            createColsLoop: LOOP
44
45                SET cols_created = cols_created + 1;
46
47                IF cols_created >= in_columns THEN
48                    LEAVE createColsLoop;
49                END IF;
50
51                SET cols = CONCAT(cols, CONCAT('col_', cols_created, IF(cols_created = 1, ' INT AUTO_INCREMENT, ', ' INT, ')));
52
53            END LOOP createColsLoop;
54
55            SET @sql = CONCAT(@sql, cols);
56            SET @sql = CONCAT(@sql, 'PRIMARY KEY (col_1)');
57            SET @sql = CONCAT(@sql, ') ENGINE = ', in_engine);
58
59            -- SELECT @sql;
60
61            PREPARE create_tbl FROM @sql;
62            EXECUTE create_tbl;
63            DEALLOCATE PREPARE create_tbl;
64
65            SET idxs_created = 0;
66
67            createIndexesLoop: LOOP
68
69                SET idxs_created = idxs_created + 1;
70
71                SET @sql = CONCAT('ALTER TABLE ', in_db_prefix, '__', dbs_created, '.tbl_', tbls_created, 
72                    ' ADD INDEX tbl_', tbls_created, '_idx_', idxs_created, ' (col_', idxs_created + 1, ')');
73
74                -- SELECT @sql;
75
76                PREPARE create_idx FROM @sql;
77                EXECUTE create_idx;
78                DEALLOCATE PREPARE create_idx;
79
80                IF idxs_created >= in_indexes THEN
81                    LEAVE createIndexesLoop;
82                END IF;
83
84            END LOOP createIndexesLoop;
85
86            SET @sql = CONCAT('ANALYZE TABLE ', in_db_prefix, '__', dbs_created, '.tbl_', tbls_created);
87
88            PREPARE analyze_tbl FROM @sql;
89            EXECUTE analyze_tbl;
90            DEALLOCATE PREPARE analyze_tbl;
91
92            IF tbls_created >= in_tables THEN
93                LEAVE createTablesLoop;
94            END IF;
95
96        END LOOP createTablesLoop;
97
98        IF dbs_created >= in_schemas THEN
99            LEAVE createDatabasesLoop;
100        END IF;
101
102    END LOOP createDatabasesLoop;
103
104END$$
105
106DELIMITER ;
107
108DROP PROCEDURE IF EXISTS drop_db_prefix;
109
110DELIMITER $$
111
112CREATE PROCEDURE drop_db_prefix (IN db_search_pattern VARCHAR(64))
113BEGIN
114
115    DECLARE done INT DEFAULT FALSE;
116    DECLARE db_name VARCHAR(64);
117    DECLARE start_time DATETIME;
118    DECLARE end_time DATETIME;
119    DECLARE dbs_dropped INT DEFAULT 0;
120    DECLARE dbs CURSOR FOR 
121     SELECT SCHEMA_NAME 
122       FROM INFORMATION_SCHEMA.SCHEMATA
123      WHERE SCHEMA_NAME = db_search_pattern
124         OR SCHEMA_NAME LIKE CONCAT(db_search_pattern, '\_\_%');
125    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
126    
127    OPEN dbs;
128    
129    SET start_time = NOW(6);
130
131    read_loop: LOOP
132
133      FETCH dbs INTO db_name;
134
135      IF done THEN
136        LEAVE read_loop;
137      END IF;
138
139      SET @dropsql = CONCAT('DROP DATABASE `', db_name, '`');
140      PREPARE drop_stmt FROM @dropsql;
141      EXECUTE drop_stmt;  
142      DEALLOCATE PREPARE drop_stmt;
143
144      SET dbs_dropped = dbs_dropped + 1;
145
146    END LOOP;
147
148    SET end_time = NOW(6);
149
150    CLOSE dbs;
151
152    IF dbs_dropped THEN
153      SELECT TIMEDIFF(end_time, start_time) AS total_time,
154             dbs_dropped;
155    END IF;
156
157END$$
158
159DELIMITER ;
160
161CALL is_test.create_dbs('InnoDB_1m', 10000, 100, 10, 2, 'InnoDB');
162
163call is_test.drop_db_prefix('InnoDB_1m');