#!/usr/bin/python

'''

Clones existing cacti-db data-source and graph templates for a particular
(host,data-template,graph-template,rrd-file) combination.

This util assumes that the Data Template and the Graph Template are already
and manually created.  It assists strictly in the process of instantiating
these templates for a large number of regular.  E.g., it will create
instances of the "/servers/*/cpu" data/graph template for each server, given
all the RRD files on stdin.

stdin should consist of paths to the rrd files, one per line.

Everything else is determined from the path, using in-built config data (the
`rrd_types` global); you should edit this for your system.

Run like:

    [sysmon/rrd]$ find ./servers -type f | ../cacti_db.py

@author Josh Sled <jsled@asynchronous.org>
'''

import MySQLdb
import itertools
import logging
import re
import sys


logging.BASIC_FORMAT='cdb-%(asctime)s:%(levelname)s:%(name)s:%(message)s'
logging.basicConfig()
logging.root.setLevel(logging.DEBUG)


cfg = {'db.host': 'rhea',
       'db.db': 'cacti',
       'db.user': 'cactiuser',
       'db.pass': 'cactipass',
       'delete.existing': True, # if we should delete any existing graphs of the same (host_id, template_name)
       }


class TypeElt (object):
    def __init__(self, rrd_path_pattern, groups, template_names):
        '''
        @param rrd_path_pattern Regexp for rrd paths for which this template
          applies.

        @param host-group a dictionary of named components mapping to regexp
          group ids; the only thing required here is the group of the 'host'
          (device name) to create the instance for.
        
        @param template_names A list of either tuples (of the form
          (data-template-name, graph-template-name)) or strings (same string
          used for both templates) to clone.
        '''
        self._rrd_path_pattern = rrd_path_pattern
        self._groups = groups
        self._template_names = template_names
    rrd_path_pattern = property(lambda s: s._rrd_path_pattern)
    groups = property(lambda s: s._groups)
    template_names = property(lambda s: s._template_names)


class RrdTypes (object):
    def __init__(self, data):
        self._log = logging.getLogger('RrdTypes')
        self._data = data

    def __getitem__(self, path):
        matches = [elt for elt in self._data if elt.rrd_path_pattern.match(path)]
        if len(matches) == 0:
            return None
        if len(matches) > 1:
            self._log.info('%d matches for path [%s]' % (len(matches), path))
        return matches[0]


rrd_types = RrdTypes([
    TypeElt(re.compile(r'''.?/servers/(.*)/cpu'''), {'host': 1}, ['/servers/*/cpu']),
    TypeElt(re.compile(r'''.?/servers/([^/]*)/disk/(.*)'''), {'host': 1}, ['/servers/*/disk']),
    # ... add more here ...
    ])


class ConnectionFactory (object):
    _cxn = None
    _cur = None
    def _get_cxn(cls):
        if cls._cxn:
            return cls._cxn
        cls._cxn = MySQLdb.connect(host=cfg['db.host'], db=cfg['db.db'], user=cfg['db.user'], passwd=cfg['db.pass'])
        return cls._cxn

    def _get_cursor(cls):
        if cls._cur:
            return cls._cur
        cls._cur = cls.get_connection().cursor()
        return cls._cur
        
    get_connection = classmethod(_get_cxn)
    get_cursor = classmethod(_get_cursor)


class Query (object):
    '''Self-executable query.'''
    def __init__(self, name, sql, autoinc_insert=False):
        self._log = logging.getLogger('query[%s]' % (name))
        self._name = name
        self._sql = sql
        self._autoinc_insert = autoinc_insert

    def execute(self):
        try:
            return self._execute()
        except Exception, e:
            self._log.error('exception %s for sql [%s]' % (str(e), self._sql))
        return None

    def _execute(self):
        cur = ConnectionFactory.get_cursor()
        row_count = cur.execute(self._sql)
        if self._autoinc_insert:
            new_id = cur.lastrowid
            self._log.debug('autoinc_id=%d' % (new_id))
            return new_id
        # mapify results
        row_dicts = []
        for row in cur.fetchall():
            row_dicts.append(dict([(col[0],val) for col,val in itertools.izip(cur.description, row)]))
        return row_dicts

    def query_single(self):
        res = self.execute()
        if not res:
            self._log.error('no result for [%s]' % (self._sql))
        if len(res) == 0:
            self._log.warn('no rows, 1 exepected')
            return None
        if len(res) > 1:
            self._log.error('len(res)=%d != 1 row expected' % (len(res)))
        return res[0]


