References:
http://dev.mysql.com/doc/refman/5.0/en/internal-locking.html


CREATE DATABASE IF NOT EXISTS `statistics`;
USE `statistics`;
DROP TABLE IF EXISTS `location2_history_active`;

-- Table with all active customers

/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `location2_history_active` (
  `db` varchar(15) NOT NULL,
  `domain` varchar(15) NOT NULL DEFAULT '',
  `username` varchar(80) NOT NULL DEFAULT '',
  `ipaddress` varchar(15) NOT NULL DEFAULT '',
  `port` smallint(5) unsigned NOT NULL DEFAULT '0',
  `useragent` varchar(20) Default 'Unknown',
  `start` datetime DEFAULT NULL,
  `stop` datetime DEFAULT NULL,
  `expires` int(10) unsigned,
  `count` int(10) unsigned DEFAULT '1',
  PRIMARY KEY (`username`,`db`,`domain`,`ipaddress`,`port`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Table with information about old registrations

CREATE TABLE IF NOT EXISTS `location2_history_old` (
  `id` int(11) NOT NULL auto_increment,
  `db` varchar(15) NOT NULL,
  `domain` varchar(15) NOT NULL,
  `username` varchar(50) NOT NULL,
  `ipaddress` varchar(15) NOT NULL,
  `port` smallint(5) unsigned NOT NULL,
  `start` datetime NOT NULL,
  `stop` datetime NOT NULL,
  `expires` int(10) unsigned,
  `count` int(10) unsigned DEFAULT 1,
  `useragent` varchar(20) Default 'Unknown',
  `configexpire` int(10) unsigned,
  PRIMARY KEY (`id`),
  INDEX USING BTREE (db),
  INDEX USING BTREE (domain),
  INDEX USING BTREE (username),
  INDEX USING BTREE (stop)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- Table with configurations of events and procedures

CREATE TABLE IF NOT EXISTS `config` (
  `name` varchar(80) NOT NULL,
  `value` varchar(80) NOT NULL,
  `description` varchar(250) DEFAULT NULL,
  PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `config` VALUES ('log_history_days','370','Keep log registration for number of days specified.');
INSERT INTO `config` VALUES ('log_history_hourly','8880','Keep log registration of location2_hourly for number of hours specified.');
INSERT INTO `config` VALUES ('log_history_daily','370','Keep log registration of location2_daily for number of days specified.');
INSERT INTO `config` VALUES ('log_history_backup','7200','Backup data after the specified time in seconds.');

-- Procedure that copies information of inactive accounts (older than log_history_backup seconds) from location2_history_active to location2_history_old

DELIMITER |
DROP PROCEDURE IF EXISTS `Proc_Backup_location2_history_active`;
CREATE PROCEDURE `Proc_Backup_location2_history_active` ()
BEGIN
  DECLARE varconfigexpiretime int(10);
  DECLARE varexpiredate datetime;
  DECLARE vardb varchar(15);
  DECLARE vardomain varchar(15);
  DECLARE varusername varchar(80);
  DECLARE varipaddress varchar(15);
  DECLARE varport smallint(5) unsigned;
  DECLARE varuseragent varchar(20);
  DECLARE varstart datetime;
  DECLARE varstop datetime;
  DECLARE varexpires int(10);
  DECLARE varcount int(10);
  
  SET varconfigexpiretime = (SELECT value FROM `statistics`.`config` WHERE name = 'log_history_backup');
  SET varexpiredate       = (SELECT DATE_SUB(NOW(), INTERVAL varconfigexpiretime SECOND));
  SELECT db, domain, username, ipaddress, port, useragent, start, stop, expires, count INTO vardb, vardomain, varusername, varipaddress, varport, varuseragent, varstart, varstop, varexpires, varcount FROM `statistics`.`location2_history_active` WHERE stop < varexpiredate LIMIT 1;
  WHILE varusername IS NOT NULL DO
    INSERT INTO `statistics`.`location2_history_old` (db, domain, username, ipaddress, port, useragent, start, stop, expires, count, configexpire) VALUES (vardb, vardomain, varusername, varipaddress, varport, varuseragent, varstart, varstop, varexpires, varcount, varconfigexpiretime);
    DELETE FROM `statistics`.`location2_history_active` WHERE db = vardb AND domain = vardomain AND username = varusername AND ipaddress = varipaddress AND port = varport;
    SELECT varusername;
    SELECT NULL into varusername;
    SELECT db, domain, username, ipaddress, port, useragent, start, stop, expires, count INTO vardb, vardomain, varusername, varipaddress, varport, varuseragent, varstart, varstop, varexpires, varcount FROM `statistics`.`location2_history_active` WHERE stop < varexpiredate LIMIT 1;
  END WHILE; 
END |
DELIMITER ;

-- Event that calls the backup procedure. It is run every 5 minutes

DELIMITER |
DROP EVENT IF EXISTS `Evt_Backup_location2_history_active`;
CREATE EVENT IF NOT EXISTS `Evt_Backup_location2_history_active` ON SCHEDULE EVERY 5 MINUTE
COMMENT 'Backups data from inactive users on location2_history_active to location2_history_old.'
DO
BEGIN
  CALL Proc_Backup_location2_history_active;
END |
DELIMITER ;

-- Event that deletes data older than log_history_days days from location2_history_old

DELIMITER |
DROP EVENT IF EXISTS `Evt_location2_history_old_Delete`;
CREATE EVENT IF NOT EXISTS `Evt_location2_history_old_Delete` ON SCHEDULE EVERY 1 DAY STARTS '2012-01-01 03:00:00'
COMMENT 'Clears out old information in location2_history_old table during night.'
DO
BEGIN
DELETE FROM location2_history_old WHERE stop < DATE_SUB(NOW(), INTERVAL (SELECT value FROM `config` WHERE name = 'log_history_days') DAY);
END |
DELIMITER ;


-- location2_hourly --

CREATE TABLE IF NOT EXISTS `location2_hourly` (
  `username` varchar(50) NOT NULL,
  `period` datetime NOT NULL,
  PRIMARY KEY (`username`,`period`),
  INDEX USING BTREE (username),
  INDEX USING BTREE (period)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Event that clears out old information (older than log_history_hourly hours) from location2_hourly table

DELIMITER |
DROP EVENT IF EXISTS `Evt_location2_hourly_Delete`;
CREATE EVENT IF NOT EXISTS `Evt_location2_hourly_Delete` ON SCHEDULE EVERY 1 HOUR STARTS '2012-01-01 03:00:00'
COMMENT 'Clears out old information in location2_hourly table.'
DO
BEGIN
DELETE FROM location2_hourly WHERE period < DATE_SUB(NOW(), INTERVAL (SELECT value FROM `config` WHERE name = 'log_history_hourly') HOUR);
END |
DELIMITER ;

-- Procedure and event that inserts all unique users registered in the last 30 minutes in location2_hourly

DELIMITER |
DROP PROCEDURE IF EXISTS `Proc_Insert_location2_hourly`|
CREATE PROCEDURE `Proc_Insert_location2_hourly`()
BEGIN
  DECLARE timeperiod datetime;
  SET timeperiod = DATE_SUB(NOW(), INTERVAL 30 MINUTE);
  SET timeperiod = (SELECT CONCAT(LEFT(timeperiod,14),(RIGHT(LEFT(timeperiod,15),1) DIV 3 * 3),'0:00'));
  INSERT INTO `statistics`.location2_hourly (username, period) 
  SELECT DISTINCT username,timeperiod FROM `statistics`.location2_history_active WHERE stop >= timeperiod; 
END |
DELIMITER ;

DELIMITER |
DROP EVENT IF EXISTS `Evt_location2_hourly_Insert`|
CREATE EVENT `Evt_location2_hourly_Insert` ON SCHEDULE EVERY 30 MINUTE STARTS '2012-01-01 03:00:00'
COMMENT 'Inserts all unique users registered in the last 30 minutes in table location2_hourly.'
DO
BEGIN
  CALL Proc_Insert_location2_hourly ();
END |
DELIMITER ;

-- location2_daily --

CREATE TABLE IF NOT EXISTS `location2_daily` (
  `username` varchar(50) NOT NULL,
  `period` date NOT NULL,
  PRIMARY KEY (`username`,`period`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Event that clears out old information (older than log_history_daily days) from location2_daily table

DELIMITER |
DROP EVENT IF EXISTS `Evt_location2_daily_Delete`;
CREATE EVENT IF NOT EXISTS `Evt_location2_daily_Delete` ON SCHEDULE EVERY 1 DAY STARTS '2012-01-01 03:00:00'
COMMENT 'Clears out old information in location2_daily table during night.'
DO
BEGIN
  DELETE FROM location2_daily WHERE period < DATE_SUB(NOW(), INTERVAL (SELECT value FROM `config` WHERE name = 'log_history_daily') DAY);
END |
DELIMITER ;

-- Procedure and event that inserts all unique users registered in the last day

DELIMITER |
DROP PROCEDURE IF EXISTS `Proc_Insert_location2_daily`|
CREATE PROCEDURE `Proc_Insert_location2_daily`()
BEGIN
  INSERT INTO `statistics`.location2_daily (username, period) 
  SELECT DISTINCT username,DATE(DATE_SUB(NOW(), INTERVAL 1 DAY)) FROM `statistics`.location2_hourly WHERE period >= DATE_SUB(NOW(), INTERVAL 1 DAY); 
END |
DELIMITER ;

DELIMITER |
DROP EVENT IF EXISTS `Evt_location2_daily_Insert`|
CREATE EVENT `Evt_location2_daily_Insert` ON SCHEDULE EVERY 1 DAY STARTS '2012-01-01 00:00:00'
COMMENT 'Inserts all unique users registered in the last day in table location2_daily.'
DO
BEGIN
  CALL Proc_Insert_location2_daily ();
END |
DELIMITER ;






DROP VIEW missing_daily_customers_week;
CREATE VIEW missing_daily_customers_week AS 
SELECT distinct username,period FROM location2_daily WHERE period = DATE(DATE_SUB(NOW(),INTERVAL 2 DAY)) AND username NOT IN (SELECT username FROM location2_daily WHERE period = DATE(DATE_SUB(NOW(),INTERVAL 1 DAY))) UNION
SELECT distinct username,period FROM location2_daily WHERE period = DATE(DATE_SUB(NOW(),INTERVAL 3 DAY)) AND username NOT IN (SELECT username FROM location2_daily WHERE period = DATE(DATE_SUB(NOW(),INTERVAL 2 DAY))) UNION
SELECT distinct username,period FROM location2_daily WHERE period = DATE(DATE_SUB(NOW(),INTERVAL 4 DAY)) AND username NOT IN (SELECT username FROM location2_daily WHERE period = DATE(DATE_SUB(NOW(),INTERVAL 3 DAY))) UNION
SELECT distinct username,period FROM location2_daily WHERE period = DATE(DATE_SUB(NOW(),INTERVAL 5 DAY)) AND username NOT IN (SELECT username FROM location2_daily WHERE period = DATE(DATE_SUB(NOW(),INTERVAL 4 DAY))) UNION
SELECT distinct username,period FROM location2_daily WHERE period = DATE(DATE_SUB(NOW(),INTERVAL 6 DAY)) AND username NOT IN (SELECT username FROM location2_daily WHERE period = DATE(DATE_SUB(NOW(),INTERVAL 5 DAY))) UNION
SELECT distinct username,period FROM location2_daily WHERE period = DATE(DATE_SUB(NOW(),INTERVAL 7 DAY)) AND username NOT IN (SELECT username FROM location2_daily WHERE period = DATE(DATE_SUB(NOW(),INTERVAL 6 DAY))) UNION
SELECT distinct username,period FROM location2_daily WHERE period = DATE(DATE_SUB(NOW(),INTERVAL 8 DAY)) AND username NOT IN (SELECT username FROM location2_daily WHERE period = DATE(DATE_SUB(NOW(),INTERVAL 7 DAY))) ORDER BY USERNAME;

DROP VIEW new_daily_customers_week;
CREATE VIEW new_daily_customers_week AS 
SELECT distinct username,period FROM location2_daily WHERE period = DATE(DATE_SUB(NOW(),INTERVAL 1 DAY)) AND username NOT IN (SELECT username FROM location2_daily WHERE period = DATE(DATE_SUB(NOW(),INTERVAL 2 DAY))) UNION
SELECT distinct username,period FROM location2_daily WHERE period = DATE(DATE_SUB(NOW(),INTERVAL 2 DAY)) AND username NOT IN (SELECT username FROM location2_daily WHERE period = DATE(DATE_SUB(NOW(),INTERVAL 3 DAY))) UNION
SELECT distinct username,period FROM location2_daily WHERE period = DATE(DATE_SUB(NOW(),INTERVAL 3 DAY)) AND username NOT IN (SELECT username FROM location2_daily WHERE period = DATE(DATE_SUB(NOW(),INTERVAL 4 DAY))) UNION
SELECT distinct username,period FROM location2_daily WHERE period = DATE(DATE_SUB(NOW(),INTERVAL 4 DAY)) AND username NOT IN (SELECT username FROM location2_daily WHERE period = DATE(DATE_SUB(NOW(),INTERVAL 5 DAY))) UNION
SELECT distinct username,period FROM location2_daily WHERE period = DATE(DATE_SUB(NOW(),INTERVAL 5 DAY)) AND username NOT IN (SELECT username FROM location2_daily WHERE period = DATE(DATE_SUB(NOW(),INTERVAL 6 DAY))) UNION
SELECT distinct username,period FROM location2_daily WHERE period = DATE(DATE_SUB(NOW(),INTERVAL 6 DAY)) AND username NOT IN (SELECT username FROM location2_daily WHERE period = DATE(DATE_SUB(NOW(),INTERVAL 7 DAY))) UNION
SELECT distinct username,period FROM location2_daily WHERE period = DATE(DATE_SUB(NOW(),INTERVAL 7 DAY)) AND username NOT IN (SELECT username FROM location2_daily WHERE period = DATE(DATE_SUB(NOW(),INTERVAL 8 DAY))) ORDER BY USERNAME;

DROP VIEW missing_total_daily_customers_week;
CREATE VIEW missing_total_daily_customers_week AS 
SELECT period, count(username) as missing FROM location2_daily WHERE period = DATE(DATE_SUB(NOW(),INTERVAL 2 DAY)) AND username NOT IN (SELECT username FROM location2_daily WHERE period = DATE(DATE_SUB(NOW(),INTERVAL 1 DAY))) UNION
SELECT period, count(username) as missing FROM location2_daily WHERE period = DATE(DATE_SUB(NOW(),INTERVAL 3 DAY)) AND username NOT IN (SELECT username FROM location2_daily WHERE period = DATE(DATE_SUB(NOW(),INTERVAL 2 DAY))) UNION
SELECT period, count(username) as missing FROM location2_daily WHERE period = DATE(DATE_SUB(NOW(),INTERVAL 4 DAY)) AND username NOT IN (SELECT username FROM location2_daily WHERE period = DATE(DATE_SUB(NOW(),INTERVAL 3 DAY))) UNION
SELECT period, count(username) as missing FROM location2_daily WHERE period = DATE(DATE_SUB(NOW(),INTERVAL 5 DAY)) AND username NOT IN (SELECT username FROM location2_daily WHERE period = DATE(DATE_SUB(NOW(),INTERVAL 4 DAY))) UNION
SELECT period, count(username) as missing FROM location2_daily WHERE period = DATE(DATE_SUB(NOW(),INTERVAL 6 DAY)) AND username NOT IN (SELECT username FROM location2_daily WHERE period = DATE(DATE_SUB(NOW(),INTERVAL 5 DAY))) UNION
SELECT period, count(username) as missing FROM location2_daily WHERE period = DATE(DATE_SUB(NOW(),INTERVAL 7 DAY)) AND username NOT IN (SELECT username FROM location2_daily WHERE period = DATE(DATE_SUB(NOW(),INTERVAL 6 DAY))) UNION
SELECT period, count(username) as missing FROM location2_daily WHERE period = DATE(DATE_SUB(NOW(),INTERVAL 8 DAY)) AND username NOT IN (SELECT username FROM location2_daily WHERE period = DATE(DATE_SUB(NOW(),INTERVAL 7 DAY)));

DROP VIEW new_total_daily_customers_week;
CREATE VIEW new_total_daily_customers_week AS 
SELECT period, count(username) as new FROM location2_daily WHERE period = DATE(DATE_SUB(NOW(),INTERVAL 1 DAY)) AND username NOT IN (SELECT username FROM location2_daily WHERE period = DATE(DATE_SUB(NOW(),INTERVAL 2 DAY))) UNION
SELECT period, count(username) as new FROM location2_daily WHERE period = DATE(DATE_SUB(NOW(),INTERVAL 2 DAY)) AND username NOT IN (SELECT username FROM location2_daily WHERE period = DATE(DATE_SUB(NOW(),INTERVAL 3 DAY))) UNION
SELECT period, count(username) as new FROM location2_daily WHERE period = DATE(DATE_SUB(NOW(),INTERVAL 3 DAY)) AND username NOT IN (SELECT username FROM location2_daily WHERE period = DATE(DATE_SUB(NOW(),INTERVAL 4 DAY))) UNION
SELECT period, count(username) as new FROM location2_daily WHERE period = DATE(DATE_SUB(NOW(),INTERVAL 4 DAY)) AND username NOT IN (SELECT username FROM location2_daily WHERE period = DATE(DATE_SUB(NOW(),INTERVAL 5 DAY))) UNION
SELECT period, count(username) as new FROM location2_daily WHERE period = DATE(DATE_SUB(NOW(),INTERVAL 5 DAY)) AND username NOT IN (SELECT username FROM location2_daily WHERE period = DATE(DATE_SUB(NOW(),INTERVAL 6 DAY))) UNION
SELECT period, count(username) as new FROM location2_daily WHERE period = DATE(DATE_SUB(NOW(),INTERVAL 6 DAY)) AND username NOT IN (SELECT username FROM location2_daily WHERE period = DATE(DATE_SUB(NOW(),INTERVAL 7 DAY))) UNION
SELECT period, count(username) as new FROM location2_daily WHERE period = DATE(DATE_SUB(NOW(),INTERVAL 7 DAY)) AND username NOT IN (SELECT username FROM location2_daily WHERE period = DATE(DATE_SUB(NOW(),INTERVAL 8 DAY)));

DROP VIEW missing_weekly_customers_week;
CREATE VIEW missing_weekly_customers_week AS 
SELECT WEEK(NOW()) as week,username FROM location2_daily WHERE period >= DATE(DATE_SUB(NOW(),INTERVAL 14 DAY)) AND period < DATE(DATE_SUB(NOW(),INTERVAL 7 DAY)) AND username NOT IN (SELECT username FROM location2_daily WHERE period >= DATE(DATE_SUB(NOW(),INTERVAL 7 DAY))) UNION
SELECT WEEK(DATE(DATE_SUB(NOW(),INTERVAL 7 DAY))),username FROM location2_daily WHERE period >= DATE(DATE_SUB(NOW(),INTERVAL 21 DAY)) AND period < DATE(DATE_SUB(NOW(),INTERVAL 14 DAY)) AND username NOT IN (SELECT username FROM location2_daily WHERE period >= DATE(DATE_SUB(NOW(),INTERVAL 14 DAY)) AND period < DATE(DATE_SUB(NOW(),INTERVAL 7 DAY))) UNION
SELECT WEEK(DATE(DATE_SUB(NOW(),INTERVAL 14 DAY))),username FROM location2_daily WHERE period >= DATE(DATE_SUB(NOW(),INTERVAL 28 DAY)) AND period < DATE(DATE_SUB(NOW(),INTERVAL 21 DAY)) AND username NOT IN (SELECT username FROM location2_daily WHERE period >= DATE(DATE_SUB(NOW(),INTERVAL 21 DAY)) AND period < DATE(DATE_SUB(NOW(),INTERVAL 14 DAY))) UNION
SELECT WEEK(DATE(DATE_SUB(NOW(),INTERVAL 21 DAY))),username FROM location2_daily WHERE period >= DATE(DATE_SUB(NOW(),INTERVAL 35 DAY)) AND period < DATE(DATE_SUB(NOW(),INTERVAL 28 DAY)) AND username NOT IN (SELECT username FROM location2_daily WHERE period >= DATE(DATE_SUB(NOW(),INTERVAL 28 DAY)) AND period < DATE(DATE_SUB(NOW(),INTERVAL 21 DAY)))
ORDER BY USERNAME;

DELIMITER |
DROP PROCEDURE IF EXISTS `Get_missing_daily_customers`|
CREATE PROCEDURE `Get_missing_daily_customers`(IN in_period DATE)
BEGIN
  SELECT period,username FROM location2_daily WHERE period = DATE(in_period) AND username NOT IN (SELECT username FROM location2_daily WHERE period = DATE(DATE_SUB(in_period,INTERVAL 1 DAY)));
END |
DELIMITER ;

DELIMITER |
DROP PROCEDURE IF EXISTS `Get_missing_weekly_customers`|
CREATE PROCEDURE `Get_missing_weekly_customers`(IN in_period DATE)
BEGIN
  DECLARE date1 date;
  DECLARE date2 date;
  SET date1 = DATE(DATE_SUB(in_period,INTERVAL 7 DAY));
  SET date2 = DATE(DATE_SUB(in_period,INTERVAL 14 DAY));
  SELECT max(period),username FROM location2_daily WHERE period >= date2 AND period < date1 AND username NOT IN (SELECT username FROM location2_daily WHERE period >= date1 AND period <= in_period) GROUP BY username ORDER BY max(period),username;
END |
DELIMITER ;

DELIMITER |
DROP PROCEDURE IF EXISTS `Get_missing_monthly_customers`|
CREATE PROCEDURE `Get_missing_monthly_customers`(IN in_period DATE)
BEGIN
  DECLARE date1 date;
  DECLARE date2 date;
  SET date1 = DATE(DATE_SUB(in_period,INTERVAL 30 DAY));
  SET date2 = DATE(DATE_SUB(in_period,INTERVAL 60 DAY));
  SELECT max(period),username FROM location2_daily WHERE period >= date2 AND period < date1 AND username NOT IN (SELECT username FROM location2_daily WHERE period >= date1 AND period <= in_period) GROUP BY username ORDER BY max(period),username;
END |
DELIMITER ;