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 is annotated in this file. Which tables and which rows are exported is
-- defined in util/dbdump.pl.
--
-- 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', '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', 'bg', 'ca', 'cs', 'da', 'de', 'el', 'en', 'eo', 'es', 'fa', 'fi', 'fr', 'ga', 'gd', 'he', 'hi', 'hr', 'hu', 'id', 'it', 'ja', 'ko', 'mk', 'ms', 'la', 'lt', 'lv', 'nl', 'no', 'pl', 'pt-pt', 'pt-br', 'ro', 'ru', 'sk', 'sl', 'sv', 'ta', 'th', 'tr', 'uk', 'ur', 'vi', 'zh');
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');

-- 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 (
  id           integer NOT NULL PRIMARY KEY, -- [pub]
  ann_id       integer, -- [pub]
  lastfetch    timestamptz,
  type         anime_type, -- [pub]
  year         smallint, -- [pub]
  nfo_id       varchar(200), -- [pub]
  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         inet NOT NULL,
  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,
  ip         inet NOT NULL DEFAULT '0.0.0.0',
  comments   text NOT NULL DEFAULT ''
);

-- 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]
  spoil_gender gender, -- [pub]
  bloodt       blood_type NOT NULL DEFAULT 'unknown', -- [pub]
  cup_size     cup_size NOT NULL DEFAULT '', -- [pub]
  main         vndbid, -- [pub] chars.id
  s_bust       smallint NOT NULL DEFAULT 0, -- [pub]
  s_waist      smallint NOT NULL DEFAULT 0, -- [pub]
  s_hip        smallint NOT NULL DEFAULT 0, -- [pub]
  b_month      smallint NOT NULL DEFAULT 0, -- [pub]
  b_day        smallint NOT NULL DEFAULT 0, -- [pub]
  height       smallint NOT NULL DEFAULT 0, -- [pub]
  weight       smallint, -- [pub]
  main_spoil   smallint NOT NULL DEFAULT 0, -- [pub]
  age          smallint, -- [pub]
  locked       boolean NOT NULL DEFAULT FALSE,
  hidden       boolean NOT NULL DEFAULT FALSE,
  name         varchar(250) NOT NULL DEFAULT '', -- [pub]
  original     varchar(250) NOT NULL DEFAULT '', -- [pub]
  alias        varchar(500) NOT NULL DEFAULT '', -- [pub]
  "desc"       text     NOT NULL DEFAULT '' -- [pub]
);

-- 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 '',
  original     varchar(250) NOT NULL DEFAULT '',
  alias        varchar(500) NOT NULL DEFAULT '',
  "desc"       text     NOT NULL DEFAULT ''
);

-- chars_traits
CREATE TABLE chars_traits (
  id         vndbid NOT NULL, -- [pub]
  tid        vndbid NOT NULL, -- [pub] traits.id
  spoil      smallint NOT NULL DEFAULT 0, -- [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,
  PRIMARY KEY(chid, tid)
);

-- chars_vns
CREATE TABLE chars_vns (
  id         vndbid NOT NULL, -- [pub]
  vid        vndbid NOT NULL, -- [pub] vn.id
  rid        vndbid NULL, -- [pub] releases.id
  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]
  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
);

-- 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]
  height            smallint NOT NULL, -- [pub]
  c_votecount       integer  NOT NULL DEFAULT 0, -- [pub] (cached columns are marked [pub] for easy querying...)
  c_sexual_avg      real, -- [pub]
  c_sexual_stddev   real, -- [pub]
  c_violence_avg    real, -- [pub]
  c_violence_stddev real, -- [pub]
  c_weight          real NOT NULL DEFAULT 0, -- [pub]
  c_uids            vndbid[] NOT NULL DEFAULT '{}'
);

-- 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]
  violence smallint NOT NULL CHECK(violence >= 0 AND violence <= 2), -- [pub]
  ignore   boolean NOT NULL DEFAULT false -- [pub]
);

-- 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]
  original   varchar(200) NOT NULL DEFAULT '', -- [pub]
  alias      varchar(500) NOT NULL DEFAULT '', -- [pub]
  website    varchar(250) NOT NULL DEFAULT '', -- [pub]
  "desc"     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 '',
  original   varchar(200) NOT NULL DEFAULT '',
  alias      varchar(500) NOT NULL DEFAULT '',
  website    varchar(250) NOT NULL DEFAULT '',
  "desc"     text NOT NULL DEFAULT '',
  l_wp       varchar(150)
);

