zhenyi
125 lines
4.6 KiB
SQL
125 lines
4.6 KiB
SQL
-- 005: Issue workspace-level upgrade + slug removal
|
|
-- 1. Drop slug from workspace
|
|
-- 2. Lift issue from repo-level to workspace-level (repo_id -> workspace_id)
|
|
-- 3. Add issue_repo_relation for multi-repo association
|
|
-- 4. Add milestone_id to issue
|
|
-- 5. Add missing unique constraints
|
|
|
|
ALTER TABLE workspace DROP COLUMN IF EXISTS slug;
|
|
CREATE UNIQUE INDEX IF NOT EXISTS uq_workspace_name ON workspace (lower(name)) WHERE deleted_at IS NULL;
|
|
|
|
DROP INDEX IF EXISTS idx_issue_repo_id;
|
|
DROP INDEX IF EXISTS idx_issue_repo_created;
|
|
|
|
ALTER TABLE issue DROP CONSTRAINT IF EXISTS issue_repo_id_fkey;
|
|
|
|
DO $$
|
|
BEGIN
|
|
IF EXISTS (
|
|
SELECT 1
|
|
FROM information_schema.columns
|
|
WHERE table_schema = 'public'
|
|
AND table_name = 'issue'
|
|
AND column_name = 'repo_id'
|
|
)
|
|
AND NOT EXISTS (
|
|
SELECT 1
|
|
FROM information_schema.columns
|
|
WHERE table_schema = 'public'
|
|
AND table_name = 'issue'
|
|
AND column_name = 'workspace_id'
|
|
) THEN
|
|
ALTER TABLE issue RENAME COLUMN repo_id TO workspace_id;
|
|
END IF;
|
|
END $$;
|
|
|
|
DO $$
|
|
BEGIN
|
|
IF NOT EXISTS (
|
|
SELECT 1
|
|
FROM pg_constraint
|
|
WHERE conname = 'issue_workspace_id_fkey'
|
|
AND conrelid = 'issue'::regclass
|
|
) THEN
|
|
ALTER TABLE issue
|
|
ADD CONSTRAINT issue_workspace_id_fkey
|
|
FOREIGN KEY (workspace_id) REFERENCES workspace(id) ON DELETE CASCADE;
|
|
END IF;
|
|
END $$;
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_issue_workspace_id ON issue (workspace_id);
|
|
CREATE INDEX IF NOT EXISTS idx_issue_ws_created ON issue (workspace_id, created_at DESC);
|
|
|
|
DO $$
|
|
BEGIN
|
|
IF NOT EXISTS (
|
|
SELECT 1
|
|
FROM pg_constraint
|
|
WHERE conname = 'uq_issue_workspace_number'
|
|
AND conrelid = 'issue'::regclass
|
|
) THEN
|
|
ALTER TABLE issue
|
|
ADD CONSTRAINT uq_issue_workspace_number UNIQUE (workspace_id, number);
|
|
END IF;
|
|
END $$;
|
|
|
|
CREATE TABLE IF NOT EXISTS issue_repo_relation (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
issue_id UUID NOT NULL REFERENCES issue(id) ON DELETE CASCADE,
|
|
repo_id UUID NOT NULL REFERENCES repo(id) ON DELETE CASCADE,
|
|
relation_type TEXT NOT NULL,
|
|
created_by UUID NULL REFERENCES "user"(id) ON DELETE CASCADE,
|
|
created_at TIMESTAMPTZ NOT NULL,
|
|
CONSTRAINT uq_issue_repo_relation UNIQUE (issue_id, repo_id)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_issue_repo_relation_issue_id
|
|
ON issue_repo_relation (issue_id);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_issue_repo_relation_repo_id
|
|
ON issue_repo_relation (repo_id);
|
|
|
|
ALTER TABLE issue
|
|
ADD COLUMN IF NOT EXISTS milestone_id UUID NULL REFERENCES issue_milestone(id) ON DELETE SET NULL;
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_issue_milestone_id ON issue (milestone_id);
|
|
|
|
DO $$
|
|
BEGIN
|
|
IF NOT EXISTS (
|
|
SELECT 1
|
|
FROM pg_constraint
|
|
WHERE conname = 'uq_issue_label_relation'
|
|
AND conrelid = 'issue_label_relation'::regclass
|
|
) THEN
|
|
ALTER TABLE issue_label_relation
|
|
ADD CONSTRAINT uq_issue_label_relation UNIQUE (issue_id, label_id);
|
|
END IF;
|
|
END $$;
|
|
CREATE UNIQUE INDEX IF NOT EXISTS uq_repo_workspace_name
|
|
ON repo (workspace_id, lower(name)) WHERE deleted_at IS NULL;
|
|
|
|
-- ─── 7. pull_request: unique number per repo ───────────────────────────
|
|
DO $$
|
|
BEGIN
|
|
IF NOT EXISTS (
|
|
SELECT 1 FROM pg_constraint
|
|
WHERE conname = 'uq_pull_request_repo_number' AND conrelid = 'pull_request'::regclass
|
|
) THEN
|
|
ALTER TABLE pull_request
|
|
ADD CONSTRAINT uq_pull_request_repo_number UNIQUE (repo_id, number);
|
|
END IF;
|
|
END $$;
|
|
|
|
-- ─── 8. pr_label_relation: unique constraint ────────────────────────────
|
|
DO $$
|
|
BEGIN
|
|
IF NOT EXISTS (
|
|
SELECT 1 FROM pg_constraint
|
|
WHERE conname = 'uq_pr_label_relation' AND conrelid = 'pr_label_relation'::regclass
|
|
) THEN
|
|
ALTER TABLE pr_label_relation
|
|
ADD CONSTRAINT uq_pr_label_relation UNIQUE (pull_request_id, label_id);
|
|
END IF;
|
|
END $$;
|