def store_album(user_name, album_title, album):
logger.debug('{Modules|Edit} store_album(%s, %s, %s)', user_name, album_title, album)
with db.pg_connection(config['app-database']) as (_, cur, err):
if err:
logger.error(err)
return False
try:
cur.execute(
'''
UPDATE travel_log.album
SET album_desc = %(description)s,
autoplay_delay = %(autoplay_delay)s,
background = CASE WHEN substring(%(background)s, 1, 1) = '#' THEN %(background)s ELSE background END
FROM travel_log.user
WHERE album.fk_user = "******".id_user
AND "user".user_name = %(user)s
AND album.album_title = %(album)s
AND NOT album.is_deleted
''',
{
'user': user_name,
'album': album_title,
'description': album['description'],
'autoplay_delay': album['autoplay_delay'],
'background': album['background']
}
)
except Exception as e:
logger.error(e)
return False
return True
def _make_file_path(user_name, album_title, fname):
with db.pg_connection(config['app-database']) as (_, cur, err):
if err:
return False
# Get database id's
data = db.query_one(
cur, '''
SELECT u.id_user, a.id_album
FROM travel_log.album a
JOIN travel_log.user u ON u.id_user = a.fk_user
WHERE u.user_name = %(user)s AND album_title = %(album)s AND NOT is_deleted
''', {
'user': user_name,
'album': album_title
})
uid, aid = data.id_user, data.id_album
# Generate file path and name
fs_path = os.path.join(config['storage-engine']['path'], str(uid),
str(aid))
if not os.path.isdir(fs_path):
os.makedirs(fs_path)
filename = hashlib.sha256(
('%s-%s-%s-%s' % (uid, aid, time.time(), secure_filename(fname))
).encode('utf-8')).hexdigest()
fs_file = os.path.join(fs_path, '%s.jpg' % filename)
rel_path = os.path.join(str(uid), str(aid), '%s.jpg' % filename)
return (fs_file, rel_path, aid)
return False
def change_item_visibility(user_name, album_title, id_item, item_visibility):
logger.debug('{Modules|Edit} set_item_visibility(%s, %s, %s, %s)', user_name, album_title, id_item, item_visibility)
if id_item is None or item_visibility is None:
return False
id_user = get_user_id(user_name)
with db.pg_connection(config['app-database']) as (_, cur, _):
try:
cur.execute(
'''
UPDATE travel_log.item
SET is_visible = %(visible)s
FROM travel_log.album
WHERE item.fk_album = album.id_album
AND fk_user=%(user)s AND album_title=%(album)s
AND item.id_item=%(id_item)s
''',
{'user': id_user, 'album': album_title, 'id_item': id_item, 'visible': item_visibility}
)
except Exception as e:
logger.error(e)
return False
return True
def delete_one_album(id_user, album_title):
logger.debug('{Module|Album} delete_one_album(%s, %s)', id_user,
album_title)
success = False
with db.pg_connection(config['app-database']) as (_, cur, err):
if err:
logger.error(err)
try:
# We only flag album as deleted here, some worker queue
# will actually delete them (and cascade to items).
cur.execute(
'''
UPDATE travel_log.album
SET is_deleted = TRUE
WHERE fk_user=%(user)s AND album_title=%(album)s
''', {
'user': id_user,
'album': album_title
})
success = True
except Exception as e:
logger.error(e)
return {'success': success}
def get_share_type(user_name, album_title):
logger.debug('{Module|Share} get_share_type(%s, %s)', user_name,
album_title)
with db.pg_connection(config['app-database']) as (_, cur, err):
if err:
logger.error(err)
try:
share_type = db.query_one(
cur, '''
SELECT
st.share_type_name
FROM travel_log.share s
JOIN travel_log.share_type st ON st.id_share_type = s.fk_share_type
JOIN travel_log.user u ON u.id_user = s.fk_user
JOIN travel_log.album a ON a.id_album = s.fk_album
WHERE u.user_name = %(user)s AND a.album_title = %(album)s
AND NOT a.is_deleted
''', {
'user': user_name,
'album': album_title
})
except Exception as e:
logger.error(e)
if share_type and share_type.share_type_name:
return share_type.share_type_name
return 'Private'
def store_background_fs(file_storage, user_name, album_title):
fname = _make_file_path(user_name, album_title, file_storage.filename)
if not fname:
return False
fs_file, rel_path, aid = fname
try:
img = Image.open(BytesIO(file_storage.read()))
except Exception as e:
logger.error(e)
return False
write_image(file_storage, img, fs_file)
result = False
with db.pg_connection(config['app-database']) as (_, cur, err):
if err:
logger.error(err)
try:
cur.execute(
'''
UPDATE travel_log.album
SET background = %(path)s
WHERE id_album = %(aid)s
AND NOT is_deleted
''', {
'path': rel_path,
'aid': aid
})
result = True
except Exception as e:
logger.error(e)
return result
def delete_one_item(id_user, album_title, id_item):
logger.debug('{Modules|Edit} delete_one_item(%s, %s, %s)', id_user, album_title, id_item)
success = False
with db.pg_connection(config['app-database']) as (_, cur, err):
if err:
logger.error(err)
return False
# We only flag items as deleted here, some worker queue
# will actually delete them asynchronously.
try:
cur.execute(
'''
UPDATE travel_log.item
SET is_deleted = TRUE
FROM travel_log.album
WHERE item.fk_album = album.id_album
AND fk_user=%(user)s AND album_title=%(album)s
AND item.id_item=%(id_item)s
''',
{'user': id_user, 'album': album_title, 'id_item': id_item}
)
success = True
except Exception as e:
logger.error(e)
return {'success': success}
def load_album(current_user, user_name, album_title):
album = None
with db.pg_connection(config['app-database']) as (_, cur, err):
if err:
logger.error(err)
try:
album = db.query_one(
cur, '''
SELECT *
FROM travel_log.album a
JOIN travel_log.user u ON u.id_user = a.fk_user
WHERE u.user_name = %(user)s AND a.album_title = %(album)s
AND NOT a.is_deleted
''', {
'user': user_name,
'album': album_title
})
except Exception as e:
logger.error(e)
if album:
bg = album.background
if not bg.startswith('#'):
bg = url_for('image.images', filename=bg)
return jsonify({
'description': album.album_desc,
'background': bg,
'autoplay_delay': album.autoplay_delay
})
return jsonify({})
def get_albums(user_name, current_user):
logger.debug('{Module|User} get_albums(%s, %s)', user_name, current_user)
albums = []
with db.pg_connection(config['app-database']) as (_, cur, err):
if err:
logger.error(err)
try:
albums = db.query_all(
cur, '''
SELECT
a.id_album, a.album_title, a.album_desc, st.share_type_name, s.secret
FROM travel_log.album a
JOIN travel_log.user u ON u.id_user = a.fk_user
JOIN travel_log.share s ON s.fk_album = a.id_album AND s.fk_user = u.id_user
JOIN travel_log.share_type st ON st.id_share_type = s.fk_share_type
WHERE u.user_name=%(user)s AND NOT a.is_deleted
ORDER BY a.id_album
''', {'user': user_name})
except Exception as e:
logger.error(e)
return [
a for a in albums
if is_shared(current_user, user_name, a.album_title, None)
]
def get_share_types():
logger.debug('{Module|Share} get_share_types()')
share_types = []
with db.pg_connection(config['app-database']) as (_, cur, err):
if err:
logger.error(err)
try:
share_types = db.query_all(
cur, 'SELECT share_type_name FROM travel_log.share_type', {})
except Exception as e:
logger.error(e)
return [s.share_type_name for s in share_types]
def share_album(user_name, album_title, share):
logger.debug('{Module|Share} share_album(%s, %s, %s)', user_name,
album_title, share)
if share == 'Hidden':
m = '%s%s' % (time.time(), ''.join([
string.ascii_letters[random.randint(0,
len(string.ascii_letters) - 1)]
for _ in range(8)
]))
secret = hashlib.sha256(m).hexdigest()
else:
secret = None
success = False
with db.pg_connection(config['app-database']) as (_, cur, err):
if err:
logger.error(err)
try:
cur.execute(
'''
UPDATE travel_log.share
SET fk_share_type = share_type.id_share_type,
secret = %(secret)s
FROM travel_log.album,
travel_log.user,
travel_log.share_type
WHERE album.id_album = share.fk_album AND album.album_title = %(album)s
AND NOT album.is_deleted
AND "user".id_user = share.fk_user AND "user".user_name = %(user)s
AND share_type.share_type_name = %(share)s
''', {
'user': user_name,
'album': album_title,
'share': share,
'secret': secret
})
success = True
except Exception as e:
logger.error(e)
return {
'success': success,
'secret': secret,
'msg': 'Changed share type to %s' % share
}
def store_item_fs(file_storage, user_name, album_title):
fname = _make_file_path(user_name, album_title, file_storage.filename)
if not fname:
return False
fs_file, rel_path, aid = fname
# TODO: we need to make sure writing to the
# filesystem/database happens as a transaction, i.e. either
# both or none.
try:
img = Image.open(BytesIO(file_storage.read()))
except Exception as e:
logger.error(e)
return False
file_storage.seek(0)
meta_data = get_meta_data(img)
zoom = DEFAULT_ZOOM_LEVEL if (meta_data['lat']
and meta_data['lon']) else None
write_image(file_storage, img, fs_file)
result = False
with db.pg_connection(config['app-database']) as (_, cur, err):
if err:
logger.error(err)
# Store item in database
try:
new_item = db.query_one(
cur, '''
INSERT INTO travel_log.item (fk_album, image, ts, lat, lon, zoom)
VALUES (%(aid)s, %(image)s, %(ts)s, %(lat)s, %(lon)s, %(zoom)s)
RETURNING id_item
''', {
'aid': aid,
'image': rel_path,
'ts': meta_data['date_time'],
'lat': meta_data['lat'],
'lon': meta_data['lon'],
'zoom': zoom
})
result = new_item.id_item
except Exception as e:
logger.error(e)
return result
def create_new_album(id_user, album_title, album_desc):
logger.debug('{Modules|Album} create_new_album(%s, %s, %s)', id_user,
album_title, album_desc)
success = False
with db.pg_connection(config['app-database']) as (_, cur, err):
if err:
logger.error(err)
try:
album = db.query_one(
cur,
'SELECT id_album FROM travel_log.album WHERE album_title = %(album)s and fk_user = %(user)s AND NOT is_deleted;',
{
'album': album_title,
'user': id_user
})
if not album.id_album:
cur.execute(
'INSERT INTO travel_log.album (album_title, album_desc, fk_user) VALUES (%(title)s, %(desc)s, %(user)s);',
{
'title': album_title,
'desc': album_desc,
'user': id_user
})
cur.execute(
'''
INSERT INTO travel_log.share (fk_album, fk_share_type, fk_user)
SELECT
id_album,
id_share_type,
%(user)s
FROM travel_log.album a
CROSS JOIN travel_log.share_type st
WHERE a.fk_user = %(user)s
AND a.album_title = %(title)s
AND st.share_type_name = \'Private\'
''', {
'title': album_title,
'user': id_user
})
success = True
except Exception as e:
logger.error(e)
return {'success': success}
def store_items(user_name, album_title, items):
logger.debug('{Modules|Edit} store_items(%s, %s)', user_name, album_title)
with db.pg_connection(config['app-database']) as (_, cur, err):
if err:
logger.error(err)
return False
for key, item in items.items():
ts = date_parser(item['ts']).strftime('%Y-%m-%d %H:%M:%S')
try:
cur.execute(
'''
UPDATE travel_log.item
SET description = %(desc)s,
lat = %(lat)s,
lon = %(lon)s,
zoom = %(zoom)s,
ts = %(ts)s
FROM travel_log.album,
travel_log.user
WHERE item.fk_album = album.id_album
AND album.fk_user = "******".id_user
AND "user".user_name = %(user)s
AND album.album_title = %(album)s
AND id_item=%(key)s
AND NOT album.is_deleted
AND NOT item.is_deleted
''',
{
'user': user_name,
'album': album_title,
'key': key,
'desc': item['description'],
'lat': item['lat'] if item['lat'] != 'None' else None,
'lon': item['lon'] if item['lon'] != 'None' else None,
'zoom': item['zoom'] or 12,
'ts': ts or None,
}
)
except Exception as e:
logger.error(e)
return False
return True
def get_user_id(user_name):
result = None
with db.pg_connection(config['app-database']) as (_, cur, err):
if err:
logger.error(err)
try:
user = db.query_one(
cur, '''
SELECT id_user
FROM travel_log.user
WHERE user_name = %(user)s
''', {'user': user_name})
result = user.id_user
except Exception as e:
logger.error(e)
return result
def load_items(user_name, album_title, only_visible=False):
visible = (True, False)
if only_visible:
visible = (True, )
result = {}
with db.pg_connection(config['app-database']) as (_, cur, err):
if err:
logger.error(err)
try:
items = db.query_all(
cur, '''
SELECT * FROM travel_log.item i
JOIN travel_log.album A ON a.id_album = i.fk_album
JOIN travel_log.user u ON u.id_user = a.fk_user
WHERE u.user_name = %(user)s AND a.album_title = %(album)s
AND NOT i.is_deleted
AND NOT a.is_deleted
AND i.is_visible IN %(visible)s
ORDER BY i.ts
''', {
'user': user_name,
'album': album_title,
'visible': visible
})
result = {
'items': [{
'id': item.id_item,
'is_visible': item.is_visible,
'image': url_for('image.images', filename=item.image),
'description': item.description,
'lat': str(item.lat),
'lon': str(item.lon),
'zoom': item.zoom,
'ts': item.ts
} for item in items]
}
except Exception as e:
logger.error(e)
return jsonify(result)
def is_shared(current_user, user_name, album_title, secret_part):
if is_current_user(user_name, current_user):
return True
share_type = None
with db.pg_connection(config['app-database']) as (_, cur, err):
if err:
logger.error(err)
try:
share_type = db.query_one(
cur,
'''
SELECT
st.share_type_name,
s.secret
FROM travel_log.share s
JOIN travel_log.share_type st ON st.id_share_type = s.fk_share_type
JOIN travel_log.album a ON a.id_album = s.fk_album
JOIN travel_log.user u ON u.id_user = s.fk_user
WHERE a.album_title = %(album)s
AND u.user_name = %(user)s
AND NOT a.is_deleted
''',
{'user': user_name, 'album': album_title}
)
except Exception as e:
logger.error(e)
if not share_type:
return False
if share_type.share_type_name == 'Public':
return True
if share_type.share_type_name == 'Hidden':
if share_type.secret and share_type.secret == secret_part:
return True
return False
def vacuum():
logger = get_logger()
logger.info('[Start][Vacuum]')
# Step 1:
# Search for database entries that have the `is_deleted` flag. If
# an album is marked as deleted, this cascaded to all the items,
# even if the flag isn't actually set.
print('Searching for image files marked as deleted...')
found_stale_items = False
with db.pg_connection(config['app-database']) as (_, cur, _):
stale_items = db.query_all(
cur, '''
SELECT
CASE WHEN a.is_deleted THEN a.id_album END AS id_album,
i.id_item,
i.image
FROM travel_log.album a
LEFT JOIN travel_log.item i ON a.id_album = i.fk_album
WHERE a.is_deleted OR i.is_deleted
''')
album_ids = tuple(
set([int(item.id_album) for item in stale_items if item.id_album]))
item_ids = tuple(
set([int(item.id_item) for item in stale_items if item.id_item]))
for item in stale_items:
found_stale_items = True
if item.id_item:
fs_path = os.path.join(config['storage-engine']['path'],
item.image)
logger.info('[Delete][File][Item]: %s', fs_path)
print(' - Deleting file %s' % fs_path)
if os.path.isfile(fs_path):
os.remove(fs_path)
if not found_stale_items:
print(' - None found!')
# Step 2:
# Once files are deleted, we can delete the corresponding database
# entries.
print('Searching for database entries to delete...')
found_db_entries = False
with db.pg_connection(config['app-database']) as (_, cur, _):
if item_ids:
found_db_entries = True
logger.info('[Delete][Database][Item]: %s', item_ids)
print(' - Deleting items')
cur.execute(
'DELETE FROM travel_log.item WHERE id_item IN %(items)s',
{'items': item_ids})
if album_ids:
logger.info('[Delete][Database][Share]: %s', album_ids)
print(' - Deleting share entries')
cur.execute(
'DELETE FROM travel_log.share WHERE fk_album IN %(albums)s',
{'albums': album_ids})
found_db_entries = True
logger.info('[Delete][Database][Album]: %s', album_ids)
print(' - Deleting albums')
cur.execute(
'DELETE FROM travel_log.album WHERE id_album IN %(albums)s',
{'albums': album_ids})
if not found_db_entries:
print(' - None found!')
# Step 3:
# Find image files that are no longer referenced from the database
# and delete them. This ideally shouldn't happen, but if anything
# goes wrong it does, so we search for them.
print('Searching stale image files...')
found_stale_files = False
with db.pg_connection(config['app-database']) as (_, cur, _):
ref_items = db.query_all(
cur, '''
SELECT image FROM travel_log.item WHERE NOT is_deleted
UNION
SELECT background FROM travel_log.album WHERE NOT is_deleted
''')
ref = set([
os.path.join(config['storage-engine']['path'], i.image)
for i in ref_items
])
on_disk = set(
glob.glob(
os.path.join(config['storage-engine']['path'], '*', '*', '*.jpg')))
# TODO: this is not very efficient, but works for now.
diff = on_disk.difference(ref)
for stale in diff:
found_stale_files = True
logger.info('[Delete][Filesystem][Image]: %s', stale)
print(' - Deleting image from disk: %s' % stale)
os.remove(stale)
if not found_stale_files:
print(' - None found!')
# Step 4:
# Remove empty directories
print('Searching stale folders...')
with db.pg_connection(config['app-database']) as (_, cur, _):
albums = db.query_all(
cur,
'SELECT fk_user, id_album FROM travel_log.album WHERE NOT is_deleted'
)
albums = set([(a.fk_user, a.id_album) for a in albums])
folders = [
d for d in glob.glob(
os.path.join(config['storage-engine']['path'], '*', '*'))
]
found_stale_folders = False
for f in folders:
rest, aid = os.path.split(f)
rest, uid = os.path.split(rest)
if (int(uid), int(aid)) not in albums:
found_stale_folders = True
logger.info('[Delete][Filesystem][Folder]: %s', f)
print(' - Delete folder from disk: %s' % f)
os.rmdir(f)
if not found_stale_folders:
print(' - None found')
logger.info('[Stop][Vacuum]')
