13e91e628d
Redirect /teacher/exams/grading* to /teacher/homework/submissions; remove exam grading UI/actions/data-access; add homework student workflow and update design docs.
275 lines
12 KiB
SQL
275 lines
12 KiB
SQL
CREATE TABLE IF NOT EXISTS `homework_answers` (
|
|
`id` varchar(128) NOT NULL,
|
|
`submission_id` varchar(128) NOT NULL,
|
|
`question_id` varchar(128) NOT NULL,
|
|
`answer_content` json,
|
|
`score` int,
|
|
`feedback` text,
|
|
`created_at` timestamp NOT NULL DEFAULT (now()),
|
|
`updated_at` timestamp NOT NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP,
|
|
CONSTRAINT `homework_answers_id` PRIMARY KEY(`id`)
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE IF NOT EXISTS `homework_assignment_questions` (
|
|
`assignment_id` varchar(128) NOT NULL,
|
|
`question_id` varchar(128) NOT NULL,
|
|
`score` int DEFAULT 0,
|
|
`order` int DEFAULT 0,
|
|
CONSTRAINT `homework_assignment_questions_assignment_id_question_id_pk` PRIMARY KEY(`assignment_id`,`question_id`)
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE IF NOT EXISTS `homework_assignment_targets` (
|
|
`assignment_id` varchar(128) NOT NULL,
|
|
`student_id` varchar(128) NOT NULL,
|
|
`created_at` timestamp NOT NULL DEFAULT (now()),
|
|
CONSTRAINT `homework_assignment_targets_assignment_id_student_id_pk` PRIMARY KEY(`assignment_id`,`student_id`)
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE IF NOT EXISTS `homework_assignments` (
|
|
`id` varchar(128) NOT NULL,
|
|
`source_exam_id` varchar(128) NOT NULL,
|
|
`title` varchar(255) NOT NULL,
|
|
`description` text,
|
|
`structure` json,
|
|
`status` varchar(50) DEFAULT 'draft',
|
|
`creator_id` varchar(128) NOT NULL,
|
|
`available_at` timestamp,
|
|
`due_at` timestamp,
|
|
`allow_late` boolean NOT NULL DEFAULT false,
|
|
`late_due_at` timestamp,
|
|
`max_attempts` int NOT NULL DEFAULT 1,
|
|
`created_at` timestamp NOT NULL DEFAULT (now()),
|
|
`updated_at` timestamp NOT NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP,
|
|
CONSTRAINT `homework_assignments_id` PRIMARY KEY(`id`)
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE IF NOT EXISTS `homework_submissions` (
|
|
`id` varchar(128) NOT NULL,
|
|
`assignment_id` varchar(128) NOT NULL,
|
|
`student_id` varchar(128) NOT NULL,
|
|
`attempt_no` int NOT NULL DEFAULT 1,
|
|
`score` int,
|
|
`status` varchar(50) DEFAULT 'started',
|
|
`started_at` timestamp NOT NULL DEFAULT (now()),
|
|
`submitted_at` timestamp,
|
|
`is_late` boolean NOT NULL DEFAULT false,
|
|
`created_at` timestamp NOT NULL DEFAULT (now()),
|
|
`updated_at` timestamp NOT NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP,
|
|
CONSTRAINT `homework_submissions_id` PRIMARY KEY(`id`)
|
|
);
|
|
--> statement-breakpoint
|
|
SET @__qkp_drop_qid := (
|
|
SELECT IF(
|
|
EXISTS(
|
|
SELECT 1
|
|
FROM information_schema.referential_constraints
|
|
WHERE constraint_schema = DATABASE()
|
|
AND constraint_name = 'questions_to_knowledge_points_question_id_questions_id_fk'
|
|
),
|
|
'ALTER TABLE `questions_to_knowledge_points` DROP FOREIGN KEY `questions_to_knowledge_points_question_id_questions_id_fk`;',
|
|
'SELECT 1;'
|
|
)
|
|
);--> statement-breakpoint
|
|
PREPARE __stmt FROM @__qkp_drop_qid;--> statement-breakpoint
|
|
EXECUTE __stmt;--> statement-breakpoint
|
|
DEALLOCATE PREPARE __stmt;--> statement-breakpoint
|
|
SET @__qkp_drop_kpid := (
|
|
SELECT IF(
|
|
EXISTS(
|
|
SELECT 1
|
|
FROM information_schema.referential_constraints
|
|
WHERE constraint_schema = DATABASE()
|
|
AND constraint_name = 'questions_to_knowledge_points_knowledge_point_id_knowledge_points_id_fk'
|
|
),
|
|
'ALTER TABLE `questions_to_knowledge_points` DROP FOREIGN KEY `questions_to_knowledge_points_knowledge_point_id_knowledge_points_id_fk`;',
|
|
'SELECT 1;'
|
|
)
|
|
);--> statement-breakpoint
|
|
PREPARE __stmt2 FROM @__qkp_drop_kpid;--> statement-breakpoint
|
|
EXECUTE __stmt2;--> statement-breakpoint
|
|
DEALLOCATE PREPARE __stmt2;--> statement-breakpoint
|
|
ALTER TABLE `homework_answers` ADD CONSTRAINT `hw_ans_sub_fk` FOREIGN KEY (`submission_id`) REFERENCES `homework_submissions`(`id`) ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE `homework_answers` ADD CONSTRAINT `hw_ans_q_fk` FOREIGN KEY (`question_id`) REFERENCES `questions`(`id`) ON DELETE no action ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE `homework_assignment_questions` ADD CONSTRAINT `hw_aq_a_fk` FOREIGN KEY (`assignment_id`) REFERENCES `homework_assignments`(`id`) ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE `homework_assignment_questions` ADD CONSTRAINT `hw_aq_q_fk` FOREIGN KEY (`question_id`) REFERENCES `questions`(`id`) ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE `homework_assignment_targets` ADD CONSTRAINT `hw_at_a_fk` FOREIGN KEY (`assignment_id`) REFERENCES `homework_assignments`(`id`) ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE `homework_assignment_targets` ADD CONSTRAINT `hw_at_s_fk` FOREIGN KEY (`student_id`) REFERENCES `users`(`id`) ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE `homework_assignments` ADD CONSTRAINT `hw_asg_exam_fk` FOREIGN KEY (`source_exam_id`) REFERENCES `exams`(`id`) ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE `homework_assignments` ADD CONSTRAINT `hw_asg_creator_fk` FOREIGN KEY (`creator_id`) REFERENCES `users`(`id`) ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE `homework_submissions` ADD CONSTRAINT `hw_sub_a_fk` FOREIGN KEY (`assignment_id`) REFERENCES `homework_assignments`(`id`) ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE `homework_submissions` ADD CONSTRAINT `hw_sub_student_fk` FOREIGN KEY (`student_id`) REFERENCES `users`(`id`) ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
SET @__idx_hw_answer_submission := (
|
|
SELECT IF(
|
|
EXISTS(
|
|
SELECT 1
|
|
FROM information_schema.statistics
|
|
WHERE table_schema = DATABASE()
|
|
AND table_name = 'homework_answers'
|
|
AND index_name = 'hw_answer_submission_idx'
|
|
),
|
|
'SELECT 1;',
|
|
'CREATE INDEX `hw_answer_submission_idx` ON `homework_answers` (`submission_id`);'
|
|
)
|
|
);--> statement-breakpoint
|
|
PREPARE __stmt3 FROM @__idx_hw_answer_submission;--> statement-breakpoint
|
|
EXECUTE __stmt3;--> statement-breakpoint
|
|
DEALLOCATE PREPARE __stmt3;--> statement-breakpoint
|
|
SET @__idx_hw_answer_submission_question := (
|
|
SELECT IF(
|
|
EXISTS(
|
|
SELECT 1
|
|
FROM information_schema.statistics
|
|
WHERE table_schema = DATABASE()
|
|
AND table_name = 'homework_answers'
|
|
AND index_name = 'hw_answer_submission_question_idx'
|
|
),
|
|
'SELECT 1;',
|
|
'CREATE INDEX `hw_answer_submission_question_idx` ON `homework_answers` (`submission_id`,`question_id`);'
|
|
)
|
|
);--> statement-breakpoint
|
|
PREPARE __stmt4 FROM @__idx_hw_answer_submission_question;--> statement-breakpoint
|
|
EXECUTE __stmt4;--> statement-breakpoint
|
|
DEALLOCATE PREPARE __stmt4;--> statement-breakpoint
|
|
SET @__idx_hw_assignment_questions_assignment := (
|
|
SELECT IF(
|
|
EXISTS(
|
|
SELECT 1
|
|
FROM information_schema.statistics
|
|
WHERE table_schema = DATABASE()
|
|
AND table_name = 'homework_assignment_questions'
|
|
AND index_name = 'hw_assignment_questions_assignment_idx'
|
|
),
|
|
'SELECT 1;',
|
|
'CREATE INDEX `hw_assignment_questions_assignment_idx` ON `homework_assignment_questions` (`assignment_id`);'
|
|
)
|
|
);--> statement-breakpoint
|
|
PREPARE __stmt5 FROM @__idx_hw_assignment_questions_assignment;--> statement-breakpoint
|
|
EXECUTE __stmt5;--> statement-breakpoint
|
|
DEALLOCATE PREPARE __stmt5;--> statement-breakpoint
|
|
SET @__idx_hw_assignment_targets_assignment := (
|
|
SELECT IF(
|
|
EXISTS(
|
|
SELECT 1
|
|
FROM information_schema.statistics
|
|
WHERE table_schema = DATABASE()
|
|
AND table_name = 'homework_assignment_targets'
|
|
AND index_name = 'hw_assignment_targets_assignment_idx'
|
|
),
|
|
'SELECT 1;',
|
|
'CREATE INDEX `hw_assignment_targets_assignment_idx` ON `homework_assignment_targets` (`assignment_id`);'
|
|
)
|
|
);--> statement-breakpoint
|
|
PREPARE __stmt6 FROM @__idx_hw_assignment_targets_assignment;--> statement-breakpoint
|
|
EXECUTE __stmt6;--> statement-breakpoint
|
|
DEALLOCATE PREPARE __stmt6;--> statement-breakpoint
|
|
SET @__idx_hw_assignment_targets_student := (
|
|
SELECT IF(
|
|
EXISTS(
|
|
SELECT 1
|
|
FROM information_schema.statistics
|
|
WHERE table_schema = DATABASE()
|
|
AND table_name = 'homework_assignment_targets'
|
|
AND index_name = 'hw_assignment_targets_student_idx'
|
|
),
|
|
'SELECT 1;',
|
|
'CREATE INDEX `hw_assignment_targets_student_idx` ON `homework_assignment_targets` (`student_id`);'
|
|
)
|
|
);--> statement-breakpoint
|
|
PREPARE __stmt7 FROM @__idx_hw_assignment_targets_student;--> statement-breakpoint
|
|
EXECUTE __stmt7;--> statement-breakpoint
|
|
DEALLOCATE PREPARE __stmt7;--> statement-breakpoint
|
|
SET @__idx_hw_assignment_creator := (
|
|
SELECT IF(
|
|
EXISTS(
|
|
SELECT 1
|
|
FROM information_schema.statistics
|
|
WHERE table_schema = DATABASE()
|
|
AND table_name = 'homework_assignments'
|
|
AND index_name = 'hw_assignment_creator_idx'
|
|
),
|
|
'SELECT 1;',
|
|
'CREATE INDEX `hw_assignment_creator_idx` ON `homework_assignments` (`creator_id`);'
|
|
)
|
|
);--> statement-breakpoint
|
|
PREPARE __stmt8 FROM @__idx_hw_assignment_creator;--> statement-breakpoint
|
|
EXECUTE __stmt8;--> statement-breakpoint
|
|
DEALLOCATE PREPARE __stmt8;--> statement-breakpoint
|
|
SET @__idx_hw_assignment_source_exam := (
|
|
SELECT IF(
|
|
EXISTS(
|
|
SELECT 1
|
|
FROM information_schema.statistics
|
|
WHERE table_schema = DATABASE()
|
|
AND table_name = 'homework_assignments'
|
|
AND index_name = 'hw_assignment_source_exam_idx'
|
|
),
|
|
'SELECT 1;',
|
|
'CREATE INDEX `hw_assignment_source_exam_idx` ON `homework_assignments` (`source_exam_id`);'
|
|
)
|
|
);--> statement-breakpoint
|
|
PREPARE __stmt9 FROM @__idx_hw_assignment_source_exam;--> statement-breakpoint
|
|
EXECUTE __stmt9;--> statement-breakpoint
|
|
DEALLOCATE PREPARE __stmt9;--> statement-breakpoint
|
|
SET @__idx_hw_assignment_status := (
|
|
SELECT IF(
|
|
EXISTS(
|
|
SELECT 1
|
|
FROM information_schema.statistics
|
|
WHERE table_schema = DATABASE()
|
|
AND table_name = 'homework_assignments'
|
|
AND index_name = 'hw_assignment_status_idx'
|
|
),
|
|
'SELECT 1;',
|
|
'CREATE INDEX `hw_assignment_status_idx` ON `homework_assignments` (`status`);'
|
|
)
|
|
);--> statement-breakpoint
|
|
PREPARE __stmt10 FROM @__idx_hw_assignment_status;--> statement-breakpoint
|
|
EXECUTE __stmt10;--> statement-breakpoint
|
|
DEALLOCATE PREPARE __stmt10;--> statement-breakpoint
|
|
SET @__idx_hw_assignment_student := (
|
|
SELECT IF(
|
|
EXISTS(
|
|
SELECT 1
|
|
FROM information_schema.statistics
|
|
WHERE table_schema = DATABASE()
|
|
AND table_name = 'homework_submissions'
|
|
AND index_name = 'hw_assignment_student_idx'
|
|
),
|
|
'SELECT 1;',
|
|
'CREATE INDEX `hw_assignment_student_idx` ON `homework_submissions` (`assignment_id`,`student_id`);'
|
|
)
|
|
);--> statement-breakpoint
|
|
PREPARE __stmt11 FROM @__idx_hw_assignment_student;--> statement-breakpoint
|
|
EXECUTE __stmt11;--> statement-breakpoint
|
|
DEALLOCATE PREPARE __stmt11;--> statement-breakpoint
|
|
SET @__qkp_add_qid := (
|
|
SELECT IF(
|
|
EXISTS(
|
|
SELECT 1
|
|
FROM information_schema.referential_constraints
|
|
WHERE constraint_schema = DATABASE()
|
|
AND constraint_name = 'q_kp_qid_fk'
|
|
),
|
|
'SELECT 1;',
|
|
'ALTER TABLE `questions_to_knowledge_points` ADD CONSTRAINT `q_kp_qid_fk` FOREIGN KEY (`question_id`) REFERENCES `questions`(`id`) ON DELETE cascade ON UPDATE no action;'
|
|
)
|
|
);--> statement-breakpoint
|
|
PREPARE __stmt12 FROM @__qkp_add_qid;--> statement-breakpoint
|
|
EXECUTE __stmt12;--> statement-breakpoint
|
|
DEALLOCATE PREPARE __stmt12;--> statement-breakpoint
|
|
SET @__qkp_add_kpid := (
|
|
SELECT IF(
|
|
EXISTS(
|
|
SELECT 1
|
|
FROM information_schema.referential_constraints
|
|
WHERE constraint_schema = DATABASE()
|
|
AND constraint_name = 'q_kp_kpid_fk'
|
|
),
|
|
'SELECT 1;',
|
|
'ALTER TABLE `questions_to_knowledge_points` ADD CONSTRAINT `q_kp_kpid_fk` FOREIGN KEY (`knowledge_point_id`) REFERENCES `knowledge_points`(`id`) ON DELETE cascade ON UPDATE no action;'
|
|
)
|
|
);--> statement-breakpoint
|
|
PREPARE __stmt13 FROM @__qkp_add_kpid;--> statement-breakpoint
|
|
EXECUTE __stmt13;--> statement-breakpoint
|
|
DEALLOCATE PREPARE __stmt13;
|