CREATE TABLE `radacct` (
  `radacctid` bigint(21) NOT NULL AUTO_INCREMENT,
  `acctsessionid` varchar(64) NOT NULL DEFAULT '',
  `acctuniqueid` varchar(32) NOT NULL DEFAULT '',
  `username` varchar(64) NOT NULL DEFAULT '',
  `groupname` varchar(64) NOT NULL DEFAULT '',
  `realm` varchar(64) DEFAULT '',
  `nasipaddress` varchar(15) NOT NULL DEFAULT '',
  `nasportid` varchar(15) DEFAULT NULL,
  `nasporttype` varchar(32) DEFAULT NULL,
  `acctstarttime` datetime DEFAULT NULL,
  `acctstoptime` datetime DEFAULT NULL,
  `acctsessiontime` int(12) DEFAULT NULL,
  `acctauthentic` varchar(32) DEFAULT NULL,
  `connectinfo_start` varchar(50) DEFAULT NULL,
  `connectinfo_stop` varchar(50) DEFAULT NULL,
  `acctinputoctets` bigint(20) DEFAULT NULL,
  `acctoutputoctets` bigint(20) DEFAULT NULL,
  `calledstationid` varchar(50) NOT NULL DEFAULT '',
  `callingstationid` varchar(50) NOT NULL DEFAULT '',
  `acctterminatecause` varchar(32) NOT NULL DEFAULT '',
  `servicetype` varchar(32) DEFAULT NULL,
  `framedprotocol` varchar(32) DEFAULT NULL,
  `framedipaddress` varchar(15) NOT NULL DEFAULT '',
  `acctstartdelay` int(12) DEFAULT NULL,
  `acctstopdelay` int(12) DEFAULT NULL,
  `xascendsessionsvrkey` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`radacctid`),
  KEY `username` (`username`),
  KEY `framedipaddress` (`framedipaddress`),
  KEY `acctsessionid` (`acctsessionid`),
  KEY `acctsessiontime` (`acctsessiontime`),
  KEY `acctuniqueid` (`acctuniqueid`),
  KEY `acctstarttime` (`acctstarttime`),
  KEY `acctstoptime` (`acctstoptime`),
  KEY `nasipaddress` (`nasipaddress`)
) ENGINE=InnoDB AUTO_INCREMENT=196 DEFAULT CHARSET=utf8

/*!50100 PARTITION BY RANGE (to_days(acctstarttime))
(PARTITION p201408 VALUES LESS THAN (735842) ENGINE = InnoDB,
 PARTITION p201409 VALUES LESS THAN (735872) ENGINE = InnoDB,
 PARTITION p201410 VALUES LESS THAN (735903) ENGINE = InnoDB,
 PARTITION p201411 VALUES LESS THAN (735933) ENGINE = InnoDB,
 PARTITION p201412 VALUES LESS THAN (735964) ENGINE = InnoDB,
 PARTITION pEOW VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */;




DELIMITER $$

USE `freeradius`$$

DROP PROCEDURE IF EXISTS `rotate_radacct_partitions`$$

