Template:SchemaDiagram/sql/wikibase-repo-1.35.txt

-- MySQL version of the database schema for the Wikibase extension. -- Licence: GNU GPL v2+

-- TODO: figure out which lengths to use for some of the varchar fields.

-- Unique ID generator. CREATE TABLE IF NOT EXISTS /*_*/wb_id_counters ( id_value                   INT unsigned        NOT NULL,  id_type                    VARBINARY(32)       NOT NULL ) /*$wgDBTableOptions*/;

CREATE UNIQUE INDEX /*i*/wb_id_counters_type ON /*_*/wb_id_counters (id_type);

-- Derived storage. -- Links site+title pairs to item ids. CREATE TABLE IF NOT EXISTS /*_*/wb_items_per_site ( ips_row_id                 BIGINT unsigned     NOT NULL PRIMARY KEY AUTO_INCREMENT, -- row ID  ips_item_id                INT unsigned        NOT NULL, -- Id of the item  ips_site_id                VARBINARY(32)       NOT NULL, -- Site identifier (global)  ips_site_page              VARCHAR(310)        NOT NULL -- Prefixed title of the page ) /*$wgDBTableOptions*/;

CREATE UNIQUE INDEX /*i*/wb_ips_item_site_page ON /*_*/wb_items_per_site (ips_site_id, ips_site_page); CREATE INDEX /*i*/wb_ips_item_id ON /*_*/wb_items_per_site (ips_item_id);

-- Lookup table for entity terms (ie labels, aliases, descriptions). -- NOTE: keep the Wikimedia specific terms.wmf.sql in sync with this! CREATE TABLE IF NOT EXISTS /*_*/wb_terms ( term_row_id                BIGINT unsigned     NOT NULL PRIMARY KEY AUTO_INCREMENT, -- row ID  term_entity_id             INT unsigned        NOT NULL, -- Id of the entity  term_full_entity_id        VARBINARY(32)       DEFAULT NULL, -- Full id of the entity (not only numeric part)  term_entity_type           VARBINARY(32)       NOT NULL, -- Type of the entity  term_language              VARBINARY(32)       NOT NULL, -- Language code  term_type                  VARBINARY(32)       NOT NULL, -- Term type  term_text                  VARCHAR(255) binary NOT NULL, -- The term text  term_search_key            VARCHAR(255) binary NOT NULL, -- The term text, lowercase for case-insensitive lookups  term_weight                FLOAT UNSIGNED     NOT NULL DEFAULT 0.0 -- weight for ranking ) /*$wgDBTableOptions*/;

-- Indexes and comments below adopted from the suggestions Sean Pringle made -- at https://phabricator.wikimedia.org/T47529#518941 based on a -- live analysis of queries on wikidata.org in January 2014. -- NOTE: keep these in sync with UpdateTermIndexes.sql

-- Some wb_terms queries use term_full_entity_id_s=X which is good selectivity. CREATE INDEX /*i*/term_full_entity ON /*_*/wb_terms (term_full_entity_id);

-- T204837 CREATE INDEX /*i*/wb_terms_text ON /*_*/wb_terms (term_text);

-- T204838 CREATE INDEX /*i*/wb_terms_search_key ON /*_*/wb_terms (term_search_key);

-- This index has good selectivity while still allowing ICP for short string values. CREATE INDEX /*i*/term_search_full ON /*_*/wb_terms (term_language, term_full_entity_id, term_type, term_search_key(16));

-- T202265 CREATE INDEX /*i*/tmp1 ON /*_*/wb_terms (`term_language`, `term_type`, `term_entity_type`, `term_search_key`);

-- T204836 CREATE INDEX /*i*/wb_terms_entity_id ON /*_*/wb_terms (`term_entity_id`);

-- General info and links: -- --- -- -- These tables represent a normalized form of the wb_terms table. -- -- The wb_terms table history leading to this point can be all followed starting -- from this epic https://phabricator.wikimedia.org/T208425 -- -- The current solution is part of that epic. work on it can be followed starting -- from this ticket https://phabricator.wikimedia.org/T219175 -- -- This design is normalized for the sake of reducing duplication and redundancy -- that accounted for ~70% of the disk space occupied by wb_terms table as of -- March 2019. It requires few joins for queries, and impact on performance -- will be addressed as the need arise. -- -- Note on splitting Items and Properties: -- --- -- wb_item_terms an wb_property_terms are two tables with similar structure. -- original draft had them combined in one table, but further discussion lead -- to the decision of splitting them for the following reasons: -- - items are lot more and will grow faster than properties, which means --  when querying only for properties we need not to look at a gigantic --  index that includes items as well, but rather a very small one. -- - conceptually, items and properties are two different entity types, and --  since different entity types might have different constraints and maybe --  even structure, splitting them into corresponding tables saves us from --  ending up with a wide polymorphic table again (lesson learned from --   wb_terms table itself) -- -- Note on Entity IDs: -- -- -- Since Wikidata entity ids are fixed and known to always be Q (like in Q123), -- entity ids are stored as integers after dropping that prefix, in both -- wb_item_terms and wb_property_terms tables. -- e.g. entity id Q123 has integer id of 123. -- -- when a new entity type gets introduced into the instance then one can either: -- - store it in wb_item_terms/wb_property_items if it has the same prefix (Q), or -- - create a separate table similar to wb_item_terms in structure for those --  entities if the prefix is different.

