DataMiddleware/SourceCode/DataMiddleware/app/init.sql

-- 创建数据库
CREATE DATABASE IF NOT EXISTS iwb_data_middleware_v1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
USE iwb_data_middleware_v1;

-- 创建 sub_project 表
CREATE TABLE IF NOT EXISTS project (
    id INT AUTO_INCREMENT PRIMARY KEY,
    project_name VARCHAR(255),
    description VARCHAR(1000),
    is_del TINYINT DEFAULT 0,
    delete_by VARCHAR(255),
    delete_time DATETIME,
    update_by VARCHAR(255),
    update_time DATETIME,
    create_by VARCHAR(255),
    create_time DATETIME
) CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

-- 创建 sub_project 表
CREATE TABLE IF NOT EXISTS sub_project (
    id INT AUTO_INCREMENT PRIMARY KEY,
    project_id int,
    INDEX idx_project_id (project_id),
    sub_project_name VARCHAR(255),
    work_catalog VARCHAR(255),
    work_content VARCHAR(255),
    standard_version VARCHAR(50),
    file_paths TEXT,
    status TINYINT DEFAULT 0,
    is_del TINYINT DEFAULT 0,
    delete_by VARCHAR(255),
    delete_time DATETIME,
    update_by VARCHAR(255),
    update_time DATETIME,
    create_by VARCHAR(255),
    create_time DATETIME
) CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

-- 创建 project_item 表
CREATE TABLE IF NOT EXISTS sub_project_item (
    id INT AUTO_INCREMENT PRIMARY KEY,
    project_id int NOT NULL,
    sub_project_id int NOT NULL,
    device_name VARCHAR(255) NOT NULL,
    device_model VARCHAR(255) ,
    device_unit VARCHAR(50) ,
    device_count FLOAT DEFAULT 0,
    standard_version VARCHAR(50),
    standard_no VARCHAR(255) ,
    process_status TINYINT DEFAULT 0,
    process_time DATETIME,
    send_status TINYINT DEFAULT 0,
    send_time DATETIME,
    remark TEXT,
    update_time DATETIME 
) CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;