Esempio n. 1
0
    def __init__(self):
        """
        Constructor.
        """

        warnings.simplefilter('default')
        warnings.warn("This module is deprecated in favor of SEKNotifier.",
                      DeprecationWarning)

        self.config = MSGConfiger()
        self.logger = SEKLogger(__name__, 'info')
        self.connector = MSGDBConnector()
        self.conn = self.connector.connectDB()
        self.cursor = self.conn.cursor()
        self.dbUtil = MSGDBUtil()
        self.noticeTable = 'NotificationHistory'
        self.notificationHeader = "This is a message from the Hawaii Smart " \
                                  "Energy Project MSG Project notification " \
                                  "system.\n\n"

        self.noReplyNotice = '\n\nThis email account is not monitored. No ' \
                             'replies will originate from this ' \
                             'account.\n\nYou are receiving this message ' \
                             'because you are on the recipient list for ' \
                             'notifications for the Hawaii Smart Energy ' \
                             'Project.'
class TestMSGDBConnect(unittest.TestCase):
    """
    These tests require a database connection to be available.
    """

    def setUp(self):
        self.connector = MSGDBConnector(True)
        self.conn = self.connector.connectDB()
        self.configer = MSGConfiger()

    def test_init(self):
        self.assertTrue(
            isinstance(self.connector, msg_db_connector.MSGDBConnector),
            "self.connection is an instance of MECODBConnector.")

    def test_db_connection(self):
        """
        DB can be connected to.
        """
        self.assertIsNotNone(self.conn, 'DB connection not available.')

        # Get the name of the database.
        self.assertEqual(
            self.configer.configOptionValue('Database', 'testing_db_name'),
            self.connector.dbName, 'Testing DB name is not correct.')

    def tearDown(self):
        self.connector.closeDB(self.conn)
Esempio n. 3
0
class TestMSGDBConnect(unittest.TestCase):
    """
    These tests require a database connection to be available.
    """
    def setUp(self):
        self.connector = MSGDBConnector(True)
        self.conn = self.connector.connectDB()
        self.configer = MSGConfiger()

    def test_init(self):
        self.assertTrue(
            isinstance(self.connector, msg_db_connector.MSGDBConnector),
            "self.connection is an instance of MECODBConnector.")

    def test_db_connection(self):
        """
        DB can be connected to.
        """
        self.assertIsNotNone(self.conn, 'DB connection not available.')

        # Get the name of the database.
        self.assertEqual(
            self.configer.configOptionValue('Database', 'testing_db_name'),
            self.connector.dbName, 'Testing DB name is not correct.')

    def tearDown(self):
        self.connector.closeDB(self.conn)
    def test_log_successful_export(self):
        """
        Test logging of export results to the export history table.
        """
        # @REVIEWED
        self.assertTrue(self.exporter.logSuccessfulExport(name = 'test_export',
                                                          url =
                                                          'http://test_url',
                                                          datetime = 0,
                                                          size = 100))

        conn = MSGDBConnector().connectDB()
        cursor = conn.cursor()
        dbUtil = MSGDBUtil()

        self.assertTrue(
            dbUtil.executeSQL(cursor, 'select * from "ExportHistory" where '
                                      'timestamp = '
                                      'to_timestamp(0)'))

        self.assertEqual(len(cursor.fetchall()), 1,
                         "There should only be one result row.")

        self.assertTrue(
            dbUtil.executeSQL(cursor, 'delete from "ExportHistory" where '
                                      'timestamp = to_timestamp(0)'))
        conn.commit()
 def setUp(self):
     self.dupeChecker = MECODupeChecker()
     self.p = MECOXMLParser(True)  # run in testing mode
     self.dbConnect = MSGDBConnector(True)
     self.dbUtil = MSGDBUtil()
     self.conn = self.dbConnect.connectDB()
     self.cur = self.conn.cursor()
Esempio n. 6
0
    def test_log_successful_export(self):
        """
        Test logging of export results to the export history table.
        """
        # @REVIEWED
        self.assertTrue(
            self.exporter.logSuccessfulExport(name='test_export',
                                              url='http://test_url',
                                              datetime=0,
                                              size=100))

        conn = MSGDBConnector().connectDB()
        cursor = conn.cursor()
        dbUtil = MSGDBUtil()

        self.assertTrue(
            dbUtil.executeSQL(
                cursor, 'select * from "ExportHistory" where '
                'timestamp = '
                'to_timestamp(0)'))

        self.assertEqual(len(cursor.fetchall()), 1,
                         "There should only be one result row.")

        self.assertTrue(
            dbUtil.executeSQL(
                cursor, 'delete from "ExportHistory" where '
                'timestamp = to_timestamp(0)'))
        conn.commit()
    def __init__(self):
        """
        Constructor.
        """

        self.logger = SEKLogger(__name__, 'DEBUG')
        self.cursor = MSGDBConnector().connectDB().cursor()
        self.dbUtil = MSGDBUtil()
Esempio n. 8
0
 def setUp(self):
     self.reader = MECODBReader()
     self.connector = MSGDBConnector(True)
     self.conn = self.connector.connectDB()
     self.inserter = MECODBInserter()
     self.util = MSGDBUtil()
     self.lastSeqVal = None
     self.tableName = 'MeterData'
     self.colName = 'meter_data_id'
     self.deleter = MECODBDeleter()
    def __init__(self):
        """
        Constructor.
        """

        self.logger = SEKLogger(__name__)
        self.viewPVReadingsinNonMLH = ''
        self.lastDateProcessed = None
        self.connector = MSGDBConnector()
        self.conn = self.connector.connectDB()
class TestMECODupeChecker(unittest.TestCase):
    """
    Unit tests for duplicate checking.
    """

    def setUp(self):
        self.dupeChecker = MECODupeChecker()
        self.p = MECOXMLParser(True) # run in testing mode
        self.dbConnect = MSGDBConnector(True)
        self.dbUtil = MSGDBUtil()
        self.conn = self.dbConnect.connectDB()
        self.cur = self.conn.cursor()

    def testInit(self):
        self.assertEqual(self.dupeChecker.__class__.__name__, "MECODupeChecker",
                         "Dupe checker has been created.")

    def testFindIndividualDupe(self):
        """
        Find a duplicate record when only one exists.
        """
        self.dbUtil.eraseTestMeco()

        self.p.filename = "../../test-data/meco_v3-energy-test-data.xml"
        fileObject = open(self.p.filename, "rb")
        self.p.parseXML(fileObject, True)

        self.assertTrue(
            self.dupeChecker.readingBranchDupeExists(self.conn, '100000',
                                                     '2013-04-08 00:30:00',
                                                     '1', True),
            "Record should already exist")

    def testLoadOnTop(self):
        """
        If the same data set is loaded in succession,
        all values will be duplicated. Verify that this is true.

        This is no longer possible as
        duplicates are dropped before insertion.
        """

        pass

    def testLoadSingleMissingEntry(self):
        """
        A reading will be inserted into the database where the reading does
        not currently exist as determined by the
        MeterName-IntervalEndTime-Channel tuple.
        """

        pass

    def tearDown(self):
        self.dbConnect.closeDB(self.conn)
class TestMECODupeChecker(unittest.TestCase):
    """
    Unit tests for duplicate checking.
    """
    def setUp(self):
        self.dupeChecker = MECODupeChecker()
        self.p = MECOXMLParser(True)  # run in testing mode
        self.dbConnect = MSGDBConnector(True)
        self.dbUtil = MSGDBUtil()
        self.conn = self.dbConnect.connectDB()
        self.cur = self.conn.cursor()

    def testInit(self):
        self.assertEqual(self.dupeChecker.__class__.__name__,
                         "MECODupeChecker", "Dupe checker has been created.")

    def testFindIndividualDupe(self):
        """
        Find a duplicate record when only one exists.
        """
        self.dbUtil.eraseTestMeco()

        self.p.filename = "../../test-data/meco_v3-energy-test-data.xml"
        fileObject = open(self.p.filename, "rb")
        self.p.parseXML(fileObject, True)

        self.assertTrue(
            self.dupeChecker.readingBranchDupeExists(self.conn, '100000',
                                                     '2013-04-08 00:30:00',
                                                     '1', True),
            "Record should already exist")

    def testLoadOnTop(self):
        """
        If the same data set is loaded in succession,
        all values will be duplicated. Verify that this is true.

        This is no longer possible as
        duplicates are dropped before insertion.
        """

        pass

    def testLoadSingleMissingEntry(self):
        """
        A reading will be inserted into the database where the reading does
        not currently exist as determined by the
        MeterName-IntervalEndTime-Channel tuple.
        """

        pass

    def tearDown(self):
        self.dbConnect.closeDB(self.conn)
Esempio n. 12
0
    def __init__(self, testing = False):
        """
        Constructor.

        :param testing: True if in testing mode.
        """

        self.connector = MSGDBConnector()
        self.conn = MSGDBConnector(testing).connectDB()
        self.dbUtil = MSGDBUtil()
        self.dbName = self.dbUtil.getDBName(self.connector.dictCur)
Esempio n. 13
0
 def setUp(self):
     self.i = MECODBInserter()
     self.util = MSGDBUtil()
     self.connector = MSGDBConnector(True)
     self.deleter = MECODBDeleter()
     self.reader = MECODBReader()
     self.lastSeqVal = None
     self.conn = self.connector.connectDB()
     self.sampleTableName = 'MeterData'
     self.sampleDict = {'MeterName': '100001', 'UtilDeviceID': '100001',
                        'MacID': '00:00:00:00:00:00:00:00'}
     self.keyName = 'meter_data_id'
Esempio n. 14
0
 def __init__(self):
     """
     Constructor.
     """
     self.logger = SEKLogger(__name__, 'DEBUG')
     self.aggregator = MSGDataAggregator()
     self.notifier = MSGNotifier()
     self.rawTypes = [x.name for x in list(MSGAggregationTypes)]
     self.connector = MSGDBConnector()
     self.conn = self.connector.connectDB()
     self.cursor = self.conn.cursor()
     self.dbUtil = MSGDBUtil()
    def __init__(self,
                 exitOnError=True,
                 commitOnEveryInsert=False,
                 testing=False):
        """
        Constructor.

        :param testing: if True, the testing DB will be connected instead of
        the production DB.
        """

        self.logger = SEKLogger(__name__, 'info')
        self.configer = MSGConfiger()
        self.conn = MSGDBConnector().connectDB()
        self.cursor = self.conn.cursor()
        self.dbUtil = MSGDBUtil()
        self.notifier = MSGNotifier()
        self.mathUtil = MSGMathUtil()
        self.timeUtil = MSGTimeUtil()
        self.nextMinuteCrossing = {}
        self.nextMinuteCrossingWithoutSubkeys = None
        self.exitOnError = exitOnError
        self.commitOnEveryInsert = commitOnEveryInsert
        section = 'Aggregation'
        tableList = [
            'irradiance', 'agg_irradiance', 'weather', 'agg_weather',
            'circuit', 'agg_circuit', 'egauge', 'agg_egauge'
        ]
        self.dataParams = {
            'weather': ('agg_weather', 'timestamp', ''),
            'egauge': ('agg_egauge', 'datetime', 'egauge_id'),
            'circuit': ('agg_circuit', 'timestamp', 'circuit'),
            'irradiance': ('agg_irradiance', 'timestamp', 'sensor_id')
        }
        self.columns = {}

        # tables[datatype] gives the table name for datatype.
        self.tables = {
            t: self.configer.configOptionValue(section, '{}_table'.format(t))
            for t in tableList
        }

        for t in self.tables.keys():
            self.logger.log('t:{}'.format(t), 'DEBUG')
            try:
                self.columns[t] = self.dbUtil.columnsString(
                    self.cursor, self.tables[t])
            except TypeError as error:
                self.logger.log(
                    'Ignoring missing table: Error is {}.'.format(error),
                    'error')
