Something went wrong. Try again.
(READ ONLY) Margin is an open annotation layer for the internet. Powered by the AT Protocol.
margin.at
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[]);