CREATE DEFINER=`root`@`150.237.170.151` PROCEDURE `rotate_radacct_partitions`(IN p_retention_months INT)
BEGIN
   /*
    * Script to archive table partitions. Creates new partitions and drops old ones
    * We select from information schema, and use a whole lot of dynamic sql. 
    */
   DECLARE l_cutoff_date	   BIGINT(20);
   DECLARE l_table_name		   VARCHAR(100);
   DECLARE l_new_partitions    VARCHAR(200);
   DECLARE l_old_partitions    VARCHAR(200);
   DECLARE sql_make_partitions VARCHAR(1000);
   DECLARE sql_drop_partitions VARCHAR(1000);
   
   DECLARE done 			INT 		DEFAULT 0;                                      
   DECLARE c_table_name CURSOR FOR SELECT a.table_name
                                     FROM information_schema.tables a,
                                          information_schema.columns b
                                    WHERE a.table_name = b.table_name
                                      AND a.engine = 'INNODB'
                                      AND a.table_name = 'radacct_sharaz'
                                      AND b.table_schema = 'freeradius'
                                      AND b.column_name = 'acctstarttime';
   DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
   -- if p_retention_months is null, we keep 12 months
   IF (p_retention_months IS NULL) THEN
     SET p_retention_months = 12;
   END IF;
   -- get the cutoff date - this is p_retention_months from midnight
   SELECT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL p_retention_months MONTH),'%Y%m')
     INTO l_cutoff_date;
     
   -- temporary table holds potential new partition names (date_string) and values less than clause (date_limit)
   DROP TEMPORARY TABLE IF EXISTS `freeradius`.`tmp_partition_months`;
   CREATE TEMPORARY TABLE `freeradius`.`tmp_partition_months` AS
      SELECT DATE_FORMAT(CURDATE(),'%Y%m') date_string, 
             TO_DAYS(DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 1 MONTH),'%Y%m01')) date_limit
       UNION
      SELECT DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 1 MONTH),'%Y%m') date_string, 
             TO_DAYS(DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 2 MONTH),'%Y%m01')) date_limit
       UNION
      SELECT DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 2 MONTH),'%Y%m') date_string, 
             TO_DAYS(DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 3 MONTH),'%Y%m01')) date_limit
       UNION
      SELECT DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 3 MONTH),'%Y%m') date_string, 
             TO_DAYS(DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 4 MONTH),'%Y%m01')) date_limit;
   -- open cursor
   OPEN c_table_name;
   REPEAT
      FETCH c_table_name INTO l_table_name;
      IF NOT done THEN
      SELECT NULL INTO l_new_partitions;
      
      -- generate list of new partitions to create
      SELECT GROUP_CONCAT(CONCAT('PARTITION p',a.date_string,' VALUES LESS THAN (',a.date_limit,')'))
        INTO l_new_partitions
        FROM tmp_partition_months a
       WHERE a.date_string NOT IN (SELECT RIGHT(partition_name,6) 
                                     FROM information_schema.partitions 
                                    WHERE table_name = l_table_name)
       ORDER BY a.date_string ASC;
      IF (l_new_partitions IS NOT NULL) THEN
         -- create new partitions by reorganizing the last partition (always pEOW)
         SET sql_make_partitions = CONCAT('ALTER TABLE `',l_table_name,'` REORGANIZE PARTITION pEOW INTO (',l_new_partitions,', PARTITION pEOW VALUES LESS THAN MAXVALUE)');
         SET @sqlstatement = sql_make_partitions;
         SELECT @sqlstatement;
         PREPARE sqlquery FROM @sqlstatement;
         EXECUTE sqlquery;
         DEALLOCATE PREPARE sqlquery;
         
      END IF;
        
     
      -- find and drop partitions older than p_retention_months
      SELECT GROUP_CONCAT(DISTINCT partition_name)
        INTO l_old_partitions
        FROM information_schema.partitions 
       WHERE RIGHT(partition_name,6) < l_cutoff_date
         AND partition_name <> 'pEOW'
         AND table_name = l_table_name;
         
      IF (l_old_partitions IS NOT NULL) THEN
         
         SET sql_drop_partitions = CONCAT('ALTER TABLE `',l_table_name,'` DROP PARTITION ',l_old_partitions);
         SET @sqlstatement = sql_drop_partitions;
         PREPARE sqlquery FROM @sqlstatement;
         EXECUTE sqlquery;
         DEALLOCATE PREPARE sqlquery;
      END IF;
      
      -- close cursor
      END IF;
      UNTIL done END REPEAT;
   CLOSE c_table_name;
END$$

DELIMITER ;






DELIMITER $$

CREATE  EVENT `e_rotate_radacct_partitions` ON SCHEDULE EVERY 1 MONTH STARTS '2014-10-01 02:07:00' ON COMPLETION NOT PRESERVE ENABLE DO CALL access_logs.rotate_radacct_partitions(12)$$

DELIMITER ;