48 lines
2.0 KiB
MySQL
48 lines
2.0 KiB
MySQL
|
-- 创建公司合作关系表
|
|||
|
|
-- 记录采购商与工厂之间的合作关系
|
|||
|
|
|
|||
|
|
CREATE TABLE company_relationships (
|
|||
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|||
|
|
purchaser_company_id UUID NOT NULL REFERENCES companies(id),
|
|||
|
|
factory_company_id UUID NOT NULL REFERENCES companies(id),
|
|||
|
|
factory_type factory_type NOT NULL,
|
|||
|
|
status TEXT NOT NULL DEFAULT 'active', -- active, inactive
|
|||
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|||
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|||
|
|
UNIQUE(purchaser_company_id, factory_company_id)
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
-- 添加注释
|
|||
|
|
COMMENT ON TABLE company_relationships IS '公司合作关系表，记录采购商与工厂的合作关系';
|
|||
|
|
COMMENT ON COLUMN company_relationships.purchaser_company_id IS '采购商公司ID';
|
|||
|
|
COMMENT ON COLUMN company_relationships.factory_company_id IS '工厂公司ID';
|
|||
|
|
COMMENT ON COLUMN company_relationships.status IS '关系状态：active-合作中，inactive-已解除';
|
|||
|
|
|
|||
|
|
-- 创建索引
|
|||
|
|
CREATE INDEX idx_relationships_purchaser ON company_relationships(purchaser_company_id);
|
|||
|
|
CREATE INDEX idx_relationships_factory ON company_relationships(factory_company_id);
|
|||
|
|
CREATE INDEX idx_relationships_status ON company_relationships(status);
|
|||
|
|
|
|||
|
|
-- 启用RLS
|
|||
|
|
ALTER TABLE company_relationships ENABLE ROW LEVEL SECURITY;
|
|||
|
|
|
|||
|
|
-- 创建查询策略：双方公司成员可查看
|
|||
|
|
CREATE POLICY company_select_relationships ON company_relationships
|
|||
|
|
FOR SELECT USING (
|
|||
|
|
purchaser_company_id = get_user_master_company_id()
|
|||
|
|
OR factory_company_id = get_user_master_company_id()
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
-- 创建插入策略：仅采购商可创建关系
|
|||
|
|
CREATE POLICY company_insert_relationships ON company_relationships
|
|||
|
|
FOR INSERT WITH CHECK (
|
|||
|
|
purchaser_company_id = get_user_master_company_id()
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
-- 创建更新策略：双方公司可更新
|
|||
|
|
CREATE POLICY company_update_relationships ON company_relationships
|
|||
|
|
FOR UPDATE USING (
|
|||
|
|
purchaser_company_id = get_user_master_company_id()
|
|||
|
|
OR factory_company_id = get_user_master_company_id()
|
|||
|
|
);
|