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')
class MSGWeatherDataRetriever(object):
"""
Retrieve national NOAA weather data relevant to the MSG project and save it
to local storage in the path given in the configuration file for [Weather
Data], weather_data_path.
Unzip the retrieved data and recompress the hourly data using gzip.
"""
def __init__(self):
self.configer = MSGConfiger()
self.pool = None
self.fileList = []
self.dateList = []
self.weatherUtil = MSGWeatherDataUtil()
global weatherDataPath
weatherDataPath = self.configer.configOptionValue(WEATHER_DATA, "weather_data_path")
global weatherDataURL
weatherDataURL = self.configer.configOptionValue(WEATHER_DATA, "weather_data_url")
global weatherDataPattern
weatherDataPattern = self.configer.configOptionValue(WEATHER_DATA, "weather_data_pattern")
def fileExists(self, filename):
# @todo Move to external module.
try:
with open(filename):
return True
except IOError, e:
# self.logger.log("IO Error: %s" % e, 'error')
return False
return False
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 __init__(self):
"""
Constructor.
"""
self.logger = SEKLogger(__name__)
self.configer = MSGConfiger()
self.fileUtil = MSGFileUtil()
class MECODataAutoloader(object):
"""
Provide automated loading of MECO energy data from exports in
gzip-compressed XML source data.
"""
def __init__(self):
"""
Constructor.
"""
self.logger = SEKLogger(__name__)
self.configer = MSGConfiger()
self.fileUtil = MSGFileUtil()
def newDataExists(self):
"""
Check the data autoload folder for the presence of new data.
:returns: True if new data exists.
"""
autoloadPath = self.configer.configOptionValue(
'MECO Autoload', 'meco_autoload_new_data_path')
if not self.fileUtil.validDirectory(autoloadPath):
raise Exception('InvalidDirectory', '%s' % autoloadPath)
patterns = ['*.gz']
matchCnt = 0
for root, dirs, filenames in os.walk(autoloadPath):
for pat in patterns:
for filename in fnmatch.filter(filenames, pat):
print filename
matchCnt += 1
if matchCnt > 0:
return True
else:
return False
def loadNewData(self):
"""
Load new data contained in the new data path.
"""
autoloadPath = self.configer.configOptionValue(
'MECO Autoload', 'meco_autoload_new_data_path')
command = self.configer.configOptionValue('MECO Autoload',
'meco_autoload_command')
os.chdir(autoloadPath)
try:
subprocess.check_call(command, shell=True)
except subprocess.CalledProcessError, e:
self.logger.log("An exception occurred: %s" % e, 'error')
class MECODataAutoloader(object):
"""
Provide automated loading of MECO energy data from exports in gzip-compressed XML source data.
"""
def __init__(self):
"""
Constructor.
"""
self.logger = MSGLogger(__name__)
self.configer = MSGConfiger()
self.fileUtil = MSGFileUtil()
def newDataExists(self):
"""
Check the data autoload folder for the presence of new data.
:returns: True if new data exists.
"""
autoloadPath = self.configer.configOptionValue('MECO Autoload', 'meco_new_data_path')
if not self.fileUtil.validDirectory(autoloadPath):
raise Exception('InvalidDirectory', '%s' % autoloadPath)
patterns = ['*.gz']
matchCnt = 0
for root, dirs, filenames in os.walk(autoloadPath):
for pat in patterns:
for filename in fnmatch.filter(filenames, pat):
print filename
matchCnt += 1
if matchCnt > 0:
return True
else:
return False
def loadNewData(self):
"""
Load new data contained in the new data path.
"""
autoloadPath = self.configer.configOptionValue('MECO Autoload', 'meco_new_data_path')
command = self.configer.configOptionValue('MECO Autoload', 'data_load_command')
os.chdir(autoloadPath)
try:
subprocess.check_call(command, shell = True)
except subprocess.CalledProcessError, e:
self.logger.log("An exception occurred: %s" % e, 'error')
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 WeatherDataLoadingTester(unittest.TestCase):
def setUp(self):
self.weatherUtil = MSGWeatherDataUtil()
self.logger = SEKLogger(__name__, 'DEBUG')
self.dbConnector = MSGDBConnector()
self.cursor = self.dbConnector.conn.cursor()
self.configer = MSGConfiger()
def testLoadDataSinceLastLoaded(self):
"""
Data should be loaded since the last data present in the database.
"""
pass
def testRetrieveDataSinceLastLoaded(self):
"""
Data since the last loaded date is retrieved.
"""
pass
def testGetLastLoadedDate(self):
myDate = self.weatherUtil.getLastDateLoaded(self.cursor).strftime(
"%Y-%m-%d %H:%M:%S")
pattern = '^(\d+-\d+-\d+\s\d+:\d+:\d+)$'
match = re.match(pattern, myDate)
assert match and (match.group(1) == myDate), "Date format is valid."
def testWeatherDataPattern(self):
myPattern = self.configer.configOptionValue('Weather Data',
'weather_data_pattern')
testString = """<A HREF="someURL">QCLCD201208.zip</A>"""
match = re.match(myPattern, testString)
self.logger.log("pattern = %s" % myPattern, 'info')
if match:
self.logger.log("match = %s" % match)
self.logger.log("match group = %s" % match.group(1))
else:
self.logger.log("match not found")
assert match and match.group(
1) == 'QCLCD201208.zip', "Download filename was matched."
def testWeatherDataURL(self):
myURL = self.configer.configOptionValue('Weather Data',
'weather_data_url')
pass
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 = ''
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 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')
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 __init__(self):
self.configer = MSGConfiger()
self.pool = None
self.fileList = []
self.dateList = []
self.weatherUtil = MSGWeatherDataUtil()
global weatherDataPath
weatherDataPath = self.configer.configOptionValue(
WEATHER_DATA, 'weather_data_path')
global weatherDataURL
weatherDataURL = self.configer.configOptionValue(
WEATHER_DATA, 'weather_data_url')
global weatherDataPattern
weatherDataPattern = self.configer.configOptionValue(
WEATHER_DATA, 'weather_data_pattern')
def __init__(self):
"""
Constructor.
"""
self.logger = MSGLogger(__name__)
self.configer = MSGConfiger()
self.fileUtil = MSGFileUtil()
def __init__(self):
"""
Constructor.
A database connection is not maintained here to keep this class
lightweight.
"""
self.logger = SEKLogger(__name__, DEBUG)
self.configer = MSGConfiger()
self.url = self.configer.configOptionValue('Weather Data',
'weather_data_url')
self.pattern = self.configer.configOptionValue('Weather Data',
'weather_data_pattern')
self.fileList = []
self.dateList = [] # List of dates corresponding weather data files.
self.fillFileListAndDateList()
self.dbUtil = MSGDBUtil()
def __init__(self):
"""
Constructor.
"""
self.logger = SEKLogger(__name__, 'debug')
self.mecoConfig = MSGConfiger()
self.currentReadingID = 0
self.dbUtil = MSGDBUtil()
def __init__(self, testing = False):
"""
Constructor.
:param testing: Use testing mode if True.
"""
self.reader = MECODBReader(testing)
self.configer = MSGConfiger()
self.dpi = 300
def __init__(self, testing=False, logLevel='silent'):
"""
Constructor.
:param testing: Boolean indicating if Testing Mode is on. When
testing mode is on, a connection to the testing database will be made
instead of the production database. This is useful for unit testing.
:param logLevel
"""
self.logger = SEKLogger(__name__, logLevel)
if testing:
self.logger.log("Testing Mode is ON.")
self.configer = MSGConfiger()
self.dbPassword = self.configer.configOptionValue(
"Database", 'db_password')
self.dbHost = self.configer.configOptionValue("Database", 'db_host')
self.dbPort = self.configer.configOptionValue("Database", 'db_port')
if testing:
self.dbName = self.configer.configOptionValue(
"Database", 'testing_db_name')
else:
self.dbName = self.configer.configOptionValue(
"Database", 'db_name')
self.logger.log("Instantiating DB connector with database {}.".format(
self.dbName))
self.dbUsername = self.configer.configOptionValue(
"Database", 'db_username')
self.conn = self.connectDB()
if not self.conn:
raise Exception('DB connection not available.')
try:
self.dictCur = self.conn.cursor(
cursor_factory=psycopg2.extras.DictCursor)
except AttributeError as error:
self.logger.log('Error while getting DictCursor: {}'.format(error))
def __init__(self, testing=False):
"""
Constructor.
:param testing: Flag indicating if testing mode is on.
"""
self.logger = SEKLogger(__name__)
self.parser = MECOXMLParser(testing)
self.configer = MSGConfiger()
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.config = MSGConfiger()
self.logger = MSGLogger(__name__, 'info')
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
class TestMECONotifier(unittest.TestCase):
"""
Unit tests for the MECO Notifier.
"""
def setUp(self):
self.logger = MSGLogger(__name__)
self.notifier = MSGNotifier()
self.configer = MSGConfiger()
def tearDown(self):
pass
def testInit(self):
self.assertIsNotNone(self.notifier, "Notifier has been initialized.")
def testEmailServer(self):
"""
Test connecting to the email server.
"""
errorOccurred = False
user = self.configer.configOptionValue('Notifications', 'email_username')
password = self.configer.configOptionValue('Notifications',
'email_password')
server = smtplib.SMTP(
self.configer.configOptionValue('Notifications', 'email_smtp_server'))
try:
server.starttls()
except smtplib.SMTPException, e:
print "Exception = %s" % e
try:
server.login(user, password)
except smtplib.SMTPException, e:
print "Exception = %s" % e
def __init__(self):
self.configer = MSGConfiger()
self.pool = None
self.fileList = []
self.dateList = []
self.weatherUtil = MSGWeatherDataUtil()
global weatherDataPath
weatherDataPath = self.configer.configOptionValue(WEATHER_DATA, "weather_data_path")
global weatherDataURL
weatherDataURL = self.configer.configOptionValue(WEATHER_DATA, "weather_data_url")
global weatherDataPattern
weatherDataPattern = self.configer.configOptionValue(WEATHER_DATA, "weather_data_pattern")
class MSGWeatherDataRetriever(object):
"""
Retrieve national NOAA weather data relevant to the MSG project and save it
to local storage in the path given in the configuration file for [Weather
Data], weather_data_path.
Unzip the retrieved data and recompress the hourly data using gzip.
"""
def __init__(self):
self.configer = MSGConfiger()
self.pool = None
self.fileList = []
self.dateList = []
self.weatherUtil = MSGWeatherDataUtil()
global weatherDataPath
weatherDataPath = self.configer.configOptionValue(
WEATHER_DATA, 'weather_data_path')
global weatherDataURL
weatherDataURL = self.configer.configOptionValue(
WEATHER_DATA, 'weather_data_url')
global weatherDataPattern
weatherDataPattern = self.configer.configOptionValue(
WEATHER_DATA, 'weather_data_pattern')
def fileExists(self, filename):
# @todo Move to external module.
try:
with open(filename):
return True
except IOError, e:
# self.logger.log("IO Error: %s" % e, 'error')
return False
return False
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")
def __init__(self):
self.configer = MSGConfiger()
self.pool = None
self.fileList = []
self.dateList = []
self.weatherUtil = MSGWeatherDataUtil()
global weatherDataPath
weatherDataPath = self.configer.configOptionValue('Weather Data',
'weather_data_path')
global weatherDataURL
weatherDataURL = self.configer.configOptionValue('Weather Data',
'weather_data_url')
global weatherDataPattern
weatherDataPattern = self.configer.configOptionValue('Weather Data',
'weather_data_pattern')
def __init__(self):
"""
Constructor.
A database connection is not maintained here to keep this class
lightweight.
"""
self.logger = SEKLogger(__name__, DEBUG)
self.configer = MSGConfiger()
self.url = self.configer.configOptionValue('Weather Data',
'weather_data_url')
self.pattern = self.configer.configOptionValue('Weather Data',
'weather_data_pattern')
self.fileList = []
self.dateList = [] # List of dates corresponding weather data files.
self.fillFileListAndDateList()
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 = 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 __init__(self):
"""
Constructor.
A database connection is not maintained here to keep this class
lightweight. This results in the class not having a parameter for
TESTING MODE.
"""
self.logger = MSGLogger(__name__, 'info')
self.configer = MSGConfiger()
self.url = self.configer.configOptionValue('Weather Data',
'weather_data_url')
self.pattern = self.configer.configOptionValue('Weather Data',
'weather_data_pattern')
self.fileList = []
self.dateList = [] # List of dates corresponding weather data files.
self.fillFileListAndDateList()
self.dbUtil = MSGDBUtil()
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 __init__(self, testing = False, logLevel = 'silent'):
"""
Constructor.
:param testing: Boolean indicating if Testing Mode is on. When
testing mode is on, a connection to the testing database will be made
instead of the production database. This is useful for unit testing.
:param logLevel
"""
self.logger = SEKLogger(__name__, logLevel)
if testing:
self.logger.log("Testing Mode is ON.")
self.configer = MSGConfiger()
self.dbPassword = self.configer.configOptionValue("Database",
'db_password')
self.dbHost = self.configer.configOptionValue("Database", 'db_host')
self.dbPort = self.configer.configOptionValue("Database", 'db_port')
if testing:
self.dbName = self.configer.configOptionValue("Database",
'testing_db_name')
else:
self.dbName = self.configer.configOptionValue("Database", 'db_name')
self.logger.log(
"Instantiating DB connector with database {}.".format(self.dbName))
self.dbUsername = self.configer.configOptionValue("Database",
'db_username')
self.conn = self.connectDB()
if not self.conn:
raise Exception('DB connection not available.')
try:
self.dictCur = self.conn.cursor(
cursor_factory = psycopg2.extras.DictCursor)
except AttributeError as error:
self.logger.log('Error while getting DictCursor: {}'.format(error))
class TestMECOConfig(unittest.TestCase):
def setUp(self):
self.configer = MSGConfiger()
def test_init(self):
# @todo Improve this test by using direct introspection instead of
# this roundabout method.
localConfiger = MSGConfiger()
self.assertIsInstance(self.configer, type(localConfiger))
def test_get_debugging(self):
"""
Verify the debugging option in the configuration file.
"""
debugging = self.configer.configOptionValue("Debugging", "debug")
if debugging is False:
self.assertFalse(debugging, "Debugging/debug is not set to False.")
elif debugging is True:
self.assertTrue(debugging, "Debugging/debug is not set to True.")
else:
self.assertTrue(False,
"Debugging/debug does not have a valid value.")
def setUp(self):
self.logger = SEKLogger(__name__)
self.notifier = MSGNotifier()
self.configer = MSGConfiger()
class TestMECONotifier(unittest.TestCase):
"""
Unit tests for the MECO Notifier.
"""
def setUp(self):
self.logger = SEKLogger(__name__)
self.notifier = MSGNotifier()
self.configer = MSGConfiger()
def tearDown(self):
pass
def testInit(self):
self.assertIsNotNone(self.notifier, "Notifier has been initialized.")
def testEmailServer(self):
"""
Test connecting to the email server.
"""
errorOccurred = False
user = self.configer.configOptionValue("Notifications", "email_username")
password = self.configer.configOptionValue("Notifications", "email_password")
server = smtplib.SMTP(self.configer.configOptionValue("Notifications", "smtp_server_and_port"))
try:
server.starttls()
except smtplib.SMTPException as detail:
self.logger.log("Exception: {}".format(detail))
try:
server.login(user, password)
except smtplib.SMTPException as detail:
self.logger.log("Exception: {}".format(detail))
self.assertFalse(errorOccurred, "No errors occurred during SMTP setup.")
def testSendEmailNotification(self):
"""
Send a test notification by email.
"""
if SEND_EMAIL:
success = self.notifier.sendNotificationEmail(
"This is a message from testSendEmailNotification.", testing=True
)
self.assertTrue(success, "Sending an email notification did not produce an" " exception.")
else:
self.assertTrue(True, "Email is not sent when SEND_EMAIL is False.")
def testSendEmailAttachment(self):
"""
Send a test notification with attachment by email.
"""
if SEND_EMAIL:
body = "Test message"
testDataPath = self.configer.configOptionValue("Testing", "test_data_path")
file = os.path.join(testDataPath, "graph.png")
success = self.notifier.sendMailWithAttachments(body, [file], testing=True)
success = success != True
self.assertTrue(success, "Sending an email notification did not produce an" " exception.")
else:
self.assertTrue(True, "Email is not sent when SEND_EMAIL is False.")
def setUp(self):
self.m = MECOMapper()
self.testColumns = self.m.dbColsMeterData.values()
self.testLabels = self.m.dbColsMeterData.keys()
self.testTableName = 'MeterData'
self.config = MSGConfiger()
class MECOXMLParser(object):
"""
Parses XML for MECO data.
"""
tableName = ''
def __init__(self, testing = False):
"""
Constructor.
:param testing: (optional) Boolean indicating if Testing Mode is on.
"""
self.logger = SEKLogger(__name__, 'silent')
if (testing):
self.logger.log("Testing Mode is ON.", 'info')
self.debug = False
self.configer = MSGConfiger()
if self.configer.configOptionValue("Debugging", 'debug') == True:
self.debug = True
self.util = MSGDBUtil()
self.mapper = MECOMapper()
self.connector = MSGDBConnector(testing)
self.conn = self.connector.connectDB()
self.filename = None
self.fileObject = None
self.processForInsertElementCount = 0
self.inserter = MECODBInserter()
self.insertDataIntoDatabase = False
# Count number of times sections in source data are encountered.
self.tableNameCount = {'SSNExportDocument': 0, 'MeterData': 0,
'RegisterData': 0, 'RegisterRead': 0, 'Tier': 0,
'Register': 0, 'IntervalReadData': 0,
'Interval': 0, 'Reading': 0, 'IntervalStatus': 0,
'ChannelStatus': 0, 'EventData': 0, 'Event': 0}
# Use this dictionary to track which channels were processed when
# readings are being processed. this is to prevent duplicate channel
# data from being inserted.
self.channelProcessed = {}
self.initChannelProcessed()
# Tables to be inserted to.
self.insertTables = self.configer.insertTables
self.lastSeqVal = None
self.fKeyVal = None
self.lastTable = None
self.fkDeterminer = MECOFKDeterminer()
self.dupeChecker = MECODupeChecker()
self.currentMeterName = None
self.currentIntervalEndTime = None
self.currentRegisterReadReadTime = None
self.dupesExist = False
self.channelDupeExists = False # For Reading dupes.
self.numberDupeExists = False # For Register dupes.
self.eventTimeDupeExists = False # For Event dupes.
self.commitCount = 0
self.readingDupeOnInsertCount = 0 # For Reading dupes.
self.registerDupeOnInsertCount = 0 # For Register dupes.
self.eventDupeOnInsertCount = 0 # For Event dupes.
self.dataProcessCount = 0
self.readingDupeCheckCount = 0 # For Reading dupes.
self.registerDupeCheckCount = 0 # For Register dupes.
self.eventDupeCheckCount = 0 # For Event dupes.
self.insertCount = 0
self.cumulativeInsertCount = 0
self.nonProcessForInsertElementCount = 0
self.readingInsertCount = 0
self.registerInsertCount = 0
self.eventInsertCount = 0
self.totalReadingInsertCount = 0
self.totalRegisterInsertCount = 0
self.totalEventInsertCount = 0
self.totalReadingDupeOnInsertCount = 0
self.totalRegisterDupeOnInsertCount = 0
self.totalEventDupeOnInsertCount = 0
def parseXML(self, fileObject, insert = False, jobID = ''):
"""
Parse an XML file.
:param fileObject: a file object referencing an XML file.
:param insert: (optional) True to insert to the database | False to
perform no
inserts.
:returns: String containing a concise log of parsing.
"""
print "parseXML:"
self.commitCount = 0
self.insertDataIntoDatabase = insert
parseMsg = "\nParsing XML in %s.\n" % self.filename
sys.stderr.write(parseMsg)
parseLog = parseMsg
tree = ET.parse(fileObject)
root = tree.getroot()
parseLog += self.walkTheTreeFromRoot(root, jobID = jobID)
return parseLog
def tableNameForAnElement(self, element):
"""
Get the tablename for an element.
:param element: Element tree element.
:returns: table name
"""
try:
name = re.search('\{.*\}(.*)', element.tag).group(1)
except:
name = None
return name
def processDataToBeInserted(self, columnsAndValues, currentTableName,
fKeyValue, parseLog, pkeyCol, jobID = ''):
"""
This is the method that performs insertion of parsed data to the
database. Duplicate checks are performed on the endpoints of the data
branches.
:param columnsAndValues: A dictionary containing columns and their
values.
:param currentTableName: The name of the current table.
:param fKeyValue: The value of the foreign key.
:param parseLog: String containing a concise log of operations.
:param pkeyCol: Column name for the primary key.
:param jobID: Identifier for multiprocessing process.
:returns: A string containing the parse log.
"""
self.dataProcessCount += 1
# Handle a special case for duplicate reading data.
# Intercept the duplicate reading data before insert.
if currentTableName == "Reading":
self.channelDupeExists = self.dupeChecker.readingBranchDupeExists(
self.conn, self.currentMeterName, self.currentIntervalEndTime,
columnsAndValues['Channel'])
self.readingDupeCheckCount += 1
if currentTableName == "Register":
self.numberDupeExists = self.dupeChecker.registerBranchDupeExists(
self.conn, self.currentMeterName,
self.currentRegisterReadReadTime, columnsAndValues['Number'])
self.registerDupeCheckCount += 1
if currentTableName == "Event":
self.eventTimeDupeExists = self.dupeChecker.eventBranchDupeExists(
self.conn, self.currentMeterName, columnsAndValues['EventTime'])
self.eventDupeCheckCount += 1
# Only perform an insert if there are no duplicate values
# for the channel.
if not self.channelDupeExists and not self.numberDupeExists and not \
self.eventTimeDupeExists:
# ***********************
# ***** INSERT DATA *****
# ***********************
cur = self.inserter.insertData(self.conn, currentTableName,
columnsAndValues,
fKeyVal = fKeyValue,
withoutCommit = 1)
# The last 1 indicates don't commit. Commits are handled externally.
self.insertCount += 1
self.cumulativeInsertCount += 1
# Only attempt getting the last sequence value if an insertion
# took place.
self.lastSeqVal = self.util.getLastSequenceID(self.conn,
currentTableName,
pkeyCol)
# Store the primary key.
self.fkDeterminer.pkValforCol[pkeyCol] = self.lastSeqVal
if currentTableName == "Reading":
self.readingInsertCount += 1
self.totalReadingInsertCount += 1
elif currentTableName == "Register":
self.registerInsertCount += 1
self.totalRegisterInsertCount += 1
elif currentTableName == "Event":
self.eventInsertCount += 1
self.totalEventInsertCount += 1
else: # Don't insert into Reading or Register table if a dupe exists.
if (self.channelDupeExists):
self.readingDupeOnInsertCount += 1
self.totalReadingDupeOnInsertCount += 1
if self.readingDupeOnInsertCount > 0 and self\
.readingDupeOnInsertCount < 2:
parseLog += self.logger.logAndWrite(
"%s:{rd-dupe==>}" % jobID)
# Also, verify the data is equivalent to the existing record.
matchingValues = self.dupeChecker.readingValuesAreInTheDatabase(
self.conn, columnsAndValues)
assert matchingValues == True, "Duplicate check found " \
"non-matching values for meter" \
" %s," \
" endtime %s, channel %s (%s, " \
"%s)." % (
self.currentMeterName,
self.currentIntervalEndTime,
columnsAndValues['Channel'],
columnsAndValues['RawValue'],
columnsAndValues['Value'])
self.channelDupeExists = False
elif (self.numberDupeExists):
self.registerDupeOnInsertCount += 1
self.totalRegisterDupeOnInsertCount += 1
if self.registerDupeOnInsertCount > 0 and self\
.registerDupeOnInsertCount < 2:
parseLog += self.logger.logAndWrite(
"%s:{re-dupe==>}" % jobID)
self.numberDupeExists = False
elif (self.eventTimeDupeExists):
self.eventDupeOnInsertCount += 1
self.totalEventDupeOnInsertCount += 1
if self.eventDupeOnInsertCount > 0 and self\
.eventDupeOnInsertCount < 2:
parseLog += self.logger.logAndWrite(
"%s:{ev-dupe==>}" % jobID)
self.eventTimeDupeExists = False
else:
assert True == False, "Duplicate condition does not exist."
self.logger.log('Record not inserted for %s.' % columnsAndValues,
'silent')
return parseLog
def generateConciseLogEntries(self, jobID = '', reportType = None):
"""
Create log entries in the concise log.
:param jobID: Identifier used to distinguish multiprocessing jobs.
:returns: A concatenated string of log entries.
"""
# @todo Change report type to enum type.
log = ''
if reportType == 'FINAL':
self.logger.log('Final report', 'info')
if self.readingDupeOnInsertCount > 0 or self\
.registerDupeOnInsertCount > 0 or self.eventDupeOnInsertCount\
> 0:
log = self.logger.logAndWrite("%s:{%srd,%sre,%sev}" % (
jobID, self.totalReadingDupeOnInsertCount,
self.totalRegisterDupeOnInsertCount,
self.totalEventDupeOnInsertCount))
else:
log = ''
log += self.logger.logAndWrite("(%s)" % self.commitCount)
log += self.logger.logAndWrite(
"[%s]" % self.processForInsertElementCount)
log += self.logger.logAndWrite("<%srd,%sre,%sev,%s>" % (
self.totalReadingInsertCount, self.totalRegisterInsertCount,
self.totalEventInsertCount, self.cumulativeInsertCount))
elif reportType == 'INTERMEDIARY':
if self.readingDupeOnInsertCount > 0 or self\
.registerDupeOnInsertCount > 0 or self.eventDupeOnInsertCount\
> 0:
log = self.logger.logAndWrite("%s:{%srd,%sre,%sev}" % (
jobID, self.readingDupeOnInsertCount,
self.registerDupeOnInsertCount,
self.eventDupeOnInsertCount))
else:
log = ''
log += self.logger.logAndWrite("(%s)" % self.commitCount)
log += self.logger.logAndWrite(
"[%s]" % self.processForInsertElementCount)
log += self.logger.logAndWrite("<%srd,%sre,%sev,%s,%s>" % (
self.readingInsertCount, self.registerInsertCount,
self.eventInsertCount, self.insertCount,
self.cumulativeInsertCount))
return log
def resetGroupCounters(self):
"""
Reset counters that are keeping track of groups.
"""
self.readingDupeOnInsertCount = 0
self.insertCount = 0
self.readingInsertCount = 0
self.registerDupeOnInsertCount = 0
self.registerInsertCount = 0
self.eventInsertCount = 0
self.eventDupeOnInsertCount = 0
def performTableBasedOperations(self, columnsAndValues, currentTableName,
element):
"""
Perform operations that are based on the current table.
:param columnsAndValues
:param currentTableName
:param element
"""
if currentTableName == "MeterData":
self.currentMeterName = columnsAndValues['MeterName']
elif currentTableName == "Interval":
self.currentIntervalEndTime = columnsAndValues['EndTime']
elif currentTableName == "RegisterRead":
self.currentRegisterReadReadTime = columnsAndValues['ReadTime']
elif currentTableName == "Event":
columnsAndValues['Event_Content'] = element.text
def walkTheTreeFromRoot(self, root, jobID = ''):
"""
Walk an XML tree from its root node.
:param root: The root node of an XML tree.
:param jobID: Identifier used to distinguish multiprocessing jobs.
:returns: String containing a concise log of parsing activity.
"""
parseLog = ''
walker = root.iter()
for element, nextElement in self.getNext(walker):
# Process every element in the tree while reading ahead to get
# the next element.
currentTableName = self.tableNameForAnElement(element)
nextTableName = self.tableNameForAnElement(nextElement)
assert currentTableName is not None, "Current table does not exist."
# Maintain a count of tables encountered.
self.tableNameCount[currentTableName] += 1
columnsAndValues = {}
it = iter(sorted(element.attrib.iteritems()))
for item in list(it):
# Create a dictionary of column names and values.
columnsAndValues[item[0]] = item[1]
if currentTableName in self.insertTables:
# Check if the current table is one of the tables to have data
# inserted.
self.processForInsertElementCount += 1
if self.debug:
self.logger.log("Processing table %s, next is %s." % (
currentTableName, nextTableName), 'debug')
# Get the column name for the primary key.
pkeyCol = self.mapper.dbColumnsForTable(currentTableName)[
'_pkey']
fkeyCol = None
fKeyValue = None
try:
# Get the column name for the foreign key.
fkeyCol = self.mapper.dbColumnsForTable(currentTableName)[
'_fkey']
except:
pass
if self.debug:
self.logger.log("foreign key col (fkey) = %s" % fkeyCol,
'debug')
self.logger.log("primary key col (pkey) = %s" % pkeyCol,
'debug')
self.logger.log(columnsAndValues, 'debug')
if fkeyCol is not None:
# Get the foreign key value.
fKeyValue = self.fkDeterminer.pkValforCol[fkeyCol]
if self.debug:
self.logger.log("fKeyValue = %s" % fKeyValue, 'debug')
self.performTableBasedOperations(columnsAndValues,
currentTableName, element)
if self.insertDataIntoDatabase:
# Data is intended to be inserted into the database.
parseLog = self.processDataToBeInserted(columnsAndValues,
currentTableName,
fKeyValue, parseLog,
pkeyCol,
jobID = jobID)
if self.debug:
self.logger.log("lastSeqVal = ", self.lastSeqVal)
if self.lastReading(currentTableName, nextTableName):
# The last reading set has been reached.
if self.debug:
self.logger.log("----- last reading found -----",
'debug')
parseLog += self.generateConciseLogEntries(jobID = jobID,
reportType =
'INTERMEDIARY')
self.resetGroupCounters()
parseLog += self.logger.logAndWrite("*")
self.commitCount += 1
self.conn.commit()
if self.lastRegister(currentTableName, nextTableName):
# The last register set has been reached.
if self.debug:
self.logger.log("----- last register found -----",
'debug')
# Initial commit.
if self.commitCount == 0:
parseLog += self.generateConciseLogEntries(jobID = jobID,
reportType =
'INTERMEDIARY')
self.resetGroupCounters()
# Final commit.
parseLog += self.logger.logAndWrite("---")
parseLog += self.generateConciseLogEntries(jobID = jobID,
reportType = 'FINAL')
self.resetGroupCounters()
parseLog += self.logger.logAndWrite("*")
self.commitCount += 1
self.conn.commit()
sys.stderr.write("\n")
self.logger.log("Data process count = %s." % self.dataProcessCount,
'info')
self.logger.log(
"Reading dupe check count = %s." % self.readingDupeCheckCount,
'info')
return parseLog
def lastReading(self, currentTable, nextTable):
"""
Determine if the last reading is being visited.
:param currentTable: current table being processsed.
:param nextTable: next table to be processed.
:returns: True if last object in Reading table was read,
otherwise return False.
"""
if currentTable == "Reading" and (
nextTable == "MeterData" or nextTable == None):
return True
return False
def lastRegister(self, currentTable, nextTable):
"""
Determine if the last register is being visited.
:param currentTable: current table being processsed.
:param nextTable: next table to be processed.
:returns: True if last object in Register table was read,
otherwise return False.
"""
if currentTable == "Register" and (
nextTable == "MeterData" or nextTable == None):
return True
return False
def getNext(self, somethingIterable, window = 1):
"""
Return the current item and next item in an iterable data structure.
:param somethingIterable: Something that has an iterator.
:param window: How far to look ahead in the collection.
:returns: The current iterable value and the next iterable value.
"""
items, nexts = tee(somethingIterable, 2)
nexts = islice(nexts, window, None)
return izip_longest(items, nexts)
def initChannelProcessed(self):
"""
Initialize the dictionary for channel processing.
"""
self.channelProcessed = {'1': False, '2': False, '3': False, '4': False}
def getLastElement(self, rows):
"""
Get the last element in a collection.
Example:
rows = (element1, element2, element3)
getLastElement(rows) # return element3
:param rows: Result rows from a query.
:returns: The last element in the collection.
"""
for i, var in enumerate(rows):
if i == len(rows) - 1:
return var
def setUp(self):
self.weatherUtil = MSGWeatherDataUtil()
self.logger = SEKLogger(__name__, 'DEBUG')
self.dbConnector = MSGDBConnector()
self.cursor = self.dbConnector.conn.cursor()
self.configer = MSGConfiger()
class MSGWeatherDataUtil(object):
"""
Utility methods for working with weather data.
"""
def __init__(self):
"""
Constructor.
A database connection is not maintained here to keep this class
lightweight.
"""
self.logger = SEKLogger(__name__, DEBUG)
self.configer = MSGConfiger()
self.url = self.configer.configOptionValue('Weather Data',
'weather_data_url')
self.pattern = self.configer.configOptionValue('Weather Data',
'weather_data_pattern')
self.fileList = []
self.dateList = [] # List of dates corresponding weather data files.
self.fillFileListAndDateList()
self.dbUtil = MSGDBUtil()
def fillFileListAndDateList(self):
"""
Return a list of weather files obtained from the remote server used
in processing weather data.
"""
response = urllib2.urlopen(self.url).read()
self.logger.log('Filling file list:', DEBUG)
for filename in re.findall(self.pattern, response):
# Only examine first match group in the filename match.
self.logger.log('filename {}'.format(filename[0]), DEBUG)
self.fileList.append(filename[0])
self.dateList.append(self.datePart(filename[0]))
def datePart(self, filename=None, datetime=None):
"""
Return the date part of a NOAA weather data filename.
:param: String of the filename.
:param: datetime object.
:returns: String of the date part of the given parameter.
"""
assert filename == None or datetime == None, "One argument is allowed."
if filename:
newName = filename.replace("QCLCD", '')
newName = newName.replace(".zip", '')
return newName
if datetime:
return datetime.strftime('%Y-%m-%d')
def getLastDateLoaded(self, cursor):
"""
Return the last date of loaded weather data.
:returns: Last date.
"""
sql = """select wban, datetime, record_type from "%s"
ORDER BY datetime desc limit 1""" % WEATHER_DATA_TABLE
self.dbUtil.executeSQL(cursor, sql)
row = cursor.fetchone()
# self.logger.log('Date last loaded = %s' % row[1], 'info')
return row[1]
def getKeepList(self, fileList, cursor):
"""
The Keep List is the list of filenames of files containing data that are
*within* the month of the last loaded date or are beyond the last loaded
date.
:param: fileList: A list of files containing weather data.
:param: DB cursor.
:returns: List of weather data filenames to process.
"""
keepList = []
i = 0
for date in fileList:
self.logger.log('Examining date %s.' % date)
# The list date should be the last day of the month.
# It is the date that is compared against the last retrieved date.
listDate = dt.datetime.strptime(self.datePart(filename=date),
"%Y%m")
lastDay = calendar.monthrange(listDate.year, listDate.month)[1]
listDate = dt.datetime.strptime(
'%s-%s-%s' % (listDate.year, listDate.month, lastDay),
"%Y-%m-%d")
self.logger.log('List date = %s.' % listDate)
lastDate = self.getLastDateLoaded(cursor)
self.logger.log('last date = %s' % lastDate)
if lastDate <= listDate:
keepList.append((i, listDate))
i += 1
if keepList:
keepList.sort()
return [fileList[d[0]] for d in keepList]
class MSGDBExporterTester(unittest.TestCase):
"""
Unit tests for the MSG Cloud Exporter.
"""
def setUp(self):
self.logger = MSGLogger(__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()
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')
def upload_test_data_to_cloud(self):
"""
Provide an upload of test data that can be used in other tests.
Side effect: Store the file ID as an ivar.
"""
self.logger.log("Uploading test data for caller: {}".format(
self.pyUtil.caller_name()))
filePath = "{}/{}".format(self.exportTestDataPath,
self.compressedTestFilename)
self.logger.log('Uploaded {}.'.format(filePath), 'info')
uploadResult = self.exporter.uploadFileToCloudStorage(filePath)
self.logger.log('upload result: {}'.format(uploadResult))
self.testDataFileID = self.exporter.fileIDForFileName(
self.compressedTestFilename)
self.logger.log("Test file ID is {}.".format(self.testDataFileID))
def test_sending_fcphase_part_0(self):
"""
/home/daniel/msg-db-dumps/2014-05-14_141223_fcphase3.sql.gz.0
"""
filesToUpload = [
'/home/daniel/msg-db-dumps/2014-05-14_141223_fcphase3.sql.gz.0',
'/home/daniel/msg-db-dumps/2014-05-14_141223_fcphase3.sql.gz.1',
'/home/daniel/msg-db-dumps/2014-05-14_141223_fcphase3.sql.gz.2',
'/home/daniel/msg-db-dumps/2014-05-14_141223_fcphase3.sql.gz.3']
for f in filesToUpload:
self.exporter.uploadFileToCloudStorage(fullPath = f,
testing = False)
def testListRemoteFiles(self):
"""
Test listing of remote files.
"""
self.logger.log('Testing listing of remote files.', 'INFO')
title = ''
id = ''
for item in self.exporter.cloudFiles['items']:
title = item['title']
id = item['id']
self.assertIsNot(title, '')
self.assertIsNot(id, '')
def testDownloadURLList(self):
"""
Test obtaining a list of downloadble URLs.
"""
self.logger.log('Testing listing of downloadable files.', 'INFO')
title = ''
id = ''
url = ''
for item in self.exporter.cloudFiles['items']:
title = item['title']
url = item['webContentLink']
id = item['id']
self.logger.log('title: %s, link: %s, id: %s' % (title, url, id))
self.assertIsNot(title, '')
self.assertIsNot(url, '')
self.assertIsNot(id, '')
def test_list_of_downloadable_files(self):
"""
Test the list of downloadable files used by the available files page.
"""
self.assertIsNotNone(self.exporter.listOfDownloadableFiles(),
'List of downloadable files is not available.')
for row in self.exporter.listOfDownloadableFiles():
print row
self.assertIsNotNone(row['id'])
self.assertIsNotNone(row['title'])
self.assertIsNotNone(row['webContentLink'])
def test_markdown_list_of_downloadable_files(self):
print self.exporter.markdownListOfDownloadableFiles()
myPath = '{}/{}'.format(
self.configer.configOptionValue('Export', 'db_export_path'),
'list-of-downloadable-files.txt')
fp = open(myPath, 'wb')
fp.write(self.exporter.markdownListOfDownloadableFiles())
fp.close()
def test_get_md5_sum_from_cloud(self):
"""
Test retrieving the MD5 sum from the cloud.
"""
# @REVIEWED
self.logger.log('Testing getting the MD5 sum.', 'info')
self.upload_test_data_to_cloud()
testFileMD5 = filter(lambda x: x['id'] == self.testDataFileID,
self.exporter.cloudFiles['items'])[0][
'md5Checksum']
self.assertEquals(len(testFileMD5), 32)
self.assertTrue(re.match(r'[0-9A-Za-z]+', testFileMD5))
def testGetFileIDsForFilename(self):
"""
Retrieve the matching file IDs for the given file name.
"""
self.logger.log('Testing getting file IDs for a filename.')
self.logger.log("Uploading test data.")
filePath = "{}/{}".format(self.exportTestDataPath,
self.compressedTestFilename)
uploadResult = self.exporter.uploadFileToCloudStorage(filePath)
self.assertTrue(uploadResult)
self.logger.log('Testing getting the file ID for a filename.')
fileIDs = self.exporter.fileIDForFileName(self.compressedTestFilename)
self.logger.log("file ids = {}".format(fileIDs), 'info')
self.assertIsNotNone(fileIDs)
def test_get_file_id_for_nonexistent_file(self):
"""
Test getting a file ID for a nonexistent file.
"""
fileIDs = self.exporter.fileIDForFileName('nonexistent_file')
self.logger.log("file ids = {}".format(fileIDs), 'info')
self.assertIsNone(fileIDs)
def test_upload_test_data(self):
"""
Upload a test data file for unit testing of DB export.
The unit test data file is a predefined set of test data stored in
the test data path of the software distribution.
"""
# @REVIEWED
self.upload_test_data_to_cloud()
self.assertGreater(len(self.testDataFileID), 0)
self.assertTrue(re.match(r'[0-9A-Za-z]+', self.testDataFileID))
def test_delete_out_dated_files(self):
"""
The timestamp of an uploaded file should be set in the past to provide
the ability to test the deleting of outdated files.
"""
# return
# @TO BE REVIEWED Prevent deleting files uploaded today.
# @IMPORTANT Prevent deleting NON-testing files.
# Need to have a test file uploaded that has an explicitly set upload
# date.
self.logger.log("Test deleting outdated files.")
self.logger.log("Uploading test data.")
filePath = "%s/%s" % (
self.exportTestDataPath, self.compressedTestFilename)
uploadResult = self.exporter.uploadFileToCloudStorage(filePath)
cnt = self.exporter.deleteOutdatedFiles(
minAge = datetime.timedelta(days = 5),
maxAge = datetime.timedelta(days = 99999))
# self.assertGreater(cnt, 0)
def testAddingReaderPermissions(self):
"""
Add reader permissions to a file that was uploaded.
@todo Needs update after cloud export restoration.
"""
self.logger.log("Testing adding reader permissions.")
self.logger.log("Uploading test data.")
filePath = "%s/%s" % (
self.exportTestDataPath, self.compressedTestFilename)
uploadResult = self.exporter.uploadFileToCloudStorage(filePath)
email = self.configer.configOptionValue('Testing', 'tester_email')
service = self.exporter.driveService
try:
id_resp = service.permissions().getIdForEmail(
email = email).execute()
print id_resp
except errors.HttpError as detail:
print 'Exception while getting ID for email: %s' % detail
new_permission = {'value': email, 'type': 'user', 'role': 'reader'}
try:
self.logger.log('Adding reader permission', 'INFO')
fileIDToAddTo = self.exporter.fileIDForFileName(
self.compressedTestFilename)
# The permission dict is being output to stdout here.
resp = service.permissions().insert(fileId = fileIDToAddTo,
sendNotificationEmails = False,
body = new_permission).execute()
except errors.HttpError as detail:
self.logger.log(
'Exception while adding reader permissions: %s' % detail,
'error')
def test_create_compressed_archived(self):
"""
* Copy test data to a temp directory.
* Create a checksum for test data.
* Create a gzip-compressed archive.
* Extract gzip-compressed archive.
* Create a checksum for the uncompressed data.
* Compare the checksums.
@todo Needs update after cloud export restoration.
"""
self.logger.log('Testing verification of a compressed archive.')
self.logger.log('cwd {}'.format(os.getcwd()))
fullPath = '{}'.format(os.path.join(os.getcwd(), self.testDir,
self.uncompressedTestFilename))
shutil.copyfile('{}/{}'.format(self.exportTestDataPath,
self.uncompressedTestFilename), fullPath)
md5sum1 = self.fileUtil.md5Checksum(fullPath)
self.exporter.fileUtil.gzipCompressFile(fullPath)
try:
os.remove(os.path.join(os.getcwd(), self.testDir,
self.uncompressedTestFilename))
except OSError as detail:
self.logger.log('Exception while removing: {}'.format(detail),
'ERROR')
# Extract archived data and generate checksum.
src = gzip.open('{}{}'.format(fullPath, '.gz'), "rb")
uncompressed = open(fullPath, "wb")
decoded = src.read()
uncompressed.write(decoded)
uncompressed.close()
md5sum2 = self.fileUtil.md5Checksum(fullPath)
self.assertEqual(md5sum1, md5sum2,
'Checksums are not equal for original and new '
'decompressed archive.')
def test_export_db(self):
"""
Perform a quick test of the DB export method using Testing Mode.
@todo This needs a static test database!
"""
self.logger.log('Testing exportDB')
dbs = ['test_meco']
success = self.exporter.exportDB(databases = dbs, toCloud = True,
localExport = True, numChunks = 4)
self.logger.log('Success: {}'.format(success))
self.assertTrue(success, "Export was not successful.")
def test_split_archive(self):
"""
Test splitting an archive into chunks.
"""
# @REVIEWED
self.logger.log('Testing archive splitting.')
fullPath = '{}/{}'.format(self.exportTestDataPath,
self.compressedTestFilename)
self.logger.log('fullpath: {}'.format(fullPath))
shutil.copyfile(fullPath, '{}/{}'.format(self.testDir,
self.compressedTestFilename))
fullPath = '{}/{}'.format(self.testDir, self.compressedTestFilename)
self.fileChunks = self.fileUtil.splitLargeFile(fullPath = fullPath,
numChunks = 3)
self.assertEquals(len(self.fileChunks), 3)
def test_get_file_size(self):
"""
Test retrieving local file sizes.
"""
# @REVIEWED
fullPath = '{}/{}'.format(self.exportTestDataPath,
self.compressedTestFilename)
fSize = self.fileUtil.fileSize(fullPath)
self.logger.log('size: {}'.format(fSize))
self.assertEqual(fSize, 12279, 'File size is correct.')
def test_upload_export_files_list(self):
"""
TBW
"""
self.exporter.sendDownloadableFiles()
def test_checksum_after_upload(self):
pass
def test_dump_exclusions_dictionary(self):
"""
Verify the exclusions dictionary by its type.
:return:
"""
# @REVIEWED
exclusions = self.exporter.dumpExclusionsDictionary()
if exclusions:
self.assertEquals(type({}), type(exclusions))
def test_plaintext_downloadable_files(self):
print self.exporter.plaintextListOfDownloadableFiles()
def test_move_to_final(self):
"""
Test moving a file to the final destination path.
"""
# @REVIEWED
self.logger.log('Testing moving to final path {}.'.format(
self.configer.configOptionValue('Export', 'db_export_final_path')))
origCompressedFile = '{}/{}'.format(
self.configer.configOptionValue('Export', 'export_test_data_path'),
self.compressedTestFilename)
newCompressedFile = '{}/{}'.format(
self.configer.configOptionValue('Export', 'export_test_data_path'),
'temp_test_file')
shutil.copyfile(origCompressedFile, newCompressedFile)
self.exporter.moveToFinalPath(compressedFullPath = newCompressedFile)
self.assertTrue(os.path.isfile('{}/{}'.format(
self.configer.configOptionValue('Export', 'db_export_final_path'),
'temp_test_file')))
# Remove the test file.
os.remove('{}/{}'.format(
self.configer.configOptionValue('Export', 'db_export_final_path'),
'temp_test_file'))
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 test_metadata_of_file_id(self):
"""
Test getting the metadata for a file ID.
"""
# @REVIEWED
self.upload_test_data_to_cloud()
self.logger.log('metadata: {}'.format(
self.exporter.metadataOfFileID(self.testDataFileID)))
self.assertTrue(re.match(r'[0-9A-Za-z]+', self.testDataFileID))
def test_filename_for_file_id(self):
"""
Test returning a file name given a file ID.
"""
# @REVIEWED
self.upload_test_data_to_cloud()
self.assertEquals(
self.exporter.filenameForFileID(fileID = self.testDataFileID),
self.compressedTestFilename)
def tearDown(self):
"""
Delete all test items.
"""
REMOVE_TEMPORARY_FILES = True
if REMOVE_TEMPORARY_FILES:
try:
os.remove(os.path.join(os.getcwd(), self.testDir,
self.uncompressedTestFilename))
os.remove(os.path.join(os.getcwd(), self.testDir,
self.compressedTestFilename))
except OSError as detail:
self.logger.log(
'Exception while removing temporary files: {}'.format(
detail), 'SILENT')
try:
os.remove(os.path.join(os.getcwd(), self.testDir,
self.compressedTestFilename))
except OSError as detail:
self.logger.log(
'Exception while removing temporary files: {}'.format(
detail), 'SILENT')
try:
for f in self.fileChunks:
os.remove(f)
except OSError as detail:
self.logger.log(
'Exception while removing temporary files: {}'.format(
detail), 'DEBUG')
try:
os.rmdir(self.testDir)
except OSError as detail:
self.logger.log(
'Exception while removing directory: {}'.format(detail),
'ERROR')
deleteSuccessful = True
# Keep deleting from the cloud until there is no more to delete.
while deleteSuccessful:
try:
fileIDToDelete = self.exporter.fileIDForFileName(
self.compressedTestFilename)
self.logger.log("file ID to delete: {}".format(fileIDToDelete),
'DEBUG')
self.exporter.driveService.files().delete(
fileId = '%s' % fileIDToDelete).execute()
except (TypeError, http.HttpError) as e:
self.logger.log('Delete not successful: {}'.format(e), 'SILENT')
break
import fnmatch
import sys
import re
import argparse
import time
import multiprocessing
from msg_configer import MSGConfiger
from msg_notifier import MSGNotifier
from meco_plotting import MECOPlotting
from insertSingleMECOEnergyDataFile import Inserter
from sek.logger import SEKLogger
xmlGzCount = 0
xmlCount = 0
configer = MSGConfiger()
logger = SEKLogger(__name__, 'info')
binPath = MSGConfiger.configOptionValue(configer, "Executable Paths",
"msg_bin_path")
COMMAND_LINE_ARGS = None
global msgBody
notifier = MSGNotifier()
def processCommandLineArguments():
"""
Generate command-line arguments. Load them into global variable
COMMAND_LINE_ARGS.
"""
global COMMAND_LINE_ARGS
class MSGDBConnector(object):
"""
Make and manage a connection to an MSG database.
Usage:
conn = MSGDBConnector().connectDB()
cursor = conn.cursor()
"""
def __init__(self, testing=False, logLevel='silent'):
"""
Constructor.
:param testing: Boolean indicating if Testing Mode is on. When
testing mode is on, a connection to the testing database will be made
instead of the production database. This is useful for unit testing.
:param logLevel
"""
self.logger = SEKLogger(__name__, logLevel)
if testing:
self.logger.log("Testing Mode is ON.")
self.configer = MSGConfiger()
self.dbPassword = self.configer.configOptionValue(
"Database", 'db_password')
self.dbHost = self.configer.configOptionValue("Database", 'db_host')
self.dbPort = self.configer.configOptionValue("Database", 'db_port')
if testing:
self.dbName = self.configer.configOptionValue(
"Database", 'testing_db_name')
else:
self.dbName = self.configer.configOptionValue(
"Database", 'db_name')
self.logger.log("Instantiating DB connector with database {}.".format(
self.dbName))
self.dbUsername = self.configer.configOptionValue(
"Database", 'db_username')
self.conn = self.connectDB()
if not self.conn:
raise Exception('DB connection not available.')
try:
self.dictCur = self.conn.cursor(
cursor_factory=psycopg2.extras.DictCursor)
except AttributeError as error:
self.logger.log('Error while getting DictCursor: {}'.format(error))
def connectDB(self):
"""
Make the DB connection.
:returns: DB connection object if successful, otherwise None.
"""
# @todo Make this method private since the init makes the connection.
conn = None
try:
conn = psycopg2.connect(
"dbname='{0}' user='******' host='{2}' port='{3}' password='******'".format(self.dbName, self.dbUsername, self.dbHost,
self.dbPort, self.dbPassword))
except:
self.logger.log("Failed to connect to the database.", 'error')
raise Exception("DB connection failed.")
self.logger.log("Opened DB connection to database {}.".format(
self.dbName))
return conn
def closeDB(self, conn):
"""
Close a database connection.
"""
self.logger.log("Closing database {}.".format(self.dbName))
conn.close()
def __del__(self):
"""
Destructor.
Close the database connection.
"""
import sys
self.logger.log("Closing the DB connection to database {}.".format(
self.dbName))
self.conn.close()
class MSGDataAggregator(object):
"""
Use for continuous data aggregation of diverse data types relevant to the
Maui Smart Grid project.
Four data types are supported:
1. Irradiance
2. Temperature/Humidity (weather)
3. Circuit
4. eGauge
The general data form conforms to
1. timestamp, subkey_id, val1, val2, val3, ...
2. timestamp, val1, val2, val3, ...
Case (2) is handled within the same space as (1) by testing for the
existence of subkeys.
Current aggregation consists of averaging over **15-min intervals**.
Aggregation is performed in-memory and saved to the DB. The time range is
delimited by start date and end date where the values are included in the
range. The timestamps for aggregation intervals are the last timestamp in a
respective series.
* Aggregation subkeys are values such as eGauge IDs or circuit numbers.
Aggregation is being implemented externally for performance and flexibility
advantages over alternative approaches such as creating a view. It may be
rolled into an internal function at future time if that proves to be
beneficial.
Usage:
from msg_data_aggregator import MSGDataAggregator
aggregator = MSGDataAggregator()
API:
aggregateAllData(dataType = dataType)
aggregateNewData(dataType = dataType)
"""
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")
def existingIntervals(self, aggDataType="", timeColumnName=""):
"""
Retrieve the existing aggregation intervals for the given data type.
:param aggDataType: string
:param timeColumnName: string
:return: List of intervals.
"""
return [
x[0]
for x in self.rows(
"""SELECT {0} from \"{1}\" ORDER BY {2}""".format(
timeColumnName, self.tables[aggDataType], timeColumnName
)
)
]
def unaggregatedIntervalCount(self, dataType="", aggDataType="", timeColumnName="", idColumnName=""):
"""
Return count of unaggregated intervals for a given data type.
:param dataType:
:param aggDataType:
:param timeColumnName:
:param idColumnName:
:return: int
"""
return len(self.unaggregatedEndpoints(dataType, aggDataType, timeColumnName, idColumnName))
def lastAggregationEndpoint(self, aggDataType="", timeColumnName=""):
"""
Last aggregation endpoint for a given datatype.
:param dataType:
:param timeColumnName:
:return:
"""
return self.existingIntervals(aggDataType=aggDataType, timeColumnName=timeColumnName)[-1]
def unaggregatedEndpoints(self, dataType="", aggDataType="", timeColumnName="", idColumnName=""):
"""
Sorted (ascending) endpoints and their IDs, if available,
for unaggregated intervals since the last aggregation endpoint for a
given data type.
This has a problem where an endpoint at 23:45:04 will be returned as
23:45:00. This makes the return value incorrect for raw data types
having readings at sub-minute intervals such as data for circuit,
irradiance and weather. This condition does not affect correct
aggregation. Only the definition of the return value is wrong.
:param dataType: string
:param aggDataType: string
:param timeColumnName: string
:param idColName: string
:return: list of datetimes.
"""
if idColumnName != "":
# Key:
# 0: raw
# 1: agg
# 2: time col
# 3: id col
# 4: last aggregated time
sql = (
'SELECT "{0}".{2}, "{0}".{3} FROM "{0}" LEFT JOIN "{1}" ON '
'"{0}".{2} = "{1}".{2} AND "{0}".{3} = "{1}".{3} WHERE "{'
'1}".{2} IS NULL AND "{0}".{2} > \'{4}\' ORDER BY {2} ASC, '
"{3} ASC"
)
self.logger.log("last agg endpoint: {}".format(self.lastAggregationEndpoint(aggDataType, timeColumnName)))
# The id column value is available in the tuple returned by
# groupby but is not being used here.
# @todo Exclude last endpoint if it is equal to the last
# aggregation endpoint.
#
# The minute position filtering may be including the last
# endpoint incorrectly because there are readings occurring
# within the same minute as the final endpoint, e.g. 23:45:04,
# 23:45:08, etc.
#
# This is not a problem with eGuage data due reading intervals
# being every minute and zero seconds.
return map(
lambda x: datetime(x[0], x[1], x[2], x[3], x[4], 0),
[
k
for k, v in groupby(
map(
lambda y: y[0].timetuple()[0:5],
filter(
lambda x: x[0].timetuple()[MINUTE_POSITION] % INTERVAL_DURATION == 0,
[
(x[0], x[1])
for x in self.rows(
sql.format(
self.tables[dataType],
self.tables[aggDataType],
timeColumnName,
idColumnName,
self.lastAggregationEndpoint(aggDataType, timeColumnName),
)
)
],
),
)
)
],
)
else:
# Key:
# 0: raw
# 1: agg
# 2: time col
# 3: last aggregated time
sql = (
'SELECT "{0}".{2} FROM "{0}" LEFT JOIN "{1}" ON "{0}".{2}='
'"{1}".{2} WHERE "{1}".{2} IS NULL AND "{0}".{2} > \'{3}\' '
"ORDER BY {2} ASC"
)
self.logger.log("last agg endpoint: {}".format(self.lastAggregationEndpoint(aggDataType, timeColumnName)))
return map(
lambda x: datetime(x[0], x[1], x[2], x[3], x[4], 0),
[
k
for k, v in groupby(
map(
lambda y: y.timetuple()[0:5],
filter(
lambda x: x.timetuple()[MINUTE_POSITION] % INTERVAL_DURATION == 0,
[
(x[0])
for x in self.rows(
sql.format(
self.tables[dataType],
self.tables[aggDataType],
timeColumnName,
self.lastAggregationEndpoint(aggDataType, timeColumnName),
)
)
],
),
)
)
],
)
def intervalCrossed(self, minute=None, subkey=None):
"""
Determine interval crossing. Intervals are at 0, 15, 45, 60 min.
The interval size is determined by MECO source data.
:param minute: The integer value of the minute.
:param subkey: The name for the subkey used for aggregation.
:returns: True if an interval was crossed, False otherwise.
"""
if not minute and minute != 0:
raise Exception("Minute not defined.")
intervalSize = 15
first = 0
last = 60
if subkey is not None:
if (
minute >= self.nextMinuteCrossing[subkey]
and minute <= last
and self.nextMinuteCrossing[subkey] != first
):
self.nextMinuteCrossing[subkey] += intervalSize
if self.nextMinuteCrossing[subkey] >= last:
self.nextMinuteCrossing[subkey] = first
self.logger.log("minute crossed at #1.", "debug")
return True
elif self.nextMinuteCrossing[subkey] == first and minute >= first and minute <= intervalSize:
self.nextMinuteCrossing[subkey] = intervalSize
self.logger.log("minute crossed at #2.", "debug")
return True
return False
else:
if (
minute >= self.nextMinuteCrossingWithoutSubkeys
and minute <= last
and self.nextMinuteCrossingWithoutSubkeys != first
):
self.nextMinuteCrossingWithoutSubkeys += intervalSize
if self.nextMinuteCrossingWithoutSubkeys >= last:
self.nextMinuteCrossingWithoutSubkeys = first
self.logger.log("minute crossed at #3.", "debug")
return True
elif self.nextMinuteCrossingWithoutSubkeys == first and minute >= first and minute <= intervalSize:
self.nextMinuteCrossingWithoutSubkeys = intervalSize
self.logger.log("minute crossed at #4.", "debug")
return True
return False
def rows(self, sql):
"""
Rows from a SQL fetch.
:param sql: Command to be executed.
:returns: DB result set.
"""
self.logger.log("sql: {}".format(sql), "debug")
self.dbUtil.executeSQL(self.cursor, sql)
return self.cursor.fetchall()
def rawData(self, dataType="", orderBy=None, timestampCol="", startDate="", endDate=""):
"""
Raw data to be aggregated.
:param dataType: string
:param orderBy: list
:param timestampCol: string
:param startDate: string
:param endDate: string
:returns: DB rows.
"""
# @todo Validate args.
orderBy = filter(None, orderBy)
return self.rows(
"""SELECT {} FROM "{}" WHERE {} BETWEEN '{}' AND
'{}' ORDER BY {}""".format(
self.columns[dataType], self.tables[dataType], timestampCol, startDate, endDate, ",".join(orderBy)
)
)
def subkeys(self, dataType="", timestampCol="", subkeyCol="", startDate="", endDate=""):
"""
The distinct subkeys for a given data type within a time range.
Subkeys are fields such as egauge_id in eGauge data or sensor_id in
irradiance data.
:param dataType: string
:param timestampCol: string
:param subkeyCol: string
:param startDate: string
:param endDate: string
:returns: List of subkeys
"""
return [
sk[0]
for sk in self.rows(
"""SELECT DISTINCT({}) FROM "{}"
WHERE {} BETWEEN '{}' AND '{}'
ORDER BY {}""".format(
subkeyCol, self.tables[dataType], timestampCol, startDate, endDate, subkeyCol
)
)
]
def insertAggregatedData(self, agg=None):
"""
:param agg: MSGAggregatedData
:return: None
"""
if not agg.columns:
raise Exception("agg columns not defined.")
if not agg.data:
raise Exception("agg data not defined.")
self.logger.log("agg data: {}".format(agg.data))
self.logger.log("agg data type: {}".format(type(agg.data)))
def __insertData(values=""):
"""
Perform insert of data to the database using the given values.
:param values: String containing values to be inserted.
:return Nothing.
"""
sql = 'INSERT INTO "{0}" ({1}) VALUES( {2})'.format(
self.tables[agg.aggregationType], ",".join(agg.columns), values
)
self.logger.log("sql: {}".format(sql), "debug")
success = self.dbUtil.executeSQL(self.cursor, sql, exitOnFail=self.exitOnError)
# Used for a special case where data is reloaded.
if self.commitOnEveryInsert:
self.conn.commit()
if not success and self.exitOnError:
raise Exception("Failure during aggregated data insert.")
for row in agg.data:
if type(row) == type({}):
# self.logger.log('row=%s' % row, 'debug')
# self.logger.log('row type: %s' % type(row))
for key in row.keys():
values = ""
valCnt = 0
for val in row[key]:
if val == "NULL":
values += val
elif type(val) == type(""):
values += "'" + val.strip() + "'"
elif isinstance(val, datetime):
values += "'" + val.isoformat() + "'"
elif type(val) == type(0):
values += str(val)
elif type(val) == type(0.0):
values += str(val)
else:
values += val
if valCnt < len(agg.columns) - 1:
values += ","
valCnt += 1
__insertData(values=values)
elif type(row) == type([]):
values = ""
valCnt = 0
for val in row:
if val == "NULL":
values += val
elif type(val) == type(""):
values += "'" + val.strip() + "'"
elif isinstance(val, datetime):
values += "'" + val.isoformat() + "'"
elif type(val) == type(0):
values += str(val)
elif type(val) == type(0.0):
values += str(val)
else:
values += val
if valCnt < len(agg.columns) - 1:
values += ","
valCnt += 1
__insertData(values=values)
else:
self.logger.log("row = {}".format(row), "error")
raise Exception("Row type not matched.")
# End for row.
self.conn.commit()
def intervalAverages(self, sums, cnts, timestamp, timestampIndex, subkeyIndex=None, subkey=None):
"""
Aggregates all data for the current interval for the given subkey.
For the case where there are no subkeys, subkeyIndex and subkey
should be None.
:param sums: list
:param cnts: list
:param timestamp: datetime
:param timestampIndex: int
:param subkeyIndex: int
:param subkey: string
:returns: Averaged data as a dict with form {subkey:data}
"""
if subkey is not None:
myAvgs = {}
reportedAgg = False
myAvgs[subkey] = []
sumIndex = 0
self.logger.log("key: {}".format(subkey), "debug")
# Iterate over sums.
for s in sums[subkey]:
if sumIndex == timestampIndex:
myAvgs[subkey].append(timestamp)
elif sumIndex == subkeyIndex:
myAvgs[subkey].append(subkey)
else:
if cnts[subkey][sumIndex] != 0:
if not reportedAgg:
self.logger.log("Aggregating {} rows of data.".format(cnts[subkey][sumIndex]), "debug")
reportedAgg = True
myAvgs[subkey].append(s / cnts[subkey][sumIndex])
else:
myAvgs[subkey].append("NULL")
sumIndex += 1
return myAvgs
else:
myAvgs = []
reportedAgg = False
sumIndex = 0
for s in sums:
if sumIndex == timestampIndex:
myAvgs.append(timestamp)
else:
if cnts[sumIndex] != 0:
if not reportedAgg:
self.logger.log("Aggregating {} rows of data.".format(cnts[sumIndex]), "debug")
reportedAgg = True
myAvgs.append(s / cnts[sumIndex])
else:
myAvgs.append("NULL")
sumIndex += 1
return myAvgs
def dataParameters(self, dataType=""):
"""
Parameters for a given data type.
:param dataType: string
:return: (aggType, timeColName, subkeyColName)
"""
try:
assert len(self.dataParams[dataType]) == 3
return self.dataParams[dataType]
except:
self.logger.log("Unmatched data type {}.".format(dataType))
def aggregateAllData(self, dataType=""):
"""
Convenience method for aggregating all data for a given data type.
Data is inserted to individual aggregated data tables.
:param dataType: String in the list of raw data types.
:return: Nothing.
"""
(aggType, timeColName, subkeyColName) = self.dataParameters(dataType)
for start, end in self.monthStartsAndEnds(timeColumnName=timeColName, dataType=dataType):
self.logger.log("start, end: {}, {}".format(start, end))
aggData = self.aggregatedData(
dataType=dataType,
aggregationType=aggType,
timeColumnName=timeColName,
subkeyColumnName=subkeyColName,
startDate=start.strftime("%Y-%m-%d %H:%M:%S"),
endDate=end.strftime("%Y-%m-%d %H:%M:%S"),
)
self.insertAggregatedData(agg=aggData)
for row in aggData.data:
self.logger.log("aggData row: {}".format(row))
def aggregateNewData(self, dataType=""):
"""
Convenience method for aggregating new data.
:param dataType:
:return: dict of {dataType: count of aggregation endpoints}
"""
# The new aggregation starting point is equal to the last aggregation
# endpoint up to the last unaggregated endpoint.
(aggType, timeColName, subkeyColName) = self.dataParameters(dataType)
(end, start) = self.lastUnaggregatedAndAggregatedEndpoints(dataType).items()[0][1]
self.logger.log(
"datatype: {}; start, end: {}, {}; end type: {}".format(dataType, start, end, type(end)), "critical"
)
if type(end) == type(None):
# No available unaggregated endpoints results in an empty list
# for type egauge. The reason this does not work for other types is
# because the other types of fractional minute readings and the
# fractional minute readings are not being handled completely but
# this method is still capable of working without problem.
self.logger.log("Nothing to aggregate.")
return {dataType: 0}
if self.incrementEndpoint(start) >= end:
self.logger.log("Nothing to aggregate.")
return {dataType: 0}
aggData = self.aggregatedData(
dataType=dataType,
aggregationType=aggType,
timeColumnName=timeColName,
subkeyColumnName=subkeyColName,
startDate=self.incrementEndpoint(start).strftime("%Y-%m-%d %H:%M:%S"),
endDate=end.strftime("%Y-%m-%d %H:%M:%S"),
)
self.insertAggregatedData(agg=aggData)
for row in aggData.data:
self.logger.log("aggData row: {}".format(row))
self.logger.log("{} rows aggregated for {}.".format(len(aggData.data), dataType))
return {dataType: len(aggData.data)}
def incrementEndpoint(self, endpoint=None):
"""
Increment an endpoint by one interval where endpoints are the final
timestamp in an aggregation interval.
:param endpoint: the endpoint to be incremented.
:return: datetime object that is the given endpoint + a predefined
amount of minutes.
"""
plusOneInterval = relativedelta(minutes=15)
return endpoint + plusOneInterval
def lastUnaggregatedAndAggregatedEndpoints(self, dataType=""):
"""
Return the endpoints for the given data type in the form
{datatype: (last unaggregated endpoint, last aggregated endpoint)}.
:param dataType:
:return: dict with tuple.
"""
self.logger.log("datatype {}".format(dataType))
(aggType, timeColName, subkeyColName) = self.dataParameters(dataType)
self.logger.log("subkey colname {}".format(subkeyColName))
unAggregatedEndpoints = self.unaggregatedEndpoints(
dataType=dataType, aggDataType=aggType, timeColumnName=timeColName, idColumnName=subkeyColName
)
self.logger.log("unagg endpoints: {}".format(unAggregatedEndpoints))
return {
dataType: (
unAggregatedEndpoints[-1] if unAggregatedEndpoints != [] else None,
self.lastAggregationEndpoint(aggDataType=aggType, timeColumnName=timeColName),
)
}
def aggregatedVsNewData(self):
"""
Convenience method.
:return: dict of tuples containing {datatype:(last raw datetime,
last agg datetime)}
"""
return {
x.keys()[0]: (x.values()[0])
for x in map(self.lastUnaggregatedAndAggregatedEndpoints, [k for k in self.dataParams])
}
def monthStartsAndEnds(self, timeColumnName="", dataType=""):
"""
Return first date and last date for the given **raw** data type for each
month in the data's entire time range.
The end date is incremented by on aggregation period to account for
the data obtained at time 00:00.
:param timeColumnName: string
:param dataType: string
:return: List of tuples.
"""
self.logger.log("datatype {}".format(dataType), "debug")
(start, end) = self.rows(
"""SELECT MIN({}), MAX({}) FROM \"{}\"""".format(timeColumnName, timeColumnName, self.tables[dataType])
)[0]
self.logger.log("start {}".format(start))
self.logger.log("end {}".format(end))
# End time needs transforming in split dates to extend the end of the
# day to 23:59:59.
splitDates = self.timeUtil.splitDates(start, end)
startEndDatesTransform = []
i = 0
while i < len(splitDates):
startEndDatesTransform.append(
(
splitDates[i][0],
self.incrementEndpoint(
datetime(
splitDates[i][1].timetuple()[0],
splitDates[i][1].timetuple()[1],
splitDates[i][1].timetuple()[2],
23,
59,
59,
)
),
)
)
i += 1
return startEndDatesTransform
def aggregatedData(
self, dataType="", aggregationType="", timeColumnName="", subkeyColumnName="", startDate="", endDate=""
):
"""
***********************************************************************
Provide aggregated data.
***********************************************************************
Start and end dates are used to calculate interval crossings.
:param dataType: String
:param aggregationType: String
:param timeColumnName: String
:param subkeyColumnName: String
:param startDate: String
:param endDate: String
:returns: MSGAggregatedData
"""
aggData = []
ci = lambda col_name: self.columns[dataType].split(",").index(col_name)
rowCnt = 0
mySubkeys = []
if subkeyColumnName:
mySubkeys = self.subkeys(
dataType=dataType,
timestampCol=timeColumnName,
subkeyCol=subkeyColumnName,
startDate=startDate,
endDate=endDate,
)
self.logger.log("subkeys: {}".format(mySubkeys), "debug")
def __initSumAndCount(subkey=None, sums=None, cnts=None):
"""
Initialize the sum and cnt data structures.
:param subkey: string
:param sums: list | dict | None
:param cnts: list | dict | None
"""
if not sums and not cnts:
sums = {}
cnts = {}
if not mySubkeys:
sums = []
cnts = []
for i in range(len(self.columns[dataType].split(","))):
sums.append(0)
cnts.append(0)
else:
if not subkey:
for i in range(len(self.columns[dataType].split(","))):
for k in mySubkeys:
if k not in sums.keys():
sums[k] = []
cnts[k] = []
sums[k].append(0)
cnts[k].append(0)
else:
sums[subkey] = []
for i in range(len(self.columns[dataType].split(","))):
sums[subkey].append(0)
cnts[subkey] = []
for i in range(len(self.columns[dataType].split(","))):
cnts[subkey].append(0)
return (sums, cnts)
(sum, cnt) = __initSumAndCount()
def __initIntervalCrossings():
"""
Perform initialization of the interval crossings used to
determine when interval crossings occur.
:returns None
"""
subkeysToCheck = copy.copy(mySubkeys)
self.logger.log("subkeys to check: {}".format(subkeysToCheck), "debug")
if mySubkeys:
for row in self.rawData(
dataType=dataType,
orderBy=[timeColumnName, subkeyColumnName],
timestampCol=timeColumnName,
startDate=startDate,
endDate=endDate,
):
# @CRITICAL: Exit after every subkey has been visited.
# This scans the raw data until each subkey is encountered
# ONCE and then exits.
if subkeysToCheck != []:
if row[ci(subkeyColumnName)] in subkeysToCheck:
subkeysToCheck.remove(row[ci(subkeyColumnName)])
minute = row[ci(timeColumnName)].timetuple()[MINUTE_POSITION]
if minute <= 15:
self.nextMinuteCrossing[row[ci(subkeyColumnName)]] = 15
elif minute <= 30:
self.nextMinuteCrossing[row[ci(subkeyColumnName)]] = 30
elif minute <= 45:
self.nextMinuteCrossing[row[ci(subkeyColumnName)]] = 45
elif minute == 0 or minute <= 59:
self.nextMinuteCrossing[row[ci(subkeyColumnName)]] = 0
else:
raise Exception("Unable to determine next minute crossing")
self.logger.log(
"next min crossing for {} = {}".format(
row[ci(subkeyColumnName)], self.nextMinuteCrossing[row[ci(subkeyColumnName)]]
),
"debug",
)
else:
break
else:
# Non-subkey case e.g. weather data.
rowCnt = 0
# @todo Optimize by querying only the first row.
for row in self.rawData(
dataType=dataType,
orderBy=[timeColumnName],
timestampCol=timeColumnName,
startDate=startDate,
endDate=endDate,
):
minute = row[ci(timeColumnName)].timetuple()[MINUTE_POSITION]
if minute <= 15:
self.nextMinuteCrossingWithoutSubkeys = 15
elif minute <= 30:
self.nextMinuteCrossingWithoutSubkeys = 30
elif minute <= 45:
self.nextMinuteCrossingWithoutSubkeys = 45
elif minute == 0 or minute <= 59:
self.nextMinuteCrossingWithoutSubkeys = 0
else:
raise Exception("Unable to determine next minute crossing")
self.logger.log("next min crossing = {}".format(self.nextMinuteCrossingWithoutSubkeys), "debug")
rowCnt += 1
if rowCnt > 0:
break
__initIntervalCrossings()
for row in self.rawData(
dataType=dataType,
orderBy=[timeColumnName, subkeyColumnName],
timestampCol=timeColumnName,
startDate=startDate,
endDate=endDate,
):
if mySubkeys:
for col in self.columns[dataType].split(","):
if self.mathUtil.isNumber(row[ci(col)]) and ci(col) != ci(subkeyColumnName):
sum[row[ci(subkeyColumnName)]][ci(col)] += row[ci(col)]
cnt[row[ci(subkeyColumnName)]][ci(col)] += 1
minute = row[ci(timeColumnName)].timetuple()[MINUTE_POSITION]
if self.intervalCrossed(minute=minute, subkey=row[ci(subkeyColumnName)]):
minuteCrossed = minute
# Perform aggregation on all of the previous data including
# the current data for the current subkey.
self.logger.log("key: {}".format(row[ci(subkeyColumnName)]), "debug")
aggData += [
self.intervalAverages(
sum,
cnt,
row[ci(timeColumnName)],
ci(timeColumnName),
ci(subkeyColumnName),
row[ci(subkeyColumnName)],
)
]
self.logger.log("minute crossed {}".format(minuteCrossed), "DEBUG")
# Init current sum and cnt for subkey that has a completed
# interval.
(sum, cnt) = __initSumAndCount(subkey=row[ci(subkeyColumnName)], sums=sum, cnts=cnt)
else:
for col in self.columns[dataType].split(","):
if self.mathUtil.isNumber(row[ci(col)]):
sum[ci(col)] += row[ci(col)]
cnt[ci(col)] += 1
minute = row[ci(timeColumnName)].timetuple()[MINUTE_POSITION]
if self.intervalCrossed(minute=minute):
aggData += [self.intervalAverages(sum, cnt, row[ci(timeColumnName)], ci(timeColumnName))]
(sum, cnt) = __initSumAndCount(subkey=None, sums=sum, cnts=cnt)
rowCnt += 1
self.logger.log("aggdata = {}".format(aggData), "debug")
return MSGAggregatedData(
aggregationType=aggregationType, columns=self.columns[dataType].split(","), data=aggData
)
class MSGDBConnector(object):
"""
Make and manage a connection to an MSG database.
Usage:
conn = MSGDBConnector().connectDB()
cursor = conn.cursor()
"""
def __init__(self, testing = False, logLevel = 'silent'):
"""
Constructor.
:param testing: Boolean indicating if Testing Mode is on. When
testing mode is on, a connection to the testing database will be made
instead of the production database. This is useful for unit testing.
:param logLevel
"""
self.logger = SEKLogger(__name__, logLevel)
if testing:
self.logger.log("Testing Mode is ON.")
self.configer = MSGConfiger()
self.dbPassword = self.configer.configOptionValue("Database",
'db_password')
self.dbHost = self.configer.configOptionValue("Database", 'db_host')
self.dbPort = self.configer.configOptionValue("Database", 'db_port')
if testing:
self.dbName = self.configer.configOptionValue("Database",
'testing_db_name')
else:
self.dbName = self.configer.configOptionValue("Database", 'db_name')
self.logger.log(
"Instantiating DB connector with database {}.".format(self.dbName))
self.dbUsername = self.configer.configOptionValue("Database",
'db_username')
self.conn = self.connectDB()
if not self.conn:
raise Exception('DB connection not available.')
try:
self.dictCur = self.conn.cursor(
cursor_factory = psycopg2.extras.DictCursor)
except AttributeError as error:
self.logger.log('Error while getting DictCursor: {}'.format(error))
def connectDB(self):
"""
Make the DB connection.
:returns: DB connection object if successful, otherwise None.
"""
# @todo Make this method private since the init makes the connection.
conn = None
try:
conn = psycopg2.connect(
"dbname='{0}' user='******' host='{2}' port='{3}' password='******'".format(self.dbName, self.dbUsername, self.dbHost,
self.dbPort, self.dbPassword))
except:
self.logger.log("Failed to connect to the database.", 'error')
raise Exception("DB connection failed.")
self.logger.log(
"Opened DB connection to database {}.".format(self.dbName))
return conn
def closeDB(self, conn):
"""
Close a database connection.
"""
self.logger.log("Closing database {}.".format(self.dbName))
conn.close()
def __del__(self):
"""
Destructor.
Close the database connection.
"""
import sys
self.logger.log(
"Closing the DB connection to database {}.".format(self.dbName))
self.conn.close()
dbConnector = MSGDBConnector()
cursor = dbConnector.conn.cursor()
weatherUtil = MSGWeatherDataUtil()
timeUtil = MSGTimeUtil()
msg = "Downloading NOAA weather data (%s)." % timeUtil.conciseNow()
print msg
MSG_BODY = "%s\n" % msg
msg = "Last loaded date is %s." % weatherUtil.datePart(datetime=weatherUtil.getLastDateLoaded(cursor))
print msg
MSG_BODY += "%s\n" % msg
retriever = MSGWeatherDataRetriever()
configer = MSGConfiger()
WEATHER_DATA_PATH = configer.configOptionValue("Weather Data", "weather_data_path")
msg = "Using URL %s." % weatherDataURL
print msg
MSG_BODY += "%s\n" % msg
msg = "Using pattern %s." % weatherDataPattern
print msg
MSG_BODY += "%s\n" % msg
global logger
# @todo verify this usage
logger = SEKLogger(__name__)
class MSGDataAggregator(object):
"""
Use for continuous data aggregation of diverse data types relevant to the
Maui Smart Grid project.
Four data types are supported:
1. Irradiance
2. Temperature/Humidity (weather)
3. Circuit
4. eGauge
The general data form conforms to
1. timestamp, subkey_id, val1, val2, val3, ...
2. timestamp, val1, val2, val3, ...
Case (2) is handled within the same space as (1) by testing for the
existence of subkeys.
Current aggregation consists of averaging over **15-min intervals**.
Aggregation is performed in-memory and saved to the DB. The time range is
delimited by start date and end date where the values are included in the
range. The timestamps for aggregation intervals are the last timestamp in a
respective series.
* Aggregation subkeys are values such as eGauge IDs or circuit numbers.
Aggregation is being implemented externally for performance and flexibility
advantages over alternative approaches such as creating a view. It may be
rolled into an internal function at future time if that proves to be
beneficial.
Usage:
from msg_data_aggregator import MSGDataAggregator
aggregator = MSGDataAggregator()
API:
aggregateAllData(dataType = dataType)
aggregateNewData(dataType = dataType)
"""
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')
def existingIntervals(self, aggDataType='', timeColumnName=''):
"""
Retrieve the existing aggregation intervals for the given data type.
:param aggDataType: string
:param timeColumnName: string
:return: List of intervals.
"""
return [
x[0] for x in self.rows(
"""SELECT {0} from \"{1}\" ORDER BY {2}""".format(
timeColumnName, self.tables[aggDataType], timeColumnName))
]
def unaggregatedIntervalCount(self,
dataType='',
aggDataType='',
timeColumnName='',
idColumnName=''):
"""
Return count of unaggregated intervals for a given data type.
:param dataType:
:param aggDataType:
:param timeColumnName:
:param idColumnName:
:return: int
"""
return len(
self.unaggregatedEndpoints(dataType, aggDataType, timeColumnName,
idColumnName))
def lastAggregationEndpoint(self, aggDataType='', timeColumnName=''):
"""
Last aggregation endpoint for a given datatype.
:param dataType:
:param timeColumnName:
:return:
"""
return self.existingIntervals(aggDataType=aggDataType,
timeColumnName=timeColumnName)[-1]
def unaggregatedEndpoints(self,
dataType='',
aggDataType='',
timeColumnName='',
idColumnName=''):
"""
Sorted (ascending) endpoints and their IDs, if available,
for unaggregated intervals since the last aggregation endpoint for a
given data type.
This has a problem where an endpoint at 23:45:04 will be returned as
23:45:00. This makes the return value incorrect for raw data types
having readings at sub-minute intervals such as data for circuit,
irradiance and weather. This condition does not affect correct
aggregation. Only the definition of the return value is wrong.
:param dataType: string
:param aggDataType: string
:param timeColumnName: string
:param idColName: string
:return: list of datetimes.
"""
if idColumnName != '':
# Key:
# 0: raw
# 1: agg
# 2: time col
# 3: id col
# 4: last aggregated time
sql = 'SELECT "{0}".{2}, "{0}".{3} FROM "{0}" LEFT JOIN "{1}" ON ' \
'"{0}".{2} = "{1}".{2} AND "{0}".{3} = "{1}".{3} WHERE "{' \
'1}".{2} IS NULL AND "{0}".{2} > \'{4}\' ORDER BY {2} ASC, ' \
'{3} ASC'
self.logger.log('last agg endpoint: {}'.format(
self.lastAggregationEndpoint(aggDataType, timeColumnName)))
# The id column value is available in the tuple returned by
# groupby but is not being used here.
# @todo Exclude last endpoint if it is equal to the last
# aggregation endpoint.
#
# The minute position filtering may be including the last
# endpoint incorrectly because there are readings occurring
# within the same minute as the final endpoint, e.g. 23:45:04,
# 23:45:08, etc.
#
# This is not a problem with eGuage data due reading intervals
# being every minute and zero seconds.
return map(lambda x: datetime(x[0], x[1], x[2], x[3], x[4], 0), [
k for k, v in groupby(
map(
lambda y: y[0].timetuple()[0:5],
filter(
lambda x: x[0].timetuple()[MINUTE_POSITION] %
INTERVAL_DURATION == 0, [(
x[0], x[1]) for x in self.rows(
sql.format(
self.tables[dataType],
self.tables[aggDataType],
timeColumnName, idColumnName,
self.lastAggregationEndpoint(
aggDataType, timeColumnName)))])))
])
else:
# Key:
# 0: raw
# 1: agg
# 2: time col
# 3: last aggregated time
sql = 'SELECT "{0}".{2} FROM "{0}" LEFT JOIN "{1}" ON "{0}".{2}=' \
'"{1}".{2} WHERE "{1}".{2} IS NULL AND "{0}".{2} > \'{3}\' ' \
'ORDER BY {2} ASC'
self.logger.log('last agg endpoint: {}'.format(
self.lastAggregationEndpoint(aggDataType, timeColumnName)))
return map(lambda x: datetime(x[0], x[1], x[2], x[3], x[4], 0), [
k for k, v in groupby(
map(
lambda y: y.timetuple()[0:5],
filter(
lambda x: x.timetuple()[MINUTE_POSITION] %
INTERVAL_DURATION == 0, [(x[0]) for x in self.rows(
sql.format(
self.tables[dataType],
self.tables[aggDataType], timeColumnName,
self.lastAggregationEndpoint(
aggDataType, timeColumnName)))])))
])
def intervalCrossed(self, minute=None, subkey=None):
"""
Determine interval crossing. Intervals are at 0, 15, 45, 60 min.
The interval size is determined by MECO source data.
:param minute: The integer value of the minute.
:param subkey: The name for the subkey used for aggregation.
:returns: True if an interval was crossed, False otherwise.
"""
if not minute and minute != 0:
raise Exception('Minute not defined.')
intervalSize = 15
first = 0
last = 60
if subkey is not None:
if minute >= self.nextMinuteCrossing[subkey] and minute <= last \
and \
self.nextMinuteCrossing[subkey] != first:
self.nextMinuteCrossing[subkey] += intervalSize
if self.nextMinuteCrossing[subkey] >= last:
self.nextMinuteCrossing[subkey] = first
self.logger.log('minute crossed at #1.', 'debug')
return True
elif self.nextMinuteCrossing[
subkey] == first and minute >= first and minute <= intervalSize:
self.nextMinuteCrossing[subkey] = intervalSize
self.logger.log('minute crossed at #2.', 'debug')
return True
return False
else:
if minute >= self.nextMinuteCrossingWithoutSubkeys and minute <= \
last and self.nextMinuteCrossingWithoutSubkeys != first:
self.nextMinuteCrossingWithoutSubkeys += intervalSize
if self.nextMinuteCrossingWithoutSubkeys >= last:
self.nextMinuteCrossingWithoutSubkeys = first
self.logger.log('minute crossed at #3.', 'debug')
return True
elif self.nextMinuteCrossingWithoutSubkeys == first and minute >=\
first and minute <= intervalSize:
self.nextMinuteCrossingWithoutSubkeys = intervalSize
self.logger.log('minute crossed at #4.', 'debug')
return True
return False
def rows(self, sql):
"""
Rows from a SQL fetch.
:param sql: Command to be executed.
:returns: DB result set.
"""
self.logger.log('sql: {}'.format(sql), 'debug')
self.dbUtil.executeSQL(self.cursor, sql)
return self.cursor.fetchall()
def rawData(self,
dataType='',
orderBy=None,
timestampCol='',
startDate='',
endDate=''):
"""
Raw data to be aggregated.
:param dataType: string
:param orderBy: list
:param timestampCol: string
:param startDate: string
:param endDate: string
:returns: DB rows.
"""
# @todo Validate args.
orderBy = filter(None, orderBy)
return self.rows("""SELECT {} FROM "{}" WHERE {} BETWEEN '{}' AND
'{}' ORDER BY {}""".format(self.columns[dataType],
self.tables[dataType], timestampCol,
startDate, endDate, ','.join(orderBy)))
def subkeys(self,
dataType='',
timestampCol='',
subkeyCol='',
startDate='',
endDate=''):
"""
The distinct subkeys for a given data type within a time range.
Subkeys are fields such as egauge_id in eGauge data or sensor_id in
irradiance data.
:param dataType: string
:param timestampCol: string
:param subkeyCol: string
:param startDate: string
:param endDate: string
:returns: List of subkeys
"""
return [
sk[0] for sk in self.rows("""SELECT DISTINCT({}) FROM "{}"
WHERE {} BETWEEN '{}' AND '{}'
ORDER BY {}""".format(subkeyCol, self.tables[dataType],
timestampCol, startDate, endDate, subkeyCol))
]
def insertAggregatedData(self, agg=None):
"""
:param agg: MSGAggregatedData
:return: None
"""
if not agg.columns:
raise Exception('agg columns not defined.')
if not agg.data:
raise Exception('agg data not defined.')
self.logger.log('agg data: {}'.format(agg.data))
self.logger.log('agg data type: {}'.format(type(agg.data)))
def __insertData(values=''):
"""
Perform insert of data to the database using the given values.
:param values: String containing values to be inserted.
:return Nothing.
"""
sql = 'INSERT INTO "{0}" ({1}) VALUES( {2})'.format(
self.tables[agg.aggregationType], ','.join(agg.columns),
values)
self.logger.log('sql: {}'.format(sql), 'debug')
success = self.dbUtil.executeSQL(self.cursor,
sql,
exitOnFail=self.exitOnError)
# Used for a special case where data is reloaded.
if self.commitOnEveryInsert:
self.conn.commit()
if not success and self.exitOnError:
raise Exception('Failure during aggregated data insert.')
for row in agg.data:
if type(row) == type({}):
# self.logger.log('row=%s' % row, 'debug')
# self.logger.log('row type: %s' % type(row))
for key in row.keys():
values = ''
valCnt = 0
for val in row[key]:
if val == 'NULL':
values += val
elif type(val) == type(''):
values += "'" + val.strip() + "'"
elif isinstance(val, datetime):
values += "'" + val.isoformat() + "'"
elif type(val) == type(0):
values += str(val)
elif type(val) == type(0.0):
values += str(val)
else:
values += val
if valCnt < len(agg.columns) - 1:
values += ","
valCnt += 1
__insertData(values=values)
elif type(row) == type([]):
values = ''
valCnt = 0
for val in row:
if val == 'NULL':
values += val
elif type(val) == type(''):
values += "'" + val.strip() + "'"
elif isinstance(val, datetime):
values += "'" + val.isoformat() + "'"
elif type(val) == type(0):
values += str(val)
elif type(val) == type(0.0):
values += str(val)
else:
values += val
if valCnt < len(agg.columns) - 1:
values += ","
valCnt += 1
__insertData(values=values)
else:
self.logger.log('row = {}'.format(row), 'error')
raise Exception('Row type not matched.')
# End for row.
self.conn.commit()
def intervalAverages(self,
sums,
cnts,
timestamp,
timestampIndex,
subkeyIndex=None,
subkey=None):
"""
Aggregates all data for the current interval for the given subkey.
For the case where there are no subkeys, subkeyIndex and subkey
should be None.
:param sums: list
:param cnts: list
:param timestamp: datetime
:param timestampIndex: int
:param subkeyIndex: int
:param subkey: string
:returns: Averaged data as a dict with form {subkey:data}
"""
if subkey is not None:
myAvgs = {}
reportedAgg = False
myAvgs[subkey] = []
sumIndex = 0
self.logger.log('key: {}'.format(subkey), 'debug')
# Iterate over sums.
for s in sums[subkey]:
if sumIndex == timestampIndex:
myAvgs[subkey].append(timestamp)
elif sumIndex == subkeyIndex:
myAvgs[subkey].append(subkey)
else:
if cnts[subkey][sumIndex] != 0:
if not reportedAgg:
self.logger.log(
'Aggregating {} rows of data.'.format(
cnts[subkey][sumIndex]), 'debug')
reportedAgg = True
myAvgs[subkey].append(s / cnts[subkey][sumIndex])
else:
myAvgs[subkey].append('NULL')
sumIndex += 1
return myAvgs
else:
myAvgs = []
reportedAgg = False
sumIndex = 0
for s in sums:
if sumIndex == timestampIndex:
myAvgs.append(timestamp)
else:
if cnts[sumIndex] != 0:
if not reportedAgg:
self.logger.log(
'Aggregating {} rows of data.'.format(
cnts[sumIndex]), 'debug')
reportedAgg = True
myAvgs.append(s / cnts[sumIndex])
else:
myAvgs.append('NULL')
sumIndex += 1
return myAvgs
def dataParameters(self, dataType=''):
"""
Parameters for a given data type.
:param dataType: string
:return: (aggType, timeColName, subkeyColName)
"""
try:
assert len(self.dataParams[dataType]) == 3
return self.dataParams[dataType]
except:
self.logger.log('Unmatched data type {}.'.format(dataType))
def aggregateAllData(self, dataType=''):
"""
Convenience method for aggregating all data for a given data type.
Data is inserted to individual aggregated data tables.
:param dataType: String in the list of raw data types.
:return: Nothing.
"""
(aggType, timeColName, subkeyColName) = self.dataParameters(dataType)
for start, end in self.monthStartsAndEnds(timeColumnName=timeColName,
dataType=dataType):
self.logger.log('start, end: {}, {}'.format(start, end))
aggData = self.aggregatedData(
dataType=dataType,
aggregationType=aggType,
timeColumnName=timeColName,
subkeyColumnName=subkeyColName,
startDate=start.strftime('%Y-%m-%d %H:%M:%S'),
endDate=end.strftime('%Y-%m-%d %H:%M:%S'))
self.insertAggregatedData(agg=aggData)
for row in aggData.data:
self.logger.log('aggData row: {}'.format(row))
def aggregateNewData(self, dataType=''):
"""
Convenience method for aggregating new data.
:param dataType:
:return: dict of {dataType: count of aggregation endpoints}
"""
# The new aggregation starting point is equal to the last aggregation
# endpoint up to the last unaggregated endpoint.
(aggType, timeColName, subkeyColName) = self.dataParameters(dataType)
(end, start) = \
self.lastUnaggregatedAndAggregatedEndpoints(dataType).items()[0][1]
self.logger.log(
'datatype: {}; start, end: {}, {}; end type: {}'.format(
dataType, start, end, type(end)), 'critical')
if type(end) == type(None):
# No available unaggregated endpoints results in an empty list
# for type egauge. The reason this does not work for other types is
# because the other types of fractional minute readings and the
# fractional minute readings are not being handled completely but
# this method is still capable of working without problem.
self.logger.log('Nothing to aggregate.')
return {dataType: 0}
if self.incrementEndpoint(start) >= end:
self.logger.log('Nothing to aggregate.')
return {dataType: 0}
aggData = self.aggregatedData(
dataType=dataType,
aggregationType=aggType,
timeColumnName=timeColName,
subkeyColumnName=subkeyColName,
startDate=self.incrementEndpoint(start).strftime(
'%Y-%m-%d %H:%M:%S'),
endDate=end.strftime('%Y-%m-%d %H:%M:%S'))
self.insertAggregatedData(agg=aggData)
for row in aggData.data:
self.logger.log('aggData row: {}'.format(row))
self.logger.log('{} rows aggregated for {}.'.format(
len(aggData.data), dataType))
return {dataType: len(aggData.data)}
def incrementEndpoint(self, endpoint=None):
"""
Increment an endpoint by one interval where endpoints are the final
timestamp in an aggregation interval.
:param endpoint: the endpoint to be incremented.
:return: datetime object that is the given endpoint + a predefined
amount of minutes.
"""
plusOneInterval = relativedelta(minutes=15)
return endpoint + plusOneInterval
def lastUnaggregatedAndAggregatedEndpoints(self, dataType=''):
"""
Return the endpoints for the given data type in the form
{datatype: (last unaggregated endpoint, last aggregated endpoint)}.
:param dataType:
:return: dict with tuple.
"""
self.logger.log('datatype {}'.format(dataType))
(aggType, timeColName, subkeyColName) = self.dataParameters(dataType)
self.logger.log('subkey colname {}'.format(subkeyColName))
unAggregatedEndpoints = self.unaggregatedEndpoints(
dataType=dataType,
aggDataType=aggType,
timeColumnName=timeColName,
idColumnName=subkeyColName)
self.logger.log('unagg endpoints: {}'.format(unAggregatedEndpoints))
return {
dataType:
(unAggregatedEndpoints[-1]
if unAggregatedEndpoints != [] else None,
self.lastAggregationEndpoint(aggDataType=aggType,
timeColumnName=timeColName))
}
def aggregatedVsNewData(self):
"""
Convenience method.
:return: dict of tuples containing {datatype:(last raw datetime,
last agg datetime)}
"""
return {
x.keys()[0]: (x.values()[0])
for x in map(self.lastUnaggregatedAndAggregatedEndpoints,
[k for k in self.dataParams])
}
def monthStartsAndEnds(self, timeColumnName='', dataType=''):
"""
Return first date and last date for the given **raw** data type for each
month in the data's entire time range.
The end date is incremented by on aggregation period to account for
the data obtained at time 00:00.
:param timeColumnName: string
:param dataType: string
:return: List of tuples.
"""
self.logger.log('datatype {}'.format(dataType), 'debug')
(start,
end) = self.rows("""SELECT MIN({}), MAX({}) FROM \"{}\"""".format(
timeColumnName, timeColumnName, self.tables[dataType]))[0]
self.logger.log('start {}'.format(start))
self.logger.log('end {}'.format(end))
# End time needs transforming in split dates to extend the end of the
# day to 23:59:59.
splitDates = self.timeUtil.splitDates(start, end)
startEndDatesTransform = []
i = 0
while i < len(splitDates):
startEndDatesTransform.append(
(splitDates[i][0],
self.incrementEndpoint(
datetime(splitDates[i][1].timetuple()[0],
splitDates[i][1].timetuple()[1],
splitDates[i][1].timetuple()[2], 23, 59, 59))))
i += 1
return startEndDatesTransform
def aggregatedData(self,
dataType='',
aggregationType='',
timeColumnName='',
subkeyColumnName='',
startDate='',
endDate=''):
"""
***********************************************************************
Provide aggregated data.
***********************************************************************
Start and end dates are used to calculate interval crossings.
:param dataType: String
:param aggregationType: String
:param timeColumnName: String
:param subkeyColumnName: String
:param startDate: String
:param endDate: String
:returns: MSGAggregatedData
"""
aggData = []
ci = lambda col_name: self.columns[dataType].split(',').index(col_name)
rowCnt = 0
mySubkeys = []
if subkeyColumnName:
mySubkeys = self.subkeys(dataType=dataType,
timestampCol=timeColumnName,
subkeyCol=subkeyColumnName,
startDate=startDate,
endDate=endDate)
self.logger.log('subkeys: {}'.format(mySubkeys), 'debug')
def __initSumAndCount(subkey=None, sums=None, cnts=None):
"""
Initialize the sum and cnt data structures.
:param subkey: string
:param sums: list | dict | None
:param cnts: list | dict | None
"""
if not sums and not cnts:
sums = {}
cnts = {}
if not mySubkeys:
sums = []
cnts = []
for i in range(len(self.columns[dataType].split(','))):
sums.append(0)
cnts.append(0)
else:
if not subkey:
for i in range(len(self.columns[dataType].split(','))):
for k in mySubkeys:
if k not in sums.keys():
sums[k] = []
cnts[k] = []
sums[k].append(0)
cnts[k].append(0)
else:
sums[subkey] = []
for i in range(len(self.columns[dataType].split(','))):
sums[subkey].append(0)
cnts[subkey] = []
for i in range(len(self.columns[dataType].split(','))):
cnts[subkey].append(0)
return (sums, cnts)
(sum, cnt) = __initSumAndCount()
def __initIntervalCrossings():
"""
Perform initialization of the interval crossings used to
determine when interval crossings occur.
:returns None
"""
subkeysToCheck = copy.copy(mySubkeys)
self.logger.log('subkeys to check: {}'.format(subkeysToCheck),
'debug')
if mySubkeys:
for row in self.rawData(
dataType=dataType,
orderBy=[timeColumnName, subkeyColumnName],
timestampCol=timeColumnName,
startDate=startDate,
endDate=endDate):
# @CRITICAL: Exit after every subkey has been visited.
# This scans the raw data until each subkey is encountered
# ONCE and then exits.
if subkeysToCheck != []:
if row[ci(subkeyColumnName)] in subkeysToCheck:
subkeysToCheck.remove(row[ci(subkeyColumnName)])
minute = row[ci(
timeColumnName)].timetuple()[MINUTE_POSITION]
if minute <= 15:
self.nextMinuteCrossing[row[ci(
subkeyColumnName)]] = 15
elif minute <= 30:
self.nextMinuteCrossing[row[ci(
subkeyColumnName)]] = 30
elif minute <= 45:
self.nextMinuteCrossing[row[ci(
subkeyColumnName)]] = 45
elif minute == 0 or minute <= 59:
self.nextMinuteCrossing[row[ci(
subkeyColumnName)]] = 0
else:
raise Exception(
'Unable to determine next minute crossing')
self.logger.log(
'next min crossing for {} = {}'.format(
row[ci(subkeyColumnName)],
self.nextMinuteCrossing[row[ci(
subkeyColumnName)]]), 'debug')
else:
break
else:
# Non-subkey case e.g. weather data.
rowCnt = 0
# @todo Optimize by querying only the first row.
for row in self.rawData(dataType=dataType,
orderBy=[timeColumnName],
timestampCol=timeColumnName,
startDate=startDate,
endDate=endDate):
minute = row[ci(
timeColumnName)].timetuple()[MINUTE_POSITION]
if minute <= 15:
self.nextMinuteCrossingWithoutSubkeys = 15
elif minute <= 30:
self.nextMinuteCrossingWithoutSubkeys = 30
elif minute <= 45:
self.nextMinuteCrossingWithoutSubkeys = 45
elif minute == 0 or minute <= 59:
self.nextMinuteCrossingWithoutSubkeys = 0
else:
raise Exception(
'Unable to determine next minute crossing')
self.logger.log(
'next min crossing = {}'.format(
self.nextMinuteCrossingWithoutSubkeys), 'debug')
rowCnt += 1
if rowCnt > 0:
break
__initIntervalCrossings()
for row in self.rawData(dataType=dataType,
orderBy=[timeColumnName, subkeyColumnName],
timestampCol=timeColumnName,
startDate=startDate,
endDate=endDate):
if mySubkeys:
for col in self.columns[dataType].split(','):
if self.mathUtil.isNumber(
row[ci(col)]) and ci(col) != ci(subkeyColumnName):
sum[row[ci(subkeyColumnName)]][ci(col)] += row[ci(col)]
cnt[row[ci(subkeyColumnName)]][ci(col)] += 1
minute = row[ci(timeColumnName)].timetuple()[MINUTE_POSITION]
if self.intervalCrossed(minute=minute,
subkey=row[ci(subkeyColumnName)]):
minuteCrossed = minute
# Perform aggregation on all of the previous data including
# the current data for the current subkey.
self.logger.log(
'key: {}'.format(row[ci(subkeyColumnName)]), 'debug')
aggData += [
self.intervalAverages(sum, cnt,
row[ci(timeColumnName)],
ci(timeColumnName),
ci(subkeyColumnName),
row[ci(subkeyColumnName)])
]
self.logger.log('minute crossed {}'.format(minuteCrossed),
'DEBUG')
# Init current sum and cnt for subkey that has a completed
# interval.
(sum,
cnt) = __initSumAndCount(subkey=row[ci(subkeyColumnName)],
sums=sum,
cnts=cnt)
else:
for col in self.columns[dataType].split(','):
if self.mathUtil.isNumber(row[ci(col)]):
sum[ci(col)] += row[ci(col)]
cnt[ci(col)] += 1
minute = row[ci(timeColumnName)].timetuple()[MINUTE_POSITION]
if self.intervalCrossed(minute=minute):
aggData += [
self.intervalAverages(sum, cnt,
row[ci(timeColumnName)],
ci(timeColumnName))
]
(sum, cnt) = __initSumAndCount(subkey=None,
sums=sum,
cnts=cnt)
rowCnt += 1
self.logger.log('aggdata = {}'.format(aggData), 'debug')
return MSGAggregatedData(aggregationType=aggregationType,
columns=self.columns[dataType].split(','),
data=aggData)
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)
msgBody += msg
f = open(filename, "r")
reader = csv.reader(f)
data = []
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)
def setUp(self):
self.connector = MSGDBConnector(True)
self.conn = self.connector.connectDB()
self.configer = MSGConfiger()
def test_init(self):
# @todo Improve this test by using direct introspection instead of
# this roundabout method.
localConfiger = MSGConfiger()
self.assertIsInstance(self.configer, type(localConfiger))
class MSGDBExporterTester(unittest.TestCase):
"""
Unit tests for the MSG Cloud Exporter.
"""
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')
def tearDown(self):
"""
Delete all test items.
"""
REMOVE_TEMPORARY_FILES = True
if REMOVE_TEMPORARY_FILES:
try:
self.logger.log(
"Removing local test files {}, {}.".format(
self.uncompressedTestFilename,
self.compressedTestFilename), 'debug')
os.remove(
os.path.join(os.getcwd(), self.testDir,
self.uncompressedTestFilename))
os.remove(
os.path.join(os.getcwd(), self.testDir,
self.compressedTestFilename))
except OSError as detail:
self.logger.log(
'Exception while removing temporary files: {}'.format(
detail), 'SILENT')
try:
os.remove(
os.path.join(os.getcwd(), self.testDir,
self.compressedTestFilename))
except OSError as detail:
self.logger.log(
'Exception while removing temporary files: {}'.format(
detail), 'SILENT')
try:
for f in self.fileChunks:
os.remove(f)
except OSError as detail:
self.logger.log(
'Exception while removing temporary files: {}'.format(
detail), 'DEBUG')
try:
os.rmdir(self.testDir)
except OSError as detail:
self.logger.log(
'Exception while removing directory: {}'.format(detail),
'ERROR')
# Keep deleting from the cloud until there are no more to delete.
def deleteFromCloud():
self.logger.log("deleting from cloud", 'debug')
try:
fileIDToDelete = self.exporter.fileIDForFileName(
self.compressedTestFilename)
if fileIDToDelete is None:
return
self.logger.log("file ID to delete: {}".format(fileIDToDelete),
'DEBUG')
self.exporter.driveService.files().delete(
fileId='{}'.format(fileIDToDelete)).execute()
deleteFromCloud()
except (TypeError, http.HttpError) as e:
self.logger.log('Delete not successful: {}'.format(e), 'DEBUG')
deleteFromCloud()
def _upload_test_data_to_cloud(self):
"""
Provide an upload of test data that can be used in other tests.
Side effect: Store the file ID as an ivar.
"""
self.logger.log("Uploading test data for caller: {}".format(
self.pyUtil.callerName()))
filePath = "{}/{}".format(self.exportTestDataPath,
self.compressedTestFilename)
self.logger.log('Uploaded {}.'.format(filePath), 'info')
uploadResult = self.exporter.uploadFileToCloudStorage(filePath)
self.logger.log('upload result: {}'.format(uploadResult))
self.testDataFileID = self.exporter.fileIDForFileName(
self.compressedTestFilename)
self.logger.log("Test file ID is {}.".format(self.testDataFileID))
def test_markdown_list_of_downloadable_files(self):
"""
Match the Markdown line entry for the uploaded file.
"""
# @REVIEWED
self._upload_test_data_to_cloud()
self.assertEquals(
len(
filter(
lambda x: self.testDataFileID in x,
self.exporter.markdownListOfDownloadableFiles().splitlines(
))), 1)
def test_get_md5_sum_from_cloud(self):
"""
Test retrieving the MD5 sum from the cloud.
"""
# @REVIEWED
self.logger.log('Testing getting the MD5 sum.', 'info')
self._upload_test_data_to_cloud()
testFileMD5 = filter(
lambda x: x['id'] == self.testDataFileID,
self.exporter.cloudFiles['items'])[0]['md5Checksum']
self.assertEquals(len(testFileMD5), 32)
self.assertTrue(re.match(r'[0-9A-Za-z]+', testFileMD5))
def test_get_file_id_for_nonexistent_file(self):
"""
Test getting a file ID for a nonexistent file.
"""
# @REVIEWED
fileIDs = self.exporter.fileIDForFileName('nonexistent_file')
self.logger.log("file ids = {}".format(fileIDs), 'info')
self.assertIsNone(fileIDs)
def test_upload_test_data(self):
"""
Upload a test data file for unit testing of DB export.
The unit test data file is a predefined set of test data stored in
the test data path of the software distribution.
"""
# @REVIEWED
self._upload_test_data_to_cloud()
self.assertGreater(len(self.testDataFileID), 0)
self.assertTrue(re.match(r'[0-9A-Za-z]+', self.testDataFileID))
def test_adding_reader_permissions(self):
"""
Add reader permissions to a file that was uploaded.
"""
# @REVIEWED
self.logger.log("Testing adding reader permissions.")
self._upload_test_data_to_cloud()
email = self.configer.configOptionValue('Testing',
'tester_email_address')
service = self.exporter.driveService
try:
id_resp = service.permissions().getIdForEmail(
email=email).execute()
print id_resp
except errors.HttpError as detail:
print 'Exception while getting ID for email: {}'.format(detail)
new_permission = {'value': email, 'type': 'user', 'role': 'reader'}
try:
self.logger.log('Adding reader permission', 'INFO')
fileIDToAddTo = self.testDataFileID
# The permission dict is being output to stdout here.
resp = service.permissions().insert(fileId=fileIDToAddTo,
sendNotificationEmails=False,
body=new_permission).execute()
except errors.HttpError as detail:
self.logger.log(
'Exception while adding reader permissions: {}'.format(detail),
'error')
def permission_id(email):
try:
id_resp = service.permissions().getIdForEmail(
email=email).execute()
return id_resp['id']
except errors.HttpError as error:
self.logger.log("HTTP error: {}".format(error))
permission = {}
try:
permission = service.permissions().get(
fileId=self.testDataFileID,
permissionId=permission_id(email)).execute()
except errors.HttpError as error:
self.logger.log("HTTP error: {}".format(error))
self.assertEquals(permission['role'], 'reader')
def test_create_compressed_archived(self):
"""
* Copy test data to a temp directory (self.testDir).
* Create a checksum for test data.
* Create a gzip-compressed archive.
* Extract gzip-compressed archive.
* Create a checksum for the uncompressed data.
* Compare the checksums.
"""
# @REVIEWED
self.logger.log('Testing verification of a compressed archive.')
self.logger.log('cwd {}'.format(os.getcwd()))
fullPath = '{}'.format(
os.path.join(os.getcwd(), self.testDir,
self.uncompressedTestFilename))
shutil.copyfile(
'{}/{}'.format(self.exportTestDataPath,
self.uncompressedTestFilename), fullPath)
md5sum1 = self.fileUtil.md5Checksum(fullPath)
self.exporter.fileUtil.gzipCompressFile(fullPath)
try:
os.remove(
os.path.join(os.getcwd(), self.testDir,
self.uncompressedTestFilename))
except OSError as detail:
self.logger.log('Exception while removing: {}'.format(detail),
'ERROR')
# Extract archived data and generate checksum.
src = gzip.open('{}{}'.format(fullPath, '.gz'), "rb")
uncompressed = open(fullPath, "wb")
decoded = src.read()
uncompressed.write(decoded)
uncompressed.close()
md5sum2 = self.fileUtil.md5Checksum(fullPath)
self.assertEqual(
md5sum1, md5sum2, 'Checksums are not equal for original and new '
'decompressed archive.')
def test_export_db(self):
"""
Perform a quick test of the DB export method using Testing Mode.
This requires sudo authorization to complete.
"""
# @REVIEWED
self.logger.log('Testing exportDB using the testing DB.')
# @todo handle case where testing db does not exist.
dbs = ['test_meco']
ids = self.exporter.exportDBs(databases=dbs,
toCloud=True,
localExport=True)
self.logger.log('Count of exports: {}'.format(len(ids)))
self.assertEquals(len(ids), 1, "Count of exported files is wrong.")
map(self.exporter.deleteFile, ids)
def test_split_archive(self):
"""
Test splitting an archive into chunks.
"""
# @REVIEWED
self.logger.log('Testing archive splitting.')
fullPath = '{}/{}'.format(self.exportTestDataPath,
self.compressedTestFilename)
self.logger.log('fullpath: {}'.format(fullPath))
shutil.copyfile(
fullPath, '{}/{}'.format(self.testDir,
self.compressedTestFilename))
fullPath = '{}/{}'.format(self.testDir, self.compressedTestFilename)
self.fileChunks = self.fileUtil.splitLargeFile(fullPath=fullPath,
numChunks=3)
self.assertEquals(len(self.fileChunks), 3)
def test_get_file_size(self):
"""
Test retrieving local file sizes.
"""
# @REVIEWED
fullPath = '{}/{}'.format(self.exportTestDataPath,
self.compressedTestFilename)
fSize = self.fileUtil.fileSize(fullPath)
self.logger.log('size: {}'.format(fSize))
self.assertEqual(fSize, 12279, 'File size is correct.')
def test_upload_export_files_list(self):
"""
TBW
"""
pass
def test_checksum_after_upload(self):
"""
TBW
"""
pass
def test_dump_exclusions_dictionary(self):
"""
Verify the exclusions dictionary by its type.
:return:
"""
# @REVIEWED
exclusions = self.exporter.dumpExclusionsDictionary()
if exclusions:
self.assertEquals(type({}), type(exclusions))
def test_move_to_final(self):
"""
Test moving a file to the final destination path.
"""
# @REVIEWED
self.logger.log('Testing moving to final path {}.'.format(
self.configer.configOptionValue('Export', 'db_export_final_path')))
origCompressedFile = '{}/{}'.format(
self.configer.configOptionValue('Export', 'export_test_data_path'),
self.compressedTestFilename)
newCompressedFile = '{}/{}'.format(
self.configer.configOptionValue('Export', 'export_test_data_path'),
'temp_test_file')
shutil.copyfile(origCompressedFile, newCompressedFile)
self.exporter.moveToFinalPath(compressedFullPath=newCompressedFile)
self.assertTrue(
os.path.isfile('{}/{}'.format(
self.configer.configOptionValue('Export',
'db_export_final_path'),
'temp_test_file')))
# Remove the test file.
os.remove('{}/{}'.format(
self.configer.configOptionValue('Export', 'db_export_final_path'),
'temp_test_file'))
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 test_metadata_of_file_id(self):
"""
Test getting the metadata for a file ID.
"""
# @REVIEWED
self._upload_test_data_to_cloud()
self.logger.log('metadata: {}'.format(
self.exporter.metadataOfFileID(self.testDataFileID)))
self.assertTrue(re.match(r'[0-9A-Za-z]+', self.testDataFileID))
def test_filename_for_file_id(self):
"""
Test returning a file name given a file ID.
"""
# @REVIEWED
self._upload_test_data_to_cloud()
self.assertEquals(
self.exporter.filenameForFileID(fileID=self.testDataFileID),
self.compressedTestFilename)
def test_outdated_files(self):
# @REVIEWED
self._upload_test_data_to_cloud()
time.sleep(1)
self.logger.log("outdated:")
# For debugging:
for item in self.exporter.outdatedFiles(
daysBeforeOutdated=datetime.timedelta(days=-1)):
self.logger.log(
"name: {}, created date: {}".format(item['originalFilename'],
item['createdDate']),
'debug')
# Get all the outdated files where outdated is equal to anything
# uploaded today or later.
self.assertTrue(
self.exporter.outdatedFiles(daysBeforeOutdated=datetime.timedelta(
days=-1))[0]['id'] == self.testDataFileID)
self.logger.log('-----')
def test_delete_outdated(self):
"""
TBW
"""
pass
def test_list_of_downloadable_files(self):
"""
Test the list of downloadable files used by the available files page.
"""
# @REVIEWED
self._upload_test_data_to_cloud()
self.assertEquals(
len(
filter(lambda row: row['id'] == self.testDataFileID,
self.exporter.listOfDownloadableFiles())), 1,
"Test file not present.")
def test_count_of_db_exports(self):
count = self.exporter.countOfDBExports(EARLIEST_DATE)
self.logger.log(count, 'DEBUG')
self.assertTrue(int(count) or int(count) == int(0))
def test_count_of_cloud_files(self):
count = self.exporter.countOfCloudFiles()
self.assertTrue(int(count) or int(count) == int(0))
def test_plaintext_list_of_downloadable_files(self):
"""
This test handles content both with content links and without content
links.
"""
content = self.exporter.plaintextListOfDownloadableFiles()
self.assertRegexpMatches(
content, '\d+-\d+-\d+.*\,'
'\s+\d+-\d+-\d+T\d+:\d+:\d+\.\d+Z\,\s+\d+\sB')
def test_last_report_date(self):
last_report = self.exporter.notifier.lastReportDate(
types=MSGNotificationHistoryTypes,
noticeType=MSGNotificationHistoryTypes.MSG_EXPORT_SUMMARY)
self.assertTrue(last_report is None or last_report > EARLIEST_DATE)
def test_current_export_summary(self):
self.assertRegexpMatches(
self.exporter.currentExportSummary(),
re.compile('last.*databases.*free.*currently.*accessed.*',
flags=re.IGNORECASE | re.DOTALL))
class MSGDBExporterTester(unittest.TestCase):
"""
Unit tests for the MSG Cloud Exporter.
"""
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')
def tearDown(self):
"""
Delete all test items.
"""
REMOVE_TEMPORARY_FILES = True
if REMOVE_TEMPORARY_FILES:
try:
self.logger.log("Removing local test files {}, {}.".format(
self.uncompressedTestFilename, self.compressedTestFilename),
'debug')
os.remove(os.path.join(os.getcwd(), self.testDir,
self.uncompressedTestFilename))
os.remove(os.path.join(os.getcwd(), self.testDir,
self.compressedTestFilename))
except OSError as detail:
self.logger.log(
'Exception while removing temporary files: {}'.format(
detail), 'SILENT')
try:
os.remove(os.path.join(os.getcwd(), self.testDir,
self.compressedTestFilename))
except OSError as detail:
self.logger.log(
'Exception while removing temporary files: {}'.format(
detail), 'SILENT')
try:
for f in self.fileChunks:
os.remove(f)
except OSError as detail:
self.logger.log(
'Exception while removing temporary files: {}'.format(
detail), 'DEBUG')
try:
os.rmdir(self.testDir)
except OSError as detail:
self.logger.log(
'Exception while removing directory: {}'.format(detail),
'ERROR')
# Keep deleting from the cloud until there are no more to delete.
def deleteFromCloud():
self.logger.log("deleting from cloud", 'debug')
try:
fileIDToDelete = self.exporter.fileIDForFileName(
self.compressedTestFilename)
if fileIDToDelete is None:
return
self.logger.log("file ID to delete: {}".format(fileIDToDelete),
'DEBUG')
self.exporter.driveService.files().delete(
fileId = '{}'.format(fileIDToDelete)).execute()
deleteFromCloud()
except (TypeError, http.HttpError) as e:
self.logger.log('Delete not successful: {}'.format(e), 'DEBUG')
deleteFromCloud()
def _upload_test_data_to_cloud(self):
"""
Provide an upload of test data that can be used in other tests.
Side effect: Store the file ID as an ivar.
"""
self.logger.log("Uploading test data for caller: {}".format(
self.pyUtil.callerName()))
filePath = "{}/{}".format(self.exportTestDataPath,
self.compressedTestFilename)
self.logger.log('Uploaded {}.'.format(filePath), 'info')
uploadResult = self.exporter.uploadFileToCloudStorage(filePath)
self.logger.log('upload result: {}'.format(uploadResult))
self.testDataFileID = self.exporter.fileIDForFileName(
self.compressedTestFilename)
self.logger.log("Test file ID is {}.".format(self.testDataFileID))
def test_markdown_list_of_downloadable_files(self):
"""
Match the Markdown line entry for the uploaded file.
"""
# @REVIEWED
self._upload_test_data_to_cloud()
self.assertEquals(len(filter(lambda x: self.testDataFileID in x,
self.exporter.markdownListOfDownloadableFiles().splitlines())),
1)
def test_get_md5_sum_from_cloud(self):
"""
Test retrieving the MD5 sum from the cloud.
"""
# @REVIEWED
self.logger.log('Testing getting the MD5 sum.', 'info')
self._upload_test_data_to_cloud()
testFileMD5 = filter(lambda x: x['id'] == self.testDataFileID,
self.exporter.cloudFiles['items'])[0][
'md5Checksum']
self.assertEquals(len(testFileMD5), 32)
self.assertTrue(re.match(r'[0-9A-Za-z]+', testFileMD5))
def test_get_file_id_for_nonexistent_file(self):
"""
Test getting a file ID for a nonexistent file.
"""
# @REVIEWED
fileIDs = self.exporter.fileIDForFileName('nonexistent_file')
self.logger.log("file ids = {}".format(fileIDs), 'info')
self.assertIsNone(fileIDs)
def test_upload_test_data(self):
"""
Upload a test data file for unit testing of DB export.
The unit test data file is a predefined set of test data stored in
the test data path of the software distribution.
"""
# @REVIEWED
self._upload_test_data_to_cloud()
self.assertGreater(len(self.testDataFileID), 0)
self.assertTrue(re.match(r'[0-9A-Za-z]+', self.testDataFileID))
def test_adding_reader_permissions(self):
"""
Add reader permissions to a file that was uploaded.
"""
# @REVIEWED
self.logger.log("Testing adding reader permissions.")
self._upload_test_data_to_cloud()
email = self.configer.configOptionValue('Testing',
'tester_email_address')
service = self.exporter.driveService
try:
id_resp = service.permissions().getIdForEmail(
email = email).execute()
print id_resp
except errors.HttpError as detail:
print 'Exception while getting ID for email: {}'.format(detail)
new_permission = {'value': email, 'type': 'user', 'role': 'reader'}
try:
self.logger.log('Adding reader permission', 'INFO')
fileIDToAddTo = self.testDataFileID
# The permission dict is being output to stdout here.
resp = service.permissions().insert(fileId = fileIDToAddTo,
sendNotificationEmails = False,
body = new_permission).execute()
except errors.HttpError as detail:
self.logger.log(
'Exception while adding reader permissions: {}'.format(detail),
'error')
def permission_id(email):
try:
id_resp = service.permissions().getIdForEmail(
email = email).execute()
return id_resp['id']
except errors.HttpError as error:
self.logger.log("HTTP error: {}".format(error))
permission = {}
try:
permission = service.permissions().get(fileId = self.testDataFileID,
permissionId = permission_id(
email)).execute()
except errors.HttpError as error:
self.logger.log("HTTP error: {}".format(error))
self.assertEquals(permission['role'], 'reader')
def test_create_compressed_archived(self):
"""
* Copy test data to a temp directory (self.testDir).
* Create a checksum for test data.
* Create a gzip-compressed archive.
* Extract gzip-compressed archive.
* Create a checksum for the uncompressed data.
* Compare the checksums.
"""
# @REVIEWED
self.logger.log('Testing verification of a compressed archive.')
self.logger.log('cwd {}'.format(os.getcwd()))
fullPath = '{}'.format(os.path.join(os.getcwd(), self.testDir,
self.uncompressedTestFilename))
shutil.copyfile('{}/{}'.format(self.exportTestDataPath,
self.uncompressedTestFilename), fullPath)
md5sum1 = self.fileUtil.md5Checksum(fullPath)
self.exporter.fileUtil.gzipCompressFile(fullPath)
try:
os.remove(os.path.join(os.getcwd(), self.testDir,
self.uncompressedTestFilename))
except OSError as detail:
self.logger.log('Exception while removing: {}'.format(detail),
'ERROR')
# Extract archived data and generate checksum.
src = gzip.open('{}{}'.format(fullPath, '.gz'), "rb")
uncompressed = open(fullPath, "wb")
decoded = src.read()
uncompressed.write(decoded)
uncompressed.close()
md5sum2 = self.fileUtil.md5Checksum(fullPath)
self.assertEqual(md5sum1, md5sum2,
'Checksums are not equal for original and new '
'decompressed archive.')
def test_export_db(self):
"""
Perform a quick test of the DB export method using Testing Mode.
This requires sudo authorization to complete.
"""
# @REVIEWED
self.logger.log('Testing exportDB using the testing DB.')
# @todo handle case where testing db does not exist.
dbs = ['test_meco']
ids = self.exporter.exportDBs(databases = dbs, toCloud = True,
localExport = True)
self.logger.log('Count of exports: {}'.format(len(ids)))
self.assertEquals(len(ids), 1, "Count of exported files is wrong.")
map(self.exporter.deleteFile, ids)
def test_split_archive(self):
"""
Test splitting an archive into chunks.
"""
# @REVIEWED
self.logger.log('Testing archive splitting.')
fullPath = '{}/{}'.format(self.exportTestDataPath,
self.compressedTestFilename)
self.logger.log('fullpath: {}'.format(fullPath))
shutil.copyfile(fullPath, '{}/{}'.format(self.testDir,
self.compressedTestFilename))
fullPath = '{}/{}'.format(self.testDir, self.compressedTestFilename)
self.fileChunks = self.fileUtil.splitLargeFile(fullPath = fullPath,
numChunks = 3)
self.assertEquals(len(self.fileChunks), 3)
def test_get_file_size(self):
"""
Test retrieving local file sizes.
"""
# @REVIEWED
fullPath = '{}/{}'.format(self.exportTestDataPath,
self.compressedTestFilename)
fSize = self.fileUtil.fileSize(fullPath)
self.logger.log('size: {}'.format(fSize))
self.assertEqual(fSize, 12279, 'File size is correct.')
def test_upload_export_files_list(self):
"""
TBW
"""
pass
def test_checksum_after_upload(self):
"""
TBW
"""
pass
def test_dump_exclusions_dictionary(self):
"""
Verify the exclusions dictionary by its type.
:return:
"""
# @REVIEWED
exclusions = self.exporter.dumpExclusionsDictionary()
if exclusions:
self.assertEquals(type({}), type(exclusions))
def test_move_to_final(self):
"""
Test moving a file to the final destination path.
"""
# @REVIEWED
self.logger.log('Testing moving to final path {}.'.format(
self.configer.configOptionValue('Export', 'db_export_final_path')))
origCompressedFile = '{}/{}'.format(
self.configer.configOptionValue('Export', 'export_test_data_path'),
self.compressedTestFilename)
newCompressedFile = '{}/{}'.format(
self.configer.configOptionValue('Export', 'export_test_data_path'),
'temp_test_file')
shutil.copyfile(origCompressedFile, newCompressedFile)
self.exporter.moveToFinalPath(compressedFullPath = newCompressedFile)
self.assertTrue(os.path.isfile('{}/{}'.format(
self.configer.configOptionValue('Export', 'db_export_final_path'),
'temp_test_file')))
# Remove the test file.
os.remove('{}/{}'.format(
self.configer.configOptionValue('Export', 'db_export_final_path'),
'temp_test_file'))
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 test_metadata_of_file_id(self):
"""
Test getting the metadata for a file ID.
"""
# @REVIEWED
self._upload_test_data_to_cloud()
self.logger.log('metadata: {}'.format(
self.exporter.metadataOfFileID(self.testDataFileID)))
self.assertTrue(re.match(r'[0-9A-Za-z]+', self.testDataFileID))
def test_filename_for_file_id(self):
"""
Test returning a file name given a file ID.
"""
# @REVIEWED
self._upload_test_data_to_cloud()
self.assertEquals(
self.exporter.filenameForFileID(fileID = self.testDataFileID),
self.compressedTestFilename)
def test_outdated_files(self):
# @REVIEWED
self._upload_test_data_to_cloud()
time.sleep(1)
self.logger.log("outdated:")
# For debugging:
for item in self.exporter.outdatedFiles(
daysBeforeOutdated = datetime.timedelta(
days = -1)): self.logger.log(
"name: {}, created date: {}".format(item['originalFilename'],
item['createdDate']), 'debug')
# Get all the outdated files where outdated is equal to anything
# uploaded today or later.
self.assertTrue(self.exporter.outdatedFiles(
daysBeforeOutdated = datetime.timedelta(days = -1))[0][
'id'] == self.testDataFileID)
self.logger.log('-----')
def test_delete_outdated(self):
"""
TBW
"""
pass
def test_list_of_downloadable_files(self):
"""
Test the list of downloadable files used by the available files page.
"""
# @REVIEWED
self._upload_test_data_to_cloud()
self.assertEquals(len(
filter(lambda row: row['id'] == self.testDataFileID,
self.exporter.listOfDownloadableFiles())), 1,
"Test file not present.")
def test_count_of_db_exports(self):
count = self.exporter.countOfDBExports(EARLIEST_DATE)
self.logger.log(count,'DEBUG')
self.assertTrue(int(count) or int(count) == int(0))
def test_count_of_cloud_files(self):
count = self.exporter.countOfCloudFiles()
self.assertTrue(int(count) or int(count) == int(0))
def test_plaintext_list_of_downloadable_files(self):
"""
This test handles content both with content links and without content
links.
"""
content = self.exporter.plaintextListOfDownloadableFiles()
self.assertRegexpMatches(content,
'\d+-\d+-\d+.*\,'
'\s+\d+-\d+-\d+T\d+:\d+:\d+\.\d+Z\,\s+\d+\sB')
def test_last_report_date(self):
last_report = self.exporter.notifier.lastReportDate(
types = MSGNotificationHistoryTypes,
noticeType = MSGNotificationHistoryTypes.MSG_EXPORT_SUMMARY)
self.assertTrue(last_report is None or last_report > EARLIEST_DATE)
def test_current_export_summary(self):
self.assertRegexpMatches(self.exporter.currentExportSummary(),
re.compile(
'last.*databases.*free.*currently.*accessed.*',
flags = re.IGNORECASE | re.DOTALL))
def __init__(self, testing = False):
"""
Constructor.
:param testing: (optional) Boolean indicating if Testing Mode is on.
"""
self.logger = SEKLogger(__name__, 'silent')
if (testing):
self.logger.log("Testing Mode is ON.", 'info')
self.debug = False
self.configer = MSGConfiger()
if self.configer.configOptionValue("Debugging", 'debug') == True:
self.debug = True
self.util = MSGDBUtil()
self.mapper = MECOMapper()
self.connector = MSGDBConnector(testing)
self.conn = self.connector.connectDB()
self.filename = None
self.fileObject = None
self.processForInsertElementCount = 0
self.inserter = MECODBInserter()
self.insertDataIntoDatabase = False
# Count number of times sections in source data are encountered.
self.tableNameCount = {'SSNExportDocument': 0, 'MeterData': 0,
'RegisterData': 0, 'RegisterRead': 0, 'Tier': 0,
'Register': 0, 'IntervalReadData': 0,
'Interval': 0, 'Reading': 0, 'IntervalStatus': 0,
'ChannelStatus': 0, 'EventData': 0, 'Event': 0}
# Use this dictionary to track which channels were processed when
# readings are being processed. this is to prevent duplicate channel
# data from being inserted.
self.channelProcessed = {}
self.initChannelProcessed()
# Tables to be inserted to.
self.insertTables = self.configer.insertTables
self.lastSeqVal = None
self.fKeyVal = None
self.lastTable = None
self.fkDeterminer = MECOFKDeterminer()
self.dupeChecker = MECODupeChecker()
self.currentMeterName = None
self.currentIntervalEndTime = None
self.currentRegisterReadReadTime = None
self.dupesExist = False
self.channelDupeExists = False # For Reading dupes.
self.numberDupeExists = False # For Register dupes.
self.eventTimeDupeExists = False # For Event dupes.
self.commitCount = 0
self.readingDupeOnInsertCount = 0 # For Reading dupes.
self.registerDupeOnInsertCount = 0 # For Register dupes.
self.eventDupeOnInsertCount = 0 # For Event dupes.
self.dataProcessCount = 0
self.readingDupeCheckCount = 0 # For Reading dupes.
self.registerDupeCheckCount = 0 # For Register dupes.
self.eventDupeCheckCount = 0 # For Event dupes.
self.insertCount = 0
self.cumulativeInsertCount = 0
self.nonProcessForInsertElementCount = 0
self.readingInsertCount = 0
self.registerInsertCount = 0
self.eventInsertCount = 0
self.totalReadingInsertCount = 0
self.totalRegisterInsertCount = 0
self.totalEventInsertCount = 0
self.totalReadingDupeOnInsertCount = 0
self.totalRegisterDupeOnInsertCount = 0
self.totalEventDupeOnInsertCount = 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.')
def setUp(self):
self.configer = MSGConfiger()
