-- daily_part.sql
-- 
-- MySQL daily partition management by mail@meo.bogliolo.name

-- DDL sample:
--   PARTITION BY RANGE (TO_DAYS(timestamp)) (
--     PARTITION pxPast VALUES LESS THAN (0),
--     PARTITION px20170401 VALUES LESS THAN (TO_DAYS('2017-04-02')),
--     PARTITION px20170402 VALUES LESS THAN (TO_DAYS('2017-04-03')),
-- ...
--     PARTITION px20170430 VALUES LESS THAN (TO_DAYS('2017-05-01')),
--     PARTITION pxFuture   VALUES LESS THAN MAXVALUE);

-- Configuration table sample:
-- create table dp.daily_part (
--   dp_table_name varchar(128) NOT NULL,
--   dp_interval integer NOT NULL);
-- 
-- insert into dp.daily_part(dp_table_name, dp_interval) values
--   ('my_user.my_partitioned_table1', 62), ('my_user.my_partitioned_table2', 7);

-- Crontab sample:
-- 30  21  *  *  *  cd /root/dp; mysql -uroot -pXXX -AN < daily_part.sql | mysql -uroot -pXXX -ANfvvv > daily_part.`date +\%Y\%m\%d`.log 2>&1

-- Check:
-- select concat(table_schema,'.',table_name), count(*), min(partition_name) from_p, max(partition_name) to_p, sum(table_rows)
--  from information_schema.partitions
--  where partition_name is not null
--    and partition_name not in('pxPast', 'pxFuture')
--  group by table_name;

select concat('-- BEGIN Daily partition management ', now());
select 'select now();';

select concat('ALTER TABLE ', dp_table_name, ' DROP PARTITION px',
              DATE_FORMAT(DATE_SUB(now(), INTERVAL dp_interval+1 DAY),'%Y%m%d'), ';')
  from dp.daily_part;

select concat('ALTER TABLE ', dp_table_name, ' REORGANIZE PARTITION pxFuture INTO (PARTITION px',
              DATE_FORMAT(DATE_ADD(now(), INTERVAL 1 DAY),'%Y%m%d'), ' VALUES LESS THAN (TO_DAYS(\'',
              DATE_FORMAT(DATE_ADD(now(), INTERVAL 2 DAY),'%Y-%m-%d'), '\')), PARTITION pxFuture VALUES LESS THAN MAXVALUE);')
  from dp.daily_part;

select '-- END partition management ';
select 'select now();';