MRovere/ReferencePythonCLI/DB.py

#!/usr/bin/env python

from optparse import OptionParser, OptionGroup
import sqlite3
#from pysqlite2 import dbapi2 as sqlite3
from IOV_WrapperFramework import IOV
import os
import sys
import re
import datetime
import base64
import pdb
import smtplib
import email
 
# utility function
def sortedDictValues(adict):
    keys = adict.keys()
    keys.sort()
#    if(_verbose):
#        for key in keys:
#            print key + ' ' + adict[key]
    return [key for key in keys]


class DB:
    """ A simple ad-hoc interface to reference sqlite Database"""
    main_db = ""
    connected = False
    table_name = 'reference'
    verbose = False
    JsonOutput = False

    def __init__(self, databaseName, table_name, Json=False, notification=True, rcpt='erosales@cern.ch'):
        """
        Constructor.

        Keyword arguments:
        databaseName -- the name of the sqlite file to use as database
        table_name   -- the name of the table to use inside the
                        specified database
        Json         -- a boolean to specify if JSON putput is to be
                        generated (used when run from Web Server)
                        (default = False)
                        
        """
        self.main_db = databaseName
        self.table_name = table_name
        self.JsonOutput = Json
        self.notification = notification
        self.rcpt = rcpt

    def connect(self):
        """
        Establish a connection with the database.
        The connection is cached across a single instance of the object.
        
        """
        if(self.checkDB()):
            if not self.connected:
                self.con = sqlite3.connect(self.main_db)
                self.con.row_factory = sqlite3.Row
                self.cur = self.con.cursor()
                self.connected = True
        else:
            print "Error.\nDatabase " + self.main_db + " not found.\nExiting\n"
            sys.exit(1)

    def connectFirstTime(self):
        """
        Establish a connection with the database.
        The connection is cached across a single instance of the object.
        It does NOT check for sqlite file existance.
        
        """
        if not self.connected:
            self.con = sqlite3.connect(self.main_db)
            self.cur = self.con.cursor()
            self.connected = True

    def setDatabase(self, databaseName):
        """Set the database internal variable to the specified value.

        Keyword arguments:
        databaseName -- the filename to use as sqlite database"""
        self.main_db = databaseName

    def setVerbose(self, value):
        """Set verbosity. Always remember to turn it OFF when used
        from Web Server.

        Keyword arguments:
        value -- boolean t turn on/off verbosity.

        """
        self.verbose = value

    def checkDB(self):
        """Check if the specified sqlite file exists and returns a Boolean """
        return os.path.exists(self.main_db)
    
    def createDB(self):
        """Create a new database and the default table. """
        if (not self.checkDB()):
            table = {
                '1_Rcd_id'     : 'integer DEFAULT 1 NOT NULL'
                ,'2_InsertTime' : 'ts'
                ,'3_LocalTag'   : 'text'
                ,'4_StartRun'   : 'int' 
                ,'5_Subsystem'  : 'text'
                ,'6_XML'        : 'clob'
                ,'7_CMS_ORCOFF_PREP' : 'text DEFAULT "None" NOT NULL'
                ,'8_CMS_ORCOFF_PROD' : 'text DEFAULT "None" NOT NULL'
                ,'9_LastUpdate' : 'ts DEFAULT CURRENT_TIMESTAMP NOT NULL'}
            statement = "Create table " + self.table_name + " (" 
            for colname in sortedDictValues(table):
                real_colname = re.sub('\d+_', '', colname)
                statement += "'" + real_colname + "' " + table[colname] + ','
            statement += "PRIMARY KEY (Rcd_ID), CONSTRAINT uc_RunTagSubsystemXML UNIQUE (LocalTag, StartRun, Subsystem))"
            #        statement = re.sub(",$", ')',statement)
            if(self.verbose): print statement
            self.connectFirstTime()
            self.cur.execute(statement)
            self.con.commit()
        else:
            print "Warning, nothing to do:\nDatabase already present"
            sys.exit(1)

    def updateRealRunNumber(self, oldRun, tag, newRun):
        """Update the starting run from which a payload will be valid in the orcoff_prod database"""
        self.connect()
        #statement = "update " + self.table_name + " StartRun=newrun where LocalTag=tag and StartRun =oldrun
        #statement = "update " + self.table_name + " set StartRun = " + newRun + " where LocalTag = " +tag " and " StartRun = " +oldRun";
        statement = "update " + self.table_name + " set StartRun = \"" + str(newRun) + "\" , CMS_ORCOFF_PROD = \"Y\" where LocalTag = \"" + tag + "\" and  StartRun = \"" + str(oldRun) +"\""
        #print statement
        self.cur.execute(statement)
        self.con.commit()
        #print 'done'

    def updateProdRunNumber(self, newRun, payload):
        """Update the starting run from which a payload will be valid in the orcoff_prod database
        Returns"""
        statement = "update " + self.table_name + " set StartRun = \"" + str(newRun) + "\" , CMS_ORCOFF_PROD = \"Y\" where Rcd_id = \"" + str(payload) +"\""
        #print statement
        try:
                self.connect()
                self.cur.execute(statement)
                self.con.commit()
                return True
        except Exception, err_msg:
                print 'loaded: false, err_msg: "' ,err_msg, '"'
                return False

    def getTagFromPayload(self, payload):
        """Gets a tag for a given payload"""    
        statement = "select LocalTag from reference WHERE Rcd_id = \"" + str(payload) +"\""
        try:
                self.connect()
                #print statement
                self.cur.execute(statement)
                return self.cur.fetchone()[0]
        except Exception, err_msg:
                print 'loaded: false, err_msg: "' ,err_msg, '"'
                return None
        
    def getRunFromPayload(self, payload):
        """Gets a run number for a given payload"""
        statement = "select StartRun from reference WHERE Rcd_id = \"" + str(payload) +"\""
        try:
                self.connect()
                #print statement
                self.cur.execute(statement)
                return self.cur.fetchone()[0]
        except Exception, err_msg:
                print 'loaded: false, err_msg: "' ,err_msg, '"'
                return None

    def createFakeLocalTag(self, tag):
        """Create a fake localtag. The StartRun field is set to -1
        to distinguish a fake local tag from a real one.

        Keyword argument:
        tag -- the local tag to create
        
        """
        self.connect()
        statement = "insert into " + self.table_name + " ('InsertTime', 'LocalTag', 'StartRun', 'Subsystem', 'XML') values(?,?,?,?,?)"
        now = datetime.datetime.now()
        if(self.verbose): print statement,now,tag, -1, '',''
        self.cur.execute(statement,(now,tag, -1, '','' ) )
        self.con.commit()
        if(self.notification):
            self.notifyMe('createFakeLocalTag created a new localtag named ' + tag + ' on db ' + self.main_db)
        
    def insertRow(self,localtag, start, subsystem, xml):
        """Insert a row into the database.

        Keyword arguments:
        localtag  -- the localtag to insert
        start     -- the start run to insert
        subsystem -- the name of the subsystem to insert
        xml       -- the path to the XML file to insert
        
        """
        self.connect()
        if (not os.path.exists(xml)):
            print "Error.\nThe specified XML does not exist on disk.\nExiting."
            sys.exit(1)
        else:
            blobdata = open(xml,'rb').read()
            statement = "Insert into " + self.table_name + " ('InsertTime', 'LocalTag', 'StartRun', 'Subsystem', 'XML') values(?,?,?,?,?)"
            now = datetime.datetime.now()
            if(self.verbose): print statement,now,localtag, start, subsystem,sqlite3.Binary(blobdata)
            self.cur.execute(statement,(now,localtag, start, subsystem,sqlite3.Binary(blobdata) ) )
            self.con.commit()
        if(self.notification):
            self.notifyMe('[insertRow]\nTag ' + localtag + '\nStartRun ' + str(start) + '\nSubsystem ' + subsystem + '\nXML\n' + blobdata + '\non DB ' + self.main_db)
        
    def insertRowFromWeb(self,localtag, start, subsystem, xml):
        """Insert a row into the database from the Web Server.
        A check is performed to assert that the specified
        run is not in the past for CMS official DB.

        Keyword arguments:
        localtag  -- the localtag to insert
        start     -- the start run to insert
        subsystem -- the name of the subsystem to insert
        xml       -- the path to the XML file to insert
        
        """
        # xml is base64 encoded!!!
        iov = IOV() #IOV_Wrapper('cms_orcoff_prod', 'CMS_COND_34X_DQM', False)
        if not iov.checkAvailability(localtag,start) :
            print '{loaded: false, err_msg: "Selected run is in the past."}'
            return 
        self.connect()
        blobdata = base64.decodestring(xml)
        statement = "Insert into " + self.table_name + " ('InsertTime', 'LocalTag', 'StartRun', 'Subsystem', 'XML') values(?,?,?,?,?)"
        now = datetime.datetime.now()
        if(self.verbose): print statement,now,localtag, start, subsystem,sqlite3.Binary(blobdata)
        try:
            self.cur.execute(statement,(now,localtag, start, subsystem,sqlite3.Binary(blobdata) ) )
            self.con.commit()
            print '{loaded: true, err_msg: ""}'
        except Exception, err_msg:
            print '{loaded: false, err_msg: "' ,err_msg, '"}'
        if(self.notification):
            self.notifyMe('[insertRowFromWeb]\nTag ' + localtag + '\nStartRun ' + str(start) + '\nSubsystem' + subsystem + '\nXML\n' + blobdata + '\non DB ' + self.main_db)

    def insertRowFromWebRelVal(self,localtag, subsystem, xml):
        """Insert a record into the database for RelVal samples.
         The logic is the following:
         1. Loop over all entries in specified skeleton_localtag for specific subsystem and take max startRun
            For RelVal StartRun is a versioning mechanism, that's it. Latest gets picked-up.
         2. Create a new record with calling sequences args and number computed at #1 + 1 as StartRun

         Keyword arguments:
         localtag  -- the local tag to insert
         subsystem -- the subsystem to insert
         xml       -- the base64 encoded xml string to insert

         """
        
        version = 1
        self.connect()
        blobdata = base64.decodestring(xml)
        statement = 'select Max(StartRun) from ' + self.table_name + ' where subsystem = "' + subsystem + '" and localtag ="' + localtag + '"'
        self.cur.execute(statement)
        rows = self.cur.fetchall()
        for row in rows:
            if row[0]:
                version = row[0] + 1

        statement2 = "Insert into " + self.table_name + " ('InsertTime', 'LocalTag', 'StartRun', 'Subsystem', 'XML') values(?,?,?,?,?)"
        now = datetime.datetime.now()
        if(self.verbose): print statement2,now,localtag, version, subsystem,sqlite3.Binary(blobdata)
        try:
            cur2 = self.con.cursor()
            cur2.execute(statement2,(now,localtag, version, subsystem,sqlite3.Binary(blobdata) ) )
            self.con.commit()
            print '{loaded: true, err_msg: ""}'
        except Exception, err_msg:
            print '{loaded: false, err_msg: "' ,err_msg, '"}'
        if(self.notification):
            self.notifyMe('[insertRowFromWebRelVal]\nTag ' + localtag + '\nSubsystem ' + subsystem + '\nXML\n' + blobdata + '\non DB ' + self.main_db)

    def listKnownSubSystems(self):
        """List all the subsystems that have been so far inserted into the database."""
        
        self.connect()
        statement = "select distinct Subsystem from " + self.table_name + " order by Subsystem asc"
        if(self.verbose): print statement
        self.cur.execute(statement)
        if not self.JsonOutput:
            self.prettyPrintIt(self.cur)
