127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
...
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
|
) 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,
................................................................................
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', 4 ),
('like', 3 ),
('react', 5 )
) 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(
2::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
|
|
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
...
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
|
) 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,
................................................................................
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
|