From 8d1bb2f59a2a258a13e3675645b24b31fbe1a8b0 Mon Sep 17 00:00:00 2001 From: Dhravya Shah Date: Tue, 23 Jul 2024 09:49:13 -0500 Subject: setup migrations --- apps/web/migrations/0000_setup.sql | 133 ++++++++++++++++++++++++++++ apps/web/migrations/0000_stormy_catseye.sql | 132 --------------------------- apps/web/migrations/meta/0000_snapshot.json | 12 ++- apps/web/migrations/meta/_journal.json | 4 +- apps/web/server/db/schema.ts | 2 +- 5 files changed, 146 insertions(+), 137 deletions(-) create mode 100644 apps/web/migrations/0000_setup.sql delete mode 100644 apps/web/migrations/0000_stormy_catseye.sql diff --git a/apps/web/migrations/0000_setup.sql b/apps/web/migrations/0000_setup.sql new file mode 100644 index 00000000..65a41795 --- /dev/null +++ b/apps/web/migrations/0000_setup.sql @@ -0,0 +1,133 @@ +CREATE TABLE `account` ( + `userId` text NOT NULL, + `type` text NOT NULL, + `provider` text NOT NULL, + `providerAccountId` text NOT NULL, + `refresh_token` text, + `access_token` text, + `expires_at` integer, + `token_type` text, + `scope` text, + `id_token` text, + `session_state` text, + PRIMARY KEY(`provider`, `providerAccountId`), + FOREIGN KEY (`userId`) REFERENCES `user`(`id`) ON UPDATE no action ON DELETE cascade +); +--> statement-breakpoint +CREATE TABLE `authenticator` ( + `credentialID` text NOT NULL, + `userId` text NOT NULL, + `providerAccountId` text NOT NULL, + `credentialPublicKey` text NOT NULL, + `counter` integer NOT NULL, + `credentialDeviceType` text NOT NULL, + `credentialBackedUp` integer NOT NULL, + `transports` text, + PRIMARY KEY(`credentialID`, `userId`), + FOREIGN KEY (`userId`) REFERENCES `user`(`id`) ON UPDATE no action ON DELETE cascade +); +--> statement-breakpoint +CREATE TABLE `canvas` ( + `id` text PRIMARY KEY NOT NULL, + `title` text DEFAULT 'Untitled' NOT NULL, + `description` text DEFAULT 'Untitled' NOT NULL, + `url` text DEFAULT '' NOT NULL, + `userId` text NOT NULL, + FOREIGN KEY (`userId`) REFERENCES `user`(`id`) ON UPDATE no action ON DELETE cascade +); +--> statement-breakpoint +CREATE TABLE `chatHistory` ( + `id` integer PRIMARY KEY AUTOINCREMENT NOT NULL, + `threadId` text NOT NULL, + `question` text NOT NULL, + `answerParts` text, + `answerSources` text, + `answerJustification` text, + FOREIGN KEY (`threadId`) REFERENCES `chatThread`(`id`) ON UPDATE no action ON DELETE cascade +); +--> statement-breakpoint +CREATE TABLE `chatThread` ( + `id` text PRIMARY KEY NOT NULL, + `firstMessage` text NOT NULL, + `userId` text NOT NULL, + FOREIGN KEY (`userId`) REFERENCES `user`(`id`) ON UPDATE no action ON DELETE cascade +); +--> statement-breakpoint +CREATE TABLE `contentToSpace` ( + `contentId` integer NOT NULL, + `spaceId` integer NOT NULL, + PRIMARY KEY(`contentId`, `spaceId`), + FOREIGN KEY (`contentId`) REFERENCES `storedContent`(`id`) ON UPDATE no action ON DELETE cascade, + FOREIGN KEY (`spaceId`) REFERENCES `space`(`id`) ON UPDATE no action ON DELETE cascade +); +--> statement-breakpoint +CREATE TABLE `session` ( + `sessionToken` text PRIMARY KEY NOT NULL, + `userId` text NOT NULL, + `expires` integer NOT NULL, + FOREIGN KEY (`userId`) REFERENCES `user`(`id`) ON UPDATE no action ON DELETE cascade +); +--> statement-breakpoint +CREATE TABLE `space` ( + `id` integer PRIMARY KEY AUTOINCREMENT NOT NULL, + `name` text DEFAULT 'none' NOT NULL, + `user` text(255), + `createdAt` integer NOT NULL, + `numItems` integer DEFAULT 0 NOT NULL, + FOREIGN KEY (`user`) REFERENCES `user`(`id`) ON UPDATE no action ON DELETE cascade +); +--> statement-breakpoint +CREATE TABLE `spacesAccess` ( + `spaceId` integer NOT NULL, + `userEmail` text NOT NULL, + PRIMARY KEY(`spaceId`, `userEmail`), + FOREIGN KEY (`spaceId`) REFERENCES `space`(`id`) ON UPDATE no action ON DELETE cascade +); +--> statement-breakpoint +CREATE TABLE `storedContent` ( + `id` integer PRIMARY KEY AUTOINCREMENT NOT NULL, + `content` text NOT NULL, + `title` text(255), + `description` text(255), + `url` text NOT NULL, + `savedAt` integer NOT NULL, + `baseUrl` text(255), + `ogImage` text(255), + `type` text DEFAULT 'page', + `image` text(255), + `user` text, + `noteId` integer, + FOREIGN KEY (`user`) REFERENCES `user`(`id`) ON UPDATE no action ON DELETE cascade +); +--> statement-breakpoint +CREATE TABLE `user` ( + `id` text PRIMARY KEY NOT NULL, + `name` text, + `email` text NOT NULL, + `emailVerified` integer, + `image` text, + `telegramId` text +); +--> statement-breakpoint +CREATE TABLE `verificationToken` ( + `identifier` text NOT NULL, + `token` text NOT NULL, + `expires` integer NOT NULL, + PRIMARY KEY(`identifier`, `token`) +); +--> statement-breakpoint +CREATE UNIQUE INDEX `authenticator_credentialID_unique` ON `authenticator` (`credentialID`);--> statement-breakpoint +CREATE INDEX `canvas_user_userId` ON `canvas` (`userId`);--> statement-breakpoint +CREATE INDEX `chatHistory_thread_idx` ON `chatHistory` (`threadId`);--> statement-breakpoint +CREATE INDEX `chatThread_user_idx` ON `chatThread` (`userId`);--> statement-breakpoint +CREATE UNIQUE INDEX `space_name_unique` ON `space` (`name`);--> statement-breakpoint +CREATE INDEX `spaces_name_idx` ON `space` (`name`);--> statement-breakpoint +CREATE INDEX `spaces_user_idx` ON `space` (`user`);--> statement-breakpoint +CREATE UNIQUE INDEX `storedContent_baseUrl_unique` ON `storedContent` (`baseUrl`);--> statement-breakpoint +CREATE INDEX `storedContent_url_idx` ON `storedContent` (`url`);--> statement-breakpoint +CREATE INDEX `storedContent_savedAt_idx` ON `storedContent` (`savedAt`);--> statement-breakpoint +CREATE INDEX `storedContent_title_idx` ON `storedContent` (`title`);--> statement-breakpoint +CREATE INDEX `storedContent_user_idx` ON `storedContent` (`user`);--> statement-breakpoint +CREATE INDEX `users_email_idx` ON `user` (`email`);--> statement-breakpoint +CREATE INDEX `users_telegram_idx` ON `user` (`telegramId`);--> statement-breakpoint +CREATE INDEX `users_id_idx` ON `user` (`id`); \ No newline at end of file diff --git a/apps/web/migrations/0000_stormy_catseye.sql b/apps/web/migrations/0000_stormy_catseye.sql deleted file mode 100644 index a9b54dce..00000000 --- a/apps/web/migrations/0000_stormy_catseye.sql +++ /dev/null @@ -1,132 +0,0 @@ -CREATE TABLE `account` ( - `userId` text NOT NULL, - `type` text NOT NULL, - `provider` text NOT NULL, - `providerAccountId` text NOT NULL, - `refresh_token` text, - `access_token` text, - `expires_at` integer, - `token_type` text, - `scope` text, - `id_token` text, - `session_state` text, - PRIMARY KEY(`provider`, `providerAccountId`), - FOREIGN KEY (`userId`) REFERENCES `user`(`id`) ON UPDATE no action ON DELETE cascade -); ---> statement-breakpoint -CREATE TABLE `authenticator` ( - `credentialID` text NOT NULL, - `userId` text NOT NULL, - `providerAccountId` text NOT NULL, - `credentialPublicKey` text NOT NULL, - `counter` integer NOT NULL, - `credentialDeviceType` text NOT NULL, - `credentialBackedUp` integer NOT NULL, - `transports` text, - PRIMARY KEY(`credentialID`, `userId`), - FOREIGN KEY (`userId`) REFERENCES `user`(`id`) ON UPDATE no action ON DELETE cascade -); ---> statement-breakpoint -CREATE TABLE `canvas` ( - `id` text PRIMARY KEY NOT NULL, - `title` text DEFAULT 'Untitled' NOT NULL, - `description` text DEFAULT 'Untitled' NOT NULL, - `url` text DEFAULT '' NOT NULL, - `userId` text NOT NULL, - FOREIGN KEY (`userId`) REFERENCES `user`(`id`) ON UPDATE no action ON DELETE cascade -); ---> statement-breakpoint -CREATE TABLE `chatHistory` ( - `id` integer PRIMARY KEY AUTOINCREMENT NOT NULL, - `threadId` text NOT NULL, - `question` text NOT NULL, - `answerParts` text, - `answerSources` text, - `answerJustification` text, - FOREIGN KEY (`threadId`) REFERENCES `chatThread`(`id`) ON UPDATE no action ON DELETE cascade -); ---> statement-breakpoint -CREATE TABLE `chatThread` ( - `id` text PRIMARY KEY NOT NULL, - `firstMessage` text NOT NULL, - `userId` text NOT NULL, - FOREIGN KEY (`userId`) REFERENCES `user`(`id`) ON UPDATE no action ON DELETE cascade -); ---> statement-breakpoint -CREATE TABLE `contentToSpace` ( - `contentId` integer NOT NULL, - `spaceId` integer NOT NULL, - PRIMARY KEY(`contentId`, `spaceId`), - FOREIGN KEY (`contentId`) REFERENCES `storedContent`(`id`) ON UPDATE no action ON DELETE cascade, - FOREIGN KEY (`spaceId`) REFERENCES `space`(`id`) ON UPDATE no action ON DELETE cascade -); ---> statement-breakpoint -CREATE TABLE `session` ( - `sessionToken` text PRIMARY KEY NOT NULL, - `userId` text NOT NULL, - `expires` integer NOT NULL, - FOREIGN KEY (`userId`) REFERENCES `user`(`id`) ON UPDATE no action ON DELETE cascade -); ---> statement-breakpoint -CREATE TABLE `space` ( - `id` integer PRIMARY KEY AUTOINCREMENT NOT NULL, - `name` text NOT NULL, - `user` text(255), - `createdAt` integer NOT NULL, - `numItems` integer DEFAULT 0 NOT NULL, - FOREIGN KEY (`user`) REFERENCES `user`(`id`) ON UPDATE no action ON DELETE cascade -); ---> statement-breakpoint -CREATE TABLE `spacesAccess` ( - `spaceId` integer NOT NULL, - `userEmail` text NOT NULL, - PRIMARY KEY(`spaceId`, `userEmail`), - FOREIGN KEY (`spaceId`) REFERENCES `space`(`id`) ON UPDATE no action ON DELETE cascade -); ---> statement-breakpoint -CREATE TABLE `storedContent` ( - `id` integer PRIMARY KEY AUTOINCREMENT NOT NULL, - `content` text NOT NULL, - `title` text(255), - `description` text(255), - `url` text NOT NULL, - `savedAt` integer NOT NULL, - `baseUrl` text(255), - `ogImage` text(255), - `type` text DEFAULT 'page', - `image` text(255), - `user` text, - `noteId` integer, - FOREIGN KEY (`user`) REFERENCES `user`(`id`) ON UPDATE no action ON DELETE cascade -); ---> statement-breakpoint -CREATE TABLE `user` ( - `id` text PRIMARY KEY NOT NULL, - `name` text, - `email` text NOT NULL, - `emailVerified` integer, - `image` text, - `telegramId` text -); ---> statement-breakpoint -CREATE TABLE `verificationToken` ( - `identifier` text NOT NULL, - `token` text NOT NULL, - `expires` integer NOT NULL, - PRIMARY KEY(`identifier`, `token`) -); ---> statement-breakpoint -CREATE UNIQUE INDEX `authenticator_credentialID_unique` ON `authenticator` (`credentialID`);--> statement-breakpoint -CREATE INDEX `canvas_user_userId` ON `canvas` (`userId`);--> statement-breakpoint -CREATE INDEX `chatHistory_thread_idx` ON `chatHistory` (`threadId`);--> statement-breakpoint -CREATE INDEX `chatThread_user_idx` ON `chatThread` (`userId`);--> statement-breakpoint -CREATE INDEX `spaces_name_idx` ON `space` (`name`);--> statement-breakpoint -CREATE INDEX `spaces_user_idx` ON `space` (`user`);--> statement-breakpoint -CREATE UNIQUE INDEX `storedContent_baseUrl_unique` ON `storedContent` (`baseUrl`);--> statement-breakpoint -CREATE INDEX `storedContent_url_idx` ON `storedContent` (`url`);--> statement-breakpoint -CREATE INDEX `storedContent_savedAt_idx` ON `storedContent` (`savedAt`);--> statement-breakpoint -CREATE INDEX `storedContent_title_idx` ON `storedContent` (`title`);--> statement-breakpoint -CREATE INDEX `storedContent_user_idx` ON `storedContent` (`user`);--> statement-breakpoint -CREATE INDEX `users_email_idx` ON `user` (`email`);--> statement-breakpoint -CREATE INDEX `users_telegram_idx` ON `user` (`telegramId`);--> statement-breakpoint -CREATE INDEX `users_id_idx` ON `user` (`id`); \ No newline at end of file diff --git a/apps/web/migrations/meta/0000_snapshot.json b/apps/web/migrations/meta/0000_snapshot.json index a4a2ec66..7e502c8b 100644 --- a/apps/web/migrations/meta/0000_snapshot.json +++ b/apps/web/migrations/meta/0000_snapshot.json @@ -1,7 +1,7 @@ { "version": "6", "dialect": "sqlite", - "id": "06dbbdd5-8ab1-410a-8710-903d29513644", + "id": "e8646bed-105d-4f69-b385-b8b6fee8a6a9", "prevId": "00000000-0000-0000-0000-000000000000", "tables": { "account": { @@ -518,7 +518,8 @@ "type": "text", "primaryKey": false, "notNull": true, - "autoincrement": false + "autoincrement": false, + "default": "'none'" }, "user": { "name": "user", @@ -544,6 +545,13 @@ } }, "indexes": { + "space_name_unique": { + "name": "space_name_unique", + "columns": [ + "name" + ], + "isUnique": true + }, "spaces_name_idx": { "name": "spaces_name_idx", "columns": [ diff --git a/apps/web/migrations/meta/_journal.json b/apps/web/migrations/meta/_journal.json index df754730..902eabee 100644 --- a/apps/web/migrations/meta/_journal.json +++ b/apps/web/migrations/meta/_journal.json @@ -5,8 +5,8 @@ { "idx": 0, "version": "6", - "when": 1721745865475, - "tag": "0000_stormy_catseye", + "when": 1721746132570, + "tag": "0000_silky_havok", "breakpoints": true } ] diff --git a/apps/web/server/db/schema.ts b/apps/web/server/db/schema.ts index 1815c515..ae293a91 100644 --- a/apps/web/server/db/schema.ts +++ b/apps/web/server/db/schema.ts @@ -148,7 +148,7 @@ export const space = createTable( "space", { id: integer("id").notNull().primaryKey({ autoIncrement: true }), - name: text("name").notNull(), + name: text("name").notNull().unique().default("none"), user: text("user", { length: 255 }).references(() => users.id, { onDelete: "cascade", }), -- cgit v1.2.3