Esempio n. 16
0
    def __init__(self):
        """
        Constructor.
        """

        self.logger = SEKLogger(__name__, 'DEBUG', useColor=False)
        self.timeUtil = MSGTimeUtil()
        self.configer = MSGConfiger()
        self.fileUtil = MSGFileUtil()
        self.pythonUtil = MSGPythonUtil()  # for debugging
        self.connector = MSGDBConnector()
        self.conn = self.connector.connectDB()
        self.cursor = self.conn.cursor()
        self.dbUtil = MSGDBUtil()
        self.notifier = SEKNotifier(
            connector=self.connector,
            dbUtil=self.dbUtil,
            user=self.configer.configOptionValue('Notifications',
                                                 'email_username'),
            password=self.configer.configOptionValue('Notifications',
                                                     'email_password'),
            fromaddr=self.configer.configOptionValue('Notifications',
                                                     'email_from_address'),
            toaddr=self.configer.configOptionValue('Notifications',
                                                   'email_recipients'),
            testing_toaddr=self.configer.configOptionValue(
                'Notifications', 'testing_email_recipients'),
            smtp_server_and_port=self.configer.configOptionValue(
                'Notifications', 'smtp_server_and_port'))

        # Google Drive parameters.
        self.clientID = self.configer.configOptionValue(
            'Export', 'google_api_client_id')
        self.clientSecret = self.configer.configOptionValue(
            'Export', 'google_api_client_secret')
        self.oauthScope = 'https://www.googleapis.com/auth/drive'
        self.oauthConsent = 'urn:ietf:wg:oauth:2.0:oob'
        self.googleAPICredentials = ''
        self.exportTempWorkPath = self.configer.configOptionValue(
            'Export', 'db_export_work_path')

        self.credentialPath = self.configer.configOptionValue(
            'Export', 'google_api_credentials_path')
        self.credentialStorage = Storage('{}/google_api_credentials'.format(
            self.credentialPath))

        self._driveService = None
        self._cloudFiles = None
        self.postAgent = 'Maui Smart Grid 1.0.0 DB Exporter'
        self.retryDelay = 10
        self.availableFilesURL = ''
Esempio n. 17
0
    def __init__(self):
        """
        Constructor.
        """
        self.logger = SEKLogger(__name__, 'DEBUG')
        self.aggregator = MSGDataAggregator(exitOnError=False,
                                            commitOnEveryInsert=True)
        self.notifier = MSGNotifier()

        # Available types are in ['weather', 'egauge', 'circuit', 'irradiance'].
        self.rawTypes = ['']
        self.connector = MSGDBConnector()
        self.conn = self.connector.connectDB()
        self.cursor = self.conn.cursor()
        self.dbUtil = MSGDBUtil()
class TestMECOXMLParser(unittest.TestCase):
    """
    Unit tests for MECO XML Parser.
    """

    def setUp(self):
        self.p = MECOXMLParser(True) # run in testing mode
        self.dbConnect = MSGDBConnector(True)
        self.dbUtil = MSGDBUtil()
        self.conn = self.dbConnect.connectDB()
        self.cur = self.conn.cursor()

    def testMECOXMLParserCanBeInited(self):
        self.assertIsNotNone(self.p)

    def testEveryElementIsVisited(self):
        self.dbUtil.eraseTestMeco()

        self.p.filename = "../../test-data/meco_v3-energy-test-data.xml"
        fileObject = open(self.p.filename, "rb")
        expectedCount = 125
        self.p.parseXML(fileObject, True)
        print "element count = %s" % self.p.processForInsertElementCount
        self.assertEqual(self.p.processForInsertElementCount, expectedCount)

    def testAllTableNamesArePresent(self):
        self.dbUtil.eraseTestMeco()

        self.p.filename = "../../test-data/meco_v3-energy-test-data.xml"
        fileObject = open(self.p.filename, "rb")
        self.p.parseXML(fileObject, True)
        fail = False

        for key in self.p.tableNameCount.keys():
            print key + ": ",
            print self.p.tableNameCount[key]

            if self.p.tableNameCount[key] < 1:
                if key != 'ChannelStatus' and key != 'IntervalStatus' and key \
                        != 'EventData' and key != 'Event':
                    print "table = %s" % key
                    fail = True
        self.assertFalse(fail,
                         "At least one table of each type should have been "
                         "encountered.")

    def tearDown(self):
        self.dbConnect.closeDB(self.conn)
class AllDataAggregator(object):
    """
    Perform aggregation of all data for a set of predefined data types.

    In this class, the raw types are left as empty to allow manual
    specification.
    """

    def __init__(self):
        """
        Constructor.
        """
        self.logger = SEKLogger(__name__, 'DEBUG')
        self.aggregator = MSGDataAggregator(exitOnError = False,
                                            commitOnEveryInsert = True)
        self.notifier = MSGNotifier()

        # Available types are in ['weather', 'egauge', 'circuit', 'irradiance'].
        self.rawTypes = ['']
        self.connector = MSGDBConnector()
        self.conn = self.connector.connectDB()
        self.cursor = self.conn.cursor()
        self.dbUtil = MSGDBUtil()

    def aggregateAllData(self):
        """
        :return: Nothing.
        """

        map(self.aggregator.aggregateAllData, self.rawTypes)
Esempio n. 20
0
    def setUp(self):
        self.logger = SEKLogger(__name__, 'DEBUG')
        self.configer = MSGConfiger()
        self.exporter = MSGDBExporter()
        self.testDir = 'db_exporter_test'
        self.uncompressedTestFilename = 'meco_v3_test_data.sql'
        self.compressedTestFilename = 'meco_v3_test_data.sql.gz'
        self.exportTestDataPath = self.configer.configOptionValue(
            'Testing', 'export_test_data_path')
        self.fileUtil = MSGFileUtil()
        self.fileChunks = []
        self.testDataFileID = ''
        self.pyUtil = MSGPythonUtil()
        self.timeUtil = MSGTimeUtil()

        conn = None
        try:
            conn = MSGDBConnector().connectDB()
        except Exception as detail:
            self.logger.log("Exception occurred: {}".format(detail), 'error')
            exit(-1)

        self.logger.log("conn = {}".format(conn), 'debug')
        self.assertIsNotNone(conn)

        # Create a temporary working directory.
        try:
            os.mkdir(self.testDir)
        except OSError as detail:
            self.logger.log(
                'Exception during creation of temp directory: %s' % detail,
                'ERROR')
Esempio n. 21
0
class AllDataAggregator(object):
    """
    Perform aggregation of all data for a set of predefined data types.

    In this class, the raw types are left as empty to allow manual
    specification.
    """
    def __init__(self):
        """
        Constructor.
        """
        self.logger = SEKLogger(__name__, 'DEBUG')
        self.aggregator = MSGDataAggregator(exitOnError=False,
                                            commitOnEveryInsert=True)
        self.notifier = MSGNotifier()

        # Available types are in ['weather', 'egauge', 'circuit', 'irradiance'].
        self.rawTypes = ['']
        self.connector = MSGDBConnector()
        self.conn = self.connector.connectDB()
        self.cursor = self.conn.cursor()
        self.dbUtil = MSGDBUtil()

    def aggregateAllData(self):
        """
        :return: Nothing.
        """

        map(self.aggregator.aggregateAllData, self.rawTypes)
 def setUp(self):
     self.dupeChecker = MECODupeChecker()
     self.p = MECOXMLParser(True) # run in testing mode
     self.dbConnect = MSGDBConnector(True)
     self.dbUtil = MSGDBUtil()
     self.conn = self.dbConnect.connectDB()
     self.cur = self.conn.cursor()
Esempio n. 23
0
class MECODBReader(object):
    """
    Read records from a database.
    """

    def __init__(self, testing = False):
        """
        Constructor.

        :param testing: True if in testing mode.
        """

        self.connector = MSGDBConnector()
        self.conn = MSGDBConnector(testing).connectDB()
        self.dbUtil = MSGDBUtil()
        self.dbName = self.dbUtil.getDBName(self.connector.dictCur)

    def selectRecord(self, conn, table, keyName, keyValue):
        """
        Read a record in the database given a table name, primary key name,
        and value for the key.

        :param conn DB connection
        :param table DB table name
        :param keyName DB column name for primary key
        :param keyValue Value to be matched
        :returns: Row containing record data.
        """

        print "selectRecord:"
        sql = """SELECT * FROM "%s" WHERE %s = %s""" % (
        table, keyName, keyValue)
        dcur = conn.cursor(cursor_factory = psycopg2.extras.DictCursor)
        self.dbUtil.executeSQL(dcur, sql)
        row = dcur.fetchone()
        return row

    def readingAndMeterCounts(self):
        """
        Retrieve the reading and meter counts.

        :returns: Multiple lists containing the retrieved data.
        """

        sql = """SELECT "Day", "Reading Count",
        "Meter Count" FROM count_of_readings_and_meters_by_day"""
        dcur = self.conn.cursor(cursor_factory = psycopg2.extras.DictCursor)
        self.dbUtil.executeSQL(dcur, sql)
        rows = dcur.fetchall()

        dates = []
        meterCounts = []
        readingCounts = []

        for row in rows:
            dates.append(row[0])
            readingCounts.append(row[1] / row[2])
            meterCounts.append(row[2])

        return dates, readingCounts, meterCounts
