Differential D8248 Diff 29790 swh/scrubber/sql/60-indexes.sql

Changeset View

Standalone View

swh/scrubber/sql/60-indexes.sql

				-------------------------------------
				-- Shared tables
				-------------------------------------

	-- datastore			-- datastore

	create unique index concurrently datastore_pkey on datastore(id);			create unique index concurrently datastore_pkey on datastore(id);
	alter table datastore add primary key using index datastore_pkey;			alter table datastore add primary key using index datastore_pkey;

	create unique index concurrently datastore_package_class_instance on datastore(package, class, instance);			create unique index concurrently datastore_package_class_instance on datastore(package, class, instance);


				-------------------------------------
				-- Inventory of objects with issues
				-------------------------------------

	-- corrupt_object			-- corrupt_object

	alter table corrupt_object add constraint corrupt_object_datastore_fkey foreign key (datastore) references datastore(id) not valid;			alter table corrupt_object add constraint corrupt_object_datastore_fkey foreign key (datastore) references datastore(id) not valid;
	alter table corrupt_object validate constraint corrupt_object_datastore_fkey;			alter table corrupt_object validate constraint corrupt_object_datastore_fkey;

	create unique index concurrently corrupt_object_pkey on corrupt_object(id, datastore);			create unique index concurrently corrupt_object_pkey on corrupt_object(id, datastore);
	alter table corrupt_object add primary key using index corrupt_object_pkey;			alter table corrupt_object add primary key using index corrupt_object_pkey;


				-- missing_object

				alter table missing_object add constraint missing_object_datastore_fkey foreign key (datastore) references datastore(id) not valid;
				alter table missing_object validate constraint missing_object_datastore_fkey;

				create unique index concurrently missing_object_pkey on missing_object(id, datastore);
				alter table missing_object add primary key using index missing_object_pkey;


				-- missing_object_reference

				alter table missing_object_reference add constraint missing_object_reference_datastore_fkey foreign key (datastore) references datastore(id) not valid;
				alter table missing_object_reference validate constraint missing_object_reference_datastore_fkey;

				create unique index concurrently missing_object_reference_missing_id_reference_id_datastore on missing_object_reference(missing_id, reference_id, datastore);
				create unique index concurrently missing_object_reference_reference_id_missing_id_datastore on missing_object_reference(reference_id, missing_id, datastore);

				-------------------------------------
				-- Issue resolution
				-------------------------------------

	-- object_origin			-- object_origin

	create unique index concurrently object_origin_pkey on object_origin (object_id, origin_url);			create unique index concurrently object_origin_pkey on object_origin (object_id, origin_url);
	create index concurrently object_origin_by_origin on object_origin (origin_url, object_id);			create index concurrently object_origin_by_origin on object_origin (origin_url, object_id);

	-- FIXME: not valid, because corrupt_object(id) is not unique			-- FIXME: not valid, because corrupt_object(id) is not unique
	-- alter table object_origin add constraint object_origin_object_fkey foreign key (object_id) references corrupt_object(id) not valid;			-- alter table object_origin add constraint object_origin_object_fkey foreign key (object_id) references corrupt_object(id) not valid;
	-- alter table object_origin validate constraint object_origin_object_fkey;			-- alter table object_origin validate constraint object_origin_object_fkey;

	-- fixed_object			-- fixed_object

	create unique index concurrently fixed_object_pkey on fixed_object(id);			create unique index concurrently fixed_object_pkey on fixed_object(id);
	alter table fixed_object add primary key using index fixed_object_pkey;			alter table fixed_object add primary key using index fixed_object_pkey;