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 _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 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 _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 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 _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 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 set_username():
username = request.args.get('username').strip()
cursor = get_db_cursor()
cursor.execute('SELECT id FROM user WHERE username = %s AND id <> %s', (
username,
g.user['id'],
))
if cursor.fetchone() is not None:
return jsonify(
error='User with name {} is already registered.'.format(username))
cursor.execute('UPDATE user SET username = %s WHERE id = %s',
(username, g.user['id']))
db_commit()
return jsonify(result='success')
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')
def _relation_remove():
id1 = int(request.json['id1'])
id2 = int(request.json['id2'])
rel = request.json['rel']
rel_id = -1
if rel == 'includes':
rel_id = Relation.REL_INCLUDES
elif rel == 'compatible':
rel_id = Relation.REL_COMPATIBLE
elif rel == 'produced':
rel_id = Relation.REL_PRODUCED
else:
abort(403)
cursor = get_db_cursor()
delete_relation(cursor, id1, id2, rel_id)
db_commit()
return jsonify(result='success')
def item_add():
try:
id = request.json['id']
comment = request.json['comment']
user_id = g.user['id']
cursor = get_db_cursor()
cursor.execute(
'INSERT INTO comment (user_id, message)'
' VALUES (%s, %s)', (user_id, comment))
comment_id = cursor.lastrowid
cursor.execute(
'INSERT INTO item_comment (comment_id, ref_id)'
' VALUES (%s, %s)', (comment_id, id))
db_commit()
return jsonify(result='success')
except:
abort(400)
def _upload_file():
id = request.form.get('id', -1, type=int)
get_catalog(id)
if 'file' not in request.files:
return abort(400)
file = request.files['file']
if file:
file_id = upload_file(file, request.form.get('desc'))
cursor = get_db_cursor()
create_attribute(cursor, id, Attribute.ATTR_ATTACH, file_id)
db_commit()
cursor.execute('SELECT id, filename FROM attachment WHERE id = %s',
(file_id, ))
return jsonify(cursor.fetchone())
return abort(400)
def _update():
id = int(request.args['id'])
catalog = get_catalog(id)
try:
field = request.json['field']
value = request.json['value']
if field not in ['title', 'title_eng', 'description', 'year']:
abort(403)
if field == 'year':
if value == "":
value = None
else:
year = int(value)
if year < 1500 or year > 2100:
abort(403)
value = year
cursor = get_db_cursor()
# field is validated, use concatenation here
cursor.execute('UPDATE catalog SET ' + field + ' = %s WHERE id = %s',
(value, id))
if not g.user['admin']:
old_value = catalog[field]
if old_value:
if field != 'year':
old_value = '"' + db_escape_string(old_value) + '"'
else:
old_value = 'NULL'
cursor.execute(
'INSERT INTO catalog_history'
' (catalog_id, user_id, field, value, old_value, undo_query, description)'
' VALUES (%s, %s, %s, %s, %s, %s, %s)',
(id, g.user['id'], field, value, catalog[field],
'UPDATE catalog SET %s = %s WHERE id = %s' % (
field,
old_value,
id,
), 'Change %s from %s to %s' %
(field, catalog[field], value)))
db_commit()
except:
db_rollback()
abort(403)
return jsonify(result='success')
def _own():
id = int(request.args['id'])
catalog = get_catalog(id)
if not catalog['is_physical'] and not catalog['is_kit']:
abort(403)
if not catalog['root']:
abort(403)
collection = get_user_collection(g.user['id'])
try:
main = request.json["-1"]
iid = ''
if 'internal' in main:
iid = main['internal']
cursor = get_db_cursor()
item_id = add_ownership(cursor, id, iid, collection['id'])
for _, attr in request.json.items():
subitem = attr['id']
subid = int(subitem)
if id == subid:
continue
if attr['use']:
# assert that catalog item exists
get_catalog(subid)
iid = ''
if 'internal' in attr:
iid = attr['internal']
subitem_id = add_ownership_all(cursor, subid, iid,
collection['id'])
cursor.execute(
'INSERT INTO item_relation (item_id1, item_id2, type)'
' VALUES (%s, %s, %s)',
(item_id, subitem_id, Relation.REL_INCLUDES))
db_commit()
except:
db_rollback()
abort(500)
return jsonify(result='success')
def _update():
id = int(request.args['id'])
item = get_item(id)
if not g.user['admin'] or (item['owner_id'] != g.user['id']):
abort(403)
try:
field = request.json['field']
value = request.json['value']
if field not in ['internal_id', 'description']:
abort(403)
cursor = get_db_cursor()
# field is validated, use concatenation here
cursor.execute('UPDATE item SET ' + field + ' = %s WHERE id = %s',
(value, id))
db_commit()
except:
db_rollback()
abort(403)
return jsonify(result='success')
def _delete_image():
id = request.args.get('id', -1, type=int)
get_catalog(id)
img = request.args.get('img', -1, type=int)
if img == -1:
return abort(400)
cursor = get_db_cursor()
cursor.execute(
'DELETE FROM catalog_attribute WHERE type = %s'
' AND catalog_id = %s AND value_id = %s', (
Attribute.ATTR_IMAGE,
id,
img,
))
db_commit()
return jsonify(result='success')
def _delete_image():
id = request.args.get('id', -1, type=int)
item = get_item(id)
if not g.user['admin'] or (item['owner_id'] != g.user['id']):
return abort(403)
img = request.args.get('img', -1, type=int)
if img == -1:
return abort(400)
cursor = get_db_cursor()
cursor.execute(
'DELETE FROM item_attribute WHERE type = %s'
' AND item_id = %s AND value_id = %s', (
Attribute.ATTR_IMAGE,
id,
img,
))
db_commit()
return jsonify(result='success')
def _software_add():
id = request.json['id']
item = get_item(id)
if item['root_title'] != 'Data storage':
abort(403)
if not g.user['admin'] or (item['owner_id'] != g.user['id']):
abort(403)
try:
soft_id = request.json['software']
soft = get_catalog(soft_id)
if soft['type'] != Type.TYPE_BITS:
abort(403)
cursor = get_db_cursor()
cursor.execute(
'INSERT INTO catalog_item_relation (catalog_id, item_id, type)'
' VALUES (%s, %s, %s)', (soft_id, id, Relation.REL_STORES))
db_commit()
except:
db_rollback()
abort(403)
return jsonify(result='success')
def _create_kit():
id = int(request.args['id'])
catalog = get_catalog(id)
# TODO
if not catalog['is_physical'] and not catalog['is_kit']:
abort(403)
kit_type = Type.TYPE_KIT
title = request.json['title']
title_eng = request.json['title_eng']
if not title_eng:
abort(403)
try:
cursor = get_db_cursor()
cursor.execute(
'SELECT c.id FROM catalog c'
' LEFT JOIN catalog_relation r'
' ON r.catalog_id2 = c.id'
' AND r.type = %s'
' WHERE title_eng = "Kit" AND r.catalog_id1 IS NULL',
(Relation.REL_ROOT, ))
root = cursor.fetchone()
kit_id = create_catalog(cursor, kit_type, title, title_eng, '', None,
root['id'])
# Add main item into the kit
create_relation(cursor, kit_id, id, Relation.REL_INCLUDES)
create_relation(cursor, kit_id, id, Relation.REL_MAIN_ITEM)
db_commit()
except:
db_rollback()
abort(403)
return jsonify(result='success')
def _relation_add():
id1 = int(request.json['id1'])
id2 = int(request.json['id2'])
rel = Relation.get_id(request.json['rel'])
# check rules
c1 = get_catalog(id1)
c2 = get_catalog(id2)
# don't add anything to root categories
if not c1['root'] or not c2['root']:
return error("can't add root category")
t1 = c1['type']
t2 = c2['type']
if rel == Relation.REL_INCLUDES:
if t1 == Type.TYPE_ABSTRACT:
# groups may include only within the same root
if c1['root'] != c2['root']:
return error("can't add group from different root")
elif t1 == Type.TYPE_PHYSICAL:
if t2 not in [Type.TYPE_PHYSICAL, Type.TYPE_BITS]:
return error("can't add to physical item")
elif t1 == Type.TYPE_KIT:
if t2 not in [Type.TYPE_PHYSICAL, Type.TYPE_KIT]:
return error("can't add to kit")
else:
return error("can't set such relation")
# TODO bits includes bits instead of stores?
elif rel == Relation.REL_MAIN_ITEM:
return error("can't set main item")
elif rel == Relation.REL_MODIFICATION:
return error("can't set modification")
elif rel == Relation.REL_STORES:
if t2 != Type.TYPE_BITS or not t1 in [
Type.TYPE_PHYSICAL, Type.TYPE_BITS
]:
return error("can't set storage for software")
elif rel == Relation.REL_COMPATIBLE:
# first one is platform
if not t1 in [Type.TYPE_ABSTRACT, Type.TYPE_BITS, Type.TYPE_PHYSICAL]:
return error("incorrect first item for compatible relation")
if t2 == Type.TYPE_COMPANY:
return error("incorrect second item for compatible relation")
elif rel == Relation.REL_ROOT:
return error("can't set root relation")
elif rel == Relation.REL_PRODUCED:
# first one is company
if t1 != Type.TYPE_COMPANY or t2 == Type.TYPE_COMPANY:
return error("first one should be company, and second one not")
# check loops
if not check_parent_loops(id1, id2) or id1 == id2:
return error("there is already relation between items")
cursor = get_db_cursor()
create_relation(cursor, id1, id2, rel)
db_commit()
return success()
def register():
if not g.user is None:
return jsonify(error='Please logout before registering')
try:
email = request.json['email'].strip()
username = request.json['username'].strip()
username = username[:64]
password = request.json['password'].strip()
collection_title = request.json['collection_title']
collection_description = request.json['collection_desc']
except:
return jsonify(error='Missing some parameters')
error = None
cursor = get_db_cursor()
if not email or not re.match("[^@]+@[^@]+.[^@]+", email):
error = 'Valid E-mail is required.'
elif not username:
error = 'Username is required.'
elif not password:
error = 'Password is required.'
elif not collection_title:
error = 'Collection title is required.'
else:
cursor.execute('SELECT id FROM user WHERE email = %s', (email, ))
if cursor.fetchone() is not None:
error = 'User with email {} is already registered.'.format(email)
else:
cursor.execute('SELECT id FROM user WHERE username = %s',
(username, ))
if cursor.fetchone() is not None:
error = 'User with name {} is already registered.'.format(
username)
if error is None:
try:
cursor.execute(
'INSERT INTO user (email, username, password) VALUES (%s, %s, %s)',
(email, username, generate_password_hash(password)))
id = cursor.lastrowid
cursor.execute(
'INSERT INTO collection (title, description, owner_id)'
' VALUES (%s, %s, %s)',
(collection_title, collection_description, id))
session.clear()
#session['user_id'] = id
if os.environ.get('FLASK_ENV') != 'development':
confirm_register(email, id)
else:
cursor.execute('UPDATE user SET status = %s WHERE id = %s', (
UserStatus.ACTIVE,
id,
))
db_commit()
except:
return jsonify(error='Internal server error')
return jsonify(result='success')
return jsonify(error=error)
def _create():
error = None
type_id = Type.get_id(request.json['type'])
if type_id == -1:
error = 'Invalid type'
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'
try:
parent_id = int(request.json['parent'])
parent = get_catalog(parent_id)
root_id = get_catalog_root(parent_id)
# parent is itself a root
if root_id is None:
root_id = parent_id
#parent_id = -1
except:
error = 'Invalid parent id'
# override root, if specified
try:
id = int(request.json['root'])
if id != -1:
root = get_catalog(id)
if root['is_group'] == 0 or root['root']:
raise Exception
root_id = id
except:
error = 'Invalid root id'
if parent['is_group'] != 1 and type == Type.TYPE_ABSTRACT:
error = 'Cannot add group not to group'
if title_eng is None or title_eng == "":
error = 'title_eng is required'
if error is not None:
# print(error)
abort(403, error)
try:
cursor = get_db_cursor()
catalog_id = create_catalog(cursor, type_id, title, title_eng,
description, year, root_id)
if parent_id != -1:
create_relation(cursor, parent_id, catalog_id,
Relation.REL_INCLUDES)
db_commit()
except:
db_rollback()
abort(403)
return jsonify(result='success', id=catalog_id)
def _join():
data = request.get_json(force=True)
cursor = get_db_cursor()
try:
id1 = int(data['id1'])
id2 = int(data['id2'])
if id1 == id2:
return error("can't join the item with itself")
title = data['title']
title_eng = data['title_eng']
year = data['year']
description = data['description']
if not title_eng or title_eng == '':
return error("title_eng wasn't specified")
if not title or title == '':
title = title_eng
if year == '':
year = None
# assert that catalog items exist
c1 = get_catalog(id1)
c2 = get_catalog(id2)
if c1['company_id'] != c2['company_id']:
return error("TODO: companies should match")
if c1['root'] != c2['root']:
return error("roots should match")
if c1['type'] != c2['type']:
return error("types should match")
if get_catalog_logo_own(id1) or get_catalog_logo_own(id2):
return error("TODO: can't join items with logo")
cursor.execute(
'SELECT * FROM catalog_relation'
' WHERE catalog_id1 = %s AND catalog_id2 = %s', (
id1,
id2,
))
if cursor.fetchone():
return error("items relate to each other")
cursor.execute(
'SELECT * FROM catalog_history'
' WHERE catalog_id = %s OR catalog_id = %s', (
id1,
id2,
))
if cursor.fetchone():
return error('items modifications should be approved or deleted')
cursor.execute(
'SELECT * FROM catalog_relation'
' WHERE catalog_id1 = %s AND catalog_id2 = %s', (
id2,
id1,
))
if cursor.fetchone():
return error("items relate to each other")
cursor.execute(
'SELECT * FROM page_catalog_section'
' WHERE parent = %s OR parent = %s', (
id1,
id2,
))
if cursor.fetchone():
return error("can't join page directory items")
# Set new parameters of the catalog item
cursor.execute(
'UPDATE catalog SET title = %s, title_eng = %s, description = %s,'
' year = %s'
' WHERE id = %s', (
title,
title_eng,
description,
year,
id1,
))
# Redirect items
cursor.execute('UPDATE item SET catalog_id = %s WHERE catalog_id = %s',
(
id1,
id2,
))
# Redirect attributes
cursor.execute(
'UPDATE catalog_attribute SET catalog_id = %s WHERE catalog_id = %s',
(
id1,
id2,
))
# save kit relations to add later
cursor.execute(
'SELECT c.id FROM catalog c'
' INNER JOIN catalog_relation cr ON c.id = cr.catalog_id1'
' WHERE c.type = %s AND cr.catalog_id2 = %s AND cr.type = %s'
' AND EXISTS (SELECT * FROM catalog_relation'
' WHERE catalog_id1=c.id AND catalog_id2=%s AND type=%s)', (
Type.TYPE_KIT,
id2,
Relation.REL_INCLUDES,
id1,
Relation.REL_INCLUDES,
))
kits = cursor.fetchall()
# Delete duplicate relations
for rel in range(Relation.REL_END):
# id2 is main
cursor.execute(
'DELETE FROM catalog_relation WHERE catalog_id1 = %s AND type = %s'
' AND catalog_id2 IN (SELECT DISTINCT catalog_id2 FROM'
' (SELECT * FROM catalog_relation'
' WHERE catalog_id1 = %s AND type = %s) AS tmp)', (
id2,
rel,
id1,
rel,
))
# id2 is part
cursor.execute(
'DELETE FROM catalog_relation WHERE catalog_id2 = %s AND type = %s'
' AND catalog_id1 IN (SELECT DISTINCT catalog_id1 FROM'
' (SELECT * FROM catalog_relation'
' WHERE catalog_id2 = %s AND type = %s) AS tmp)', (
id2,
rel,
id1,
rel,
))
# add kits back
for k in kits:
create_relation(cursor, k['id'], id1, Relation.REL_INCLUDES)
# Redirect relations
cursor.execute(
'UPDATE catalog_relation SET catalog_id1 = %s WHERE catalog_id1 = %s',
(
id1,
id2,
))
cursor.execute(
'UPDATE catalog_relation SET catalog_id2 = %s WHERE catalog_id2 = %s',
(
id1,
id2,
))
# Redirect catalog item relations
cursor.execute(
'UPDATE catalog_item_relation SET catalog_id = %s WHERE catalog_id = %s',
(
id1,
id2,
))
# Redirect comment relations
cursor.execute(
'UPDATE catalog_comment SET ref_id = %s WHERE ref_id = %s', (
id1,
id2,
))
# Delete old catalog item
cursor.execute('DELETE FROM catalog WHERE id = %s', (id2, ))
# Commit all the changes
db_commit()
except:
db_rollback()
return error("uknown error")
return success()