class Queries (object):

    '''Predefined, named, parameterized query factory.'''

    def _data_template_id_by_name(cls, name):
        return Query('data_template_id_by_name', '''SELECT id FROM data_template WHERE name = '%(name)s' ''' % {'name': name})
    data_template_id_by_name = classmethod(_data_template_id_by_name)

    def _host_id_by_name(cls, name):
        return Query('host_id_by_name', '''SELECT id FROM host WHERE description = '%(host_name)s' ''' % {'host_name': name})
    host_id_by_name = classmethod(_host_id_by_name)

    def _ins_data_local(cls, data_template_id, host_id):
        return Query('ins_data_local', '''INSERT INTO data_local (data_template_id, host_id) VALUES (%(data_template_id)s, %(host_id)s)'''
                     % {'data_template_id': data_template_id,
                        'host_id': host_id}, autoinc_insert=True)
    ins_data_local = classmethod(_ins_data_local)
    
    def _data_template_rrds(cls, data_template_id):
        return Query('data_template_rrds', '''SELECT * FROM data_template_rrd WHERE local_data_template_rrd_id = 0 AND data_template_id = %(data_template_id)s''' % {'data_template_id': data_template_id})
    data_template_rrds = classmethod(_data_template_rrds)

    def _ins_instance_rrd(cls, local_data_id, template_rrd_id, template_rrd_row):
        format_args = dict([(k,v) for k,v in template_rrd_row.iteritems()])
        format_args['local_data_id'] = local_data_id
        format_args['template_rrd_id'] = template_rrd_id
        return Query('ins_instance_rrd', '''
INSERT INTO data_template_rrd
( local_data_template_rrd_id,
  local_data_id,
  data_template_id,
  t_rrd_maximum, rrd_maximum,
  t_rrd_minimum, rrd_minimum,
  t_rrd_heartbeat, rrd_heartbeat,
  t_data_source_type_id, data_source_type_id,
  t_data_source_name, data_source_name,
  t_data_input_field_id, data_input_field_id
  )
VALUES (
  %(template_rrd_id)s,
  %(local_data_id)s,
  %(data_template_id)s,
  NULL, %(rrd_maximum)s,
  NULL, %(rrd_minimum)s,
  NULL, %(rrd_heartbeat)s,
  NULL, %(data_source_type_id)s,
  NULL, '%(data_source_name)s',
  NULL, %(data_input_field_id)s
)''' % format_args, autoinc_insert=True)
    ins_instance_rrd = classmethod(_ins_instance_rrd)

    def _get_data_template_data_by_id(cls, data_template_id):
        return Query('get_data_template_data_by_id',
                     '''SELECT * FROM data_template_data WHERE local_data_id = 0 AND data_template_id = '%(data_template_id)s' ''' % {'data_template_id': data_template_id})
    get_data_template_data_by_id = classmethod(_get_data_template_data_by_id)

    def _ins_data_template_data(cls, parent_data_template_data_id, local_data_id, datasource_name, rrd_path, parent_row_data):
        format_args = dict([(k,v) for k,v in parent_row_data.iteritems()])
        format_args['local_data_template_data_id'] = parent_data_template_data_id
        format_args['local_data_id'] = local_data_id
        format_args['name_cache'] = datasource_name
        format_args['rrd_path'] = '<path_rra>/%s' % (rrd_path)
        return Query('ins_data_template_data', '''
INSERT INTO data_template_data
( local_data_template_data_id,
  local_data_id,
  data_template_id,
  t_name,
  name,
  name_cache,
  data_source_path,
  t_active, active,
  t_rrd_step, rrd_step,
  t_rra_id
)
VALUES
( %(local_data_template_data_id)s,
  %(local_data_id)s,
  %(data_template_id)s,
  NULL,
  '%(name)s',
  '%(name_cache)s',
  '%(rrd_path)s',
  NULL,'%(active)s',
  NULL, %(rrd_step)s,
  NULL )''' % format_args, autoinc_insert=True)
    ins_data_template_data = classmethod(_ins_data_template_data)

    def _get_data_template_data_rra(cls, parent_data_template_data_id):
        return Query('get_data_template_data_rra', '''SELECT rra_id FROM data_template_data_rra WHERE data_template_data_id = %(parent_data_template_data_id)s''' % {'parent_data_template_data_id': parent_data_template_data_id})
    get_data_template_data_rra = classmethod(_get_data_template_data_rra)

    def _ins_data_template_data_rra(cls, data_template_data_id, rra_id):
        return Query('ins_data_template_data_rra', '''INSERT INTO data_template_data_rra ( data_template_data_id, rra_id ) VALUES ( %(data_template_data_id)s, %(rra_id)s )''' % {'data_template_data_id': data_template_data_id, 'rra_id': rra_id})
    ins_data_template_data_rra = classmethod(_ins_data_template_data_rra)

    def _get_graph_template_id_by_name(cls, name):
        return Query('get_graph_template_id_by_name', '''SELECT id FROM graph_templates WHERE name = '%(name)s' ''' % {'name': name})
    get_graph_template_id_by_name = classmethod(_get_graph_template_id_by_name)

    def _ins_local_graph(cls, graph_template_id, host_id):
        return Query('ins_local_graph', '''INSERT INTO graph_local ( graph_template_id, host_id ) VALUES ( %(graph_template_id)s, %(host_id)s )''' % {'graph_template_id': graph_template_id, 'host_id': host_id}, autoinc_insert=True)
    ins_local_graph = classmethod(_ins_local_graph)

    def _get_graph_template(cls, graph_template_id):
        return Query('get_graph_template', '''SELECT * FROM graph_templates_graph WHERE local_graph_template_graph_id = 0 AND graph_template_id = %(graph_template_id)s''' % {'graph_template_id': graph_template_id})
    get_graph_template = classmethod(_get_graph_template)

    def _ins_graph_instance(cls, local_graph_id, graph_name, parent_row):
        format_args = dict([(k,v) for k,v in parent_row.iteritems()])
        format_args['title_cache'] = graph_name
        format_args['parent_graph_template_graph_id'] = parent_row['id']
        format_args['local_graph_id'] = local_graph_id
        return Query('ins_graph_instance', '''
INSERT INTO graph_templates_graph
( local_graph_template_graph_id,
  local_graph_id,
  graph_template_id,
  image_format_id,
  title,
  title_cache,
  height,
  width,
  upper_limit,
  auto_scale,
  base_value
)
VALUES (
  %(parent_graph_template_graph_id)s,
  %(local_graph_id)s,
  %(graph_template_id)s,
  1,
  '%(title)s',
  '%(title_cache)s',
  %(height)s,
  %(width)s,
  %(upper_limit)s,
  '%(auto_scale)s',
  %(base_value)s
  )''' % format_args, autoinc_insert=True)
    ins_graph_instance = classmethod(_ins_graph_instance)

    def _get_graph_templates_items(cls, graph_template_id):
        return Query('get_graph_templates_items', '''SELECT * FROM graph_templates_item WHERE local_graph_template_item_id = 0 AND graph_template_id = %(graph_template_id)s''' % {'graph_template_id': graph_template_id})
    get_graph_templates_items = classmethod(_get_graph_templates_items)

    def _ins_graph_instance_item(cls, parent_item, local_graph_id, data_template_rrd_map):
        format_args = dict([(k,v) for k,v in parent_item.iteritems()])
        format_args['local_graph_template_item_id'] = parent_item['id']
        format_args['local_graph_id'] = local_graph_id
        format_args['task_item_id'] = data_template_rrd_map[str(parent_item['task_item_id'])]
        return Query('ins_graph_instance_item', '''
INSERT INTO graph_templates_item
( local_graph_template_item_id,
  local_graph_id,
  graph_template_id,
  task_item_id,
  color_id,
  graph_type_id,
  cdef_id,
  consolidation_function_id,
  text_format,
  value,
  hard_return,
  gprint_id,
  sequence
)
VALUES
( %(local_graph_template_item_id)s,
  %(local_graph_id)s,
  %(graph_template_id)s,
  %(task_item_id)s,
  %(color_id)s,
  %(graph_type_id)s,
  %(cdef_id)s,
  %(consolidation_function_id)s,
  '%(text_format)s',
  '%(value)s',
  '%(hard_return)s',
  %(gprint_id)s,
  %(sequence)s )''' % format_args, autoinc_insert=True)
    ins_graph_instance_item = classmethod(_ins_graph_instance_item)

    def _get_data_instance_id_by_name(cls, name):
        return Query('get_data_instance_id_with_name', '''
SELECT local_data_id
FROM data_template_data
WHERE name_cache = '%(name)s'
''' % {'name': name})
    get_data_instance_id_by_name = classmethod(_get_data_instance_id_by_name)

    def _delete_existing_data_rras(cls, local_data_id):
        return Query('delete_existing_data_rras', '''
DELETE FROM data_template_data_rra
WHERE data_template_data_id IN ( SELECT id
                                 FROM data_template_data
                                 WHERE local_data_id = %(local_data_id)d )
''' % {'local_data_id': local_data_id} )
    delete_existing_data_rras = classmethod(_delete_existing_data_rras)

    def _delete_existing_data_template(cls, local_data_id):
        return Query('delete_existing_data_template', '''
DELETE FROM data_template_data
WHERE local_data_id = %(local_data_id)d
''' % {'local_data_id': local_data_id})
    delete_existing_data_template = classmethod(_delete_existing_data_template)

    def _delete_existing_data_rrd(cls, local_data_id):
        return Query('delete_existing_data_rrd', '''
DELETE FROM data_template_rrd
WHERE local_data_id = %(local_data_id)s
''' % {'local_data_id': local_data_id})
    delete_existing_data_rrd = classmethod(_delete_existing_data_rrd)

    def _delete_existing_data_local(cls, local_data_id):
        return Query('delete_existing_data_local', '''
DELETE FROM data_local WHERE id = %(local_data_id)d
''' % {'local_data_id': local_data_id})
    delete_existing_data_local = classmethod(_delete_existing_data_local)

    def _get_graph_instance_id_by_name(cls, name):
        return Query('get_graph_instance_id_by_name', '''
SELECT local_graph_id
FROM graph_templates_graph
WHERE title_cache = '%(name)s'
''' % {'name': name})
    get_graph_instance_id_by_name = classmethod(_get_graph_instance_id_by_name)

    def _delete_existing_graph_items(cls, local_graph_id):
        return Query('delete_existing_graph_items', '''
DELETE FROM graph_templates_item
WHERE local_graph_id = %(local_graph_id)d
''' % {'local_graph_id': local_graph_id})
    delete_existing_graph_items = classmethod(_delete_existing_graph_items)

    def _delete_existing_graph(cls, local_graph_id):
        return Query('delete_existing_graph', '''
DELETE FROM graph_templates_graph
WHERE local_graph_id = %(local_graph_id)d
''' % {'local_graph_id': local_graph_id})
    delete_existing_graph = classmethod(_delete_existing_graph)

    def _delete_existing_graph_local(cls, local_graph_id):
        return Query('delete_existing_graph_local', '''
DELETE FROM graph_local WHERE id = %(local_graph_id)d
''' % {'local_graph_id': local_graph_id})
    delete_existing_graph_local = classmethod(_delete_existing_graph_local)

