initial_db.sh
1.75 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
# 使用 admin 用户连接到 ecommerce 数据库并创建表
kubectl exec -it -n ecommerce postgresql-0 -- psql -U admin -d ecommerce -c "
-- 创建 categories 表
CREATE TABLE IF NOT EXISTS categories (
id BIGSERIAL PRIMARY KEY,
name VARCHAR(255) NOT NULL UNIQUE,
description TEXT,
parent_id BIGINT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (parent_id) REFERENCES categories(id)
);
CREATE INDEX IF NOT EXISTS idx_categories_parent_id ON categories(parent_id);
CREATE INDEX IF NOT EXISTS idx_categories_name ON categories(name);
-- 创建 products 表
CREATE TABLE IF NOT EXISTS products (
id BIGSERIAL PRIMARY KEY,
name VARCHAR(255) NOT NULL,
description TEXT,
price DECIMAL(10,2) NOT NULL,
sku VARCHAR(100) UNIQUE,
category_id BIGINT NOT NULL,
stock_quantity INTEGER DEFAULT 0,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (category_id) REFERENCES categories(id)
);
CREATE INDEX IF NOT EXISTS idx_products_category_id ON products(category_id);
CREATE INDEX IF NOT EXISTS idx_products_sku ON products(sku);
-- 创建 inventory 表
CREATE TABLE IF NOT EXISTS inventory (
id BIGSERIAL PRIMARY KEY,
product_id BIGINT NOT NULL UNIQUE,
quantity INTEGER NOT NULL DEFAULT 0,
reserved_quantity INTEGER NOT NULL DEFAULT 0,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (product_id) REFERENCES products(id)
);
CREATE INDEX IF NOT EXISTS idx_inventory_product_id ON inventory(product_id);
"
# 验证表是否创建成功
kubectl exec -it -n ecommerce postgresql-0 -- psql -U admin -d ecommerce -c "\dt"