-- producers_relations
CREATE TABLE producers_relations (
  id         vndbid NOT NULL, -- [pub]
  pid        vndbid NOT NULL, -- [pub] producers.id
  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 (
  vid        vndbid NOT NULL, -- [pub]
  quote      varchar(250) NOT NULL, -- [pub]
  PRIMARY KEY(vid, quote)
);

-- 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]
  type         release_type NOT NULL DEFAULT 'complete', -- [pub]
  gtin         bigint NOT NULL DEFAULT 0, -- [pub]
  l_toranoana  bigint NOT NULL DEFAULT 0, -- [pub]
  l_appstore   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]
  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]
  minage       smallint, -- [pub]
  voiced       smallint NOT NULL DEFAULT 0, -- [pub]
  ani_story    smallint NOT NULL DEFAULT 0, -- [pub]
  ani_ero      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]
  patch        boolean NOT NULL DEFAULT FALSE, -- [pub]
  freeware     boolean NOT NULL DEFAULT FALSE, -- [pub]
  doujin       boolean NOT NULL DEFAULT FALSE, -- [pub] (deprecated)
  uncensored   boolean NOT NULL DEFAULT FALSE, -- [pub]
  official     boolean NOT NULL DEFAULT TRUE, -- [pub]
  locked       boolean NOT NULL DEFAULT FALSE,
  hidden       boolean NOT NULL DEFAULT FALSE,
  title        varchar(300) NOT NULL DEFAULT '', -- [pub]
  original     varchar(250) NOT NULL DEFAULT '', -- [pub]
  website      varchar(250) 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 '', -- [pub] (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_gyutto     integer[] NOT NULL DEFAULT '{}', -- [pub]
  l_dmm        text[] NOT NULL DEFAULT '{}' -- [pub]
);

-- releases_hist
CREATE TABLE releases_hist (
  chid         integer NOT NULL PRIMARY KEY,
  type         release_type NOT NULL DEFAULT 'complete',
  gtin         bigint NOT NULL DEFAULT 0,
  l_toranoana  bigint NOT NULL DEFAULT 0,
  l_appstore   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,
  minage       smallint,
  voiced       smallint NOT NULL DEFAULT 0,
  ani_story    smallint NOT NULL DEFAULT 0,
  ani_ero      smallint NOT NULL DEFAULT 0,
  reso_x       smallint NOT NULL DEFAULT 0,
  reso_y       smallint NOT NULL DEFAULT 0,
  patch        boolean NOT NULL DEFAULT FALSE,
  freeware     boolean NOT NULL DEFAULT FALSE,
  doujin       boolean NOT NULL DEFAULT FALSE,
  uncensored   boolean NOT NULL DEFAULT FALSE,
  official     boolean NOT NULL DEFAULT TRUE,
  title        varchar(300) NOT NULL DEFAULT '',
  original     varchar(250) NOT NULL DEFAULT '',
  website      varchar(250) 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_gyutto     integer[] NOT NULL DEFAULT '{}',
  l_dmm        text[] NOT NULL DEFAULT '{}'
);

-- releases_lang
CREATE TABLE releases_lang (
  id         vndbid NOT NULL, -- [pub]
  lang       language NOT NULL, -- [pub]
  mtl        boolean NOT NULL DEFAULT false, -- [pub]
  PRIMARY KEY(id, lang)
);

-- releases_lang_hist
CREATE TABLE releases_lang_hist (
  chid       integer NOT NULL,
  lang       language NOT NULL,
  mtl        boolean NOT NULL DEFAULT false, -- [pub]
  PRIMARY KEY(chid, lang)
);

-- 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] producers.id
  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_vn
CREATE TABLE releases_vn (
  id         vndbid NOT NULL, -- [pub]
  vid        vndbid NOT NULL, -- [pub] vn.id
  PRIMARY KEY(id, vid)
);

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

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

-- 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,
  uid        vndbid,
  rid        vndbid,
  date       timestamptz NOT NULL DEFAULT NOW(),
  lastmod    timestamptz,
  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 (
  id       vndbid NOT NULL,
  uid      vndbid,
  date     timestamptz NOT NULL DEFAULT NOW(),
  edited   timestamptz,
  num      smallint NOT NULL,
  hidden   boolean NOT NULL DEFAULT FALSE,
  msg      text NOT NULL DEFAULT '',
  PRIMARY KEY(id, num)
);

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