def execute_plan_alpha(host_name, data_template_name, graph_template_name, rrd_file_path):
    '''Workhorse function: executes the deleteing and cloning operation.'''
    log = logging.getLogger('alpha')
    rrd_path = rrd_file_path[:rrd_file_path.rindex('.')]
    data_template_id = Queries.data_template_id_by_name(data_template_name).query_single()['id']
    host_id = Queries.host_id_by_name(host_name).query_single()['id']
    if cfg['delete.existing']:
        # select local_data_id from data_template_data where name_cache = 'rrd_path'
        # delete where local_data_id = ...
        rtn = Queries.get_data_instance_id_by_name(rrd_path).query_single()
        if rtn:
            local_data_id = rtn['local_data_id']
            Queries.delete_existing_data_rras(local_data_id).execute()
            Queries.delete_existing_data_template(local_data_id).execute()
            Queries.delete_existing_data_rrd(local_data_id).execute()
            Queries.delete_existing_data_local(local_data_id).execute()
    local_data_id = Queries.ins_data_local(data_template_id, host_id).execute()
    # data_template_rrd_id => data_instance_rrd_id, to be used when mapping graph_templates_item rows, below...
    data_template_rrd_map = {}
    for rrd_row in Queries.data_template_rrds(data_template_id).execute():
        template_rrd_id = rrd_row['id']
        rrd_instance_id = Queries.ins_instance_rrd(local_data_id, template_rrd_id, rrd_row).execute()
        log.debug('mapping [%s] to [%s]' % (str(template_rrd_id), str(rrd_instance_id)))
        data_template_rrd_map[str(template_rrd_id)] = rrd_instance_id
    #
    data_template = Queries.get_data_template_data_by_id(data_template_id).query_single()
    parent_data_template_data_id,name_pattern = data_template['id'], data_template['name']
    datasource_name = rrd_path
    data_template_data_id = Queries.ins_data_template_data(parent_data_template_data_id,
                                                           local_data_id,
                                                           datasource_name,
                                                           rrd_file_path,
                                                           data_template).execute()
    #
    for rra_id_row in Queries.get_data_template_data_rra(parent_data_template_data_id).execute():
        rra_id = rra_id_row['rra_id']
        Queries.ins_data_template_data_rra(data_template_data_id, rra_id).execute()
    #
    graph_template_id = Queries.get_graph_template_id_by_name(graph_template_name).query_single()['id']
    if cfg['delete.existing']:
        rtn = Queries.get_graph_instance_id_by_name(rrd_path).query_single()
        if rtn:
            local_graph_id = rtn['local_graph_id']
            Queries.delete_existing_graph_items(local_graph_id).execute()
            Queries.delete_existing_graph(local_graph_id).execute()
            Queries.delete_existing_graph_local(local_graph_id).execute()
    local_graph_id = Queries.ins_local_graph(graph_template_id, host_id).execute()
    parent_graph = Queries.get_graph_template(graph_template_id).query_single()
    title_pattern = parent_graph['title']
    graph_name = rrd_path
    local_graph_templates_graph_id = Queries.ins_graph_instance(local_graph_id,
                                                                   graph_name,
                                                                parent_graph).execute()
    # ##############################
    for item in Queries.get_graph_templates_items(graph_template_id).execute():
        Queries.ins_graph_instance_item(item, local_graph_id, data_template_rrd_map).execute()


def create_from_rrd(path):
    log = logging.getLogger('create_from_rrd')
    rrd_type = rrd_types[path]
    if not rrd_type:
        log.warn('''could not find handler for path [%s]''' % (path))
        return
    match = rrd_type.rrd_path_pattern.match(path)
    host_name = match.group(rrd_type.groups['host'])
    for name in rrd_type.template_names:
        if type((1,)) == type(name):
            data_template_name, graph_template_name = name
        else:
            data_template_name, graph_template_name = name,name
        log.debug('%s:%s:%s:%s' % (host_name, data_template_name, graph_template_name, path))
        execute_plan_alpha(host_name, data_template_name, graph_template_name, path)


def main():
    for rrd_path in sys.stdin.readlines():
        rrd_path = rrd_path.strip()
        create_from_rrd(rrd_path)

if __name__ == '__main__':
    main()