60 lines
1.6 KiB
SQL
60 lines
1.6 KiB
SQL
CREATE TABLE "icon"(
|
|
"id" UUID DEFAULT gen_random_uuid () NOT NULL PRIMARY KEY,
|
|
"name" VARCHAR(255) NOT NULL,
|
|
"content_type" VARCHAR(255),
|
|
"path" VARCHAR(512) NOT NULL,
|
|
"url" VARCHAR(512),
|
|
"width" INT4,
|
|
"height" INT4,
|
|
"state" JSONB
|
|
);
|
|
|
|
CREATE TABLE "resource"(
|
|
"id" UUID DEFAULT gen_random_uuid () NOT NULL PRIMARY KEY,
|
|
"name" VARCHAR(255) NOT NULL,
|
|
"title" VARCHAR(255) NOT NULL,
|
|
"url" VARCHAR(512),
|
|
"icon" UUID,
|
|
"state" JSONB,
|
|
FOREIGN KEY ("icon") REFERENCES "icon"("id")
|
|
);
|
|
|
|
CREATE TABLE "recipe"(
|
|
"id" UUID DEFAULT gen_random_uuid () NOT NULL PRIMARY KEY,
|
|
"resource" UUID NOT NULL,
|
|
"quantity" INT4 NOT NULL,
|
|
"recipe_type" VARCHAR(50) NOT NULL,
|
|
"duration" INT4 NOT NULL,
|
|
"unit" VARCHAR(50) NOT NULL,
|
|
"state" JSONB,
|
|
FOREIGN KEY ("resource") REFERENCES "resource"("id")
|
|
);
|
|
|
|
CREATE TABLE "ingredient"(
|
|
"id" UUID DEFAULT gen_random_uuid () NOT NULL PRIMARY KEY,
|
|
"resource" UUID NOT NULL,
|
|
"quantity" INT4 NOT NULL,
|
|
"state" JSONB,
|
|
"recipe" UUID NOT NULL,
|
|
FOREIGN KEY ("resource") REFERENCES "resource"("id"),
|
|
FOREIGN KEY ("recipe") REFERENCES "recipe"("id")
|
|
);
|
|
|
|
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_icon_name
|
|
ON icon USING btree
|
|
(name COLLATE pg_catalog."default" ASC NULLS LAST);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_recipe_type
|
|
ON recipe USING btree
|
|
(recipe_type COLLATE pg_catalog."default" ASC NULLS LAST);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_resource_name
|
|
ON resource USING btree
|
|
(name COLLATE pg_catalog."default" ASC NULLS LAST);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_resource_title
|
|
ON resource USING btree
|
|
(title COLLATE pg_catalog."default" ASC NULLS LAST);
|