Move SQL views and joins to Diesel.

[dessalines]

Up till now, we've used SQL views to give a representation of extra (joined data) that the front end needs. For example, the community table has a creator_id, but we need to join to the user_ table to get the community creator's name. So CommunityView has extra columns for creator_actor_id, creator_name, etc.

Negatives of SQL views

• Database changes / migrations are a pain: every column change requires rebuilding any dependent views, such that adding a column to the post table, requires many dependent views to be built from scratch.
  • We have to manually re-create the view tables in rust code, to match the database, but there's no guarantee the columns, or their order, exactly match, except through trial and error.
• Diesel doesn't handle very large numbers of columns (> 32, which we have already) Remove 64-column-tables feature from Diesel #805
• Flat rust objects, with extra fields having to be manually defined to exactly match the SQL join migrations.

CommunityView: {
  creator_id: 1
  creator_name:..
}

I initially designed Lemmy this way because I'm generally afraid of relying too heavily on a single ORM: I've seen them superceded in the past, and when using views, you always have the possiblity that your ORM could be switched out at a later time with little effort. But Diesel is pretty-much the de-facto Rust ORM, and is mature and well contributed enough, that I don't see any reason we shouldn't switch to using its join features.

Positives of Switching to Diesel joins

• Moves SQL logic (unchecked, hard to debug), to rust (compile time checked).
• Possibly return hierarchical data:

CommunityObj {
  creator: {
    id,
    name,
  },
  id: ...

• No more painful migrations. Upcoming changes like adding private communities, user / community blocking, will be much easier as a result.

Some things to consider

• Lemmy uses "fast tables", which are cached versions of the views, where inserts to the primary tables fire off table triggers to update the fast tables. These fast tables should be slimmed down as @masterstur suggested, and only contain the hard to calculate aggregate data (things like post_score, post_comment_count, etc). Then these fast tables can be joined back to the primary tables.
  • The triggers on these fast tables can be optimized in the process.

[dessalines]

Checklist:

• Move all views to diesel.
• Remove old views, fast tables, triggers, etc.
• Get unit tests working
• Make a v2 API
• Go through API again, clean it up.
• Make a v2 branch for lemmy-js-client
• Get integration tests working
• Double check websockets
• Upgrade lemmy-ui to use the new API
• Fix User mentions and private messages
• Do before and after postgres performance comparisons, using tatiyants and the query tester
  • Make sure hot_rank is indexed.
  • Make sure stickied and post.published is duped on post_aggregates.
• Make sure all counts are correct: post counts, comment counts, scores, etc.
• Update API docs

[revmischa]

Not sure if this is helpful but instead of creating fast tables consider materialized views for caches of SQL views