Something went wrong. Try again.
(READ ONLY) Margin is an open annotation layer for the internet. Powered by the AT Protocol.
margin.at
extension web atproto comments
Something went wrong. Try again.
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103-- name: CreateCollection :execINSERT INTO collections (uri, author_did, name, description, icon, created_at, indexed_at)VALUES ($1, $2, $3, $4, $5, $6, $7)ON CONFLICT(uri) DO UPDATE SET name = EXCLUDED.name, description = EXCLUDED.description, icon = EXCLUDED.icon, indexed_at = EXCLUDED.indexed_at;
-- name: GetCollectionsByAuthor :manySELECT uri, author_did, name, description, icon, created_at, indexed_atFROM collectionsWHERE author_did = $1ORDER BY created_at DESC;
-- name: GetCollectionByURI :oneSELECT uri, author_did, name, description, icon, created_at, indexed_atFROM collectionsWHERE uri = $1;
-- name: DeleteCollectionItemsByCollection :execDELETE FROM collection_items WHERE collection_uri = $1;
-- name: DeleteCollection :execDELETE FROM collections WHERE uri = $1;
-- name: AddToCollection :execINSERT INTO collection_items (uri, author_did, collection_uri, annotation_uri, position, created_at, indexed_at)VALUES ($1, $2, $3, $4, $5, $6, $7)ON CONFLICT(uri) DO UPDATE SET position = EXCLUDED.position, indexed_at = EXCLUDED.indexed_at;
-- name: GetCollectionItems :manySELECT uri, author_did, collection_uri, annotation_uri, position, created_at, indexed_atFROM collection_itemsWHERE collection_uri = $1ORDER BY position ASC, created_at DESC;
-- name: RemoveFromCollection :execDELETE FROM collection_items WHERE uri = $1;
-- name: GetRecentCollectionItems :manySELECT uri, author_did, collection_uri, annotation_uri, position, created_at, indexed_atFROM collection_itemsORDER BY created_at DESCLIMIT $1 OFFSET $2;
-- name: GetPopularCollectionItems :manySELECT c.uri, c.author_did, c.collection_uri, c.annotation_uri, c.position, c.created_at, c.indexed_atFROM collection_items cLEFT JOIN LATERAL ( SELECT COUNT(*) as cnt FROM likes WHERE subject_uri = c.annotation_uri) l ON trueLEFT JOIN LATERAL ( SELECT COUNT(*) as cnt FROM replies WHERE root_uri = c.annotation_uri) r ON trueWHERE c.created_at > $1 AND (l.cnt + r.cnt) > 0ORDER BY (l.cnt + r.cnt) DESC, c.created_at DESCLIMIT $2 OFFSET $3;
-- name: GetShelvedCollectionItems :manySELECT c.uri, c.author_did, c.collection_uri, c.annotation_uri, c.position, c.created_at, c.indexed_atFROM collection_items cWHERE c.created_at < $1 AND c.created_at > $2 AND NOT EXISTS (SELECT 1 FROM likes WHERE subject_uri = c.annotation_uri) AND NOT EXISTS (SELECT 1 FROM replies WHERE root_uri = c.annotation_uri)ORDER BY RANDOM()LIMIT $3 OFFSET $4;
-- name: GetCollectionItemsByAuthor :manySELECT uri, author_did, collection_uri, annotation_uri, position, created_at, indexed_atFROM collection_itemsWHERE author_did = $1ORDER BY created_at DESC;
-- name: GetCollectionURIsForAnnotation :manySELECT collection_uri FROM collection_items WHERE annotation_uri = $1;
-- name: GetCollectionItemCounts :manySELECT collection_uri, COUNT(*)FROM collection_itemsWHERE collection_uri = ANY($1::text[])GROUP BY collection_uri;
-- name: GetCollectionsForNoteURIs :manySELECT DISTINCT ON (ci.annotation_uri) ci.annotation_uri, c.uri, c.author_did, c.name, c.description, c.icon, c.created_at, c.indexed_atFROM collection_items ciJOIN collections c ON c.uri = ci.collection_uriWHERE ci.annotation_uri = ANY($1::text[])ORDER BY ci.annotation_uri, ci.created_at ASC;
-- name: GetCollectionsByURIs :manySELECT uri, author_did, name, description, icon, created_at, indexed_atFROM collectionsWHERE uri = ANY($1::text[]);