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 +++++++++++++++++++++++++ 1 file changed, 31 insertions(+) create mode 100644 packages/db/drizzle/0024_premium_hammerhead.sql (limited to 'packages/db/drizzle/0024_premium_hammerhead.sql') 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`; -- cgit v1.2.3-70-g09d2