wwweww
49 lines
2.1 KiB
SQL
49 lines
2.1 KiB
SQL
CREATE TABLE shops (
|
|
id BIGINT PRIMARY KEY,
|
|
owner_id BIGINT NOT NULL UNIQUE,
|
|
name VARCHAR(200) NOT NULL,
|
|
banner TEXT,
|
|
description TEXT,
|
|
rating DECIMAL(3,2) DEFAULT 5.00,
|
|
total_orders INT DEFAULT 0,
|
|
player_count INT DEFAULT 0,
|
|
commission_type VARCHAR(20) NOT NULL DEFAULT 'percentage',
|
|
commission_value DECIMAL(10,2) NOT NULL,
|
|
allow_multi_shop BOOLEAN DEFAULT FALSE,
|
|
allow_independent_orders BOOLEAN DEFAULT TRUE,
|
|
dispatch_mode VARCHAR(20) NOT NULL DEFAULT 'manual',
|
|
announcements TEXT[] DEFAULT ARRAY[]::TEXT[],
|
|
template_config JSONB,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
|
|
CONSTRAINT chk_commission_type CHECK (commission_type IN ('fixed', 'percentage')),
|
|
CONSTRAINT chk_dispatch_mode CHECK (dispatch_mode IN ('manual', 'auto')),
|
|
CONSTRAINT chk_rating_range CHECK (rating >= 0 AND rating <= 5)
|
|
);
|
|
|
|
-- 基础索引
|
|
CREATE INDEX idx_shops_owner ON shops(owner_id);
|
|
CREATE INDEX idx_shops_rating ON shops(rating DESC);
|
|
|
|
-- 三元组索引用于店铺名称模糊搜索
|
|
CREATE INDEX idx_shops_name_trgm ON shops USING gin(name gin_trgm_ops);
|
|
|
|
-- 全文搜索索引
|
|
CREATE INDEX idx_shops_fulltext ON shops USING gin(
|
|
to_tsvector('simple', name || ' ' || coalesce(description, ''))
|
|
);
|
|
|
|
-- 复合索引优化排序查询
|
|
CREATE INDEX idx_shops_rating_orders ON shops(rating DESC, total_orders DESC);
|
|
|
|
-- 公告数组索引
|
|
CREATE INDEX idx_shops_announcements ON shops USING gin(announcements);
|
|
|
|
-- JSONB 索引优化模板配置查询
|
|
CREATE INDEX idx_shops_template ON shops USING gin(template_config);
|
|
|
|
CREATE TRIGGER trigger_shops_updated_at
|
|
BEFORE UPDATE ON shops
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION update_updated_at_column(); |