-- rlists
CREATE TABLE rlists (
  uid      vndbid NOT NULL, -- [pub]
  rid      vndbid NOT NULL, -- [pub]
  added    timestamptz NOT NULL DEFAULT NOW(), -- [pub]
  status   smallint NOT NULL DEFAULT 0, -- [pub]
  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)
);

-- sessions
CREATE TABLE sessions (
  uid      vndbid NOT NULL,
  type     session_type NOT NULL,
  added    timestamptz NOT NULL DEFAULT NOW(),
  expires  timestamptz NOT NULL,
  token    bytea NOT NULL,
  mail     text,
  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_jlist
CREATE TABLE shop_jlist (
  lastfetch  timestamptz,
  deadsince  timestamptz,
  jbox       boolean NOT NULL DEFAULT false,
  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]
  aid         integer NOT NULL DEFAULT 0, -- [pub] staff_alias.aid
  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,
  "desc"      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]
);

-- staff_hist
CREATE TABLE staff_hist (
  chid        integer NOT NULL PRIMARY KEY,
  gender      gender NOT NULL DEFAULT 'unknown',
  lang        language NOT NULL DEFAULT 'ja',
  aid         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,
  "desc"      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 ''
);

-- 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]
  original   varchar(200) NOT NULL DEFAULT '' -- [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 '',
  original   varchar(200) NOT NULL DEFAULT '',
  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]
  searchable   boolean NOT NULL DEFAULT TRUE, -- [pub]
  applicable   boolean NOT NULL DEFAULT TRUE, -- [pub]
  name         varchar(250) NOT NULL DEFAULT '' UNIQUE, -- [pub]
  description  text NOT NULL DEFAULT '', -- [pub]
  alias        varchar(500) NOT NULL DEFAULT '', -- [pub]
  locked       boolean NOT NULL DEFAULT FALSE,
  hidden       boolean NOT NULL DEFAULT TRUE
);

-- 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 '',
  description  text NOT NULL DEFAULT '',
  alias        varchar(500) 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 (
  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]
  spoiler  smallint CHECK(spoiler >= 0 AND spoiler <= 2), -- [pub]
  date     timestamptz NOT NULL DEFAULT NOW(), -- [pub]
  ignore   boolean NOT NULL DEFAULT false, -- [pub]
  notes    text NOT NULL DEFAULT '' -- [pub]
);

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

-- 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,
  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 (
  tid      vndbid NOT NULL,
  uid      vndbid,
  date     timestamptz NOT NULL DEFAULT NOW(),
  edited   timestamptz,
  num      smallint NOT NULL,
  hidden   boolean NOT NULL DEFAULT FALSE,
  msg      text NOT NULL DEFAULT '',
  PRIMARY KEY(tid, num),
  CONSTRAINT threads_posts_first_nonhidden CHECK(num > 1 OR NOT hidden)
);

-- 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,
  elm_mods  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]
  "group"       vndbid, -- [pub]
  added         timestamptz NOT NULL DEFAULT NOW(),
  c_items       integer NOT NULL DEFAULT 0,
  "order"       smallint NOT NULL DEFAULT 0, -- [pub]
  defaultspoil  smallint NOT NULL DEFAULT 0, -- [pub]
  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]
  hidden        boolean NOT NULL DEFAULT TRUE,
  locked        boolean NOT NULL DEFAULT FALSE
);

-- traits_hist
CREATE TABLE traits_hist (
  chid          integer NOT NULL,
  "order"       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
);

