---
--- 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(88, 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 <email>'
  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';