315 lines
14 KiB
YAML
315 lines
14 KiB
YAML
apiVersion: v1
|
||
kind: Namespace
|
||
metadata:
|
||
name: erp-prod
|
||
---
|
||
# ============================================================
|
||
# 数据库初始化Job - 用于启动时执行数据库迁移
|
||
# ============================================================
|
||
apiVersion: batch/v1
|
||
kind: Job
|
||
metadata:
|
||
name: erp-db-init
|
||
namespace: erp-prod
|
||
labels:
|
||
app: erp-db-init
|
||
tier: database
|
||
annotations:
|
||
description: "ERP数据库初始化Job，在应用启动前执行"
|
||
spec:
|
||
ttlSecondsAfterFinished: 300
|
||
backoffLimit: 3
|
||
template:
|
||
metadata:
|
||
labels:
|
||
app: erp-db-init
|
||
tier: database
|
||
spec:
|
||
restartPolicy: OnFailure
|
||
initContainers:
|
||
# 等待MySQL就绪
|
||
- name: wait-for-mysql
|
||
image: busybox:1.36
|
||
command:
|
||
- sh
|
||
- -c
|
||
- |
|
||
echo "等待MySQL服务..."
|
||
until nc -z mysql 3306; do
|
||
echo "MySQL未就绪，等待中..."
|
||
sleep 5
|
||
done
|
||
echo "MySQL已就绪"
|
||
containers:
|
||
- name: erp-db-init
|
||
image: mysql:8.0
|
||
command:
|
||
- sh
|
||
- -c
|
||
- |
|
||
echo "开始初始化数据库..."
|
||
mysql -h mysql -P 3306 -uroot -p"${MYSQL_ROOT_PASSWORD}" <<'EOSQL'
|
||
CREATE DATABASE IF NOT EXISTS erp_java CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
||
CREATE DATABASE IF NOT EXISTS nacos_config CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
||
CREATE DATABASE IF NOT EXISTS seata CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
||
|
||
CREATE USER IF NOT EXISTS 'erp_user'@'%' IDENTIFIED BY 'erp123456';
|
||
CREATE USER IF NOT EXISTS 'erp_user'@'localhost' IDENTIFIED BY 'erp123456';
|
||
GRANT ALL PRIVILEGES ON erp_java.* TO 'erp_user'@'%';
|
||
GRANT ALL PRIVILEGES ON erp_java.* TO 'erp_user'@'localhost';
|
||
GRANT ALL PRIVILEGES ON nacos_config.* TO 'erp_user'@'%';
|
||
GRANT ALL PRIVILEGES ON seata.* TO 'erp_user'@'%';
|
||
FLUSH PRIVILEGES;
|
||
|
||
USE erp_java;
|
||
|
||
-- 创建版本跟踪表
|
||
CREATE TABLE IF NOT EXISTS schema_version (
|
||
version_rank INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
||
installed_rank INT NOT NULL,
|
||
version VARCHAR(50) NOT NULL,
|
||
description VARCHAR(200) NOT NULL,
|
||
type VARCHAR(20) NOT NULL DEFAULT 'SQL',
|
||
script VARCHAR(1000) NOT NULL,
|
||
checksum INT,
|
||
installed_by VARCHAR(100) NOT NULL DEFAULT 'system',
|
||
installed_on DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
execution_time INT NOT NULL,
|
||
success TINYINT NOT NULL DEFAULT 1,
|
||
UNIQUE KEY unique_ver_idx (version),
|
||
KEY installed_rank_idx (installed_rank),
|
||
KEY success_idx (success),
|
||
KEY version_rank_idx (version_rank)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
||
CREATE TABLE IF NOT EXISTS users (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
||
tenant_id BIGINT NOT NULL DEFAULT 0,
|
||
username VARCHAR(50) NOT NULL,
|
||
email VARCHAR(100) UNIQUE,
|
||
phone VARCHAR(20) UNIQUE,
|
||
password_hash VARCHAR(255) NOT NULL,
|
||
real_name VARCHAR(50),
|
||
avatar VARCHAR(255),
|
||
status TINYINT NOT NULL DEFAULT 1,
|
||
is_super_admin TINYINT NOT NULL DEFAULT 0,
|
||
last_login_at DATETIME,
|
||
last_login_ip VARCHAR(45),
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
deleted_at DATETIME,
|
||
INDEX idx_tenant_id (tenant_id),
|
||
INDEX idx_username (username)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
||
CREATE TABLE IF NOT EXISTS tenants (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
||
company_name VARCHAR(200) NOT NULL,
|
||
contact_name VARCHAR(50) NOT NULL,
|
||
contact_phone VARCHAR(20) NOT NULL,
|
||
contact_email VARCHAR(100) NOT NULL,
|
||
domain VARCHAR(100) UNIQUE,
|
||
status TINYINT NOT NULL DEFAULT 1,
|
||
max_users INT DEFAULT 10,
|
||
features JSON,
|
||
settings JSON,
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
INDEX idx_company_name (company_name)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
||
CREATE TABLE IF NOT EXISTS products (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
||
tenant_id BIGINT NOT NULL DEFAULT 0,
|
||
product_code VARCHAR(50) NOT NULL,
|
||
product_name VARCHAR(200) NOT NULL,
|
||
category_id BIGINT,
|
||
unit VARCHAR(20),
|
||
price DECIMAL(12,2),
|
||
cost DECIMAL(12,2),
|
||
stock INT DEFAULT 0,
|
||
min_stock INT DEFAULT 0,
|
||
status TINYINT NOT NULL DEFAULT 1,
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
deleted_at DATETIME,
|
||
UNIQUE KEY uk_code_tenant (product_code, tenant_id),
|
||
INDEX idx_category (category_id),
|
||
INDEX idx_tenant_id (tenant_id)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
||
CREATE TABLE IF NOT EXISTS categories (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
||
tenant_id BIGINT NOT NULL DEFAULT 0,
|
||
parent_id BIGINT DEFAULT 0,
|
||
category_name VARCHAR(100) NOT NULL,
|
||
sort_order INT DEFAULT 0,
|
||
status TINYINT NOT NULL DEFAULT 1,
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
INDEX idx_tenant_parent (tenant_id, parent_id)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
||
CREATE TABLE IF NOT EXISTS orders (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
||
order_no VARCHAR(50) NOT NULL UNIQUE,
|
||
tenant_id BIGINT NOT NULL DEFAULT 0,
|
||
customer_id BIGINT NOT NULL,
|
||
total_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
|
||
discount_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
|
||
payable_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
|
||
paid_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
|
||
status VARCHAR(20) NOT NULL DEFAULT 'PENDING',
|
||
order_date DATE NOT NULL,
|
||
delivery_date DATE,
|
||
remark TEXT,
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
deleted_at DATETIME,
|
||
INDEX idx_tenant_id (tenant_id),
|
||
INDEX idx_customer_id (customer_id),
|
||
INDEX idx_order_no (order_no),
|
||
INDEX idx_order_date (order_date),
|
||
INDEX idx_status (status)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
||
CREATE TABLE IF NOT EXISTS order_items (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
||
order_id BIGINT NOT NULL,
|
||
tenant_id BIGINT NOT NULL DEFAULT 0,
|
||
product_id BIGINT NOT NULL,
|
||
product_code VARCHAR(50) NOT NULL,
|
||
product_name VARCHAR(200) NOT NULL,
|
||
unit VARCHAR(20),
|
||
quantity INT NOT NULL DEFAULT 1,
|
||
unit_price DECIMAL(12,2) NOT NULL,
|
||
discount_rate DECIMAL(5,2) DEFAULT 100.00,
|
||
amount DECIMAL(12,2) NOT NULL,
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
INDEX idx_order_id (order_id),
|
||
INDEX idx_tenant_id (tenant_id),
|
||
INDEX idx_product_id (product_id)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
||
CREATE TABLE IF NOT EXISTS inventory_logs (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
||
tenant_id BIGINT NOT NULL DEFAULT 0,
|
||
product_id BIGINT NOT NULL,
|
||
warehouse_id BIGINT,
|
||
order_no VARCHAR(50),
|
||
in_out_type VARCHAR(10) NOT NULL COMMENT 'IN/OUT/ADJUST',
|
||
quantity INT NOT NULL,
|
||
before_stock INT NOT NULL DEFAULT 0,
|
||
after_stock INT NOT NULL DEFAULT 0,
|
||
operator_id BIGINT,
|
||
remark VARCHAR(500),
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
INDEX idx_tenant_product (tenant_id, product_id),
|
||
INDEX idx_created_at (created_at),
|
||
INDEX idx_order_no (order_no)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
||
CREATE TABLE IF NOT EXISTS warehouses (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
||
tenant_id BIGINT NOT NULL DEFAULT 0,
|
||
warehouse_code VARCHAR(50) NOT NULL,
|
||
warehouse_name VARCHAR(100) NOT NULL,
|
||
address VARCHAR(255),
|
||
contact_name VARCHAR(50),
|
||
contact_phone VARCHAR(20),
|
||
is_default TINYINT NOT NULL DEFAULT 0,
|
||
status TINYINT NOT NULL DEFAULT 1,
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
deleted_at DATETIME,
|
||
UNIQUE KEY uk_code_tenant (warehouse_code, tenant_id),
|
||
INDEX idx_tenant_id (tenant_id)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
||
CREATE TABLE IF NOT EXISTS audit_logs (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
||
tenant_id BIGINT NOT NULL DEFAULT 0,
|
||
user_id BIGINT,
|
||
username VARCHAR(50),
|
||
module VARCHAR(50) NOT NULL,
|
||
operation VARCHAR(100) NOT NULL,
|
||
method VARCHAR(200),
|
||
request_url VARCHAR(500),
|
||
request_method VARCHAR(10),
|
||
request_params TEXT,
|
||
request_body TEXT,
|
||
response_body TEXT,
|
||
ip_address VARCHAR(45),
|
||
user_agent VARCHAR(500),
|
||
execution_time INT NOT NULL DEFAULT 0,
|
||
status TINYINT NOT NULL DEFAULT 1,
|
||
error_message TEXT,
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
INDEX idx_tenant_id (tenant_id),
|
||
INDEX idx_user_id (user_id),
|
||
INDEX idx_module (module),
|
||
INDEX idx_created_at (created_at)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
||
CREATE TABLE IF NOT EXISTS scheduled_tasks (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
||
tenant_id BIGINT NOT NULL DEFAULT 0,
|
||
task_name VARCHAR(100) NOT NULL,
|
||
task_code VARCHAR(100) NOT NULL UNIQUE,
|
||
task_type VARCHAR(20) NOT NULL COMMENT 'HTTP/METHOD/MQ',
|
||
cron_expression VARCHAR(50),
|
||
target_url VARCHAR(500),
|
||
target_method VARCHAR(100),
|
||
target_params TEXT,
|
||
mq_topic VARCHAR(100),
|
||
mq_tag VARCHAR(100),
|
||
retry_count INT DEFAULT 0,
|
||
timeout_seconds INT DEFAULT 30,
|
||
status VARCHAR(20) NOT NULL DEFAULT 'STOPPED',
|
||
last_execution_at DATETIME,
|
||
last_execution_status VARCHAR(20),
|
||
next_execution_at DATETIME,
|
||
remark TEXT,
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
deleted_at DATETIME,
|
||
INDEX idx_tenant_id (tenant_id),
|
||
INDEX idx_status (status),
|
||
INDEX idx_next_execution (next_execution_at)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
||
CREATE TABLE IF NOT EXISTS task_execution_logs (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
||
task_id BIGINT NOT NULL,
|
||
tenant_id BIGINT NOT NULL DEFAULT 0,
|
||
execution_no VARCHAR(50) NOT NULL,
|
||
started_at DATETIME NOT NULL,
|
||
finished_at DATETIME,
|
||
execution_time INT,
|
||
status VARCHAR(20) NOT NULL,
|
||
result_message TEXT,
|
||
retry_count INT DEFAULT 0,
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
INDEX idx_task_id (task_id),
|
||
INDEX idx_status (status),
|
||
INDEX idx_started_at (started_at)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
||
SELECT '数据库表创建完成' AS result;
|
||
EOSQL
|
||
env:
|
||
- name: MYSQL_ROOT_PASSWORD
|
||
valueFrom:
|
||
secretKeyRef:
|
||
name: erp-secrets
|
||
key: MYSQL_ROOT_PASSWORD
|
||
resources:
|
||
requests:
|
||
cpu: 100m
|
||
memory: 128Mi
|
||
limits:
|
||
cpu: 500m
|
||
memory: 512Mi
|
||
imagePullSecrets:
|
||
- name: regcred
|