diff --git a/sql/Makefile b/sql/Makefile index 32a7250b..20a10b1b 100644 --- a/sql/Makefile +++ b/sql/Makefile @@ -1,53 +1,54 @@ # Depends: postgresql-client, postgresql-autodoc DBNAME = softwareheritage-dev DOCDIR = autodoc SQL_INIT = swh-init.sql +SQL_ENUMS = swh-enums.sql SQL_SCHEMA = swh-schema.sql SQL_FUNC = swh-func.sql SQL_DATA = swh-data.sql -SQLS = $(SQL_INIT) $(SQL_SCHEMA) $(SQL_FUNC) $(SQL_DATA) +SQLS = $(SQL_INIT) $(SQL_ENUMS) $(SQL_SCHEMA) $(SQL_FUNC) $(SQL_DATA) PSQL_BIN = psql PSQL_FLAGS = --single-transaction --echo-all -X PSQL = $(PSQL_BIN) $(PSQL_FLAGS) all: createdb: createdb-stamp createdb-stamp: $(SQL_INIT) createdb $(DBNAME) touch $@ filldb: filldb-stamp filldb-stamp: createdb-stamp cat $(SQLS) | $(PSQL) $(DBNAME) touch $@ dropdb: -dropdb $(DBNAME) dumpdb: swh.dump swh.dump: filldb-stamp pg_dump -Fc $(DBNAME) > $@ doc: autodoc-stamp $(DOCDIR)/swh.pdf autodoc-stamp: filldb-stamp test -d $(DOCDIR)/ || mkdir $(DOCDIR) postgresql_autodoc -d $(DBNAME) -f $(DOCDIR)/swh cp -a $(DOCDIR)/swh.dot $(DOCDIR)/swh.dot.orig touch $@ $(DOCDIR)/swh.pdf: clusters.dot autodoc-stamp bin/dot_add_content $(DOCDIR)/swh.dot.orig clusters.dot > $(DOCDIR)/swh.dot dot -T pdf $(DOCDIR)/swh.dot > $(DOCDIR)/swh.pdf clean: rm -rf *-stamp $(DOCDIR)/ distclean: clean dropdb rm -f swh.dump .PHONY: all initdb createdb dropdb doc clean diff --git a/sql/swh-enums.sql b/sql/swh-enums.sql new file mode 100644 index 00000000..bc6a4b28 --- /dev/null +++ b/sql/swh-enums.sql @@ -0,0 +1,146 @@ +--- +--- Software Heritage Data Types +--- + +create type content_status as enum ('absent', 'visible', 'hidden'); + +-- Types of entities. +-- +-- - organization: a root entity, usually backed by a non-profit, a +-- company, or another kind of "association". (examples: Software +-- Heritage, Debian, GNU, GitHub) +-- +-- - group_of_entities: used for hierarchies, doesn't need to have a +-- concrete existence. (examples: GNU hosting facilities, Debian +-- hosting facilities, GitHub users, ...) +-- +-- - hosting: a hosting facility, can usually be listed to generate +-- other data. (examples: GitHub git hosting, alioth.debian.org, +-- snapshot.debian.org) +-- +-- - group_of_persons: an entity representing a group of +-- persons. (examples: a GitHub organization, a Debian team) +-- +-- - person: an entity representing a person. (examples: +-- a GitHub user, a Debian developer) +-- +-- - project: an entity representing a software project. (examples: a +-- GitHub project, Apache httpd, a Debian source package, ...) +create type entity_type as enum ( + 'organization', + 'group_of_entities', + 'hosting', + 'group_of_persons', + 'person', + 'project' +); + +create type revision_type as enum ('git', 'tar', 'dsc', 'svn'); + +-- the data object types stored in our data model +create type object_type as enum ('content', 'directory', 'revision', 'release'); + +create type origin_visit_status as enum ( + 'ongoing', + 'full', + 'partial' +); + +comment on type origin_visit_status IS 'Possible visit status'; + +create type languages as enum ( 'abap', 'abnf', 'actionscript', + 'actionscript-3', 'ada', 'adl', 'agda', 'alloy', 'ambienttalk', + 'antlr', 'antlr-with-actionscript-target', 'antlr-with-c#-target', + 'antlr-with-cpp-target', 'antlr-with-java-target', + 'antlr-with-objectivec-target', 'antlr-with-perl-target', + 'antlr-with-python-target', 'antlr-with-ruby-target', 'apacheconf', + 'apl', 'applescript', 'arduino', 'aspectj', 'aspx-cs', 'aspx-vb', + 'asymptote', 'autohotkey', 'autoit', 'awk', 'base-makefile', 'bash', + 'bash-session', 'batchfile', 'bbcode', 'bc', 'befunge', + 'blitzbasic', 'blitzmax', 'bnf', 'boo', 'boogie', 'brainfuck', + 'bro', 'bugs', 'c', 'c#', 'c++', 'c-objdump', 'ca65-assembler', + 'cadl', 'camkes', 'cbm-basic-v2', 'ceylon', 'cfengine3', + 'cfstatement', 'chaiscript', 'chapel', 'cheetah', 'cirru', 'clay', + 'clojure', 'clojurescript', 'cmake', 'cobol', 'cobolfree', + 'coffeescript', 'coldfusion-cfc', 'coldfusion-html', 'common-lisp', + 'component-pascal', 'coq', 'cpp-objdump', 'cpsa', 'crmsh', 'croc', + 'cryptol', 'csound-document', 'csound-orchestra', 'csound-score', + 'css', 'css+django/jinja', 'css+genshi-text', 'css+lasso', + 'css+mako', 'css+mozpreproc', 'css+myghty', 'css+php', 'css+ruby', + 'css+smarty', 'cuda', 'cypher', 'cython', 'd', 'd-objdump', + 'darcs-patch', 'dart', 'debian-control-file', 'debian-sourcelist', + 'delphi', 'dg', 'diff', 'django/jinja', 'docker', 'dtd', 'duel', + 'dylan', 'dylan-session', 'dylanlid', 'earl-grey', 'easytrieve', + 'ebnf', 'ec', 'ecl', 'eiffel', 'elixir', 'elixir-iex-session', + 'elm', 'emacslisp', 'embedded-ragel', 'erb', 'erlang', + 'erlang-erl-session', 'evoque', 'ezhil', 'factor', 'fancy', + 'fantom', 'felix', 'fish', 'fortran', 'fortranfixed', 'foxpro', + 'fsharp', 'gap', 'gas', 'genshi', 'genshi-text', 'gettext-catalog', + 'gherkin', 'glsl', 'gnuplot', 'go', 'golo', 'gooddata-cl', 'gosu', + 'gosu-template', 'groff', 'groovy', 'haml', 'handlebars', 'haskell', + 'haxe', 'hexdump', 'html', 'html+cheetah', 'html+django/jinja', + 'html+evoque', 'html+genshi', 'html+handlebars', 'html+lasso', + 'html+mako', 'html+myghty', 'html+php', 'html+smarty', 'html+twig', + 'html+velocity', 'http', 'hxml', 'hy', 'hybris', 'idl', 'idris', + 'igor', 'inform-6', 'inform-6-template', 'inform-7', 'ini', 'io', + 'ioke', 'irc-logs', 'isabelle', 'j', 'jade', 'jags', 'jasmin', + 'java', 'java-server-page', 'javascript', 'javascript+cheetah', + 'javascript+django/jinja', 'javascript+genshi-text', + 'javascript+lasso', 'javascript+mako', 'javascript+mozpreproc', + 'javascript+myghty', 'javascript+php', 'javascript+ruby', + 'javascript+smarty', 'jcl', 'json', 'json-ld', 'julia', + 'julia-console', 'kal', 'kconfig', 'koka', 'kotlin', 'lasso', + 'lean', 'lesscss', 'lighttpd-configuration-file', 'limbo', 'liquid', + 'literate-agda', 'literate-cryptol', 'literate-haskell', + 'literate-idris', 'livescript', 'llvm', 'logos', 'logtalk', 'lsl', + 'lua', 'makefile', 'mako', 'maql', 'mask', 'mason', 'mathematica', + 'matlab', 'matlab-session', 'minid', 'modelica', 'modula-2', + 'moinmoin/trac-wiki-markup', 'monkey', 'moocode', 'moonscript', + 'mozhashpreproc', 'mozpercentpreproc', 'mql', 'mscgen', + 'msdos-session', 'mupad', 'mxml', 'myghty', 'mysql', 'nasm', + 'nemerle', 'nesc', 'newlisp', 'newspeak', + 'nginx-configuration-file', 'nimrod', 'nit', 'nix', 'nsis', 'numpy', + 'objdump', 'objdump-nasm', 'objective-c', 'objective-c++', + 'objective-j', 'ocaml', 'octave', 'odin', 'ooc', 'opa', + 'openedge-abl', 'pacmanconf', 'pan', 'parasail', 'pawn', 'perl', + 'perl6', 'php', 'pig', 'pike', 'pkgconfig', 'pl/pgsql', + 'postgresql-console-(psql)', 'postgresql-sql-dialect', 'postscript', + 'povray', 'powershell', 'powershell-session', 'praat', 'prolog', + 'properties', 'protocol-buffer', 'puppet', 'pypy-log', 'python', + 'python-3', 'python-3.0-traceback', 'python-console-session', + 'python-traceback', 'qbasic', 'qml', 'qvto', 'racket', 'ragel', + 'ragel-in-c-host', 'ragel-in-cpp-host', 'ragel-in-d-host', + 'ragel-in-java-host', 'ragel-in-objective-c-host', + 'ragel-in-ruby-host', 'raw-token-data', 'rconsole', 'rd', 'rebol', + 'red', 'redcode', 'reg', 'resourcebundle', 'restructuredtext', + 'rexx', 'rhtml', 'roboconf-graph', 'roboconf-instances', + 'robotframework', 'rpmspec', 'rql', 'rsl', 'ruby', + 'ruby-irb-session', 'rust', 's', 'sass', 'scala', + 'scalate-server-page', 'scaml', 'scheme', 'scilab', 'scss', 'shen', + 'slim', 'smali', 'smalltalk', 'smarty', 'snobol', 'sourcepawn', + 'sparql', 'sql', 'sqlite3con', 'squidconf', 'stan', 'standard-ml', + 'supercollider', 'swift', 'swig', 'systemverilog', 'tads-3', 'tap', + 'tcl', 'tcsh', 'tcsh-session', 'tea', 'termcap', 'terminfo', + 'terraform', 'tex', 'text-only', 'thrift', 'todotxt', + 'trafficscript', 'treetop', 'turtle', 'twig', 'typescript', + 'urbiscript', 'vala', 'vb.net', 'vctreestatus', 'velocity', + 'verilog', 'vgl', 'vhdl', 'viml', 'x10', 'xml', 'xml+cheetah', + 'xml+django/jinja', 'xml+evoque', 'xml+lasso', 'xml+mako', + 'xml+myghty', 'xml+php', 'xml+ruby', 'xml+smarty', 'xml+velocity', + 'xquery', 'xslt', 'xtend', 'xul+mozpreproc', 'yaml', 'yaml+jinja', + 'zephir', 'unknown' ); + +create type ctags_languages as enum ( 'Ada', 'AnsiblePlaybook', 'Ant', + 'Asm', 'Asp', 'Autoconf', 'Automake', 'Awk', 'Basic', 'BETA', 'C', + 'C#', 'C++', 'Clojure', 'Cobol', 'CoffeeScript [disabled]', 'CSS', + 'ctags', 'D', 'DBusIntrospect', 'Diff', 'DosBatch', 'DTS', 'Eiffel', + 'Erlang', 'Falcon', 'Flex', 'Fortran', 'gdbinit [disabled]', + 'Glade', 'Go', 'HTML', 'Iniconf', 'Java', 'JavaProperties', + 'JavaScript', 'JSON', 'Lisp', 'Lua', 'M4', 'Make', 'man [disabled]', + 'MatLab', 'Maven2', 'Myrddin', 'ObjectiveC', 'OCaml', 'OldC + [disabled]', 'OldC++ [disabled]', 'Pascal', 'Perl', 'Perl6', 'PHP', + 'PlistXML', 'pod', 'Protobuf', 'Python', 'PythonLoggingConfig', 'R', + 'RelaxNG', 'reStructuredText', 'REXX', 'RpmSpec', 'Ruby', 'Rust', + 'Scheme', 'Sh', 'SLang', 'SML', 'SQL', 'SVG', 'SystemdUnit', + 'SystemVerilog', 'Tcl', 'Tex', 'TTCN', 'Vera', 'Verilog', 'VHDL', + 'Vim', 'WindRes', 'XSLT', 'YACC', 'Yaml', 'YumRepo', 'Zephir' ); diff --git a/sql/swh-schema.sql b/sql/swh-schema.sql index e2d130b2..a84b7150 100644 --- a/sql/swh-schema.sql +++ b/sql/swh-schema.sql @@ -1,1144 +1,605 @@ --- --- Software Heritage Data Model --- -- drop schema if exists swh cascade; -- create schema swh; -- set search_path to swh; create table dbversion ( version int primary key, release timestamptz, description text ); insert into dbversion(version, release, description) values(89, now(), 'Work In Progress'); -- a SHA1 checksum (not necessarily originating from Git) create domain sha1 as bytea check (length(value) = 20); -- a Git object ID, i.e., a SHA1 checksum create domain sha1_git as bytea check (length(value) = 20); -- a SHA256 checksum create domain sha256 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; -create type content_status as enum ('absent', 'visible', 'hidden'); - -- 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 primary key, sha1_git sha1_git not null, sha256 sha256 not null, 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 ); create unique index on content(sha1_git); create unique index on content(sha256); create index on content(ctime); -- TODO use a BRIN index here (postgres >= 9.5) create index on content(object_id); -- Asynchronous notification of new content insertions create function notify_new_content() returns trigger language plpgsql as $$ begin perform pg_notify('new_content', encode(new.sha1, 'hex')); return null; end; $$; create trigger notify_new_content after insert on content for each row execute procedure notify_new_content(); -- 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. --- Types of entities. --- --- - organization: a root entity, usually backed by a non-profit, a --- company, or another kind of "association". (examples: Software --- Heritage, Debian, GNU, GitHub) --- --- - group_of_entities: used for hierarchies, doesn't need to have a --- concrete existence. (examples: GNU hosting facilities, Debian --- hosting facilities, GitHub users, ...) --- --- - hosting: a hosting facility, can usually be listed to generate --- other data. (examples: GitHub git hosting, alioth.debian.org, --- snapshot.debian.org) --- --- - group_of_persons: an entity representing a group of --- persons. (examples: a GitHub organization, a Debian team) --- --- - person: an entity representing a person. (examples: --- a GitHub user, a Debian developer) --- --- - project: an entity representing a software project. (examples: a --- GitHub project, Apache httpd, a Debian source package, ...) -create type entity_type as enum ( - 'organization', - 'group_of_entities', - 'hosting', - 'group_of_persons', - 'person', - 'project' -); - -- 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 primary key, 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 ); create index on entity_history(uuid); create index on entity_history(name); -- 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 primary key, parent uuid references entity(uuid) deferrable initially deferred, 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 references entity_history(id) -- last listing id ); create index on entity(name); create index on entity using gin(lister_metadata jsonb_path_ops); -- Register the equivalence between two entities. Allows sideways -- navigation in the entity table create table entity_equivalence ( entity1 uuid references entity(uuid), entity2 uuid references entity(uuid), primary key (entity1, entity2), constraint order_entities check (entity1 < entity2) ); -- Register a lister for a specific entity. create table listable_entity ( uuid uuid references entity(uuid) primary key, 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 primary key, entity uuid references listable_entity(uuid), 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 ); -- 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. create table origin ( id bigserial primary key, type text, -- TODO use an enum here (?) url text not null, lister uuid references listable_entity(uuid), project uuid references entity(uuid) ); create index on origin(type, url); -- Asynchronous notification of new origin insertions create function notify_new_origin() returns trigger language plpgsql as $$ begin perform pg_notify('new_origin', new.id::text); return null; end; $$; create trigger notify_new_origin after insert on origin for each row execute procedure notify_new_origin(); -- 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. create table skipped_content ( sha1 sha1, sha1_git sha1_git, sha256 sha256, length bigint not null, ctime timestamptz not null default now(), status content_status not null default 'absent', reason text not null, origin bigint references origin(id), object_id bigserial, unique (sha1, sha1_git, sha256) ); -- Those indexes support multiple NULL values. create unique index on skipped_content(sha1); create unique index on skipped_content(sha1_git); create unique index on skipped_content(sha256); create index on skipped_content(object_id); -- Asynchronous notification of new skipped content insertions create function notify_new_skipped_content() returns trigger language plpgsql as $$ begin perform pg_notify('new_skipped_content', json_build_object( 'sha1', encode(new.sha1, 'hex'), 'sha1_git', encode(new.sha1_git, 'hex'), 'sha256', encode(new.sha256, 'hex') )::text); return null; end; $$; create trigger notify_new_skipped_content after insert on skipped_content for each row execute procedure notify_new_skipped_content(); -- 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 primary key, origin bigint references origin(id), 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 primary key, 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 ); create index on directory using gin (dir_entries); create index on directory using gin (file_entries); create index on directory using gin (rev_entries); create index on directory(object_id); -- Asynchronous notification of new directory insertions create function notify_new_directory() returns trigger language plpgsql as $$ begin perform pg_notify('new_directory', encode(new.id, 'hex')); return null; end; $$; create trigger notify_new_directory after insert on directory for each row execute procedure notify_new_directory(); -- A directory entry pointing to a sub-directory. create table directory_entry_dir ( id bigserial primary key, target sha1_git, -- id of target directory name unix_path, -- path name, relative to containing dir perms file_perms -- unix-like permissions ); create unique index on directory_entry_dir(target, name, perms); -- A directory entry pointing to a file. create table directory_entry_file ( id bigserial primary key, target sha1_git, -- id of target file name unix_path, -- path name, relative to containing dir perms file_perms -- unix-like permissions ); create unique index on directory_entry_file(target, name, perms); -- A directory entry pointing to a revision. create table directory_entry_rev ( id bigserial primary key, target sha1_git, -- id of target revision name unix_path, -- path name, relative to containing dir perms file_perms -- unix-like permissions ); create unique index on directory_entry_rev(target, name, perms); create table person ( id bigserial primary key, fullname bytea not null, -- freeform specification; what is actually used in the checksums -- will usually be of the form 'name ' name bytea, -- advisory: not null if we managed to parse a name email bytea -- advisory: not null if we managed to parse an email ); create unique index on person(fullname); create index on person(name); create index on person(email); -create type revision_type as enum ('git', 'tar', 'dsc', 'svn'); - --- the data object types stored in our data model -create type object_type as enum ('content', 'directory', 'revision', 'release'); - -- A snapshot of a software project 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 primary key, date timestamptz, date_offset smallint, date_neg_utc_offset boolean, committer_date timestamptz, committer_date_offset smallint, committer_date_neg_utc_offset boolean, type revision_type not null, directory sha1_git, -- file-system tree message bytea, author bigint references person(id), committer bigint references person(id), metadata jsonb, -- extra metadata (tarball checksums, extra commit information, etc...) synthetic boolean not null default false, -- true if synthetic (cf. swh-loader-tar) object_id bigserial ); create index on revision(directory); create index on revision(object_id); -- Asynchronous notification of new revision insertions create function notify_new_revision() returns trigger language plpgsql as $$ begin perform pg_notify('new_revision', encode(new.id, 'hex')); return null; end; $$; create trigger notify_new_revision after insert on revision for each row execute procedure notify_new_revision(); -- either this table or the sha1_git[] column on the revision table create table revision_history ( id sha1_git references revision(id), parent_id sha1_git, parent_rank int not null default 0, -- parent position in merge commits, 0-based primary key (id, parent_rank) ); create index on revision_history(parent_id); -create type origin_visit_status as enum ( - 'ongoing', - 'full', - 'partial' -); - -comment on type origin_visit_status IS 'Possible visit status'; - -- The timestamps at which Software Heritage has made a visit of the given origin. create table origin_visit ( origin bigint not null references origin(id), visit bigint not null, date timestamptz not null, status origin_visit_status not null, metadata jsonb, primary key (origin, visit) ); 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'; create index on origin_visit(date); -- Asynchronous notification of new origin visits create function notify_new_origin_visit() returns trigger language plpgsql as $$ begin perform pg_notify('new_origin_visit', json_build_object( 'origin', new.origin, 'visit', new.visit )::text); return null; end; $$; create trigger notify_new_origin_visit after insert on origin_visit for each row execute procedure notify_new_origin_visit(); -- 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 references origin(id) 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 object_id bigserial not null, -- short object identifier visits bigint[] not null, -- the visits where that occurrence was valid. References -- origin_visit(visit), where o_h.origin = origin_visit.origin. primary key (object_id) ); create index on occurrence_history(target, target_type); create index on occurrence_history(origin, branch); create unique index on occurrence_history(origin, branch, target, target_type); create index on occurrence_history(object_id); -- Materialized view of occurrence_history, storing the *current* value of each -- branch, as last seen by SWH. create table occurrence ( origin bigint references origin(id) not null, branch bytea not null, target sha1_git not null, target_type object_type not null, primary key(origin, branch) ); -- A "memorable" point in the development history of a 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 primary key, target sha1_git, target_type object_type, date timestamptz, date_offset smallint, date_neg_utc_offset boolean, name bytea, comment bytea, author bigint references person(id), synthetic boolean not null default false, -- true if synthetic (cf. swh-loader-tar) object_id bigserial ); create index on release(target, target_type); create index on release(object_id); -- Asynchronous notification of new release insertions create function notify_new_release() returns trigger language plpgsql as $$ begin perform pg_notify('new_release', encode(new.id, 'hex')); return null; end; $$; create trigger notify_new_release after insert on release for each row execute procedure notify_new_release(); -- Content provenance information caches -- https://forge.softwareheritage.org/T547 -- -- Those tables aren't expected to be exhaustive, and get filled on a case by -- case basis: absence of data doesn't mean the data is not there -- content <-> revision mapping cache -- -- semantics: "we have seen the content with given id in the given path inside -- the given revision" create table cache_content_revision ( content sha1_git not null primary key references content(sha1_git), blacklisted boolean default false, revision_paths bytea[][] ); create table cache_content_revision_processed ( revision sha1_git not null primary key references revision(id) ); -- revision <-> origin_visit mapping cache -- -- semantics: "we have seen the given revision in the given origin during the -- given visit" create table cache_revision_origin ( revision sha1_git not null references revision(id), origin bigint not null, visit bigint not null, primary key (revision, origin, visit), foreign key (origin, visit) references origin_visit (origin, visit) ); create index on cache_revision_origin(revision); -- Computing metadata on sha1's contents -- Properties (mimetype, encoding, etc...) create table content_mimetype ( id sha1 primary key references content(sha1) not null, mimetype bytea not null, encoding bytea not null ); comment on table content_mimetype is 'Metadata associated to a raw content'; comment on column content_mimetype.mimetype is 'Raw content Mimetype'; comment on column content_mimetype.encoding is 'Raw content encoding'; -create type languages as enum ( - 'abap', - 'abnf', - 'actionscript', - 'actionscript-3', - 'ada', - 'adl', - 'agda', - 'alloy', - 'ambienttalk', - 'antlr', - 'antlr-with-actionscript-target', - 'antlr-with-c#-target', - 'antlr-with-cpp-target', - 'antlr-with-java-target', - 'antlr-with-objectivec-target', - 'antlr-with-perl-target', - 'antlr-with-python-target', - 'antlr-with-ruby-target', - 'apacheconf', - 'apl', - 'applescript', - 'arduino', - 'aspectj', - 'aspx-cs', - 'aspx-vb', - 'asymptote', - 'autohotkey', - 'autoit', - 'awk', - 'base-makefile', - 'bash', - 'bash-session', - 'batchfile', - 'bbcode', - 'bc', - 'befunge', - 'blitzbasic', - 'blitzmax', - 'bnf', - 'boo', - 'boogie', - 'brainfuck', - 'bro', - 'bugs', - 'c', - 'c#', - 'c++', - 'c-objdump', - 'ca65-assembler', - 'cadl', - 'camkes', - 'cbm-basic-v2', - 'ceylon', - 'cfengine3', - 'cfstatement', - 'chaiscript', - 'chapel', - 'cheetah', - 'cirru', - 'clay', - 'clojure', - 'clojurescript', - 'cmake', - 'cobol', - 'cobolfree', - 'coffeescript', - 'coldfusion-cfc', - 'coldfusion-html', - 'common-lisp', - 'component-pascal', - 'coq', - 'cpp-objdump', - 'cpsa', - 'crmsh', - 'croc', - 'cryptol', - 'csound-document', - 'csound-orchestra', - 'csound-score', - 'css', - 'css+django/jinja', - 'css+genshi-text', - 'css+lasso', - 'css+mako', - 'css+mozpreproc', - 'css+myghty', - 'css+php', - 'css+ruby', - 'css+smarty', - 'cuda', - 'cypher', - 'cython', - 'd', - 'd-objdump', - 'darcs-patch', - 'dart', - 'debian-control-file', - 'debian-sourcelist', - 'delphi', - 'dg', - 'diff', - 'django/jinja', - 'docker', - 'dtd', - 'duel', - 'dylan', - 'dylan-session', - 'dylanlid', - 'earl-grey', - 'easytrieve', - 'ebnf', - 'ec', - 'ecl', - 'eiffel', - 'elixir', - 'elixir-iex-session', - 'elm', - 'emacslisp', - 'embedded-ragel', - 'erb', - 'erlang', - 'erlang-erl-session', - 'evoque', - 'ezhil', - 'factor', - 'fancy', - 'fantom', - 'felix', - 'fish', - 'fortran', - 'fortranfixed', - 'foxpro', - 'fsharp', - 'gap', - 'gas', - 'genshi', - 'genshi-text', - 'gettext-catalog', - 'gherkin', - 'glsl', - 'gnuplot', - 'go', - 'golo', - 'gooddata-cl', - 'gosu', - 'gosu-template', - 'groff', - 'groovy', - 'haml', - 'handlebars', - 'haskell', - 'haxe', - 'hexdump', - 'html', - 'html+cheetah', - 'html+django/jinja', - 'html+evoque', - 'html+genshi', - 'html+handlebars', - 'html+lasso', - 'html+mako', - 'html+myghty', - 'html+php', - 'html+smarty', - 'html+twig', - 'html+velocity', - 'http', - 'hxml', - 'hy', - 'hybris', - 'idl', - 'idris', - 'igor', - 'inform-6', - 'inform-6-template', - 'inform-7', - 'ini', - 'io', - 'ioke', - 'irc-logs', - 'isabelle', - 'j', - 'jade', - 'jags', - 'jasmin', - 'java', - 'java-server-page', - 'javascript', - 'javascript+cheetah', - 'javascript+django/jinja', - 'javascript+genshi-text', - 'javascript+lasso', - 'javascript+mako', - 'javascript+mozpreproc', - 'javascript+myghty', - 'javascript+php', - 'javascript+ruby', - 'javascript+smarty', - 'jcl', - 'json', - 'json-ld', - 'julia', - 'julia-console', - 'kal', - 'kconfig', - 'koka', - 'kotlin', - 'lasso', - 'lean', - 'lesscss', - 'lighttpd-configuration-file', - 'limbo', - 'liquid', - 'literate-agda', - 'literate-cryptol', - 'literate-haskell', - 'literate-idris', - 'livescript', - 'llvm', - 'logos', - 'logtalk', - 'lsl', - 'lua', - 'makefile', - 'mako', - 'maql', - 'mask', - 'mason', - 'mathematica', - 'matlab', - 'matlab-session', - 'minid', - 'modelica', - 'modula-2', - 'moinmoin/trac-wiki-markup', - 'monkey', - 'moocode', - 'moonscript', - 'mozhashpreproc', - 'mozpercentpreproc', - 'mql', - 'mscgen', - 'msdos-session', - 'mupad', - 'mxml', - 'myghty', - 'mysql', - 'nasm', - 'nemerle', - 'nesc', - 'newlisp', - 'newspeak', - 'nginx-configuration-file', - 'nimrod', - 'nit', - 'nix', - 'nsis', - 'numpy', - 'objdump', - 'objdump-nasm', - 'objective-c', - 'objective-c++', - 'objective-j', - 'ocaml', - 'octave', - 'odin', - 'ooc', - 'opa', - 'openedge-abl', - 'pacmanconf', - 'pan', - 'parasail', - 'pawn', - 'perl', - 'perl6', - 'php', - 'pig', - 'pike', - 'pkgconfig', - 'pl/pgsql', - 'postgresql-console-(psql)', - 'postgresql-sql-dialect', - 'postscript', - 'povray', - 'powershell', - 'powershell-session', - 'praat', - 'prolog', - 'properties', - 'protocol-buffer', - 'puppet', - 'pypy-log', - 'python', - 'python-3', - 'python-3.0-traceback', - 'python-console-session', - 'python-traceback', - 'qbasic', - 'qml', - 'qvto', - 'racket', - 'ragel', - 'ragel-in-c-host', - 'ragel-in-cpp-host', - 'ragel-in-d-host', - 'ragel-in-java-host', - 'ragel-in-objective-c-host', - 'ragel-in-ruby-host', - 'raw-token-data', - 'rconsole', - 'rd', - 'rebol', - 'red', - 'redcode', - 'reg', - 'resourcebundle', - 'restructuredtext', - 'rexx', - 'rhtml', - 'roboconf-graph', - 'roboconf-instances', - 'robotframework', - 'rpmspec', - 'rql', - 'rsl', - 'ruby', - 'ruby-irb-session', - 'rust', - 's', - 'sass', - 'scala', - 'scalate-server-page', - 'scaml', - 'scheme', - 'scilab', - 'scss', - 'shen', - 'slim', - 'smali', - 'smalltalk', - 'smarty', - 'snobol', - 'sourcepawn', - 'sparql', - 'sql', - 'sqlite3con', - 'squidconf', - 'stan', - 'standard-ml', - 'supercollider', - 'swift', - 'swig', - 'systemverilog', - 'tads-3', - 'tap', - 'tcl', - 'tcsh', - 'tcsh-session', - 'tea', - 'termcap', - 'terminfo', - 'terraform', - 'tex', - 'text-only', - 'thrift', - 'todotxt', - 'trafficscript', - 'treetop', - 'turtle', - 'twig', - 'typescript', - 'urbiscript', - 'vala', - 'vb.net', - 'vctreestatus', - 'velocity', - 'verilog', - 'vgl', - 'vhdl', - 'viml', - 'x10', - 'xml', - 'xml+cheetah', - 'xml+django/jinja', - 'xml+evoque', - 'xml+lasso', - 'xml+mako', - 'xml+myghty', - 'xml+php', - 'xml+ruby', - 'xml+smarty', - 'xml+velocity', - 'xquery', - 'xslt', - 'xtend', - 'xul+mozpreproc', - 'yaml', - 'yaml+jinja', - 'zephir', - 'unknown' -); - -- Language metadata create table content_language ( id sha1 primary key references content(sha1) not null, lang languages not null ); comment on table content_language is 'Language information on a raw content'; comment on column content_language.lang is 'Language information'; -create type ctags_languages as enum ( - 'Ada', - 'AnsiblePlaybook', - 'Ant', - 'Asm', - 'Asp', - 'Autoconf', - 'Automake', - 'Awk', - 'Basic', - 'BETA', - 'C', - 'C#', - 'C++', - 'Clojure', - 'Cobol', - 'CoffeeScript [disabled]', - 'CSS', - 'ctags', - 'D', - 'DBusIntrospect', - 'Diff', - 'DosBatch', - 'DTS', - 'Eiffel', - 'Erlang', - 'Falcon', - 'Flex', - 'Fortran', - 'gdbinit [disabled]', - 'Glade', - 'Go', - 'HTML', - 'Iniconf', - 'Java', - 'JavaProperties', - 'JavaScript', - 'JSON', - 'Lisp', - 'Lua', - 'M4', - 'Make', - 'man [disabled]', - 'MatLab', - 'Maven2', - 'Myrddin', - 'ObjectiveC', - 'OCaml', - 'OldC [disabled]', - 'OldC++ [disabled]', - 'Pascal', - 'Perl', - 'Perl6', - 'PHP', - 'PlistXML', - 'pod', - 'Protobuf', - 'Python', - 'PythonLoggingConfig', - 'R', - 'RelaxNG', - 'reStructuredText', - 'REXX', - 'RpmSpec', - 'Ruby', - 'Rust', - 'Scheme', - 'Sh', - 'SLang', - 'SML', - 'SQL', - 'SVG', - 'SystemdUnit', - 'SystemVerilog', - 'Tcl', - 'Tex', - 'TTCN', - 'Vera', - 'Verilog', - 'VHDL', - 'Vim', - 'WindRes', - 'XSLT', - 'YACC', - 'Yaml', - 'YumRepo', - 'Zephir' -); - - -- ctags information per content create table content_ctags ( id sha1 references content(sha1) not null, name text not null, kind text not null, line bigint not null, lang ctags_languages not null ); comment on table content_ctags is 'Ctags information on a raw content'; comment on column content_ctags.id is 'Content identifier'; comment on column content_ctags.name is 'Symbol name'; comment on column content_ctags.kind is 'Symbol kind (function, class, variable, const...)'; comment on column content_ctags.line is 'Symbol line'; comment on column content_ctags.lang is 'Language information for that content'; create index on content_ctags(id); create unique index on content_ctags(id, md5(name), kind, line, lang);