7fBktcrR

1DROP PROCEDURE IF EXISTS create_new_partitions;
2DELIMITER $
3CREATE PROCEDURE create_new_partitions(p_schema varchar(64), p_table varchar(64), p_YEARs_to_add int)
4   LANGUAGE SQL
5   NOT DETERMINISTIC
6   SQL SECURITY INVOKER
7BEGIN  
8   DECLARE done INT DEFAULT FALSE;
9   DECLARE current_partition_name varchar(64);
10   DECLARE current_partition_ts int;
11   DECLARE cur1 CURSOR FOR 
12   SELECT partition_name 
13   FROM information_schema.partitions 
14   WHERE TABLE_SCHEMA = p_schema 
15   AND TABLE_NAME = p_table 
16   AND PARTITION_NAME != 'p_first'
17   AND PARTITION_NAME != 'p_future'
18   AND PARTITION_NAME = @partition_name_to_add;
19   DECLARE cur2 CURSOR FOR 
20   SELECT partition_name, partition_range_ts 
21   FROM partitions_to_add;
22   DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
23   DROP TEMPORARY TABLE IF EXISTS partitions_to_add;
24   CREATE TEMPORARY TABLE partitions_to_add (
25      partition_name varchar(64),
26      partition_range_ts int
27   );
28   SET @partitions_added = FALSE;
29   SET @YEARs_ahead = 0;
30   WHILE @YEARs_ahead <= p_YEARs_to_add DO
31      SET @date = CURDATE();
32      SET @q = 'SELECT DATE_ADD(?, INTERVAL ? YEAR) INTO @YEAR_to_add';
33      PREPARE st FROM @q;
34      EXECUTE st USING @date, @YEARs_ahead;
35      DEALLOCATE PREPARE st;
36      SET @YEARs_ahead = @YEARs_ahead + 1;
37      SET @q = 'SELECT DATE_FORMAT(@YEAR_to_add, ''%Y'') INTO @formatted_YEAR_to_add';
38      PREPARE st FROM @q;
39      EXECUTE st;
40      DEALLOCATE PREPARE st;
41      SET @q = 'SELECT CONCAT(''p'', @formatted_YEAR_to_add) INTO @partition_name_to_add';
42      PREPARE st FROM @q;
43      EXECUTE st;
44      DEALLOCATE PREPARE st;
45      SET done = FALSE; 
46      SET @first = TRUE;
47      OPEN cur1;
48      read_loop: LOOP
49         FETCH cur1 INTO current_partition_name;
50         IF done AND @first THEN
51            SELECT CONCAT('Creating partition: ', @partition_name_to_add);
52            SET @q = 'SELECT DATE_FORMAT(@YEAR_to_add, ''%Y-01-01 00:00:00'') INTO @YEAR_to_add';
53            PREPARE st FROM @q;
54            EXECUTE st;
55            DEALLOCATE PREPARE st; 
56            SET @q = 'SELECT DATE_ADD(?, INTERVAL 1 YEAR) INTO @partition_end_date';
57            PREPARE st FROM @q;
58            EXECUTE st USING @YEAR_to_add;
59            DEALLOCATE PREPARE st;
60            SELECT YEAR(@partition_end_date) INTO @partition_end_ts;
61            INSERT INTO partitions_to_add VALUES (@partition_name_to_add, @partition_end_ts);
62            SET @partitions_added = TRUE;
63         END IF;
64         IF ! @first THEN
65            LEAVE read_loop;
66         END IF;
67         SET @first = FALSE;
68      END LOOP;
69     CLOSE cur1;
70   END WHILE;
71   IF @partitions_added THEN
72      SET @schema = p_schema;
73      SET @table = p_table;
74      SET @q = 'SELECT CONCAT(''ALTER TABLE '', @schema, ''.'', @table, '' REORGANIZE PARTITION p_future INTO ( '') INTO @query';
75      PREPARE st FROM @q;
76      EXECUTE st;
77      DEALLOCATE PREPARE st;
78      SET done = FALSE;
79      SET @first = TRUE;
80      OPEN cur2;
81      read_loop: LOOP
82         FETCH cur2 INTO current_partition_name, current_partition_ts;
83        IF done THEN
84            LEAVE read_loop;
85         END IF;
86         IF ! @first THEN
87            SET @q = 'SELECT CONCAT(@query, '', '') INTO @query';
88            PREPARE st FROM @q;
89            EXECUTE st;
90            DEALLOCATE PREPARE st;
91         END IF;
92         SET @partition_name =  current_partition_name;
93         SET @partition_ts =  current_partition_ts;         
94         SET @q = 'SELECT CONCAT(@query, ''PARTITION '', @partition_name, '' VALUES LESS THAN ('', @partition_ts, '')'') INTO @query';
95         PREPARE st FROM @q;
96         EXECUTE st;
97         DEALLOCATE PREPARE st;
98         SET @first = FALSE;
99      END LOOP;
100      CLOSE cur2;
101      SET @q = 'SELECT CONCAT(@query, '', PARTITION p_future VALUES LESS THAN (MAXVALUE))'') INTO @query';
102      PREPARE st FROM @q;
103      EXECUTE st;
104      DEALLOCATE PREPARE st;
105      PREPARE st FROM @query;
106      EXECUTE st;
107      DEALLOCATE PREPARE st;  
108   END IF;
109   DROP TEMPORARY TABLE partitions_to_add;
110END$
111DELIMITER ;