eUwd12Ub

1# Helper functions and procedures for idempotent schema migrations
2# Provides if (not) exists functionality for creating and dropping indices and columns
3
4DELIMITER //
5DROP FUNCTION IF EXISTS m_index_exists //
6CREATE FUNCTION m_index_exists(
7  f_table_name varchar(64),
8  f_index_name varchar(64)
9)
10RETURNS BOOL
11BEGIN
12  DECLARE m_index_exists BOOL DEFAULT FALSE;
13  SELECT EXISTS (
14      SELECT 1
15      FROM information_schema.statistics
16      WHERE table_schema = SCHEMA()
17            AND table_name = f_table_name
18            AND index_name = f_index_name
19  ) INTO m_index_exists;
20  RETURN m_index_exists;
21END //
22DELIMITER ;
23
24DELIMITER //
25DROP PROCEDURE IF EXISTS m_drop_index //
26CREATE PROCEDURE m_drop_index (
27  IN p_table_name varchar(64),
28  IN p_index_name varchar(64)
29)
30BEGIN
31  IF m_index_exists(p_table_name, p_index_name)
32  THEN
33    SET @m_drop_index_sql = CONCAT('ALTER TABLE `', SCHEMA(), '`.`', p_table_name, '` DROP INDEX `', p_index_name, '`');
34    PREPARE stmt FROM @m_drop_index_sql;
35    EXECUTE stmt;
36    DEALLOCATE PREPARE stmt;
37    SET @m_drop_index_sql = NULL;
38  END IF;
39END //
40DELIMITER ;
41
42DELIMITER //
43DROP PROCEDURE IF EXISTS m_create_index //
44CREATE PROCEDURE m_create_index (
45  IN p_table_name varchar(64),
46  IN p_index_name varchar(64),
47  IN p_index_columns varchar(512)
48)
49BEGIN
50  IF NOT m_index_exists(p_table_name, p_index_name)
51  THEN
52    SET @m_create_index_sql = CONCAT(
53      'ALTER TABLE `', SCHEMA(), '`.`', p_table_name, '` ADD INDEX `', p_index_name, '` (', p_index_columns, ')'
54    );
55    PREPARE stmt FROM @m_create_index_sql;
56    EXECUTE stmt;
57    DEALLOCATE PREPARE stmt;
58    SET @m_create_index_sql = NULL;
59  END IF;
60END //
61DELIMITER ;
62
63DELIMITER //
64DROP PROCEDURE IF EXISTS m_create_unique_index //
65CREATE PROCEDURE m_create_unique_index (
66  IN p_table_name varchar(64),
67  IN p_index_name varchar(64),
68  IN p_index_columns varchar(512)
69)
70BEGIN
71  IF NOT m_index_exists(p_table_name, p_index_name)
72  THEN
73    SET @m_create_index_sql = CONCAT(
74      'ALTER TABLE `', SCHEMA(), '`.`', p_table_name, '` ADD UNIQUE INDEX `', p_index_name, '` (', p_index_columns, ')'
75    );
76    PREPARE stmt FROM @m_create_index_sql;
77    EXECUTE stmt;
78    DEALLOCATE PREPARE stmt;
79    SET @m_create_index_sql = NULL;
80  END IF;
81END //
82DELIMITER ;
83
84DELIMITER //
85DROP FUNCTION IF EXISTS m_column_exists //
86CREATE FUNCTION m_column_exists(
87  f_table_name varchar(64),
88  f_column_name varchar(64)
89)
90RETURNS BOOL
91BEGIN
92  DECLARE m_column_exists BOOL DEFAULT FALSE;
93  SELECT EXISTS (
94      SELECT 1
95      FROM information_schema.columns
96      WHERE table_schema = SCHEMA()
97            AND table_name = f_table_name
98            AND column_name = f_column_name
99  ) INTO m_column_exists;
100  RETURN m_column_exists;
101END //
102DELIMITER ;
103
104DELIMITER //
105DROP PROCEDURE IF EXISTS m_drop_column //
106CREATE PROCEDURE m_drop_column (
107  IN p_table_name varchar(64),
108  IN p_column_name varchar(64)
109)
110BEGIN
111  IF m_column_exists(p_table_name, p_column_name)
112  THEN
113    SET @m_drop_column_sql = CONCAT('ALTER TABLE `', SCHEMA(), '`.`', p_table_name, '` DROP COLUMN `', p_column_name, '`');
114    PREPARE stmt FROM @m_drop_column_sql;
115    EXECUTE stmt;
116    DEALLOCATE PREPARE stmt;
117    SET @m_drop_column_sql = NULL;
118  END IF;
119END //
120DELIMITER ;
121
122DELIMITER //
123DROP PROCEDURE IF EXISTS m_add_column //
124CREATE PROCEDURE m_add_column (
125  IN p_table_name varchar(64),
126  IN p_column_name varchar(64),
127  IN p_column_definition varchar(64)
128)
129BEGIN
130  IF NOT m_column_exists(p_table_name, p_column_name)
131  THEN
132    SET @m_add_column_sql = CONCAT(
133      'ALTER TABLE `', SCHEMA(), '`.`', p_table_name, '` ADD COLUMN `', p_column_name, '` ', p_column_definition
134    );
135    PREPARE stmt FROM @m_add_column_sql;
136    EXECUTE stmt;
137    DEALLOCATE PREPARE stmt;
138    SET @m_add_column_sql = NULL;
139  END IF;
140END //
141DELIMITER ;