path: root/sql/schema.sql

-- Convention for database items with version control:
--
--   CREATE TABLE items ( -- dbentry_type=x
--     id        vndbid NOT NULL PRIMARY KEY ..,
--     locked    boolean NOT NULL DEFAULT FALSE,
--     hidden    boolean NOT NULL DEFAULT FALSE,
--     -- item-specific columns here
--   );
--   CREATE TABLE items_hist ( -- History of the 'items' table
--     chid integer NOT NULL,  -- references changes.id
--     -- item-specific columns here
--   );
--
-- The '-- dbentry_type=x' comment is required, and is used by sqleditfunc.pl
-- to generate the correct editing functions. It's possible for 'items' to have
-- more item-specific columns than 'items_hist'.  Some columns are caches or
-- otherwise autogenerated, and do not need to be versioned.
--
-- The (hidden,locked) columns indicate the item's state:
--   !hidden && !locked -> Normal
--   !hidden &&  locked -> Locked
--    hidden && !locked -> Awaiting approval (tags/traits only)
--    hidden &&  locked -> Deleted
-- The history of these flags is recorded as (ihid,ilock) in the changes table.
-- (Yes, the state is better represented as an ENUM, but this way it's easier
-- to filter out 'hidden' items in listings)
--
-- item-related tables work roughly the same:
--
--   CREATE TABLE items_field (
--     id vndbid,  -- references items.id
--     -- field-specific columns here
--   );
--   CREATE TABLE items_field_hist ( -- History of the 'items_field' table
--     chid integer, -- references changes.id
--     -- field-specific columns here
--   );
--
-- The changes and *_hist tables contain all the data. In a sense, the other
-- tables related to the item are just a cache/view into the latest versions.
-- All modifications to the item tables has to go through the edit_* functions
-- in editfunc.sql, these are also responsible for keeping things synchronized.
--
-- Columns marked with a '[pub]' comment on the same line are included in the
-- public database dump. Be aware that not all properties of the to-be-dumped
-- data are annotated in this file. Which tables and which rows are exported is
-- defined in util/dbdump.pl.
--
-- Comments on CREATE TABLE and column lines for '[pub]' items are included in
-- the public database dump import.sql in the form of "COMMENT ON" commands.
--
-- Columns in tables are generally ordered for efficient storage: larger
-- fixed-sized columns go before smaller fixed-sized columns, variable-length
-- columns go at the end. When a new column is added to a table, it should
-- always be added at the end because that's the only thing Postgres supports.
-- Once in a while I re-order all newly added columns for efficiency and that
-- requires a full dump and re-import of the database using
--   util/dbdump.pl export-data
-- to take effect.  That's why I typically plan these at the same time that I'm
-- upgrading to a new major Postgres version, after all, a dump-and-import is a
-- good upgrade strategy.
-- Code should not depend on column order!
--
-- Note: Every CREATE TABLE clause and each column should be on a separate
-- line. This file is parsed by lib/VNDB/Schema.pm and it doesn't implement a
-- full SQL query parser.


-- data types

CREATE TYPE anime_type        AS ENUM ('tv', 'ova', 'mov', 'oth', 'web', 'spe', 'mv');
CREATE TYPE blood_type        AS ENUM ('unknown', 'a', 'b', 'ab', 'o');
CREATE TYPE board_type        AS ENUM ('an', 'db', 'ge', 'v', 'p', 'u');
CREATE TYPE char_role         AS ENUM ('main', 'primary', 'side', 'appears');
CREATE TYPE credit_type       AS ENUM ('scenario', 'chardesign', 'art', 'music', 'songs', 'director', 'translator', 'editor', 'qa', 'staff');
CREATE TYPE cup_size          AS ENUM ('', 'AAA', 'AA', 'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z');
CREATE TYPE dbentry_type      AS ENUM ('v', 'r', 'p', 'c', 's', 'd');
CREATE TYPE gender            AS ENUM ('unknown', 'm', 'f', 'b');
CREATE TYPE language          AS ENUM ('ar', 'be', 'bg', 'ca', 'cs', 'ck', 'da', 'de', 'el', 'en', 'eo', 'es', 'eu', 'fa', 'fi', 'fr', 'ga', 'gd', 'he', 'hi', 'hr', 'hu', 'id', 'it', 'iu', 'ja', 'ko', 'mk', 'ms', 'la', 'lt', 'lv', 'nl', 'no', 'pl', 'pt-pt', 'pt-br', 'ro', 'ru', 'sk', 'sl', 'sr', 'sv', 'ta', 'th', 'tr', 'uk', 'ur', 'vi', 'zh', 'zh-Hans', 'zh-Hant');
CREATE TYPE medium            AS ENUM ('cd', 'dvd', 'gdr', 'blr', 'flp', 'cas', 'mrt', 'mem', 'umd', 'nod', 'in', 'otc');
CREATE TYPE notification_ntype AS ENUM ('pm', 'dbdel', 'listdel', 'dbedit', 'announce', 'post', 'comment', 'subpost', 'subedit', 'subreview', 'subapply');
CREATE TYPE platform          AS ENUM ('win', 'dos', 'lin', 'mac', 'ios', 'and', 'dvd', 'bdp', 'fm7', 'fm8', 'fmt', 'gba', 'gbc', 'msx', 'nds', 'nes', 'p88', 'p98', 'pce', 'pcf', 'psp', 'ps1', 'ps2', 'ps3', 'ps4', 'ps5', 'psv', 'drc', 'smd', 'scd', 'sat', 'sfc', 'swi', 'wii', 'wiu', 'n3d', 'vnd', 'x1s', 'x68', 'xb1', 'xb3', 'xbo', 'xxs', 'web', 'tdo', 'mob', 'oth');
CREATE TYPE producer_type     AS ENUM ('co', 'in', 'ng');
CREATE TYPE producer_relation AS ENUM ('old', 'new', 'sub', 'par', 'imp', 'ipa', 'spa', 'ori');
CREATE TYPE release_type      AS ENUM ('complete', 'partial', 'trial');
CREATE TYPE report_status     AS ENUM ('new', 'busy', 'done', 'dismissed');
CREATE TYPE tag_category      AS ENUM('cont', 'ero', 'tech');
CREATE TYPE vn_relation       AS ENUM ('seq', 'preq', 'set', 'alt', 'char', 'side', 'par', 'ser', 'fan', 'orig');
CREATE TYPE session_type      AS ENUM ('web', 'pass', 'mail', 'api', 'api2');

CREATE TYPE ipinfo AS (
    ip                 inet,
    country            text,
    asn                integer,
    as_name            text,
    anonymous_proxy    boolean,
    sattelite_provider boolean,
    anycast            boolean,
    drop               boolean
);


CREATE TYPE item_info_type AS (title text[], uid vndbid, hidden boolean, locked boolean);

CREATE TYPE titleprefs AS (
    -- NULL langs means unused slot
    t1_lang     language,
    t2_lang     language,
    t3_lang     language,
    t4_lang     language,
    a1_lang     language,
    a2_lang     language,
    a3_lang     language,
    a4_lang     language,
    -- These should never be NULL
    t1_latin    boolean,
    t2_latin    boolean,
    t3_latin    boolean,
    t4_latin    boolean,
    to_latin    boolean, -- Original language fallback
    a1_latin    boolean,
    a2_latin    boolean,
    a3_latin    boolean,
    a4_latin    boolean,
    ao_latin    boolean,
    -- These have three possible options:
    -- * NULL:  Only if lang == original, i.e. skip this slot if it's not the original language
    -- * true:  Only if official
    -- * false: Use this language regardless of official/original status
    t1_official boolean,
    t2_official boolean,
    t3_official boolean,
    t4_official boolean,
    a1_official boolean,
    a2_official boolean,
    a3_official boolean,
    a4_official boolean
);


-- Animation types & frequency encoded as bitflags in a smallint.
-- Bitflags suck balls, but the alternatives suck too.
-- Special values:
--   NULL  Animation information not known
--      0  No animation
--      1  Animation type does not apply (e.g. VN has no sprites)
-- Otherwise, bit flags:
--      4  type = handrawn
--      8  type = vectorial
--     16  type = 3d
--     32  type = live
--    256  frequency = some scenes
--    512  frequency = all scenes
-- At least one of the 'type' flags must be set.
-- If none of the frequency flags are set -> frequency = unknown.
CREATE DOMAIN animation AS smallint CHECK(value IS NULL OR value IN(0,1) OR ((value & (4+8+16+32)) > 0 AND (value & (256+512)) <> (256+512)));


-- Sequences used for ID generation
CREATE SEQUENCE charimg_seq;
CREATE SEQUENCE chars_id_seq;
CREATE SEQUENCE covers_seq;
CREATE SEQUENCE docs_id_seq;
CREATE SEQUENCE producers_id_seq;
CREATE SEQUENCE releases_id_seq;
CREATE SEQUENCE reviews_seq;
CREATE SEQUENCE screenshots_seq;
CREATE SEQUENCE staff_id_seq;
CREATE SEQUENCE tags_id_seq;
CREATE SEQUENCE traits_id_seq;
CREATE SEQUENCE threads_id_seq;
CREATE SEQUENCE vn_id_seq;
CREATE SEQUENCE users_id_seq;


-- anime
CREATE TABLE anime ( -- Anime information fetched from AniDB, only used for linking with visual novels.
  id           integer NOT NULL PRIMARY KEY, -- [pub] AniDB identifier
  ann_id       integer, -- [pub] Anime News Network identifier
  lastfetch    timestamptz,
  type         anime_type, -- [pub]
  year         smallint, -- [pub]
  nfo_id       varchar(200), -- [pub] AnimeNFO identifier (unused, site is long dead)
  title_romaji varchar(250), -- [pub]
  title_kanji  varchar(250) -- [pub]
);

