~jan0sch/smederee
Showing details for patch 3b26852baf0846b946c0c72cc9fa4522da709a46.
- - 3b26852baf0846b946c0c72cc9fa4522da709a46
BREAKING: database: remove unnecessary quoting in sql
Summary of changes
22 files modified with 502 lines added and 511 lines removed
- modules/hub/src/main/resources/db/migration/hub/V1__base_tables.sql with 74 added and 73 removed lines
- modules/hub/src/main/resources/db/migration/hub/V2__repository_tables.sql with 24 added and 25 removed lines
- modules/hub/src/main/resources/db/migration/hub/V3__fork_tables.sql with 12 added and 13 removed lines
- modules/hub/src/main/resources/db/migration/hub/V4__add_language.sql with 4 added and 4 removed lines
- modules/hub/src/main/resources/db/migration/hub/V6__add_full_name.sql with 4 added and 4 removed lines
- modules/hub/src/main/resources/db/migration/hub/V7__organisations_table.sql with 40 added and 41 removed lines
- modules/hub/src/main/scala/de/smederee/hub/DoobieAuthenticationRepository.scala with 11 added and 11 removed lines
- modules/hub/src/main/scala/de/smederee/hub/DoobieOrganisationRepository.scala with 5 added and 5 removed lines
- modules/hub/src/main/scala/de/smederee/hub/DoobieResetPasswordRepository.scala with 5 added and 5 removed lines
- modules/hub/src/main/scala/de/smederee/hub/DoobieSignupRepository.scala with 3 added and 5 removed lines
- modules/hub/src/main/scala/de/smederee/hub/DoobieVcsMetadataRepository.scala with 32 added and 34 removed lines
- modules/hub/src/test/scala/de/smederee/hub/BaseSpec.scala with 10 added and 10 removed lines
- modules/tickets/src/main/resources/db/migration/tickets/V1__create_schema.sql with 3 added and 3 removed lines
- modules/tickets/src/main/resources/db/migration/tickets/V2__base_tables.sql with 155 added and 155 removed lines
- modules/tickets/src/main/resources/db/migration/tickets/V3__add_language.sql with 4 added and 4 removed lines
- modules/tickets/src/main/resources/db/migration/tickets/V4__add_resolution.sql with 4 added and 4 removed lines
- modules/tickets/src/main/resources/db/migration/tickets/V5__add_closeable_milestones.sql with 4 added and 5 removed lines
- modules/tickets/src/main/scala/de/smederee/tickets/DoobieLabelRepository.scala with 5 added and 6 removed lines
- modules/tickets/src/main/scala/de/smederee/tickets/DoobieProjectRepository.scala with 30 added and 29 removed lines
- modules/tickets/src/main/scala/de/smederee/tickets/DoobieTicketRepository.scala with 63 added and 64 removed lines
- modules/tickets/src/main/scala/de/smederee/tickets/DoobieTicketServiceApi.scala with 2 added and 3 removed lines
- modules/tickets/src/test/scala/de/smederee/tickets/BaseSpec.scala with 8 added and 8 removed lines
diff -rN -u old-smederee/modules/hub/src/main/resources/db/migration/hub/V1__base_tables.sql new-smederee/modules/hub/src/main/resources/db/migration/hub/V1__base_tables.sql --- old-smederee/modules/hub/src/main/resources/db/migration/hub/V1__base_tables.sql 2025-01-13 01:25:25.761526357 +0000 +++ new-smederee/modules/hub/src/main/resources/db/migration/hub/V1__base_tables.sql 2025-01-13 01:25:25.765526369 +0000 @@ -1,88 +1,89 @@ -CREATE SCHEMA IF NOT EXISTS "hub"; +CREATE SCHEMA IF NOT EXISTS hub; +COMMENT ON SCHEMA hub IS 'Central data storage containing accounts, projects and so on.'; -CREATE TABLE "hub"."accounts" +CREATE TABLE hub.accounts ( - "uid" UUID NOT NULL, - "name" CHARACTER VARYING(32) NOT NULL, - "email" CHARACTER VARYING(128) NOT NULL, - "password" TEXT, - "failed_attempts" INTEGER DEFAULT 0, - "locked_at" TIMESTAMP WITH TIME ZONE DEFAULT NULL, - "unlock_token" TEXT DEFAULT NULL, - "reset_expiry" TIMESTAMP WITH TIME ZONE DEFAULT NULL, - "reset_token" TEXT DEFAULT NULL, - "created_at" TIMESTAMP WITH TIME ZONE NOT NULL, - "updated_at" TIMESTAMP WITH TIME ZONE NOT NULL, - "validated_email" BOOLEAN DEFAULT FALSE, - "validation_token" TEXT DEFAULT NULL, - CONSTRAINT "accounts_pk" PRIMARY KEY ("uid"), - CONSTRAINT "accounts_unique_name" UNIQUE ("name"), - CONSTRAINT "accounts_unique_email" UNIQUE ("email") + uid UUID NOT NULL, + name CHARACTER VARYING(32) NOT NULL, + email CHARACTER VARYING(128) NOT NULL, + password TEXT, + failed_attempts INTEGER DEFAULT 0, + locked_at TIMESTAMP WITH TIME ZONE DEFAULT NULL, + unlock_token TEXT DEFAULT NULL, + reset_expiry TIMESTAMP WITH TIME ZONE DEFAULT NULL, + reset_token TEXT DEFAULT NULL, + created_at TIMESTAMP WITH TIME ZONE NOT NULL, + updated_at TIMESTAMP WITH TIME ZONE NOT NULL, + validated_email BOOLEAN DEFAULT FALSE, + validation_token TEXT DEFAULT NULL, + CONSTRAINT accounts_pk PRIMARY KEY (uid), + CONSTRAINT accounts_unique_name UNIQUE (name), + CONSTRAINT accounts_unique_email UNIQUE (email) ) WITH ( OIDS=FALSE ); -COMMENT ON TABLE "hub"."accounts" IS 'All user accounts for the system live within this table.'; -COMMENT ON COLUMN "hub"."accounts"."uid" IS 'A globally unique ID for the related user account.'; -COMMENT ON COLUMN "hub"."accounts"."name" IS 'A username between 2 and 32 characters which must be globally unique, contain only lowercase alphanumeric characters and start with a character.'; -COMMENT ON COLUMN "hub"."accounts"."email" IS 'A globally unique email address associated with the account.'; -COMMENT ON COLUMN "hub"."accounts"."password" IS 'The hashed password for the account.'; -COMMENT ON COLUMN "hub"."accounts"."failed_attempts" IS 'The number of failed login attempts since the last sucessful login.'; -COMMENT ON COLUMN "hub"."accounts"."locked_at" IS 'A timestamp when the account was locked. If this value is not NULL then the account should be considered locked'; -COMMENT ON COLUMN "hub"."accounts"."unlock_token" IS 'An unlock token which can be used to unlock the account.'; -COMMENT ON COLUMN "hub"."accounts"."reset_expiry" IS 'The timestamp when the reset token will expire.'; -COMMENT ON COLUMN "hub"."accounts"."reset_token" IS 'A token which can be used for a password reset.'; -COMMENT ON COLUMN "hub"."accounts"."created_at" IS 'The timestamp of when the account was created.'; -COMMENT ON COLUMN "hub"."accounts"."updated_at" IS 'A timestamp when the account was last changed.'; -COMMENT ON COLUMN "hub"."accounts"."validated_email" IS 'This flag indicates if the email address of the user has been validated via a validation email.'; -COMMENT ON COLUMN "hub"."accounts"."validation_token" IS 'A token used to validate the email address of the user.'; +COMMENT ON TABLE hub.accounts IS 'All user accounts for the system live within this table.'; +COMMENT ON COLUMN hub.accounts.uid IS 'A globally unique ID for the related user account.'; +COMMENT ON COLUMN hub.accounts.name IS 'A username between 2 and 32 characters which must be globally unique, contain only lowercase alphanumeric characters and start with a character.'; +COMMENT ON COLUMN hub.accounts.email IS 'A globally unique email address associated with the account.'; +COMMENT ON COLUMN hub.accounts.password IS 'The hashed password for the account.'; +COMMENT ON COLUMN hub.accounts.failed_attempts IS 'The number of failed login attempts since the last sucessful login.'; +COMMENT ON COLUMN hub.accounts.locked_at IS 'A timestamp when the account was locked. If this value is not NULL then the account should be considered locked'; +COMMENT ON COLUMN hub.accounts.unlock_token IS 'An unlock token which can be used to unlock the account.'; +COMMENT ON COLUMN hub.accounts.reset_expiry IS 'The timestamp when the reset token will expire.'; +COMMENT ON COLUMN hub.accounts.reset_token IS 'A token which can be used for a password reset.'; +COMMENT ON COLUMN hub.accounts.created_at IS 'The timestamp of when the account was created.'; +COMMENT ON COLUMN hub.accounts.updated_at IS 'A timestamp when the account was last changed.'; +COMMENT ON COLUMN hub.accounts.validated_email IS 'This flag indicates if the email address of the user has been validated via a validation email.'; +COMMENT ON COLUMN hub.accounts.validation_token IS 'A token used to validate the email address of the user.'; -CREATE TABLE "hub"."sessions" +CREATE TABLE hub.sessions ( - "id" VARCHAR(32) NOT NULL, - "uid" UUID NOT NULL, - "created_at" TIMESTAMP WITH TIME ZONE NOT NULL, - "updated_at" TIMESTAMP WITH TIME ZONE NOT NULL, - CONSTRAINT "sessions_pk" PRIMARY KEY ("id"), - CONSTRAINT "sessions_fk_uid" FOREIGN KEY ("uid") - REFERENCES "hub"."accounts" ("uid") ON UPDATE CASCADE ON DELETE CASCADE + id VARCHAR(32) NOT NULL, + uid UUID NOT NULL, + created_at TIMESTAMP WITH TIME ZONE NOT NULL, + updated_at TIMESTAMP WITH TIME ZONE NOT NULL, + CONSTRAINT sessions_pk PRIMARY KEY (id), + CONSTRAINT sessions_fk_uid FOREIGN KEY (uid) + REFERENCES hub.accounts (uid) ON UPDATE CASCADE ON DELETE CASCADE ) WITH ( OIDS=FALSE ); -COMMENT ON TABLE "hub"."sessions" IS 'Keeps the sessions of users.'; -COMMENT ON COLUMN "hub"."sessions"."id" IS 'A globally unique session ID.'; -COMMENT ON COLUMN "hub"."sessions"."uid" IS 'The unique ID of the user account to whom the session belongs.'; -COMMENT ON COLUMN "hub"."sessions"."created_at" IS 'The timestamp of when the session was created.'; -COMMENT ON COLUMN "hub"."sessions"."updated_at" IS 'The session ID should be re-generated in regular intervals resulting in a copy of the old session entry with a new ID and the corresponding timestamp in this column.'; +COMMENT ON TABLE hub.sessions IS 'Keeps the sessions of users.'; +COMMENT ON COLUMN hub.sessions.id IS 'A globally unique session ID.'; +COMMENT ON COLUMN hub.sessions.uid IS 'The unique ID of the user account to whom the session belongs.'; +COMMENT ON COLUMN hub.sessions.created_at IS 'The timestamp of when the session was created.'; +COMMENT ON COLUMN hub.sessions.updated_at IS 'The session ID should be re-generated in regular intervals resulting in a copy of the old session entry with a new ID and the corresponding timestamp in this column.'; -CREATE TABLE "hub"."ssh_keys" +CREATE TABLE hub.ssh_keys ( - "id" UUID NOT NULL, - "uid" UUID NOT NULL, - "key_type" CHARACTER VARYING(32) NOT NULL, - "key" TEXT NOT NULL, - "fingerprint" CHARACTER VARYING(256) NOT NULL, - "comment" CHARACTER VARYING(256) DEFAULT NULL, - "created_at" TIMESTAMP WITH TIME ZONE NOT NULL, - "last_used_at" TIMESTAMP WITH TIME ZONE DEFAULT NULL, - CONSTRAINT "ssh_keys_pk" PRIMARY KEY ("id"), - CONSTRAINT "ssh_keys_unique_fp" UNIQUE ("fingerprint"), - CONSTRAINT "ssh_keys_fk_uid" FOREIGN KEY ("uid") - REFERENCES "hub"."accounts" ("uid") ON UPDATE CASCADE ON DELETE CASCADE + id UUID NOT NULL, + uid UUID NOT NULL, + key_type CHARACTER VARYING(32) NOT NULL, + key TEXT NOT NULL, + fingerprint CHARACTER VARYING(256) NOT NULL, + comment CHARACTER VARYING(256) DEFAULT NULL, + created_at TIMESTAMP WITH TIME ZONE NOT NULL, + last_used_at TIMESTAMP WITH TIME ZONE DEFAULT NULL, + CONSTRAINT ssh_keys_pk PRIMARY KEY (id), + CONSTRAINT ssh_keys_unique_fp UNIQUE (fingerprint), + CONSTRAINT ssh_keys_fk_uid FOREIGN KEY (uid) + REFERENCES hub.accounts (uid) ON UPDATE CASCADE ON DELETE CASCADE ) WITH ( OIDS=FALSE ); -COMMENT ON TABLE "hub"."ssh_keys" IS 'SSH keys uploaded by users live within this table. Updates are not intended, so keys have to be deleted and re-uploaded upon changes.'; -COMMENT ON COLUMN "hub"."ssh_keys"."id" IS 'The globally unique ID of the ssh key.'; -COMMENT ON COLUMN "hub"."ssh_keys"."uid" IS 'The unique ID of the user account to whom the ssh key belongs.'; -COMMENT ON COLUMN "hub"."ssh_keys"."key_type" IS 'The type of the key e.g. ssh-rsa or ssh-ed25519 and so on.'; -COMMENT ON COLUMN "hub"."ssh_keys"."key" IS 'A base 64 string containing the public ssh key.'; -COMMENT ON COLUMN "hub"."ssh_keys"."fingerprint" IS 'The fingerprint of the ssh key. It must be unique because a key can only be used by one account.'; -COMMENT ON COLUMN "hub"."ssh_keys"."comment" IS 'An optional comment for the ssh key limited to 256 characters.'; -COMMENT ON COLUMN "hub"."ssh_keys"."created_at" IS 'The timestamp of when the ssh key was created.'; -COMMENT ON COLUMN "hub"."ssh_keys"."last_used_at" IS 'The timestamp of when the ssh key was last used by the user.'; +COMMENT ON TABLE hub.ssh_keys IS 'SSH keys uploaded by users live within this table. Updates are not intended, so keys have to be deleted and re-uploaded upon changes.'; +COMMENT ON COLUMN hub.ssh_keys.id IS 'The globally unique ID of the ssh key.'; +COMMENT ON COLUMN hub.ssh_keys.uid IS 'The unique ID of the user account to whom the ssh key belongs.'; +COMMENT ON COLUMN hub.ssh_keys.key_type IS 'The type of the key e.g. ssh-rsa or ssh-ed25519 and so on.'; +COMMENT ON COLUMN hub.ssh_keys.key IS 'A base 64 string containing the public ssh key.'; +COMMENT ON COLUMN hub.ssh_keys.fingerprint IS 'The fingerprint of the ssh key. It must be unique because a key can only be used by one account.'; +COMMENT ON COLUMN hub.ssh_keys.comment IS 'An optional comment for the ssh key limited to 256 characters.'; +COMMENT ON COLUMN hub.ssh_keys.created_at IS 'The timestamp of when the ssh key was created.'; +COMMENT ON COLUMN hub.ssh_keys.last_used_at IS 'The timestamp of when the ssh key was last used by the user.'; diff -rN -u old-smederee/modules/hub/src/main/resources/db/migration/hub/V2__repository_tables.sql new-smederee/modules/hub/src/main/resources/db/migration/hub/V2__repository_tables.sql --- old-smederee/modules/hub/src/main/resources/db/migration/hub/V2__repository_tables.sql 2025-01-13 01:25:25.761526357 +0000 +++ new-smederee/modules/hub/src/main/resources/db/migration/hub/V2__repository_tables.sql 2025-01-13 01:25:25.765526369 +0000 @@ -1,30 +1,29 @@ -CREATE TABLE "hub"."repositories" +CREATE TABLE hub.repositories ( - "id" BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, - "name" CHARACTER VARYING(64) NOT NULL, - "owner" UUID NOT NULL, - "is_private" BOOLEAN NOT NULL DEFAULT FALSE, - "description" CHARACTER VARYING(254), - "vcs_type" CHARACTER VARYING(16) NOT NULL, - "website" TEXT, - "created_at" TIMESTAMP WITH TIME ZONE NOT NULL, - "updated_at" TIMESTAMP WITH TIME ZONE NOT NULL, - CONSTRAINT "repositories_unique_owner_name" UNIQUE ("owner", "name"), - CONSTRAINT "repositories_fk_uid" FOREIGN KEY ("owner") - REFERENCES "hub"."accounts" ("uid") ON UPDATE CASCADE ON DELETE CASCADE + id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, + name CHARACTER VARYING(64) NOT NULL, + owner UUID NOT NULL, + is_private BOOLEAN NOT NULL DEFAULT FALSE, + description CHARACTER VARYING(254), + vcs_type CHARACTER VARYING(16) NOT NULL, + website TEXT, + created_at TIMESTAMP WITH TIME ZONE NOT NULL, + updated_at TIMESTAMP WITH TIME ZONE NOT NULL, + CONSTRAINT repositories_unique_owner_name UNIQUE (owner, name), + CONSTRAINT repositories_fk_uid FOREIGN KEY (owner) + REFERENCES hub.accounts (uid) ON UPDATE CASCADE ON DELETE CASCADE ) WITH ( OIDS=FALSE ); -COMMENT ON TABLE "hub"."repositories" IS 'All repositories i.e. their metadata are stored within this table. The combination of owner id and repository name must always be unique.'; -COMMENT ON COLUMN "hub"."repositories"."id" IS 'An auto generated primary key.'; -COMMENT ON COLUMN "hub"."repositories"."name" IS 'A repository name must start with a letter or number and must contain only alphanumeric ASCII characters as well as minus or underscore signs. It must be between 2 and 64 characters long.'; -COMMENT ON COLUMN "hub"."repositories"."owner" IS 'The unique ID of the user account that owns the repository.'; -COMMENT ON COLUMN "hub"."repositories"."is_private" IS 'A flag indicating if this repository is private i.e. only visible / accessible for accounts with appropriate permissions.'; -COMMENT ON COLUMN "hub"."repositories"."description" IS 'An optional short text description of the repository.'; -COMMENT ON COLUMN "hub"."repositories"."vcs_type" IS 'The type of the underlying DVCS that manages the repository.'; -COMMENT ON COLUMN "hub"."repositories"."website" IS 'An optional uri pointing to a website related to the repository / project.'; -COMMENT ON COLUMN "hub"."repositories"."created_at" IS 'The timestamp of when the repository was created.'; -COMMENT ON COLUMN "hub"."repositories"."updated_at" IS 'A timestamp when the repository was last changed.'; - +COMMENT ON TABLE hub.repositories IS 'All repositories i.e. their metadata are stored within this table. The combination of owner id and repository name must always be unique.'; +COMMENT ON COLUMN hub.repositories.id IS 'An auto generated primary key.'; +COMMENT ON COLUMN hub.repositories.name IS 'A repository name must start with a letter or number and must contain only alphanumeric ASCII characters as well as minus or underscore signs. It must be between 2 and 64 characters long.'; +COMMENT ON COLUMN hub.repositories.owner IS 'The unique ID of the user account that owns the repository.'; +COMMENT ON COLUMN hub.repositories.is_private IS 'A flag indicating if this repository is private i.e. only visible / accessible for accounts with appropriate permissions.'; +COMMENT ON COLUMN hub.repositories.description IS 'An optional short text description of the repository.'; +COMMENT ON COLUMN hub.repositories.vcs_type IS 'The type of the underlying DVCS that manages the repository.'; +COMMENT ON COLUMN hub.repositories.website IS 'An optional uri pointing to a website related to the repository / project.'; +COMMENT ON COLUMN hub.repositories.created_at IS 'The timestamp of when the repository was created.'; +COMMENT ON COLUMN hub.repositories.updated_at IS 'A timestamp when the repository was last changed.'; diff -rN -u old-smederee/modules/hub/src/main/resources/db/migration/hub/V3__fork_tables.sql new-smederee/modules/hub/src/main/resources/db/migration/hub/V3__fork_tables.sql --- old-smederee/modules/hub/src/main/resources/db/migration/hub/V3__fork_tables.sql 2025-01-13 01:25:25.761526357 +0000 +++ new-smederee/modules/hub/src/main/resources/db/migration/hub/V3__fork_tables.sql 2025-01-13 01:25:25.765526369 +0000 @@ -1,18 +1,17 @@ -CREATE TABLE "hub"."forks" +CREATE TABLE hub.forks ( - "original_repo" BIGINT NOT NULL, - "forked_repo" BIGINT NOT NULL, - CONSTRAINT "forks_pk" PRIMARY KEY ("original_repo", "forked_repo"), - CONSTRAINT "forks_fk_original_repo" FOREIGN KEY ("original_repo") - REFERENCES "hub"."repositories" ("id") ON UPDATE CASCADE ON DELETE CASCADE, - CONSTRAINT "forks_fk_forked_repo" FOREIGN KEY ("forked_repo") - REFERENCES "hub"."repositories" ("id") ON UPDATE CASCADE ON DELETE CASCADE + original_repo BIGINT NOT NULL, + forked_repo BIGINT NOT NULL, + CONSTRAINT forks_pk PRIMARY KEY (original_repo, forked_repo), + CONSTRAINT forks_fk_original_repo FOREIGN KEY (original_repo) + REFERENCES hub.repositories (id) ON UPDATE CASCADE ON DELETE CASCADE, + CONSTRAINT forks_fk_forked_repo FOREIGN KEY (forked_repo) + REFERENCES hub.repositories (id) ON UPDATE CASCADE ON DELETE CASCADE ) WITH ( OIDS=FALSE ); -COMMENT ON TABLE "hub"."forks" IS 'Stores fork relationships between repositories.'; -COMMENT ON COLUMN "hub"."forks"."original_repo" IS 'The ID of the original repository from which was forked.'; -COMMENT ON COLUMN "hub"."forks"."forked_repo" IS 'The ID of the repository which is the fork.'; - +COMMENT ON TABLE hub.forks IS 'Stores fork relationships between repositories.'; +COMMENT ON COLUMN hub.forks.original_repo IS 'The ID of the original repository from which was forked.'; +COMMENT ON COLUMN hub.forks.forked_repo IS 'The ID of the repository which is the fork.'; diff -rN -u old-smederee/modules/hub/src/main/resources/db/migration/hub/V4__add_language.sql new-smederee/modules/hub/src/main/resources/db/migration/hub/V4__add_language.sql --- old-smederee/modules/hub/src/main/resources/db/migration/hub/V4__add_language.sql 2025-01-13 01:25:25.761526357 +0000 +++ new-smederee/modules/hub/src/main/resources/db/migration/hub/V4__add_language.sql 2025-01-13 01:25:25.765526369 +0000 @@ -1,4 +1,4 @@ -ALTER TABLE "hub"."accounts" - ADD COLUMN "language" CHARACTER VARYING(3) DEFAULT NULL; +ALTER TABLE hub.accounts + ADD COLUMN language CHARACTER VARYING(3) DEFAULT NULL; -COMMENT ON COLUMN "hub"."accounts"."language" IS 'The ISO-639 language code of the preferred language of the user.'; +COMMENT ON COLUMN hub.accounts.language IS 'The ISO-639 language code of the preferred language of the user.'; diff -rN -u old-smederee/modules/hub/src/main/resources/db/migration/hub/V6__add_full_name.sql new-smederee/modules/hub/src/main/resources/db/migration/hub/V6__add_full_name.sql --- old-smederee/modules/hub/src/main/resources/db/migration/hub/V6__add_full_name.sql 2025-01-13 01:25:25.761526357 +0000 +++ new-smederee/modules/hub/src/main/resources/db/migration/hub/V6__add_full_name.sql 2025-01-13 01:25:25.765526369 +0000 @@ -1,4 +1,4 @@ -ALTER TABLE "hub"."accounts" - ADD COLUMN "full_name" CHARACTER VARYING(128); +ALTER TABLE hub.accounts + ADD COLUMN full_name CHARACTER VARYING(128); -COMMENT ON COLUMN "hub"."accounts"."full_name" IS 'The optional human readable full name of an account which must be non empty and is limited to 128 characters.'; +COMMENT ON COLUMN hub.accounts.full_name IS 'The optional human readable full name of an account which must be non empty and is limited to 128 characters.'; diff -rN -u old-smederee/modules/hub/src/main/resources/db/migration/hub/V7__organisations_table.sql new-smederee/modules/hub/src/main/resources/db/migration/hub/V7__organisations_table.sql --- old-smederee/modules/hub/src/main/resources/db/migration/hub/V7__organisations_table.sql 2025-01-13 01:25:25.761526357 +0000 +++ new-smederee/modules/hub/src/main/resources/db/migration/hub/V7__organisations_table.sql 2025-01-13 01:25:25.765526369 +0000 @@ -1,51 +1,50 @@ -CREATE TABLE "hub"."organisations" +CREATE TABLE hub.organisations ( - "id" UUID NOT NULL, - "name" CHARACTER VARYING(32) NOT NULL, - "owner" UUID NOT NULL, - "full_name" CHARACTER VARYING(128), - "description" CHARACTER VARYING(254), - "is_private" BOOLEAN NOT NULL DEFAULT FALSE, - "website" TEXT, - "created_at" TIMESTAMP WITH TIME ZONE NOT NULL, - "updated_at" TIMESTAMP WITH TIME ZONE NOT NULL, - CONSTRAINT "organisations_pk" PRIMARY KEY ("id"), - CONSTRAINT "organisations_unique_name" UNIQUE ("name"), - CONSTRAINT "organisations_fk_uid" FOREIGN KEY ("owner") - REFERENCES "hub"."accounts" ("uid") ON UPDATE CASCADE ON DELETE CASCADE + id UUID NOT NULL, + name CHARACTER VARYING(32) NOT NULL, + owner UUID NOT NULL, + full_name CHARACTER VARYING(128), + description CHARACTER VARYING(254), + is_private BOOLEAN NOT NULL DEFAULT FALSE, + website TEXT, + created_at TIMESTAMP WITH TIME ZONE NOT NULL, + updated_at TIMESTAMP WITH TIME ZONE NOT NULL, + CONSTRAINT organisations_pk PRIMARY KEY (id), + CONSTRAINT organisations_unique_name UNIQUE (name), + CONSTRAINT organisations_fk_uid FOREIGN KEY (owner) + REFERENCES hub.accounts (uid) ON UPDATE CASCADE ON DELETE CASCADE ) WITH ( OIDS=FALSE ); -CREATE INDEX "organisations_private" ON "hub"."organisations" ("is_private"); +CREATE INDEX organisations_private ON hub.organisations (is_private); -COMMENT ON TABLE "hub"."organisations" IS 'All organisations live within this table.'; -COMMENT ON COLUMN "hub"."organisations"."id" IS 'A globally unique ID for the organisation.'; -COMMENT ON COLUMN "hub"."organisations"."name" IS 'A name (like a username) between 2 and 32 characters which must be globally unique, contain only lowercase alphanumeric characters and start with a character.'; -COMMENT ON COLUMN "hub"."organisations"."owner" IS 'The unique ID of the user account owning the organisation.'; -COMMENT ON COLUMN "hub"."organisations"."full_name" IS 'An optional full name for the organisation.'; -COMMENT ON COLUMN "hub"."organisations"."description" IS 'An optional description of the organisation.'; -COMMENT ON COLUMN "hub"."organisations"."is_private" IS 'A flag indicating if this organisation is private i.e. only visible / accessible for accounts with appropriate permissions.'; -COMMENT ON COLUMN "hub"."organisations"."website" IS 'An optional uri pointing to a website related to the organisation.'; -COMMENT ON COLUMN "hub"."organisations"."created_at" IS 'The timestamp of when the organisation was created.'; -COMMENT ON COLUMN "hub"."organisations"."updated_at" IS 'A timestamp when the organisation was last changed.'; +COMMENT ON TABLE hub.organisations IS 'All organisations live within this table.'; +COMMENT ON COLUMN hub.organisations.id IS 'A globally unique ID for the organisation.'; +COMMENT ON COLUMN hub.organisations.name IS 'A name (like a username) between 2 and 32 characters which must be globally unique, contain only lowercase alphanumeric characters and start with a character.'; +COMMENT ON COLUMN hub.organisations.owner IS 'The unique ID of the user account owning the organisation.'; +COMMENT ON COLUMN hub.organisations.full_name IS 'An optional full name for the organisation.'; +COMMENT ON COLUMN hub.organisations.description IS 'An optional description of the organisation.'; +COMMENT ON COLUMN hub.organisations.is_private IS 'A flag indicating if this organisation is private i.e. only visible / accessible for accounts with appropriate permissions.'; +COMMENT ON COLUMN hub.organisations.website IS 'An optional uri pointing to a website related to the organisation.'; +COMMENT ON COLUMN hub.organisations.created_at IS 'The timestamp of when the organisation was created.'; +COMMENT ON COLUMN hub.organisations.updated_at IS 'A timestamp when the organisation was last changed.'; -CREATE TABLE "hub"."organisation_admins" +CREATE TABLE hub.organisation_admins ( - "organisation" UUID NOT NULL, - "admin" UUID NOT NULL, - CONSTRAINT "organisation_admins_pk" PRIMARY KEY ("organisation", "admin"), - CONSTRAINT "organisation_admins_fk_oid" FOREIGN KEY ("organisation") - REFERENCES "hub"."organisations" ("id") ON UPDATE CASCADE ON DELETE CASCADE, - CONSTRAINT "organisation_admins_fk_uid" FOREIGN KEY ("admin") - REFERENCES "hub"."accounts" ("uid") ON UPDATE CASCADE ON DELETE CASCADE + organisation UUID NOT NULL, + admin UUID NOT NULL, + CONSTRAINT organisation_admins_pk PRIMARY KEY (organisation, admin), + CONSTRAINT organisation_admins_fk_oid FOREIGN KEY (organisation) + REFERENCES hub.organisations (id) ON UPDATE CASCADE ON DELETE CASCADE, + CONSTRAINT organisation_admins_fk_uid FOREIGN KEY (admin) + REFERENCES hub.accounts (uid) ON UPDATE CASCADE ON DELETE CASCADE ) WITH ( OIDS=FALSE ); -COMMENT ON TABLE "hub"."organisation_admins" IS 'A mapping table to allow multiple users administering an organisation.'; -COMMENT ON COLUMN "hub"."organisation_admins"."organisation" IS 'The globally unique ID of the organisation.'; -COMMENT ON COLUMN "hub"."organisation_admins"."admin" IS 'The unique ID of one of the user accounts allowed to administer the organisation.'; - +COMMENT ON TABLE hub.organisation_admins IS 'A mapping table to allow multiple users administering an organisation.'; +COMMENT ON COLUMN hub.organisation_admins.organisation IS 'The globally unique ID of the organisation.'; +COMMENT ON COLUMN hub.organisation_admins.admin IS 'The unique ID of one of the user accounts allowed to administer the organisation.'; diff -rN -u old-smederee/modules/hub/src/main/scala/de/smederee/hub/DoobieAuthenticationRepository.scala new-smederee/modules/hub/src/main/scala/de/smederee/hub/DoobieAuthenticationRepository.scala --- old-smederee/modules/hub/src/main/scala/de/smederee/hub/DoobieAuthenticationRepository.scala 2025-01-13 01:25:25.761526357 +0000 +++ new-smederee/modules/hub/src/main/scala/de/smederee/hub/DoobieAuthenticationRepository.scala 2025-01-13 01:25:25.765526369 +0000 @@ -43,22 +43,22 @@ private val lockedFilter = fr"""locked_at IS NOT NULL""" private val notLockedFilter = fr"""locked_at IS NULL""" private val selectAccountColumns = - fr"""SELECT uid, name, email, full_name, validated_email, language FROM "hub"."accounts"""" + fr"""SELECT uid, name, email, full_name, validated_email, language FROM hub.accounts""" override def allAccounts(): Stream[F, Account] = { - val query = selectAccountColumns ++ fr"""ORDER BY "name" ASC""" + val query = selectAccountColumns ++ fr"""ORDER BY name ASC""" query.query[Account].stream.transact(tx) } override def createUserSession(session: Session): F[Int] = - sql"""INSERT INTO "hub"."sessions" (id, uid, created_at, updated_at) VALUES (${session.id}, ${session.uid}, ${session.createdAt}, ${session.updatedAt})""".update.run + sql"""INSERT INTO hub.sessions (id, uid, created_at, updated_at) VALUES (${session.id}, ${session.uid}, ${session.createdAt}, ${session.updatedAt})""".update.run .transact(tx) override def deleteAllUserSessions(uid: UserId): F[Int] = - sql"""DELETE FROM "hub"."sessions" WHERE uid = $uid""".update.run.transact(tx) + sql"""DELETE FROM hub.sessions WHERE uid = $uid""".update.run.transact(tx) override def deleteUserSession(id: SessionId): F[Int] = - sql"""DELETE FROM "hub"."sessions" WHERE id = $id""".update.run.transact(tx) + sql"""DELETE FROM hub.sessions WHERE id = $id""".update.run.transact(tx) override def findAccount(uid: UserId): F[Option[Account]] = { val uidFilter = fr"""uid = $uid""" @@ -88,7 +88,7 @@ override def findPasswordHashAndAttempts(uid: UserId): F[Option[(PasswordHash, Int)]] = { val uidFilter = fr"""uid = $uid""" - val query = fr"""SELECT password, failed_attempts FROM "hub"."accounts"""" ++ whereAnd( + val query = fr"""SELECT password, failed_attempts FROM hub.accounts""" ++ whereAnd( notLockedFilter, uidFilter ) ++ fr"""LIMIT 1""" @@ -96,23 +96,23 @@ } override def findUserSession(id: SessionId): F[Option[Session]] = - sql"""SELECT id, uid, created_at, updated_at FROM "hub"."sessions" WHERE id = $id LIMIT 1""" + sql"""SELECT id, uid, created_at, updated_at FROM hub.sessions WHERE id = $id LIMIT 1""" .query[Session] .option .transact(tx) override def incrementFailedAttempts(uid: UserId): F[Int] = - sql"""UPDATE "hub"."accounts" SET failed_attempts = failed_attempts + 1 WHERE uid = $uid""".update.run + sql"""UPDATE hub.accounts SET failed_attempts = failed_attempts + 1 WHERE uid = $uid""".update.run .transact(tx) override def lockAccount(uid: UserId)(token: Option[UnlockToken]): F[Int] = - sql"""UPDATE "hub"."accounts" SET locked_at = NOW(), unlock_token = $token WHERE uid = $uid""".update.run + sql"""UPDATE hub.accounts SET locked_at = NOW(), unlock_token = $token WHERE uid = $uid""".update.run .transact(tx) override def resetFailedAttempts(uid: UserId): F[Int] = - sql"""UPDATE "hub"."accounts" SET failed_attempts = 0 WHERE uid = $uid""".update.run.transact(tx) + sql"""UPDATE hub.accounts SET failed_attempts = 0 WHERE uid = $uid""".update.run.transact(tx) override def unlockAccount(uid: UserId): F[Int] = - sql"""UPDATE "hub"."accounts" SET locked_at = NULL, unlock_token = NULL WHERE uid = $uid""".update.run + sql"""UPDATE hub.accounts SET locked_at = NULL, unlock_token = NULL WHERE uid = $uid""".update.run .transact(tx) } diff -rN -u old-smederee/modules/hub/src/main/scala/de/smederee/hub/DoobieOrganisationRepository.scala new-smederee/modules/hub/src/main/scala/de/smederee/hub/DoobieOrganisationRepository.scala --- old-smederee/modules/hub/src/main/scala/de/smederee/hub/DoobieOrganisationRepository.scala 2025-01-13 01:25:25.761526357 +0000 +++ new-smederee/modules/hub/src/main/scala/de/smederee/hub/DoobieOrganisationRepository.scala 2025-01-13 01:25:25.765526369 +0000 @@ -47,7 +47,7 @@ fr"""SELECT id, name, owner, full_name, description, is_private, website FROM hub.organisations AS organisations""" override def addAdministrator(organisationId: OrganisationId)(user: UserId): F[Int] = - sql"""INSERT INTO "hub"."organisation_admins" ( + sql"""INSERT INTO hub.organisation_admins ( organisation, admin ) VALUES ( @@ -63,7 +63,7 @@ } override def create(org: Organisation): F[Int] = - sql"""INSERT INTO "hub"."organisations" ( + sql"""INSERT INTO hub.organisations ( id, name, owner, @@ -86,7 +86,7 @@ )""".update.run.transact(tx) override def delete(organisationId: OrganisationId): F[Int] = - sql"""DELETE FROM "hub"."organisations" WHERE id = $organisationId""".update.run.transact(tx) + sql"""DELETE FROM hub.organisations WHERE id = $organisationId""".update.run.transact(tx) override def find(organisationId: OrganisationId): F[Option[Organisation]] = { val idFilter = fr"""id = $organisationId""" @@ -133,11 +133,11 @@ } override def removeAdministrator(organisationId: OrganisationId)(user: UserId): F[Int] = - sql"""DELETE FROM "hub"."organisation_admins" WHERE organisation = $organisationId AND admin = $user""".update.run + sql"""DELETE FROM hub.organisation_admins WHERE organisation = $organisationId AND admin = $user""".update.run .transact(tx) override def update(org: Organisation): F[Int] = - sql"""UPDATE "hub"."organisations" SET + sql"""UPDATE hub.organisations SET name = ${org.name}, owner = ${org.owner}, full_name = ${org.fullName}, diff -rN -u old-smederee/modules/hub/src/main/scala/de/smederee/hub/DoobieResetPasswordRepository.scala new-smederee/modules/hub/src/main/scala/de/smederee/hub/DoobieResetPasswordRepository.scala --- old-smederee/modules/hub/src/main/scala/de/smederee/hub/DoobieResetPasswordRepository.scala 2025-01-13 01:25:25.761526357 +0000 +++ new-smederee/modules/hub/src/main/scala/de/smederee/hub/DoobieResetPasswordRepository.scala 2025-01-13 01:25:25.765526369 +0000 @@ -45,7 +45,7 @@ private val resetTokenExpirySetFilter = fr"""reset_expiry IS NOT NULL""" private val resetTokenNotExpiredFilter = fr"""reset_expiry > NOW()""" private val selectAccountColumns = - fr"""SELECT uid, name, email, full_name, validated_email, language FROM "hub"."accounts"""" + fr"""SELECT uid, name, email, full_name, validated_email, language FROM hub.accounts""" override def findByNameAndResetPasswordToken(name: Username, token: ResetToken): F[Option[Account]] = { val nameFilter = fr"""name = $name""" @@ -71,16 +71,16 @@ } override def removeResetPasswordExpirationDate(uid: UserId): F[Int] = - sql"""UPDATE "hub"."accounts" SET reset_expiry = NULL WHERE uid = $uid""".update.run.transact(tx) + sql"""UPDATE hub.accounts SET reset_expiry = NULL WHERE uid = $uid""".update.run.transact(tx) override def removeResetPasswordToken(uid: UserId): F[Int] = - sql"""UPDATE "hub"."accounts" SET reset_expiry = NULL, reset_token = NULL WHERE uid = $uid""".update.run + sql"""UPDATE hub.accounts SET reset_expiry = NULL, reset_token = NULL WHERE uid = $uid""".update.run .transact(tx) override def setPassword(uid: UserId)(hash: PasswordHash): F[Int] = - sql"""UPDATE "hub"."accounts" SET password = $hash WHERE uid = $uid""".update.run.transact(tx) + sql"""UPDATE hub.accounts SET password = $hash WHERE uid = $uid""".update.run.transact(tx) override def setResetPasswordToken(uid: UserId)(token: ResetToken, tokenExpiration: OffsetDateTime): F[Int] = - sql"""UPDATE "hub"."accounts" SET reset_expiry = $tokenExpiration, reset_token = $token WHERE uid = $uid""".update.run + sql"""UPDATE hub.accounts SET reset_expiry = $tokenExpiration, reset_token = $token WHERE uid = $uid""".update.run .transact(tx) } diff -rN -u old-smederee/modules/hub/src/main/scala/de/smederee/hub/DoobieSignupRepository.scala new-smederee/modules/hub/src/main/scala/de/smederee/hub/DoobieSignupRepository.scala --- old-smederee/modules/hub/src/main/scala/de/smederee/hub/DoobieSignupRepository.scala 2025-01-13 01:25:25.761526357 +0000 +++ new-smederee/modules/hub/src/main/scala/de/smederee/hub/DoobieSignupRepository.scala 2025-01-13 01:25:25.765526369 +0000 @@ -27,20 +27,18 @@ import doobie.postgres.implicits.* final class DoobieSignupRepository[F[_]: Sync](tx: Transactor[F]) extends SignupRepository[F] { - given Meta[EmailAddress] = Meta[String].timap(EmailAddress.apply)(_.toString) given Meta[PasswordHash] = Meta[String].timap(PasswordHash.apply)(_.toString) given Meta[UserId] = Meta[UUID].timap(UserId.apply)(_.toUUID) given Meta[Username] = Meta[String].timap(Username.apply)(_.toString) override def createAccount(account: Account, hash: PasswordHash): F[Int] = - sql"""INSERT INTO "hub"."accounts" (uid, name, email, password, created_at, updated_at, validated_email) VALUES(${account.uid}, ${account.name}, ${account.email}, $hash, NOW(), NOW(), ${account.validatedEmail})""".update.run + sql"""INSERT INTO hub.accounts (uid, name, email, password, created_at, updated_at, validated_email) VALUES(${account.uid}, ${account.name}, ${account.email}, $hash, NOW(), NOW(), ${account.validatedEmail})""".update.run .transact(tx) override def findEmail(address: EmailAddress): F[Option[EmailAddress]] = - sql"""SELECT email FROM "hub"."accounts" WHERE email = $address""".query[EmailAddress].option.transact(tx) + sql"""SELECT email FROM hub.accounts WHERE email = $address""".query[EmailAddress].option.transact(tx) override def findUsername(name: Username): F[Option[Username]] = - sql"""SELECT name FROM "hub"."accounts" WHERE name = $name""".query[Username].option.transact(tx) - + sql"""SELECT name FROM hub.accounts WHERE name = $name""".query[Username].option.transact(tx) } diff -rN -u old-smederee/modules/hub/src/main/scala/de/smederee/hub/DoobieVcsMetadataRepository.scala new-smederee/modules/hub/src/main/scala/de/smederee/hub/DoobieVcsMetadataRepository.scala --- old-smederee/modules/hub/src/main/scala/de/smederee/hub/DoobieVcsMetadataRepository.scala 2025-01-13 01:25:25.761526357 +0000 +++ new-smederee/modules/hub/src/main/scala/de/smederee/hub/DoobieVcsMetadataRepository.scala 2025-01-13 01:25:25.765526369 +0000 @@ -33,7 +33,6 @@ import org.http4s.Uri final class DoobieVcsMetadataRepository[F[_]: Sync](tx: Transactor[F]) extends VcsMetadataRepository[F] { - given Meta[EmailAddress] = Meta[String].timap(EmailAddress.apply)(_.toString) given Meta[Uri] = Meta[String].timap(Uri.unsafeFromString)(_.toString) given Meta[UserId] = Meta[UUID].timap(UserId.apply)(_.toUUID) @@ -45,21 +44,21 @@ private val selectRepositoryColumns = fr"""SELECT - "repos".name AS name, - "accounts".uid AS owner_id, - "accounts".name AS owner_name, - "accounts".email AS owner_email, - "repos".is_private AS is_private, - "repos".description AS description, - "repos".tickets_enabled AS tickets_enabled, - "repos".vcs_type AS vcs_type, - "repos".website AS website - FROM "hub"."repositories" AS "repos" - JOIN "hub"."accounts" AS "accounts" - ON "repos".owner = "accounts".uid""" + repos.name AS name, + accounts.uid AS owner_id, + accounts.name AS owner_name, + accounts.email AS owner_email, + repos.is_private AS is_private, + repos.description AS description, + repos.tickets_enabled AS tickets_enabled, + repos.vcs_type AS vcs_type, + repos.website AS website + FROM hub.repositories AS repos + JOIN hub.accounts AS accounts + ON repos.owner = accounts.uid""" override def createFork(source: VcsRepositoryId, target: VcsRepositoryId): F[Int] = - sql"""INSERT INTO "hub"."forks" ( + sql"""INSERT INTO hub.forks ( original_repo, forked_repo ) VALUES ( @@ -68,7 +67,7 @@ )""".update.run.transact(tx) override def createVcsRepository(repository: VcsRepository): F[Int] = - sql"""INSERT INTO "hub"."repositories" ( + sql"""INSERT INTO hub.repositories ( name, owner, is_private, @@ -91,7 +90,7 @@ )""".update.run.transact(tx) override def deleteVcsRepository(repository: VcsRepository): F[Int] = - sql"""DELETE FROM "hub"."repositories" + sql"""DELETE FROM hub.repositories WHERE owner = ${repository.owner.uid} AND name = ${repository.name}""".update.run.transact(tx) @@ -99,8 +98,8 @@ owner: VcsRepositoryOwner, name: VcsRepositoryName ): F[Option[VcsRepository]] = { - val nameFilter = fr""""repos".name = $name""" - val ownerFilter = fr""""repos".owner = ${owner.uid}""" + val nameFilter = fr"""repos.name = $name""" + val ownerFilter = fr"""repos.owner = ${owner.uid}""" val query = selectRepositoryColumns ++ whereAnd(ownerFilter, nameFilter) ++ fr"""LIMIT 1""" query.query[VcsRepository].option.transact(tx) } @@ -109,26 +108,26 @@ originalRepositoryId: VcsRepositoryId ): Stream[F, (Username, VcsRepositoryName)] = { val query = sql"""SELECT - "accounts"."name" AS "owner_name", - "repos"."name" AS "repository_name" - FROM "hub"."forks" AS "forks" - JOIN "hub"."repositories" AS "repos" - ON "forks"."forked_repo" = "repos"."id" - JOIN "hub"."accounts" AS "accounts" - ON "repos"."owner" = "accounts"."uid" - WHERE "forks"."original_repo" = $originalRepositoryId""" + accounts.name AS owner_name, + repos.name AS repository_name + FROM hub.forks AS forks + JOIN hub.repositories AS repos + ON forks.forked_repo = repos.id + JOIN hub.accounts AS accounts + ON repos.owner = accounts.uid + WHERE forks.original_repo = $originalRepositoryId""" query.query[(Username, VcsRepositoryName)].stream.transact(tx) } override def findVcsRepositoryId(owner: VcsRepositoryOwner, name: VcsRepositoryName): F[Option[VcsRepositoryId]] = { val nameFilter = fr"""name = $name""" val ownerFilter = fr"""owner = ${owner.uid}""" - val query = fr"""SELECT id FROM "hub"."repositories"""" ++ whereAnd(ownerFilter, nameFilter) ++ fr"""LIMIT 1""" + val query = fr"""SELECT id FROM hub.repositories""" ++ whereAnd(ownerFilter, nameFilter) ++ fr"""LIMIT 1""" query.query[VcsRepositoryId].option.transact(tx) } override def findVcsRepositoryOwner(name: Username): F[Option[VcsRepositoryOwner]] = - sql"""SELECT uid, name, email FROM "hub"."accounts" WHERE name = $name LIMIT 1""" + sql"""SELECT uid, name, email FROM hub.accounts WHERE name = $name LIMIT 1""" .query[VcsRepositoryOwner] .option .transact(tx) @@ -138,7 +137,7 @@ name: VcsRepositoryName ): F[Option[VcsRepository]] = { val query = - selectRepositoryColumns ++ fr"""WHERE "repos".id = (SELECT original_repo FROM "hub"."forks" WHERE forked_repo = (SELECT id FROM "hub"."repositories" WHERE name = $name AND owner = ${owner.uid}))""" + selectRepositoryColumns ++ fr"""WHERE repos.id = (SELECT original_repo FROM hub.forks WHERE forked_repo = (SELECT id FROM hub.repositories WHERE name = $name AND owner = ${owner.uid}))""" query.query[VcsRepository].option.transact(tx) } @@ -159,21 +158,21 @@ override def listRepositories( requester: Option[Account] )(owner: VcsRepositoryOwner): Stream[F, VcsRepository] = { - val ownerFilter = fr""""repos".owner = ${owner.uid}""" + val ownerFilter = fr"""repos.owner = ${owner.uid}""" val whereClause = requester match { - case None => whereAnd(ownerFilter, fr""""repos".is_private = FALSE""") // Guest only see public repos. + case None => whereAnd(ownerFilter, fr"""repos.is_private = FALSE""") // Guest only see public repos. case Some(account) => if (account.uid === owner.uid) whereAnd(ownerFilter) // The user asks for their own repositories. else - whereAnd(ownerFilter, fr""""repos".is_private = FALSE""") // TODO: More logic later (groups, perms). + whereAnd(ownerFilter, fr"""repos.is_private = FALSE""") // TODO: More logic later (groups, perms). } val query = selectRepositoryColumns ++ whereClause ++ fr"""ORDER BY name ASC""" query.query[VcsRepository].stream.transact(tx) } override def updateVcsRepository(repository: VcsRepository): F[Int] = - sql"""UPDATE "hub"."repositories" + sql"""UPDATE hub.repositories SET is_private = ${repository.isPrivate}, description = ${repository.description}, tickets_enabled = ${repository.ticketsEnabled}, @@ -181,5 +180,4 @@ updated_at = NOW() WHERE owner = ${repository.owner.uid} AND name = ${repository.name}""".update.run.transact(tx) - } diff -rN -u old-smederee/modules/hub/src/test/scala/de/smederee/hub/BaseSpec.scala new-smederee/modules/hub/src/test/scala/de/smederee/hub/BaseSpec.scala --- old-smederee/modules/hub/src/test/scala/de/smederee/hub/BaseSpec.scala 2025-01-13 01:25:25.761526357 +0000 +++ new-smederee/modules/hub/src/test/scala/de/smederee/hub/BaseSpec.scala 2025-01-13 01:25:25.765526369 +0000 @@ -166,19 +166,19 @@ (unlockToken, validationToken) match { case (None, None) => con.prepareStatement( - """INSERT INTO "hub"."accounts" (uid, name, email, full_name, password, failed_attempts, created_at, updated_at, validated_email) VALUES(?, ?, ?, ?, ?, ?, NOW(), NOW(), ?)""" + """INSERT INTO hub.accounts (uid, name, email, full_name, password, failed_attempts, created_at, updated_at, validated_email) VALUES(?, ?, ?, ?, ?, ?, NOW(), NOW(), ?)""" ) case (Some(_), None) => con.prepareStatement( - """INSERT INTO "hub"."accounts" (uid, name, email, full_name, password, failed_attempts, validated_email, locked_at, unlock_token, created_at, updated_at) VALUES(?, ?, ?, ?, ?, ?, ?, NOW(), ?, NOW(), NOW())""" + """INSERT INTO hub.accounts (uid, name, email, full_name, password, failed_attempts, validated_email, locked_at, unlock_token, created_at, updated_at) VALUES(?, ?, ?, ?, ?, ?, ?, NOW(), ?, NOW(), NOW())""" ) case (None, Some(_)) => con.prepareStatement( - """INSERT INTO "hub"."accounts" (uid, name, email, full_name, password, failed_attempts, validated_email, locked_at, validation_token, created_at, updated_at) VALUES(?, ?, ?, ?, ?, ?, ?, NOW(), ?, NOW(), NOW())""" + """INSERT INTO hub.accounts (uid, name, email, full_name, password, failed_attempts, validated_email, locked_at, validation_token, created_at, updated_at) VALUES(?, ?, ?, ?, ?, ?, ?, NOW(), ?, NOW(), NOW())""" ) case (Some(_), Some(_)) => con.prepareStatement( - """INSERT INTO "hub"."accounts" (uid, name, email, full_name, password, failed_attempts, validated_email, locked_at, unlock_token, validation_token, created_at, updated_at) VALUES(?, ?, ?, ?, ?, ?, ?, NOW(), ?, NOW(), NOW())""" + """INSERT INTO hub.accounts (uid, name, email, full_name, password, failed_attempts, validated_email, locked_at, unlock_token, validation_token, created_at, updated_at) VALUES(?, ?, ?, ?, ?, ?, ?, NOW(), ?, NOW(), NOW())""" ) } } @@ -226,7 +226,7 @@ for { statement <- IO.delay( con.prepareStatement( - """INSERT INTO "hub"."sessions" (id, uid, created_at, updated_at) VALUES(?, ?, ?, ?)""" + """INSERT INTO hub.sessions (id, uid, created_at, updated_at) VALUES(?, ?, ?, ?)""" ) ) _ <- IO.delay(statement.setString(1, session.id.toString)) @@ -256,7 +256,7 @@ for { statement <- IO.delay( con.prepareStatement( - """SELECT uid, name, email, full_name, password, created_at, updated_at, validated_email, language FROM "hub"."accounts" WHERE uid = ? LIMIT 1""" + """SELECT uid, name, email, full_name, password, created_at, updated_at, validated_email, language FROM hub.accounts WHERE uid = ? LIMIT 1""" ) ) _ <- IO.delay(statement.setObject(1, uid)) @@ -300,7 +300,7 @@ connectToDb(configuration).use { con => for { statement <- IO.delay( - con.prepareStatement("""SELECT password FROM "hub"."accounts" WHERE uid = ? LIMIT 1""") + con.prepareStatement("""SELECT password FROM hub.accounts WHERE uid = ? LIMIT 1""") ) _ <- IO.delay(statement.setObject(1, uid)) result <- IO.delay(statement.executeQuery) @@ -329,7 +329,7 @@ for { statement <- IO.delay( con.prepareStatement( - """SELECT reset_expiry, reset_token FROM "hub"."accounts" WHERE uid = ? LIMIT 1""" + """SELECT reset_expiry, reset_token FROM hub.accounts WHERE uid = ? LIMIT 1""" ) ) _ <- IO.delay(statement.setObject(1, uid)) @@ -368,7 +368,7 @@ for { statement <- IO.delay( con.prepareStatement( - """SELECT validated_email, validation_token FROM "hub"."accounts" WHERE uid = ? LIMIT 1""" + """SELECT validated_email, validation_token FROM hub.accounts WHERE uid = ? LIMIT 1""" ) ) _ <- IO.delay(statement.setObject(1, uid)) @@ -405,7 +405,7 @@ for { statement <- IO.delay( con.prepareStatement( - """SELECT id FROM "hub"."repositories" WHERE owner = ? AND name = ? LIMIT 1""" + """SELECT id FROM hub.repositories WHERE owner = ? AND name = ? LIMIT 1""" ) ) _ <- IO.delay(statement.setObject(1, owner)) diff -rN -u old-smederee/modules/tickets/src/main/resources/db/migration/tickets/V1__create_schema.sql new-smederee/modules/tickets/src/main/resources/db/migration/tickets/V1__create_schema.sql --- old-smederee/modules/tickets/src/main/resources/db/migration/tickets/V1__create_schema.sql 2025-01-13 01:25:25.761526357 +0000 +++ new-smederee/modules/tickets/src/main/resources/db/migration/tickets/V1__create_schema.sql 2025-01-13 01:25:25.765526369 +0000 @@ -1,3 +1,3 @@ -CREATE SCHEMA IF NOT EXISTS "tickets"; +CREATE SCHEMA IF NOT EXISTS tickets; -COMMENT ON SCHEMA "tickets" IS 'Data related to ticket tracking.'; +COMMENT ON SCHEMA tickets IS 'Data related to ticket tracking.'; diff -rN -u old-smederee/modules/tickets/src/main/resources/db/migration/tickets/V2__base_tables.sql new-smederee/modules/tickets/src/main/resources/db/migration/tickets/V2__base_tables.sql --- old-smederee/modules/tickets/src/main/resources/db/migration/tickets/V2__base_tables.sql 2025-01-13 01:25:25.761526357 +0000 +++ new-smederee/modules/tickets/src/main/resources/db/migration/tickets/V2__base_tables.sql 2025-01-13 01:25:25.765526369 +0000 @@ -1,200 +1,200 @@ -CREATE TABLE "tickets"."users" +CREATE TABLE tickets.users ( - "uid" UUID NOT NULL, - "name" CHARACTER VARYING(32) NOT NULL, - "email" CHARACTER VARYING(128) NOT NULL, - "created_at" TIMESTAMP WITH TIME ZONE NOT NULL, - "updated_at" TIMESTAMP WITH TIME ZONE NOT NULL, - CONSTRAINT "users_pk" PRIMARY KEY ("uid"), - CONSTRAINT "users_unique_name" UNIQUE ("name"), - CONSTRAINT "users_unique_email" UNIQUE ("email") + uid UUID NOT NULL, + name CHARACTER VARYING(32) NOT NULL, + email CHARACTER VARYING(128) NOT NULL, + created_at TIMESTAMP WITH TIME ZONE NOT NULL, + updated_at TIMESTAMP WITH TIME ZONE NOT NULL, + CONSTRAINT users_pk PRIMARY KEY (uid), + CONSTRAINT users_unique_name UNIQUE (name), + CONSTRAINT users_unique_email UNIQUE (email) ) WITH ( OIDS=FALSE ); -COMMENT ON TABLE "tickets"."users" IS 'All users for the ticket system live within this table.'; -COMMENT ON COLUMN "tickets"."users"."uid" IS 'A globally unique ID for the related user account. It must match the user ID from the hub account.'; -COMMENT ON COLUMN "tickets"."users"."name" IS 'A username between 2 and 32 characters which must be globally unique, contain only lowercase alphanumeric characters and start with a character.'; -COMMENT ON COLUMN "tickets"."users"."email" IS 'A globally unique email address associated with the account.'; -COMMENT ON COLUMN "tickets"."users"."created_at" IS 'The timestamp of when the account was created.'; -COMMENT ON COLUMN "tickets"."users"."updated_at" IS 'A timestamp when the account was last changed.'; +COMMENT ON TABLE tickets.users IS 'All users for the ticket system live within this table.'; +COMMENT ON COLUMN tickets.users.uid IS 'A globally unique ID for the related user account. It must match the user ID from the hub account.'; +COMMENT ON COLUMN tickets.users.name IS 'A username between 2 and 32 characters which must be globally unique, contain only lowercase alphanumeric characters and start with a character.'; +COMMENT ON COLUMN tickets.users.email IS 'A globally unique email address associated with the account.'; +COMMENT ON COLUMN tickets.users.created_at IS 'The timestamp of when the account was created.'; +COMMENT ON COLUMN tickets.users.updated_at IS 'A timestamp when the account was last changed.'; -CREATE TABLE "tickets"."sessions" +CREATE TABLE tickets.sessions ( - "id" VARCHAR(32) NOT NULL, - "uid" UUID NOT NULL, - "created_at" TIMESTAMP WITH TIME ZONE NOT NULL, - "updated_at" TIMESTAMP WITH TIME ZONE NOT NULL, - CONSTRAINT "sessions_pk" PRIMARY KEY ("id"), - CONSTRAINT "sessions_fk_uid" FOREIGN KEY ("uid") - REFERENCES "tickets"."users" ("uid") ON UPDATE CASCADE ON DELETE CASCADE + id VARCHAR(32) NOT NULL, + uid UUID NOT NULL, + created_at TIMESTAMP WITH TIME ZONE NOT NULL, + updated_at TIMESTAMP WITH TIME ZONE NOT NULL, + CONSTRAINT sessions_pk PRIMARY KEY (id), + CONSTRAINT sessions_fk_uid FOREIGN KEY (uid) + REFERENCES tickets.users (uid) ON UPDATE CASCADE ON DELETE CASCADE ) WITH ( OIDS=FALSE ); -COMMENT ON TABLE "tickets"."sessions" IS 'Keeps the sessions of users.'; -COMMENT ON COLUMN "tickets"."sessions"."id" IS 'A globally unique session ID.'; -COMMENT ON COLUMN "tickets"."sessions"."uid" IS 'The unique ID of the user account to whom the session belongs.'; -COMMENT ON COLUMN "tickets"."sessions"."created_at" IS 'The timestamp of when the session was created.'; -COMMENT ON COLUMN "tickets"."sessions"."updated_at" IS 'The session ID should be re-generated in regular intervals resulting in a copy of the old session entry with a new ID and the corresponding timestamp in this column.'; +COMMENT ON TABLE tickets.sessions IS 'Keeps the sessions of users.'; +COMMENT ON COLUMN tickets.sessions.id IS 'A globally unique session ID.'; +COMMENT ON COLUMN tickets.sessions.uid IS 'The unique ID of the user account to whom the session belongs.'; +COMMENT ON COLUMN tickets.sessions.created_at IS 'The timestamp of when the session was created.'; +COMMENT ON COLUMN tickets.sessions.updated_at IS 'The session ID should be re-generated in regular intervals resulting in a copy of the old session entry with a new ID and the corresponding timestamp in this column.'; -CREATE TABLE "tickets"."projects" +CREATE TABLE tickets.projects ( - "id" BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, - "name" CHARACTER VARYING(64) NOT NULL, - "owner" UUID NOT NULL, - "is_private" BOOLEAN NOT NULL DEFAULT FALSE, - "description" CHARACTER VARYING(254), - "created_at" TIMESTAMP WITH TIME ZONE NOT NULL, - "updated_at" TIMESTAMP WITH TIME ZONE NOT NULL, - "next_ticket_number" INTEGER NOT NULL DEFAULT 1, - CONSTRAINT "projects_unique_owner_name" UNIQUE ("owner", "name"), - CONSTRAINT "projects_fk_uid" FOREIGN KEY ("owner") - REFERENCES "tickets"."users" ("uid") ON UPDATE CASCADE ON DELETE CASCADE + id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, + name CHARACTER VARYING(64) NOT NULL, + owner UUID NOT NULL, + is_private BOOLEAN NOT NULL DEFAULT FALSE, + description CHARACTER VARYING(254), + created_at TIMESTAMP WITH TIME ZONE NOT NULL, + updated_at TIMESTAMP WITH TIME ZONE NOT NULL, + next_ticket_number INTEGER NOT NULL DEFAULT 1, + CONSTRAINT projects_unique_owner_name UNIQUE (owner, name), + CONSTRAINT projects_fk_uid FOREIGN KEY (owner) + REFERENCES tickets.users (uid) ON UPDATE CASCADE ON DELETE CASCADE ) WITH ( OIDS=FALSE ); -COMMENT ON TABLE "tickets"."projects" IS 'All projects which are basically mirrored repositories from the hub are stored within this table.'; -COMMENT ON COLUMN "tickets"."projects"."id" IS 'An auto generated primary key.'; -COMMENT ON COLUMN "tickets"."projects"."name" IS 'The name of the project. A project name must start with a letter or number and must contain only alphanumeric ASCII characters as well as minus or underscore signs. It must be between 2 and 64 characters long.'; -COMMENT ON COLUMN "tickets"."projects"."owner" IS 'The unique ID of the user account that owns the project.'; -COMMENT ON COLUMN "tickets"."projects"."is_private" IS 'A flag indicating if this project is private i.e. only visible / accessible for users with appropriate permissions.'; -COMMENT ON COLUMN "tickets"."projects"."description" IS 'An optional short text description of the project.'; -COMMENT ON COLUMN "tickets"."projects"."created_at" IS 'The timestamp of when the project was created.'; -COMMENT ON COLUMN "tickets"."projects"."updated_at" IS 'A timestamp when the project was last changed.'; -COMMENT ON COLUMN "tickets"."projects"."next_ticket_number" IS 'Tickets are numbered ascending per project and this field holds the next logical ticket number to be used and must be incremented upon creation of a new ticket.'; +COMMENT ON TABLE tickets.projects IS 'All projects which are basically mirrored repositories from the hub are stored within this table.'; +COMMENT ON COLUMN tickets.projects.id IS 'An auto generated primary key.'; +COMMENT ON COLUMN tickets.projects.name IS 'The name of the project. A project name must start with a letter or number and must contain only alphanumeric ASCII characters as well as minus or underscore signs. It must be between 2 and 64 characters long.'; +COMMENT ON COLUMN tickets.projects.owner IS 'The unique ID of the user account that owns the project.'; +COMMENT ON COLUMN tickets.projects.is_private IS 'A flag indicating if this project is private i.e. only visible / accessible for users with appropriate permissions.'; +COMMENT ON COLUMN tickets.projects.description IS 'An optional short text description of the project.'; +COMMENT ON COLUMN tickets.projects.created_at IS 'The timestamp of when the project was created.'; +COMMENT ON COLUMN tickets.projects.updated_at IS 'A timestamp when the project was last changed.'; +COMMENT ON COLUMN tickets.projects.next_ticket_number IS 'Tickets are numbered ascending per project and this field holds the next logical ticket number to be used and must be incremented upon creation of a new ticket.'; -CREATE TABLE "tickets"."labels" +CREATE TABLE tickets.labels ( - "id" BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, - "project" BIGINT NOT NULL, - "name" CHARACTER VARYING(40) NOT NULL, - "description" CHARACTER VARYING(254) DEFAULT NULL, - "colour" CHARACTER VARYING(7) NOT NULL, - CONSTRAINT "labels_unique_project_label" UNIQUE ("project", "name"), - CONSTRAINT "labels_fk_project" FOREIGN KEY ("project") - REFERENCES "tickets"."projects" ("id") ON UPDATE CASCADE ON DELETE CASCADE + id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, + project BIGINT NOT NULL, + name CHARACTER VARYING(40) NOT NULL, + description CHARACTER VARYING(254) DEFAULT NULL, + colour CHARACTER VARYING(7) NOT NULL, + CONSTRAINT labels_unique_project_label UNIQUE (project, name), + CONSTRAINT labels_fk_project FOREIGN KEY (project) + REFERENCES tickets.projects (id) ON UPDATE CASCADE ON DELETE CASCADE ) WITH ( OIDS=FALSE ); -COMMENT ON TABLE "tickets"."labels" IS 'Labels used to add information to tickets.'; -COMMENT ON COLUMN "tickets"."labels"."id" IS 'An auto generated primary key.'; -COMMENT ON COLUMN "tickets"."labels"."project" IS 'The project to which this label belongs.'; -COMMENT ON COLUMN "tickets"."labels"."name" IS 'A short descriptive name for the label which is supposed to be unique in a project context.'; -COMMENT ON COLUMN "tickets"."labels"."description" IS 'An optional description if needed.'; -COMMENT ON COLUMN "tickets"."labels"."colour" IS 'A hexadecimal HTML colour code which can be used to mark the label on a rendered website.'; +COMMENT ON TABLE tickets.labels IS 'Labels used to add information to tickets.'; +COMMENT ON COLUMN tickets.labels.id IS 'An auto generated primary key.'; +COMMENT ON COLUMN tickets.labels.project IS 'The project to which this label belongs.'; +COMMENT ON COLUMN tickets.labels.name IS 'A short descriptive name for the label which is supposed to be unique in a project context.'; +COMMENT ON COLUMN tickets.labels.description IS 'An optional description if needed.'; +COMMENT ON COLUMN tickets.labels.colour IS 'A hexadecimal HTML colour code which can be used to mark the label on a rendered website.'; -CREATE TABLE "tickets"."milestones" +CREATE TABLE tickets.milestones ( - "id" BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, - "project" BIGINT NOT NULL, - "title" CHARACTER VARYING(64) NOT NULL, - "due_date" DATE DEFAULT NULL, - "description" TEXT DEFAULT NULL, - CONSTRAINT "milestones_unique_project_title" UNIQUE ("project", "title"), - CONSTRAINT "milestones_fk_project" FOREIGN KEY ("project") - REFERENCES "tickets"."projects" ("id") ON UPDATE CASCADE ON DELETE CASCADE + id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, + project BIGINT NOT NULL, + title CHARACTER VARYING(64) NOT NULL, + due_date DATE DEFAULT NULL, + description TEXT DEFAULT NULL, + CONSTRAINT milestones_unique_project_title UNIQUE (project, title), + CONSTRAINT milestones_fk_project FOREIGN KEY (project) + REFERENCES tickets.projects (id) ON UPDATE CASCADE ON DELETE CASCADE ) WITH ( OIDS=FALSE ); -COMMENT ON TABLE "tickets"."milestones" IS 'Milestones used to organise tickets'; -COMMENT ON COLUMN "tickets"."milestones"."project" IS 'The project to which this milestone belongs.'; -COMMENT ON COLUMN "tickets"."milestones"."title" IS 'A title for the milestone, usually a version number, a word or a short phrase that is supposed to be unique within a project context.'; -COMMENT ON COLUMN "tickets"."milestones"."due_date" IS 'An optional date on which the milestone is supposed to be reached.'; -COMMENT ON COLUMN "tickets"."milestones"."description" IS 'An optional longer description of the milestone.'; +COMMENT ON TABLE tickets.milestones IS 'Milestones used to organise tickets'; +COMMENT ON COLUMN tickets.milestones.project IS 'The project to which this milestone belongs.'; +COMMENT ON COLUMN tickets.milestones.title IS 'A title for the milestone, usually a version number, a word or a short phrase that is supposed to be unique within a project context.'; +COMMENT ON COLUMN tickets.milestones.due_date IS 'An optional date on which the milestone is supposed to be reached.'; +COMMENT ON COLUMN tickets.milestones.description IS 'An optional longer description of the milestone.'; -CREATE TABLE "tickets"."tickets" +CREATE TABLE tickets.tickets ( - "id" BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, - "project" BIGINT NOT NULL, - "number" INT NOT NULL, - "title" CHARACTER VARYING(72) NOT NULL, - "content" TEXT DEFAULT NULL, - "status" CHARACTER VARYING(16) NOT NULL, - "submitter" UUID DEFAULT NULL, - "created_at" TIMESTAMP WITH TIME ZONE NOT NULL, - "updated_at" TIMESTAMP WITH TIME ZONE NOT NULL, - CONSTRAINT "tickets_unique_project_ticket" UNIQUE ("project", "number"), - CONSTRAINT "tickets_fk_project" FOREIGN KEY ("project") - REFERENCES "tickets"."projects" ("id") ON UPDATE CASCADE ON DELETE CASCADE, - CONSTRAINT "tickets_fk_submitter" FOREIGN KEY ("submitter") - REFERENCES "tickets"."users" ("uid") ON UPDATE CASCADE ON DELETE SET NULL + id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, + project BIGINT NOT NULL, + number INT NOT NULL, + title CHARACTER VARYING(72) NOT NULL, + content TEXT DEFAULT NULL, + status CHARACTER VARYING(16) NOT NULL, + submitter UUID DEFAULT NULL, + created_at TIMESTAMP WITH TIME ZONE NOT NULL, + updated_at TIMESTAMP WITH TIME ZONE NOT NULL, + CONSTRAINT tickets_unique_project_ticket UNIQUE (project, number), + CONSTRAINT tickets_fk_project FOREIGN KEY (project) + REFERENCES tickets.projects (id) ON UPDATE CASCADE ON DELETE CASCADE, + CONSTRAINT tickets_fk_submitter FOREIGN KEY (submitter) + REFERENCES tickets.users (uid) ON UPDATE CASCADE ON DELETE SET NULL ) WITH ( OIDS=FALSE ); -CREATE INDEX "tickets_status" ON "tickets"."tickets" ("status"); +CREATE INDEX tickets_status ON tickets.tickets (status); -COMMENT ON TABLE "tickets"."tickets" IS 'Information about tickets for projects.'; -COMMENT ON COLUMN "tickets"."tickets"."id" IS 'An auto generated primary key.'; -COMMENT ON COLUMN "tickets"."tickets"."project" IS 'The unique ID of the project which is associated with the ticket.'; -COMMENT ON COLUMN "tickets"."tickets"."number" IS 'The number of the ticket which must be unique within the scope of the project.'; -COMMENT ON COLUMN "tickets"."tickets"."title" IS 'A concise and short description of the ticket which should not exceed 72 characters.'; -COMMENT ON COLUMN "tickets"."tickets"."content" IS 'An optional field to describe the ticket in great detail if needed.'; -COMMENT ON COLUMN "tickets"."tickets"."status" IS 'The current status of the ticket describing its life cycle.'; -COMMENT ON COLUMN "tickets"."tickets"."submitter" IS 'The person who submitted (created) this ticket which is optional because of possible account deletion or other reasons.'; -COMMENT ON COLUMN "tickets"."tickets"."created_at" IS 'The timestamp when the ticket was created / submitted.'; -COMMENT ON COLUMN "tickets"."tickets"."updated_at" IS 'The timestamp when the ticket was last updated. Upon creation the update time equals the creation time.'; +COMMENT ON TABLE tickets.tickets IS 'Information about tickets for projects.'; +COMMENT ON COLUMN tickets.tickets.id IS 'An auto generated primary key.'; +COMMENT ON COLUMN tickets.tickets.project IS 'The unique ID of the project which is associated with the ticket.'; +COMMENT ON COLUMN tickets.tickets.number IS 'The number of the ticket which must be unique within the scope of the project.'; +COMMENT ON COLUMN tickets.tickets.title IS 'A concise and short description of the ticket which should not exceed 72 characters.'; +COMMENT ON COLUMN tickets.tickets.content IS 'An optional field to describe the ticket in great detail if needed.'; +COMMENT ON COLUMN tickets.tickets.status IS 'The current status of the ticket describing its life cycle.'; +COMMENT ON COLUMN tickets.tickets.submitter IS 'The person who submitted (created) this ticket which is optional because of possible account deletion or other reasons.'; +COMMENT ON COLUMN tickets.tickets.created_at IS 'The timestamp when the ticket was created / submitted.'; +COMMENT ON COLUMN tickets.tickets.updated_at IS 'The timestamp when the ticket was last updated. Upon creation the update time equals the creation time.'; -CREATE TABLE "tickets"."milestone_tickets" +CREATE TABLE tickets.milestone_tickets ( - "milestone" BIGINT NOT NULL, - "ticket" BIGINT NOT NULL, - CONSTRAINT "milestone_tickets_pk" PRIMARY KEY ("milestone", "ticket"), - CONSTRAINT "milestone_tickets_fk_milestone" FOREIGN KEY ("milestone") - REFERENCES "tickets"."milestones" ("id") ON UPDATE CASCADE ON DELETE CASCADE, - CONSTRAINT "milestone_tickets_fk_ticket" FOREIGN KEY ("ticket") - REFERENCES "tickets"."tickets" ("id") ON UPDATE CASCADE ON DELETE CASCADE + milestone BIGINT NOT NULL, + ticket BIGINT NOT NULL, + CONSTRAINT milestone_tickets_pk PRIMARY KEY (milestone, ticket), + CONSTRAINT milestone_tickets_fk_milestone FOREIGN KEY (milestone) + REFERENCES tickets.milestones (id) ON UPDATE CASCADE ON DELETE CASCADE, + CONSTRAINT milestone_tickets_fk_ticket FOREIGN KEY (ticket) + REFERENCES tickets.tickets (id) ON UPDATE CASCADE ON DELETE CASCADE ) WITH ( OIDS=FALSE ); -COMMENT ON TABLE "tickets"."milestone_tickets" IS 'This table stores the relation between milestones and their tickets.'; -COMMENT ON COLUMN "tickets"."milestone_tickets"."milestone" IS 'The unique ID of the milestone.'; -COMMENT ON COLUMN "tickets"."milestone_tickets"."ticket" IS 'The unique ID of the ticket that is attached to the milestone.'; +COMMENT ON TABLE tickets.milestone_tickets IS 'This table stores the relation between milestones and their tickets.'; +COMMENT ON COLUMN tickets.milestone_tickets.milestone IS 'The unique ID of the milestone.'; +COMMENT ON COLUMN tickets.milestone_tickets.ticket IS 'The unique ID of the ticket that is attached to the milestone.'; -CREATE TABLE "tickets"."ticket_assignees" +CREATE TABLE tickets.ticket_assignees ( - "ticket" BIGINT NOT NULL, - "assignee" UUID NOT NULL, - CONSTRAINT "ticket_assignees_pk" PRIMARY KEY ("ticket", "assignee"), - CONSTRAINT "ticket_assignees_fk_ticket" FOREIGN KEY ("ticket") - REFERENCES "tickets"."tickets" ("id") ON UPDATE CASCADE ON DELETE CASCADE, - CONSTRAINT "ticket_assignees_fk_assignee" FOREIGN KEY ("assignee") - REFERENCES "tickets"."users" ("uid") ON UPDATE CASCADE ON DELETE CASCADE + ticket BIGINT NOT NULL, + assignee UUID NOT NULL, + CONSTRAINT ticket_assignees_pk PRIMARY KEY (ticket, assignee), + CONSTRAINT ticket_assignees_fk_ticket FOREIGN KEY (ticket) + REFERENCES tickets.tickets (id) ON UPDATE CASCADE ON DELETE CASCADE, + CONSTRAINT ticket_assignees_fk_assignee FOREIGN KEY (assignee) + REFERENCES tickets.users (uid) ON UPDATE CASCADE ON DELETE CASCADE ) WITH ( OIDS=FALSE ); -COMMENT ON TABLE "tickets"."ticket_assignees" IS 'This table stores the relation between tickets and their assignees.'; -COMMENT ON COLUMN "tickets"."ticket_assignees"."ticket" IS 'The unqiue ID of the ticket.'; -COMMENT ON COLUMN "tickets"."ticket_assignees"."assignee" IS 'The unique ID of the user account that is assigned to the ticket.'; +COMMENT ON TABLE tickets.ticket_assignees IS 'This table stores the relation between tickets and their assignees.'; +COMMENT ON COLUMN tickets.ticket_assignees.ticket IS 'The unqiue ID of the ticket.'; +COMMENT ON COLUMN tickets.ticket_assignees.assignee IS 'The unique ID of the user account that is assigned to the ticket.'; -CREATE TABLE "tickets"."ticket_labels" +CREATE TABLE tickets.ticket_labels ( - "ticket" BIGINT NOT NULL, - "label" BIGINT NOT NULL, - CONSTRAINT "ticket_labels_pk" PRIMARY KEY ("ticket", "label"), - CONSTRAINT "ticket_labels_fk_ticket" FOREIGN KEY ("ticket") - REFERENCES "tickets"."tickets" ("id") ON UPDATE CASCADE ON DELETE CASCADE, - CONSTRAINT "ticket_labels_fk_label" FOREIGN KEY ("label") - REFERENCES "tickets"."labels" ("id") ON UPDATE CASCADE ON DELETE CASCADE + ticket BIGINT NOT NULL, + label BIGINT NOT NULL, + CONSTRAINT ticket_labels_pk PRIMARY KEY (ticket, label), + CONSTRAINT ticket_labels_fk_ticket FOREIGN KEY (ticket) + REFERENCES tickets.tickets (id) ON UPDATE CASCADE ON DELETE CASCADE, + CONSTRAINT ticket_labels_fk_label FOREIGN KEY (label) + REFERENCES tickets.labels (id) ON UPDATE CASCADE ON DELETE CASCADE ) WITH ( OIDS=FALSE ); -COMMENT ON TABLE "tickets"."ticket_labels" IS 'This table stores the relation between tickets and their labels.'; -COMMENT ON COLUMN "tickets"."ticket_labels"."ticket" IS 'The unqiue ID of the ticket.'; -COMMENT ON COLUMN "tickets"."ticket_labels"."label" IS 'The unique ID of the label that is attached to the ticket.'; +COMMENT ON TABLE tickets.ticket_labels IS 'This table stores the relation between tickets and their labels.'; +COMMENT ON COLUMN tickets.ticket_labels.ticket IS 'The unqiue ID of the ticket.'; +COMMENT ON COLUMN tickets.ticket_labels.label IS 'The unique ID of the label that is attached to the ticket.'; diff -rN -u old-smederee/modules/tickets/src/main/resources/db/migration/tickets/V3__add_language.sql new-smederee/modules/tickets/src/main/resources/db/migration/tickets/V3__add_language.sql --- old-smederee/modules/tickets/src/main/resources/db/migration/tickets/V3__add_language.sql 2025-01-13 01:25:25.761526357 +0000 +++ new-smederee/modules/tickets/src/main/resources/db/migration/tickets/V3__add_language.sql 2025-01-13 01:25:25.765526369 +0000 @@ -1,4 +1,4 @@ -ALTER TABLE "tickets"."users" - ADD COLUMN "language" CHARACTER VARYING(3) DEFAULT NULL; +ALTER TABLE tickets.users + ADD COLUMN language CHARACTER VARYING(3) DEFAULT NULL; -COMMENT ON COLUMN "tickets"."users"."language" IS 'The ISO-639 language code of the preferred language of the user.'; +COMMENT ON COLUMN tickets.users.language IS 'The ISO-639 language code of the preferred language of the user.'; diff -rN -u old-smederee/modules/tickets/src/main/resources/db/migration/tickets/V4__add_resolution.sql new-smederee/modules/tickets/src/main/resources/db/migration/tickets/V4__add_resolution.sql --- old-smederee/modules/tickets/src/main/resources/db/migration/tickets/V4__add_resolution.sql 2025-01-13 01:25:25.761526357 +0000 +++ new-smederee/modules/tickets/src/main/resources/db/migration/tickets/V4__add_resolution.sql 2025-01-13 01:25:25.765526369 +0000 @@ -1,4 +1,4 @@ -ALTER TABLE "tickets"."tickets" - ADD COLUMN "resolution" CHARACTER VARYING(16) DEFAULT NULL; +ALTER TABLE tickets.tickets + ADD COLUMN resolution CHARACTER VARYING(16) DEFAULT NULL; -COMMENT ON COLUMN "tickets"."tickets"."resolution" IS 'An optional resolution state of the ticket that should be set if it is closed.'; +COMMENT ON COLUMN tickets.tickets.resolution IS 'An optional resolution state of the ticket that should be set if it is closed.'; diff -rN -u old-smederee/modules/tickets/src/main/resources/db/migration/tickets/V5__add_closeable_milestones.sql new-smederee/modules/tickets/src/main/resources/db/migration/tickets/V5__add_closeable_milestones.sql --- old-smederee/modules/tickets/src/main/resources/db/migration/tickets/V5__add_closeable_milestones.sql 2025-01-13 01:25:25.761526357 +0000 +++ new-smederee/modules/tickets/src/main/resources/db/migration/tickets/V5__add_closeable_milestones.sql 2025-01-13 01:25:25.765526369 +0000 @@ -1,5 +1,4 @@ -ALTER TABLE "tickets"."milestones" - ADD COLUMN "closed" BOOLEAN DEFAULT FALSE; - -COMMENT ON COLUMN "tickets"."milestones"."closed" IS 'This flag indicates if the milestone is closed e.g. considered done or obsolete.'; +ALTER TABLE tickets.milestones + ADD COLUMN closed BOOLEAN DEFAULT FALSE; +COMMENT ON COLUMN tickets.milestones.closed IS 'This flag indicates if the milestone is closed e.g. considered done or obsolete.'; diff -rN -u old-smederee/modules/tickets/src/main/scala/de/smederee/tickets/DoobieLabelRepository.scala new-smederee/modules/tickets/src/main/scala/de/smederee/tickets/DoobieLabelRepository.scala --- old-smederee/modules/tickets/src/main/scala/de/smederee/tickets/DoobieLabelRepository.scala 2025-01-13 01:25:25.761526357 +0000 +++ new-smederee/modules/tickets/src/main/scala/de/smederee/tickets/DoobieLabelRepository.scala 2025-01-13 01:25:25.765526369 +0000 @@ -35,13 +35,13 @@ given Meta[ProjectId] = Meta[Long].timap(ProjectId.apply)(_.toLong) override def allLabels(projectId: ProjectId): Stream[F, Label] = - sql"""SELECT id, name, description, colour FROM "tickets"."labels" WHERE project = $projectId ORDER BY name ASC""" + sql"""SELECT id, name, description, colour FROM tickets.labels WHERE project = $projectId ORDER BY name ASC""" .query[Label] .stream .transact(tx) override def createLabel(projectId: ProjectId)(label: Label): F[Int] = - sql"""INSERT INTO "tickets"."labels" + sql"""INSERT INTO tickets.labels ( project, name, @@ -59,11 +59,11 @@ label.id match { case None => Sync[F].pure(0) case Some(id) => - sql"""DELETE FROM "tickets"."labels" WHERE id = $id""".update.run.transact(tx) + sql"""DELETE FROM tickets.labels WHERE id = $id""".update.run.transact(tx) } override def findLabel(projectId: ProjectId)(name: LabelName): F[Option[Label]] = - sql"""SELECT id, name, description, colour FROM "tickets"."labels" WHERE project = $projectId AND name = $name LIMIT 1""" + sql"""SELECT id, name, description, colour FROM tickets.labels WHERE project = $projectId AND name = $name LIMIT 1""" .query[Label] .option .transact(tx) @@ -72,11 +72,10 @@ label.id match { case None => Sync[F].pure(0) case Some(id) => - sql"""UPDATE "tickets"."labels" + sql"""UPDATE tickets.labels SET name = ${label.name}, description = ${label.description}, colour = ${label.colour} WHERE id = $id""".update.run.transact(tx) } - } diff -rN -u old-smederee/modules/tickets/src/main/scala/de/smederee/tickets/DoobieProjectRepository.scala new-smederee/modules/tickets/src/main/scala/de/smederee/tickets/DoobieProjectRepository.scala --- old-smederee/modules/tickets/src/main/scala/de/smederee/tickets/DoobieProjectRepository.scala 2025-01-13 01:25:25.761526357 +0000 +++ new-smederee/modules/tickets/src/main/scala/de/smederee/tickets/DoobieProjectRepository.scala 2025-01-13 01:25:25.765526369 +0000 @@ -40,7 +40,7 @@ given Meta[TicketNumber] = Meta[Int].timap(TicketNumber.apply)(_.toInt) override def createProject(project: Project): F[Int] = - sql"""INSERT INTO "tickets"."projects" (name, owner, is_private, description, created_at, updated_at) + sql"""INSERT INTO tickets.projects (name, owner, is_private, description, created_at, updated_at) VALUES ( ${project.name}, ${project.owner.uid}, @@ -51,61 +51,63 @@ )""".update.run.transact(tx) override def deleteProject(project: Project): F[Int] = - sql"""DELETE FROM "tickets"."projects" WHERE owner = ${project.owner.uid} AND name = ${project.name}""".update.run + sql"""DELETE FROM tickets.projects WHERE owner = ${project.owner.uid} AND name = ${project.name}""".update.run .transact(tx) override def findProject(owner: ProjectOwner, name: ProjectName): F[Option[Project]] = sql"""SELECT - "users".uid AS owner_id, - "users".name AS owner_name, - "users".email AS owner_email, - "projects".name, - "projects".description, - "projects".is_private - FROM "tickets"."projects" AS "projects" - JOIN "tickets"."users" AS "users" - ON "projects".owner = "users".uid + users.uid AS owner_id, + users.name AS owner_name, + users.email AS owner_email, + projects.name, + projects.description, + projects.is_private + FROM tickets.projects AS projects + JOIN tickets.users AS users + ON projects.owner = users.uid WHERE - "projects".owner = ${owner.uid} + projects.owner = ${owner.uid} AND - "projects".name = $name""".query[Project].option.transact(tx) + projects.name = $name""".query[Project].option.transact(tx) override def findProjectId(owner: ProjectOwner, name: ProjectName): F[Option[ProjectId]] = sql"""SELECT - "projects".id - FROM "tickets"."projects" AS "projects" - JOIN "tickets"."users" AS "users" - ON "projects".owner = "users".uid + projects.id + FROM tickets.projects AS projects + JOIN tickets.users AS users + ON projects.owner = users.uid WHERE - "projects".owner = ${owner.uid} + projects.owner = ${owner.uid} AND - "projects".name = $name""".query[ProjectId].option.transact(tx) + projects.name = $name""".query[ProjectId].option.transact(tx) override def findProjectOwner(name: ProjectOwnerName): F[Option[ProjectOwner]] = sql"""SELECT - "users".uid, - "users".name, - "users".email - FROM "tickets"."users" + users.uid, + users.name, + users.email + FROM tickets.users WHERE name = $name""".query[ProjectOwner].option.transact(tx) override def incrementNextTicketNumber(projectId: ProjectId): F[TicketNumber] = { // TODO: Find out which of the queries is more reliable and more performant. - val sqlQuery1 = sql"""UPDATE "tickets"."projects" AS alias1 + /* + val sqlQuery1 = sql"""UPDATE tickets.projects AS alias1 SET next_ticket_number = alias2.next_ticket_number + 1 FROM ( SELECT id, next_ticket_number - FROM "tickets"."projects" + FROM tickets.projects WHERE id = $projectId ) AS alias2 WHERE alias1.id = alias2.id RETURNING alias2.next_ticket_number AS next_ticket_number""" + */ val sqlQuery2 = sql"""WITH old_number AS ( - SELECT next_ticket_number FROM "tickets"."projects" WHERE id = $projectId + SELECT next_ticket_number FROM tickets.projects WHERE id = $projectId ) - UPDATE "tickets"."projects" + UPDATE tickets.projects SET next_ticket_number = next_ticket_number + 1 WHERE id = $projectId RETURNING ( @@ -115,12 +117,11 @@ } override def updateProject(project: Project): F[Int] = - sql"""UPDATE "tickets"."projects" SET + sql"""UPDATE tickets.projects SET is_private = ${project.isPrivate}, description = ${project.description}, updated_at = NOW() WHERE owner = ${project.owner.uid} AND name = ${project.name}""".update.run.transact(tx) - } diff -rN -u old-smederee/modules/tickets/src/main/scala/de/smederee/tickets/DoobieTicketRepository.scala new-smederee/modules/tickets/src/main/scala/de/smederee/tickets/DoobieTicketRepository.scala --- old-smederee/modules/tickets/src/main/scala/de/smederee/tickets/DoobieTicketRepository.scala 2025-01-13 01:25:25.761526357 +0000 +++ new-smederee/modules/tickets/src/main/scala/de/smederee/tickets/DoobieTicketRepository.scala 2025-01-13 01:25:25.769526382 +0000 @@ -56,18 +56,18 @@ private val selectTicketColumns = fr"""SELECT - "tickets".number AS number, - "tickets".title AS title, - "tickets".content AS content, - "tickets".status AS status, - "tickets".resolution AS resolution, - "submitters".uid AS submitter_uid, - "submitters".name AS submitter_name, - "tickets".created_at AS created_at, - "tickets".updated_at AS updated_at - FROM "tickets"."tickets" AS "tickets" - LEFT OUTER JOIN "tickets"."users" AS "submitters" - ON "tickets".submitter = "submitters".uid""" + tickets.number AS number, + tickets.title AS title, + tickets.content AS content, + tickets.status AS status, + tickets.resolution AS resolution, + submitters.uid AS submitter_uid, + submitters.name AS submitter_name, + tickets.created_at AS created_at, + tickets.updated_at AS updated_at + FROM tickets.tickets AS tickets + LEFT OUTER JOIN tickets.users AS submitters + ON tickets.submitter = submitters.uid""" /** Construct a query fragment that fetches the internal unique ticket id from the tickets table via the given * project id and ticket number. The fetched id can be referenced like this `SELECT id FROM ticket_id`. @@ -82,18 +82,18 @@ private def withTicketId(projectId: ProjectId, ticketNumber: TicketNumber): Fragment = fr"""WITH ticket_id AS ( SELECT id AS id - FROM "tickets"."tickets" AS "tickets" - WHERE "tickets".project = $projectId - AND "tickets".number = $ticketNumber)""" + FROM tickets.tickets AS tickets + WHERE tickets.project = $projectId + AND tickets.number = $ticketNumber)""" override def addAssignee(projectId: ProjectId)(ticketNumber: TicketNumber)(assignee: Assignee): F[Int] = - sql"""INSERT INTO "tickets"."ticket_assignees" ( + sql"""INSERT INTO tickets.ticket_assignees ( ticket, assignee ) SELECT id, ${assignee.id} - FROM "tickets"."tickets" + FROM tickets.tickets WHERE project = $projectId AND number = $ticketNumber""".update.run.transact(tx) @@ -101,13 +101,13 @@ label.id match { case None => Sync[F].pure(0) case Some(labelId) => - sql"""INSERT INTO "tickets"."ticket_labels" ( + sql"""INSERT INTO tickets.ticket_labels ( ticket, label ) SELECT id, $labelId - FROM "tickets"."tickets" + FROM tickets.tickets WHERE project = $projectId AND number = $ticketNumber""".update.run.transact(tx) } @@ -116,28 +116,28 @@ milestone.id match { case None => Sync[F].pure(0) case Some(milestoneId) => - sql"""INSERT INTO "tickets"."milestone_tickets" ( + sql"""INSERT INTO tickets.milestone_tickets ( ticket, milestone ) SELECT id, $milestoneId - FROM "tickets"."tickets" + FROM tickets.tickets WHERE project = $projectId AND number = $ticketNumber""".update.run.transact(tx) } override def allTickets(filter: Option[TicketFilter])(projectId: ProjectId): Stream[F, Ticket] = { - val projectFilter = fr""""tickets".project = $projectId""" + val projectFilter = fr"""tickets.project = $projectId""" val tickets = filter match { case None => selectTicketColumns ++ whereAnd(projectFilter) case Some(filter) => - val numberFilter = filter.number.toNel.map(numbers => Fragments.in(fr""""tickets".number""", numbers)) - val statusFilter = filter.status.toNel.map(status => Fragments.in(fr""""tickets".status""", status)) + val numberFilter = filter.number.toNel.map(numbers => Fragments.in(fr"""tickets.number""", numbers)) + val statusFilter = filter.status.toNel.map(status => Fragments.in(fr"""tickets.status""", status)) val resolutionFilter = - filter.resolution.toNel.map(resolutions => Fragments.in(fr""""tickets".resolution""", resolutions)) + filter.resolution.toNel.map(resolutions => Fragments.in(fr"""tickets.resolution""", resolutions)) val submitterFilter = - filter.submitter.toNel.map(submitters => Fragments.in(fr""""submitters".name""", submitters)) + filter.submitter.toNel.map(submitters => Fragments.in(fr"""submitters.name""", submitters)) selectTicketColumns ++ whereAndOpt( projectFilter.some, numberFilter, @@ -150,7 +150,7 @@ } override def createTicket(projectId: ProjectId)(ticket: Ticket): F[Int] = - sql"""INSERT INTO "tickets"."tickets" ( + sql"""INSERT INTO tickets.tickets ( project, number, title, @@ -173,7 +173,7 @@ )""".update.run.transact(tx) override def deleteTicket(projectId: ProjectId)(ticket: Ticket): F[Int] = - sql"""DELETE FROM "tickets"."tickets" + sql"""DELETE FROM tickets.tickets WHERE project = $projectId AND number = ${ticket.number}""".update.run.transact(tx) @@ -185,7 +185,7 @@ } override def findTicketId(projectId: ProjectId)(ticketNumber: TicketNumber): F[Option[TicketId]] = - sql"""SELECT id FROM "tickets"."tickets" WHERE project = $projectId AND number = $ticketNumber""" + sql"""SELECT id FROM tickets.tickets WHERE project = $projectId AND number = $ticketNumber""" .query[TicketId] .option .transact(tx) @@ -193,50 +193,50 @@ override def loadAssignees(projectId: ProjectId)(ticketNumber: TicketNumber): Stream[F, Assignee] = { val sqlQuery = withTicketId(projectId, ticketNumber) ++ fr"""SELECT - "users".uid AS uid, - "users".name AS name - FROM "tickets"."ticket_assignees" AS "assignees" - JOIN "tickets"."users" AS "users" - ON "assignees".assignee = "users".uid - WHERE "assignees".ticket = (SELECT id FROM ticket_id)""" + users.uid AS uid, + users.name AS name + FROM tickets.ticket_assignees AS assignees + JOIN tickets.users AS users + ON assignees.assignee = users.uid + WHERE assignees.ticket = (SELECT id FROM ticket_id)""" sqlQuery.query[Assignee].stream.transact(tx) } override def loadLabels(projectId: ProjectId)(ticketNumber: TicketNumber): Stream[F, Label] = { val sqlQuery = withTicketId(projectId, ticketNumber) ++ fr"""SELECT - "labels".id AS id, - "labels".name AS name, - "labels".description AS description, - "labels".colour AS colour - FROM "tickets"."labels" AS "labels" - JOIN "tickets"."ticket_labels" AS "ticket_labels" - ON "labels".id = "ticket_labels".label - WHERE "ticket_labels".ticket = (SELECT id FROM ticket_id)""" + labels.id AS id, + labels.name AS name, + labels.description AS description, + labels.colour AS colour + FROM tickets.labels AS labels + JOIN tickets.ticket_labels AS ticket_labels + ON labels.id = ticket_labels.label + WHERE ticket_labels.ticket = (SELECT id FROM ticket_id)""" sqlQuery.query[Label].stream.transact(tx) } override def loadMilestones(projectId: ProjectId)(ticketNumber: TicketNumber): Stream[F, Milestone] = { val sqlQuery = withTicketId(projectId, ticketNumber) ++ fr"""SELECT - "milestones".id AS id, - "milestones".title AS title, - "milestones".description AS description, - "milestones".due_date AS due_date, - "milestones".closed AS closed - FROM "tickets"."milestones" AS "milestones" - JOIN "tickets"."milestone_tickets" AS "milestone_tickets" - ON "milestones".id = "milestone_tickets"."milestone" - WHERE "milestone_tickets".ticket = (SELECT id FROM ticket_id) - ORDER BY "milestones".due_date ASC, "milestones".title ASC""" + milestones.id AS id, + milestones.title AS title, + milestones.description AS description, + milestones.due_date AS due_date, + milestones.closed AS closed + FROM tickets.milestones AS milestones + JOIN tickets.milestone_tickets AS milestone_tickets + ON milestones.id = milestone_tickets.milestone + WHERE milestone_tickets.ticket = (SELECT id FROM ticket_id) + ORDER BY milestones.due_date ASC, milestones.title ASC""" sqlQuery.query[Milestone].stream.transact(tx) } override def removeAssignee(projectId: ProjectId)(ticket: Ticket)(assignee: Assignee): F[Int] = { val sqlQuery = withTicketId(projectId, ticket.number) ++ - fr"""DELETE FROM "tickets"."ticket_assignees" AS "ticket_assignees" - WHERE "ticket_assignees".ticket = (SELECT id FROM ticket_id) - AND "ticket_assignees".assignee = ${assignee.id}""" + fr"""DELETE FROM tickets.ticket_assignees AS ticket_assignees + WHERE ticket_assignees.ticket = (SELECT id FROM ticket_id) + AND ticket_assignees.assignee = ${assignee.id}""" sqlQuery.update.run.transact(tx) } @@ -245,9 +245,9 @@ case None => Sync[F].pure(0) case Some(labelId) => val sqlQuery = withTicketId(projectId, ticket.number) ++ - fr"""DELETE FROM "tickets"."ticket_labels" AS "labels" - WHERE "labels".ticket = (SELECT id FROM ticket_id) - AND "labels".label = $labelId""" + fr"""DELETE FROM tickets.ticket_labels AS labels + WHERE labels.ticket = (SELECT id FROM ticket_id) + AND labels.label = $labelId""" sqlQuery.update.run.transact(tx) } @@ -256,14 +256,14 @@ case None => Sync[F].pure(0) case Some(milestoneId) => val sqlQuery = withTicketId(projectId, ticket.number) ++ - fr"""DELETE FROM "tickets"."milestone_tickets" AS "milestone_tickets" - WHERE "milestone_tickets".ticket = (SELECT id FROM ticket_id) - AND "milestone_tickets".milestone = $milestoneId""" + fr"""DELETE FROM tickets.milestone_tickets AS milestone_tickets + WHERE milestone_tickets.ticket = (SELECT id FROM ticket_id) + AND milestone_tickets.milestone = $milestoneId""" sqlQuery.update.run.transact(tx) } override def updateTicket(projectId: ProjectId)(ticket: Ticket): F[Int] = - sql"""UPDATE "tickets"."tickets" SET + sql"""UPDATE tickets.tickets SET title = ${ticket.title}, content = ${ticket.content}, status = ${ticket.status}, @@ -272,5 +272,4 @@ updated_at = NOW() WHERE project = $projectId AND number = ${ticket.number}""".update.run.transact(tx) - } diff -rN -u old-smederee/modules/tickets/src/main/scala/de/smederee/tickets/DoobieTicketServiceApi.scala new-smederee/modules/tickets/src/main/scala/de/smederee/tickets/DoobieTicketServiceApi.scala --- old-smederee/modules/tickets/src/main/scala/de/smederee/tickets/DoobieTicketServiceApi.scala 2025-01-13 01:25:25.761526357 +0000 +++ new-smederee/modules/tickets/src/main/scala/de/smederee/tickets/DoobieTicketServiceApi.scala 2025-01-13 01:25:25.769526382 +0000 @@ -40,7 +40,7 @@ given Meta[Username] = Meta[String].timap(Username.apply)(_.toString) override def createOrUpdateUser(user: TicketsUser): F[Int] = - sql"""INSERT INTO "tickets"."users" (uid, name, email, language, created_at, updated_at) + sql"""INSERT INTO tickets.users (uid, name, email, language, created_at, updated_at) VALUES ( ${user.uid}, ${user.name}, @@ -55,6 +55,5 @@ updated_at = EXCLUDED.updated_at""".update.run.transact(tx) override def deleteUser(uid: UserId): F[Int] = - sql"""DELETE FROM "tickets"."users" WHERE uid = $uid""".update.run.transact(tx) - + sql"""DELETE FROM tickets.users WHERE uid = $uid""".update.run.transact(tx) } diff -rN -u old-smederee/modules/tickets/src/test/scala/de/smederee/tickets/BaseSpec.scala new-smederee/modules/tickets/src/test/scala/de/smederee/tickets/BaseSpec.scala --- old-smederee/modules/tickets/src/test/scala/de/smederee/tickets/BaseSpec.scala 2025-01-13 01:25:25.765526369 +0000 +++ new-smederee/modules/tickets/src/test/scala/de/smederee/tickets/BaseSpec.scala 2025-01-13 01:25:25.769526382 +0000 @@ -156,7 +156,7 @@ for { statement <- IO.delay { con.prepareStatement( - """INSERT INTO "tickets"."projects" (name, owner, is_private, description, created_at, updated_at) VALUES(?, ?, ?, ?, NOW(), NOW())""" + """INSERT INTO tickets.projects (name, owner, is_private, description, created_at, updated_at) VALUES(?, ?, ?, ?, NOW(), NOW())""" ) } _ <- IO.delay(statement.setString(1, project.name.toString)) @@ -185,7 +185,7 @@ for { statement <- IO.delay { con.prepareStatement( - """INSERT INTO "tickets"."users" (uid, name, email, created_at, updated_at) VALUES(?, ?, ?, NOW(), NOW())""" + """INSERT INTO tickets.users (uid, name, email, created_at, updated_at) VALUES(?, ?, ?, NOW(), NOW())""" ) } _ <- IO.delay(statement.setObject(1, submitter.id)) @@ -209,7 +209,7 @@ for { statement <- IO.delay { con.prepareStatement( - """INSERT INTO "tickets"."users" (uid, name, email, created_at, updated_at) VALUES(?, ?, ?, NOW(), NOW())""" + """INSERT INTO tickets.users (uid, name, email, created_at, updated_at) VALUES(?, ?, ?, NOW(), NOW())""" ) } _ <- IO.delay(statement.setObject(1, owner.uid)) @@ -233,7 +233,7 @@ for { statement <- IO.delay { con.prepareStatement( - """INSERT INTO "tickets"."users" (uid, name, email, created_at, updated_at) VALUES(?, ?, ?, NOW(), NOW())""" + """INSERT INTO tickets.users (uid, name, email, created_at, updated_at) VALUES(?, ?, ?, NOW(), NOW())""" ) } _ <- IO.delay(statement.setObject(1, user.uid)) @@ -257,7 +257,7 @@ for { statement <- IO.delay( con.prepareStatement( - """SELECT next_ticket_number FROM "tickets"."projects" WHERE id = ?""" + """SELECT next_ticket_number FROM tickets.projects WHERE id = ?""" ) ) _ <- IO.delay(statement.setLong(1, projectId.toLong)) @@ -285,7 +285,7 @@ for { statement <- IO.delay( con.prepareStatement( - """SELECT id FROM "tickets"."projects" WHERE owner = ? AND name = ? LIMIT 1""" + """SELECT id FROM tickets.projects WHERE owner = ? AND name = ? LIMIT 1""" ) ) _ <- IO.delay(statement.setObject(1, owner)) @@ -317,7 +317,7 @@ for { statement <- IO.delay( con.prepareStatement( - """SELECT id FROM "tickets"."tickets" WHERE project = ? AND number = ? LIMIT 1""" + """SELECT id FROM tickets.tickets WHERE project = ? AND number = ? LIMIT 1""" ) ) _ <- IO.delay(statement.setLong(1, project.toLong)) @@ -346,7 +346,7 @@ connectToDb(configuration).use { con => for { statement <- IO.delay( - con.prepareStatement("""SELECT uid, name, email, language FROM "tickets"."users" WHERE uid = ?""") + con.prepareStatement("""SELECT uid, name, email, language FROM tickets.users WHERE uid = ?""") ) _ <- IO.delay(statement.setObject(1, uid.toUUID)) result <- IO.delay(statement.executeQuery())