79 lines
2.3 KiB
MySQL
79 lines
2.3 KiB
MySQL
|
-- 0.（若旧库里还没有）创建 updated_at 自动更新函数
|
||
|
|
DO $$
|
||
|
|
BEGIN
|
||
|
|
IF NOT EXISTS (
|
||
|
|
SELECT 1 FROM pg_proc WHERE proname = 'update_updated_at_column'
|
||
|
|
) THEN
|
||
|
|
CREATE FUNCTION update_updated_at_column()
|
||
|
|
RETURNS TRIGGER AS $func$
|
||
|
|
BEGIN
|
||
|
|
NEW.updated_at = now();
|
||
|
|
RETURN NEW;
|
||
|
|
END;
|
||
|
|
$func$ LANGUAGE plpgsql;
|
||
|
|
END IF;
|
||
|
|
END
|
||
|
|
$$;
|
||
|
|
|
||
|
|
-- 1. 角色表
|
||
|
|
CREATE TABLE IF NOT EXISTS roles (
|
||
|
|
id SERIAL PRIMARY KEY,
|
||
|
|
name VARCHAR(64) UNIQUE NOT NULL,
|
||
|
|
description TEXT NOT NULL DEFAULT '',
|
||
|
|
permissions JSONB NOT NULL DEFAULT '[]'::jsonb,
|
||
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
|
|
);
|
||
|
|
|
||
|
|
-- 更新时间触发器
|
||
|
|
DROP TRIGGER IF EXISTS update_role_modtime ON roles;
|
||
|
|
CREATE TRIGGER update_role_modtime
|
||
|
|
BEFORE UPDATE ON roles
|
||
|
|
FOR EACH ROW
|
||
|
|
EXECUTE PROCEDURE update_updated_at_column();
|
||
|
|
|
||
|
|
-- 2. 用户角色关联表
|
||
|
|
CREATE TABLE IF NOT EXISTS user_roles (
|
||
|
|
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
||
|
|
role_id INTEGER NOT NULL REFERENCES roles(id) ON DELETE CASCADE,
|
||
|
|
assigned_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
|
|
PRIMARY KEY (user_id, role_id)
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE INDEX IF NOT EXISTS ix_user_roles_role_id ON user_roles(role_id);
|
||
|
|
|
||
|
|
-- 3. 插入默认角色
|
||
|
|
INSERT INTO roles (name, description, permissions)
|
||
|
|
VALUES ('admin', 'System administrator with full privileges', '["*"]')
|
||
|
|
ON CONFLICT (name) DO NOTHING;
|
||
|
|
|
||
|
|
-- 4. 给指定邮箱的用户授予 admin
|
||
|
|
DO $$
|
||
|
|
DECLARE
|
||
|
|
admin_role_id INTEGER;
|
||
|
|
target_user_id INTEGER;
|
||
|
|
BEGIN
|
||
|
|
SELECT id INTO admin_role_id FROM roles WHERE name = 'admin';
|
||
|
|
SELECT id INTO target_user_id FROM users WHERE email = 'zsh74110@gmail.com';
|
||
|
|
IF admin_role_id IS NOT NULL AND target_user_id IS NOT NULL THEN
|
||
|
|
INSERT INTO user_roles (user_id, role_id)
|
||
|
|
VALUES (target_user_id, admin_role_id)
|
||
|
|
ON CONFLICT DO NOTHING;
|
||
|
|
END IF;
|
||
|
|
END
|
||
|
|
$$;
|
||
|
|
|
||
|
|
-- 5. 文章排序字段
|
||
|
|
ALTER TABLE articles
|
||
|
|
ALTER COLUMN sort_weight TYPE bigint USING sort_weight::bigint,
|
||
|
|
ALTER COLUMN sort_weight SET DEFAULT 0;
|
||
|
|
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_articles_top_sort
|
||
|
|
ON articles (is_top DESC, sort_weight DESC, created_at DESC);
|
||
|
|
|
||
|
|
-- 6. 用户附加字段
|
||
|
|
ALTER TABLE users
|
||
|
|
ADD COLUMN IF NOT EXISTS phone TEXT,
|
||
|
|
ADD COLUMN IF NOT EXISTS user_type TEXT,
|
||
|
|
ADD COLUMN IF NOT EXISTS company_name TEXT;
|