-- stores a record per term per item per language. this table is expected to be the longest one -- in this group of tables. -- -- term text, type and language are normalized further through wb_term_in_lang -- table. CREATE TABLE IF NOT EXISTS /*_*/wbt_item_terms ( wbit_id                                BIGINT unsigned       NOT NULL PRIMARY KEY AUTO_INCREMENT,  wbit_item_id                           INT    unsigned       NOT NULL,  wbit_term_in_lang_id                   INT    unsigned       NOT NULL ) /*$wgDBTableOptions*/;

CREATE INDEX /*i*/wbt_item_terms_item_id ON /*_*/wbt_item_terms (wbit_item_id); CREATE UNIQUE INDEX /*i*/wbt_item_terms_term_in_lang_id_item_id ON /*_*/wbt_item_terms (wbit_term_in_lang_id, wbit_item_id);

-- stores a record per term per property per language. -- -- term text, type and language are normalized further through wb_term_in_lang -- table. CREATE TABLE IF NOT EXISTS /*_*/wbt_property_terms ( wbpt_id                                INT unsigned       NOT NULL PRIMARY KEY AUTO_INCREMENT,  wbpt_property_id                       INT unsigned       NOT NULL,  wbpt_term_in_lang_id                   INT unsigned       NOT NULL ) /*$wgDBTableOptions*/;

CREATE INDEX /*i*/wbt_property_terms_property_id ON /*_*/wbt_property_terms (wbpt_property_id); CREATE UNIQUE INDEX /*i*/wbt_property_terms_term_in_lang_id_property_id ON /*_*/wbt_property_terms (wbpt_term_in_lang_id, wbpt_property_id);

-- stores a record per term per text per language. -- -- term text and language are normalized further through wb_text_in_lang table. CREATE TABLE IF NOT EXISTS /*_*/wbt_term_in_lang ( wbtl_id                          INT unsigned       NOT NULL PRIMARY KEY AUTO_INCREMENT,  wbtl_type_id                     INT unsigned       NOT NULL,  wbtl_text_in_lang_id             INT unsigned       NOT NULL ) /*$wgDBTableOptions*/;

CREATE INDEX /*i*/wbt_term_in_lang_type_id_text_in ON /*_*/wbt_term_in_lang (wbtl_type_id); CREATE UNIQUE INDEX /*i*/wbt_term_in_lang_text_in_lang_id_lang_id ON /*_*/wbt_term_in_lang (wbtl_text_in_lang_id, wbtl_type_id);

-- stores a record per term text per language. -- -- text is normalized through wb_term_text table. CREATE TABLE IF NOT EXISTS /*_*/wbt_text_in_lang ( wbxl_id                         INT unsigned       NOT NULL PRIMARY KEY AUTO_INCREMENT,  wbxl_language                   VARBINARY(20)      NOT NULL,  wbxl_text_id                    INT unsigned       NOT NULL ) /*$wgDBTableOptions*/;

CREATE INDEX /*i*/wbt_text_in_lang_language ON /*_*/wbt_text_in_lang (wbxl_language); CREATE UNIQUE INDEX /*i*/wbt_text_in_lang_text_id_text_id ON /*_*/wbt_text_in_lang (wbxl_text_id, wbxl_language);

-- stores a record per text value that are used in different terms -- in different languages. CREATE TABLE IF NOT EXISTS /*_*/wbt_text ( wbx_id                             INT unsigned         NOT NULL PRIMARY KEY AUTO_INCREMENT,  wbx_text                           VARBINARY(255)       NOT NULL ) /*$wgDBTableOptions*/;

CREATE UNIQUE INDEX /*i*/wbt_text_text ON /*_*/wbt_text (wbx_text);

-- normalized term type names CREATE TABLE IF NOT EXISTS /*_*/wbt_type ( wby_id                                INT unsigned         NOT NULL PRIMARY KEY AUTO_INCREMENT,  wby_name                              VARBINARY(45)        NOT NULL ) /*$wgDBTableOptions*/;

CREATE UNIQUE INDEX /*i*/wbt_type_name ON /*_*/wbt_type (wby_name);