40
41
42
43
44
45
46
47
48
49
50
51
52
53
...
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
|
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,
................................................................................
--);
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,
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
|
>
>
|
|
|
|
|
|
|
|
|
|
|
|
|
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
...
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
|
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>)
) 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,
................................................................................
--);
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,
|