No Comment !

Emin, Nicolas, Oussama

2011-06-15

Switzernet

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 |

DROP PROCEDURE IF EXISTS `StatReg`|

CREATE PROCEDURE StatReg( IN DOM VARCHAR(15) , IN ST datetime, IN SP datetime, IN ITV INT(5))

BEGIN

  DECLARE TMP1 datetime default '';

  DECLARE TMP2 datetime default '';

  DECLARE NB INT(10) default 0;

  DECLARE done INT DEFAULT 0;

  DECLARE USR VARCHAR(11);

  DECLARE cur CURSOR FOR select distinct(username) from location2_history where domain=DOM;

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;   

  DROP TABLE IF EXISTS timetempo;

    CREATE TEMPORARY TABLE timetempo (

    `START` VARCHAR(19) default '',

     `STOP` VARCHAR(19) default '',

     `COUNT = 1` INT(10) default 0,

     `COUNT > 1` INT(10) default 0,

     PRIMARY KEY  (`START`,`STOP`)

  );

  OPEN cur;

  read_loop: LOOP

    FETCH cur INTO USR;

      IF done THEN

      LEAVE read_loop;

    END IF;    

    SET TMP1=ST; 

    SET TMP2=DATE_ADD(TMP1, INTERVAL ITV MINUTE);     

    WHILE TMP1 < SP DO

       SET NB=(select count(*) from location2_history where start>TMP1 and stop<TMP2 and domain=DOM and username=USR);

       IF (NB>1) THEN

        INSERT INTO timetempo (`START`,`STOP`,`COUNT > 1`) VALUES (TMP1,TMP2,1) ON DUPLICATE KEY UPDATE `COUNT > 1`=`COUNT > 1`+1;

       ELSE

        INSERT INTO timetempo (`START`,`STOP`,`COUNT = 1`) VALUES (TMP1,TMP2,1) ON DUPLICATE KEY UPDATE `COUNT = 1`=`COUNT = 1`+1;

       END IF; 

      SET TMP1=TMP2; 

      SET TMP2=DATE_ADD(TMP1, INTERVAL ITV MINUTE);

    END WHILE;

  END LOOP;

  CLOSE cur;

  SELECT * FROM timetempo;

  DELETE FROM timetempo;

END|

DELIMITER ;

mysql> CALL StatReg('91.121.XX.XX','2011-06-16 13:00:00','2011-06-16 19:00:00',30);

+---------------------+---------------------+-----------+-----------+

| START               | STOP                | COUNT = 1 | COUNT > 1 |

+---------------------+---------------------+-----------+-----------+

| 2011-06-16 13:00:00 | 2011-06-16 13:30:00 |       166 |        26 |

| 2011-06-16 13:30:00 | 2011-06-16 14:00:00 |       166 |        26 |

| 2011-06-16 14:00:00 | 2011-06-16 14:30:00 |       165 |        27 |

| 2011-06-16 14:30:00 | 2011-06-16 15:00:00 |       166 |        26 |

| 2011-06-16 15:00:00 | 2011-06-16 15:30:00 |       166 |        26 |

| 2011-06-16 15:30:00 | 2011-06-16 16:00:00 |       166 |        26 |

| 2011-06-16 16:00:00 | 2011-06-16 16:30:00 |       192 |         0 |

| 2011-06-16 16:30:00 | 2011-06-16 17:00:00 |       192 |         0 |

| 2011-06-16 17:00:00 | 2011-06-16 17:30:00 |       191 |         1 |

| 2011-06-16 17:30:00 | 2011-06-16 18:00:00 |       191 |         1 |

| 2011-06-16 18:00:00 | 2011-06-16 18:30:00 |       192 |         0 |

| 2011-06-16 18:30:00 | 2011-06-16 19:00:00 |       192 |         0 |

+---------------------+---------------------+-----------+-----------+

12 rows in set (1.67 sec)

Query OK, 12 rows affected, 2 warnings (1.67 sec)

* * *