Let’s say I want to create something like GitHub issues. I have a global unique UUID for each issue, and the corresponding repo ID. But I also need a sequence number for each issue which increments independently for each repo. How can I achieve something like this using CRDB and what do you think would be a better schema for this?
Hi! I don’t quite understand the schema design you’re seeking. Could you write out some SQL commands that illustrate what you want and where your questions are?
CREATE TABLE "public"."issues" ("id" UUID,"repo_id" UUID NOT NULL,"int_id" INT NOT NULL,"title" STRING NOT NULL, PRIMARY KEY ("id")); CREATE UNIQUE INDEX "repo_id_int_id" ON "public"."issues" (repo_id,int_id); ALTER TABLE "public"."issues" ALTER COLUMN "id" SET DEFAULT gen_random_uuid();
This is the basic schema of what I’m looking for. As you can see there are two different IDs, One is the UUID which is the primary key and automatically generated, The other one is an integer ID. Also combination of repo_id and int_id should be unique.
Each time an issue is created for a repo, I need to lookup the latest integer ID for that specific repo, +1, it and insert the issue with the new integer ID.
INSERT into issues (repo_id, int_id, title) VALUES ('33c897b8-8243-45ca-a7df-c82ceb6ffde1', (SELECT COALESCE((SELECT MAX(int_id) from issues WHERE repo_id = '33c897b8-8243-45ca-a7df-c82ceb6ffde1'), 0) + 1), 'Issue #1')
Is there any better way for doing this in CRDB?
aha! that clarifies things. You could make int_id a sequence. See docs.