Differences From
Artifact [2e62d4947d]:
22 22 delete from parsav_config where
23 23 key = $1::text
24 24 ]];
25 25 };
26 26
27 27 actor_fetch_uid = {
28 28 params = {uint64}, sql = [[
29 - select a.id, a.nym, a.handle, a.origin, a.bio,
30 - a.avataruri, a.rank, a.quota, a.key, a.epithet,
31 - extract(epoch from a.knownsince)::bigint,
32 - coalesce(a.handle || '@' || s.domain,
33 - '@' || a.handle) as xid,
34 - a.invites
35 -
36 - from parsav_actors as a
37 - left join parsav_servers as s
38 - on a.origin = s.id
39 - where a.id = $1::bigint
29 + select (pg_temp.parsavpg_translate_actor(a)).*
30 + from parsav_actors as a
31 + where a.id = $1::bigint
40 32 ]];
41 33 };
42 34
43 35 actor_fetch_xid = {
44 36 params = {pstring}, sql = [[
45 - select a.id, a.nym, a.handle, a.origin, a.bio,
46 - a.avataruri, a.rank, a.quota, a.key, a.epithet,
47 - extract(epoch from a.knownsince)::bigint,
48 - coalesce(a.handle || '@' || s.domain,
49 - '@' || a.handle) as xid,
50 - a.invites,
51 -
52 - coalesce(s.domain,
53 - (select value from parsav_config
54 - where key='domain' limit 1)) as domain
55 -
56 - from parsav_actors as a
57 - left join parsav_servers as s
58 - on a.origin = s.id
59 -
60 - where $1::text = (a.handle || '@' || domain) or
61 - $1::text = ('@' || a.handle || '@' || domain) or
37 + with txd as (
38 + select (pg_temp.parsavpg_translate_actor(a)).* from parsav_actors as a
39 + )
40 + select * from txd as a where $1::text = xid or
62 41 (a.origin is null and
63 42 $1::text = a.handle or
64 - $1::text = ('@' || a.handle))
43 + $1::text = (a.handle ||'@'||
44 + (select value from parsav_config where key='domain')))
65 45 ]];
66 46 };
67 47
68 48 actor_purge_uid = {
69 49 params = {uint64}, cmd = true, sql = [[
70 50 with d as ( -- cheating
71 51 delete from parsav_sanctions where victim = $1::bigint
................................................................................
102 82 };
103 83
104 84 actor_create = {
105 85 params = {
106 86 rawstring, rawstring, uint64, lib.store.timepoint,
107 87 rawstring, rawstring, lib.mem.ptr(uint8),
108 88 rawstring, uint16, uint32, uint32
109 - };
110 - sql = [[
89 + }, sql = [[
111 90 insert into parsav_actors (
112 91 nym,handle,
113 92 origin,knownsince,
114 93 bio,avataruri,key,
115 94 epithet,rank,quota,
116 - invites
95 + invites,authtime
117 96 ) values ($1::text, $2::text,
118 97 case when $3::bigint = 0 then null
119 98 else $3::bigint end,
120 - to_timestamp($4::bigint),
99 + $4::bigint,
121 100 $5::bigint, $6::bigint, $7::bytea,
122 101 $8::text, $9::smallint, $10::integer,
123 - $11::integer
102 + $11::integer,$4::bigint
124 103 ) returning id
125 104 ]];
126 105 };
127 106
128 107 actor_auth_pw = {
129 108 params = {pstring,rawstring,pstring,lib.store.inet}, sql = [[
130 109 select a.aid, a.uid, a.name from parsav_auth as a
................................................................................
138 117 ]];
139 118 };
140 119
141 120 actor_enum_local = {
142 121 params = {}, sql = [[
143 122 select id, nym, handle, origin, bio,
144 123 null::text, rank, quota, key, epithet,
145 - extract(epoch from knownsince)::bigint,
124 + knownsince::bigint,
146 125 '@' || handle,
147 126 invites
148 127 from parsav_actors where origin is null
149 128 order by nullif(rank,0) nulls last, handle
150 129 ]];
151 130 };
152 131
153 132 actor_enum = {
154 133 params = {}, sql = [[
155 - select a.id, a.nym, a.handle, a.origin, a.bio,
156 - a.avataruri, a.rank, a.quota, a.key, a.epithet,
157 - extract(epoch from a.knownsince)::bigint,
158 - coalesce(a.handle || '@' || s.domain,
159 - '@' || a.handle) as xid,
160 - invites
161 - from parsav_actors a
162 - left join parsav_servers s on s.id = a.origin
134 + select (pg_temp.parsavpg_translate_actor(a)).*
135 + from parsav_actors as a
136 +
163 137 order by nullif(a.rank,0) nulls last, a.handle, a.origin
164 138 ]];
165 139 };
166 140
167 141 actor_stats = {
168 - params = {uint64}, sql = ([[
142 + params = {uint64}, sql = [[
169 143 with tweets as (
170 144 select from parsav_posts where author = $1::bigint
171 145 ),
172 146 follows as (
173 147 select relatee as user from parsav_rels
174 - where relator = $1::bigint and kind = <follow>
148 + where relator = $1::bigint and kind = <rel:follow>
175 149 ),
176 150 followers as (
177 151 select relator as user from parsav_rels
178 - where relatee = $1::bigint and kind = <follow>
152 + where relatee = $1::bigint and kind = <rel:follow>
179 153 ),
180 154 mutuals as (
181 155 select * from follows intersect select * from followers
182 156 )
183 157
184 158 values (
185 159 (select count(tweets.*)::bigint from tweets),
186 160 (select count(follows.*)::bigint from follows),
187 161 (select count(followers.*)::bigint from followers),
188 162 (select count(mutuals.*)::bigint from mutuals)
189 163 )
190 - ]]):gsub('<(%w+)>',function(r) return tostring(lib.store.relation.idvmap[r]) end)
164 + ]]
191 165 };
192 166
193 167 actor_auth_how = {
194 168 params = {rawstring, lib.store.inet}, sql = [[
195 169 with mts as (select a.kind from parsav_auth as a
196 170 left join parsav_actors as u on u.id = a.uid
197 171 where (a.uid is null or u.handle = $1::text or (
................................................................................
206 180 (select count(*) from mts where kind like 'challenge-%') > 0,
207 181 (select count(*) from mts where kind = 'trust') > 0
208 182 ]]; -- cheat
209 183 };
210 184
211 185 actor_session_fetch = {
212 186 params = {uint64, lib.store.inet, int64}, sql = [[
213 - select a.id, a.nym, a.handle, a.origin, a.bio,
214 - a.avataruri, a.rank, a.quota, a.key, a.epithet,
215 - extract(epoch from a.knownsince)::bigint,
216 - coalesce(a.handle || '@' || s.domain,
217 - '@' || a.handle) as xid,
187 + select (pg_temp.parsavpg_translate_actor(a)).*,
218 188
219 189 au.restrict,
220 190 array['post' ] <@ au.restrict,
221 191 array['edit' ] <@ au.restrict,
222 192 array['account' ] <@ au.restrict,
223 193 array['upload' ] <@ au.restrict,
224 194 array['moderate'] <@ au.restrict,
225 195 array['admin' ] <@ au.restrict
226 196
227 197 from parsav_auth au
228 198 left join parsav_actors a on au.uid = a.id
229 - left join parsav_servers s on a.origin = s.id
230 199
231 200 where au.aid = $1::bigint and au.blacklist = false and
232 201 (au.netmask is null or au.netmask >> $2::inet) and
233 202 ($3::bigint = 0 or --slightly abusing the epoch time fmt here, but
234 - ((a.authtime is null or a.authtime <= to_timestamp($3::bigint)) and
235 - (au.valperiod is null or au.valperiod <= to_timestamp($3::bigint))))
203 + ((a.authtime is null or a.authtime <= $3::bigint) and
204 + (au.valperiod is null or au.valperiod <= $3::bigint)))
236 205 ]];
237 206 };
238 207
239 208 actor_powers_fetch = {
240 209 params = {uint64}, sql = [[
241 210 select key, allow from parsav_rights where actor = $1::bigint
242 211 ]]
................................................................................
253 222 actor_power_delete = {
254 223 params = {uint64,lib.mem.ptr(int8)}, cmd = true, sql = [[
255 224 delete from parsav_rights where
256 225 actor = $1::bigint and
257 226 key = $2::text
258 227 ]]
259 228 };
229 +
230 + actor_rel_create = {
231 + params = {uint16,uint64, uint64}, cmd = true, sql = [[
232 + insert into parsav_rels (kind,relator,relatee)
233 + values($1::smallint, $2::bigint, $3::bigint)
234 + on conflict do nothing
235 + ]];
236 + };
237 +
238 + actor_rel_destroy = {
239 + params = {uint16,uint64, uint64}, cmd = true, sql = [[
240 + delete from parsav_rels where
241 + kind = $1::smallint and
242 + relator = $2::bigint and
243 + relatee = $3::bigint
244 + ]];
245 + };
246 +
247 + actor_rel_enum = {
248 + params = {uint64, uint64}, sql = [[
249 + select kind from parsav_rels where
250 + relator = $1::bigint and
251 + relatee = $2::bigint
252 + ]];
253 + };
254 +
255 + actor_notice_enum = {
256 + params = {uint64}, sql = [[
257 + select (notice).* from pg_temp.parsavpg_notices
258 + where rcpt = $1::bigint
259 + ]];
260 + };
260 261
261 262 auth_sigtime_user_fetch = {
262 263 params = {uint64}, sql = [[
263 - select extract(epoch from authtime)::bigint
264 + select authtime::bigint
264 265 from parsav_actors where id = $1::bigint
265 266 ]];
266 267 };
267 268
268 269 auth_sigtime_user_alter = {
269 270 params = {uint64,int64}, cmd = true, sql = [[
270 271 update parsav_actors set
271 - authtime = to_timestamp($2::bigint)
272 + authtime = $2::bigint
272 273 where id = $1::bigint
273 274 ]];
274 275 };
275 276
276 277 auth_create_pw = {
277 - params = {uint64, binblob, pstring}, cmd = true, sql = [[
278 - insert into parsav_auth (uid, name, kind, cred, comment) values (
278 + params = {uint64, binblob, int64, pstring}, cmd = true, sql = [[
279 + insert into parsav_auth (uid, name, kind, cred, valperiod, comment) values (
279 280 $1::bigint,
280 281 (select handle from parsav_actors where id = $1::bigint),
281 282 'pw-sha256', $2::bytea,
282 - $3::text
283 + $3::bigint, $4::text
283 284 )
284 285 ]]
285 286 };
286 287
287 288 auth_purge_type = {
288 289 params = {rawstring, uint64, rawstring}, cmd = true, sql = [[
289 290 delete from parsav_auth where
................................................................................
310 311 rawstring, rawstring, rawstring;
311 312 }, cmd = true, sql = [[
312 313 update parsav_posts set
313 314 subject = $4::text,
314 315 acl = $5::text,
315 316 body = $6::text,
316 317 chgcount = $2::integer,
317 - edited = to_timestamp($3::bigint)
318 + edited = $3::bigint
318 319 where id = $1::bigint
319 320 ]]
320 321 };
321 322
322 323 post_create = {
323 324 params = {
324 325 uint64, rawstring, rawstring, rawstring,
................................................................................
327 328 insert into parsav_posts (
328 329 author, subject, acl, body,
329 330 parent, posted, discovered,
330 331 circles, mentions, convoheaduri
331 332 ) values (
332 333 $1::bigint, case when $2::text = '' then null else $2::text end,
333 334 $3::text, $4::text,
334 - $5::bigint, to_timestamp($6::bigint), now(),
335 + $5::bigint, $6::bigint, $6::bigint,
335 336 array[]::bigint[], array[]::bigint[], $7::text
336 337 ) returning id
337 338 ]]; -- TODO array handling
338 339 };
339 340
340 341 post_destroy_prepare = {
341 342 params = {uint64}, cmd = true, sql = [[
................................................................................
349 350 params = {uint64}, cmd = true, sql = [[
350 351 delete from parsav_posts where id = $1::bigint
351 352 ]]
352 353 };
353 354
354 355 post_fetch = {
355 356 params = {uint64}, sql = [[
356 - with counts as (
357 - select a.kind, p.id as subject, count(*) as ct from parsav_acts as a
358 - inner join parsav_posts as p on p.id = a.subject
359 - group by a.kind, p.id
360 - )
361 -
362 - select a.origin is null,
363 - p.id, p.author, p.subject, p.acl, p.body,
364 - extract(epoch from p.posted )::bigint,
365 - extract(epoch from p.discovered)::bigint,
366 - extract(epoch from p.edited )::bigint,
367 - p.parent, p.convoheaduri, p.chgcount,
368 - coalesce(c.value, -1)::smallint, 0::bigint, 0::bigint,
369 - coalesce((select ct from counts where kind = 'like' and counts.subject = p.id),0)::integer,
370 - coalesce((select ct from counts where kind = 'rt' and counts.subject = p.id),0)::integer
371 -
372 - from parsav_posts as p
373 - inner join parsav_actors as a on p.author = a.id
374 - left join parsav_actor_conf_ints as c on c.uid = a.id and c.key = 'ui-accent'
375 - where p.id = $1::bigint
376 - ]];
357 + select (p.post).*
358 + from pg_temp.parsavpg_known_content as p
359 + where (p.post).id = $1::bigint and (p.post).rtdby = 0
360 + ]]
377 361 };
378 362
379 363 post_enum_parent = {
380 364 params = {uint64}, sql = [[
381 - with counts as (
382 - select a.kind, p.id as subject, count(*) as ct from parsav_acts as a
383 - inner join parsav_posts as p on p.id = a.subject
384 - group by a.kind, p.id
385 - )
386 -
387 - select a.origin is null,
388 - p.id, p.author, p.subject, p.acl, p.body,
389 - extract(epoch from p.posted )::bigint,
390 - extract(epoch from p.discovered)::bigint,
391 - extract(epoch from p.edited )::bigint,
392 - p.parent, p.convoheaduri, p.chgcount,
393 - coalesce(c.value, -1)::smallint, 0::bigint, 0::bigint,
394 - coalesce((select ct from counts where kind = 'like' and counts.subject = p.id),0)::integer,
395 - coalesce((select ct from counts where kind = 'rt' and counts.subject = p.id),0)::integer
396 -
397 - from parsav_posts as p
398 - inner join parsav_actors as a on a.id = p.author
399 - left join parsav_actor_conf_ints as c on c.uid = a.id and c.key = 'ui-accent'
400 - where p.parent = $1::bigint
401 - order by p.posted, p.discovered asc
402 - ]]
365 + select (p.post).*
366 + from pg_temp.parsavpg_known_content as p
367 + where (p.post).parent = $1::bigint and (p.post).rtdby = 0
368 + order by (p.post).posted, (p.post).discovered asc
369 + ]];
403 370 };
404 371
405 372 thread_latest_arrival_calc = {
406 373 params = {uint64}, sql = [[
407 374 with recursive posts(id) as (
408 375 select id from parsav_posts where parent = $1::bigint
409 376 union
................................................................................
414 381 maxes as (
415 382 select unnest(array[max(p.posted), max(p.discovered), max(p.edited)]) as m
416 383 from posts
417 384 inner join parsav_posts as p
418 385 on p.id = posts.id
419 386 )
420 387
421 - select extract(epoch from max(m))::bigint from maxes
388 + select max(m)::bigint from maxes
422 389 ]];
423 390 };
424 391
425 392 post_react_simple = {
426 - params = {uint64, uint64, pstring}, sql = [[
427 - insert into parsav_acts (kind,actor,subject) values (
428 - $3::text, $1::bigint, $2::bigint
393 + params = {uint64, uint64, pstring, int64}, sql = [[
394 + insert into parsav_acts (kind,actor,subject,time) values (
395 + $3::text, $1::bigint, $2::bigint, $4::bigint
429 396 ) returning id
430 397 ]];
431 398 };
432 399
433 400 post_react_cancel = {
434 401 params = {uint64, uint64, pstring}, cmd = true, sql = [[
435 402 delete from parsav_acts where
................................................................................
446 413 ($2::bigint = 0 or subject = $2::bigint) and
447 414 ($3::text is null or kind = $3::text )
448 415 ]]
449 416 };
450 417
451 418 post_enum_author_uid = {
452 419 params = {uint64,uint64,uint64,uint64, uint64}, sql = [[
453 - with ownposts as (
454 - select *, 0::bigint as rtid from parsav_posts as p
455 - where p.author = $5::bigint and
456 - ($1::bigint = 0 or p.posted <= to_timestamp($1::bigint)) and
457 - ($2::bigint = 0 or to_timestamp($2::bigint) < p.posted)
458 - ),
420 + select (c.post).*
421 + from pg_temp.parsavpg_known_content as c
459 422
460 - retweets as (
461 - select p.*, a.id as rtid from parsav_acts as a
462 - inner join parsav_posts as p on a.subject = p.id
463 - where a.actor = $5::bigint and
464 - a.kind = 'rt' and
465 - ($1::bigint = 0 or a.time <= to_timestamp($1::bigint)) and
466 - ($2::bigint = 0 or to_timestamp($2::bigint) < a.time)
467 - ),
423 + where c.promoter = $5::bigint and
424 + ($1::bigint = 0 or c.tltime <= $1::bigint) and
425 + ($2::bigint = 0 or $2::bigint < c.tltime)
426 + order by c.tltime desc
468 427
469 - allposts as (select *, 0::bigint as retweeter from ownposts
470 - union select *, $5::bigint as retweeter from retweets),
471 -
472 - counts as (
473 - select a.kind, p.id as subject, count(*) as ct from parsav_acts as a
474 - inner join parsav_posts as p on p.id = a.subject
475 - group by a.kind, p.id
476 - )
477 -
478 - select a.origin is null,
479 - p.id, p.author, p.subject, p.acl, p.body,
480 - extract(epoch from p.posted )::bigint,
481 - extract(epoch from p.discovered)::bigint,
482 - extract(epoch from p.edited )::bigint,
483 - p.parent, p.convoheaduri, p.chgcount,
484 - coalesce(c.value,-1)::smallint,
485 - p.retweeter, p.rtid,
486 - coalesce((select ct from counts where kind = 'like' and counts.subject = p.id),0)::integer,
487 - coalesce((select ct from counts where kind = 'rt' and counts.subject = p.id),0)::integer
488 - from allposts as p
489 - inner join parsav_actors as a on p.author = a.id
490 - left join parsav_actor_conf_ints as c
491 - on c.key = 'ui-accent' and
492 - c.uid = a.id
493 - order by (p.posted, p.discovered) desc
494 428 limit case when $3::bigint = 0 then null
495 429 else $3::bigint end
496 430 offset $4::bigint
497 - ]]
431 + ]];
498 432 };
499 433
500 434 -- maybe there's some way to unify these two, idk, im tired
501 435
502 436 timeline_instance_fetch = {
503 437 params = {uint64, uint64, uint64, uint64}, sql = [[
504 - with posts as (
505 - select true,
506 - p.id, p.author, p.subject, p.acl, p.body,
507 - extract(epoch from p.posted )::bigint,
508 - extract(epoch from p.discovered)::bigint,
509 - extract(epoch from p.edited )::bigint,
510 - p.parent, null::text, p.chgcount,
511 - coalesce(c.value, -1)::smallint, 0::bigint, 0::bigint
512 -
513 - from parsav_posts as p
514 - inner join parsav_actors as a on p.author = a.id
515 - left join parsav_actor_conf_ints as c on c.uid = a.id and c.key = 'ui-accent'
516 - where
517 - ($1::bigint = 0 or p.posted <= to_timestamp($1::bigint)) and
518 - ($2::bigint = 0 or to_timestamp($2::bigint) < p.posted) and
519 - (a.origin is null)
520 - order by (p.posted, p.discovered) desc
521 - limit case when $3::bigint = 0 then null
522 - else $3::bigint end
523 - offset $4::bigint
524 - ), counts as (
525 - select a.kind, p.id as subject, count(*) as ct from parsav_acts as a
526 - inner join parsav_posts as p on p.id = a.subject
527 - group by a.kind, p.id
438 + select (c.post).*
439 + from pg_temp.parsavpg_known_content as c
440 +
441 + where (c.post).localpost = true and
442 + ($1::bigint = 0 or c.tltime <= $1::bigint) and
443 + ($2::bigint = 0 or $2::bigint < c.tltime)
444 + order by c.tltime desc
445 +
446 + limit case when $3::bigint = 0 then null
447 + else $3::bigint end
448 + offset $4::bigint
449 + ]];
450 + };
451 +
452 + timeline_actor_fetch = {
453 + params = {uint64, uint64, uint64, uint64, uint64}, sql = [[
454 + with followed as (
455 + select relatee from parsav_rels where
456 + kind = <rel:follow> and
457 + relator = $1::bigint
458 + ), avoided as (
459 + select relatee as avoidee from parsav_rels where
460 + kind = <rel:avoid> or kind = <rel:mute> and
461 + relator = $1::bigint
462 + union select relator as avoidee from parsav_rels where
463 + kind = <rel:exclude> and
464 + relatee = $1::bigint
528 465 )
529 466
530 - select *,
531 - coalesce((select ct from counts as c where kind = 'like' and c.subject = posts.id),0)::integer,
532 - coalesce((select ct from counts as c where kind = 'rt' and c.subject = posts.id),0)::integer
533 - from posts
534 - ]]
467 + select (c.post).*
468 + from pg_temp.parsavpg_known_content as c
469 +
470 + where ($2::bigint = 0 or c.tltime <= $2::bigint) and
471 + ($3::bigint = 0 or $3::bigint < c.tltime) and
472 + (c.promoter in (table followed) or
473 + c.promoter = $1::bigint) and
474 + not ((c.post).author in (table avoided))
475 + order by c.tltime desc
476 +
477 + limit case when $4::bigint = 0 then null
478 + else $4::bigint end
479 + offset $5::bigint
480 + ]];
535 481 };
536 482
537 483 artifact_instantiate = {
538 484 params = {binblob, binblob, pstring}, sql = [[
539 485 insert into parsav_artifacts (content,hash,mime) values (
540 486 $1::bytea, $2::bytea, $3::text
541 487 ) on conflict do nothing returning id
................................................................................
744 690 buf[2] = tycode
745 691 buf[3] = sz
746 692 for j=0,sz do buf[4 + j] = i.v6[j] end -- 😬
747 693 return buf
748 694 end
749 695 end;
750 696 }
697 +
698 +local sqlvars = {}
699 +for i, n in ipairs(lib.store.noticetype.members) do
700 + sqlvars['notice:' .. n] = lib.store.noticetype[n]
701 +end
702 +
703 +for i, n in ipairs(lib.store.relation.members) do
704 + sqlvars['rel:' .. n] = lib.store.relation.idvmap[n]
705 +end
751 706
752 707 local con = symbol(&lib.pq.PGconn)
753 -local prep = {}
754 708 local function sqlsquash(s) return s
755 709 :gsub('%%include (.-)%%',function(f)
756 710 return sqlsquash(lib.util.ingest('backend/schema/' .. f))
757 711 end) -- include dependencies
758 712 :gsub('%-%-.-\n','') -- remove disruptive line comments
759 713 :gsub('%-%-.-$','') -- remove unnecessary terminal comments
714 + :gsub('<(%g-)>',function(r) return tostring(sqlvars[r]) end)
760 715 :gsub('%s+',' ') -- remove whitespace
761 716 :gsub('^%s*(.-)%s*$','%1') -- chomp
762 717 end
718 +
719 +-- to simplify queries and reduce development headaches in general, we
720 +-- offload as much logic as possible into views. to avoid versioning
721 +-- difficulties, these views are not part of the schema, but are rather
722 +-- uploaded to the database at the start of a parsav connection, visible
723 +-- only to the connecting parsav instance, stored in memory, and dropped
724 +-- as soon as the connection session ends.
725 +
726 +local tempviews = sqlsquash(lib.util.ingest 'backend/schema/pgsql-views.sql')
727 +local prep = { quote
728 + var res = lib.pq.PQexec([con], tempviews)
729 + if lib.pq.PQresultStatus(res) == lib.pq.PGRES_COMMAND_OK then
730 + lib.dbg('uploading pgsql session views')
731 + else
732 + lib.bail('backend pgsql - failed to upload session views: \n', lib.pq.PQresultErrorMessage(res))
733 + end
734 +end }
763 735
764 736 for k,q in pairs(queries) do
765 737 local qt = sqlsquash(q.sql)
766 738 local stmt = 'parsavpg_' .. k
767 739 terra q.prep([con])
768 740 var res = lib.pq.PQprepare([con], stmt, qt, [#q.params], nil)
769 741 defer lib.pq.PQclear(res)
................................................................................
979 951 lib.dbg(['searching for hashed password credentials in format SHA' .. tostring(hash)])
980 952 var [out]
981 953 [vdrs]
982 954 lib.dbg(['could not find password hash'])
983 955 end
984 956 end
985 957
986 -local schema = sqlsquash(lib.util.ingest('backend/schema/pgsql.sql'))
987 -local obliterator = sqlsquash(lib.util.ingest('backend/schema/pgsql-drop.sql'))
958 +local schema = sqlsquash(lib.util.ingest 'backend/schema/pgsql.sql')
959 +local obliterator = sqlsquash(lib.util.ingest 'backend/schema/pgsql-drop.sql')
988 960
989 961 local privupdate = terra(
990 962 src: &lib.store.source,
991 963 ac: &lib.store.actor
992 964 ): {}
993 965 var pdef: lib.store.powerset pdef:clear()
994 966 var map = array([privmap])
................................................................................
1081 1053 return con
1082 1054 end];
1083 1055
1084 1056 close = [terra(src: &lib.store.source) lib.pq.PQfinish([&lib.pq.PGconn](src.handle)) end];
1085 1057
1086 1058 tx_enter = txdo, tx_complete = txdone;
1087 1059
1088 - conprep = [terra(src: &lib.store.source, mode: lib.store.prepmode.t)
1060 + conprep = [terra(src: &lib.store.source, mode: lib.store.prepmode.t): {}
1089 1061 var [con] = [&lib.pq.PGconn](src.handle)
1090 1062 if mode == lib.store.prepmode.full then [prep]
1091 1063 elseif mode == lib.store.prepmode.conf or
1092 1064 mode == lib.store.prepmode.admin then
1093 1065 queries.conf_get.prep(con)
1094 1066 queries.conf_set.prep(con)
1095 1067 queries.conf_reset.prep(con)
1096 1068 if mode == lib.store.prepmode.admin then
1097 1069 end
1098 1070 else lib.bail('unsupported connection preparation mode') end
1099 1071 end];
1100 1072
1101 - dbsetup = [terra(src: &lib.store.source)
1073 + dbsetup = [terra(src: &lib.store.source): bool
1102 1074 var res = lib.pq.PQexec([&lib.pq.PGconn](src.handle), schema)
1103 1075 if lib.pq.PQresultStatus(res) == lib.pq.PGRES_COMMAND_OK then
1104 1076 lib.report('successfully instantiated schema in database')
1105 1077 return true
1106 1078 else
1107 1079 lib.warn('backend pgsql - failed to initialize database: \n', lib.pq.PQresultErrorMessage(res))
1108 1080 return false
1109 1081 end
1110 1082 end];
1111 1083
1112 - obliterate_everything = [terra(src: &lib.store.source)
1084 + obliterate_everything = [terra(src: &lib.store.source): bool
1113 1085 var res = lib.pq.PQexec([&lib.pq.PGconn](src.handle), obliterator)
1114 1086 if lib.pq.PQresultStatus(res) == lib.pq.PGRES_COMMAND_OK then
1115 1087 lib.report('successfully wiped out everything parsav-related in database')
1116 1088 return true
1117 1089 else
1118 1090 lib.warn('backend pgsql - failed to obliterate database: \n', lib.pq.PQresultErrorMessage(res))
1119 1091 return false
................................................................................
1242 1214
1243 1215 var a = row_to_actor(&r, 0)
1244 1216 a.ptr.source = src
1245 1217
1246 1218 var au = [lib.stat(lib.store.auth)] { ok = true }
1247 1219 au.val.aid = aid
1248 1220 au.val.uid = a.ptr.id
1249 - if not r:null(0,14) then -- restricted?
1221 + if not r:null(0,13) then -- restricted?
1250 1222 au.val.privs:clear()
1251 - (au.val.privs.post << r:bool(0,15))
1252 - (au.val.privs.edit << r:bool(0,16))
1253 - (au.val.privs.account << r:bool(0,17))
1254 - (au.val.privs.upload << r:bool(0,18))
1255 - (au.val.privs.moderate<< r:bool(0,19))
1256 - (au.val.privs.admin << r:bool(0,20))
1223 + (au.val.privs.post << r:bool(0,14))
1224 + (au.val.privs.edit << r:bool(0,15))
1225 + (au.val.privs.account << r:bool(0,16))
1226 + (au.val.privs.upload << r:bool(0,17))
1227 + (au.val.privs.moderate<< r:bool(0,18))
1228 + (au.val.privs.admin << r:bool(0,19))
1257 1229 else au.val.privs:fill() end
1258 1230
1259 1231 return au, a
1260 1232 end
1261 1233
1262 1234 ::fail:: return [lib.stat (lib.store.auth) ] { ok = false },
1263 1235 [lib.mem.ptr(lib.store.actor)] { ptr = nil, ct = 0 }
................................................................................
1301 1273
1302 1274 post_retweet = [terra(
1303 1275 src: &lib.store.source,
1304 1276 uid: uint64,
1305 1277 post: uint64,
1306 1278 undo: bool
1307 1279 ): {}
1280 + var time = lib.osclock.time(nil)
1308 1281 if not undo then
1309 - queries.post_react_simple.exec(src,uid,post,"rt")
1282 + queries.post_react_simple.exec(src,uid,post,"rt",time)
1310 1283 else
1311 1284 queries.post_react_cancel.exec(src,uid,post,"rt")
1312 1285 end
1313 1286 end];
1314 1287 post_like = [terra(
1315 1288 src: &lib.store.source,
1316 1289 uid: uint64,
1317 1290 post: uint64,
1318 1291 undo: bool
1319 1292 ): {}
1293 + var time = lib.osclock.time(nil)
1320 1294 if not undo then
1321 - queries.post_react_simple.exec(src,uid,post,"like")
1295 + queries.post_react_simple.exec(src,uid,post,"like",time)
1322 1296 else
1323 1297 queries.post_react_cancel.exec(src,uid,post,"like")
1324 1298 end
1325 1299 end];
1326 1300 post_liked_uid = [terra(
1327 1301 src: &lib.store.source,
1328 1302 uid: uint64,
................................................................................
1391 1365 -- check against default rights, insert records for wherever powers differ
1392 1366 lib.dbg('created new actor, establishing powers')
1393 1367 privupdate(src,ac)
1394 1368
1395 1369 lib.dbg('powers established')
1396 1370 return ac.id
1397 1371 end];
1372 +
1373 + actor_rel_create = [terra(
1374 + src: &lib.store.source,
1375 + kind: uint16,
1376 + relator: uint64,
1377 + relatee: uint64
1378 + ): {} queries.actor_rel_create.exec(src,kind,relator,relatee) end];
1379 +
1380 + actor_rel_destroy = [terra(
1381 + src: &lib.store.source,
1382 + kind: uint16,
1383 + relator: uint64,
1384 + relatee: uint64
1385 + ): {} queries.actor_rel_destroy.exec(src,kind,relator,relatee) end];
1386 +
1387 + actor_rel_calc = [terra(
1388 + src: &lib.store.source,
1389 + relator: uint64,
1390 + relatee: uint64
1391 + ): lib.store.relationship
1392 + var r = lib.store.relationship {
1393 + agent = relator, patient = relatee
1394 + } r.rel:clear()
1395 + r.recip:clear()
1396 +
1397 + var res = queries.actor_rel_enum.exec(src,relator,relatee)
1398 + var recip = queries.actor_rel_enum.exec(src,relatee,relator)
1399 +
1400 + if res.sz > 0 then defer res:free()
1401 + for i = 0, res.sz do
1402 + var bit = res:int(uint16, i, 0)-1
1403 + if bit < [#lib.store.relation.members] then r.rel:setbit(bit, true)
1404 + else lib.warn('unknown relationship type in database') end
1405 + end
1406 + end
1407 +
1408 + if recip.sz > 0 then defer recip:free()
1409 + for i = 0, recip.sz do
1410 + var bit = recip:int(uint16, i, 0)-1
1411 + if bit < [#lib.store.relation.members] then r.recip:setbit(bit, true)
1412 + else lib.warn('unknown relationship type in database') end
1413 + end
1414 + end
1415 +
1416 + return r
1417 + end];
1398 1418
1399 1419 actor_purge_uid = [terra(
1400 1420 src: &lib.store.source,
1401 1421 uid: uint64
1402 1422 ) queries.actor_purge_uid.exec(src,uid) end];
1403 1423
1404 1424 auth_enum_uid = [terra(
................................................................................
1432 1452 ): {}
1433 1453 var hash: uint8[lib.crypt.algsz.sha256]
1434 1454 if lib.md.mbedtls_md(lib.md.mbedtls_md_info_from_type(lib.crypt.alg.sha256.id),
1435 1455 [&uint8](pw.ptr), pw.ct, &hash[0]) ~= 0 then
1436 1456 lib.bail('cannot hash password')
1437 1457 end
1438 1458 if reset then queries.auth_purge_type.exec(src, nil, uid, 'pw-%') end
1439 - queries.auth_create_pw.exec(src, uid, binblob {ptr = &hash[0], ct = [hash.type.N]}, comment)
1459 + queries.auth_create_pw.exec(src, uid, binblob {ptr = &hash[0], ct = [hash.type.N]}, lib.osclock.time(nil), comment)
1440 1460 end];
1441 1461
1442 1462 auth_purge_pw = [terra(src: &lib.store.source, uid: uint64, handle: rawstring): {}
1443 1463 queries.auth_purge_type.exec(src, handle, uid, 'pw-%')
1444 1464 end];
1445 1465
1446 1466 auth_purge_otp = [terra(src: &lib.store.source, uid: uint64, handle: rawstring): {}