def test_alerts(self):
"""Test if alert sender function sends email to subscribed users"""
with captured_output() as (out, err):
with self.app.app_context():
init_db()
get_db().executescript(_data_sql_email)
send_alerts_to_subscribed_users(self.app)
output = out.getvalue().strip()
self.assertIn('Sent email to [email protected]', output)
def test_expired_entries_cleanup(self):
"""Test if the expired entries are removed from database"""
with captured_output() as (out, err):
with self.app.app_context():
init_db()
get_db().executescript(_data_sql_expired_entry)
remove_outdated_entries(self.app)
output = out.getvalue().strip()
self.assertIn('*****@*****.**', output)
self.assertIn('AUH', output)
self.assertIn('DOH', output)
self.assertIn('2020-05-01', output)
def remove_outdated_entries(app):
print('Started cron job which cleans out of date database entries')
with app.app_context():
db = get_db()
# Select Query to check which rows will be deleted
# This is done because delete statement has no output clause and we
# need to see what all rows got deleted. https://stackoverflow.com/a/43211369
cursor = db.execute("""
SELECT *
FROM tracked_flights
WHERE
(return_date IS NULL AND date('now') > departure_date)
OR
(return_date IS NOT NULL AND date('now') > return_date)
""")
print('Deleting the following rows...')
for row in cursor.fetchall():
print(tuple(row))
# Query to delete out of date entries
db.execute("""
DELETE
FROM tracked_flights
WHERE
(return_date IS NULL AND date('now') > departure_date)
OR
(return_date is NOT NULL AND date('now') > return_date)
""")
db.commit()
print('Out of date entries deleted.')
def get_user_emails(app):
"""Returns all the user emails present in the database"""
with app.app_context():
db = get_db()
cursor = db.execute("""
SELECT DISTINCT email
FROM tracked_flights
""")
return [row[0] for row in cursor.fetchall()]
def setUp(self):
"""Method called prior each unittest execution"""
self.db_fd, self.db_path = tempfile.mkstemp()
app = create_app({'TESTING': True, 'DATABASE': self.db_path})
with app.app_context():
# NOTE: Make sure that insert commands in schema.sql are commented or removed
init_db()
get_db().executescript(_data_sql)
self.app = app
self.app_client = app.test_client()
self.tokens = {
'test_unsubscribe':
'InRlc3Rib2lAdGVzdGJvaS5jb20i.cshVCqvl20ukOAh1Tgv53NDtjQk',
'test_flightstatus':
'InRlc3Rib2lAdGVzdGJvaS5jb20i.jTrlhxNTqHIsz3kTVweRT9pUo18',
'evil_unsubscribe':
'ImV2aWxib2lAZXZpbGJvaS5jb20i.lMCPwRVp2XAOSqaJzGccdyDQtcE'
}
def update_stored_prices(app, updates):
with app.app_context():
db = get_db()
for update in updates:
db.execute(
"""
UPDATE tracked_flights
SET prev_price = ?
WHERE id = ?
""", (update[1], update[0]))
db.commit()
def get_tracked_flights(app, email):
"""Returns all the flights tracked by given email"""
with app.app_context():
db = get_db()
cursor = db.execute(
"""
SELECT *
FROM tracked_flights
WHERE email = ?
""", (email, ))
column_names = list(map(lambda x: x[0], cursor.description))
return [{k: v
for k, v in zip(column_names, tuple(row))}
for row in cursor.fetchall()]
def make_entry(email_id, flight_details, price_details):
db = get_db()
if len(flight_details) == 2:
db.execute(
"""
INSERT INTO tracked_flights(id,
email,
dept_aircraft_code,
dept_carrier_code,
return_aircraft_code,
return_carrier_code,
adults, children,
infants,
from_location,
to_location,
departure_date,
return_date,
type_of_class,
prev_price)
VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
""",
(None,
email_id,
flight_details[0]['aircraft_code'],
flight_details[0]['carrier_code'],
flight_details[1]['aircraft_code'],
flight_details[1]['carrier_code'],
flight_details[0]['adults'],
flight_details[0]['children'],
flight_details[0]['infants'],
flight_details[0]['from_location'],
flight_details[0]['to_location'],
flight_details[0]['departure_date'],
flight_details[0]['return_date'],
flight_details[0]['type_of_class'],
str(price_details))
)
else:
db.execute(
"""
INSERT INTO tracked_flights(id,
email,
dept_aircraft_code,
dept_carrier_code,
return_aircraft_code,
return_carrier_code,
adults,
children,
infants,
from_location,
to_location,
departure_date,
return_date,
type_of_class,
prev_price)
VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
""",
(None,
email_id,
flight_details[0]['aircraft_code'],
flight_details[0]['carrier_code'],
None,
None,
flight_details[0]['adults'],
flight_details[0]['children'],
flight_details[0]['infants'],
flight_details[0]['from_location'],
flight_details[0]['to_location'],
flight_details[0]['departure_date'],
None,
flight_details[0]['type_of_class'],
str(price_details))
)
print('Entry made into DB')
db.commit()
def entry_exists(email_id, flight_details): db = get_db() if len(flight_details) == 2: round_entry = ( email_id, flight_details[0]['aircraft_code'], flight_details[0]['carrier_code'], flight_details[1]['aircraft_code'], flight_details[1]['carrier_code'], flight_details[0]['adults'], flight_details[0]['children'], flight_details[0]['infants'], flight_details[0]['from_location'], flight_details[0]['to_location'], flight_details[0]['departure_date'], flight_details[0]['return_date'], flight_details[0]['type_of_class'], ) row = db.execute( """ SELECT * FROM tracked_flights WHERE email = ? AND dept_aircraft_code = ? AND dept_carrier_code = ? AND return_aircraft_code = ? AND return_carrier_code = ? AND adults = ? AND children = ? AND infants = ? AND from_location = ? AND to_location = ? AND departure_date = ? AND return_date = ? AND type_of_class = ? """, round_entry ).fetchall() return (len(row) != 0) else: one_way_entry = ( email_id, flight_details[0]['aircraft_code'], flight_details[0]['carrier_code'], flight_details[0]['adults'], flight_details[0]['children'], flight_details[0]['infants'], flight_details[0]['from_location'], flight_details[0]['to_location'], flight_details[0]['departure_date'], flight_details[0]['type_of_class'], ) row = db.execute( """ SELECT * FROM tracked_flights WHERE email = ? AND dept_aircraft_code = ? AND dept_carrier_code = ? AND adults = ? AND children = ? AND infants = ? AND from_location = ? AND to_location = ? AND departure_date = ? AND type_of_class = ? """, one_way_entry ).fetchall() return (len(row) != 0)
def index(token):
# Token verification
with open('credentials.txt') as f:
credentials = {k: v for k, v in map(str.split, f.readlines())}
server_secret = credentials['SERVER_SECRET']
serializer = URLSafeSerializer(server_secret, salt='unsubscribe')
try:
email = serializer.loads(token)
except BadData as e:
return 'Invalid token provided'
# Take user input, unsubscribe and display confirmation
if request.method == 'POST':
# for i in request.form.items():
# print(i)
flightids_continue_tracking = [
k for (k, v) in request.form.items() if v == 'on'
]
# Print rows which will be deleted.
db = get_db()
cursor = db.execute(
"""
SELECT *
FROM tracked_flights
WHERE
email = ?
AND
id NOT IN %s
""" % ('(' + ', '.join(flightids_continue_tracking) + ')'),
(email, ))
print('Deleting the following rows...')
for row in cursor.fetchall():
print(tuple(row))
# Delete the rows
db.execute(
"""
DELETE
FROM tracked_flights
WHERE
email = ?
AND
id NOT IN %s
""" % ('(' + ', '.join(flightids_continue_tracking) + ')'),
(email, ))
db.commit()
# Display unsubscribe page
# Get tracked flights
db = get_db()
cursor = db.execute(
"""
SELECT *
FROM tracked_flights
WHERE email = ?
""", (email, ))
column_names = list(map(lambda x: x[0], cursor.description))
flight_details = [{k: v
for k, v in zip(column_names, tuple(row))}
for row in cursor.fetchall()]
return render_template('unsubscribe.html', flight_details=flight_details)
