#!/usr/bin/env python

import os
import threading
import MySQLdb
import sys
import _mysql_exceptions

def usage(progname):
    print """
Usage: %s [-domain <domain>] [-size] <nebkey sql pattern>

    Get the files on the cluster whose nebkey matches the <nebkey pattern>

    Options:
      -domain <domain>: with <domain> set to mhpcc (wrt atrc, wrt stsci), all (default: all)
        Restrict the search to MHPCC (wrt ATRC, wrt STSCI) storages nodes
        'all': search on all nodes (default)
      -size: display the size of the file

    Examples:
      - Search a file on all nodes
          %s gpc1/20100228/o5255g0304o/o5255g0304o.ota71.burn.tbl
      - Search with SQL wildcard (can be really long)
          %s gpc1/20100228/%%/o5255g0308o.ota11.burn.tbl (SQL wildcard)
      - Search only on STSCI nodes
          %s -domain stsci gpc1/20100228/o5255g0308o/o5255g0308o.ota11.burn.tbl
      - Search only on ATRC nodes
          %s -domain atrc gpc1/20100228/o5255g0308o/o5255g0308o.ota11.burn.tbl
      - Search only on MHPCC nodes
          %s -domain mhpcc gpc1/20100228/o5255g0308o/o5255g0308o.ota11.burn.tbl
      - Search a file on all nodes and show their sizes
          %s -size gpc1/20100228/o5255g0304o/o5255g0304o.ota71.burn.tbl

    Notes: 
    * Error messages as (2003, "Can't connect to MySQL server on '<node name>' (111)")
          can be displayed if for some reason one of the MySQL servers (e.g. the one on <node name>) 
          has crashed.
    * Accuracy of the result is not asserted since the nebulous directory contents are
          refreshed only once a day.
    * If the file size is -1, it means that the file has been inserted very recently and not been been "refreshed" yet
    """ % (progname, progname, progname, progname, progname, progname, progname)

class Nodes:
    mhpcc = [ ("ipp004", "0"), 
              ("ipp005", "0"), ("ipp006", "0"), ("ipp007", "0"), ("ipp008", "0"), ("ipp009", "0"), 
              ("ipp010", "0"), ("ipp011", "0"), ("ipp012", "0"), ("ipp013", "0"), ("ipp014", "0"), 
              ("ipp015", "0"), ("ipp016", "0"), ("ipp017", "0"), ("ipp018", "0"), ("ipp019", "0"),
              ("ipp020", "0"), ("ipp021", "0"), ("ipp023", "0"), ("ipp024", "0"), 
              ("ipp025", "0"), ("ipp026", "0"), ("ipp027", "0"), ("ipp028", "0"), ("ipp029", "0"),
              ("ipp030", "0"), ("ipp031", "0"), ("ipp032", "0"), ("ipp033", "0"), ("ipp034", "0"), 
              ("ipp035", "0"), ("ipp036", "0"), ("ipp037", "0"), ("ipp038", "0"), ("ipp039", "0"),
              ("ipp040", "0"), ("ipp041", "0"), ("ipp042", "0"), ("ipp043", "0"), ("ipp044", "0"), 
              ("ipp045", "0"), ("ipp046", "0"), ("ipp047", "0"), ("ipp048", "0"), ("ipp049", "0"),
              ("ipp050", "0"), ("ipp051", "0"), ("ipp052", "0"), ("ipp053", "0"), ("ipp054", "0"), 
              ("ipp055", "0"), ("ipp056", "0"), ("ipp057", "0"), ("ipp058", "0"), ("ipp059", "0"),
              ("ipp060", "0"), ("ipp061", "0"), ("ipp062", "0"), ("ipp063", "0"), ("ipp064", "0"), 
              ("ipp065", "0"), ("ipp066", "0"), 
              ]
    atrc = [ ("ippb00", "0"), ("ippb00", "1"), ("ippb00", "2"),
             ("ippb01", "0"), ("ippb01", "1"), ("ippb01", "2"),
             ("ippb02", "0"), ("ippb02", "1"), ("ippb02", "2"),
             ("ippb03", "0"), ("ippb03", "1"), ("ippb03", "2"), 
             ]
    stsci = [ ("stsci00", "0"), ("stsci00", "1"), ("stsci00", "2"),
              ("stsci01", "0"), ("stsci01", "1"), ("stsci01", "2"),
              ("stsci02", "0"), ("stsci02", "1"), ("stsci02", "2"),
              ("stsci03", "0"), ("stsci03", "1"), ("stsci03", "2"),
              ("stsci04", "0"), ("stsci04", "1"), ("stsci04", "2"),
              ("stsci05", "0"), ("stsci05", "1"), ("stsci05", "2"),
              ("stsci06", "0"), ("stsci06", "1"), ("stsci06", "2"),
              ("stsci07", "0"), ("stsci07", "1"), ("stsci07", "2"),
              ("stsci08", "0"), ("stsci08", "1"), ("stsci08", "2"),
              ("stsci09", "0"), ("stsci09", "1"), ("stsci09", "2"), 
              ("stsci10", "0"), ("stsci10", "1"), ("stsci10", "2"),
              ("stsci11", "0"), ("stsci11", "1"), ("stsci11", "2"),
              ("stsci12", "0"), ("stsci12", "1"), ("stsci12", "2"),
              ("stsci13", "0"), ("stsci13", "1"), ("stsci13", "2"),
              ("stsci14", "0"), ("stsci14", "1"), ("stsci14", "2"),
              ("stsci15", "0"), ("stsci15", "1"), ("stsci15", "2"),
              ("stsci16", "0"), ("stsci16", "1"), ("stsci16", "2"),
              ("stsci17", "0"), ("stsci17", "1"), ("stsci17", "2"),
              ("stsci18", "0"), ("stsci18", "1"), ("stsci18", "2"),
              ("stsci19", "0"), ("stsci19", "1"), ("stsci19", "2"), 
              ]

