source: people/peter.buschman/backup_monitoring/backmon/commands/db/_db.py

#!/usr/bin/python26
###
### backmon.commands.db
###

import sys
import os
import os.path
import glob
import re
import datetime
import pytz
import dateutil
import MySQLdb

from optparse import OptionParser

from ...lib import *
from ....dal.builtins import *
from ....datetime import *

import subcommands

SUBCOMMAND_LIST = [
    'test',
    'update',
]

SUBCOMMAND_HELP = {
    'test'      : 'test database connectivity',
    'update'    : 'update database with current data',
}

##
## OPTIONS
##
usage = """Usage: %prog [options] db [subcommand] [options] [arguments]'

  available subcommands:

""" + '\n'.join(['  %-12s       %s' % (cmd, SUBCOMMAND_HELP[cmd]) for cmd in SUBCOMMAND_LIST])

parser = OptionParser(usage=usage)
parser.add_option('-n', '--now', action='store', default='now', help='override now for updates')

def run(args, kwargs):

    #
    # add kwargs to local namespace
    #
    for key in kwargs.keys():

        if re.compile('^[A-Z][A-Z_]+$').match(key):
            exec(key + ' = kwargs[\'' + key + '\']')

    #
    # connect to database
    #
    DEBUG('DATABASE_ENGINE....: %s' % (DATABASE_ENGINE))
    DEBUG('DATABASE_NAME......: %s' % (DATABASE_NAME))
    DEBUG('DATABASE_USER......: %s' % (DATABASE_USER))
    DEBUG('DATABASE_PASSWORD..: %s' % (DATABASE_PASSWORD))
    DEBUG('DATABASE_HOST......: %s' % (DATABASE_HOST))
    DEBUG('DATABASE_PORT......: %s' % (DATABASE_PORT))

    try:

        CONN = MySQLdb.connect(host=DATABASE_HOST, user=DATABASE_USER, passwd=DATABASE_PASSWORD, db=DATABASE_NAME)
        DEBUG('Connected to %s database.' % (DATABASE_NAME))

    except Exception, e:

        ERROR('Could not connect to %s database! %s' % (DATABASE_NAME, e))
        sys.exit(1)


    SUBCOMMAND=None
    subcommand_pos = 0

    i = 0

    for arg in args:

        i += 1

        if arg in SUBCOMMAND_LIST:
            SUBCOMMAND = arg
            subcommand_pos = i
            break

    subcommand_args = args[subcommand_pos:]

    if SUBCOMMAND:

        (options, args) = parser.parse_args(args[:subcommand_pos])

        #
        # timestamps
        #
        TZ = pytz.timezone(one_value(CONN.cursor(), "SELECT value FROM settings WHERE name = 'timezone'"))
        UTC = pytz.timezone('UTC')

        BOT = dateutil.parser.parse(one_value(CONN.cursor(), "SELECT value FROM settings WHERE name = 'bot'")).replace(tzinfo=TZ)
        DEBUG('BOT = %s' % (BOT))

        BOT_ISO = BOT.strftime('%Y-%m-%d %H:%M:%S')
        DEBUG('BOT_ISO = %s' % (BOT_ISO))

        if options.now == 'now':
            NOW = datetime.datetime.now(TZ)
        else:
            NOW = dateutil.parser.parse(options.now).replace(tzinfo=TZ)

        DEBUG('NOW = %s' % (NOW))

        UTCNOW = NOW.astimezone(UTC)
        DEBUG('UTCNOW = %s' % (UTCNOW))

        UTCNOW_ISO = UTCNOW.strftime('%Y-%m-%d %H:%M:%S')
        DEBUG('UTCNOW_ISO = %s' % (UTCNOW_ISO))

        #TODAY = NOW.replace(hour=0, minute=0, second=0, microsecond=0, tzinfo=None)
        TODAY = NOW.replace(hour=0, minute=0, second=0, microsecond=0)
        DEBUG('TODAY = %s' % (TODAY))

        TODAY_ISO = TODAY.strftime('%Y-%m-%d %H:%M:%S')
        DEBUG('TODAY_ISO = %s' % (TODAY_ISO))

        TOMORROW = TODAY + datetime.timedelta(days=1)
        DEBUG('TOMORROW = %s' % (TOMORROW))

        TOMORROW_ISO = TOMORROW.strftime('%Y-%m-%d %H:%M:%S')
        DEBUG('TOMORROW_ISO = %s' % (TOMORROW_ISO))

        YESTERDAY = TODAY - datetime.timedelta(days=1)
        DEBUG('YESTERDAY = %s' % (YESTERDAY))

        YESTERDAY_ISO = YESTERDAY.strftime('%Y-%m-%d %H:%M:%S')
        DEBUG('YESTERDAY_ISO = %s' % (YESTERDAY_ISO))

        CURR_BACKUP_DAY = backup_day(NOW)
        DEBUG('CURR_BACKUP_DAY = %s' % (CURR_BACKUP_DAY))

        CURR_BACKUP_DAY_ISO = CURR_BACKUP_DAY.strftime('%Y-%m-%d %H:%M:%S')
        DEBUG('CURR_BACKUP_DAY_ISO = %s' % (CURR_BACKUP_DAY_ISO))

        NEXT_BACKUP_DAY = CURR_BACKUP_DAY + datetime.timedelta(days=1)
        DEBUG('NEXT_BACKUP_DAY = %s' % (NEXT_BACKUP_DAY))

        NEXT_BACKUP_DAY_ISO = NEXT_BACKUP_DAY.strftime('%Y-%m-%d %H:%M:%S')
        DEBUG('NEXT_BACKUP_DAY_ISO = %s' % (NEXT_BACKUP_DAY_ISO))

        PREV_BACKUP_DAY = CURR_BACKUP_DAY - datetime.timedelta(days=1)
        DEBUG('PREV_BACKUP_DAY = %s' % (PREV_BACKUP_DAY))

        PREV_BACKUP_DAY_ISO = PREV_BACKUP_DAY.strftime('%Y-%m-%d %H:%M:%S')
        DEBUG('PREV_BACKUP_DAY_ISO = %s' % (PREV_BACKUP_DAY_ISO))

        TIMESTAMP = NOW.replace(second=0, microsecond=0)
        DEBUG('TIMESTAMP = %s' % (TIMESTAMP))

        TIMESTAMP_ISO = TIMESTAMP.strftime('%Y-%m-%d %H:%M:%S')
        DEBUG('TIMESTAMP_ISO = %s' % (TIMESTAMP_ISO))

        UTCTIMESTAMP = UTCNOW.replace(second=0, microsecond=0)
        DEBUG('UTCTIMESTAMP = %s' % (UTCTIMESTAMP))

        UTCTIMESTAMP_ISO = UTCTIMESTAMP.strftime('%Y-%m-%d %H:%M:%S')
        DEBUG('UTCTIMESTAMP_ISO = %s' % (UTCTIMESTAMP_ISO))


    #
    # SUBCOMMAND
    #
    if( not SUBCOMMAND ):
        parser.print_help()
        sys.exit(1)

    DEBUG('SUBCOMMAND=%s' % (SUBCOMMAND))

    if hasattr(subcommands, SUBCOMMAND):
        exec 'subcommands.' + SUBCOMMAND + '.run(subcommand_args, locals())'
    else:
        ERROR('Invalid subcommand \'%s\'.' % (SUBCOMMAND))