def get_associated_user_wallet(
args: AssociatedUserWalletArgs) -> AssociatedUserWallet:
"""
Returns a list of associated wallets
Args:
args: dict The parsed args from the request
args.user_id: number The blockchain user id
Returns:
Array of strings representing the user's associated wallets
"""
db = db_session.get_db_read_replica()
with db.scoped_session() as session:
user_wallet: List[Tuple[str, str]] = (session.query(
AssociatedWallet.wallet, AssociatedWallet.chain).filter(
AssociatedWallet.is_current == True).filter(
AssociatedWallet.is_delete == False).filter(
AssociatedWallet.user_id == args.get("user_id")).all())
eth_wallets: List[str] = []
sol_wallets: List[str] = []
for wallet, chain in user_wallet:
if chain == "eth":
eth_wallets.append(wallet)
elif chain == "sol":
sol_wallets.append(wallet)
return {"eth": eth_wallets, "sol": sol_wallets}
def get_previously_private_playlists(args):
db = get_db_read_replica()
with db.scoped_session() as session:
if "date" not in args:
raise exceptions.ArgumentError(
"'date' required to query for retrieving previously private playlists"
)
date = args.get("date")
playlist_after_date = (session.query(
Playlist.playlist_id,
Playlist.updated_at).distinct(Playlist.playlist_id).filter(
Playlist.is_private == False,
Playlist.updated_at >= date).subquery())
playlist_before_date = (session.query(
Playlist.playlist_id,
Playlist.updated_at).distinct(Playlist.playlist_id).filter(
Playlist.is_private == True,
Playlist.updated_at < date).subquery())
previously_private_results = session.query(
playlist_before_date.c['playlist_id']).join(
playlist_after_date,
playlist_after_date.c['playlist_id'] ==
playlist_before_date.c['playlist_id'],
).all()
playlist_ids = [result[0] for result in previously_private_results]
return {'ids': playlist_ids}
def get_app_names(args):
"""
Returns a list of app names
Args:
args: dict The parsed args from the request
args.offset: number The offset to start querying from
args.limit: number The max number of queries to return
Returns:
Array of dictionaries with the name field
"""
db = db_session.get_db_read_replica()
with db.scoped_session() as session:
app_names = (
session.query(AppNameMetrics.application_name)
.group_by(AppNameMetrics.application_name)
.order_by(asc(AppNameMetrics.application_name))
.limit(args.get('limit'))
.offset(args.get('offset'))
.all()
)
names = [{'name': app_name[0]} for app_name in app_names]
return names
def inner_wrap(*args, **kwargs):
message = request.headers.get(MESSAGE_HEADER)
signature = request.headers.get(SIGNATURE_HEADER)
authed_user_id = None
if message and signature:
web3 = web3_provider.get_web3()
encoded_to_recover = encode_defunct(text=message)
wallet = web3.eth.account.recover_message(
encoded_to_recover, signature=signature
)
db = db_session.get_db_read_replica()
with db.scoped_session() as session:
user = (
session.query(User.user_id)
.filter(
# Convert checksum wallet to lowercase
User.wallet == wallet.lower(),
User.is_current == True,
)
# In the case that multiple wallets match (not enforced on the data layer),
# pick the user id that is lowest (created first).
.order_by(User.user_id.asc())
.first()
)
if user:
authed_user_id = user.user_id
logger.info(
f"auth_middleware.py | authed_user_id: {authed_user_id}"
)
return func(*args, **kwargs, authed_user_id=authed_user_id)
def trending(time):
(limit, offset) = get_pagination_vars()
# Increment total trending count
REDIS.incr(trending_cache_total_key, 1)
genre = request.args.get("genre", default=None, type=str)
if genre is None:
redis_key = f"trending-{time}"
redis_cache_value = REDIS.get(redis_key)
if redis_cache_value is not None:
json_cache = json.loads(redis_cache_value.decode('utf-8'))
if json_cache is not None:
num_cached_entries = len(json_cache['listen_counts'])
logger.info(
f'Cache for {redis_key}, {num_cached_entries} entries, request limit {limit}'
)
if offset + limit <= num_cached_entries:
json_cache['listen_counts'] = json_cache['listen_counts'][
offset:offset + limit]
logger.info(f'Returning cache for {redis_key}')
# Increment cache hit count
REDIS.incr(trending_cache_hits_key, 1)
return api_helpers.success_response(json_cache)
# Increment cache miss count
REDIS.incr(trending_cache_miss_key, 1)
# Recalculate trending values if necessary
final_resp = generate_trending(get_db_read_replica(), time, genre, limit,
offset)
return api_helpers.success_response(final_resp)
def get_previously_unlisted_tracks(args):
db = get_db_read_replica()
with db.scoped_session() as session:
if "date" not in args:
raise exceptions.ArgumentError(
"'date' required to query for retrieving previously unlisted tracks"
)
date = args.get("date")
tracks_after_date = (session.query(
Track.track_id, Track.updated_at).distinct(Track.track_id).filter(
Track.is_unlisted == False,
Track.updated_at >= date).subquery())
tracks_before_date = (session.query(
Track.track_id, Track.updated_at).distinct(Track.track_id).filter(
Track.is_unlisted == True, Track.updated_at < date).subquery())
previously_unlisted_results = (session.query(
tracks_before_date.c["track_id"]).join(
tracks_after_date,
tracks_after_date.c["track_id"] ==
tracks_before_date.c["track_id"],
).all())
track_ids = [result[0] for result in previously_unlisted_results]
return {"ids": track_ids}
def get_follow_intersection_users(followee_user_id, follower_user_id):
users = []
db = get_db_read_replica()
with db.scoped_session() as session:
query = session.query(User).filter(
User.is_current == True,
User.user_id.in_(
session.query(Follow.follower_user_id).filter(
Follow.followee_user_id == followee_user_id,
Follow.is_current == True,
Follow.is_delete == False,
).intersect(
session.query(Follow.followee_user_id).filter(
Follow.follower_user_id == follower_user_id,
Follow.is_current == True,
Follow.is_delete == False,
))),
)
users = paginate_query(query).all()
users = helpers.query_result_to_list(users)
user_ids = [user[response_name_constants.user_id] for user in users]
current_user_id = get_current_user_id(required=False)
# bundle peripheral info into user results
users = populate_user_metadata(session, user_ids, users,
current_user_id)
# order by follower_count desc
users.sort(
key=lambda user: user[response_name_constants.follower_count],
reverse=True)
return users
def milestones_followers():
db = get_db_read_replica()
if "user_id" not in request.args:
return api_helpers.error_response({'msg': 'Please provider user ids'},
500)
try:
user_id_str_list = request.args.getlist("user_id")
user_ids = []
user_ids = [int(y) for y in user_id_str_list]
except ValueError as e:
logger.error("Invalid value found in user id list", esc_info=True)
return api_helpers.error_response({'msg': e}, 500)
with db.scoped_session() as session:
follower_counts = (session.query(
Follow.followee_user_id,
func.count(Follow.followee_user_id)).filter(
Follow.is_current == True, Follow.is_delete == False,
Follow.followee_user_id.in_(user_ids)).group_by(
Follow.followee_user_id).all())
follower_count_dict = \
{user_id: follower_count for (
user_id, follower_count) in follower_counts}
return api_helpers.success_response(follower_count_dict)
def search(isAutocomplete):
searchStr = request.args.get("query", type=str)
if not searchStr:
return api_helpers.error_response("Invalid value for parameter 'query'")
searchStr = searchStr.replace('&', 'and') # when creating query table, we substitute this too
kind = request.args.get("kind", type=str, default="all")
if kind not in SearchKind.__members__:
return api_helpers.error_response(
"Invalid value for parameter 'kind' must be in %s" % [k.name for k in SearchKind]
)
searchKind = SearchKind[kind]
(limit, offset) = get_pagination_vars()
results = {}
if searchStr:
db = get_db_read_replica()
with db.scoped_session() as session:
# Set similarity threshold to be used by % operator in queries.
session.execute(sqlalchemy.text(f"select set_limit({minSearchSimilarity});"))
if (searchKind in [SearchKind.all, SearchKind.tracks]):
results['tracks'] = track_search_query(session, searchStr, limit, offset, False, isAutocomplete)
results['saved_tracks'] = track_search_query(session, searchStr, limit, offset, True, isAutocomplete)
if (searchKind in [SearchKind.all, SearchKind.users]):
results['users'] = user_search_query(session, searchStr, limit, offset, False, isAutocomplete)
results['followed_users'] = user_search_query(session, searchStr, limit, offset, True, isAutocomplete)
if (searchKind in [SearchKind.all, SearchKind.playlists]):
results['playlists'] = playlist_search_query(
session,
searchStr,
limit,
offset,
False,
False,
isAutocomplete
)
results['saved_playlists'] = playlist_search_query(
session,
searchStr,
limit,
offset,
False,
True,
isAutocomplete
)
if (searchKind in [SearchKind.all, SearchKind.albums]):
results['albums'] = playlist_search_query(session, searchStr, limit, offset, True, False, isAutocomplete)
results['saved_albums'] = playlist_search_query(
session,
searchStr,
limit,
offset,
True,
True,
isAutocomplete
)
return api_helpers.success_response(results)
def _get_db_block_state(latest_blocknum, latest_blockhash):
db = get_db_read_replica()
with db.scoped_session() as session:
# Fetch latest block from DB
db_block_query = session.query(Block).filter(
Block.is_current == True).all()
assert len(
db_block_query) == 1, "Expected SINGLE row marked as current"
health_results = {
"web": {
"blocknumber": latest_blocknum,
"blockhash": latest_blockhash,
},
"db": helpers.model_to_dictionary(db_block_query[0]),
"git": os.getenv("GIT_SHA"),
}
block_difference = abs(health_results["web"]["blocknumber"] -
health_results["db"]["number"])
health_results["block_difference"] = block_difference
health_results[
"maximum_healthy_block_difference"] = default_healthy_block_diff
health_results.update(disc_prov_version)
return health_results
def get_trending_tracks(args, strategy):
"""Gets trending by getting the currently cached tracks and then populating them."""
db = get_db_read_replica()
with db.scoped_session() as session:
current_user_id, genre, time = args.get("current_user_id"), args.get(
"genre"), args.get("time", "week")
time_range = "week" if time not in ["week", "month", "year"] else time
key = make_trending_cache_key(time_range, genre, strategy.version)
# Will try to hit cached trending from task, falling back
# to generating it here if necessary and storing it with no TTL
(tracks, track_ids) = use_redis_cache(
key, None,
make_generate_unpopulated_trending(session, genre, time_range,
strategy))
# populate track metadata
tracks = populate_track_metadata(session, track_ids, tracks,
current_user_id)
tracks_map = {track['track_id']: track for track in tracks}
# Re-sort the populated tracks b/c it loses sort order in sql query
sorted_tracks = [tracks_map[track_id] for track_id in track_ids]
if args.get("with_users", False):
user_id_list = get_users_ids(sorted_tracks)
users = get_users_by_id(session, user_id_list, current_user_id)
for track in sorted_tracks:
user = users[track['owner_id']]
if user:
track['user'] = user
return sorted_tracks
def get_latest_spl_audio() -> Optional[Dict]:
db = get_db_read_replica()
with db.scoped_session() as session:
token_tx = session.query(SPLTokenTransaction).first()
if token_tx:
return helpers.model_to_dictionary(token_tx)
return None
def _get_underground_trending(args, strategy):
db = get_db_read_replica()
with db.scoped_session() as session:
current_user_id = args.get("current_user_id", None)
limit, offset = args.get("limit"), args.get("offset")
key = make_underground_trending_cache_key(strategy.version)
(tracks, track_ids) = use_redis_cache(
key, None, make_get_unpopulated_tracks(session, redis, strategy))
# Apply limit + offset early to reduce the amount of
# population work we have to do
if limit is not None and offset is not None:
track_ids = track_ids[offset:limit + offset]
tracks = populate_track_metadata(session, track_ids, tracks,
current_user_id)
tracks_map = {track['track_id']: track for track in tracks}
# Re-sort the populated tracks b/c it loses sort order in sql query
sorted_tracks = [tracks_map[track_id] for track_id in track_ids]
user_id_list = get_users_ids(sorted_tracks)
users = get_users_by_id(session, user_id_list, current_user_id)
for track in sorted_tracks:
user = users[track['owner_id']]
if user:
track['user'] = user
sorted_tracks = list(map(extend_track, sorted_tracks))
return sorted_tracks
def get_followers_for_user(args):
users = []
followee_user_id = args.get("followee_user_id")
current_user_id = args.get("current_user_id")
limit = args.get("limit")
offset = args.get("offset")
db = get_db_read_replica()
with db.scoped_session() as session:
rows = session.execute(
sql,
{
"followee_user_id": followee_user_id,
"limit": limit,
"offset": offset
},
)
user_ids = [r[0] for r in rows]
# get all users for above user_ids
users = get_unpopulated_users(session, user_ids)
# bundle peripheral info into user results
users = populate_user_metadata(session, user_ids, users,
current_user_id)
return users
def get_playlist_repost_intersection_users(repost_playlist_id,
follower_user_id):
users = []
db = get_db_read_replica()
with db.scoped_session() as session:
# ensure playlist_id exists
playlist_entry = (session.query(Playlist).filter(
Playlist.playlist_id == repost_playlist_id,
Playlist.is_current == True).first())
if playlist_entry is None:
raise exceptions.NotFoundError(
"Resource not found for provided playlist id")
query = session.query(User).filter(
User.is_current == True,
User.user_id.in_(
session.query(Repost.user_id).filter(
Repost.repost_item_id == repost_playlist_id,
Repost.repost_type != RepostType.track,
Repost.is_current == True,
Repost.is_delete == False,
).intersect(
session.query(Follow.followee_user_id).filter(
Follow.follower_user_id == follower_user_id,
Follow.is_current == True,
Follow.is_delete == False,
))),
)
users = paginate_query(query).all()
users = helpers.query_result_to_list(users)
return users
def get_app_name_metrics(app_name, args):
"""
Returns the usage metrics for a specified app_name
Args:
app_name: string The name of the app to query for metrics
args: dict The parsed args from the request
args.start_time: date The date to start the query from
args.limit: number The max number of metrics to return
Returns:
Array of dictionaries with the timestamp and count
"""
db = db_session.get_db_read_replica()
with db.scoped_session() as session:
metrics = (
session.query(
AppNameMetrics.timestamp,
AppNameMetrics.count
).filter(
AppNameMetrics.application_name == app_name,
AppNameMetrics.timestamp > args.get('start_time')
)
.order_by(desc(AppNameMetrics.timestamp))
.limit(args.get('limit'))
.all()
)
metrics = [{'timestamp': int(time.mktime(
m[0].timetuple())), 'count': m[1]} for m in metrics]
return metrics
def get_trending_tracks(args):
(limit, offset) = get_pagination_vars()
current_user_id = get_current_user_id(required=False)
db = get_db_read_replica()
time = args.get('time')
# Identity understands allTime as millennium.
# TODO: Change this in https://github.com/AudiusProject/audius-protocol/pull/768/files
query_time = time
if time == 'allTime':
query_time = 'millennium'
with db.scoped_session() as session:
trending_tracks = generate_trending(get_db_read_replica(), query_time,
args.get('genre', None), limit,
offset)
track_scores = [
z(time, track) for track in trending_tracks['listen_counts']
]
sorted_track_scores = sorted(track_scores,
key=lambda k: k['score'],
reverse=True)
track_ids = [track['track_id'] for track in sorted_track_scores]
tracks = session.query(Track).filter(
Track.is_current == True, Track.is_unlisted == False,
Track.stem_of == None, Track.track_id.in_(track_ids)).all()
tracks = helpers.query_result_to_list(tracks)
tracks = populate_track_metadata(session, track_ids, tracks,
current_user_id)
tracks_map = {track['track_id']: track for track in tracks}
# Re-sort the populated tracks b/c it loses sort order in sql query
sorted_tracks = [tracks_map[track_id] for track_id in track_ids]
if args.get("with_users", False):
user_id_list = get_users_ids(sorted_tracks)
users = get_users_by_id(session, user_id_list)
for track in sorted_tracks:
user = users[track['owner_id']]
if user:
track['user'] = user
return sorted_tracks
def get_reposters_for_track(args):
user_results = []
current_user_id = args.get('current_user_id')
repost_track_id = args.get('repost_track_id')
limit = args.get('limit')
offset = args.get('offset')
db = get_db_read_replica()
with db.scoped_session() as session:
# Ensure Track exists for provided repost_track_id.
track_entry = session.query(Track).filter(
Track.track_id == repost_track_id,
Track.is_current == True).first()
if track_entry is None:
raise exceptions.NotFoundError(
'Resource not found for provided track id')
# Subquery to get all (user_id, follower_count) entries from Follows table.
follower_count_subquery = (session.query(
Follow.followee_user_id,
func.count(Follow.followee_user_id).label(
response_name_constants.follower_count)).filter(
Follow.is_current == True,
Follow.is_delete == False).group_by(
Follow.followee_user_id).subquery())
# Get all Users that reposted track, ordered by follower_count desc & paginated.
query = (
session.query(
User,
# Replace null values from left outer join with 0 to ensure sort works correctly.
(func.coalesce(follower_count_subquery.c.follower_count, 0)
).label(response_name_constants.follower_count))
# Left outer join to associate users with their follower count.
.outerjoin(
follower_count_subquery,
follower_count_subquery.c.followee_user_id ==
User.user_id).filter(
User.is_current == True,
# Only select users that reposted given track.
User.user_id.in_(
session.query(Repost.user_id).filter(
Repost.repost_item_id == repost_track_id,
Repost.repost_type == RepostType.track,
Repost.is_current == True,
Repost.is_delete == False)
)).order_by(desc(response_name_constants.follower_count)))
user_results = add_query_pagination(query, limit, offset).all()
# Fix format to return only Users objects with follower_count field.
if user_results:
users, _ = zip(*user_results)
user_results = helpers.query_result_to_list(users)
# bundle peripheral info into user results
user_ids = [user['user_id'] for user in user_results]
user_results = populate_user_metadata(session, user_ids,
user_results,
current_user_id)
return user_results
def get_top_followee_windowed(type, window, args):
if type != "track":
raise exceptions.ArgumentError(
"Invalid type provided, must be one of 'track'")
valid_windows = ["week", "month", "year"]
if not window or window not in valid_windows:
raise exceptions.ArgumentError(
f"Invalid window provided, must be one of {valid_windows}")
limit = args.get("limit", 25)
current_user_id = args.get("user_id")
db = get_db_read_replica()
with db.scoped_session() as session:
followee_user_ids = session.query(Follow.followee_user_id).filter(
Follow.follower_user_id == current_user_id,
Follow.is_current == True,
Follow.is_delete == False,
)
followee_user_ids_subquery = followee_user_ids.subquery()
# Queries for tracks joined against followed users and counts
tracks_query = (
session.query(Track, ).join(
followee_user_ids_subquery,
Track.owner_id ==
followee_user_ids_subquery.c.followee_user_id,
).join(AggregateTrack, Track.track_id == AggregateTrack.track_id).
filter(
Track.is_current == True,
Track.is_delete == False,
Track.is_unlisted == False,
Track.stem_of == None,
# Query only tracks created `window` time ago (week, month, etc.)
Track.created_at >= text(f"NOW() - interval '1 {window}'"),
).order_by(
desc(AggregateTrack.repost_count + AggregateTrack.save_count),
desc(Track.track_id),
).limit(limit))
tracks_query_results = tracks_query.all()
tracks = helpers.query_result_to_list(tracks_query_results)
track_ids = list(map(lambda track: track["track_id"], tracks))
# Bundle peripheral info into track results
tracks = populate_track_metadata(session, track_ids, tracks,
current_user_id)
if args.get("with_users", False):
user_id_list = get_users_ids(tracks)
users = get_users_by_id(session, user_id_list)
for track in tracks:
user = users[track["owner_id"]]
if user:
track["user"] = user
return tracks
def get(self):
args = get_reactions_parser.parse_args()
tx_ids, type = args.get("tx_signatures"), args.get("type")
db = get_db_read_replica()
with db.scoped_session() as session:
reactions = get_reactions(session, tx_ids, type)
reactions = list(map(extend_reaction, reactions))
return success_response(reactions)
def get_latest_sol_user_bank() -> Optional[Dict]:
db = get_db_read_replica()
with db.scoped_session() as session:
user_bank_tx = (session.query(UserBankTransaction).order_by(
desc(UserBankTransaction.slot)).first())
if user_bank_tx:
return helpers.model_to_dictionary(user_bank_tx)
return None
def _get_db_block_state():
db = db_session.get_db_read_replica()
with db.scoped_session() as session:
# Fetch latest block from DB
db_block_query = session.query(Block).filter(
Block.is_current == True).all()
assert len(db_block_query) == 1, "Expected SINGLE row marked as current"
return helpers.model_to_dictionary(db_block_query[0])
def get_followers_for_user(args):
users = []
followee_user_id = args.get('followee_user_id')
current_user_id = args.get('current_user_id')
limit = args.get('limit')
offset = args.get('offset')
db = get_db_read_replica()
with db.scoped_session() as session:
# correlated subquery sqlalchemy code:
# https://groups.google.com/forum/#!topic/sqlalchemy/WLIy8jxD7qg
inner_follow = aliased(Follow)
outer_follow = aliased(Follow)
# subquery to get a user's follower count
inner_select = (session.query(func.count(
inner_follow.followee_user_id)).filter(
inner_follow.is_current == True,
inner_follow.is_delete == False, inner_follow.followee_user_id
== outer_follow.follower_user_id).correlate(outer_follow))
# get all users that follow input user, sorted by their follower count desc
outer_select = (
session.query(
outer_follow.follower_user_id,
inner_select.as_scalar().label(
response_name_constants.follower_count)).filter(
outer_follow.followee_user_id == followee_user_id,
outer_follow.is_current == True,
outer_follow.is_delete == False).
group_by(outer_follow.follower_user_id).order_by(
desc(response_name_constants.follower_count),
# secondary sort to guarantee determinism as explained here:
# https://stackoverflow.com/questions/13580826/postgresql-repeating-rows-from-limit-offset
asc(outer_follow.follower_user_id)))
follower_user_ids_by_follower_count = add_query_pagination(
outer_select, limit, offset).all()
user_ids = [
user_id for (user_id,
follower_count) in follower_user_ids_by_follower_count
]
# get all users for above user_ids
users = get_unpopulated_users(session, user_ids)
# bundle peripheral info into user results
users = populate_user_metadata(session, user_ids, users,
current_user_id)
# order by (follower_count desc, user_id asc) to match query sorting
# tuple key syntax from: https://stackoverflow.com/a/4233482/8414360
users.sort(key=lambda user:
(user[response_name_constants.follower_count],
(user['user_id']) * (-1)),
reverse=True)
return users
def get_oldest_unarchived_play():
"""
Gets the oldest unarchived play in the database
"""
db = db_session.get_db_read_replica()
with db.scoped_session() as session:
play_query = session.query(func.min(Play.created_at))
play = play_query.scalar()
return play
def get_top_followee_saves(saveType, args):
if saveType != 'track':
raise exceptions.ArgumentError(
"Invalid type provided, must be one of 'track'")
limit = args.get('limit', 25)
current_user_id = get_current_user_id()
db = get_db_read_replica()
with db.scoped_session() as session:
# Construct a subquery of all followees
followee_user_ids = (session.query(Follow.followee_user_id).filter(
Follow.follower_user_id == current_user_id,
Follow.is_current == True, Follow.is_delete == False))
followee_user_ids_subquery = followee_user_ids.subquery()
# Construct a subquery of all saves from followees aggregated by id
save_count = (session.query(
Save.save_item_id,
func.count(Save.save_item_id).label(
response_name_constants.save_count)).join(
followee_user_ids_subquery, Save.user_id ==
followee_user_ids_subquery.c.followee_user_id).filter(
Save.is_current == True,
Save.is_delete == False,
Save.save_type == saveType,
).group_by(Save.save_item_id).order_by(
desc(response_name_constants.save_count)).limit(limit))
save_count_subquery = save_count.subquery()
# Query for tracks joined against followee save counts
tracks_query = (session.query(Track, ).join(
save_count_subquery,
Track.track_id == save_count_subquery.c.save_item_id).filter(
Track.is_current == True,
Track.is_delete == False,
Track.is_unlisted == False,
Track.stem_of == None,
))
tracks_query_results = tracks_query.all()
tracks = helpers.query_result_to_list(tracks_query_results)
track_ids = list(map(lambda track: track['track_id'], tracks))
# bundle peripheral info into track results
tracks = populate_track_metadata(session, track_ids, tracks,
current_user_id)
if args.get('with_users', False):
user_id_list = get_users_ids(tracks)
users = get_users_by_id(session, user_id_list)
for track in tracks:
user = users[track['owner_id']]
if user:
track['user'] = user
return tracks
def get_latest_play():
"""
Gets the latest play in the database
"""
db = db_session.get_db_read_replica()
with db.scoped_session() as session:
play_query = (session.query(Play.created_at).order_by(
Play.created_at.desc()).limit(1))
play = play_query.scalar()
return play
def get_aggregate_route_metrics_trailing_month():
"""
Returns trailing count and unique count for all routes in the last trailing 30 days
Returns:
{ unique_count, total_count }
"""
db = db_session.get_db_read_replica()
with db.scoped_session() as session:
return _get_aggregate_route_metrics_trailing_month(session)
def get_users_cnode(cnode_endpoint_string, replica_type=ReplicaType.PRIMARY):
'''
Query all users with `cnode_endpoint_string` in replica set
If replica_type=ReplicaType.PRIMARY -> returns users with `cnode_endpoint_string` as primary
Else if replica_type=ReplicaType.SECONDARY -> returns users with `cnode_endpoint_string` as secondary1 or secondary2
Else (only other option is replica_type=ReplicaType.ALL)
Only returns values where 1/2 secondaries are non-null
'''
users = []
db = get_db_read_replica()
with db.scoped_session() as session:
users_res = sqlalchemy.text(f"""
SELECT
*
FROM
(
SELECT
"user_id",
"wallet",
("creator_node_endpoints") [1] as "primary",
("creator_node_endpoints") [2] as "secondary1",
("creator_node_endpoints") [3] as "secondary2"
FROM
(
SELECT
"user_id",
"wallet",
string_to_array("creator_node_endpoint", ',') as "creator_node_endpoints"
FROM
"users"
WHERE
"creator_node_endpoint" IS NOT NULL
AND "is_current" IS TRUE
ORDER BY
"user_id" ASC
) as "s"
) as "t"
WHERE
{
"t.primary = :cnode_endpoint_string AND"
if replica_type == ReplicaType.PRIMARY
else '(t.secondary1 = :cnode_endpoint_string OR t.secondary2 = :cnode_endpoint_string) AND'
if replica_type == ReplicaType.SECONDARY
else '(t.primary = :cnode_endpoint_string OR '
't.secondary1 = :cnode_endpoint_string OR '
't.secondary2 = :cnode_endpoint_string) AND'
}
t.secondary1 is not NULL;
""")
users = session.execute(users_res, {
"cnode_endpoint_string": cnode_endpoint_string
}).fetchall()
users_dict = [dict(row) for row in users]
return users_dict
def get_app_names(args):
"""
Returns a list of app names
Args:
args: dict The parsed args from the request
args.offset: number The offset to start querying from
args.limit: number The max number of queries to return
args.start_time: date The start of the query
args.include_unknown: bool Whether or not to include a line item for unknown
Returns:
Array of dictionaries with name, count, and unique_count fields
"""
db = db_session.get_db_read_replica()
with db.scoped_session() as session:
app_names = (session.query(
AppNameMetrics.application_name,
func.sum(AppNameMetrics.count).label('count'),
func.count(AppNameMetrics.ip.distinct())).filter(
AppNameMetrics.timestamp > args.get('start_time')).group_by(
AppNameMetrics.application_name).order_by(
desc('count'),
asc(AppNameMetrics.application_name)).limit(
args.get('limit')).offset(
args.get('offset')).all())
names = [{
'name': app_name[0],
'count': app_name[1],
'unique_count': app_name[2]
} for app_name in app_names]
if args.get('include_unknown', False):
existing_count = reduce(lambda x, y: x + y['count'], names, 0)
existing_unique_count = reduce(lambda x, y: x + y['unique_count'],
names, 0)
total_requests = (session.query(
func.sum(RouteMetrics.count).label('count'),
func.count(RouteMetrics.ip.distinct())).filter(
RouteMetrics.timestamp > args.get('start_time')).first())
unknown_count = total_requests[0] - existing_count
unique_count = total_requests[1] - existing_unique_count
# Insert unique counts "in order" (desc by count)
for i, name in enumerate(names[:]):
if unknown_count > name['count'] or i == len(names):
names.insert(
i, {
'name': 'unknown',
'count': unknown_count,
'unique_count': unique_count
})
break
return names
def get_playlist_tracks(args):
playlists = []
current_user_id = args.get("current_user_id")
limit = args.get("limit")
offset = args.get("offset")
db = get_db_read_replica()
with db.scoped_session() as session:
try:
playlist_id = args.get("playlist_id")
playlist = (
session
.query(Playlist)
.filter(
Playlist.is_current == True,
Playlist.playlist_id == playlist_id
)
.first()
)
if playlist is None:
return None
playlist_track_ids = [track_id['track']
for track_id in playlist.playlist_contents['track_ids']]
if limit and offset:
playlist_track_ids = playlist_track_ids[offset:offset+limit]
playlist_tracks = (
session
.query(Track)
.filter(
Track.is_current == True,
Track.track_id.in_(playlist_track_ids)
)
.all()
)
tracks = helpers.query_result_to_list(playlist_tracks)
tracks = populate_track_metadata(
session, playlist_track_ids, tracks, current_user_id)
if args.get("with_users", False):
add_users_to_tracks(session, tracks, current_user_id)
tracks_dict = {track['track_id']: track for track in tracks}
playlist_tracks = []
for track_id in playlist_track_ids:
playlist_tracks.append(tracks_dict[track_id])
return playlist_tracks
except sqlalchemy.orm.exc.NoResultFound:
pass
return playlists