Omit local instance from federated instances list (#3712)

[lemmy.git] / crates / db_schema / src / impls / instance.rs

diff --git a/crates/db_schema/src/impls/instance.rs b/crates/db_schema/src/impls/instance.rs
index 068e317fc8e37600d20dc6afd549b72596e53a95..f92d261b544c9910a63cf5839aad9c6bf3bbaaa7 100644 (file)
--- a/crates/db_schema/src/impls/instance.rs
+++ b/crates/db_schema/src/impls/instance.rs
@@ -1,10 +1,17 @@
 use crate::{
+  diesel::dsl::IntervalDsl,
   newtypes::InstanceId,
-  schema::{federation_allowlist, federation_blocklist, instance},
+  schema::{federation_allowlist, federation_blocklist, instance, local_site, site},
   source::instance::{Instance, InstanceForm},
   utils::{get_conn, naive_now, DbPool},
 };
-use diesel::{dsl::insert_into, result::Error, ExpressionMethods, QueryDsl};
+use diesel::{
+  dsl::{insert_into, now},
+  result::Error,
+  sql_types::{Nullable, Timestamp},
+  ExpressionMethods,
+  QueryDsl,
+};
 use diesel_async::RunQueryDsl;
 
 impl Instance {
@@ -46,6 +53,24 @@ impl Instance {
       .execute(conn)
       .await
   }
+
+  pub async fn read_all(pool: &mut DbPool<'_>) -> Result<Vec<Instance>, Error> {
+    let conn = &mut get_conn(pool).await?;
+    instance::table
+      .select(instance::all_columns)
+      .get_results(conn)
+      .await
+  }
+
+  pub async fn dead_instances(pool: &mut DbPool<'_>) -> Result<Vec<String>, Error> {
+    let conn = &mut get_conn(pool).await?;
+    instance::table
+      .select(instance::domain)
+      .filter(coalesce(instance::updated, instance::published).lt(now - 3.days()))
+      .get_results(conn)
+      .await
+  }
+
   #[cfg(test)]
   pub async fn delete_all(pool: &mut DbPool<'_>) -> Result<usize, Error> {
     let conn = &mut get_conn(pool).await?;
@@ -72,6 +97,10 @@ impl Instance {
   pub async fn linked(pool: &mut DbPool<'_>) -> Result<Vec<Self>, Error> {
     let conn = &mut get_conn(pool).await?;
     instance::table
+      // omit instance representing the local site
+      .left_join(site::table.inner_join(local_site::table))
+      .filter(local_site::id.is_null())
+      // omit instances in the blocklist
       .left_join(federation_blocklist::table)
       .filter(federation_blocklist::id.is_null())
       .select(instance::all_columns)
@@ -79,3 +108,5 @@ impl Instance {
       .await
   }
 }
+
+sql_function! { fn coalesce(x: Nullable<Timestamp>, y: Timestamp) -> Timestamp; }