Changes between Version 1 and Version 2 of ddlScript.sql


Ignore:
Timestamp:
03/03/26 23:56:39 (3 weeks ago)
Author:
211099
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • ddlScript.sql

    v1 v2  
    11{{{#!sql
    22-- Delete tables if they exist
     3DROP TABLE IF EXISTS NEED_APPROVAL CASCADE;
     4DROP TABLE IF EXISTS NOTIFY CASCADE;
    35DROP TABLE IF EXISTS ROLES CASCADE;
    46DROP TABLE IF EXISTS PERMISSION_LEVEL CASCADE;
    … …  
    2325
    2426-- Tables
    25 ----
     27
    2628CREATE 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,}$')
     29    user_id SERIAL PRIMARY KEY,
     30    username VARCHAR(255) NOT NULL UNIQUE,
     31    email VARCHAR(255) NOT NULL UNIQUE,
     32    name VARCHAR(100) NOT NULL,
     33    surname VARCHAR(100) NOT NULL,
     34    password VARCHAR(255) NOT NULL,
     35    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
     36    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
     37    CONSTRAINT email_format CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$')
    3638);
    3739
    3840CREATE TABLE ADMINS(
    39         user_id INTEGER PRIMARY KEY,
    40         FOREIGN KEY (user_id) REFERENCES USERS(user_id)
     41    user_id INTEGER PRIMARY KEY,
     42    FOREIGN KEY (user_id) REFERENCES USERS(user_id)
    4143        ON DELETE CASCADE
    4244        ON UPDATE CASCADE
    … …  
    4446
    4547CREATE TABLE REGULAR_USER(
    46         user_id INTEGER PRIMARY KEY,
    47         FOREIGN KEY (user_id) REFERENCES USERS(user_id)
     48    user_id INTEGER PRIMARY KEY,
     49    FOREIGN KEY (user_id) REFERENCES USERS(user_id)
    4850        ON DELETE CASCADE
    4951        ON UPDATE CASCADE
    … …  
    5153
    5254CREATE TABLE WRITER(
    53         user_id INTEGER PRIMARY KEY,
    54         created_story BOOLEAN,
    55         FOREIGN KEY (user_id) REFERENCES USERS(user_id)
     55    user_id INTEGER PRIMARY KEY,
     56    FOREIGN KEY (user_id) REFERENCES USERS(user_id)
    5657        ON DELETE CASCADE
    5758        ON UPDATE CASCADE
    … …  
    5960
    6061CREATE 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)
     62    story_id SERIAL PRIMARY KEY,
     63    mature_content BOOLEAN NOT NULL,
     64    short_description VARCHAR(500) NOT NULL,
     65    image VARCHAR(2048),
     66    content TEXT NOT NULL,
     67    user_id INTEGER NOT NULL,
     68    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
     69    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
     70    FOREIGN KEY (user_id) REFERENCES WRITER(user_id)
    7071        ON DELETE CASCADE
    7172        ON UPDATE CASCADE
    … …  
    7374
    7475CREATE 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)
     76    story_id INTEGER,
     77    status VARCHAR(50) NOT NULL,
     78    CONSTRAINT status_pk PRIMARY KEY(story_id, status),
     79    CONSTRAINT status_values CHECK (status IN ('draft', 'published', 'archived')),
     80    FOREIGN KEY (story_id) REFERENCES STORY(story_id)
    7981        ON DELETE CASCADE
    8082        ON UPDATE CASCADE
    … …  
    8284
    8385CREATE 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)
     86    chapter_id SERIAL PRIMARY KEY,
     87    chapter_number INTEGER NOT NULL,
     88    chapter_name VARCHAR(100) NOT NULL,
     89    title VARCHAR(200) NOT NULL,
     90    content TEXT NOT NULL,
     91    word_count INTEGER CHECK (word_count >= 0),
     92    rating DECIMAL(3,2) CHECK (rating >= 0 AND rating <= 5),
     93    published_at TIMESTAMPTZ NOT NULL,
     94    view_count INTEGER DEFAULT 0 CHECK (view_count >= 0),
     95    story_id INTEGER NOT NULL,
     96    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
     97    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
     98    CONSTRAINT unique_chapter_number UNIQUE(story_id, chapter_number),
     99    FOREIGN KEY (story_id) REFERENCES STORY(story_id)
    98100        ON DELETE CASCADE
    99101        ON UPDATE CASCADE
    … …  
    101103
    102104CREATE TABLE GENRE(
    103         genre_id SERIAL PRIMARY KEY,
    104         name VARCHAR(100) NOT NULL UNIQUE
     105    genre_id SERIAL PRIMARY KEY,
     106    name VARCHAR(100) NOT NULL UNIQUE
    105107);
    106108
    107109CREATE 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)
     110    list_id SERIAL PRIMARY KEY,
     111    name VARCHAR(100) NOT NULL,
     112    content TEXT,
     113    is_public BOOLEAN NOT NULL,
     114    user_id INTEGER NOT NULL,
     115    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
     116    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
     117    FOREIGN KEY (user_id) REFERENCES USERS(user_id)
    116118        ON DELETE CASCADE
    117119        ON UPDATE CASCADE
    … …  
    119121
    120122CREATE 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
     123    notification_id SERIAL PRIMARY KEY,
     124    content TEXT NOT NULL,
     125    is_read BOOLEAN DEFAULT FALSE,
     126    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
    129127);
    130128
    131129CREATE 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)
     130    notification_id INTEGER,
     131    content_type VARCHAR(50) NOT NULL,
     132    CONSTRAINT content_type_pk PRIMARY KEY(notification_id, content_type),
     133    FOREIGN KEY (notification_id) REFERENCES NOTIFICATION(notification_id)
    136134        ON DELETE CASCADE
    137135        ON UPDATE CASCADE
    … …  
    139137
    140138CREATE 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
     139    suggestion_id SERIAL PRIMARY KEY,
     140    original_text TEXT NOT NULL,
     141    suggested_text TEXT NOT NULL,
     142    accepted BOOLEAN NOT NULL DEFAULT FALSE,
     143    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
     144    applied_at TIMESTAMPTZ,
     145    story_id INTEGER NOT NULL,
     146    FOREIGN KEY (story_id) REFERENCES STORY(story_id)
     147       ON DELETE CASCADE
     148       ON UPDATE CASCADE
    151149);
    152150
    153151CREATE 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)
     152    suggestion_id INTEGER,
     153    suggestion_type VARCHAR(50) NOT NULL,
     154    CONSTRAINT suggestion_type_pk PRIMARY KEY(suggestion_id, suggestion_type),
     155    FOREIGN KEY (suggestion_id) REFERENCES AI_SUGGESTION(suggestion_id)
    158156        ON DELETE CASCADE
    159157        ON UPDATE CASCADE
    … …  
    161159
    162160CREATE 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)
     161    user_id INTEGER,
     162    story_id INTEGER,
     163    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
     164    CONSTRAINT like_pk PRIMARY KEY(user_id, story_id),
     165    FOREIGN KEY (user_id) REFERENCES USERS(user_id)
     166        ON DELETE CASCADE
     167        ON UPDATE CASCADE,
     168    FOREIGN KEY (story_id) REFERENCES STORY(story_id)
    171169        ON DELETE CASCADE
    172170        ON UPDATE CASCADE
    … …  
    174172
    175173CREATE 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 
     174    comment_id SERIAL PRIMARY KEY,
     175    content TEXT NOT NULL,
     176    user_id INTEGER NOT NULL,
     177    story_id INTEGER NOT NULL,
     178    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
     179    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
     180    FOREIGN KEY (user_id) REFERENCES USERS(user_id)
     181        ON DELETE CASCADE
     182        ON UPDATE CASCADE,
     183    FOREIGN KEY (story_id) REFERENCES STORY(story_id)
     184        ON DELETE CASCADE
     185        ON UPDATE CASCADE
     186);
    190187CREATE 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)
     188    user_id INTEGER,
     189    story_id INTEGER,
     190    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
     191    CONSTRAINT collaboration_pk PRIMARY KEY(user_id, story_id),
     192    FOREIGN KEY (user_id) REFERENCES USERS(user_id)
     193        ON DELETE CASCADE
     194        ON UPDATE CASCADE,
     195    FOREIGN KEY (story_id) REFERENCES STORY(story_id)
    199196        ON DELETE CASCADE
    200197        ON UPDATE CASCADE
    … …  
    202199
    203200CREATE 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)
     201    user_id INTEGER,
     202    story_id INTEGER,
     203    roles VARCHAR(50) NOT NULL,
     204    CONSTRAINT role_pk PRIMARY KEY(user_id, story_id, roles),
     205    FOREIGN KEY (user_id, story_id) REFERENCES COLLABORATION(user_id, story_id)
    209206        ON DELETE CASCADE
    210207        ON UPDATE CASCADE
    … …  
    212209
    213210CREATE 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)
     211    user_id INTEGER,
     212    story_id INTEGER,
     213    permission_level INTEGER NOT NULL,
     214    CONSTRAINT permission_level_pk PRIMARY KEY(user_id, story_id, permission_level),
     215    FOREIGN KEY (user_id, story_id) REFERENCES COLLABORATION(user_id, story_id)
    219216        ON DELETE CASCADE
    220217        ON UPDATE CASCADE
    … …  
    222219
    223220CREATE 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)
     221    story_id INTEGER,
     222    genre_id INTEGER,
     223    CONSTRAINT has_genre_pk PRIMARY KEY(story_id, genre_id),
     224    FOREIGN KEY (story_id) REFERENCES STORY(story_id)
     225        ON DELETE CASCADE
     226        ON UPDATE CASCADE,
     227    FOREIGN KEY (genre_id) REFERENCES GENRE(genre_id)
    231228        ON DELETE CASCADE
    232229        ON UPDATE CASCADE
    … …  
    234231
    235232CREATE 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 );
     233    list_id INTEGER,
     234    story_id INTEGER,
     235    added_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
     236    CONSTRAINT reading_list_items_pk PRIMARY KEY(list_id, story_id),
     237    FOREIGN KEY (list_id) REFERENCES READING_LIST(list_id)
     238        ON DELETE CASCADE
     239        ON UPDATE CASCADE,
     240    FOREIGN KEY (story_id) REFERENCES STORY(story_id)
     241        ON DELETE CASCADE
     242        ON UPDATE CASCADE
     243);
     244
     245CREATE TABLE NOTIFY(
     246    user_id INTEGER,
     247    story_id INTEGER,
     248    notification_id INTEGER,
     249    CONSTRAINT notify_pk PRIMARY KEY(user_id, story_id, notification_id),
     250    FOREIGN KEY (user_id) REFERENCES USERS(user_id)
     251        ON DELETE CASCADE
     252        ON UPDATE CASCADE,
     253    FOREIGN KEY (story_id) REFERENCES STORY(story_id)
     254        ON DELETE CASCADE
     255        ON UPDATE CASCADE,
     256    FOREIGN KEY (notification_id) REFERENCES NOTIFICATION(notification_id)
     257        ON DELETE CASCADE
     258        ON UPDATE CASCADE
     259);
     260
     261CREATE TABLE NEED_APPROVAL(
     262    suggestion_id INTEGER,
     263    story_id INTEGER,
     264    chapter_id INTEGER,
     265    CONSTRAINT need_approval_pk PRIMARY KEY(suggestion_id, story_id, chapter_id),
     266    FOREIGN KEY (suggestion_id) REFERENCES AI_SUGGESTION(suggestion_id)
     267        ON DELETE CASCADE
     268        ON UPDATE CASCADE,
     269    FOREIGN KEY (story_id) REFERENCES STORY(story_id)
     270        ON DELETE CASCADE
     271        ON UPDATE CASCADE,
     272    FOREIGN KEY (chapter_id) REFERENCES CHAPTER(chapter_id)
     273        ON DELETE CASCADE
     274        ON UPDATE CASCADE
     275);