class TestMECOXMLParser(unittest.TestCase):
    """
    Unit tests for MECO XML Parser.
    """
    def setUp(self):
        self.p = MECOXMLParser(True)  # run in testing mode
        self.dbConnect = MSGDBConnector(True)
        self.dbUtil = MSGDBUtil()
        self.conn = self.dbConnect.connectDB()
        self.cur = self.conn.cursor()

    def testMECOXMLParserCanBeInited(self):
        self.assertIsNotNone(self.p)

    def testEveryElementIsVisited(self):
        self.dbUtil.eraseTestMeco()

        self.p.filename = "../../test-data/meco_v3-energy-test-data.xml"
        fileObject = open(self.p.filename, "rb")
        expectedCount = 125
        self.p.parseXML(fileObject, True)
        print "element count = %s" % self.p.processForInsertElementCount
        self.assertEqual(self.p.processForInsertElementCount, expectedCount)

    def testAllTableNamesArePresent(self):
        self.dbUtil.eraseTestMeco()

        self.p.filename = "../../test-data/meco_v3-energy-test-data.xml"
        fileObject = open(self.p.filename, "rb")
        self.p.parseXML(fileObject, True)
        fail = False

        for key in self.p.tableNameCount.keys():
            print key + ": ",
            print self.p.tableNameCount[key]

            if self.p.tableNameCount[key] < 1:
                if key != 'ChannelStatus' and key != 'IntervalStatus' and key \
                        != 'EventData' and key != 'Event':
                    print "table = %s" % key
                    fail = True
        self.assertFalse(
            fail, "At least one table of each type should have been "
            "encountered.")

    def tearDown(self):
        self.dbConnect.closeDB(self.conn)
Esempio n. 25
0
class TestMECODBRead(unittest.TestCase):
    def setUp(self):
        self.reader = MECODBReader()
        self.connector = MSGDBConnector(True)
        self.conn = self.connector.connectDB()
        self.inserter = MECODBInserter()
        self.util = MSGDBUtil()
        self.lastSeqVal = None
        self.tableName = 'MeterData'
        self.colName = 'meter_data_id'
        self.deleter = MECODBDeleter()

    def testMECODBReadCanBeInited(self):
        self.assertIsNotNone(self.reader)

    def testSelectRecord(self):
        """
        Insert and retrieve a record to test the ability to select a record.
        """

        print "testSelectRecord:"
        print "self.conn = %s" % self.conn

        sampleDict = {
            'MeterName': '100001',
            'UtilDeviceID': '100001',
            'MacID': '00:00:00:00:00:00:00:00'
        }
        self.inserter.insertData(self.conn, self.tableName, sampleDict)
        self.lastSeqVal = self.util.getLastSequenceID(self.conn,
                                                      self.tableName,
                                                      self.colName)

        print "lastSeqVal = %s" % self.lastSeqVal

        row = self.reader.selectRecord(self.conn, self.tableName, self.colName,
                                       self.lastSeqVal)
        self.assertEqual(row[self.colName], self.lastSeqVal)

    def tearDown(self):
        # Delete the record that was inserted.
        if self.lastSeqVal != None:
            self.deleter.deleteRecord(self.conn, self.tableName, self.colName,
                                      self.lastSeqVal)

        self.connector.closeDB(self.conn)
    def __init__(self):
        """
        Constructor.
        """

        self.logger = SEKLogger(__name__)
        self.viewPVReadingsinNonMLH = ''
        self.lastDateProcessed = None
        self.connector = MSGDBConnector()
        self.conn = self.connector.connectDB()
Esempio n. 27
0
    def setUp(self):
        self.i = MECODBInserter()

        # Connect to the testing database.
        self.connector = MSGDBConnector(testing=True)

        self.conn = self.connector.connectDB()
        self.lastSeqVal = None

        # Does this work having the dictCur be in another class?
        self.dictCur = self.connector.dictCur

        self.cursor = self.conn.cursor()
        self.deleter = MECODBDeleter()
        self.tableName = 'MeterData'
        self.columnName = 'meter_data_id'
        self.configer = MSGConfiger()
        self.logger = SEKLogger(__name__, 'debug')
        self.dbUtil = MSGDBUtil()
 def setUp(self):
     self.reader = MECODBReader()
     self.connector = MSGDBConnector(True)
     self.conn = self.connector.connectDB()
     self.inserter = MECODBInserter()
     self.util = MSGDBUtil()
     self.lastSeqVal = None
     self.tableName = 'MeterData'
     self.colName = 'meter_data_id'
     self.deleter = MECODBDeleter()
class TestMECODBRead(unittest.TestCase):
    def setUp(self):
        self.reader = MECODBReader()
        self.connector = MSGDBConnector(True)
        self.conn = self.connector.connectDB()
        self.inserter = MECODBInserter()
        self.util = MSGDBUtil()
        self.lastSeqVal = None
        self.tableName = 'MeterData'
        self.colName = 'meter_data_id'
        self.deleter = MECODBDeleter()

    def testMECODBReadCanBeInited(self):
        self.assertIsNotNone(self.reader)

    def testSelectRecord(self):
        """
        Insert and retrieve a record to test the ability to select a record.
        """

        print "testSelectRecord:"
        print "self.conn = %s" % self.conn

        sampleDict = {'MeterName': '100001', 'UtilDeviceID': '100001',
                      'MacID': '00:00:00:00:00:00:00:00'}
        self.inserter.insertData(self.conn, self.tableName, sampleDict)
        self.lastSeqVal = self.util.getLastSequenceID(self.conn, self.tableName,
                                                      self.colName)

        print "lastSeqVal = %s" % self.lastSeqVal

        row = self.reader.selectRecord(self.conn, self.tableName, self.colName,
                                       self.lastSeqVal)
        self.assertEqual(row[self.colName], self.lastSeqVal)

    def tearDown(self):
        # Delete the record that was inserted.
        if self.lastSeqVal != None:
            self.deleter.deleteRecord(self.conn, self.tableName, self.colName,
                                      self.lastSeqVal)

        self.connector.closeDB(self.conn)
Esempio n. 30
0
    def __init__(self):
        """
        Constructor.
        """

        print __name__
        self.logger = SEKLogger(__name__)
        self.connector = MSGDBConnector()
        self.dbUtil = MSGDBUtil()
        self.notifier = MSGNotifier()
        self.configer = MSGConfiger()
    def __init__(self):
        """
        Constructor.
        """

        self.logger = SEKLogger(__name__, 'DEBUG', useColor = False)
        self.timeUtil = MSGTimeUtil()
        self.configer = MSGConfiger()
        self.fileUtil = MSGFileUtil()
        self.pythonUtil = MSGPythonUtil()  # for debugging
        self.connector = MSGDBConnector()
        self.conn = self.connector.connectDB()
        self.cursor = self.conn.cursor()
        self.dbUtil = MSGDBUtil()
        self.notifier = SEKNotifier(connector = self.connector,
                                    dbUtil = self.dbUtil,
                                    user = self.configer.configOptionValue(
                                        'Notifications', 'email_username'),
                                    password = self.configer.configOptionValue(
                                        'Notifications', 'email_password'),
                                    fromaddr = self.configer.configOptionValue(
                                        'Notifications', 'email_from_address'),
                                    toaddr = self.configer.configOptionValue(
                                        'Notifications', 'email_recipients'),
                                    testing_toaddr =
                                    self.configer.configOptionValue(
                                        'Notifications',
                                        'testing_email_recipients'),
                                    smtp_server_and_port =
                                    self.configer.configOptionValue(
                                        'Notifications',
                                        'smtp_server_and_port'))

        # Google Drive parameters.
        self.clientID = self.configer.configOptionValue('Export',
                                                        'google_api_client_id')
        self.clientSecret = self.configer.configOptionValue('Export',
                                                            'google_api_client_secret')
        self.oauthScope = 'https://www.googleapis.com/auth/drive'
        self.oauthConsent = 'urn:ietf:wg:oauth:2.0:oob'
        self.googleAPICredentials = ''
        self.exportTempWorkPath = self.configer.configOptionValue('Export',
                                                                  'db_export_work_path')

        self.credentialPath = self.configer.configOptionValue('Export',
                                                              'google_api_credentials_path')
        self.credentialStorage = Storage(
            '{}/google_api_credentials'.format(self.credentialPath))

        self._driveService = None
        self._cloudFiles = None
        self.postAgent = 'Maui Smart Grid 1.0.0 DB Exporter'
        self.retryDelay = 10
        self.availableFilesURL = ''
 def __init__(self):
     """
     Constructor.
     """
     self.logger = MSGLogger(__name__, 'DEBUG')
     self.aggregator = MSGDataAggregator()
     self.notifier = MSGNotifier()
     self.rawTypes = ['weather', 'egauge', 'circuit', 'irradiance']
     self.connector = MSGDBConnector()
     self.conn = self.connector.connectDB()
     self.cursor = self.conn.cursor()
     self.dbUtil = MSGDBUtil()
 def __init__(self):
     """
     Constructor.
     """
     self.logger = SEKLogger(__name__, 'DEBUG')
     self.aggregator = MSGDataAggregator()
     self.notifier = MSGNotifier()
     self.rawTypes = [x.name for x in list(MSGAggregationTypes)]
     self.connector = MSGDBConnector()
     self.conn = self.connector.connectDB()
     self.cursor = self.conn.cursor()
     self.dbUtil = MSGDBUtil()
Esempio n. 34
0
    def countOfDBExports(self, since=None):
        """
        :param since: datetime indicating last export datetime.
        :return: Int of count of exports.
        """
        myDatetime = lambda x: datetime.datetime.strptime(x, '%Y-%m-%d %H:%S')
        if not since:
            since = myDatetime('1900-01-01 00:00')
        self.logger.log(since.strftime('%Y-%m-%d %H:%M'), 'DEBUG')

        sql = 'SELECT COUNT("public"."ExportHistory"."timestamp") FROM ' \
              '"public"."ExportHistory" WHERE "timestamp" > \'{}\''.format(
            since.strftime('%Y-%m-%d %H:%M'))

        conn = MSGDBConnector().connectDB()
        cursor = conn.cursor()
        dbUtil = MSGDBUtil()
        rows = None
        if dbUtil.executeSQL(cursor, sql, exitOnFail=False):
            rows = cursor.fetchall()
        assert len(rows) == 1, 'Invalid return value.'
        return rows[0][0]
    def countOfDBExports(self, since = None):
        """
        :param since: datetime indicating last export datetime.
        :return: Int of count of exports.
        """
        myDatetime = lambda x: datetime.datetime.strptime(x, '%Y-%m-%d %H:%S')
        if not since:
            since = myDatetime('1900-01-01 00:00')
        self.logger.log(since.strftime('%Y-%m-%d %H:%M'), 'DEBUG')

        sql = 'SELECT COUNT("public"."ExportHistory"."timestamp") FROM ' \
              '"public"."ExportHistory" WHERE "timestamp" > \'{}\''.format(
            since.strftime('%Y-%m-%d %H:%M'))

        conn = MSGDBConnector().connectDB()
        cursor = conn.cursor()
        dbUtil = MSGDBUtil()
        rows = None
        if dbUtil.executeSQL(cursor, sql, exitOnFail = False):
            rows = cursor.fetchall()
        assert len(rows) == 1, 'Invalid return value.'
        return rows[0][0]
 def __init__(self):
     """
     Constructor.
     """
     self.logger = MSGLogger(__name__, 'DEBUG')
     self.aggregator = MSGDataAggregator(exitOnError = False,
                                         commitOnEveryInsert = True)
     self.notifier = MSGNotifier()
     # Available types are in ['weather', 'egauge', 'circuit', 'irradiance'].
     self.rawTypes = ['']
     self.connector = MSGDBConnector()
     self.conn = self.connector.connectDB()
     self.cursor = self.conn.cursor()
     self.dbUtil = MSGDBUtil()
    def logSuccessfulExport(self, name = '', url = '', datetime = 0, size = 0):
        """
        When an export has been successful, log information about the export
        to the database.

        The items to log include:
        * filename
        * URL
        * timestamp
        * filesize

        :param name: String
        :param url: String
        :param datetime:
        :param size: Int
        :return: True if no errors occurred, else False.
        """

        def exportHistoryColumns():
            return ['name', 'url', 'timestamp', 'size']

        timestamp = lambda \
                datetime: 'to_timestamp(0)' if datetime == 0 else "timestamp " \
                                                                  "'{}'".format(
            datetime)

        sql = 'INSERT INTO "{0}" ({1}) VALUES ({2}, {3}, {4}, {5})'.format(
            self.configer.configOptionValue('Export', 'export_history_table'),
            ','.join(exportHistoryColumns()), "'" + name + "'", "'" + url + "'",
            timestamp(datetime), size)

        conn = MSGDBConnector().connectDB()
        cursor = conn.cursor()
        dbUtil = MSGDBUtil()
        result = dbUtil.executeSQL(cursor, sql, exitOnFail = False)
        conn.commit()
        return result