#            for row in self.cur:
#                print row
        else:
            index = 0
            print '{ rows: ['
            print '{ id: -1, subsystem: "ALL", value: ""},'
            for row in self.cur:
                if not row[0] : # this means no data is there or that Subsystem is empty (fake record to make localtag appear...
                    continue
                print '{ id: ' + str(index) + ',  subsystem: "' + row[0] +  '", value: "' + row[0] + '" },'
                index +=1
            print ']}'
            
                
    def dumpLocalTags(self):
        """List all the local tags that have been so far inserted into the database.
        Returns a list of corresponding tags.
        """
        self.connect()

        localtags = []
        statement = "Select distinct LocalTag from " + self.table_name 
        if(self.verbose): print statement
        self.cur.execute(statement)
        if not self.JsonOutput:
            r = self.cur.fetchone()
            while r:
                for key in r.keys():
                    print key, r[key]
                    localtags.append(r[key])
                r = self.cur.fetchone()
        else:
            index = 0
            print '{ rows: ['
            print '{ id: -1, localtag: "ALL", value: ""},'
            for row in self.cur:
                print '{ id: ' + str(index) + ',  localtag: "' + row[0] +  '", value: "' + row[0] + '"},'
                index +=1
            print ']}'

        return localtags

#             print '<data>'
#             print ' <tags>'
#             for row in self.cur:
#                 print '  <localtag>' + row[0] +  '</localtag>'
#             print ' </tags>'
#             print '</data>'

    def dumpAllData(self, localtag, subsystem, start=0, limit=sys.maxint):
        """Dump all data currently available in the database.

        Keyword arguments:
        localtag  -- the name of the localtag used to filter data  (if not '')
        subsystem -- the name of the subsystem used to filter data (if not '')
        start     -- the record after which data is to be dumped (used only for paging
                     in the Web Server) ( default = 0 )
        limit     -- the number of records to be dumped (used only for paging
                     in the Web Server) ( default = sys.maxint )

        """
        startV = float(start)
        limitV = float(limit)
        self.connect()
        statement = "Select InsertTime, LocalTag, StartRun, Subsystem, XML, cms_orcoff_prep, cms_orcoff_prod, LastUpdate from " + self.table_name + " where StartRun > 0 "
        if(localtag != ''):
            statement += ' and LocalTag ="' + localtag +'"'
        if(subsystem != ''):
            statement += ' and Subsystem ="' + subsystem +'"'
        statement += " order by LocalTag, Subsystem"
        if(self.verbose): print statement
        self.cur.execute(statement)
