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]')