51 lines
1.4 KiB
SQL
51 lines
1.4 KiB
SQL
--
|
|
-- This file is used to create the database schema. It is a copy of the `bb_todo-datamodel.sql` file
|
|
-- generated by the basebox compiler.
|
|
--
|
|
|
|
--
|
|
-- Generated by basebox compiler (bbc) version 0.1.0-beta.23 at 2023-11-01 10:35:59+01:00
|
|
--
|
|
CREATE EXTENSION IF NOT EXISTS pgcrypto;
|
|
|
|
CREATE TABLE "List" (
|
|
"id" UUID DEFAULT gen_random_uuid() NOT NULL,
|
|
"title" VARCHAR NOT NULL,
|
|
"user_username" VARCHAR NOT NULL
|
|
);
|
|
|
|
CREATE TABLE "Task" (
|
|
"id" UUID DEFAULT gen_random_uuid() NOT NULL,
|
|
"title" VARCHAR NOT NULL,
|
|
"description" VARCHAR,
|
|
"completed" BOOLEAN NOT NULL,
|
|
"user_username" VARCHAR NOT NULL,
|
|
"list_id" UUID NOT NULL
|
|
);
|
|
|
|
CREATE TABLE "User" (
|
|
"username" VARCHAR NOT NULL,
|
|
"name" VARCHAR
|
|
);
|
|
|
|
ALTER TABLE "List" ADD COLUMN ".ownerId" VARCHAR NOT NULL;
|
|
|
|
ALTER TABLE "Task" ADD COLUMN ".ownerId" VARCHAR NOT NULL;
|
|
|
|
ALTER TABLE "User" ADD COLUMN ".ownerId" VARCHAR NOT NULL;
|
|
|
|
ALTER TABLE "List" ADD PRIMARY KEY ("id");
|
|
|
|
ALTER TABLE "Task" ADD PRIMARY KEY ("id");
|
|
|
|
ALTER TABLE "User" ADD PRIMARY KEY ("username");
|
|
|
|
ALTER TABLE "List" ADD CONSTRAINT fk_list_1 FOREIGN KEY ("user_username") REFERENCES "User" ("username");
|
|
|
|
ALTER TABLE "Task" ADD CONSTRAINT fk_task_2 FOREIGN KEY ("user_username") REFERENCES "User" ("username");
|
|
|
|
ALTER TABLE "Task" ADD CONSTRAINT fk_task_3 FOREIGN KEY ("list_id") REFERENCES "List" ("id");
|
|
|
|
ALTER TABLE "User" ADD CONSTRAINT uq_user_4 UNIQUE (".ownerId");
|
|
|