def get_user_suspended(com):
"""
Checks if user is suspended from a community
Args:
com : The community being checked
Returns:
A list of suspended users
"""
conn = connect()
cur = conn.cursor()
all_suspended_users = []
cur.execute(
"""SELECT community_suspended_users FROM community WHERE community_name = '%s'"""
% (com))
results = cur.fetchall()
st = [item[0] for item in results]
sus_user = st[0].split(",")
for sender in sus_user:
if (sender not in all_suspended_users and sender != ''):
all_suspended_users.append(sender)
conn.close()
return all_suspended_users
def test_egdb3_sus_and_send(self):
suspend_community('Suspend', 'LexFromTex', 'Metropolis')
with self.assertRaises(Exception) as context:
send_community_message('LexFromTex', 'Metropolis', '#DailyPlanet',
'This planet blows, Im moving to Mars!')
self.assertTrue("""User is a suspended member of this channel""" in
str(context.exception))
community_membership('Join', 'LexFromTex', 'Comedy')
send_community_message(
'LexFromTex', 'Comedy', '#Dialogs',
'I got suspended from Metropolis and I think its cuz clark likes being alone'
)
conn = connect()
cur = conn.cursor()
cur.execute(
"""SELECT sender FROM community_channels WHERE chan_message = '%s'"""
%
('I got suspended from Metropolis and I think its cuz clark likes being alone'
))
results = cur.fetchall()
st = [item[0] for item in results]
self.assertEqual('LexFromTex', st[0],
'Message was not sent in either channel')
conn.close()
def test_ebdb3_create_user(self):
#Using LexFromTex instead of Lex because of 8 character min on username creation
conn = connect()
cur = conn.cursor()
create_user('9', 'LexFromTex', '*****@*****.**',
'045-125-1622', '845-023-1252')
cur.execute("""SELECT COUNT(*) FROM people""")
results = cur.fetchall()
st = [item[0] for item in results]
self.assertEqual(
9, st[0],
"Incorrect Number of People within Database: LexFromTex was not created"
)
#Lex and Moe have a quick conversation
send_new_message('Hey Moe, Im new here', 'Moe', 'LexFromTex',
'2-23-2021')
send_new_message('Welcome to the community!', 'LexFromTex', 'Moe',
'2-23-2021')
send_new_message('Thanks! Glad to be here', 'Moe', 'LexFromTex',
'2-23-2021')
send_new_message('Let me know if you have any questions', 'LexFromTex',
'Moe', '2-23-2021')
send_new_message('I will definitely reach out', 'Moe', 'LexFromTex',
'2-23-2021')
conn.close()
def search_community_message(community, mes):
"""
Searches a community for a given message
Args:
community : the community to search in
mes : the message string that is being searched for
Returns:
Returns all messages with the string within
"""
conn = connect()
cur = conn.cursor()
all_words = mes.split(" ")
word_string = all_words[0]
if (len(all_words) > 1):
for row_word in all_words[1:]:
word_string = word_string + """ & """ + row_word
cur.execute(
"""SELECT chan_message FROM community_channels WHERE to_tsvector(chan_message) @@ to_tsquery('%s')"""
% (word_string))
results = cur.fetchall()
st = [item[0] for item in results]
conn.close()
return st
def populate_tables_db1():
conn = connect()
cur = conn.cursor()
# check why this is necessary in CI
rebuild_tables()
add_users = """
INSERT INTO users(user_id, name, phone_number, email,
userid_set, userid_reset, suspended_since, suspended_till) VALUES
('Abbott1234', 'Abbott', '1234567890', '*****@*****.**', '1990-01-01 00:00:00',
NULL, NULL, NULL),
('Costello1234', 'Costello', '1234567891', '*****@*****.**', '1990-01-01 00:00:00',
NULL, NULL, NULL),
('Moe1234', 'Moe', '1234567892', '*****@*****.**', '1990-01-01 00:00:00', NULL, NULL, NULL),
('Larry1234', 'Larry', '1234567893', '*****@*****.**',
'1989-01-01 00:00:00', NULL, '1990-01-01 00:00:00', '2060-01-01 00:00:00'),
('Curly1234', 'Curly', '1234567894', '*****@*****.**',
'1989-01-01 00:00:00', NULL, '1990-01-01 00:00:00', '2000-01-01 00:00:00');
"""
cur.execute(add_users)
add_messages = """
INSERT INTO direct_messages(message_id, sender_id, receiver_id, time_sent, message, is_read) VALUES
(1, 'Abbott1234', 'Costello1234', '2000-02-12 11:00:00', 'C! How are you?', TRUE),
(2, 'Costello1234', 'Abbott1234', '2000-02-12 12:00:40', 'Hey A! Fine. You?', TRUE),
(3, 'Moe1234', 'Larry1234', '1995-02-12 11:00:00', 'How are you Larry?', TRUE),
(4, 'Larry1234', 'Moe1234', '1995-02-12 12:00:40', 'Fine, Moe! You?', TRUE),
(5, 'Moe1234', 'Larry1234', '1922-02-12 12:00:40', 'I got fired.', FALSE),
(6, 'Costello1234', 'Abbott1234', '2020-02-12 11:00:00',
'Abbott! So long. How are you?', FALSE),
(7, 'Moe1234', 'Abbott1234', '2020-02-12 11:10:00', 'Abbott, this is Moe. Hi!', FALSE);
"""
cur.execute(add_messages)
conn.commit()
conn.close()
def check_user_change(username, date):
"""
Checks if a username has been changed in the last 6 months
Args:
username : The user we are checking for a change
Returned:
An integer where 0 represents no and 1 represents yes
"""
conn = connect()
cur = conn.cursor()
cur.execute(
"""SELECT people.username_change FROM people WHERE people.username = '******'"""
% (username))
results = cur.fetchall()
st = [item[0] for item in results]
chDate = st[0].split('-')
currDate = date.split('-')
conn.close()
monthDifference = ((int(currDate[2]) - int(chDate[2])) * 12) + int(
currDate[0]) - int(chDate[0])
if (abs(monthDifference) < 6):
#print("Less tha 6 months")
return 1
elif (abs(monthDifference) == 6 and int(currDate[1]) < int(chDate[1])):
#print("6months not days tho")
return 1
#print("More than 6 months")
return 0
def account_suspension(action, userID, endDate="NULL", endYear="NULL"):
"""
Dependent upon the action, a user can be either suspended or cleared from suspension
Args:
action : Can be either 'Suspend' or 'Clear'. Determines action of this method
userID : The id of the user associated with the action
endDate : If the action is 'Suspend' the endDate is the date the suspension ends, NULL otherwise
endYear : If the action is 'Suspend' the endYear is the year the suspension ends, NULL otherwise
Returned:
None
"""
conn = connect()
cur = conn.cursor()
if (action == "Suspend"):
datetimeObj = datetime.now()
startYear = str(datetimeObj.year)
time, start = getTimeAndDate()
cur.execute(
"INSERT INTO suspension (userID, start, expiration, startYear, endYear) VALUES (%s, %s, %s, %s, %s);",
(userID, start, endDate, startYear, endYear))
if (action == "Clear"):
cur.execute("""DELETE FROM suspension WHERE userID = '%s'""" %
(userID))
conn.commit()
conn.close()
def create_user(new_id, username, email, ssn, phone):
"""
Creates a new user with the given arguments
Args:
new_id : The ID of the new user
username : The username of the new user
email : The email of the new user
ssn : the social security number of the new user
phone : the phone number of the new user
Returned:
Error message if username is less than 8 characters
None if successful
"""
if (len(username) < 8):
return "ERROR: New usernames must be at least 8 characters long"
conn = connect()
cur = conn.cursor()
cur.execute(
"INSERT INTO people (ID, username, email, ssn, phone, username_change) VALUES (%s, %s, %s, %s, %s, %s);",
(new_id, username, email, ssn, phone, '00-00-0000'))
conn.commit()
conn.close()
def test_all_messages_between(self):
print("Test retrieval of all messages between two users")
conn = connect()
cur = conn.cursor()
populate_tables_db1()
cur.execute(
'SELECT direct_messages.message_id FROM direct_messages WHERE direct_messages.sender_id = \'Abbott1234\' '
'AND direct_messages.receiver_id = \'Costello1234\'')
a_to_c = len(cur.fetchall())
cur.execute(
'SELECT direct_messages.message_id FROM direct_messages WHERE direct_messages.sender_id = \'Costello1234\' '
'AND direct_messages.receiver_id = \'Abbott1234\'')
c_to_a = len(cur.fetchall())
self.assertTrue(3 == a_to_c + c_to_a,
"Abbott and Costello exchanged 3 messages")
print(
"Test retrieval of all messages between two users given a timeframe"
)
cur.execute(
'SELECT direct_messages.message_id FROM direct_messages WHERE direct_messages.sender_id = \'Moe1234\' '
'AND direct_messages.receiver_id = \'Larry1234\' AND EXTRACT(year FROM time_sent) = 1995'
)
m_to_l = len(cur.fetchall())
cur.execute(
'SELECT direct_messages.message_id FROM direct_messages WHERE direct_messages.sender_id = \'Larry1234\' '
'AND direct_messages.receiver_id = \'Moe1234\' AND EXTRACT(year FROM time_sent) = 1995'
)
l_to_m = len(cur.fetchall())
self.assertTrue(2 == m_to_l + l_to_m,
"Moe and Larry exchanged 2 messages in 1995")
def test_a_rebuild_tables(self):
"""Re-Build the tables"""
conn = connect()
cur = conn.cursor()
rebuildTables()
cur.execute('SELECT * FROM people')
self.assertEqual([], cur.fetchall(), "no rows in people")
conn.close()
def test_build_tables(self):
"""Build the tables"""
conn = connect()
cur = conn.cursor()
rebuild_tables()
cur.execute('SELECT * FROM users')
self.assertEqual([], cur.fetchall(), "no rows in users")
conn.close()
def test_b_populateTables(self):
"""Populate the tables and ensure that at least one of the tables was filled"""
populateTables()
conn = connect()
cur = conn.cursor()
cur.execute('SELECT ID FROM people')
self.assertEqual([(1, ), (2, ), (3, ), (4, ), (5, ), (6, ), (7, )],
cur.fetchall(), "Missing IDs")
conn.close()
def test_a_rebuild_tables_is_idempotent(self):
"""Drop and rebuild the tables twice"""
rebuildTables()
rebuildTables()
conn = connect()
cur = conn.cursor()
cur.execute('SELECT * FROM people')
self.assertEqual([], cur.fetchall(), "no rows in people")
conn.close()
def test_eadb3_populated_tables(self):
"""Populate the tables and ensure that at least one of the tables was filled"""
populate_community_tables()
conn = connect()
cur = conn.cursor()
cur.execute('SELECT COUNT(*) FROM community')
results = cur.fetchall()
st = [item[0] for item in results]
self.assertEqual(2, st[0], "Missing Communities")
conn.close()
def test_cdb1_total_messages(self):
"""Checks count of total messages system wide"""
conn = connect()
cur = conn.cursor()
cur.execute("""SELECT COUNT(chatID) FROM messages """)
results = cur.fetchall()
countTotalM = [item[0] for item in results]
self.assertEqual(14, countTotalM[0],
"Total chat conversations incorrect")
conn.close()
def test_cdb1_unread_to_Abbott(self):
"""Checks if amount of unread messages to Abbott are correct"""
conn = connect()
cur = conn.cursor()
cur.execute(
"""SELECT COUNT(messages) FROM messages WHERE messages.receiver = 'Abbott' AND messages.status = 'Unread' """
)
results = cur.fetchall()
countAUnread = [item[0] for item in results]
self.assertEqual(3, countAUnread[0], "Unread to Abbott incorrect")
conn.close()
def test_populate_tables(self):
print("Test that database is seeded with data without crashing")
conn = connect()
cur = conn.cursor()
# check why this is necessary in CI
populate_tables_db1()
cur.execute('SELECT * FROM users')
self.assertEqual(5, len(cur.fetchall()),
"There should be 5 rows in users table")
cur.execute('SELECT * FROM direct_messages')
self.assertEqual(7, len(cur.fetchall()),
"There should be 7 rows in messages table")
def test_cdb1_messages_Abbott_Costello(self):
"""Makes sure we can get all messages between Abbott and Costello"""
conn = connect()
cur = conn.cursor()
cur.execute(
'SELECT COUNT(messages.chatID) FROM messages WHERE messages.chatID = 1 '
)
results = cur.fetchall()
countAandCTotal = [item[0] for item in results]
self.assertEqual(
6, countAandCTotal[0],
"Incorrect number of messages between Abbott and Costello")
conn.close()
def get_count_unread_community(action, user, community='NULL', channel='NULL'):
"""
Gets a count of unread messages from a community or channel dependent upon action
Args:
action : 'Community' or 'Channel' determines where the unread messages will come from
user : The user who we are getting counts for
community : required for channel as well as community, specifies the community in which the messages are found at
channel : specifies the channel for which the counts come from
Returned:
An integer with the number of unread messages
"""
conn = connect()
cur = conn.cursor()
if (action == 'Community'):
cur.execute(
"""SELECT community.community_channels FROM community WHERE community_name = '%s'"""
% (community))
results = cur.fetchall()
st = [item[0] for item in results]
curr_com = st[0].split(',')
#print(curr_com)
total_count = 0
for row in curr_com:
cur.execute(
"""SELECT COUNT(chan_message) FROM community_channels WHERE status = 'Unread' AND community_name = '%s' AND channel_name = '%s'"""
% (community, row))
results = cur.fetchall()
xt = [item[0] for item in results]
total_count = total_count + xt[0]
conn.close()
return total_count
elif (action == 'Channel'):
cur.execute(
"""SELECT COUNT(chan_message) FROM community_channels WHERE status = 'Unread' AND community_name = '%s' AND channel_name = '%s'"""
% (community, channel))
results = cur.fetchall()
st = [item[0] for item in results]
conn.close()
return st[0]
else:
conn.close()
raise Exception("Incorrect use of function")
def test_dcdb2_username_change_Bob(self):
conn = connect()
cur = conn.cursor()
update_username('BOBbyBoy34', 'BabySteps2Door', '05-19-1991')
cur.execute(
"""SELECT people.username FROM people WHERE people.ID = '8'""")
results = cur.fetchall()
st = [item[0] for item in results]
self.assertEqual('BabySteps2Door', st[0],
"Bob was unable to change his username")
conn.close()
def test_messages_sent_by(self):
print("Test the messages sent by a user given a timeframe")
conn = connect()
cur = conn.cursor()
populate_tables_db1()
cur.execute(
'SELECT direct_messages.message_id FROM direct_messages INNER JOIN users '
'ON users.user_id = direct_messages.sender_id WHERE '
'EXTRACT( '
'year FROM direct_messages.time_sent) > 1990 AND users.name = \'Costello\''
)
message_id_list = cur.fetchall()
self.assertTrue(2 == len(message_id_list),
"Costello sent 2 messages after 1990")
def test_cdb1_messages_Moe_Larry_1995(self):
"""Makes sure we can get all messages between Moe and Larry during the year 1995"""
conn = connect()
cur = conn.cursor()
#Doesnt track 1995 yet, but will shortly
cur.execute(
'SELECT COUNT(messages.chatID) FROM messages WHERE messages.chatID = 2 '
)
results = cur.fetchall()
countMandLTotal = [item[0] for item in results]
self.assertEqual(
5, countMandLTotal[0],
"Incorrect number of messages between Abbott and Costello")
conn.close()
def community_membership(choice, user, com_name):
"""
Given choice, allows the user to join or leave a community
Args:
choice : 'Join' or 'Leave' string that determines functionality
userID : the user attempting to perform this action
com_name : the name of the community associated with this action
Returned:
None if successful
Error if unsuccessful
"""
conn = connect()
cur = conn.cursor()
if (choice == 'Join'):
cur.execute(
"""SELECT community.community_members FROM community WHERE community_name = '%s'"""
% (com_name))
results = cur.fetchall()
st = [item[0] for item in results]
curr_members = st[0].split(',')
if (user not in curr_members):
curr_members.append(user)
new_curr_members = ','.join(map(str, curr_members))
cur.execute(
"""UPDATE community SET community_members = '%s' WHERE community_name = '%s'"""
% (new_curr_members, com_name))
elif (choice == 'Leave'):
cur.execute(
"""SELECT community.community_members FROM community WHERE community_name = '%s'"""
% (com_name))
results = cur.fetchall()
st = [item[0] for item in results]
curr_members = st[0].split(',')
if (user in curr_members):
curr_members.remove(user)
new_curr_members = ','.join(map(str, curr_members))
cur.execute(
"""UPDATE community SET community_members = '%s' WHERE community_name = '%s'"""
% (new_curr_members, com_name))
else:
return 'ERROR: Incorrect Command'
conn.commit()
conn.close()
def test_unread_messages(self):
print("Test keeping track of read/unread messages")
conn = connect()
cur = conn.cursor()
populate_tables_db1()
cur.execute(
'SELECT direct_messages.message_id FROM direct_messages WHERE direct_messages.is_read = TRUE '
'AND direct_messages.receiver_id = \'Abbott1234\'')
reads = len(cur.fetchall())
cur.execute(
'SELECT direct_messages.message_id FROM direct_messages WHERE direct_messages.is_read = FALSE '
'AND direct_messages.receiver_id = \'Abbott1234\'')
unreads = len(cur.fetchall())
self.assertTrue(1 == reads, "Abbott has 1 read message")
self.assertTrue(2 == unreads, "Abbott has 2 unread messages")
def test_didb2_read_csv_into_db(self):
read_csv_into_db('whos_on_first.csv')
conn = connect()
cur = conn.cursor()
cur.execute(
"""SELECT messages.status FROM messages WHERE messages.time = '00:00'"""
)
results = cur.fetchall()
st = [item[0] for item in results]
self.assertEqual('Unread', st[0],
"CSV not loaded correctly into database")
conn.close()
def suspend_community(action, user, com_name):
"""
Suspends or removes suspension for a user from a community
Args:
action : 'Suspend' or 'Remove' determines action for function
user : The user to be suspended
com_name : the community related to the suspension
Returns:
None
"""
conn = connect()
cur = conn.cursor()
if (action == 'Suspend'):
cur.execute(
"""SELECT community.community_suspended_users FROM community WHERE community_name = '%s'"""
% (com_name))
results = cur.fetchall()
st = [item[0] for item in results]
curr_members = st[0].split(',')
if (user not in curr_members):
curr_members.append(user)
new_curr_members = ','.join(map(str, curr_members))
cur.execute(
"""UPDATE community SET community_suspended_users = '%s' WHERE community_name = '%s'"""
% (new_curr_members, com_name))
elif (action == 'Remove'):
cur.execute(
"""SELECT community.community_suspended_users FROM community WHERE community_name = '%s'"""
% (com_name))
results = cur.fetchall()
st = [item[0] for item in results]
curr_members = st[0].split(',')
if (user in curr_members):
curr_members.remove(user)
new_curr_members = ','.join(map(str, curr_members))
cur.execute(
"""UPDATE community SET community_suspended_users = '%s' WHERE community_name = '%s'"""
% (new_curr_members, com_name))
else:
raise Exception('Incorrect Usage of Function')
conn.commit()
conn.close()
def test_dbdb2_send_message_BobToMarv(self):
conn = connect()
cur = conn.cursor()
send_new_message('Im doing work, Im baby-stepping', 'DrMarvin',
'BOBbyBoy34', '05-18-1991')
cur.execute(
"""SELECT messages.sender FROM messages WHERE messages.message = 'Im doing work, Im baby-stepping'"""
)
results = cur.fetchall()
st = [item[0] for item in results]
self.assertEqual(
'BOBbyBoy34', st[0],
"Incorrect Number of People within Database: Bob was not created")
conn.close()
def test_dhdb2_break_suspension(self):
account_suspension('Clear', '4')
send_new_message('Attention! This is Larry', 'Moe', 'Larry',
'05-05-2012')
conn = connect()
cur = conn.cursor()
cur.execute(
"""SELECT messages.date FROM messages WHERE messages.message = 'Attention! This is Larry'"""
)
results = cur.fetchall()
st = [item[0] for item in results]
self.assertEqual('05-05-2012', st[0],
"User not successfully cleared of suspension")
conn.close()
def moderator_query(sDate, eDate='None'):
"""
Lists all users who have sent a message in a given date range who are currently suspended from any community
Args:
sDate : Start date of given date range
eDate : End date of given date range
Defaults to current date
Returns:
A list of users
"""
conn = connect()
cur = conn.cursor()
all_coms = get_communities()
all_suspended_users = []
mes_sent_sus_user = []
if (eDate == 'None'):
time, date = getTimeAndDate()
eDate = date
for com in all_coms:
cur.execute(
"""SELECT community_suspended_users FROM community WHERE community_name = '%s'"""
% (com))
results = cur.fetchall()
st = [item[0] for item in results]
sus_user = st[0].split(",")
for sender in sus_user:
if (sender not in all_suspended_users and sender != ''):
all_suspended_users.append(sender)
for sender in all_suspended_users:
cur.execute(
"""SELECT sender, date FROM community_channels WHERE sender = '%s'"""
% (sender))
results = cur.fetchall()
for mes in results:
if (check_date_inbetween(mes[1], sDate, eDate)
and mes[0] not in mes_sent_sus_user):
mes_sent_sus_user.append(mes[0])
conn.close()
return mes_sent_sus_user
def get_mentions(user):
conn = connect()
cur = conn.cursor()
mention_string = '@' + user
#Get a list of existing communities
cur.execute("""SELECT DISTINCT community_name FROM community""")
results = cur.fetchall()
all_comms = [item[0] for item in results]
#Check for member not within communities (Remove communities from all_coms if not a member)
for row_mem in all_comms:
cur.execute(
"""SELECT community_members FROM community WHERE community_name = '%s'"""
% (row_mem))
results = cur.fetchall()
st = [item[0] for item in results]
all_mem = st[0].split(',')
if (user not in all_mem):
all_comms.remove(row_mem)
#Get mentions
all_mentions = []
for row in all_comms:
cur.execute(
"""SELECT community_channels FROM community WHERE community_name = '%s'"""
% (row))
results = cur.fetchall()
st = [item[0] for item in results]
all_chans = st[0].split(',')
for row_chan in all_chans:
cur.execute(
"""SELECT chan_message FROM community_channels WHERE community_name = '%s' AND channel_name = '%s'"""
% (row, row_chan))
results = cur.fetchall()
all_mess = [item[0] for item in results]
for row_mess in all_mess:
if (mention_string in row_mess):
all_mentions.append(row_mess)
conn.close()
return all_mentions