forked from voipmonitor/sniffer
-
Notifications
You must be signed in to change notification settings - Fork 0
/
cdrtable.sql.history
205 lines (163 loc) · 8.99 KB
/
cdrtable.sql.history
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
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
PLEASE NOTE THAT SINCE 5 SQL UPGRADES AUTOMATICALLY. THIS IS ONLY FOR REFERENCE!
5.1.2013 version 6.0
--------------------
CREATE TABLE `message` (
`ID` int(32) unsigned NOT NULL AUTO_INCREMENT,
`calldate` datetime NOT NULL,
`caller` varchar(255) DEFAULT NULL,
`caller_domain` varchar(255) DEFAULT NULL,
`caller_reverse` varchar(255) DEFAULT NULL,
`callername` varchar(255) DEFAULT NULL,
`callername_reverse` varchar(255) DEFAULT NULL,
`called` varchar(255) DEFAULT NULL,
`called_domain` varchar(255) DEFAULT NULL,
`called_reverse` varchar(255) DEFAULT NULL,
`sipcallerip` int(10) unsigned DEFAULT NULL,
`sipcalledip` int(10) unsigned DEFAULT NULL,
`bye` tinyint(3) unsigned DEFAULT NULL,
`lastSIPresponse_id` smallint(5) unsigned DEFAULT NULL,
`lastSIPresponseNum` smallint(5) unsigned DEFAULT NULL,
`sighup` tinyint(4) DEFAULT NULL,
`id_sensor` smallint(10) unsigned DEFAULT NULL,
`a_ua_id` int(10) unsigned DEFAULT NULL,
`b_ua_id` int(10) unsigned DEFAULT NULL,
`fbasename` varchar(255) DEFAULT NULL,
`message` text,
PRIMARY KEY (`ID`),
KEY `calldate` (`calldate`),
KEY `source` (`caller`),
KEY `source_reverse` (`caller_reverse`),
KEY `destination` (`called`),
KEY `destination_reverse` (`called_reverse`),
KEY `callername` (`callername`),
KEY `callername_reverse` (`callername_reverse`),
KEY `sipcallerip` (`sipcallerip`),
KEY `sipcalledip` (`sipcalledip`),
KEY `lastSIPresponseNum` (`lastSIPresponseNum`),
KEY `bye` (`bye`),
KEY `lastSIPresponse_id` (`lastSIPresponse_id`),
KEY `id_sensor` (`id_sensor`),
KEY `a_ua_id` (`a_ua_id`),
KEY `b_ua_id` (`b_ua_id`),
KEY `fbasename` (`fbasename`),
CONSTRAINT `messages_ibfk_1` FOREIGN KEY (`lastSIPresponse_id`) REFERENCES `cdr_sip_response` (`id`) ON UPDATE CASCADE,
CONSTRAINT `messages_ibfk_2` FOREIGN KEY (`a_ua_id`) REFERENCES `cdr_ua` (`id`) ON UPDATE CASCADE,
CONSTRAINT `messages_ibfk_3` FOREIGN KEY (`b_ua_id`) REFERENCES `cdr_ua` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED
15.12.2012 version 5.5 (upgrades are automatic)
----------------------------------------------
ALTER TABLE register_state ADD `sipcalledip` int(32) unsigned, ADD KEY `sipcalledip` (`sipcalledip`);
ALTER TABLE register_failed ADD `sipcalledip` int(32) unsigned, ADD KEY `sipcalledip` (`sipcalledip`);
30.11.2012 version 5.4
----------------------
ALTER TABLE register ADD KEY `to_domain` (`to_domain`), ADD KEY `to_num` (`to_num`);
ALTER TABLE register_state ADD `to_domain` varchar(255) NULL DEFAULT NULL;
ALTER TABLE register_failed ADD `to_domain` varchar(255) NULL DEFAULT NULL;
2.11.2012 version 5.3
----------------------
ALTER TABLE cdr_next ADD match_header VARCHAR(128), ADD KEY `match_header` (`match_header`);
27.10.2012 version 5.2
---------------------
since version 5.2 the upgrade is performed automatically on voipmonitor start
CREATE TABLE IF NOT EXISTS `sensors` (
`id_sensor` int(32) unsigned NOT NULL,
`host` varchar(255) NULL DEFAULT NULL,
`port` int(8) NULL DEFAULT NULL,
PRIMARY KEY (`id_sensor`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
19.10.2012 version 5.1
----------------------
ALTER TABLE filter_telnum CHANGE prefix prefix varchar(32);
DROP TABLE `register`;
CREATE TABLE `register` ( `ID` int(32) unsigned NOT NULL AUTO_INCREMENT, `calldate` datetime NOT NULL, `sipcallerip` int(32) unsigned NOT NULL,
`sipcalledip` int(32) unsigned NOT NULL,
`from_num` varchar(255) NULL DEFAULT NULL,
`from_name` varchar(255) NULL DEFAULT NULL,
`from_domain` varchar(255) NULL DEFAULT NULL,
`to_num` varchar(255) NULL DEFAULT NULL,
`to_domain` varchar(255) NULL DEFAULT NULL,
`contact_num` varchar(255) NULL DEFAULT NULL,
`contact_domain` varchar(255) NULL DEFAULT NULL,
`digestusername` varchar(255) NULL DEFAULT NULL,
`digestrealm` varchar(255) NULL DEFAULT NULL,
`expires` mediumint NULL DEFAULT NULL,
`expires_at` datetime NULL DEFAULT NULL,
`state` tinyint unsigned NULL DEFAULT NULL,
`ua_id` int(10) unsigned DEFAULT NULL,
PRIMARY KEY (`ID`),
KEY `calldate` (`calldate`), KEY `sipcallerip` (`sipcallerip`),
KEY `sipcalledip` (`sipcalledip`),
KEY `from_num` (`from_num`),
KEY `digestusername` (`digestusername`)
) ENGINE=MEMORY DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED;
CREATE TABLE `register_state` (
`ID` int(32) unsigned NOT NULL AUTO_INCREMENT,
`created_at` datetime NOT NULL,
`sipcallerip` int(32) unsigned NOT NULL,
`from_num` varchar(255) NULL DEFAULT NULL,
`to_num` varchar(255) NULL DEFAULT NULL,
`contact_num` varchar(255) NULL DEFAULT NULL,
`contact_domain` varchar(255) NULL DEFAULT NULL,
`digestusername` varchar(255) NULL DEFAULT NULL,
`expires` mediumint NULL DEFAULT NULL,
`state` tinyint unsigned NULL DEFAULT NULL,
`ua_id` int(10) unsigned DEFAULT NULL, PRIMARY KEY (`ID`),
KEY `created_at` (`created_at`),
KEY `sipcallerip` (`sipcallerip`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED;
CREATE TABLE `register_failed` (
`ID` int(32) unsigned NOT NULL AUTO_INCREMENT,
`counter` int DEFAULT 0,
`created_at` datetime NOT NULL,
`sipcallerip` int(32) unsigned NOT NULL,
`from_num` varchar(255) NULL DEFAULT NULL,
`to_num` varchar(255) NULL DEFAULT NULL,
`contact_num` varchar(255) NULL DEFAULT NULL,
`contact_domain` varchar(255) NULL DEFAULT NULL,
`digestusername` varchar(255) NULL DEFAULT NULL,
`ua_id` int(10) unsigned DEFAULT NULL,
PRIMARY KEY (`ID`),
KEY `created_at` (`created_at`),
KEY `sipcallerip` (`sipcallerip`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED;
25.9.2012 version 5.0
--------------------
for upgrading from 4.2 -> 5 please create new database with new cdrtable.sql and use scripts/mysql_copy_4.2to5.0.php (edit database names in scripts)
- altering tables involves several alter table commands which makes internal temp table several times and for large CDR tables it is too long process locking cdr table for hours
- converting 12 milions cdr takes 24 hour on magnetic disk and older xeon and 2 hours on SSD disk on newer xeons.
4.9.2012 version 4.2
--------------------
ALTER TABLE cdr DROP KEY a_lossr_f1, DROP KEY a_lossr_f2, DROP KEY a_lossr_adapt, DROP KEY a_burstr_f1, DROP KEY a_burstr_f2, DROP KEY a_burstr_adapt, DROP KEY b_lossr_f1, DROP KEY b_lossr_f2, DROP KEY b_lossr_adapt, DROP KEY b_burstr_f1, DROP KEY b_burstr_f2, DROP KEY b_burstr_adapt;
ALTER TABLE cdr ADD KEY `a_mos_f1` (`a_mos_f1`), ADD KEY `a_mos_f2` (`a_mos_f2`), ADD KEY `a_mos_adapt` (`a_mos_adapt`), ADD KEY `b_mos_f1` (`b_mos_f1`), ADD KEY `b_mos_f2` (`b_mos_f2`), ADD KEY `b_mos_adapt` (`b_mos_adapt`);
ALTER TABLE cdr ADD a_rtcp_loss int(8) DEFAULT NULL AFTER b_burstr_adapt, ADD a_rtcp_maxfr int(8) DEFAULT NULL, ADD a_rtcp_avgfr float(8) DEFAULT NULL, ADD a_rtcp_maxjitter int(8) DEFAULT NULL, ADD a_rtcp_avgjitter float(8) DEFAULT NULL, ADD b_rtcp_loss int(8) DEFAULT NULL AFTER b_burstr_adapt, ADD b_rtcp_maxfr int(8) DEFAULT NULL, ADD b_rtcp_avgfr float(8) DEFAULT NULL, ADD b_rtcp_maxjitter int(8) DEFAULT NULL, ADD b_rtcp_avgjitter float(8) DEFAULT NULL, ADD KEY `a_rtcp_loss` (`a_rtcp_loss`), ADD KEY `a_rtcp_maxfr` (`a_rtcp_maxfr`), ADD KEY `a_rtcp_avgfr` (`a_rtcp_avgfr`), ADD KEY `a_rtcp_maxjitter` (`a_rtcp_maxjitter`), ADD KEY `a_rtcp_avgjitter` (`a_rtcp_avgjitter`), ADD KEY `b_rtcp_loss` (`b_rtcp_loss`), ADD KEY `b_rtcp_maxfr` (`b_rtcp_maxfr`), ADD KEY `b_rtcp_avgfr` (`b_rtcp_avgfr`), ADD KEY `b_rtcp_maxjitter` (`b_rtcp_maxjitter`), ADD KEY `b_rtcp_avgjitter` (`b_rtcp_avgjitter`);
1.9.2012
--------
ALTER TABLE cdr ADD `callend` datetime DEFAULT NULL AFTER calldate, add KEY `callend` (`callend`);
UPDATE cdr SET callend = DATE_ADD(calldate, INTERVAL duration SECOND);
1.7.2012
--------
ALTER TABLE filter_telnum CHANGE prefix prefix BIGINT(32);
28.6.2012 - version 4.1
-----------------------
ALTER TABLE cdr ADD caller_domain VARCHAR(255) DEFAULT NULL AFTER caller, ADD called_domain VARCHAR(255) DEFAULT NULL AFTER called, ADD KEY `caller_domain` (`caller_domain`), add KEY `called_domain` (`called_domain`);
2.5.2012
--------
ALTER TABLE filter_ip ADD `direction` tinyint(1) DEFAULT 0;
ALTER TABLE filter_telnum ADD `direction` tinyint(1) DEFAULT 0;
14.4.2012
---------
ALTER TABLE cdr ADD `custom_header1` varchar(32) DEFAULT NULL AFTER sipcalledip, add KEY `custom_header1` (`custom_header1`);
2.2.2012
--------
ALTER TABLE cdr ADD caller_reverse VARCHAR(255) DEFAULT NULL AFTER caller, ADD called_reverse VARCHAR(255) DEFAULT NULL AFTER called, ADD callername_reverse VARCHAR(255) DEFAULT NULL AFTER callername, add KEY `callername` (`callername`), add KEY `callername_reverse` (`callername`), add KEY `caller_reverse` (`caller_reverse`), add KEY `called_reverse` (`caller_reverse`);
DROP TRIGGER cdr_bi;
DELIMITER |
CREATE TRIGGER cdr_bi BEFORE INSERT ON cdr
FOR EACH ROW
BEGIN
SET NEW.caller_reverse = REVERSE(NEW.caller);
SET NEW.called_reverse = REVERSE(NEW.called);
SET NEW.callername_reverse = REVERSE(NEW.callername);
END |
DELIMITER ; |
UPDATE cdr SET caller_reverse = REVERSE(caller) , called_reverse = REVERSE(called), callername_reverse = REVERSE(callername) ;