iot/projects/StudyCenter/db.sql

CREATE TABLE "study_BookCategory" (
    "Id" TEXT NOT NULL CONSTRAINT "PK_study_BookCategory" PRIMARY KEY,
    "IsDeleted" TEXT NULL,
    "Name" TEXT NULL,
    "Number" TEXT NULL,
    "Path" TEXT NULL,
    "ParentId" TEXT NULL,
    CONSTRAINT "FK_study_BookCategory_study_BookCategory_ParentId" FOREIGN KEY ("ParentId") REFERENCES "study_BookCategory" ("Id") ON DELETE RESTRICT
);

CREATE TABLE "study_CourseCategory" (
    "Id" TEXT NOT NULL CONSTRAINT "PK_study_CourseCategory" PRIMARY KEY,
    "IsDeleted" TEXT NULL,
    "Name" TEXT NULL,
    "Number" TEXT NULL,
    "Path" TEXT NULL,
    "ParentId" TEXT NULL,
    "DisplayOrder" INTEGER NOT NULL,
    CONSTRAINT "FK_study_CourseCategory_study_CourseCategory_ParentId" FOREIGN KEY ("ParentId") REFERENCES "study_CourseCategory" ("Id") ON DELETE RESTRICT
);

CREATE TABLE "study_MajorCategory" (
    "Id" TEXT NOT NULL CONSTRAINT "PK_study_MajorCategory" PRIMARY KEY,
    "IsDeleted" TEXT NULL,
    "Name" TEXT NULL,
    "Number" TEXT NULL,
    "Path" TEXT NULL,
    "ParentId" TEXT NULL,
    CONSTRAINT "FK_study_MajorCategory_study_MajorCategory_ParentId" FOREIGN KEY ("ParentId") REFERENCES "study_MajorCategory" ("Id") ON DELETE RESTRICT
);

CREATE TABLE "study_Paper" (
    "Id" TEXT NOT NULL CONSTRAINT "PK_study_Paper" PRIMARY KEY,
    "IsDeleted" TEXT NULL,
    "Name" TEXT NULL
);

CREATE TABLE "study_PermissionCategory" (
    "Id" TEXT NOT NULL CONSTRAINT "PK_study_PermissionCategory" PRIMARY KEY,
    "IsDeleted" TEXT NULL,
    "Name" TEXT NULL,
    "Number" TEXT NULL,
    "Path" TEXT NULL,
    "ParentId" TEXT NULL,
    CONSTRAINT "FK_study_PermissionCategory_study_PermissionCategory_ParentId" FOREIGN KEY ("ParentId") REFERENCES "study_PermissionCategory" ("Id") ON DELETE RESTRICT
);

CREATE TABLE "study_ResourceCategory" (
    "Id" TEXT NOT NULL CONSTRAINT "PK_study_ResourceCategory" PRIMARY KEY,
    "IsDeleted" TEXT NULL,
    "Name" TEXT NULL,
    "Number" TEXT NULL,
    "Path" TEXT NULL,
    "ParentId" TEXT NULL,
    CONSTRAINT "FK_study_ResourceCategory_study_ResourceCategory_ParentId" FOREIGN KEY ("ParentId") REFERENCES "study_ResourceCategory" ("Id") ON DELETE RESTRICT
);

CREATE TABLE "study_Role" (
    "Id" TEXT NOT NULL CONSTRAINT "PK_study_Role" PRIMARY KEY,
    "IsDeleted" TEXT NULL,
    "Name" TEXT NULL,
    "IsReadOnly" INTEGER NOT NULL
);

CREATE TABLE "study_Setting" (
    "Id" TEXT NOT NULL CONSTRAINT "PK_study_Setting" PRIMARY KEY,
    "IsDeleted" TEXT NULL,
    "Name" TEXT NOT NULL,
    "Value" TEXT NULL,
    "Type" INTEGER NOT NULL
);

CREATE TABLE "study_User" (
    "Id" TEXT NOT NULL CONSTRAINT "PK_study_User" PRIMARY KEY,
    "IsDeleted" TEXT NULL,
    "UserName" TEXT NULL,
    "NickName" TEXT NULL,
    "Avatar" TEXT NULL
);

