Skip to content

A self-hostable bookmark-everything app (links, notes and images) with AI-based automatic tagging and full text search

8.8 KB
Raw Blame
import {
  and,
  eq,
  exists,
  gt,
  gte,
  inArray,
  isNotNull,
  isNull,
  like,
  lt,
  lte,
  ne,
  notExists,
  notInArray,
  not,
  notLike,
  or,
  SQL,
  sql,
} from "drizzle-orm";

import {
  bookmarkAssets,
  bookmarkLinks,
  bookmarkLists,
  bookmarks,
  bookmarksInLists,
  bookmarkTags,
  highlights,
  rssFeedImportsTable,
  rssFeedsTable,
  tagsOnBookmarks,
} from "@karakeep/db/schema";
import { Matcher } from "@karakeep/shared/types/search";
import { toAbsoluteDate } from "@karakeep/shared/utils/relativeDateUtils";

import { AuthedContext } from "..";

// SQLite caps expression depth at 1000 and a flat chain of N ANDs/ORs nests N
// deep, so combine conditions as a balanced tree (depth log2(N)) instead.
function combineBalanced(op: typeof and | typeof or, conditions: SQL[]): SQL {
  if (conditions.length === 0) {
    return sql`0`;
  }
  if (conditions.length === 1) {
    return conditions[0];
  }
  const mid = Math.ceil(conditions.length / 2);
  return op(
    combineBalanced(op, conditions.slice(0, mid)),
    combineBalanced(op, conditions.slice(mid)),
  )!;
}

