diff --git a/sql/upgrades/164.sql b/sql/upgrades/164.sql new file mode 100644 --- /dev/null +++ b/sql/upgrades/164.sql @@ -0,0 +1,9 @@ +-- SWH DB schema upgrade +-- from_version: 163 +-- to_version: 164 +-- description: rename raw_extrinsic_metadata.id to raw_extrinsic_metadata.target + +insert into dbversion(version, release, description) + values(164, now(), 'Work In Progress'); + +alter table raw_extrinsic_metadata rename id to target; diff --git a/swh/storage/backfill.py b/swh/storage/backfill.py --- a/swh/storage/backfill.py +++ b/swh/storage/backfill.py @@ -46,7 +46,7 @@ "directory": "id", "metadata_authority": "type, url", "metadata_fetcher": "name, version", - "raw_extrinsic_metadata": "id", + "raw_extrinsic_metadata": "target", "revision": "revision.id", "release": "release.id", "snapshot": "id", @@ -80,7 +80,7 @@ "metadata_fetcher": ["name", "version", "metadata",], "raw_extrinsic_metadata": [ "raw_extrinsic_metadata.type", - "raw_extrinsic_metadata.id", + "raw_extrinsic_metadata.target", "metadata_authority.type", "metadata_authority.url", "metadata_fetcher.name", diff --git a/swh/storage/postgresql/converters.py b/swh/storage/postgresql/converters.py --- a/swh/storage/postgresql/converters.py +++ b/swh/storage/postgresql/converters.py @@ -296,7 +296,7 @@ def db_to_raw_extrinsic_metadata(row) -> RawExtrinsicMetadata: type_ = MetadataTargetType(row["raw_extrinsic_metadata.type"]) - target = row["raw_extrinsic_metadata.id"] + target = row["raw_extrinsic_metadata.target"] if type_ != MetadataTargetType.ORIGIN: target = parse_swhid(target) return RawExtrinsicMetadata( diff --git a/swh/storage/postgresql/db.py b/swh/storage/postgresql/db.py --- a/swh/storage/postgresql/db.py +++ b/swh/storage/postgresql/db.py @@ -28,7 +28,7 @@ """ - current_version = 163 + current_version = 164 def mktemp_dir_entry(self, entry_type, cur=None): self._cursor(cur).execute( @@ -1133,7 +1133,7 @@ _raw_extrinsic_metadata_insert_cols = [ "type", - "id", + "target", "authority_id", "fetcher_id", "discovery_date", @@ -1148,12 +1148,12 @@ INSERT INTO raw_extrinsic_metadata ({', '.join(_raw_extrinsic_metadata_insert_cols)}) VALUES ({', '.join('%s' for _ in _raw_extrinsic_metadata_insert_cols)}) - ON CONFLICT (id, authority_id, discovery_date, fetcher_id) + ON CONFLICT (target, authority_id, discovery_date, fetcher_id) DO NOTHING """ raw_extrinsic_metadata_get_cols = [ - "raw_extrinsic_metadata.id", + "raw_extrinsic_metadata.target", "raw_extrinsic_metadata.type", "discovery_date", "metadata_authority.type", @@ -1175,13 +1175,13 @@ INNER JOIN metadata_authority ON (metadata_authority.id=authority_id) INNER JOIN metadata_fetcher ON (metadata_fetcher.id=fetcher_id) - WHERE raw_extrinsic_metadata.id=%s AND authority_id=%s + WHERE raw_extrinsic_metadata.target=%s AND authority_id=%s """ def raw_extrinsic_metadata_add( self, type: str, - id: str, + target: str, discovery_date: datetime.datetime, authority_id: int, fetcher_id: int, @@ -1199,7 +1199,7 @@ query = self._raw_extrinsic_metadata_insert_query args: Dict[str, Any] = dict( type=type, - id=id, + target=target, authority_id=authority_id, fetcher_id=fetcher_id, discovery_date=discovery_date, @@ -1221,7 +1221,7 @@ def raw_extrinsic_metadata_get( self, type: str, - id: str, + target: str, authority_id: int, after_time: Optional[datetime.datetime], after_fetcher: Optional[int], @@ -1229,7 +1229,7 @@ cur, ): query_parts = [self._raw_extrinsic_metadata_select_query] - args = [id, authority_id] + args = [target, authority_id] if after_fetcher is not None: assert after_time diff --git a/swh/storage/postgresql/storage.py b/swh/storage/postgresql/storage.py --- a/swh/storage/postgresql/storage.py +++ b/swh/storage/postgresql/storage.py @@ -1246,7 +1246,7 @@ db.raw_extrinsic_metadata_add( type=metadata_entry.type.value, - id=str(metadata_entry.target), + target=str(metadata_entry.target), discovery_date=metadata_entry.discovery_date, authority_id=authority_id, fetcher_id=fetcher_id, @@ -1315,7 +1315,7 @@ rows = [dict(zip(db.raw_extrinsic_metadata_get_cols, row)) for row in rows] results = [] for row in rows: - assert str(id) == row["raw_extrinsic_metadata.id"] + assert str(id) == row["raw_extrinsic_metadata.target"] results.append(converters.db_to_raw_extrinsic_metadata(row)) if len(results) > limit: diff --git a/swh/storage/sql/30-schema.sql b/swh/storage/sql/30-schema.sql --- a/swh/storage/sql/30-schema.sql +++ b/swh/storage/sql/30-schema.sql @@ -17,7 +17,7 @@ -- latest schema version insert into dbversion(version, release, description) - values(163, now(), 'Work In Progress'); + values(164, now(), 'Work In Progress'); -- a SHA1 checksum create domain sha1 as bytea check (length(value) = 20); @@ -429,7 +429,7 @@ create table raw_extrinsic_metadata ( type text not null, - id text not null, + target text not null, -- metadata source authority_id bigint not null, @@ -452,7 +452,7 @@ comment on table raw_extrinsic_metadata is 'keeps all metadata found concerning an object'; comment on column raw_extrinsic_metadata.type is 'the type of object (content/directory/revision/release/snapshot/origin) the metadata is on'; -comment on column raw_extrinsic_metadata.id is 'the SWHID or origin URL for which the metadata was found'; +comment on column raw_extrinsic_metadata.target is 'the SWHID or origin URL for which the metadata was found'; comment on column raw_extrinsic_metadata.discovery_date is 'the date of retrieval'; comment on column raw_extrinsic_metadata.authority_id is 'the metadata provider: github, openhub, deposit, etc.'; comment on column raw_extrinsic_metadata.fetcher_id is 'the tool used for extracting metadata: loaders, crawlers, etc.'; diff --git a/swh/storage/sql/60-indexes.sql b/swh/storage/sql/60-indexes.sql --- a/swh/storage/sql/60-indexes.sql +++ b/swh/storage/sql/60-indexes.sql @@ -264,7 +264,7 @@ -- raw_extrinsic_metadata -create unique index concurrently raw_extrinsic_metadata_content_authority_date_fetcher on raw_extrinsic_metadata(id, authority_id, discovery_date, fetcher_id); +create unique index concurrently raw_extrinsic_metadata_content_authority_date_fetcher on raw_extrinsic_metadata(target, authority_id, discovery_date, fetcher_id); \if :dbflavor_default alter table raw_extrinsic_metadata add constraint raw_extrinsic_metadata_authority_fkey foreign key (authority_id) references metadata_authority(id) not valid;