42 lines
1.9 KiB
PL/PgSQL
42 lines
1.9 KiB
PL/PgSQL
CREATE TABLE t_platform_user (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
user_id BIGINT NOT NULL,
|
||
platform_type SMALLINT NOT NULL,
|
||
platform_openid VARCHAR(100) NOT NULL,
|
||
platform_unionid VARCHAR(100),
|
||
platform_session_key VARCHAR(100),
|
||
platform_extra JSONB,
|
||
last_login_time TIMESTAMP,
|
||
create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
deleted SMALLINT DEFAULT 0,
|
||
UNIQUE (platform_type, platform_openid),
|
||
CONSTRAINT fk_platform_user_user_id FOREIGN KEY (user_id) REFERENCES t_user(id) ON DELETE CASCADE
|
||
);
|
||
|
||
COMMENT ON TABLE t_platform_user IS '平台用户关联表（微信/抖音小程序用户信息）';
|
||
COMMENT ON COLUMN t_platform_user.id IS '平台用户ID（自增）';
|
||
COMMENT ON COLUMN t_platform_user.user_id IS '关联t_user.id';
|
||
COMMENT ON COLUMN t_platform_user.platform_type IS '平台类型：1-微信小程序，2-抖音小程序，3-支付宝小程序';
|
||
COMMENT ON COLUMN t_platform_user.platform_openid IS '平台唯一标识（微信openid/抖音open_id）';
|
||
COMMENT ON COLUMN t_platform_user.platform_unionid IS '平台统一标识（微信unionid，多小程序互通用）';
|
||
COMMENT ON COLUMN t_platform_user.platform_session_key IS '平台会话密钥（微信session_key，加密存储）';
|
||
COMMENT ON COLUMN t_platform_user.platform_extra IS '平台扩展字段（如抖音的user_name、微信的city等）';
|
||
COMMENT ON COLUMN t_platform_user.last_login_time IS '最后登录时间';
|
||
COMMENT ON COLUMN t_platform_user.create_time IS '创建时间';
|
||
COMMENT ON COLUMN t_platform_user.update_time IS '更新时间';
|
||
COMMENT ON COLUMN t_platform_user.deleted IS '软删除：0-未删，1-已删';
|
||
|
||
CREATE OR REPLACE FUNCTION set_update_time()
|
||
RETURNS TRIGGER AS $$
|
||
BEGIN
|
||
NEW.update_time = CURRENT_TIMESTAMP;
|
||
RETURN NEW;
|
||
END;
|
||
$$ LANGUAGE plpgsql;
|
||
|
||
CREATE TRIGGER t_platform_user_set_update_time
|
||
BEFORE UPDATE ON t_platform_user
|
||
FOR EACH ROW
|
||
EXECUTE FUNCTION set_update_time();
|