diff --git a/sql/swh-schema.sql b/sql/swh-schema.sql index 2ca592a42..9145976c9 100644 --- a/sql/swh-schema.sql +++ b/sql/swh-schema.sql @@ -1,445 +1,473 @@ --- ---- Software Heritage Data Model +--- SQL implementation of the Software Heritage data model --- --- drop schema if exists swh cascade; --- create schema swh; --- set search_path to swh; - +-- schema versions create table dbversion ( version int primary key, release timestamptz, description text ); +-- latest schema version insert into dbversion(version, release, description) values(121, now(), 'Work In Progress'); --- a SHA1 checksum (not necessarily originating from Git) +-- a SHA1 checksum create domain sha1 as bytea check (length(value) = 20); --- a Git object ID, i.e., a SHA1 checksum +-- a Git object ID, i.e., a Git-style salted SHA1 checksum create domain sha1_git as bytea check (length(value) = 20); -- a SHA256 checksum create domain sha256 as bytea check (length(value) = 32); -- a blake2 checksum create domain blake2s256 as bytea check (length(value) = 32); -- UNIX path (absolute, relative, individual path component, etc.) create domain unix_path as bytea; -- a set of UNIX-like access permissions, as manipulated by, e.g., chmod create domain file_perms as int; + -- Checksums about actual file content. Note that the content itself is not -- stored in the DB, but on external (key-value) storage. A single checksum is -- used as key there, but the other can be used to verify that we do not inject -- content collisions not knowingly. create table content ( sha1 sha1 not null, sha1_git sha1_git not null, sha256 sha256 not null, blake2s256 blake2s256, length bigint not null, ctime timestamptz not null default now(), -- creation time, i.e. time of (first) injection into the storage status content_status not null default 'visible', object_id bigserial ); -- Entities constitute a typed hierarchy of organization, hosting -- facilities, groups, people and software projects. -- -- Examples of entities: Software Heritage, Debian, GNU, GitHub, -- Apache, The Linux Foundation, the Debian Python Modules Team, the -- torvalds GitHub user, the torvalds/linux GitHub project. -- -- The data model is hierarchical (via the parent attribute) and might -- store sub-branches of existing entities. The key feature of an -- entity is might be *listed* (if it is available in listable_entity) -- to retrieve information about its content, i.e: sub-entities, -- projects, origins. -- The history of entities. Allows us to keep historical metadata -- about entities. The temporal invariant is the uuid. Root -- organization uuids are manually generated (and available in -- swh-data.sql). -- -- For generated entities (generated = true), we can provide -- generation_metadata to allow listers to retrieve the uuids of previous -- iterations of the entity. -- -- Inactive entities that have been active in the past (active = -- false) should register the timestamp at which we saw them -- deactivate, in a new entry of entity_history. create table entity_history ( id bigserial not null, uuid uuid, parent uuid, -- should reference entity_history(uuid) name text not null, type entity_type not null, description text, homepage text, active boolean not null, -- whether the entity was seen on the last listing generated boolean not null, -- whether this entity has been generated by a lister lister_metadata jsonb, -- lister-specific metadata, used for queries metadata jsonb, validity timestamptz[] -- timestamps at which we have seen this entity ); -- The entity table provides a view of the latest information on a -- given entity. It is updated via a trigger on entity_history. create table entity ( uuid uuid not null, parent uuid, name text not null, type entity_type not null, description text, homepage text, active boolean not null, -- whether the entity was seen on the last listing generated boolean not null, -- whether this entity has been generated by a lister lister_metadata jsonb, -- lister-specific metadata, used for queries metadata jsonb, last_seen timestamptz, -- last listing time or disappearance time for active=false last_id bigint -- last listing id ); -- Register the equivalence between two entities. Allows sideways -- navigation in the entity table create table entity_equivalence ( entity1 uuid, entity2 uuid ); -- Register a lister for a specific entity. create table listable_entity ( uuid uuid, enabled boolean not null default true, -- do we list this entity automatically? list_engine text, -- crawler to be used to list entity's content list_url text, -- root URL to start the listing list_params jsonb, -- org-specific listing parameter latest_list timestamptz -- last time the entity's content has been listed ); -- Log of all entity listings (i.e., entity crawling) that have been -- done in the past, or are still ongoing. create table list_history ( id bigserial not null, date timestamptz not null, status boolean, -- true if and only if the listing has been successful result jsonb, -- more detailed return value, depending on status stdout text, stderr text, duration interval, -- fetch duration of NULL if still ongoing entity uuid ); --- An origin is a place, identified by an URL, where software can be found. We --- support different kinds of origins, e.g., git and other VCS repositories, --- web pages that list tarballs URLs (e.g., http://www.kernel.org), indirect --- tarball URLs (e.g., http://www.example.org/latest.tar.gz), etc. The key --- feature of an origin is that it can be *fetched* (wget, git clone, svn --- checkout, etc.) to retrieve all the contained software. +-- An origin is a place, identified by an URL, where software source code +-- artifacts can be found. We support different kinds of origins, e.g., git and +-- other VCS repositories, web pages that list tarballs URLs (e.g., +-- http://www.kernel.org), indirect tarball URLs (e.g., +-- http://www.example.org/latest.tar.gz), etc. The key feature of an origin is +-- that it can be *fetched* from (wget, git clone, svn checkout, etc.) to +-- retrieve all the contained software. create table origin ( id bigserial not null, type text, -- TODO use an enum here (?) url text not null, lister uuid, project uuid ); --- Content we have seen but skipped for some reason. This table is --- separate from the content table as we might not have the sha1 --- checksum of that data (for instance when we inject git --- repositories, objects that are too big will be skipped here, and we --- will only know their sha1_git). 'reason' contains the reason the --- content was skipped. origin is a nullable column allowing to find --- out which origin contains that skipped content. + +-- Content blobs observed somewhere, but not ingested into the archive for +-- whatever reason. This table is separate from the content table as we might +-- not have the sha1 checksum of skipped contents (for instance when we inject +-- git repositories, objects that are too big will be skipped here, and we will +-- only know their sha1_git). 'reason' contains the reason the content was +-- skipped. origin is a nullable column allowing to find out which origin +-- contains that skipped content. create table skipped_content ( sha1 sha1, sha1_git sha1_git, sha256 sha256, blake2s256 blake2s256, length bigint not null, ctime timestamptz not null default now(), status content_status not null default 'absent', reason text not null, origin bigint, object_id bigserial ); + -- Log of all origin fetches (i.e., origin crawling) that have been done in the -- past, or are still ongoing. Similar to list_history, but for origins. create table fetch_history ( id bigserial, origin bigint, date timestamptz not null, status boolean, -- true if and only if the fetch has been successful result jsonb, -- more detailed returned values, times, etc... stdout text, stderr text, -- null when status is true, filled otherwise duration interval -- fetch duration of NULL if still ongoing ); -- A file-system directory. A directory is a list of directory entries (see -- tables: directory_entry_{dir,file}). -- -- To list the contents of a directory: -- 1. list the contained directory_entry_dir using array dir_entries -- 2. list the contained directory_entry_file using array file_entries -- 3. list the contained directory_entry_rev using array rev_entries -- 4. UNION -- -- Synonyms/mappings: -- * git: tree create table directory ( id sha1_git, dir_entries bigint[], -- sub-directories, reference directory_entry_dir file_entries bigint[], -- contained files, reference directory_entry_file rev_entries bigint[], -- mounted revisions, reference directory_entry_rev object_id bigserial -- short object identifier ); --- A directory entry pointing to a sub-directory. +-- A directory entry pointing to a (sub-)directory. create table directory_entry_dir ( id bigserial, target sha1_git, -- id of target directory name unix_path, -- path name, relative to containing dir perms file_perms -- unix-like permissions ); --- A directory entry pointing to a file. +-- A directory entry pointing to a file content. create table directory_entry_file ( id bigserial, target sha1_git, -- id of target file name unix_path, -- path name, relative to containing dir perms file_perms -- unix-like permissions ); -- A directory entry pointing to a revision. create table directory_entry_rev ( id bigserial, target sha1_git, -- id of target revision name unix_path, -- path name, relative to containing dir perms file_perms -- unix-like permissions ); + +-- A person referenced by some source code artifacts, e.g., a VCS revision or +-- release metadata. create table person ( id bigserial, name bytea, -- advisory: not null if we managed to parse a name email bytea, -- advisory: not null if we managed to parse an email fullname bytea not null -- freeform specification; what is actually used in the checksums -- will usually be of the form 'name ' ); --- A snapshot of a software project at a specific point in time. + +-- The state of a source code tree at a specific point in time. -- -- Synonyms/mappings: -- * git / subversion / etc: commit -- * tarball: a specific tarball -- -- Revisions are organized as DAGs. Each revision points to 0, 1, or more (in -- case of merges) parent revisions. Each revision points to a directory, i.e., -- a file-system tree containing files and directories. create table revision ( id sha1_git, date timestamptz, date_offset smallint, committer_date timestamptz, committer_date_offset smallint, type revision_type not null, - directory sha1_git, -- file-system tree + directory sha1_git, -- source code "root" directory message bytea, author bigint, committer bigint, - synthetic boolean not null default false, -- true if synthetic (cf. swh-loader-tar) - metadata jsonb, -- extra metadata (tarball checksums, extra commit information, etc...) + synthetic boolean not null default false, -- true iff revision has been created by Software Heritage + metadata jsonb, -- extra metadata (tarball checksums, extra commit information, etc...) object_id bigserial, date_neg_utc_offset boolean, committer_date_neg_utc_offset boolean ); - -- either this table or the sha1_git[] column on the revision table create table revision_history ( id sha1_git, parent_id sha1_git, parent_rank int not null default 0 -- parent position in merge commits, 0-based ); --- The timestamps at which Software Heritage has made a visit of the given origin. + +-- Crawling history of software origins visited by Software Heritage. Each +-- visit is a 3-way mapping between a software origin, a timestamp, and a +-- snapshot object capturing the full-state of the origin at visit time. create table origin_visit ( origin bigint not null, visit bigint not null, date timestamptz not null, status origin_visit_status not null, metadata jsonb, snapshot_id bigint ); comment on column origin_visit.origin is 'Visited origin'; -comment on column origin_visit.visit is 'Visit number the visit occurred for that origin'; -comment on column origin_visit.date is 'Visit date for that origin'; -comment on column origin_visit.status is 'Visit status for that origin'; -comment on column origin_visit.metadata is 'Metadata associated with the visit'; -comment on column origin_visit.snapshot_id is 'id of the snapshot associated with the visit'; +comment on column origin_visit.visit is 'Sequential visit number for the origin'; +comment on column origin_visit.date is 'Visit timestamp'; +comment on column origin_visit.status is 'Visit result'; +comment on column origin_visit.metadata is 'Origin metadata at visit time'; +comment on column origin_visit.snapshot_id is 'Origin snapshot at visit time'; +-- BEGIN legacy section (T830) + -- The content of software origins is indexed starting from top-level pointers -- called "branches". Every time we fetch some origin we store in this table -- where the branches pointed to at fetch time. -- -- Synonyms/mappings: -- * git: ref (in the "git update-ref" sense) create table occurrence_history ( origin bigint not null, branch bytea not null, -- e.g., b"master" (for VCS), or b"sid" (for Debian) target sha1_git not null, -- ref target, e.g., commit id target_type object_type not null, -- ref target type visits bigint[] not null, -- the visits where that occurrence was valid. References -- origin_visit(visit), where o_h.origin = origin_visit.origin. object_id bigserial not null, -- short object identifier snapshot_branch_id bigint ); -- Materialized view of occurrence_history, storing the *current* value of each -- branch, as last seen by SWH. create table occurrence ( origin bigint, branch bytea not null, target sha1_git not null, target_type object_type not null ); +-- END legacy section (T830) -create table snapshot ( - object_id bigserial not null, - id sha1_git +-- A snapshot represents the entire state of a software origin as crawled by +-- Software Heritage. This table is a simple mapping between (public) intrinsic +-- snapshot identifiers and (private) numeric sequential identifiers. +create table snapshot +( + object_id bigserial not null, -- PK internal object identifier + id sha1_git -- snapshot intrinsic identifier ); -create table snapshot_branch ( - object_id bigserial not null, - name bytea not null, - target bytea, - target_type snapshot_target +-- Each snapshot associate "branch" names to other objects in the Software +-- Heritage Merkle DAG. This table describes branches as mappings between names +-- and target typed objects. +create table snapshot_branch +( + object_id bigserial not null, -- PK internal object identifier + name bytea not null, -- branch name, e.g., "master" or "feature/drag-n-drop" + target bytea, -- target object identifier, e.g., a revision identifier + target_type snapshot_target -- target object type, e.g., "revision" ); -create table snapshot_branches ( - snapshot_id bigint not null, - branch_id bigint not null +-- Mapping between snapshots and their branches. +create table snapshot_branches +( + snapshot_id bigint not null, -- snapshot identifier, ref. snapshot.object_id + branch_id bigint not null -- branch identifier, ref. snapshot_branch.object_id ); --- A "memorable" point in the development history of a project. +-- A "memorable" point in time in the development history of a software +-- project. -- -- Synonyms/mappings: -- * git: tag (of the annotated kind, otherwise they are just references) -- * tarball: the release version number create table release ( id sha1_git not null, target sha1_git, date timestamptz, date_offset smallint, name bytea, comment bytea, author bigint, - synthetic boolean not null default false, -- true if synthetic (cf. swh-loader-tar) + synthetic boolean not null default false, -- true iff release has been created by Software Heritage object_id bigserial, target_type object_type not null, date_neg_utc_offset boolean ); --- Tools -create table tool ( +-- Tools +create table tool +( id serial not null, name text not null, version text not null, configuration jsonb ); comment on table tool is 'Tool information'; comment on column tool.id is 'Tool identifier'; comment on column tool.version is 'Tool name'; comment on column tool.version is 'Tool version'; comment on column tool.configuration is 'Tool configuration: command line, flags, etc...'; -create table metadata_provider ( +create table metadata_provider +( id serial not null, provider_name text not null, provider_type text not null, provider_url text, metadata jsonb ); comment on table metadata_provider is 'Metadata provider information'; comment on column metadata_provider.id is 'Provider''s identifier'; comment on column metadata_provider.provider_name is 'Provider''s name'; comment on column metadata_provider.provider_url is 'Provider''s url'; comment on column metadata_provider.metadata is 'Other metadata about provider'; -- Discovery of metadata during a listing, loading, deposit or external_catalog of an origin -- also provides a translation to a defined json schema using a translation tool (tool_id) -create table origin_metadata( - id bigserial not null, -- PK object identifier - origin_id bigint not null, -- references origin(id) - discovery_date timestamptz not null, -- when it was extracted - provider_id bigint not null, -- ex: 'hal', 'lister-github', 'loader-github' +create table origin_metadata +( + id bigserial not null, -- PK internal object identifier + origin_id bigint not null, -- references origin(id) + discovery_date timestamptz not null, -- when it was extracted + provider_id bigint not null, -- ex: 'hal', 'lister-github', 'loader-github' tool_id bigint not null, metadata jsonb not null ); comment on table origin_metadata is 'keeps all metadata found concerning an origin'; comment on column origin_metadata.id is 'the origin_metadata object''s id'; comment on column origin_metadata.origin_id is 'the origin id for which the metadata was found'; comment on column origin_metadata.discovery_date is 'the date of retrieval'; comment on column origin_metadata.provider_id is 'the metadata provider: github, openhub, deposit, etc.'; comment on column origin_metadata.tool_id is 'the tool used for extracting metadata: lister-github, etc.'; comment on column origin_metadata.metadata is 'metadata in json format but with original terms'; + -- Keep a cache of object counts -create table object_counts ( +create table object_counts +( object_type text, -- table for which we're counting objects (PK) value bigint, -- count of objects in the table last_update timestamptz, -- last update for the object count in this table single_update boolean -- whether we update this table standalone (true) or through bucketed counts (false) ); -CREATE TABLE object_counts_bucketed ( - line serial NOT NULL, -- PK - object_type text NOT NULL, -- table for which we're counting objects - identifier text NOT NULL, -- identifier across which we're bucketing objects +create table object_counts_bucketed +( + line serial not null, -- PK + object_type text not null, -- table for which we're counting objects + identifier text not null, -- identifier across which we're bucketing objects bucket_start bytea, -- lower bound (inclusive) for the bucket bucket_end bytea, -- upper bound (exclusive) for the bucket value bigint, -- count of objects in the bucket last_update timestamptz -- last update for the object count in this bucket );