def test_delete_one_node(self): """ Delete a node """ with self.app.app_context(): init_db() c = db.cursor() c.execute(fetch_query_string('insert_node.sql'), {'name': 'a', 'value':'apple'}) a = c.lastrowid db.commit() result = c.execute(fetch_query_string('select_node_from_id.sql'), {'node_id': a}).fetchall() (result, col_names) = rowify(result, c.description) assert len(result) == 1 r = result.pop() assert a == r.get('node_id') assert 'a' == r.get('name') assert 'apple' == r.get('value') # now delete c = db.cursor() delete_node(node_id=a) result = c.execute(fetch_query_string('select_node_from_id.sql'), {'node_id': a}).fetchall() (result, col_names) = rowify(result, c.description) assert len(result) == 0
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) c = db.cursor() result = c.execute(fetch_query_string('select_link_node_from_node.sql'), {'node_id': a_id}).fetchall() (result, col_names) = rowify(result, c.description) 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 = c.execute(fetch_query_string('select_link_node_from_node.sql'), {'node_id': b_id}).fetchall() (result, col_names) = rowify(result, c.description) 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) c = db.cursor() c.execute(fetch_query_string('delete_node_for_id.sql'), {'node_id': a_id}) db.commit() result = c.execute(fetch_query_string('select_node_from_id.sql'), {'node_id': a_id}).fetchall() (result, col_names) = rowify(result, c.description) assert len(result) == 0 c = db.cursor() result = c.execute(fetch_query_string('select_link_node_from_node.sql'), {'node_id': a_id}).fetchall() (result, col_names) = rowify(result, c.description) assert len(result) == 0 result = c.execute(fetch_query_string('select_node_node_from_node_id.sql'), {'node_id': a_id}).fetchall() (result, col_names) = rowify(result, c.description) assert len(result) == 0
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. c = db.cursor() try: c.execute(fetch_query_string('select_route_where_dynamic.sql')) except sqlite3.OperationalError as err: current_app.logger.error("OperationalError: %s", err) return (None, None) result = c.fetchall() if result: (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 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) c = db.cursor() result = c.execute(fetch_query_string('select_link_node_from_node.sql'), {'node_id': a_id}).fetchall() (result, col_names) = rowify(result, c.description) 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 = c.execute(fetch_query_string('select_link_node_from_node.sql'), {'node_id': b_id}).fetchall() (result, col_names) = rowify(result, c.description) 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 link_picturename_for_node(node_id, picturename, **kw): """ Link a picture for a node id. Use this if the picture has already been added to the database. """ with current_app.app_context(): c = db.cursor() result = c.execute(fetch_query_string("select_picture_by_name.sql"), { 'picturename':picturename }) (result, col_names) = rowify(result, c.description) if result: picture = result[0].get('id') else: current_app.logger.warn('picture by name:"{0}" is not in database.'.format(filepath)) return False c.execute(fetch_query_string("insert_node_picture.sql"),{ 'node_id': node_id, 'picture': picture }) db.commit() insert_query(name='select_picture_for_node.sql', node_id=node_id) db.commit()
def insert_node(**kw): "Insert a node with a name and optional value. Return the node id." with current_app.app_context(): c = db.cursor() c.execute(fetch_query_string('insert_node.sql'), kw) node_id = c.lastrowid db.commit() return node_id
def delete_node(**kw): """ Delete a node by id. """ with current_app.app_context(): c = db.cursor() c.execute(fetch_query_string('delete_node_for_id.sql'), kw) db.commit()
def test_a(self): """ """ f = open(os.path.join(self.tmp_template_dir, 'update_llama.sql'), 'w') f.write(""" update Llama set location = :location, description = :description where llama_name = :llama_name; """) f.close() f = open(os.path.join(self.tmp_template_dir, 'select_llama.sql'), 'w') f.write(""" select * from Llama where llama_name = :llama_name; """) f.close() with self.app.app_context(): with self.app.test_client() as c: init_db() cursor = db.cursor() cursor.execute(""" create table Llama ( llama_name varchar(255), location varchar(255), description text ); """) db.commit() cursor.execute(""" insert into Llama (llama_name) values ('Pocky'); """) db.commit() llamas_id = insert_node(name='llamas', value=None) insert_route(path='/api/llamas/name/<llama_name>/', node_id=llamas_id, weight=1, method="PATCH") insert_query(name='update_llama.sql', node_id=llamas_id) llama_1 = { 'llama_name': 'Pocky', 'location': 'unknown', 'description': 'first llama' } rv = c.patch('/api/llamas/name/Pocky/', data=llama_1) assert 201 == rv.status_code select_llama = insert_node(name='llamas', value=None) insert_route(path='/api/llamas/name/<llama_name>/', node_id=select_llama, weight=1) insert_query(name='select_llama.sql', node_id=select_llama) rv = c.get('/api/llamas/name/Pocky/', follow_redirects=True) rv_json = json.loads(rv.data) assert set(llama_1.keys()) == set(rv_json.keys()) assert set(llama_1.values()) == set(rv_json.values())
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) c = db.cursor() result = c.execute(fetch_query_string('select_template_from_node.sql'), {'node_id': a}).fetchall() (result, col_names) = rowify(result, c.description) 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 c = db.cursor() result = c.execute(fetch_query_string('select_template_from_node.sql'), {'node_id': aa}).fetchall() (result, col_names) = rowify(result, c.description) 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) c = db.cursor() result = c.execute(fetch_query_string('select_template_from_node.sql'), {'node_id': a}).fetchall() (result, col_names) = rowify(result, c.description) 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 c = db.cursor() result = c.execute(fetch_query_string('select_template_from_node.sql'), {'node_id': aa}).fetchall() (result, col_names) = rowify(result, c.description) result = [x.get('name', None) for x in result] assert len(result) == 1 assert result[0] == 'template_a.html'
def select_node(**kw): """ Select node by id. """ with current_app.app_context(): c = db.cursor() result = c.execute(fetch_query_string('select_node_from_id.sql'), kw).fetchall() (result, col_names) = rowify(result, c.description) return result
def test_db(self): """Check usage of db""" with self.app.app_context(): with self.app.test_client() as c: init_db() cursor = db.cursor() cursor.execute("""insert into Node (name, value) values (:name, :value)""", {"name": "bill", "value": "?"}) db.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')) c = db.cursor() c.execute(fetch_query_string('insert_node_node.sql'), kw) db.commit()
def test_a(self): """ """ f = open(os.path.join(self.tmp_template_dir, 'delete_llama.sql'), 'w') f.write(""" Delete from Llama where llama_name = :llama_name; """) f.close() f = open(os.path.join(self.tmp_template_dir, 'select_llama.sql'), 'w') f.write(""" select * from Llama where llama_name = :llama_name; """) f.close() with self.app.app_context(): with self.app.test_client() as c: init_db() cursor = db.cursor() cursor.execute(""" create table Llama ( llama_name varchar(255), location varchar(255), description text ); """) db.commit() cursor.execute(""" insert into Llama (llama_name, location, description) values ('Docky', 'somewhere', 'damaged'); """) db.commit() select_llama = insert_node(name='llamas', value=None) insert_route(path='/api/llamas/name/<llama_name>/', node_id=select_llama, weight=1) insert_query(name='select_llama.sql', node_id=select_llama) llamas_id = insert_node(name='llamas', value=None) insert_route(path='/api/llamas/name/<llama_name>/', node_id=llamas_id, weight=1, method="DELETE") insert_query(name='delete_llama.sql', node_id=llamas_id) rv = c.get('/api/llamas/name/Docky/', follow_redirects=True) assert 200 == rv.status_code rv = c.delete('/api/llamas/name/Docky/') assert 204 == rv.status_code rv = c.get('/api/llamas/name/Docky/', follow_redirects=True) assert 404 == rv.status_code
def add_template_for_node(name, node_id): "Set the template to use to display the node" with current_app.app_context(): c = db.cursor() c.execute(fetch_query_string('insert_template.sql'), {'name':name, 'node_id':node_id}) c.execute(fetch_query_string('select_template.sql'), {'name':name, 'node_id':node_id}) result = c.fetchone() if result: template_id = result[0] c.execute(fetch_query_string('update_template_node.sql'), {'template':template_id, 'node_id':node_id}) db.commit()
def init_picture_tables(): """Create optional picture and staticfile database tables: Picture Image Srcset StaticFile Node_Picture """ with current_app.app_context(): c = db.cursor() for filename in CHILL_CREATE_PICTURE_TABLE_FILES: c.execute(fetch_query_string(filename)) db.commit()
def _link(node_id): "Add the value for a linked node" c = db.cursor() linked_value = c.execute( fetch_query_string('select_link_node_from_node.sql'), { 'node_id': node_id }).fetchall() if linked_value: if len(linked_value) > 1: list = [] for v in linked_value: list.append({v[1]: render_node(v[2], None, v[1])}) linked_value = list else: linked_value = render_node(linked_value[0][0]) #TODO return linked_value
def test_insert_one_node(self): """ Add a node """ with self.app.app_context(): init_db() c = db.cursor() c.execute(fetch_query_string('insert_node.sql'), {'name': 'a', 'value':'apple'}) a = c.lastrowid db.commit() result = c.execute('select * from Node where id = :id;', {'id':a}).fetchall() (result, col_names) = rowify(result, c.description) assert len(result) == 1 r = result.pop() assert a == r.get('id') assert 'a' == r.get('name') assert 'apple' == r.get('value')
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(): c = db.cursor() c.execute(fetch_query_string('insert_route.sql'), binding) db.commit()
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(): #db = get_db() c = db.cursor() for filename in CHILL_CREATE_TABLE_FILES: c.execute(fetch_query_string(filename)) db.commit()
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') c = db.cursor() try: c.execute(select_node_from_route, {'uri':uri, 'method':method}) except sqlite3.DatabaseError as err: current_app.logger.error("DatabaseError: %s", err) result = c.fetchall() #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: c.execute(select_node_from_route, {'uri':rule, 'method':method}) result = c.fetchall() except sqlite3.DatabaseError as err: current_app.logger.error("DatabaseError: %s", err) if result: (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_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(): c = db.cursor() result = c.execute(fetch_query_string('select_query_where_name.sql'), kw).fetchall() (result, col_names) = rowify(result, c.description) if result: kw['query_id'] = result[0].get('id') else: c.execute(fetch_query_string('insert_query.sql'), kw) kw['query_id'] = c.lastrowid c.execute(fetch_query_string('insert_query_node.sql'), kw) db.commit()
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() cursor = db.cursor() cursor.execute(""" create table PromoAttr ( node_id integer, abc integer, title varchar(255), description text ); """) db.commit() page_id = insert_node(name='page1', value=None) insert_route(path='/page1/', node_id=page_id) pageattr_id = insert_node(name='pageattr', value=None) 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) cursor.execute(fetch_query_string('insert_promoattr.sql'), {'node_id':a_id, 'title':'promo %i' % a, 'description': 'a'*a}) db.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) 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())
def add_picture_for_node(node_id, filepath, **kw): """ Add a picture for a node id. This adds to the Image, StaticFile, Picture, ... tables. The `filepath` must be an image file within the media folder. width and height are deduced from the image. Other attributes that should be associated with the picture can be passed in: title description author (and others, some have not been implemented) """ with current_app.app_context(): c = db.cursor() # media folder needs to be set media_folder = current_app.config.get('MEDIA_FOLDER') if not media_folder: current_app.logger.warn('No MEDIA_FOLDER set in config.') return False # filepath needs to exist media_filepath = os.path.join(media_folder, filepath) if not os.path.exists(media_filepath): current_app.logger.warn('filepath not exists: {0}'.format(media_filepath)) return False # file needs to be an image try: img = Image.open(media_filepath) except IOError as err: current_app.logger.warn(err) return False (width, height) = img.size c.execute(fetch_query_string("insert_staticfile.sql"), { 'path':filepath }) staticfile = c.lastrowid c.execute(fetch_query_string("insert_image.sql"),{ 'width': width, 'height': height, 'staticfile': staticfile }) image = c.lastrowid c.execute(fetch_query_string("insert_picture.sql"),{ 'picturename': filepath, 'title': kw.get('title', None), 'description': '', 'author': None, 'created': '', 'image': image }) picture = c.lastrowid c.execute(fetch_query_string("insert_node_picture.sql"),{ 'node_id': node_id, 'picture': picture }) db.commit() insert_query(name='select_picture_for_node.sql', node_id=node_id) db.commit()