-- these views are not part of the schema proper, but rather are uploaded
-- into postgres' memory by parsav at the beginning of a connection. they
-- are not visible to other clients and politely disappear once the
-- connection terminates, allowing us to simultaneously avoid versioning
-- headaches, limit the amount of data we need to send to the server, and
-- reduce the compilation time of our prepared queries.
create or replace temp view parsavpg_post_react_counts as (
with counts as (
select a.kind, p.id as subject, count(*) as ct from parsav_acts as a
inner join parsav_posts as p on p.id = a.subject
group by a.kind, p.id
)
select p.id as post,
coalesce((select counts.ct from counts where counts.subject = p.id
and counts.kind = 'like'),0)::integer as likes,
coalesce((select counts.ct from counts where counts.subject = p.id
and counts.kind = 'rt' ),0)::integer as rts
from parsav_posts as p
);
create type pg_temp.parsavpg_intern_notice as (
kind smallint,
"when" bigint,
who bigint,
what bigint,
reply bigint,
reaction text
);
create type pg_temp.parsavpg_intern_actor as (
id bigint,
nym text,
handle text,
origin bigint,
bio text,
avataruri text,
rank smallint,
quota integer,
key bytea,
epithet text,
knownsince bigint,
xid text,
invites integer
);
create or replace function
pg_temp.parsavpg_translate_actor(parsav_actors)
returns pg_temp.parsavpg_intern_actor as $$
select
($1).id, ($1).nym, ($1).handle, ($1).origin, ($1).bio,
($1).avataruri, ($1).rank, ($1).quota, ($1).key, ($1).epithet,
($1).knownsince::bigint,
coalesce(($1).handle || '@' ||
(select domain from parsav_servers as s where s.id = ($1).origin),
'@' || ($1).handle) as xid,
($1).invites
$$ language sql;
--drop type if exists pg_temp.parsavpg_intern_post;
create type pg_temp.parsavpg_intern_post as (
-- order is crucially important, and must match the order used
-- in row_to_actor. names don't matter
localpost bool,
id bigint,
author bigint,
subject text,
acl text,
body text,
posted bigint,
discovered bigint,
edited bigint,
parent bigint,
convoheaduri text,
chgcount integer,
accent smallint,
rtdby bigint, -- note that these must be 0 if the record
rtid bigint, -- in question does not represent an RT!
n_likes integer,
n_rts integer
);
create or replace function
pg_temp.parsavpg_translate_post(parsav_posts,bigint,bigint)
returns pg_temp.parsavpg_intern_post as $$
select a.origin is null,
($1).id, ($1).author,
($1).subject,($1).acl, ($1).body,
($1).posted, ($1).discovered, ($1).edited,
($1).parent, ($1).convoheaduri,($1).chgcount,
coalesce(c.value, -1)::smallint,
$2 as rtdby, $3 as rtid,
re.likes, re.rts
from parsav_actors as a
left join parsav_actor_conf_ints as c
on c.key = 'ui-accent' and
c.uid = a.id
left join pg_temp.parsavpg_post_react_counts as re
on re.post = ($1).id
where a.id = ($1).author
$$ language sql;
create or replace temp view parsavpg_known_content as (
with posts as (
select p as orig,
null::bigint as promoter,
null::bigint as promotion,
coalesce(p.posted,p.discovered) as promotime
from parsav_posts as p
),
rts as (
select p as orig,
a.actor as promoter,
a.id as promotion,
a.time as promotime
from parsav_acts as a
inner join parsav_posts as p on a.subject = p.id
where a.kind = 'rt'
),
content as (select * from posts union select * from rts)
select pg_temp.parsavpg_translate_post(cn.orig,
coalesce(cn.promoter,0), coalesce(cn.promotion,0)
) as post,
cn.promotime::bigint as tltime,
coalesce(cn.promoter, (cn.orig).author) as promoter
from content as cn
order by cn.promotime desc
);
--create temp view parsavpg_post_threads as (
--
--);
create temp view parsavpg_notices as (
-- TODO add mentions
with ntimes as (
select uid, value as when from parsav_actor_conf_ints where key = 'notice-clear-time'
), acts as (
select row(
kmap.kind::smallint,
a.time,
a.actor,
a.subject,
null::bigint,
null::text
)::pg_temp.parsavpg_intern_notice as notice,
p.author as rcpt
from parsav_acts as a
inner join parsav_posts as p on a.subject = p.id
inner join (values
('rt', <notice:rt> ),
('like', <notice:like> ),
('react', <notice:react>)
) as kmap(kstr,kind) on kmap.kstr = a.kind
left join ntimes as nt on nt.uid = p.author
where a.time >= coalesce(nt.when,0)
), replies as (
select row(
<notice:reply>::smallint,
coalesce(p.posted,p.discovered),
p.author,
p.parent,
p.id,
null::text
)::pg_temp.parsavpg_intern_notice as notice,
par.author as rcpt
from parsav_posts as p
inner join parsav_posts as par on p.parent = par.id
left join ntimes as nt on nt.uid = p.author
where p.discovered >= coalesce(nt.when,0)
), allnotices as (select * from acts union select * from replies)
table allnotices order by (notice).when desc
);