def get(self, id=None): db = database.connect() db.row_factory = database.row_factory(['album', 'cover']) if id: album = db.execute( "SELECT Album.*, '#', Photo.* FROM albums as Album LEFT JOIN photos as Photo ON Album.cover = Photo.id WHERE Album.id=:id", { 'id': id }).fetchone() print(album) item = album['album'] if album['cover']['id']: item['cover'] = photos.preparePhoto(album['cover']) return success(item) albums = db.execute( "SELECT Album.*, '#', Photo.* FROM albums as Album LEFT JOIN photos as Photo ON Album.cover = Photo.id" ).fetchall() db.close() data = [] for album in albums: item = album['album'] if album['cover']['id']: item['cover'] = photos.preparePhoto(album['cover']) data.append(item) data.append({'title': 'unsorted', 'id': 'unsorted', 'cover': None}) return success(data)
def get(self, id=None): db = database.connect() db.row_factory = database.row_factory(['album', 'cover']) if id: album = db.execute("SELECT Album.*, '#', Photo.* FROM albums as Album LEFT JOIN photos as Photo ON Album.cover = Photo.id WHERE Album.id=:id", {'id':id}).fetchone() print(album) item = album['album'] if album['cover']['id']: item['cover'] = photos.preparePhoto(album['cover']) return success(item) albums = db.execute("SELECT Album.*, '#', Photo.* FROM albums as Album LEFT JOIN photos as Photo ON Album.cover = Photo.id").fetchall() db.close() data = [] for album in albums: item = album['album'] if album['cover']['id']: item['cover'] = photos.preparePhoto(album['cover']) data.append(item) data.append({ 'title':'unsorted', 'id':'unsorted', 'cover':None }) return success(data)
def photosAlbums(self, id): db = database.connect() cur = db.execute("""SELECT albums.name, albums.id FROM albums INNER JOIN albums_photos AS r ON r.album_id = albums.id INNER JOIN photos ON r.photo_id = photos.id WHERE photos.id=:id""", {'id':id}).fetchall() db.close() return success([dict(a) for a in cur])
def smart(self, type): db = database.connect() cur = db.execute( """SELECT * FROM photos WHERE id NOT IN (SELECT albums_photos.photo_id FROM albums_photos)""" ).fetchall() db.close() return success([photos.preparePhoto(dict(a)) for a in cur])
def delete_photo(self, album_id, photo_id): db = database.connect() cur = db.execute("DELETE FROM albums_photos WHERE album_id = :album_id AND photo_id = :photo_id", { 'album_id': album_id, 'photo_id': photo_id }) db.commit() db.close() return success()
def handle_raw_files(alreadyHandled, filename, storage, storage_options, file, **kwargs): title, ext = os.path.splitext(filename) ext = ext.lower() if ext.lower() != '.pef': return alreadyHandled exifData = photos.get_exif(file) if not 'DateTimeOriginal' in exifData: return alreadyHandled capturedTime = photos.date_time_to_timestamp(exifData['DateTimeOriginal']) db = database.connect() with db: row = db.execute( "SELECT id, title, attachments FROM photos WHERE captured_on = :capturedTime", { 'capturedTime': capturedTime }).fetchone() if row == None: db.close() return alreadyHandled row = dict(row) if not row['attachments']: attachments = {} else: attachments = json.loads(row['attachments']) if not attachments: attachments = {} filename = photos.uid(30) + ext attachments['raw_file'] = filename attachments = json.dumps(attachments) db.execute('UPDATE photos SET attachments = :attachments WHERE id=:id', { 'attachments': attachments, 'id': row['id'] }) if not storages.save( file, str(row['id']) + '_' + filename, type='attachment'): db.rollback() return alreadyHandled return { 'id': row['id'], 'attachments': { 'raw_file': storages.url(str(row['id']) + '_' + filename, type='attachment') } }
def delete(self, id): query = '' value = {'id': id} db = database.connect() db.execute("DELETE FROM albums_photos WHERE album_id = :id", value) db.execute("DELETE FROM albums WHERE id = :id", value) db.commit() db.close() return success()
def photosAlbums(self, id): db = database.connect() cur = db.execute( """SELECT albums.name, albums.id FROM albums INNER JOIN albums_photos AS r ON r.album_id = albums.id INNER JOIN photos ON r.photo_id = photos.id WHERE photos.id=:id""", { 'id': id }).fetchall() db.close() return success([dict(a) for a in cur])
def delete_photo(self, album_id, photo_id): db = database.connect() cur = db.execute( "DELETE FROM albums_photos WHERE album_id = :album_id AND photo_id = :photo_id", { 'album_id': album_id, 'photo_id': photo_id }) db.commit() db.close() return success()
def add(self, title): created_on = int(time.time()) value = { 'title' : title, 'created_on' : created_on, 'modified_on': created_on } db = database.connect() cur = db.execute("INSERT INTO albums (title, created_on, modified_on) VALUES (:title, :created_on, :modified_on)", value) db.commit() db.close() return success({'id': cur.lastrowid})
def add(self, title): created_on = int(time.time()) value = { 'title': title, 'created_on': created_on, 'modified_on': created_on } db = database.connect() cur = db.execute( "INSERT INTO albums (title, created_on, modified_on) VALUES (:title, :created_on, :modified_on)", value) db.commit() db.close() return success({'id': cur.lastrowid})
def add_photo(self, album_id, photo_id): db = database.connect() cur = db.execute("SELECT * FROM albums_photos WHERE album_id=:album_id AND photo_id = :photo_id", { 'album_id': album_id, 'photo_id': photo_id }).fetchone() if not cur: cur = db.execute("INSERT INTO albums_photos (album_id, photo_id) VALUES (:album_id, :photo_id)", { 'album_id': album_id, 'photo_id': photo_id }) db.commit() db.close() return success()
def add_photo(self, album_id, photo_id): db = database.connect() cur = db.execute( "SELECT * FROM albums_photos WHERE album_id=:album_id AND photo_id = :photo_id", { 'album_id': album_id, 'photo_id': photo_id }).fetchone() if not cur: cur = db.execute( "INSERT INTO albums_photos (album_id, photo_id) VALUES (:album_id, :photo_id)", { 'album_id': album_id, 'photo_id': photo_id }) db.commit() db.close() return success()
def handle_raw_files(alreadyHandled, filename, storage, storage_options, file, **kwargs): title, ext = os.path.splitext(filename) ext = ext.lower() if ext.lower() != '.pef': return alreadyHandled exifData = photos.get_exif(file) if not 'DateTimeOriginal' in exifData: return alreadyHandled capturedTime = photos.date_time_to_timestamp(exifData['DateTimeOriginal']) db = database.connect() with db: row = db.execute("SELECT id, title, attachments FROM photos WHERE captured_on = :capturedTime", {'capturedTime': capturedTime}).fetchone() if row == None: db.close() return alreadyHandled row = dict(row) if not row['attachments']: attachments = {} else: attachments = json.loads(row['attachments']) if not attachments: attachments = {} filename = photos.uid(30)+ext attachments['raw_file'] = filename attachments = json.dumps(attachments) db.execute('UPDATE photos SET attachments = :attachments WHERE id=:id', {'attachments': attachments, 'id': row['id']}) if not storages.save(file, str(row['id'])+'_'+filename, type='attachment'): db.rollback() return alreadyHandled return {'id':row['id'],'attachments':{'raw_file': storages.url(str(row['id'])+'_'+filename, type='attachment')}}
def update(self, id): query = '' value = {'id': id} acceptedFields = ['title'] for i in acceptedFields: if i in request.params: value[i] = request.params[i] query += "SET "+i+" = :"+i if len(query) == 0: return error('no data sent (accepted fields: '+(','.join(acceptedFields))+')') db = database.connect() query = "UPDATE albums "+query+" WHERE id = :id" cur = db.execute(query, value) db.commit() db.close() return success()
def update(self, id): query = '' value = {'id': id} acceptedFields = ['title'] for i in acceptedFields: if i in request.params: value[i] = request.params[i] query += "SET " + i + " = :" + i if len(query) == 0: return error('no data sent (accepted fields: ' + (','.join(acceptedFields)) + ')') db = database.connect() query = "UPDATE albums " + query + " WHERE id = :id" cur = db.execute(query, value) db.commit() db.close() return success()
def smart(self, type): db = database.connect() cur = db.execute("""SELECT * FROM photos WHERE id NOT IN (SELECT albums_photos.photo_id FROM albums_photos)""").fetchall() db.close() return success([photos.preparePhoto(dict(a)) for a in cur])