def reorder():
orig = request.form.get('orig')
dest = request.form.get('dest')
refer = request.form.get('refer')
db = get_db()
orig_seq = db.execute(
'SELECT seq FROM bookmark WHERE bookmark = ? AND user_id = ?',
(orig, g.user['id'])).fetchone()['seq']
if dest != '#TOP_POSITION#':
dest_seq = db.execute(
'SELECT seq FROM bookmark WHERE bookmark = ? AND user_id = ?',
(dest, g.user['id'])).fetchone()['seq']
else:
dest_seq = db.execute(
'SELECT seq FROM bookmark WHERE bookmark = ? AND user_id = ?',
(refer, g.user['id'])).fetchone()['seq'] - 1
if orig_seq > dest_seq:
dest_seq += 1
db.execute(
'UPDATE bookmark SET seq = seq+1 WHERE seq >= ? AND user_id = ? AND seq < ?',
(dest_seq, g.user['id'], orig_seq))
else:
db.execute(
'UPDATE bookmark SET seq = seq-1 WHERE seq <= ? AND user_id = ? AND seq > ?',
(dest_seq, g.user['id'], orig_seq))
db.execute(
'UPDATE bookmark SET seq = ? WHERE bookmark = ? AND user_id = ?',
(dest_seq, orig, g.user['id']))
db.commit()
return '1'
def edit_category(id):
'''Edit a category for the current user.'''
db = get_db()
category = db.execute(
'SELECT * FROM category WHERE id = ? AND user_id = ?',
(id, g.user['id'])).fetchone()
if not category:
abort(403)
if request.method == 'POST':
old = category['category']
new = request.form.get('category').strip()
error = 0
if new == '':
message = 'New category name is empty.'
error = 1
elif old == new:
message = 'New category is same as old category.'
elif len(new.encode('utf-8')) > 15:
message = 'Category name exceeded length limit.'
error = 1
elif db.execute(
'SELECT id FROM category WHERE category = ? AND user_id = ?',
(new, g.user['id'])).fetchone() is not None:
message = f'Category {new} is already existed.'
error = 1
else:
db.execute(
'UPDATE category SET category = ? WHERE id = ? AND user_id = ?',
(new, id, g.user['id']))
db.commit()
return jsonify({'status': 1})
return jsonify({'status': 0, 'message': message, 'error': error})
return render_template('bookmark/category.html', id=id, category=category)
def delete_bookmark(id):
'''Edit a bookmark for the current user.'''
db = get_db()
db.execute('DELETE FROM bookmark WHERE id = ? and user_id = ?',
(id, g.user['id']))
db.commit()
return jsonify({'status': 1})
def load_logged_in_user():
'''If a user id is stored in the session, load the user object from
the database into ``g.user``.'''
user_id = session.get('user_id')
db = get_db()
if user_id is None:
g.user = None
else:
g.user = db.execute('SELECT * FROM user WHERE id = ?',
(user_id, )).fetchone()
def delete_category(id):
'''Edit a category for the current user.'''
db = get_db()
db.execute('DELETE FROM category WHERE id = ? and user_id = ?',
(id, g.user['id']))
db.execute(
'UPDATE bookmark SET category_id = 0 WHERE category_id = ? and user_id = ?',
(id, g.user['id']))
db.commit()
return jsonify({'status': 1})
def edit_bookmark(id):
'''Edit a bookmark for the current user.'''
db = get_db()
bookmark = db.execute(
'SELECT bookmark, url, category FROM bookmark'
' LEFT JOIN category ON category_id = category.id'
' WHERE bookmark.id = ? AND bookmark.user_id = ?',
(id, g.user['id'])).fetchone()
if not bookmark:
abort(403)
else:
if not bookmark['category']:
bookmark['category'] = ''
categories = db.execute(
'SELECT category FROM category WHERE user_id = ? ORDER BY category',
(g.user['id'], )).fetchall()
if request.method == 'POST':
old = (bookmark['bookmark'], bookmark['url'], bookmark['category'])
bookmark = request.form.get('bookmark').strip()
url = request.form.get('url').strip()
category = request.form.get('category').strip()
category_id = get_category_id(category, g.user['id'])
error = 0
if bookmark == '':
message = f'Bookmark name is empty.'
error = 1
elif old == (bookmark, url, category):
message = 'New bookmark is same as old bookmark.'
elif db.execute(
'SELECT id FROM bookmark WHERE bookmark = ? AND id != ? AND user_id = ?',
(bookmark, id, g.user['id'])).fetchone() is not None:
message = f'Bookmark name {bookmark} is already existed.'
error = 1
elif db.execute(
'SELECT id FROM bookmark WHERE url = ? AND id != ? AND user_id = ?',
(url, id, g.user['id'])).fetchone() is not None:
message = f'Bookmark url {url} is already existed.'
error = 2
elif category_id is None:
message = 'Category name exceeded length limit.'
error = 3
else:
db.execute(
'UPDATE bookmark SET bookmark = ?, url = ?, category_id = ?'
' WHERE id = ? AND user_id = ?',
(bookmark, url, category_id, id, g.user['id']))
db.commit()
return jsonify({'status': 1})
return jsonify({'status': 0, 'message': message, 'error': error})
return render_template('bookmark/bookmark.html',
id=id,
bookmark=bookmark,
categories=categories)
def add_bookmark():
'''Create a new bookmark for the current user.'''
category_id = request.args.get('category_id')
db = get_db()
if category_id:
category = db.execute(
'SELECT category FROM category WHERE id = ? AND user_id = ?',
(category_id, g.user['id'])).fetchone()['category']
else:
category = ''
categories = db.execute(
'SELECT category FROM category WHERE user_id = ? ORDER BY category',
(g.user['id'], )).fetchall()
if request.method == 'POST':
category = request.form.get('category').strip()
bookmark = request.form.get('bookmark').strip()
url = request.form.get('url').strip()
category_id = get_category_id(category, g.user['id'])
error = 0
if bookmark == '':
message = f'Bookmark name is empty.'
error = 1
elif db.execute(
'SELECT id FROM bookmark WHERE bookmark = ? AND user_id = ?',
(bookmark, g.user['id'])).fetchone() is not None:
message = f'Bookmark name {bookmark} is already existed.'
error = 1
elif db.execute(
'SELECT id FROM bookmark WHERE url = ? AND user_id = ?',
(url, g.user['id'])).fetchone() is not None:
message = f'Bookmark url {url} is already existed.'
error = 2
elif category_id is None:
message = 'Category name exceeded length limit.'
error = 3
else:
db.execute(
'INSERT INTO bookmark (bookmark, url, user_id, category_id)'
' VALUES (?, ?, ?, ?)',
(bookmark, url, g.user['id'], category_id))
db.commit()
return jsonify({'status': 1})
return jsonify({'status': 0, 'message': message, 'error': error})
return render_template('bookmark/bookmark.html',
id=0,
bookmark={'category': category},
categories=categories)
def get_category_id(category, user_id):
if category:
db = get_db()
category_id = db.execute(
'SELECT id FROM category WHERE category = ? AND user_id = ?',
(category, user_id)).fetchone()
if len(category.encode('utf-8')) > 15:
return None
elif category_id:
return category_id['id']
else:
db.execute(
'INSERT INTO category (category, user_id) VALUES (?, ?)',
(category, user_id))
return db.execute('SELECT last_insert_rowid() id').fetchone()['id']
else:
return 0
def login():
'''Log in a user by adding the user id to the session.'''
if g.user:
return redirect(url_for('index'))
if request.method == 'POST':
username = request.form.get('username').strip()
password = request.form.get('password')
rememberme = request.form.get('rememberme')
db = get_db()
error = None
try:
user = db.execute('SELECT * FROM user WHERE username = ?',
(username.lower(), )).fetchone()
except:
tables = db.execute('SELECT name FROM sqlite_master').fetchall()
if tables == []:
init_db()
flash('Detected first time running. Initialized the database.')
else:
flash(
'Critical Error! Please contact your system administrator.'
)
return render_template('auth/login.html')
if user is None:
error = 'Incorrect username.'
elif not check_password_hash(
user['password'], password) and user['password'] != password:
error = 'Incorrect password.'
if error is None:
# store the user id in a new session and return to the index
session.clear()
session['user_id'] = user['id']
if rememberme == 'on':
session.permanent = True
else:
session.permanent = False
return redirect(url_for('index'))
flash(error)
return render_template('auth/login.html')
def get_category():
'''Get current user's categories.'''
db = get_db()
total = db.execute(
'SELECT count(bookmark) num FROM bookmark WHERE user_id = ?',
(g.user['id'], )).fetchone()['num']
uncategorized = db.execute(
'SELECT count(bookmark) num FROM bookmark WHERE category_id = 0 AND user_id = ?',
(g.user['id'], )).fetchone()['num']
categories = db.execute(
'SELECT category.id, category, count(bookmark) num'
' FROM category LEFT JOIN bookmark ON category.id = category_id'
' WHERE category.user_id = ? GROUP BY category.id ORDER BY category',
(g.user['id'], )).fetchall()
return jsonify({
'total': total,
'uncategorized': uncategorized,
'categories': categories
})
def add_category():
'''Create a new category for the current user.'''
if request.method == 'POST':
db = get_db()
category = request.form.get('category').strip()
if category == '':
message = 'Category name is empty.'
elif len(category.encode('utf-8')) > 15:
message = 'Category name exceeded length limit.'
elif db.execute(
'SELECT id FROM category WHERE category = ? AND user_id = ?',
(category, g.user['id'])).fetchone() is not None:
message = f'Category {category} is already existed.'
else:
db.execute(
'INSERT INTO category (category, user_id) VALUES (?, ?)',
(category, g.user['id']))
db.commit()
return jsonify({'status': 1})
return jsonify({'status': 0, 'message': message, 'error': 1})
return render_template('bookmark/category.html', id=0, category={})
def setting():
'''Change current user's password.'''
if request.method == 'POST':
password = request.form.get('password')
password1 = request.form.get('password1')
password2 = request.form.get('password2')
db = get_db()
error = 0
message = None
user = db.execute('SELECT password FROM user WHERE id = ?',
(g.user['id'], )).fetchone()
if not check_password_hash(user['password'],
password) and user['password'] != password:
message = 'Incorrect password.'
error = 1
elif password1 != password2:
message = "Confirm password doesn't match new password."
error = 2
elif password1 == password:
message = 'New password cannot be the same as your current password.'
error = 2
elif password1 is None or password1 == '':
message = 'New password cannot be blank.'
if not message:
# Store new password in the database and go to
# the login page
db.execute(
'UPDATE user SET password = ? WHERE id = ?',
(generate_password_hash(password1), g.user['id']),
)
db.commit()
session.clear()
return jsonify({'status': 1})
return jsonify({'status': 0, 'message': message, 'error': error})
return render_template('auth/setting.html')
def bookmark():
'''Show the bookmarks belong the current user.'''
category_id = request.args.get('category')
db = get_db()
if category_id is None:
category = {'id': -1, 'name': 'All Bookmarks'}
bookmarks = db.execute(
'SELECT bookmark.id, bookmark, url, category FROM bookmark'
' LEFT JOIN category ON category_id = category.id'
' WHERE bookmark.user_id = ? ORDER BY seq',
(g.user['id'], )).fetchall()
for i in bookmarks:
if not i['category']:
i['category'] = ''
elif category_id == '0':
category = {'id': 0, 'name': 'Uncategorized'}
bookmarks = db.execute(
'SELECT id, bookmark, url FROM bookmark'
' WHERE category_id = 0 AND user_id = ?'
' ORDER BY seq', (g.user['id'], )).fetchall()
else:
category = {'id': int(category_id)}
try:
category['name'] = db.execute(
'SELECT category FROM category WHERE id = ? AND user_id = ?',
(category_id, g.user['id'])).fetchone()['category']
except TypeError:
abort(403)
bookmarks = db.execute(
'SELECT id, bookmark, url FROM bookmark'
' WHERE category_id = ? AND user_id = ?'
' ORDER BY seq', (category_id, g.user['id'])).fetchall()
for i in bookmarks:
i['category'] = category['name']
return render_template('bookmark/index.html',
category=category,
bookmarks=bookmarks)
