use strict;
use DBIx::DWIW;    # CPAN
use Getopt::Std;
use Data::Dumper;

# Reference Implementation of PARTITION Maintenance.

# This demonstrates, in working code, a sliding set of several partitions
# and how to drop and add as time goes on.

# Two calling methods:
#   * Drop and Create a table with some partitions.
#      demo_part_maint.pl -x -c -d dbname -t tblname
#   * Perform daily maintenance.
#      demo_part_maint.pl -s -d dbname -t tblname -k keep
# Each displays the resulting set of partitions.

# Assumptions and notes:
#   * There is a mysql running and a use with sufficient privs (see connection code)
#   * The goal is to have at least 4 days' worth of data.
#   * Other time amounts are discussed.
#   * Non-daily (eg, hourly) partitions is only briefly mentioned.
#   * This can be run via cron, or manually.
#   * Extra runs are harmless (it protects itself).

# Rick James, Oct, 2012

my %opt;
getopts('xcsd:t:k:v', \%opt) or die Usage();
my $do_drop   = $opt{x} || undef;    # Action: DROP demo table
my $do_create = $opt{c} || undef;    # Action: CREATE demo table
my $do_shift  = $opt{s} || undef;    # Action: Shift: drop&add partitions if time to do so
my $dbname    = $opt{d} || undef;    # Database name
my $tblname   = $opt{t} || undef;    # Table name
my $keep_days = $opt{k} || undef;    # Number of days (not periods) to keep
my $verbose   = $opt{v} || not $ENV{CRON};  # Show debugging except when run by cron

die Usage()  if not ($dbname and $tblname);

# Assuming:
#   CREATE DATABASE PmDemo;
#   GRANT ALL ON PmDemo.* TO 'pm_demo'@'localhost' IDENTIFIED BY 'pm_demo';
my $db = DBIx::DWIW->Connect(Host => 'localhost', DB => $dbname,
                             User=>'pm_demo', Pass=>'pm_demo'
                            )
                        or die "Connection trouble?  $@";

if ($do_drop)
{
    DoDropTable($tblname);
}
if ($do_create)
{
    DoCreate($tblname);
}
if ($do_shift)
{
    die Usage()  if not $keep_days;
    my $parts = GatherPartitionInfo($dbname, $tblname);
    my $today = $db->Scalar(qq{SELECT TO_DAYS(CURRENT_DATE())});
    die $@ if $@;
    DoAddPartition($parts,  $tblname, $today + 1);   # Create next daily, but not beyond tomorrow.
    DoDropPartition($parts, $tblname, $today - $keep_days);  # Drop oldest, if old enough
}
ShowPartitions($dbname, $tblname);

exit;

