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 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_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 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 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 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 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 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 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 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 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_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 delete(self, uri=''):
"For sql queries that start with 'DELETE from ...'"
# get node...
(node, rule_kw) = node_from_uri(uri, method=request.method)
rule_kw.update(node)
values = rule_kw
xhr_data = request.get_json()
if xhr_data:
values.update(xhr_data)
values.update(request.form.to_dict(flat=True))
values.update(request.args.to_dict(flat=True))
values['method'] = request.method
# Execute the sql query with the data
_query(node.get('id'), **values)
db.commit()
response = make_response('ok', 204)
return response
def delete(self, uri=''):
"For sql queries that start with 'DELETE from ...'"
# get node...
(node, rule_kw) = node_from_uri(uri, method=request.method)
rule_kw.update( node )
values = rule_kw
xhr_data = request.get_json()
if xhr_data:
values.update( xhr_data )
values.update( request.form.to_dict(flat=True) )
values.update( request.args.to_dict(flat=True) )
values['method'] = request.method
# Execute the sql query with the data
_query(node.get('id'), **values)
db.commit()
response = make_response('ok', 204)
return response
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()