CREATE TABLE "study_Book" (
    "Id" TEXT NOT NULL CONSTRAINT "PK_study_Book" PRIMARY KEY,
    "IsDeleted" TEXT NULL,
    "Name" TEXT NULL,
    "Number" TEXT NULL,
    "Press" TEXT NULL,
    "BookCategoryId" TEXT NULL,
    CONSTRAINT "FK_study_Book_study_BookCategory_BookCategoryId" FOREIGN KEY ("BookCategoryId") REFERENCES "study_BookCategory" ("Id") ON DELETE RESTRICT
);

CREATE TABLE "study_Course" (
    "Id" TEXT NOT NULL CONSTRAINT "PK_study_Course" PRIMARY KEY,
    "IsDeleted" TEXT NULL,
    "Name" TEXT NULL,
    "Number" TEXT NULL,
    "Image" TEXT NULL,
    "Description" TEXT NULL,
    "CategoryId" TEXT NULL,
    CONSTRAINT "FK_study_Course_study_CourseCategory_CategoryId" FOREIGN KEY ("CategoryId") REFERENCES "study_CourseCategory" ("Id") ON DELETE RESTRICT
);

CREATE TABLE "study_Major" (
    "Id" TEXT NOT NULL CONSTRAINT "PK_study_Major" PRIMARY KEY,
    "IsDeleted" TEXT NULL,
    "Name" TEXT NULL,
    "Number" TEXT NULL,
    "CategoryId" TEXT NOT NULL,
    CONSTRAINT "FK_study_Major_study_MajorCategory_CategoryId" FOREIGN KEY ("CategoryId") REFERENCES "study_MajorCategory" ("Id") ON DELETE CASCADE
);

CREATE TABLE "study_PaperQuestion" (
    "Id" TEXT NOT NULL CONSTRAINT "PK_study_PaperQuestion" PRIMARY KEY,
    "IsDeleted" TEXT NULL,
    "Title" TEXT NULL,
    "Options" TEXT NULL,
    "Answer" TEXT NULL,
    "Score" REAL NOT NULL,
    "PaperId" TEXT NOT NULL,
    CONSTRAINT "FK_study_PaperQuestion_study_Paper_PaperId" FOREIGN KEY ("PaperId") REFERENCES "study_Paper" ("Id") ON DELETE CASCADE
);

CREATE TABLE "study_Permission" (
    "Id" TEXT NOT NULL CONSTRAINT "PK_study_Permission" PRIMARY KEY,
    "IsDeleted" TEXT NULL,
    "Name" TEXT NULL,
    "Number" TEXT NULL,
    "CategoryId" TEXT NULL,
    CONSTRAINT "FK_study_Permission_study_PermissionCategory_CategoryId" FOREIGN KEY ("CategoryId") REFERENCES "study_PermissionCategory" ("Id") ON DELETE RESTRICT
);

CREATE TABLE "study_UserPaper" (
    "Id" TEXT NOT NULL CONSTRAINT "PK_study_UserPaper" PRIMARY KEY,
    "IsDeleted" TEXT NULL,
    "UserId" TEXT NOT NULL,
    "PaperId" TEXT NOT NULL,
    "Start" TEXT NOT NULL,
    "End" TEXT NOT NULL,
    "Score" REAL NOT NULL,
    CONSTRAINT "FK_study_UserPaper_study_Paper_PaperId" FOREIGN KEY ("PaperId") REFERENCES "study_Paper" ("Id") ON DELETE CASCADE,
    CONSTRAINT "FK_study_UserPaper_study_User_UserId" FOREIGN KEY ("UserId") REFERENCES "study_User" ("Id") ON DELETE CASCADE
);

