use astrad;
CREATE TABLE `location2_history` (
  `start` datetime default NULL,
  `stop`  datetime default NULL,
  `domain` varchar(15) NOT NULL default '',
  `username` varchar(80) NOT NULL default '',
  `ipaddr`   varchar(15) NOT NULL default '',
  `port`     smallint(5) unsigned NOT NULL default 0,
  `count` INT(10) unsigned default 1,
  PRIMARY KEY  (`username`,`domain`,`ipaddr`,`port`)
) ;

CREATE TABLE `config` (
  `name` varchar(80) NOT NULL default '',
  `value` varchar(80) NOT NULL default '',
  `description` text default NULL,
  PRIMARY KEY  (`name`)
) ;

INSERT INTO config VALUES ('day_log_history',60,'Keep log registration for number of days specified.');
INSERT INTO config VALUES ('max_reg_to_check',1000,'Execute delete query  every N registration received.');
INSERT INTO config VALUES ('tmp_reg_to_check',1,'Used by procedure REGISTER, auto-increment for each registration received.');

DELIMITER |
DROP PROCEDURE IF EXISTS `REGISTER`|
CREATE PROCEDURE `REGISTER`(IN USR VARCHAR(80),IN DOM VARCHAR(15), IN CON VARCHAR(255), IN EXR DATETIME)
BEGIN
DECLARE MAXD INT(5) DEFAULT 0;
IF (SELECT 1 FROM location2 WHERE username=USR AND contact=CON AND domain=DOM) THEN
  UPDATE location2 SET expires=EXR WHERE username=USR AND contact=CON AND domain=DOM;
ELSE
  INSERT INTO location2 VALUES (USR,DOM,CON,EXR);
END IF;
INSERT INTO location2_history (start,stop,domain,username,ipaddr,port,count) VALUES (NOW(),NOW(),DOM,USR,SUBSTRING_INDEX(SUBSTRING(CON,LOCATE('@', CON)+1), ':', 1),SUBSTRING_INDEX(SUBSTRING(CON,LOCATE('@', CON)+1), ':', -1),1) ON DUPLICATE KEY UPDATE count=count+1, stop=NOW();
IF (SELECT value FROM config WHERE name='tmp_reg_to_check') < 1000 THEN
  UPDATE config SET value=value+1 WHERE name='tmp_reg_to_check';
  ELSE
    UPDATE config SET value=1 WHERE name='tmp_reg_to_check';
	SET MAXD=(SELECT value FROM config WHERE name='day_log_history');
	DELETE FROM location2_history WHERE stop < DATE_ADD(NOW(), INTERVAL -MAXD DAY);
END IF;
END |
DELIMITER ;