def test_get_close_db(app):
with app.app_context():
db = get_db()
assert db is get_db()
with pytest.raises(sqlite3.ProgrammingError) as e:
db.execute('SELECT 1')
assert 'closed' in str(e)
def get(user: str):
"""
Return the investments held by a given user.
:param user:
:return:
"""
db_connection = db.get_db()
i_raw = db_connection.execute(f"""
SELECT id, portfolio, duration, principal
FROM investment
WHERE username = "******"
ORDER BY id ASC
""").fetchall()
investments = []
for i_row in i_raw:
investment = {
'id': i_row[0],
'portfolio': i_row[1],
'duration': i_row[2],
'principal': i_row[3]
}
investments.append(investment)
if len(investments) == 0:
return f"No investments for {user}"
return investments
def app():
db_fd, db_path = tempfile.mkstemp()
app = create_app({
'TESTING': True,
'DATABASE': db_path,
})
with app.app_context():
init_db()
get_db().executescript(_data_sql)
yield app
os.close(db_fd)
os.unlink(db_path)
def test_create(client, auth, app):
auth.login()
assert client.get('/create').status_code == 200
client.post('/create', data={'title': 'created', 'body': ''})
with app.app_context():
db = get_db()
count = db.execute('SELECT COUNT(id) FROM post').fetchone()[0]
assert count == 2
def test_delete(client, auth, app):
auth.login()
response = client.post('/1/delete')
assert response.headers['Location'] == 'http://localhost/'
with app.app_context():
db = get_db()
post = db.execute('SELECT * FROM post WHERE id = 1').fetchone()
assert post is None
def test_update(client, auth, app):
auth.login()
assert client.get('/1/update').status_code == 200
client.post('/1/update', data={'title': 'updated', 'body':''})
with app.app_context():
db = get_db()
post = db.execute('SELECT * FROM post WHERE id = 1').fetchone()
assert post['title'] == 'updated'
def _fixture(query):
"""
Put a test fixture in the test application database
"""
with app.test_client() as client:
with app.app_context():
db_connection = db.get_db()
db_connection.execute(query)
db_connection.commit()
def test_register(client, app):
assert client.get('/auth/register').status_code == 200
response = client.post('/auth/register',
data={
'username': '******',
'password': '******'
})
assert 'http://localhost/auth/login' == response.headers['Location']
with app.app_context():
assert get_db().execute(
"select * from user where username = '******'", ).fetchone() is not None
def get_post(id, check_author=True):
post = get_db().execute(
'SELECT p.id, title, body, created, author_id, username'
' FROM post p JOIN user u ON p.author_id = u.id'
' WHERE p.id = ?', (id, )).fetchone()
if post is None:
abort(404, "Post id {0} does not exist.".format(id))
if check_author and post['author_id'] != g.user['id']:
abort(403)
return post
def test_author_required(app, client, auth):
# change post author to another uer
with app.app_context():
db = get_db()
db.execute('UPDATE post SET author_id = 2 WHERE id = 1')
db.commit()
auth.login()
# current user cant modify other user's post
assert client.post('/1/update').status_code == 403
assert client.post('/1/delete').status_code == 403
# current user diesnt see edit link
assert b'href="/1/update"' not in client.get('/').data
def check_exists(username: str):
"""
Check whether or not a given username exists in the database.
:param username: the username to be checked
:return: bool
"""
db_connection = db.get_db()
if db_connection.execute(
f'SELECT username FROM user WHERE username = "******"'
).fetchone() is not None:
return True
return False
def load_from_db():
"""
Load the data for investment portfolios from the database.
"""
db_connection = db.get_db()
pf_raw = db_connection.execute(
'SELECT id, max, min, risk FROM portfolio ORDER BY id ASC'
).fetchall()
portfolios = []
for pf_row in pf_raw:
portfolio = Portfolio(pf_row[0], pf_row[1], pf_row[2], pf_row[3])
portfolios.append(portfolio)
return portfolios
def create(user: str, principal: float, duration: int, portfolio: str):
"""
Insert an investment into the DB.
:param user:
:param principal:
:param duration:
:param portfolio:
"""
db_connection = db.get_db()
db_connection.execute(f"""
INSERT INTO investment (username, portfolio, duration, principal)
VALUES ("{user}", "{portfolio}", {duration}, {principal})
""")
db_connection.commit()
def create():
if request.method == 'POST':
title = request.form['title']
body = request.form['body']
error = None
if not title:
error = 'Title is required'
if error is not None:
flash(error)
else:
db = get_db()
db.execute(
'INSERT INTO post (title, body, author_id)'
' VALUES (?, ?, ?)', (title, body, g.user['id']))
db.commit()
return redirect(url_for('blog.index'))
return render_template('blog/create.html')
def get_products():
db = get_db()
products = db.execute(
'SELECT * FROM product'
).fetchall()
# Converting to tag format
encoded_prods = []
for prod in products:
code = UUID('{' + prod['code'] + '}')
euros = int(prod['price'] // 100)
cents = int(prod['price'] % 100)
encoded_prods.append(
b64_encode(
pack('4s', encode('Acme')) +
pack('16s', code.bytes) +
pack('>i', euros) +
pack('>i', cents) +
pack('b', len(prod['prodName'])) +
pack('35s', encode(prod['prodName']))
)
)
# Appending signature
encoded_prods = list(map(
lambda prod:
decode(
prod + b64_encode(sign(
current_app.config['PRIVATE_KEY'],
prod
)),
),
encoded_prods
))
return current_app.response_class(
response=json.dumps(encoded_prods),
status=200,
mimetype='application/json'
)
def update(id):
post = get_post(id)
if request.method == 'POST':
title = request.form['title']
body = request.form['body']
error = None
if not title:
error = 'Title is required'
if error is not None:
flash(error)
else:
db = get_db()
db.execute('UPDATE post SET title = ?, body = ?'
' WHERE id = ?', (title, body, id))
db.commit()
return redirect(url_for('blog.index'))
return render_template('blog/update.html', post=post)
def ensure_exists(username: str):
"""
Make sure the passed username exists in the database. Return a dict
stating whether the user already existed or not.
:param username: str: the username of to be confirmed or written to the
database
:return: dict
"""
db_connection = db.get_db()
user_created = {"user_exists": check_exists(username)}
if not user_created["user_exists"]:
db_connection.execute(
f'INSERT INTO user (username) VALUES ("{username}")')
db_connection.commit()
return user_created
def checkout():
db = get_db()
content = request.data[: -SIGNATURE_BASE64_SIZE]
decoded_content = b64_decode(content)
signature = b64_decode(request.data[-SIGNATURE_BASE64_SIZE:])
# Acme tag
_ = decoded_content[0: INTEGER_SIZE]
decoded_content = decoded_content[INTEGER_SIZE:]
# Checking if User exists
uuid = str(UUID_from_bytes(decoded_content[:UUID_SIZE]))
decoded_content = decoded_content[UUID_SIZE:]
user = db.execute(
'SELECT userPublicKey, accumulatedDiscount FROM user WHERE id = ?',
(uuid, )
).fetchone()
if user is None:
abort(401)
# Verifying User through signature
if not verify(user_key_from_bytes(user['userPublicKey']),
signature,
content):
abort(401)
# Extract discount
discont = unpack('b', decoded_content[-1:])[0]
decoded_content = decoded_content[:-1]
# Extract voucher
has_voucher = unpack('b', decoded_content[-1:])[0]
decoded_content = decoded_content[:-1]
if has_voucher:
voucher_id = str(UUID_from_bytes(decoded_content[-UUID_SIZE:]))
decoded_content = decoded_content[:-UUID_SIZE]
# Extracting products and total
products = {}
total = 0
for i in range(0, len(decoded_content) // PRODUCT_SIZE):
base = i * PRODUCT_SIZE
prod = decoded_content[base: base + PRODUCT_SIZE]
# Acme tag
_ = prod[0: INTEGER_SIZE]
prod = prod[INTEGER_SIZE:]
# Product Code
code = str(UUID_from_bytes(prod[:UUID_SIZE]))
prod = prod[UUID_SIZE:]
if code in products:
products[code] += 1
else:
products[code] = 1
# Prices, maybe do validation
price = prod[2 * INTEGER_SIZE:]
# Validating product and getting total
dbProd = db.execute(
'SELECT price FROM product WHERE code = ?',
(code, )
).fetchone()
if dbProd is None:
abort(400)
else:
total += dbProd['price']
# Creating new Vouchers
for _ in range(0, total // ONE_HUNDRED_EUROS_IN_CENTS):
db.execute(
'INSERT INTO voucher (id, ownerID) VALUES (?, ?)',
(str(gen_UUID()), uuid)
)
# Processing voucher
discont_accumulator = 0
if has_voucher:
discont_accumulator = 0.15
# Checking if voucher is valid
if db.execute(
'SELECT * FROM voucher WHERE id = ? AND used = 0',
(voucher_id, )
).fetchone() is None:
abort(400)
# Updating voucher
db.execute(
'UPDATE voucher SET used = 1 WHERE id = ?',
(voucher_id, )
)
# Updating discont
discounted = 0
acc_discont = user['accumulatedDiscount'] + \
round(total * discont_accumulator)
if discont:
if user['accumulatedDiscount'] > total:
acc_discont = acc_discont - total
discounted = total
else:
discounted = user['accumulatedDiscount']
acc_discont = round(total * discont_accumulator)
db.execute(
'UPDATE user SET accumulatedDiscount = ? WHERE id = ?',
(acc_discont, uuid,)
)
else:
db.execute(
'UPDATE user SET accumulatedDiscount = ? WHERE id = ?',
(acc_discont, uuid)
)
# Creating transaction
trans_exec = db.cursor()
if has_voucher:
trans_exec.execute(
'INSERT INTO acmeTransaction (ownerID, total,\
discounted, voucherID) VALUES (?, ?, ?, ?)',
(uuid, total, discounted, voucher_id)
)
else:
trans_exec.execute(
'INSERT INTO acmeTransaction (ownerID, total,\
discounted) VALUES (?, ?, ?)',
(uuid, total, discounted)
)
transaction_id = trans_exec.lastrowid
# Creating Transaction - Products association
for prod in products:
db.execute(
'INSERT INTO transactionProdcuts (transactionID,\
productID, quantity) VALUES (?, ?, ?)',
(transaction_id, prod, products[prod])
)
# Commiting everything!
db.commit()
return current_app.response_class(
response=str(total-discounted),
status=200,
mimetype='application/json'
)
def get_user_by_username(username):
cursor = get_db().cursor()
cursor.execute('select * from user where email = %s or phone = %s', (username, username))
return cursor.fetchone()
def index():
db = get_db()
posts = db.execute('SELECT p.id, title, body, created, author_id, username'
' FROM post p JOIN user u ON p.author_id = u.id'
' ORDER BY created DESC').fetchall()
return render_template('blog/index.html', posts=posts)
def get_user_id_by_phone(phone):
cursor = get_db().cursor()
cursor.execute('select id from user where phone = %s', (phone,))
return cursor.fetchone()
def get_user_id_by_email(email):
cursor = get_db().cursor()
cursor.execute('select id from user where email = %s', (email,))
return cursor.fetchone()
def get_transactions():
db = get_db()
content = request.data[: -SIGNATURE_BASE64_SIZE]
signature = b64_decode(request.data[-SIGNATURE_BASE64_SIZE:])
# Acme tag
decoded_content = b64_decode(content)
_ = decoded_content[0: INTEGER_SIZE]
decoded_content = decoded_content[INTEGER_SIZE:]
# Checking if User exists
uuid = str(UUID_from_bytes(decoded_content))
user = db.execute(
'SELECT userPublicKey FROM user WHERE id = ?',
(uuid, )
).fetchone()
if user is None:
abort(401)
# Verifying User through signature
if not verify(user_key_from_bytes(user['userPublicKey']),
signature,
content):
abort(401)
# Getting Transactions
transactions = db.execute(
'SELECT total, discounted, voucherID, created\
FROM acmeTransaction WHERE ownerID = ?',
(uuid, )
).fetchall()
# Encrypting every single transaction
content = b64_encode(encode(
json.dumps({
'transactions': [
decode(b64_encode(encrypt(
encode(json.dumps({
'd': t['created'].strftime("%d-%m-%Y"),
't': t['total'],
'di': t['discounted'],
'v': t['voucherID'] is not None,
})),
user_key_from_bytes(user['userPublicKey'])
)))
for t in transactions
]
})
))
# Signing content
final_content = decode(
content + b64_encode(sign(
current_app.config['PRIVATE_KEY'],
content
))
)
return current_app.response_class(
response=final_content,
status=200,
mimetype='application/json'
)
def set_user_active_by_email(email):
db = get_db()
cursor = db.cursor()
cursor.execute('update user set status=%s where email=%s', ('active', email))
db.commit()
def delete(id):
get_post(id)
db = get_db()
db.execute('DELETE FROM post where id = ?', (id, ))
db.commit()
return redirect(url_for('blog.index'))
def get_vouchers():
db = get_db()
content = request.data[: -SIGNATURE_BASE64_SIZE]
signature = b64_decode(request.data[-SIGNATURE_BASE64_SIZE:])
# Acme tag
decoded_content = b64_decode(content)
_ = decoded_content[0: INTEGER_SIZE]
decoded_content = decoded_content[INTEGER_SIZE:]
# Checking if User exists
uuid = str(UUID_from_bytes(decoded_content))
user = db.execute(
'SELECT userPublicKey, accumulatedDiscount FROM user WHERE id = ?',
(uuid, )
).fetchone()
if user is None:
abort(401)
# Verifying User through signature
if not verify(user_key_from_bytes(user['userPublicKey']),
signature,
content):
abort(401)
# Getting vouchers
vouchers = db.execute(
'SELECT id FROM voucher WHERE ownerID = ? AND used = 0',
(uuid, )
).fetchall()
# Encrypting every single voucher and the discount
content = b64_encode(encode(
json.dumps({
'vouchers': [
decode(b64_encode(encrypt(
encode(str(row['id'])),
user_key_from_bytes(user['userPublicKey'])
)))
for row in vouchers
],
'discount': decode(b64_encode(encrypt(
encode(str(user['accumulatedDiscount'])),
user_key_from_bytes(user['userPublicKey'])
)))
})
))
# Signing content
final_content = decode(
content + b64_encode(sign(
current_app.config['PRIVATE_KEY'],
content
))
)
return current_app.response_class(
response=final_content,
status=200,
mimetype='application/json'
)
def get_user_id_by_id(id):
cursor = get_db().cursor()
cursor.execute('select id from user where id = %s', (id,))
return cursor.fetchone()
def register():
# Extracting from request
data = json.loads(decode(request.data))
db = get_db()
if not data['metadata']['name'] or\
not data['metadata']['username'] or\
not data['metadata']['password'] or\
not data['metadata']['publicKey'] or\
not data['paymentInfo']['CVV'] or\
not data['paymentInfo']['cardNumber'] or\
not data['paymentInfo']['cardValidity']['month'] or\
not data['paymentInfo']['cardValidity']['year']:
abort(400)
if db.execute(
'SELECT id FROM user WHERE nickname = ?',
(data['metadata']['username'],)
).fetchone() is not None:
abort(409)
if db.execute(
'SELECT * FROM paymentCard WHERE cardNumber = ?',
(data['paymentInfo']['cardNumber'],)
).fetchone() is not None:
abort(409)
# Creating the PaymentCard
db.execute(
'INSERT INTO paymentCard (cvv, cardNumber, monthValid,\
yearValid) VALUES (?, ?, ?, ?)',
(data['paymentInfo']['CVV'],
data['paymentInfo']['cardNumber'],
data['paymentInfo']['cardValidity']['month'],
data['paymentInfo']['cardValidity']['year'])
)
# Registering the new USer
user_uuid = str(gen_UUID())
public_key = b64_decode(data['metadata']['publicKey'][:-1])
db.execute(
'INSERT INTO user (id, username, nickname, cardNumber,\
userPublicKey) VALUES (?, ?, ?, ?, ?)',
(user_uuid,
data['metadata']['name'],
data['metadata']['username'],
data['paymentInfo']['cardNumber'],
public_key)
)
# Committing changes to db
db.commit()
# Returning the User's uuid and the supermarket key
return current_app.response_class(
response=json.dumps({
'uuid': user_uuid,
'public_key': decode(b64_encode(
public_key_to_bytes(current_app.config["PUBLIC_KEY"])
))
}),
status=201,
mimetype='application/json'
)
def add_user(first_name, last_name, email, phone, password):
db = get_db()
cursor = db.cursor()
cursor.execute('insert into user (first_name, last_name, email, phone, password) values (%s, %s, %s, %s, %s)',
(first_name, last_name, email, phone, generate_password_hash(password)))
db.commit()
