41 lines
1.2 KiB
MySQL
41 lines
1.2 KiB
MySQL
|
-- Migration 191: Per-user menu visibility preferences
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS user_menu_preferences (
|
||
|
|
id SERIAL PRIMARY KEY,
|
||
|
|
user_id INTEGER NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
|
||
|
|
menu_key VARCHAR(120) NOT NULL,
|
||
|
|
visible BOOLEAN NOT NULL DEFAULT TRUE,
|
||
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
UNIQUE (user_id, menu_key)
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_user_menu_preferences_user_id
|
||
|
|
ON user_menu_preferences(user_id);
|
||
|
|
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_user_menu_preferences_menu_key
|
||
|
|
ON user_menu_preferences(menu_key);
|
||
|
|
|
||
|
|
CREATE OR REPLACE FUNCTION update_user_menu_preferences_updated_at()
|
||
|
|
RETURNS TRIGGER AS $$
|
||
|
|
BEGIN
|
||
|
|
NEW.updated_at = CURRENT_TIMESTAMP;
|
||
|
|
RETURN NEW;
|
||
|
|
END;
|
||
|
|
$$ LANGUAGE plpgsql;
|
||
|
|
|
||
|
|
DO $$
|
||
|
|
BEGIN
|
||
|
|
IF NOT EXISTS (
|
||
|
|
SELECT 1
|
||
|
|
FROM pg_trigger
|
||
|
|
WHERE tgname = 'user_menu_preferences_updated_at_trigger'
|
||
|
|
) THEN
|
||
|
|
CREATE TRIGGER user_menu_preferences_updated_at_trigger
|
||
|
|
BEFORE UPDATE ON user_menu_preferences
|
||
|
|
FOR EACH ROW
|
||
|
|
EXECUTE FUNCTION update_user_menu_preferences_updated_at();
|
||
|
|
END IF;
|
||
|
|
END;
|
||
|
|
$$;
|