324 lines
24 KiB
SQL
324 lines
24 KiB
SQL
/*
|
|
Warnings:
|
|
|
|
- The primary key for the `classes` table will be changed. If it partially fails, the table could be left without primary key constraint.
|
|
- You are about to alter the column `id` on the `classes` table. The data in that column could be lost. The data in that column will be cast from `BigInt` to `Int`.
|
|
- You are about to alter the column `teacher_id` on the `classes` table. The data in that column could be lost. The data in that column will be cast from `BigInt` to `Int`.
|
|
- You are about to alter the column `tenant_id` on the `classes` table. The data in that column could be lost. The data in that column will be cast from `BigInt` to `Int`.
|
|
- The primary key for the `course_activities` table will be changed. If it partially fails, the table could be left without primary key constraint.
|
|
- You are about to alter the column `course_id` on the `course_activities` table. The data in that column could be lost. The data in that column will be cast from `BigInt` to `Int`.
|
|
- You are about to alter the column `id` on the `course_activities` table. The data in that column could be lost. The data in that column will be cast from `BigInt` to `Int`.
|
|
- The primary key for the `course_resources` table will be changed. If it partially fails, the table could be left without primary key constraint.
|
|
- You are about to alter the column `course_id` on the `course_resources` table. The data in that column could be lost. The data in that column will be cast from `BigInt` to `Int`.
|
|
- You are about to alter the column `file_size` on the `course_resources` table. The data in that column could be lost. The data in that column will be cast from `BigInt` to `Int`.
|
|
- You are about to alter the column `id` on the `course_resources` table. The data in that column could be lost. The data in that column will be cast from `BigInt` to `Int`.
|
|
- The primary key for the `course_script_pages` table will be changed. If it partially fails, the table could be left without primary key constraint.
|
|
- You are about to alter the column `id` on the `course_script_pages` table. The data in that column could be lost. The data in that column will be cast from `BigInt` to `Int`.
|
|
- You are about to alter the column `script_id` on the `course_script_pages` table. The data in that column could be lost. The data in that column will be cast from `BigInt` to `Int`.
|
|
- The primary key for the `course_scripts` table will be changed. If it partially fails, the table could be left without primary key constraint.
|
|
- You are about to alter the column `course_id` on the `course_scripts` table. The data in that column could be lost. The data in that column will be cast from `BigInt` to `Int`.
|
|
- You are about to alter the column `id` on the `course_scripts` table. The data in that column could be lost. The data in that column will be cast from `BigInt` to `Int`.
|
|
- The primary key for the `courses` table will be changed. If it partially fails, the table could be left without primary key constraint.
|
|
- You are about to alter the column `id` on the `courses` table. The data in that column could be lost. The data in that column will be cast from `BigInt` to `Int`.
|
|
- The primary key for the `lesson_feedbacks` table will be changed. If it partially fails, the table could be left without primary key constraint.
|
|
- You are about to alter the column `id` on the `lesson_feedbacks` table. The data in that column could be lost. The data in that column will be cast from `BigInt` to `Int`.
|
|
- You are about to alter the column `lesson_id` on the `lesson_feedbacks` table. The data in that column could be lost. The data in that column will be cast from `BigInt` to `Int`.
|
|
- You are about to alter the column `teacher_id` on the `lesson_feedbacks` table. The data in that column could be lost. The data in that column will be cast from `BigInt` to `Int`.
|
|
- The primary key for the `lessons` table will be changed. If it partially fails, the table could be left without primary key constraint.
|
|
- You are about to alter the column `class_id` on the `lessons` table. The data in that column could be lost. The data in that column will be cast from `BigInt` to `Int`.
|
|
- You are about to alter the column `course_id` on the `lessons` table. The data in that column could be lost. The data in that column will be cast from `BigInt` to `Int`.
|
|
- You are about to alter the column `id` on the `lessons` table. The data in that column could be lost. The data in that column will be cast from `BigInt` to `Int`.
|
|
- You are about to alter the column `teacher_id` on the `lessons` table. The data in that column could be lost. The data in that column will be cast from `BigInt` to `Int`.
|
|
- You are about to alter the column `tenant_id` on the `lessons` table. The data in that column could be lost. The data in that column will be cast from `BigInt` to `Int`.
|
|
- The primary key for the `student_records` table will be changed. If it partially fails, the table could be left without primary key constraint.
|
|
- You are about to alter the column `id` on the `student_records` table. The data in that column could be lost. The data in that column will be cast from `BigInt` to `Int`.
|
|
- You are about to alter the column `lesson_id` on the `student_records` table. The data in that column could be lost. The data in that column will be cast from `BigInt` to `Int`.
|
|
- You are about to alter the column `student_id` on the `student_records` table. The data in that column could be lost. The data in that column will be cast from `BigInt` to `Int`.
|
|
- The primary key for the `students` table will be changed. If it partially fails, the table could be left without primary key constraint.
|
|
- You are about to alter the column `class_id` on the `students` table. The data in that column could be lost. The data in that column will be cast from `BigInt` to `Int`.
|
|
- You are about to alter the column `id` on the `students` table. The data in that column could be lost. The data in that column will be cast from `BigInt` to `Int`.
|
|
- You are about to alter the column `tenant_id` on the `students` table. The data in that column could be lost. The data in that column will be cast from `BigInt` to `Int`.
|
|
- The primary key for the `tags` table will be changed. If it partially fails, the table could be left without primary key constraint.
|
|
- You are about to alter the column `id` on the `tags` table. The data in that column could be lost. The data in that column will be cast from `BigInt` to `Int`.
|
|
- You are about to alter the column `parent_id` on the `tags` table. The data in that column could be lost. The data in that column will be cast from `BigInt` to `Int`.
|
|
- The primary key for the `teachers` table will be changed. If it partially fails, the table could be left without primary key constraint.
|
|
- You are about to alter the column `id` on the `teachers` table. The data in that column could be lost. The data in that column will be cast from `BigInt` to `Int`.
|
|
- You are about to alter the column `tenant_id` on the `teachers` table. The data in that column could be lost. The data in that column will be cast from `BigInt` to `Int`.
|
|
- The primary key for the `tenant_courses` table will be changed. If it partially fails, the table could be left without primary key constraint.
|
|
- You are about to alter the column `course_id` on the `tenant_courses` table. The data in that column could be lost. The data in that column will be cast from `BigInt` to `Int`.
|
|
- You are about to alter the column `id` on the `tenant_courses` table. The data in that column could be lost. The data in that column will be cast from `BigInt` to `Int`.
|
|
- You are about to alter the column `tenant_id` on the `tenant_courses` table. The data in that column could be lost. The data in that column will be cast from `BigInt` to `Int`.
|
|
- The primary key for the `tenants` table will be changed. If it partially fails, the table could be left without primary key constraint.
|
|
- You are about to alter the column `id` on the `tenants` table. The data in that column could be lost. The data in that column will be cast from `BigInt` to `Int`.
|
|
- Made the column `picture_book_id` on table `courses` required. This step will fail if there are existing NULL values in that column.
|
|
|
|
*/
|
|
-- RedefineTables
|
|
PRAGMA defer_foreign_keys=ON;
|
|
PRAGMA foreign_keys=OFF;
|
|
CREATE TABLE "new_classes" (
|
|
"id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
|
|
"tenant_id" INTEGER NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"grade" TEXT NOT NULL,
|
|
"teacher_id" INTEGER,
|
|
"student_count" INTEGER NOT NULL DEFAULT 0,
|
|
"lesson_count" INTEGER NOT NULL DEFAULT 0,
|
|
"created_at" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updated_at" DATETIME NOT NULL,
|
|
CONSTRAINT "classes_tenant_id_fkey" FOREIGN KEY ("tenant_id") REFERENCES "tenants" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
CONSTRAINT "classes_teacher_id_fkey" FOREIGN KEY ("teacher_id") REFERENCES "teachers" ("id") ON DELETE SET NULL ON UPDATE CASCADE
|
|
);
|
|
INSERT INTO "new_classes" ("created_at", "grade", "id", "lesson_count", "name", "student_count", "teacher_id", "tenant_id", "updated_at") SELECT "created_at", "grade", "id", "lesson_count", "name", "student_count", "teacher_id", "tenant_id", "updated_at" FROM "classes";
|
|
DROP TABLE "classes";
|
|
ALTER TABLE "new_classes" RENAME TO "classes";
|
|
CREATE TABLE "new_course_activities" (
|
|
"id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
|
|
"course_id" INTEGER NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"domain" TEXT,
|
|
"domain_tag_id" INTEGER,
|
|
"activity_type" TEXT NOT NULL,
|
|
"duration" INTEGER,
|
|
"online_materials" TEXT,
|
|
"offlineMaterials" TEXT,
|
|
"activityGuide" TEXT,
|
|
"objectives" TEXT,
|
|
"sort_order" INTEGER NOT NULL DEFAULT 0,
|
|
"created_at" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
CONSTRAINT "course_activities_course_id_fkey" FOREIGN KEY ("course_id") REFERENCES "courses" ("id") ON DELETE CASCADE ON UPDATE CASCADE
|
|
);
|
|
INSERT INTO "new_course_activities" ("activityGuide", "activity_type", "course_id", "created_at", "domain", "domain_tag_id", "duration", "id", "name", "objectives", "offlineMaterials", "online_materials", "sort_order") SELECT "activityGuide", "activity_type", "course_id", "created_at", "domain", "domain_tag_id", "duration", "id", "name", "objectives", "offlineMaterials", "online_materials", "sort_order" FROM "course_activities";
|
|
DROP TABLE "course_activities";
|
|
ALTER TABLE "new_course_activities" RENAME TO "course_activities";
|
|
CREATE TABLE "new_course_resources" (
|
|
"id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
|
|
"course_id" INTEGER NOT NULL,
|
|
"resource_type" TEXT NOT NULL,
|
|
"resource_name" TEXT NOT NULL,
|
|
"file_url" TEXT NOT NULL,
|
|
"file_size" INTEGER,
|
|
"mime_type" TEXT,
|
|
"metadata" TEXT,
|
|
"sort_order" INTEGER NOT NULL DEFAULT 0,
|
|
"created_at" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
CONSTRAINT "course_resources_course_id_fkey" FOREIGN KEY ("course_id") REFERENCES "courses" ("id") ON DELETE CASCADE ON UPDATE CASCADE
|
|
);
|
|
INSERT INTO "new_course_resources" ("course_id", "created_at", "file_size", "file_url", "id", "metadata", "mime_type", "resource_name", "resource_type", "sort_order") SELECT "course_id", "created_at", "file_size", "file_url", "id", "metadata", "mime_type", "resource_name", "resource_type", "sort_order" FROM "course_resources";
|
|
DROP TABLE "course_resources";
|
|
ALTER TABLE "new_course_resources" RENAME TO "course_resources";
|
|
CREATE TABLE "new_course_script_pages" (
|
|
"id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
|
|
"script_id" INTEGER NOT NULL,
|
|
"page_number" INTEGER NOT NULL,
|
|
"questions" TEXT,
|
|
"interaction_component" TEXT,
|
|
"teacher_notes" TEXT,
|
|
"created_at" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updated_at" DATETIME NOT NULL,
|
|
CONSTRAINT "course_script_pages_script_id_fkey" FOREIGN KEY ("script_id") REFERENCES "course_scripts" ("id") ON DELETE CASCADE ON UPDATE CASCADE
|
|
);
|
|
INSERT INTO "new_course_script_pages" ("created_at", "id", "interaction_component", "page_number", "questions", "script_id", "teacher_notes", "updated_at") SELECT "created_at", "id", "interaction_component", "page_number", "questions", "script_id", "teacher_notes", "updated_at" FROM "course_script_pages";
|
|
DROP TABLE "course_script_pages";
|
|
ALTER TABLE "new_course_script_pages" RENAME TO "course_script_pages";
|
|
CREATE UNIQUE INDEX "course_script_pages_script_id_page_number_key" ON "course_script_pages"("script_id", "page_number");
|
|
CREATE TABLE "new_course_scripts" (
|
|
"id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
|
|
"course_id" INTEGER NOT NULL,
|
|
"step_index" INTEGER NOT NULL,
|
|
"step_name" TEXT NOT NULL,
|
|
"step_type" TEXT NOT NULL,
|
|
"duration" INTEGER NOT NULL,
|
|
"objective" TEXT,
|
|
"teacher_script" TEXT,
|
|
"interaction_points" TEXT,
|
|
"resource_ids" TEXT,
|
|
"sort_order" INTEGER NOT NULL DEFAULT 0,
|
|
"created_at" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updated_at" DATETIME NOT NULL,
|
|
CONSTRAINT "course_scripts_course_id_fkey" FOREIGN KEY ("course_id") REFERENCES "courses" ("id") ON DELETE CASCADE ON UPDATE CASCADE
|
|
);
|
|
INSERT INTO "new_course_scripts" ("course_id", "created_at", "duration", "id", "interaction_points", "objective", "resource_ids", "sort_order", "step_index", "step_name", "step_type", "teacher_script", "updated_at") SELECT "course_id", "created_at", "duration", "id", "interaction_points", "objective", "resource_ids", "sort_order", "step_index", "step_name", "step_type", "teacher_script", "updated_at" FROM "course_scripts";
|
|
DROP TABLE "course_scripts";
|
|
ALTER TABLE "new_course_scripts" RENAME TO "course_scripts";
|
|
CREATE UNIQUE INDEX "course_scripts_course_id_step_index_key" ON "course_scripts"("course_id", "step_index");
|
|
CREATE TABLE "new_courses" (
|
|
"id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
|
|
"name" TEXT NOT NULL,
|
|
"description" TEXT,
|
|
"picture_book_id" INTEGER NOT NULL,
|
|
"picture_book_name" TEXT,
|
|
"grade_tags" TEXT NOT NULL DEFAULT '[]',
|
|
"domain_tags" TEXT NOT NULL DEFAULT '[]',
|
|
"duration" INTEGER NOT NULL DEFAULT 25,
|
|
"status" TEXT NOT NULL DEFAULT 'DRAFT',
|
|
"version" TEXT NOT NULL DEFAULT '1.0',
|
|
"usage_count" INTEGER NOT NULL DEFAULT 0,
|
|
"teacher_count" INTEGER NOT NULL DEFAULT 0,
|
|
"avg_rating" REAL NOT NULL DEFAULT 0,
|
|
"created_by" INTEGER,
|
|
"created_at" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updated_at" DATETIME NOT NULL,
|
|
"published_at" DATETIME
|
|
);
|
|
INSERT INTO "new_courses" ("avg_rating", "created_at", "created_by", "description", "domain_tags", "duration", "grade_tags", "id", "name", "picture_book_id", "picture_book_name", "published_at", "status", "teacher_count", "updated_at", "usage_count", "version") SELECT "avg_rating", "created_at", "created_by", "description", "domain_tags", "duration", "grade_tags", "id", "name", "picture_book_id", "picture_book_name", "published_at", "status", "teacher_count", "updated_at", "usage_count", "version" FROM "courses";
|
|
DROP TABLE "courses";
|
|
ALTER TABLE "new_courses" RENAME TO "courses";
|
|
CREATE TABLE "new_lesson_feedbacks" (
|
|
"id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
|
|
"lesson_id" INTEGER NOT NULL,
|
|
"teacher_id" INTEGER NOT NULL,
|
|
"design_quality" INTEGER,
|
|
"participation" INTEGER,
|
|
"goal_achievement" INTEGER,
|
|
"step_feedbacks" TEXT,
|
|
"pros" TEXT,
|
|
"suggestions" TEXT,
|
|
"activities_done" TEXT,
|
|
"created_at" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updated_at" DATETIME NOT NULL,
|
|
CONSTRAINT "lesson_feedbacks_lesson_id_fkey" FOREIGN KEY ("lesson_id") REFERENCES "lessons" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
CONSTRAINT "lesson_feedbacks_teacher_id_fkey" FOREIGN KEY ("teacher_id") REFERENCES "teachers" ("id") ON DELETE RESTRICT ON UPDATE CASCADE
|
|
);
|
|
INSERT INTO "new_lesson_feedbacks" ("activities_done", "created_at", "design_quality", "goal_achievement", "id", "lesson_id", "participation", "pros", "step_feedbacks", "suggestions", "teacher_id", "updated_at") SELECT "activities_done", "created_at", "design_quality", "goal_achievement", "id", "lesson_id", "participation", "pros", "step_feedbacks", "suggestions", "teacher_id", "updated_at" FROM "lesson_feedbacks";
|
|
DROP TABLE "lesson_feedbacks";
|
|
ALTER TABLE "new_lesson_feedbacks" RENAME TO "lesson_feedbacks";
|
|
CREATE UNIQUE INDEX "lesson_feedbacks_lesson_id_teacher_id_key" ON "lesson_feedbacks"("lesson_id", "teacher_id");
|
|
CREATE TABLE "new_lessons" (
|
|
"id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
|
|
"tenant_id" INTEGER NOT NULL,
|
|
"teacher_id" INTEGER NOT NULL,
|
|
"class_id" INTEGER NOT NULL,
|
|
"course_id" INTEGER NOT NULL,
|
|
"planned_datetime" DATETIME,
|
|
"start_datetime" DATETIME,
|
|
"end_datetime" DATETIME,
|
|
"actual_duration" INTEGER,
|
|
"status" TEXT NOT NULL DEFAULT 'PLANNED',
|
|
"overall_rating" TEXT,
|
|
"participation_rating" TEXT,
|
|
"completion_note" TEXT,
|
|
"created_at" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updated_at" DATETIME NOT NULL,
|
|
CONSTRAINT "lessons_tenant_id_fkey" FOREIGN KEY ("tenant_id") REFERENCES "tenants" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
|
|
CONSTRAINT "lessons_teacher_id_fkey" FOREIGN KEY ("teacher_id") REFERENCES "teachers" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
|
|
CONSTRAINT "lessons_class_id_fkey" FOREIGN KEY ("class_id") REFERENCES "classes" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
|
|
CONSTRAINT "lessons_course_id_fkey" FOREIGN KEY ("course_id") REFERENCES "courses" ("id") ON DELETE RESTRICT ON UPDATE CASCADE
|
|
);
|
|
INSERT INTO "new_lessons" ("actual_duration", "class_id", "completion_note", "course_id", "created_at", "end_datetime", "id", "overall_rating", "participation_rating", "planned_datetime", "start_datetime", "status", "teacher_id", "tenant_id", "updated_at") SELECT "actual_duration", "class_id", "completion_note", "course_id", "created_at", "end_datetime", "id", "overall_rating", "participation_rating", "planned_datetime", "start_datetime", "status", "teacher_id", "tenant_id", "updated_at" FROM "lessons";
|
|
DROP TABLE "lessons";
|
|
ALTER TABLE "new_lessons" RENAME TO "lessons";
|
|
CREATE TABLE "new_student_records" (
|
|
"id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
|
|
"lesson_id" INTEGER NOT NULL,
|
|
"student_id" INTEGER NOT NULL,
|
|
"focus" INTEGER,
|
|
"participation" INTEGER,
|
|
"interest" INTEGER,
|
|
"understanding" INTEGER,
|
|
"domainAchievements" TEXT,
|
|
"notes" TEXT,
|
|
"created_at" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updated_at" DATETIME NOT NULL,
|
|
CONSTRAINT "student_records_lesson_id_fkey" FOREIGN KEY ("lesson_id") REFERENCES "lessons" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
CONSTRAINT "student_records_student_id_fkey" FOREIGN KEY ("student_id") REFERENCES "students" ("id") ON DELETE RESTRICT ON UPDATE CASCADE
|
|
);
|
|
INSERT INTO "new_student_records" ("created_at", "domainAchievements", "focus", "id", "interest", "lesson_id", "notes", "participation", "student_id", "understanding", "updated_at") SELECT "created_at", "domainAchievements", "focus", "id", "interest", "lesson_id", "notes", "participation", "student_id", "understanding", "updated_at" FROM "student_records";
|
|
DROP TABLE "student_records";
|
|
ALTER TABLE "new_student_records" RENAME TO "student_records";
|
|
CREATE UNIQUE INDEX "student_records_lesson_id_student_id_key" ON "student_records"("lesson_id", "student_id");
|
|
CREATE TABLE "new_students" (
|
|
"id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
|
|
"tenant_id" INTEGER NOT NULL,
|
|
"class_id" INTEGER NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"gender" TEXT,
|
|
"birth_date" DATETIME,
|
|
"parent_phone" TEXT,
|
|
"parent_name" TEXT,
|
|
"reading_count" INTEGER NOT NULL DEFAULT 0,
|
|
"lesson_count" INTEGER NOT NULL DEFAULT 0,
|
|
"created_at" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updated_at" DATETIME NOT NULL,
|
|
CONSTRAINT "students_tenant_id_fkey" FOREIGN KEY ("tenant_id") REFERENCES "tenants" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
CONSTRAINT "students_class_id_fkey" FOREIGN KEY ("class_id") REFERENCES "classes" ("id") ON DELETE CASCADE ON UPDATE CASCADE
|
|
);
|
|
INSERT INTO "new_students" ("birth_date", "class_id", "created_at", "gender", "id", "lesson_count", "name", "parent_name", "parent_phone", "reading_count", "tenant_id", "updated_at") SELECT "birth_date", "class_id", "created_at", "gender", "id", "lesson_count", "name", "parent_name", "parent_phone", "reading_count", "tenant_id", "updated_at" FROM "students";
|
|
DROP TABLE "students";
|
|
ALTER TABLE "new_students" RENAME TO "students";
|
|
CREATE TABLE "new_tags" (
|
|
"id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
|
|
"level" INTEGER NOT NULL,
|
|
"code" TEXT NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"parent_id" INTEGER,
|
|
"description" TEXT,
|
|
"metadata" TEXT,
|
|
"sort_order" INTEGER NOT NULL DEFAULT 0,
|
|
"created_at" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
INSERT INTO "new_tags" ("code", "created_at", "description", "id", "level", "metadata", "name", "parent_id", "sort_order") SELECT "code", "created_at", "description", "id", "level", "metadata", "name", "parent_id", "sort_order" FROM "tags";
|
|
DROP TABLE "tags";
|
|
ALTER TABLE "new_tags" RENAME TO "tags";
|
|
CREATE UNIQUE INDEX "tags_code_key" ON "tags"("code");
|
|
CREATE TABLE "new_teachers" (
|
|
"id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
|
|
"tenant_id" INTEGER NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"phone" TEXT NOT NULL,
|
|
"email" TEXT,
|
|
"login_account" TEXT NOT NULL,
|
|
"password_hash" TEXT NOT NULL,
|
|
"class_ids" TEXT DEFAULT '[]',
|
|
"status" TEXT NOT NULL DEFAULT 'ACTIVE',
|
|
"lesson_count" INTEGER NOT NULL DEFAULT 0,
|
|
"feedback_count" INTEGER NOT NULL DEFAULT 0,
|
|
"created_at" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updated_at" DATETIME NOT NULL,
|
|
"last_login_at" DATETIME,
|
|
CONSTRAINT "teachers_tenant_id_fkey" FOREIGN KEY ("tenant_id") REFERENCES "tenants" ("id") ON DELETE CASCADE ON UPDATE CASCADE
|
|
);
|
|
INSERT INTO "new_teachers" ("class_ids", "created_at", "email", "feedback_count", "id", "last_login_at", "lesson_count", "login_account", "name", "password_hash", "phone", "status", "tenant_id", "updated_at") SELECT "class_ids", "created_at", "email", "feedback_count", "id", "last_login_at", "lesson_count", "login_account", "name", "password_hash", "phone", "status", "tenant_id", "updated_at" FROM "teachers";
|
|
DROP TABLE "teachers";
|
|
ALTER TABLE "new_teachers" RENAME TO "teachers";
|
|
CREATE UNIQUE INDEX "teachers_login_account_key" ON "teachers"("login_account");
|
|
CREATE TABLE "new_tenant_courses" (
|
|
"id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
|
|
"tenant_id" INTEGER NOT NULL,
|
|
"course_id" INTEGER NOT NULL,
|
|
"authorized" BOOLEAN NOT NULL DEFAULT true,
|
|
"authorized_at" DATETIME,
|
|
"created_at" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
CONSTRAINT "tenant_courses_tenant_id_fkey" FOREIGN KEY ("tenant_id") REFERENCES "tenants" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
CONSTRAINT "tenant_courses_course_id_fkey" FOREIGN KEY ("course_id") REFERENCES "courses" ("id") ON DELETE CASCADE ON UPDATE CASCADE
|
|
);
|
|
INSERT INTO "new_tenant_courses" ("authorized", "authorized_at", "course_id", "created_at", "id", "tenant_id") SELECT "authorized", "authorized_at", "course_id", "created_at", "id", "tenant_id" FROM "tenant_courses";
|
|
DROP TABLE "tenant_courses";
|
|
ALTER TABLE "new_tenant_courses" RENAME TO "tenant_courses";
|
|
CREATE UNIQUE INDEX "tenant_courses_tenant_id_course_id_key" ON "tenant_courses"("tenant_id", "course_id");
|
|
CREATE TABLE "new_tenants" (
|
|
"id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
|
|
"name" TEXT NOT NULL,
|
|
"address" TEXT,
|
|
"contact_person" TEXT,
|
|
"contact_phone" TEXT,
|
|
"logo_url" TEXT,
|
|
"package_type" TEXT NOT NULL DEFAULT 'STANDARD',
|
|
"teacher_quota" INTEGER NOT NULL DEFAULT 20,
|
|
"student_quota" INTEGER NOT NULL DEFAULT 200,
|
|
"storage_quota" BIGINT NOT NULL DEFAULT 5368709120,
|
|
"start_date" TEXT NOT NULL,
|
|
"expire_date" TEXT NOT NULL,
|
|
"teacher_count" INTEGER NOT NULL DEFAULT 0,
|
|
"student_count" INTEGER NOT NULL DEFAULT 0,
|
|
"storage_used" BIGINT NOT NULL DEFAULT 0,
|
|
"status" TEXT NOT NULL DEFAULT 'ACTIVE',
|
|
"created_at" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updated_at" DATETIME NOT NULL
|
|
);
|
|
INSERT INTO "new_tenants" ("address", "contact_person", "contact_phone", "created_at", "expire_date", "id", "logo_url", "name", "package_type", "start_date", "status", "storage_quota", "storage_used", "student_count", "student_quota", "teacher_count", "teacher_quota", "updated_at") SELECT "address", "contact_person", "contact_phone", "created_at", "expire_date", "id", "logo_url", "name", "package_type", "start_date", "status", "storage_quota", "storage_used", "student_count", "student_quota", "teacher_count", "teacher_quota", "updated_at" FROM "tenants";
|
|
DROP TABLE "tenants";
|
|
ALTER TABLE "new_tenants" RENAME TO "tenants";
|
|
PRAGMA foreign_keys=ON;
|
|
PRAGMA defer_foreign_keys=OFF;
|