Mirror of Lumina
strawmelonjuice.com
1/*
2 * Lumina/Peonies
3 * Copyright (C) 2018-2026 MLC 'Strawmelonjuice' Bloeiman and contributors.
4 *
5 * This program is free software: you can redistribute it and/or modify
6 * it under the terms of the GNU Affero General Public License as published
7 * by the Free Software Foundation, either version 3 of the License, or
8 * (at your option) any later version.
9 *
10 * This program is distributed in the hope that it will be useful,
11 * but WITHOUT ANY WARRANTY; without even the implied warranty of
12 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
13 * GNU Affero General Public License for more details.
14 *
15 * You should have received a copy of the GNU Affero General Public License
16 * along with this program. If not, see <https://www.gnu.org/licenses/>.
17 */
18
19/*
20 This file is also auto-included by server/src/database.rs, which uses it to create the database on first launch.
21 */
22
23-- Create logs table
24CREATE TABLE IF NOT EXISTS logs
25(
26 type VARCHAR NOT NULL,
27 message TEXT NOT NULL,
28 timestamp TIMESTAMP NOT NULL
29);
30
31-- Create users table
32CREATE TABLE IF NOT EXISTS users
33(
34 id UUID DEFAULT gen_random_uuid() UNIQUE PRIMARY KEY,
35 foreign_instance_id VARCHAR,
36 foreign_user_id UUID,
37 email VARCHAR NOT NULL UNIQUE,
38 username VARCHAR NOT NULL UNIQUE,
39 password VARCHAR NOT NULL
40);
41
42-- Create timelines table
43CREATE TABLE IF NOT EXISTS timelines
44(
45 tlid UUID NOT NULL,
46 item_id UUID NOT NULL,
47 timestamp TIMESTAMP WITH TIME ZONE NOT NULL,
48 PRIMARY KEY (tlid, item_id)
49);
50
51-- Create item type lookup table
52CREATE TABLE IF NOT EXISTS itemtypelookupdb
53(
54 itemtype VARCHAR NOT NULL,
55 item_id UUID NOT NULL PRIMARY KEY
56);
57
58-- Create sesions table
59CREATE TABLE IF NOT EXISTS sessions
60(
61 id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
62 user_id UUID NOT NULL REFERENCES users (id) ON DELETE CASCADE,
63 session_key VARCHAR NOT NULL UNIQUE,
64 created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
65);
66
67-- Create table for posts in text type
68CREATE TABLE IF NOT EXISTS post_text
69(
70 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
71 author_id UUID REFERENCES users (id),
72 content TEXT NOT NULL,
73 created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
74 foreign_instance_id VARCHAR,
75 foreign_post_id VARCHAR
76);
77
78-- Create table for posts of media type
79CREATE TABLE IF NOT EXISTS post_media
80(
81 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
82 author_id UUID REFERENCES users (id),
83 minio_object_id VARCHAR NOT NULL,
84 caption TEXT,
85 created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
86 foreign_instance_id VARCHAR,
87 foreign_post_id VARCHAR
88);
89
90-- Create table for posts of article type
91CREATE TABLE IF NOT EXISTS post_article
92(
93 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
94 author_id UUID REFERENCES users (id),
95 title VARCHAR NOT NULL,
96 content TEXT NOT NULL,
97 created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
98 foreign_instance_id VARCHAR,
99 foreign_post_id VARCHAR
100);