~ knotless Search/
↑↓ move↵ open⇧↵ all results

grain.social/grain
Switch repository
Loading…

photography-focused photo sharing platform

grain.social
1 collaborator
0
2

grain-serve serves the gallery feeds, named by URI

Merged chadtmiller.com asked to merge into feature/rust from grain.social/grain:read/feeds

Only what this pull adds. It is built on grain runs on a Rust server modelled on knotless, without hatk.

crates/grain-serve/src/index/tests.rs+17−59
View file
@@ -448,72 +448,30 @@ fn migrations() {
}
#[test]
fn the_feeds_read_by_index() {
fn the_queries_still_to_port_read_by_index() {
let index = Index::memory().unwrap();
let shown = schema::shown("g.did");
let unhidden = schema::unhidden("g.uri");
let pooled = "EXISTS (SELECT 1 FROM gallery_item gi WHERE gi.gallery = g.uri)";
let unblocked = "g.did NOT IN (SELECT subject FROM block WHERE did = ?1)
AND g.did NOT IN (SELECT did FROM block WHERE subject = ?1)
AND g.did NOT IN (SELECT subject FROM mute WHERE did = ?1)";
let feed = |filter: &str| {
format!(
"SELECT g.uri FROM gallery g WHERE {shown} AND {unhidden} AND {pooled} AND {unblocked}
AND {filter} ORDER BY g.sort_at DESC LIMIT 30"
)
};
// The feeds' and the views' own queries are checked where they are
// written, by `read::tests::the_reads_read_by_index`; these are the
// ones of what is still to be ported, as they will read.
let queries = [
// recent
feed("g.sort_at < ?2"),
// following
feed("g.did IN (SELECT subject FROM follow WHERE did = ?1)"),
// actor
format!("SELECT g.uri FROM gallery g WHERE g.did = ?1 AND {unhidden} AND {pooled} ORDER BY g.created_at DESC LIMIT 30"),
// camera
"SELECT DISTINCT make || ' ' || model FROM photo_exif WHERE make IS NOT NULL AND model IS NOT NULL".into(),
feed("EXISTS (SELECT 1 FROM gallery_item i JOIN photo_exif e ON e.photo = i.item WHERE i.gallery = g.uri AND e.make || ' ' || e.model IN (?3))"),
// hashtag
feed("(g.description LIKE ?2 OR EXISTS (SELECT 1 FROM comment c WHERE c.subject = g.uri AND c.text LIKE ?2))"),
// location
format!("SELECT g.uri FROM gallery g WHERE g.h3 = ?1 AND {shown} ORDER BY g.sort_at DESC LIMIT 30"),
// group, and groups
format!("SELECT g.uri FROM group_item p JOIN gallery g ON g.uri = p.gallery WHERE p.did = ?1 AND {shown} AND {unhidden} ORDER BY p.created_at DESC LIMIT 30"),
format!("SELECT g.uri FROM group_item p JOIN gallery g ON g.uri = p.gallery WHERE {shown} AND {unhidden} ORDER BY p.created_at DESC LIMIT 30"),
// for you: the viewer's favorites, who else favorited them, and how much
"SELECT subject, created_at FROM favorite WHERE did = ?1 ORDER BY created_at DESC LIMIT 500".into(),
"SELECT f.did, f.subject FROM favorite f WHERE f.subject IN (SELECT value FROM json_each(?1)) AND f.did != ?2".into(),
"SELECT f.did, count(*) FROM favorite f WHERE f.did IN (SELECT value FROM json_each(?1)) GROUP BY f.did".into(),
"SELECT f.subject, count(*) FROM favorite f WHERE f.subject IN (SELECT value FROM json_each(?1)) GROUP BY f.subject".into(),
// hydration
"SELECT * FROM gallery_item i WHERE i.gallery IN (SELECT value FROM json_each(?1)) ORDER BY i.position".into(),
"SELECT * FROM photo_exif e WHERE e.photo IN (SELECT value FROM json_each(?1))".into(),
"SELECT * FROM photo p WHERE p.uri IN (SELECT value FROM json_each(?1))".into(),
"SELECT val FROM self_label s WHERE s.uri IN (SELECT value FROM json_each(?1))".into(),
"SELECT f.subject FROM favorite f WHERE f.did = ?1 AND f.subject IN (SELECT value FROM json_each(?2))".into(),
"SELECT c.subject, count(*) FROM comment c WHERE c.subject IN (SELECT value FROM json_each(?1)) GROUP BY c.subject".into(),
"SELECT p.uri FROM bsky_post p WHERE p.did = ?1 AND p.text LIKE ?2 LIMIT 1".into(),
"SELECT did FROM repo r WHERE r.handle = ?1".into(),
"SELECT f.subject FROM bsky_follow f JOIN profile p ON p.did = f.subject WHERE f.did = ?1 LIMIT 10".into(),
"SELECT f.did FROM follow f WHERE f.subject = ?1 ORDER BY f.did LIMIT 50".into(),
"SELECT s.uri FROM story s WHERE s.created_at > ?1 AND s.did = ?2".into(),
"SELECT s.did FROM story s WHERE s.created_at > ?1".into(),
"SELECT f.subject FROM bsky_follow f JOIN profile p ON p.did = f.subject WHERE f.did = ?1 LIMIT 10",
"SELECT f.did FROM follow f WHERE f.subject = ?1 ORDER BY f.did LIMIT 50",
"SELECT count(*) FROM group_submission s WHERE s.group_did = ?1
AND NOT EXISTS (SELECT 1 FROM group_item i WHERE i.did = s.group_did AND i.gallery = s.gallery)
AND NOT EXISTS (SELECT 1 FROM group_decline d WHERE d.did = s.group_did AND d.submission = s.uri)".into(),
"SELECT uri FROM group_declaration d WHERE d.did = ?1".into(),
"SELECT * FROM germ_declaration d WHERE d.did = ?1".into(),
"SELECT * FROM space_invite v WHERE v.member_did = ?1 ORDER BY v.created_at DESC".into(),
"SELECT * FROM report r WHERE r.status = ?1 ORDER BY r.created_at DESC LIMIT 50".into(),
"SELECT * FROM label l WHERE l.src = ?1 AND l.id > ?2 ORDER BY l.id LIMIT 100".into(),
"DELETE FROM story_view WHERE created_at < ?1".into(),
"SELECT did FROM push_token t WHERE t.token = ?1".into(),
AND NOT EXISTS (SELECT 1 FROM group_decline d WHERE d.did = s.group_did AND d.submission = s.uri)",
"SELECT uri FROM group_declaration d WHERE d.did = ?1",
"SELECT * FROM space_invite v WHERE v.member_did = ?1 ORDER BY v.created_at DESC",
"SELECT * FROM report r WHERE r.status = ?1 ORDER BY r.created_at DESC LIMIT 50",
"SELECT * FROM label l WHERE l.src = ?1 AND l.id > ?2 ORDER BY l.id LIMIT 100",
"DELETE FROM story_view WHERE created_at < ?1",
"SELECT did FROM push_token t WHERE t.token = ?1",
// backfill's queue, and the revisions it holds records to
"SELECT did FROM repo r WHERE r.state = 'pending' AND r.retry_after <= ?1
AND r.host_status IS NULL ORDER BY r.retry_after LIMIT ?2".into(),
AND r.host_status IS NULL ORDER BY r.retry_after LIMIT ?2",
"SELECT t.uri FROM gallery t LEFT JOIN record_rev r ON r.uri = t.uri
WHERE t.did = ?1 AND (r.rev IS NULL OR r.rev < ?2)".into(),
"DELETE FROM record_rev WHERE uri >= ?1 AND uri < ?2 AND gone = 1 AND rev <= ?3".into(),
"DELETE FROM record_rev WHERE gone = 1 AND rev < ?1".into(),
WHERE t.did = ?1 AND (r.rev IS NULL OR r.rev < ?2)",
"DELETE FROM record_rev WHERE uri >= ?1 AND uri < ?2 AND gone = 1 AND rev <= ?3",
"DELETE FROM record_rev WHERE gone = 1 AND rev < ?1",
];
index.read(|db| {
sized(db);
crates/grain-serve/src/read/camera.rsnew+225−0
View file
@@ -0,0 +1,225 @@
//! The Camera feed, and cameras as grain names them: a camera's make and
//! model, as EXIF writes them, read as `web/src/lib/utils/cameraName.ts`
//! reads them, so that the raw string and the name a card shows both
//! find the same galleries.
use super::feed::{empty, filtered, listed_param};
use super::{Params, Reader};
use crate::xrpc::Failure;
use serde_json::Value;
/// Every make and model the index holds, as EXIF writes them.
pub const CAMERAS_SQL: &str = "SELECT DISTINCT make || ' ' || model FROM photo_exif
WHERE make IS NOT NULL AND model IS NOT NULL";
/// What a gallery in the feed has: a photo taken with one of `?5`.
pub const FILTER: &str =
"EXISTS (SELECT 1 FROM gallery_item i JOIN photo_exif e ON e.photo = i.item
WHERE i.gallery = g.uri AND e.make || ' ' || e.model IN (SELECT value FROM json_each(?5)))";
/// Camera: the galleries with a photo taken with the camera `camera`
/// names, by its raw make and model or its cleaned name.
pub fn feed(r: &Reader, p: &Params) -> Result<Value, Failure> {
let Some(camera) = p.get("camera") else {
return Ok(empty());
};
let target = clean(camera);
let raws: Vec<String> = r
.all::<(String,)>(CAMERAS_SQL, [])?
.into_iter()
.map(|(raw,)| raw)
.filter(|raw| clean(raw) == target)
.collect();
if raws.is_empty() {
return Ok(empty());
}
filtered(r, p, FILTER, vec![listed_param(&raws)])
}
/// A step of a pattern [`RULES`] match.
#[derive(Clone, Copy)]
enum Step {
/// These letters, in either case.
Word(&'static str),
/// This character, or none.
Maybe(char),
/// Any run of white space, or none.
Space,
}
use Step::{Maybe, Space, Word};
/// What `cleanCameraName` strips from a maker's name, in order: each a
/// pattern that starts at a word's start, whether it must end at a
/// word's end, whether its letters match in either case, and what it
/// leaves in its place.
const RULES: &[(&[Step], bool, bool, &str)] = &[
(
&[
Word("IMAGING COMPANY"),
Maybe(','),
Space,
Word("LTD"),
Maybe('.'),
],
false,
true,
"",
),
(&[Word("IMAGING CORP"), Maybe('.')], false, true, ""),
(
&[
Word("PHOTO FILM CO."),
Maybe(','),
Space,
Word("LTD"),
Maybe('.'),
],
false,
true,
"PHOTO FILM",
),
(&[Word("CAMERA AG")], true, true, ""),
(&[Word("CORPORATION")], true, true, ""),
(
&[Word("CO."), Maybe(','), Space, Word("LTD"), Maybe('.')],
false,
true,
"",
),
(&[Word("GmbH")], true, false, ""),
];
fn word_char(c: u8) -> bool {
c.is_ascii_alphanumeric() || c == b'_'
}
/// Where `steps` matching at `at` in `s` ends, if they do.
fn matches(s: &[u8], at: usize, steps: &[Step], either_case: bool) -> Option<usize> {
let mut i = at;
for step in steps {
match *step {
Word(w) => {
let w = w.as_bytes();
let got = s.get(i..i + w.len())?;
let same = match either_case {
true => got.eq_ignore_ascii_case(w),
false => got == w,
};
if !same {
return None;
}
i += w.len();
}
Maybe(c) => {
if s.get(i) == Some(&(c as u8)) {
i += 1;
}
}
Space => {
while s.get(i).is_some_and(u8::is_ascii_whitespace) {
i += 1;
}
}
}
}
Some(i)
}
/// `raw` with every match of `steps` replaced by `with`.
fn strip(raw: &str, steps: &[Step], ends: bool, either_case: bool, with: &str) -> String {
let s = raw.as_bytes();
let mut out = String::with_capacity(raw.len());
let mut i = 0;
let mut kept = 0;
while i < s.len() {
let starts = i == 0 || !word_char(s[i - 1]);
let end = starts
.then(|| matches(s, i, steps, either_case))
.flatten()
.filter(|&e| !ends || e == s.len() || !word_char(s[e]));
match end {
Some(e) => {
out.push_str(&raw[kept..i]);
out.push_str(with);
i = e;
kept = e;
}
None => i += 1,
}
}
out.push_str(&raw[kept..]);
out
}
/// A camera's make and model as a card names it: the maker's company
/// words dropped, a word said twice running said once, and a maker's
/// name in capitals or in lower case written as a name.
pub fn clean(raw: &str) -> String {
let mut s = raw.to_string();
for (steps, ends, either_case, with) in RULES {
s = strip(&s, steps, *ends, *either_case, with);
}
let mut words: Vec<&str> = Vec::new();
for w in s.split_whitespace() {
if words
.last()
.is_none_or(|last| last.to_uppercase() != w.to_uppercase())
{
words.push(w);
}
}
words
.into_iter()
.map(|w| {
let upper = w.len() >= 4 && w.bytes().all(|c| c.is_ascii_uppercase());
let lower = w.len() >= 2 && w.bytes().all(|c| c.is_ascii_lowercase());
match (upper, lower) {
(true, _) => format!("{}{}", &w[..1], w[1..].to_ascii_lowercase()),
(_, true) => format!("{}{}", w[..1].to_ascii_uppercase(), &w[1..]),
_ => w.to_string(),
}
})
.collect::<Vec<_>>()
.join(" ")
}
#[cfg(test)]
pub fn queries() -> Vec<String> {
vec![CAMERAS_SQL.into(), super::feed::filtered_sql(FILTER)]
}
#[cfg(test)]
mod tests {
use super::clean;
#[test]
fn a_camera_reads_as_a_card_names_it() {
for (raw, name) in [
("RICOH IMAGING COMPANY, LTD. GR II", "Ricoh GR II"),
("RICOH IMAGING COMPANY, LTD. RICOH GR III", "Ricoh GR III"),
("NIKON CORPORATION NIKON D600", "Nikon D600"),
("OLYMPUS IMAGING CORP. TG-3", "Olympus TG-3"),
(
"FUJI PHOTO FILM CO., LTD. SP-3000",
"Fuji Photo Film SP-3000",
),
("LEICA CAMERA AG LEICA Q (Typ 116)", "Leica Q (Typ 116)"),
(
"KONICA MINOLTA ALPHA SWEET DIGITAL",
"Konica Minolta Alpha Sweet Digital",
),
("samsung Galaxy S24+", "Samsung Galaxy S24+"),
("Apple iPhone 14 Pro", "Apple iPhone 14 Pro"),
("SONY ILCE-7M3", "Sony ILCE-7M3"),
("FUJIFILM X-T30 II", "Fujifilm X-T30 II"),
("Canon Co., Ltd EOS R", "Canon EOS R"),
("Leica Camera GmbH M11", "Leica Camera M11"),
("ZEISS GMBH ZX1", "Zeiss Gmbh ZX1"),
("NOCORPORATION X", "Nocorporation X"),
("", ""),
] {
assert_eq!(clean(raw), name, "{raw}");
}
}
}
crates/grain-serve/src/read/feed.rsnew+243−0
View file
@@ -0,0 +1,243 @@
//! `social.grain.feed.*`: grain's gallery feeds, each named by a URI as a
//! feed generator is on Bluesky, and each ported query for query from
//! hatk's `server/feeds`.
//!
//! A feed's URI is `at://<the server's DID>/social.grain.feed.generator/<name>`.
//! The chronological feeds page by a cursor naming the last gallery's sort
//! key and URI; For You, which ranks rather than sorts, by an offset.
use super::moderation::{shown, unblocked, unhidden, unhidden_by_author, unmuted};
use super::views::put;
use super::{Params, Reader, camera, foryou, list, location, page};
use crate::xrpc::Failure;
use axum::http::StatusCode;
use rusqlite::types::Value as Sql;
use serde_json::{Value, json};
/// The collection a feed's URI names.
pub const COLLECTION: &str = "social.grain.feed.generator";
/// A feed: its page of galleries for the call.
pub type Feed = fn(&Reader, &Params) -> Result<Value, Failure>;
/// Each feed served: its name, what a reader sees it called, and its
/// generator, in the order hatk listed them.
pub const FEEDS: &[(&str, &str, Feed)] = &[
("actor", "Actor Galleries", actor),
("camera", "Camera", camera::feed),
("following", "Following", following),
("foryou", "For You", foryou::feed),
("group", "Group Pool", group),
("groups", "Groups", groups),
("hashtag", "Hashtag", hashtag),
("location", "Location", location::feed),
("recent", "Recent Galleries", recent),
];
/// `social.grain.feed.describeFeeds`.
pub fn describe_feeds(r: &Reader, _: &Params) -> Result<Value, Failure> {
let did = service(r)?;
let feeds: Vec<Value> = FEEDS
.iter()
.map(|(name, label, _)| {
json!({"uri": format!("at://{did}/{COLLECTION}/{name}"), "name": name, "label": label})
})
.collect();
Ok(json!({"did": did, "feeds": feeds}))
}
/// `social.grain.feed.getFeed`: a page of the feed `feed` names.
pub fn get_feed(r: &Reader, p: &Params) -> Result<Value, Failure> {
let uri = p.at_uri("feed")?;
let did = service(r)?;
let prefix = format!("at://{did}/{COLLECTION}/");
let name = uri.strip_prefix(&prefix).unwrap_or_default();
let Some((_, _, feed)) = FEEDS.iter().find(|(n, _, _)| *n == name) else {
let why = format!("no feed {uri}");
return Err(Failure::new(StatusCode::BAD_REQUEST, "UnknownFeed", why));
};
feed(r, p)
}
/// The DID the server names its feeds by.
fn service<'a>(r: &Reader<'a>) -> Result<&'a str, Failure> {
r.service.ok_or_else(|| {
let why = "this server has no DID to name its feeds by";
Failure::new(StatusCode::NOT_IMPLEMENTED, "MethodNotImplemented", why)
})
}
/// A feed with nothing in it, as one asked for without what it filters by
/// answers.
pub fn empty() -> Value {
json!({"items": []})
}
/// SQL that holds where the gallery `g` has photos and is not hidden: of
/// an account shown, and carrying no label that hides, from grain's
/// labeler or its author.
pub fn listed(g: &str) -> String {
format!(
"{} AND {} AND {} AND EXISTS (SELECT 1 FROM gallery_item gi WHERE gi.gallery = {g}.uri)",
shown(&format!("{g}.did")),
unhidden(&format!("{g}.uri")),
unhidden_by_author(&format!("{g}.uri")),
)
}
/// [`listed`], of an account neither blocking nor blocked by the viewer
/// `?1`, nor muted by them.
pub fn seen(g: &str) -> String {
let did = format!("{g}.did");
format!(
"{} AND {} AND {}",
listed(g),
unblocked(&did, "?1"),
unmuted(&did, "?1")
)
}
/// A page of a chronological feed: the galleries `uri` names, from
/// `from` where `filter` holds, the latest by `key` first, ties broken by
/// `tie`. `?1` is the viewer, `?2` and `?3` the cursor's key and tie, `?4`
/// one past the limit; a feed's own parameters come after.
pub fn chronological(from: &str, uri: &str, key: &str, tie: &str, filter: &str) -> String {
format!(
"SELECT {uri}, {key}, {tie} FROM {from} WHERE {filter} AND ({key}, {tie}) < (?2, ?3)
ORDER BY {key} DESC, {tie} DESC LIMIT ?4"
)
}
/// The galleries of `sql`, a [`chronological`] page, with `own` the
/// feed's parameters from `?5`.
pub fn run(r: &Reader, p: &Params, sql: &str, own: Vec<Sql>) -> Result<Value, Failure> {
let limit = p.limit(30, 100)?;
let (at, after) = p.after(false)?;
let mut bound = vec![
r.viewer.map_or(Sql::Null, |v| Sql::Text(v.into())),
Sql::Text(at),
Sql::Text(after),
Sql::Integer((limit + 1) as i64),
];
bound.extend(own);
let rows: Vec<(String, String, String)> = r.all(sql, rusqlite::params_from_iter(bound))?;
let (rows, cursor) = page(rows, limit, |(_, key, tie)| (key.as_str(), tie.as_str()));
let uris: Vec<&str> = rows.iter().map(|(uri, _, _)| uri.as_str()).collect();
let mut answer = json!({"items": r.galleries_at(&uris)?});
put(&mut answer, "cursor", cursor);
Ok(answer)
}
fn text(s: impl Into<String>) -> Sql {
Sql::Text(s.into())
}
/// Recent Galleries: every gallery the viewer may see.
pub fn recent_sql() -> String {
filtered_sql("1")
}
fn recent(r: &Reader, p: &Params) -> Result<Value, Failure> {
run(r, p, &recent_sql(), vec![])
}
/// Following: the galleries of the accounts the viewer follows.
pub fn following_sql() -> String {
filtered_sql("g.did IN (SELECT subject FROM follow WHERE did = ?1)")
}
fn following(r: &Reader, p: &Params) -> Result<Value, Failure> {
r.signed_in()?;
run(r, p, &following_sql(), vec![])
}
/// Actor Galleries: the galleries of the account `?5`, by when it says
/// it posted them, whoever is looking.
pub fn actor_sql() -> String {
let filter = format!("g.did = ?5 AND {}", listed("g"));
chronological("gallery g", "g.uri", "g.created_at", "g.uri", &filter)
}
fn actor(r: &Reader, p: &Params) -> Result<Value, Failure> {
let Some(did) = p.get("actor").map(|a| r.resolve(a)).transpose()?.flatten() else {
return Ok(empty());
};
run(r, p, &actor_sql(), vec![text(did)])
}
/// Hashtag: the galleries whose description, or a comment on which,
/// holds `?5`'s `#tag`.
pub fn hashtag_sql() -> String {
filtered_sql(
"(g.description LIKE ?5
OR EXISTS (SELECT 1 FROM comment c WHERE c.subject = g.uri AND c.text LIKE ?5))",
)
}
fn hashtag(r: &Reader, p: &Params) -> Result<Value, Failure> {
let Some(tag) = p.get("tag") else {
return Ok(empty());
};
run(r, p, &hashtag_sql(), vec![text(format!("%#{tag}%"))])
}
/// The galleries pooled in a group, latest pooled first.
fn pooled(filter: &str) -> String {
let from = "group_item p JOIN gallery g ON g.uri = p.gallery";
chronological(from, "g.uri", "p.created_at", "p.uri", filter)
}
/// Group Pool: what the group `?5` pooled, whoever is looking.
pub fn group_sql() -> String {
pooled(&format!("p.did = ?5 AND {}", listed("g")))
}
fn group(r: &Reader, p: &Params) -> Result<Value, Failure> {
let Some(group) = p.get("group") else {
return Ok(empty());
};
run(r, p, &group_sql(), vec![text(group)])
}
/// Groups: what every group pooled.
pub fn groups_sql() -> String {
pooled(&seen("g"))
}
fn groups(r: &Reader, p: &Params) -> Result<Value, Failure> {
run(r, p, &groups_sql(), vec![])
}
/// A page of the galleries in `gallery g` where `filter` holds that the
/// viewer may see, by when they were posted, unless that is still to
/// come, as hatk's `galleryFeedTable` sorts them.
pub fn filtered(r: &Reader, p: &Params, filter: &str, own: Vec<Sql>) -> Result<Value, Failure> {
run(r, p, &filtered_sql(filter), own)
}
/// The page [`filtered`] reads.
pub fn filtered_sql(filter: &str) -> String {
let filter = format!("{filter} AND {}", seen("g"));
chronological("gallery g", "g.uri", "g.sort_at", "g.uri", &filter)
}
/// `items` as a JSON list bound as one parameter.
pub fn listed_param<S: AsRef<str>>(items: &[S]) -> Sql {
Sql::Text(list(items))
}
#[cfg(test)]
pub fn queries() -> Vec<String> {
let mut queries = vec![
recent_sql(),
following_sql(),
actor_sql(),
hashtag_sql(),
group_sql(),
groups_sql(),
];
queries.extend(camera::queries());
queries.extend(location::queries());
queries.extend(foryou::queries());
queries
}
crates/grain-serve/src/read/foryou.rsnew+198−0
View file
@@ -0,0 +1,198 @@
//! For You: galleries favorited by those who favorited what the viewer
//! did, ranked as hatk's `server/feeds/foryou.ts` ranks them, with
//! spacecowboy17's weights; and where the viewer has favorited nothing
//! anyone else has, the month's most favorited.
use super::feed::seen;
use super::views::{ago, put};
use super::{Params, Reader, list};
use crate::xrpc::Failure;
use rusqlite::params;
use serde_json::{Value, json};
use std::collections::{HashMap, HashSet};
/// How many hours a gallery's score takes to halve.
const HALF_LIFE_HOURS: f64 = 6.0;
/// How much a gallery reached by more paths gains: its paths to this.
const SMOOTHING: f64 = 0.5;
/// How much a gallery favorited by many loses: its favorites to this.
const POPULARITY_PENALTY: f64 = 0.3;
/// A co-favoriter's favorite counts for one over their favorites to this.
const DIVISOR_POWER: f64 = 1.0;
/// How long, in days, after the viewer a co-favoriter may have favorited
/// a gallery and still count as having got there first.
const TIME_SHIFT_DAYS: f64 = 1.0;
/// How many of the viewer's latest favorites the feed starts from.
const SEEDS: i64 = 500;
/// How many co-favoriters it reads, those sharing most first.
const COLIKERS: usize = 1000;
/// The most a page holds.
const PAGE: usize = 50;
/// How long the cold start looks back.
const MONTH_MICROS: u64 = 30 * 24 * 60 * 60 * 1_000_000;
/// The viewer's latest favorites, and when, as a Julian day.
pub const SEEDS_SQL: &str = "SELECT subject, julianday(created_at) FROM favorite
WHERE did = ?1 ORDER BY created_at DESC LIMIT ?2";
/// Who else favorited `?2`'s subjects, and when, the latest first.
pub const COLIKERS_SQL: &str = "SELECT did, subject, julianday(created_at) FROM favorite
WHERE subject IN (SELECT value FROM json_each(?2)) AND did != ?1 ORDER BY created_at DESC";
/// How many favorites each of `?1` holds.
pub const LIKES_SQL: &str = "SELECT did, count(*) FROM favorite
WHERE did IN (SELECT value FROM json_each(?1)) GROUP BY did";
/// How many favorites each of `?1` has.
pub const POPULARITY_SQL: &str = "SELECT subject, count(*) FROM favorite
WHERE subject IN (SELECT value FROM json_each(?1)) GROUP BY subject";
/// Each gallery the co-favoriters `?2` favorited, other than the seeds
/// `?3` and the viewer `?1`'s own, with who and how many hours old.
pub fn candidates_sql() -> String {
format!(
"SELECT f.did, f.subject, (julianday('now') - julianday(g.sort_at)) * 24 FROM favorite f
JOIN gallery g ON g.uri = f.subject
WHERE f.did IN (SELECT value FROM json_each(?2))
AND f.subject NOT IN (SELECT value FROM json_each(?3))
AND g.did != ?1 AND {}
ORDER BY f.created_at DESC, f.uri",
seen("g")
)
}
/// The most favorited galleries since `?2` that the viewer `?1` did not
/// post, the latest first among those as favorited.
pub fn cold_sql() -> String {
format!(
"SELECT g.uri FROM gallery g LEFT JOIN favorite f ON f.subject = g.uri
WHERE g.did != ?1 AND g.sort_at > ?2 AND {}
GROUP BY g.uri ORDER BY count(f.did) DESC, g.sort_at DESC, g.uri LIMIT ?3 OFFSET ?4",
seen("g")
)
}
/// For You, for the viewer, from the offset `cursor` names.
pub fn feed(r: &Reader, p: &Params) -> Result<Value, Failure> {
let viewer = r.signed_in()?;
let limit = p.limit(PAGE, 100)?.min(PAGE);
let offset: usize = match p.get("cursor") {
None => 0,
Some(c) => c
.parse()
.map_err(|_| Failure::invalid("malformed cursor"))?,
};
let (uris, more): (Vec<String>, bool) = match ranked(r, viewer)? {
Some(ranked) => {
let more = offset + limit < ranked.len();
let page = ranked.into_iter().skip(offset).take(limit).collect();
(page, more)
}
None => {
let rows: Vec<(String,)> = r.all(
&cold_sql(),
params![viewer, ago(MONTH_MICROS), (limit + 1) as i64, offset as i64],
)?;
let more = rows.len() > limit;
(rows.into_iter().take(limit).map(|(u,)| u).collect(), more)
}
};
let uris: Vec<&str> = uris.iter().map(String::as_str).collect();
let mut answer = json!({"items": r.galleries_at(&uris)?});
put(
&mut answer,
"cursor",
more.then(|| (offset + limit).to_string()),
);
Ok(answer)
}
/// What there is to show the viewer, best first; none where no one else
/// favorited what they did before them.
fn ranked(r: &Reader, viewer: &str) -> rusqlite::Result<Option<Vec<String>>> {
let seeds: Vec<(String, Option<f64>)> = r.all(SEEDS_SQL, params![viewer, SEEDS])?;
// When the viewer favorited each, the first time where they did twice.
let mut liked: HashMap<&str, Option<f64>> = HashMap::new();
for (subject, at) in &seeds {
liked.insert(subject.as_str(), *at);
}
let subjects: Vec<&str> = seeds.iter().map(|(s, _)| s.as_str()).collect();
let rows: Vec<(String, String, Option<f64>)> =
r.all(COLIKERS_SQL, params![viewer, list(&subjects)])?;
// Who got to a seed first, or within a day after, and to which.
let mut colikers: Vec<&str> = Vec::new();
let mut shared: HashMap<&str, HashSet<&str>> = HashMap::new();
for (did, subject, at) in &rows {
let Some(Some(mine)) = liked.get(subject.as_str()) else {
continue;
};
if at.is_some_and(|at| at <= mine + TIME_SHIFT_DAYS) {
let to = shared.entry(did.as_str()).or_default();
if to.is_empty() {
colikers.push(did.as_str());
}
to.insert(subject.as_str());
}
}
if colikers.is_empty() {
return Ok(None);
}
colikers.sort_by_key(|did| std::cmp::Reverse(shared[did].len()));
colikers.truncate(COLIKERS);
let candidates: Vec<(String, String, Option<f64>)> = r.all(
&candidates_sql(),
params![viewer, list(&colikers), list(&subjects)],
)?;
let likes: HashMap<String, i64> = r
.all::<(String, i64)>(LIKES_SQL, [list(&colikers)])?
.into_iter()
.collect();
let mut galleries: Vec<&str> = candidates.iter().map(|c| c.1.as_str()).collect();
galleries.sort_unstable();
galleries.dedup();
let popularity: HashMap<String, i64> = r
.all::<(String, i64)>(POPULARITY_SQL, [list(&galleries)])?
.into_iter()
.collect();
// Each gallery's score, its paths and age, in the order first reached.
let mut order: Vec<&str> = Vec::new();
let mut scores: HashMap<&str, (f64, f64, Option<f64>)> = HashMap::new();
for (did, gallery, age) in &candidates {
let total = likes.get(did).copied().filter(|n| *n > 0).unwrap_or(1) as f64;
let entry = scores.entry(gallery.as_str()).or_insert_with(|| {
order.push(gallery.as_str());
(0.0, 0.0, *age)
});
entry.0 += 1.0 / total.powf(DIVISOR_POWER);
entry.1 += 1.0;
}
let mut scored: Vec<(&str, f64)> = order
.into_iter()
.map(|uri| {
let (score, paths, age) = scores[uri];
let popularity = popularity.get(uri).copied().filter(|n| *n > 0).unwrap_or(1);
let decay = 0.5f64.powf(age.unwrap_or(f64::INFINITY) / HALF_LIFE_HOURS);
let score = score * paths.powf(SMOOTHING) * decay
/ (popularity as f64).powf(POPULARITY_PENALTY);
(uri, score)
})
.collect();
scored.sort_by(|a, b| b.1.total_cmp(&a.1));
Ok(Some(
scored.into_iter().map(|(u, _)| u.to_string()).collect(),
))
}
#[cfg(test)]
pub fn queries() -> Vec<String> {
vec![
SEEDS_SQL.into(),
COLIKERS_SQL.into(),
LIKES_SQL.into(),
POPULARITY_SQL.into(),
candidates_sql(),
cold_sql(),
]
}
crates/grain-serve/src/read/location.rsnew+314−0
View file
@@ -0,0 +1,314 @@
//! The Location feed: the galleries at a place, named by the label a view
//! shows for it, by its H3 cell, or both, as hatk's
//! `server/feeds/location.ts` finds them.
use super::feed::{empty, filtered};
use super::place::{REGIONS, country_code};
use super::{Params, Reader};
use crate::xrpc::Failure;
use rusqlite::types::Value as Sql;
use serde_json::Value;
/// Location: by `name`, the label a view shows for a place, where the
/// call gives one and it finds any; otherwise by `location`, an H3 cell,
/// a city's (resolution 5) taking in each place within it.
pub fn feed(r: &Reader, p: &Params) -> Result<Value, Failure> {
let name = p.get("name").map(str::trim).unwrap_or_default();
let location = p.get("location");
if !name.is_empty() {
let (filter, own) = by_name(name);
let page = filtered(r, p, &filter, own)?;
if location.is_none() || page["items"].as_array().is_some_and(|i| !i.is_empty()) {
return Ok(page);
}
}
let Some(cell) = location else {
return Ok(empty());
};
filtered(r, p, CELL_FILTER, cells(cell))
}
/// One reading of a place's label: the parts of a gallery's address it
/// would say, and a region the address may have or leave out.
#[derive(Default)]
struct Reading<'a> {
locality: Option<&'a str>,
region: Option<&'a str>,
country: Option<&'a str>,
region_or_none: Option<&'a str>,
}
/// The ways `name` may have been written, as a label lists a place's
/// parts, `[locality, region, country]`, whichever it has, after the
/// place's own name where it has one: a label of two parts may be a
/// locality's or a region's, and one of one part a country's or a
/// locality's.
fn readings(name: &str) -> Vec<Reading<'_>> {
let parts: Vec<&str> = name
.split(',')
.map(str::trim)
.filter(|s| !s.is_empty())
.collect();
match *parts.as_slice() {
[.., locality, region, country] => vec![
Reading {
locality: Some(locality),
region: Some(region),
country: Some(country),
..Default::default()
},
// A place within a locality of no region, or of one named as
// it is, as "Tokyo Midtown, Minato, JP" and "India Street,
// New York, US" are; not Washington, D.C. for "Seattle,
// Washington, US".
Reading {
locality: Some(region),
country: Some(country),
region_or_none: Some(region),
..Default::default()
},
],
[first, country] => vec![
Reading {
locality: Some(first),
country: Some(country),
..Default::default()
},
Reading {
region: Some(first),
country: Some(country),
..Default::default()
},
],
[only] => vec![
Reading {
country: Some(only),
..Default::default()
},
Reading {
locality: Some(only),
..Default::default()
},
],
_ => vec![],
}
}
/// Each spelling of the country `raw` names that an address may hold, in
/// capitals: its code, an alias of it, and its English name.
pub fn spellings(raw: &str) -> Vec<String> {
let Some(code) = country_code(raw) else {
return vec![];
};
let mut all = vec![code.clone()];
for (alias, of) in super::place::ALIASES {
if *of == code && !all.iter().any(|a| a == alias) {
all.push(alias.to_string());
}
}
if let Some((_, name)) = REGIONS.iter().find(|(c, _)| *c == code) {
let name = name.to_uppercase();
if !all.contains(&name) {
all.push(name);
}
}
all
}
/// The filter for galleries at the place `name` labels, and its
/// parameters from `?5`: any reading of it its address says, or a place
/// named just so.
fn by_name(name: &str) -> (String, Vec<Sql>) {
let mut own: Vec<Sql> = Vec::new();
let mut next = |value: &str| {
own.push(Sql::Text(value.to_string()));
format!("?{}", own.len() + 4)
};
let field = |f: &str| format!("json_extract(g.address, '$.{f}')");
let mut any: Vec<String> = Vec::new();
for reading in readings(name) {
let mut all = Vec::new();
if let Some(locality) = reading.locality {
all.push(format!(
"UPPER({}) = UPPER({})",
field("locality"),
next(locality)
));
}
if let Some(region) = reading.region {
all.push(format!(
"UPPER({}) = UPPER({})",
field("region"),
next(region)
));
}
if let Some(country) = reading.country {
let mut spelled = Vec::new();
for spelling in spellings(country) {
spelled.push(next(&spelling));
}
all.push(format!(
"UPPER({}) IN ({})",
field("country"),
spelled.join(", ")
));
}
if let Some(region) = reading.region_or_none {
all.push(format!(
"({0} IS NULL OR UPPER({0}) = UPPER({1}))",
field("region"),
next(region)
));
}
any.push(format!("({})", all.join(" AND ")));
}
any.push(format!(
"json_extract(g.location, '$.name') = {}",
next(name)
));
(format!("({})", any.join(" OR ")), own)
}
/// How many cell ranges [`CELL_FILTER`] reads: the cell itself, and its
/// descendants at each finer resolution, 6 to 15.
const RANGES: usize = 11;
/// What a gallery in the feed has: a cell within one of the ranges `?5`
/// to `?26` bound, each from its first to its last cell.
pub const CELL_FILTER: &str = "(g.h3 BETWEEN ?5 AND ?6 OR g.h3 BETWEEN ?7 AND ?8
OR g.h3 BETWEEN ?9 AND ?10 OR g.h3 BETWEEN ?11 AND ?12 OR g.h3 BETWEEN ?13 AND ?14
OR g.h3 BETWEEN ?15 AND ?16 OR g.h3 BETWEEN ?17 AND ?18 OR g.h3 BETWEEN ?19 AND ?20
OR g.h3 BETWEEN ?21 AND ?22 OR g.h3 BETWEEN ?23 AND ?24 OR g.h3 BETWEEN ?25 AND ?26)";
/// The resolution H3 calls a city's, whose cells take in the places
/// within them.
const CITY: u64 = 5;
/// The ranges of cells [`CELL_FILTER`] reads for `cell`: the cell itself,
/// and where it is a city's, each cell within it, a range a resolution.
/// H3 numbers a cell's descendants at one resolution in a run, its own
/// digits then the finer ones, so each run is a range of the hex strings
/// cells are written as, which are all fifteen digits long.
fn cells(cell: &str) -> Vec<Sql> {
let mut ranges = vec![(cell.to_string(), cell.to_string()); RANGES];
let index = u64::from_str_radix(cell, 16).ok();
let city = index.filter(|i| mode(*i) == 1 && resolution(*i) == CITY);
if let (Some(index), true) = (city, cell == cell.to_lowercase()) {
for (slot, res) in ranges.iter_mut().skip(1).zip(CITY + 1..=15) {
let at = (index & !(0xf << 52)) | (res << 52);
let (mut first, mut last) = (at, at);
for digit in CITY + 1..=res {
let shift = (15 - digit) * 3;
first &= !(0b111 << shift);
last = (last & !(0b111 << shift)) | (0b110 << shift);
}
*slot = (format!("{first:x}"), format!("{last:x}"));
}
}
ranges
.into_iter()
.flat_map(|(first, last)| [Sql::Text(first), Sql::Text(last)])
.collect()
}
fn mode(index: u64) -> u64 {
(index >> 59) & 0xf
}
fn resolution(index: u64) -> u64 {
(index >> 52) & 0xf
}
#[cfg(test)]
pub fn queries() -> Vec<String> {
use super::feed::filtered_sql;
let (named, _) = by_name("The Space Needle, Seattle, Washington, US");
vec![filtered_sql(&named), filtered_sql(CELL_FILTER)]
}
#[cfg(test)]
mod tests {
use super::*;
use rusqlite::types::ValueRef;
fn texts(values: &[Sql]) -> Vec<String> {
values
.iter()
.map(|v| ValueRef::from(v).as_str().unwrap().to_string())
.collect()
}
#[test]
fn a_city_takes_in_the_cells_within_it() {
// Ocean Beach, a resolution 10 cell, and the city cell it is in.
let venue = "8a2830959c2ffff";
let city = "85283097fffffff";
let ranges = texts(&cells(city));
assert_eq!(ranges.len(), RANGES * 2);
assert_eq!(&ranges[..2], [city, city]);
// Resolution 10, its five finer digits run from 0 to 6.
let (first, last) = (&ranges[10], &ranges[11]);
assert_eq!(
(first.as_str(), last.as_str()),
("8a2830940007fff", "8a2830976db7fff")
);
assert!(first.as_str() <= venue && venue <= last.as_str());
// Resolution 15 has no digit left unused.
assert_eq!(
(ranges[20].as_str(), ranges[21].as_str()),
("8f2830940000000", "8f2830976db6db6")
);
// A cell finer than a city's, or none at all, is only itself.
for other in [venue, "nonsense", "85283097FFFFFFF"] {
assert!(texts(&cells(other)).iter().all(|c| c == other));
}
}
#[test]
fn a_label_is_read_every_way_it_may_have_been_written() {
let (filter, own) = by_name("Paris, FR");
assert_eq!(
texts(&own),
[
"Paris",
"FR",
"FRANCE",
"Paris",
"FR",
"FRANCE",
"Paris, FR"
]
);
assert_eq!(filter.matches(" OR ").count(), 2);
let (_, own) = by_name("Seattle, Washington, US");
assert_eq!(
texts(&own),
[
"Seattle",
"Washington",
"US",
"USA",
"UNITED STATES",
"Washington",
"US",
"USA",
"UNITED STATES",
"Washington",
"Seattle, Washington, US"
]
);
let (_, own) = by_name("Waldport");
assert_eq!(texts(&own), ["WALDPORT", "Waldport", "Waldport"]);
let (filter, own) = by_name(" , ");
assert_eq!(texts(&own), [" , "]);
assert!(!filter.contains("address"));
}
#[test]
fn a_country_is_spelled_every_way_an_address_holds_it() {
assert_eq!(spellings("usa"), ["US", "USA", "UNITED STATES"]);
assert_eq!(spellings("Greece"), ["GR", "GREECE"]);
assert_eq!(spellings("Narnia"), ["NARNIA"]);
assert!(spellings(" ").is_empty());
}
}
crates/grain-serve/src/read/mod.rs+9−0
View file
@@ -13,9 +13,13 @@
//! [`viewer`]: crate::viewer
pub mod actor;
pub mod camera;
pub mod comment;
pub mod feed;
pub mod foryou;
pub mod gallery;
pub mod graph;
pub mod location;
pub mod moderation;
pub mod place;
pub mod story;
@@ -42,6 +46,8 @@ pub struct Reader<'a> {
pub cdn: &'a Cdn,
/// The caller, if they said who they are.
pub viewer: Option<&'a str>,
/// The server's own DID, if it has one.
pub service: Option<&'a str>,
}
/// A query: its answer, from the index, for its parameters.
@@ -63,6 +69,8 @@ pub const METHODS: &[(&str, Method)] = &[
("social.grain.story.getStories", story::get_stories),
("social.grain.story.getStory", story::get_story),
("social.grain.story.getAuthors", story::get_authors),
("social.grain.feed.describeFeeds", feed::describe_feeds),
("social.grain.feed.getFeed", feed::get_feed),
];
/// `router` with each of [`METHODS`] at `/xrpc/<nsid>`.
@@ -108,6 +116,7 @@ async fn answer(
db,
cdn: &app.cdn,
viewer: viewer.as_deref(),
service: app.did.as_deref(),
};
method(&reader, &params).map(Json)
})
crates/grain-serve/src/read/place.rs+1−1
View file
@@ -48,7 +48,7 @@ pub fn display(location: &Value, address: &Value) -> String {
}
/// Spellings of a country neither its code nor its English name.
const ALIASES: &[(&str, &str)] = &[("USA", "US")];
pub const ALIASES: &[(&str, &str)] = &[("USA", "US")];
/// The ISO 3166 code `raw` names, by its code, an alias or its English
/// name, in any case; what it says, in capitals, where it is none of
crates/grain-serve/src/read/tests.rs+2−0
View file
@@ -11,6 +11,8 @@ fn the_reads_read_by_index() {
queries.extend(comment::queries());
queries.extend(graph::queries());
queries.extend(story::queries());
queries.extend(feed::queries());
queries.extend(views::queries());
index.read(|db| {
sized(db);
for sql in &queries {
crates/grain-serve/src/read/views.rs+132−77
View file
@@ -52,19 +52,59 @@ pub fn ago(micros: u64) -> String {
grain::keys::iso(grain::keys::now_micros().saturating_sub(micros))
}
/// The handle of each of `?1`.
const HANDLES_SQL: &str =
"SELECT did, handle FROM repo WHERE did IN (SELECT value FROM json_each(?1))";
/// The profile record of each of `?1`.
const PROFILES_SQL: &str =
"SELECT * FROM profile p WHERE p.did IN (SELECT value FROM json_each(?1)) ORDER BY p.uri";
/// The labels standing on each of `?1` at `?2`.
const LABELS_SQL: &str = "SELECT l.src, l.uri, l.val, l.cts, l.exp FROM label l
WHERE l.uri IN (SELECT value FROM json_each(?1)) AND l.neg = 0
AND (l.exp IS NULL OR l.exp > ?2)
AND NOT EXISTS (SELECT 1 FROM label n WHERE n.uri = l.uri AND n.val = l.val
AND (n.cts > l.cts OR (n.cts = l.cts AND n.id > l.id)))
ORDER BY l.id";
/// The self-labels on each of `?1`.
const SELF_LABELS_SQL: &str = "SELECT uri, val FROM self_label WHERE uri IN (SELECT value FROM json_each(?1)) ORDER BY uri, val";
/// The viewer `?1`'s favorite of each of `?2`.
const VIEWER_FAVORITES_SQL: &str = "SELECT subject, uri FROM favorite
WHERE did = ?1 AND subject IN (SELECT value FROM json_each(?2)) ORDER BY uri";
/// A post of `?1`'s that links to its record `?2`.
const CROSS_POST_SQL: &str = "SELECT p.uri FROM bsky_post p WHERE p.did = ?1 AND p.text LIKE '%/profile/%' || ?2 || '%' LIMIT 1";
/// The pool each of `?1` is in.
const POOLS_SQL: &str = "SELECT i.gallery, i.did, i.uri FROM group_item i
WHERE i.gallery IN (SELECT value FROM json_each(?1)) ORDER BY i.created_at, i.uri";
/// How many favorited each of `?1`.
const FAV_COUNTS_SQL: &str = "SELECT subject, count(DISTINCT did) FROM favorite
WHERE subject IN (SELECT value FROM json_each(?1)) GROUP BY subject";
/// The items of each gallery of `?1`, in order.
const ITEMS_SQL: &str =
"SELECT * FROM gallery_item i WHERE i.gallery IN (SELECT value FROM json_each(?1))
ORDER BY i.position, i.uri";
/// The EXIF of each photo of `?1`.
const EXIFS_SQL: &str =
"SELECT * FROM photo_exif e WHERE e.photo IN (SELECT value FROM json_each(?1)) ORDER BY e.uri";
/// Which of `?2` the viewer `?1` watched.
const VIEWED_SQL: &str = "SELECT subject FROM story_view WHERE did = ?1 AND subject IN (SELECT value FROM json_each(?2))";
impl Reader<'_> {
/// The accounts `dids` name, each one, known or not.
pub fn actors(&self, wanted: &[&str]) -> rusqlite::Result<HashMap<String, Actor>> {
let dids = list(wanted);
let handles: Vec<(String, Option<String>)> = self.all(
"SELECT did, handle FROM repo WHERE did IN (SELECT value FROM json_each(?1))",
[&dids],
)?;
let handles: Vec<(String, Option<String>)> = self.all(HANDLES_SQL, [&dids])?;
let handles: HashMap<String, Option<String>> = handles.into_iter().collect();
let profiles = self.rows::<Profile>(
"SELECT * FROM profile p WHERE p.did IN (SELECT value FROM json_each(?1)) ORDER BY p.uri",
[&dids],
)?;
let profiles = self.rows::<Profile>(PROFILES_SQL, [&dids])?;
let mut profiles: HashMap<String, Row<Profile>> =
profiles.into_iter().map(|p| (p.did.clone(), p)).collect();
Ok(wanted
@@ -86,15 +126,8 @@ impl Reader<'_> {
/// The labels standing on each of `uris`: grain's own, latest first
/// where one was given again, less those negated since or expired.
pub fn labels(&self, uris: &[&str]) -> rusqlite::Result<HashMap<String, Vec<Value>>> {
let rows: Vec<(String, String, String, String, Option<String>)> = self.all(
"SELECT l.src, l.uri, l.val, l.cts, l.exp FROM label l
WHERE l.uri IN (SELECT value FROM json_each(?1)) AND l.neg = 0
AND (l.exp IS NULL OR l.exp > ?2)
AND NOT EXISTS (SELECT 1 FROM label n WHERE n.uri = l.uri AND n.val = l.val
AND (n.cts > l.cts OR (n.cts = l.cts AND n.id > l.id)))
ORDER BY l.id",
params![list(uris), grain::keys::now_iso()],
)?;
let rows: Vec<(String, String, String, String, Option<String>)> =
self.all(LABELS_SQL, params![list(uris), grain::keys::now_iso()])?;
let mut labels: HashMap<String, Vec<Value>> = HashMap::new();
for (src, uri, val, cts, exp) in rows {
let mut label = json!({"src": src, "uri": uri, "val": val, "neg": false, "cts": cts});
@@ -113,10 +146,7 @@ impl Reader<'_> {
) -> rusqlite::Result<HashMap<String, Vec<Value>>> {
let uris: Vec<&str> = records.iter().map(|r| r.0).collect();
let mut labels = self.labels(&uris)?;
let own: Vec<(String, String)> = self.all(
"SELECT uri, val FROM self_label WHERE uri IN (SELECT value FROM json_each(?1)) ORDER BY uri, val",
[list(&uris)],
)?;
let own: Vec<(String, String)> = self.all(SELF_LABELS_SQL, [list(&uris)])?;
for (uri, val) in own {
let (_, did, at) = records
.iter()
@@ -185,10 +215,7 @@ impl Reader<'_> {
/// The photos `uris` name, of accounts shown, by URI.
pub fn photos(&self, uris: &[&str]) -> rusqlite::Result<HashMap<String, Row<Photo>>> {
let sql = format!(
"SELECT * FROM photo p WHERE p.uri IN (SELECT value FROM json_each(?1)) AND {}",
shown("p.did")
);
let sql = photos_sql();
let photos = self.rows::<Photo>(&sql, [list(uris)])?;
Ok(photos.into_iter().map(|p| (p.uri.clone(), p)).collect())
}
@@ -196,12 +223,7 @@ impl Reader<'_> {
/// How many comments each of `subjects` has: of accounts shown, and
/// not answering one since deleted.
pub fn comment_counts(&self, subjects: &[&str]) -> rusqlite::Result<HashMap<String, i64>> {
let sql = format!(
"SELECT c.subject, count(*) FROM comment c WHERE c.subject IN (SELECT value FROM json_each(?1))
AND {} AND {} GROUP BY c.subject",
shown("c.did"),
not_orphaned("c")
);
let sql = comment_counts_sql();
Ok(self
.all::<(String, i64)>(&sql, [list(subjects)])?
.into_iter()
@@ -210,8 +232,7 @@ impl Reader<'_> {
/// The viewer's favorite of each of `subjects` they favorited.
pub fn viewer_favorites(&self, subjects: &[&str]) -> rusqlite::Result<HashMap<String, String>> {
let sql = "SELECT subject, uri FROM favorite
WHERE did = ?1 AND subject IN (SELECT value FROM json_each(?2)) ORDER BY uri";
let sql = VIEWER_FAVORITES_SQL;
Ok(self
.all::<(String, String)>(sql, params![self.viewer, list(subjects)])?
.into_iter()
@@ -229,10 +250,7 @@ impl Reader<'_> {
let mut posts = HashMap::new();
for &(uri, did) in records {
let tail = format!("/{kind}/{}", uri.rsplit('/').next().unwrap_or_default());
let found: Vec<(String,)> = self.all(
"SELECT p.uri FROM bsky_post p WHERE p.did = ?1 AND p.text LIKE '%/profile/%' || ?2 || '%' LIMIT 1",
[did, tail.as_str()],
)?;
let found: Vec<(String,)> = self.all(CROSS_POST_SQL, [did, tail.as_str()])?;
for (post,) in found {
let rkey = post.rsplit('/').next().unwrap_or_default();
posts.insert(
@@ -246,10 +264,7 @@ impl Reader<'_> {
/// The galleries `uris` name, of accounts shown, in that order.
pub fn galleries_at(&self, uris: &[&str]) -> rusqlite::Result<Vec<Value>> {
let sql = format!(
"SELECT * FROM gallery g WHERE g.uri IN (SELECT value FROM json_each(?1)) AND {}",
shown("g.did")
);
let sql = galleries_at_sql();
let mut rows: HashMap<String, Row<Gallery>> = self
.rows::<Gallery>(&sql, [list(uris)])?
.into_iter()
@@ -266,26 +281,11 @@ impl Reader<'_> {
let favorites = self.viewer_favorites(&uris)?;
// The viewer's follows who favorited each, most recent first: one
// row an account, however many favorites or follows it holds.
let facepile_sql = format!(
"SELECT subject, did FROM (
SELECT subject, did, row_number() OVER (PARTITION BY subject ORDER BY latest DESC, did) AS n
FROM (SELECT f.subject AS subject, f.did AS did, max(f.created_at) AS latest
FROM favorite f JOIN follow o ON o.did = ?1 AND o.subject = f.did
WHERE f.subject IN (SELECT value FROM json_each(?2)) AND f.did <> ?1
AND {} AND {}
GROUP BY f.subject, f.did))
WHERE n <= ?3 ORDER BY subject, n",
shown("f.did"),
unblocked("f.did", "?1")
);
let pile_sql = facepile_sql();
let facepiles: Vec<(String, String)> =
self.all(&facepile_sql, params![self.viewer, uri_list, FACEPILE])?;
self.all(&pile_sql, params![self.viewer, uri_list, FACEPILE])?;
// The pool each is in: the latest, where it is in more than one.
let pooled: Vec<(String, String, String)> = self.all(
"SELECT i.gallery, i.did, i.uri FROM group_item i
WHERE i.gallery IN (SELECT value FROM json_each(?1)) ORDER BY i.created_at, i.uri",
[&uri_list],
)?;
let pooled: Vec<(String, String, String)> = self.all(POOLS_SQL, [&uri_list])?;
let groups: HashMap<&str, (&str, &str)> = pooled
.iter()
.map(|(g, did, item)| (g.as_str(), (did.as_str(), item.as_str())))
@@ -298,11 +298,7 @@ impl Reader<'_> {
let actors = self.actors(&dids)?;
let creators = self.profiles(&dids)?;
let fav_counts: HashMap<String, i64> = self
.all::<(String, i64)>(
"SELECT subject, count(DISTINCT did) FROM favorite
WHERE subject IN (SELECT value FROM json_each(?1)) GROUP BY subject",
[&uri_list],
)?
.all::<(String, i64)>(FAV_COUNTS_SQL, [&uri_list])?
.into_iter()
.collect();
let comment_counts = self.comment_counts(&uris)?;
@@ -311,17 +307,10 @@ impl Reader<'_> {
.map(|g| (g.uri.as_str(), g.did.as_str(), g.indexed_at.as_str()))
.collect();
let labels = self.record_labels(&records)?;
let items = self.rows::<GalleryItem>(
"SELECT * FROM gallery_item i WHERE i.gallery IN (SELECT value FROM json_each(?1))
ORDER BY i.position, i.uri",
[&uri_list],
)?;
let items = self.rows::<GalleryItem>(ITEMS_SQL, [&uri_list])?;
let photo_uris: Vec<&str> = items.iter().map(|i| i.record.item.as_str()).collect();
let photos = self.photos(&photo_uris)?;
let exifs = self.rows::<PhotoExif>(
"SELECT * FROM photo_exif e WHERE e.photo IN (SELECT value FROM json_each(?1)) ORDER BY e.uri",
[list(&photo_uris)],
)?;
let exifs = self.rows::<PhotoExif>(EXIFS_SQL, [list(&photo_uris)])?;
let exifs: HashMap<&str, &Row<PhotoExif>> =
exifs.iter().map(|e| (e.record.photo.as_str(), e)).collect();
let authored: Vec<(&str, &str)> = rows
@@ -385,10 +374,7 @@ impl Reader<'_> {
pub fn stories(&self, rows: Vec<Row<Story>>) -> rusqlite::Result<Vec<Value>> {
let uris: Vec<&str> = rows.iter().map(|s| s.uri.as_str()).collect();
let favorites = self.viewer_favorites(&uris)?;
let viewed: Vec<(String,)> = self.all(
"SELECT subject FROM story_view WHERE did = ?1 AND subject IN (SELECT value FROM json_each(?2))",
params![self.viewer, list(&uris)],
)?;
let viewed: Vec<(String,)> = self.all(VIEWED_SQL, params![self.viewer, list(&uris)])?;
let mut dids: Vec<&str> = rows.iter().map(|s| s.did.as_str()).collect();
dids.sort_unstable();
dids.dedup();
@@ -507,3 +493,72 @@ pub fn exif_view(e: &Row<PhotoExif>) -> Value {
put(&mut view, "model", text(&r.model));
view
}
/// The photos `?1` names, of accounts shown.
pub fn photos_sql() -> String {
format!(
"SELECT * FROM photo p WHERE p.uri IN (SELECT value FROM json_each(?1)) AND {}",
shown("p.did")
)
}
/// How many comments each of `?1` has that a thread shows.
pub fn comment_counts_sql() -> String {
format!(
"SELECT c.subject, count(*) FROM comment c WHERE c.subject IN (SELECT value FROM json_each(?1))
AND {} AND {} GROUP BY c.subject",
shown("c.did"),
not_orphaned("c")
)
}
/// The galleries `?1` names, of accounts shown.
pub fn galleries_at_sql() -> String {
format!(
"SELECT * FROM gallery g WHERE g.uri IN (SELECT value FROM json_each(?1)) AND {}",
shown("g.did")
)
}
/// The viewer `?1`'s follows who favorited each of `?2`, the latest first, `?3` a gallery.
pub fn facepile_sql() -> String {
format!(
"SELECT subject, did FROM (
SELECT subject, did, row_number() OVER (PARTITION BY subject ORDER BY latest DESC, did) AS n
FROM (SELECT f.subject AS subject, f.did AS did, max(f.created_at) AS latest
FROM favorite f JOIN follow o ON o.did = ?1 AND o.subject = f.did
WHERE f.subject IN (SELECT value FROM json_each(?2)) AND f.did <> ?1
AND {} AND {}
GROUP BY f.subject, f.did))
WHERE n <= ?3 ORDER BY subject, n",
shown("f.did"),
unblocked("f.did", "?1")
)
}
#[cfg(test)]
pub fn queries() -> Vec<String> {
let mut queries: Vec<String> = [
HANDLES_SQL,
PROFILES_SQL,
LABELS_SQL,
SELF_LABELS_SQL,
VIEWER_FAVORITES_SQL,
CROSS_POST_SQL,
POOLS_SQL,
FAV_COUNTS_SQL,
ITEMS_SQL,
EXIFS_SQL,
VIEWED_SQL,
]
.into_iter()
.map(String::from)
.collect();
queries.extend([
photos_sql(),
comment_counts_sql(),
galleries_at_sql(),
facepile_sql(),
]);
queries
}
crates/grain-serve/tests/read.rs+499−0
View file
499 lines changed: not loaded until asked for.
lexicons/social/grain/feed/getFeed.json+4−0
View file
@@ -19,6 +19,10 @@
"actor": { "type": "string", "format": "did", "description": "Filter by actor DID" },
"camera": { "type": "string", "description": "Filter by camera make and model" },
"location": { "type": "string", "description": "Filter by H3 cell index" },
"name": {
"type": "string",
"description": "Filter by a place's label, as a gallery view's locationDisplay gives it. Where it finds nothing, location is used instead."
},
"tag": { "type": "string", "description": "Filter by hashtag" },
"group": { "type": "string", "format": "did", "description": "Filter by group DID" }
}
hatk.generated.ts+1−1
View file