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()