Esempio n. 38
0
def insertData(files, table, cols):
    """
	Insert aggregated data generated by this script into a database table.

	:param files: A list of the filenames to be processed.
	:param table: The name of the table in the DB.
	:param cols: A list of the columns (as strings) in the table.
	:param testing: Specify whether to use test 
	"""

    connector = MSGDBConnector()
    conn = connector.connectDB()
    dbUtil = MSGDBUtil()
    cursor = conn.cursor()

    cnt = 0

    for file in files:

        with open(file, 'rb') as csvfile:
            myReader = csv.reader(csvfile, delimiter=',')
            # Skip the header line.
            myReader.next()
            for row in myReader:
                print row
                sql = """INSERT INTO "%s" (%s) VALUES (%s)""" % (
                    table, ','.join(cols), ','.join("'" + item.strip() + "'"
                                                    for item in row))

                sql = sql.replace("'NULL'", 'NULL')
                dbUtil.executeSQL(cursor, sql)
                cnt += 1
                if cnt % 10000 == 0:
                    conn.commit()

        conn.commit()
        cnt = 0
def insertData(files, table, cols, testing = False):
	"""
	Insert aggregated data generated by this script into a database table.

	:param files: A list of the filenames to be processed.
	:param table: The name of the table in the DB.
	:param cols: A list of the columns (as strings) in the table.
	:param testing: Specify whether to use test (false by default).
	"""
	connector = MSGDBConnector()
	conn = connector.connectDB()
	dbUtil = MSGDBUtil()
	cursor = conn.cursor()

	cnt = 0

	for file in files:

		with open(file, 'r') as csvfile:
			reader = csv.reader(csvfile, delimiter = ',')
			# Skip the header line.
			reader.next()
			for row in reader:
				sql = """INSERT INTO "%s" (%s) VALUES (%s)""" % (
					table, ','.join(cols),
					','.join("'" + item.strip() + "'" for item in row))

				sql = sql.replace("'NULL'", 'NULL')

				dbUtil.executeSQL(cursor, sql)

				cnt += 1
				if cnt % 10000 == 0:
					conn.commit()

		conn.commit()
		cnt = 0
Esempio n. 40
0
    def logSuccessfulExport(self, name='', url='', datetime=0, size=0):
        """
        When an export has been successful, log information about the export
        to the database.

        The items to log include:
        * filename
        * URL
        * timestamp
        * filesize

        :param name: String
        :param url: String
        :param datetime:
        :param size: Int
        :return: True if no errors occurred, else False.
        """
        def exportHistoryColumns():
            return ['name', 'url', 'timestamp', 'size']

        timestamp = lambda \
                datetime: 'to_timestamp(0)' if datetime == 0 else "timestamp " \
                                                                  "'{}'".format(
            datetime)

        sql = 'INSERT INTO "{0}" ({1}) VALUES ({2}, {3}, {4}, {5})'.format(
            self.configer.configOptionValue('Export', 'export_history_table'),
            ','.join(exportHistoryColumns()), "'" + name + "'",
            "'" + url + "'", timestamp(datetime), size)

        conn = MSGDBConnector().connectDB()
        cursor = conn.cursor()
        dbUtil = MSGDBUtil()
        result = dbUtil.executeSQL(cursor, sql, exitOnFail=False)
        conn.commit()
        return result
    def __init__(self, exitOnError=True, commitOnEveryInsert=False, testing=False):
        """
        Constructor.

        :param testing: if True, the testing DB will be connected instead of
        the production DB.
        """

        self.logger = SEKLogger(__name__, "info")
        self.configer = MSGConfiger()
        self.conn = MSGDBConnector().connectDB()
        self.cursor = self.conn.cursor()
        self.dbUtil = MSGDBUtil()
        self.notifier = MSGNotifier()
        self.mathUtil = MSGMathUtil()
        self.timeUtil = MSGTimeUtil()
        self.nextMinuteCrossing = {}
        self.nextMinuteCrossingWithoutSubkeys = None
        self.exitOnError = exitOnError
        self.commitOnEveryInsert = commitOnEveryInsert
        section = "Aggregation"
        tableList = [
            "irradiance",
            "agg_irradiance",
            "weather",
            "agg_weather",
            "circuit",
            "agg_circuit",
            "egauge",
            "agg_egauge",
        ]
        self.dataParams = {
            "weather": ("agg_weather", "timestamp", ""),
            "egauge": ("agg_egauge", "datetime", "egauge_id"),
            "circuit": ("agg_circuit", "timestamp", "circuit"),
            "irradiance": ("agg_irradiance", "timestamp", "sensor_id"),
        }
        self.columns = {}

        # tables[datatype] gives the table name for datatype.
        self.tables = {t: self.configer.configOptionValue(section, "{}_table".format(t)) for t in tableList}

        for t in self.tables.keys():
            self.logger.log("t:{}".format(t), "DEBUG")
            try:
                self.columns[t] = self.dbUtil.columnsString(self.cursor, self.tables[t])
            except TypeError as error:
                self.logger.log("Ignoring missing table: Error is {}.".format(error), "error")
class MECONonPVinMLHNotifier(object):
    def __init__(self):
        """
        Constructor.
        """

        self.logger = SEKLogger(__name__)
        self.viewPVReadingsinNonMLH = ''
        self.lastDateProcessed = None
        self.connector = MSGDBConnector()
        self.conn = self.connector.connectDB()

    def sendNewReadingsNotification(self):
        pass

    def checkForNewReadings(self, lastDate = None):
        sql = """SELECT * FROM %s """ % (self.viewPVReadingsinNonMLH)
class MECONonPVinMLHNotifier(object):
    def __init__(self):
        """
        Constructor.
        """

        self.logger = SEKLogger(__name__)
        self.viewPVReadingsinNonMLH = ''
        self.lastDateProcessed = None
        self.connector = MSGDBConnector()
        self.conn = self.connector.connectDB()

    def sendNewReadingsNotification(self):
        pass

    def checkForNewReadings(self, lastDate=None):
        sql = """SELECT * FROM %s """ % (self.viewPVReadingsinNonMLH)
    def __init__(self, exitOnError = True, commitOnEveryInsert = False,
                 testing = False):
        """
        Constructor.

        :param testing: if True, the testing DB will be connected instead of
        the production DB.
        """

        self.logger = MSGLogger(__name__, 'info')
        self.configer = MSGConfiger()
        self.conn = MSGDBConnector().connectDB()
        self.cursor = self.conn.cursor()
        self.dbUtil = MSGDBUtil()
        self.notifier = MSGNotifier()
        self.mathUtil = MSGMathUtil()
        self.timeUtil = MSGTimeUtil()
        self.nextMinuteCrossing = {}
        self.nextMinuteCrossingWithoutSubkeys = None
        self.exitOnError = exitOnError
        self.commitOnEveryInsert = commitOnEveryInsert
        section = 'Aggregation'
        tableList = ['irradiance', 'agg_irradiance', 'weather', 'agg_weather',
                     'circuit', 'agg_circuit', 'egauge', 'agg_egauge']
        self.dataParams = {'weather': ('agg_weather', 'timestamp', ''),
                           'egauge': ('agg_egauge', 'datetime', 'egauge_id'),
                           'circuit': ('agg_circuit', 'timestamp', 'circuit'),
                           'irradiance': (
                               'agg_irradiance', 'timestamp', 'sensor_id')}
        self.columns = {}

        # tables[datatype] gives the table name for datatype.
        self.tables = {
            t: self.configer.configOptionValue(section, '%s_table' % t) for t in
            tableList}

        for t in self.tables.keys():
            self.logger.log('t:%s' % t, 'DEBUG')
            try:
                self.columns[t] = self.dbUtil.columnsString(self.cursor,
                                                            self.tables[t])
            except TypeError as error:
                self.logger.log('Ignoring missing table: Error is %s.' % error,
                                'error')
    def setUp(self):
        self.i = MECODBInserter()

        # Connect to the testing database.
        self.connector = MSGDBConnector(testing = True)

        self.conn = self.connector.connectDB()
        self.lastSeqVal = None

        # Does this work having the dictCur be in another class?
        self.dictCur = self.connector.dictCur

        self.cursor = self.conn.cursor()
        self.deleter = MECODBDeleter()
        self.tableName = 'MeterData'
        self.columnName = 'meter_data_id'
        self.configer = MSGConfiger()
        self.logger = MSGLogger(__name__, 'debug')
        self.dbUtil = MSGDBUtil()
