Changes between Version 3 and Version 4 of DdlScript


Ignore:
Timestamp:
06/10/26 16:10:33 (2 months ago)
Author:
231141
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • DdlScript

    v3 v4  
    1010    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    1111    updated_at TIMESTAMP,
    12     deleted_at TIMESTAMP,
    13 
    14     CONSTRAINT chk_user_updated_at CHECK (updated_at IS NULL OR updated_at >= created_at),
    15     CONSTRAINT chk_user_deleted_at CHECK (deleted_at IS NULL OR deleted_at >= created_at),
    16     CONSTRAINT chk_email CHECK (email ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'),
    17     CONSTRAINT chk_phone CHECK (phone_number ~ '^\+?[0-9]{7,15}$'),
    18     CONSTRAINT chk_user_created_at CHECK (created_at <= CURRENT_TIMESTAMP)
    19 );
     12
     13    CONSTRAINT chk_user_updated_at
     14        CHECK (updated_at IS NULL OR updated_at >= created_at),
     15
     16    CONSTRAINT chk_email
     17        CHECK (email ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'),
     18
     19    CONSTRAINT chk_phone
     20        CHECK (phone_number ~ '^\+?[0-9]{7,15}$'),
     21
     22    CONSTRAINT chk_user_created_at
     23        CHECK (created_at <= CURRENT_TIMESTAMP)
     24);
     25
    2026
    2127CREATE TABLE Location (
    … …  
    2430    latitude DECIMAL(9,6) NOT NULL,
    2531    longitude DECIMAL(9,6) NOT NULL,
    26     CONSTRAINT uq_location_cords UNIQUE (latitude, longitude),
    27     CONSTRAINT chk_latitude CHECK (latitude BETWEEN -90 AND 90),
    28     CONSTRAINT chk_longitude CHECK (longitude BETWEEN -180 AND 180)
    29 );
     32
     33    CONSTRAINT uq_location_cords
     34        UNIQUE (latitude, longitude),
     35
     36    CONSTRAINT chk_latitude
     37        CHECK (latitude BETWEEN -90 AND 90),
     38
     39    CONSTRAINT chk_longitude
     40        CHECK (longitude BETWEEN -180 AND 180)
     41);
     42
    3043
    3144CREATE TABLE Category (
    … …  
    3447    description VARCHAR(500),
    3548    parent_category_id INT,
     49
    3650    CONSTRAINT fk_category_parent
    3751        FOREIGN KEY (parent_category_id)
    3852        REFERENCES Category(id)
    39         ON DELETE SET NULL
    40 );
     53        ON DELETE RESTRICT
     54);
     55
    4156
    4257CREATE TABLE NotificationType (
    … …  
    4560    description VARCHAR(500)
    4661);
    47 
    4862CREATE TABLE Badge (
    4963    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    … …  
    5569    tier_level INT,
    5670
    57     CONSTRAINT chk_min_price CHECK (min_price >= 0),
    58     CONSTRAINT chk_max_price CHECK (max_price >= 0),
    59     CONSTRAINT chk_price_range CHECK (min_price <= max_price),
    60     CONSTRAINT chk_tier_range CHECK (1<=tier_level and 5>=tier_level),
     71    CONSTRAINT chk_min_price
     72        CHECK (min_price >= 0),
     73
     74    CONSTRAINT chk_max_price
     75        CHECK (max_price >= 0),
     76
     77    CONSTRAINT chk_price_range
     78        CHECK (min_price <= max_price),
     79
     80    CONSTRAINT chk_tier_range
     81        CHECK (tier_level BETWEEN 1 AND 5),
    6182
    6283    CONSTRAINT fk_badge_category
    6384        FOREIGN KEY (category_id)
    6485        REFERENCES Category(id)
    65         ON DELETE RESTRICT
    66 );
     86        ON DELETE CASCADE
     87);
     88
    6789
    6890CREATE TABLE Worker (
    … …  
    7597    updated_at TIMESTAMP,
    7698
    77     CONSTRAINT chk_worker_created_at CHECK (created_at<=CURRENT_TIMESTAMP),
    78     CONSTRAINT chk_worker_updated_at CHECK (updated_at IS NULL OR updated_at >= created_at),
    79     CONSTRAINT chk_radius CHECK (service_radius_km > 0),
    80     CONSTRAINT chk_worker_work_mode CHECK (work_mode IN ('HYBRID','ONSITE','REMOTE')),
     99    CONSTRAINT chk_worker_created_at
     100        CHECK (created_at <= CURRENT_TIMESTAMP),
     101
     102    CONSTRAINT chk_worker_updated_at
     103        CHECK (updated_at IS NULL OR updated_at >= created_at),
     104
     105    CONSTRAINT chk_radius
     106        CHECK (service_radius_km > 0),
     107
     108    CONSTRAINT chk_worker_work_mode
     109        CHECK (work_mode IN ('HYBRID', 'ONSITE', 'REMOTE')),
     110
    81111    CONSTRAINT fk_worker_user
    82112        FOREIGN KEY (user_id)
    83113        REFERENCES UserAccount(id)
    84114        ON DELETE CASCADE,
     115
    85116    CONSTRAINT fk_worker_location
    86117        FOREIGN KEY (location_id)
    … …  
    89120);
    90121
     122
    91123CREATE TABLE Client (
    92124    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    93125    user_id INT NOT NULL,
     126
    94127    CONSTRAINT fk_client_user
    95128        FOREIGN KEY (user_id)
    … …  
    98131);
    99132
     133
    100134CREATE TABLE Favourite (
    101135    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    102136    worker_id INT NOT NULL,
    103137    client_id INT NOT NULL,
     138
    104139    CONSTRAINT fk_favourite_worker
    105140        FOREIGN KEY (worker_id)
    106141        REFERENCES Worker(id)
    107142        ON DELETE CASCADE,
     143
    108144    CONSTRAINT fk_favourite_client
    109145        FOREIGN KEY (client_id)
    110146        REFERENCES Client(id)
    111147        ON DELETE CASCADE,
    112     CONSTRAINT unq_worker_client UNIQUE(worker_id,client_id)
    113 );
    114 
     148
     149    CONSTRAINT unq_worker_client
     150        UNIQUE (worker_id, client_id)
     151);
    115152CREATE TABLE WorkerCategory (
    116153    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    117154    worker_id INT NOT NULL,
    118155    category_id INT NOT NULL,
    119     CONSTRAINT unq_worker_category UNIQUE(worker_id, category_id),
     156
     157    CONSTRAINT unq_worker_category
     158        UNIQUE (worker_id, category_id),
     159
    120160    CONSTRAINT fk_worker_category_worker
    121161        FOREIGN KEY (worker_id)
    122162        REFERENCES Worker(id)
    123         ON DELETE CASCADE ,
     163        ON DELETE CASCADE,
     164
    124165    CONSTRAINT fk_worker_category_category
    125166        FOREIGN KEY (category_id)
    … …  
    128169);
    129170
     171
    130172CREATE TABLE WorkerBadge (
    131173    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    … …  
    134176    date_acquired DATE,
    135177    is_active BOOLEAN,
    136     CONSTRAINT unq_worker_badge UNIQUE(badge_id, worker_id),
     178
     179    CONSTRAINT unq_worker_badge
     180        UNIQUE (badge_id, worker_id),
     181
    137182    CONSTRAINT fk_worker_badge_badge
    138183        FOREIGN KEY (badge_id)
    139184        REFERENCES Badge(id)
    140185        ON DELETE CASCADE,
     186
    141187    CONSTRAINT fk_worker_badge_worker
    142188        FOREIGN KEY (worker_id)
    … …  
    145191);
    146192
     193
    147194CREATE TABLE TaskRequest (
    148195    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    … …  
    150197    description VARCHAR(1000) NOT NULL,
    151198    work_mode VARCHAR(20) NOT NULL,
    152     status VARCHAR(20) CHECK (status in('OPEN','CLOSED')),
     199    status VARCHAR(20)
     200        CHECK (status IN ('OPEN', 'CLOSED')),
    153201    category_id INT NOT NULL,
    154202    location_id INT NOT NULL,
    155203    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    156204    updated_at TIMESTAMP,
    157     deleted_at TIMESTAMP,
    158     CONSTRAINT chk_task_req_work_mode CHECK (work_mode IN ('HYBRID','ONSITE','REMOTE')),
    159     CONSTRAINT chk_task_req_created_at CHECK (created_at <= CURRENT_TIMESTAMP),
    160     CONSTRAINT chk_task_req_updated_at CHECK
    161         (updated_at IS NULL OR (updated_at >= created_at AND updated_at <= CURRENT_TIMESTAMP)),
    162     CONSTRAINT chk_task_req_deleted_at CHECK
    163         (deleted_at IS NULL OR (deleted_at >= created_at AND deleted_at <= CURRENT_TIMESTAMP)),
     205
     206    CONSTRAINT chk_task_req_work_mode
     207        CHECK (work_mode IN ('HYBRID', 'ONSITE', 'REMOTE')),
     208
     209    CONSTRAINT chk_task_req_created_at
     210        CHECK (created_at <= CURRENT_TIMESTAMP),
     211
     212    CONSTRAINT chk_task_req_updated_at
     213        CHECK (
     214            updated_at IS NULL OR
     215            (updated_at >= created_at
     216             AND updated_at <= CURRENT_TIMESTAMP)
     217        ),
     218
    164219    CONSTRAINT fk_task_request_client
    165220        FOREIGN KEY (client_id)
    166221        REFERENCES Client(id)
    167222        ON DELETE CASCADE,
     223
    168224    CONSTRAINT fk_task_request_category
    169225        FOREIGN KEY (category_id)
    170226        REFERENCES Category(id)
    171227        ON DELETE RESTRICT,
     228
    172229    CONSTRAINT fk_task_request_location
    173230        FOREIGN KEY (location_id)
    … …  
    176233);
    177234
     235
    178236CREATE TABLE Offer (
    179237    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    … …  
    182240    price INT NOT NULL,
    183241    offer_status VARCHAR(20) NOT NULL,
    184     initiated_by VARCHAR(10) NOT NULL CHECK (initiated_by IN ('CLIENT','WORKER')),
    185     created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    186     updated_at TIMESTAMP,
    187     deleted_at TIMESTAMP,
    188     CONSTRAINT chk_price CHECK (price > 0),
    189     CONSTRAINT chk_offer_status CHECK (offer_status IN ('PENDING', 'ACCEPTED', 'REJECTED')),
    190     CONSTRAINT chk_offer_created_at CHECK (created_at <= CURRENT_TIMESTAMP),
    191     CONSTRAINT chk_task_off_updated_at CHECK
    192         (updated_at IS NULL OR (updated_at >= created_at AND updated_at <= CURRENT_TIMESTAMP)),
    193     CONSTRAINT chk_task_off_deleted_at CHECK
    194         (deleted_at IS NULL OR (deleted_at >= created_at AND deleted_at <= CURRENT_TIMESTAMP)),
     242    initiated_by VARCHAR(10) NOT NULL
     243        CHECK (initiated_by IN ('CLIENT', 'WORKER')),
     244    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
     245    updated_at TIMESTAMP,
     246
     247    CONSTRAINT chk_price
     248        CHECK (price > 0),
     249
     250    CONSTRAINT chk_offer_status
     251        CHECK (offer_status IN ('PENDING', 'ACCEPTED', 'REJECTED')),
     252
     253    CONSTRAINT chk_offer_created_at
     254        CHECK (created_at <= CURRENT_TIMESTAMP),
     255
     256    CONSTRAINT chk_task_off_updated_at
     257        CHECK (
     258            updated_at IS NULL OR
     259            (updated_at >= created_at
     260             AND updated_at <= CURRENT_TIMESTAMP)
     261        ),
     262
    195263    CONSTRAINT fk_offer_worker
    196264        FOREIGN KEY (worker_id)
    197265        REFERENCES Worker(id)
    198266        ON DELETE CASCADE,
     267
    199268    CONSTRAINT fk_offer_task_request
    200269        FOREIGN KEY (task_request_id)
    201270        REFERENCES TaskRequest(id)
    202         ON DELETE RESTRICT
    203 );
    204 
     271        ON DELETE CASCADE
     272);
    205273CREATE TABLE Task (
    206274    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    … …  
    209277    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    210278    updated_at TIMESTAMP,
    211     completed_at TIMESTAMP,
    212     CONSTRAINT chk_task_status CHECK (status IN ('ACTIVE', 'COMPLETED', 'CANCELLED')),
    213     CONSTRAINT chk_created_at CHECK (created_at <= CURRENT_TIMESTAMP),
    214     CONSTRAINT chk_completed_at CHECK (completed_at <= CURRENT_TIMESTAMP),
    215     CONSTRAINT chk_timeline CHECK (updated_at IS NULL OR updated_at >= created_at),
     279
     280    CONSTRAINT chk_task_status
     281        CHECK (status IN ('ACTIVE', 'COMPLETED', 'CANCELLED')),
     282
     283    CONSTRAINT chk_created_at
     284        CHECK (created_at <= CURRENT_TIMESTAMP),
     285
     286    CONSTRAINT chk_timeline
     287        CHECK (updated_at IS NULL OR updated_at >= created_at),
     288
    216289    CONSTRAINT fk_task_offer
    217290        FOREIGN KEY (offer_id)
    … …  
    220293);
    221294
     295
    222296CREATE TABLE Message (
    223297    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    … …  
    225299    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    226300    task_id INT NOT NULL,
    227     sender_id INT NOT NULL,
    228 
    229     CONSTRAINT chk_sent_at CHECK (created_at <= CURRENT_TIMESTAMP),
     301    sender_id INT,
     302
     303    CONSTRAINT chk_sent_at
     304        CHECK (created_at <= CURRENT_TIMESTAMP),
    230305
    231306    CONSTRAINT fk_message_task
    232307        FOREIGN KEY (task_id)
    233308        REFERENCES Task(id)
    234         ON DELETE RESTRICT,
     309        ON DELETE CASCADE,
     310
    235311    CONSTRAINT fk_message_sender
    236312        FOREIGN KEY (sender_id)
    237313        REFERENCES UserAccount(id)
    238         ON DELETE RESTRICT
    239 );
     314        ON DELETE SET NULL
     315);
     316
    240317
    241318CREATE TABLE Review (
    … …  
    245322    task_id INT NOT NULL,
    246323    reviewed_id INT NOT NULL,
    247     reviewer_id INT NOT NULL,
    248     created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    249     CONSTRAINT chk_rating CHECK (rating BETWEEN 1 AND 5),
    250     CONSTRAINT chk_created CHECK (created_at <= CURRENT_TIMESTAMP),
     324    reviewer_id INT,
     325    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
     326
     327    CONSTRAINT chk_rating
     328        CHECK (rating BETWEEN 1 AND 5),
     329
     330    CONSTRAINT chk_created
     331        CHECK (created_at <= CURRENT_TIMESTAMP),
     332
    251333    CONSTRAINT fk_review_task
    252334        FOREIGN KEY (task_id)
    253335        REFERENCES Task(id)
    254         ON DELETE RESTRICT,
     336        ON DELETE CASCADE,
     337
    255338    CONSTRAINT fk_review_reviewed
    256339        FOREIGN KEY (reviewed_id)
    257340        REFERENCES UserAccount(id)
    258         ON DELETE RESTRICT,
     341        ON DELETE CASCADE,
     342
    259343    CONSTRAINT fk_review_reviewer
    260344        FOREIGN KEY (reviewer_id)
    261345        REFERENCES UserAccount(id)
    262         ON DELETE RESTRICT,
    263     CONSTRAINT unq UNIQUE(task_id, reviewer_id)
    264 );
     346        ON DELETE SET NULL,
     347
     348    CONSTRAINT unq
     349        UNIQUE (task_id, reviewer_id)
     350);
     351
    265352
    266353CREATE TABLE Complaint (
    … …  
    274361    client_id INT NOT NULL,
    275362    worker_id INT NOT NULL,
    276     CONSTRAINT chk_complaint_status CHECK (status IN ('OPEN', 'RESOLVED', 'DISMISSED')),
    277     CONSTRAINT chk_complaint_created_at CHECK (created_at <= CURRENT_TIMESTAMP),
    278     CONSTRAINT chk_timeline CHECK (updated_at IS NULL OR updated_at >= created_at),
     363
     364    CONSTRAINT chk_complaint_status
     365        CHECK (status IN ('OPEN', 'RESOLVED', 'DISMISSED')),
     366
     367    CONSTRAINT chk_complaint_created_at
     368        CHECK (created_at <= CURRENT_TIMESTAMP),
     369
     370    CONSTRAINT chk_timeline
     371        CHECK (updated_at IS NULL OR updated_at >= created_at),
     372
    279373    CONSTRAINT fk_complaint_task
    280374        FOREIGN KEY (task_id)
    281375        REFERENCES Task(id)
    282         ON DELETE RESTRICT,
     376        ON DELETE CASCADE,
     377
    283378    CONSTRAINT fk_complaint_client
    284379        FOREIGN KEY (client_id)
    285380        REFERENCES Client(id)
    286         ON DELETE RESTRICT,
     381        ON DELETE CASCADE,
     382
    287383    CONSTRAINT fk_complaint_worker
    288384        FOREIGN KEY (worker_id)
    289385        REFERENCES Worker(id)
    290         ON DELETE RESTRICT
    291 );
    292 
     386        ON DELETE CASCADE
     387);
    293388CREATE TABLE ComplaintAttachment (
    294389    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    … …  
    297392    complaint_id INT NOT NULL,
    298393    user_id INT,
     394
    299395    CONSTRAINT fk_complaint_attachment_complaint
    300396        FOREIGN KEY (complaint_id)
    301397        REFERENCES Complaint(id)
    302398        ON DELETE CASCADE,
     399
    303400    CONSTRAINT fk_complaint_attachment_user
    304401        FOREIGN KEY (user_id)
    … …  
    306403        ON DELETE SET NULL
    307404);
     405
    308406
    309407CREATE TABLE Payment (
    … …  
    312410    payment_method VARCHAR(50) NOT NULL,
    313411    status VARCHAR(20) NOT NULL,
    314     task_id INT NOT NULL,
    315     client_id INT NOT NULL,
    316     worker_id INT NOT NULL,
    317     created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    318     updated_at TIMESTAMP,
    319 
    320     CONSTRAINT chk_amount CHECK (amount > 0),
    321     CONSTRAINT chk_status CHECK (status IN ('PENDING', 'PAID', 'FAILED')),
    322     CONSTRAINT chk_payment_method CHECK (payment_method IN ('CARD','CASH','PAYPAL')),
    323     CONSTRAINT chk_p_created_at CHECK (created_at <= CURRENT_TIMESTAMP),
    324     CONSTRAINT chk_task_p_updated_at CHECK
    325         (updated_at IS NULL OR (updated_at >= created_at AND updated_at <= CURRENT_TIMESTAMP)),
     412    task_id INT,
     413    client_id INT,
     414    worker_id INT,
     415    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
     416    updated_at TIMESTAMP,
     417
     418    CONSTRAINT chk_amount
     419        CHECK (amount > 0),
     420
     421    CONSTRAINT chk_status
     422        CHECK (status IN ('PENDING', 'PAID', 'FAILED')),
     423
     424    CONSTRAINT chk_payment_method
     425        CHECK (payment_method IN ('CARD','CASH','PAYPAL')),
     426
     427    CONSTRAINT chk_p_created_at
     428        CHECK (created_at <= CURRENT_TIMESTAMP),
     429
     430    CONSTRAINT chk_task_p_updated_at
     431        CHECK (
     432            updated_at IS NULL OR
     433            (updated_at >= created_at
     434             AND updated_at <= CURRENT_TIMESTAMP)
     435        ),
     436
    326437    CONSTRAINT fk_payment_task
    327438        FOREIGN KEY (task_id)
    328439        REFERENCES Task(id)
    329         ON DELETE RESTRICT,
     440        ON DELETE SET NULL,
     441
    330442    CONSTRAINT fk_payment_client
    331443        FOREIGN KEY (client_id)
    332444        REFERENCES Client(id)
    333         ON DELETE RESTRICT,
     445        ON DELETE SET NULL,
     446
    334447    CONSTRAINT fk_payment_worker
    335448        FOREIGN KEY (worker_id)
    336449        REFERENCES Worker(id)
    337         ON DELETE RESTRICT
     450        ON DELETE SET NULL
    338451);
    339452
    … …  
    345458    user_id INT NOT NULL,
    346459    notification_type_id INT NOT NULL,
     460
    347461    CONSTRAINT fk_notification_preference_user
    348462        FOREIGN KEY (user_id)
    349463        REFERENCES UserAccount(id)
    350464        ON DELETE CASCADE,
     465
    351466    CONSTRAINT fk_notification_preference_type
    352467        FOREIGN KEY (notification_type_id)
    353468        REFERENCES NotificationType(id)
    354         ON DELETE RESTRICT,
    355     CONSTRAINT chk_channel CHECK (channel IN ('EMAIL','SMS','PUSH'))
    356 );
     469        ON DELETE CASCADE,
     470
     471    CONSTRAINT chk_channel
     472        CHECK (channel IN ('EMAIL','SMS','PUSH'))
     473);
     474
    357475
    358476CREATE TABLE Notification (
    … …  
    366484    offer_id INT,
    367485    payment_id INT,
    368     CONSTRAINT chk_notification_time CHECK (created_at <= CURRENT_TIMESTAMP),
     486
     487    CONSTRAINT chk_notification_time
     488        CHECK (created_at <= CURRENT_TIMESTAMP),
     489
    369490    CONSTRAINT fk_notification_user
    370491        FOREIGN KEY (user_id)
    371492        REFERENCES UserAccount(id)
    372493        ON DELETE CASCADE,
     494
    373495    CONSTRAINT fk_notification_type
    374496        FOREIGN KEY (notification_type_id)
    375497        REFERENCES NotificationType(id)
    376498        ON DELETE SET NULL,
     499
    377500    CONSTRAINT fk_notification_task
    378501        FOREIGN KEY (task_id)
    379502        REFERENCES Task(id)
    380503        ON DELETE SET NULL,
     504
    381505    CONSTRAINT fk_notification_offer
    382506        FOREIGN KEY (offer_id)
    383507        REFERENCES Offer(id)
    384508        ON DELETE SET NULL,
     509
    385510    CONSTRAINT fk_notification_payment
    386511        FOREIGN KEY (payment_id)
    … …  
    389514);
    390515
     516
    391517CREATE TABLE cities (
    392518    city VARCHAR(25),