Changes between Initial Version and Version 1 of ddlScript.sql


Ignore:
Timestamp:
02/13/26 20:53:07 (5 weeks ago)
Author:
211099
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • ddlScript.sql

    v1 v1  
     1{{{#!sql
     2-- Delete tables if they exist
     3DROP TABLE IF EXISTS ROLES CASCADE;
     4DROP TABLE IF EXISTS PERMISSION_LEVEL CASCADE;
     5DROP TABLE IF EXISTS SUGGESTION_TYPE CASCADE;
     6DROP TABLE IF EXISTS CONTENT_TYPE CASCADE;
     7DROP TABLE IF EXISTS READING_LIST_ITEMS CASCADE;
     8DROP TABLE IF EXISTS HAS_GENRE CASCADE;
     9DROP TABLE IF EXISTS COLLABORATION CASCADE;
     10DROP TABLE IF EXISTS COMMENT CASCADE;
     11DROP TABLE IF EXISTS LIKES CASCADE;
     12DROP TABLE IF EXISTS AI_SUGGESTION CASCADE;
     13DROP TABLE IF EXISTS NOTIFICATION CASCADE;
     14DROP TABLE IF EXISTS READING_LIST CASCADE;
     15DROP TABLE IF EXISTS CHAPTER CASCADE;
     16DROP TABLE IF EXISTS STATUS CASCADE;
     17DROP TABLE IF EXISTS STORY CASCADE;
     18DROP TABLE IF EXISTS WRITER CASCADE;
     19DROP TABLE IF EXISTS REGULAR_USER CASCADE;
     20DROP TABLE IF EXISTS ADMINS CASCADE;
     21DROP TABLE IF EXISTS GENRE CASCADE;
     22DROP TABLE IF EXISTS USERS CASCADE;
     23
     24-- Tables
     25----
     26CREATE TABLE USERS(
     27        user_id SERIAL PRIMARY KEY,
     28        username VARCHAR(255) NOT NULL UNIQUE,
     29        email VARCHAR(255) NOT NULL UNIQUE,
     30        name VARCHAR(100) NOT NULL,
     31        surname VARCHAR(100) NOT NULL,
     32        password VARCHAR(255) NOT NULL,
     33        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
     34        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
     35        CONSTRAINT email_format CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$')
     36);
     37
     38CREATE TABLE ADMINS(
     39        user_id INTEGER PRIMARY KEY,
     40        FOREIGN KEY (user_id) REFERENCES USERS(user_id)
     41        ON DELETE CASCADE
     42        ON UPDATE CASCADE
     43);
     44
     45CREATE TABLE REGULAR_USER(
     46        user_id INTEGER PRIMARY KEY,
     47        FOREIGN KEY (user_id) REFERENCES USERS(user_id)
     48        ON DELETE CASCADE
     49        ON UPDATE CASCADE
     50);
     51
     52CREATE TABLE WRITER(
     53        user_id INTEGER PRIMARY KEY,
     54        created_story BOOLEAN,
     55        FOREIGN KEY (user_id) REFERENCES USERS(user_id)
     56        ON DELETE CASCADE
     57        ON UPDATE CASCADE
     58);
     59
     60CREATE TABLE STORY(
     61        story_id SERIAL PRIMARY KEY,
     62        mature_content BOOLEAN NOT NULL,
     63        short_description VARCHAR(500) NOT NULL,
     64        image VARCHAR(2048),
     65        content TEXT NOT NULL,
     66        user_id INTEGER NOT NULL,
     67        created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
     68        updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
     69        FOREIGN KEY (user_id) REFERENCES WRITER(user_id)
     70        ON DELETE CASCADE
     71        ON UPDATE CASCADE
     72);
     73
     74CREATE TABLE STATUS(
     75        story_id INTEGER,
     76        status VARCHAR(50) NOT NULL,
     77        CONSTRAINT status_pk PRIMARY KEY(story_id, status),
     78        FOREIGN KEY (story_id) REFERENCES STORY(story_id)
     79        ON DELETE CASCADE
     80        ON UPDATE CASCADE
     81);
     82
     83CREATE TABLE CHAPTER(
     84        chapter_id SERIAL PRIMARY KEY,
     85        chapter_number INTEGER NOT NULL,
     86        chapter_name VARCHAR(100) NOT NULL,
     87        title VARCHAR(200) NOT NULL,
     88        content TEXT NOT NULL,
     89        word_count INTEGER CHECK (word_count >= 0),
     90        rating DECIMAL(3,2) CHECK (rating >= 0 AND rating <= 5),
     91        published_at TIMESTAMPTZ NOT NULL,
     92        view_count INTEGER DEFAULT 0 CHECK (view_count >= 0),
     93        story_id INTEGER NOT NULL,
     94        created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
     95        updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
     96        CONSTRAINT unique_chapter_number UNIQUE(story_id, chapter_number),
     97        FOREIGN KEY (story_id) REFERENCES STORY(story_id)
     98        ON DELETE CASCADE
     99        ON UPDATE CASCADE
     100);
     101
     102CREATE TABLE GENRE(
     103        genre_id SERIAL PRIMARY KEY,
     104        name VARCHAR(100) NOT NULL UNIQUE
     105);
     106
     107CREATE TABLE READING_LIST(
     108        list_id SERIAL PRIMARY KEY,
     109        name VARCHAR(100) NOT NULL,
     110        content TEXT,
     111        is_public BOOLEAN NOT NULL,
     112        user_id INTEGER NOT NULL,
     113        created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
     114        updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
     115        FOREIGN KEY (user_id) REFERENCES USERS(user_id)
     116        ON DELETE CASCADE
     117        ON UPDATE CASCADE
     118);
     119
     120CREATE TABLE NOTIFICATION(
     121        notification_id SERIAL PRIMARY KEY,
     122        content TEXT NOT NULL,
     123        is_read BOOLEAN DEFAULT FALSE,
     124        user_id INTEGER NOT NULL,
     125        created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
     126        FOREIGN KEY (user_id) REFERENCES USERS(user_id)
     127        ON DELETE CASCADE
     128        ON UPDATE CASCADE
     129);
     130
     131CREATE TABLE CONTENT_TYPE(
     132        notification_id INTEGER,
     133        content_type VARCHAR(50) NOT NULL,
     134        CONSTRAINT content_type_pk PRIMARY KEY(notification_id, content_type),
     135        FOREIGN KEY (notification_id) REFERENCES NOTIFICATION(notification_id)
     136        ON DELETE CASCADE
     137        ON UPDATE CASCADE
     138);
     139
     140CREATE TABLE AI_SUGGESTION(
     141        suggestion_id SERIAL PRIMARY KEY,
     142        original_text TEXT NOT NULL,
     143        suggested_text TEXT NOT NULL,
     144        accepted BOOLEAN NOT NULL DEFAULT FALSE,
     145        story_id INTEGER NOT NULL,
     146        created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
     147        applied_at TIMESTAMPTZ,
     148        FOREIGN KEY (story_id) REFERENCES STORY(story_id)
     149        ON DELETE CASCADE
     150        ON UPDATE CASCADE
     151);
     152
     153CREATE TABLE SUGGESTION_TYPE(
     154        suggestion_id INTEGER,
     155        suggestion_type VARCHAR(50) NOT NULL,
     156        CONSTRAINT suggestion_type_pk PRIMARY KEY(suggestion_id, suggestion_type),
     157        FOREIGN KEY (suggestion_id) REFERENCES AI_SUGGESTION(suggestion_id)
     158        ON DELETE CASCADE
     159        ON UPDATE CASCADE
     160);
     161
     162CREATE TABLE LIKES(
     163        user_id INTEGER,
     164        story_id INTEGER,
     165        created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
     166        CONSTRAINT like_pk PRIMARY KEY(user_id, story_id),
     167        FOREIGN KEY (user_id) REFERENCES USERS(user_id)
     168        ON DELETE CASCADE
     169        ON UPDATE CASCADE,
     170        FOREIGN KEY (story_id) REFERENCES STORY(story_id)
     171        ON DELETE CASCADE
     172        ON UPDATE CASCADE
     173);
     174
     175CREATE TABLE COMMENT(
     176        comment_id SERIAL PRIMARY KEY,
     177        content TEXT NOT NULL,
     178        user_id INTEGER NOT NULL,
     179        story_id INTEGER NOT NULL,
     180        created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
     181        updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
     182        FOREIGN KEY (user_id) REFERENCES USERS(user_id)
     183        ON DELETE CASCADE
     184        ON UPDATE CASCADE,
     185        FOREIGN KEY (story_id) REFERENCES STORY(story_id)
     186        ON DELETE CASCADE
     187        ON UPDATE CASCADE
     188);
     189
     190CREATE TABLE COLLABORATION(
     191        user_id INTEGER,
     192        story_id INTEGER,
     193        created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
     194        CONSTRAINT collaboration_pk PRIMARY KEY(user_id, story_id),
     195        FOREIGN KEY (user_id) REFERENCES USERS(user_id)
     196        ON DELETE CASCADE
     197        ON UPDATE CASCADE,
     198        FOREIGN KEY (story_id) REFERENCES STORY(story_id)
     199        ON DELETE CASCADE
     200        ON UPDATE CASCADE
     201);
     202
     203CREATE TABLE ROLES(
     204        user_id INTEGER,
     205        story_id INTEGER,
     206        roles VARCHAR(50) NOT NULL,
     207        CONSTRAINT role_pk PRIMARY KEY(user_id, story_id, roles),
     208        FOREIGN KEY (user_id, story_id) REFERENCES COLLABORATION(user_id, story_id)
     209        ON DELETE CASCADE
     210        ON UPDATE CASCADE
     211);
     212
     213CREATE TABLE PERMISSION_LEVEL(
     214        user_id INTEGER,
     215        story_id INTEGER,
     216        permission_level INTEGER NOT NULL,
     217        CONSTRAINT permission_level_pk PRIMARY KEY(user_id, story_id, permission_level),
     218        FOREIGN KEY (user_id, story_id) REFERENCES COLLABORATION(user_id, story_id)
     219        ON DELETE CASCADE
     220        ON UPDATE CASCADE
     221);
     222
     223CREATE TABLE HAS_GENRE(
     224        story_id INTEGER,
     225        genre_id INTEGER,
     226        CONSTRAINT has_genre_pk PRIMARY KEY(story_id, genre_id),
     227        FOREIGN KEY (story_id) REFERENCES STORY(story_id)
     228        ON DELETE CASCADE
     229        ON UPDATE CASCADE,
     230        FOREIGN KEY (genre_id) REFERENCES GENRE(genre_id)
     231        ON DELETE CASCADE
     232        ON UPDATE CASCADE
     233);
     234
     235CREATE TABLE READING_LIST_ITEMS(
     236        list_id INTEGER,
     237        story_id INTEGER,
     238        added_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
     239        CONSTRAINT reading_list_items_pk PRIMARY KEY(list_id, story_id),
     240        FOREIGN KEY (list_id) REFERENCES READING_LIST(list_id)
     241        ON DELETE CASCADE
     242        ON UPDATE CASCADE,
     243        FOREIGN KEY (story_id) REFERENCES STORY(story_id)
     244        ON DELETE CASCADE
     245        ON UPDATE CASCADE
     246);