def calc_dest_timestamp(dest_granularity, source_timestamp):
# Hack for whole hour timestamps
offset = timedelta(0, 1)
plugin = get_plugin("trend")
most_recent = plugin.get_most_recent_timestamp(source_timestamp - offset, dest_granularity)
return plugin.get_next_timestamp(most_recent, dest_granularity)
def source_table_names(self, cursor, timestamp):
"""
Return source table names based on source items and timestamp
"""
views = get_views(cursor)
source_datasource_ids = [trendstore.datasource.id for trendstore in self.source_trendstores]
def view_matches_trendstore(view, trendstore):
return view.datasource.id == trendstore.datasource.id \
and view.entitytype.id == trendstore.entitytype.id \
and view.granularity.name == trendstore.granularity.name
matching_views = [view for view in views if any(view_matches_trendstore(view, trendstore) for trendstore in self.source_trendstores)]
table_names = [trendstore.make_table_name(timestamp)
for trendstore in self.source_trendstores]
plugin = get_plugin("trend")
for view in matching_views:
for source in view.sources:
table_name = plugin.get_table_name(source.datasource, source.granularity, source.entitytype.name, timestamp)
table_names.append(table_name)
return table_names
def file_name(ts):
"""
Return file name for output
"""
plugin = get_plugin("trend")
most_recent_ts = plugin.get_most_recent_timestamp(ts, 86400)
_ts = plugin.get_previous_timestamp(most_recent_ts, 86400)
return "cell_downtime_history_{0:%Y%m%d}_23hr.csv".format(_ts)
def test_create_granularity():
plugin = get_plugin("trend")
instance = plugin(None, api_version=4)
granularity = instance.create_granularity(3600)
eq_(granularity.name, "3600")
def test_create_v4_instance(conn):
"""
A V4 API should be returned when specifying api_version 4.
"""
plugin = get_plugin("trend")
instance = plugin(conn, api_version=4)
eq_(instance.api_version(), 4)
def test_create_default_instance(conn):
"""
By default, a V3 API should be returned.
"""
plugin = get_plugin("trend")
instance = plugin(conn)
eq_(instance.api_version(), 3)
def get_timestamps(ts):
timestamps = []
granularity =86400
plugin = get_plugin("trend")
_ts = plugin.get_most_recent_timestamp(ts, granularity)
for i in range(28):
timestamps.append(_ts)
_ts = plugin.get_previous_timestamp(_ts, granularity)
return timestamps
def fields(conn, ts):
granularity = 86400
plugin = get_plugin("trend")
timestamps = get_timestamps(ts)
return ["SITE_NR", "CONTROLLER", "NETWERK", "PLACE", "ADDRESS", "CI",
"Dagen met Downtime"] + list(chain(*[
("{0:%Y%m%d}_man".format(plugin.get_previous_timestamp(ts, granularity)),
"{0:%Y%m%d}_auto".format(plugin.get_previous_timestamp(ts, granularity)),
"{0:%Y%m%d}_total".format(plugin.get_previous_timestamp(ts, granularity)))
for ts in timestamps]))
def test_create_datapackage():
plugin = get_plugin("trend")
instance = plugin(None, api_version=4)
granularity = instance.create_granularity(3600)
timestamp = pytz.utc.localize(datetime.now())
trend_names = ["a", "b", "c"]
rows = [(123, [1, 2, 3])]
datapackage = instance.DataPackage(granularity, timestamp, trend_names, rows)
assert datapackage is not None
def test_get_trendstore(conn):
plugin = get_plugin("trend")
instance = plugin(conn, api_version=4)
granularity = create_granularity("900")
with closing(conn.cursor()) as cursor:
datasource = name_to_datasource(cursor, "test-src")
entitytype = name_to_entitytype(cursor, "test-type")
instance.TrendStore(datasource, entitytype, granularity, 86400,
"table").create(cursor)
trendstore = instance.get_trendstore(datasource, entitytype, granularity)
assert_not_equal(trendstore, None)
def records(conn, ts):
granularity = 86400
plugin = get_plugin("trend")
most_recent_ts = plugin.get_most_recent_timestamp(ts, granularity)
previous_ts = plugin.get_previous_timestamp(most_recent_ts, granularity)
query = (
' SELECT'
" substring(config_cell.technology from '[[:alpha:]]+') tech,"
' config_cell.controller, config_cell.site_nr, cell.name, config_cell.state,'
" (ref.timestamp[2] - interval '1 hour')::date,"
' ref.downtime_man[2], ref.downtime_auto[2], ref.downtime[2], ref.samples[2],'
" (ref.timestamp[1] - interval '1 hour')::date,"
' ref.downtime_man[1], ref.downtime_auto[1], ref.downtime[1], ref.samples[1],'
' config_site.place, config_site.address'
' FROM ('
' SELECT'
' entity_id,'
' array_agg(timestamp ORDER BY timestamp) AS timestamp,'
' array_agg(samples ORDER BY timestamp) samples,'
' array_agg(downtime_man ORDER BY timestamp) downtime_man,'
' array_agg(downtime_auto ORDER BY timestamp) downtime_auto,'
' array_agg(downtime ORDER BY timestamp) downtime'
' FROM trend."transform-availability_Cell_day"'
' WHERE timestamp IN (%s, %s)'
' GROUP BY entity_id'
' ) AS ref'
' INNER JOIN directory.entity cell ON cell.id = ref.entity_id'
' INNER JOIN attribute."configuration_Cell" config_cell ON config_cell.entity_id = cell.id'
' INNER JOIN attribute."configuration_Site" config_site ON config_site.site_nr = config_cell.site_nr'
' WHERE ref.downtime[2] > 86300 AND ref.downtime[1] < 86300'
" AND config_cell.state = 'ID'"
' ORDER BY tech, config_cell.site_nr, cell.name'
)
with closing(conn.cursor()) as cursor:
cursor.execute(query, (most_recent_ts, previous_ts))
return cursor.fetchall()
def get_trend_data(conn, datasource, entitytype, subquery_filter, timestamps):
"""
Return dict of trend_data specified by subquery_filter, timestamp and number_of_days
"""
trends = ["downtime_man", "downtime_auto", "downtime"]
(start,end) = (timestamps[-1], timestamps[0])
plugin = get_plugin("trend")(conn)
rows = plugin.retrieve([datasource], 86400, entitytype, trends,
None, start, end, subquery_filter=subquery_filter)
trend_data = {}
emptyrecord = [(None,) * len(trends)] * len(timestamps)
for row in rows:
trend_data.setdefault(row[0], emptyrecord[:])[timestamps.index(row[1])] = row[2:]
return trend_data
def store_txn(conn, transformation, transformed_rows):
"""
Stores transformed data in destination as specified in function_set
"""
timestamp = transformation.dest_timestamp.astimezone(transformation.function_set.dest_trendstore.datasource.tzinfo)
with closing(conn.cursor()) as cursor:
column_names = transformation.function_set.get_dest_columns(cursor)
rows = [(row[0], ([transformation.function_set.id],) + row[2:])
for row in transformed_rows if row[0]]
plugin = get_plugin("trend")(conn, api_version=4)
datapackage = plugin.DataPackage(transformation.function_set.dest_trendstore.granularity,
transformation.dest_timestamp, column_names, rows)
return plugin.store_txn(transformation.function_set.dest_trendstore, datapackage)
def get_records(conn, datasource, ts):
plugin = get_plugin("trend")
granularity = 86400
with closing(conn.cursor()) as cursor:
most_recent_ts = plugin.get_most_recent_timestamp(ts, granularity)
entitytype = get_entitytype(conn, "Cell")
# Only cells with downtime >= (previous day in seconds - 1 hour)
subquery_filter = (
"SELECT entity_id AS id "
"FROM trend.\"transform-availability_Cell_day\" "
"WHERE timestamp = '{0}' "
"AND downtime >= EXTRACT(EPOCH FROM timestamp - (timestamp - interval '1 day - 1 hour')) ".format(most_recent_ts)) #Deals with DST!
timestamps = get_timestamps(ts)
trend_data = get_trend_data(
conn, datasource, entitytype, subquery_filter, timestamps)
cell_info = get_cell_info(conn, subquery_filter)
#For sorting
ref = [(int(v[1]), int(v[0]), k) for k,v in cell_info.iteritems()]
ref.sort()
_records = []
for id in (r[2] for r in ref):
(cellid, sitenr, controller, place, address, generation) = cell_info.get(id)
downtime_man, downtime_auto, downtime, = zip(*trend_data.get(id))
#sneaky HACK to deal with possible empty 'address' field
if address is None:
address = ""
#replace() is nasty HACK for MS Excel
line = [sitenr, controller, generation, place, address.replace(",", " - "), cellid, len([v for v in downtime if v])]
line += [str(v) if not (v is None) else "" for v in [_v for _v in chain(*zip(downtime_man, downtime_auto, downtime))]]
_records.append(line)
return _records
def test_create_view(conn):
testset_small = TestSet1Small()
with closing(conn.cursor()) as cursor:
testset_small.load(cursor)
datasource = name_to_datasource(cursor, "view-test")
trendstore = TrendStore.get(cursor, datasource, testset_small.entitytype,
testset_small.granularity)
if not trendstore:
trendstore = TrendStore(datasource, testset_small.entitytype,
testset_small.granularity, partition_size=86400,
type="view").create(cursor)
view_sql = (
"SELECT "
"999 AS entity_id, "
"'2013-08-26 13:00:00+02:00'::timestamp with time zone AS timestamp, "
'10 AS "CntrA"')
view = View(trendstore, view_sql).define(cursor).create(cursor)
conn.commit()
plugin = get_plugin("trend")
instance_v4 = plugin(conn, api_version=4)
start = testset_small.datasource.tzinfo.localize(datetime.datetime(2013, 8, 26, 13, 0, 0))
end = start
result = instance_v4.retrieve(trendstore, ["CntrA"], None, start, end)
eq_(len(result), 1)
def test_retrieve_from_v4_trendstore(conn):
plugin = get_plugin("trend")
data = TestData()
plugin_obj = plugin(conn, api_version=3)
with closing(conn.cursor()) as cursor:
clear_database(cursor)
data.load(cursor)
start = data.timestamp_1
end = data.timestamp_1
entity = data.entities[1]
entities = [entity.id]
column_names = [
"CellID",
"CCR",
"CCRatts",
"Drops"]
datasources = [data.datasource_a]
entitytype = data.entitytype
granularity = 900
r = plugin_obj.retrieve(datasources, granularity, entitytype,
column_names, entities, start, end)
eq_(len(r), 1)
first_result = head(r)
entity_id, timestamp, c1, c2, c3, c4 = first_result
eq_(entity_id, entity.id)
eq_(c4, 18)
def test_load_plugin():
plugin = get_plugin("trend")
assert_not_equal(plugin, None)
def retrieve(conn, function_set, timestamp):
"""
Retrieve specific transformed data
"""
with closing(conn.cursor()) as cursor:
map_to_function_call = partial(function_call_from_mapping, cursor)
column_expressions = map(map_to_function_call, function_set.mapping_signature)
plugin = get_plugin("trend")(conn, api_version=4)
result_rows = []
max_modified = None
if function_set.group_by:
end = timestamp
start = function_set.dest_trendstore.granularity.decr(timestamp)
interval = (start, end)
modified_column = Call("max", Column("modified"))
columns = [modified_column] + column_expressions
trendstore = function_set.source_trendstores[0]
fetchall = partial(plugin.retrieve_aggregated, trendstore, columns,
interval, function_set.group_by, subquery_filter=None,
relation_table_name=function_set.relation_table)
for row in fetchall():
r = row[:3] + row[4:]
result_rows.append(r)
modified = row[3]
if max_modified is None:
max_modified = modified
else:
max_modified = max(max_modified, modified)
else:
table_names = get_table_names(function_set.source_trendstores, timestamp, timestamp)
tables = map(Table, table_names)
modified_columns = [Column(table, "modified") for table in tables]
modified_column = Call("greatest", *modified_columns)
columns = [modified_column] + column_expressions
fetchall = partial(plugin.retrieve,
function_set.source_trendstores, columns, None, timestamp, timestamp,
subquery_filter=None, relation_table_name=function_set.relation_table)
for row in fetchall():
modified = row[2]
if modified:
r = row[:2] + row[3:]
result_rows.append(r)
if max_modified is None:
max_modified = modified
else:
max_modified = max(max_modified, modified)
return max_modified, result_rows
def test_get_plugin():
plugin = get_plugin("attribute")
eq_('create', plugin.__name__)
def test_unsupported_api_version(conn):
plugin = get_plugin("trend")
plugin(conn, api_version=42)
def test_create_v4_instance(conn):
plugin = get_plugin("trend")
v3_instance = plugin(conn, api_version=4)
eq_(v3_instance.api_version(), 4)
def test_load_plugin(conn):
plugin = get_plugin("attribute")(conn)
assert not plugin is None
def test_most_recent_timestamp():
"""
Test `get_most_recent_timestamp` static method of trend plugin
"""
plugin_v3 = get_plugin("trend")
tz = pytz.timezone("Europe/Amsterdam")
ts = tz.localize(datetime(2012, 10, 8, 2, 42, 0))
most_recent_timestamp = tz.localize(datetime(2012, 10, 8, 2, 0, 0))
granularity = 3600
assert_equal(plugin_v3.get_most_recent_timestamp(ts, granularity),
most_recent_timestamp)
ts = tz.localize(datetime(2012, 10, 8, 2, 42, 0))
most_recent_timestamp = tz.localize(datetime(2012, 10, 8, 2, 0, 0))
granularity = 3600
assert_equal(plugin_v3.get_most_recent_timestamp(ts, granularity),
most_recent_timestamp)
ts = tz.localize(datetime(2012, 10, 29, 0, 0, 0))
most_recent_timestamp = tz.localize(datetime(2012, 10, 29, 0, 0, 0))
granularity = 604800
assert_equal(plugin_v3.get_most_recent_timestamp(ts, granularity),
most_recent_timestamp)
ts = tz.localize(datetime(2012, 10, 28, 23, 59, 59))
most_recent_timestamp = tz.localize(datetime(2012, 10, 22, 0, 0, 0))
granularity = 604800
assert_equal(plugin_v3.get_most_recent_timestamp(ts, granularity),
most_recent_timestamp)
ts = tz.localize(datetime(2012, 10, 8, 0, 0, 0)) - timedelta(0, 1)
most_recent_timestamp = tz.localize(datetime(2012, 10, 1, 0, 0, 0))
granularity = 604800
assert_equal(plugin_v3.get_most_recent_timestamp(ts, granularity),
most_recent_timestamp)
ts = tz.localize(datetime(2012, 10, 9, 2, 30, 0))
most_recent_timestamp = tz.localize(datetime(2012, 10, 9, 0, 0, 0))
granularity = 86400
assert_equal(plugin_v3.get_most_recent_timestamp(ts, granularity),
most_recent_timestamp)
ts = tz.localize(datetime(2012, 10, 29, 0, 0, 0))
most_recent_timestamp = tz.localize(datetime(2012, 10, 29, 0, 0, 0))
for granularity in [900, 3600, 86400, 604800]:
assert_equal(plugin_v3.get_most_recent_timestamp(ts, granularity),
most_recent_timestamp)
ts = pytz.utc.localize(datetime(2012, 10, 9, 0, 14, 0))
loc_ts = ts.astimezone(tz)
timestamp = tz.localize(datetime(2012, 10, 9, 2, 0, 0))
granularity = 86400
assert_false(timestamp <= plugin_v3.get_most_recent_timestamp(loc_ts, granularity))
ts = pytz.utc.localize(datetime(2012, 10, 9, 9, 14, 0))
loc_ts = ts.astimezone(tz)
timestamp = tz.localize(datetime(2012, 10, 9, 11, 0, 0))
granularity = 3600
assert_true(timestamp <= plugin_v3.get_most_recent_timestamp(loc_ts, granularity))
# DST switch on oct 28th
ts = tz.localize(datetime(2012, 10, 28, 17, 42, 0))
most_recent_timestamp = tz.localize(datetime(2012, 10, 28, 0, 0, 0))
granularity = 86400
assert_equal(plugin_v3.get_most_recent_timestamp(ts, granularity),
most_recent_timestamp)
ts_utc = pytz.utc.localize(datetime(2013, 2, 25, 23, 0, 0))
most_recent_timestamp = tz.localize(datetime(2013, 2, 26, 0, 0, 0))
granularity = 86400
assert_equal(plugin_v3.get_most_recent_timestamp(ts_utc, granularity, minerva_tz=tz),
most_recent_timestamp)
# DST switch on oct 28th
ts_utc = pytz.utc.localize(datetime(2012, 10, 28, 16, 42, 0))
most_recent_timestamp = tz.localize(datetime(2012, 10, 28, 0, 0, 0))
granularity = 86400
assert_equal(plugin_v3.get_most_recent_timestamp(ts_utc, granularity, minerva_tz=tz),
most_recent_timestamp)