No OneTemporary
Actions

Size

42 KB

Referenced Files

None

Subscribers

None

View Options

	diff --git a/setup/mysql/alter_tables.mysql b/setup/mysql/alter_tables.mysql
	index 8432fb9..8992fa7 100644
	--- a/setup/mysql/alter_tables.mysql
	+++ b/setup/mysql/alter_tables.mysql
	@@ -1,545 +1,549 @@
	update log set reedit = SUBSTRING(reedit,10);
	update log set rerun = SUBSTRING(rerun,10);
	alter table auth_user change column only_customer domainFilter varchar(255) not null;
	alter table auth_user change column only_cscode codeFilter varchar(255) not null;
	alter table auth_user change column only_cli aNumberFilter varchar(255) not null;
	alter table auth_user change column only_after_date afterDateFilter varchar(255) not null;
	alter table auth_user drop column only_cardservice;
	alter table auth_user drop column only_cardclass;

	alter table active_sessions add column count bigint(20) unsigned NOT NULL default '0' after changed;
	alter table radacct change column Rate Rate text not null;

	alter table radacct add column SipMethod varchar(50) not null after H323ConfID;
	alter table radacct add column SipResponseCode smallint unsigned not null after SipMethod;
	alter table radacct add column SipToTag varchar(255) not null after SipResponseCode;
	alter table radacct add column SipFromTag varchar(255) not null after SipToTag;
	alter table radacct add column SipTranslatedRequestURI varchar(255) NOT NULL after SipFromTag;

	INSERT INTO sip_status VALUES (422,'SessionTimerTooSmall','Client-Error');
	INSERT INTO sip_status VALUES (202,'Accepted','Success');
	INSERT INTO sip_status VALUES (489,'UnknownEvent','Client-Error');
	INSERT INTO sip_status VALUES (490,'RequestUpdated','Client-Error');
	INSERT INTO sip_status VALUES (580,'PreconditionFailure','Server-Error');
	INSERT INTO sip_status VALUES (421,'ExtensionRequired','Client-Error');

	# v 1.4.4
	alter table auth_user change column display_card compidFilter varchar(255) not null;
	alter table auth_user add column serviceFilter varchar(255) not null after codeFilter;
	alter table auth_user change column codeFilter cscodeFilter varchar(255) not null;

	# v 1.5.3
	alter table radacct add column RemotePartyId varchar(255) not null ;
	alter table radacct add column BillingPartyId varchar(255) not null ;
	alter table radacct add column SIPAccount varchar(255) not null ;

	# v 1.6
	alter table cdrtool.destinations add column gateway varchar(50) not null after id;
	alter table cdrtool.destinations add column domain varchar(50) not null after gateway;
	alter table cdrtool.destinations add column subscriber varchar(50) not null after domain;
	alter table cdrtool.billing_customers add column profileNGN varchar(10) not null;

	CREATE TABLE cdrtool.billing_profilesNGN (
	id bigint(20) unsigned NOT NULL auto_increment,
	name varchar(25) NOT NULL default '',
	rate varchar(25) NOT NULL default '',
	application varchar(255) NOT NULL,
	PRIMARY KEY (id),
	KEY name_idx (name)
	) ;

	CREATE TABLE cdrtool.billing_ratesNGN (
	id bigint(20) unsigned NOT NULL auto_increment,
	name varchar(25) NOT NULL default '',
	TrafficRate varchar(25) NOT NULL default '',
	ConnectCost varchar(255) NOT NULL,
	PRIMARY KEY (id),
	KEY name_idx (name)
	) ;

	# v 1.7
	CREATE TABLE ser.user_quota (
	id int(10) unsigned NOT NULL auto_increment,
	username varchar(64) NOT NULL default '',
	domain varchar(128) NOT NULL default '',
	notify_address varchar(255) NOT NULL default '',
	notified smallint unsigned not null,
	cost varchar(128) NOT NULL default '',
	traffic varchar(128) NOT NULL,
	PRIMARY KEY (id),
	INDEX sipuser (username, domain)
	);

	alter table radacct add index caller_idx (CallingStationId);
	alter table radacct add index called_idx (CalledStationId);

	alter table radacct add column SipUserAgents varchar(255) NOT NULL after SipTranslatedRequestURI;
	alter table radacct add column SipApplicationType varchar(255) NOT NULL after SipUserAgents;
	alter table radacct add column SipCodecs varchar(255) NOT NULL after SipApplicationType;

	update radacct set SipToTag = H323ConfID;
	update radacct set SipFromTag = H323CallOrigin;
	update radacct set SipTranslatedRequestURI = H323RemoteAddress;
	update radacct set SipMethod = H323CallType;
	update radacct set SipResponseCode = H323DisconnectCause;
	update radacct set SipCodecs = FramedProtocol;
	update radacct set UserName= CONCAT(UserName,'@',Realm) where UserName not like '%@%';
	update radacct set SipUserAgents = NASPortType;

	alter table radacct drop column BillingPartyId;
	alter table radacct drop column RemotePartyId;

	alter table radacct change column SipToTag SipToTag varchar(128) not null;
	alter table radacct change column SipFromTag SipFromTag varchar(128) not null;

	alter table radacct drop index sess_id;
	alter table radacct add unique sess_id(AcctSessionId(128),SipFromTag,SipToTag)
	alter table radacct drop column H323GWID;
	alter table radacct drop column H323CallOrigin;
	alter table radacct drop column H323CallType;
	alter table radacct drop column H323SetupTime;
	alter table radacct drop column H323ConnectTime;
	alter table radacct drop column H323DisconnectTime;
	alter table radacct drop column H323DisconnectCause;
	alter table radacct drop column H323RemoteAddress;
	alter table radacct drop column H323VoiceQuality;
	alter table radacct drop column H323ConfID;

	alter table cdrtool.billing_customers add column timezone varchar(128) not null;

	2.0.2
	alter table radacct drop column Redirected;

	2.0.3
	Add to sql.conf
	accounting_stop_query_alt = "UPDATE ${acct_table1} SET AcctStopTime = '%S',AcctSessionTime=unix_timestamp('%S') - unix_timestamp(AcctStartTime),AcctTerminateCause = '%{SIP-Response-Code}', AcctStopDelay ='%{Acct-Delay-Time}', ConnectInfo_stop = '%{Connect-Info}' WHEREAcctSessionId = '%{Acct-Session-Id}' AND (SipToTag = 'n/a' AND (SipFromTag= '%{Sip-From-Tag}' OR SipFromTag = '%{Sip-To-Tag}')) AND NASIPAddress ='%{NAS-IP-Address}'"

	2.1.3
	alter table radacct add column SipRPID varchar(25) not null after SipCodecs;

	2.2
	insert into sip_status values ('430','Quota exceeded','Client-Error');

	2.3
	alter table billing_customers add unique cust_idx (gateway,domain,subscriber);
	alter table destinations add unique cust_dest_idx (gateway,domain,subscriber,dest_id);
	alter table billing_profiles add unique profile_idx (name,hour1,hour2,hour3,hour4);
	alter table billing_rates add unique rate_idx (name,destination);
	alter table billing_ratesNGN add unique rate_idx (name);
	alter table billing_profilesNGN add unique profile_idx (name,application);

	2.5.3
	insert into sip_status values ('477','SendingError','Client-Error');
	insert into sip_status values ('478','UnresolvableNextHopAddress','Client-Error');
	insert into sip_status values ('479','UnparseableURI ','Client-Error');

	2.5.5
	insert into sip_status values ('434','User not online','Client-Error');

	2.5.9
	update sip_status set description = 'Canceled' where code = '487';
	update sip_status set description = 'Busy' where code = '486';
	update sip_status set description = 'TemporarilyUnavailable' where code = '480';
	update sip_status set description = 'NotAcceptable' where code = '488';
	update sip_status set description = 'Timeout' where code = '408';
	update sip_status set description = 'NotOnline' where code = '434';

	2.6.0
	alter table radacct add column SipRPIDHeader varchar(255) not null after SipRPID;
	alter table radacct add column SourceIP varchar(255) not null;
	alter table radacct add column SourcePort varchar(255) not null;
	alter table radacct add column CanonicalURI varchar(255) not null;
	alter table radacct add column DelayTime varchar(5) not null;
	alter table radacct add column BillingId varchar(255) not null;

	insert into sip_status VALUES ('492','CrossReferencedURI','Client-Error');
	alter table sip_status add column isdn_cause smallint(5) unsigned not null;
	update sip_status set isdn_cause = '57' where code = 401;
	update sip_status set isdn_cause = '21' where code = 402;
	update sip_status set isdn_cause = '57' where code = 403;
	update sip_status set isdn_cause = '1' where code = 404;
	update sip_status set isdn_cause = '127' where code = 405;
	update sip_status set isdn_cause = '127' where code = 406;
	update sip_status set isdn_cause = '21' where code = 407;
	update sip_status set isdn_cause = '10' where code = 408;
	update sip_status set isdn_cause = '41' where code = 409;
	update sip_status set isdn_cause = '1' where code = 410;
	update sip_status set isdn_cause = '127' where code = 413;
	update sip_status set isdn_cause = '127' where code = 414;
	update sip_status set isdn_cause = '79' where code = 415;
	update sip_status set isdn_cause = '127' where code = 420;
	update sip_status set isdn_cause = '127' where code = 422;
	update sip_status set isdn_cause = '18' where code = 480;
	update sip_status set isdn_cause = '127' where code = 481;
	update sip_status set isdn_cause = '127' where code = 482;
	update sip_status set isdn_cause = '127' where code = 483;
	update sip_status set isdn_cause = '28' where code = 484;
	update sip_status set isdn_cause = '1' where code = 485;
	update sip_status set isdn_cause = '17' where code = 486;
	update sip_status set isdn_cause = '127' where code = 487;
	update sip_status set isdn_cause = '127' where code = 488;
	update sip_status set isdn_cause = '41' where code = 500;
	update sip_status set isdn_cause = '79' where code = 501;
	update sip_status set isdn_cause = '38' where code = 502;
	update sip_status set isdn_cause = '63' where code = 503;
	update sip_status set isdn_cause = '102' where code = 504;
	update sip_status set isdn_cause = '127' where code = 505;
	update sip_status set isdn_cause = '47' where code = 580;
	update sip_status set isdn_cause = '17' where code = 600;
	update sip_status set isdn_cause = '21' where code = 603;
	update sip_status set isdn_cause = '1' where code = 604;
	update sip_status set isdn_cause = '58' where code = 606;

	3.0
	CREATE TABLE prepaid (
	id int(10) unsigned NOT NULL auto_increment,
	account varchar(255) NOT NULL default '',
	balance decimal(10,4) not NULL default '0.0000',
	balance_previous decimal(10,4) not NULL,
	change_date datetime not null,
	PRIMARY KEY (id),
	INDEX acc_id (account)
	);

	3.0.3
	alter table cdrtool.log add column datasource varchar(255) not null;
	update cdrtool.log set datasource = SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(rerun, '?','-1'),'&','1'),'=','-1') ;

	3.1.2
	alter table radius.radacct add index canon_idx (CanonicalURI);
	alter table ser.sip_trace add index call_id(callid);
	alter table prepaid add column last_call_price decimal(10,4) not null;
	alter table prepaid add column expires date not null after change_date;
	alter table prepaid add column disabled char(1) not null after expires;
	alter table prepaid add column call_in_progress datetime not null after disabled;
	alter table billing_customers add column profile_name1_alt varchar(10) not null after profile_name1;
	alter table billing_customers add column profile_name2_alt varchar(10) not null after profile_name2;

	3.2
	insert into settings (var_name,var_description,var_value) values
	('reloadRating','Rating tables have changed','');

	3.2.2
	alter table radacct change column Price Price double(20, 4);

	3.2.4
	alter table prepaid add column call_lock enum ('0','1') default '0' after disabled;
	alter table prepaid change column disabled disabled enum ('0','1') default '0' after account;
	alter table prepaid drop index acc_id;
	alter table prepaid add unique index acc_id(account);

	3.2.7
	alter table cdrtool.settings add column billing_party varchar(255) not null after id;
	insert into cdrtool.settings (billing_party,var_name,var_value) values ('domain.com','providerName','Provider Name');
	insert into cdrtool.settings (billing_party,var_name,var_value) values ('domain.com','fromEmail','support@example.com');

	3.3.2
	alter table cdrtool.billing_rates add column gateway varchar(50) NOT NULL default '' after id;
	alter table cdrtool.billing_rates add column domain varchar(50) NOT NULL default '' after gateway;
	alter table cdrtool.billing_rates add column subscriber varchar(50) NOT NULL default '' after domain;
	alter table cdrtool.billing_profiles add column gateway varchar(50) NOT NULL default '' after id;
	alter table cdrtool.billing_profiles add column domain varchar(50) NOT NULL default '' after gateway;
	alter table cdrtool.billing_profiles add column subscriber varchar(50) NOT NULL default '' after domain;
	alter table cdrtool.billing_ratesNGN add column gateway varchar(50) NOT NULL default '' after id;
	alter table cdrtool.billing_ratesNGN add column domain varchar(50) NOT NULL default '' after gateway;
	alter table cdrtool.billing_ratesNGN add column subscriber varchar(50) NOT NULL default '' after domain;
	alter table cdrtool.billing_profilesNGN add column gateway varchar(50) NOT NULL default '' after id;
	alter table cdrtool.billing_profilesNGN add column domain varchar(50) NOT NULL default '' after gateway;
	alter table cdrtool.billing_profilesNGN add column subscriber varchar(50) NOT NULL default '' after domain;

	alter table cdrtool.destinations drop column asr;

	alter table cdrtool.prepaid drop column disabled;
	alter table cdrtool.prepaid drop column balance_previous;
	alter table cdrtool.prepaid drop column expires;
	alter table cdrtool.prepaid add column maxsessiontime bigint unsigned not null;
	alter table cdrtool.prepaid add column destination varchar(50) not null;

	3.3.4
	alter table radacct drop index FramedIPAddress;
	alter table radacct add index source_ip_idx (SourceIP);
	alter table radacct add index billing_id_idx (BillingId);
	alter table radacct add index dest_id_idx (DestinationId);
	alter table radacct drop column CiscoNASPort;

	3.3.5
	alter table asterisk_cdr add index calldate_idx (calldate);
	alter table asterisk_cdr add index clid_idx (clid);
	alter table asterisk_cdr add index src_idx (src);
	alter table asterisk_cdr add index dst_idx (dst);
	alter table asterisk_cdr add index channel_idx (channel);
	alter table asterisk_cdr add index dstchannel_idx (dstchannel);

	3.3.6
	alter table radacct add index sip_req_uri_idx (SipTranslatedRequestURI);
	alter table radacct add index sip_req_uri_idx (SIPAccount);

	3.4.0
	alter table radacct add index normalize_idx(Normalized);

	4.0.3
	alter table radacct add column MediaInfo varchar(32) default NULL;
	alter table radacct add index MediaInfo_idx(MediaInfo);
	update radacct set MediaInfo = '' where AcctStopTime != '0000-00-00 00:00:00';
	update radacct set MediaInfo = 'timeout' where AcctStopTime = '0000-00-00 00:00:00' and AcctSessionTime > 0;
	update radacct set MediaInfo = 'timeout' where AcctStopTime = '0000-00-00 00:00:00' and AcctInputOctets > 0;
	update radacct set MediaInfo = 'timeout' where AcctStopTime = '0000-00-00 00:00:00' and AcctOutputOctets > 0;
	update radacct set MediaInfo = 'timeout' where AcctStopTime = '0000-00-00 00:00:00' and SipCodecs != '';

	4.1.2
	alter table radacct add index Realm_idx(Realm);

	4.1.3
	alter table settings change column var_value var_value text not null;

	4.1.2
	alter table radacct add column RTPStatistics text not null after MediaInfo;

	4.2
	CREATE TABLE `prepaid_history` (
	`id` bigint(20) unsigned NOT NULL auto_increment,
	`username` varchar(64) NOT NULL default '',
	`domain` varchar(64) NOT NULL default '',
	`action` varchar(255) NOT NULL default '',
	`number` varchar(255) NOT NULL default '',
	`value` float(10,2) NOT NULL default '0.00',
	`balance` float(10,2) NOT NULL default '0.00',
	`date` datetime default NULL,
	PRIMARY KEY (`id`),
	KEY `acct_id` (`username`,`domain`)
	);

	CREATE TABLE `prepaid_cards` (
	`id` bigint(20) unsigned NOT NULL auto_increment,
	`batch` varchar(255) NOT NULL default '',
	`number` bigint(20) unsigned NOT NULL default '0',
	`value` smallint(5) unsigned NOT NULL default '0',
	`blocked` enum('0','1') NOT NULL default '0',
	`date_active` date NOT NULL default '0000-00-00',
	`date_batch` date NOT NULL default '0000-00-00',
	`service` varchar(255) NOT NULL default 'sip',
	PRIMARY KEY (`id`),
	UNIQUE KEY `card_idx` (`number`),
	KEY `nr_id` (`number`)
	);

	alter table radacct
	add column FromHeader varchar(128) not null,
	add column UserAgent varchar(128) not null,
	add column Contact varchar(128) not null;

	4.3
	update radacct set SIPApplicationType = 'audio' where SIPApplicationType='';

	4.3.1
	alter table asterisk_cdr change column Rate Rate text not null;

	4.4.5
	update radacct set
	CalledStationId = trim(leading 'sip:' from trim(leading 'sips:' from CalledStationId)),
	CallingStationId = trim(leading 'sip:' from trim(leading 'sips:' from CallingStationId)),
	CanonicalURI = trim(leading 'sip:' from trim(leading 'sips:' from CanonicalURI)),
	SipTranslatedRequestURI = trim(leading 'sip:' from trim(leading 'sips:' from SipTranslatedRequestURI))
	where Normalized = 1 and CalledStationId like 'sip:%';

	4.4.6
	INSERT INTO sip_status VALUES (435,'Diverted','Client-Error','0');

	4.5.9
	alter table log add index login_idx(login);

	4.6-4
	update sip_status set description = 'NotAcceptableMedia' where code = 488;

	4.7-0
	DROP TABLE IF EXISTS `memcache`;
	CREATE TABLE `memcache` (
	`key` varchar(255) NOT NULL,
	`value` text NOT NULL,
	PRIMARY KEY (`key`)
	) ;

	4.7-6
	alter table billing_customers add column increment smallint unsigned not null;
	alter table billing_customers add column min_duration smallint unsigned not null;

	4.8.1
	alter table asterisk_cdr change column disposition disposition varchar(45) not null default '';

	5.0-2
	alter table billing_profiles drop index `profile_idx` ;
	alter table billing_profiles add UNIQUE KEY `profile_idx` (`gateway`,`domain`,`subscriber`,`name`);

	alter table billing_rates drop index `rate_idx` ;
	alter table billing_rates add UNIQUE KEY `rate_idx` (`gateway`,`domain`,`subscriber`,`name`,`destination`);

	alter table billing_customers add column country_code varchar(8) not NULL;

	alter table billing_profilesNGN drop index `profile_idx` ;
	alter table billing_profilesNGN add UNIQUE KEY `profile_idx` (`gateway`,`domain`,`subscriber`,`name`);

	alter table billing_ratesNGN drop index `rate_idx` ;
	alter table billing_ratesNGN add UNIQUE KEY `rate_idx` (`gateway`,`domain`,`subscriber`,`name`);

	5.0.10
	alter table destinations change column dest_id dest_id varchar(100) not null;
	alter table billing_rates change column destination destination varchar(100) not null;

	5.1.0
	alter table billing_rates change column `name` `name` varchar(25) not null;
	alter table billing_profiles change column `name` `name` varchar(25) not null;
	alter table billing_customers change column `profile_name1` `profile_name1` varchar(25) not null;
	alter table billing_customers change column `profile_name1_alt` `profile_name1_alt` varchar(25) not null;
	alter table billing_customers change column `profile_name2` `profile_name2` varchar(25) not null;
	alter table billing_customers change column `profile_name2_alt` `profile_name2_alt` varchar(25) not null;

	CREATE TABLE `billing_rates_history` (
	`id` bigint(20) unsigned NOT NULL auto_increment,
	`gateway` varchar(50) NOT NULL default '',
	`domain` varchar(50) NOT NULL default '',
	`subscriber` varchar(50) NOT NULL default '',
	`name` varchar(25) NOT NULL default '',
	`destination` varchar(100) NOT NULL default '',
	`durationRate` varchar(25) NOT NULL default '',
	`trafficRate` varchar(25) NOT NULL default '',
	`application` varchar(25) NOT NULL default '',
	`connectCost` varchar(25) NOT NULL default '',
	`startDate` date NOT NULL,
	`endDate` date NOT NULL,
	PRIMARY KEY (`id`),
	UNIQUE KEY `rate_idx` (`gateway`,`domain`,`subscriber`,`name`,`destination`,`application`,`startDate`,`endDate`),
	KEY `name_idx` (`name`),
	KEY `dest_idx` (`destination`)
	) ENGINE=MyISAM DEFAULT CHARSET=latin1;

	alter table billing_rates change column applicationRate application varchar(25) not null default 'audio';

	update billing_rates set application ='audio';

	update billing_rates_history set application ='audio';

	alter table billing_customers drop column profileNGN;

	alter table billing_rates drop index rate_idx;

	alter table billing_rates add UNIQUE KEY `rate_idx` (`gateway`,`domain`,`subscriber`,`name`,`destination`,`application`);


	5.2.1
	Apply again the MySQL stored procedures to radius database from:
	setup/radius/OpenSER/radius_accounting.proc

	5.2.7
	alter table auth_user add column reseller bigint unsigned not null;

	CREATE TABLE `billing_enum_tlds` (
	`id` bigint(20) unsigned NOT NULL auto_increment,
	`gateway` varchar(50) NOT NULL default '',
	`domain` varchar(50) NOT NULL default '',
	`subscriber` varchar(50) NOT NULL default '',
	`enum_tld` varchar(100) NOT NULL,
	`e164_regexp` varchar(100) NOT NULL,
	`discount` varchar(25) NOT NULL default '',
	PRIMARY KEY (`id`),
	UNIQUE KEY `enum_idx` (`gateway`,`domain`,`subscriber`,`enum_tld`),
	KEY `tld_idx` (`enum_tld`)
	);

	INSERT into billing_enum_tlds (enum_tld,e164_regexp,discount) values ('e164.example.com','([1-9][0-9]{7,})','25');

	alter table radius.radacctYYYMMDD change column FramedProtocol ENUMtld varchar(64) not NULL;

	5.4
	alter table prepaid_cards change column blocked blocked smallint unsigned not null;
	alter table prepaid_history change column value value decimal(10,4) not null;
	alter table prepaid_history change column balance balance decimal(10,4) not null;
	alter table auth_user change column reseller impersonate varchar(50) not null;

	6.2.3
	alter table cdrtool.billing_customers change column domain domain varchar(64) not null;
	alter table cdrtool.billing_customers change column subscriber subscriber varchar(128) not null;
	alter table cdrtool.billing_customers change column gateway gateway varchar(15) not null;

	alter table cdrtool.billing_profiles change column domain domain varchar(64) not null;
	alter table cdrtool.billing_profiles change column subscriber subscriber varchar(128) not null;
	alter table cdrtool.billing_profiles change column gateway gateway varchar(15) not null;

	alter table cdrtool.billing_rates change column domain domain varchar(64) not null;
	alter table cdrtool.billing_rates change column subscriber subscriber varchar(128) not null;
	alter table cdrtool.billing_rates change column gateway gateway varchar(15) not null;

	alter table cdrtool.billing_rates_history change column domain domain varchar(64) not null;
	alter table cdrtool.billing_rates_history change column subscriber subscriber varchar(128) not null;
	alter table cdrtool.billing_rates_history change column gateway gateway varchar(15) not null;

	alter table cdrtool.billing_enum_tlds change column domain domain varchar(64) not null;
	alter table cdrtool.billing_enum_tlds change column subscriber subscriber varchar(128) not null;
	alter table cdrtool.billing_enum_tlds change column gateway gateway varchar(15) not null;

	alter table cdrtool.billing_ratesNGN change column domain domain varchar(64) not null;
	alter table cdrtool.billing_ratesNGN change column subscriber subscriber varchar(128) not null;
	alter table cdrtool.billing_ratesNGN change column gateway gateway varchar(15) not null;

	alter table cdrtool.prepaid change column account account varchar(128) not null;

	optimize table billing_rates;
	optimize table billing_rates_history;
	optimize table billing_customers;
	optimize table billing_profiles;
	optimize table prepaid;

	6.3.3

	DROP TABLE IF EXISTS `quota_usage`;
	CREATE TABLE `quota_usage` (
	`id` int(10) unsigned NOT NULL auto_increment,
	`datasource` varchar(50) NOT NULL,
	`account` varchar(128) NOT NULL default '',
	`domain` varchar(64) default NULL,
	`quota` int(11) unsigned NOT NULL,
	`blocked` enum('0','1') NOT NULL default '0',
	`notified` datetime NOT NULL,
	`calls` int(10) unsigned NOT NULL,
	`duration` bigint(20) unsigned NOT NULL,
	`cost` decimal(10,4) NOT NULL,
	`traffic` varchar(50) NOT NULL,
	`change_date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
	PRIMARY KEY (`id`),
	UNIQUE KEY `account_idx` (`datasource`,`account`)
	);

	alter table memcache change `value` `value` longblob not null;


	6.6.0

	alter table prepaid add column active_sessions text not null;
	alter table prepaid add column session_counter int unsigned not null;


	6.7.0
	alter table billing_rates drop column trafficRate;
	alter table cdrtool.billing_rates_history drop column trafficRate;

	alter table billing_rates_history modify application varchar(25) not null default 'audio' after destination;
	alter table billing_rates modify application varchar(25) not null default 'audio' after destination;

	alter table billing_rates_history modify connectCost varchar(25) not null after application;
	alter table billing_rates modify connectCost varchar(25) not null after application;

	alter table billing_rates add column connectCostIn varchar(25) not null;
	alter table billing_rates add column durationRateIn varchar(25) not null;

	alter table cdrtool.billing_rates_history add column connectCostIn varchar(25) not null after durationRate;
	alter table cdrtool.billing_rates_history add column durationRateIn varchar(25) not null after connectCostIn;
	alter table prepaid_history add column duration bigint unsigned not null after action;
	alter table prepaid_history add column session varchar(255) not null after duration;
	alter table prepaid_history add column destination varchar (15) not null after duration;
	alter table prepaid_history add index session_idx(session);

	alter table prepaid add column duration bigint(20) unsigned not null after destination;
	alter table prepaid add column domain varchar(128) not null after account;
	update prepaid set domain = SUBSTRING_INDEX(account, '@',-1);

	6.7.6
	alter table prepaid drop column call_lock;
	+
	+6.7.9
	+alter table prepaid_history change column `number` `description` varchar(255) not null;
	+
	diff --git a/setup/mysql/create_tables.mysql b/setup/mysql/create_tables.mysql
	index 64ae4f4..232e72c 100644
	--- a/setup/mysql/create_tables.mysql
	+++ b/setup/mysql/create_tables.mysql
	@@ -1,489 +1,489 @@
	-- MySQL dump 10.10
	--
	-- Host: db-log Database: cdrtool
	-- ------------------------------------------------------
	-- Server version 5.0.24a-Debian_9-log

	/!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT /;
	/!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS /;
	/!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION /;
	/!40101 SET NAMES utf8 /;
	/!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE /;
	/!40103 SET TIME_ZONE='+00:00' /;
	/!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 /;
	/!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 /;
	/!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' /;
	/!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 /;

	--
	-- Table structure for table `active_sessions`
	--

	DROP TABLE IF EXISTS `active_sessions`;
	CREATE TABLE `active_sessions` (
	`sid` varchar(32) NOT NULL default '',
	`name` varchar(32) NOT NULL default '',
	`val` text,
	`changed` varchar(14) NOT NULL default '',
	`count` bigint(20) unsigned NOT NULL default '0',
	PRIMARY KEY (`name`,`sid`),
	KEY `changed` (`changed`)
	) DEFAULT CHARSET=latin1;

	--
	-- Table structure for table `active_sessions_split`
	--

	DROP TABLE IF EXISTS `active_sessions_split`;
	CREATE TABLE `active_sessions_split` (
	`ct_sid` varchar(32) NOT NULL default '',
	`ct_name` varchar(32) NOT NULL default '',
	`ct_pos` varchar(6) NOT NULL default '',
	`ct_val` text,
	`ct_changed` varchar(14) NOT NULL default '',
	PRIMARY KEY (`ct_name`,`ct_sid`,`ct_pos`),
	KEY `ct_changed` (`ct_changed`)
	) DEFAULT CHARSET=latin1;

	--
	-- Table structure for table `asterisk_cdr`
	--

	DROP TABLE IF EXISTS `asterisk_cdr`;
	CREATE TABLE `asterisk_cdr` (
	`id` bigint(20) unsigned NOT NULL auto_increment,
	`calldate` datetime NOT NULL default '0000-00-00 00:00:00',
	`clid` varchar(45) NOT NULL default '',
	`src` varchar(45) NOT NULL default '',
	`dst` varchar(45) NOT NULL default '',
	`dcontext` varchar(45) NOT NULL default '',
	`channel` varchar(45) NOT NULL default '',
	`dstchannel` varchar(45) NOT NULL default '',
	`lastapp` varchar(45) NOT NULL default '',
	`lastdata` varchar(45) NOT NULL default '',
	`duration` int(11) NOT NULL default '0',
	`billsec` int(11) NOT NULL default '0',
	`disposition` varchar(45) NOT NULL default '',
	`amaflags` int(11) NOT NULL default '0',
	`accountcode` varchar(45) NOT NULL default '',
	`uniqueid` varchar(45) NOT NULL default '',
	`DestinationId` varchar(255) NOT NULL default '',
	`Price` varchar(255) NOT NULL default '',
	`Rate` text NOT NULL,
	`Normalized` enum('0','1') default '0',
	PRIMARY KEY (`id`),
	KEY `calldate_idx` (`calldate`),
	KEY `clid_idx` (`clid`),
	KEY `src_idx` (`src`),
	KEY `dst_idx` (`dst`),
	KEY `channel_idx` (`channel`),
	KEY `dstchannel_idx` (`dstchannel`),
	KEY `normalized_idx` (`Normalized`)
	) DEFAULT CHARSET=latin1;

	--
	-- Table structure for table `auth_user`
	--

	DROP TABLE IF EXISTS `auth_user`;
	CREATE TABLE `auth_user` (
	`user_id` varchar(32) NOT NULL default '',
	`username` varchar(32) NOT NULL default '',
	`password` varchar(32) NOT NULL default '',
	`perms` varchar(255) default NULL,
	`expire` date NOT NULL default '0000-00-00',
	`organization` varchar(255) NOT NULL default '',
	`name` varchar(50) NOT NULL default '',
	`tel` varchar(50) NOT NULL default '',
	`email` varchar(50) NOT NULL default '',
	`display_cli` char(1) NOT NULL default '',
	`compidFilter` varchar(255) NOT NULL default '',
	`domainFilter` varchar(255) NOT NULL default '',
	`gatewayFilter` varchar(255) NOT NULL default '',
	`cscodeFilter` varchar(255) NOT NULL default '',
	`serviceFilter` varchar(255) NOT NULL default '',
	`aNumberFilter` varchar(255) NOT NULL default '',
	`afterDateFilter` varchar(255) NOT NULL default '',
	`sources` varchar(255) NOT NULL default '',
	`otp_enable` enum('0','1') NOT NULL default '0',
	`otp_rcpt` varchar(255) NOT NULL default '',
	`otp_expire` datetime NOT NULL default '0000-00-00 00:00:00',
	`otp_passwd` varchar(255) NOT NULL default '',
	`impersonate` varchar(50) not null default '',
	PRIMARY KEY (`user_id`),
	UNIQUE KEY `k_username` (`username`)
	) DEFAULT CHARSET=latin1;

	--
	-- Table structure for table `billing_customers`
	--

	DROP TABLE IF EXISTS `billing_customers`;
	CREATE TABLE `billing_customers` (
	`id` bigint(20) unsigned NOT NULL auto_increment,
	`gateway` varchar(15) NOT NULL default '',
	`domain` varchar(64) NOT NULL default '',
	`subscriber` varchar(128) NOT NULL default '',
	`profile_name1` varchar(25) NOT NULL,
	`profile_name1_alt` varchar(25) NOT NULL,
	`profile_name2` varchar(25) NOT NULL,
	`profile_name2_alt` varchar(25) NOT NULL,
	`timezone` varchar(128) NOT NULL default '',
	`increment` smallint(5) unsigned NOT NULL,
	`min_duration` smallint(5) unsigned NOT NULL,
	`country_code` varchar(8) NOT NULL,
	PRIMARY KEY (`id`),
	UNIQUE KEY `cust_idx` (`gateway`,`domain`,`subscriber`),
	KEY `subsc_idx` (`gateway`,`domain`,`subscriber`),
	KEY `gatedom_idx` (`gateway`,`domain`),
	KEY `gate_idx` (`gateway`),
	KEY `sub_idx` (`subscriber`)
	) DEFAULT CHARSET=latin1;

	--
	-- Table structure for table `billing_holidays`
	--

	DROP TABLE IF EXISTS `billing_holidays`;
	CREATE TABLE `billing_holidays` (
	`day` date NOT NULL default '0000-00-00',
	UNIQUE KEY `day_idx` (`day`)
	) DEFAULT CHARSET=latin1;

	--
	-- Table structure for table `billing_profiles`
	--

	DROP TABLE IF EXISTS `billing_profiles`;
	CREATE TABLE `billing_profiles` (
	`id` bigint(20) unsigned NOT NULL auto_increment,
	`gateway` varchar(15) NOT NULL default '',
	`domain` varchar(64) NOT NULL default '',
	`subscriber` varchar(128) NOT NULL default '',
	`name` varchar(25) NOT NULL,
	`rate_name1` varchar(25) NOT NULL default '',
	`hour1` smallint(5) unsigned NOT NULL default '0',
	`rate_name2` varchar(25) NOT NULL default '',
	`hour2` smallint(5) unsigned NOT NULL default '0',
	`rate_name3` varchar(25) NOT NULL default '',
	`hour3` smallint(5) unsigned NOT NULL default '0',
	`rate_name4` varchar(25) NOT NULL default '',
	`hour4` smallint(5) unsigned NOT NULL default '0',
	PRIMARY KEY (`id`),
	UNIQUE KEY `profile_idx` (`gateway`,`domain`,`subscriber`,`name`),
	KEY `name_idx` (`name`)
	) DEFAULT CHARSET=latin1;


	DROP TABLE IF EXISTS `billing_enum_tlds`;
	CREATE TABLE `billing_enum_tlds` (
	`id` bigint(20) unsigned NOT NULL auto_increment,
	`gateway` varchar(15) NOT NULL default '',
	`domain` varchar(64) NOT NULL default '',
	`subscriber` varchar(128) NOT NULL default '',
	`enum_tld` varchar(100) NOT NULL,
	`e164_regexp` varchar(100) NOT NULL,
	`discount` varchar(25) NOT NULL default '',
	PRIMARY KEY (`id`),
	UNIQUE KEY `enum_idx` (`gateway`,`domain`,`subscriber`,`enum_tld`),
	KEY `tld_idx` (`enum_tld`)
	) DEFAULT CHARSET=latin1;

	--
	-- Table structure for table `billing_profilesNGN`
	--

	DROP TABLE IF EXISTS `billing_profilesNGN`;
	CREATE TABLE `billing_profilesNGN` (
	`id` bigint(20) unsigned NOT NULL auto_increment,
	`gateway` varchar(15) NOT NULL default '',
	`domain` varchar(64) NOT NULL default '',
	`subscriber` varchar(128) NOT NULL default '',
	`name` varchar(25) NOT NULL default '',
	`rate` varchar(25) NOT NULL default '',
	`application` varchar(255) NOT NULL default '',
	PRIMARY KEY (`id`),
	UNIQUE KEY `profile_idx` (`gateway`,`domain`,`subscriber`,`name`),
	KEY `name_idx` (`name`)
	) DEFAULT CHARSET=latin1;

	--
	-- Table structure for table `billing_rates`
	--

	DROP TABLE IF EXISTS `billing_rates`;
	CREATE TABLE `billing_rates` (
	`id` bigint(20) unsigned NOT NULL auto_increment,
	`gateway` varchar(15) NOT NULL default '',
	`domain` varchar(64) NOT NULL default '',
	`subscriber` varchar(128) NOT NULL default '',
	`name` varchar(25) NOT NULL,
	`destination` varchar(100) NOT NULL,
	`application` varchar(25) NOT NULL default 'audio',
	`connectCost` varchar(25) NOT NULL default '',
	`durationRate` varchar(25) NOT NULL default '',
	`connectCostIn` varchar(25) NOT NULL default '',
	`durationRateIn` varchar(25) NOT NULL default '',
	PRIMARY KEY (`id`),
	UNIQUE KEY `rate_idx` (`gateway`,`domain`,`subscriber`,`name`,`destination`,`application`),
	KEY `name_idx` (`name`),
	KEY `dest_idx` (`destination`)
	) DEFAULT CHARSET=latin1;

	--
	-- Table structure for table `billing_ratesNGN`
	--

	DROP TABLE IF EXISTS `billing_ratesNGN`;
	CREATE TABLE `billing_ratesNGN` (
	`id` bigint(20) unsigned NOT NULL auto_increment,
	`gateway` varchar(15) NOT NULL default '',
	`domain` varchar(64) NOT NULL default '',
	`subscriber` varchar(128) NOT NULL default '',
	`name` varchar(25) NOT NULL default '',
	`TrafficRate` varchar(25) NOT NULL default '',
	`ConnectCost` varchar(255) NOT NULL default '',
	PRIMARY KEY (`id`),
	UNIQUE KEY `rate_idx` (`gateway`,`domain`,`subscriber`,`name`),
	KEY `name_idx` (`name`)
	) DEFAULT CHARSET=latin1;

	--
	-- Table structure for table `billing_rates_history`
	--

	DROP TABLE IF EXISTS `billing_rates_history`;
	CREATE TABLE `billing_rates_history` (
	`id` bigint(20) unsigned NOT NULL auto_increment,
	`gateway` varchar(15) NOT NULL default '',
	`domain` varchar(64) NOT NULL default '',
	`subscriber` varchar(128) NOT NULL default '',
	`name` varchar(25) NOT NULL default '',
	`destination` varchar(100) NOT NULL default '',
	`application` varchar(25) NOT NULL default 'audio',
	`connectCost` varchar(25) NOT NULL default '',
	`durationRate` varchar(25) NOT NULL default '',
	`connectCostIn` varchar(25) NOT NULL default '',
	`durationRateIn` varchar(25) NOT NULL default '',
	`startDate` date NOT NULL,
	`endDate` date NOT NULL,
	PRIMARY KEY (`id`),
	UNIQUE KEY `rate_idx` (`gateway`,`domain`,`subscriber`,`name`,`destination`,`startDate`,`endDate`,`application`),
	KEY `name_idx` (`name`),
	KEY `dest_idx` (`destination`)
	) DEFAULT CHARSET=latin1;

	--
	-- Table structure for table `db_sequence`
	--

	DROP TABLE IF EXISTS `db_sequence`;
	CREATE TABLE `db_sequence` (
	`seq_name` varchar(127) NOT NULL default '',
	`nextid` int(10) unsigned NOT NULL default '0',
	PRIMARY KEY (`seq_name`)
	) DEFAULT CHARSET=latin1;

	--
	-- Table structure for table `destinations`
	--

	DROP TABLE IF EXISTS `destinations`;
	CREATE TABLE `destinations` (
	`id` bigint(20) unsigned NOT NULL auto_increment,
	`gateway` varchar(15) NOT NULL default '',
	`domain` varchar(64) NOT NULL default '',
	`subscriber` varchar(128) NOT NULL default '',
	`dest_id` varchar(100) NOT NULL,
	`dest_name` varchar(255) NOT NULL default '',
	PRIMARY KEY (`id`),
	UNIQUE KEY `cust_dest_idx` (`gateway`,`domain`,`subscriber`,`dest_id`),
	KEY `dest_idx` (`dest_id`)
	) DEFAULT CHARSET=latin1;

	--
	-- Table structure for table `isdncause`
	--

	DROP TABLE IF EXISTS `isdncause`;
	CREATE TABLE `isdncause` (
	`cause` int(11) NOT NULL default '0',
	`description` varchar(255) NOT NULL default ''
	) DEFAULT CHARSET=latin1;

	--
	-- Table structure for table `lastquery`
	--

	DROP TABLE IF EXISTS `lastquery`;
	CREATE TABLE `lastquery` (
	`id` int(10) unsigned NOT NULL auto_increment,
	`date` datetime NOT NULL default '0000-00-00 00:00:00',
	`query` text,
	PRIMARY KEY (`id`)
	) DEFAULT CHARSET=latin1;

	--
	-- Table structure for table `log`
	--

	DROP TABLE IF EXISTS `log`;
	CREATE TABLE `log` (
	`id` int(10) unsigned NOT NULL auto_increment,
	`date` datetime NOT NULL default '0000-00-00 00:00:00',
	`login` varchar(32) NOT NULL default '',
	`ip` varchar(32) NOT NULL default '',
	`url` text NOT NULL,
	`reedit` text NOT NULL,
	`rerun` text NOT NULL,
	`results` int(10) unsigned default NULL,
	`description` text,
	`datasource` varchar(255) NOT NULL default '',
	PRIMARY KEY (`id`),
	KEY `log_date` (`date`),
	KEY `login_idx` (`login`)
	) DEFAULT CHARSET=latin1;

	--
	-- Table structure for table `memcache`
	--

	DROP TABLE IF EXISTS `memcache`;
	CREATE TABLE `memcache` (
	`key` varchar(255) NOT NULL default '',
	`value` longblob NOT NULL,
	PRIMARY KEY (`key`)
	) DEFAULT CHARSET=latin1;

	--
	-- Table structure for table `normalize_lock`
	--

	DROP TABLE IF EXISTS `normalize_lock`;
	CREATE TABLE `normalize_lock` (
	`notused` char(1) default NULL
	) DEFAULT CHARSET=latin1;

	--
	-- Table structure for table `prepaid`
	--

	DROP TABLE IF EXISTS `prepaid`;
	CREATE TABLE `prepaid` (
	`id` int(10) unsigned NOT NULL auto_increment,
	`account` varchar(255) NOT NULL default '',
	`balance` decimal(10,4) NOT NULL default '0.0000',
	`change_date` datetime NOT NULL default '0000-00-00 00:00:00',
	`last_call_price` decimal(10,4) NOT NULL default '0.0000',
	`destination` varchar(50) NOT NULL default '',
	`duration` bigint unsigned not null,
	`active_sessions` text NOT NULL,
	`maxsessiontime` bigint(20) unsigned NOT NULL,
	`call_in_progress` datetime NOT NULL default '0000-00-00 00:00:00',
	`session_counter` int(10) unsigned NOT NULL,
	PRIMARY KEY (`id`),
	UNIQUE KEY `acc_id` (`account`)
	) DEFAULT CHARSET=latin1;

	--
	-- Table structure for table `prepaid_cards`
	--

	DROP TABLE IF EXISTS `prepaid_cards`;
	CREATE TABLE `prepaid_cards` (
	`id` bigint(20) unsigned NOT NULL auto_increment,
	`batch` varchar(255) NOT NULL default '',
	`number` bigint(20) unsigned NOT NULL default '0',
	`value` smallint(5) unsigned NOT NULL default '0',
	`blocked` smallint unsigned not null,
	`date_active` date NOT NULL default '0000-00-00',
	`date_batch` date NOT NULL default '0000-00-00',
	`service` varchar(255) NOT NULL default 'sip',
	PRIMARY KEY (`id`),
	UNIQUE KEY `card_idx` (`number`),
	KEY `nr_id` (`number`)
	) DEFAULT CHARSET=latin1;

	--
	-- Table structure for table `prepaid_history`
	--

	DROP TABLE IF EXISTS `prepaid_history`;
	CREATE TABLE `prepaid_history` (
	`id` bigint(20) unsigned NOT NULL auto_increment,
	`username` varchar(64) NOT NULL default '',
	`domain` varchar(64) NOT NULL default '',
	`action` varchar(255) NOT NULL default '',
	- `number` varchar(255) NOT NULL default '',
	+ `description` varchar(255) NOT NULL default '',
	`duration` bigint unsigned not null,
	`destination` varchar(15) not null default '',
	`session` varchar(255) not null default '',
	`value` decimal(10,4) not null,
	`balance` decimal(10,4) not null,
	`date` datetime default NULL,
	PRIMARY KEY (`id`),
	KEY `acct_id` (`username`,`domain`),
	index session_idx(session)
	) DEFAULT CHARSET=latin1;

	--
	-- Table structure for table `settings`
	--

	DROP TABLE IF EXISTS `settings`;
	CREATE TABLE `settings` (
	`id` bigint(20) unsigned NOT NULL auto_increment,
	`billing_party` varchar(255) NOT NULL default '',
	`var_name` varchar(255) default NULL,
	`var_description` varchar(255) default NULL,
	`var_type` varchar(255) default NULL,
	`var_value` text NOT NULL,
	`var_read_only` char(1) default NULL,
	`var_defaults` varchar(255) default NULL,
	`var_module` varchar(255) default NULL,
	PRIMARY KEY (`id`),
	KEY `name` (`var_name`)
	) DEFAULT CHARSET=latin1;

	--
	-- Table structure for table `sip_status`
	--

	DROP TABLE IF EXISTS `sip_status`;
	CREATE TABLE `sip_status` (
	`code` smallint(5) unsigned NOT NULL default '0',
	`description` varchar(255) NOT NULL default '',
	`code_type` varchar(255) NOT NULL default '',
	`isdn_cause` smallint(5) unsigned NOT NULL default '0'
	) DEFAULT CHARSET=latin1;

	--
	-- Table structure for table `spam`
	--

	DROP TABLE IF EXISTS `spam`;
	CREATE TABLE `spam` (
	`ip` varchar(64) NOT NULL default '',
	`tries` smallint(5) unsigned NOT NULL default '0',
	`login` varchar(255) default NULL,
	`stamp` int(10) unsigned NOT NULL default '0',
	UNIQUE KEY `spam_ip` (`ip`)
	) DEFAULT CHARSET=latin1;

	DROP TABLE IF EXISTS `quota_usage`;
	CREATE TABLE `quota_usage` (
	`id` int(10) unsigned NOT NULL auto_increment,
	`datasource` varchar(50) NOT NULL,
	`account` varchar(128) NOT NULL default '',
	`domain` varchar(64) default NULL,
	`quota` int(11) unsigned NOT NULL,
	`blocked` enum('0','1') NOT NULL default '0',
	`notified` datetime NOT NULL,
	`calls` int(10) unsigned NOT NULL,
	`duration` bigint(20) unsigned NOT NULL,
	`cost` decimal(10,4) NOT NULL,
	`traffic` varchar(50) NOT NULL,
	`change_date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
	PRIMARY KEY (`id`),
	UNIQUE KEY `account_idx` (`datasource`,`account`)
	);

File Metadata

Mime Type: text/x-diff
Expires: Sat, Feb 1, 6:49 AM (1 d, 12 h)
Storage Engine: blob
Storage Format: Raw Data
Storage Handle: 3488884
Default Alt Text: (42 KB)

No OneTemporaryActions

View Options

File Metadata

Event Timeline

No OneTemporary
Actions