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