#-----------------------------
sub Usage
{
    print <<'USAGE';
One-time Set up of Database:
    CREATE DATABASE PmDemo;
    GRANT ALL ON PmDemo.* TO 'pm_demo'@'localhost' IDENTIFIED BY 'pm_demo';
Create sample table:
    demo_part_maint.pl -d PmDemo -t Tbl -c
Shift partitions by 1 day (won't do much yet):
    demo_part_maint.pl -d PmDemo -t Tbl -s -k 4
Repeat; eventually you will have 6 days and it won't budge until tomorrow.
Start over (-x drops the table):
    demo_part_maint.pl -d PmDemo -t Tbl -x -c
USAGE
}

#-----------------------------
sub DoDropTable
{
    my ($tblname) = @_;
    print STDERR "About to DROP TABLE $tblname, press return, or abort "; <STDIN>;
    my $sql = qq{DROP TABLE IF EXISTS $tblname};
    print "$sql\n";
    $db->Execute($sql);
    die $@ if $@;
}

#-----------------------------
sub DoCreate
{
    my ($tblname) = @_;
    # For demo purposes, create one partition for 5 days ago
    # It will soon be DROPped.

    # Build useful partition name:
    my $ago4 = $db->Scalar(qq{SELECT CURRENT_DATE() - INTERVAL 6 DAY});
    die $@ if $@;
    my $yyyymmdd = $ago4;
    $yyyymmdd =~ s{\D}{}g;
    # For the "LESS THAN VALUE", use the next day:
    my $ago3 = $db->Scalar(qq{SELECT CURRENT_DATE() - INTERVAL 5 DAY});
    die $@ if $@;
    my $partition_def = "PARTITION from$yyyymmdd VALUES LESS THAN (TO_DAYS('$ago3'))";
    # print "Partition definition: $partition_def\n";

    my $sql = qq{
    CREATE TABLE $tblname (
        dt DATETIME NOT NULL
        )
    PARTITION BY RANGE (TO_DAYS(dt)) (
        PARTITION start        VALUES LESS THAN (0),
        $partition_def,
        PARTITION future       VALUES LESS THAN MAXVALUE
    )};
    print "$sql\n";
    $db->Execute($sql);
    die $@ if $@;
}

# Drop oldest, if old enough
# (This should work, as is, for 'weekly' partitions.)
# $ago is the TO_DAYS() for earliest day to keep.
#-----------------------------
sub DoDropPartition
{
    my ($parts, $tblname, $ago) = @_;
    # This demo code depends on the PARTITION_DESCRIPTION being "days" (from TO_DAYS()).

    return if @$parts < 4;  # bulletproofing

    # Note:  $parts[1] is 'start'; $parts[2] is the one to consider dropping
    my $pname   = $parts->[2]{PARTITION_NAME};
    my $to_toss = $parts->[2]{PARTITION_DESCRIPTION};
    die $@ if $@;
     print "$ago : $to_toss\n";
    return  if $ago < $to_toss;   # partitions do not go back far enough

    my $sql = qq{ALTER TABLE $tblname
                    DROP PARTITION $pname};
    print "$sql\n";
    $db->Execute($sql);
    die $@ if $@;
}

# Create next daily, but not beyond tomorrow.
#-----------------------------
sub DoAddPartition
{
    my ($parts, $tblname, $tomorrow) = @_;
    # We depend on the PARTITION_DESCRIPTION being "days" (from TO_DAYS()).
    # print "TO_DAYS(tomorrow) = $tomorrow\n";

    # First check 'tomorrow' already existing
    # Note:  [-1] is the "future" partition,
    #        [-2] is the last 'real' partition
    my $thru = $parts->[-2]{PARTITION_DESCRIPTION};
    return  if $thru > $tomorrow;    # There is already a non-'future' partition covering tomorrow

    my $ymd = $db->Scalar(qq{SELECT FROM_DAYS($thru)});
    $ymd =~ s{\D}{}g;
    $thru += 1;       # (+=1 for daily; +=7 for weekly; etc)

    Reorg($tblname,
            [ 'future' ],         # Split this one partition ('future') into two:
            [["from$ymd" => $thru],
             ['future' => 'MAXVALUE']]);   # and a new 'future' partition
}

# Generic Helper routine.
# Needed because information_schema is so slow.
# Returns arrayref of hashes
#   [0] not used
#   [1] is 'start' partition
#   [2]..[-2] are 'real' partitions
#   [-1] is 'future' partition
#-----------------------------
sub GatherPartitionInfo
{
    my ($dbname, $tbl) = @_;
    my $sql = qq{SELECT  *
                    FROM information_schema.PARTITIONS
                    WHERE TABLE_SCHEMA = ?
                      AND TABLE_NAME = ?};
    my @rows = $db->Hashes($sql, $dbname, $tbl);
    die $@ if $@;
    # This should be redundant, but just in case, let's put them into a correctly ordered array:
    my @arr;
    map { $arr[$_->{PARTITION_ORDINAL_POSITION}] = $_ } @rows;
    return \@arr;
}

# Generic Helper routine -- constructs and runs ALTER TABLE ... REORGANIZE.
# $from is arrayref of partition name(s) being replaced.
# $to is arrayref of arrayref(s) like [name,limit] of new partitions.
# (This code assumes you have at least 5.1.4, since it may reuse partition names.)
#-----------------------------
sub Reorg
{
    my ($table, $from, $to) = @_;
    my $froms = join(",\n        ", @$from);
    my @tos;
    for my $pair (@$to)
    {
        my ($name, $bound) = @$pair;
        $bound = "($bound)"  if $bound ne 'MAXVALUE';
        push @tos, "    PARTITION $name VALUES LESS THAN $bound";
    }
    my $tos = join(",\n    ", @tos);
    # $froms is one or more partition names;
    # $tos is one or more partition definitions (name + LESS THAN).
    my $alter = qq{ALTER TABLE $table REORGANIZE PARTITION
        $froms
INTO (
    $tos
)
};
    print STDERR $alter;
    $db->Execute($alter);
    die $@ if $@;
}

#-----------------------------
sub ShowPartitions
{
    my ($dbname, $tblname) = @_;
    my $parts = GatherPartitionInfo($dbname, $tblname);
    for my $j (1..@$parts-1)
    {
        my $p = $parts->[$j];
        printf "%3d  %-25s %9s %9d %9d\n",
            $p->{PARTITION_ORDINAL_POSITION},
            $p->{PARTITION_NAME},
            $p->{PARTITION_DESCRIPTION},
            $p->{DATA_LENGTH},
            $p->{INDEX_LENGTH};
    }
}