#!/usr/bin/python
# coding: utf-8

import sys
import math
import MySQLdb
from pprint import pprint
from optparse import OptionParser

parser = OptionParser()
parser.add_option("-s", type="string", dest="server", default="127.0.0.1")
parser.add_option("-p", type="int", dest="port", default="3306")
parser.add_option("-r", type="int", dest="rate", default="1000"
        , help="how many logdata to convert in an one loop.")


(opt, argv) = parser.parse_args()


dbArgs = {"host": opt.server
        , "port" : opt.port
        , "db": "Syslog"
        , "user": "syslog"
        , "passwd": "syslog"
        }

####################
# switch to New table
####################
with MySQLdb.connect(**dbArgs) as cursor:
    cursor.execute("ALTER TABLE SystemEvents RENAME to oldSystemEvents")
    cursor.execute('''\
CREATE TABLE SystemEvents
(
        ID int unsigned not null auto_increment primary key,
        CustomerID bigint,
        ReceivedAt datetime NULL,
        DeviceReportedTime datetime NULL,
        Facility smallint NULL,
        Priority smallint NULL,
        FromHost varchar(60) NULL,
        Message text CHARACTER SET latin1 COLLATE latin1_ip_mac_ci,
        NTSeverity int NULL,
        Importance int NULL,
        EventSource varchar(60),
        EventUser varchar(60) NULL,
        EventCategory int NULL,
        EventID int NULL,
        EventBinaryData text NULL,
        MaxAvailable int NULL,
        CurrUsage int NULL,
        MinUsage int NULL,
        MaxUsage int NULL,
        InfoUnitID int NULL ,
        SysLogTag varchar(60),
        EventLogType varchar(60),
        GenericFileName VarChar(60),
        SystemID int NULL,
        FULLTEXT INDEX(`Message`)
) ENGINE=MyISAM''')


############
# move data
############

db = MySQLdb.connect(**dbArgs)
db.autocommit(False)
cur = db.cursor()
cur.execute("select count(*) from oldSystemEvents")
count = cur.fetchall()[0][0]

try:
    for i in range(0, count / opt.rate + 1):
        params = ()
        db.begin()

        cur.execute("""\
select
 CustomerID,
 ReceivedAt,
 DeviceReportedTime,
 Facility,
 Priority,
 FromHost,
 Message,
 NTSeverity,
 Importance,
 EventSource,
 EventUser,
 EventCategory,
 EventID,
 EventBinaryData,
 MaxAvailable,
 CurrUsage,
 MinUsage,
 MaxUsage,
 InfoUnitID,
 SysLogTag,
 EventLogType,
 GenericFileName,
 SystemID
 from oldSystemEvents LIMIT {}""".format(opt.rate))
        result = cur.fetchall()

        insertSQL = """\
insert into SystemEvents(
 CustomerID,
 ReceivedAt,
 DeviceReportedTime,
 Facility,
 Priority,
 FromHost,
 Message,
 NTSeverity,
 Importance,
 EventSource,
 EventUser,
 EventCategory,
 EventID,
 EventBinaryData,
 MaxAvailable,
 CurrUsage,
 MinUsage,
 MaxUsage,
 InfoUnitID,
 SysLogTag,
 EventLogType,
 GenericFileName,
 SystemID
) values"""

        # RATEオプションのぶんだけいっぺんにインサートする
        # 23カラム
        for i in range(0, len(result)):
            insertSQL += """\
(
 %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,
 %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,
 %s, %s, %s
),"""
            params += result[i]

        # 最後のカンマは外す
        cur.execute(insertSQL[:-1], params)

        cur.execute("delete from oldSystemEvents LIMIT {}".format(opt.rate))
        db.commit()
except:
    db.rollback()
    raise
    sys.exit(1)

cur.execute("drop table oldSystemEvents")
sys.exit(0)