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 <me@mbassem.com>

1 files changed, 31 insertions, 0 deletions

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`;