Lemmy-helper as prototype of Lemmy to Lemmy replication of posts/comments by community - back-filling content

[RocketDerp]

The command-line interface (CLI) of lemmy-helper is already evolving into a prototype of back-fill for Community posts and comments within Lemmy. I'd like to see the main Lemmy rust add some API for more raw query of the Posts/Comments focused on the internal database record id and published timestamps....

[RocketDerp]

Ok, taking a stab at a URGENT Drastically needed to cope with federation performance scaling problems, API implementation for Lemmy to Lemmy replication.

/api/syncshare/comments
/api/syncshare/posts
/api/syncshare/communities
/api/syncshare/users

This focuses on the internals of Lemmy database structure for:

1. Performance. Avoid SQL JOIN. Polling of data, SQL SELECT transactions with minimal overhead.
2. Consistency in detecting new or edited content

Parameters to these API:

dbid - integer database ID key, typically used with > or < than opearation. The site already exposes these raw ID numbers for http://lemmy.ml/post/xxxx to the public, so go forward with that established precedence.
published timestamp, typically used for a > than or range of > and <.
updated timestamp, typically used for > than to get edits to comments/posts
outputFormat indicate if you want minimal or full content. minimal being a list of key record identifiers to validate sync awareness. Full content being enough to reconstruct the comment/post locally.
orderby, minimal of choices. published timestamp, update timestamp, dbid, ap_id

api should return a count of records and indicate if the limit is reached. Avoid edge cases where you ask for 300 and get exactly 300 and do not know if you should query for another page.

Keep it simple, sally, KISS

• when SELECT data to respond to API call, no concept of pinned posts for community or system, this is focused on getting new data, not presenting data.
• no concept of user-specific filtering such as language, blocked persons, NSFW

[RocketDerp]

the Lemmy comments table is an interesting problem. Comments are tied to a post, but with current 0.18.1 era federation - I've raised issues about the timing of that.. Such as, what happens when a post fails to replicate between peer servers and comments do come in (timing issue between sender HTTP retries and sequence).

The SQL comment table does not have any database field to indicate if it is a local or remote comment. (it does not indicate which server is 'home' to the comment, more accurate) Nor does the SQL comment table have any indication of which community a comment belongs to. Nevertheless, the lemmy-ui standard is to be able to llink database id of a comment directly: http://lemmy.ml/comment/xxxx - although I think these URL's should use a cleaned version of the api_id that the comment table does.

So query of comments by community and by origin-of-comment (local vs duplicate of a federated comment from another server) is an issue to discuss.

[XtremeOwnageDotCom]

The SQL comment table does not have any database field to indicate if it is a local or remote comment.

select 
	c.id as comment_id
	, p.id as post_id
	, p.local as post_local
	, cr.local as person_local
FROM comment c
JOIN post p on p.id = c.post_id
JOIN person cr on cr.id = c.creator_id
LIMIT 100;

I do believe, if the person who created the comment is local, then it would be considered a local comment.

[RocketDerp]

Ok, that's another perspective on the origin of comment, not the posting (tied to community tied to instance), but the person commenting. More direct.

My perspective is Reddit 12 years ago, where a user could delete their account (person gone), but comment is still tied to posting.

[RocketDerp]

4 days later, I'm revisiting this. Because studying pg_stat_statements, I did notice yes, there is a 'local' flag on comments in Lemmy. But I think what would be ideal going forward is to know which instance federated (delivered) the comment into the database, so that queries such as "all comments delivered by remote server x" can be easily identified.

my queries that pull comments and study ap_id I'm speculating give misleading results because it's entirely possible a user on midwest.social comments on a community homed by lemmy.world which then federate delivers it to my local instance. I haven't verified this, but I assume the ap_id is identical on all 3 servers (indicating midwest.socail) involved in such a comment.

I have concerns that there are peer to peer problems lurking under the surface, such as race conditions where due to how federation code retries sending, a comment may actually come into the system before a posting - I described my concern more detailed here: https://lemmy.ml/comment/1062436

[RocketDerp]

/api/syncshare/comments
/api/syncshare/posts
/api/syncshare/communities
/api/syncshare/users

What to do about votes. A lot of logic in Lemmy about votes isn't that different from "saves", it is about showing an end-user in the user interlace which posts/comments they have already saved or voted on. A peer server doesn't have that user logging-in to read their own saved and voted posts/comments. So that granular of data isn't needed.