class NewDataAggregator(object):
    """
    Perform aggregation of new data for a set of predefined data types (self
    .rawTypes).
    """

    def __init__(self):
        """
        Constructor.
        """
        self.logger = SEKLogger(__name__, 'DEBUG')
        self.aggregator = MSGDataAggregator()
        self.notifier = MSGNotifier()
        self.rawTypes = [x.name for x in list(MSGAggregationTypes)]
        self.connector = MSGDBConnector()
        self.conn = self.connector.connectDB()
        self.cursor = self.conn.cursor()
        self.dbUtil = MSGDBUtil()


    def sendNewDataNotification(self, result = None, testing = False):
        """
        Sending notification reporting on new data being available since the
        last time new data was reported.

        :param result: list of dicts containing aggregation results as
        provided by MSGDataAggregator::aggregateNewData.
        :param testing: Use testing mode when True.
        """

        self.logger.log('result {}'.format(result), 'debug')

        lastReportDate = self.notifier.lastReportDate(
            MSGNotificationHistoryTypes.MSG_DATA_AGGREGATOR)

        if not lastReportDate:
            lastReportDate = "never"

        if not result:
            msgBody = '\nNew data has NOT been aggregated in {}. No result ' \
                      'was obtained. This is an error that should be ' \
                      'investigated.'.format(self.connector.dbName)
        else:
            msgBody = '\nNew data has been aggregated in {}.'.format(
                self.connector.dbName)
            msgBody += '\n\n'
            for i in range(len(result)):
                msgBody += 'The new data count for type {} is {} readings' \
                           '.\n'.format(result[i].keys()[0],
                                        result[i].values()[0])
            msgBody += '\n\n'
            msgBody += 'The last report date was %s.' % lastReportDate
            msgBody += '\n\n'
        self.notifier.sendNotificationEmail(msgBody, testing = testing)
        self.notifier.recordNotificationEvent(
            MSGNotificationHistoryTypes.MSG_DATA_AGGREGATOR)


    def aggregateNewData(self):
        """
        :return: list of dicts obtained from
        MSGDataAggregator::aggregateNewData.
        """

        result = map(self.aggregator.aggregateNewData, self.rawTypes)

        self.logger.log('result {}'.format(result))
        return result
Esempio n. 47
0
class MSGNotifier(object):
    """
    Provides notification service functionality for MSG data processing.

    Email settings are stored in the local configuration.

    Usage:

    from msg_notifier import MSGNotifier
    self.notifier = MSGNotifier()

    Public API:

    sendNotificationEmail(msgBody, testing = False):
        Send msgBody as a notification to the mailing list defined in the
        config file.

    sendMailWithAttachments(msgBody, files = None, testing = False)
        Send msgBody with files attached as a notification to the mailing
        list defined in the config file.

    lastReportDate(noticeType):
        The last date where a notification of the given type was reported.

    recordNotificationEvent(noticeType):
        Record an event in the notification history.
    """


    def __init__(self):
        """
        Constructor.
        """

        warnings.simplefilter('default')
        warnings.warn("This module is deprecated in favor of SEKNotifier.",
                      DeprecationWarning)

        self.config = MSGConfiger()
        self.logger = SEKLogger(__name__, 'info')
        self.connector = MSGDBConnector()
        self.conn = self.connector.connectDB()
        self.cursor = self.conn.cursor()
        self.dbUtil = MSGDBUtil()
        self.noticeTable = 'NotificationHistory'
        self.notificationHeader = "This is a message from the Hawaii Smart " \
                                  "Energy Project MSG Project notification " \
                                  "system.\n\n"

        self.noReplyNotice = '\n\nThis email account is not monitored. No ' \
                             'replies will originate from this ' \
                             'account.\n\nYou are receiving this message ' \
                             'because you are on the recipient list for ' \
                             'notifications for the Hawaii Smart Energy ' \
                             'Project.'


    def sendNotificationEmail(self, msgBody, testing = False):
        """
        This method is an alternative to the multipart method in
        sendMailWithAttachments.

        :param msgBody: The body of the message to be sent.
        :param testing: True if running in testing mode.
        :returns: True for success, False for an error.
        """

        errorOccurred = False
        user = self.config.configOptionValue('Notifications', 'email_username')
        password = self.config.configOptionValue('Notifications',
                                                 'email_password')
        fromaddr = self.config.configOptionValue('Notifications',
                                                 'email_from_address')

        if testing:
            toaddr = self.config.configOptionValue('Notifications',
                                                   'testing_email_recipients')
        else:
            toaddr = self.config.configOptionValue('Notifications',
                                                   'email_recipients')
        server = smtplib.SMTP(self.config.configOptionValue('Notifications',
                                                            'smtp_server_and_port'))

        try:
            server.starttls()
        except smtplib.SMTPException as detail:
            errorOccurred = True
            self.logger.log("Exception during SMTP STARTTLS: {}".format(detail),
                            'ERROR')

        try:
            server.login(user, password)
        except smtplib.SMTPException as detail:
            errorOccurred = True
            self.logger.log("Exception during SMTP login: %s" % detail, 'ERROR')

        senddate = datetime.now().strftime('%Y-%m-%d')
        subject = "HISEP Notification"

        msgHeader = "Date: {}\r\nFrom: {}\r\nTo: {}\r\nSubject: {" \
                    "}\r\nX-Mailer: My-Mail\r\n\r\n".format(senddate, fromaddr,
                                                            toaddr, subject)

        msgBody = self.notificationHeader + msgBody

        msgBody += self.noReplyNotice

        try:
            self.logger.log("Send email notification.", 'INFO')
            server.sendmail(fromaddr, toaddr, msgHeader + msgBody)
            server.quit()
        except smtplib.SMTPException as detail:
            errorOccurred = True
            self.logger.log("Exception during SMTP sendmail: {}".format(detail),
                            'ERROR')

        return errorOccurred != True


    def sendMailWithAttachments(self, msgBody, files = None, testing = False):
        """
        Send email along with attachments.

        :param msgBody: String containing the body of the messsage to send.
        :param files: List of file paths. This is a mutable argument that
        should be handled carefully as the default is defined only once.
        :param testing: True if running in testing mode.
        :returns: True if no exceptions are raised.
        """

        if files is None:
            files = []

        sys.stderr.write("Sending multipart email.\n")
        if testing:
            self.logger.log("Notification testing mode is ON.\n", 'info')

        errorOccurred = False
        assert type(files) == list

        user = self.config.configOptionValue('Notifications', 'email_username')
        password = self.config.configOptionValue('Notifications',
                                                 'email_password')

        if testing:
            send_to = self.config.configOptionValue('Notifications',
                                                    'testing_email_recipients')
        else:
            send_to = self.config.configOptionValue('Notifications',
                                                    'email_recipients')

        send_from = self.config.configOptionValue('Notifications',
                                                  'email_from_address')

        msg = MIMEMultipart()
        msg['From'] = send_from
        msg['To'] = send_to
        msg['Date'] = formatdate(localtime = True)
        msg['Subject'] = "HISEP Notification"

        msg.attach(MIMEText(msgBody))

        for f in files:
            sys.stderr.write("Attaching file %s.\n" % f)
            part = MIMEBase('application', "octet-stream")
            part.set_payload(open(f, "rb").read())
            Encoders.encode_base64(part)
            part.add_header('Content-Disposition',
                            'attachment; filename="%s"' % os.path.basename(f))
            msg.attach(part)

        server = smtplib.SMTP(self.config.configOptionValue('Notifications',
                                                            'smtp_server_and_port'))
        try:
            server.starttls()
        except smtplib.SMTPException as detail:
            errorOccurred = True
            self.logger.log("Exception during SMTP STARTTLS: %s" % detail,
                            'ERROR')

        try:
            server.login(user, password)
        except smtplib.SMTPException as detail:
            errorOccurred = True
            self.logger.log("Exception during SMTP login: %s" % detail, 'ERROR')

        self.logger.log("Send email notification.", 'INFO')

        try:
            server.sendmail(send_from, send_to, msg.as_string())
        except smtplib.SMTPException as detail:
            errorOccurred = True
            self.logger.log("Exception during SMTP sendmail: %s" % detail,
                            'ERROR')

        server.quit()

        if errorOccurred == False:
            self.logger.log('No exceptions occurred.\n', 'info')

        return errorOccurred


    def recordNotificationEvent(self, noticeType = None):
        """
        Save a notification event to the notification history.
        :param table: String
        :param noticeType: <enum 'MSGNotificationHistoryTypes'>
        :returns: Boolean
        """

        if not noticeType:
            return False
        if not noticeType in MSGNotificationHistoryTypes:
            return False

        cursor = self.cursor
        sql = """INSERT INTO "{}" ("notificationType", "notificationTime")
        VALUES ('{}', NOW())""".format(self.noticeTable, noticeType.name)
        success = self.dbUtil.executeSQL(cursor, sql)
        self.conn.commit()
        if not success:
            raise Exception('Exception while saving the notification time.')
        return success


    def lastReportDate(self, noticeType = None):
        """
        Get the last time a notification was reported for the given
        noticeType.

        :param noticeType: String indicating the type of the
        notification. It is stored in the event history.
        :returns: datetime of last report date.
        """

        if not noticeType or (not noticeType in MSGNotificationHistoryTypes):
            raise Exception('Invalid notice type.')

        cursor = self.cursor

        sql = 'SELECT MAX("notificationTime") FROM "{}" WHERE ' \
              '"notificationType" = \'{}\''.format(self.noticeTable,
                                                   noticeType.name)

        success = self.dbUtil.executeSQL(cursor, sql)
        if success:
            rows = cursor.fetchall()

            if not rows[0][0]:
                return None
            else:
                return rows[0][0]
        else:
            raise Exception('Exception during getting last report date.')
                                  'specified.')
    argParser.add_argument('--testing', action = 'store_true', default = False,
                           help = 'If this flag is on, '
                                  'insert data to the testing database as '
                                  'specified in the local configuration file.')
    commandLineArgs = argParser.parse_args()

if __name__ == '__main__':

    processCommandLineArguments()

    filename = COMMAND_LINE_ARGS.filename

    success = True
    anyFailure = False
    connector = MSGDBConnector(testing = COMMAND_LINE_ARGS.testing)
    conn = connector.connectDB()
    cur = conn.cursor()
    dbUtil = MSGDBUtil()
    notifier = MSGNotifier()
    msgBody = ''
    configer = MSGConfiger()

    if COMMAND_LINE_ARGS.testing:
        dbName = configer.configOptionValue("Database", "testing_db_name")
    else:
        dbName = configer.configOptionValue("Database", "db_name")

    msg = ("Loading Meter Location History data in file %s to database %s.\n" % (
        filename, dbName))
    sys.stderr.write(msg)
class MSGDataVerifier(object):
    """
    Perform verification procedures related to data integrity.
    """

    def __init__(self):
        """
        Constructor.
        """

        self.logger = SEKLogger(__name__, 'DEBUG')
        self.cursor = MSGDBConnector().connectDB().cursor()
        self.dbUtil = MSGDBUtil()

    def mecoReadingsDupeCount(self):
        """
        Generate counts of MECO dupe readings.
        """

        dupes = 0
        startDate = lambda y, m: '%d-%02d-%02d' % (y, m, 1)
        endDate = lambda y, m: '%d-%02d-%02d' % (
            y, m, calendar.monthrange(y, m)[1])

        for y in YEARS:
            startDates = [startDate(y, m) for m in
                          map(lambda x: x + 1, range(12))]
            endDates = [endDate(y, m) for m in map(lambda x: x + 1, range(12))]

            for start in startDates:
                cnt = self.__mecoReadingsDupeCount(start, endDates[
                    startDates.index(start)])
                self.logger.log('start: %s, dupe cnt: %s' % (start, cnt),
                                'INFO')
                dupes += cnt

        return dupes


    def __mecoReadingsDupeCount(self, startDate, endDate):
        """

        :param startDate:
        :param endDate:
        :returns: DB row count.
        """

        self.dbUtil.executeSQL(self.cursor, """SELECT "Interval".end_time,
                            "MeterData".meter_name,
                            "Reading".channel
                     FROM "MeterData"
                     INNER JOIN "IntervalReadData" ON "MeterData"
                     .meter_data_id = "IntervalReadData".meter_data_id
                     INNER JOIN "Interval" ON "IntervalReadData"
                     .interval_read_data_id = "Interval".interval_read_data_id
                     INNER JOIN "Reading" ON "Interval".interval_id = "Reading"
                     .interval_id
                     WHERE "Interval".end_time BETWEEN '%s' and '%s'
                     GROUP BY "MeterData".meter_name,
                     "Interval".end_time,
                     "Reading".channel
                     HAVING (COUNT(*) > 1)""" % (startDate, endDate))
        return len(self.cursor.fetchall())


    def egaugeAggregationCount(self):
        """
        There should not be more than 96 15-min interval endpoints within a
        single calendar day for a given sub ID.
        :return:
        """
        pass
 def setUp(self):
     self.connector = MSGDBConnector(True)
     self.conn = self.connector.connectDB()
     self.configer = MSGConfiger()
