def test_link(self): """ Link to any node """ with self.app.app_context(): init_db() a_id = insert_node(name='a', value=None) b_id = insert_node(name='b', value=None) c_id = insert_node(name='c', value="c") d_id = insert_node(name='d', value="d") # a -> c, b -> c # a -> d insert_node_node(node_id=a_id, target_node_id=c_id) insert_node_node(node_id=a_id, target_node_id=d_id) insert_node_node(node_id=b_id, target_node_id=c_id) result = db.query( fetch_query_string('select_link_node_from_node.sql'), fetchall=True, **{'node_id': a_id}) result = [x.get('node_id', None) for x in result] assert c_id in result assert d_id in result assert a_id not in result result = db.query( fetch_query_string('select_link_node_from_node.sql'), fetchall=True, **{'node_id': b_id}) result = [x.get('node_id', None) for x in result] assert c_id in result assert d_id not in result assert a_id not in result
def _query(_node_id, value=None, **kw): "Look up value by using Query table" query_result = [] try: query_result = db.query( fetch_query_string('select_query_from_node.sql'), fetchall=True, **kw) except DatabaseError as err: current_app.logger.error("DatabaseError: %s, %s", err, kw) return value #current_app.logger.debug("queries kw: %s", kw) #current_app.logger.debug("queries value: %s", value) #current_app.logger.debug("queries: %s", query_result) if query_result: values = [] for query_name in [x.get('name', None) for x in query_result]: if query_name: result = [] try: result = db.query(fetch_query_string(query_name), **kw) if len(result) == 0: values.append(([], [])) else: # There may be more results, but only interested in the # first one values.append((result.as_dict(), result[0].keys())) except (DatabaseError, StatementError) as err: current_app.logger.error("DatabaseError (%s) %s: %s", query_name, kw, err) value = values #current_app.logger.debug("value: %s", value) return value
def test_delete_one_node(self): """ Delete a node """ with self.app.app_context(): init_db() trans = db.transaction() result = db.db.execute(fetch_query_string('insert_node.sql'), { 'name': 'a', 'value': 'apple' }) a = result.lastrowid trans.commit() result = db.query(fetch_query_string('select_node_from_id.sql'), fetchall=True, **{'node_id': a}) assert len(result) == 1 r = result.first() assert a == r.get('node_id') assert 'a' == r.get('name') assert 'apple' == r.get('value') # now delete delete_node(node_id=a) result = db.query(fetch_query_string('select_node_from_id.sql'), fetchall=True, **{'node_id': a}) assert len(result) == 0
def delete_node(**kw): """ Delete a node by id. """ with current_app.app_context(): trans = db.transaction() db.query(fetch_query_string('delete_node_for_id.sql'), **kw) trans.commit()
def insert_node_node(**kw): """ Link a node to another node. node_id -> target_node_id. Where `node_id` is the parent and `target_node_id` is the child. """ with current_app.app_context(): insert_query(name='select_link_node_from_node.sql', node_id=kw.get('node_id')) trans = db.transaction() db.query(fetch_query_string('insert_node_node.sql'), **kw) trans.commit()
def test_db(self): """Check usage of db""" with self.app.app_context(): with self.app.test_client() as c: init_db() db.query( """insert into Node (name, value) values (:name, :value)""", **{ "name": "bill", "value": "?" })
def node_from_uri(uri, method="GET"): # check if page exists in data_path # a//b == a/b/ == a/./b == a/foo/../b # '' == '.' # Prepend the uri with '/' and normalize uri = os.path.normpath(os.path.join('/', uri)) uri, ext = os.path.splitext(uri) if not uri.endswith('/'): uri = ''.join((uri, '/')) #current_app.logger.debug('uri: "%s"' % uri) rule_kw = {} select_node_from_route = fetch_query_string('select_node_from_route.sql') result = [] try: result = db.query(select_node_from_route, fetchall=True, **{ 'uri': uri, 'method': method }) except DatabaseError as err: current_app.logger.error("DatabaseError: %s", err) #current_app.logger.debug('result: "%s"' % result) if not result or len(result) == 0: # See if the uri matches any dynamic rules (rule, rule_kw) = check_map(uri, request.url_root) #current_app.logger.debug(rule) #current_app.logger.debug('rule: "%s"' % rule or '') if rule: try: result = db.query(select_node_from_route, fetchall=True, **{ 'uri': rule, 'method': method }) except DatabaseError as err: current_app.logger.error("DatabaseError: %s", err) if result: result = result.as_dict() #(result, col_names) = rowify(result, c.description) # Only one result for a getting a node from a unique path. return (result[0], rule_kw) return (None, rule_kw)
def insert_route(**kw): """ `path` - '/', '/some/other/path/', '/test/<int:index>/' `node_id` `weight` - How this path is selected before other similar paths `method` - 'GET' is default. """ binding = {'path': None, 'node_id': None, 'weight': None, 'method': "GET"} binding.update(kw) with current_app.app_context(): trans = db.transaction() db.query(fetch_query_string('insert_route.sql'), **binding) trans.commit()
def test_template(self): with self.app.app_context(): init_db() a = insert_node(name='a', value=None) add_template_for_node('template_a.html', a) aa = insert_node(name='aa', value=None) add_template_for_node('template_a.html', aa) b = insert_node(name='b', value=None) add_template_for_node('template_b.html', b) c = insert_node(name='c', value=None) add_template_for_node('template_c.html', c) result = db.query( fetch_query_string('select_template_from_node.sql'), fetchall=True, **{'node_id': a}) result = [x.get('name', None) for x in result] assert len(result) == 1 assert result[0] == 'template_a.html' # another node that uses the same template result = db.query( fetch_query_string('select_template_from_node.sql'), fetchall=True, **{'node_id': aa}) result = [x.get('name', None) for x in result] assert len(result) == 1 assert result[0] == 'template_a.html' # can overwrite what node is tied to what template add_template_for_node('template_over_a.html', a) result = db.query( fetch_query_string('select_template_from_node.sql'), fetchall=True, **{'node_id': a}) result = [x.get('name', None) for x in result] assert len(result) == 1 assert result[0] == 'template_over_a.html' # this one still uses the other template result = db.query( fetch_query_string('select_template_from_node.sql'), fetchall=True, **{'node_id': aa}) result = [x.get('name', None) for x in result] assert len(result) == 1 assert result[0] == 'template_a.html'
def init_db(): """Initialize a new database with the default tables for chill. Creates the following tables: Chill Node Node_Node Route Query Template """ with current_app.app_context(): trans = db.transaction() for filename in CHILL_CREATE_TABLE_FILES: db.query(fetch_query_string(filename)) trans.commit()
def check_map(uri, url_root): """ return a tuple of the rule and kw. """ # TODO: Building the Map each time this is called seems like it could be more effiecent. result = [] try: result = db.query(fetch_query_string('select_route_where_dynamic.sql'), fetchall=True) except OperationalError as err: current_app.logger.error("OperationalError: %s", err) return (None, None) if result: routes = result.as_dict() #(routes, col_names) = rowify(result, c.description) #current_app.logger.debug( [x['rule'] for x in routes] ) rules = map(lambda r: Rule(r['rule'], endpoint='dynamic'), routes) d_map = Map(rules) map_adapter = d_map.bind(url_root) #current_app.logger.debug(uri) try: (rule, rule_kw) = map_adapter.match(path_info=uri, return_rule=True) #current_app.logger.debug(rule) return (str(rule), rule_kw) except HTTPException: pass return (None, {})
def add_template_for_node(name, node_id): "Set the template to use to display the node" with current_app.app_context(): trans = db.transaction() db.query(fetch_query_string('insert_template.sql'), **{ 'name': name, 'node_id': node_id }) result = db.query(fetch_query_string('select_template.sql'), **{ 'name': name, 'node_id': node_id }).first(as_dict=True) if result: template_id = result.get('id') db.query(fetch_query_string('update_template_node.sql'), **{ 'template': template_id, 'node_id': node_id }) trans.commit()
def test_delete_node_with_link(self): """ Delete a node also will delete from link """ with self.app.app_context(): init_db() a_id = insert_node(name='a', value=None) b_id = insert_node(name='b', value=None) c_id = insert_node(name='c', value="c") d_id = insert_node(name='d', value="d") # a -> c, b -> c # a -> d insert_node_node(node_id=a_id, target_node_id=c_id) insert_node_node(node_id=a_id, target_node_id=d_id) insert_node_node(node_id=b_id, target_node_id=c_id) result = db.query( fetch_query_string('select_link_node_from_node.sql'), fetchall=True, **{'node_id': a_id}) result = [x.get('node_id', None) for x in result] assert c_id in result assert d_id in result assert a_id not in result result = db.query( fetch_query_string('select_link_node_from_node.sql'), fetchall=True, **{'node_id': b_id}) result = [x.get('node_id', None) for x in result] assert c_id in result assert d_id not in result assert a_id not in result # now delete (should use the 'on delete cascade' sql bit) trans = db.transaction() db.query(fetch_query_string('delete_node_for_id.sql'), **{'node_id': a_id}) trans.commit() result = db.query(fetch_query_string('select_node_from_id.sql'), fetchall=True, **{'node_id': a_id}) assert len(result) == 0 result = db.query( fetch_query_string('select_link_node_from_node.sql'), fetchall=True, **{'node_id': a_id}) assert len(result) == 0 result = db.query( fetch_query_string('select_node_node_from_node_id.sql'), fetchall=True, **{'node_id': a_id}) assert len(result) == 0
def insert_query(**kw): """ Insert a query name for a node_id. `name` `node_id` Adds the name to the Query table if not already there. Sets the query field in Node table. """ with current_app.app_context(): #trans = db.transaction() result = db.query(fetch_query_string('select_query_where_name.sql'), fetchall=True, **kw) if result: kw['query_id'] = result[0].get('id') else: result = db.db.execute(fetch_query_string('insert_query.sql'), kw) kw['query_id'] = result.lastrowid db.db.execute(fetch_query_string('insert_query_node.sql'), kw)
def _template(node_id, value=None): "Check if a template is assigned to it and render that with the value" result = [] select_template_from_node = fetch_query_string( 'select_template_from_node.sql') try: result = db.query(select_template_from_node, **{'node_id': node_id}) template_result = result.first() if template_result and template_result.get('name'): template = template_result.get('name') if isinstance(value, dict): return render_template(template, **value) else: return render_template(template, value=value) except DatabaseError as err: current_app.logger.error("DatabaseError: %s", err) # No template assigned to this node so just return the value return value
def test_insert_one_node_with_unicode(self): """ Add a node with a unicode value """ with self.app.app_context(): init_db() trans = db.transaction() result = db.db.execute(fetch_query_string('insert_node.sql'), { 'name': 'a', 'value': u'Àрpĺè' }) a = result.lastrowid trans.commit() result = db.query('select * from Node where id = :id;', fetchall=True, **{'id': a}) assert len(result) == 1 r = result.first() assert a == r.get('id') assert 'a' == r.get('name') assert u'Àрpĺè' == r.get('value')
def test_rules(self): f = open(os.path.join(self.tmp_template_dir, 'insert_promoattr.sql'), 'w') f.write(""" insert into PromoAttr (node_id, title, description) values (:node_id, :title, :description); """) f.close() f = open(os.path.join(self.tmp_template_dir, 'select_promoattr.sql'), 'w') f.write(""" select * from PromoAttr where node_id = :node_id; """) f.close() f = open(os.path.join(self.tmp_template_dir, 'select_promos.sql'), 'w') f.write(""" select id as node_id, * from Node where name = 'promo' order by id limit 2 offset 13; """) f.close() f = open(os.path.join(self.tmp_template_dir, 'select_mainmenu.sql'), 'w') f.write(""" select name as link from Node where name like 'page_' order by link; """) f.close() f = open(os.path.join(self.tmp_template_dir, 'select_pageattr.sql'), 'w') f.write(""" select 'example title' as title, 'a description of the page' as description; """) f.close() expected = { "mainmenu": [{ "link": "page1" }, { "link": "page2" }, { "link": "page3" }], "pageattr": { "description": "a description of the page", "title": "example title" }, "promos": [{ "promo": { "description": "aaaaaaaaaaaaa", "node_id": 20, "title": "promo 13" } }, { "promo": { "description": "aaaaaaaaaaaaaa", "node_id": 21, "title": "promo 14" } }] } with self.app.app_context(): with self.app.test_client() as c: init_db() trans = db.transaction() db.query(""" create table PromoAttr ( node_id integer, abc integer, title varchar(255), description text ); """) trans.commit() page_id = insert_node(name='page1', value=None) print page_id insert_route(path='/page1/', node_id=page_id) pageattr_id = insert_node(name='pageattr', value=None) print pageattr_id insert_node_node(node_id=page_id, target_node_id=pageattr_id) insert_query(name='select_pageattr.sql', node_id=pageattr_id) mainmenu_id = insert_node(name='mainmenu', value=None) insert_node_node(node_id=page_id, target_node_id=mainmenu_id) insert_query(name='select_mainmenu.sql', node_id=mainmenu_id) # Add some other pages that will be shown in menu as just links insert_node(name='page2', value=None) insert_node(name='page3', value=None) promos_id = insert_node(name='promos', value=None) insert_node_node(node_id=page_id, target_node_id=promos_id) insert_query(name='select_promos.sql', node_id=promos_id) for a in range(0, 100): a_id = insert_node(name='promo', value=None) trans = db.transaction() db.query( fetch_query_string('insert_promoattr.sql'), **{ 'node_id': a_id, 'title': 'promo %i' % a, 'description': 'a' * a }) trans.commit() # wire the promo to it's attr insert_query(name='select_promoattr.sql', node_id=a_id) rv = c.get('/page1', follow_redirects=True) print rv assert 200 == rv.status_code rv_json = json.loads(rv.data) assert set(expected.keys()) == set(rv_json.keys()) assert set(expected['pageattr'].keys()) == set( rv_json['pageattr'].keys())