CREATE TABLE `location_history` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(80) NOT NULL default '',
`ipaddr` varchar(15) NOT NULL default '',
`port` smallint(5) unsigned NOT NULL default '0',
`regexpire` int(11) NOT NULL default '0',
`count` int(11) NOT NULL default '0',
`start` int(11) NOT NULL default '0',
`stop` int(11) NOT NULL default '0',
`datestart` DATETIME NOT NULL default '2011-01-01 00:00:00',
`datestop` DATETIME NOT NULL default '2011-01-01 00:00:00',
PRIMARY KEY (`id`),
UNIQUE KEY `id` (`id`),
KEY `name` (`name`)
) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;


DROP PROCEDURE IF EXISTS `location_history_function`;
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `location_history_function`(location_name varchar(80), location_ipaddr varchar(15), location_port smallint(5) unsigned, location_regrequest int(11), location_regexpire int(11))
BEGIN
DECLARE lastid int;
DECLARE lastip varchar(15);
DECLARE lastport smallint(5) unsigned;
DECLARE aux integer;
SELECT max(id) INTO lastid FROM location_history WHERE name = location_name;
SET aux = 0;
IF lastid is not NULL THEN
  SELECT ipaddr INTO lastip FROM location_history WHERE id = lastid;
  IF lastip = location_ipaddr THEN
    SELECT port INTO lastport FROM location_history WHERE id = lastid;
    SET aux = 1;
    IF lastport = location_port THEN
      SET aux = 2;
      UPDATE location_history SET count = count+1, stop = location_regrequest, datestop = DATE_FORMAT(FROM_UNIXTIME(location_regrequest), '%Y-%m-%d %H:%i:%S'), regexpire = location_regexpire WHERE id = lastid;
    END IF;
  END IF;
END IF;
IF aux != 2 THEN
  IF location_port != 0 THEN
    IF location_ipaddr != '' THEN
      IF location_ipaddr is not NULL THEN
       INSERT INTO location_history (name, ipaddr, port, regexpire, count, start, stop, datestart, datestop) VALUES (location_name, location_ipaddr, location_port, location_regexpire, 1, location_regrequest, location_regrequest, DATE_FORMAT(FROM_UNIXTIME(location_regrequest), '%Y-%m-%d %H:%i:%S'), DATE_FORMAT(FROM_UNIXTIME(location_regrequest), '%Y-%m-%d %H:%i:%S'));
      END IF;
    END IF;
  END IF;  
END IF;
END $$
DELIMITER ;



DROP trigger IF EXISTS `location_trigger`;
CREATE trigger `location_trigger` AFTER INSERT ON location
	FOR EACH ROW CALL location_history_function(NEW.name, NEW.ipaddr, NEW.port, NEW.regrequest, NEW.regexpire);