CREATE TABLE "study_UserRole" (
    "Id" TEXT NOT NULL CONSTRAINT "PK_study_UserRole" PRIMARY KEY,
    "IsDeleted" TEXT NULL,
    "UserId" TEXT NOT NULL,
    "RoleId" TEXT NOT NULL,
    CONSTRAINT "FK_study_UserRole_study_Role_RoleId" FOREIGN KEY ("RoleId") REFERENCES "study_Role" ("Id") ON DELETE CASCADE,
    CONSTRAINT "FK_study_UserRole_study_User_UserId" FOREIGN KEY ("UserId") REFERENCES "study_User" ("Id") ON DELETE CASCADE
);

CREATE TABLE "study_Section" (
    "Id" TEXT NOT NULL CONSTRAINT "PK_study_Section" PRIMARY KEY,
    "IsDeleted" TEXT NULL,
    "Name" TEXT NULL,
    "Number" TEXT NULL,
    "Path" TEXT NULL,
    "ParentId" TEXT NULL,
    "BookId" TEXT NOT NULL,
    CONSTRAINT "FK_study_Section_study_Book_BookId" FOREIGN KEY ("BookId") REFERENCES "study_Book" ("Id") ON DELETE CASCADE,
    CONSTRAINT "FK_study_Section_study_Section_ParentId" FOREIGN KEY ("ParentId") REFERENCES "study_Section" ("Id") ON DELETE RESTRICT
);

CREATE TABLE "study_Term" (
    "Id" TEXT NOT NULL CONSTRAINT "PK_study_Term" PRIMARY KEY,
    "IsDeleted" TEXT NULL,
    "Name" TEXT NULL,
    "Start" TEXT NOT NULL,
    "End" TEXT NOT NULL,
    "CourseId" TEXT NOT NULL,
    "BookId" TEXT NULL,
    CONSTRAINT "FK_study_Term_study_Book_BookId" FOREIGN KEY ("BookId") REFERENCES "study_Book" ("Id") ON DELETE RESTRICT,
    CONSTRAINT "FK_study_Term_study_Course_CourseId" FOREIGN KEY ("CourseId") REFERENCES "study_Course" ("Id") ON DELETE CASCADE
);

CREATE TABLE "study_TeachingPlan" (
    "Id" TEXT NOT NULL CONSTRAINT "PK_study_TeachingPlan" PRIMARY KEY,
    "IsDeleted" TEXT NULL,
    "Start" TEXT NOT NULL,
    "Years" INTEGER NOT NULL,
    "Degree" TEXT NULL,
    "Career" TEXT NULL,
    "MajorId" TEXT NOT NULL,
    CONSTRAINT "FK_study_TeachingPlan_study_Major_MajorId" FOREIGN KEY ("MajorId") REFERENCES "study_Major" ("Id") ON DELETE CASCADE
);

CREATE TABLE "study_UserPaperQuestion" (
    "Id" TEXT NOT NULL CONSTRAINT "PK_study_UserPaperQuestion" PRIMARY KEY,
    "IsDeleted" TEXT NULL,
    "UserId" TEXT NOT NULL,
    "PaperQuestionId" TEXT NOT NULL,
    "Answer" TEXT NULL,
    "Success" INTEGER NOT NULL,
    CONSTRAINT "FK_study_UserPaperQuestion_study_PaperQuestion_PaperQuestionId" FOREIGN KEY ("PaperQuestionId") REFERENCES "study_PaperQuestion" ("Id") ON DELETE CASCADE,
    CONSTRAINT "FK_study_UserPaperQuestion_study_User_UserId" FOREIGN KEY ("UserId") REFERENCES "study_User" ("Id") ON DELETE CASCADE
);

CREATE TABLE "study_RolePermission" (
    "Id" TEXT NOT NULL CONSTRAINT "PK_study_RolePermission" PRIMARY KEY,
    "IsDeleted" TEXT NULL,
    "RoleId" TEXT NOT NULL,
    "PermissionId" TEXT NOT NULL,
    "IsReadOnly" INTEGER NOT NULL,
    CONSTRAINT "FK_study_RolePermission_study_Permission_PermissionId" FOREIGN KEY ("PermissionId") REFERENCES "study_Permission" ("Id") ON DELETE CASCADE,
    CONSTRAINT "FK_study_RolePermission_study_Role_RoleId" FOREIGN KEY ("RoleId") REFERENCES "study_Role" ("Id") ON DELETE CASCADE
);

