Changes between Version 5 and Version 6 of ddlScript.sql


Ignore:
Timestamp:
08/24/26 15:31:19 (13 days ago)
Author:
211099
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • ddlScript.sql

    v5 v6  
    44-- Delete tables if they exist
    55DROP TABLE IF EXISTS NEED_APPROVAL CASCADE;
    6 DROP TABLE IF EXISTS NOTIFY CASCADE;
    7 DROP TABLE IF EXISTS ROLES CASCADE;
    8 DROP TABLE IF EXISTS PERMISSION_LEVEL CASCADE;
    9 DROP TABLE IF EXISTS SUGGESTION_TYPE CASCADE;
    10 DROP TABLE IF EXISTS CONTENT_TYPE CASCADE;
    116DROP TABLE IF EXISTS READING_LIST_ITEMS CASCADE;
    127DROP TABLE IF EXISTS HAS_GENRE CASCADE;
    … …  
    1813DROP TABLE IF EXISTS READING_LIST CASCADE;
    1914DROP TABLE IF EXISTS CHAPTER CASCADE;
    20 DROP TABLE IF EXISTS STATUS CASCADE;
    2115DROP TABLE IF EXISTS STORY CASCADE;
    2216DROP TABLE IF EXISTS WRITER CASCADE;
    … …  
    3226    username VARCHAR(255) NOT NULL UNIQUE,
    3327    email VARCHAR(255) NOT NULL UNIQUE,
    34     user_name VARCHAR(100) NOT NULL,                       
     28    user_name VARCHAR(100) NOT NULL,
    3529    surname VARCHAR(100) NOT NULL,
    3630    password VARCHAR(255) NOT NULL,
    37     user_created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP, 
    38     user_updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP, 
     31    user_created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
     32    user_updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    3933    CONSTRAINT email_format CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$')
    4034);
    … …  
    4236CREATE TABLE ADMINS(
    4337    user_id INTEGER PRIMARY KEY,
     38    assigned_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    4439    FOREIGN KEY (user_id) REFERENCES USERS(user_id)
    4540        ON DELETE CASCADE
    … …  
    4944CREATE TABLE REGULAR_USER(
    5045    user_id INTEGER PRIMARY KEY,
     46    joined_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    5147    FOREIGN KEY (user_id) REFERENCES USERS(user_id)
    5248        ON DELETE CASCADE
    … …  
    5652CREATE TABLE WRITER(
    5753    user_id INTEGER PRIMARY KEY,
     54    bio TEXT,
    5855    FOREIGN KEY (user_id) REFERENCES USERS(user_id)
    5956        ON DELETE CASCADE
    … …  
    6764    short_description VARCHAR(500) NOT NULL,
    6865    image VARCHAR(2048),
    69     story_content TEXT NOT NULL,                             
    70     user_id INTEGER NOT NULL,
    71     story_created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    72     story_updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
     66    story_content TEXT NOT NULL,
     67    status VARCHAR(50) NOT NULL DEFAULT 'draft',
     68    user_id INTEGER NOT NULL,
     69    story_created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
     70    story_updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
     71    CONSTRAINT status_values CHECK (status IN ('draft', 'published', 'archived')),
    7372    FOREIGN KEY (user_id) REFERENCES WRITER(user_id)
    74         ON DELETE CASCADE
    75         ON UPDATE CASCADE
    76 );
    77 
    78 CREATE TABLE STATUS(
    79     story_id INTEGER,
    80     status VARCHAR(50) NOT NULL,
    81     CONSTRAINT status_pk PRIMARY KEY(story_id, status),
    82     CONSTRAINT status_values CHECK (status IN ('draft', 'published', 'archived')),
    83     FOREIGN KEY (story_id) REFERENCES STORY(story_id)
    8473        ON DELETE CASCADE
    8574        ON UPDATE CASCADE
    … …  
    9180    chapter_name VARCHAR(100) NOT NULL,
    9281    title VARCHAR(200) NOT NULL,
    93     chapter_content TEXT NOT NULL,                           
     82    chapter_content TEXT NOT NULL,
    9483    word_count INTEGER CHECK (word_count >= 0),
    9584    rating DECIMAL(3,2) CHECK (rating >= 0 AND rating <= 5),
    … …  
    9887    story_id INTEGER NOT NULL,
    9988    chapter_created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    100     chapter_updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP, 
     89    chapter_updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    10190    CONSTRAINT unique_chapter_number UNIQUE(story_id, chapter_number),
    10291    FOREIGN KEY (story_id) REFERENCES STORY(story_id)
    … …  
    10796CREATE TABLE GENRE(
    10897    genre_id SERIAL PRIMARY KEY,
    109     genre_name VARCHAR(100) NOT NULL UNIQUE                 
     98    genre_name VARCHAR(100) NOT NULL UNIQUE
    11099);
    111100
    112101CREATE TABLE READING_LIST(
    113102    list_id SERIAL PRIMARY KEY,
    114     list_name VARCHAR(100) NOT NULL,                         
    115     list_content TEXT,                                     
     103    list_name VARCHAR(100) NOT NULL,
     104    list_content TEXT,
    116105    is_public BOOLEAN NOT NULL,
    117106    user_id INTEGER NOT NULL,
    118     list_created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP, 
    119     list_updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP, 
     107    list_created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
     108    list_updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    120109    FOREIGN KEY (user_id) REFERENCES USERS(user_id)
    121110        ON DELETE CASCADE
    … …  
    125114CREATE TABLE NOTIFICATION(
    126115    notification_id SERIAL PRIMARY KEY,
    127     notification_content TEXT NOT NULL,                     
     116    notification_content TEXT NOT NULL,
     117    content_type VARCHAR(50) NOT NULL,
    128118    is_read BOOLEAN DEFAULT FALSE,
    129     recipient_user_id INTEGER,
    130     type VARCHAR(50),
    131119    link VARCHAR(500),
    132     notification_created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    133     FOREIGN KEY (recipient_user_id) REFERENCES USERS(user_id)
    134         ON DELETE SET NULL
    135         ON UPDATE CASCADE
    136 );
    137 
    138 CREATE TABLE CONTENT_TYPE(
    139     notification_id INTEGER,
    140     content_type VARCHAR(50) NOT NULL,
    141     CONSTRAINT content_type_pk PRIMARY KEY(notification_id, content_type),
    142     FOREIGN KEY (notification_id) REFERENCES NOTIFICATION(notification_id)
     120    user_id INTEGER NOT NULL,
     121    story_id INTEGER NOT NULL,
     122    notification_created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
     123    FOREIGN KEY (user_id) REFERENCES USERS(user_id)
     124        ON DELETE CASCADE
     125        ON UPDATE CASCADE,
     126    FOREIGN KEY (story_id) REFERENCES STORY(story_id)
    143127        ON DELETE CASCADE
    144128        ON UPDATE CASCADE
    … …  
    149133    original_text TEXT NOT NULL,
    150134    suggested_text TEXT NOT NULL,
     135    suggestion_type VARCHAR(50) NOT NULL,
    151136    accepted BOOLEAN NOT NULL DEFAULT FALSE,
    152     suggestion_created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP, 
     137    suggestion_created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    153138    applied_at TIMESTAMPTZ,
    154139    story_id INTEGER NOT NULL,
    … …  
    158143);
    159144
    160 CREATE TABLE SUGGESTION_TYPE(
    161     suggestion_id INTEGER,
    162     suggestion_type VARCHAR(50) NOT NULL,
    163     CONSTRAINT suggestion_type_pk PRIMARY KEY(suggestion_id, suggestion_type),
    164     FOREIGN KEY (suggestion_id) REFERENCES AI_SUGGESTION(suggestion_id)
    165         ON DELETE CASCADE
    166         ON UPDATE CASCADE
    167 );
    168 
    169145CREATE TABLE LIKES(
    170146    user_id INTEGER,
    171147    story_id INTEGER,
    172     like_created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
     148    liked_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    173149    CONSTRAINT like_pk PRIMARY KEY(user_id, story_id),
    174150    FOREIGN KEY (user_id) REFERENCES USERS(user_id)
    … …  
    182158CREATE TABLE COMMENT(
    183159    comment_id SERIAL PRIMARY KEY,
    184     comment_content TEXT NOT NULL,                         
     160    comment_content TEXT NOT NULL,
    185161    user_id INTEGER NOT NULL,
    186162    story_id INTEGER NOT NULL,
    187163    comment_created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    188     comment_updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP, 
     164    comment_updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    189165    FOREIGN KEY (user_id) REFERENCES USERS(user_id)
    190166        ON DELETE CASCADE
    … …  
    198174    user_id INTEGER,
    199175    story_id INTEGER,
    200     collab_created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
     176    role VARCHAR(50) NOT NULL,
     177    permission_level INTEGER,
     178    collab_created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    201179    CONSTRAINT collaboration_pk PRIMARY KEY(user_id, story_id),
    202180    FOREIGN KEY (user_id) REFERENCES USERS(user_id)
    … …  
    204182        ON UPDATE CASCADE,
    205183    FOREIGN KEY (story_id) REFERENCES STORY(story_id)
    206         ON DELETE CASCADE
    207         ON UPDATE CASCADE
    208 );
    209 
    210 CREATE TABLE ROLES(
    211     user_id INTEGER,
    212     story_id INTEGER,
    213     roles VARCHAR(50) NOT NULL,
    214     CONSTRAINT role_pk PRIMARY KEY(user_id, story_id, roles),
    215     FOREIGN KEY (user_id, story_id) REFERENCES COLLABORATION(user_id, story_id)
    216         ON DELETE CASCADE
    217         ON UPDATE CASCADE
    218 );
    219 
    220 CREATE TABLE PERMISSION_LEVEL(
    221     user_id INTEGER,
    222     story_id INTEGER,
    223     permission_level INTEGER NOT NULL,
    224     CONSTRAINT permission_level_pk PRIMARY KEY(user_id, story_id, permission_level),
    225     FOREIGN KEY (user_id, story_id) REFERENCES COLLABORATION(user_id, story_id)
    226184        ON DELETE CASCADE
    227185        ON UPDATE CASCADE
    … …  
    253211);
    254212
    255 CREATE TABLE NOTIFY(
    256     user_id INTEGER,
    257     story_id INTEGER,
    258     notification_id INTEGER,
    259     CONSTRAINT notify_pk PRIMARY KEY(user_id, story_id, notification_id),
    260     FOREIGN KEY (user_id) REFERENCES USERS(user_id)
    261         ON DELETE CASCADE
    262         ON UPDATE CASCADE,
    263     FOREIGN KEY (story_id) REFERENCES STORY(story_id)
    264         ON DELETE CASCADE
    265         ON UPDATE CASCADE,
    266     FOREIGN KEY (notification_id) REFERENCES NOTIFICATION(notification_id)
    267         ON DELETE CASCADE
    268         ON UPDATE CASCADE
    269 );
    270 
    271213CREATE TABLE NEED_APPROVAL(
    272214    suggestion_id INTEGER,
    … …  
    283225        ON DELETE CASCADE
    284226        ON UPDATE CASCADE
    285 ); 
     227);
    286228}}}