#        toBoolean = {}
        iov = IOV() #_Wrapper('cms_orcoff_prep','CMS_COND_31X_DQM_SUMMARY', self.verbose)
        if not self.JsonOutput:
            for row in self.cur:
                print row
            print self.cur.fetchall()
        else:
            rows = self.cur.fetchall()
            print '{ "total": ' + str(len(rows)) + ', "rows": ['
            for index, row in enumerate(rows):
                if not row[3]: # this means either subsystem is an empty string or that no data is available
                    continue
#                print 'Before ' + str(index) + ' ' + str(startV) + ' ' + str(startV+limitV)
                if(index < startV):
                    continue
                if(index >= startV+limitV):
                    continue
#                print 'After ' + str(index) + ' ' + str(start) + ' ' + str(start+limit)
#            for row in self.cur:
#                inserted = 1
#                if iov.checkAvailability(row[1], row[2]) :
#                    inserted = 0
                date = row[0]
                date = re.sub("\.\d+$", '', date) # get rid of final .XXXXX from InsertTime field
                lastupdate = row[7]
                lastupdate = re.sub("\.\d+$", '', lastupdate) # get rid of final .XXXXX from LastUpdate field
                encodedXML = base64.encodestring(row[4])
                encodedXML = re.sub('\n', '', encodedXML)
                
