def test_update_last_file():
conn = connect.connect()
cur = conn.cursor()
cur.execute("SELECT * FROM file_list;")
old_results = cur.fetchall()
db.update_last_file('001002004', '2020-01-01T07:14:05Z', 'hour', False)
conn = connect.connect()
cur = conn.cursor()
cur.execute("SELECT * FROM file_list;")
new_results = cur.fetchall()
assert len(new_results) == 1
assert old_results != new_results
def history_all_changesets(action):
conn = connect.connect()
cur = conn.cursor()
if action in ['create', 'c']:
cur.execute("""
CREATE TABLE history_all_changesets (
id SERIAL NOT NULL PRIMARY KEY,
changeset TEXT NOT NULL,
username TEXT NOT NULL,
timestamp TEXT NOT NULL,
created TEXT,
modified TEXT,
deleted TEXT
);
""")
elif action in ['truncate', 't']:
cur.execute("""
TRUNCATE TABLE history_all_changesets;
""")
elif action in ['drop', 'delete', 'd']:
cur.execute("""
DROP TABLE history_all_changesets;
""")
else:
raise NotImplementedError(error_message)
conn.commit()
def watched_keys(action):
conn = connect.connect()
cur = conn.cursor()
if action in ['create', 'c']:
cur.execute("""
CREATE TABLE watched_keys (
id SERIAL NOT NULL PRIMARY KEY,
key TEXT NOT NULL,
value TEXT NOT NULL,
reason TEXT,
author TEXT,
authorid BIGINT,
email TEXT
);
""")
elif action in ['truncate', 't']:
cur.execute("""
TRUNCATE TABLE watched_keys;
""")
elif action in ['drop', 'delete', 'd']:
cur.execute("""
DROP TABLE watched_keys;
""")
else:
raise NotImplementedError(error_message)
conn.commit()
def add_watched_object(element,
reason=None,
author=None,
authorid=None,
email=None):
"""
Add object to watched object list.
Inputs
------
element : str
Object to track, with type specified as single letter
prepended to object id (e.g. node 322 is 'n322')
reason : str, optional
Reason to track user
author : str, optional
User adding tracking entry
authorid : int, optional
Userid of user adding entry
email : str, optional
Email address for notification of events
"""
conn = connect.connect()
cur = conn.cursor()
info = (element, reason, author, authorid, email)
cur.execute(
"""INSERT INTO watched_objects
(element, reason, author, authorid, email)
VALUES (%s, %s, %s, %s, %s);""", info)
conn.commit()
def history_keys(action):
conn = connect.connect()
cur = conn.cursor()
if action in ['create', 'c']:
cur.execute("""
CREATE TABLE history_keys (
id SERIAL NOT NULL PRIMARY KEY,
wid INTEGER NOT NULL,
userid BIGINT NOT NULL,
key TEXT NOT NULL,
value TEXT NOT NULL,
element TEXT NOT NULL,
username TEXT NOT NULL,
changeset BIGINT NOT NULL,
timestamp TEXT NOT NULL,
action SMALLINT NOT NULL
);
""")
elif action in ['truncate', 't']:
cur.execute("""
TRUNCATE TABLE history_keys;
""")
elif action in ['drop', 'delete', 'd']:
cur.execute("""
DROP TABLE history_keys;
""")
else:
raise NotImplementedError(error_message)
conn.commit()
def add_watched_key_event(changeset, key, wid):
"""
Add watched key event.
Inputs
------
changeset : dict
Information about key event
wid : int
Watched key ID
"""
conn = connect.connect()
cur = conn.cursor()
info = (wid, changeset['id'], changeset['timestamp'],
changeset['username'].encode('utf8'), changeset['uid'],
changeset['action'], key, changeset['tags'][key],
changeset['changeset'])
cur.execute(
"""INSERT INTO history_keys
(wid, element, timestamp, username, userid, action, key,
value, changeset) VALUES
(%s, %s, %s, %s, %s, %s, %s, %s, %s);""", info)
conn.commit()
def add_watched_user_object(username,
reason=None,
author=None,
authorid=None,
email=None):
"""
Add user to watched user list with object composites for tracking.
Inputs
------
username : str
Username to track
reason : str, optional
Reason to track user
author : str, optional
User adding tracking entry
authorid : int, optional
Userid of user adding entry
email : str, optional
Email address for notification of events
"""
conn = connect.connect()
cur = conn.cursor()
info = (username, reason, author, authorid, email)
cur.execute(
"""INSERT INTO watched_users_objects
(username, reason, author, authorid, email)
VALUES (%s, %s, %s, %s, %s);""", info)
conn.commit()
def history_filters(action):
conn = connect.connect()
cur = conn.cursor()
if action in ['create', 'c']:
cur.execute("""
CREATE TABLE history_filters (
id SERIAL NOT NULL PRIMARY KEY,
flag INT NOT NULL,
username TEXT NOT NULL,
changeset BIGINT NOT NULL,
timestamp TEXT NOT NULL,
quantity TEXT NOT NULL
);
""")
elif action in ['truncate', 't']:
cur.execute("""
TRUNCATE TABLE history_filters;
""")
elif action in ['drop', 'delete', 'd']:
cur.execute("""
DROP TABLE history_filters;
""")
else:
raise NotImplementedError(error_message)
conn.commit()
def user_object_filter(objects,
notification=False,
notifier=send_notification.basic_send_mail):
notify_list = []
watched_users = queries.query_user_object_list()
conn = connect.connect()
cur = conn.cursor()
if watched_users:
for user in watched_users:
for item_id, item in objects.iteritems():
if fnmatch.fnmatch(item['username'].encode('utf-8'),
user['username']):
if item['create'] == 1:
action = 'create'
elif item['modify'] == 1:
action = 'modify'
elif item['delete'] == 1:
action = 'delete'
for item_key in item['tags']:
info = (item['timestamp'], item['changeset'],
item['username'].encode('utf8'), action,
item_key, item['tags'][item_key])
cur.execute(
"""INSERT INTO history_users_objects
(timestamp,changeset,username,action,key,value)
VALUES (%s, %s, %s, %s, %s, %s);""", info)
conn.commit()
def update_last_file(sequence, timestamp, timetype, read):
"""
Update information about the last state file seen.
Inputs
------
sequence : int
Sequence number of state file
timestamp : str
Stringified timestamp from file
timetype : str
Time resolution of state file
read : bool
Flag indicating if file has been read or not
"""
conn = connect.connect()
cur = conn.cursor()
cur.execute(
"""UPDATE file_list SET
(sequence, timestamp, timetype, read)
= (%s, %s, %s, %s);""",
(sequence, timestamp, timetype, read))
conn.commit()
def add_whitelisted_user(username, reason=None, author=None, authorid=None):
"""
Add whitelisted user that is not picked up in tracking.
Inputs
------
username : str
Username to track
reason : str, optional
Reason to track user
author : str, optional
User adding tracking entry
authorid : int, optional
Userid of user adding entry
"""
conn = connect.connect()
cur = conn.cursor()
info = (username, reason, author, authorid)
cur.execute(
"""INSERT INTO whitelisted_users
(username, reason, author, authorid)
VALUES (%s, %s, %s, %s);""", info)
conn.commit()
def test_remove_watched_user_by_wrong_authorid():
db.remove_watched_user('testuser', 1)
conn = connect.connect()
cur = conn.cursor()
cur.execute("SELECT * FROM watched_users;")
results = cur.fetchall()
assert len(results) == 2
def test_remove_watched_object_by_authorid():
db.remove_watched_object('n322', 2)
conn = connect.connect()
cur = conn.cursor()
cur.execute("SELECT * FROM watched_objects;")
results = cur.fetchall()
assert len(results) == 1
def test_add_watched_object_minimal_details():
db.add_watched_object('n323')
conn = connect.connect()
cur = conn.cursor()
cur.execute("SELECT * FROM watched_objects;")
results = cur.fetchall()
assert len(results) == 2
def test_add_watched_object():
db.add_watched_object('n322', 'Because', 'testadmin', 2, '*****@*****.**')
conn = connect.connect()
cur = conn.cursor()
cur.execute("SELECT * FROM watched_objects;")
results = cur.fetchall()
assert len(results) == 1
def test_remove_last_file():
db.remove_last_file()
conn = connect.connect()
cur = conn.cursor()
cur.execute("SELECT * FROM file_list;")
results = cur.fetchall()
assert len(results) == 0
def test_add_watched_key_minimal_details():
db.add_watched_key('railway', 'rail')
conn = connect.connect()
cur = conn.cursor()
cur.execute("SELECT * FROM watched_keys;")
results = cur.fetchall()
assert len(results) == 2
def test_add_last_file():
db.add_last_file('001002003', '2020-01-01T06:14:01Z', 'hour', False)
conn = connect.connect()
cur = conn.cursor()
cur.execute("SELECT * FROM file_list;")
results = cur.fetchall()
assert len(results) == 1
def test_remove_watched_key_by_authorid():
db.remove_watched_key('railway', 'abandoned', 3)
conn = connect.connect()
cur = conn.cursor()
cur.execute("SELECT * FROM watched_keys;")
results = cur.fetchall()
assert len(results) == 1
def test_add_watched_key():
db.add_watched_key('railway', 'abandoned', 'Because', 'testnonadmin', 3,
'*****@*****.**')
conn = connect.connect()
cur = conn.cursor()
cur.execute("SELECT * FROM watched_keys;")
results = cur.fetchall()
assert len(results) == 1
def get_last_file():
"""
Retrieve information about the last state file seen.
"""
conn = connect.connect()
cur = conn.cursor()
cur.execute("SELECT * FROM file_list;")
return cur.fetchone()
def remove_last_file():
"""
Remove the last file information.
"""
conn = connect.connect()
cur = conn.cursor()
cur.execute("DELETE FROM file_list;")
conn.commit()
def query_key_list():
"""
Retrieve list of key/value pairs on key/value pair list.
"""
conn = connect.connect()
cur = conn.cursor()
cur.execute("SELECT * FROM watched_keys")
watched_keys = cur.fetchall()
return watched_keys
def query_object_list():
"""
Retrieve list of objects on object watch list.
"""
conn = connect.connect()
cur = conn.cursor()
cur.execute("SELECT * FROM watched_objects")
watched_objects = cur.fetchall()
return watched_objects
def query_user_object_list():
"""
Retrieve list of users on object composite watch list.
"""
conn = connect.connect()
cur = conn.cursor()
cur.execute("SELECT * FROM watched_users_objects")
watched_users = cur.fetchall()
return watched_users
def test_get_last_file():
db_results = db.get_last_file()
# get_last_file returns a row or None
assert db_results != None
conn = connect.connect()
cur = conn.cursor()
cur.execute("SELECT * FROM file_list;")
# Use first row to match get_last_file behavior
test_results = cur.fetchall()[0]
assert db_results == test_results
def query_white_list():
"""
Retrieve list of users on whitelist.
"""
conn = connect.connect()
cur = conn.cursor()
cur.execute("SELECT username FROM whitelisted_users")
white_list = cur.fetchall()
whitelist = [name[0] for name in white_list]
return white_list
def insert_all_changesets(changesets):
conn = connect.connect()
cur = conn.cursor()
for changesetid, changeset in changesets.iteritems():
info = (changesetid, changeset["username"], changeset["timestamp"],
changeset["create"], changeset["modify"], changeset["delete"])
cur.execute(
"""INSERT INTO history_all_changesets
(changeset, username, timestamp, created, modified, deleted)
VALUES (%s, %s, %s, %s, %s, %s);""", info)
conn.commit()
def insert_all_users(users):
conn = connect.connect()
cur = conn.cursor()
for username, user in users.iteritems():
info = (username, user["changesets"], user["timestamps"],
user["action"]["create"], user["action"]["modify"],
user["action"]["delete"])
cur.execute(
"""INSERT INTO history_all_users
(username, changeset, timestamp, created, modified, deleted)
VALUES (%s, %s, %s, %s, %s, %s);""", info)
conn.commit()
def key_filter(objects,
notification=False,
notifier=send_notification.basic_send_mail):
notify_list = []
watched_keys = queries.query_key_list()
conn = connect.connect()
cur = conn.cursor()
if watched_keys:
for key in watched_keys:
for item_id, item in objects.iteritems():
for item_key in item['tags']:
if fnmatch.fnmatch(
item_key, key['key']) and fnmatch.fnmatch(
item['tags'][item_key], key['value']):
if item['create'] == 1:
item['action'] = 1
elif item['modify'] == 1:
item['action'] = 2
elif item['delete'] == 1:
item['action'] = 4
db.add_watched_key_event(item, item_key, key['id'])
notify_list.append({
'timestamp':
item['timestamp'],
'changesetid':
item['changeset'],
'username':
item['username'].encode('utf8'),
'action':
item['action'],
'key':
item_key,
'value':
item['tags'][item_key],
'author':
item['author'],
'address':
item['email'],
'reason':
item['reason']
})
if notify_list and notification:
send_notification.send_notification(notify_list,
'key',
notifier=notifier)