CREATE TABLE "study_Question" (
    "Id" TEXT NOT NULL CONSTRAINT "PK_study_Question" PRIMARY KEY,
    "IsDeleted" TEXT NULL,
    "Title" TEXT NULL,
    "Options" TEXT NULL,
    "Answer" TEXT NULL,
    "UserId" TEXT NULL,
    "SectionId" TEXT NULL,
    "CourseId" TEXT NOT NULL,
    CONSTRAINT "FK_study_Question_study_Course_CourseId" FOREIGN KEY ("CourseId") REFERENCES "study_Course" ("Id") ON DELETE CASCADE,
    CONSTRAINT "FK_study_Question_study_Section_SectionId" FOREIGN KEY ("SectionId") REFERENCES "study_Section" ("Id") ON DELETE RESTRICT,
    CONSTRAINT "FK_study_Question_study_User_UserId" FOREIGN KEY ("UserId") REFERENCES "study_User" ("Id") ON DELETE RESTRICT
);

CREATE TABLE "study_Resource" (
    "Id" TEXT NOT NULL CONSTRAINT "PK_study_Resource" PRIMARY KEY,
    "IsDeleted" TEXT NULL,
    "Name" TEXT NOT NULL,
    "File" TEXT NULL,
    "UserId" TEXT NULL,
    "CategoryId" TEXT NULL,
    "SectionId" TEXT NULL,
    "CourseId" TEXT NOT NULL,
    CONSTRAINT "FK_study_Resource_study_ResourceCategory_CategoryId" FOREIGN KEY ("CategoryId") REFERENCES "study_ResourceCategory" ("Id") ON DELETE RESTRICT,
    CONSTRAINT "FK_study_Resource_study_Course_CourseId" FOREIGN KEY ("CourseId") REFERENCES "study_Course" ("Id") ON DELETE CASCADE,
    CONSTRAINT "FK_study_Resource_study_Section_SectionId" FOREIGN KEY ("SectionId") REFERENCES "study_Section" ("Id") ON DELETE RESTRICT,
    CONSTRAINT "FK_study_Resource_study_User_UserId" FOREIGN KEY ("UserId") REFERENCES "study_User" ("Id") ON DELETE RESTRICT
);

CREATE TABLE "study_Eaxm" (
    "Id" TEXT NOT NULL CONSTRAINT "PK_study_Eaxm" PRIMARY KEY,
    "IsDeleted" TEXT NULL,
    "Name" TEXT NULL,
    "TermId" TEXT NOT NULL,
    CONSTRAINT "FK_study_Eaxm_study_Term_TermId" FOREIGN KEY ("TermId") REFERENCES "study_Term" ("Id") ON DELETE CASCADE
);

CREATE TABLE "study_Lesson" (
    "Id" TEXT NOT NULL CONSTRAINT "PK_study_Lesson" PRIMARY KEY,
    "IsDeleted" TEXT NULL,
    "Name" TEXT NULL,
    "TermId" TEXT NOT NULL,
    "DisplayOrder" INTEGER NOT NULL,
    CONSTRAINT "FK_study_Lesson_study_Term_TermId" FOREIGN KEY ("TermId") REFERENCES "study_Term" ("Id") ON DELETE CASCADE
);

CREATE TABLE "study_TermUser" (
    "Id" TEXT NOT NULL CONSTRAINT "PK_study_TermUser" PRIMARY KEY,
    "IsDeleted" TEXT NULL,
    "TermId" TEXT NOT NULL,
    "UserId" TEXT NOT NULL,
    "Score" REAL NOT NULL,
    CONSTRAINT "FK_study_TermUser_study_Term_TermId" FOREIGN KEY ("TermId") REFERENCES "study_Term" ("Id") ON DELETE CASCADE,
    CONSTRAINT "FK_study_TermUser_study_User_UserId" FOREIGN KEY ("UserId") REFERENCES "study_User" ("Id") ON DELETE CASCADE
);

