src/test/resources/schema.sql

CREATE TABLE IF NOT EXISTS help_posts (
                                          id INT AUTO_INCREMENT PRIMARY KEY,
                                          author_id INT NOT NULL,
                                          title VARCHAR(255) NOT NULL,
                                          content TEXT NOT NULL,
                                          like_count INT NOT NULL DEFAULT 0,
                                          reply_count INT NOT NULL DEFAULT 0,
                                          create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP()
);


CREATE TABLE IF NOT EXISTS help_comments (
                               id INT AUTO_INCREMENT PRIMARY KEY,
                               post_id INT NOT NULL,
                               author_id INT NOT NULL,
                               content TEXT NOT NULL,
                               like_count INT NOT NULL DEFAULT 0,
                               create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP(),
                               CONSTRAINT fk_help_comments_post FOREIGN KEY (post_id) REFERENCES help_posts(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS seed_posts (
                            id INT AUTO_INCREMENT PRIMARY KEY,
                            author_id INT NOT NULL,
                            title VARCHAR(255) NOT NULL,
                            content TEXT NOT NULL,
                            like_count INT NOT NULL DEFAULT 0,
                            reply_count INT NOT NULL DEFAULT 0,
                            create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP()
);

CREATE TABLE IF NOT EXISTS seed_comments (
                               id INT AUTO_INCREMENT PRIMARY KEY,
                               post_id INT NOT NULL,
                               author_id INT NOT NULL,
                               content TEXT NOT NULL,
                               like_count INT NOT NULL DEFAULT 0,
                               create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP(),
                               CONSTRAINT fk_seed_comments_post FOREIGN KEY (post_id) REFERENCES seed_posts(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS "user" (
                                      username     VARCHAR(20)  NOT NULL PRIMARY KEY,
    password     VARCHAR(200) NOT NULL,
    authority    VARCHAR(10)  NOT NULL,
    level        INT          NOT NULL DEFAULT 0,
    registTime   DATE         NOT NULL,
    lastLogin    DATE         NULL,
    upload       BIGINT       NOT NULL DEFAULT 0,
    download     BIGINT       NOT NULL DEFAULT 0,
    shareRate    DOUBLE       NULL,
    magicPoints  BIGINT       NOT NULL DEFAULT 0
    );