46 lines
2.3 KiB
SQL
46 lines
2.3 KiB
SQL
CREATE TABLE "course_maps" (
|
|
"id" UUID NOT NULL,
|
|
"course_id" UUID NOT NULL,
|
|
"key" TEXT NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"description" TEXT,
|
|
"platform_key" TEXT NOT NULL DEFAULT 'NAV2',
|
|
"world_file" TEXT NOT NULL DEFAULT 'empty_world.world',
|
|
"viewer_model" TEXT NOT NULL DEFAULT 'turtlebot3_world',
|
|
"layout" JSONB NOT NULL DEFAULT '{"version":1,"objects":[]}',
|
|
"created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updated_at" TIMESTAMPTZ(3) NOT NULL,
|
|
CONSTRAINT "course_maps_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE TABLE "course_week_maps" (
|
|
"id" UUID NOT NULL,
|
|
"course_id" UUID NOT NULL,
|
|
"week" INTEGER NOT NULL,
|
|
"map_key" TEXT NOT NULL,
|
|
"sort_order" INTEGER NOT NULL DEFAULT 0,
|
|
"created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updated_at" TIMESTAMPTZ(3) NOT NULL,
|
|
CONSTRAINT "course_week_maps_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE UNIQUE INDEX "course_maps_course_id_key_key" ON "course_maps"("course_id", "key");
|
|
CREATE INDEX "course_maps_course_id_platform_key_idx" ON "course_maps"("course_id", "platform_key");
|
|
CREATE UNIQUE INDEX "course_week_maps_course_id_week_map_key_key" ON "course_week_maps"("course_id", "week", "map_key");
|
|
CREATE INDEX "course_week_maps_course_id_week_idx" ON "course_week_maps"("course_id", "week");
|
|
|
|
ALTER TABLE "course_maps" ADD CONSTRAINT "course_maps_course_id_fkey" FOREIGN KEY ("course_id") REFERENCES "courses"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
ALTER TABLE "course_week_maps" ADD CONSTRAINT "course_week_maps_course_id_fkey" FOREIGN KEY ("course_id") REFERENCES "courses"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
INSERT INTO "course_week_maps" ("id", "course_id", "week", "map_key", "sort_order", "updated_at")
|
|
SELECT gen_random_uuid(), "course_id", "week", 'nav2-turtlebot3-world', 0, CURRENT_TIMESTAMP
|
|
FROM "course_week_visualization_platforms"
|
|
WHERE "platform_key" = 'NAV2' AND "enabled" = true
|
|
ON CONFLICT ("course_id", "week", "map_key") DO NOTHING;
|
|
|
|
INSERT INTO "course_week_maps" ("id", "course_id", "week", "map_key", "sort_order", "updated_at")
|
|
SELECT gen_random_uuid(), "course_id", "week", 'nav2-turtlebot3-house', 1, CURRENT_TIMESTAMP
|
|
FROM "course_week_visualization_platforms"
|
|
WHERE "platform_key" = 'NAV2' AND "enabled" = true
|
|
ON CONFLICT ("course_id", "week", "map_key") DO NOTHING;
|