Differential D8100 Diff 29249 swh/provenance/sql/60-indexes.sql

Changeset View

Standalone View

swh/provenance/sql/60-indexes.sql

	-- psql variables to get the current database flavor			-- psql variables to get the current database flavor
	select position('denormalized' in swh_get_dbflavor()::text) = 0 as dbflavor_norm \gset			select position('denormalized' in swh_get_dbflavor()::text) = 0 as dbflavor_norm \gset

	-- create unique indexes (instead of pkey) because location might be null for			-- create unique indexes (instead of pkey) because location might be null for
	-- the without-path flavor			-- the without-path flavor
	\if :dbflavor_norm			\if :dbflavor_norm
	create unique index on content_in_revision(content, revision, location);			create unique index on content_in_revision(content, revision, location);
	create unique index on directory_in_revision(directory, revision, location);			create unique index on directory_in_revision(directory, revision, location);
	create unique index on content_in_directory(content, directory, location);			create unique index on content_in_directory(content, directory, location);
	\else			\else
	create unique index on content_in_revision(content);			create unique index on content_in_revision(content);
	create unique index on directory_in_revision(directory);			create unique index on directory_in_revision(directory);
	create unique index on content_in_directory(content);			create unique index on content_in_directory(content);
	\endif			\endif

	create unique index on location(digest(path, 'sha1'));			create unique index on location(digest(path, 'sha1'));
				create index on directory(sha1) where flat=false;

	alter table revision_in_origin add primary key (revision, origin);			alter table revision_in_origin add primary key (revision, origin);
	alter table revision_before_revision add primary key (prev, next);			alter table revision_before_revision add primary key (prev, next);