Differential D3802 Diff 13421 swh/storage/sql/60-swh-indexes.sql

Changeset View

Standalone View

swh/storage/sql/60-swh-indexes.sql

	Show First 20 Lines • Show All 75 Lines • ▼ Show 20 Lines
	create unique index concurrently revision_pkey on revision(id);			create unique index concurrently revision_pkey on revision(id);
	alter table revision add primary key using index revision_pkey;			alter table revision add primary key using index revision_pkey;

	alter table revision add constraint revision_author_fkey foreign key (author) references person(id) not valid;			alter table revision add constraint revision_author_fkey foreign key (author) references person(id) not valid;
	alter table revision validate constraint revision_author_fkey;			alter table revision validate constraint revision_author_fkey;
	alter table revision add constraint revision_committer_fkey foreign key (committer) references person(id) not valid;			alter table revision add constraint revision_committer_fkey foreign key (committer) references person(id) not valid;
	alter table revision validate constraint revision_committer_fkey;			alter table revision validate constraint revision_committer_fkey;

				alter table revision
				add constraint revision_date_neg_utc_offset_not_null
				check (date is null or date_neg_utc_offset is not null)
				not valid;
				alter table revision
				add constraint revision_committer_date_neg_utc_offset_not_null
				check (committer_date is null or committer_date_neg_utc_offset is not null)
				not valid;

				alter table revision
				validate constraint revision_date_neg_utc_offset_not_null;
				alter table revision
				validate constraint revision_committer_date_neg_utc_offset_not_null;

	create index concurrently on revision(directory);			create index concurrently on revision(directory);
	create unique index concurrently on revision(object_id);			create unique index concurrently on revision(object_id);

	-- revision_history			-- revision_history
	create unique index concurrently revision_history_pkey on revision_history(id, parent_rank);			create unique index concurrently revision_history_pkey on revision_history(id, parent_rank);
	alter table revision_history add primary key using index revision_history_pkey;			alter table revision_history add primary key using index revision_history_pkey;

	create index concurrently on revision_history(parent_id);			create index concurrently on revision_history(parent_id);
	▲ Show 20 Lines • Show All 55 Lines • ▼ Show 20 Lines
	alter table release add primary key using index release_pkey;			alter table release add primary key using index release_pkey;

	create index concurrently on release(target, target_type);			create index concurrently on release(target, target_type);
	create unique index concurrently on release(object_id);			create unique index concurrently on release(object_id);

	alter table release add constraint release_author_fkey foreign key (author) references person(id) not valid;			alter table release add constraint release_author_fkey foreign key (author) references person(id) not valid;
	alter table release validate constraint release_author_fkey;			alter table release validate constraint release_author_fkey;

				alter table release
				add constraint release_date_neg_utc_offset_not_null
				check (date is null or date_neg_utc_offset is not null)
				not valid;

				alter table release
				validate constraint release_date_neg_utc_offset_not_null;

	-- if the author is null, then the date must be null			-- if the author is null, then the date must be null
	alter table release add constraint release_author_date_check check ((date is null) or (author is not null)) not valid;			alter table release add constraint release_author_date_check check ((date is null) or (author is not null)) not valid;
	alter table release validate constraint release_author_date_check;			alter table release validate constraint release_author_date_check;

	-- metadata_fetcher			-- metadata_fetcher
	create unique index metadata_fetcher_pkey on metadata_fetcher(id);			create unique index metadata_fetcher_pkey on metadata_fetcher(id);
	alter table metadata_fetcher add primary key using index metadata_fetcher_pkey;			alter table metadata_fetcher add primary key using index metadata_fetcher_pkey;

	Show All 24 Lines