Esempio n. 51
0
              '-LICENSE.txt'

import csv
import sys
from msg_db_connector import MSGDBConnector
from msg_db_util import MSGDBUtil
from msg_notifier import MSGNotifier
from msg_configer import MSGConfiger

if __name__ == '__main__':

    filename = sys.argv[1]

    success = True
    anyFailure = False
    connector = MSGDBConnector()
    conn = connector.connectDB()
    cur = conn.cursor()
    dbUtil = MSGDBUtil()
    notifier = MSGNotifier()
    msgBody = ''
    configer = MSGConfiger()

    dbName = configer.configOptionValue("Database", "db_name")

    msg = ("Loading static location record data in file %s to database %s.\n" %
           (filename, dbName))
    sys.stderr.write(msg)
    msgBody += msg

    f = open(filename, "r")
Esempio n. 52
0
 def setUp(self):
     self.weatherUtil = MSGWeatherDataUtil()
     self.logger = SEKLogger(__name__, 'DEBUG')
     self.dbConnector = MSGDBConnector()
     self.cursor = self.dbConnector.conn.cursor()
     self.configer = MSGConfiger()
class MSGDBExporter(object):
    """
    Export MSG DBs as SQL scripts.

    Supports export to local storage and to cloud storage.

    Usage:

    from msg_db_exporter import MSGDBExporter
    exporter = MSGDBExporter()

    Public API:

    exportDB(databases:List, 
             toCloud:Boolean, 
             testing:Boolean,
             numChunks:Integer, 
             deleteOutdated:Boolean): Export a list of DBs to the cloud.
    """

    # List of cloud files.
    @property
    def cloudFiles(self):
        self._cloudFiles = self.driveService.files().list().execute()
        return self._cloudFiles

    @property
    def driveService(self):
        if self._driveService:
            return self._driveService

        if not self.credentialPath:
            raise Exception("Credential path is required.")
        storage = Storage(
            '{}/google_api_credentials'.format(self.credentialPath))

        self.googleAPICredentials = storage.get()

        self.logger.log("Authorizing credentials.", 'info')
        http = httplib2.Http()
        http = self.googleAPICredentials.authorize(http)

        self.logger.log("Authorized.", 'info')

        self._driveService = build('drive', 'v2', http = http)

        return self._driveService


    def __init__(self):
        """
        Constructor.
        """

        self.logger = SEKLogger(__name__, 'DEBUG', useColor = False)
        self.timeUtil = MSGTimeUtil()
        self.configer = MSGConfiger()
        self.fileUtil = MSGFileUtil()
        self.pythonUtil = MSGPythonUtil()  # for debugging
        self.connector = MSGDBConnector()
        self.conn = self.connector.connectDB()
        self.cursor = self.conn.cursor()
        self.dbUtil = MSGDBUtil()
        self.notifier = SEKNotifier(connector = self.connector,
                                    dbUtil = self.dbUtil,
                                    user = self.configer.configOptionValue(
                                        'Notifications', 'email_username'),
                                    password = self.configer.configOptionValue(
                                        'Notifications', 'email_password'),
                                    fromaddr = self.configer.configOptionValue(
                                        'Notifications', 'email_from_address'),
                                    toaddr = self.configer.configOptionValue(
                                        'Notifications', 'email_recipients'),
                                    testing_toaddr =
                                    self.configer.configOptionValue(
                                        'Notifications',
                                        'testing_email_recipients'),
                                    smtp_server_and_port =
                                    self.configer.configOptionValue(
                                        'Notifications',
                                        'smtp_server_and_port'))

        # Google Drive parameters.
        self.clientID = self.configer.configOptionValue('Export',
                                                        'google_api_client_id')
        self.clientSecret = self.configer.configOptionValue('Export',
                                                            'google_api_client_secret')
        self.oauthScope = 'https://www.googleapis.com/auth/drive'
        self.oauthConsent = 'urn:ietf:wg:oauth:2.0:oob'
        self.googleAPICredentials = ''
        self.exportTempWorkPath = self.configer.configOptionValue('Export',
                                                                  'db_export_work_path')

        self.credentialPath = self.configer.configOptionValue('Export',
                                                              'google_api_credentials_path')
        self.credentialStorage = Storage(
            '{}/google_api_credentials'.format(self.credentialPath))

        self._driveService = None
        self._cloudFiles = None
        self.postAgent = 'Maui Smart Grid 1.0.0 DB Exporter'
        self.retryDelay = 10
        self.availableFilesURL = ''


    def verifyExportChecksum(self, testing = False):
        """
        Verify the compressed export file using a checksum.

        * Save the checksum of the original uncompressed export data.
        * Extract the compressed file.
        * Verify the uncompressed export data.

        :param testing: When set to True, Testing Mode is used.
        """

        # Get the checksum of the original file.
        md5sum = self.fileUtil.md5Checksum(self.exportTempWorkPath)
        self.logger.log('md5sum: {}'.format(md5sum))


    def db_username(self):
        return "postgres"
        # return self.configer.configOptionValue('Database', 'db_username')

    def db_password(self):
        return self.configer.configOptionValue('Database', 'db_password')

    def db_port(self):
        return self.configer.configOptionValue('Database', 'db_port')


    def dumpCommand(self, db = '', dumpName = ''):
        """
        This method makes use of

        pg_dump -s -p ${PORT}
                   -U ${USERNAME}
                   [-T ${OPTIONAL_TABLE_EXCLUSIONS}]
                   ${DB_NAME} >
                   ${EXPORT_TEMP_WORK_PATH}/${DUMP_TIMESTAMP}_{DB_NAME}.sql

        :param db: String
        :param dumpName: String
        :return: String of command used to export DB.
        """

        # For reference only:
        # Password is passed from ~/.pgpass.
        # Note that ':' and '\' characters should be escaped with '\'.
        # Ref: http://www.postgresql.org/docs/9.1/static/libpq-pgpass.html

        # Dump databases as the superuser. This method does not require a
        # stored password when running under a root crontab.
        if not db or not dumpName:
            raise Exception('DB and dumpname required.')

        # Process exclusions.

        exclusions = self.dumpExclusionsDictionary()
        excludeList = []
        if db in exclusions:
            excludeList = exclusions[db]
        excludeString = ''
        if len(excludeList) > 0 and exclusions != None:
            for e in excludeList:
                excludeString += """-T '"{}"' """.format(e)

        return 'sudo -u postgres pg_dump -p {0} -U {1} {5} {2} > {3}/{4}' \
               '.sql'.format(self.db_port(), self.db_username(), db,
                             self.exportTempWorkPath, dumpName, excludeString)


    def dumpExclusionsDictionary(self):
        """
        :param db: String of DB name for which to retrieve exclusions.
        :return: Dictionary with keys as DBs and values as lists of tables to
        be excluded for a given database.
        """
        try:
            if type(eval(self.configer.configOptionValue('Export',
                                                         'db_export_exclusions'))) == type(
                    {}):
                return eval(self.configer.configOptionValue('Export',
                                                            'db_export_exclusions'))
            else:
                return None
        except SyntaxError as detail:
            self.logger.log(
                'SyntaxError exception while getting exclusions: {}'.format(
                    detail))


    def dumpName(self, db = ''):
        """
        :param db: String
        :return: String of file name used for dump file of db.
        """
        if not db:
            raise Exception('DB required.')
        return "{}_{}".format(self.timeUtil.conciseNow(), db)


    def filesToUpload(self, compressedFullPath = '', numChunks = 0,
                      chunkSize = 0):
        """
        :param compressedFullPath: String
        :param numChunks: Int
        :param chunkSize: Int
        :return: List of files to be uploaded according to their split
        sections, if applicable.
        """
        if numChunks != 0:
            self.logger.log('Splitting {}'.format(compressedFullPath), 'DEBUG')

            filesToUpload = self.fileUtil.splitLargeFile(
                fullPath = compressedFullPath, chunkSize = chunkSize,
                numChunks = numChunks)

            if not filesToUpload:
                raise Exception('Exception during file splitting.')
            else:
                self.logger.log('to upload: {}'.format(filesToUpload), 'debug')
                return filesToUpload

        else:
            return [compressedFullPath]


    def dumpResult(self, db = '', dumpName = '', fullPath = ''):
        """
        :param dumpName: String of filename of dump file.
        :param fullPath: String of full path to dump file.
        :return: Boolean True if dump operation was successful, otherwise False.
        """

        success = True

        self.logger.log('fullPath: {}'.format(fullPath), 'DEBUG')

        try:
            # Generate the SQL script export.
            # @todo check return value of dump command
            self.logger.log('cmd: {}'.format(
                self.dumpCommand(db = db, dumpName = dumpName)))
            subprocess.check_call(
                self.dumpCommand(db = db, dumpName = dumpName), shell = True)
        except subprocess.CalledProcessError as error:
            self.logger.log("Exception while dumping: {}".format(error))
            sys.exit(-1)

        return success


    def exportDBs(self, databases = None, toCloud = False, localExport = True,
                  testing = False, chunkSize = 0, deleteOutdated = False):
        """
        Export a set of DBs to local storage.

        :param databases: List of database names that will be exported.
        :param toCloud: Boolean if set to True, then the export will also be
        copied to cloud storage.
        :param localExport: Boolean when set to True the DB is exported
        locally.
        :param testing: Boolean flag for testing mode. (@DEPRECATED)
        :param chunkSize: Integer size in bytes of chunk size used for
        splitting.
        :param deleteOutdated: Boolean indicating outdated files in the cloud
        should be removed.
        :returns: List of file IDs of uploaded files or None if there is an
        error condition.
        """

        # @todo separate uploading and exporting functions

        noErrors = True
        uploaded = []

        for db in databases:
            self.logger.log('Exporting {} using pg_dump.'.format(db), 'info')

            dumpName = self.dumpName(db = db)
            fullPath = '{}/{}.sql'.format(self.exportTempWorkPath, dumpName)
            if localExport:
                noErrors = self.dumpResult(db, dumpName, fullPath)

            # Perform compression of the file.
            self.logger.log("Compressing {} using gzip.".format(db), 'info')
            self.logger.log('fullpath: {}'.format(fullPath), 'DEBUG')

            gzipResult = self.fileUtil.gzipCompressFile(fullPath)
            compressedFullPath = '{}{}'.format(fullPath, '.gz')
            numChunks = self.numberOfChunksToUse(compressedFullPath)

            # Gzip uncompress and verify by checksum is disabled until a more
            # efficient, non-memory-based, uncompress is implemented.
            # md5sum1 = self.fileUtil.md5Checksum(fullPath)
            # self.md5Verification(compressedFullPath=compressedFullPath,
            # fullPath=fullPath,md5sum1=md5sum1)

            if toCloud:
                # Split compressed files into a set of chunks to improve the
                # reliability of uploads.

                # Upload the files to the cloud.
                for f in self.filesToUpload(
                        compressedFullPath = compressedFullPath,
                        numChunks = numChunks, chunkSize = chunkSize):
                    self.logger.log('Uploading {}.'.format(f), 'info')
                    fileID = self.uploadFileToCloudStorage(fullPath = f,
                                                           testing = testing,
                                                           retryCount = int(
                                                               self.configer.configOptionValue(
                                                                   'Export',
                                                                   'export_retry_count')))

                    self.logger.log('file id after upload: {}'.format(fileID))

                    if fileID != None:
                        uploaded.append(fileID)
                        self.logger.log('uploaded: {}'.format(uploaded),
                                        'DEBUG')
                        if not self.addReaders(fileID,
                                               self.configer.configOptionValue(
                                                       'Export',
                                                       'reader_permission_email_addresses').split(
                                                       ','), retryCount = int(
                                        self.configer.configOptionValue(
                                                'Export',
                                                'export_retry_count'))):
                            self.logger.log(
                                'Failed to add readers for {}.'.format(f),
                                'error')
                        self.logSuccessfulExport(*self.metadataOfFileID(fileID))

                    # Remove split sections if they exist.
                    try:
                        if not testing and numChunks > 1:
                            self.logger.log('Removing {}'.format(f))
                            os.remove('{}'.format(f))
                    except OSError as error:
                        self.logger.log(
                            'Exception while removing {}: {}.'.format(fullPath,
                                                                      error))
                        noErrors = False

            # End if toCloud.

            if gzipResult:
                self.moveToFinalPath(compressedFullPath = compressedFullPath)

            # Remove the uncompressed file.
            try:
                if not testing:
                    self.logger.log('Removing {}'.format(fullPath))
                    os.remove('{}'.format(fullPath))
            except OSError as error:
                self.logger.log(
                    'Exception while removing {}: {}.'.format(fullPath, error))
                noErrors = False

        # End for db in databases.

        if deleteOutdated:
            self.deleteOutdatedFiles(datetime.timedelta(days = int(
                self.configer.configOptionValue('Export',
                                                'export_days_to_keep'))))

        return uploaded if noErrors else None


    def moveToFinalPath(self, compressedFullPath = ''):
        """
        Move a compressed final to the final export path.
        :param compressedFullPath: String for the compressed file.
        :return:
        """
        self.logger.log('Moving {} to final path.'.format(compressedFullPath),
                        'debug')
        try:
            shutil.move(compressedFullPath,
                        self.configer.configOptionValue('Export',
                                                        'db_export_final_path'))
        except Exception as detail:
            self.logger.log(
                'Exception while moving {} to final export path: {}'.format(
                    compressedFullPath, detail), 'error')


    def md5Verification(self, compressedFullPath = '', fullPath = '',
                        md5sum1 = ''):
        """
        Perform md5 verification of a compressed file at compressedFullPath
        where the original file is at fullPath and has md5sum1.

        :param compressedFullPath: String
        :param fullPath: String
        :param md5sum1: String of md5sum of source file.
        :return:
        """

        GZIP_UNCOMPRESS_FILE = False
        if GZIP_UNCOMPRESS_FILE:
            # Verify the compressed file by uncompressing it and
            # verifying its
            # checksum against the original checksum.
            self.logger.log('reading: {}'.format(compressedFullPath), 'DEBUG')
            self.logger.log('writing: {}'.format(os.path.join(
                self.configer.configOptionValue('Testing',
                                                'export_test_data_path'),
                os.path.splitext(os.path.basename(fullPath))[0])), 'DEBUG')

            self.fileUtil.gzipUncompressFile(compressedFullPath, os.path.join(
                self.configer.configOptionValue('Testing',
                                                'export_test_data_path'),
                fullPath))

        VERIFY_BY_CHECKSUM = False
        if VERIFY_BY_CHECKSUM:
            md5sum2 = self.fileUtil.md5Checksum(fullPath)

            self.logger.log("mtime: {}, md5sum2: {}".format(
                time.ctime(os.path.getmtime(fullPath)), md5sum2), 'INFO')

            if md5sum1 == md5sum2:
                self.logger.log(
                    'Compressed file has been validated by checksum.', 'INFO')
            else:
                noErrors = False

    def numberOfChunksToUse(self, fullPath):
        """
        Return the number of chunks to be used by the file splitter based on
        the file size of the file at fullPath.
        :param fullPath: String
        :returns: Int Number of chunks to create.
        """

        fsize = os.path.getsize(fullPath)
        self.logger.log('fullpath: {}, fsize: {}'.format(fullPath, fsize))
        if (fsize >= int(self.configer.configOptionValue('Export',
                                                         'max_bytes_before_split'))):
            # Note that this does not make use of the remainder in the division.
            chunks = int(fsize / int(self.configer.configOptionValue('Export',
                                                                     'max_bytes_before_split')))
            self.logger.log('Will split with {} chunks.'.format(chunks))
            return chunks
        self.logger.log('Will NOT split file.', 'debug')
        return 1


    def uploadFileToCloudStorage(self, fullPath = '', retryCount = 0,
                                 testing = False):
        """
        Export a file to cloud storage.

        :param fullPath: String of file to be exported.
        :param testing: Boolean when set to True, Testing Mode is used.
        :param retryCount: Int of number of times to retry the upload if
        there is a failure.
        :returns: String File ID on verified on upload; None if verification
        fails.
        """

        success = True
        myFile = os.path.basename(fullPath)

        self.logger.log(
            'full path {}'.format(os.path.dirname(fullPath), 'DEBUG'))
        self.logger.log("Uploading {}.".format(myFile))

        result = {}
        try:
            media_body = MediaFileUpload(fullPath,
                                         mimetype =
                                         'application/gzip-compressed',
                                         resumable = True)
            body = {'title': myFile,
                    'description': 'Hawaii Smart Energy Project gzip '
                                   'compressed DB export.',
                    'mimeType': 'application/gzip-compressed'}

            # Result is a Files resource.
            result = self.driveService.files().insert(body = body,
                                                      media_body =
                                                      media_body).execute()

        except Exception as detail:
            # Upload failures can result in a BadStatusLine.
            self.logger.log(
                "Exception while uploading {}: {}.".format(myFile, detail),
                'error')
            success = False

        if not self.__verifyMD5Sum(fullPath, self.fileIDForFileName(myFile)):
            self.logger.log('Failed MD5 checksum verification.', 'INFO')
            success = False

        if success:
            self.logger.log('Verification by MD5 checksum succeeded.', 'INFO')
            self.logger.log("Finished.")
            return result['id']

        if not success and retryCount <= 0:
            return None
        else:
            time.sleep(self.retryDelay)
            self.logger.log('Retrying upload of {}.'.format(fullPath),
                            'warning')
            self.uploadFileToCloudStorage(fullPath = fullPath,
                                          retryCount = retryCount - 1)


    def __retrieveCredentials(self):
        """
        Perform authorization at the server.

        Credentials are loaded into the object attribute googleAPICredentials.
        """

        flow = OAuth2WebServerFlow(self.clientID, self.clientSecret,
                                   self.oauthScope, self.oauthConsent)
        authorize_url = flow.step1_get_authorize_url()
        print 'Go to the following link in your browser: ' + authorize_url
        code = raw_input('Enter verification code: ').strip()
        self.googleAPICredentials = flow.step2_exchange(code)

        print "refresh_token = {}".format(
            self.googleAPICredentials.refresh_token)
        print "expiry = {}".format(self.googleAPICredentials.token_expiry)


    def freeSpace(self):
        """
        Get free space from the drive service.
        :param driveService: Object for the drive service.
        :returns: Int of free space (bytes B) on the drive service.
        """
        aboutData = self.driveService.about().get().execute()
        return int(aboutData['quotaBytesTotal']) - int(
            aboutData['quotaBytesUsed']) - int(
            aboutData['quotaBytesUsedInTrash'])


    def deleteFile(self, fileID = ''):
        """
        Delete the file with ID fileID.
        :param fileID: String of a Google API file ID.
        """

        if not len(fileID) > 0:
            raise Exception("File ID has not been given.")

        self.logger.log(
            'Deleting file with file ID {} and name {}.'.format(fileID,
                                                                self.filenameForFileID(
                                                                    fileID)),
            'debug')

        try:
            # Writing the fileId arg name is required here.
            self.driveService.files().delete(fileId = fileID).execute()

        except errors.HttpError as error:
            self.logger.log('Exception while deleting: {}'.format(error),
                            'error')


    def deleteOutdatedFiles(self, maxAge = datetime.timedelta(weeks = 9999999)):
        """
        Remove outdated files from cloud storage.

        :param minAge: datetime.timedelta of the minimum age before a file is
        considered outdated.
        :param maxAge: datetime.timedelta of the maximum age to consider for
        a file.
        :returns: Int count of deleted items.
        """

        # @todo Return count of actual successfully deleted files.

        outdated = self.outdatedFiles(maxAge)

        """:type : dict"""
        for f in outdated:
            self.deleteFile(f['id'])

        return len(outdated)


    def outdatedFiles(self,
                      daysBeforeOutdated = datetime.timedelta(days = 9999999)):
        """
        Outdated files in the cloud where they are outdated if their age is
        greater than or equal to daysBeforeOutdated.

        Note: When t1 is the same day as t2, the timedelta comes back as -1.
        Not sure why this isn't represented as zero. Perhaps to avoid a false
        evaluation of a predicate on a tdelta.

        :param daysBeforeOutdated: datetime.timedelta where the value
        indicates that outdated files that have an age greater than this
        parameter.
        :return: Int count of deleted items.
        """

        t1 = lambda x: datetime.datetime.strptime(x['createdDate'],
                                                  "%Y-%m-%dT%H:%M:%S.%fZ")
        t2 = datetime.datetime.now()

        return filter(lambda x: t2 - t1(x) >= daysBeforeOutdated,
                      self.cloudFiles['items'])


    def sendNotificationOfFiles(self):
        """
        Provide a notification that lists the export files along with sharing
        links.
        """

        pass


    def sendDownloadableFiles(self):
        """
        Send available files via HTTP POST.
        :returns: None
        """

        myPath = '{}/{}'.format(self.exportTempWorkPath,
                                'list-of-downloadable-files.txt')

        fp = open(myPath, 'wb')

        output = StringIO()
        output.write(self.markdownListOfDownloadableFiles())

        fp.write(self.markdownListOfDownloadableFiles())
        fp.close()

        headers = {'User-Agent': self.postAgent, 'Content-Type': 'text/html'}
        try:
            r = requests.post(self.configer.configOptionValue('Export',
                                                              'export_list_post_url'),
                              output.getvalue(), headers = headers)
            print 'text: {}'.format(r.text)
        except requests.adapters.SSLError as error:
            # @todo Implement alternative verification.
            self.logger.log('SSL error: {}'.format(error), 'error')

        output.close()


    def metadataOfFileID(self, fileID = ''):
        """
        :param fileID: String of a file ID in the cloud.
        :return: Tuple of metadata (name, url, timestamp, size) for a given
        file ID.
        """
        item = [i for i in self.cloudFiles['items'] if i['id'] == fileID][0]
        return (item[u'originalFilename'], item[u'webContentLink'],
                item[u'createdDate'], item[u'fileSize'])


    def listOfDownloadableFiles(self):
        """
        Create a list of downloadable files.
        :returns: List of dicts of files that are downloadable from the cloud.
        """

        files = []
        for i in reversed(sorted(self.cloudFiles['items'],
                                 key = lambda k: k['createdDate'])):
            item = dict()
            item['title'] = i['title']
            item['webContentLink'] = i['webContentLink']
            item['id'] = i['id']
            item['createdDate'] = i['createdDate']
            item['fileSize'] = i['fileSize']
            files.append(item)
        return files


    def markdownListOfDownloadableFiles(self):
        """
        Generate content containing a list of downloadable files in Markdown
        format.

        :returns: String content in Markdown format.
        """

        content = "||*Name*||*Created*||*Size*||\n"
        for i in self.listOfDownloadableFiles():
            content += "||[`{}`]({})".format(i['title'], i['webContentLink'])
            content += "||`{}`".format(i['createdDate'])
            content += "||`{} B`||".format(int(i['fileSize']))
            content += '\n'

        # self.logger.log('content: {}'.format(content))
        return content


    def plaintextListOfDownloadableFiles(self):
        """
        Generate content containing a list of downloadable files in plaintext
        format.

        :returns: String content as plaintext.
        """
        content = ''
        includeLink = False
        for i in reversed(sorted(self.cloudFiles['items'],
                                 key = lambda k: k['createdDate'])):
            if includeLink:
                content += "{}, {}, {}, {} B\n".format(i['title'],
                                                       i['webContentLink'],
                                                       i['createdDate'],
                                                       int(i['fileSize']))
            else:
                content += "{}, {}, {} B\n".format(i['title'], i['createdDate'],
                                                   int(i['fileSize']))

        return content


    def logSuccessfulExport(self, name = '', url = '', datetime = 0, size = 0):
        """
        When an export has been successful, log information about the export
        to the database.

        The items to log include:
        * filename
        * URL
        * timestamp
        * filesize

        :param name: String
        :param url: String
        :param datetime:
        :param size: Int
        :return: True if no errors occurred, else False.
        """

        def exportHistoryColumns():
            return ['name', 'url', 'timestamp', 'size']

        timestamp = lambda \
                datetime: 'to_timestamp(0)' if datetime == 0 else "timestamp " \
                                                                  "'{}'".format(
            datetime)

        sql = 'INSERT INTO "{0}" ({1}) VALUES ({2}, {3}, {4}, {5})'.format(
            self.configer.configOptionValue('Export', 'export_history_table'),
            ','.join(exportHistoryColumns()), "'" + name + "'", "'" + url + "'",
            timestamp(datetime), size)

        conn = MSGDBConnector().connectDB()
        cursor = conn.cursor()
        dbUtil = MSGDBUtil()
        result = dbUtil.executeSQL(cursor, sql, exitOnFail = False)
        conn.commit()
        return result


    def sendExportSummary(self, summary = ''):
        """
        Send a summary of exports via email to a preconfigured list of
        recipients.
        :param summary: String of summary content.
        :return:
        """
        try:
            if self.notifier.sendNotificationEmail(summary, testing = False):
                self.notifier.recordNotificationEvent(
                    types = MSGNotificationHistoryTypes,
                    noticeType = MSGNotificationHistoryTypes.MSG_EXPORT_SUMMARY)
        except Exception as detail:
            self.logger.log('Exception occurred: {}'.format(detail), 'ERROR')


    def currentExportSummary(self):
        """
        Current summary of exports since the last summary report time.

        Summaries are reported with identifier MSG_EXPORT_SUMMARY in the
        NotificationHistory.

        Includes:
        * Number of databases exported
        * Total number of files in the cloud.
        * A report of available storage capacity.
        * A list of available DBs.
        * A link where exports can be accessed.

        :return: String of summary text.
        """
        availableFilesURL = self.configer.configOptionValue('Export',
                                                            'export_list_url')
        lastReportDate = self.notifier.lastReportDate(
            types = MSGNotificationHistoryTypes,
            noticeType = MSGNotificationHistoryTypes.MSG_EXPORT_SUMMARY)
        content = 'Cloud Export Summary:\n\n'
        content += 'Last report date: {}\n'.format(lastReportDate)

        # @TO BE REVIEWED: Verify time zone adjustment.
        content += '{} databases have been exported since the last report ' \
                   'date.\n'.format(self.countOfDBExports(
            lastReportDate + datetime.timedelta(
                hours = 10)) if lastReportDate else self.countOfDBExports())

        content += '{} B free space is available.\n'.format(self.freeSpace())
        content += '\nCurrently available DBs:\n'
        content += self.plaintextListOfDownloadableFiles()
        content += '\n{} files can be accessed through Google Drive (' \
                   'https://drive.google.com) or at {}.'.format(
            self.countOfCloudFiles(), availableFilesURL)

        return content


    def countOfDBExports(self, since = None):
        """
        :param since: datetime indicating last export datetime.
        :return: Int of count of exports.
        """
        myDatetime = lambda x: datetime.datetime.strptime(x, '%Y-%m-%d %H:%S')
        if not since:
            since = myDatetime('1900-01-01 00:00')
        self.logger.log(since.strftime('%Y-%m-%d %H:%M'), 'DEBUG')

        sql = 'SELECT COUNT("public"."ExportHistory"."timestamp") FROM ' \
              '"public"."ExportHistory" WHERE "timestamp" > \'{}\''.format(
            since.strftime('%Y-%m-%d %H:%M'))

        conn = MSGDBConnector().connectDB()
        cursor = conn.cursor()
        dbUtil = MSGDBUtil()
        rows = None
        if dbUtil.executeSQL(cursor, sql, exitOnFail = False):
            rows = cursor.fetchall()
        assert len(rows) == 1, 'Invalid return value.'
        return rows[0][0]


    def countOfCloudFiles(self):
        """
        :param since: datetime indicating last trailing export datetime.
        :return: Int of count of exports.
        """
        return len(self.cloudFiles['items'])


    def __verifyMD5Sum(self, localFilePath, remoteFileID):
        """
        Verify that the local MD5 sum matches the MD5 sum for the remote file
        corresponding to an ID.

        This verifies that the uploaded file matches the local compressed
        export file.

        :param localFilePath: String of the full path of the local file.
        :param remoteFileID: String of the cloud ID for the remote file.
        :returns: Boolean True if the MD5 sums match, otherwise, False.
        """

        self.logger.log('remote file ID: {}'.format(remoteFileID))
        self.logger.log('local file path: {}'.format(localFilePath))

        # Get the md5sum for the local file.
        f = open(localFilePath, mode = 'rb')
        fContent = hashlib.md5()
        for buf in iter(partial(f.read, 128), b''):
            fContent.update(buf)
        localMD5Sum = fContent.hexdigest()
        f.close()

        self.logger.log('local md5: {}'.format(localMD5Sum), 'DEBUG')

        def verifyFile():
            # Get the MD5 sum for the remote file.
            for item in self.cloudFiles['items']:
                if (item['id'] == remoteFileID):
                    self.logger.log(
                        'remote md5: {}'.format(item['md5Checksum']), 'DEBUG')
                    if localMD5Sum == item['md5Checksum']:
                        return True
                    else:
                        return False

        try:
            if verifyFile():
                return True
            else:
                return False

        except errors.HttpError as detail:
            self.logger.log('HTTP error during MD5 verification.', 'error')

            time.sleep(10)

            if verifyFile():
                return True
            else:
                return False


    def fileIDForFileName(self, filename):
        """
        Get the file ID for the given filename.

        This method supports matching multiple cloud filenames but only
        returns the ID for a single matching filename.

        This can then be called recursively to obtain all the file IDs for a
        given filename.

        :param String of the filename for which to retrieve the ID.
        :returns: String of a cloud file ID or None if no match.
        """
        fileIDList = filter(lambda x: x['originalFilename'] == filename,
                            self.cloudFiles['items'])
        return fileIDList[0]['id'] if len(fileIDList) > 0 else None


    def filenameForFileID(self, fileID = ''):
        """
        :param fileID: String of cloud-based file ID.
        :return: String of filename for a given file ID.
        """
        return filter(lambda x: x['id'] == fileID, self.cloudFiles['items'])[0][
            'originalFilename']


    def addReaders(self, fileID = None, emailAddressList = None,
                   retryCount = 0):
        """
        Add reader permission to an export file that has been uploaded to the
        cloud for the given list of email addresses.

        Email notification is suppressed by default.

        :param fileID: String of the cloud file ID to be processed.
        :param emailAddressList: List of email addresses.
        :returns: Boolean True if successful, otherwise False.
        """
        # @todo Provide support for retry count
        success = True

        self.logger.log('file id: {}'.format(fileID))
        self.logger.log('address list: {}'.format(emailAddressList))

        for addr in emailAddressList:
            permission = {'value': addr, 'type': 'user', 'role': 'reader'}

            if fileID:
                try:
                    resp = self.driveService.permissions().insert(
                        fileId = fileID, sendNotificationEmails = False,
                        body = permission).execute()
                    self.logger.log(
                        'Reader permission added for {}.'.format(addr))
                except errors.HttpError as error:
                    self.logger.log('An error occurred: {}'.format(error))
                    success = False

        if not success and retryCount <= 0:
            return False
        elif success:
            return True
        else:
            time.sleep(self.retryDelay)
            self.logger.log('Retrying adding readers for ID {}.'.format(fileID),
                            'warning')
            self.addReaders(fileID = fileID,
                            emailAddressList = emailAddressList,
                            retryCount = retryCount - 1)