def edit_user_active(_params): """edit user preferences Keyword arguments: _params includes: active -- activity state of user user_id -- id of user """ if _params['active'] not in ['0', '1']: raise FailedRequest('Invalid active', 400) get_user(_params['user_id']) _params['active'] = int(_params['active']) database.query( db.music_db, ''' UPDATE users SET active = :active WHERE user_id = :user_id LIMIT 1 ''', _params ) elastic_search.update_from_index('users', 'user', _params['user_id'], ['active'], _params)
def edit_user_picture(_params): """edit user picture Keyword arguments: _params includes: picture -- picture of user """ data = get_user(_params['user_id']) file_ext = _params['image_filename'][_params['image_filename'].rfind('.'):] new_filename = _params['user_id'] + file_ext path = '/'.join([ utils.hash_string(data['email']), utils.generate_filename(new_filename) ]) _params['picture'] = '/'.join( [app.config['CLOUDFRONT_URL'], 'users', path]) uploader = Uploader(config) uploader.upload_image('users', path, _params['image'].read()) database.query( db.music_db, ''' UPDATE users SET picture = :picture WHERE user_id = :user_id LIMIT 1 ''', _params) elastic_search.update_from_index('users', 'user', _params['user_id'], ['picture'], _params)
def edit_user_active(_params): """edit user preferences Keyword arguments: _params includes: active -- activity state of user user_id -- id of user """ if _params['active'] not in ['0', '1']: raise FailedRequest('Invalid active', 400) get_user(_params['user_id']) _params['active'] = int(_params['active']) database.query( db.music_db, ''' UPDATE users SET active = :active WHERE user_id = :user_id LIMIT 1 ''', _params) elastic_search.update_from_index('users', 'user', _params['user_id'], ['active'], _params)
def add_user(_params): """add user information to database servers Keyword arguments: _params includes: email -- email of user role -- role of user user_id -- id of user """ _params.update({ 'active': 1, 'rank': 0, 'picture': None, 'genre': [], 'mood': [], 'instrument': [], 'skype': None, 'date_created': datetime.now(), 'date_updated': None }) database.query( db.music_db, ''' INSERT INTO users VALUES ( :user_id, :email, :name, :active, :rank, :role, :picture, :skype, :application_status, :date_created, :date_updated ) ''', _params ) database.query( db.music_db, ''' INSERT INTO user_preferences ( user_id ) VALUES ( :user_id ) ''', _params ) elastic_search.add_to_index('users', 'user', _params, _params['user_id'], config['ES']['user_cols'])
def add_user(_params): """add user information to database servers Keyword arguments: _params includes: email -- email of user role -- role of user user_id -- id of user """ _params.update({ 'active': 1, 'rank': 0, 'picture': None, 'genre': [], 'mood': [], 'instrument': [], 'skype': None, 'date_created': datetime.now(), 'date_updated': None }) database.query( db.music_db, ''' INSERT INTO users VALUES ( :user_id, :email, :name, :active, :rank, :role, :picture, :skype, :application_status, :date_created, :date_updated ) ''', _params) database.query( db.music_db, ''' INSERT INTO user_preferences ( user_id ) VALUES ( :user_id ) ''', _params) elastic_search.add_to_index('users', 'user', _params, _params['user_id'], config['ES']['user_cols'])
def apply(_params): _params.update({ 'artist_id': utils.generate_UUID(), 'application_id': 0, 'application_status': 'accepted', 'agree_on_contract': 'yes', 'picture': None, 'rating': None, 'music_provider_id': config['DEFAULT_MUSIC_PROVIDER'], 'date_applied': datetime.now(), 'date_approved': None, 'date_created': datetime.now(), 'date_updated': None }) artist.process_artist_params(_params, True) artist.insert_artist(_params) database.query( db.music_db, ''' INSERT INTO applications VALUES ( :application_id, :user_id, :contract_id, :agree_on_contract, :source, :date_applied, :date_approved, :date_created, :date_updated ) ''', _params ) database.query( db.music_db, ''' UPDATE users SET skype = :skype, application_status = :application_status WHERE user_id = :user_id LIMIT 1 ''', _params ) elastic_search.update_from_index('users', 'user', _params['user_id'], ['skype', 'application_status'], _params)
def update_user(_params): get_user(_params['user_id']) database.query( db.music_db, ''' UPDATE users SET name = :name, application_status = :application_status WHERE user_id = :user_id LIMIT 1 ''', _params) elastic_search.update_from_index('users', 'user', _params['user_id'], ['name', 'application_status'], _params)
def decline_invitation(_params): data = get_user(_params['user_id']) _params['email'] = data['email'] mp_op = '=' if _params['music_provider_id'] else 'IS' a_op = '=' if _params['artist_id'] else 'IS' database.query( db.music_db, ''' DELETE FROM user_invites WHERE email = :email AND role = :role AND music_provider_id %s :music_provider_id AND artist_id %s :artist_id ''' % (mp_op, a_op), _params)
def edit_user_preference(_params): """edit user preferences Keyword arguments: _params includes: genre -- genre preferences of user (default None) instrument -- instrument preferences of user (default None) mood -- mood preferences of user (default None) """ get_user(_params['user_id']) for category in ['genre', 'instrument', 'mood']: if not _params[category]: continue results = [] for preference in utils.split(_params[category], ','): results.append(preference) data = elastic_search.multi_get(category + 's', key='name', values=results) results = [row['name'] for row in data] _params[category] = ','.join(results) or None database.query( db.music_db, ''' UPDATE user_preferences SET genre = :genre, mood = :mood, instrument = :instrument WHERE user_id = :user_id LIMIT 1 ''', _params ) _params.update({ 'genre': utils.split(_params['genre'], ','), 'instrument': utils.split(_params['instrument'], ','), 'mood': utils.split(_params['mood'], ',') }) elastic_search.update_from_index('users', 'user', _params['user_id'], ['genre', 'mood', 'instrument'], _params)
def update_user(_params): get_user(_params['user_id']) database.query( db.music_db, ''' UPDATE users SET name = :name, application_status = :application_status WHERE user_id = :user_id LIMIT 1 ''', _params ) elastic_search.update_from_index('users', 'user', _params['user_id'], ['name', 'application_status'], _params)
def apply(_params): _params.update({ 'artist_id': utils.generate_UUID(), 'application_id': 0, 'application_status': 'accepted', 'agree_on_contract': 'yes', 'picture': None, 'rating': None, 'music_provider_id': config['DEFAULT_MUSIC_PROVIDER'], 'date_applied': datetime.now(), 'date_approved': None, 'date_created': datetime.now(), 'date_updated': None }) artist.process_artist_params(_params, True) artist.insert_artist(_params) database.query( db.music_db, ''' INSERT INTO applications VALUES ( :application_id, :user_id, :contract_id, :agree_on_contract, :source, :date_applied, :date_approved, :date_created, :date_updated ) ''', _params) database.query( db.music_db, ''' UPDATE users SET skype = :skype, application_status = :application_status WHERE user_id = :user_id LIMIT 1 ''', _params) elastic_search.update_from_index('users', 'user', _params['user_id'], ['skype', 'application_status'], _params)
def edit_user_preference(_params): """edit user preferences Keyword arguments: _params includes: genre -- genre preferences of user (default None) instrument -- instrument preferences of user (default None) mood -- mood preferences of user (default None) """ get_user(_params['user_id']) for category in ['genre', 'instrument', 'mood']: if not _params[category]: continue results = [] for preference in utils.split(_params[category], ','): results.append(preference) data = elastic_search.multi_get(category + 's', key='name', values=results) results = [row['name'] for row in data] _params[category] = ','.join(results) or None database.query( db.music_db, ''' UPDATE user_preferences SET genre = :genre, mood = :mood, instrument = :instrument WHERE user_id = :user_id LIMIT 1 ''', _params) _params.update({ 'genre': utils.split(_params['genre'], ','), 'instrument': utils.split(_params['instrument'], ','), 'mood': utils.split(_params['mood'], ',') }) elastic_search.update_from_index('users', 'user', _params['user_id'], ['genre', 'mood', 'instrument'], _params)
def decline_invitation(_params): data = get_user(_params['user_id']) _params['email'] = data['email'] mp_op = '=' if _params['music_provider_id'] else 'IS' a_op = '=' if _params['artist_id'] else 'IS' database.query( db.music_db, ''' DELETE FROM user_invites WHERE email = :email AND role = :role AND music_provider_id %s :music_provider_id AND artist_id %s :artist_id ''' % (mp_op, a_op), _params )
def delete_user(user_id): """delete user from elastic search index, set active to 0 in mysql Keyword arguments: _params includes: user_id -- id of user """ get_user(user_id) database.query( db.music_db, ''' UPDATE users SET active = 0 WHERE user_id = :user_id LIMIT 1 ''', {'user_id': user_id}) elastic_search.delete_from_index('users', 'user', user_id)
def load_measurement_counts(places): if places.count > 0: params = [place.Id for place in places] meta = '' for id in params: meta = meta + ',?' SQL_MEASUREMENT_COUNT = 'SELECT Place, COUNT(Value) FROM Measurement WHERE Place IN (%s) GROUP BY Place' % meta[1:] result = database.query(SQL_MEASUREMENT_COUNT, params) for mc in result: for place in places: if place.Id == mc[0]: place.MeasurementCount = mc[1] return True
def edit_user_picture(_params): """edit user picture Keyword arguments: _params includes: picture -- picture of user """ data = get_user(_params['user_id']) file_ext = _params['image_filename'][_params['image_filename'].rfind('.'):] new_filename = _params['user_id'] + file_ext path = '/'.join([ utils.hash_string(data['email']), utils.generate_filename(new_filename) ]) _params['picture'] = '/'.join([ app.config['CLOUDFRONT_URL'], 'users', path ]) uploader = Uploader(config) uploader.upload_image('users', path, _params['image'].read()) database.query( db.music_db, ''' UPDATE users SET picture = :picture WHERE user_id = :user_id LIMIT 1 ''', _params ) elastic_search.update_from_index('users', 'user', _params['user_id'], ['picture'], _params)
def delete_user(user_id): """delete user from elastic search index, set active to 0 in mysql Keyword arguments: _params includes: user_id -- id of user """ get_user(user_id) database.query( db.music_db, ''' UPDATE users SET active = 0 WHERE user_id = :user_id LIMIT 1 ''', {'user_id': user_id} ) elastic_search.delete_from_index('users', 'user', user_id)
def get_all(place_id = None, from_date = None, to_date = None, type_id = None): params = () SQL = 'SELECT Time, Place as PlaceId, Type as TypeId, Value FROM Measurement WHERE 1=1' if place_id: SQL = SQL + ' AND Place = ?' params = params + (place_id,) if type_id: SQL = SQL + ' AND Type = ?' params = params + (type_id,) if from_date > MIN_DATE: SQL = SQL + ' AND Time >= ?' params = params + (from_date,) if to_date < MAX_DATE: SQL = SQL + ' AND Time < ?' params = params + (to_date,) print SQL return [Measurement(row) for row in database.query(SQL, params)]
def get_all(): if not Place.cache: SQL_GET_ALL = "SELECT Id, Name, CoordLon, CoordLat, Country FROM Place" Place.cache = [Place(row) for row in database.query(SQL_GET_ALL)] return Place.cache
def add_user(_params): data = database.query( db.test_app_db, 'INSERT INTO users(`user_id`, `email`) VALUES(:user_id, :email)', _params) return data
def accept_invitation(_params): data = get_user(_params['user_id']) _params['email'] = data['email'] mp_op = '=' if _params['music_provider_id'] else 'IS' a_op = '=' if _params['artist_id'] else 'IS' data = database.get( db.music_db, ''' SELECT * FROM user_invites WHERE email = :email AND role = :role AND music_provider_id %s :music_provider_id AND artist_id %s :artist_id ''' % (mp_op, a_op), _params ) if not data: raise FailedRequest('Invalid parameters', 400) if _params['role'] == 'artist': database.query( db.music_db, ''' UPDATE artists SET user_id = :user_id WHERE artist_id = :artist_id LIMIT 1 ''', _params ) elastic_search.update_from_index('artists', 'artist', _params['artist_id'], ['user_id', 'email'], _params) database.query( db.music_db, ''' DELETE FROM user_invites WHERE artist_id = :artist_id ''', _params ) else: database.query( db.music_db, ''' INSERT IGNORE INTO music_provider_owners_managers VALUES ( :user_id, :music_provider_id, :role ) ''', _params ) data = music_provider.get_music_provider(_params['music_provider_id']) _params['users'] = data['users'] _params['users'][_params['user_id']] = _params['role'] elastic_search.update_from_index('music_providers', 'music_provider', _params['music_provider_id'], ['users'], _params) database.query( db.music_db, ''' DELETE FROM user_invites WHERE music_provider_id = :music_provider_id ''', _params ) if _params['role'] != 'artist': _params['role'] = 'music_provider' database.query( db.music_db, ''' UPDATE users SET role = :role WHERE user_id = :user_id LIMIT 1 ''', _params ) elastic_search.update_from_index('users', 'user', _params['user_id'], ['role'], _params) _params['scopes'] = config['SCOPES'][_params['role']] auth.remove_scopes(_params['user_id']) auth.add_scopes(_params) database.query( db.music_db, ''' DELETE FROM user_invites WHERE email = :email ''', _params )
def accept_invitation(_params): data = get_user(_params['user_id']) _params['email'] = data['email'] mp_op = '=' if _params['music_provider_id'] else 'IS' a_op = '=' if _params['artist_id'] else 'IS' data = database.get( db.music_db, ''' SELECT * FROM user_invites WHERE email = :email AND role = :role AND music_provider_id %s :music_provider_id AND artist_id %s :artist_id ''' % (mp_op, a_op), _params) if not data: raise FailedRequest('Invalid parameters', 400) if _params['role'] == 'artist': database.query( db.music_db, ''' UPDATE artists SET user_id = :user_id WHERE artist_id = :artist_id LIMIT 1 ''', _params) elastic_search.update_from_index('artists', 'artist', _params['artist_id'], ['user_id', 'email'], _params) database.query( db.music_db, ''' DELETE FROM user_invites WHERE artist_id = :artist_id ''', _params) else: database.query( db.music_db, ''' INSERT IGNORE INTO music_provider_owners_managers VALUES ( :user_id, :music_provider_id, :role ) ''', _params) data = music_provider.get_music_provider(_params['music_provider_id']) _params['users'] = data['users'] _params['users'][_params['user_id']] = _params['role'] elastic_search.update_from_index('music_providers', 'music_provider', _params['music_provider_id'], ['users'], _params) database.query( db.music_db, ''' DELETE FROM user_invites WHERE music_provider_id = :music_provider_id ''', _params) if _params['role'] != 'artist': _params['role'] = 'music_provider' database.query( db.music_db, ''' UPDATE users SET role = :role WHERE user_id = :user_id LIMIT 1 ''', _params) elastic_search.update_from_index('users', 'user', _params['user_id'], ['role'], _params) _params['scopes'] = config['SCOPES'][_params['role']] auth.remove_scopes(_params['user_id']) auth.add_scopes(_params) database.query( db.music_db, ''' DELETE FROM user_invites WHERE email = :email ''', _params)
def add_user(_params): database.query( db.app_db, 'INSERT INTO users(`user_id`, `email`) VALUES \ (:user_id, :email)', _params) return
def get_all(): if not MeasurementType.cache: SQL = 'SELECT Id, Name, Unit FROM MeasurementType' MeasurementType.cache = [MeasurementType(row) for row in database.query(SQL)] return MeasurementType.cache