def new(self, environment_type, path_info):
conf.log.warning("New context created, Check your db! '%s', '%s'" % (environment_type, path_info))
insert_new = "INSERT INTO context_dim VALUES(null, %s, %s, %s, %s, null)"
new_id = None
with analytical_transaction() as cursor:
try:
now = now_str()
# We don't know the context name so lets just use first part of the path info
path_info = '/'.join(path_info.split('/')[:2])
context = path_info
path_info += '.*'
cursor.execute(insert_new, (context, environment_type, path_info, now))
cursor.execute("SELECT last_insert_id() FROM context_dim LIMIT 1")
row = cursor.fetchone()
if row and len(row) > 0:
new_id = row[0]
is_home = environment_type == 'home'
cache = {True: self.home_prefix_cache, False: self.project_prefix_cache}[is_home]
cache[context] = row[0]
except:
conf.log.exception("Failed creating a new record to context dimension. %s" % str(path_info))
raise
return new_id
def add_new_partitions(self, table_plan):
date = DateDimension()
now = date.date_sk_utcnow()
# Calculate partition limits
latest = table_plan.partitions[-1]
step = table_plan.partition_size
first = latest + step
limit = now + (table_plan.partition_size *
(table_plan.buffer_partitions + 1))
if limit <= first:
return
# Create sql statement
partitions = []
for partition in range(first, limit, step):
partitions.append("PARTITION p_%d VALUES LESS THAN (%d)" %
(partition, partition))
sql = "ALTER TABLE %s ADD PARTITION (%s)" % (table_plan.table,
','.join(partitions))
# Run partitioning command
with analytical_transaction() as cursor:
try:
cursor.execute(sql)
except:
conf.log.exception("Adding partitions FAILED! %s" % sql)
conf.log.info("Added partitions for %s" % table_plan.table)
def add_new_partitions(self, table_plan):
date = DateDimension()
now = date.date_sk_utcnow()
# Calculate partition limits
latest = table_plan.partitions[-1]
step = table_plan.partition_size
first = latest + step
limit = now + (table_plan.partition_size * (table_plan.buffer_partitions + 1))
if limit <= first:
return
# Create sql statement
partitions = []
for partition in range(first, limit, step):
partitions.append("PARTITION p_%d VALUES LESS THAN (%d)" % (partition, partition))
sql = "ALTER TABLE %s ADD PARTITION (%s)" % (table_plan.table, ','.join(partitions))
# Run partitioning command
with analytical_transaction() as cursor:
try:
cursor.execute(sql)
except:
conf.log.exception("Adding partitions FAILED! %s" % sql)
conf.log.info("Added partitions for %s" % table_plan.table)
def new(self, environment_type, path_info):
conf.log.warning("New context created, Check your db! '%s', '%s'" %
(environment_type, path_info))
insert_new = "INSERT INTO context_dim VALUES(null, %s, %s, %s, %s, null)"
new_id = None
with analytical_transaction() as cursor:
try:
now = now_str()
# We don't know the context name so lets just use first part of the path info
path_info = '/'.join(path_info.split('/')[:2])
context = path_info
path_info += '.*'
cursor.execute(insert_new,
(context, environment_type, path_info, now))
cursor.execute(
"SELECT last_insert_id() FROM context_dim LIMIT 1")
row = cursor.fetchone()
if row and len(row) > 0:
new_id = row[0]
is_home = environment_type == 'home'
cache = {
True: self.home_prefix_cache,
False: self.project_prefix_cache
}[is_home]
cache[context] = row[0]
except:
conf.log.exception(
"Failed creating a new record to context dimension. %s" %
str(path_info))
raise
return new_id
def write_event(self, event):
"""
Write one event into database queue.
Trigger notify event if `refresh_global_timeline` is enabled.
:param dict event: Event data
Example: {'timestamp': <datetime>, 'event': 'wiki_create',
'project': 'bestproject', 'username': '******', 'forum_id': '35'}
"""
# use this server's datetime as timestamp, UTC
curdate = datetime.datetime.utcnow()
event['timestamp'] = curdate.isoformat()
# generate JSON from dict
json_data = json.dumps(event)
with analytical_transaction() as cursor:
cursor.execute('INSERT INTO `data_queue` (`data`) VALUES (%s)', json_data)
# Refresh global timeline if needed
is_home_env = event['project'] == 'home'
is_page_view = event['event'] == 'page_request'
is_enabled = conf.refresh_global_timeline
if is_enabled and not (is_home_env or is_page_view):
_notify_event_occurred(event['project'], event['event'])
def write_event(self, event):
"""
Write one event into database queue.
Trigger notify event if `refresh_global_timeline` is enabled.
:param dict event: Event data
Example: {'timestamp': <datetime>, 'event': 'wiki_create',
'project': 'bestproject', 'username': '******', 'forum_id': '35'}
"""
# use this server's datetime as timestamp, UTC
curdate = datetime.datetime.utcnow()
event['timestamp'] = curdate.isoformat()
# generate JSON from dict
json_data = json.dumps(event)
with analytical_transaction() as cursor:
cursor.execute('INSERT INTO `data_queue` (`data`) VALUES (%s)',
json_data)
# Refresh global timeline if needed
is_home_env = event['project'] == 'home'
is_page_view = event['event'] == 'page_request'
is_enabled = conf.refresh_global_timeline
if is_enabled and not (is_home_env or is_page_view):
_notify_event_occurred(event['project'], event['event'])
def consume_queue(self):
"""
Iterates over queued unprocessed json data.
Deletes all iterated rows.
"""
with analytical_transaction() as cursor:
cursor.execute('SELECT `id`, `data` FROM `data_queue`')
rows = cursor.fetchall()
for row in rows:
cursor.execute('DELETE FROM data_queue WHERE id = %s', row[0])
yield json.loads(row[1])
def consume_queue(self):
"""
Iterates over queued unprocessed json data.
Deletes all iterated rows.
"""
with analytical_transaction() as cursor:
cursor.execute('SELECT `id`, `data` FROM `data_queue`')
rows = cursor.fetchall()
for row in rows:
cursor.execute('DELETE FROM data_queue WHERE id = %s', row[0])
yield json.loads(row[1])
def new(self, project_identifier, forum_id):
dibo = self.from_operational(project_identifier, forum_id)
if not dibo:
return None
dibo['now'] = now_str()
dibo_insert = """
INSERT INTO discussion_dim(discussion_sk,
discussion_name,
author,
moderators,
subscribers,
subject,
description,
forum_key,
project_key,
project_name,
project_identifier,
VALID_FROM)
VALUES(null,
'%(discussion_name)s',
'%(author)s',
'%(moderators)s',
'%(subscribers)s',
'%(subject)s',
'%(description)s',
%(forum_key)d,
%(project_key)d,
'%(project_name)s',
'%(project_identifier)s',
'%(now)s')""" % dibo
invalidate_old = """
UPDATE discussion_dim SET VALID_TO = '%(now)s'
WHERE project_key = %(project_key)d AND forum_key = %(forum_key)d
AND VALID_TO IS NULL""" % dibo
with analytical_transaction() as cursor:
try:
cursor.execute(invalidate_old)
cursor.execute(dibo_insert)
except:
conf.log.exception(
"Failed creating a new record to discussion dimension. %s"
% str(dibo))
raise
return self.get_active_discussion_sk(project_identifier, forum_id)
def new(self, project_identifier, forum_id):
dibo = self.from_operational(project_identifier, forum_id)
if not dibo:
return None
dibo['now'] = now_str()
dibo_insert = """
INSERT INTO discussion_dim(discussion_sk,
discussion_name,
author,
moderators,
subscribers,
subject,
description,
forum_key,
project_key,
project_name,
project_identifier,
VALID_FROM)
VALUES(null,
'%(discussion_name)s',
'%(author)s',
'%(moderators)s',
'%(subscribers)s',
'%(subject)s',
'%(description)s',
%(forum_key)d,
%(project_key)d,
'%(project_name)s',
'%(project_identifier)s',
'%(now)s')""" % dibo
invalidate_old = """
UPDATE discussion_dim SET VALID_TO = '%(now)s'
WHERE project_key = %(project_key)d AND forum_key = %(forum_key)d
AND VALID_TO IS NULL""" % dibo
with analytical_transaction() as cursor:
try:
cursor.execute(invalidate_old)
cursor.execute(dibo_insert)
except:
conf.log.exception("Failed creating a new record to discussion dimension. %s" % str(dibo))
raise
return self.get_active_discussion_sk(project_identifier, forum_id)
def new(self, username):
""" Create new dimension record from user
"""
user = self.from_operational(username)
if not user:
return None
# Current time for user
user['now'] = now_str()
user_insert = """
INSERT INTO user_dim(user_sk,
username,
mail,
mobile,
givenName,
lastName,
authentication,
status,
user_key,
VALID_FROM)
VALUES(null,
'%(username)s',
'%(mail)s',
'%(mobile)s',
'%(givenName)s',
'%(lastName)s',
'%(authentication)s',
'%(status)s',
%(user_key)d,
'%(now)s')""" % user
invalidate_old = """
UPDATE user_dim SET VALID_TO = '%(now)s'
WHERE username = '******' AND VALID_TO IS NULL""" % user
with analytical_transaction() as cursor:
try:
cursor.execute(invalidate_old)
cursor.execute(user_insert)
except:
conf.log.exception(
"Failed creating a new user record to dimension. %s" %
str(user))
raise
return self.get_active_user_sk(username)
def drop_aged_partitions(self, table_plan):
"""
Drop aged partitions from the end so that max_partitions rule holds
"""
partitions_to_drop = len(table_plan.partitions) - table_plan.max_partitions
# Sanity check calculation and then run
if 0 < partitions_to_drop < table_plan.partitions:
with analytical_transaction() as cursor:
for partition in table_plan.partitions[:partitions_to_drop]:
partition_name = 'p_%s' % partition
drop_clause = "ALTER TABLE %s DROP PARTITION %s" % (table_plan.table, partition_name)
try:
cursor.execute(drop_clause)
except:
conf.log.exception("Unable to drop partition from table. %s" % drop_clause)
conf.log.info("Dropped partitions for %s" % table_plan.table)
def new(self, identifier):
""" Create new dimension record from project
"""
project = self.from_operational(identifier)
if not project:
return None
# Current time for project
project['now'] = now_str()
project_insert = """
INSERT INTO project_dim(project_sk,
identifier,
project_name,
author,
created,
updated,
published,
project_key,
VALID_FROM)
VALUES(null,
'%(identifier)s',
'%(project_name)s',
'%(author)s',
'%(created)s',
'%(updated)s',
'%(published)s',
'%(project_key)s',
'%(now)s')""" % project
invalidate_old = """
UPDATE project_dim SET VALID_TO = '%(now)s'
WHERE identifier = '%(identifier)s' AND VALID_TO IS NULL""" % project
with analytical_transaction() as cursor:
try:
cursor.execute(invalidate_old)
cursor.execute(project_insert)
except Exception:
conf.log.exception(
"Failed creating a new project record to dimension. insert: %s\nupdate:%s"
% (project_insert, invalidate_old))
raise
return self.get_active_project_sk(identifier)
def new(self, username):
""" Create new dimension record from user
"""
user = self.from_operational(username)
if not user:
return None
# Current time for user
user['now'] = now_str()
user_insert = """
INSERT INTO user_dim(user_sk,
username,
mail,
mobile,
givenName,
lastName,
authentication,
status,
user_key,
VALID_FROM)
VALUES(null,
'%(username)s',
'%(mail)s',
'%(mobile)s',
'%(givenName)s',
'%(lastName)s',
'%(authentication)s',
'%(status)s',
%(user_key)d,
'%(now)s')""" % user
invalidate_old = """
UPDATE user_dim SET VALID_TO = '%(now)s'
WHERE username = '******' AND VALID_TO IS NULL""" % user
with analytical_transaction() as cursor:
try:
cursor.execute(invalidate_old)
cursor.execute(user_insert)
except:
conf.log.exception("Failed creating a new user record to dimension. %s" % str(user))
raise
return self.get_active_user_sk(username)
def new(self, identifier):
""" Create new dimension record from project
"""
project = self.from_operational(identifier)
if not project:
return None
# Current time for project
project['now'] = now_str()
project_insert = """
INSERT INTO project_dim(project_sk,
identifier,
project_name,
author,
created,
updated,
published,
project_key,
VALID_FROM)
VALUES(null,
'%(identifier)s',
'%(project_name)s',
'%(author)s',
'%(created)s',
'%(updated)s',
'%(published)s',
'%(project_key)s',
'%(now)s')""" % project
invalidate_old = """
UPDATE project_dim SET VALID_TO = '%(now)s'
WHERE identifier = '%(identifier)s' AND VALID_TO IS NULL""" % project
with analytical_transaction() as cursor:
try:
cursor.execute(invalidate_old)
cursor.execute(project_insert)
except Exception:
conf.log.exception("Failed creating a new project record to dimension. insert: %s\nupdate:%s" %
(project_insert, invalidate_old))
raise
return self.get_active_project_sk(identifier)
def load_sql(self, sql_rows):
"""
Helper method to be used for loading
sql into analytical database.
If sql fails we write sqldump
"""
with analytical_transaction() as cursor:
try:
for row in sql_rows:
cursor.execute(row)
except:
conf.log.exception("Doing sql dump failed.")
self.dump_sql("\n".join(sql_rows))
# Used to return false here prior to rollback, but now we need to raise
# the exception for the rollback to be called.
raise
return True
def load_sql(self, sql_rows):
"""
Helper method to be used for loading
sql into analytical database.
If sql fails we write sqldump
"""
with analytical_transaction() as cursor:
try:
for row in sql_rows:
cursor.execute(row)
except:
conf.log.exception("Doing sql dump failed.")
self.dump_sql("\n".join(sql_rows))
# Used to return false here prior to rollback, but now we need to raise
# the exception for the rollback to be called.
raise
return True
def drop_aged_partitions(self, table_plan):
"""
Drop aged partitions from the end so that max_partitions rule holds
"""
partitions_to_drop = len(
table_plan.partitions) - table_plan.max_partitions
# Sanity check calculation and then run
if 0 < partitions_to_drop < table_plan.partitions:
with analytical_transaction() as cursor:
for partition in table_plan.partitions[:partitions_to_drop]:
partition_name = 'p_%s' % partition
drop_clause = "ALTER TABLE %s DROP PARTITION %s" % (
table_plan.table, partition_name)
try:
cursor.execute(drop_clause)
except:
conf.log.exception(
"Unable to drop partition from table. %s" %
drop_clause)
conf.log.info("Dropped partitions for %s" %
table_plan.table)
def run_in_analytical_db(self, sql):
try:
with analytical_transaction() as cursor:
cursor.execute(sql)
except:
conf.log.exception("Summarizing failed with sql: %s" % sql)
def run_in_analytical_db(self, sql):
try:
with analytical_transaction() as cursor:
cursor.execute(sql)
except:
conf.log.exception("Summarizing failed with sql: %s" % sql)