class QueryingThread(threading.Thread):
    count_query_exact = "SELECT COUNT(*) FROM %s WHERE nebkey = '%s'"
    count_query_approximate = "SELECT COUNT(*) FROM %s WHERE nebkey LIKE '%s'"
    query_exact = "SELECT subdirname, filename FROM %s WHERE nebkey = '%s'"
    query_approximate = "SELECT subdirname, filename FROM %s WHERE nebkey LIKE '%s'"
    query_exact_with_size = "SELECT subdirname, filename, size FROM %s WHERE nebkey = '%s'"
    query_approximate_with_size = "SELECT subdirname, filename, size FROM %s WHERE nebkey LIKE '%s'"
    letters = "0123456789abcdef"

    def __init__(self, node, disk, pattern, show_size):
        threading.Thread.__init__(self)
        self.node = node
        self.disk = disk
        self.show_size = show_size
        self.db = MySQLdb.connect(host=node, user='ippuser', passwd='ipp', db='DisksMonitoring')
        self.cursor = self.db.cursor()
        if '%' in pattern:
            if self.show_size:
                self.count_query = QueryingThread.count_query_approximate
                self.query = QueryingThread.query_approximate_with_size
            else:
                self.count_query = QueryingThread.count_query_approximate
                self.query = QueryingThread.query_approximate
        else:
            if self.show_size:
                self.count_query = QueryingThread.count_query_exact
                self.query = QueryingThread.query_exact_with_size
            else:
                self.count_query = QueryingThread.count_query_exact
                self.query = QueryingThread.query_exact

    def run(self):
        try:
            for letter1 in QueryingThread.letters:
                for letter2 in QueryingThread.letters:
                    tablename = 'neb_%s_%s%s' % (self.disk, letter1, letter2)
                    self.cursor.execute(self.count_query % (tablename, pattern))
                    rows = self.cursor.fetchall()
                    count = rows[0][0]
                    if count != 0:
                        self.cursor.execute(self.query % (tablename, pattern))
                        for row in self.cursor.fetchall():
                            if self.show_size:
                                print '%9d | /data/%s.%s/nebulous/%s%s/%s/%s' % (row[2], self.node, self.disk, letter1, letter2, row[0], row[1])
                            else:
                                print '/data/%s.%s/nebulous/%s%s/%s/%s' % (self.node, self.disk, letter1, letter2, row[0], row[1])
        except _mysql_exceptions.ProgrammingError, e:
            print e
            print 'Problem with %s %s %s' % (self.node, self.disk, tablename)
        except _mysql_exceptions.InternalError, e:
            print "InternalError: %s %s" % (self.node, self.disk)
            print e
            print

def process_options(arguments):
    if len(arguments) == 1:
        usage(arguments[0])
        sys.exit(1)
    domain = 'all'
    count = 1
    show_size = False
    while count < len(arguments):
        if arguments[count] == '-domain':
            domain = arguments[count+1]
            count += 2
        elif arguments[count] == '-size':
            show_size = True
            count += 1
        else:
            pattern = arguments[count]
            count += 1
    if domain == 'mhpcc':
        nodes = Nodes.mhpcc
    elif domain == 'atrc':
        nodes = Nodes.atrc
    elif domain == 'stsci':
        nodes = Nodes.stsci
    elif domain == 'all':
        nodes = Nodes.mhpcc
        nodes.extend(Nodes.atrc)
        nodes.extend(Nodes.stsci)
    else:
        usage(arguments[0])
        sys.exit(1)
    return pattern, nodes, show_size

if __name__ == '__main__':
    pattern, nodes, show_size = process_options(sys.argv)
    queryingThreads = []
    for (node, disk) in nodes:
        try:
            queryingThreads.append(QueryingThread(node, disk, pattern, show_size))
        except _mysql_exceptions.OperationalError, e:
            print e
    for t in queryingThreads:
        t.start()
    for t in queryingThreads:
        t.join()

