def test_get_latest_post_data_no_results(self):
cur = self.test_db.cursor()
cur.execute("DELETE from attendance")
cur.execute("DELETE from posts")
dbutils.commit_or_rollback(self.test_db)
result = self.bot.get_latest_post_data()
self.assertIsNone(result)
def update_members(self):
res = self.client.api_call("users.list")
members = res["members"]
cur = self.db.cursor()
current_member_data = []
ids_for_deletion = []
for member in members:
if not member["deleted"]:
slack_id = member["id"]
real_name = member["real_name"]
current_member_data.append({
"id": slack_id,
"realname": real_name
})
else:
ids_for_deletion.append((member["id"], ))
insertion_query = (
"INSERT INTO members VALUES(%(id)s, %(realname)s, FALSE) "
"ON CONFLICT (slack_id) DO UPDATE "
"SET real_name = %(realname)s "
"WHERE members.slack_id = %(id)s")
cur.executemany(insertion_query, current_member_data)
cur.executemany("DELETE FROM members WHERE slack_id = (%s)",
ids_for_deletion)
dbutils.commit_or_rollback(self.db)
def update_attendance_table(self, timestamp):
query = (
"INSERT INTO attendance(slack_id, post_timestamp)"
"SELECT slack_id, (%s) FROM Members WHERE ignore = FALSE ON CONFLICT DO NOTHING"
)
cur = self.db.cursor()
cur.execute(query, (timestamp, ))
dbutils.commit_or_rollback(self.db)
def post_message(self, message):
res = self.client.api_call("chat.postMessage",
channel=self.channel,
text=message,
username=self.bot_name,
icon_emoji=self.bot_emoji)
ts = res.get("ts")
channel_id = res.get("channel")
post_date = datetime.fromtimestamp(float(ts)).strftime("%d/%m/%y")
self.db.cursor().execute(
"INSERT INTO posts VALUES(%s, %s, %s) ON CONFLICT DO NOTHING",
(ts, post_date, channel_id))
dbutils.commit_or_rollback(self.db)
return [ts, channel_id]
def test_process_attendance_ignores_correctly(self, mock_get_reactions, mock_api_call, ):
expected_value = [(None,), (True,), (True,), (True,)]
mock_api_call.return_value = {"members": [{"id": "23456", "real_name": "Bob Loblaw", "deleted": False},
{"id": "34567", "real_name": "Michael Bluth", "deleted": False},
{"id": "101011", "real_name": "GOB Bluth", "deleted": True}]}
mock_get_reactions.return_value = [{"name": "thumbsup", "users": ["12345", "23456", "45678"]},
{"name": "thumbsdown", "users": ["34567"]}]
cur = self.test_db.cursor()
cur.execute("insert into members values ('23456', 'Tobias Funke', FALSE),('34567', 'GOB Bluth', TRUE),"
"('45678', 'Buster Bluth', FALSE), ('56789', 'George Michael Bluth', FALSE)")
dbutils.commit_or_rollback(self.test_db)
self.bot.process_attendance()
cur.execute("select present from attendance where post_timestamp = '1477908000'")
result = cur.fetchall()
self.assertEqual(result, expected_value)
def set_up_db_for_absence_tests(self):
cur = self.test_db.cursor()
cur.execute("insert into members values ('23456', 'Tobias Funke'),('34567', 'GOB Bluth'),"
"('45678', 'Buster Bluth'), ('56789', 'George Michael Bluth')")
cur.executemany("INSERT INTO Posts VALUES(%s, %s, %s)", (("1478908000", "01/11/16", "abc123"),
("1479908000", "10/11/16", "abc123"),
("1487908000", "15/11/16", "abc123"),
("1497908000", "25/11/16", "abc123")))
query = ("insert into attendance(slack_id, post_timestamp) select m.slack_id, "
"p.post_timestamp from members as m, posts as p on conflict do nothing")
cur.execute(query)
cur.executemany("UPDATE attendance SET present=(%s) WHERE slack_id=(%s) AND post_timestamp=(%s)",
((True, '12345', '1477908000'), (True, '34567', '1477908000'), (True, '45678', '1477908000'),
(False, '56789', '1477908000'),
(True, '12345', '1478908000'), (False, '34567', '1478908000'), (True, '56789', '1478908000'),
(False, '12345', '1479908000'), (True, '34567', '1479908000'), (False, '56789', '1479908000'),
(True, '34567', '1487908000'), (False, '56789', '1487908000'),
(True, '12345', '1497908000'), (True, '34567', '1497908000'), (False, '56789', '1497908000')))
dbutils.commit_or_rollback(self.test_db)
def create_tables(self):
cur = self.db.cursor()
members_query = ("CREATE TABLE IF NOT EXISTS members"
"(slack_id varchar(255) PRIMARY KEY, "
"real_name varchar(255) NOT NULL,"
"ignore boolean)")
posts_query = ("CREATE TABLE IF NOT EXISTS posts"
"(post_timestamp varchar(255) PRIMARY KEY, "
"rehearsal_date varchar(255) UNIQUE NOT NULL, "
"channel_id varchar(255) NOT NULL)")
attendance_query = (
"CREATE TABLE IF NOT EXISTS attendance"
"(slack_id varchar(255) REFERENCES members(slack_id) ON DELETE CASCADE, "
"post_timestamp varchar(255) REFERENCES posts(post_timestamp), "
"present boolean, "
"PRIMARY KEY (slack_id, post_timestamp))")
cur.execute(members_query)
cur.execute(posts_query)
cur.execute(attendance_query)
dbutils.commit_or_rollback(self.db)
def tearDownClass(cls):
cls.bot.db.close()
cur = cls.test_db.cursor()
cur.execute("DROP TABLE Members, Posts, Attendance")
dbutils.commit_or_rollback(cls.test_db)
cls.test_db.close()
def setUp(self):
self.test_db.cursor().execute("INSERT INTO Members VALUES(%s, %s)", ("12345", "Bobby Tables"))
self.test_db.cursor().execute("INSERT INTO Posts VALUES(%s, %s, %s)", ("1477908000", "31/10/16", "abc123"))
self.test_db.cursor().execute("INSERT INTO Attendance(slack_id, post_timestamp) VALUES(%s, %s)",
("12345", "1477908000"))
dbutils.commit_or_rollback(self.test_db)