vigiboard / bdd.sql @ ed374445
History | View | Annotate | Download (4.62 KB)
1 | 49c8da1d | Thomas ANDREJAK | REATE TABLE IF NOT EXISTS `graph` ( |
---|---|---|---|
2 | `name` varchar(100) NOT NULL, |
||
3 | `template` varchar(2500) NOT NULL, |
||
4 | `vlabel` varchar(2500) NOT NULL, |
||
5 | PRIMARY KEY (`name`) |
||
6 | ) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
||
7 | |||
8 | |||
9 | |||
10 | |||
11 | |||
12 | CREATE TABLE IF NOT EXISTS `graphgroups` ( |
||
13 | `graphname` varchar(100) NOT NULL, |
||
14 | `idgraphgroup` int(10) unsigned NOT NULL, |
||
15 | `parent` int(10) unsigned NOT NULL, |
||
16 | PRIMARY KEY (`graphname`,`idgraphgroup`), |
||
17 | FOREIGN KEY (graphname) REFERENCES graph(name) |
||
18 | ) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
||
19 | |||
20 | |||
21 | |||
22 | |||
23 | |||
24 | |||
25 | CREATE TABLE IF NOT EXISTS `host` ( |
||
26 | `name` varchar(255) NOT NULL, |
||
27 | `checkhostcmd` varchar(255) NOT NULL, |
||
28 | `community` varchar(255) NOT NULL, |
||
29 | `fqhn` varchar(255) NOT NULL, |
||
30 | `hosttpl` varchar(255) NOT NULL, |
||
31 | `mainip` varchar(255) NOT NULL, |
||
32 | `port` int(10) unsigned NOT NULL, |
||
33 | `snmpoidsperpdu` int(10) unsigned DEFAULT NULL, |
||
34 | `snmpversion` varchar(255) DEFAULT NULL, |
||
35 | PRIMARY KEY (`name`) |
||
36 | ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ; |
||
37 | |||
38 | |||
39 | |||
40 | |||
41 | |||
42 | CREATE TABLE IF NOT EXISTS `service` ( |
||
43 | `name` varchar(255) NOT NULL, |
||
44 | `type` varchar(255) NOT NULL, |
||
45 | `command` varchar(255) NOT NULL, |
||
46 | PRIMARY KEY (`name`) |
||
47 | ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ; |
||
48 | |||
49 | |||
50 | |||
51 | |||
52 | |||
53 | CREATE TABLE IF NOT EXISTS `events` ( |
||
54 | `idevent` int(10) unsigned NOT NULL AUTO_INCREMENT, |
||
55 | `hostname` varchar(100) NOT NULL, |
||
56 | `servicename` varchar(100) DEFAULT NULL, |
||
57 | `service_source` varchar(100) NOT NULL, |
||
58 | `severity` int(10) unsigned NOT NULL, |
||
59 | `status` enum( 'None', 'Acknowledged', 'Closed' ) NOT NULL DEFAULT 'None', |
||
60 | `active` bool DEFAULT TRUE, |
||
61 | `timestamp` datetime DEFAULT NULL, |
||
62 | `output` text NOT NULL, |
||
63 | `event_timestamp` datetime DEFAULT NULL, |
||
64 | `last_check` datetime DEFAULT NULL, |
||
65 | `recover_output` text, |
||
66 | `timestamp_active` datetime DEFAULT NULL, |
||
67 | `timestamp_cleared` datetime DEFAULT NULL, |
||
68 | `trouble_ticket` varchar(20) DEFAULT NULL, |
||
69 | `occurence` int(10) unsigned DEFAULT NULL, |
||
70 | PRIMARY KEY (`idevent`), |
||
71 | FOREIGN KEY (`servicename`) REFERENCES service(name), |
||
72 | FOREIGN KEY (`hostname`) REFERENCES host(name), |
||
73 | INDEX (`hostname`,`servicename`) |
||
74 | ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ; |
||
75 | |||
76 | |||
77 | |||
78 | |||
79 | CREATE TABLE IF NOT EXISTS `event_history` ( |
||
80 | `idhistory` int(10) unsigned NOT NULL AUTO_INCREMENT, |
||
81 | `type_action` varchar(50) NOT NULL, |
||
82 | `idevent` int(10) unsigned NOT NULL, |
||
83 | `key` varchar(255) DEFAULT NULL, |
||
84 | `value` text, |
||
85 | `timestamp` datetime DEFAULT NULL, |
||
86 | `username` varchar(255) DEFAULT NULL, |
||
87 | PRIMARY KEY (`idhistory`), |
||
88 | INDEX (`idevent`), |
||
89 | FOREIGN KEY ( idevent) REFERENCES events(idevent) |
||
90 | ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ; |
||
91 | |||
92 | |||
93 | |||
94 | |||
95 | |||
96 | |||
97 | CREATE TABLE IF NOT EXISTS `groups` ( |
||
98 | `name` varchar(100) NOT NULL, |
||
99 | `parent` varchar(100) DEFAULT NULL, |
||
100 | PRIMARY KEY (`name`), |
||
101 | FOREIGN KEY (parent) REFERENCES groups(name) |
||
102 | ) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
||
103 | CREATE TABLE IF NOT EXISTS `grouppermissions` ( |
||
104 | `groupname` varchar(100) NOT NULL, |
||
105 | `idpermission` int(10) unsigned NOT NULL, |
||
106 | FOREIGN KEY (groupname) REFERENCES groups(name), |
||
107 | PRIMARY KEY (groupname,idpermission) |
||
108 | ) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
||
109 | |||
110 | |||
111 | |||
112 | |||
113 | |||
114 | |||
115 | CREATE TABLE IF NOT EXISTS `hostgroups` ( |
||
116 | `hostname` varchar(100) NOT NULL, |
||
117 | `groupname` varchar(100) NOT NULL, |
||
118 | PRIMARY KEY (`hostname`,`groupname`), |
||
119 | FOREIGN KEY (hostname) REFERENCES host(name), |
||
120 | FOREIGN KEY (groupname) REFERENCES groups(name) |
||
121 | ) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
||
122 | |||
123 | |||
124 | |||
125 | |||
126 | |||
127 | CREATE TABLE IF NOT EXISTS `servicegroups` ( |
||
128 | `servicename` varchar(100) NOT NULL, |
||
129 | `groupname` varchar(100) NOT NULL, |
||
130 | PRIMARY KEY (`servicename`,`groupname`), |
||
131 | FOREIGN KEY (servicename) REFERENCES service(name), |
||
132 | FOREIGN KEY (groupname) REFERENCES groups(name) |
||
133 | ) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
||
134 | |||
135 | |||
136 | |||
137 | |||
138 | |||
139 | |||
140 | |||
141 | CREATE TABLE IF NOT EXISTS `perfdatasource` ( |
||
142 | `hostname` varchar(100) NOT NULL, |
||
143 | `servicename` varchar(100) NOT NULL, |
||
144 | `graphname` varchar(100) NOT NULL, |
||
145 | `type` varchar(100) NOT NULL, |
||
146 | `label` varchar(255) DEFAULT NULL, |
||
147 | `factor` float NOT NULL, |
||
148 | PRIMARY KEY (`hostname`,`servicename`), |
||
149 | FOREIGN KEY (hostname) REFERENCES host(name), |
||
150 | FOREIGN KEY (servicename) REFERENCES service(name), |
||
151 | FOREIGN KEY (graphname) REFERENCES graph(name) |
||
152 | ) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
||
153 | |||
154 | |||
155 | |||
156 | |||
157 | CREATE TABLE IF NOT EXISTS `servicehautniveau` ( |
||
158 | `servicename` varchar(100) NOT NULL, |
||
159 | `servicename_dep` varchar(100) NOT NULL, |
||
160 | PRIMARY KEY (`servicename`,`servicename_dep`), |
||
161 | FOREIGN KEY (servicename) REFERENCES service(name), |
||
162 | FOREIGN KEY (servicename_dep) REFERENCES service(name) |
||
163 | ) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
||
164 | |||
165 | |||
166 | |||
167 | |||
168 | |||
169 | |||
170 | CREATE TABLE IF NOT EXISTS `servicetopo` ( |
||
171 | `servicename` varchar(100) NOT NULL, |
||
172 | `function` varchar(50) NOT NULL, |
||
173 | PRIMARY KEY (`servicename`), |
||
174 | FOREIGN KEY (servicename) REFERENCES servicehautniveau(servicename) |
||
175 | ) ENGINE=InnoDB DEFAULT CHARSET=utf8; |