#                toBoolean['Y'] = '1'
#                toBoolean['N'] = '0'
#                toBoolean['None'] = '0'
#                print '{ "id": ' + str(index) + ',  "InsertTime": "' + date + '"' + ',  "LocalTag":   "' + row[1] + '"' + ',  "StartRun":    ' + str(row[2]) + ',  "Subsystem":  "' + row[3] + '", "XML": "'  + encodedXML + '", "InDB": ' + str(inserted) + '},'
                print '{ "id": ' + str(index) + ',  "InsertTime": "' + date + '"' + ',  "LocalTag":   "' + row[1] + '"' + ',  "StartRun":    ' + str(row[2]) + ',  "Subsystem":  "' + row[3] + '", "XML": "'  + encodedXML + '", isIn: "None", "cms_orcoff_prep": "' + row[5] + '", "cms_orcoff_prod": "' + row[6] + '", "LastUpdate": "' + lastupdate + '"},'
                #index +=1
            print ']}'
                
    def dumpLocalTag(self,tag):
        """Dump data pertaining to the specified local tag."""
        self.connect()
        statement = "Select InsertTime, LocalTag, StartRun, Subsystem from " + self.table_name + " where LocalTag = '" + tag + "' order by Subsystem" 
        if(self.verbose): print statement
        self.cur.execute(statement)
        for row in self.cur:
            print row
        print self.cur.fetchall()

    def getNextAvailableRun(self, localtag):
        """ Search for the next available run number, based on currently SAVED IOVs
        in cms_orcoff_prep(To be switched to prod ASAP!!).

        The Logic:
        1. select the Maximum run number of all records
           with specified localtag that have already been
           inserted into cms_orcoff_prep.
        2. If no record has been found it means that:
           a. the tag does not yet exists => return 1.
           b. the tag is there but the record has not
              yet been uploaded. Return the maximum
              safest value anyway!!! with an alert message.
        3. return the maximum value + 1.
        """

        self.connect()
        localtag = re.sub('\n', '', localtag)
        statement = 'select  Max(StartRun), cms_orcoff_prep from '  + self.table_name + ' where LocalTag = "' + localtag + '";'
        if(self.verbose): print statement
        self.cur.execute(statement)
        print '{ rows: ['

        for row in self.cur:
            if re.match('None', str(row[0])) :
                print '{ nextAvailable: 1, Mesg: "New Tag"},'
            else:
                if re.match('Y', str(row[1])) :
                    print '{ nextAvailable: ' + str(row[0]+1) + ', Mesg: "Existing.<br/>Loaded."},'
                if re.match('N$', str(row[1])) : # match just 'N'
                    statement2 = 'select  Max(StartRun) from '  + self.table_name + ' where LocalTag = "' + localtag + '" and cms_orcoff_prep = "Y" ;'
                    if(self.verbose): print statement
                    cur2 = self.con.cursor()
                    cur2.execute(statement2)
                    for row2 in cur2:
                        if re.match('None', str(row2[0])):
                            print '{ nextAvailable: ' + str(row[0]+1) + ', Mesg: "Existing.<br/>Never Loaded."},'
                        else:
                            print '{ nextAvailable: ' + str(row[0]+1) + ', Mesg: "Existing.<br/>Loaded.<br/>Last Loaded: ' + row[0]+ '"},'
                if re.match('None', str(row[1])) :
                    print '{ nextAvailable: ' + str(row[0]+1) + ', Mesg: "Existing.<br/>NOT Checked."},'

        print ']}'

        
    def produceValidDataForRun(self, tag, subsystem, runToCheck,start=0, limit=sys.maxint):
        """Search for data valid for the specified tag and run and subsystem.
        This method must be used only by the Web Server, since it prints data only
        in JSON format.

        The Logic:
        1. make a loop over all the localtags
        2. make a loop over all the subsystems
        3. if the user supplied a tag and/or a subsystem to filter on,
           pick up only proper records.
        4. do a select on each proper record taking the Max(StartRun)
           where StartRun <= runToCheck: this guarantes that this
           record is the one if validity for that particular run!!
           (it is the latest startrun before or at the run we want
           to check: the next StartRun would be beyond our run of interest!!


        Keyword arguments:
        tag        -- the name of the tag to search for
        subsystem  -- the name of the subsystem to search data for
        runToCheck -- the run for which data must be valid (data returned
                      is contained in the proper IOV for the specified run)
        start      -- the record after which data is to be dumped (used
                      only for paging in the Web Server) ( default = 0 )
        limit      -- the number of records to be dumped (used only for
                      paging in the Web Server) ( default = sys.maxint )

        """

        startV = float(start)
        limitV = float(limit)
        self.connect()
        statement = 'select distinct LocalTag from reference order by LocalTag ASC;'
        if(self.verbose): print statement
        self.cur.execute(statement)
        print '{ "rows": ['
        index = 0
        for ltag in self.cur:
            if(tag != ''):
                if(tag != ltag[0]):
                    continue
            statement2 = 'select distinct Subsystem from reference order by Subsystem ASC;'
            cur2 = self.con.cursor()
            cur2.execute(statement2)
            for subsys in cur2:
                if(subsystem != ''):
                    if(subsystem != subsys[0]):
                        continue
                statement3 = 'select Max(StartRun), LocalTag, Subsystem, InsertTime, XML , cms_orcoff_prep, cms_orcoff_prod, lastupdate from reference where StartRun <= ' + str(runToCheck) + ' and LocalTag ="' + ltag[0] + '" and Subsystem = "' +subsys[0] + '"'
                cur3 = self.con.cursor()
                cur3.execute(statement3)
                rows = cur3.fetchall()
                for row in rows:
                    if not row[3]: #this means no data match the criteria!!!
                        continue
                    date = row[3]
                    date = re.sub("\.\d+$", '', date) # get rid of final .XXXXX from InsertTime field
                    lastupdate = row[7]
                    lastupdate = re.sub("\.\d+$", '', lastupdate) # get rid of final .XXXXX from InsertTime field
                    encodedXML = base64.encodestring(row[4])
                    encodedXML = re.sub('\n', '', encodedXML)
                    if(index < startV):
                        continue
                    if(index >= startV+limitV):
                        continue
                    print '{ id: ' + str(index) + ', InsertTime: "' + date + '", LocalTag: "' + row[1] + '", StartRun: ' + str(row[0]) + ', Subsystem: "' + row[2] + '", XML: "' + encodedXML + '", cms_orcoff_prep:  "' + row[5] + '", cms_orcoff_prod: "' + row[6] + '", LastUpdate: "' + lastupdate + '"},'
                index +=1
        print ']}'


    def produceForTag(self,tag, oracleDB):
        """Write XML files for the specified tag and use the specified Oracle database
        in the metadata definition.
        All records that are in the past with respect to the official CMS
        Oracle Database are discarded and NO XML file is produced: a
        warning message is issued to the end user.
        
        The Logic:
        1. first fetch all StartRun linked to a particular local tag and
           order them in ASCENDING order: for each of this values there
           should be a new record, since one or more subsystem have changed
           their reference histograms
        2. Loop over values obtained from point 1
        3. Fetch all subsystems defined in the localTag under study
        4. For each subsystem of point 3 I query the DB for the XML
           corresponding to StartRun <= values of point one and I order
           the results in DESCENDING order according to StartRun: in
           this way the most up-to-date record for this run and this
           subsystem is always the first one, that is the one I use.


        Keyword arguments:
        tag      -- the tag for which the XML have to be produced
        oracleDB -- the name of the official CMS Oracle Database
                    to which payloads have to put uploaded. This
                    is automatically written in the metadata file.

        Returns:
        A list of produced XML files.

        """
        self.connect()
        writtenXML = []
        statement = 'select distinct StartRun from reference where LocalTag = "' + tag + '" order by StartRun ASC;'
        if(self.verbose): print statement
        self.cur.execute(statement)
        runs = []
        for row in self.cur:
            runs.append(row[0])
        if(self.verbose): print runs

        statement = 'select distinct Subsystem from reference where LocalTag = "' + tag + '" order by Subsystem ASC;'
        if(self.verbose): print statement
        self.cur.execute(statement)
        subsystems = []
        for row in self.cur:
            subsystems.append(row[0])
        if(self.verbose): print subsystems

        iov = IOV() #_Wrapper(oracleDB,'CMS_COND_34X_DQM', self.verbose)
        for run in runs:
            if(self.verbose):
                print "Analyzing Run Number " + str(run)
            if not iov.checkAvailability(tag, run) :
                print 'Run ', run, ' for tag ', tag, ' has to be discarded since is in the past for DB ', oracleDB
                continue
            xmlFileName = 'referencesHisto_since_%(run)06d_%(tag)s.xml' % {"run": int(run), 'tag': tag} #+ str(run) + '_' + tag + '.xml'
            f = open(xmlFileName, 'w')
            self.printHeader(f,tag,run, oracleDB)
            for subsystem in subsystems:
                if(self.verbose):
                    print "Analyzing SubSystem " + subsystem
                statement = 'Select XML, StartRun from reference where StartRun <=' + str(run) + ' and Subsystem ="' + subsystem + '" and localtag="' + tag +'" order by StartRun DESC'
                if(self.verbose):
                    print statement
                self.cur.execute(statement)
                try:
                    row = self.cur.fetchone()
                    f.write("  ")
                    f.write(row[0])
                except:
                    if(self.verbose):
                        print 'No record found for subsystem %s and run within %s' % ( subsystem, run)
            f.write("\n\n")
            f.write('</RefHistos>\n')
            f.close()
            writtenXML.append(xmlFileName)
        return writtenXML


    def printHeader(self,f, tag, run, oracleDB):
        """Print the header of the XML. The values specified
        will be automatically propagated into the metadata file.

        Keyword arguments:
        tag -- the tag to be written in the header as 'tag'   attribute to
               the MetaData xml field.
        run -- the run to be written in the header as 'since' attribute to
               the MetaData xml field.
        oracleDB -- the name of the official CMS Oracle database to be
                    written in the header as 'destDB' attribute to the MetaData xml field.
                    
        """
        f.write('<?xml version="1.0" encoding="UTF-8"?>\n')
        f.write('<!DOCTYPE RefHistos  SYSTEM "referenceHisto.dtd">')
        f.write('\n\n')
        f.write('<RefHistos DataSetName="Cosmics" SoftwareVersion="CMSSW_3_5_0" GlobalTag="GR09_P_V1">')
        f.write('\n\n')
        f.write('  <MetaData destDB="oracle://' + oracleDB + '/CMS_COND_42X_DQM" tag="' + tag + '" since="' + str(run) +'"/>\n\n')

    def produceSkeletonXML(self, subsystem, sourceFile):
        """Produce a skeleton XML using the specified subsystem
        and path filename as source and return the generated XML.

        Keyword arguments:

        subsystem  -- the name of the subsystem to use in the XML
        sourceFile -- the path to the file to be used in the 'source'
                      attribute of the SubDetectors XML field.
                      If it is on castor, please prepend rfio:/
                      
        """
        paths = sourceFile.split("/")
        filename = paths[len(paths)-1]
        filename = re.sub("DQM.*V\d+_R\d+?__", "", filename)
        filename = subsystem + "_" + re.sub("\.root", ".XML", filename )
        if(self.verbose): print filename
        f = open(filename,'w')
        f.write('<SubDetectors name="' + subsystem + '" category="Run summary" source="' + sourceFile + '">\n')
        f.write('  <ReferenceEntity name=".*"/>\n')
        f.write('</SubDetectors>\n')
        f.close()
        return filename

    def produceSkeletonXMLAndInsert(self, subsystem, sourceFile, tag, run):
        """Produce a skeleton XML and insert it into the database.

        Keyword arguments:
        subsystem  -- the subsystem for which the skeleton XML has to
                      be created and inserted
        sourceFile -- the path to the file to be used in the 'source'
                      attribute of the SubDetectors XML field.
                      If it is on castor, please prepend rfio:/
        tag        -- the tag to be used
        run        -- teh run to be used

        """

        xml = self.produceSkeletonXML(subsystem, sourceFile)
        self.insertRow(tag, run, subsystem, xml)

    def cloneDBTag(self, oldTag, newTag):
        """
        This method is used to clone an existing tag into a new one. The copy is done for each
        and every IOV of the existing tag, for each and every subsystem of the existing
        tag.

        Keyword arguments:
        oldTag -- the tag that is to be cloned
        newTag -- the tag that is to be created

        """
        self.connect()
        statement = 'select InsertTime, LocalTag, StartRun, Subsystem, XML from ' + self.table_name + ' where localtag= "' + oldTag + '" and StartRun > 0'
        self.cur.execute(statement)
        rows = self.cur.fetchall()
        for row in rows:
            statement2 = 'insert into ' + self.table_name + ' ("InsertTime", "LocalTag", "StartRun", "Subsystem", "XML") values(?,?,?,?,?) '
            now = datetime.datetime.now()
            xml_orig = row[3]