CREATE TABLE "study_Semester" (
    "Id" TEXT NOT NULL CONSTRAINT "PK_study_Semester" PRIMARY KEY,
    "IsDeleted" TEXT NULL,
    "Name" TEXT NULL,
    "PlanId" TEXT NOT NULL,
    CONSTRAINT "FK_study_Semester_study_TeachingPlan_PlanId" FOREIGN KEY ("PlanId") REFERENCES "study_TeachingPlan" ("Id") ON DELETE CASCADE
);

CREATE TABLE "study_UserQuestion" (
    "Id" TEXT NOT NULL CONSTRAINT "PK_study_UserQuestion" PRIMARY KEY,
    "IsDeleted" TEXT NULL,
    "UserId" TEXT NOT NULL,
    "QuestionId" TEXT NOT NULL,
    "Answer" TEXT NULL,
    "Success" INTEGER NOT NULL,
    CONSTRAINT "FK_study_UserQuestion_study_Question_QuestionId" FOREIGN KEY ("QuestionId") REFERENCES "study_Question" ("Id") ON DELETE CASCADE,
    CONSTRAINT "FK_study_UserQuestion_study_User_UserId" FOREIGN KEY ("UserId") REFERENCES "study_User" ("Id") ON DELETE CASCADE
);

CREATE TABLE "study_LessonResource" (
    "Id" TEXT NOT NULL CONSTRAINT "PK_study_LessonResource" PRIMARY KEY,
    "IsDeleted" TEXT NULL,
    "Name" TEXT NULL,
    "LessionId" TEXT NOT NULL,
    "ResourceId" TEXT NOT NULL,
    CONSTRAINT "FK_study_LessonResource_study_Lesson_LessionId" FOREIGN KEY ("LessionId") REFERENCES "study_Lesson" ("Id") ON DELETE CASCADE,
    CONSTRAINT "FK_study_LessonResource_study_Resource_ResourceId" FOREIGN KEY ("ResourceId") REFERENCES "study_Resource" ("Id") ON DELETE CASCADE
);

CREATE TABLE "study_SemesterCourse" (
    "Id" TEXT NOT NULL CONSTRAINT "PK_study_SemesterCourse" PRIMARY KEY,
    "IsDeleted" TEXT NULL,
    "Name" TEXT NULL,
    "Number" TEXT NULL,
    "Category" TEXT NULL,
    "Type" TEXT NULL,
    "Score" REAL NOT NULL,
    "SemesterId" TEXT NOT NULL,
    "TermId" TEXT NULL,
    CONSTRAINT "FK_study_SemesterCourse_study_Semester_SemesterId" FOREIGN KEY ("SemesterId") REFERENCES "study_Semester" ("Id") ON DELETE CASCADE,
    CONSTRAINT "FK_study_SemesterCourse_study_Term_TermId" FOREIGN KEY ("TermId") REFERENCES "study_Term" ("Id") ON DELETE RESTRICT
);

CREATE INDEX "IX_study_Book_BookCategoryId" ON "study_Book" ("BookCategoryId");

CREATE UNIQUE INDEX "IX_study_BookCategory_ParentId_Number" ON "study_BookCategory" ("ParentId", "Number");

CREATE INDEX "IX_study_Course_CategoryId" ON "study_Course" ("CategoryId");

CREATE UNIQUE INDEX "IX_study_CourseCategory_ParentId_Number" ON "study_CourseCategory" ("ParentId", "Number");

CREATE INDEX "IX_study_Eaxm_TermId" ON "study_Eaxm" ("TermId");

CREATE INDEX "IX_study_Lesson_TermId" ON "study_Lesson" ("TermId");

CREATE INDEX "IX_study_LessonResource_ResourceId" ON "study_LessonResource" ("ResourceId");

CREATE UNIQUE INDEX "IX_study_LessonResource_LessionId_ResourceId" ON "study_LessonResource" ("LessionId", "ResourceId");

CREATE INDEX "IX_study_Major_CategoryId" ON "study_Major" ("CategoryId");

CREATE UNIQUE INDEX "IX_study_MajorCategory_ParentId_Number" ON "study_MajorCategory" ("ParentId", "Number");

