select-distinct-from-mttemplate.pl

#!/usr/bin/perl -w
use strict;
use warnings;
use Date::Simple;
use DBI;


# Buckup
# ------
my $today1 = Date::Simple::today();
my $today2 = $today1->format("%Y%m");
my $today3 = $today1->format("%y%m%d");
my @gmt = gmtime();
my $gmt;
my $output = 'd:\\log\\'.$today2.'\\'.$today3.'_000_bkup_mt_placement_'.$gmt[2].'.sql';


# DBI
# ---
my $datasource = 'DBI:mysql:mtbook:localhost:3306';
my $user = 'mtbook';
my $pass = 'mtbook';
my $statement;
my $sth;
my $rv;
my $dbh;

$dbh= DBI->connect($datasource, $user, $pass) or die $DBI::errstr;

# $statement = 'BACKUP TABLE `mt_placement` TO \''.$output.'\'';
# $sth = $dbh->prepare($statement) or die $dbh->errstr();
# $rv = $sth->execute() or die $sth->errstr();
$statement = 'DROP TABLE IF EXISTS `tmp_mt_placement`';
$sth = $dbh->prepare($statement) or die $dbh->errstr();
$rv = $sth->execute() or die $sth->errstr();
$statement = 'CREATE TABLE `tmp_mt_placement` SELECT `placement_blog_id` , `placement_category_id` , `placement_entry_id` , `placement_is_primary` FROM mt_placement';
$sth = $dbh->prepare($statement) or die $dbh->errstr();
$rv = $sth->execute() or die $sth->errstr();
$statement = 'TRUNCATE table `mt_placement`';
$sth = $dbh->prepare($statement) or die $dbh->errstr();
$rv = $sth->execute() or die $sth->errstr();
$statement = 'INSERT INTO `mt_placement`(`placement_blog_id`, `placement_category_id`, `placement_entry_id`, `placement_is_primary`) SELECT DISTINCT `placement_blog_id`, `placement_category_id`, `placement_entry_id`, `placement_is_primary` FROM `tmp_mt_placement`';
$sth = $dbh->prepare($statement) or die $dbh->errstr();
$rv = $sth->execute() or die $sth->errstr();

# $dbh->disconnect();


exit;