#            xml_final = re.sub("source=\".*\"", 'source="' + sourceFile + '"', xml_orig) 
            if(self.verbose): print statement2,now,newTag, run,  row[1], row[2], row[3]
            cur3 = self.con.cursor() ;
            cur3.execute(statement2,(now,newTag, row[2], row[3], row[4] ) )
            self.con.commit()
        if(self.notification):
            self.notifyMe('[cloneDBTag]\nOriginal Tag ' + oldTag + '\nNew Tag ' + newTag + '\non DB ' + self.main_db)

    def modifySource(self, tag, run, subsystem, newSource):
        """
        This method is used to modify the source attribute of an
        existing XML, using the newSource field supplied by the
        end user
        
        Keyword arguments:
        tag       -- the tag to which the XML to modify belongs to
        run       -- the run to which the XML to modify belongs to
        subsystem -- the subsystem to which the XML to modify belongs to
        newSource -- the new source that has to be replaced into the
                     record identified by the previous 3 parameters

        """
        self.connect()
        statement = 'select InsertTime, LocalTag, StartRun, Subsystem, XML from ' + self.table_name + ' where localtag= "' + tag + '" and StartRun = ' + str(run) + ' and subsystem = "' + subsystem + '"'
        if self.verbose: print statement
        self.cur.execute(statement)
        rows = self.cur.fetchall()
