62 lines
1.8 KiB
SQL
62 lines
1.8 KiB
SQL
DO $$
|
|
BEGIN
|
|
IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'dbtool') THEN
|
|
CREATE ROLE dbtool
|
|
LOGIN
|
|
PASSWORD 'dbtool'
|
|
NOSUPERUSER
|
|
NOCREATEDB
|
|
NOCREATEROLE
|
|
INHERIT;
|
|
ELSE
|
|
ALTER ROLE dbtool
|
|
WITH LOGIN
|
|
PASSWORD 'dbtool'
|
|
NOSUPERUSER
|
|
NOCREATEDB
|
|
NOCREATEROLE
|
|
INHERIT;
|
|
END IF;
|
|
END
|
|
$$;
|
|
|
|
GRANT CONNECT ON DATABASE dbtool_demo TO dbtool;
|
|
|
|
DROP SCHEMA IF EXISTS qa_demo CASCADE;
|
|
CREATE SCHEMA qa_demo AUTHORIZATION postgres;
|
|
GRANT USAGE ON SCHEMA qa_demo TO dbtool;
|
|
ALTER ROLE dbtool IN DATABASE dbtool_demo SET search_path TO qa_demo, public;
|
|
SET search_path TO qa_demo;
|
|
|
|
CREATE TABLE accounts (
|
|
id BIGINT PRIMARY KEY,
|
|
email TEXT NOT NULL UNIQUE,
|
|
display_name TEXT,
|
|
is_active BOOLEAN NOT NULL,
|
|
created_at TIMESTAMPTZ NOT NULL
|
|
);
|
|
|
|
CREATE TABLE tickets (
|
|
id BIGINT PRIMARY KEY,
|
|
account_id BIGINT NOT NULL REFERENCES accounts(id),
|
|
title TEXT NOT NULL,
|
|
status TEXT NOT NULL,
|
|
notes TEXT,
|
|
amount_cents INTEGER NOT NULL,
|
|
created_at TIMESTAMPTZ NOT NULL
|
|
);
|
|
|
|
INSERT INTO accounts (id, email, display_name, is_active, created_at) VALUES
|
|
(1, 'alice@example.com', 'Alice', TRUE, '2026-03-01T08:30:00Z'),
|
|
(2, 'maria@example.com', 'Máría', FALSE, '2026-03-10T09:45:00Z'),
|
|
(3, 'zhang@example.com', '张敏', TRUE, '2026-03-15T12:00:00Z');
|
|
|
|
INSERT INTO tickets (id, account_id, title, status, notes, amount_cents, created_at) VALUES
|
|
(101, 1, 'login timeout', 'open', NULL, 0, '2026-03-20T10:00:00Z'),
|
|
(102, 1, 'csv export mismatch', 'closed', 'fixed after re-run', 1500, '2026-03-21T11:15:00Z'),
|
|
(103, 2, '权限验证', 'open', 'needs DBA review', 300, '2026-03-22T15:30:00Z'),
|
|
(104, 3, 'emoji smoke 😀', 'open', NULL, 42, '2026-03-23T07:05:00Z');
|
|
|
|
GRANT SELECT ON ALL TABLES IN SCHEMA qa_demo TO dbtool;
|
|
ALTER DEFAULT PRIVILEGES IN SCHEMA qa_demo GRANT SELECT ON TABLES TO dbtool;
|