-- 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_artifact as (
rid bigint,
owner bigint,
"desc" text,
folder text,
mime text
);
create or replace function
pg_temp.parsavpg_translate_artifact(parsav_artifact_claims)
returns pg_temp.parsavpg_intern_artifact as $$
select ($1).rid, ($1).uid, ($1).description, ($1).folder, a.mime
from parsav_artifacts a where
a.id = ($1).rid limit 1
$$ language sql;
create type pg_temp.parsavpg_intern_notice as (
kind smallint,
"when" bigint,
who bigint,
what bigint,
reply bigint,
reaction text
);
create or replace function
pg_temp.parsavpg_translate_act(parsav_acts)
returns pg_temp.parsavpg_intern_notice as $$
select row(
kmap.kind::smallint,
($1).time,
($1).actor,
($1).subject,
null::bigint,
($1).body
)::pg_temp.parsavpg_intern_notice as notice
from (values
('rt', <notice:rt> ),
('like', <notice:like> ),
('react', <notice:react> ),
('follow',<notice:follow>)
) as kmap(kstr,kind) where kmap.kstr = ($1).kind
$$ language sql;
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,
avatarid bigint
);
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, ($1).avatarid
$$ 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,
-- ephemeral
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,
isreply bool -- true if parent in (table posts); saves us a bunch of queries
);
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,
($1).parent in (select id from parsav_posts)
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
pg_temp.parsavpg_translate_act(a) as notice,
-- 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)
), follows as (
select row(
<notice:follow>::smallint,
r.since,
r.relator,
r.relatee,
null::bigint,
null::text
)::pg_temp.parsavpg_intern_notice as notice,
r.relatee as rcpt
from parsav_rels as r
left join ntimes as nt on nt.uid = r.relatee
where
r.since >= coalesce(nt.when,0) and
r.kind = <rel:follow>
), allnotices as (table acts union table replies union table follows)
table allnotices order by (notice).when desc
);