#        print len(rows)
        if int(len(rows)) == 0:
            print 'No matching object to update: (', tag, ',', run, ',', subsystem, ')'
            return
        for row in rows:
            now = datetime.datetime.now()
            xml_orig = row[4]
            xml_final = re.sub("source=\".*\"", 'source="' + newSource + '"', xml_orig) 
            statement2 = 'update ' + self.table_name + ' set XML=?,LastUpdate=?  where localtag= "' + tag + '" and StartRun = ' + str(run) + ' and subsystem = "' + subsystem + '"'
            if self.verbose: print 'updating record ... ', statement2
            if self.verbose : print sqlite3.Binary(xml_final)
            cur2 = self.con.cursor() ;
            cur2.execute( statement2, (sqlite3.Binary(xml_final),now) )
            self.con.commit()
        if(self.notification):
            self.notifyMe('[modifySource]\nRecord to be modified\nTag ' + tag + '\nStartRun ' + str(run) + '\nSubsystem ' + subsystem + '\nNewSource ' + newSource + '\non DB ' + self.main_db)

    def syncWithCMSDBS(self, fast=True):
        """
        This method is used to modify the records that still
        have no information about their status on both
        orcoff_prep and orcoff_prod. These records are
        updated according to their status in the CMS DBs
        
        Keyword arguments:
        fast -- when set to True (default) the check on the CMS DBs
                is done only on the records that have not been
                already updated, that is the records that still
                have CMSDB='None'.
                When set to False, the check is done on ALL the
                records (actually all the distinct couples
                localtag-startrun) of the DB.

        """
#        self.syncWithCMSDB('cms_orcoff_prep', fast)
        self.syncWithCMSDB('cms_orcoff_prod', fast)
    
    def syncWithCMSDB(self, cmsdb, fast=True):
        """
        This method is used to modify the records that still
        have no information about their status on selected db.
        These records are updated according to their status
        in the specified CMS DBs
        
        Keyword arguments:
        fast -- when set to True (default) the check on the CMS DBs
                is done only on the records that have not been
                already updated, that is the records that still
                have CMSDB='None'.
                When set to False, the check is done on ALL the
                records (actually all the distinct couples
                localtag-startrun) of the DB.

        """
        # CMS_ORCOFF_PROD
        checker = IOV() #_Wrapper(cmsdb, 'CMS_COND_34X_DQM', self.verbose)
        self.connect()
        statement = 'select distinct localtag, startrun from ' + self.table_name + ' where StartRun >0'
        if fast:
            statement += ' and ' + cmsdb + ' = "None"'
        if self.verbose: print statement
        self.cur.execute(statement)
        rows = self.cur.fetchall()
        for row in rows:
#            checker.loadIOVs(row[0])
            now = datetime.datetime.now()
            statement = 'update ' + self.table_name + ' set ' + cmsdb + '=?,LastUpdate=? where localtag="' + row[0] + '" and StartRun =' + str(row[1]) 
            if self.verbose: print 'updating record ... ', statement
            if checker.checkPresence(str(row[0]), int(row[1])) :
                self.cur.execute(statement, ('Y', now))
                self.con.commit()
                if(self.notification):
                    self.notifyMe('[syncwithCMSDBS]\nRecord syncronized with ' + cmsdb + '\nTag ' + str(row[0]) + '\nStartRun ' + str(row[1]) + '\nStatus Y\non DB ' + self.main_db)
            else:
                self.cur.execute(statement, ('N', now))
                self.con.commit()
                if(self.notification):
                    self.notifyMe('[syncwithCMSDBS]\nRecord syncronized with ' + cmsdb + '\nTag ' + str(row[0]) + '\nStartRun ' + str(row[1]) + '\nStatus N\non DB ' + self.main_db)

        
    def modifyRun(self, tag, run, subsystem, newRun):
        """
        This method is used to modify the start Run of an
        existing record using the newRun field supplied by the
        end user.
        
        Keyword arguments:
        tag       -- the tag to which the record to modify belongs to
        run       -- the run to which the record to modify belongs to
        subsystem -- the subsystem to which the record to
                     modify belongs to
        newRun    -- the new run that has to be used

        """
        self.connect()
        now = datetime.datetime.now()
        statement = 'update ' + self.table_name + ' set StartRun=?,LastUpdate=?  where localtag= "' + tag + '" and StartRun = ' + str(run) + ' and subsystem = "' + subsystem + '"'
        if self.verbose: print 'updating record ... ', statement
        self.cur.execute(statement, (newRun, now))
        self.con.commit()
        if(self.notification):
            self.notifyMe('[modifyRun]\nRecord to be modified\n Tag ' + tag + '\nStartRun ' + str(run) + '\nSubsystem ' + subsystem + '\nNewRun ' + str(newRun) + '\non DB ' + self.main_db)

    def produceXMLFromDBSkeletonAndInsert(self, skeletonTag, sourceFile, tag, run):
        """Produce a new local tag from a skeleton one
        and insert latest payloads it into the database.
        The skeletonTag is not entirely cloned, but
        only the latest record for each subsystem
        is cloned, with the modified source, into
        the new local tag.

        Keyword arguments:
        skeletonTag -- the skeleton tag that you want to 'clone'
        sourceFile  -- the new source file that you want to be
                       put in the XML for the newly created
                       local tag.
        tag         -- the tag to be used
        run         -- teh run to be used

        """

        self.connect()
        statement = 'select distinct subsystem from ' + self.table_name + ' where localtag= "' + skeletonTag + '" and StartRun > 0'
        self.cur.execute(statement)
        rows = self.cur.fetchall()
        for row in rows:
#            pdb.set_trace()
            if not row[0]:
                continue
            cur2 = self.con.cursor()
            statement2 = 'select Max(StartRun), LocalTag,  Subsystem, XML from ' + self.table_name + ' where LocalTag = "' + skeletonTag + '" and SubSystem = "' + row[0] + '" and StartRun > 0'
            cur2.execute(statement2)
            for row in cur2:
                print 'Cloning ', row[2]
                statement3 = 'insert into ' + self.table_name + '  ("InsertTime", "LocalTag", "StartRun", "Subsystem", "XML") values(?,?,?,?,?) '
                now = datetime.datetime.now()
                xml_orig = row[3]
                xml_final = re.sub("source=\".*\"", 'source="' + sourceFile + '"', xml_orig) 
                if(self.verbose): print statement2,now,tag, run, row[2], xml_final
                cur3 = self.con.cursor() ;
                cur3.execute(statement3,(now,tag, run, row[2], xml_final ) )
                self.con.commit()
        if(self.notification):
            self.notifyMe('[produceXMLFromDBSkeletonAndInsert]\nSkeletonTag ' + skeletonTag + '\nTag ' + tag + '\nStartRun ' + str(run) + '\nSource ' + sourceFile + '\non DB ' + self.main_db)

    def produceXMLFromDBSkeletonAndInsertNoSourceOverried(self, skeletonTag, sourceFile, tag, run):
        """Produce a new local tag from a skeleton one
        and insert latest payloads it into the database.
        The skeletonTag is not entirely clone, but
        only the latest record for each subsystem
        is cloned, with NO modified source, into
        the new local tag.

        Keyword arguments:
        skeletonTag -- the skeleton tag that you want to 'clone'
        sourceFile  -- useless, never used
        tag         -- the tag to be used
        run         -- teh run to be used

        """

        self.connect()
        statement = 'select distinct subsystem from ' + self.table_name + ' where localtag= "' + skeletonTag + '" and StartRun > 0'
        self.cur.execute(statement)
        rows = self.cur.fetchall()
        for row in rows:
