Ejemplo n.º 1
0
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')
Ejemplo n.º 2
0
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
Ejemplo n.º 3
0
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()
Ejemplo n.º 4
0
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')
Ejemplo n.º 5
0
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
Ejemplo n.º 6
0
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)
Ejemplo n.º 7
0
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)
Ejemplo n.º 8
0
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)
Ejemplo n.º 9
0
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')
Ejemplo n.º 10
0
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)
Ejemplo n.º 11
0
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.')
Ejemplo n.º 12
0
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')
Ejemplo n.º 13
0
def catalog():
    try:
        cursor = get_db_cursor()
        cursor.execute('SELECT * FROM page_catalog' ' ORDER BY num')

        return jsonify(cursor.fetchall())
    except:
        abort(400)
Ejemplo n.º 14
0
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()
Ejemplo n.º 15
0
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)
Ejemplo n.º 16
0
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)
Ejemplo n.º 17
0
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())
Ejemplo n.º 18
0
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
Ejemplo n.º 19
0
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()
Ejemplo n.º 20
0
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()
Ejemplo n.º 21
0
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
Ejemplo n.º 22
0
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)
Ejemplo n.º 23
0
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')
Ejemplo n.º 24
0
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
Ejemplo n.º 25
0
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)
Ejemplo n.º 26
0
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)
Ejemplo n.º 27
0
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
Ejemplo n.º 28
0
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)
Ejemplo n.º 29
0
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
Ejemplo n.º 30
0
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')