import typing
from html import unescape
from tracker import db, artist_model, cacher
from tracker.logger import get_logger
from api.resources import login
logger = get_logger(__name__)
TheSession = db.ROSession
# in lists for 'standard' models.
# An attempt to consolidate some of the query formats. In lists should work.
from tracker.utils import null_safe_path, StopWatch, chunks
def soundcloud_tracks(scids) -> typing.Generator:
return db.execute_to_dicts(
"""
with scores as (
select
track_scid,
jsonb_agg_uniq_by_first_item(
jsonb_build_array(
to_stat_date(as_of::date),
playback_count,
comment_count,
favoritings_count
)
order by as_of
) as timeseries,
dfdt_stat_array(
jsonb_agg_uniq_by_first_item(
jsonb_build_array(
to_stat_date(as_of::date),
playback_count,
comment_count,
favoritings_count
)
order by as_of
)
) as df_timeseries,
hockey(
(array_agg(playback_count::float order by as_of) filter (where as_of > current_date - interval '30 days'))::float[],
20,
25
) as plays,
hockey(
(array_agg(favoritings_count::float order by as_of) filter (where as_of > current_date - interval '30 days'))::float[],
20,
25
) as likes
from sc_track_stats
where track_scid = any(:scids)
and as_of > current_date - interval '730 days'
group by track_scid
)
SELECT
sct.scid,
jsonb_build_object(
'source', 'sc',
'scid', sct.scid,
'artist_scid', sct.artist_scid,
'title', sct.name,
'artwork_url', COALESCE (sct.data->>'artwork_url', sct.data->'user'->>'avatar_url'),
'soundcloud_url', sct.data->>'permalink_url',
'release_date', sct.created_at,
'total_plays', COALESCE (sct.data->>'playback_count','0')::int,
'total_likes', COALESCE (sct.data->>'favoritings_count','0')::int,
'total_comments', COALESCE (sct.data->>'comment_count','0')::int,
'genre', sct.data->>'genre',
'stats', scores.timeseries,
'df_stats', scores.df_timeseries,
'scores', jsonb_build_object(
'plays', scores.plays,
'likes', scores.likes
)
) as track,
sct.artist_scid as artist_scid
from sc_tracks sct
join scores on sct.scid = scores.track_scid
where sct.scid = any(:scids)
order by array_position(:scids, sct.scid)
""",
params=dict(scids=scids),
read_replica=True,
)
def spotify_tracks(
spyids, with_artist_stats=True, explain_analyze=False
) -> typing.Generator:
if not spyids:
return (_ for _ in ())
return db.execute_to_dicts(
("explain analyze " if explain_analyze else "")
+ """
with
artistids as (
select array_agg(distinct primary_artist_spyid) ids
from spy_tracks
where spyid = any(:spyids)
),
track_data as (
SELECT
t.spyid,
t.primary_artist_spyid,
jsonb_build_object(
'source', 'spy',
'spyid', t.spyid,
'artist_spyid', first(t.primary_artist_spyid),
'title', first(t.name),
'artwork_url', first(t.album_data->'images'->0->>'url'),
'spotify_uri', first(t.data->'uri'),
'release_date', first(t.album_data->>'release_date'),
'popularity', first(COALESCE (t.data->>'popularity','0')::int),
'genre', first(t.album_data->'genres'->0),
'copyrights', first(t.album_data->'copyrights'),
'isSigned', t.tags ?| array['label-sony', 'label-warner', 'label-universal', 'label-other'],
'stats', jsonb_agg_uniq(
jsonb_build_array(to_stat_date(p.as_of::date), p.value) order by p.as_of),
'streamsEstimate100', first(est.streams),
'countryCodeIsrc', upper(substring(first(t.data ->'external_ids'->>'isrc'), 1, 2))
) as track
from spy_tracks t
join spy_track_popularity p on p.track_spyid = t.spyid and p.track_spyid = any(:spyids)
left join spy_track_stream_estimates est on est.spyid = t.spyid
where t.spyid = any(:spyids)
group by t.spyid
),
with_top200 as (
select
t.spyid,
first(t.primary_artist_spyid) primary_artist_spyid,
first(t.track) || jsonb_build_object(
'top200', jsonb_agg(
distinct jsonb_build_array(
soc0.country_code,
soc0.position,
200,
soc0.streams,
soc0.position - soc1.position
)
) filter (where soc0.as_of = current_date),
'top200History', jsonb_agg(
jsonb_build_array(
soc0.country_code,
to_stat_date(soc0.as_of),
soc0.position,
soc0.streams
)
order by soc0.country_code, soc0.as_of
)
) as track
from track_data t
left join spy_official_charts soc0
on soc0.spyid = t.spyid
and soc0.spyid = any(:spyids)
left join spy_official_charts soc1
on soc1.spyid = t.spyid
and soc1.spyid = any(:spyids)
and soc1.as_of = current_date - 1
and soc1.country_code = soc0.country_code
where t.spyid = any(:spyids)
group by t.spyid
),
with_playlists as (
select
t.spyid,
first(t.primary_artist_spyid) primary_artist_spyid,
first(t.track) || jsonb_build_object(
'playlistStats', json_build_object(
'currentPlaylistCount',
count(spt.playlist_spyid) filter (where spt.last_seen > now() - interval '36 hours'),
'allPlaylistCount',
count(spt.playlist_spyid),
'currentPlaylistFollowers',
sum(COALESCE(sp.data->'followers'->>'total', '0')::integer)
filter (where spt.last_seen > now() - interval '36 hours'),
'allPlaylistFollowers',
sum(COALESCE(sp.data->'followers'->>'total', '0')::integer)
),
'playlists', jsonb_agg_uniq(
jsonb_build_array(
to_stat_date(coalesce(spt.added_at, spt.first_seen)::date),
spt.playlist_spyid,
spt.last_seen > now() - interval '36 hours',
COALESCE(sp.data->'followers'->>'total', '0')
)
order by coalesce(spt.added_at, spt.first_seen)
)
) as track
from with_top200 t
left join spy_playlist_track spt on spt.track_spyid = t.spyid and spt.track_spyid = any(:spyids)
left join spy_playlist sp on sp.spyid = spt.playlist_spyid
where t.spyid = any(:spyids)
group by t.spyid
),
with_artist_stats as (
select
t.spyid,
jsonb_build_object(
'artistStats', jsonb_agg_uniq(
jsonb_build_array(to_stat_date(sam.as_of :: date), sam.followers, sam.popularity)
order by sam.as_of
)
) as with_artist_stats
from with_playlists t
join spy_artist_metrics sam
on sam.artist_spyid = t.primary_artist_spyid
and sam.artist_spyid = any((select ids from artistids)::text[])
and sam.as_of > now() - interval '120 days'
where t.spyid = any(:spyids)
group by t.spyid
)
select
t.spyid,
t.track || coalesce(a.with_artist_stats, '{}'::jsonb) as track
from with_playlists t
left join with_artist_stats a on :with_artist_stats = true and a.spyid = t.spyid
order by array_position((:spyids)::text[], t.spyid::text);
""",
params=dict(spyids=spyids, with_artist_stats=with_artist_stats),
read_replica=False,
)
def _unescape_title(yt_result):
yt_result["video"]["title"] = unescape(yt_result["video"]["title"])
return yt_result
def youtube_videos(ytids) -> typing.Generator:
if not ytids:
return (_ for _ in ())
return (
_unescape_title(r)
for r in db.execute_to_dicts(
"""
with videos as (
select
v.ytid,
v.title,
v.published,
coalesce(ch.name, v.api_data->'snippet'->>'channelTitle') as channel_name,
coalesce(ch.ytid, v.api_data->'snippet'->>'channelId') as channel_ytid,
v.removed_at
from yt_videos v
left join yt_channel_videos cv on cv.video_ytid = v.ytid
left join yt_channels ch on ch.ytid = cv.channel_ytid
WHERE v.ytid = ANY (:ytids)
)
select
v.ytid as ytid,
first(to_jsonb(v.*)) || first(to_jsonb(s.*) order by s.as_of desc)
|| jsonb_build_object(
'stats', jsonb_agg_uniq_by_first_item(
jsonb_build_array(to_stat_date(s.as_of::date), s.view_count, s.comment_count, s.like_count, s.dislike_count)
order by s.as_of))
as "video"
from videos v
join yt_video_statistics s
on s.video_ytid = v.ytid
and s.video_ytid = ANY (:ytids)
where v.ytid = ANY (:ytids)
group by v.ytid
order by array_position((:ytids)::text[], v.ytid::text)
""",
params=dict(ytids=ytids),
read_replica=True,
)
)
def custom_items(item_ids) -> typing.Generator:
if not item_ids:
return (_ for _ in ())
return db.execute_to_dicts(
"""
select
c.item_id as custom_item_id,
to_jsonb(c.*) as "customItem"
from chart_custom_items c
where c.item_id = ANY (:item_ids)
""",
params=dict(item_ids=item_ids),
read_replica=True,
)
def setf_artist_profiles(keys: list) -> list:
def setf_query(source, ids):
all_artists = []
for chunk in chunks(ids, 5):
all_artists.extend(
list(
db.execute_to_dicts(
"""
select
groupkey as id,
arid,
scid,
eid,
inid,
spyid,
ytid,
to_jsonb(p.*) || jsonb_build_object('query_key', p.groupkey) as artist
from setf_artist_profiles2((:source)::text, variadic (:ids)::text[]) p""",
params=dict(source=source, ids=chunk),
)
)
)
return all_artists
source_groups = {}
for key in keys:
source, id = key.split("/")
source_groups.setdefault(source, []).append(id)
all_queried_keys = set([])
artists = []
for source, ids in source_groups.items():
for artist in setf_query(source, ids):
if all_queried_keys.isdisjoint(artist["artist"]["keys"]):
artists.append(artist)
all_queried_keys.update(artist["artist"]["keys"])
return artists
def artists(
source,
ids,
include_alerts=False,
include_media=False,
include_stats=False,
include_scouts=True,
include_source_data=False,
only_alerts_for_today=False,
use_cache_only=False,
refresh_cache=False,
profile_chunk_size=5,
extra_data_chunk_size=5,
log_explain_analyze=False,
) -> typing.List[dict]:
if False and use_cache_only:
query_keys = [f"{source}/{i}" for i in ids]
artist_dicts = cacher.get_artists(query_keys)
if refresh_cache:
ids_needing_caching = [
a["query_key"].split("/")[1]
for a in artist_dicts
if a["cache_status"] in ("LOADING", "REFRESHING")
]
from api import cache_model
print(f"Refreshing {source} {ids_needing_caching}")
cache_model.executor.submit(
cache_model.cache_artists, source, ids_needing_caching
)
def make_artist_api_obj(artist_dict):
src, id = artist_dict["query_key"].split("/")
top_level = {
k: artist_dict.get(k)
or (id if src == k[0:-2] or src == "a" and k == "arid" else None)
for k in ["arid", "scid", "eid", "inid", "spyid", "ytid"]
}
if top_level["arid"]:
top_level["arid"] = int(top_level["arid"])
if top_level["scid"]:
top_level["scid"] = int(top_level["scid"])
if top_level["eid"]:
top_level["eid"] = int(top_level["eid"])
top_level["artist"] = artist_dict
return top_level
return [make_artist_api_obj(a) for a in artist_dicts]
return artist_model.fetch_artist_models(
source,
ids,
include_alerts=include_alerts,
include_media=include_media,
include_stats=include_stats,
include_scouts=include_scouts,
include_source_data=include_source_data,
only_alerts_for_today=only_alerts_for_today,
profile_chunk_size=profile_chunk_size,
extra_data_chunk_size=extra_data_chunk_size,
log_explain_analyze=log_explain_analyze,
)
def get_whitelisted_data(arid, username):
all_arids = (
(
TheSession.execute(
"""
select
jsonb_build_array(arid)
|| coalesce(merged_arids, '[]'::jsonb)
|| jsonb_build_array(coalesce(merged_to_arid, -1))
from artists
where arid = :arid""",
params=dict(arid=arid),
).fetchone()
or (None,)
)[0]
or []
)
wa = (
TheSession.query(db.WhitelistedArtistAudit)
.filter(
db.WhitelistedArtistAudit.username == username,
db.WhitelistedArtistAudit.arid.in_(all_arids),
)
.order_by(db.WhitelistedArtistAudit.ts)
.all()
)
if wa:
wl_data = {
"first_listed": {"val": wa[0].ts.isoformat() + "Z"},
"current_state": wa[-1].action,
"as_of": {"val": wa[-1].ts.isoformat() + "Z"},
}
else:
wl_data = None
return wl_data
def get_whitelisted_data_v2(arid, user: login.User):
all_arids = (
(
TheSession.execute(
"""
select
jsonb_build_array(arid)
|| coalesce(merged_arids, '[]'::jsonb)
|| jsonb_build_array(coalesce(merged_to_arid, -1))
from artists
where arid = :arid""",
params=dict(arid=arid),
).fetchone()
or (None,)
)[0]
or []
)
whitelist_ids = [user.name]
if user.team:
whitelist_ids.append(user.team)
rows = db.Session.execute(
"""
select
username,
min(ts),
first(action order by ts desc),
max(ts)
from whitelisted_artists_audit
where username = any(:whitelist_ids)
and arid = any(:arids)
group by username
""",
params=dict(arids=all_arids, whitelist_ids=whitelist_ids),
).fetchall()
info = {"private": None, "team": None}
for whitelist_id, first_listed, current, as_of in rows:
obj = {
"first_listed": first_listed.isoformat() + "Z",
"current_state": current,
"as_of": as_of.isoformat() + "Z",
}
if whitelist_id == user.name:
info["private"] = obj
if whitelist_id == user.team:
# should we use elif?... thinker.
info["team"] = obj
return info
def _to_source_link(source, artist_dict):
ret = {"arid": artist_dict.get("arid"), "source": source}
if source == "sc":
if not artist_dict.get("scid"):
return None
ret.update(
{
"id": str(artist_dict["scid"]),
"source_url": artist_dict["source_data"]["sc"]["permalink_url"],
}
)
return ret
elif source == "tw":
if not artist_dict.get("eid"):
return None
ret.update(
{
"id": str(artist_dict["eid"]),
"source_url": "https://twitter.com/"
+ artist_dict["source_data"]["tw"]["screen_name"],
}
)
return ret
elif source == "in":
if not artist_dict.get("inid"):
return None
ret.update(
{
"id": str(artist_dict["inid"]),
"source_url": "https://www.instagram.com/" + artist_dict["inid"] + "/",
}
)
return ret
elif source == "spy":
if not artist_dict.get("spyid"):
return None
ret.update(
{
"id": str(artist_dict["spyid"]),
"source_url": artist_dict["source_data"]["spy"]["external_urls"][
"spotify"
],
}
)
return ret
raise ValueError(source)
def _get_web_profiles(base_with_links):
def _url_key(url):
return url.lower().strip().strip("/").strip("http:").strip("https:")
profile_map = {}
if base_with_links.get("scid"):
q = (
TheSession.query(db.ScUser.web_profiles)
.filter_by(scid=base_with_links["scid"])
.first()
)
if q and q[0]:
for p in [p for p in q[0] if p.get("url")]:
url = p["url"]
key = _url_key(url)
profile_map[key] = {
"url": p["url"],
"title": p.get("title") or p.get("service") or p["url"],
}
titles = {"sc": "SoundCloud", "tw": "Twitter", "in": "Instagram", "spy": "Spotify"}
for source in base_with_links["links"]:
profile_map[_url_key(source["source_url"])] = {
"url": source["source_url"],
"title": titles.get(source["source"]) or source["source_url"],
}
return list(profile_map.values())
def full_artist(
source,
id,
user: login.User,
include_alerts=False,
include_stats=False,
include_media=False,
include_scouts=False,
include_brains=False,
use_new_setf=False,
):
sw = StopWatch()
if source == "in":
# handle merged inids:
exists = db.Session.execute(
"""select 1 from in_user where inid = :arg_inid""", params=dict(arg_inid=id)
).fetchone()
if not exists:
row = db.Session.execute(
"""select inid from in_user where :arg_inid = any(other_inids)""",
params=dict(arg_inid=id),
).fetchone()
if row:
id = row[0]
else:
return None
arteest = artists(
source,
[str(id)],
include_alerts=include_alerts,
include_stats=include_stats,
include_media=include_media,
include_scouts=include_scouts,
include_source_data=True,
)
sw.print_lap("artist")
base = list(arteest)[0].get("artist") if len(list(arteest)) > 0 else None
if not base:
return None
base["countryCodes"] = base.get("country_codes")
if base.get("arid"):
base["whitelisted"] = get_whitelisted_data(base.get("arid"), user.name)
base["whitelistedInfo"] = get_whitelisted_data_v2(base.get("arid"), user)
sw.print_lap("whitelist")
base["lastSpidered"] = (
TheSession.query(db.Artist).get(base["arid"]).last_spidered
)
sw.print_lap("spidered")
if not base.get("links"):
# older version of the setf_artist_profile doesn't include links
base["links"] = [
_to_source_link(s, base)
for s in ["sc", "tw", "in", "spy"]
if base["source_data"].get(s)
]
sw.print_lap("links")
base["webProfiles"] = _get_web_profiles(base)
sw.print_lap("webProfiles")
if base.get("inid"):
row = TheSession.execute(
"select other_inids from in_user where inid = :inid",
params=dict(inid=base["inid"]),
).fetchone()
if row and row[0]:
base["otherInids"] = row[0]
if base.get("spyid"):
base["spotifyPlaylists"] = [
r[0]
for r in TheSession.execute(
"""
select
distinct pl.name
from spy_playlist pl
join spy_playlist_track pltr on pltr.playlist_spyid = pl.spyid
join spy_tracks tr on tr.spyid = pltr.track_spyid
where tr.primary_artist_spyid = :id
""",
params=dict(id=base["spyid"]),
).fetchall()
]
sw.print_lap("spotifyPlaylists")
base["spotifyTrackData"] = spotify_tracks_for_artist_spyid(
base["spyid"], max_num=None if include_brains else 5
)
sw.print_lap("spotifyTrackData")
if include_brains:
base["spotifyMonthlyListenerData"] = _fetch_monthly_listener_data(
base["spyid"]
)
if base.get("scid"):
base["soundCloudTrackData"] = get_soundcloud_tracks_for_artist_scid(
base["scid"], max_num=None if include_brains else 5
)
sw.print_lap("soundCloudTrackData")
base["ytVideoData"] = youtube_videos_for_artist_spyid(
artist_ytid=base.get("ytid"),
artist_spyid=base.get("spyid"),
max_num=None if include_brains else 5,
)
sw.print_lap("ytVideoData")
if include_brains:
base["brains"] = {}
_append_brains_to_res(
base["links"],
base["brains"],
include_table_refresh=True,
)
# Gradually migrating off of these stat tables.
sw.print_lap("brains")
if base.get("source_data"):
# sourcedata is removed in new setf
base.pop("source_data")
sw.print_results()
return base
def _fetch_monthly_listener_data(artist_spyid):
return next(
db.execute_to_dicts(
"""
with
cities_series as (
select
as_of,
jsonb_agg( to_jsonb(ci.*) order by ci.listeners desc) as cities
from spy_artist_listener_cities ci
where ci.artist_spyid = :artist_spyid
and ci.as_of > current_date - 14
group by 1
),
playlists_series as (
select
as_of,
jsonb_agg(
jsonb_build_object(
'name', spl.name,
'listeners', pl.listeners
)
order by pl.listeners desc
) as playlists
from spy_artist_listener_playlists pl
join spy_playlist spl on spl.spyid = pl.playlist_spyid
where pl.artist_spyid = :artist_spyid
and pl.as_of > current_date - 14
group by 1
)
select
sal.artist_spyid,
first(to_jsonb(sal.*) order by sal.as_of desc) overall,
jsonb_agg_uniq(jsonb_build_array(to_stat_date(sal.as_of), sal.monthly_listeners) order by sal.as_of) as listeners_series,
first(
jsonb_build_object(
'as_of', cities_series.as_of,
'cities', cities_series.cities
) order by cities_series.as_of asc
) cities,
first(
jsonb_build_object(
'as_of', playlists_series.as_of,
'playlists', playlists_series.playlists
) order by playlists_series.as_of asc
) playlists
from spy_artist_listeners sal
left join cities_series on true
left join playlists_series on true
where sal.artist_spyid = :artist_spyid
and sal.as_of > current_date - 14
group by 1;
""",
params=dict(artist_spyid=artist_spyid),
),
None,
)
from tracker.db import daily_stats
def _append_brains_to_res(links, res, include_table_refresh=False):
for l in links:
source = l["source"].strip()
id = l["id"].strip()
if source == "sc":
if include_table_refresh:
daily_stats.update_for_single_artist_scid(id)
res.setdefault("FollowerCounts", {})["SoundCloud"] = (
TheSession.execute(
"""select to_jsonb(s.*) as d
from stats_sc_artists_stats_followers_count s
where artist_scid = :id
""",
params=dict(id=id),
).fetchone()
or [None]
)[0]
track_scids = [
r[0]
for r in TheSession.execute(
"""
select scid
from sc_tracks
where artist_scid = :artist_scid
and created_at > CURRENT_DATE - interval '730 days'
order by created_at desc
limit 40
""",
params=dict(artist_scid=id),
)
]
soundcloud_engagement_series = (
[
[r[0], int(r[1])]
for r in reversed(
list(
TheSession.execute(
"""
select
(extract(epoch from as_of::date)/(24*3600))::int,
sum((playback_count::bigint+1)*(comment_count::bigint+1))/count(*)
from sc_track_stats
where track_scid = any (:scids)
group by 1
order by 1 desc
""",
params=dict(scids=track_scids),
)
)
)
]
if track_scids
else []
)
res["SoundCloudEngagement"] = {
"series": soundcloud_engagement_series,
"latest": soundcloud_engagement_series[-1][1]
if soundcloud_engagement_series
else None,
}
if source == "spy":
if include_table_refresh:
daily_stats.initialise_table_from_def(
daily_stats.stat_table_defs[6], id_between_inclusive=[id, id]
)
daily_stats.initialise_table_from_def(
daily_stats.stat_table_defs[7], id_between_inclusive=[id, id]
)
res.setdefault("FollowerCounts", {})["Spotify"] = (
TheSession.execute(
"""
select to_jsonb(s.*) as d from stats_spy_artist_metrics_followers s where artist_spyid = :id
""",
params=dict(id=id),
).fetchone()
or [None]
)[0]
res["SpotifyEngagement"] = (
TheSession.execute(
"""
select to_jsonb(s.*) as d from stats_spy_artist_metrics_popularity s where artist_spyid = :id
""",
params=dict(id=id),
).fetchone()
or [None]
)[0]
if source == "in":
if include_table_refresh:
daily_stats.initialise_table_from_def(
daily_stats.stat_table_defs[8], id_between_inclusive=[id, id]
)
res.setdefault("FollowerCounts", {})["Instagram"] = (
TheSession.execute(
"""
select to_jsonb(s.*) as d from stats_in_stats_followed_by_count s where inid = :id
""",
params=dict(id=id),
).fetchone()
or [None]
)[0]
if source == "tw":
if include_table_refresh:
daily_stats.initialise_table_from_def(
daily_stats.stat_table_defs[9], id_between_inclusive=[id, id]
)
res.setdefault("FollowerCounts", {})["Twitter"] = (
TheSession.execute(
"""
select to_jsonb(s.*) as d from stats_tw_stats_followers_count s where eid = :id
""",
params=dict(id=id),
).fetchone()
or [None]
)[0]
if source == "yt":
res["youTubeArtistChannelSeries"] = [
r[0]
for r in db.Session.execute(
"""
select
jsonb_build_array(
to_stat_date(as_of),
subscriber_count,
view_count,
comment_count
)
from yt_artist_channel_stats
where artist_channel_ytid = :ytid
order by as_of asc
""",
params=dict(ytid=id),
).fetchall()
]
def spotify_tracks_for_artist_spyid(artist_spyid, max_num=None):
if max_num:
logger.info(f"Limiting spy tracks to {max_num}")
track_spyids = [
r[0]
for r in TheSession.execute(
"""
select spyid
from spy_tracks
where primary_artist_spyid = :artist_spyid
order by coalesce(album_data->>'release_date', '000') desc
limit :limit
""",
params=dict(artist_spyid=artist_spyid, limit=max_num or 100),
)
]
return {"tracks": list(spotify_tracks(track_spyids, with_artist_stats=False))}
def youtube_videos_for_artist_spyid(artist_ytid=None, artist_spyid=None, max_num=None):
ytids = [
r[0]
for r in TheSession.execute(
(
"select ytid "
"from yt_videos "
"where artist_spyid = :artist_spyid "
" or artist_channel_ytid = :artist_ytid "
"order by published desc "
"limit :limit"
),
params=dict(
artist_ytid=artist_ytid, artist_spyid=artist_spyid, limit=max_num or 50
),
)
]
return {"videos": list(youtube_videos(ytids))}
def get_soundcloud_tracks_for_artist_scid(artist_scid, max_num=None):
track_scids = [
r[0]
for r in TheSession.execute(
"select scid from sc_tracks "
"where artist_scid = :artist_scid "
" and created_at > current_date - interval '1095 days' "
"order by created_at "
"desc limit :limit",
params=dict(artist_scid=artist_scid, limit=max_num or 50),
)
]
return {"tracks": list(soundcloud_tracks(track_scids))}
def regression_artists(username):
"""These objects are too large, we can't safely load all the artists into memory"""
return [
dict(a[0] or {}, added=a[1], isProcessing=a[2])
for a in db.Session.execute(
"""
select artist_profile, added, followers_download_completed is null
from users_sc_regression_artists
where username = :username
and deleted is null
""",
params=dict(username=username),
)
]
def tiktok_sounds(ttids):
return db.execute_to_dicts(
"""
select
ts.ttid,
first(ts.title) as title,
first(ts.author) as author,
first(ts.cover_image_url) as cover_image_url,
first(ts.last_posts) as num_posts,
first(ts.first_seen)::text as first_seen,
first(case when ts.web_url is not null then ts.web_url when ts.ttid like '%-%' then 'https://www.tiktok.com/music/' || ts.ttid else 'https://www.tiktok.com/share/music/' || ts.ttid end) as web_url,
count(*) as num_scout_videos,
first(
jsonb_build_object(
'scout_id', tu.ttid,
'scout_name', tu.nickname,
'scout_avatar', tu.cover_image_url
) order by tv.created_at desc
) as first_video,
first(
jsonb_build_object(
'scout_id', tu.ttid,
'scout_name', tu.nickname,
'scout_avatar', tu.cover_image_url
) order by tv.created_at asc
) as last_video,
jsonb_agg(
distinct jsonb_build_object(
'id', tu.ttid,
'name', tu.nickname,
'avatar', tu.cover_image_url
)
) as scouts,
count(distinct tu.ttid) as num_unique_scouts,
min(tv.created_at)::text earliest_scout_video_created,
max(tv.created_at)::text latest_scout_video_created,
sum(last_digg_count) as top_video_likes,
avg(last_digg_count) as avg_video_likes
from tiktok_sounds ts
left join tiktok_videos tv on ts.ttid = tv.sound_ttid
left join tiktok_users tu on tu.ttid = tv.author_ttid
where ts.ttid = any (:ttids)
group by ts.ttid
order by array_position((:ttids)::text[], ts.ttid::text)
""",
params=dict(ttids=ttids),
)