#            pdb.set_trace()
            if not row[0]:
                continue
            cur2 = self.con.cursor()
            statement2 = 'select Max(StartRun), LocalTag,  Subsystem, XML from ' + self.table_name + ' where LocalTag = "' + skeletonTag + '" and SubSystem = "' + row[0] + '" and StartRun > 0'
            cur2.execute(statement2)
            for row in cur2:
                print 'Cloning ', row[2]
                statement3 = 'insert into ' + self.table_name + '  ("InsertTime", "LocalTag", "StartRun", "Subsystem", "XML") values(?,?,?,?,?) '
                now = datetime.datetime.now()
                xml_orig = row[3]
                #xml_final = re.sub("source=\".*\"", 'source="' + sourceFile + '"', xml_orig) 
                if(self.verbose): print statement2,now,tag, run, row[2], xml_final
                cur3 = self.con.cursor() ;
                cur3.execute(statement3,(now,tag, run, row[2], xml_final ) )
                self.con.commit()
        if(self.notification):
            self.notifyMe('[produceXMLFromDBSkeletonAndInsertNoSourceOverrid]\nSkeletonTag ' + skeletonTag + '\nTag ' + tag + 'StartRun ' + str(run) + '\nSubsystem ' + subsystem + '\non DB ' + self.main_db)

    def JsonIt(self, cursor):
        """
        This is a simple method that, given a cursor (filled after
        executing a query) will print the correspondig JSON Object
        """
        r = cursor.fetchone()
        obj = ''
        obj += '{"rows": ['
        while r:
            obj += '{'
            for key in r.keys():
                obj += ' "' + str(key) + '": "' + str(r[key]) + '",'
            obj = re.sub(',$', '', obj)
            obj += ' },'
            r = self.cur.fetchone()
        obj = re.sub(',$', '', obj)
        obj += ']}'
        print obj

    def prettyPrintIt(self, cursor):
        """
        This is a simple method that, given a cursor (filled after
        executing a query) will pretty print it on the screen
        """
        r = cursor.fetchone()
        while r:
            for key in r.keys():
                print key, r[key]
            r = self.cur.fetchone()


    def notifyMe(self, message):
        """Send an email notification to the appropriate recipient
        with fixed SUBJECT(ReferenceWebGUI) and the specified message body.
        This function is triggered only if the class variable notification
        is set to True in the constructor (which is the default case)."""

        me = 'marco.rovere@cern.ch'
        eduardo = 'erosales@cern.ch'
        msg = email.message_from_string(message+'\n')
        msg['Subject'] = 'ReferenceWebGUI'
        msg['From']    = me
        msg['To']      = self.rcpt

        if(self.verbose):
            print msg.as_string()

        s=smtplib.SMTP("localhost")
        s.sendmail(me, [self.rcpt], msg.as_string())
        s.sendmail(eduardo, [self.rcpt], msg.as_string())
        s.quit()


