63 lines
1.7 KiB
SQL
63 lines
1.7 KiB
SQL
CREATE TABLE IF NOT EXISTS "messages" (
|
|
"id" INTEGER NOT NULL UNIQUE,
|
|
"user" INTEGER NOT NULL,
|
|
"thread" INTEGER NOT NULL,
|
|
"content" TEXT NOT NULL,
|
|
"time" INTEGER NOT NULL,
|
|
PRIMARY KEY("id"),
|
|
FOREIGN KEY ("user") REFERENCES "users"("id")
|
|
ON UPDATE NO ACTION ON DELETE NO ACTION,
|
|
FOREIGN KEY ("thread") REFERENCES "threads"("id")
|
|
ON UPDATE NO ACTION ON DELETE NO ACTION
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS "users" (
|
|
"id" INTEGER NOT NULL UNIQUE,
|
|
"name" TEXT NOT NULL,
|
|
"passhash" TEXT NOT NULL,
|
|
PRIMARY KEY("id")
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS "forums" (
|
|
"id" INTEGER NOT NULL UNIQUE,
|
|
"name" TEXT NOT NULL,
|
|
PRIMARY KEY("id")
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS "user_forums" (
|
|
"user_id" INTEGER NOT NULL,
|
|
"forum_id" INTEGER NOT NULL,
|
|
PRIMARY KEY("user_id", "forum_id"),
|
|
FOREIGN KEY ("forum_id") REFERENCES "forums"("id")
|
|
ON UPDATE NO ACTION ON DELETE NO ACTION,
|
|
FOREIGN KEY ("user_id") REFERENCES "users"("id")
|
|
ON UPDATE NO ACTION ON DELETE NO ACTION
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS "threads" (
|
|
"id" INTEGER NOT NULL UNIQUE,
|
|
"name" TEXT NOT NULL,
|
|
"forum" INTEGER NOT NULL,
|
|
PRIMARY KEY("id"),
|
|
FOREIGN KEY ("forum") REFERENCES "forums"("id")
|
|
ON UPDATE NO ACTION ON DELETE NO ACTION
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS "attachments" (
|
|
"id" INTEGER NOT NULL UNIQUE,
|
|
"message_id" INTEGER NOT NULL,
|
|
"file_path" TEXT NOT NULL,
|
|
PRIMARY KEY("id"),
|
|
FOREIGN KEY ("message_id") REFERENCES "messages"("id")
|
|
ON UPDATE NO ACTION ON DELETE NO ACTION
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS "user_forum_owners" (
|
|
"user_id" INTEGER NOT NULL,
|
|
"forum_id" INTEGER NOT NULL,
|
|
FOREIGN KEY ("user_id") REFERENCES "users"("id")
|
|
ON UPDATE NO ACTION ON DELETE NO ACTION,
|
|
FOREIGN KEY ("forum_id") REFERENCES "forums"("id")
|
|
ON UPDATE NO ACTION ON DELETE NO ACTION
|
|
);
|