-- audit_log
CREATE TABLE audit_log (
  date          timestamptz NOT NULL DEFAULT NOW(),
  by_uid        vndbid,
  affected_uid  vndbid,
  by_ip         ipinfo,
  by_name       text,
  affected_name text,
  action        text NOT NULL,
  detail        text
);

-- changes
CREATE TABLE changes (
  id         SERIAL PRIMARY KEY,
  requester  vndbid,
  added      timestamptz NOT NULL DEFAULT NOW(),
  itemid     vndbid NOT NULL,
  rev        integer NOT NULL DEFAULT 1,
  ihid       boolean NOT NULL DEFAULT FALSE,
  ilock      boolean NOT NULL DEFAULT FALSE,
  comments   text NOT NULL DEFAULT ''
);

-- changes_patrolled
CREATE TABLE changes_patrolled (
  id         integer NOT NULL,
  uid        vndbid NOT NULL,
  PRIMARY KEY(id,uid)
);

-- chars
CREATE TABLE chars ( -- dbentry_type=c
  id           vndbid NOT NULL PRIMARY KEY DEFAULT vndbid('c', nextval('chars_id_seq')::int) CONSTRAINT chars_id_check CHECK(vndbid_type(id) = 'c'), -- [pub]
  image        vndbid CONSTRAINT chars_image_check CHECK(vndbid_type(image) = 'ch'), -- [pub]
  gender       gender NOT NULL DEFAULT 'unknown', -- [pub] Character's sex, not gender
  spoil_gender gender, -- [pub] Character's actual sex, in case it's a spoiler
  bloodt       blood_type NOT NULL DEFAULT 'unknown', -- [pub] Blood type
  cup_size     cup_size NOT NULL DEFAULT '', -- [pub]
  main         vndbid, -- [pub] When this character is an instance of another character
  s_bust       smallint NOT NULL DEFAULT 0, -- [pub] cm
  s_waist      smallint NOT NULL DEFAULT 0, -- [pub] cm
  s_hip        smallint NOT NULL DEFAULT 0, -- [pub] cm
  b_month      smallint NOT NULL DEFAULT 0, -- [pub] Birthday month, 1-12
  b_day        smallint NOT NULL DEFAULT 0, -- [pub] Birthday day, 1-32
  height       smallint NOT NULL DEFAULT 0, -- [pub] cm
  weight       smallint, -- [pub] kg
  main_spoil   smallint NOT NULL DEFAULT 0, -- [pub]
  age          smallint, -- [pub] years
  locked       boolean NOT NULL DEFAULT FALSE,
  hidden       boolean NOT NULL DEFAULT FALSE,
  name         varchar(250) NOT NULL DEFAULT '', -- [pub]
  latin        varchar(250), -- [pub]
  alias        varchar(500) NOT NULL DEFAULT '', -- [pub]
  description  text NOT NULL DEFAULT '', -- [pub]
  c_lang       language NOT NULL DEFAULT 'ja'
);

-- chars_hist
CREATE TABLE chars_hist (
  chid         integer  NOT NULL PRIMARY KEY,
  image        vndbid CONSTRAINT chars_hist_image_check CHECK(vndbid_type(image) = 'ch'),
  gender       gender NOT NULL DEFAULT 'unknown',
  spoil_gender gender,
  bloodt       blood_type NOT NULL DEFAULT 'unknown',
  cup_size     cup_size NOT NULL DEFAULT '',
  main         vndbid, -- chars.id
  s_bust       smallint NOT NULL DEFAULT 0,
  s_waist      smallint NOT NULL DEFAULT 0,
  s_hip        smallint NOT NULL DEFAULT 0,
  b_month      smallint NOT NULL DEFAULT 0,
  b_day        smallint NOT NULL DEFAULT 0,
  height       smallint NOT NULL DEFAULT 0,
  weight       smallint,
  main_spoil   smallint NOT NULL DEFAULT 0,
  age          smallint,
  name         varchar(250) NOT NULL DEFAULT '',
  latin        varchar(250),
  alias        varchar(500) NOT NULL DEFAULT '',
  description  text     NOT NULL DEFAULT ''
);

-- chars_traits
CREATE TABLE chars_traits (
  id         vndbid NOT NULL, -- [pub]
  tid        vndbid NOT NULL, -- [pub]
  spoil      smallint NOT NULL DEFAULT 0, -- [pub]
  lie        boolean NOT NULL DEFAULT false, -- [pub]
  PRIMARY KEY(id, tid)
);

-- chars_traits_hist
CREATE TABLE chars_traits_hist (
  chid       integer NOT NULL,
  tid        vndbid NOT NULL, -- traits.id
  spoil      smallint NOT NULL DEFAULT 0,
  lie        boolean NOT NULL DEFAULT false,
  PRIMARY KEY(chid, tid)
);

-- chars_vns
CREATE TABLE chars_vns (
  id         vndbid NOT NULL, -- [pub]
  vid        vndbid NOT NULL, -- [pub]
  rid        vndbid NULL, -- [pub]
  role       char_role NOT NULL DEFAULT 'main', -- [pub]
  spoil      smallint NOT NULL DEFAULT 0 -- [pub]
);

-- chars_vns_hist
CREATE TABLE chars_vns_hist (
  chid       integer NOT NULL,
  vid        vndbid NOT NULL, -- vn.id
  rid        vndbid NULL, -- releases.id
  role       char_role NOT NULL DEFAULT 'main',
  spoil      smallint NOT NULL DEFAULT 0
);

-- docs
CREATE TABLE docs ( -- dbentry_type=d
  id         vndbid NOT NULL PRIMARY KEY DEFAULT vndbid('d', nextval('docs_id_seq')::int) CONSTRAINT docs_id_check CHECK(vndbid_type(id) = 'd') , -- [pub]
  locked     boolean NOT NULL DEFAULT FALSE,
  hidden     boolean NOT NULL DEFAULT FALSE,
  title      varchar(200) NOT NULL DEFAULT '', -- [pub]
  content    text NOT NULL DEFAULT '', -- [pub] In MultiMarkdown format
  html       text -- cache, can be manually updated with util/update-docs-html-cache.pl
);

-- docs_hist
CREATE TABLE docs_hist (
  chid       integer  NOT NULL PRIMARY KEY,
  title      varchar(200) NOT NULL DEFAULT '',
  content    text NOT NULL DEFAULT '',
  html       text -- cache
);

-- drm
CREATE TABLE drm ( -- DRM types, for use with release info
  id          serial PRIMARY KEY, -- [pub]
  c_ref       integer NOT NULL DEFAULT 0, -- [pub] How many release entries use this DRM type
  state       smallint NOT NULL DEFAULT 0, -- 0 = new, 1 = approved, 2 = deleted
  disc        boolean NOT NULL, -- [pub]
  cdkey       boolean NOT NULL, -- [pub]
  activate    boolean NOT NULL, -- [pub]
  alimit      boolean NOT NULL, -- [pub]
  account     boolean NOT NULL, -- [pub]
  online      boolean NOT NULL, -- [pub]
  cloud       boolean NOT NULL, -- [pub]
  physical    boolean NOT NULL, -- [pub]
  name        text NOT NULL, -- [pub]
  description text NOT NULL -- [pub]
);

-- email_optout
CREATE TABLE email_optout (
  mail        uuid, -- hash_email()
  date        timestamptz NOT NULL DEFAULT NOW(),
  PRIMARY KEY (mail)
);

-- global_settings
CREATE TABLE global_settings (
  -- Only permit a single row in this table
  id                    boolean NOT NULL PRIMARY KEY DEFAULT FALSE CONSTRAINT global_settings_single_row CHECK(id),
  -- locks down any DB edits, including image voting and tagging
  lockdown_edit         boolean NOT NULL DEFAULT FALSE,
  -- locks down any forum & review posting
  lockdown_board        boolean NOT NULL DEFAULT FALSE,
  lockdown_registration boolean NOT NULL DEFAULT FALSE
);