class DBOptionParser:
    def __init__(self):
        self.parser = OptionParser()
        self.group_general = OptionGroup(self.parser, "General Options",
                                         "These options are used with other options(insert/list/update)."
                                         )
        self.group_insert  = OptionGroup(self.parser, "Insertion Options",
                                         "These options directly insert new records into the Database: use with caution."
                                         )
        self.group_list    = OptionGroup(self.parser, "Listing Options",
                                         "These options dump values from the Database to the terminal."
                                         )
        self.group_update  = OptionGroup(self.parser, "Update Options",
                                         "These options directly update existing records into the Database: use with caution."
                                         )
        self.group_output  = OptionGroup(self.parser, "Output Options",
                                         "These options produce the final XML to be used to assemble a DQM Reference Histogram File."
                                         )
        
    def parseOptions(self):
        (self.options, self.args) = self.parser.parse_args()

    def error(self, message):
        self.parser.error(message)
    
    def clone(self):
        return self.options.cloneSkeletonTag

    def db(self):
        return self.options.db

    def db_filename(self):
        return self.options.db_filename

    def duplicateTag(self):
        return self.options.duplicateTag

    def insert(self):
        return self.options.insert

    def fastsync(self):
        return self.options.fastsync

    def faketag(self):
        return self.options.faketag

    def g(self):
        return self.options.g

    def source(self):
        return self.options.source

    def l(self):
        return self.options.l

    def ltag(self):
        return self.options.ltag

    def lsub(self):
        return self.options.lsub

    def modifySource(self):
        return self.options.modifySource

    def modifyRun(self):
        return self.options.modifyRun

    def n(self):
        return self.options.n

    def o(self):
        return self.options.o

    def p(self):
        return self.options.p

    def t(self):
        return self.options.t

    def r(self):
        return self.options.r

    def x(self):
        return self.options.x

    def s(self):
        return self.options.s

    def sync(self):
        return self.options.sync

    def v(self):
        return self.options.v

    def u(self):
        return self.options.u


    def defineOptions(self):
        # GENARAL OPTIONS

        self.group_general.add_option("-d",
                                      "--database",
                                      dest="db",
                                      default="referenceHisto.db",
                                      help="Use the specified database to perform operations." +
                                      " If this option is not specified, the default is used [referenceHisto.db].",
                                      action="store",
                                      type="string",
                                      metavar="DB_NAME")
        self.group_general.add_option("--source",
                                      dest="source",
                                      help="Source file to be used when producing skeleton XMLs",
                                      action="store")
        self.group_general.add_option("-r",
                                      "--run",
                                      dest="r",
                                      type="long",
                                      help="Specify the starting run from which this record is valid (inclusive)",
                                      action="store",
                                      metavar="RUN")
        self.group_general.add_option("-s",
                                      "--subsystem",
                                      dest="s",
                                      type="string",
                                      help="Specify to which subsystem this record belongs to",
                                      action="store",
                                      metavar="SUBSYS")
        self.group_general.add_option("-t",
                                      "--tag",
                                      dest="t",
                                      help="Specify which local tag is to be used to insert data",
                                      action="store",
                                      metavar="TAG")
        self.group_general.add_option("-v",
                                      "--verbose",
                                      dest="v",
                                      help="Verbose printouts",
                                      action="store_true",
                                      default=False)
        self.group_general.add_option("-n",
                                      "--no-notify",
                                      dest="n",
                                      help="Suppress Email Notifications",
                                      action="store_false",
                                      default=True)        
        self.group_general.add_option("-x",
                                      "--xml-file",
                                      dest="x",
                                      type="string",
                                      help="Specify the location of the XML file to upload into the DB",
                                      action="store",
                                      metavar="XML")
        self.group_general.add_option("--oracle",
                               dest="o",
                               type="string",
                               help="Use the specified Oracle DB to produce the Metadata file (cms_orcoff_prep|cms_orcoff_prod)",
                               action="store",
                               metavar="ORACLE_DB")
        self.parser.add_option_group(self.group_general)

        # INSERTION OPTIONS

        self.group_insert.add_option("-c",
                                     "--create",
                                     dest="db_filename",
                                     help="Create Database Table from scratch using the supplied filename",
                                     action="store",
                                     type="string",
                                     metavar="DB_NAME")
        self.group_insert.add_option("-i",
                                     "--insert",
                                     dest="insert",
                                     help="Insert new data into the database."+
                                     " Options -r -t -s -x are mandatory. Calls insertRow().",
                                     action="store_true",
                                     default=False)
        self.group_insert.add_option("--faketag",
                                     dest="faketag",
                                     help="Create a fake Tag that needs to be filled in via Web Application."
                                     + " Calls createFakeLocalTag().",
                                     action="store",
                                     metavar="TAG")
        self.group_insert.add_option("--cloneskeleton",
                                     dest="cloneSkeletonTag",
                                     help="Create New records based on the specified skeleton." +
                                     " Calls produceXMLFromDBSkeletonAndInsert()"
                                     ,action="store",
                                     type="string",
                                     metavar="TAG_TO_CLONE")
        self.group_insert.add_option("--duplicate",
                                     dest="duplicateTag",
                                     help="Deep copy the specified tag into a new one."+
                                     " The option -t is mandatory.",
                                     action="store",
                                     type="string",
                                     metavar="TAG_TO_DUPLICATE" )
        self.group_insert.add_option("--generate",
                                     dest="g",
                                     help="Generate a skeleton XML for all the known subsystems using the specified file a source." +
                                     " The --source parameter is mandatory." +
                                     " The default behaviour is to dump the produced XMLs into the local filesystem (using produceSkeletonXML)." +
                                     " If used with -u option, the XMLs will be uploaded directly into the DB (using produceSkeletopnXMLAndInsert). In" +
                                     " this case the options -t and -r are mandatory.",
                                     action="store_true",
                                     default=False
                                     )
        self.group_insert.add_option("-u",
                                     "--upload",
                                     dest="u",
                                     help="Upload new skeleton XML files into the database using specified tag(mandatory) and run(mandatory)",
                                     action="store_true",
                                     default=False)
        self.parser.add_option_group(self.group_insert)
        
        # LISTING OPTIONS

        self.group_list.add_option("-l",
                                   "--list",
                                   dest="l",
                                   help="List all local tags known to the specified Database",
                                   action="store_true")
        self.group_list.add_option("--localtag",
                                   dest="ltag",
                                   help="List the specified local tag composition",
                                   action="store",
                                   metavar="TAG")
        self.group_list.add_option("--listsub",
                                   dest="lsub",
                                   help="List all known subsystems",
                                   action="store_true")
        self.parser.add_option_group(self.group_list)

        # UPDATE OPTIONS
        
        self.group_update.add_option("--modifyRun",
                                     dest="modifyRun",
                                     help="Modify the StartRun of an existing record identified by localtag, run and subsystem",
                                     action="store",
                                     metavar="NEW_RUN")
        self.group_update.add_option("--modifySource",
                                     dest="modifySource",
                                     help="Modify the source attribute of the XML specified by localtag, run and subsystem",
                                     action="store",
                                     metavar="NEW_SOURCE")
        self.group_update.add_option("--sync",
                                     dest="sync",
                                     help="Like fastsync, but the check is done on all records, including the ones already sync'ed.",
                                     action="store_true")
        self.group_update.add_option("--fastsync",
                                     dest="fastsync",
                                     help="Modify a record to see if it is present in orcoff_prod and orcoff_prep. It only checks records that were not already sync'ed.",
                                     action="store_true")
        self.parser.add_option_group(self.group_update)

        # OUPUT OPTIONS

        self.group_output.add_option("-p",
                                     "--producefortag",
                                     dest="p",
                                     type="string",
                                     help="Produce all necessary XMLs for the specified localTag in the range 1 to infinity",
                                     action="store",
                                     metavar="TAG")
        self.parser.add_option_group(self.group_output)
                
Revision:	1.11
Committed:	Mon May 21 15:14:00 2012 UTC (12 years, 11 months ago) by erosales
Content type:	text/x-python
Branch:	MAIN
CVS Tags:	JSON-release-pp-2012Nov23, JSON-release-pp-20121026, HEAD
Changes since 1.10:	+53 -2 lines
Log Message:	Corrections to support the PayloadChecker.py script