-- 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 (
  uid      vndbid NOT NULL, -- [pub] user.id
  id       integer 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
CREATE TABLE ulist_vns (
  uid         vndbid NOT NULL, -- [pub] users.id
  vid         vndbid NOT NULL, -- [pub] vn.id
  added       timestamptz NOT NULL DEFAULT NOW(), -- [pub]
  lastmod     timestamptz NOT NULL DEFAULT NOW(), -- [pub] updated when anything in this row has changed?
  vote_date   timestamptz, -- [pub] Used for "recent votes" - also updated when vote has changed?
  started     date, -- [pub]
  finished    date, -- [pub]
  vote        smallint CHECK(vote IS NULL OR vote BETWEEN 10 AND 100), -- [pub]
  notes       text NOT NULL DEFAULT '', -- [pub]
  PRIMARY KEY(uid, vid)
);

-- ulist_vns_labels
CREATE TABLE ulist_vns_labels (
  uid vndbid NOT NULL, -- [pub] user.id
  lbl integer NOT NULL, -- [pub]
  vid vndbid NOT NULL, -- [pub] vn.id
  PRIMARY KEY(uid, lbl, vid)
);

-- users
CREATE TABLE users (
  registered          timestamptz NOT NULL DEFAULT NOW(),
  last_reports        timestamptz, -- For mods: Most recent activity seen on the reports listing
  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,
  tableopts_c         integer,
  spoilers            smallint NOT NULL DEFAULT 0,
  max_sexual          smallint NOT NULL DEFAULT 0,
  max_violence        smallint NOT NULL DEFAULT 0,
  ign_votes           boolean NOT NULL DEFAULT false, -- [pub]
  email_confirmed     boolean NOT NULL DEFAULT false,
  notify_dbedit       boolean NOT NULL DEFAULT true,
  notify_announce     boolean NOT NULL DEFAULT false,
  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,
  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] (public because this is used in calculating image flagging scores)
  perm_tag            boolean NOT NULL DEFAULT true, -- [pub] (public because this is used in calculating VN tag scores)
  perm_tagmod         boolean NOT NULL DEFAULT false,
  perm_review         boolean NOT NULL DEFAULT true,
  username            varchar(20) NOT NULL UNIQUE, -- [pub]
  uniname             text NOT NULL DEFAULT '',
  ip                  inet NOT NULL DEFAULT '0.0.0.0',
  skin                text NOT NULL DEFAULT '',
  customcss           text NOT NULL DEFAULT '',
  ulist_votes         jsonb,
  ulist_vnlist        jsonb,
  ulist_wish          jsonb,
  vnlang              jsonb, -- '$lang(-mtl)?' => true/false, which languages to expand/collapse on VN pages
  tableopts_v         integer,
  tableopts_vt        integer, -- VN listing on tag pages
  perm_lengthvote     boolean NOT NULL DEFAULT true -- [pub] (public because this is used in calculating VN lengths)
);

-- 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 ''
);

-- 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]
  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_popularity  smallint, -- [pub], ratio between 0 and 10000
  c_pop_rank    integer,
  c_rating      smallint, -- [pub], decimal vote*100, i.e. 100 - 1000
  c_rat_rank    integer,
  c_released    integer NOT NULL DEFAULT 0,
  length        smallint NOT NULL DEFAULT 0, -- [pub]
  img_nsfw      boolean NOT NULL DEFAULT FALSE, -- (deprecated)
  locked        boolean NOT NULL DEFAULT FALSE,
  hidden        boolean NOT NULL DEFAULT FALSE,
  title         varchar(250) NOT NULL DEFAULT '', -- [pub]
  original      varchar(250) NOT NULL DEFAULT '', -- [pub]
  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]
  "desc"        text NOT NULL DEFAULT '', -- [pub]
  c_search      text,
  c_languages   language[] NOT NULL DEFAULT '{}',
  c_platforms   platform[] NOT NULL DEFAULT '{}',
  c_developers  vndbid[] NOT NULL DEFAULT '{}',
  c_average     smallint -- [pub], decimal vote*100, i.e. 100 - 1000
);

-- 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,
  img_nsfw     boolean NOT NULL DEFAULT FALSE,
  title        varchar(250) NOT NULL DEFAULT '',
  original     varchar(250) NOT NULL DEFAULT '',
  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 '',
  "desc"       text NOT NULL DEFAULT ''
);

-- vn_anime
CREATE TABLE vn_anime (
  id         vndbid NOT NULL, -- [pub]
  aid        integer NOT NULL, -- [pub] anime.id
  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_relations
CREATE TABLE vn_relations (
  id         vndbid NOT NULL, -- [pub]
  vid        vndbid NOT NULL, -- [pub] vn.id
  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] images.id
  rid        vndbid,          -- [pub] releases.id (only NULL for old revisions, nowadays not allowed anymore)
  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] staff_alias.aid
  cid        vndbid NOT NULL, -- [pub] chars.id
  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, -- chars.id
  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] staff_alias.aid
  role       credit_type NOT NULL DEFAULT 'staff', -- [pub]
  note       varchar(250) NOT NULL DEFAULT '', -- [pub]
  PRIMARY KEY (id, aid, role)
);

-- 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',
  note       varchar(250) NOT NULL DEFAULT '',
  PRIMARY KEY (chid, aid, role)
);

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

-- wikidata
CREATE TABLE wikidata (
  lastfetch          timestamptz,
  id                 integer NOT NULL PRIMARY KEY, -- [pub]
  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
);