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
|
ALTER TABLE icinga_command_var
ADD COLUMN checksum VARBINARY(20) DEFAULT NULL AFTER command_id,
ADD INDEX search_idx (varname),
ADD INDEX checksum (checksum);
ALTER TABLE icinga_host_var
ADD COLUMN checksum VARBINARY(20) DEFAULT NULL AFTER host_id,
ADD INDEX checksum (checksum);
ALTER TABLE icinga_notification_var
ADD COLUMN checksum VARBINARY(20) DEFAULT NULL AFTER notification_id,
ADD INDEX checksum (checksum);
ALTER TABLE icinga_service_set_var
ADD COLUMN checksum VARBINARY(20) DEFAULT NULL AFTER service_set_id,
ADD INDEX search_idx (varname),
ADD INDEX checksum (checksum);
ALTER TABLE icinga_service_var
ADD COLUMN checksum VARBINARY(20) DEFAULT NULL AFTER service_id,
ADD INDEX checksum (checksum);
ALTER TABLE icinga_user_var
ADD COLUMN checksum VARBINARY(20) DEFAULT NULL AFTER user_id,
ADD INDEX checksum (checksum);
CREATE TABLE icinga_var (
checksum VARBINARY(20) NOT NULL,
rendered_checksum VARBINARY(20) NOT NULL,
varname VARCHAR(255) NOT NULL COLLATE utf8_bin,
varvalue TEXT NOT NULL,
rendered TEXT NOT NULL,
PRIMARY KEY (checksum),
INDEX search_idx (varname)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE icinga_flat_var (
var_checksum VARBINARY(20) NOT NULL,
flatname_checksum VARBINARY(20) NOT NULL,
flatname VARCHAR(512) NOT NULL COLLATE utf8_bin,
flatvalue TEXT NOT NULL,
PRIMARY KEY (var_checksum, flatname_checksum),
INDEX search_varname (flatname (191)),
INDEX search_varvalue (flatvalue (128)),
CONSTRAINT flat_var_var
FOREIGN KEY checksum (var_checksum)
REFERENCES icinga_var (checksum)
ON DELETE CASCADE
ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE icinga_command_resolved_var (
command_id INT(10) UNSIGNED NOT NULL,
varname VARCHAR(255) NOT NULL COLLATE utf8_bin,
checksum VARBINARY(20) NOT NULL,
PRIMARY KEY (command_id, checksum),
INDEX search_varname (varname),
CONSTRAINT command_resolved_var_command
FOREIGN KEY command (command_id)
REFERENCES icinga_command (id)
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT command_resolved_var_checksum
FOREIGN KEY checksum (checksum)
REFERENCES icinga_var (checksum)
ON DELETE RESTRICT
ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE icinga_host_resolved_var (
host_id INT(10) UNSIGNED NOT NULL,
varname VARCHAR(255) NOT NULL COLLATE utf8_bin,
checksum VARBINARY(20) NOT NULL,
PRIMARY KEY (host_id, checksum),
INDEX search_varname (varname),
FOREIGN KEY host_resolved_var_host (host_id)
REFERENCES icinga_host (id)
ON DELETE CASCADE
ON UPDATE CASCADE,
FOREIGN KEY host_resolved_var_checksum (checksum)
REFERENCES icinga_var (checksum)
ON DELETE RESTRICT
ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE icinga_notification_resolved_var (
notification_id INT(10) UNSIGNED NOT NULL,
varname VARCHAR(255) NOT NULL COLLATE utf8_bin,
checksum VARBINARY(20) NOT NULL,
PRIMARY KEY (notification_id, checksum),
INDEX search_varname (varname),
FOREIGN KEY notification_resolved_var_notification (notification_id)
REFERENCES icinga_notification (id)
ON DELETE CASCADE
ON UPDATE CASCADE,
FOREIGN KEY notification_resolved_var_checksum (checksum)
REFERENCES icinga_var (checksum)
ON DELETE RESTRICT
ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE icinga_service_set_resolved_var (
service_set_id INT(10) UNSIGNED NOT NULL,
varname VARCHAR(255) NOT NULL COLLATE utf8_bin,
checksum VARBINARY(20) NOT NULL,
PRIMARY KEY (service_set_id, checksum),
INDEX search_varname (varname),
FOREIGN KEY service_set_resolved_var_service_set (service_set_id)
REFERENCES icinga_service_set (id)
ON DELETE CASCADE
ON UPDATE CASCADE,
FOREIGN KEY service_set_resolved_var_checksum(checksum)
REFERENCES icinga_var (checksum)
ON DELETE RESTRICT
ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE icinga_service_resolved_var (
service_id INT(10) UNSIGNED NOT NULL,
varname VARCHAR(255) NOT NULL COLLATE utf8_bin,
checksum VARBINARY(20) NOT NULL,
PRIMARY KEY (service_id, checksum),
INDEX search_varname (varname),
FOREIGN KEY service_resolve_var_service (service_id)
REFERENCES icinga_service (id)
ON DELETE CASCADE
ON UPDATE CASCADE,
FOREIGN KEY service_resolve_var_checksum(checksum)
REFERENCES icinga_var (checksum)
ON DELETE RESTRICT
ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE icinga_user_resolved_var (
user_id INT(10) UNSIGNED NOT NULL,
varname VARCHAR(255) NOT NULL COLLATE utf8_bin,
checksum VARBINARY(20) NOT NULL,
PRIMARY KEY (user_id, checksum),
INDEX search_varname (varname),
FOREIGN KEY user_resolve_var_user (user_id)
REFERENCES icinga_user (id)
ON DELETE CASCADE
ON UPDATE CASCADE,
FOREIGN KEY user_resolve_var_checksum(checksum)
REFERENCES icinga_var (checksum)
ON DELETE RESTRICT
ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO director_schema_migration
(schema_version, migration_time)
VALUES (127, NOW());
|
