This repository has been archived by the owner on Jul 5, 2023. It is now read-only.
-
Notifications
You must be signed in to change notification settings - Fork 119
/
009_add_idempotent_migration_scaffolding.php
101 lines (88 loc) · 3.86 KB
/
009_add_idempotent_migration_scaffolding.php
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
<?php
defined('BASEPATH') OR exit('No direct script access allowed');
class Migration_add_idempotent_migration_scaffolding extends CI_Migration
{
public function up()
{
return $this->create_idempotent_helpers();
}
public function down()
{
return $this->drop_idempotent_helpers();
}
private function drop_idempotent_helpers() {
$this->db->query('DROP FUNCTION IF EXISTS column_exists');
$this->db->query('DROP PROCEDURE IF EXISTS drop_column_if_exists');
$this->db->query('DROP PROCEDURE IF EXISTS add_column_if_not_exists');
return true;
}
/*
* Create functions and stored procedures useful for writing idempotent DDL statements
* in future migrations.
*
* Example usage:
* SELECT column_exists('my_table', 'my_column'); -- 0
* CALL add_column_if_not_exists('my_table', 'my_column', varchar(15)); -- success
* SELECT column_exists('my_table', 'my_column'); -- 1
* CALL add_column_if_not_exists('my_table', 'my_column', varchar(15)); -- success
* SELECT column_exists('my_table', 'my_column'); -- 1
* CALL drop_column_if_exists('my_table', 'my_column'); -- success
* SELECT column_exists('my_table', 'my_column'); -- 0
* CALL drop_column_if_exists('my_table', 'my_column'); -- success
* SELECT column_exists('my_table', 'my_column'); -- 0
*
* Source for this approach: https://stackoverflow.com/a/49676339
*/
private function create_idempotent_helpers() {
// column_exists: test whether a table already has a column
$this->db->query('DROP FUNCTION IF EXISTS column_exists');
$this->db->query('CREATE FUNCTION column_exists(
tname VARCHAR(64),
cname VARCHAR(64)
)
RETURNS BOOLEAN
READS SQL DATA
BEGIN
RETURN 0 < (SELECT COUNT(*)
FROM `INFORMATION_SCHEMA`.`COLUMNS`
WHERE `TABLE_SCHEMA` = SCHEMA()
AND `TABLE_NAME` = tname
AND `COLUMN_NAME` = cname);
END');
// drop_column_if_exists: idempotently drop a column from a table
$this->db->query('DROP PROCEDURE IF EXISTS drop_column_if_exists');
$this->db->query('CREATE PROCEDURE drop_column_if_exists(
tname VARCHAR(64),
cname VARCHAR(64)
)
BEGIN
IF column_exists(tname, cname) THEN
SET @drop_column_if_exists = CONCAT(\'ALTER TABLE `\', tname, \'` DROP COLUMN `\', cname, \'`\');
PREPARE drop_query FROM @drop_column_if_exists;
EXECUTE drop_query;
END IF;
END');
// Similar function for only adding a column if it doesn't already exist
// add_column_if_not_exists: idempotently add a column to a table
$this->db->query('DROP PROCEDURE IF EXISTS add_column_if_not_exists');
$this->db->query('CREATE PROCEDURE add_column_if_not_exists(
tname VARCHAR(64),
cname VARCHAR(64),
cdef VARCHAR(255)
)
BEGIN
IF NOT column_exists(tname, cname) THEN
SET @statement = \'ALTER TABLE `tname` ADD COLUMN `cname` cdef\';
ELSE
-- Make sure the column is defined the way we want
SET @statement = \'ALTER TABLE `tname` CHANGE `cname` `cname` cdef\';
END IF;
SET @statement = REPLACE(@statement, \'tname\', tname);
SET @statement = REPLACE(@statement, \'cname\', cname);
SET @statement = REPLACE(@statement, \'cdef\', cdef);
PREPARE change_query FROM @statement;
EXECUTE change_query;
END');
return true;
}
}