-- images
CREATE TABLE images (
  id                vndbid NOT NULL PRIMARY KEY CONSTRAINT images_id_check CHECK(vndbid_type(id) IN('ch', 'cv', 'sf')), -- [pub]
  width             smallint NOT NULL, -- [pub] px
  height            smallint NOT NULL, -- [pub] px
  -- cached columns are marked [pub] for easy querying
  c_votecount       smallint NOT NULL DEFAULT 0, -- [pub]
  c_sexual_avg      smallint NOT NULL DEFAULT 200, -- [pub] 0 - 200, so average vote * 100
  c_sexual_stddev   smallint NOT NULL DEFAULT 0, -- [pub]
  c_violence_avg    smallint NOT NULL DEFAULT 200, -- [pub] 0 - 200
  c_violence_stddev smallint NOT NULL DEFAULT 0, -- [pub]
  c_weight          smallint NOT NULL DEFAULT 0, -- [pub] Random selection weight for the image flagging UI
  c_uids            vndbid[] NOT NULL DEFAULT '{}',
  uploader          vndbid
  -- (technically, c_votecount is redundant as it can be easily derived from
  --  c_uids, but otherwise we'd lose the space to padding anyway)
);

-- image_votes
CREATE TABLE image_votes (
  id       vndbid NOT NULL, -- [pub]
  uid      vndbid, -- [pub]
  date     timestamptz NOT NULL DEFAULT NOW(),-- [pub]
  sexual   smallint NOT NULL CHECK(sexual >= 0 AND sexual <= 2), -- [pub] 0 = safe, 1 = suggestive, 2 = explicit
  violence smallint NOT NULL CHECK(violence >= 0 AND violence <= 2), -- [pub] 0 = tame, 1 = violent, 2 = brutal
  ignore   boolean NOT NULL DEFAULT false -- [pub] Set when overruled by a moderator
);

-- login_throttle
CREATE TABLE login_throttle (
  ip        inet NOT NULL PRIMARY KEY,
  timeout   timestamptz NOT NULL
);

-- notification_subs
CREATE TABLE notification_subs (
  uid         vndbid NOT NULL,
  iid         vndbid NOT NULL,
  -- Indicates a subscription on the creation of a new 'num' for the item, i.e. new post, new comment, new edit.
  -- Affects the following ntypes: dbedit, subedit, pm, post, comment, subpost. Does not affect: dbdel, listdel.
  --   NULL  = Default behavior as if this entry did not have a row; i.e. use users.notify_post / users.notify_comment / users.notify_dbedit settings.
  --   true  = Default behavior + get subedit/subpost notifications for this entry.
  --   false = Disable all affected ntypes for this entry.
  subnum      boolean,
  subreview   boolean NOT NULL DEFAULT false, -- VNs
  subapply    boolean NOT NULL DEFAULT false, -- Traits
  PRIMARY KEY(iid,uid)
);

-- notifications
CREATE TABLE notifications (
  id       serial PRIMARY KEY,
  uid      vndbid NOT NULL,
  date     timestamptz NOT NULL DEFAULT NOW(),
  read     timestamptz,
  iid      vndbid NOT NULL,
  num      integer,
  ntype    notification_ntype[] NOT NULL
);

-- producers
CREATE TABLE producers ( -- dbentry_type=p
  id         vndbid NOT NULL PRIMARY KEY DEFAULT vndbid('p', nextval('producers_id_seq')::int) CONSTRAINT producers_id_check CHECK(vndbid_type(id) = 'p'), -- [pub]
  type       producer_type NOT NULL DEFAULT 'co', -- [pub]
  lang       language NOT NULL DEFAULT 'ja', -- [pub]
  l_wikidata integer, -- [pub]
  locked     boolean NOT NULL DEFAULT FALSE,
  hidden     boolean NOT NULL DEFAULT FALSE,
  name       varchar(200) NOT NULL DEFAULT '', -- [pub]
  latin      varchar(200), -- [pub]
  alias      varchar(500) NOT NULL DEFAULT '', -- [pub]
  website    varchar(1024) NOT NULL DEFAULT '', -- [pub]
  description text NOT NULL DEFAULT '', -- [pub]
  l_wp       varchar(150) -- (deprecated)
);

-- producers_hist
CREATE TABLE producers_hist (
  chid       integer NOT NULL PRIMARY KEY,
  type       producer_type NOT NULL DEFAULT 'co',
  lang       language NOT NULL DEFAULT 'ja',
  l_wikidata integer,
  name       varchar(200) NOT NULL DEFAULT '',
  latin      varchar(200),
  alias      varchar(500) NOT NULL DEFAULT '',
  website    varchar(1024) NOT NULL DEFAULT '',
  description text NOT NULL DEFAULT '',
  l_wp       varchar(150)
);

-- producers_relations
CREATE TABLE producers_relations (
  id         vndbid NOT NULL, -- [pub]
  pid        vndbid NOT NULL, -- [pub]
  relation   producer_relation NOT NULL, -- [pub]
  PRIMARY KEY(id, pid)
);

-- producers_relations_hist
CREATE TABLE producers_relations_hist (
  chid       integer NOT NULL,
  pid        vndbid NOT NULL, -- producers.id
  relation   producer_relation NOT NULL,
  PRIMARY KEY(chid, pid)
);

-- quotes
CREATE TABLE quotes (
  id         serial PRIMARY KEY, -- [pub]
  vid        vndbid NOT NULL, -- [pub]
  cid        vndbid, -- [pub]
  addedby    vndbid,
  rand       real,
  score      smallint NOT NULL DEFAULT 0, -- [pub]
  quote      text NOT NULL, -- [pub]
  hidden     boolean NOT NULL DEFAULT FALSE,
  added      timestamptz NOT NULL DEFAULT NOW()
);

-- quotes_log
CREATE TABLE quotes_log (
  date      timestamptz NOT NULL DEFAULT NOW(),
  id        integer NOT NULL,
  uid       vndbid,
  action    text NOT NULL
);

-- quotes_votes
CREATE TABLE quotes_votes (
  date      timestamptz NOT NULL DEFAULT NOW(),
  id        integer NOT NULL,
  uid       vndbid NOT NULL,
  vote      smallint NOT NULL,
  PRIMARY KEY(id, uid)
);

-- registration_throttle
CREATE TABLE registration_throttle (
  ip        inet NOT NULL PRIMARY KEY,
  timeout   timestamptz NOT NULL
);

-- releases
CREATE TABLE releases ( -- dbentry_type=r
  id           vndbid NOT NULL PRIMARY KEY DEFAULT vndbid('r', nextval('releases_id_seq')::int) CONSTRAINT releases_id_check CHECK(vndbid_type(id) = 'r'), -- [pub]
  olang        language NOT NULL DEFAULT 'ja', -- [pub] Refers to the main title to use for display purposes, not necessarily the original language.
  gtin         bigint NOT NULL DEFAULT 0, -- [pub] JAN/UPC/EAN/ISBN
  l_toranoana  bigint NOT NULL DEFAULT 0, -- [pub]
  l_appstore   bigint NOT NULL DEFAULT 0, -- [pub]
  l_nintendo_jp bigint NOT NULL DEFAULT 0, -- [pub]
  l_nintendo_hk bigint NOT NULL DEFAULT 0, -- [pub]
  released     integer NOT NULL DEFAULT 0, -- [pub]
  l_steam      integer NOT NULL DEFAULT 0, -- [pub]
  l_digiket    integer NOT NULL DEFAULT 0, -- [pub]
  l_melon      integer NOT NULL DEFAULT 0, -- [pub]
  l_mg         integer NOT NULL DEFAULT 0, -- [pub]
  l_getchu     integer NOT NULL DEFAULT 0, -- [pub]
  l_getchudl   integer NOT NULL DEFAULT 0, -- [pub]
  l_egs        integer NOT NULL DEFAULT 0, -- [pub]
  l_erotrail   integer NOT NULL DEFAULT 0, -- [pub] (deprecated, site hasn't been reachable for a while)
  l_melonjp    integer NOT NULL DEFAULT 0, -- [pub]
  l_gamejolt   integer NOT NULL DEFAULT 0, -- [pub]
  l_animateg   integer NOT NULL DEFAULT 0, -- [pub]
  l_freem      integer NOT NULL DEFAULT 0, -- [pub]
  l_novelgam   integer NOT NULL DEFAULT 0, -- [pub]
  voiced       smallint NOT NULL DEFAULT 0, -- [pub]
  reso_x       smallint NOT NULL DEFAULT 0, -- [pub] When reso_x is 0, reso_y is either 0 for 'unknown' or 1 for 'non-standard'.
  reso_y       smallint NOT NULL DEFAULT 0, -- [pub]
  minage       smallint, -- [pub] Age rating, 0 - 18
  ani_story    smallint NOT NULL DEFAULT 0, -- [pub] (old, superseded by the newer ani_* columns)
  ani_ero      smallint NOT NULL DEFAULT 0, -- [pub] (^ but the newer columns haven't been filled out much)
  -- These replace the old ani_story and ani_ero columns.
  ani_story_sp animation, -- [pub] Story sprite animation
  ani_story_cg animation, -- [pub] Story CG animation
  -- "Not animated" and frequency options are irrelevant for ani_cutscene.
  ani_cutscene animation CONSTRAINT releases_cutscene_check CHECK(ani_cutscene <> 0 AND (ani_cutscene & (256+512)) = 0), -- [pub] Cutscene animation
  ani_ero_sp   animation, -- [pub] Ero scene sprite animation
  ani_ero_cg   animation, -- [pub] Ero scene CG animation
  ani_bg       boolean, -- [pub] Background effects
  ani_face     boolean, -- [pub] Eye blink / lip sync
  has_ero      boolean NOT NULL DEFAULT FALSE, -- [pub]
  patch        boolean NOT NULL DEFAULT FALSE, -- [pub]
  freeware     boolean NOT NULL DEFAULT FALSE, -- [pub]
  doujin       boolean NOT NULL DEFAULT FALSE,
  uncensored   boolean, -- [pub]
  official     boolean NOT NULL DEFAULT TRUE, -- [pub]
  locked       boolean NOT NULL DEFAULT FALSE,
  hidden       boolean NOT NULL DEFAULT FALSE,
  website      varchar(1024) NOT NULL DEFAULT '', -- [pub]
  catalog      varchar(50) NOT NULL DEFAULT '', -- [pub]
  engine       varchar(50) NOT NULL DEFAULT '', -- [pub]
  notes        text NOT NULL DEFAULT '', -- [pub]
  l_dlsite     text NOT NULL DEFAULT '', -- [pub]
  l_dlsiteen   text NOT NULL DEFAULT '', -- (deprecated, DLsite doesn't have a separate English shop anymore)
  l_gog        text NOT NULL DEFAULT '', -- [pub]
  l_denpa      text NOT NULL DEFAULT '', -- [pub]
  l_jlist      text NOT NULL DEFAULT '', -- [pub]
  l_jastusa    text NOT NULL DEFAULT '', -- [pub]
  l_itch       text NOT NULL DEFAULT '', -- [pub]
  l_nutaku     text NOT NULL DEFAULT '', -- [pub]
  l_googplay   text NOT NULL DEFAULT '', -- [pub]
  l_fakku      text NOT NULL DEFAULT '', -- [pub]
  l_freegame   text NOT NULL DEFAULT '', -- [pub]
  l_playstation_jp text NOT NULL DEFAULT '', -- [pub]
  l_playstation_na text NOT NULL DEFAULT '', -- [pub]
  l_playstation_eu text NOT NULL DEFAULT '',  -- [pub]
  l_playstation_hk text NOT NULL DEFAULT '', -- [pub]
  l_nintendo    text NOT NULL DEFAULT '', -- [pub]
  l_gyutto     integer[] NOT NULL DEFAULT '{}', -- [pub]
  l_dmm        text[] NOT NULL DEFAULT '{}', -- [pub]
  l_booth      integer NOT NULL DEFAULT 0, -- [pub]
  l_patreonp   integer NOT NULL DEFAULT 0, -- [pub]
  l_patreon    text NOT NULL DEFAULT '', -- [pub]
  l_substar    text NOT NULL DEFAULT '' -- [pub]
);

-- releases_hist
CREATE TABLE releases_hist (
  chid         integer NOT NULL PRIMARY KEY,
  olang        language NOT NULL DEFAULT 'ja',
  gtin         bigint NOT NULL DEFAULT 0,
  l_toranoana  bigint NOT NULL DEFAULT 0,
  l_appstore   bigint NOT NULL DEFAULT 0,
  l_nintendo_jp bigint NOT NULL DEFAULT 0,
  l_nintendo_hk bigint NOT NULL DEFAULT 0,
  released     integer NOT NULL DEFAULT 0,
  l_steam      integer NOT NULL DEFAULT 0,
  l_digiket    integer NOT NULL DEFAULT 0,
  l_melon      integer NOT NULL DEFAULT 0,
  l_mg         integer NOT NULL DEFAULT 0,
  l_getchu     integer NOT NULL DEFAULT 0,
  l_getchudl   integer NOT NULL DEFAULT 0,
  l_egs        integer NOT NULL DEFAULT 0,
  l_erotrail   integer NOT NULL DEFAULT 0,
  l_melonjp    integer NOT NULL DEFAULT 0,
  l_gamejolt   integer NOT NULL DEFAULT 0,
  l_animateg   integer NOT NULL DEFAULT 0,
  l_freem      integer NOT NULL DEFAULT 0,
  l_novelgam   integer NOT NULL DEFAULT 0,
  voiced       smallint NOT NULL DEFAULT 0,
  reso_x       smallint NOT NULL DEFAULT 0,
  reso_y       smallint NOT NULL DEFAULT 0,
  minage       smallint,
  ani_story    smallint NOT NULL DEFAULT 0,
  ani_ero      smallint NOT NULL DEFAULT 0,
  ani_story_sp animation,
  ani_story_cg animation,
  ani_cutscene animation,
  ani_ero_sp   animation,
  ani_ero_cg   animation,
  ani_bg       boolean,
  ani_face     boolean,
  has_ero      boolean NOT NULL DEFAULT FALSE,
  patch        boolean NOT NULL DEFAULT FALSE,
  freeware     boolean NOT NULL DEFAULT FALSE,
  doujin       boolean NOT NULL DEFAULT FALSE,
  uncensored   boolean,
  official     boolean NOT NULL DEFAULT TRUE,
  website      varchar(1024) NOT NULL DEFAULT '',
  catalog      varchar(50) NOT NULL DEFAULT '',
  engine       varchar(50) NOT NULL DEFAULT '',
  notes        text NOT NULL DEFAULT '',
  l_dlsite     text NOT NULL DEFAULT '',
  l_dlsiteen   text NOT NULL DEFAULT '',
  l_gog        text NOT NULL DEFAULT '',
  l_denpa      text NOT NULL DEFAULT '',
  l_jlist      text NOT NULL DEFAULT '',
  l_jastusa    text NOT NULL DEFAULT '',
  l_itch       text NOT NULL DEFAULT '',
  l_nutaku     text NOT NULL DEFAULT '',
  l_googplay   text NOT NULL DEFAULT '',
  l_fakku      text NOT NULL DEFAULT '',
  l_freegame   text NOT NULL DEFAULT '',
  l_playstation_jp text NOT NULL DEFAULT '',
  l_playstation_na text NOT NULL DEFAULT '',
  l_playstation_eu text NOT NULL DEFAULT '',
  l_playstation_hk text NOT NULL DEFAULT '',
  l_nintendo    text NOT NULL DEFAULT '',
  l_gyutto     integer[] NOT NULL DEFAULT '{}',
  l_dmm        text[] NOT NULL DEFAULT '{}',
  l_booth      integer NOT NULL DEFAULT 0,
  l_patreonp   integer NOT NULL DEFAULT 0,
  l_patreon    text NOT NULL DEFAULT '',
  l_substar    text NOT NULL DEFAULT ''
);

-- releases_drm
CREATE TABLE releases_drm (
  id      vndbid NOT NULL, -- [pub]
  drm     integer NOT NULL, -- [pub]
  notes   text NOT NULL DEFAULT '', -- [pub]
  PRIMARY KEY(id, drm)
);

-- releases_drm_hist
CREATE TABLE releases_drm_hist (
  chid    integer NOT NULL,
  drm     integer NOT NULL,
  notes   text NOT NULL DEFAULT '',
  PRIMARY KEY(chid, drm)
);

-- releases_media
CREATE TABLE releases_media (
  id         vndbid NOT NULL, -- [pub]
  medium     medium NOT NULL, -- [pub]
  qty        smallint NOT NULL DEFAULT 1, -- [pub]
  PRIMARY KEY(id, medium, qty)
);

-- releases_media_hist
CREATE TABLE releases_media_hist (
  chid       integer NOT NULL,
  medium     medium NOT NULL,
  qty        smallint NOT NULL DEFAULT 1,
  PRIMARY KEY(chid, medium, qty)
);

-- releases_platforms
CREATE TABLE releases_platforms (
  id         vndbid NOT NULL, -- [pub]
  platform   platform NOT NULL, -- [pub]
  PRIMARY KEY(id, platform)
);

-- releases_platforms_hist
CREATE TABLE releases_platforms_hist (
  chid       integer NOT NULL,
  platform   platform NOT NULL,
  PRIMARY KEY(chid, platform)
);

-- releases_producers
CREATE TABLE releases_producers (
  id         vndbid NOT NULL, -- [pub]
  pid        vndbid NOT NULL, -- [pub]
  developer  boolean NOT NULL DEFAULT FALSE, -- [pub]
  publisher  boolean NOT NULL DEFAULT TRUE, -- [pub]
  CONSTRAINT releases_producers_check1 CHECK(developer OR publisher),
  PRIMARY KEY(id, pid)
);

-- releases_producers_hist
CREATE TABLE releases_producers_hist (
  chid       integer NOT NULL,
  pid        vndbid NOT NULL, -- producers.id
  developer  boolean NOT NULL DEFAULT FALSE,
  publisher  boolean NOT NULL DEFAULT TRUE,
  CHECK(developer OR publisher),
  PRIMARY KEY(chid, pid)
);

-- releases_titles (also: languages this release is available in)
CREATE TABLE releases_titles (
  id         vndbid NOT NULL, -- [pub]
  lang       language NOT NULL, -- [pub]
  mtl        boolean NOT NULL DEFAULT false, -- [pub]
  title      text, -- [pub]
  latin      text, -- [pub]
  PRIMARY KEY(id, lang)
);

-- releases_titles_hist
CREATE TABLE releases_titles_hist (
  chid       integer NOT NULL,
  lang       language NOT NULL,
  mtl        boolean NOT NULL DEFAULT false,
  title      text,
  latin      text,
  PRIMARY KEY(chid, lang)
);

-- releases_vn
CREATE TABLE releases_vn (
  id         vndbid NOT NULL, -- [pub]
  vid        vndbid NOT NULL, -- [pub]
  rtype      release_type NOT NULL, -- [pub]
  PRIMARY KEY(id, vid)
);

-- releases_vn_hist
CREATE TABLE releases_vn_hist (
  chid       integer NOT NULL,
  vid        vndbid NOT NULL, -- vn.id
  rtype      release_type NOT NULL,
  PRIMARY KEY(chid, vid)
);

-- reports
CREATE TABLE reports (
  id         SERIAL PRIMARY KEY,
  status     report_status NOT NULL DEFAULT 'new',
  date       timestamptz NOT NULL DEFAULT NOW(),
  lastmod    timestamptz,
  uid        vndbid, -- user who created the report, if logged in
  object     vndbid NOT NULL, -- The id of the thing being reported
  objectnum  integer, -- The sub-id of the thing to be reported
  ip         ipinfo, -- IP address of the visitor, if not logged in
  reason     text NOT NULL,
  message    text NOT NULL,
  log        text NOT NULL DEFAULT '' -- replaced by reports_log for new reports
);

-- reports_log
CREATE TABLE reports_log (
  date       timestamptz NOT NULL DEFAULT NOW(),
  id         integer NOT NULL,
  status     report_status NOT NULL,
  uid        vndbid,
  message    text NOT NULL
);

-- reset_throttle
CREATE TABLE reset_throttle (
  ip        inet NOT NULL PRIMARY KEY,
  timeout   timestamptz NOT NULL
);

-- reviews
CREATE TABLE reviews (
  id         vndbid PRIMARY KEY DEFAULT vndbid('w', nextval('reviews_seq')::int) CONSTRAINT reviews_id_check CHECK(vndbid_type(id) = 'w'),
  vid        vndbid NOT NULL,
  date       timestamptz NOT NULL DEFAULT NOW(),
  lastmod    timestamptz,
  uid        vndbid,
  rid        vndbid,
  c_up       integer NOT NULL DEFAULT 0,
  c_down     integer NOT NULL DEFAULT 0,
  c_count    smallint NOT NULL DEFAULT 0,
  c_lastnum  smallint,
  spoiler    boolean NOT NULL,
  isfull     boolean NOT NULL,
  locked     boolean NOT NULL DEFAULT false,
  c_flagged  boolean NOT NULL DEFAULT false,
  text       text NOT NULL,
  modnote    text NOT NULL DEFAULT ''
);

-- reviews_posts
CREATE TABLE reviews_posts (
  date     timestamptz NOT NULL DEFAULT NOW(),
  edited   timestamptz,
  id       vndbid NOT NULL,
  uid      vndbid,
  num      smallint NOT NULL,
  hidden   text,
  msg      text NOT NULL DEFAULT '',
  PRIMARY KEY(id, num)
);

-- reviews_votes
CREATE TABLE reviews_votes (
  date      timestamptz NOT NULL,
  id        vndbid NOT NULL,
  uid       vndbid,
  vote      boolean NOT NULL, -- true = upvote, false = downvote
  overrule  boolean NOT NULL DEFAULT false,
  ip        inet -- Only for anonymous votes
);

-- rlists
CREATE TABLE rlists ( -- User's releases list
  uid      vndbid NOT NULL, -- [pub]
  rid      vndbid NOT NULL, -- [pub]
  added    timestamptz NOT NULL DEFAULT NOW(), -- [pub]
  status   smallint NOT NULL DEFAULT 0, -- [pub] 0 = Unknown, 1 = Pending, 2 = Obtained, 3 = On loan, 4 = Deleted
  PRIMARY KEY(uid, rid)
);

-- saved_queries
CREATE TABLE saved_queries (
  uid     vndbid NOT NULL,
  qtype   dbentry_type NOT NULL,
  name    text NOT NULL, -- Empty string is the users' default filter for the given qtype
  query   text NOT NULL, -- compact encoded form
  PRIMARY KEY(uid, qtype, name)
);

-- search_cache
CREATE TABLE search_cache (
    id    vndbid NOT NULL,
    subid integer, -- only for staff_alias.id at the moment
    prio  smallint NOT NULL, -- 1 for indirect titles, 2 for aliases, 3 for main titles
    label text NOT NULL COLLATE "C"
) PARTITION BY RANGE(id);

CREATE TABLE search_cache_v PARTITION OF search_cache FOR VALUES FROM ('v1') TO (vndbid_max('v'));
CREATE TABLE search_cache_r PARTITION OF search_cache FOR VALUES FROM ('r1') TO (vndbid_max('r'));
CREATE TABLE search_cache_c PARTITION OF search_cache FOR VALUES FROM ('c1') TO (vndbid_max('c'));
CREATE TABLE search_cache_p PARTITION OF search_cache FOR VALUES FROM ('p1') TO (vndbid_max('p'));
CREATE TABLE search_cache_s PARTITION OF search_cache FOR VALUES FROM ('s1') TO (vndbid_max('s'));
CREATE TABLE search_cache_g PARTITION OF search_cache FOR VALUES FROM ('g1') TO (vndbid_max('g'));
CREATE TABLE search_cache_i PARTITION OF search_cache FOR VALUES FROM ('i1') TO (vndbid_max('i'));

-- sessions
CREATE TABLE sessions (
  uid      vndbid NOT NULL,
  type     session_type NOT NULL,
  added    timestamptz NOT NULL DEFAULT NOW(),
  expires  timestamptz NOT NULL, -- 'api2' tokens don't expire, this column is used for last-use tracking
  token    bytea NOT NULL,
  mail     text,
  notes    text,
  listread boolean NOT NULL DEFAULT false,
  listwrite boolean NOT NULL DEFAULT false,
  PRIMARY KEY (uid, token)
);

-- shop_denpa
CREATE TABLE shop_denpa (
  lastfetch  timestamptz,
  deadsince  timestamptz,
  id         text NOT NULL PRIMARY KEY,
  sku        text NOT NULL DEFAULT '',
  price      text NOT NULL DEFAULT ''
);

-- shop_dlsite
CREATE TABLE shop_dlsite (
  lastfetch  timestamptz,
  deadsince  timestamptz,
  id         text NOT NULL PRIMARY KEY,
  shop       text NOT NULL DEFAULT '',
  price      text NOT NULL DEFAULT ''
);

-- shop_jastusa
CREATE TABLE shop_jastusa (
  lastfetch  timestamptz,
  deadsince  timestamptz,
  id         text NOT NULL PRIMARY KEY,
  price      text NOT NULL DEFAULT '',
  slug       text NOT NULL DEFAULT ''
);

-- shop_jlist
CREATE TABLE shop_jlist (
  lastfetch  timestamptz,
  deadsince  timestamptz,
  id         text NOT NULL PRIMARY KEY,
  price      text NOT NULL DEFAULT '' -- empty when unknown or not in stock
);

-- shop_mg
CREATE TABLE shop_mg (
  lastfetch  timestamptz,
  deadsince  timestamptz,
  id         integer NOT NULL PRIMARY KEY,
  r18        boolean NOT NULL DEFAULT true,
  price      text NOT NULL DEFAULT ''
);

-- shop_playasia
CREATE TABLE shop_playasia (
  gtin       bigint NOT NULL,
  lastfetch  timestamptz,
  pax        text NOT NULL PRIMARY KEY,
  url        text NOT NULL DEFAULT '',
  price      text NOT NULL DEFAULT ''
);

-- shop_playasia_gtin
CREATE TABLE shop_playasia_gtin (
  gtin       bigint NOT NULL PRIMARY KEY,
  lastfetch  timestamptz
);

-- staff
CREATE TABLE staff ( -- dbentry_type=s
  id          vndbid NOT NULL PRIMARY KEY DEFAULT vndbid('s', nextval('staff_id_seq')::int) CONSTRAINT staff_id_check CHECK(vndbid_type(id) = 's'), -- [pub]
  gender      gender NOT NULL DEFAULT 'unknown', -- [pub]
  lang        language NOT NULL DEFAULT 'ja', -- [pub]
  main        integer NOT NULL DEFAULT 0, -- [pub] Primary name for the staff entry
  l_anidb     integer, -- [pub]
  l_wikidata  integer, -- [pub]
  l_pixiv     integer NOT NULL DEFAULT 0, -- [pub]
  locked      boolean NOT NULL DEFAULT FALSE,
  hidden      boolean NOT NULL DEFAULT FALSE,
  description text NOT NULL DEFAULT '', -- [pub]
  l_wp        varchar(150) NOT NULL DEFAULT '', -- (deprecated)
  l_site      varchar(250) NOT NULL DEFAULT '', -- [pub]
  l_twitter   varchar(16) NOT NULL DEFAULT '', -- [pub]
  l_vgmdb     integer NOT NULL DEFAULT 0, -- [pub]
  l_discogs   integer NOT NULL DEFAULT 0, -- [pub]
  l_mobygames integer NOT NULL DEFAULT 0, -- [pub]
  l_bgmtv     integer NOT NULL DEFAULT 0, -- [pub]
  l_imdb      integer NOT NULL DEFAULT 0, -- [pub]
  l_vndb      vndbid, -- [pub]
  l_mbrainz   uuid, -- [pub]
  l_scloud    text NOT NULL DEFAULT '' -- [pub]
);

-- staff_hist
CREATE TABLE staff_hist (
  chid        integer NOT NULL PRIMARY KEY,
  gender      gender NOT NULL DEFAULT 'unknown',
  lang        language NOT NULL DEFAULT 'ja',
  main        integer NOT NULL DEFAULT 0, -- Can't refer to staff_alias.id, because the alias might have been deleted
  l_anidb     integer,
  l_wikidata  integer,
  l_pixiv     integer NOT NULL DEFAULT 0,
  description text NOT NULL DEFAULT '',
  l_wp        varchar(150) NOT NULL DEFAULT '',
  l_site      varchar(250) NOT NULL DEFAULT '',
  l_twitter   varchar(16) NOT NULL DEFAULT '',
  l_vgmdb     integer NOT NULL DEFAULT 0,
  l_discogs   integer NOT NULL DEFAULT 0,
  l_mobygames integer NOT NULL DEFAULT 0,
  l_bgmtv     integer NOT NULL DEFAULT 0,
  l_imdb      integer NOT NULL DEFAULT 0,
  l_vndb      vndbid,
  l_mbrainz   uuid,
  l_scloud    text NOT NULL DEFAULT ''
);

-- staff_alias
CREATE TABLE staff_alias (
  id         vndbid NOT NULL, -- [pub]
  aid        SERIAL PRIMARY KEY, -- [pub] Globally unique ID of this alias
  name       varchar(200) NOT NULL DEFAULT '', -- [pub]
  latin      varchar(200) -- [pub]
);

-- staff_alias_hist
CREATE TABLE staff_alias_hist (
  chid       integer NOT NULL,
  aid        integer NOT NULL, -- staff_alias.aid, but can't reference it because the alias may have been deleted
  name       varchar(200) NOT NULL DEFAULT '',
  latin      varchar(200),
  PRIMARY KEY(chid, aid)
);

-- stats_cache
CREATE TABLE stats_cache (
  section varchar(25) NOT NULL PRIMARY KEY,
  count   integer NOT NULL DEFAULT 0
);

-- tags
CREATE TABLE tags ( -- dbentry_type=g
  id           vndbid NOT NULL PRIMARY KEY DEFAULT vndbid('g', nextval('tags_id_seq')::int) CONSTRAINT tags_id_check CHECK(vndbid_type(id) = 'g'), -- [pub]
  cat          tag_category NOT NULL DEFAULT 'cont', -- [pub]
  added        timestamptz NOT NULL DEFAULT NOW(), -- Tag creation time. Relic of a long forgotten past where changes to tag entries weren't logged.
  c_items      integer NOT NULL DEFAULT 0,
  defaultspoil smallint NOT NULL DEFAULT 0, -- [pub]
  locked       boolean NOT NULL DEFAULT FALSE,
  hidden       boolean NOT NULL DEFAULT TRUE,
  searchable   boolean NOT NULL DEFAULT TRUE, -- [pub]
  applicable   boolean NOT NULL DEFAULT TRUE, -- [pub]
  name         varchar(250) NOT NULL DEFAULT '' UNIQUE, -- [pub]
  alias        varchar(500) NOT NULL DEFAULT '', -- [pub]
  description  text NOT NULL DEFAULT '' -- [pub]
);

-- tags_hist
CREATE TABLE tags_hist (
  chid         integer NOT NULL PRIMARY KEY,
  cat          tag_category NOT NULL DEFAULT 'cont',
  defaultspoil smallint NOT NULL DEFAULT 0,
  searchable   boolean NOT NULL DEFAULT TRUE,
  applicable   boolean NOT NULL DEFAULT TRUE,
  name         varchar(250) NOT NULL DEFAULT '',
  alias        varchar(500) NOT NULL DEFAULT '',
  description  text NOT NULL DEFAULT ''
);

-- tags_parents
CREATE TABLE tags_parents (
  id       vndbid NOT NULL, -- [pub]
  parent   vndbid NOT NULL, -- [pub]
  main     boolean NOT NULL DEFAULT false, -- [pub]
  PRIMARY KEY(id, parent)
);

-- tags_parents_hist
CREATE TABLE tags_parents_hist (
  chid     integer NOT NULL,
  parent   vndbid NOT NULL,
  main     boolean NOT NULL DEFAULT false,
  PRIMARY KEY(chid, parent)
);

-- tags_vn
CREATE TABLE tags_vn (
  date     timestamptz NOT NULL DEFAULT NOW(), -- [pub]
  tag      vndbid NOT NULL, -- [pub]
  vid      vndbid NOT NULL, -- [pub]
  uid      vndbid, -- [pub]
  vote     smallint NOT NULL DEFAULT 3 CHECK (vote >= -3 AND vote <= 3 AND vote <> 0), -- [pub] negative for downvote, 1-3 otherwise
  spoiler  smallint CHECK(spoiler >= 0 AND spoiler <= 2), -- [pub]
  ignore   boolean NOT NULL DEFAULT false, -- [pub]
  lie      boolean, -- [pub] implies spoiler=0
  notes    text NOT NULL DEFAULT '' -- [pub]
);

-- tags_vn_direct
CREATE TABLE tags_vn_direct (
  tag     vndbid NOT NULL,
  vid     vndbid NOT NULL,
  rating  real NOT NULL,
  spoiler smallint NOT NULL,
  lie     boolean NOT NULL,
  count   smallint NOT NULL,
  PRIMARY KEY(tag, vid)
);

-- tags_vn_inherit
CREATE TABLE tags_vn_inherit (
  tag     vndbid NOT NULL,
  vid     vndbid NOT NULL,
  rating  real NOT NULL,
  spoiler smallint NOT NULL,
  lie     boolean NOT NULL,
  PRIMARY KEY(tag, vid)
);

-- threads
CREATE TABLE threads (
  id               vndbid PRIMARY KEY DEFAULT vndbid('t', nextval('threads_id_seq')::int) CONSTRAINT threads_id_check CHECK(vndbid_type(id) = 't'),
  poll_max_options smallint NOT NULL DEFAULT 1,
  c_count          smallint NOT NULL DEFAULT 0, -- Number of non-hidden posts
  c_lastnum        smallint NOT NULL DEFAULT 1, -- 'num' of the most recent non-hidden post
  locked           boolean NOT NULL DEFAULT FALSE,
  hidden           boolean NOT NULL DEFAULT FALSE,
  private          boolean NOT NULL DEFAULT FALSE,
  boards_locked    boolean NOT NULL DEFAULT FALSE,
  title            varchar(50) NOT NULL DEFAULT '',
  poll_question    varchar(100)
);

-- threads_poll_options
CREATE TABLE threads_poll_options (
  id       SERIAL PRIMARY KEY,
  tid      vndbid NOT NULL,
  option   varchar(100) NOT NULL
);

-- threads_poll_votes
CREATE TABLE threads_poll_votes (
  uid      vndbid NOT NULL,
  optid    integer NOT NULL,
  date     timestamptz DEFAULT NOW(),
  PRIMARY KEY (optid, uid)
);

-- threads_posts
CREATE TABLE threads_posts (
  date     timestamptz NOT NULL DEFAULT NOW(),
  edited   timestamptz,
  tid      vndbid NOT NULL,
  uid      vndbid,
  num      smallint NOT NULL,
  hidden   text,
  msg      text NOT NULL DEFAULT '',
  PRIMARY KEY(tid, num),
  CONSTRAINT threads_posts_first_nonhidden CHECK(num > 1 OR hidden IS NULL)
);

-- threads_boards
CREATE TABLE threads_boards (
  tid   vndbid NOT NULL,
  type  board_type NOT NULL,
  iid   vndbid
);

-- trace_log
CREATE TABLE trace_log (
  date      timestamptz NOT NULL DEFAULT NOW(),
  line      integer,
  sql_num   integer,
  sql_time  real,
  perl_time real,
  has_txn   boolean,
  loggedin  boolean,
  method    text NOT NULL,
  path      text NOT NULL,
  query     text NOT NULL DEFAULT '',
  module    text,
  js        text[]
);

-- traits
CREATE TABLE traits ( -- dbentry_type=i
  id            vndbid NOT NULL PRIMARY KEY DEFAULT vndbid('i', nextval('traits_id_seq')::int) CONSTRAINT traits_id_check CHECK(vndbid_type(id) = 'i'), -- [pub]
  c_items       integer NOT NULL DEFAULT 0,
  added         timestamptz NOT NULL DEFAULT NOW(),
  gid           vndbid, -- [pub] Trait group (technically a cached column, main parent's root trait)
  gorder        smallint NOT NULL DEFAULT 0, -- [pub] Group order, only used when gid IS NULL
  defaultspoil  smallint NOT NULL DEFAULT 0, -- [pub]
  hidden        boolean NOT NULL DEFAULT TRUE,
  locked        boolean NOT NULL DEFAULT FALSE,
  sexual        boolean NOT NULL DEFAULT false, -- [pub]
  searchable    boolean NOT NULL DEFAULT true, -- [pub]
  applicable    boolean NOT NULL DEFAULT true, -- [pub]
  name          varchar(250) NOT NULL DEFAULT '', -- [pub]
  alias         varchar(500) NOT NULL DEFAULT '', -- [pub]
  description   text NOT NULL DEFAULT '' -- [pub]
);

-- traits_hist
CREATE TABLE traits_hist (
  chid          integer NOT NULL,
  gorder        smallint NOT NULL DEFAULT 0,
  defaultspoil  smallint NOT NULL DEFAULT 0,
  sexual        boolean NOT NULL DEFAULT false,
  searchable    boolean NOT NULL DEFAULT true,
  applicable    boolean NOT NULL DEFAULT true,
  name          varchar(250) NOT NULL DEFAULT '',
  alias         varchar(500) NOT NULL DEFAULT '',
  description   text NOT NULL DEFAULT ''
);

-- traits_chars
-- This table is a cache for the data in chars_traits and includes child traits
-- into parent traits. In order to improve performance, there are no foreign
-- key constraints on this table.
CREATE TABLE traits_chars (
  cid    vndbid NOT NULL,  -- chars (id)
  tid    vndbid NOT NULL,  -- traits (id)
  spoil  smallint NOT NULL DEFAULT 0,
  lie    boolean NOT NULL DEFAULT false,
  PRIMARY KEY (tid, cid)
);

-- traits_parents
CREATE TABLE traits_parents (
  id       vndbid NOT NULL, -- [pub]
  parent   vndbid NOT NULL, -- [pub]
  main     boolean NOT NULL DEFAULT false, -- [pub]
  PRIMARY KEY(id, parent)
);

-- traits_parents_hist
CREATE TABLE traits_parents_hist (
  chid     integer NOT NULL,
  parent   vndbid NOT NULL,
  main     boolean NOT NULL DEFAULT false,
  PRIMARY KEY(chid, parent)
);

-- ulist_labels
CREATE TABLE ulist_labels ( -- User labels assigned to visual novels
  uid      vndbid NOT NULL, -- [pub]
  id       smallint NOT NULL, -- [pub] 0 < builtin < 10 <= custom, ids are reused
  private  boolean NOT NULL,
  label    text NOT NULL, -- [pub]
  PRIMARY KEY(uid, id)
);

-- ulist_vns
-- XXX: dbdump.pl has a custom query for this table, make sure to sync that when adding/removing [pub] columns.
CREATE TABLE ulist_vns ( -- User's VN lists
  uid         vndbid NOT NULL, -- [pub]
  vid         vndbid NOT NULL, -- [pub]
  added       timestamptz NOT NULL DEFAULT NOW(), -- [pub]
  lastmod     timestamptz NOT NULL DEFAULT NOW(), -- [pub] updated when any column in this row has changed
  vote_date   timestamptz, -- [pub] Not updated when the vote is changed
  started     date, -- [pub]
  finished    date, -- [pub]
  vote        smallint CHECK(vote IS NULL OR vote BETWEEN 10 AND 100), -- [pub] 0 - 100
  -- Cache, equivalent to 'coalesce(bool_and(private), true)' on the labels.
  -- Updated by update_users_ulist_private(), which MUST be called any time:
  -- * when a label's private flag has been changed, or
  -- * when the 'vote' or 'labels' column has been changed
  -- There's no triggers for this (yet).
  c_private   boolean NOT NULL DEFAULT true,
  notes       text NOT NULL DEFAULT '', -- [pub]
  -- The 'Voted' label (id 7) is special: it is included in this array, but
  -- actually redundant with the 'vote' column. The 'ulist_voted_label' trigger
  -- ensures that the label is added/removed automatically when the 'vote'
  -- column is changed.
  -- In public database dumps, the voted label is not included if the label is
  -- flagged as private, even if a 'vote' is set.
  -- This array is sorted, for no real reason.
  labels      smallint[] NOT NULL DEFAULT '{}', -- [pub]
  PRIMARY KEY(uid, vid)
);

-- users
CREATE TABLE users (
  registered          timestamptz NOT NULL DEFAULT NOW(),
  id                  vndbid NOT NULL PRIMARY KEY DEFAULT vndbid('u', nextval('users_id_seq')::int) CONSTRAINT users_id_check CHECK(vndbid_type(id) = 'u'), -- [pub]
  c_votes             integer NOT NULL DEFAULT 0,
  c_changes           integer NOT NULL DEFAULT 0,
  c_tags              integer NOT NULL DEFAULT 0,
  c_vns               integer NOT NULL DEFAULT 0,
  c_wish              integer NOT NULL DEFAULT 0,
  c_imgvotes          integer NOT NULL DEFAULT 0,
  ign_votes           boolean NOT NULL DEFAULT false, -- [pub] Set when user's votes are ignored
  email_confirmed     boolean NOT NULL DEFAULT false,
  notify_dbedit       boolean NOT NULL DEFAULT true,
  notify_announce     boolean NOT NULL DEFAULT false,
  notify_post         boolean NOT NULL DEFAULT true,
  notify_comment      boolean NOT NULL DEFAULT true,
  nodistract_can      boolean NOT NULL DEFAULT false,
  nodistract_noads    boolean NOT NULL DEFAULT false,
  nodistract_nofancy  boolean NOT NULL DEFAULT false,
  support_can         boolean NOT NULL DEFAULT false,
  support_enabled     boolean NOT NULL DEFAULT false,
  uniname_can         boolean NOT NULL DEFAULT false,
  pubskin_can         boolean NOT NULL DEFAULT false,
  pubskin_enabled     boolean NOT NULL DEFAULT false,
  perm_board          boolean NOT NULL DEFAULT true,
  perm_boardmod       boolean NOT NULL DEFAULT false,
  perm_dbmod          boolean NOT NULL DEFAULT false,
  perm_edit           boolean NOT NULL DEFAULT true,
  perm_imgvote        boolean NOT NULL DEFAULT true, -- [pub] User's image votes don't count when false
  perm_tag            boolean NOT NULL DEFAULT true, -- [pub] User's tag votes don't count when false
  perm_tagmod         boolean NOT NULL DEFAULT false,
  perm_review         boolean NOT NULL DEFAULT true,
  perm_lengthvote     boolean NOT NULL DEFAULT true, -- [pub] User's length votes don't count when false
  username            varchar(20), -- [pub]
  uniname             text NOT NULL DEFAULT ''
);

-- Additional, less frequently accessed fields for the 'users' table.
-- (Separated to debloat the main users table, which is often used in JOINs)
CREATE TABLE users_prefs (
  customcss_csum      bigint NOT NULL DEFAULT 0, -- hash of 'customcss'
  id                  vndbid NOT NULL PRIMARY KEY,
  max_sexual          smallint NOT NULL DEFAULT 0,
  max_violence        smallint NOT NULL DEFAULT 0,
  last_reports        timestamptz, -- For mods: Most recent activity seen on the reports listing
  tableopts_c         integer,
  tableopts_v         integer,
  tableopts_vt        integer, -- VN listing on tag pages
  spoilers            smallint NOT NULL DEFAULT 0,
  tags_all            boolean NOT NULL DEFAULT false,
  tags_cont           boolean NOT NULL DEFAULT true,
  tags_ero            boolean NOT NULL DEFAULT false,
  tags_tech           boolean NOT NULL DEFAULT true,
  traits_sexual       boolean NOT NULL DEFAULT false,
  prodrelexpand       boolean NOT NULL DEFAULT true,
  vnrel_olang         boolean NOT NULL DEFAULT true,
  vnrel_mtl           boolean NOT NULL DEFAULT false,
  staffed_olang       boolean NOT NULL DEFAULT true,
  staffed_unoff       boolean NOT NULL DEFAULT false,
  skin                text NOT NULL DEFAULT '',
  customcss           text NOT NULL DEFAULT '',
  timezone            text NOT NULL DEFAULT '',
  ulist_votes         jsonb,
  ulist_vnlist        jsonb,
  ulist_wish          jsonb,
  vnlang              jsonb, -- Deprecated, replaced by vnrel_x. '$lang(-mtl)?' => true/false, which languages to expand/collapse on VN pages
  title_langs         jsonb, -- Deprecated, replaced by 'titles'
  alttitle_langs      jsonb, -- Deprecated, replaced by 'titles'
  vnrel_langs         language[], -- NULL meaning "show all languages"
  staffed_langs       language[],
  titles              titleprefs
);

-- users_prefs_tags
CREATE TABLE users_prefs_tags (
  id     vndbid NOT NULL,
  tid    vndbid NOT NULL,
  spoil  smallint, -- 0 = always show, 3 = always hide
  childs boolean NOT NULL,
  color  text, -- NULL / 'standout' / 'grayedout' / '#customcolor'
  PRIMARY KEY(id, tid)
);

-- users_prefs_traits
CREATE TABLE users_prefs_traits (
  id     vndbid NOT NULL,
  tid    vndbid NOT NULL,
  spoil  smallint,
  childs boolean NOT NULL,
  color  text,
  PRIMARY KEY(id, tid)
);

-- Additional fields for the 'users' table, but with some protected columns.
-- (Separated from the users table to simplify permission management)
CREATE TABLE users_shadow (
  id             vndbid NOT NULL PRIMARY KEY,
  -- Usermods can see other users' mail and edit their passwords, so this
  -- permission is separated in this table to prevent unauthorized writes.
  perm_usermod   boolean NOT NULL DEFAULT false,
  mail           varchar(100) NOT NULL,
  -- A valid passwd column is 46 bytes:
  --   4 bytes: N (big endian)
  --   1 byte: r
  --   1 byte: p
  --   8 bytes: salt
  --   32 bytes: scrypt(passwd, global_salt + salt, N, r, p, 32)
  -- Anything else is invalid, account disabled.
  passwd         bytea NOT NULL DEFAULT '',
  ip             ipinfo,
  delete_at      timestamptz
);

-- users_traits
CREATE TABLE users_traits (
  id  vndbid NOT NULL,
  tid vndbid NOT NULL,
  PRIMARY KEY(id, tid)
);

-- users_username_hist
CREATE TABLE users_username_hist (
  date  timestamptz NOT NULL DEFAULT NOW(),
  id    vndbid NOT NULL,
  old   text NOT NULL,
  new   text NOT NULL,
  PRIMARY KEY(id, date)
);

-- vn
CREATE TABLE vn ( -- dbentry_type=v
  id            vndbid NOT NULL PRIMARY KEY DEFAULT vndbid('v', nextval('vn_id_seq')::int) CONSTRAINT vn_id_check CHECK(vndbid_type(id) = 'v'), -- [pub]
  olang         language NOT NULL DEFAULT 'ja', -- [pub] Original language
  image         vndbid CONSTRAINT vn_image_check CHECK(vndbid_type(image) = 'cv'), -- [pub]
  l_wikidata    integer, -- [pub]
  c_votecount   integer NOT NULL DEFAULT 0, -- [pub]
  c_pop_rank    integer NOT NULL DEFAULT 10000000,
  c_rat_rank    integer,
  c_released    integer NOT NULL DEFAULT 0,
  c_rating      smallint, -- [pub] decimal vote*100, i.e. 100 - 1000
  c_average     smallint, -- [pub] decimal vote*100, i.e. 100 - 1000
  c_length      smallint,
  c_lengthnum   smallint NOT NULL DEFAULT 0,
  length        smallint NOT NULL DEFAULT 0, -- [pub] Old length field, 0 = unknown, 1 = very short [..] 5 = very long
  devstatus     smallint NOT NULL DEFAULT 0, -- [pub] 0 = finished, 1 = ongoing, 2 = cancelled
  img_nsfw      boolean NOT NULL DEFAULT FALSE, -- (deprecated)
  locked        boolean NOT NULL DEFAULT FALSE,
  hidden        boolean NOT NULL DEFAULT FALSE,
  alias         varchar(500) NOT NULL DEFAULT '', -- [pub]
  l_wp          varchar(150) NOT NULL DEFAULT '', -- (deprecated)
  l_encubed     varchar(100) NOT NULL DEFAULT '', -- (deprecated)
  l_renai       varchar(100) NOT NULL DEFAULT '', -- [pub] Renai.us identifier
  description   text NOT NULL DEFAULT '', -- [pub]
  c_languages   language[] NOT NULL DEFAULT '{}',
  c_platforms   platform[] NOT NULL DEFAULT '{}',
  c_developers  vndbid[] NOT NULL DEFAULT '{}'
);

-- vn_hist
CREATE TABLE vn_hist (
  chid         integer NOT NULL PRIMARY KEY,
  olang        language NOT NULL DEFAULT 'ja',
  image        vndbid CONSTRAINT vn_hist_image_check CHECK(vndbid_type(image) = 'cv'),
  l_wikidata   integer,
  length       smallint NOT NULL DEFAULT 0,
  devstatus    smallint NOT NULL DEFAULT 0,
  img_nsfw     boolean NOT NULL DEFAULT FALSE,
  alias        varchar(500) NOT NULL DEFAULT '',
  l_wp         varchar(150) NOT NULL DEFAULT '',
  l_encubed    varchar(100) NOT NULL DEFAULT '',
  l_renai      varchar(100) NOT NULL DEFAULT '',
  description  text NOT NULL DEFAULT ''
);

-- vn_anime
CREATE TABLE vn_anime (
  id         vndbid NOT NULL, -- [pub]
  aid        integer NOT NULL, -- [pub]
  PRIMARY KEY(id, aid)
);

-- vn_anime_hist
CREATE TABLE vn_anime_hist (
  chid       integer NOT NULL,
  aid        integer NOT NULL, -- anime.id
  PRIMARY KEY(chid, aid)
);

-- vn_editions
CREATE TABLE vn_editions (
  id         vndbid NOT NULL, -- [pub]
  lang       language, -- [pub]
  eid        smallint NOT NULL, -- [pub] Edition identifier, local to the VN, not stable across revisions
  official   boolean NOT NULL DEFAULT TRUE, -- [pub]
  name       text NOT NULL, -- [pub]
  PRIMARY KEY(id, eid)
);

-- vn_editions_hist
CREATE TABLE vn_editions_hist (
  chid       integer NOT NULL,
  lang       language,
  eid        smallint NOT NULL,
  official   boolean NOT NULL DEFAULT TRUE,
  name       text NOT NULL,
  PRIMARY KEY(chid, eid)
);

-- vn_relations
CREATE TABLE vn_relations (
  id         vndbid NOT NULL, -- [pub]
  vid        vndbid NOT NULL, -- [pub]
  relation   vn_relation NOT NULL, -- [pub]
  official   boolean NOT NULL DEFAULT TRUE, -- [pub]
  PRIMARY KEY(id, vid)
);

-- vn_relations_hist
CREATE TABLE vn_relations_hist (
  chid       integer NOT NULL,
  vid        vndbid NOT NULL, -- vn.id
  relation   vn_relation NOT NULL,
  official   boolean NOT NULL DEFAULT TRUE,
  PRIMARY KEY(chid, vid)
);

-- vn_screenshots
CREATE TABLE vn_screenshots (
  id         vndbid NOT NULL, -- [pub]
  scr        vndbid NOT NULL CONSTRAINT vn_screenshots_scr_check CHECK(vndbid_type(scr) = 'sf'), -- [pub]
  rid        vndbid, -- [pub]
  nsfw       boolean NOT NULL DEFAULT FALSE, -- (deprecated)
  PRIMARY KEY(id, scr)
);

-- vn_screenshots_hist
CREATE TABLE vn_screenshots_hist (
  chid       integer NOT NULL,
  scr        vndbid NOT NULL CONSTRAINT vn_screenshots_hist_scr_check CHECK(vndbid_type(scr) = 'sf'),
  rid        vndbid,
  nsfw       boolean NOT NULL DEFAULT FALSE,
  PRIMARY KEY(chid, scr)
);

-- vn_seiyuu
CREATE TABLE vn_seiyuu (
  id         vndbid NOT NULL, -- [pub]
  aid        integer NOT NULL, -- [pub]
  cid        vndbid NOT NULL, -- [pub]
  note       varchar(250) NOT NULL DEFAULT '', -- [pub]
  PRIMARY KEY (id, aid, cid)
);

-- vn_seiyuu_hist
CREATE TABLE vn_seiyuu_hist (
  chid       integer NOT NULL,
  aid        integer NOT NULL, -- staff_alias.aid, but can't reference it because the alias may have been deleted
  cid        vndbid NOT NULL,
  note       varchar(250) NOT NULL DEFAULT '',
  PRIMARY KEY (chid, aid, cid)
);

-- vn_staff
CREATE TABLE vn_staff (
  id         vndbid NOT NULL, -- [pub]
  aid        integer NOT NULL, -- [pub]
  role       credit_type NOT NULL DEFAULT 'staff', -- [pub]
  eid        smallint, -- [pub]
  note       varchar(250) NOT NULL DEFAULT '' -- [pub]
);

-- vn_staff_hist
CREATE TABLE vn_staff_hist (
  chid       integer NOT NULL,
  aid        integer NOT NULL, -- See note at vn_seiyuu_hist.aid
  role       credit_type NOT NULL DEFAULT 'staff',
  eid        smallint,
  note       varchar(250) NOT NULL DEFAULT ''
);

-- vn_titles
CREATE TABLE vn_titles (
  id         vndbid NOT NULL, -- [pub]
  lang       language NOT NULL, -- [pub]
  official   boolean NOT NULL, -- [pub]
  title      text NOT NULL, -- [pub]
  latin      text, -- [pub]
  PRIMARY KEY(id, lang)
);

-- vn_titles_hist
CREATE TABLE vn_titles_hist (
  chid       integer NOT NULL,
  lang       language NOT NULL,
  official   boolean NOT NULL,
  title      text NOT NULL,
  latin      text,
  PRIMARY KEY(chid, lang)
);

-- vn_length_votes
CREATE TABLE vn_length_votes (
  id         serial PRIMARY KEY,
  vid        vndbid NOT NULL, -- [pub]
  date       timestamptz NOT NULL DEFAULT NOW(), -- [pub]
  length     smallint NOT NULL, -- [pub] minutes
  speed      smallint, -- [pub] NULL=uncounted/ignored, 0=slow, 1=normal, 2=fast
  private    boolean NOT NULL,
  uid        vndbid, -- [pub]
  rid        vndbid[] NOT NULL, -- [pub]
  notes      text NOT NULL DEFAULT '' -- [pub]
);

-- wikidata
CREATE TABLE wikidata ( -- Information fetched from Wikidata
  lastfetch          timestamptz,
  id                 integer NOT NULL PRIMARY KEY, -- [pub] Q-number
  enwiki             text,      -- [pub]
  jawiki             text,      -- [pub]
  website            text[],    -- [pub] P856
  vndb               text[],    -- [pub] P3180
  mobygames          text[],    -- [pub] P1933
  mobygames_company  text[],    -- [pub] P4773
  gamefaqs_game      integer[], -- [pub] P4769
  gamefaqs_company   integer[], -- [pub] P6182
  anidb_anime        integer[], -- [pub] P5646
  anidb_person       integer[], -- [pub] P5649
  ann_anime          integer[], -- [pub] P1985
  ann_manga          integer[], -- [pub] P1984
  musicbrainz_artist uuid[],    -- [pub] P434
  twitter            text[],    -- [pub] P2002
  vgmdb_product      integer[], -- [pub] P5659
  vgmdb_artist       integer[], -- [pub] P3435
  discogs_artist     integer[], -- [pub] P1953
  acdb_char          integer[], -- [pub] P7013
  acdb_source        integer[], -- [pub] P7017
  indiedb_game       text[],    -- [pub] P6717
  howlongtobeat      integer[], -- [pub] P2816
  crunchyroll        text[],    -- [pub] P4110
  igdb_game          text[],    -- [pub] P5794
  giantbomb          text[],    -- [pub] P5247
  pcgamingwiki       text[],    -- [pub] P6337
  steam              integer[], -- [pub] P1733
  gog                text[],    -- [pub] P2725
  pixiv_user         integer[], -- [pub] P5435
  doujinshi_author   integer[], -- [pub] P7511
  soundcloud         text[],    -- [pub] P3040
  humblestore        text[],    -- [pub] P4477
  itchio             text[],    -- [pub] P7294
  playstation_jp     text[],    -- [pub] P5999
  playstation_na     text[],    -- [pub] P5944
  playstation_eu     text[],    -- [pub] P5971
  lutris             text[],    -- [pub] P7597
  wine               integer[]  -- [pub] P600
);


-- This view is equivalent to vnt(NULL), see func.sql for a more detailed explanation.
-- This view serves two purposes:
-- * It's easier for the Postgres query planner to optimize this than vnt(NULL).
-- * This view creates a type that can be used as return value for vnt().
--
-- This view and the vnt() function must be recreated anytime a column has been
-- added/removed/changed in the vn table.
CREATE VIEW vnt AS
    SELECT v.*
         , ARRAY[ v.olang::text, COALESCE(vo.latin, vo.title)
                , v.olang::text, vo.title ] AS title
         , COALESCE(vo.latin, vo.title) AS sorttitle
      FROM vn v
      JOIN vn_titles vo ON vo.id = v.id AND vo.lang = v.olang;

-- Same for releases
CREATE VIEW releasest AS
    SELECT r.*
         , ARRAY[ r.olang::text, COALESCE(ro.latin, ro.title)
                , r.olang::text, ro.title ] AS title
         , COALESCE(ro.latin, ro.title) AS sorttitle
      FROM releases r
      JOIN releases_titles ro ON ro.id = r.id AND ro.lang = r.olang;

-- And producers
CREATE VIEW producerst AS
    SELECT *
         , ARRAY [ lang::text, COALESCE(latin, name)
                 , lang::text, name ] AS title
         , COALESCE(latin, name) AS sorttitle
      FROM producers;

-- And chars
CREATE VIEW charst AS
    SELECT *
         , ARRAY [ c_lang::text, COALESCE(latin, name)
                 , c_lang::text, name ] AS title
         , COALESCE(latin, name) AS sorttitle
      FROM chars;

-- This joins staff & staff_alias and adds the title + sorttitle fields.
CREATE VIEW staff_aliast AS
    SELECT s.*, sa.aid, sa.name, sa.latin
         , ARRAY [ s.lang::text, COALESCE(sa.latin, sa.name)
                 , s.lang::text, sa.name ] AS title
         , COALESCE(sa.latin, sa.name) AS sorttitle
      FROM staff s
      JOIN staff_alias sa ON sa.id = s.id;