// Builds a condition on the `bookmarks` table that matches the bookmarks
// selected by `matcher`. The condition doesn't scope by user, so it must be
// applied to a query that already filters on `bookmarks.userId`.
export async function getBookmarksConditionFromMatcher(
  ctx: AuthedContext,
  matcher: Matcher,
  visitedListIds = new Set<string>(),
): Promise<SQL> {
  const { db } = ctx;
  const userId = ctx.user.id;

  switch (matcher.type) {
    case "tagName": {
      const comp = matcher.inverse ? notInArray : inArray;
      return comp(
        bookmarks.id,
        db
          .select({ id: tagsOnBookmarks.bookmarkId })
          .from(tagsOnBookmarks)
          .innerJoin(bookmarkTags, eq(tagsOnBookmarks.tagId, bookmarkTags.id))
          .where(
            and(
              eq(bookmarkTags.userId, userId),
              eq(bookmarkTags.name, matcher.tagName),
            ),
          ),
      );
    }
    case "tagged": {
      const comp = matcher.tagged ? exists : notExists;
      return comp(
        db
          .select()
          .from(tagsOnBookmarks)
          .where(eq(tagsOnBookmarks.bookmarkId, bookmarks.id)),
      );
    }
    case "listName": {
      // First, look up the list by name
      const lists = await db.query.bookmarkLists.findMany({
        where: and(
          eq(bookmarkLists.userId, userId),
          eq(bookmarkLists.name, matcher.listName),
        ),
      });

      if (lists.length === 0) {
        // No matching lists
        return sql`0`;
      }

      // Use List model to resolve list membership (manual and smart)
      // Import dynamically to avoid circular dependency
      const { List } = await import("../models/lists");
      const conditions = await Promise.all(
        lists.map(async (list) => {
          const listModel = await List.fromId(ctx, list.id);
          return await listModel.getBookmarksCondition(visitedListIds);
        }),
      );
      const inLists = combineBalanced(or, conditions);
      // Smart list conditions can evaluate to NULL (e.g. LIKE on a NULL
      // column), so coalesce before negating.
      return matcher.inverse ? sql`coalesce(${inLists}, 0) = 0` : inLists;
    }
    case "inlist": {
      const comp = matcher.inList ? exists : notExists;
      return comp(
        db
          .select()
          .from(bookmarksInLists)
          .where(eq(bookmarksInLists.bookmarkId, bookmarks.id)),
      );
    }
    case "rssFeedName": {
      const comp = matcher.inverse ? notInArray : inArray;
      return comp(
        bookmarks.id,
        db
          .select({ id: rssFeedImportsTable.bookmarkId })
          .from(rssFeedImportsTable)
          .innerJoin(
            rssFeedsTable,
            eq(rssFeedImportsTable.rssFeedId, rssFeedsTable.id),
          )
          .where(
            and(
              // NOT IN never matches if the subquery returns a NULL
              isNotNull(rssFeedImportsTable.bookmarkId),
              eq(rssFeedsTable.userId, userId),
              eq(rssFeedsTable.name, matcher.feedName),
            ),
          ),
      );
    }
    case "archived": {
      return eq(bookmarks.archived, matcher.archived);
    }
    case "url": {
      const comp = matcher.inverse ? notLike : like;
      return or(
        exists(
          db
            .select()
            .from(bookmarkLinks)
            .where(
              and(
                eq(bookmarkLinks.id, bookmarks.id),
                comp(bookmarkLinks.url, `%${matcher.url}%`),
              ),
            ),
        ),
        exists(
          db
            .select()
            .from(bookmarkAssets)
            .where(
              and(
                eq(bookmarkAssets.id, bookmarks.id),
                // When a user is asking for a link, the inverse matcher should match only assets with URLs.
                isNotNull(bookmarkAssets.sourceUrl),
                comp(bookmarkAssets.sourceUrl, `%${matcher.url}%`),
              ),
            ),
        ),
      )!;
    }
    case "title": {
      const linkTitleMatches = exists(
        db
          .select()
          .from(bookmarkLinks)
          .where(
            and(
              eq(bookmarkLinks.id, bookmarks.id),
              like(bookmarkLinks.title, `%${matcher.title}%`),
            ),
          ),
      );
      if (matcher.inverse) {
        return and(
          or(
            isNull(bookmarks.title),
            notLike(bookmarks.title, `%${matcher.title}%`),
          ),
          not(linkTitleMatches),
        )!;
      }
      return or(like(bookmarks.title, `%${matcher.title}%`), linkTitleMatches)!;
    }
    case "favourited": {
      return eq(bookmarks.favourited, matcher.favourited);
    }
    case "hasNotes": {
      return matcher.hasNotes
        ? and(isNotNull(bookmarks.note), ne(bookmarks.note, ""))!
        : or(isNull(bookmarks.note), eq(bookmarks.note, ""))!;
    }
    case "hasHighlights": {
      const comp = matcher.hasHighlights ? inArray : notInArray;
      return comp(
        bookmarks.id,
        db
          .select({ id: highlights.bookmarkId })
          .from(highlights)
          .where(eq(highlights.userId, userId)),
      );
    }
    case "dateAfter": {
      const comp = matcher.inverse ? lt : gte;
      return comp(bookmarks.createdAt, matcher.dateAfter);
    }
    case "dateBefore": {
      // `before:<date>` is inclusive of the whole day, so compare against
      // the start of the following day.
      const endOfDay = new Date(
        matcher.dateBefore.getTime() + 24 * 60 * 60 * 1000,
      );
      const comp = matcher.inverse ? gte : lt;
      return comp(bookmarks.createdAt, endOfDay);
    }
    case "age": {
      const comp = matcher.relativeDate.direction === "newer" ? gte : lt;
      return comp(bookmarks.createdAt, toAbsoluteDate(matcher.relativeDate));
    }
    case "type": {
      const comp = matcher.inverse ? ne : eq;
      return comp(bookmarks.type, matcher.typeName);
    }
    case "brokenLinks": {
      // Only applies to bookmarks of type LINK
      return exists(
        db
          .select()
          .from(bookmarkLinks)
          .where(
            and(
              eq(bookmarkLinks.id, bookmarks.id),
              matcher.brokenLinks
                ? or(
                    eq(bookmarkLinks.crawlStatus, "failure"),
                    lt(bookmarkLinks.crawlStatusCode, 200),
                    gt(bookmarkLinks.crawlStatusCode, 299),
                  )
                : and(
                    eq(bookmarkLinks.crawlStatus, "success"),
                    gte(bookmarkLinks.crawlStatusCode, 200),
                    lte(bookmarkLinks.crawlStatusCode, 299),
                  ),
            ),
          ),
      );
    }
    case "source": {
      return matcher.inverse
        ? or(ne(bookmarks.source, matcher.source), isNull(bookmarks.source))!
        : eq(bookmarks.source, matcher.source);
    }
    case "and": {
      const conditions = await Promise.all(
        matcher.matchers.map((m) =>
          getBookmarksConditionFromMatcher(ctx, m, visitedListIds),
        ),
      );
      return combineBalanced(and, conditions);
    }
    case "or": {
      const conditions = await Promise.all(
        matcher.matchers.map((m) =>
          getBookmarksConditionFromMatcher(ctx, m, visitedListIds),
        ),
      );
      return combineBalanced(or, conditions);
    }
    default: {
      const _exhaustiveCheck: never = matcher;
      throw new Error("Unknown matcher type");
    }
  }
}

export async function getBookmarkIdsFromMatcher(
  ctx: AuthedContext,
  matcher: Matcher,
): Promise<string[]> {
  const condition = await getBookmarksConditionFromMatcher(ctx, matcher);
  const results = await ctx.db
    .select({ id: bookmarks.id })
    .from(bookmarks)
    .where(and(eq(bookmarks.userId, ctx.user.id), condition));
  return results.map((r) => r.id);
}