def _subitem_add():
id = request.json['id']
item = get_item(id)
if not g.user['admin'] or (item['owner_id'] != g.user['id']):
abort(403)
try:
subitem_id = request.json['subitem']
# assert that subitem exists
subitem = get_item(subitem_id)
if not g.user['admin'] or (subitem['owner_id'] != g.user['id']):
abort(403)
cursor = get_db_cursor()
# check whether this item is already included somewhere
cursor.execute(
'SELECT * FROM item_relation'
' WHERE item_id2 = %s AND type = %s',
(subitem_id, Relation.REL_INCLUDES))
if cursor.fetchone() is not None:
abort(403)
# check possible recursion
if id == subitem_id or not check_parent_loops(id, subitem_id):
abort(403)
cursor.execute(
'INSERT INTO item_relation (item_id1, item_id2, type)'
' VALUES (%s, %s, %s)', (id, subitem_id, Relation.REL_INCLUDES))
db_commit()
except:
db_rollback()
abort(403)
return jsonify(result='success')
def upload_image(file, desc='', width=-1, height=-1):
if not allowed_image(file.filename):
abort(403)
if not g.user:
abort(403)
img = Image.open(file)
if width != -1:
if (img.width != width and img.height != height
) or img.width > width or img.height > height:
return None
filename = secure_filename(file.filename)
ext = filename.rsplit('.', 1)[1].lower()
cursor = get_db_cursor()
cursor.execute(
'INSERT INTO image (ext, filename, description, owner_id) VALUES (%s, %s, %s, %s)',
(
ext,
filename,
desc,
g.user['id'],
))
file_id = cursor.lastrowid
img.save(
os.path.join(
os.path.join(os.path.abspath(current_app.instance_path),
'uploads'), '%d.%s' % (file_id, ext)))
return file_id
def get_catalog_full(id):
cursor = get_db_cursor()
cursor.execute(
'SELECT c.id, c.title, c.title_eng, c.description, c.created,'
' IF(c.type = %s, 1, 0) AS is_physical,'
' IF(c.type = %s, 1, 0) AS is_group,'
' IF(c.type = %s, 1, 0) AS is_kit,'
' IF(c.type = %s, 1, 0) AS is_bits,'
' IF(c.type = %s, 1, 0) AS is_company,'
' IFNULL(c.year, "") as year,'
' ccomp.title_eng as company, ccomp.id as company_id,'
' c.type,'
' rr.catalog_id1 AS root, cr.title_eng AS root_title'
' FROM catalog c'
' LEFT JOIN catalog_relation rr ON rr.catalog_id2 = c.id AND rr.type = %s'
' LEFT JOIN catalog cr ON rr.catalog_id1 = cr.id'
' LEFT JOIN catalog_relation rcomp ON rcomp.catalog_id2 = c.id AND rcomp.type = %s'
' LEFT JOIN catalog ccomp ON rcomp.catalog_id1 = ccomp.id'
' WHERE c.id = %s', (
Type.TYPE_PHYSICAL,
Type.TYPE_ABSTRACT,
Type.TYPE_KIT,
Type.TYPE_BITS,
Type.TYPE_COMPANY,
Relation.REL_ROOT,
Relation.REL_PRODUCED,
id,
))
return cursor.fetchone()
def _create_modification():
error = None
id = int(request.args['id'])
catalog = get_catalog(id)
title = request.json['title']
title_eng = request.json['title_eng']
description = request.json['description']
year = None
if request.json['year'] != '':
try:
year = int(request.json['year'])
if year < 1500 or year > 2100:
error = 'Invalid year'
except:
error = 'Invalid year'
if title_eng is None or title_eng == "":
error = 'title_eng is required'
if error is not None:
abort(403)
try:
cursor = get_db_cursor()
catalog_id = create_catalog(cursor, catalog['type'], title, title_eng,
description, year, get_catalog_root(id))
create_relation(cursor, id, catalog_id, Relation.REL_MODIFICATION)
db_commit()
except:
db_rollback()
abort(403)
return jsonify(result='success')
def included_rec(id, t, rel):
params = ()
q = ''
if t != -1:
q = ' WHERE c.type = %s'
params = (t, )
cursor = get_db_cursor()
cursor.execute(
'WITH RECURSIVE families (id) AS ('
' SELECT %s'
' UNION DISTINCT'
' SELECT r.catalog_id1 FROM families'
' INNER JOIN catalog_relation AS r ON families.id = r.catalog_id2 AND (r.type = %s OR r.type = %s)'
' INNER JOIN catalog c ON c.id = r.catalog_id1'
' WHERE c.type = %s'
')'
'SELECT c.id, c.title_eng, IF(families.id = %s, 1, 0) AS own,'
' cr.title_eng AS root_title'
' FROM families'
' INNER JOIN catalog_relation r ON r.catalog_id2 = families.id AND r.type = %s'
' INNER JOIN catalog c ON r.catalog_id1 = c.id'
' LEFT JOIN catalog_relation rr ON rr.catalog_id2 = c.id AND rr.type = %s'
' LEFT JOIN catalog cr ON rr.catalog_id1 = cr.id' + q,
(id, Relation.REL_INCLUDES, Relation.REL_MODIFICATION,
Type.TYPE_ABSTRACT, id, rel, Relation.REL_ROOT, *params))
res = {}
for c in cursor.fetchall():
res[c['id']] = c
return res
def _upload_image():
id = request.form.get('id', -1, type=int)
item = get_item(id)
if item['owner_id'] != g.user['id']:
return abort(403)
if 'file' not in request.files:
return abort(400)
file = request.files['file']
if file:
file_id = upload_image(file, request.form.get('desc'))
cursor = get_db_cursor()
cursor.execute(
'INSERT INTO item_attribute (type, item_id, value_id)'
' VALUES (%s, %s, %s)', (
Attribute.ATTR_IMAGE,
id,
file_id,
))
db_commit()
cursor.execute('SELECT id, filename FROM image WHERE id = %s',
(file_id, ))
return jsonify(cursor.fetchone())
return abort(400)
def _filtered_count():
q = filtered_query(request.args, True)
cursor = get_db_cursor()
cursor.execute(q['query'], q['params'])
result = cursor.fetchone()
return jsonify(result)
def latest():
try:
cursor = get_db_cursor()
cursor.execute(
'SELECT c.id, c.message, c.user_id, c.created,'
' u.username, cc.ref_id AS catalog_id, ic.ref_id AS item_id,'
' CASE WHEN cat.title IS NOT NULL'
' THEN cat.title'
' ELSE icat.title'
' END AS catalog_title,'
' CASE WHEN cat.title_eng IS NOT NULL'
' THEN cat.title_eng'
' ELSE icat.title_eng'
' END AS catalog_title_eng'
' FROM comment c'
' INNER JOIN user u ON u.id = c.user_id'
' LEFT OUTER JOIN catalog_comment cc ON cc.comment_id = c.id'
' LEFT OUTER JOIN catalog cat ON cc.ref_id = cat.id'
' LEFT OUTER JOIN item_comment ic ON ic.comment_id = c.id'
' LEFT OUTER JOIN item it ON ic.ref_id = it.id'
' LEFT OUTER JOIN catalog icat ON it.catalog_id = icat.id'
' ORDER BY c.created DESC'
' LIMIT 10')
return jsonify(cursor.fetchall())
except:
abort(400)
def _set_logo():
try:
id = int(request.form['id'])
get_catalog(id)
file = request.files['file']
file_id = upload_image(file, width=64, height=64)
if file_id is None:
# Only 64x64 images can be used as a logo
abort(400)
cursor = get_db_cursor()
cursor.execute(
'DELETE FROM catalog_attribute'
' WHERE type=%s AND catalog_id=%s', (
Attribute.ATTR_LOGO,
id,
))
cursor.execute(
'INSERT INTO catalog_attribute (type, catalog_id, value_id)'
' VALUES (%s, %s, %s)', (
Attribute.ATTR_LOGO,
id,
file_id,
))
db_commit()
except:
db_rollback()
abort(403)
return jsonify(result='success')
def _filtered_list():
q = filtered_query(request.args, False)
cursor = get_db_cursor()
cursor.execute(q['query'], q['params'])
result = cursor.fetchall()
return jsonify(result)
def confirm_email():
try:
email = request.json['email']
h = request.json['h']
cursor = get_db_cursor()
cursor.execute('SELECT * FROM user WHERE email = %s', (email, ))
user = cursor.fetchone()
if user is None:
return jsonify(error='Can\'t find the user.')
if user['status'] != UserStatus.REGISTERED:
return jsonify(error='Can\'t enable this user login.')
hash = user_hash(email, user['id'])
if h != hash:
return jsonify(error='Wrong confirmation code.')
cursor.execute('UPDATE user SET status = %s WHERE id = %s', (
UserStatus.ACTIVE,
user['id'],
))
db_commit()
except:
return jsonify(error='Internal server error.')
return jsonify(success='Email was successfully validated.')
def set_password():
old_password = request.args.get('old_password').strip()
new_password = request.args.get('new_password').strip()
new_password = generate_password_hash(new_password)
error = None
if not old_password:
error = 'Password is required.'
if not new_password or new_password == '':
error = 'New password is required.'
cursor = get_db_cursor()
cursor.execute('SELECT * FROM user WHERE id = %s', (g.user['id'], ))
user = cursor.fetchone()
if not check_password_hash(user['password'], old_password):
error = 'Incorrect password.'
if error:
return jsonify(error=error)
cursor.execute('UPDATE user SET password = %s WHERE id = %s',
(new_password, g.user['id']))
db_commit()
return jsonify(result='success')
def catalog():
try:
cursor = get_db_cursor()
cursor.execute('SELECT * FROM page_catalog' ' ORDER BY num')
return jsonify(cursor.fetchall())
except:
abort(400)
def get_user(id):
cursor = get_db_cursor()
cursor.execute(
'SELECT user.id as id, username, admin, collection.id as col_id,'
' email FROM user '
' LEFT JOIN collection ON user.id = collection.owner_id'
' WHERE user.id = %s', (id, ))
return cursor.fetchone()
def get():
try:
id = request.args['id']
cursor = get_db_cursor()
cursor.execute('SELECT * FROM comment WHERE id = %s', (id, ))
return jsonify(cursor.fetchone())
except:
abort(400)
def get():
id = request.args.get('id', -1, type=int)
cursor = get_db_cursor()
cursor.execute('SELECT description FROM image WHERE id = %s', (id, ))
image = cursor.fetchone()
if image is None:
abort(403)
return jsonify(image)
def list():
# No parameters yet
cursor = get_db_cursor()
cursor.execute(
'SELECT *'
' FROM catalog_history ch'
' ORDER BY ch.created, ch.id'
)
return jsonify(cursor.fetchall())
def get_catalog_child(id, rel):
cursor = get_db_cursor()
cursor.execute(
'SELECT catalog_id2 FROM catalog_relation'
' WHERE catalog_id1 = %s AND type = %s', (id, rel))
try:
res = cursor.fetchone()['catalog_id2']
except:
res = None
return res
def get_catalog_attachments(id):
cursor = get_db_cursor()
cursor.execute(
'SELECT a.value_id AS id'
' FROM catalog_attribute a'
' WHERE a.type = %s AND a.catalog_id = %s', (
Attribute.ATTR_ATTACH,
id,
))
return cursor.fetchall()
def get_item_images(id):
cursor = get_db_cursor()
cursor.execute(
'SELECT a.value_id AS id'
' FROM item_attribute a'
' WHERE a.type = %s AND a.item_id = %s', (
Attribute.ATTR_IMAGE,
id,
))
return cursor.fetchall()
def get_item(id):
cursor = get_db_cursor()
cursor.execute(
'SELECT * FROM catalog_history ch'
' WHERE id = %s', (id,)
)
it = cursor.fetchone()
if it is None:
abort(403, "Catalog history id {0} doesn't exist.".format(id))
return it
def get():
id = request.args.get('id', -1, type=int)
cursor = get_db_cursor()
cursor.execute(
'SELECT filename, description FROM attachment WHERE id = %s',
(id,)
)
res = cursor.fetchone()
if res is None:
abort(403)
return jsonify(res)
def approve():
id = int(request.args['id'])
item = get_item(id)
cursor = get_db_cursor()
cursor.execute(
'DELETE FROM catalog_history WHERE id = %s', (id,)
)
db_commit()
return jsonify(result='success')
def get_catalog_logo_own(id):
cursor = get_db_cursor()
cursor.execute(
'SELECT img.id'
' FROM catalog c JOIN catalog_attribute a ON c.id = a.catalog_id'
' JOIN image img ON a.value_id = img.id'
' WHERE a.type = %s AND c.id = %s', (
Attribute.ATTR_LOGO,
id,
))
res = cursor.fetchone()
if res:
return res['id']
return None
def _categories():
cursor = get_db_cursor()
cursor.execute(
'SELECT c.id, c.title_eng FROM catalog c'
' LEFT JOIN catalog_relation r'
' ON c.id = r.catalog_id2 AND r.type = %s'
' WHERE r.catalog_id1 IS NULL'
' AND c.type = %s', (
Relation.REL_ROOT,
Type.TYPE_ABSTRACT,
))
result = cursor.fetchall()
return jsonify(result)
def item():
try:
id = request.args['id']
cursor = get_db_cursor()
cursor.execute(
'SELECT c.id, c.message, c.user_id, c.created,'
' u.username FROM comment c'
' INNER JOIN item_comment ii ON ii.comment_id = c.id'
' INNER JOIN user u ON u.id = c.user_id'
' WHERE ii.ref_id = %s'
' ORDER BY c.created DESC', (id, ))
return jsonify(cursor.fetchall())
except:
abort(400)
def get_user_collection(id):
cursor = get_db_cursor()
cursor.execute(
'SELECT id, title, description, created, owner_id'
' FROM collection'
' WHERE owner_id = %s',
(id,)
)
collection = cursor.fetchone()
if collection is None:
abort(404, "Collection id {0} doesn't exist.".format(id))
return collection
def _filtered_list():
# No parameters yet
db = get_db_cursor()
db.execute(
'SELECT c.id, title, description, created, owner_id, username,'
' (SELECT COUNT(*) FROM item it WHERE it.collection_id=c.id) AS count'
' FROM collection c JOIN user u ON c.owner_id = u.id'
' WHERE u.status = %s'
' ORDER BY title',
(UserStatus.ACTIVE,)
)
collections = db.fetchall()
return jsonify(collections)
def upload_file(file, desc):
if not g.user:
abort(403)
filename = secure_filename(file.filename)
cursor = get_db_cursor()
cursor.execute(
'INSERT INTO attachment (filename, description, owner_id) VALUES (%s, %s, %s)',
(filename, desc, g.user['id'],)
)
file_id = cursor.lastrowid
file.save(os.path.join(
os.path.join(os.path.abspath(current_app.instance_path), 'uploads'),
'%d' % (file_id,)))
return file_id
def undo():
id = int(request.args['id'])
item = get_item(id)
if item['field'] == 'create':
# no cascade delete yet
abort(403)
cursor = get_db_cursor()
cursor.execute(item['undo_query'])
cursor.execute(
'DELETE FROM catalog_history WHERE id = %s', (id,)
)
db_commit()
return jsonify(result='success')
