From 9ce6958ada86dade84e406e4e930775c59abf289 Mon Sep 17 00:00:00 2001 From: kamtschatka Date: Sun, 23 Jun 2024 13:08:27 +0200 Subject: refactor: extract assets into their own database table. #215 (#220) * Allow downloading more content from a webpage and index it #215 added a new table that contains the information about assets for link bookmarks created migration code that transfers the existing data into the new table * Allow downloading more content from a webpage and index it #215 removed the old asset columns from the database updated the UI to use the data from the linkBookmarkAssets array * generalize the assets table to not be linked in particular to links * fix migrations post merge * fix missing asset ids in the getBookmarks call --------- Co-authored-by: MohamedBassem --- packages/db/drizzle/0024_premium_hammerhead.sql | 31 + packages/db/drizzle/meta/0024_snapshot.json | 1060 +++++++++++++++++++++++ packages/db/drizzle/meta/_journal.json | 7 + packages/db/schema.ts | 40 +- packages/trpc/routers/bookmarks.ts | 85 +- 5 files changed, 1200 insertions(+), 23 deletions(-) create mode 100644 packages/db/drizzle/0024_premium_hammerhead.sql create mode 100644 packages/db/drizzle/meta/0024_snapshot.json (limited to 'packages') diff --git a/packages/db/drizzle/0024_premium_hammerhead.sql b/packages/db/drizzle/0024_premium_hammerhead.sql new file mode 100644 index 00000000..35fec295 --- /dev/null +++ b/packages/db/drizzle/0024_premium_hammerhead.sql @@ -0,0 +1,31 @@ +CREATE TABLE `assets` ( + `id` text PRIMARY KEY NOT NULL, + `assetType` text NOT NULL, + `bookmarkId` text NOT NULL, + FOREIGN KEY (`bookmarkId`) REFERENCES `bookmarks`(`id`) ON UPDATE no action ON DELETE cascade +); +--> statement-breakpoint +CREATE INDEX `assets_bookmarkId_idx` ON `assets` (`bookmarkId`); +--> statement-breakpoint +CREATE INDEX `assets_assetType_idx` ON `assets` (`assetType`); +--> statement-breakpoint +INSERT INTO `assets` (`id`, `assetType`, `bookmarkId`) +SELECT `screenshotAssetId`, 'linkScreenshot', `id` +FROM `bookmarkLinks` +WHERE screenshotAssetId IS NOT NULL; +--> statement-breakpoint +INSERT INTO `assets` (`id`, `assetType`, `bookmarkId`) +SELECT `fullPageArchiveAssetId`, 'linkFullPageArchive', `id` +FROM `bookmarkLinks` +WHERE `fullPageArchiveAssetId` IS NOT NULL; +--> statement-breakpoint +INSERT INTO `assets` (`id`, `assetType`, `bookmarkId`) +SELECT `imageAssetId`, 'linkBannerImage', `id` +FROM `bookmarkLinks` +WHERE `imageAssetId` IS NOT NULL; +--> statement-breakpoint +ALTER TABLE `bookmarkLinks` DROP COLUMN `screenshotAssetId`; +--> statement-breakpoint +ALTER TABLE `bookmarkLinks` DROP COLUMN `fullPageArchiveAssetId`; +--> statement-breakpoint +ALTER TABLE `bookmarkLinks` DROP COLUMN `imageAssetId`; diff --git a/packages/db/drizzle/meta/0024_snapshot.json b/packages/db/drizzle/meta/0024_snapshot.json new file mode 100644 index 00000000..e72605ad --- /dev/null +++ b/packages/db/drizzle/meta/0024_snapshot.json @@ -0,0 +1,1060 @@ +{ + "version": "5", + "dialect": "sqlite", + "id": "8a080f45-d358-465e-80b7-ae0c557e3872", + "prevId": "d33de747-6acb-4160-a5ec-a4a7adee3023", + "tables": { + "account": { + "name": "account", + "columns": { + "userId": { + "name": "userId", + "type": "text", + "primaryKey": false, + "notNull": true, + "autoincrement": false + }, + "type": { + "name": "type", + "type": "text", + "primaryKey": false, + "notNull": true, + "autoincrement": false + }, + "provider": { + "name": "provider", + "type": "text", + "primaryKey": false, + "notNull": true, + "autoincrement": false + }, + "providerAccountId": { + "name": "providerAccountId", + "type": "text", + "primaryKey": false, + "notNull": true, + "autoincrement": false + }, + "refresh_token": { + "name": "refresh_token", + "type": "text", + "primaryKey": false, + "notNull": false, + "autoincrement": false + }, + "access_token": { + "name": "access_token", + "type": "text", + "primaryKey": false, + "notNull": false, + "autoincrement": false + }, + "expires_at": { + "name": "expires_at", + "type": "integer", + "primaryKey": false, + "notNull": false, + "autoincrement": false + }, + "token_type": { + "name": "token_type", + "type": "text", + "primaryKey": false, + "notNull": false, + "autoincrement": false + }, + "scope": { + "name": "scope", + "type": "text", + "primaryKey": false, + "notNull": false, + "autoincrement": false + }, + "id_token": { + "name": "id_token", + "type": "text", + "primaryKey": false, + "notNull": false, + "autoincrement": false + }, + "session_state": { + "name": "session_state", + "type": "text", + "primaryKey": false, + "notNull": false, + "autoincrement": false + } + }, + "indexes": {}, + "foreignKeys": { + "account_userId_user_id_fk": { + "name": "account_userId_user_id_fk", + "tableFrom": "account", + "tableTo": "user", + "columnsFrom": [ + "userId" + ], + "columnsTo": [ + "id" + ], + "onDelete": "cascade", + "onUpdate": "no action" + } + }, + "compositePrimaryKeys": { + "account_provider_providerAccountId_pk": { + "columns": [ + "provider", + "providerAccountId" + ], + "name": "account_provider_providerAccountId_pk" + } + }, + "uniqueConstraints": {} + }, + "apiKey": { + "name": "apiKey", + "columns": { + "id": { + "name": "id", + "type": "text", + "primaryKey": true, + "notNull": true, + "autoincrement": false + }, + "name": { + "name": "name", + "type": "text", + "primaryKey": false, + "notNull": true, + "autoincrement": false + }, + "createdAt": { + "name": "createdAt", + "type": "integer", + "primaryKey": false, + "notNull": true, + "autoincrement": false + }, + "keyId": { + "name": "keyId", + "type": "text", + "primaryKey": false, + "notNull": true, + "autoincrement": false + }, + "keyHash": { + "name": "keyHash", + "type": "text", + "primaryKey": false, + "notNull": true, + "autoincrement": false + }, + "userId": { + "name": "userId", + "type": "text", + "primaryKey": false, + "notNull": true, + "autoincrement": false + } + }, + "indexes": { + "apiKey_keyId_unique": { + "name": "apiKey_keyId_unique", + "columns": [ + "keyId" + ], + "isUnique": true + }, + "apiKey_name_userId_unique": { + "name": "apiKey_name_userId_unique", + "columns": [ + "name", + "userId" + ], + "isUnique": true + } + }, + "foreignKeys": { + "apiKey_userId_user_id_fk": { + "name": "apiKey_userId_user_id_fk", + "tableFrom": "apiKey", + "tableTo": "user", + "columnsFrom": [ + "userId" + ], + "columnsTo": [ + "id" + ], + "onDelete": "cascade", + "onUpdate": "no action" + } + }, + "compositePrimaryKeys": {}, + "uniqueConstraints": {} + }, + "assets": { + "name": "assets", + "columns": { + "id": { + "name": "id", + "type": "text", + "primaryKey": true, + "notNull": true, + "autoincrement": false + }, + "assetType": { + "name": "assetType", + "type": "text", + "primaryKey": false, + "notNull": true, + "autoincrement": false + }, + "bookmarkId": { + "name": "bookmarkId", + "type": "text", + "primaryKey": false, + "notNull": true, + "autoincrement": false + } + }, + "indexes": { + "assets_bookmarkId_idx": { + "name": "assets_bookmarkId_idx", + "columns": [ + "bookmarkId" + ], + "isUnique": false + }, + "assets_assetType_idx": { + "name": "assets_assetType_idx", + "columns": [ + "assetType" + ], + "isUnique": false + } + }, + "foreignKeys": { + "assets_bookmarkId_bookmarks_id_fk": { + "name": "assets_bookmarkId_bookmarks_id_fk", + "tableFrom": "assets", + "tableTo": "bookmarks", + "columnsFrom": [ + "bookmarkId" + ], + "columnsTo": [ + "id" + ], + "onDelete": "cascade", + "onUpdate": "no action" + } + }, + "compositePrimaryKeys": {}, + "uniqueConstraints": {} + }, + "bookmarkAssets": { + "name": "bookmarkAssets", + "columns": { + "id": { + "name": "id", + "type": "text", + "primaryKey": true, + "notNull": true, + "autoincrement": false + }, + "assetType": { + "name": "assetType", + "type": "text", + "primaryKey": false, + "notNull": true, + "autoincrement": false + }, + "assetId": { + "name": "assetId", + "type": "text", + "primaryKey": false, + "notNull": true, + "autoincrement": false + }, + "content": { + "name": "content", + "type": "text", + "primaryKey": false, + "notNull": false, + "autoincrement": false + }, + "metadata": { + "name": "metadata", + "type": "text", + "primaryKey": false, + "notNull": false, + "autoincrement": false + }, + "fileName": { + "name": "fileName", + "type": "text", + "primaryKey": false, + "notNull": false, + "autoincrement": false + }, + "sourceUrl": { + "name": "sourceUrl", + "type": "text", + "primaryKey": false, + "notNull": false, + "autoincrement": false + } + }, + "indexes": {}, + "foreignKeys": { + "bookmarkAssets_id_bookmarks_id_fk": { + "name": "bookmarkAssets_id_bookmarks_id_fk", + "tableFrom": "bookmarkAssets", + "tableTo": "bookmarks", + "columnsFrom": [ + "id" + ], + "columnsTo": [ + "id" + ], + "onDelete": "cascade", + "onUpdate": "no action" + } + }, + "compositePrimaryKeys": {}, + "uniqueConstraints": {} + }, + "bookmarkLinks": { + "name": "bookmarkLinks", + "columns": { + "id": { + "name": "id", + "type": "text", + "primaryKey": true, + "notNull": true, + "autoincrement": false + }, + "url": { + "name": "url", + "type": "text", + "primaryKey": false, + "notNull": true, + "autoincrement": false + }, + "title": { + "name": "title", + "type": "text", + "primaryKey": false, + "notNull": false, + "autoincrement": false + }, + "description": { + "name": "description", + "type": "text", + "primaryKey": false, + "notNull": false, + "autoincrement": false + }, + "imageUrl": { + "name": "imageUrl", + "type": "text", + "primaryKey": false, + "notNull": false, + "autoincrement": false + }, + "favicon": { + "name": "favicon", + "type": "text", + "primaryKey": false, + "notNull": false, + "autoincrement": false + }, + "content": { + "name": "content", + "type": "text", + "primaryKey": false, + "notNull": false, + "autoincrement": false + }, + "htmlContent": { + "name": "htmlContent", + "type": "text", + "primaryKey": false, + "notNull": false, + "autoincrement": false + }, + "crawledAt": { + "name": "crawledAt", + "type": "integer", + "primaryKey": false, + "notNull": false, + "autoincrement": false + }, + "crawlStatus": { + "name": "crawlStatus", + "type": "text", + "primaryKey": false, + "notNull": false, + "autoincrement": false, + "default": "'pending'" + } + }, + "indexes": { + "bookmarkLinks_url_idx": { + "name": "bookmarkLinks_url_idx", + "columns": [ + "url" + ], + "isUnique": false + } + }, + "foreignKeys": { + "bookmarkLinks_id_bookmarks_id_fk": { + "name": "bookmarkLinks_id_bookmarks_id_fk", + "tableFrom": "bookmarkLinks", + "tableTo": "bookmarks", + "columnsFrom": [ + "id" + ], + "columnsTo": [ + "id" + ], + "onDelete": "cascade", + "onUpdate": "no action" + } + }, + "compositePrimaryKeys": {}, + "uniqueConstraints": {} + }, + "bookmarkLists": { + "name": "bookmarkLists", + "columns": { + "id": { + "name": "id", + "type": "text", + "primaryKey": true, + "notNull": true, + "autoincrement": false + }, + "name": { + "name": "name", + "type": "text", + "primaryKey": false, + "notNull": true, + "autoincrement": false + }, + "icon": { + "name": "icon", + "type": "text", + "primaryKey": false, + "notNull": true, + "autoincrement": false + }, + "createdAt": { + "name": "createdAt", + "type": "integer", + "primaryKey": false, + "notNull": true, + "autoincrement": false + }, + "userId": { + "name": "userId", + "type": "text", + "primaryKey": false, + "notNull": true, + "autoincrement": false + }, + "parentId": { + "name": "parentId", + "type": "text", + "primaryKey": false, + "notNull": false, + "autoincrement": false + } + }, + "indexes": { + "bookmarkLists_userId_idx": { + "name": "bookmarkLists_userId_idx", + "columns": [ + "userId" + ], + "isUnique": false + } + }, + "foreignKeys": { + "bookmarkLists_userId_user_id_fk": { + "name": "bookmarkLists_userId_user_id_fk", + "tableFrom": "bookmarkLists", + "tableTo": "user", + "columnsFrom": [ + "userId" + ], + "columnsTo": [ + "id" + ], + "onDelete": "cascade", + "onUpdate": "no action" + }, + "bookmarkLists_parentId_bookmarkLists_id_fk": { + "name": "bookmarkLists_parentId_bookmarkLists_id_fk", + "tableFrom": "bookmarkLists", + "tableTo": "bookmarkLists", + "columnsFrom": [ + "parentId" + ], + "columnsTo": [ + "id" + ], + "onDelete": "set null", + "onUpdate": "no action" + } + }, + "compositePrimaryKeys": {}, + "uniqueConstraints": {} + }, + "bookmarkTags": { + "name": "bookmarkTags", + "columns": { + "id": { + "name": "id", + "type": "text", + "primaryKey": true, + "notNull": true, + "autoincrement": false + }, + "name": { + "name": "name", + "type": "text", + "primaryKey": false, + "notNull": true, + "autoincrement": false + }, + "createdAt": { + "name": "createdAt", + "type": "integer", + "primaryKey": false, + "notNull": true, + "autoincrement": false + }, + "userId": { + "name": "userId", + "type": "text", + "primaryKey": false, + "notNull": true, + "autoincrement": false + } + }, + "indexes": { + "bookmarkTags_name_idx": { + "name": "bookmarkTags_name_idx", + "columns": [ + "name" + ], + "isUnique": false + }, + "bookmarkTags_userId_idx": { + "name": "bookmarkTags_userId_idx", + "columns": [ + "userId" + ], + "isUnique": false + }, + "bookmarkTags_userId_name_unique": { + "name": "bookmarkTags_userId_name_unique", + "columns": [ + "userId", + "name" + ], + "isUnique": true + } + }, + "foreignKeys": { + "bookmarkTags_userId_user_id_fk": { + "name": "bookmarkTags_userId_user_id_fk", + "tableFrom": "bookmarkTags", + "tableTo": "user", + "columnsFrom": [ + "userId" + ], + "columnsTo": [ + "id" + ], + "onDelete": "cascade", + "onUpdate": "no action" + } + }, + "compositePrimaryKeys": {}, + "uniqueConstraints": {} + }, + "bookmarkTexts": { + "name": "bookmarkTexts", + "columns": { + "id": { + "name": "id", + "type": "text", + "primaryKey": true, + "notNull": true, + "autoincrement": false + }, + "text": { + "name": "text", + "type": "text", + "primaryKey": false, + "notNull": false, + "autoincrement": false + } + }, + "indexes": {}, + "foreignKeys": { + "bookmarkTexts_id_bookmarks_id_fk": { + "name": "bookmarkTexts_id_bookmarks_id_fk", + "tableFrom": "bookmarkTexts", + "tableTo": "bookmarks", + "columnsFrom": [ + "id" + ], + "columnsTo": [ + "id" + ], + "onDelete": "cascade", + "onUpdate": "no action" + } + }, + "compositePrimaryKeys": {}, + "uniqueConstraints": {} + }, + "bookmarks": { + "name": "bookmarks", + "columns": { + "id": { + "name": "id", + "type": "text", + "primaryKey": true, + "notNull": true, + "autoincrement": false + }, + "createdAt": { + "name": "createdAt", + "type": "integer", + "primaryKey": false, + "notNull": true, + "autoincrement": false + }, + "title": { + "name": "title", + "type": "text", + "primaryKey": false, + "notNull": false, + "autoincrement": false + }, + "archived": { + "name": "archived", + "type": "integer", + "primaryKey": false, + "notNull": true, + "autoincrement": false, + "default": false + }, + "favourited": { + "name": "favourited", + "type": "integer", + "primaryKey": false, + "notNull": true, + "autoincrement": false, + "default": false + }, + "userId": { + "name": "userId", + "type": "text", + "primaryKey": false, + "notNull": true, + "autoincrement": false + }, + "taggingStatus": { + "name": "taggingStatus", + "type": "text", + "primaryKey": false, + "notNull": false, + "autoincrement": false, + "default": "'pending'" + }, + "note": { + "name": "note", + "type": "text", + "primaryKey": false, + "notNull": false, + "autoincrement": false + } + }, + "indexes": { + "bookmarks_userId_idx": { + "name": "bookmarks_userId_idx", + "columns": [ + "userId" + ], + "isUnique": false + }, + "bookmarks_archived_idx": { + "name": "bookmarks_archived_idx", + "columns": [ + "archived" + ], + "isUnique": false + }, + "bookmarks_favourited_idx": { + "name": "bookmarks_favourited_idx", + "columns": [ + "favourited" + ], + "isUnique": false + }, + "bookmarks_createdAt_idx": { + "name": "bookmarks_createdAt_idx", + "columns": [ + "createdAt" + ], + "isUnique": false + } + }, + "foreignKeys": { + "bookmarks_userId_user_id_fk": { + "name": "bookmarks_userId_user_id_fk", + "tableFrom": "bookmarks", + "tableTo": "user", + "columnsFrom": [ + "userId" + ], + "columnsTo": [ + "id" + ], + "onDelete": "cascade", + "onUpdate": "no action" + } + }, + "compositePrimaryKeys": {}, + "uniqueConstraints": {} + }, + "bookmarksInLists": { + "name": "bookmarksInLists", + "columns": { + "bookmarkId": { + "name": "bookmarkId", + "type": "text", + "primaryKey": false, + "notNull": true, + "autoincrement": false + }, + "listId": { + "name": "listId", + "type": "text", + "primaryKey": false, + "notNull": true, + "autoincrement": false + }, + "addedAt": { + "name": "addedAt", + "type": "integer", + "primaryKey": false, + "notNull": false, + "autoincrement": false + } + }, + "indexes": { + "bookmarksInLists_bookmarkId_idx": { + "name": "bookmarksInLists_bookmarkId_idx", + "columns": [ + "bookmarkId" + ], + "isUnique": false + }, + "bookmarksInLists_listId_idx": { + "name": "bookmarksInLists_listId_idx", + "columns": [ + "listId" + ], + "isUnique": false + } + }, + "foreignKeys": { + "bookmarksInLists_bookmarkId_bookmarks_id_fk": { + "name": "bookmarksInLists_bookmarkId_bookmarks_id_fk", + "tableFrom": "bookmarksInLists", + "tableTo": "bookmarks", + "columnsFrom": [ + "bookmarkId" + ], + "columnsTo": [ + "id" + ], + "onDelete": "cascade", + "onUpdate": "no action" + }, + "bookmarksInLists_listId_bookmarkLists_id_fk": { + "name": "bookmarksInLists_listId_bookmarkLists_id_fk", + "tableFrom": "bookmarksInLists", + "tableTo": "bookmarkLists", + "columnsFrom": [ + "listId" + ], + "columnsTo": [ + "id" + ], + "onDelete": "cascade", + "onUpdate": "no action" + } + }, + "compositePrimaryKeys": { + "bookmarksInLists_bookmarkId_listId_pk": { + "columns": [ + "bookmarkId", + "listId" + ], + "name": "bookmarksInLists_bookmarkId_listId_pk" + } + }, + "uniqueConstraints": {} + }, + "session": { + "name": "session", + "columns": { + "sessionToken": { + "name": "sessionToken", + "type": "text", + "primaryKey": true, + "notNull": true, + "autoincrement": false + }, + "userId": { + "name": "userId", + "type": "text", + "primaryKey": false, + "notNull": true, + "autoincrement": false + }, + "expires": { + "name": "expires", + "type": "integer", + "primaryKey": false, + "notNull": true, + "autoincrement": false + } + }, + "indexes": {}, + "foreignKeys": { + "session_userId_user_id_fk": { + "name": "session_userId_user_id_fk", + "tableFrom": "session", + "tableTo": "user", + "columnsFrom": [ + "userId" + ], + "columnsTo": [ + "id" + ], + "onDelete": "cascade", + "onUpdate": "no action" + } + }, + "compositePrimaryKeys": {}, + "uniqueConstraints": {} + }, + "tagsOnBookmarks": { + "name": "tagsOnBookmarks", + "columns": { + "bookmarkId": { + "name": "bookmarkId", + "type": "text", + "primaryKey": false, + "notNull": true, + "autoincrement": false + }, + "tagId": { + "name": "tagId", + "type": "text", + "primaryKey": false, + "notNull": true, + "autoincrement": false + }, + "attachedAt": { + "name": "attachedAt", + "type": "integer", + "primaryKey": false, + "notNull": false, + "autoincrement": false + }, + "attachedBy": { + "name": "attachedBy", + "type": "text", + "primaryKey": false, + "notNull": true, + "autoincrement": false + } + }, + "indexes": { + "tagsOnBookmarks_tagId_idx": { + "name": "tagsOnBookmarks_tagId_idx", + "columns": [ + "bookmarkId" + ], + "isUnique": false + }, + "tagsOnBookmarks_bookmarkId_idx": { + "name": "tagsOnBookmarks_bookmarkId_idx", + "columns": [ + "bookmarkId" + ], + "isUnique": false + } + }, + "foreignKeys": { + "tagsOnBookmarks_bookmarkId_bookmarks_id_fk": { + "name": "tagsOnBookmarks_bookmarkId_bookmarks_id_fk", + "tableFrom": "tagsOnBookmarks", + "tableTo": "bookmarks", + "columnsFrom": [ + "bookmarkId" + ], + "columnsTo": [ + "id" + ], + "onDelete": "cascade", + "onUpdate": "no action" + }, + "tagsOnBookmarks_tagId_bookmarkTags_id_fk": { + "name": "tagsOnBookmarks_tagId_bookmarkTags_id_fk", + "tableFrom": "tagsOnBookmarks", + "tableTo": "bookmarkTags", + "columnsFrom": [ + "tagId" + ], + "columnsTo": [ + "id" + ], + "onDelete": "cascade", + "onUpdate": "no action" + } + }, + "compositePrimaryKeys": { + "tagsOnBookmarks_bookmarkId_tagId_pk": { + "columns": [ + "bookmarkId", + "tagId" + ], + "name": "tagsOnBookmarks_bookmarkId_tagId_pk" + } + }, + "uniqueConstraints": {} + }, + "user": { + "name": "user", + "columns": { + "id": { + "name": "id", + "type": "text", + "primaryKey": true, + "notNull": true, + "autoincrement": false + }, + "name": { + "name": "name", + "type": "text", + "primaryKey": false, + "notNull": true, + "autoincrement": false + }, + "email": { + "name": "email", + "type": "text", + "primaryKey": false, + "notNull": true, + "autoincrement": false + }, + "emailVerified": { + "name": "emailVerified", + "type": "integer", + "primaryKey": false, + "notNull": false, + "autoincrement": false + }, + "image": { + "name": "image", + "type": "text", + "primaryKey": false, + "notNull": false, + "autoincrement": false + }, + "password": { + "name": "password", + "type": "text", + "primaryKey": false, + "notNull": false, + "autoincrement": false + }, + "role": { + "name": "role", + "type": "text", + "primaryKey": false, + "notNull": false, + "autoincrement": false, + "default": "'user'" + } + }, + "indexes": { + "user_email_unique": { + "name": "user_email_unique", + "columns": [ + "email" + ], + "isUnique": true + } + }, + "foreignKeys": {}, + "compositePrimaryKeys": {}, + "uniqueConstraints": {} + }, + "verificationToken": { + "name": "verificationToken", + "columns": { + "identifier": { + "name": "identifier", + "type": "text", + "primaryKey": false, + "notNull": true, + "autoincrement": false + }, + "token": { + "name": "token", + "type": "text", + "primaryKey": false, + "notNull": true, + "autoincrement": false + }, + "expires": { + "name": "expires", + "type": "integer", + "primaryKey": false, + "notNull": true, + "autoincrement": false + } + }, + "indexes": {}, + "foreignKeys": {}, + "compositePrimaryKeys": { + "verificationToken_identifier_token_pk": { + "columns": [ + "identifier", + "token" + ], + "name": "verificationToken_identifier_token_pk" + } + }, + "uniqueConstraints": {} + } + }, + "enums": {}, + "_meta": { + "schemas": {}, + "tables": {}, + "columns": {} + } +} \ No newline at end of file diff --git a/packages/db/drizzle/meta/_journal.json b/packages/db/drizzle/meta/_journal.json index 75cbfc09..8196ad5f 100644 --- a/packages/db/drizzle/meta/_journal.json +++ b/packages/db/drizzle/meta/_journal.json @@ -169,6 +169,13 @@ "when": 1717960986361, "tag": "0023_late_night_nurse", "breakpoints": true + }, + { + "idx": 24, + "version": "5", + "when": 1719135100480, + "tag": "0024_premium_hammerhead", + "breakpoints": true } ] } \ No newline at end of file diff --git a/packages/db/schema.ts b/packages/db/schema.ts index 07f1686e..63d23782 100644 --- a/packages/db/schema.ts +++ b/packages/db/schema.ts @@ -143,9 +143,6 @@ export const bookmarkLinks = sqliteTable( favicon: text("favicon"), content: text("content"), htmlContent: text("htmlContent"), - screenshotAssetId: text("screenshotAssetId"), - fullPageArchiveAssetId: text("fullPageArchiveAssetId"), - imageAssetId: text("imageAssetId"), crawledAt: integer("crawledAt", { mode: "timestamp" }), crawlStatus: text("crawlStatus", { enum: ["pending", "failure", "success"], @@ -158,6 +155,35 @@ export const bookmarkLinks = sqliteTable( }, ); +export const enum AssetTypes { + LINK_BANNER_IMAGE = "linkBannerImage", + LINK_SCREENSHOT = "linkScreenshot", + LINK_FULL_PAGE_ARCHIVE = "linkFullPageArchive", +} + +export const assets = sqliteTable( + "assets", + { + // Asset ids don't have a default function as they are generated by the caller + id: text("id").notNull().primaryKey(), + assetType: text("assetType", { + enum: [ + AssetTypes.LINK_BANNER_IMAGE, + AssetTypes.LINK_SCREENSHOT, + AssetTypes.LINK_FULL_PAGE_ARCHIVE, + ], + }).notNull(), + bookmarkId: text("bookmarkId") + .notNull() + .references(() => bookmarks.id, { onDelete: "cascade" }), + }, + + (tb) => ({ + bookmarkIdIdx: index("assets_bookmarkId_idx").on(tb.bookmarkId), + assetTypeIdx: index("assets_assetType_idx").on(tb.assetType), + }), +); + export const bookmarkTexts = sqliteTable("bookmarkTexts", { id: text("id") .notNull() @@ -292,6 +318,14 @@ export const bookmarkRelations = relations(bookmarks, ({ many, one }) => ({ }), tagsOnBookmarks: many(tagsOnBookmarks), bookmarksInLists: many(bookmarksInLists), + assets: many(assets), +})); + +export const assetRelations = relations(assets, ({ one }) => ({ + bookmark: one(bookmarks, { + fields: [assets.bookmarkId], + references: [bookmarks.id], + }), })); export const bookmarkTagsRelations = relations( diff --git a/packages/trpc/routers/bookmarks.ts b/packages/trpc/routers/bookmarks.ts index e083f83c..2175f704 100644 --- a/packages/trpc/routers/bookmarks.ts +++ b/packages/trpc/routers/bookmarks.ts @@ -10,6 +10,8 @@ import type { import type { ZBookmarkTags } from "@hoarder/shared/types/tags"; import { db as DONT_USE_db } from "@hoarder/db"; import { + assets, + AssetTypes, bookmarkAssets, bookmarkLinks, bookmarks, @@ -71,6 +73,41 @@ export const ensureBookmarkOwnership = experimental_trpcMiddleware<{ return opts.next(); }); +function assetTypeToBookmarkField( + asset: + | { + id: string; + assetType: AssetTypes; + } + | undefined, +) { + if (!asset) { + return undefined; + } + switch (asset.assetType) { + case AssetTypes.LINK_SCREENSHOT: + return { screenshotAssetId: asset.id }; + case AssetTypes.LINK_FULL_PAGE_ARCHIVE: + return { fullPageArchiveAssetId: asset.id }; + case AssetTypes.LINK_BANNER_IMAGE: + return { imageAssetId: asset.id }; + } +} + +function getBookmarkAssets(assets: { id: string; assetType: AssetTypes }[]) { + return { + ...assetTypeToBookmarkField( + assets.find((a) => a.assetType == AssetTypes.LINK_SCREENSHOT), + ), + ...assetTypeToBookmarkField( + assets.find((a) => a.assetType == AssetTypes.LINK_FULL_PAGE_ARCHIVE), + ), + ...assetTypeToBookmarkField( + assets.find((a) => a.assetType == AssetTypes.LINK_BANNER_IMAGE), + ), + }; +} + async function getBookmark(ctx: AuthedContext, bookmarkId: string) { const bookmark = await ctx.db.query.bookmarks.findFirst({ where: and(eq(bookmarks.userId, ctx.user.id), eq(bookmarks.id, bookmarkId)), @@ -83,6 +120,7 @@ async function getBookmark(ctx: AuthedContext, bookmarkId: string) { link: true, text: true, asset: true, + assets: true, }, }); if (!bookmark) { @@ -121,6 +159,7 @@ async function dummyDrizzleReturnType() { link: true, text: true, asset: true, + assets: true, }, }); if (!x) { @@ -134,36 +173,32 @@ type BookmarkQueryReturnType = Awaited< >; async function cleanupAssetForBookmark( - bookmark: Pick, + bookmark: Pick, ) { - const assetIds = []; + const assetIds: Set = new Set( + bookmark.assets.map((a) => a.id), + ); + // Todo: Remove when the bookmark asset is also in the assets table if (bookmark.asset) { - assetIds.push(bookmark.asset.assetId); - } - if (bookmark.link) { - if (bookmark.link.screenshotAssetId) { - assetIds.push(bookmark.link.screenshotAssetId); - } - if (bookmark.link.imageAssetId) { - assetIds.push(bookmark.link.imageAssetId); - } - if (bookmark.link.fullPageArchiveAssetId) { - assetIds.push(bookmark.link.fullPageArchiveAssetId); - } + assetIds.add(bookmark.asset.assetId); } await Promise.all( - assetIds.map((assetId) => + Array.from(assetIds).map((assetId) => deleteAsset({ userId: bookmark.userId, assetId }), ), ); } function toZodSchema(bookmark: BookmarkQueryReturnType): ZBookmark { - const { tagsOnBookmarks, link, text, asset, ...rest } = bookmark; + const { tagsOnBookmarks, link, text, asset, assets, ...rest } = bookmark; let content: ZBookmarkContent; if (link) { - content = { type: "link", ...link }; + content = { + type: "link", + ...getBookmarkAssets(assets), + ...link, + }; } else if (text) { content = { type: "text", text: text.text ?? "" }; } else if (asset) { @@ -200,7 +235,7 @@ export const bookmarksAppRouter = router({ ) .mutation(async ({ input, ctx }) => { if (input.type == "link") { - // This doesn't 100% protect from duplicates because of races but it's more than enough for this usecase. + // This doesn't 100% protect from duplicates because of races, but it's more than enough for this usecase. const alreadyExists = await attemptToDedupLink(ctx, input.url); if (alreadyExists) { return { ...alreadyExists, alreadyExists: true }; @@ -369,6 +404,7 @@ export const bookmarksAppRouter = router({ with: { asset: true, link: true, + assets: true, }, }); const deleted = await ctx.db @@ -383,8 +419,8 @@ export const bookmarksAppRouter = router({ if (deleted.changes > 0 && bookmark) { await cleanupAssetForBookmark({ asset: bookmark.asset, - link: bookmark.link, userId: ctx.user.id, + assets: bookmark.assets, }); } }), @@ -453,6 +489,7 @@ export const bookmarksAppRouter = router({ link: true, text: true, asset: true, + assets: true, }, }); results.sort((a, b) => idToRank[b.id] - idToRank[a.id]); @@ -536,6 +573,7 @@ export const bookmarksAppRouter = router({ .leftJoin(bookmarkLinks, eq(bookmarkLinks.id, sq.id)) .leftJoin(bookmarkTexts, eq(bookmarkTexts.id, sq.id)) .leftJoin(bookmarkAssets, eq(bookmarkAssets.id, sq.id)) + .leftJoin(assets, eq(assets.bookmarkId, sq.id)) .orderBy(desc(sq.createdAt), desc(sq.id)); const bookmarksRes = results.reduce>( @@ -575,6 +613,13 @@ export const bookmarksAppRouter = router({ }); } + if (row.assets) { + acc[bookmarkId].content = { + ...acc[bookmarkId].content, + ...assetTypeToBookmarkField(row.assets), + }; + } + return acc; }, {}, @@ -629,7 +674,7 @@ export const bookmarksAppRouter = router({ ) .use(ensureBookmarkOwnership) .mutation(async ({ input, ctx }) => { - return await ctx.db.transaction(async (tx) => { + return ctx.db.transaction(async (tx) => { // Detaches if (input.detach.length > 0) { await tx.delete(tagsOnBookmarks).where( -- cgit v1.2.3-70-g09d2