def test_add_to_db(app: Flask):
"""Test if branches are added to database"""
runner = app.test_cli_runner()
tree_id = 1
result = runner.invoke(
args=['render', '-d', '8', '-z', '0-1', '-o', 'db:{}'.format(tree_id)])
assert result.exception is None
db = get_db()
cur = db.cursor()
# check entry to tree table was added
cur.execute('SELECT * FROM tree WHERE tree_id=?', [tree_id])
res = cur.fetchall()
assert len(res) == 1
# check correct number of entries have been added to zoom_info
cur.execute('SELECT zoom_id FROM zoom_info WHERE tree_id=?', [tree_id])
res2 = cur.fetchall()
assert res2 is not None and len(res2) == 2
# check tiles information has been added to tiles table
cur.execute('SELECT tile_id FROM tiles WHERE zoom_id IN (?, ?)',
[res2[0]['zoom_id'], res2[1]['zoom_id']])
res4 = cur.fetchall()
assert res4 is not None and len(res4) > 0
def view_inventory():
""" Display user's inventory. """
_initial_render()
if session.get('user_id') is None:
return redirect(url_for('login.login_as_get'))
db = get_db()
user_id = session['user_id']
cur = db.execute('SELECT * FROM branches_ownership INNER JOIN branches b ON branches_ownership.branch_id = b.id'
' WHERE owner_id = ? AND available_for_purchase = 0', [user_id])
branches = cur.fetchall()
# get user's name and token amount
cur = db.execute('SELECT name, token FROM users WHERE id = ?', [user_id])
user = cur.fetchone()
# get notifications for the user
cur = db.execute('SELECT notifications.id, message FROM notifications '
'JOIN notification_objects ON notifications.entity_id = notification_objects.id '
'WHERE receiver_id = ?', [user_id])
notifications = cur.fetchall()
return render_template("view_inventory.html", branches=branches, user=user, notifications=notifications)
def test_buy_branches(client, app):
"""
Test if post request to /buy/branch for change of ownership of a branch is reflected in the `branches_ownership`
table.
"""
db = get_db()
cur = db.cursor()
# sign up user 1
user1_id = register_user('ted', 'ted', 'qwertyY123')
branch_id = insert_branch(owner_id=user1_id, text='Branch 1', sell=True)
branch_id2 = insert_branch(owner_id=user1_id, text='Branch 2', sell=True)
# sign up and login user 2
user = signup_login(client)
# buy branch
client.post('/buy/branch',
data={
'new-bt': 'new Branch',
'branch_id': branch_id,
'branch_price': 10
},
follow_redirects=True)
cur.execute(
'SELECT text, users.id, users.token FROM branches_ownership INNER JOIN users ON '
'branches_ownership.owner_id = users.id WHERE owner_id=?;',
[user['id']])
row = cur.fetchone()
assert row["text"] == "new Branch"
assert row["token"] == 90
def login_as_post():
""" Login user with a form. """
session.clear()
# query database for username
db = get_db()
cur = db.execute("SELECT * FROM users WHERE username = (?)",
[request.form.get("username")])
user_data = cur.fetchone()
# ensure username exists and password is correct
if user_data is None or not check_password_hash(
user_data["hash_password"], request.form.get("password")):
flash("Invalid username and/or password.")
return render_template("login.html")
# remember which user has logged in
session["user_id"] = user_data["id"]
# insert login successful notification
db.execute(
"INSERT INTO notifications (receiver_id, entity_id) VALUES (?, ?)",
[user_data["id"], 1])
db.commit()
# redirect user to marketplace with user's inventory shown
return redirect(url_for("view_inventory.view_inventory"))
def delete_notification():
""" Deletes notification in database"""
db = get_db()
db.execute('DELETE FROM notifications WHERE id = ?', [request.form["id"]])
db.commit()
return ('', 204)
def buy_branches_search():
"""search for branches that have similar text elements"""
if session.get('user_id') is None:
return redirect(url_for('login.login_as_get'))
db = get_db()
user_id = session['user_id']
query = request.args['search_field']
cur = db.execute(
"SELECT * FROM branches_ownership INNER JOIN branches b on branches_ownership.branch_id"
"= b.id WHERE text LIKE (?) ", ('%' + query + '%', ))
filtered_branches = cur.fetchall()
# get user's name and token amount
cur = db.execute('SELECT name, token FROM users WHERE id = ?', [user_id])
user = cur.fetchone()
# get notifications for the user
cur = db.execute(
'SELECT notifications.id, message FROM notifications '
'JOIN notification_objects ON notifications.entity_id = notification_objects.id '
'WHERE receiver_id=?', [user_id])
notifications = cur.fetchall()
return render_template('buy_branch.html',
branches=filtered_branches,
user=user,
notifications=notifications)
def signup_as_post():
""" Register user with a form. """
# ensure password and confirmation match
if request.form.get("password") != request.form.get("password-confirm"):
flash("Your passwords must match.")
return render_template("signup.html")
# ensure password meets policy
# https://stackoverflow.com/questions/17140408/if-statement-to-check-whether-a-string-has-a-capital-letter-a-lower-case-letter/17140466
rules = [
lambda s: any(x.isupper()
for x in s), # must have at least one uppercase
lambda s: any(x.islower()
for x in s), # must have at least one lowercase
lambda s: any(x.isdigit() for x in s), # must have at least one digit
lambda s: len(s) >= 7 # must be at least 7 characters
]
if not all(rule(request.form.get("password")) for rule in rules):
flash(
"Password must have at least 7 characters, including at least one uppercase, one lowercase, and one digit."
)
return render_template("signup.html")
# hash password to not store the actual password
password = request.form.get("password")
hash_password = generate_password_hash(password)
db = get_db()
# username must be unique
try:
db.execute(
"INSERT INTO users (name, username, hash_password) VALUES (?, ?, ?)",
[
request.form.get("name"),
request.form.get("username"), hash_password
])
except sqlite3.IntegrityError:
# pick another username
flash("Your username has been used. Pick another username")
return render_template("signup.html")
db.commit()
# store their id in session to log them in automatically
cur = db.execute("SELECT * FROM users WHERE username=(?)",
[request.form.get("username")])
user_data = cur.fetchone()
# automatically log in user and redirect to user's marketplace
session['user_id'] = user_data['id']
# insert signup and login successful notification
db.execute(
"INSERT INTO notifications (receiver_id, entity_id) VALUES (?, ?)",
[user_data["id"], 2])
db.commit()
return redirect(url_for("view_inventory.view_inventory"))
def save_zoom_info(self, **kwargs):
"""
Add zoom level information to database.
:param tree_id: id of the corresponding entry in `tree` table; if none is provided, then assumed to the same
as the tree that was most recently loaded with :meth:`load_branches`
:param zoom_level: level of zoom
:param grid: size of the square grid
:param tile_size: (width, height) of the tile
:param img_size: (width, height) of the tile image, i.e dimensions of the image
:param img_dir: path to the directory containing the images of the tile
:param json_dir: path to the directory containing the JSON files that contain list of branches
contained in a tile
:return:
"""
req_args = ['tree_id', 'zoom_level', 'grid', 'tile_size', 'img_size', 'img_dir', 'json_dir']
if not kwargs.get('tree_id', None):
kwargs['tree_id'] = self.output_tree('tree_id')
for key in req_args:
if kwargs.get(key, None) is None:
raise ValueError('{} cannot be None'.format(key))
tree_id = kwargs['tree_id']
zoom_level = kwargs['zoom_level']
grid = kwargs['grid']
tile_width, tile_height = kwargs['tile_size']
img_width, img_height = kwargs['img_size']
img_dir = kwargs['img_dir']
json_dir = kwargs['json_dir']
with self.app.app_context():
db = get_db()
cur = db.cursor()
cur.execute('SELECT zoom_id FROM zoom_info WHERE zoom_level=? AND tree_id=?', [zoom_level, tree_id])
res = cur.fetchone()
if res is not None:
# drop existing entry with same zoom_level and tree_id
cur.execute('SELECT tile_id FROM tiles WHERE "zoom_id"=?', [res['zoom_id']])
res2 = cur.fetchall()
print(' Dropping existing zoom level info and {} associated tiles ...'.format(len(res2)))
cur.execute('DELETE FROM tiles WHERE "zoom_id"=?', [res['zoom_id']])
cur.execute('DELETE FROM zoom_info WHERE "zoom_id"=?', [res['zoom_id']])
cur.execute(
'INSERT INTO zoom_info (zoom_level, tree_id, grid, tile_width, tile_height, image_width, image_height,'
' imgs_path, jsons_path) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)',
[zoom_level, tree_id, grid, tile_width, tile_height, img_width, img_height, img_dir, json_dir]
)
cur.execute('SELECT last_insert_rowid();')
zoom_id = cur.fetchone()[0]
self.__map[(tree_id, zoom_level)] = zoom_id
db.commit()
print(' Added zoom level {}, {} {:.2f}x{:.2f} tiles'.format(zoom_level, grid, tile_width, tile_height))
def _initial_render():
tree_id = current_app.config['TREE_ID']
db = get_db()
res = db.execute('SELECT * FROM zoom_info WHERE tree_id=?',
[tree_id]).fetchall()
if (res is None or len(res) == 0) and not current_app.config['TESTING']:
# if not testing, render all branches once
_initial_branches(1)
from .services import render_service
render_service.render(zooms=None)
def get_about():
"""display about page"""
if session.get('user_id') is None:
return redirect(url_for('login.login_as_get'))
db = get_db()
user_id = session['user_id']
# get user's name and token amount
cur = db.execute('SELECT name, token FROM users WHERE id = ?', [user_id])
user = cur.fetchone()
return render_template('about.html', user=user)
def home():
""" Handles requests to the root page """
_initial_render()
user_id = session.get('user_id')
db = get_db()
row = db.execute('SELECT id FROM users WHERE id=?', [user_id]).fetchone()
if row is not None:
return redirect(url_for("view_inventory.view_inventory"))
else:
return render_template('index.html')
def query_tree():
tree_ids = request.args.getlist('id')
keys = set(request.args.getlist('keys'))
queries = set(request.args.getlist('q'))
length = len(tree_ids)
db = get_db()
cur = db.cursor()
if length > 0:
query = 'SELECT * FROM tree WHERE tree_id in ({});'.format(
_to_csv('?' * length))
cur.execute(query, tree_ids)
else:
cur.execute('SELECT * FROM tree;')
res = cur.fetchall()
rv = []
if res is not None and len(res) > 0:
valid_keys = set(res[0].keys())
if keys:
keys = valid_keys.intersection(keys).union(
{'tree_name', 'tree_id'})
else:
keys = valid_keys
for row in res:
dic = dict()
for key in keys:
dic[key] = row[key]
rv.append(dic)
# handle queries
for tree_dic in rv:
if 'max_zoom' in queries:
cur.execute(
'SELECT tree.tree_id, COUNT(zoom_id) FROM tree JOIN zoom_info zi on tree.tree_id = zi.tree_id '
'WHERE tree.tree_id=?', [tree_dic['tree_id']])
tree_dic['max_zoom'] = cur.fetchone()[1]
if 'num_branches' in queries:
cur.execute(
'SELECT tree_id, COUNT(branches.id) FROM branches WHERE tree_id=?',
[tree_dic['tree_id']])
tree_dic['num_branches'] = cur.fetchone()[1]
response = Response(response=json.dumps(rv),
mimetype='application/json',
content_type='application/json;charset=utf-8')
return response
def buy_branches_get():
"""get all the branches that belong to the given user"""
_initial_render()
if session.get('user_id') is None:
return redirect(url_for('login.login_as_get'))
db = get_db()
user_id = session['user_id']
# Filter view if category specified in query string
if "filter" in request.args:
if request.args["filter"] == "visibility":
cur = db.execute(
'SELECT * FROM branches_ownership INNER JOIN branches b on branches_ownership.branch_id'
'= b.id WHERE available_for_purchase=1 ORDER BY b.depth ASC')
available_branches = cur.fetchall()
if request.args["filter"] == "price-high":
cur = db.execute(
'SELECT * FROM branches_ownership INNER JOIN branches b on branches_ownership.branch_id'
'= b.id WHERE available_for_purchase=1 ORDER BY branches_ownership.price DESC'
)
available_branches = cur.fetchall()
if request.args["filter"] == "price-low":
cur = db.execute(
'SELECT * FROM branches_ownership INNER JOIN branches b on branches_ownership.branch_id'
'= b.id WHERE available_for_purchase=1 ORDER BY branches_ownership.price ASC'
)
available_branches = cur.fetchall()
else:
cur = db.execute(
'SELECT * FROM branches_ownership INNER JOIN branches b on branches_ownership.branch_id'
'= b.id WHERE available_for_purchase=1 ORDER BY b.id DESC')
available_branches = cur.fetchall()
# get user's name and token amount
cur = db.execute('SELECT name, token FROM users WHERE id = ?', [user_id])
user = cur.fetchone()
# get notifications for the user
cur = db.execute(
'SELECT notifications.id, message FROM notifications '
'JOIN notification_objects ON notifications.entity_id = notification_objects.id '
'WHERE receiver_id = ?', [user_id])
notifications = cur.fetchall()
return render_template('buy_branch.html',
branches=available_branches,
user=user,
notifications=notifications)
def query_tile():
zoom_level = request.args.get('zoom', default=0)
row = request.args.get('row', default=0)
col = request.args.get('col', default=0)
res_type = request.args.get('type', default='img')
tree_id = request.args.get('tree-id')
zoom_id = request.args.get('zoom-id')
if not tree_id and not zoom_id:
return Response('zoom-id or tree-id and zoom must be provided',
status=500)
if res_type not in ['img', 'json']:
return Response('\'{}\' type unknown'.format(res_type), status=500)
try:
row = int(row)
col = int(col)
cur = get_db().cursor()
if zoom_id:
cur.execute(
'SELECT * FROM tiles WHERE zoom_id=? AND tile_row=? AND tile_col=?',
[zoom_id, row, col])
else:
cur.execute(
'SELECT * FROM tiles INNER JOIN zoom_info zi ON tiles.zoom_id = zi.zoom_id WHERE '
'zoom_level=? AND tree_id=? AND tile_row=? AND tile_col=?',
[zoom_level, tree_id, row, col])
row = cur.fetchone()
if row is None:
return Response('tile not found', status=404)
if res_type == 'img':
with open(row['img_file'], mode='rb') as f:
response = Response(response=f.read(),
mimetype='image/png',
content_type='image/png')
else:
with open(row['json_file'], mode='r') as f:
response = Response(response=f.read(),
mimetype='application/json',
content_type='application/json')
return response
except FileNotFoundError:
return Response('tile data not found', status=404)
except ValueError as e:
return Response(str(e), status=500)
def buy_branch():
"""purchase a branch and change the contents of a the branch"""
db = get_db()
buying_id = request.form["branch_id"]
branch_price = request.form["branch_price"]
branch_text = request.form['new-bt']
user_id = session['user_id']
cur = db.execute('SELECT token FROM users WHERE id = ?', [user_id])
token = cur.fetchone()["token"]
if token <= int(branch_price):
# insert insufficient tokens notification
db.execute(
"INSERT INTO notifications (receiver_id, entity_id) VALUES (?, ?)",
[user_id, 6])
db.commit()
return redirect(url_for("buy.buy_branches_get"))
else:
cur = db.execute(
'SELECT owner_id FROM branches_ownership WHERE branch_id= ?',
[buying_id])
old_owner_id = cur.fetchone()["owner_id"]
db.execute(
'UPDATE branches_ownership SET owner_id = ?, text = ?, available_for_purchase = 0 '
'WHERE branch_id=?', [user_id, branch_text, buying_id])
db.execute('UPDATE users SET token = token - ? WHERE id = ?',
[branch_price, user_id])
db.execute('UPDATE users SET token = token + ? WHERE id = ?',
[branch_price, old_owner_id])
# insert sell branch successful notification for previous owner
db.execute(
"INSERT INTO notifications (receiver_id, entity_id) VALUES (?, ?)",
[old_owner_id, 5])
db.commit()
# insert buy branch successful notification
db.execute(
"INSERT INTO notifications (receiver_id, entity_id) VALUES (?, ?)",
[user_id, 3])
db.commit()
# re-render all tiles at all zoom levels in another thread
render_service.render(zooms=None)
return redirect(url_for("buy.buy_branches_get"))
def testing_signup(client, app):
"""
Adding new account to the database through a post request and checking if info was added in the database correctly
"""
db = get_db()
cur = db.cursor()
user = signup_login(client)
cur.execute(
'SELECT id, name, username, hash_password FROM users WHERE id=?',
[user['id']])
user_content = cur.fetchone()
assert user_content['name'] == user['name']
assert user_content['username'] == user['username']
assert check_password_hash(user_content['hash_password'], user['password'])
def test_add_layer(app: Flask):
""" Tests `add-layer` cli command. """
runner = app.test_cli_runner()
# dummy tree should have no branches
tree_id = app.config['DUMMY_TEST_TREE_ID']
result = runner.invoke(
args=['add-layer', '-f', 'db:{}'.format(tree_id), '-n', '1'])
assert result.exception is None
db = get_db()
cur = db.cursor()
cur.execute('SELECT * FROM branches WHERE tree_id=?', [tree_id])
res = cur.fetchall()
# there should only be the root branch
assert len(res) == 1
row = res[0]
assert row['depth'] == 0
# test if entries are added to branches_ownership
dummy_id = app.config['DUMMY_USER_ID']
result = runner.invoke(args=[
'add-layer', '-f', 'db:{}'.format(tree_id), '-n', 2, '--owner-id',
dummy_id
])
assert result.exception is None
cur.execute(
'SELECT DISTINCT depth, tree_id FROM branches WHERE tree_id=?',
[tree_id])
res = cur.fetchall()
# there should be 3 layers now
assert len(res) == 3
# number of new branches added
cur.execute('SELECT * FROM branches WHERE tree_id=? AND depth > 0',
[tree_id])
num_branches = len(cur.fetchall())
# all of them should have corresponding entries in branches_ownership with owner_id of dummy_id
cur.execute(
'SELECT * FROM branches INNER JOIN branches_ownership bo on branches.id = bo.branch_id '
'WHERE bo.owner_id=?', [dummy_id])
assert num_branches == len(cur.fetchall())
def __read_all_branches(self, tree_id: int):
with self.app.app_context():
db = get_db()
cur = db.cursor()
cur.execute('SELECT tree_name FROM tree WHERE tree_id=?', [tree_id])
res = cur.fetchone()
if res is None:
raise Exception('entry with tree_id={} does not exist'.format(tree_id))
tree_name = res[0]
print('Reading branches from tree \'{}\', tree_id={} ...'.format(tree_name, tree_id))
cur.execute('SELECT * FROM branches LEFT JOIN main.branches_ownership ON '
'branches.id = branches_ownership.branch_id WHERE tree_id=? ORDER BY "ind" ASC', [tree_id])
results = cur.fetchall()
if results is None or len(results) == 0:
num_branches = 0
branches = []
layers = []
else:
num_branches = len(results)
branches = [None for i in range(num_branches)]
layers, layer = [], -1
for i in range(num_branches):
row = results[i]
depth, length, width = row['depth'], row['length'], row['width']
angle = row['angle']
posx, posy = row['pos_x'], row['pos_y']
text = row['text']
branches[i] = Branch(i, Vec(posx, posy), depth=depth, length=length, width=width, angle=angle, text=text)
if depth > layer:
layers.append(i)
layer = depth
elif depth < layer:
raise Exception('branches not in order')
print(' branches read: {}\n layers: {}'.format(num_branches, str(layers).strip('[]')))
self.__branches = branches
self.__num_branches = num_branches
self.__layers = layers
self.__input_tree['tree_name'] = tree_name
self.__input_tree['tree_id'] = tree_id
def register_user(name, username, password):
"""
Add an entry to `users` table with the given credentials.
:param name: name
:param username: username
:param password: password
:return: the `id` of the entry in `users` table
"""
db = get_db()
cur = db.cursor()
hash_password = generate_password_hash(password)
cur.execute(
"INSERT INTO users (name, username, hash_password) VALUES (?, ?, ?)",
[name, username, hash_password])
cur.execute('SELECT last_insert_rowid();')
user_id = cur.fetchone()[0]
return user_id
def insert_branch(depth=0, ind=None, owner_id=None, text='', sell=False):
"""
Insert branch into `branches` table and an entry to `branches_ownership` table with the given owner-id, text, and
availability of purchase.
Note: requires application context
:param ind: index of branch
:param depth: depth of branch
:param owner_id: id of entry in `users` table
:param text: value of `text` column for entry in `branches_ownership` table
:param sell: whether the branch is available for purchase
:return: the id of the newly inserted entry in the `branches` table
"""
if not flask.has_app_context():
raise Exception('app_context not pushed to application context stack')
app = current_app
# dummy tree has no branches
test_tree_id = app.config['DUMMY_TEST_TREE_ID']
db = get_db()
cur = db.cursor()
if ind is None:
# find index that's not already used
ind = 0
while (test_tree_id, ind) in __cache:
ind += 1
cur.execute(
'INSERT INTO branches (ind, depth, length, width, angle, pos_x, pos_y, tree_id) VALUES'
'(?, ?, ?, ?, ?, ?, ?, ?)',
[ind, depth, 10, 10, 0, 0, 0, test_tree_id])
branch_id = cur.execute('select last_insert_rowid()').fetchone()[0]
__cache.add((test_tree_id, ind))
if owner_id:
sell = 1 if sell else 0
cur.execute(
'INSERT INTO branches_ownership (branch_id, owner_id, text, available_for_purchase) VALUES'
'(?, ?, ?, ?)', [branch_id, owner_id, text, sell])
return branch_id
def sell_branches_post():
""" Convert branch to available for purchase as requested by user. """
if session.get('user_id') is None:
return redirect(url_for('login.login_as_get'))
user_id = session['user_id']
db = get_db()
db.execute(
'UPDATE branches_ownership SET price=?, available_for_purchase=1 WHERE branch_id=?',
[request.form["selling_price"], request.form["branch_id"]])
db.commit()
# insert sell branch waiting for buyer notification
db.execute(
"INSERT INTO notifications (receiver_id, entity_id) VALUES (?, ?)",
[user_id, 4])
db.commit()
return redirect(url_for("view_inventory.view_inventory"))
def test_branch_grid_intersection(app: Flask):
"""Test if correct number of branches are recorded as being contained in a grid cell"""
runner = app.test_cli_runner()
tree_id = app.config['TEST_TREE_ID']
result = runner.invoke(args=[
'render', '-z', '0', '--name=test_tree', '-f', 'db:{}'.format(tree_id)
])
assert result.exception is None
db = get_db()
cur = db.cursor()
cur.execute('SELECT tree_name FROM tree WHERE tree_id=?', [tree_id])
tree_name = cur.fetchone()['tree_name']
test_cases = [
# [(gridx, gridy), # of branches in the grid cell]
[(2, 2), 5],
[(1, 2), 5],
[(2, 3), 1],
[(3, 0), 0]
]
success = 0
total = len(test_cases)
cache_dir = app.config['CACHE_DIR']
dir = os.path.join(cache_dir, '{}/json/zoom_0'.format(tree_name))
files = os.listdir(dir)
for file in files:
for case in test_cases:
i, j = case[0]
if file.endswith('{}_{}.json'.format(i, j)):
# load json files and check for correct number of branches
with open(os.path.join(dir, file), mode='r') as f:
assert len(json.load(f)) == case[1]
success += 1
assert success == total
def signup_login(client):
name = 'acbdefghijklmnopqrstuvwxyz'
username = '******'
password = '******'
rv = client.post('/signup',
data={
'name': name,
'username': username,
'password': password,
'password-confirm': password
},
follow_redirects=True)
user_id = get_db().execute(
'SELECT id FROM users WHERE username=? AND name=?',
[username, name]).fetchone()[0]
return {
'username': username,
'name': name,
'password': password,
'id': user_id
}
def query_zoom():
"""
Query for zoom information. Returns list of entries in `zoom_info` table that match any of the conditions
specified in the query. The following conditions are known:
1. `id` - refers to `zoom_id` of the entry
2. `tree-id` - refers to `tree_id` column of the entry
3. `q` - comma separated values of of the from `<zoom level>,<tree_id>|...`; refers to entries with
matching zoom level and tree id
4. keys - columns to include in the other than the zoom_id; by default all column values are returned
"""
ids = request.args.getlist('id')
tree_ids = request.args.getlist('tree-id')
queries = request.args.getlist('q')
keys = set(request.args.getlist('keys'))
parsed_queries = []
# parse queries
qlen = len(queries)
try:
for i in range(qlen):
query = queries[i]
slen = len(query)
j = 0
while j < slen:
comma = query.find(',', j)
pipe = query.find('|', j)
print(query, comma, pipe)
if comma < 0:
raise ValueError('\'{}\' malformed query'.format(query))
zoom_level = int(query[j:comma])
if pipe < 0:
tree_id = int(query[comma + 1:])
pipe = slen
else:
tree_id = int(query[comma + 1:pipe])
parsed_queries.append((zoom_level, tree_id))
j = pipe + 1
except ValueError as e:
return Response('Malformed query', status=500)
rv = []
cur = get_db().cursor()
# construct condition for WHERE clause
clause, args = '', []
if len(ids) > 0:
clause += 'zoom_id IN ({})'.format(_to_csv('?' * len(ids)))
args.extend(ids)
if len(tree_ids) > 0:
if clause:
clause += ' OR '
clause += 'tree_id IN ({})'.format(_to_csv('?' * len(tree_ids)))
args.extend(tree_ids)
if len(parsed_queries) > 0:
if clause:
clause += ' OR '
length = len(parsed_queries)
for i in range(length):
if i > 0:
clause += ' OR '
zoom_level, tree_id = parsed_queries[i]
clause += '(zoom_level=? AND tree_id =?)'
args.extend([zoom_level, tree_id])
# construct rest of SQL statement
if clause:
statement = 'SELECT * FROM zoom_info WHERE ' + clause + ';'
else:
statement = 'SELECT * FROM zoom_info;'
cur.execute(statement, args)
res = cur.fetchall()
rv = []
if res is not None and len(res) > 0:
valid_keys = set(res[0].keys())
if keys:
keys = valid_keys.intersection(keys).union({'zoom_id'})
else:
keys = valid_keys
for row in res:
dic = dict()
for key in keys:
dic[key] = row[key]
rv.append(dic)
response = Response(response=json.dumps(rv),
mimetype='application/json',
content_type='application/json;charset=utf-8')
return response
def save_tile_info(self, **kwargs):
"""
Save tile information to `tiles` table of application database. Will raise exception if zoom information
has not already been added to `zoom_info` table.
:param tree_id: `tree_id` column value of the entry in `tree` table that this tile belongs to
:param zoom_level: zoom level that the tile is being rendered at
:param tile_path: path to the PNG image of the tile
:param json_path: path to the JSON file containing list of branches visible in the tile
:param grid_location: tuple `(i, j)` where `i` is the zero-indexed row of the tile, `j` is the column
:param tile_index: index of the grid if all the squares of the grid were numbered starting from 0, should be
equivalent to `i*(num of tiles per row) + j` where `(i, j)` refer to row and column number of the tile
respectively
:param tile_position: tuple `(x, y)` of the position of the top-left corner of the tile in the full rendering
of the tree
"""
req_args = ['tree_id', 'zoom_level', 'tile_index', 'grid_location', 'tile_position']
if not kwargs.get('tree_id', None):
kwargs['tree_id'] = self.output_tree('tree_id')
for key in req_args:
if kwargs.get(key, None) is None:
raise ValueError('{} cannot be None'.format(key))
zoom_level = kwargs['zoom_level']
tree_id = kwargs['tree_id']
img_path = kwargs.get('img_path', '')
json_path = kwargs.get('json_path', '')
index = kwargs['tile_index']
row, col = kwargs['grid_location']
x, y = kwargs['tile_position']
with self.app.app_context():
db = get_db()
cur = db.cursor()
zoom_id = self.__map.get((tree_id, zoom_level), None)
if zoom_id is None:
cur.execute('SELECT zoom_id FROM zoom_info WHERE zoom_level=? AND tree_id=?', [zoom_level, tree_id])
result = cur.fetchone()
if len(result) == 0:
raise Exception('tree-id {}, zoom level {} information not added to zoom_info table'
.format(tree_id, zoom_level))
else:
zoom_id = result[0]
cur.execute('SELECT tile_id FROM tiles WHERE zoom_id=? AND tile_index=?;', [zoom_id, index])
res = cur.fetchone()
cur.execute('SELECT * FROM zoom_info WHERE zoom_id=?;', [zoom_id])
res2 = cur.fetchone()
if res2 is None:
print('NONE FOR ZOOM_ID', res2)
if res is None:
cur.execute(
'INSERT INTO tiles (tile_index, zoom_id, img_file, json_file, tile_col, tile_row, tile_pos_x, '
'tile_pos_y) VALUES (?,?,?,?,?,?,?,?)',
[index, zoom_id, img_path, json_path, col, row, x, y]
)
else:
cur.execute(
'UPDATE tiles SET img_file=?, json_file=?, tile_col=?, tile_row=?, tile_pos_x=?,'
' tile_pos_y=? WHERE tile_id=?;',
[img_path, json_path, col, row, x, y, res['tile_id']]
)
db.commit()
def update_branches(self, tree_id, **kwargs):
"""
Update branch entries with tree-id `tree-id` with information in contained is the list of :class:`Branch`
objects in `branches`. If entries have same index (i.e `ind` column value same as :meth:`Branch.index`), then
the entry will be updated. If such an entry does not exist, it will be inserted.
Additionally, a map of branch-id, dictionary pairs can be passed to :param:`ownership_info` which will be used
to update the `branches_ownership` table. Existing entries with same branch-id will be updated, and new ones will
be inserted into the table.
:param tree_id: id of entry in the `tree` table
:param branches: list of :class:`Branch` objects to insert into or update in the `branches` table
:param num_branches: number of :class:`Branch` objects containing in the `branches` list; if not provided,
defaults to `len(branches)`
:param ownership_info: map of branch-id and dictionary pairs where the dictionary maps all the columns in
`branches_ownership` table to values for that column. Note that the dictionary that each branch-id maps to
must provide values for the following columns, unless defaults are given:
======================== ======= ===============
Column Name Type Default
======================== ======= ===============
`owner_id` `int`
`text` `str` ``
`price` `int` `0`
`available_for_purchase` `bool` `False`
`available_for_bid` `bool` `True`
:param kwargs: additional options
:return:
"""
# use self.branches if branches kwarg not provided
branches, num_branches = kwargs.get('branches', None), kwargs.get('num_branches', None)
if branches is None:
branches = self.branches
num_branches = self.num_branches
elif not num_branches:
num_branches = len(branches)
ownership_info = kwargs.get('ownership_info', dict())
with self.app.app_context():
db = get_db()
cur = db.cursor()
cur.execute('SELECT tree_id FROM tree WHERE tree_id=?', [tree_id])
res = cur.fetchone()
if res is None:
raise Exception('entry with tree-id \'{}\' does not exist'.format(tree_id))
print('\nUpdating branches with tree-id \'{}\' ...'.format(tree_id))
updated, inserted = 0, 0
for i in range(num_branches):
branch = branches[i]
index = branch.index
cur.execute('SELECT * FROM branches WHERE tree_id=? AND "ind"=?', [tree_id, index])
branches_row = cur.fetchone()
if branches_row is None:
cur.execute('INSERT INTO branches (tree_id, ind, depth, length, width, angle, pos_x, pos_y) '
'VALUES (?, ?, ?, ?, ?, ?, ?, ?);',
[tree_id, index, branch.depth, branch.length, branch.width, branch.angle, branch.pos.x,
branch.pos.y])
inserted += 1
else:
cur.execute('UPDATE branches SET "depth"=?, "length"=?, "width"=?, "angle"=?, "pos_x"=?, "pos_y"=? '
' WHERE tree_id=? AND "ind"=?',
[branch.depth, branch.length, branch.width,
branch.angle, branch.pos.x, branch.pos.y, tree_id, index]
)
updated += 1
print(' branches table: updated {}, inserted {}'.format(updated, inserted))
print('\nUpdating branches_ownership entries ...')
updated, inserted = 0, 0
for branch_index, info in ownership_info.items():
cur.execute('SELECT * FROM branches WHERE branches.tree_id=? AND branches.ind=?;',
[tree_id, branch_index])
branches_row = cur.fetchone()
branch_id = branches_row['id']
cur.execute('SELECT * FROM branches_ownership WHERE branch_id=?;', [branch_id])
ownership_row = cur.fetchone()
owner_id = info['owner_id']
text = info.get('text', '')
price = info.get('price', 0)
available_for_purchase = 1 if info.get('available_for_purchase', None) else 0
available_for_bid = 1 if info.get('available_for_bid', None) else 0
if ownership_row is None:
cur.execute('INSERT INTO branches_ownership (branch_id, owner_id, text, price, '
'available_for_purchase, available_for_bid) VALUES (?, ?, ?, ?, ?, ?);',
[branch_id, owner_id, text, price, available_for_purchase, available_for_bid])
inserted += 1
else:
cur.execute('UPDATE branches_ownership SET owner_id=?, text=?, price=?, available_for_purchase=?,'
'available_for_bid=? WHERE branch_id=?',
[owner_id, text, price, available_for_purchase, available_for_bid, branch_id]
)
updated += 1
print(' branches_ownership table: updated {}, inserted {}'.format(updated, inserted))
db.commit()
def save_branches(self, **kwargs):
tree_id = kwargs.get('tree_id', None)
full_width = kwargs.get('width', 0)
full_height = kwargs.get('height', 0)
tree_name = kwargs.get('tree_name', None)
# for formatting
newline = True
# use self.branches if branches kwarg not provided
branches, num_branches = kwargs.get('branches', None), kwargs.get('num_branches', None)
if branches is None:
branches = self.branches
num_branches = self.num_branches
elif not num_branches:
# branches kwarg provided but no num_branches provided
raise Exception('num_branches not provided')
with self.app.app_context():
db = get_db()
cur = db.cursor()
if tree_id is not None:
cur.execute('SELECT tree_id, tree_name FROM tree WHERE tree_id=?', [tree_id])
res = cur.fetchone()
if res is not None:
# get tree_name from existing entry, if there is one and no tree_name is provided
orig_tree_name = res['tree_name']
if not tree_name:
tree_name = orig_tree_name
# get associated tiles
cur.execute('SELECT zoom_id FROM zoom_info WHERE tree_id=?', [tree_id])
res_zooms = cur.fetchall()
cur.execute('SELECT tiles.zoom_id, tree_id FROM zoom_info INNER JOIN tiles ON '
'tiles.zoom_id=zoom_info.zoom_id WHERE zoom_info.tree_id=?', [tree_id])
res_tiles = cur.fetchall()
print('\nDropping existing entry \'{}\', tree_id={}'.format(orig_tree_name, tree_id))
print(' {} zoom level(s), {} total tiles'.format(len(res_zooms), len(res_tiles)))
newline = False
# entries in zoom_info, tiles .. should be deleted automatically
# if PRAGMA foreign_keys=ON
cur.execute(r'DELETE FROM branches WHERE "tree_id"=?', [tree_id])
cur.execute(r'DELETE FROM tree WHERE "tree_id"=?', [tree_id])
elif not tree_name:
tree_name = _get_default_tree_name()
cur.execute('INSERT INTO tree (tree_id, tree_name, full_width, full_height) '
'VALUES (?, ?, ?, ?)', [tree_id, tree_name, full_width, full_height])
else:
if not tree_name:
tree_name = _get_default_tree_name()
cur.execute('INSERT INTO tree (tree_name, full_width, full_height) VALUES (?, ?, ?)',
[tree_name, full_width, full_height])
cur.execute('SELECT last_insert_rowid()')
rowid = cur.fetchone()[0]
if newline:
print()
print('Created new entry \'{}\' tree_id={}'.format(tree_name, rowid))
self.__output_tree['tree_name'] = tree_name
self.__output_tree['tree_id'] = tree_id
self.__add_all_branches(cur, rowid, branches=branches, num_branches=num_branches)
db.commit()