CREATE INDEX "IX_study_PaperQuestion_PaperId" ON "study_PaperQuestion" ("PaperId");

CREATE INDEX "IX_study_Permission_CategoryId" ON "study_Permission" ("CategoryId");

CREATE UNIQUE INDEX "IX_study_Permission_Number" ON "study_Permission" ("Number");

CREATE UNIQUE INDEX "IX_study_PermissionCategory_Number" ON "study_PermissionCategory" ("Number");

CREATE INDEX "IX_study_PermissionCategory_ParentId" ON "study_PermissionCategory" ("ParentId");

CREATE INDEX "IX_study_Question_CourseId" ON "study_Question" ("CourseId");

CREATE INDEX "IX_study_Question_SectionId" ON "study_Question" ("SectionId");

CREATE INDEX "IX_study_Question_UserId" ON "study_Question" ("UserId");

CREATE INDEX "IX_study_Resource_CategoryId" ON "study_Resource" ("CategoryId");

CREATE INDEX "IX_study_Resource_CourseId" ON "study_Resource" ("CourseId");

CREATE INDEX "IX_study_Resource_SectionId" ON "study_Resource" ("SectionId");

CREATE INDEX "IX_study_Resource_UserId" ON "study_Resource" ("UserId");

CREATE INDEX "IX_study_ResourceCategory_ParentId" ON "study_ResourceCategory" ("ParentId");

CREATE UNIQUE INDEX "IX_study_Role_Name" ON "study_Role" ("Name");

CREATE INDEX "IX_study_RolePermission_PermissionId" ON "study_RolePermission" ("PermissionId");

CREATE UNIQUE INDEX "IX_study_RolePermission_RoleId_PermissionId" ON "study_RolePermission" ("RoleId", "PermissionId");

CREATE INDEX "IX_study_Section_BookId" ON "study_Section" ("BookId");

CREATE UNIQUE INDEX "IX_study_Section_ParentId_Number" ON "study_Section" ("ParentId", "Number");

CREATE INDEX "IX_study_Semester_PlanId" ON "study_Semester" ("PlanId");

CREATE INDEX "IX_study_SemesterCourse_SemesterId" ON "study_SemesterCourse" ("SemesterId");

CREATE INDEX "IX_study_SemesterCourse_TermId" ON "study_SemesterCourse" ("TermId");

CREATE INDEX "IX_study_TeachingPlan_MajorId" ON "study_TeachingPlan" ("MajorId");

CREATE INDEX "IX_study_Term_BookId" ON "study_Term" ("BookId");

CREATE INDEX "IX_study_Term_CourseId" ON "study_Term" ("CourseId");

CREATE INDEX "IX_study_TermUser_UserId" ON "study_TermUser" ("UserId");

CREATE UNIQUE INDEX "IX_study_TermUser_TermId_UserId" ON "study_TermUser" ("TermId", "UserId");

CREATE UNIQUE INDEX "IX_study_User_UserName" ON "study_User" ("UserName");

CREATE INDEX "IX_study_UserPaper_PaperId" ON "study_UserPaper" ("PaperId");

CREATE UNIQUE INDEX "IX_study_UserPaper_UserId_PaperId" ON "study_UserPaper" ("UserId", "PaperId");

CREATE INDEX "IX_study_UserPaperQuestion_PaperQuestionId" ON "study_UserPaperQuestion" ("PaperQuestionId");

CREATE UNIQUE INDEX "IX_study_UserPaperQuestion_UserId_PaperQuestionId" ON "study_UserPaperQuestion" ("UserId", "PaperQuestionId");

CREATE INDEX "IX_study_UserQuestion_QuestionId" ON "study_UserQuestion" ("QuestionId");

CREATE UNIQUE INDEX "IX_study_UserQuestion_UserId_QuestionId" ON "study_UserQuestion" ("UserId", "QuestionId");

CREATE INDEX "IX_study_UserRole_RoleId" ON "study_UserRole" ("RoleId");

CREATE UNIQUE INDEX "IX_study_UserRole_UserId_RoleId" ON "study_UserRole" ("UserId", "RoleId");