parsav  Diff

Differences From Artifact [25f9d405bc]:

• File backend/schema/pgsql-views.sql — part of check-in [d6024624c6] at 2021-01-08 05:58:30 on branch trunk — enable passwords (user: lexi, size: 5600) [annotate] [blame] [check-ins using]

To Artifact [c997d2f5ad]:

• File backend/schema/pgsql-views.sql — part of check-in [05af79b909] at 2021-01-09 07:15:21 on branch trunk — user mgmt and rt improvements (user: lexi, size: 6132) [annotate] [blame] [check-ins using]

    40     40   	kind	smallint,
    41     41   	"when"	bigint,
    42     42   	who		bigint,
    43     43   	what	bigint,
    44     44   	reply	bigint,
    45     45   	reaction text
    46     46   );
           47  +
           48  +create or replace function
           49  +pg_temp.parsavpg_translate_act(parsav_acts)
           50  +returns pg_temp.parsavpg_intern_notice as $$
           51  +	select row(
           52  +			kmap.kind::smallint,
           53  +			($1).time,
           54  +			($1).actor,
           55  +			($1).subject,
           56  +			null::bigint,
           57  +			($1).body
           58  +		)::pg_temp.parsavpg_intern_notice as notice
           59  +	from (values
           60  +		('rt',    <notice:rt>   ),
           61  +		('like',  <notice:like> ),
           62  +		('react', <notice:react>)
           63  +	) as kmap(kstr,kind) where kmap.kstr = ($1).kind
           64  +$$ language sql;
    47     65   
    48     66   create type pg_temp.parsavpg_intern_actor as (
    49     67   	id			bigint,
    50     68   	nym			text,
    51     69   	handle		text,
    52     70   	origin		bigint,
    53     71   	bio			text,
................................................................................
   156    174   --);
   157    175   
   158    176   create temp view parsavpg_notices as (
   159    177    -- TODO add mentions
   160    178   	with ntimes as (
   161    179   		select uid, value as when from parsav_actor_conf_ints where key = 'notice-clear-time'
   162    180   	), acts as (
   163         -		select row(
   164         -				kmap.kind::smallint,
   165         -				a.time,
   166         -				a.actor,
   167         -				a.subject,
   168         -				null::bigint,
   169         -				null::text
   170         -			)::pg_temp.parsavpg_intern_notice as notice,
          181  +		select
          182  +			pg_temp.parsavpg_translate_act(a) as notice,
          183  +		-- row(
          184  +		-- 		kmap.kind::smallint,
          185  +		-- 		a.time,
          186  +		-- 		a.actor,
          187  +		-- 		a.subject,
          188  +		-- 		null::bigint,
          189  +		-- 		null::text
          190  +		-- 	)::pg_temp.parsavpg_intern_notice as notice,
   171    191   			p.author as rcpt
   172    192   		from parsav_acts as a
   173    193   			inner join parsav_posts as p on a.subject = p.id
   174         -			inner join (values
   175         -				('rt',    <notice:rt>   ),
   176         -				('like',  <notice:like> ),
   177         -				('react', <notice:react>)
   178         -			) as kmap(kstr,kind) on kmap.kstr = a.kind
          194  +			-- inner join (values
          195  +			-- 	('rt',    <notice:rt>   ),
          196  +			-- 	('like',  <notice:like> ),
          197  +			-- 	('react', <notice:react>)
          198  +			-- ) as kmap(kstr,kind) on kmap.kstr = a.kind
   179    199   			left  join ntimes as nt on nt.uid = p.author
   180    200   		where a.time >= coalesce(nt.when,0)
   181    201   	), replies as (
   182    202   		select row(
   183    203   				<notice:reply>::smallint,
   184    204   				coalesce(p.posted,p.discovered),
   185    205   				p.author,