/*
SQLyog Community v11.2 Beta1 (32 bit)
MySQL - 5.6.12-log : Database - zabbix
*********************************************************************
*/


/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`zabbix` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_bin */;

USE `zabbix`;

/* Procedure structure for procedure `create_next_partitions` */

/*!50003 DROP PROCEDURE IF EXISTS  `create_next_partitions` */;

DELIMITER $$

/*!50003 CREATE DEFINER=`root`@`%` PROCEDURE `create_next_partitions`(SCHEMANAME varchar(64), TABLENAME varchar(64))
BEGIN
  DECLARE NEXTCLOCK timestamp;
  DECLARE PARTITIONNAME varchar(16);
  DECLARE CLOCK int;
  SET @totaldays = 14;
  SET @i = 1;
  createloop: LOOP
	  SET NEXTCLOCK = DATE_ADD(NOW(),INTERVAL @i DAY);
	  SET PARTITIONNAME = DATE_FORMAT( NEXTCLOCK, 'p%Y%m%d' );
	  SET CLOCK = UNIX_TIMESTAMP(DATE_FORMAT(DATE_ADD( NEXTCLOCK ,INTERVAL 1 DAY),'%Y-%m-%d 00:00:00'));
	  CALL zabbix.create_partition( SCHEMANAME, TABLENAME, PARTITIONNAME, CLOCK );
	  SET @i=@i+1;
	  IF @i > @totaldays THEN
		  LEAVE createloop;
	  END IF;
  END LOOP;
END */$$
DELIMITER ;

/* Procedure structure for procedure `create_partition` */

/*!50003 DROP PROCEDURE IF EXISTS  `create_partition` */;

DELIMITER $$

/*!50003 CREATE DEFINER=`root`@`%` PROCEDURE `create_partition`(SCHEMANAME varchar(64), TABLENAME varchar(64), PARTITIONNAME varchar(64), CLOCK int)
BEGIN
  DECLARE RETROWS int;
  SELECT COUNT(1) INTO RETROWS
	  FROM information_schema.partitions
	  WHERE table_schema = SCHEMANAME AND table_name = TABLENAME AND partition_name = PARTITIONNAME;

  IF RETROWS = 0 THEN
	  SELECT CONCAT( "create_partition(", SCHEMANAME, ",", TABLENAME, ",", PARTITIONNAME, ",", CLOCK, ")" ) AS msg;
	  SET @sql = CONCAT( 'ALTER TABLE ', SCHEMANAME, '.', TABLENAME,
			  ' ADD PARTITION (PARTITION ', PARTITIONNAME, ' VALUES LESS THAN (', CLOCK, '));' );
	  PREPARE STMT FROM @sql;
	  EXECUTE STMT;
	  DEALLOCATE PREPARE STMT;
  END IF;
END */$$
DELIMITER ;

/* Procedure structure for procedure `create_zabbix_partitions` */

/*!50003 DROP PROCEDURE IF EXISTS  `create_zabbix_partitions` */;

DELIMITER $$

/*!50003 CREATE DEFINER=`root`@`%` PROCEDURE `create_zabbix_partitions`()
BEGIN
  CALL zabbix.create_next_partitions("zabbix","history");
  CALL zabbix.drop_old_partitions("zabbix","history");
  CALL zabbix.create_next_partitions("zabbix","history_log");
  CALL zabbix.drop_old_partitions("zabbix","history_log");
  CALL zabbix.create_next_partitions("zabbix","history_str");
  CALL zabbix.drop_old_partitions("zabbix","history_str");
  CALL zabbix.create_next_partitions("zabbix","history_text");
  CALL zabbix.drop_old_partitions("zabbix","history_text");
  CALL zabbix.create_next_partitions("zabbix","history_uint");
  CALL zabbix.drop_old_partitions("zabbix","history_uint");
  CALL zabbix.create_next_partitions("zabbix","trends");
  CALL zabbix.create_next_partitions("zabbix","trends_uint");
END */$$
DELIMITER ;

/* Procedure structure for procedure `drop_old_partitions` */

/*!50003 DROP PROCEDURE IF EXISTS  `drop_old_partitions` */;

DELIMITER $$

/*!50003 CREATE DEFINER=`root`@`%` PROCEDURE `drop_old_partitions`(SCHEMANAME varchar(64), TABLENAME varchar(64))
BEGIN
  DECLARE OLDCLOCK timestamp;
  DECLARE PARTITIONNAME varchar(16);
  DECLARE CLOCK int;
  SET @mindays = 30;
  SET @maxdays = @mindays+30;
  SET @i = @maxdays;
  droploop: LOOP
	  SET OLDCLOCK = DATE_SUB(NOW(),INTERVAL @i DAY);
	  SET PARTITIONNAME = DATE_FORMAT( OLDCLOCK, 'p%Y%m%d' );
	  CALL zabbix.drop_partition( SCHEMANAME, TABLENAME, PARTITIONNAME );
	  SET @i=@i-1;
	  IF @i <= @mindays THEN
		  LEAVE droploop;
	  END IF;
  END LOOP;
END */$$
DELIMITER ;

/* Procedure structure for procedure `drop_partition` */

/*!50003 DROP PROCEDURE IF EXISTS  `drop_partition` */;

DELIMITER $$

/*!50003 CREATE DEFINER=`root`@`%` PROCEDURE `drop_partition`(SCHEMANAME varchar(64), TABLENAME varchar(64), PARTITIONNAME varchar(64))
BEGIN
  DECLARE RETROWS int;
  SELECT COUNT(1) INTO RETROWS
	  FROM information_schema.partitions
	  WHERE table_schema = SCHEMANAME AND table_name = TABLENAME AND partition_name = PARTITIONNAME;

  IF RETROWS = 1 THEN
	  SELECT CONCAT( "drop_partition(", SCHEMANAME, ",", TABLENAME, ",", PARTITIONNAME, ")" ) AS msg;
	  SET @sql = CONCAT( 'ALTER TABLE ', SCHEMANAME, '.', TABLENAME,
			  ' DROP PARTITION ', PARTITIONNAME, ';' );
	  PREPARE STMT FROM @sql;
	  EXECUTE STMT;
	  DEALLOCATE PREPARE STMT;
  END IF;
END */$$
DELIMITER ;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;