diff --git a/app/models/issue.rb b/app/models/issue.rb index 7e303bc257a51d370f0639e75308c16afda2bced..d0dd97dbcd9abc17d47d0d9cbb7d12ad502fe8d1 100644 --- a/app/models/issue.rb +++ b/app/models/issue.rb @@ -28,6 +28,8 @@ class Issue < ApplicationRecord SORTING_PREFERENCE_FIELD = :issues_sort + self.primary_key = :id + belongs_to :project belongs_to :duplicated_to, class_name: 'Issue' belongs_to :closed_by, class_name: 'User' diff --git a/db/migrate/20200429095943_partition_issues_table.down.sql b/db/migrate/20200429095943_partition_issues_table.down.sql new file mode 100644 index 0000000000000000000000000000000000000000..87bb07e0b86fa38ee963d55c72737cba987e7b2a --- /dev/null +++ b/db/migrate/20200429095943_partition_issues_table.down.sql @@ -0,0 +1,308 @@ +DROP SCHEMA parts CASCADE; +DROP SERVER IF EXISTS shard1 CASCADE; +DROP SERVER IF EXISTS shard2 CASCADE; +DROP EXTENSION IF EXISTS postgres_fdw; + +CREATE TABLE public.issues ( + id integer NOT NULL, + title character varying, + author_id integer, + project_id integer, + created_at timestamp without time zone, + updated_at timestamp without time zone, + description text, + milestone_id integer, + iid integer, + updated_by_id integer, + weight integer, + confidential boolean DEFAULT false NOT NULL, + due_date date, + moved_to_id integer, + lock_version integer DEFAULT 0, + title_html text, + description_html text, + time_estimate integer, + relative_position integer, + service_desk_reply_to character varying, + cached_markdown_version integer, + last_edited_at timestamp without time zone, + last_edited_by_id integer, + discussion_locked boolean, + closed_at timestamp with time zone, + closed_by_id integer, + state_id smallint DEFAULT 1 NOT NULL, + duplicated_to_id integer, + promoted_to_epic_id integer, + health_status smallint, + external_key character varying(255), + sprint_id bigint +); + + +CREATE SEQUENCE public.issues_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +-- +-- Name: issues_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - +-- + +ALTER SEQUENCE public.issues_id_seq OWNED BY public.issues.id; + + +-- +-- Name: issues id; Type: DEFAULT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY public.issues ALTER COLUMN id SET DEFAULT nextval('public.issues_id_seq'::regclass); + + +-- +-- Name: issues issues_pkey; Type: CONSTRAINT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY public.issues + ADD CONSTRAINT issues_pkey PRIMARY KEY (id); + + +-- +-- Name: idx_issues_on_health_status_not_null; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX idx_issues_on_health_status_not_null ON public.issues USING btree (health_status) WHERE (health_status IS NOT NULL); + + +-- +-- Name: idx_issues_on_project_id_and_created_at_and_id_and_state_id; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX idx_issues_on_project_id_and_created_at_and_id_and_state_id ON public.issues USING btree (project_id, created_at, id, state_id); + + +-- +-- Name: idx_issues_on_project_id_and_due_date_and_id_and_state_id; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX idx_issues_on_project_id_and_due_date_and_id_and_state_id ON public.issues USING btree (project_id, due_date, id, state_id) WHERE (due_date IS NOT NULL); + + +-- +-- Name: idx_issues_on_project_id_and_rel_position_and_state_id_and_id; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX idx_issues_on_project_id_and_rel_position_and_state_id_and_id ON public.issues USING btree (project_id, relative_position, state_id, id DESC); + + +-- +-- Name: idx_issues_on_project_id_and_updated_at_and_id_and_state_id; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX idx_issues_on_project_id_and_updated_at_and_id_and_state_id ON public.issues USING btree (project_id, updated_at, id, state_id); + + +-- +-- Name: idx_issues_on_state_id; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX idx_issues_on_state_id ON public.issues USING btree (state_id); + + +-- +-- Name: index_issues_on_author_id; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX index_issues_on_author_id ON public.issues USING btree (author_id); + + +-- +-- Name: index_issues_on_author_id_and_id_and_created_at; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX index_issues_on_author_id_and_id_and_created_at ON public.issues USING btree (author_id, id, created_at); + + +-- +-- Name: index_issues_on_closed_by_id; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX index_issues_on_closed_by_id ON public.issues USING btree (closed_by_id); + + +-- +-- Name: index_issues_on_confidential; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX index_issues_on_confidential ON public.issues USING btree (confidential); + + +-- +-- Name: index_issues_on_description_trigram; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX index_issues_on_description_trigram ON public.issues USING gin (description public.gin_trgm_ops); + + +-- +-- Name: index_issues_on_duplicated_to_id; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX index_issues_on_duplicated_to_id ON public.issues USING btree (duplicated_to_id) WHERE (duplicated_to_id IS NOT NULL); + + +-- +-- Name: index_issues_on_lock_version; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX index_issues_on_lock_version ON public.issues USING btree (lock_version) WHERE (lock_version IS NULL); + + +-- +-- Name: index_issues_on_milestone_id; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX index_issues_on_milestone_id ON public.issues USING btree (milestone_id); + + +-- +-- Name: index_issues_on_moved_to_id; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX index_issues_on_moved_to_id ON public.issues USING btree (moved_to_id) WHERE (moved_to_id IS NOT NULL); + + +-- +-- Name: index_issues_on_project_id_and_external_key; Type: INDEX; Schema: public; Owner: - +-- + +CREATE UNIQUE INDEX index_issues_on_project_id_and_external_key ON public.issues USING btree (project_id, external_key) WHERE (external_key IS NOT NULL); + + +-- +-- Name: index_issues_on_project_id_and_iid; Type: INDEX; Schema: public; Owner: - +-- + +CREATE UNIQUE INDEX index_issues_on_project_id_and_iid ON public.issues USING btree (project_id, iid); + + +-- +-- Name: index_issues_on_promoted_to_epic_id; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX index_issues_on_promoted_to_epic_id ON public.issues USING btree (promoted_to_epic_id) WHERE (promoted_to_epic_id IS NOT NULL); + + +-- +-- Name: index_issues_on_relative_position; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX index_issues_on_relative_position ON public.issues USING btree (relative_position); + + +-- +-- Name: index_issues_on_sprint_id; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX index_issues_on_sprint_id ON public.issues USING btree (sprint_id); + + +-- +-- Name: index_issues_on_title_trigram; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX index_issues_on_title_trigram ON public.issues USING gin (title public.gin_trgm_ops); + + +-- +-- Name: index_issues_on_updated_at; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX index_issues_on_updated_at ON public.issues USING btree (updated_at); + + +-- +-- Name: index_issues_on_updated_by_id; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX index_issues_on_updated_by_id ON public.issues USING btree (updated_by_id) WHERE (updated_by_id IS NOT NULL); + + +-- +-- Name: issues fk_05f1e72feb; Type: FK CONSTRAINT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY public.issues + ADD CONSTRAINT fk_05f1e72feb FOREIGN KEY (author_id) REFERENCES public.users(id) ON DELETE SET NULL; + + +-- +-- Name: issues fk_3b8c72ea56; Type: FK CONSTRAINT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY public.issues + ADD CONSTRAINT fk_3b8c72ea56 FOREIGN KEY (sprint_id) REFERENCES public.sprints(id) ON DELETE CASCADE; + + +-- +-- Name: issues fk_899c8f3231; Type: FK CONSTRAINT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY public.issues + ADD CONSTRAINT fk_899c8f3231 FOREIGN KEY (project_id) REFERENCES public.projects(id) ON DELETE CASCADE; + + +-- +-- Name: issues fk_96b1dd429c; Type: FK CONSTRAINT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY public.issues + ADD CONSTRAINT fk_96b1dd429c FOREIGN KEY (milestone_id) REFERENCES public.milestones(id) ON DELETE SET NULL; + + +-- +-- Name: issues fk_9c4516d665; Type: FK CONSTRAINT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY public.issues + ADD CONSTRAINT fk_9c4516d665 FOREIGN KEY (duplicated_to_id) REFERENCES public.issues(id) ON DELETE SET NULL; + + +-- +-- Name: issues fk_a194299be1; Type: FK CONSTRAINT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY public.issues + ADD CONSTRAINT fk_a194299be1 FOREIGN KEY (moved_to_id) REFERENCES public.issues(id) ON DELETE SET NULL; + + +-- +-- Name: issues fk_c63cbf6c25; Type: FK CONSTRAINT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY public.issues + ADD CONSTRAINT fk_c63cbf6c25 FOREIGN KEY (closed_by_id) REFERENCES public.users(id) ON DELETE SET NULL; + + +-- +-- Name: issues fk_df75a7c8b8; Type: FK CONSTRAINT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY public.issues + ADD CONSTRAINT fk_df75a7c8b8 FOREIGN KEY (promoted_to_epic_id) REFERENCES public.epics(id) ON DELETE SET NULL; + + +-- +-- Name: issues fk_ffed080f01; Type: FK CONSTRAINT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY public.issues + ADD CONSTRAINT fk_ffed080f01 FOREIGN KEY (updated_by_id) REFERENCES public.users(id) ON DELETE SET NULL; + + +-- +-- PostgreSQL database dump complete +-- + diff --git a/db/migrate/20200429095943_partition_issues_table.rb b/db/migrate/20200429095943_partition_issues_table.rb new file mode 100644 index 0000000000000000000000000000000000000000..6ba6a0583128aea4a8f3ddd8f6dca59f800198ec --- /dev/null +++ b/db/migrate/20200429095943_partition_issues_table.rb @@ -0,0 +1,29 @@ +# frozen_string_literal: true + +class PartitionIssuesTable < ActiveRecord::Migration[6.0] + DOWNTIME = false + + disable_ddl_transaction! + + def up + execute 'DROP DATABASE IF EXISTS shard1' + execute 'DROP DATABASE IF EXISTS shard2' + execute 'CREATE DATABASE shard1' + execute 'CREATE DATABASE shard2' + + ActiveRecord::Base.transaction do + execute 'DROP TABLE IF EXISTS issues CASCADE' + execute File.read(File.join(__dir__, '20200429095943_partition_issues_table.up.sql')) + end + end + + def down + ActiveRecord::Base.transaction do + execute 'DROP TABLE IF EXISTS issues CASCADE' + execute File.read(File.join(__dir__, '20200429095943_partition_issues_table.down.sql')) + end + + execute 'DROP DATABASE IF EXISTS shard1' + execute 'DROP DATABASE IF EXISTS shard2' + end +end diff --git a/db/migrate/20200429095943_partition_issues_table.up.sql b/db/migrate/20200429095943_partition_issues_table.up.sql new file mode 100644 index 0000000000000000000000000000000000000000..5b9643a169f9abd9bebc3d102ff9cbf419b711fa --- /dev/null +++ b/db/migrate/20200429095943_partition_issues_table.up.sql @@ -0,0 +1,80 @@ +CREATE TABLE public.issues ( + id integer NOT NULL, + title character varying, + author_id integer, + project_id integer, + created_at timestamp without time zone, + updated_at timestamp without time zone, + description text, + milestone_id integer, + iid integer, + updated_by_id integer, + weight integer, + confidential boolean DEFAULT false NOT NULL, + due_date date, + moved_to_id integer, + lock_version integer DEFAULT 0, + title_html text, + description_html text, + time_estimate integer, + relative_position integer, + service_desk_reply_to character varying, + cached_markdown_version integer, + last_edited_at timestamp without time zone, + last_edited_by_id integer, + discussion_locked boolean, + closed_at timestamp with time zone, + closed_by_id integer, + state_id smallint DEFAULT 1 NOT NULL, + duplicated_to_id integer, + promoted_to_epic_id integer, + health_status smallint, + external_key character varying(255), + sprint_id bigint +) PARTITION BY HASH (project_id); + + +CREATE SEQUENCE public.issues_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +-- +-- Name: issues_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - +-- + +ALTER SEQUENCE public.issues_id_seq OWNED BY public.issues.id; + + +-- +-- Name: issues id; Type: DEFAULT; Schema: public; Owner: - +-- + +ALTER TABLE public.issues ALTER COLUMN id SET DEFAULT nextval('public.issues_id_seq'::regclass); + + +-- +-- PostgreSQL database dump complete +-- + +CREATE SCHEMA IF NOT EXISTS parts; + +CREATE EXTENSION IF NOT EXISTS postgres_fdw; + +CREATE SERVER IF NOT EXISTS shard1 FOREIGN DATA WRAPPER postgres_fdw OPTIONS (dbname 'shard1', host '/home/abrandl-gl/workspace/gdk/postgresql'); +CREATE SERVER IF NOT EXISTS shard2 FOREIGN DATA WRAPPER postgres_fdw OPTIONS (dbname 'shard2', host '/home/abrandl-gl/workspace/gdk/postgresql'); + +CREATE USER MAPPING IF NOT EXISTS for "abrandl-gl" SERVER shard1 OPTIONS (user 'abrandl-gl', password 'abrandl-gl'); +CREATE USER MAPPING IF NOT EXISTS for "abrandl-gl" SERVER shard2 OPTIONS (user 'abrandl-gl', password 'abrandl-gl'); + +CREATE FOREIGN TABLE parts.issues_0 PARTITION OF public.issues FOR VALUES WITH (modulus 8, remainder 0) SERVER shard1; +CREATE FOREIGN TABLE parts.issues_1 PARTITION OF public.issues FOR VALUES WITH (modulus 8, remainder 1) SERVER shard2; +CREATE FOREIGN TABLE parts.issues_2 PARTITION OF public.issues FOR VALUES WITH (modulus 8, remainder 2) SERVER shard1; +CREATE FOREIGN TABLE parts.issues_3 PARTITION OF public.issues FOR VALUES WITH (modulus 8, remainder 3) SERVER shard2; +CREATE FOREIGN TABLE parts.issues_4 PARTITION OF public.issues FOR VALUES WITH (modulus 8, remainder 4) SERVER shard1; +CREATE FOREIGN TABLE parts.issues_5 PARTITION OF public.issues FOR VALUES WITH (modulus 8, remainder 5) SERVER shard2; +CREATE FOREIGN TABLE parts.issues_6 PARTITION OF public.issues FOR VALUES WITH (modulus 8, remainder 6) SERVER shard1; +CREATE FOREIGN TABLE parts.issues_7 PARTITION OF public.issues FOR VALUES WITH (modulus 8, remainder 7) SERVER shard2; diff --git a/db/sharding/add_column/main.sql b/db/sharding/add_column/main.sql new file mode 100644 index 0000000000000000000000000000000000000000..7ae36f6c294ebc222b3fbe930e85fc6220457412 --- /dev/null +++ b/db/sharding/add_column/main.sql @@ -0,0 +1 @@ +ALTER TABLE issues ADD COLUMN foo integer; \ No newline at end of file diff --git a/db/sharding/add_column/shard1.sql b/db/sharding/add_column/shard1.sql new file mode 100644 index 0000000000000000000000000000000000000000..d361642c4c5be920e9b9e0cd7bbcc06d2c4a06b0 --- /dev/null +++ b/db/sharding/add_column/shard1.sql @@ -0,0 +1,4 @@ +ALTER TABLE parts.issues_0 ADD COLUMN foo integer; +ALTER TABLE parts.issues_2 ADD COLUMN foo integer; +ALTER TABLE parts.issues_4 ADD COLUMN foo integer; +ALTER TABLE parts.issues_6 ADD COLUMN foo integer; \ No newline at end of file diff --git a/db/sharding/add_column/shard2.sql b/db/sharding/add_column/shard2.sql new file mode 100644 index 0000000000000000000000000000000000000000..22df3b8ce01b20deaaff38880fabf9e796c41976 --- /dev/null +++ b/db/sharding/add_column/shard2.sql @@ -0,0 +1,4 @@ +ALTER TABLE parts.issues_1 ADD COLUMN foo integer; +ALTER TABLE parts.issues_3 ADD COLUMN foo integer; +ALTER TABLE parts.issues_5 ADD COLUMN foo integer; +ALTER TABLE parts.issues_7 ADD COLUMN foo integer; \ No newline at end of file diff --git a/db/sharding/drop_column/main.sql b/db/sharding/drop_column/main.sql new file mode 100644 index 0000000000000000000000000000000000000000..e720b3659503f49ba539eb40e4a42c7539e4f00b --- /dev/null +++ b/db/sharding/drop_column/main.sql @@ -0,0 +1 @@ +ALTER TABLE issues DROP COLUMN foo; \ No newline at end of file diff --git a/db/sharding/drop_column/shard1.sql b/db/sharding/drop_column/shard1.sql new file mode 100644 index 0000000000000000000000000000000000000000..e08a4df8ded7e1d406d10cc748156cf6e2a6761f --- /dev/null +++ b/db/sharding/drop_column/shard1.sql @@ -0,0 +1,4 @@ +ALTER TABLE parts.issues_0 DROP COLUMN foo; +ALTER TABLE parts.issues_2 DROP COLUMN foo; +ALTER TABLE parts.issues_4 DROP COLUMN foo; +ALTER TABLE parts.issues_6 DROP COLUMN foo; \ No newline at end of file diff --git a/db/sharding/drop_column/shard2.sql b/db/sharding/drop_column/shard2.sql new file mode 100644 index 0000000000000000000000000000000000000000..948b41c3fdbdc35de7c9c9270455a8b0efdd1ba8 --- /dev/null +++ b/db/sharding/drop_column/shard2.sql @@ -0,0 +1,4 @@ +ALTER TABLE parts.issues_1 DROP COLUMN foo; +ALTER TABLE parts.issues_3 DROP COLUMN foo; +ALTER TABLE parts.issues_5 DROP COLUMN foo; +ALTER TABLE parts.issues_7 DROP COLUMN foo; \ No newline at end of file diff --git a/db/sharding/inheritance/shard1.sql b/db/sharding/inheritance/shard1.sql new file mode 100644 index 0000000000000000000000000000000000000000..b21680f0bbd6b5c7c17597d4911f7d86223037e0 --- /dev/null +++ b/db/sharding/inheritance/shard1.sql @@ -0,0 +1,8 @@ +create table public.issues (LIKE parts.issues_0 INCLUDING ALL); +alter table issues_0 INHERIT public.issues; +alter table issues_2 INHERIT public.issues; +alter table issues_4 INHERIT public.issues; +alter table issues_6 INHERIT public.issues; +alter table issues_8 INHERIT public.issues; + + diff --git a/db/sharding/reference-tables/main.sql b/db/sharding/reference-tables/main.sql new file mode 100644 index 0000000000000000000000000000000000000000..bb8fee83a43e30f84b41accea6a4bf1c99e40ba7 --- /dev/null +++ b/db/sharding/reference-tables/main.sql @@ -0,0 +1,4 @@ +select pg_create_logical_replication_slot('shard1', 'pgoutput'); +select pg_create_logical_replication_slot('shard2', 'pgoutput'); + +create publication test for table users, projects; \ No newline at end of file diff --git a/db/sharding/reference-tables/reference-tables.sql b/db/sharding/reference-tables/reference-tables.sql new file mode 100644 index 0000000000000000000000000000000000000000..9b5eae3b736f5e2eb9167fb2bf8b620d65909c98 --- /dev/null +++ b/db/sharding/reference-tables/reference-tables.sql @@ -0,0 +1,827 @@ +-- +-- PostgreSQL database dump +-- + +-- Dumped from database version 11.7 (Ubuntu 11.7-2.pgdg19.10+1) +-- Dumped by pg_dump version 11.7 (Ubuntu 11.7-2.pgdg19.10+1) + +SET statement_timeout = 0; +SET lock_timeout = 0; +SET idle_in_transaction_session_timeout = 0; +SET client_encoding = 'UTF8'; +SET standard_conforming_strings = on; +SELECT pg_catalog.set_config('search_path', '', false); +SET check_function_bodies = false; +SET xmloption = content; +SET client_min_messages = warning; +SET row_security = off; + +SET default_tablespace = ''; + +SET default_with_oids = false; + +-- +-- Name: projects; Type: TABLE; Schema: public; Owner: - +-- + +CREATE TABLE public.projects ( + id integer NOT NULL, + name character varying, + path character varying, + description text, + created_at timestamp without time zone, + updated_at timestamp without time zone, + creator_id integer, + namespace_id integer NOT NULL, + last_activity_at timestamp without time zone, + import_url character varying, + visibility_level integer DEFAULT 0 NOT NULL, + archived boolean DEFAULT false NOT NULL, + avatar character varying, + merge_requests_template text, + star_count integer DEFAULT 0 NOT NULL, + merge_requests_rebase_enabled boolean DEFAULT false, + import_type character varying, + import_source character varying, + approvals_before_merge integer DEFAULT 0 NOT NULL, + reset_approvals_on_push boolean DEFAULT true, + merge_requests_ff_only_enabled boolean DEFAULT false, + issues_template text, + mirror boolean DEFAULT false NOT NULL, + mirror_last_update_at timestamp without time zone, + mirror_last_successful_update_at timestamp without time zone, + mirror_user_id integer, + shared_runners_enabled boolean DEFAULT true NOT NULL, + runners_token character varying, + build_coverage_regex character varying, + build_allow_git_fetch boolean DEFAULT true NOT NULL, + build_timeout integer DEFAULT 3600 NOT NULL, + mirror_trigger_builds boolean DEFAULT false NOT NULL, + pending_delete boolean DEFAULT false, + public_builds boolean DEFAULT true NOT NULL, + last_repository_check_failed boolean, + last_repository_check_at timestamp without time zone, + container_registry_enabled boolean, + only_allow_merge_if_pipeline_succeeds boolean DEFAULT false NOT NULL, + has_external_issue_tracker boolean, + repository_storage character varying DEFAULT 'default'::character varying NOT NULL, + repository_read_only boolean, + request_access_enabled boolean DEFAULT true NOT NULL, + has_external_wiki boolean, + ci_config_path character varying, + lfs_enabled boolean, + description_html text, + only_allow_merge_if_all_discussions_are_resolved boolean, + repository_size_limit bigint, + printing_merge_request_link_enabled boolean DEFAULT true NOT NULL, + auto_cancel_pending_pipelines integer DEFAULT 1 NOT NULL, + service_desk_enabled boolean DEFAULT true, + cached_markdown_version integer, + delete_error text, + last_repository_updated_at timestamp without time zone, + disable_overriding_approvers_per_merge_request boolean, + storage_version smallint, + resolve_outdated_diff_discussions boolean, + remote_mirror_available_overridden boolean, + only_mirror_protected_branches boolean, + pull_mirror_available_overridden boolean, + jobs_cache_index integer, + external_authorization_classification_label character varying, + mirror_overwrites_diverged_branches boolean, + pages_https_only boolean DEFAULT true, + external_webhook_token character varying, + packages_enabled boolean, + merge_requests_author_approval boolean, + pool_repository_id bigint, + runners_token_encrypted character varying, + bfg_object_map character varying, + detected_repository_languages boolean, + merge_requests_disable_committers_approval boolean, + require_password_to_approve boolean, + emails_disabled boolean, + max_pages_size integer, + max_artifacts_size integer, + pull_mirror_branch_prefix character varying(50), + remove_source_branch_after_merge boolean, + marked_for_deletion_at date, + marked_for_deletion_by_user_id integer, + autoclose_referenced_issues boolean, + suggestion_commit_message character varying(255) +); + + +-- +-- Name: projects_id_seq; Type: SEQUENCE; Schema: public; Owner: - +-- + +CREATE SEQUENCE public.projects_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +-- +-- Name: projects_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - +-- + +ALTER SEQUENCE public.projects_id_seq OWNED BY public.projects.id; + + +-- +-- Name: users; Type: TABLE; Schema: public; Owner: - +-- + +CREATE TABLE public.users ( + id integer NOT NULL, + email character varying DEFAULT ''::character varying NOT NULL, + encrypted_password character varying DEFAULT ''::character varying NOT NULL, + reset_password_token character varying, + reset_password_sent_at timestamp without time zone, + remember_created_at timestamp without time zone, + sign_in_count integer DEFAULT 0, + current_sign_in_at timestamp without time zone, + last_sign_in_at timestamp without time zone, + current_sign_in_ip character varying, + last_sign_in_ip character varying, + created_at timestamp without time zone, + updated_at timestamp without time zone, + name character varying, + admin boolean DEFAULT false NOT NULL, + projects_limit integer NOT NULL, + skype character varying DEFAULT ''::character varying NOT NULL, + linkedin character varying DEFAULT ''::character varying NOT NULL, + twitter character varying DEFAULT ''::character varying NOT NULL, + bio character varying, + failed_attempts integer DEFAULT 0, + locked_at timestamp without time zone, + username character varying, + can_create_group boolean DEFAULT true NOT NULL, + can_create_team boolean DEFAULT true NOT NULL, + state character varying, + color_scheme_id integer DEFAULT 1 NOT NULL, + password_expires_at timestamp without time zone, + created_by_id integer, + last_credential_check_at timestamp without time zone, + avatar character varying, + confirmation_token character varying, + confirmed_at timestamp without time zone, + confirmation_sent_at timestamp without time zone, + unconfirmed_email character varying, + hide_no_ssh_key boolean DEFAULT false, + website_url character varying DEFAULT ''::character varying NOT NULL, + admin_email_unsubscribed_at timestamp without time zone, + notification_email character varying, + hide_no_password boolean DEFAULT false, + password_automatically_set boolean DEFAULT false, + location character varying, + encrypted_otp_secret character varying, + encrypted_otp_secret_iv character varying, + encrypted_otp_secret_salt character varying, + otp_required_for_login boolean DEFAULT false NOT NULL, + otp_backup_codes text, + public_email character varying DEFAULT ''::character varying NOT NULL, + dashboard integer DEFAULT 0, + project_view integer DEFAULT 0, + consumed_timestep integer, + layout integer DEFAULT 0, + hide_project_limit boolean DEFAULT false, + note text, + unlock_token character varying, + otp_grace_period_started_at timestamp without time zone, + external boolean DEFAULT false, + incoming_email_token character varying, + organization character varying, + auditor boolean DEFAULT false NOT NULL, + require_two_factor_authentication_from_group boolean DEFAULT false NOT NULL, + two_factor_grace_period integer DEFAULT 48 NOT NULL, + ghost boolean, + last_activity_on date, + notified_of_own_activity boolean, + preferred_language character varying, + email_opted_in boolean, + email_opted_in_ip character varying, + email_opted_in_source_id integer, + email_opted_in_at timestamp without time zone, + theme_id smallint, + accepted_term_id integer, + feed_token character varying, + private_profile boolean DEFAULT false NOT NULL, + roadmap_layout smallint, + include_private_contributions boolean, + commit_email character varying, + group_view integer, + managing_group_id integer, + bot_type smallint, + first_name character varying(255), + last_name character varying(255), + static_object_token character varying(255), + role smallint, + user_type smallint +); + + +-- +-- Name: users_id_seq; Type: SEQUENCE; Schema: public; Owner: - +-- + +CREATE SEQUENCE public.users_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +-- +-- Name: users_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - +-- + +ALTER SEQUENCE public.users_id_seq OWNED BY public.users.id; + + +-- +-- Name: projects id; Type: DEFAULT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY public.projects ALTER COLUMN id SET DEFAULT nextval('public.projects_id_seq'::regclass); + + +-- +-- Name: users id; Type: DEFAULT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY public.users ALTER COLUMN id SET DEFAULT nextval('public.users_id_seq'::regclass); + + +-- +-- Name: projects projects_pkey; Type: CONSTRAINT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY public.projects + ADD CONSTRAINT projects_pkey PRIMARY KEY (id); + + +-- +-- Name: users users_pkey; Type: CONSTRAINT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY public.users + ADD CONSTRAINT users_pkey PRIMARY KEY (id); + + +-- +-- Name: idx_project_repository_check_partial; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX idx_project_repository_check_partial ON public.projects USING btree (repository_storage, created_at) WHERE (last_repository_check_at IS NULL); + + +-- +-- Name: idx_projects_on_repository_storage_last_repository_updated_at; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX idx_projects_on_repository_storage_last_repository_updated_at ON public.projects USING btree (id, repository_storage, last_repository_updated_at); + + +-- +-- Name: index_for_migrating_user_highest_roles_table; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX index_for_migrating_user_highest_roles_table ON public.users USING btree (id) WHERE (((state)::text = 'active'::text) AND (user_type IS NULL) AND (bot_type IS NULL) AND (ghost IS NOT TRUE)); + + +-- +-- Name: index_on_id_partial_with_legacy_storage; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX index_on_id_partial_with_legacy_storage ON public.projects USING btree (id) WHERE ((storage_version < 2) OR (storage_version IS NULL)); + + +-- +-- Name: index_on_users_name_lower; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX index_on_users_name_lower ON public.users USING btree (lower((name)::text)); + + +-- +-- Name: index_projects_api_created_at_id_desc; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX index_projects_api_created_at_id_desc ON public.projects USING btree (created_at, id DESC); + + +-- +-- Name: index_projects_api_created_at_id_for_archived; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX index_projects_api_created_at_id_for_archived ON public.projects USING btree (created_at, id) WHERE ((archived = true) AND (pending_delete = false)); + + +-- +-- Name: index_projects_api_created_at_id_for_archived_vis20; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX index_projects_api_created_at_id_for_archived_vis20 ON public.projects USING btree (created_at, id) WHERE ((archived = true) AND (visibility_level = 20) AND (pending_delete = false)); + + +-- +-- Name: index_projects_api_created_at_id_for_vis10; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX index_projects_api_created_at_id_for_vis10 ON public.projects USING btree (created_at, id) WHERE ((visibility_level = 10) AND (pending_delete = false)); + + +-- +-- Name: index_projects_api_last_activity_at_id_desc; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX index_projects_api_last_activity_at_id_desc ON public.projects USING btree (last_activity_at, id DESC); + + +-- +-- Name: index_projects_api_name_id_desc; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX index_projects_api_name_id_desc ON public.projects USING btree (name, id DESC); + + +-- +-- Name: index_projects_api_path_id_desc; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX index_projects_api_path_id_desc ON public.projects USING btree (path, id DESC); + + +-- +-- Name: index_projects_api_updated_at_id_desc; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX index_projects_api_updated_at_id_desc ON public.projects USING btree (updated_at, id DESC); + + +-- +-- Name: index_projects_api_vis20_created_at; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX index_projects_api_vis20_created_at ON public.projects USING btree (created_at, id) WHERE (visibility_level = 20); + + +-- +-- Name: index_projects_api_vis20_last_activity_at; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX index_projects_api_vis20_last_activity_at ON public.projects USING btree (last_activity_at, id) WHERE (visibility_level = 20); + + +-- +-- Name: index_projects_api_vis20_name; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX index_projects_api_vis20_name ON public.projects USING btree (name, id) WHERE (visibility_level = 20); + + +-- +-- Name: index_projects_api_vis20_path; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX index_projects_api_vis20_path ON public.projects USING btree (path, id) WHERE (visibility_level = 20); + + +-- +-- Name: index_projects_api_vis20_updated_at; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX index_projects_api_vis20_updated_at ON public.projects USING btree (updated_at, id) WHERE (visibility_level = 20); + + +-- +-- Name: index_projects_on_created_at_and_id; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX index_projects_on_created_at_and_id ON public.projects USING btree (created_at, id); + + +-- +-- Name: index_projects_on_creator_id_and_created_at_and_id; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX index_projects_on_creator_id_and_created_at_and_id ON public.projects USING btree (creator_id, created_at, id); + + +-- +-- Name: index_projects_on_creator_id_and_id; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX index_projects_on_creator_id_and_id ON public.projects USING btree (creator_id, id); + + +-- +-- Name: index_projects_on_description_trigram; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX index_projects_on_description_trigram ON public.projects USING gin (description public.gin_trgm_ops); + + +-- +-- Name: index_projects_on_id_and_archived_and_pending_delete; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX index_projects_on_id_and_archived_and_pending_delete ON public.projects USING btree (id) WHERE ((archived = false) AND (pending_delete = false)); + + +-- +-- Name: index_projects_on_id_partial_for_visibility; Type: INDEX; Schema: public; Owner: - +-- + +CREATE UNIQUE INDEX index_projects_on_id_partial_for_visibility ON public.projects USING btree (id) WHERE (visibility_level = ANY (ARRAY[10, 20])); + + +-- +-- Name: index_projects_on_id_service_desk_enabled; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX index_projects_on_id_service_desk_enabled ON public.projects USING btree (id) WHERE (service_desk_enabled = true); + + +-- +-- Name: index_projects_on_last_activity_at_and_id; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX index_projects_on_last_activity_at_and_id ON public.projects USING btree (last_activity_at, id); + + +-- +-- Name: index_projects_on_last_repository_check_at; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX index_projects_on_last_repository_check_at ON public.projects USING btree (last_repository_check_at) WHERE (last_repository_check_at IS NOT NULL); + + +-- +-- Name: index_projects_on_last_repository_check_failed; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX index_projects_on_last_repository_check_failed ON public.projects USING btree (last_repository_check_failed); + + +-- +-- Name: index_projects_on_last_repository_updated_at; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX index_projects_on_last_repository_updated_at ON public.projects USING btree (last_repository_updated_at); + + +-- +-- Name: index_projects_on_lower_name; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX index_projects_on_lower_name ON public.projects USING btree (lower((name)::text)); + + +-- +-- Name: index_projects_on_marked_for_deletion_at; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX index_projects_on_marked_for_deletion_at ON public.projects USING btree (marked_for_deletion_at) WHERE (marked_for_deletion_at IS NOT NULL); + + +-- +-- Name: index_projects_on_marked_for_deletion_by_user_id; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX index_projects_on_marked_for_deletion_by_user_id ON public.projects USING btree (marked_for_deletion_by_user_id) WHERE (marked_for_deletion_by_user_id IS NOT NULL); + + +-- +-- Name: index_projects_on_mirror_creator_id_created_at; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX index_projects_on_mirror_creator_id_created_at ON public.projects USING btree (creator_id, created_at) WHERE ((mirror = true) AND (mirror_trigger_builds = true)); + + +-- +-- Name: index_projects_on_mirror_id_where_mirror_and_trigger_builds; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX index_projects_on_mirror_id_where_mirror_and_trigger_builds ON public.projects USING btree (id) WHERE ((mirror = true) AND (mirror_trigger_builds = true)); + + +-- +-- Name: index_projects_on_mirror_last_successful_update_at; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX index_projects_on_mirror_last_successful_update_at ON public.projects USING btree (mirror_last_successful_update_at); + + +-- +-- Name: index_projects_on_mirror_user_id; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX index_projects_on_mirror_user_id ON public.projects USING btree (mirror_user_id); + + +-- +-- Name: index_projects_on_name_and_id; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX index_projects_on_name_and_id ON public.projects USING btree (name, id); + + +-- +-- Name: index_projects_on_name_trigram; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX index_projects_on_name_trigram ON public.projects USING gin (name public.gin_trgm_ops); + + +-- +-- Name: index_projects_on_namespace_id_and_id; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX index_projects_on_namespace_id_and_id ON public.projects USING btree (namespace_id, id); + + +-- +-- Name: index_projects_on_path_and_id; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX index_projects_on_path_and_id ON public.projects USING btree (path, id); + + +-- +-- Name: index_projects_on_path_trigram; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX index_projects_on_path_trigram ON public.projects USING gin (path public.gin_trgm_ops); + + +-- +-- Name: index_projects_on_pending_delete; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX index_projects_on_pending_delete ON public.projects USING btree (pending_delete); + + +-- +-- Name: index_projects_on_pool_repository_id; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX index_projects_on_pool_repository_id ON public.projects USING btree (pool_repository_id) WHERE (pool_repository_id IS NOT NULL); + + +-- +-- Name: index_projects_on_repository_storage; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX index_projects_on_repository_storage ON public.projects USING btree (repository_storage); + + +-- +-- Name: index_projects_on_runners_token; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX index_projects_on_runners_token ON public.projects USING btree (runners_token); + + +-- +-- Name: index_projects_on_runners_token_encrypted; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX index_projects_on_runners_token_encrypted ON public.projects USING btree (runners_token_encrypted); + + +-- +-- Name: index_projects_on_star_count; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX index_projects_on_star_count ON public.projects USING btree (star_count); + + +-- +-- Name: index_projects_on_updated_at_and_id; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX index_projects_on_updated_at_and_id ON public.projects USING btree (updated_at, id); + + +-- +-- Name: index_service_desk_enabled_projects_on_id_creator_id_created_at; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX index_service_desk_enabled_projects_on_id_creator_id_created_at ON public.projects USING btree (id, creator_id, created_at) WHERE (service_desk_enabled = true); + + +-- +-- Name: index_users_on_accepted_term_id; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX index_users_on_accepted_term_id ON public.users USING btree (accepted_term_id); + + +-- +-- Name: index_users_on_admin; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX index_users_on_admin ON public.users USING btree (admin); + + +-- +-- Name: index_users_on_bot_type; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX index_users_on_bot_type ON public.users USING btree (bot_type); + + +-- +-- Name: index_users_on_confirmation_token; Type: INDEX; Schema: public; Owner: - +-- + +CREATE UNIQUE INDEX index_users_on_confirmation_token ON public.users USING btree (confirmation_token); + + +-- +-- Name: index_users_on_created_at; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX index_users_on_created_at ON public.users USING btree (created_at); + + +-- +-- Name: index_users_on_email; Type: INDEX; Schema: public; Owner: - +-- + +CREATE UNIQUE INDEX index_users_on_email ON public.users USING btree (email); + + +-- +-- Name: index_users_on_email_trigram; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX index_users_on_email_trigram ON public.users USING gin (email public.gin_trgm_ops); + + +-- +-- Name: index_users_on_feed_token; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX index_users_on_feed_token ON public.users USING btree (feed_token); + + +-- +-- Name: index_users_on_ghost; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX index_users_on_ghost ON public.users USING btree (ghost); + + +-- +-- Name: index_users_on_group_view; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX index_users_on_group_view ON public.users USING btree (group_view); + + +-- +-- Name: index_users_on_incoming_email_token; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX index_users_on_incoming_email_token ON public.users USING btree (incoming_email_token); + + +-- +-- Name: index_users_on_managing_group_id; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX index_users_on_managing_group_id ON public.users USING btree (managing_group_id); + + +-- +-- Name: index_users_on_name; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX index_users_on_name ON public.users USING btree (name); + + +-- +-- Name: index_users_on_name_trigram; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX index_users_on_name_trigram ON public.users USING gin (name public.gin_trgm_ops); + + +-- +-- Name: index_users_on_public_email; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX index_users_on_public_email ON public.users USING btree (public_email) WHERE ((public_email)::text <> ''::text); + + +-- +-- Name: index_users_on_reset_password_token; Type: INDEX; Schema: public; Owner: - +-- + +CREATE UNIQUE INDEX index_users_on_reset_password_token ON public.users USING btree (reset_password_token); + + +-- +-- Name: index_users_on_state; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX index_users_on_state ON public.users USING btree (state); + + +-- +-- Name: index_users_on_state_and_user_type_internal; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX index_users_on_state_and_user_type_internal ON public.users USING btree (state, user_type) WHERE (ghost IS NOT TRUE); + + +-- +-- Name: index_users_on_static_object_token; Type: INDEX; Schema: public; Owner: - +-- + +CREATE UNIQUE INDEX index_users_on_static_object_token ON public.users USING btree (static_object_token); + + +-- +-- Name: index_users_on_unconfirmed_email; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX index_users_on_unconfirmed_email ON public.users USING btree (unconfirmed_email) WHERE (unconfirmed_email IS NOT NULL); + + +-- +-- Name: index_users_on_unlock_token; Type: INDEX; Schema: public; Owner: - +-- + +CREATE UNIQUE INDEX index_users_on_unlock_token ON public.users USING btree (unlock_token); + + +-- +-- Name: index_users_on_user_type; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX index_users_on_user_type ON public.users USING btree (user_type); + + +-- +-- Name: index_users_on_username; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX index_users_on_username ON public.users USING btree (username); + + +-- +-- Name: index_users_on_username_trigram; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX index_users_on_username_trigram ON public.users USING gin (username public.gin_trgm_ops); + + +-- +-- Name: tmp_idx_on_user_id_where_bio_is_filled; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX tmp_idx_on_user_id_where_bio_is_filled ON public.users USING btree (id) WHERE ((COALESCE(bio, ''::character varying))::text IS DISTINCT FROM ''::text); + + +-- +-- Name: projects fk_25d8780d11; Type: FK CONSTRAINT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY public.projects + ADD CONSTRAINT fk_25d8780d11 FOREIGN KEY (marked_for_deletion_by_user_id) REFERENCES public.users(id) ON DELETE SET NULL; + + +-- +-- Name: projects fk_6e5c14658a; Type: FK CONSTRAINT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY public.projects + ADD CONSTRAINT fk_6e5c14658a FOREIGN KEY (pool_repository_id) REFERENCES public.pool_repositories(id) ON DELETE SET NULL; + + +-- +-- Name: users fk_789cd90b35; Type: FK CONSTRAINT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY public.users + ADD CONSTRAINT fk_789cd90b35 FOREIGN KEY (accepted_term_id) REFERENCES public.application_setting_terms(id) ON DELETE CASCADE; + + +-- +-- Name: users fk_a4b8fefe3e; Type: FK CONSTRAINT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY public.users + ADD CONSTRAINT fk_a4b8fefe3e FOREIGN KEY (managing_group_id) REFERENCES public.namespaces(id) ON DELETE SET NULL; + + +-- +-- PostgreSQL database dump complete +-- + diff --git a/db/sharding/reference-tables/shard1.sql b/db/sharding/reference-tables/shard1.sql new file mode 100644 index 0000000000000000000000000000000000000000..2275f3abecb7820d70cac20d61664c64151ffce9 --- /dev/null +++ b/db/sharding/reference-tables/shard1.sql @@ -0,0 +1 @@ +CREATE SUBSCRIPTION test CONNECTION 'dbname=gitlabhq_development host=/home/abrandl-gl/workspace/gdk/postgresql' PUBLICATION test WITH (slot_name = 'shard1', create_slot = false); diff --git a/db/sharding/reference-tables/shard2.sql b/db/sharding/reference-tables/shard2.sql new file mode 100644 index 0000000000000000000000000000000000000000..641b0b67391814fc85ab94d04537a353308c8927 --- /dev/null +++ b/db/sharding/reference-tables/shard2.sql @@ -0,0 +1 @@ +CREATE SUBSCRIPTION test CONNECTION 'dbname=gitlabhq_development host=/home/abrandl-gl/workspace/gdk/postgresql' PUBLICATION test WITH (slot_name = 'shard2', create_slot = false); diff --git a/db/sharding/shard.rb b/db/sharding/shard.rb new file mode 100644 index 0000000000000000000000000000000000000000..80aece348b60dad978043a90014c9a1d4bf50af1 --- /dev/null +++ b/db/sharding/shard.rb @@ -0,0 +1,13 @@ +require 'erb' +template = ERB.new(File.read(File.join(__dir__, 'shards_template.sql'))) + +Dir.glob(File.join(__dir__, "shard?.sql")).each { |f| File.delete(f) } + +(0..7).each do |part_no| + shard = "shard#{(part_no % 2)+1}" + part = "issues_#{part_no}" + + File.open(File.join(__dir__, "#{shard}.sql"), "a+") do |io| + io << template.result(binding) + end +end \ No newline at end of file diff --git a/db/sharding/shard1.sql b/db/sharding/shard1.sql new file mode 100644 index 0000000000000000000000000000000000000000..3c84cec0b52f83f5286bafff356c04757e4068df --- /dev/null +++ b/db/sharding/shard1.sql @@ -0,0 +1,376 @@ +CREATE SCHEMA IF NOT EXISTS parts; +SET SEARCH_PATH=parts,public; + +CREATE TABLE issues_0 ( + id integer NOT NULL, + title character varying, + author_id integer, + project_id integer, + created_at timestamp without time zone, + updated_at timestamp without time zone, + description text, + milestone_id integer, + iid integer, + updated_by_id integer, + weight integer, + confidential boolean DEFAULT false NOT NULL, + due_date date, + moved_to_id integer, + lock_version integer DEFAULT 0, + title_html text, + description_html text, + time_estimate integer, + relative_position integer, + service_desk_reply_to character varying, + cached_markdown_version integer, + last_edited_at timestamp without time zone, + last_edited_by_id integer, + discussion_locked boolean, + closed_at timestamp with time zone, + closed_by_id integer, + state_id smallint DEFAULT 1 NOT NULL, + duplicated_to_id integer, + promoted_to_epic_id integer, + health_status smallint, + external_key character varying(255), + sprint_id bigint +); + +CREATE EXTENSION IF NOT EXISTS pg_trgm WITH SCHEMA public; + +-- +-- Name: issues issues_pkey; Type: CONSTRAINT; Schema: public; Owner: - +-- + +-- LIMITATION +-- ALTER TABLE issues_0 ADD CONSTRAINT issues_pkey PRIMARY KEY (project_id, id); + +CREATE INDEX idx_issues_0_on_health_status_not_null ON issues_0 USING btree (health_status) WHERE (health_status IS NOT NULL); +CREATE INDEX idx_issues_0_on_project_id_and_created_at_and_id_and_state_id ON issues_0 USING btree (project_id, created_at, id, state_id); +CREATE INDEX idx_issues_0_on_project_id_and_due_date_and_id_and_state_id ON issues_0 USING btree (project_id, due_date, id, state_id) WHERE (due_date IS NOT NULL); +CREATE INDEX idx_issues_0_on_project_id_and_rel_position_and_state_id_and_id ON issues_0 USING btree (project_id, relative_position, state_id, id DESC); +CREATE INDEX idx_issues_0_on_project_id_and_updated_at_and_id_and_state_id ON issues_0 USING btree (project_id, updated_at, id, state_id); +CREATE INDEX idx_issues_0_on_state_id ON issues_0 USING btree (state_id); +CREATE INDEX issues_0_on_author_id ON issues_0 USING btree (author_id); +CREATE INDEX issues_0_on_author_id_and_id_and_created_at ON issues_0 USING btree (author_id, id, created_at); +CREATE INDEX issues_0_on_closed_by_id ON issues_0 USING btree (closed_by_id); +CREATE INDEX issues_0_on_confidential ON issues_0 USING btree (confidential); +CREATE INDEX issues_0_on_description_trigram ON issues_0 USING gin (description public.gin_trgm_ops); +CREATE INDEX issues_0_on_duplicated_to_id ON issues_0 USING btree (duplicated_to_id) WHERE (duplicated_to_id IS NOT NULL); +CREATE INDEX issues_0_on_lock_version ON issues_0 USING btree (lock_version) WHERE (lock_version IS NULL); +CREATE INDEX issues_0_on_milestone_id ON issues_0 USING btree (milestone_id); +CREATE INDEX issues_0_on_moved_to_id ON issues_0 USING btree (moved_to_id) WHERE (moved_to_id IS NOT NULL); +CREATE UNIQUE INDEX issues_0_on_project_id_and_external_key ON issues_0 USING btree (project_id, external_key) WHERE (external_key IS NOT NULL); +CREATE UNIQUE INDEX issues_0_on_project_id_and_iid ON issues_0 USING btree (project_id, iid); +CREATE INDEX issues_0_on_promoted_to_epic_id ON issues_0 USING btree (promoted_to_epic_id) WHERE (promoted_to_epic_id IS NOT NULL); +CREATE INDEX issues_0_on_relative_position ON issues_0 USING btree (relative_position); +CREATE INDEX issues_0_on_sprint_id ON issues_0 USING btree (sprint_id); +CREATE INDEX issues_0_on_title_trigram ON issues_0 USING gin (title public.gin_trgm_ops); +CREATE INDEX issues_0_on_updated_at ON issues_0 USING btree (updated_at); +CREATE INDEX issues_0_on_updated_by_id ON issues_0 USING btree (updated_by_id) WHERE (updated_by_id IS NOT NULL); + + +-- LIMITATION +-- ALTER TABLE issues_0 ADD CONSTRAINT fk_issues_0_05f1e72feb FOREIGN KEY (author_id) REFERENCES public.users(id) ON DELETE SET NULL; + +-- LIMITATION + -- ALTER TABLE issues_0 ADD CONSTRAINT fk_issues_0_3b8c72ea56 FOREIGN KEY (sprint_id) REFERENCES public.sprints(id) ON DELETE CASCADE; + + +-- LIMITATION +-- ALTER TABLE issues_0 ADD CONSTRAINT fk_issues_0_899c8f3231 FOREIGN KEY (project_id) REFERENCES public.projects(id) ON DELETE CASCADE; + + +-- LIMITATION + -- ALTER TABLE issues_0 ADD CONSTRAINT fk_issues_0_96b1dd429c FOREIGN KEY (milestone_id) REFERENCES public.milestones(id) ON DELETE SET NULL; + +-- LIMITATION +-- ALTER TABLE issues_0 ADD CONSTRAINT fk_issues_0_c63cbf6c25 FOREIGN KEY (closed_by_id) REFERENCES public.users(id) ON DELETE SET NULL; + +-- LIMITATION +-- ALTER TABLE issues_0 ADD CONSTRAINT fk_issues_0_df75a7c8b8 FOREIGN KEY (promoted_to_epic_id) REFERENCES public.epics(id) ON DELETE SET NULL; + +-- LIMITATION +-- ALTER TABLE issues_0 ADD CONSTRAINT fk_issues_0_ffed080f01 FOREIGN KEY (updated_by_id) REFERENCES public.users(id) ON DELETE SET NULL; +CREATE SCHEMA IF NOT EXISTS parts; +SET SEARCH_PATH=parts,public; + +CREATE TABLE issues_2 ( + id integer NOT NULL, + title character varying, + author_id integer, + project_id integer, + created_at timestamp without time zone, + updated_at timestamp without time zone, + description text, + milestone_id integer, + iid integer, + updated_by_id integer, + weight integer, + confidential boolean DEFAULT false NOT NULL, + due_date date, + moved_to_id integer, + lock_version integer DEFAULT 0, + title_html text, + description_html text, + time_estimate integer, + relative_position integer, + service_desk_reply_to character varying, + cached_markdown_version integer, + last_edited_at timestamp without time zone, + last_edited_by_id integer, + discussion_locked boolean, + closed_at timestamp with time zone, + closed_by_id integer, + state_id smallint DEFAULT 1 NOT NULL, + duplicated_to_id integer, + promoted_to_epic_id integer, + health_status smallint, + external_key character varying(255), + sprint_id bigint +); + +CREATE EXTENSION IF NOT EXISTS pg_trgm WITH SCHEMA public; + +-- +-- Name: issues issues_pkey; Type: CONSTRAINT; Schema: public; Owner: - +-- + +-- LIMITATION +-- ALTER TABLE issues_2 ADD CONSTRAINT issues_pkey PRIMARY KEY (project_id, id); + +CREATE INDEX idx_issues_2_on_health_status_not_null ON issues_2 USING btree (health_status) WHERE (health_status IS NOT NULL); +CREATE INDEX idx_issues_2_on_project_id_and_created_at_and_id_and_state_id ON issues_2 USING btree (project_id, created_at, id, state_id); +CREATE INDEX idx_issues_2_on_project_id_and_due_date_and_id_and_state_id ON issues_2 USING btree (project_id, due_date, id, state_id) WHERE (due_date IS NOT NULL); +CREATE INDEX idx_issues_2_on_project_id_and_rel_position_and_state_id_and_id ON issues_2 USING btree (project_id, relative_position, state_id, id DESC); +CREATE INDEX idx_issues_2_on_project_id_and_updated_at_and_id_and_state_id ON issues_2 USING btree (project_id, updated_at, id, state_id); +CREATE INDEX idx_issues_2_on_state_id ON issues_2 USING btree (state_id); +CREATE INDEX issues_2_on_author_id ON issues_2 USING btree (author_id); +CREATE INDEX issues_2_on_author_id_and_id_and_created_at ON issues_2 USING btree (author_id, id, created_at); +CREATE INDEX issues_2_on_closed_by_id ON issues_2 USING btree (closed_by_id); +CREATE INDEX issues_2_on_confidential ON issues_2 USING btree (confidential); +CREATE INDEX issues_2_on_description_trigram ON issues_2 USING gin (description public.gin_trgm_ops); +CREATE INDEX issues_2_on_duplicated_to_id ON issues_2 USING btree (duplicated_to_id) WHERE (duplicated_to_id IS NOT NULL); +CREATE INDEX issues_2_on_lock_version ON issues_2 USING btree (lock_version) WHERE (lock_version IS NULL); +CREATE INDEX issues_2_on_milestone_id ON issues_2 USING btree (milestone_id); +CREATE INDEX issues_2_on_moved_to_id ON issues_2 USING btree (moved_to_id) WHERE (moved_to_id IS NOT NULL); +CREATE UNIQUE INDEX issues_2_on_project_id_and_external_key ON issues_2 USING btree (project_id, external_key) WHERE (external_key IS NOT NULL); +CREATE UNIQUE INDEX issues_2_on_project_id_and_iid ON issues_2 USING btree (project_id, iid); +CREATE INDEX issues_2_on_promoted_to_epic_id ON issues_2 USING btree (promoted_to_epic_id) WHERE (promoted_to_epic_id IS NOT NULL); +CREATE INDEX issues_2_on_relative_position ON issues_2 USING btree (relative_position); +CREATE INDEX issues_2_on_sprint_id ON issues_2 USING btree (sprint_id); +CREATE INDEX issues_2_on_title_trigram ON issues_2 USING gin (title public.gin_trgm_ops); +CREATE INDEX issues_2_on_updated_at ON issues_2 USING btree (updated_at); +CREATE INDEX issues_2_on_updated_by_id ON issues_2 USING btree (updated_by_id) WHERE (updated_by_id IS NOT NULL); + + +-- LIMITATION +-- ALTER TABLE issues_2 ADD CONSTRAINT fk_issues_2_05f1e72feb FOREIGN KEY (author_id) REFERENCES public.users(id) ON DELETE SET NULL; + +-- LIMITATION + -- ALTER TABLE issues_2 ADD CONSTRAINT fk_issues_2_3b8c72ea56 FOREIGN KEY (sprint_id) REFERENCES public.sprints(id) ON DELETE CASCADE; + + +-- LIMITATION +-- ALTER TABLE issues_2 ADD CONSTRAINT fk_issues_2_899c8f3231 FOREIGN KEY (project_id) REFERENCES public.projects(id) ON DELETE CASCADE; + + +-- LIMITATION + -- ALTER TABLE issues_2 ADD CONSTRAINT fk_issues_2_96b1dd429c FOREIGN KEY (milestone_id) REFERENCES public.milestones(id) ON DELETE SET NULL; + +-- LIMITATION +-- ALTER TABLE issues_2 ADD CONSTRAINT fk_issues_2_c63cbf6c25 FOREIGN KEY (closed_by_id) REFERENCES public.users(id) ON DELETE SET NULL; + +-- LIMITATION +-- ALTER TABLE issues_2 ADD CONSTRAINT fk_issues_2_df75a7c8b8 FOREIGN KEY (promoted_to_epic_id) REFERENCES public.epics(id) ON DELETE SET NULL; + +-- LIMITATION +-- ALTER TABLE issues_2 ADD CONSTRAINT fk_issues_2_ffed080f01 FOREIGN KEY (updated_by_id) REFERENCES public.users(id) ON DELETE SET NULL; +CREATE SCHEMA IF NOT EXISTS parts; +SET SEARCH_PATH=parts,public; + +CREATE TABLE issues_4 ( + id integer NOT NULL, + title character varying, + author_id integer, + project_id integer, + created_at timestamp without time zone, + updated_at timestamp without time zone, + description text, + milestone_id integer, + iid integer, + updated_by_id integer, + weight integer, + confidential boolean DEFAULT false NOT NULL, + due_date date, + moved_to_id integer, + lock_version integer DEFAULT 0, + title_html text, + description_html text, + time_estimate integer, + relative_position integer, + service_desk_reply_to character varying, + cached_markdown_version integer, + last_edited_at timestamp without time zone, + last_edited_by_id integer, + discussion_locked boolean, + closed_at timestamp with time zone, + closed_by_id integer, + state_id smallint DEFAULT 1 NOT NULL, + duplicated_to_id integer, + promoted_to_epic_id integer, + health_status smallint, + external_key character varying(255), + sprint_id bigint +); + +CREATE EXTENSION IF NOT EXISTS pg_trgm WITH SCHEMA public; + +-- +-- Name: issues issues_pkey; Type: CONSTRAINT; Schema: public; Owner: - +-- + +-- LIMITATION +-- ALTER TABLE issues_4 ADD CONSTRAINT issues_pkey PRIMARY KEY (project_id, id); + +CREATE INDEX idx_issues_4_on_health_status_not_null ON issues_4 USING btree (health_status) WHERE (health_status IS NOT NULL); +CREATE INDEX idx_issues_4_on_project_id_and_created_at_and_id_and_state_id ON issues_4 USING btree (project_id, created_at, id, state_id); +CREATE INDEX idx_issues_4_on_project_id_and_due_date_and_id_and_state_id ON issues_4 USING btree (project_id, due_date, id, state_id) WHERE (due_date IS NOT NULL); +CREATE INDEX idx_issues_4_on_project_id_and_rel_position_and_state_id_and_id ON issues_4 USING btree (project_id, relative_position, state_id, id DESC); +CREATE INDEX idx_issues_4_on_project_id_and_updated_at_and_id_and_state_id ON issues_4 USING btree (project_id, updated_at, id, state_id); +CREATE INDEX idx_issues_4_on_state_id ON issues_4 USING btree (state_id); +CREATE INDEX issues_4_on_author_id ON issues_4 USING btree (author_id); +CREATE INDEX issues_4_on_author_id_and_id_and_created_at ON issues_4 USING btree (author_id, id, created_at); +CREATE INDEX issues_4_on_closed_by_id ON issues_4 USING btree (closed_by_id); +CREATE INDEX issues_4_on_confidential ON issues_4 USING btree (confidential); +CREATE INDEX issues_4_on_description_trigram ON issues_4 USING gin (description public.gin_trgm_ops); +CREATE INDEX issues_4_on_duplicated_to_id ON issues_4 USING btree (duplicated_to_id) WHERE (duplicated_to_id IS NOT NULL); +CREATE INDEX issues_4_on_lock_version ON issues_4 USING btree (lock_version) WHERE (lock_version IS NULL); +CREATE INDEX issues_4_on_milestone_id ON issues_4 USING btree (milestone_id); +CREATE INDEX issues_4_on_moved_to_id ON issues_4 USING btree (moved_to_id) WHERE (moved_to_id IS NOT NULL); +CREATE UNIQUE INDEX issues_4_on_project_id_and_external_key ON issues_4 USING btree (project_id, external_key) WHERE (external_key IS NOT NULL); +CREATE UNIQUE INDEX issues_4_on_project_id_and_iid ON issues_4 USING btree (project_id, iid); +CREATE INDEX issues_4_on_promoted_to_epic_id ON issues_4 USING btree (promoted_to_epic_id) WHERE (promoted_to_epic_id IS NOT NULL); +CREATE INDEX issues_4_on_relative_position ON issues_4 USING btree (relative_position); +CREATE INDEX issues_4_on_sprint_id ON issues_4 USING btree (sprint_id); +CREATE INDEX issues_4_on_title_trigram ON issues_4 USING gin (title public.gin_trgm_ops); +CREATE INDEX issues_4_on_updated_at ON issues_4 USING btree (updated_at); +CREATE INDEX issues_4_on_updated_by_id ON issues_4 USING btree (updated_by_id) WHERE (updated_by_id IS NOT NULL); + + +-- LIMITATION +-- ALTER TABLE issues_4 ADD CONSTRAINT fk_issues_4_05f1e72feb FOREIGN KEY (author_id) REFERENCES public.users(id) ON DELETE SET NULL; + +-- LIMITATION + -- ALTER TABLE issues_4 ADD CONSTRAINT fk_issues_4_3b8c72ea56 FOREIGN KEY (sprint_id) REFERENCES public.sprints(id) ON DELETE CASCADE; + + +-- LIMITATION +-- ALTER TABLE issues_4 ADD CONSTRAINT fk_issues_4_899c8f3231 FOREIGN KEY (project_id) REFERENCES public.projects(id) ON DELETE CASCADE; + + +-- LIMITATION + -- ALTER TABLE issues_4 ADD CONSTRAINT fk_issues_4_96b1dd429c FOREIGN KEY (milestone_id) REFERENCES public.milestones(id) ON DELETE SET NULL; + +-- LIMITATION +-- ALTER TABLE issues_4 ADD CONSTRAINT fk_issues_4_c63cbf6c25 FOREIGN KEY (closed_by_id) REFERENCES public.users(id) ON DELETE SET NULL; + +-- LIMITATION +-- ALTER TABLE issues_4 ADD CONSTRAINT fk_issues_4_df75a7c8b8 FOREIGN KEY (promoted_to_epic_id) REFERENCES public.epics(id) ON DELETE SET NULL; + +-- LIMITATION +-- ALTER TABLE issues_4 ADD CONSTRAINT fk_issues_4_ffed080f01 FOREIGN KEY (updated_by_id) REFERENCES public.users(id) ON DELETE SET NULL; +CREATE SCHEMA IF NOT EXISTS parts; +SET SEARCH_PATH=parts,public; + +CREATE TABLE issues_6 ( + id integer NOT NULL, + title character varying, + author_id integer, + project_id integer, + created_at timestamp without time zone, + updated_at timestamp without time zone, + description text, + milestone_id integer, + iid integer, + updated_by_id integer, + weight integer, + confidential boolean DEFAULT false NOT NULL, + due_date date, + moved_to_id integer, + lock_version integer DEFAULT 0, + title_html text, + description_html text, + time_estimate integer, + relative_position integer, + service_desk_reply_to character varying, + cached_markdown_version integer, + last_edited_at timestamp without time zone, + last_edited_by_id integer, + discussion_locked boolean, + closed_at timestamp with time zone, + closed_by_id integer, + state_id smallint DEFAULT 1 NOT NULL, + duplicated_to_id integer, + promoted_to_epic_id integer, + health_status smallint, + external_key character varying(255), + sprint_id bigint +); + +CREATE EXTENSION IF NOT EXISTS pg_trgm WITH SCHEMA public; + +-- +-- Name: issues issues_pkey; Type: CONSTRAINT; Schema: public; Owner: - +-- + +-- LIMITATION +-- ALTER TABLE issues_6 ADD CONSTRAINT issues_pkey PRIMARY KEY (project_id, id); + +CREATE INDEX idx_issues_6_on_health_status_not_null ON issues_6 USING btree (health_status) WHERE (health_status IS NOT NULL); +CREATE INDEX idx_issues_6_on_project_id_and_created_at_and_id_and_state_id ON issues_6 USING btree (project_id, created_at, id, state_id); +CREATE INDEX idx_issues_6_on_project_id_and_due_date_and_id_and_state_id ON issues_6 USING btree (project_id, due_date, id, state_id) WHERE (due_date IS NOT NULL); +CREATE INDEX idx_issues_6_on_project_id_and_rel_position_and_state_id_and_id ON issues_6 USING btree (project_id, relative_position, state_id, id DESC); +CREATE INDEX idx_issues_6_on_project_id_and_updated_at_and_id_and_state_id ON issues_6 USING btree (project_id, updated_at, id, state_id); +CREATE INDEX idx_issues_6_on_state_id ON issues_6 USING btree (state_id); +CREATE INDEX issues_6_on_author_id ON issues_6 USING btree (author_id); +CREATE INDEX issues_6_on_author_id_and_id_and_created_at ON issues_6 USING btree (author_id, id, created_at); +CREATE INDEX issues_6_on_closed_by_id ON issues_6 USING btree (closed_by_id); +CREATE INDEX issues_6_on_confidential ON issues_6 USING btree (confidential); +CREATE INDEX issues_6_on_description_trigram ON issues_6 USING gin (description public.gin_trgm_ops); +CREATE INDEX issues_6_on_duplicated_to_id ON issues_6 USING btree (duplicated_to_id) WHERE (duplicated_to_id IS NOT NULL); +CREATE INDEX issues_6_on_lock_version ON issues_6 USING btree (lock_version) WHERE (lock_version IS NULL); +CREATE INDEX issues_6_on_milestone_id ON issues_6 USING btree (milestone_id); +CREATE INDEX issues_6_on_moved_to_id ON issues_6 USING btree (moved_to_id) WHERE (moved_to_id IS NOT NULL); +CREATE UNIQUE INDEX issues_6_on_project_id_and_external_key ON issues_6 USING btree (project_id, external_key) WHERE (external_key IS NOT NULL); +CREATE UNIQUE INDEX issues_6_on_project_id_and_iid ON issues_6 USING btree (project_id, iid); +CREATE INDEX issues_6_on_promoted_to_epic_id ON issues_6 USING btree (promoted_to_epic_id) WHERE (promoted_to_epic_id IS NOT NULL); +CREATE INDEX issues_6_on_relative_position ON issues_6 USING btree (relative_position); +CREATE INDEX issues_6_on_sprint_id ON issues_6 USING btree (sprint_id); +CREATE INDEX issues_6_on_title_trigram ON issues_6 USING gin (title public.gin_trgm_ops); +CREATE INDEX issues_6_on_updated_at ON issues_6 USING btree (updated_at); +CREATE INDEX issues_6_on_updated_by_id ON issues_6 USING btree (updated_by_id) WHERE (updated_by_id IS NOT NULL); + + +-- LIMITATION +-- ALTER TABLE issues_6 ADD CONSTRAINT fk_issues_6_05f1e72feb FOREIGN KEY (author_id) REFERENCES public.users(id) ON DELETE SET NULL; + +-- LIMITATION + -- ALTER TABLE issues_6 ADD CONSTRAINT fk_issues_6_3b8c72ea56 FOREIGN KEY (sprint_id) REFERENCES public.sprints(id) ON DELETE CASCADE; + + +-- LIMITATION +-- ALTER TABLE issues_6 ADD CONSTRAINT fk_issues_6_899c8f3231 FOREIGN KEY (project_id) REFERENCES public.projects(id) ON DELETE CASCADE; + + +-- LIMITATION + -- ALTER TABLE issues_6 ADD CONSTRAINT fk_issues_6_96b1dd429c FOREIGN KEY (milestone_id) REFERENCES public.milestones(id) ON DELETE SET NULL; + +-- LIMITATION +-- ALTER TABLE issues_6 ADD CONSTRAINT fk_issues_6_c63cbf6c25 FOREIGN KEY (closed_by_id) REFERENCES public.users(id) ON DELETE SET NULL; + +-- LIMITATION +-- ALTER TABLE issues_6 ADD CONSTRAINT fk_issues_6_df75a7c8b8 FOREIGN KEY (promoted_to_epic_id) REFERENCES public.epics(id) ON DELETE SET NULL; + +-- LIMITATION +-- ALTER TABLE issues_6 ADD CONSTRAINT fk_issues_6_ffed080f01 FOREIGN KEY (updated_by_id) REFERENCES public.users(id) ON DELETE SET NULL; diff --git a/db/sharding/shard2.sql b/db/sharding/shard2.sql new file mode 100644 index 0000000000000000000000000000000000000000..12178cc9d168a771f2073aba4ed7da07b1c531bc --- /dev/null +++ b/db/sharding/shard2.sql @@ -0,0 +1,376 @@ +CREATE SCHEMA IF NOT EXISTS parts; +SET SEARCH_PATH=parts,public; + +CREATE TABLE issues_1 ( + id integer NOT NULL, + title character varying, + author_id integer, + project_id integer, + created_at timestamp without time zone, + updated_at timestamp without time zone, + description text, + milestone_id integer, + iid integer, + updated_by_id integer, + weight integer, + confidential boolean DEFAULT false NOT NULL, + due_date date, + moved_to_id integer, + lock_version integer DEFAULT 0, + title_html text, + description_html text, + time_estimate integer, + relative_position integer, + service_desk_reply_to character varying, + cached_markdown_version integer, + last_edited_at timestamp without time zone, + last_edited_by_id integer, + discussion_locked boolean, + closed_at timestamp with time zone, + closed_by_id integer, + state_id smallint DEFAULT 1 NOT NULL, + duplicated_to_id integer, + promoted_to_epic_id integer, + health_status smallint, + external_key character varying(255), + sprint_id bigint +); + +CREATE EXTENSION IF NOT EXISTS pg_trgm WITH SCHEMA public; + +-- +-- Name: issues issues_pkey; Type: CONSTRAINT; Schema: public; Owner: - +-- + +-- LIMITATION +-- ALTER TABLE issues_1 ADD CONSTRAINT issues_pkey PRIMARY KEY (project_id, id); + +CREATE INDEX idx_issues_1_on_health_status_not_null ON issues_1 USING btree (health_status) WHERE (health_status IS NOT NULL); +CREATE INDEX idx_issues_1_on_project_id_and_created_at_and_id_and_state_id ON issues_1 USING btree (project_id, created_at, id, state_id); +CREATE INDEX idx_issues_1_on_project_id_and_due_date_and_id_and_state_id ON issues_1 USING btree (project_id, due_date, id, state_id) WHERE (due_date IS NOT NULL); +CREATE INDEX idx_issues_1_on_project_id_and_rel_position_and_state_id_and_id ON issues_1 USING btree (project_id, relative_position, state_id, id DESC); +CREATE INDEX idx_issues_1_on_project_id_and_updated_at_and_id_and_state_id ON issues_1 USING btree (project_id, updated_at, id, state_id); +CREATE INDEX idx_issues_1_on_state_id ON issues_1 USING btree (state_id); +CREATE INDEX issues_1_on_author_id ON issues_1 USING btree (author_id); +CREATE INDEX issues_1_on_author_id_and_id_and_created_at ON issues_1 USING btree (author_id, id, created_at); +CREATE INDEX issues_1_on_closed_by_id ON issues_1 USING btree (closed_by_id); +CREATE INDEX issues_1_on_confidential ON issues_1 USING btree (confidential); +CREATE INDEX issues_1_on_description_trigram ON issues_1 USING gin (description public.gin_trgm_ops); +CREATE INDEX issues_1_on_duplicated_to_id ON issues_1 USING btree (duplicated_to_id) WHERE (duplicated_to_id IS NOT NULL); +CREATE INDEX issues_1_on_lock_version ON issues_1 USING btree (lock_version) WHERE (lock_version IS NULL); +CREATE INDEX issues_1_on_milestone_id ON issues_1 USING btree (milestone_id); +CREATE INDEX issues_1_on_moved_to_id ON issues_1 USING btree (moved_to_id) WHERE (moved_to_id IS NOT NULL); +CREATE UNIQUE INDEX issues_1_on_project_id_and_external_key ON issues_1 USING btree (project_id, external_key) WHERE (external_key IS NOT NULL); +CREATE UNIQUE INDEX issues_1_on_project_id_and_iid ON issues_1 USING btree (project_id, iid); +CREATE INDEX issues_1_on_promoted_to_epic_id ON issues_1 USING btree (promoted_to_epic_id) WHERE (promoted_to_epic_id IS NOT NULL); +CREATE INDEX issues_1_on_relative_position ON issues_1 USING btree (relative_position); +CREATE INDEX issues_1_on_sprint_id ON issues_1 USING btree (sprint_id); +CREATE INDEX issues_1_on_title_trigram ON issues_1 USING gin (title public.gin_trgm_ops); +CREATE INDEX issues_1_on_updated_at ON issues_1 USING btree (updated_at); +CREATE INDEX issues_1_on_updated_by_id ON issues_1 USING btree (updated_by_id) WHERE (updated_by_id IS NOT NULL); + + +-- LIMITATION +-- ALTER TABLE issues_1 ADD CONSTRAINT fk_issues_1_05f1e72feb FOREIGN KEY (author_id) REFERENCES public.users(id) ON DELETE SET NULL; + +-- LIMITATION + -- ALTER TABLE issues_1 ADD CONSTRAINT fk_issues_1_3b8c72ea56 FOREIGN KEY (sprint_id) REFERENCES public.sprints(id) ON DELETE CASCADE; + + +-- LIMITATION +-- ALTER TABLE issues_1 ADD CONSTRAINT fk_issues_1_899c8f3231 FOREIGN KEY (project_id) REFERENCES public.projects(id) ON DELETE CASCADE; + + +-- LIMITATION + -- ALTER TABLE issues_1 ADD CONSTRAINT fk_issues_1_96b1dd429c FOREIGN KEY (milestone_id) REFERENCES public.milestones(id) ON DELETE SET NULL; + +-- LIMITATION +-- ALTER TABLE issues_1 ADD CONSTRAINT fk_issues_1_c63cbf6c25 FOREIGN KEY (closed_by_id) REFERENCES public.users(id) ON DELETE SET NULL; + +-- LIMITATION +-- ALTER TABLE issues_1 ADD CONSTRAINT fk_issues_1_df75a7c8b8 FOREIGN KEY (promoted_to_epic_id) REFERENCES public.epics(id) ON DELETE SET NULL; + +-- LIMITATION +-- ALTER TABLE issues_1 ADD CONSTRAINT fk_issues_1_ffed080f01 FOREIGN KEY (updated_by_id) REFERENCES public.users(id) ON DELETE SET NULL; +CREATE SCHEMA IF NOT EXISTS parts; +SET SEARCH_PATH=parts,public; + +CREATE TABLE issues_3 ( + id integer NOT NULL, + title character varying, + author_id integer, + project_id integer, + created_at timestamp without time zone, + updated_at timestamp without time zone, + description text, + milestone_id integer, + iid integer, + updated_by_id integer, + weight integer, + confidential boolean DEFAULT false NOT NULL, + due_date date, + moved_to_id integer, + lock_version integer DEFAULT 0, + title_html text, + description_html text, + time_estimate integer, + relative_position integer, + service_desk_reply_to character varying, + cached_markdown_version integer, + last_edited_at timestamp without time zone, + last_edited_by_id integer, + discussion_locked boolean, + closed_at timestamp with time zone, + closed_by_id integer, + state_id smallint DEFAULT 1 NOT NULL, + duplicated_to_id integer, + promoted_to_epic_id integer, + health_status smallint, + external_key character varying(255), + sprint_id bigint +); + +CREATE EXTENSION IF NOT EXISTS pg_trgm WITH SCHEMA public; + +-- +-- Name: issues issues_pkey; Type: CONSTRAINT; Schema: public; Owner: - +-- + +-- LIMITATION +-- ALTER TABLE issues_3 ADD CONSTRAINT issues_pkey PRIMARY KEY (project_id, id); + +CREATE INDEX idx_issues_3_on_health_status_not_null ON issues_3 USING btree (health_status) WHERE (health_status IS NOT NULL); +CREATE INDEX idx_issues_3_on_project_id_and_created_at_and_id_and_state_id ON issues_3 USING btree (project_id, created_at, id, state_id); +CREATE INDEX idx_issues_3_on_project_id_and_due_date_and_id_and_state_id ON issues_3 USING btree (project_id, due_date, id, state_id) WHERE (due_date IS NOT NULL); +CREATE INDEX idx_issues_3_on_project_id_and_rel_position_and_state_id_and_id ON issues_3 USING btree (project_id, relative_position, state_id, id DESC); +CREATE INDEX idx_issues_3_on_project_id_and_updated_at_and_id_and_state_id ON issues_3 USING btree (project_id, updated_at, id, state_id); +CREATE INDEX idx_issues_3_on_state_id ON issues_3 USING btree (state_id); +CREATE INDEX issues_3_on_author_id ON issues_3 USING btree (author_id); +CREATE INDEX issues_3_on_author_id_and_id_and_created_at ON issues_3 USING btree (author_id, id, created_at); +CREATE INDEX issues_3_on_closed_by_id ON issues_3 USING btree (closed_by_id); +CREATE INDEX issues_3_on_confidential ON issues_3 USING btree (confidential); +CREATE INDEX issues_3_on_description_trigram ON issues_3 USING gin (description public.gin_trgm_ops); +CREATE INDEX issues_3_on_duplicated_to_id ON issues_3 USING btree (duplicated_to_id) WHERE (duplicated_to_id IS NOT NULL); +CREATE INDEX issues_3_on_lock_version ON issues_3 USING btree (lock_version) WHERE (lock_version IS NULL); +CREATE INDEX issues_3_on_milestone_id ON issues_3 USING btree (milestone_id); +CREATE INDEX issues_3_on_moved_to_id ON issues_3 USING btree (moved_to_id) WHERE (moved_to_id IS NOT NULL); +CREATE UNIQUE INDEX issues_3_on_project_id_and_external_key ON issues_3 USING btree (project_id, external_key) WHERE (external_key IS NOT NULL); +CREATE UNIQUE INDEX issues_3_on_project_id_and_iid ON issues_3 USING btree (project_id, iid); +CREATE INDEX issues_3_on_promoted_to_epic_id ON issues_3 USING btree (promoted_to_epic_id) WHERE (promoted_to_epic_id IS NOT NULL); +CREATE INDEX issues_3_on_relative_position ON issues_3 USING btree (relative_position); +CREATE INDEX issues_3_on_sprint_id ON issues_3 USING btree (sprint_id); +CREATE INDEX issues_3_on_title_trigram ON issues_3 USING gin (title public.gin_trgm_ops); +CREATE INDEX issues_3_on_updated_at ON issues_3 USING btree (updated_at); +CREATE INDEX issues_3_on_updated_by_id ON issues_3 USING btree (updated_by_id) WHERE (updated_by_id IS NOT NULL); + + +-- LIMITATION +-- ALTER TABLE issues_3 ADD CONSTRAINT fk_issues_3_05f1e72feb FOREIGN KEY (author_id) REFERENCES public.users(id) ON DELETE SET NULL; + +-- LIMITATION + -- ALTER TABLE issues_3 ADD CONSTRAINT fk_issues_3_3b8c72ea56 FOREIGN KEY (sprint_id) REFERENCES public.sprints(id) ON DELETE CASCADE; + + +-- LIMITATION +-- ALTER TABLE issues_3 ADD CONSTRAINT fk_issues_3_899c8f3231 FOREIGN KEY (project_id) REFERENCES public.projects(id) ON DELETE CASCADE; + + +-- LIMITATION + -- ALTER TABLE issues_3 ADD CONSTRAINT fk_issues_3_96b1dd429c FOREIGN KEY (milestone_id) REFERENCES public.milestones(id) ON DELETE SET NULL; + +-- LIMITATION +-- ALTER TABLE issues_3 ADD CONSTRAINT fk_issues_3_c63cbf6c25 FOREIGN KEY (closed_by_id) REFERENCES public.users(id) ON DELETE SET NULL; + +-- LIMITATION +-- ALTER TABLE issues_3 ADD CONSTRAINT fk_issues_3_df75a7c8b8 FOREIGN KEY (promoted_to_epic_id) REFERENCES public.epics(id) ON DELETE SET NULL; + +-- LIMITATION +-- ALTER TABLE issues_3 ADD CONSTRAINT fk_issues_3_ffed080f01 FOREIGN KEY (updated_by_id) REFERENCES public.users(id) ON DELETE SET NULL; +CREATE SCHEMA IF NOT EXISTS parts; +SET SEARCH_PATH=parts,public; + +CREATE TABLE issues_5 ( + id integer NOT NULL, + title character varying, + author_id integer, + project_id integer, + created_at timestamp without time zone, + updated_at timestamp without time zone, + description text, + milestone_id integer, + iid integer, + updated_by_id integer, + weight integer, + confidential boolean DEFAULT false NOT NULL, + due_date date, + moved_to_id integer, + lock_version integer DEFAULT 0, + title_html text, + description_html text, + time_estimate integer, + relative_position integer, + service_desk_reply_to character varying, + cached_markdown_version integer, + last_edited_at timestamp without time zone, + last_edited_by_id integer, + discussion_locked boolean, + closed_at timestamp with time zone, + closed_by_id integer, + state_id smallint DEFAULT 1 NOT NULL, + duplicated_to_id integer, + promoted_to_epic_id integer, + health_status smallint, + external_key character varying(255), + sprint_id bigint +); + +CREATE EXTENSION IF NOT EXISTS pg_trgm WITH SCHEMA public; + +-- +-- Name: issues issues_pkey; Type: CONSTRAINT; Schema: public; Owner: - +-- + +-- LIMITATION +-- ALTER TABLE issues_5 ADD CONSTRAINT issues_pkey PRIMARY KEY (project_id, id); + +CREATE INDEX idx_issues_5_on_health_status_not_null ON issues_5 USING btree (health_status) WHERE (health_status IS NOT NULL); +CREATE INDEX idx_issues_5_on_project_id_and_created_at_and_id_and_state_id ON issues_5 USING btree (project_id, created_at, id, state_id); +CREATE INDEX idx_issues_5_on_project_id_and_due_date_and_id_and_state_id ON issues_5 USING btree (project_id, due_date, id, state_id) WHERE (due_date IS NOT NULL); +CREATE INDEX idx_issues_5_on_project_id_and_rel_position_and_state_id_and_id ON issues_5 USING btree (project_id, relative_position, state_id, id DESC); +CREATE INDEX idx_issues_5_on_project_id_and_updated_at_and_id_and_state_id ON issues_5 USING btree (project_id, updated_at, id, state_id); +CREATE INDEX idx_issues_5_on_state_id ON issues_5 USING btree (state_id); +CREATE INDEX issues_5_on_author_id ON issues_5 USING btree (author_id); +CREATE INDEX issues_5_on_author_id_and_id_and_created_at ON issues_5 USING btree (author_id, id, created_at); +CREATE INDEX issues_5_on_closed_by_id ON issues_5 USING btree (closed_by_id); +CREATE INDEX issues_5_on_confidential ON issues_5 USING btree (confidential); +CREATE INDEX issues_5_on_description_trigram ON issues_5 USING gin (description public.gin_trgm_ops); +CREATE INDEX issues_5_on_duplicated_to_id ON issues_5 USING btree (duplicated_to_id) WHERE (duplicated_to_id IS NOT NULL); +CREATE INDEX issues_5_on_lock_version ON issues_5 USING btree (lock_version) WHERE (lock_version IS NULL); +CREATE INDEX issues_5_on_milestone_id ON issues_5 USING btree (milestone_id); +CREATE INDEX issues_5_on_moved_to_id ON issues_5 USING btree (moved_to_id) WHERE (moved_to_id IS NOT NULL); +CREATE UNIQUE INDEX issues_5_on_project_id_and_external_key ON issues_5 USING btree (project_id, external_key) WHERE (external_key IS NOT NULL); +CREATE UNIQUE INDEX issues_5_on_project_id_and_iid ON issues_5 USING btree (project_id, iid); +CREATE INDEX issues_5_on_promoted_to_epic_id ON issues_5 USING btree (promoted_to_epic_id) WHERE (promoted_to_epic_id IS NOT NULL); +CREATE INDEX issues_5_on_relative_position ON issues_5 USING btree (relative_position); +CREATE INDEX issues_5_on_sprint_id ON issues_5 USING btree (sprint_id); +CREATE INDEX issues_5_on_title_trigram ON issues_5 USING gin (title public.gin_trgm_ops); +CREATE INDEX issues_5_on_updated_at ON issues_5 USING btree (updated_at); +CREATE INDEX issues_5_on_updated_by_id ON issues_5 USING btree (updated_by_id) WHERE (updated_by_id IS NOT NULL); + + +-- LIMITATION +-- ALTER TABLE issues_5 ADD CONSTRAINT fk_issues_5_05f1e72feb FOREIGN KEY (author_id) REFERENCES public.users(id) ON DELETE SET NULL; + +-- LIMITATION + -- ALTER TABLE issues_5 ADD CONSTRAINT fk_issues_5_3b8c72ea56 FOREIGN KEY (sprint_id) REFERENCES public.sprints(id) ON DELETE CASCADE; + + +-- LIMITATION +-- ALTER TABLE issues_5 ADD CONSTRAINT fk_issues_5_899c8f3231 FOREIGN KEY (project_id) REFERENCES public.projects(id) ON DELETE CASCADE; + + +-- LIMITATION + -- ALTER TABLE issues_5 ADD CONSTRAINT fk_issues_5_96b1dd429c FOREIGN KEY (milestone_id) REFERENCES public.milestones(id) ON DELETE SET NULL; + +-- LIMITATION +-- ALTER TABLE issues_5 ADD CONSTRAINT fk_issues_5_c63cbf6c25 FOREIGN KEY (closed_by_id) REFERENCES public.users(id) ON DELETE SET NULL; + +-- LIMITATION +-- ALTER TABLE issues_5 ADD CONSTRAINT fk_issues_5_df75a7c8b8 FOREIGN KEY (promoted_to_epic_id) REFERENCES public.epics(id) ON DELETE SET NULL; + +-- LIMITATION +-- ALTER TABLE issues_5 ADD CONSTRAINT fk_issues_5_ffed080f01 FOREIGN KEY (updated_by_id) REFERENCES public.users(id) ON DELETE SET NULL; +CREATE SCHEMA IF NOT EXISTS parts; +SET SEARCH_PATH=parts,public; + +CREATE TABLE issues_7 ( + id integer NOT NULL, + title character varying, + author_id integer, + project_id integer, + created_at timestamp without time zone, + updated_at timestamp without time zone, + description text, + milestone_id integer, + iid integer, + updated_by_id integer, + weight integer, + confidential boolean DEFAULT false NOT NULL, + due_date date, + moved_to_id integer, + lock_version integer DEFAULT 0, + title_html text, + description_html text, + time_estimate integer, + relative_position integer, + service_desk_reply_to character varying, + cached_markdown_version integer, + last_edited_at timestamp without time zone, + last_edited_by_id integer, + discussion_locked boolean, + closed_at timestamp with time zone, + closed_by_id integer, + state_id smallint DEFAULT 1 NOT NULL, + duplicated_to_id integer, + promoted_to_epic_id integer, + health_status smallint, + external_key character varying(255), + sprint_id bigint +); + +CREATE EXTENSION IF NOT EXISTS pg_trgm WITH SCHEMA public; + +-- +-- Name: issues issues_pkey; Type: CONSTRAINT; Schema: public; Owner: - +-- + +-- LIMITATION +-- ALTER TABLE issues_7 ADD CONSTRAINT issues_pkey PRIMARY KEY (project_id, id); + +CREATE INDEX idx_issues_7_on_health_status_not_null ON issues_7 USING btree (health_status) WHERE (health_status IS NOT NULL); +CREATE INDEX idx_issues_7_on_project_id_and_created_at_and_id_and_state_id ON issues_7 USING btree (project_id, created_at, id, state_id); +CREATE INDEX idx_issues_7_on_project_id_and_due_date_and_id_and_state_id ON issues_7 USING btree (project_id, due_date, id, state_id) WHERE (due_date IS NOT NULL); +CREATE INDEX idx_issues_7_on_project_id_and_rel_position_and_state_id_and_id ON issues_7 USING btree (project_id, relative_position, state_id, id DESC); +CREATE INDEX idx_issues_7_on_project_id_and_updated_at_and_id_and_state_id ON issues_7 USING btree (project_id, updated_at, id, state_id); +CREATE INDEX idx_issues_7_on_state_id ON issues_7 USING btree (state_id); +CREATE INDEX issues_7_on_author_id ON issues_7 USING btree (author_id); +CREATE INDEX issues_7_on_author_id_and_id_and_created_at ON issues_7 USING btree (author_id, id, created_at); +CREATE INDEX issues_7_on_closed_by_id ON issues_7 USING btree (closed_by_id); +CREATE INDEX issues_7_on_confidential ON issues_7 USING btree (confidential); +CREATE INDEX issues_7_on_description_trigram ON issues_7 USING gin (description public.gin_trgm_ops); +CREATE INDEX issues_7_on_duplicated_to_id ON issues_7 USING btree (duplicated_to_id) WHERE (duplicated_to_id IS NOT NULL); +CREATE INDEX issues_7_on_lock_version ON issues_7 USING btree (lock_version) WHERE (lock_version IS NULL); +CREATE INDEX issues_7_on_milestone_id ON issues_7 USING btree (milestone_id); +CREATE INDEX issues_7_on_moved_to_id ON issues_7 USING btree (moved_to_id) WHERE (moved_to_id IS NOT NULL); +CREATE UNIQUE INDEX issues_7_on_project_id_and_external_key ON issues_7 USING btree (project_id, external_key) WHERE (external_key IS NOT NULL); +CREATE UNIQUE INDEX issues_7_on_project_id_and_iid ON issues_7 USING btree (project_id, iid); +CREATE INDEX issues_7_on_promoted_to_epic_id ON issues_7 USING btree (promoted_to_epic_id) WHERE (promoted_to_epic_id IS NOT NULL); +CREATE INDEX issues_7_on_relative_position ON issues_7 USING btree (relative_position); +CREATE INDEX issues_7_on_sprint_id ON issues_7 USING btree (sprint_id); +CREATE INDEX issues_7_on_title_trigram ON issues_7 USING gin (title public.gin_trgm_ops); +CREATE INDEX issues_7_on_updated_at ON issues_7 USING btree (updated_at); +CREATE INDEX issues_7_on_updated_by_id ON issues_7 USING btree (updated_by_id) WHERE (updated_by_id IS NOT NULL); + + +-- LIMITATION +-- ALTER TABLE issues_7 ADD CONSTRAINT fk_issues_7_05f1e72feb FOREIGN KEY (author_id) REFERENCES public.users(id) ON DELETE SET NULL; + +-- LIMITATION + -- ALTER TABLE issues_7 ADD CONSTRAINT fk_issues_7_3b8c72ea56 FOREIGN KEY (sprint_id) REFERENCES public.sprints(id) ON DELETE CASCADE; + + +-- LIMITATION +-- ALTER TABLE issues_7 ADD CONSTRAINT fk_issues_7_899c8f3231 FOREIGN KEY (project_id) REFERENCES public.projects(id) ON DELETE CASCADE; + + +-- LIMITATION + -- ALTER TABLE issues_7 ADD CONSTRAINT fk_issues_7_96b1dd429c FOREIGN KEY (milestone_id) REFERENCES public.milestones(id) ON DELETE SET NULL; + +-- LIMITATION +-- ALTER TABLE issues_7 ADD CONSTRAINT fk_issues_7_c63cbf6c25 FOREIGN KEY (closed_by_id) REFERENCES public.users(id) ON DELETE SET NULL; + +-- LIMITATION +-- ALTER TABLE issues_7 ADD CONSTRAINT fk_issues_7_df75a7c8b8 FOREIGN KEY (promoted_to_epic_id) REFERENCES public.epics(id) ON DELETE SET NULL; + +-- LIMITATION +-- ALTER TABLE issues_7 ADD CONSTRAINT fk_issues_7_ffed080f01 FOREIGN KEY (updated_by_id) REFERENCES public.users(id) ON DELETE SET NULL; diff --git a/db/sharding/shards_template.sql b/db/sharding/shards_template.sql new file mode 100644 index 0000000000000000000000000000000000000000..81977cb144f0f53fd5cf13932cccd39bdcecf773 --- /dev/null +++ b/db/sharding/shards_template.sql @@ -0,0 +1,94 @@ +CREATE SCHEMA IF NOT EXISTS parts; +SET SEARCH_PATH=parts,public; + +CREATE TABLE <%= part %> ( + id integer NOT NULL, + title character varying, + author_id integer, + project_id integer, + created_at timestamp without time zone, + updated_at timestamp without time zone, + description text, + milestone_id integer, + iid integer, + updated_by_id integer, + weight integer, + confidential boolean DEFAULT false NOT NULL, + due_date date, + moved_to_id integer, + lock_version integer DEFAULT 0, + title_html text, + description_html text, + time_estimate integer, + relative_position integer, + service_desk_reply_to character varying, + cached_markdown_version integer, + last_edited_at timestamp without time zone, + last_edited_by_id integer, + discussion_locked boolean, + closed_at timestamp with time zone, + closed_by_id integer, + state_id smallint DEFAULT 1 NOT NULL, + duplicated_to_id integer, + promoted_to_epic_id integer, + health_status smallint, + external_key character varying(255), + sprint_id bigint +); + +CREATE EXTENSION IF NOT EXISTS pg_trgm WITH SCHEMA public; + +-- +-- Name: issues issues_pkey; Type: CONSTRAINT; Schema: public; Owner: - +-- + +-- LIMITATION +-- ALTER TABLE <%= part %> ADD CONSTRAINT issues_pkey PRIMARY KEY (project_id, id); + +CREATE INDEX idx_<%= part %>_on_health_status_not_null ON <%= part %> USING btree (health_status) WHERE (health_status IS NOT NULL); +CREATE INDEX idx_<%= part %>_on_project_id_and_created_at_and_id_and_state_id ON <%= part %> USING btree (project_id, created_at, id, state_id); +CREATE INDEX idx_<%= part %>_on_project_id_and_due_date_and_id_and_state_id ON <%= part %> USING btree (project_id, due_date, id, state_id) WHERE (due_date IS NOT NULL); +CREATE INDEX idx_<%= part %>_on_project_id_and_rel_position_and_state_id_and_id ON <%= part %> USING btree (project_id, relative_position, state_id, id DESC); +CREATE INDEX idx_<%= part %>_on_project_id_and_updated_at_and_id_and_state_id ON <%= part %> USING btree (project_id, updated_at, id, state_id); +CREATE INDEX idx_<%= part %>_on_state_id ON <%= part %> USING btree (state_id); +CREATE INDEX <%= part %>_on_author_id ON <%= part %> USING btree (author_id); +CREATE INDEX <%= part %>_on_author_id_and_id_and_created_at ON <%= part %> USING btree (author_id, id, created_at); +CREATE INDEX <%= part %>_on_closed_by_id ON <%= part %> USING btree (closed_by_id); +CREATE INDEX <%= part %>_on_confidential ON <%= part %> USING btree (confidential); +CREATE INDEX <%= part %>_on_description_trigram ON <%= part %> USING gin (description public.gin_trgm_ops); +CREATE INDEX <%= part %>_on_duplicated_to_id ON <%= part %> USING btree (duplicated_to_id) WHERE (duplicated_to_id IS NOT NULL); +CREATE INDEX <%= part %>_on_lock_version ON <%= part %> USING btree (lock_version) WHERE (lock_version IS NULL); +CREATE INDEX <%= part %>_on_milestone_id ON <%= part %> USING btree (milestone_id); +CREATE INDEX <%= part %>_on_moved_to_id ON <%= part %> USING btree (moved_to_id) WHERE (moved_to_id IS NOT NULL); +CREATE UNIQUE INDEX <%= part %>_on_project_id_and_external_key ON <%= part %> USING btree (project_id, external_key) WHERE (external_key IS NOT NULL); +CREATE UNIQUE INDEX <%= part %>_on_project_id_and_iid ON <%= part %> USING btree (project_id, iid); +CREATE INDEX <%= part %>_on_promoted_to_epic_id ON <%= part %> USING btree (promoted_to_epic_id) WHERE (promoted_to_epic_id IS NOT NULL); +CREATE INDEX <%= part %>_on_relative_position ON <%= part %> USING btree (relative_position); +CREATE INDEX <%= part %>_on_sprint_id ON <%= part %> USING btree (sprint_id); +CREATE INDEX <%= part %>_on_title_trigram ON <%= part %> USING gin (title public.gin_trgm_ops); +CREATE INDEX <%= part %>_on_updated_at ON <%= part %> USING btree (updated_at); +CREATE INDEX <%= part %>_on_updated_by_id ON <%= part %> USING btree (updated_by_id) WHERE (updated_by_id IS NOT NULL); + + +-- LIMITATION +-- ALTER TABLE <%= part %> ADD CONSTRAINT fk_<%= part %>_05f1e72feb FOREIGN KEY (author_id) REFERENCES public.users(id) ON DELETE SET NULL; + +-- LIMITATION + -- ALTER TABLE <%= part %> ADD CONSTRAINT fk_<%= part %>_3b8c72ea56 FOREIGN KEY (sprint_id) REFERENCES public.sprints(id) ON DELETE CASCADE; + + +-- LIMITATION +-- ALTER TABLE <%= part %> ADD CONSTRAINT fk_<%= part %>_899c8f3231 FOREIGN KEY (project_id) REFERENCES public.projects(id) ON DELETE CASCADE; + + +-- LIMITATION + -- ALTER TABLE <%= part %> ADD CONSTRAINT fk_<%= part %>_96b1dd429c FOREIGN KEY (milestone_id) REFERENCES public.milestones(id) ON DELETE SET NULL; + +-- LIMITATION +-- ALTER TABLE <%= part %> ADD CONSTRAINT fk_<%= part %>_c63cbf6c25 FOREIGN KEY (closed_by_id) REFERENCES public.users(id) ON DELETE SET NULL; + +-- LIMITATION +-- ALTER TABLE <%= part %> ADD CONSTRAINT fk_<%= part %>_df75a7c8b8 FOREIGN KEY (promoted_to_epic_id) REFERENCES public.epics(id) ON DELETE SET NULL; + +-- LIMITATION +-- ALTER TABLE <%= part %> ADD CONSTRAINT fk_<%= part %>_ffed080f01 FOREIGN KEY (updated_by_id) REFERENCES public.users(id) ON DELETE SET NULL;