Toolserver:Toolserver database

This page was moved from the Toolserver wiki.
Toolserver has been replaced by Toolforge. As such, the instructions here may no longer work, but may still be of historical interest.
Please help by updating examples, links, template links, etc. If a page is still relevant, move it to a normal title and leave a redirect.

The "toolserver" database contains metadata about the various Wikimedia wikis and databases. It consists of four tables: language, namespace, namespacename, and wiki.

The "toolserver" database exists on every host, so it's possible to JOIN against it with every database. The database updates daily.

language

mysql> DESCRIBE language;
+--------------+--------------+------+-----+---------+-------+
| Field        | Type         | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| lang         | varchar(16)  | NO   | PRI | NULL    |       | 
| english_name | varchar(255) | YES  |     | NULL    |       | 
| native_name  | varchar(255) | YES  |     | NULL    |       | 
+--------------+--------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

namespace

This table contains the primary namespace names for the wikis in the wiki table. Use of this table is deprecated, use the namespacename table instead, which covers all valid names for each namespace in each wiki, not just the primary names.

mysql> DESCRIBE namespace;
+---------+----------------+------+-----+---------+-------+
| Field   | Type           | Null | Key | Default | Extra |
+---------+----------------+------+-----+---------+-------+
| dbname  | varbinary(32)  | NO   | MUL | NULL    |       | 
| domain  | varbinary(48)  | NO   | PRI | NULL    |       | 
| ns_id   | int(8)         | NO   | PRI | NULL    |       | 
| ns_name | varbinary(255) | NO   |     | NULL    |       | 
+---------+----------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

namespacename

mysql> DESCRIBE namespacename;
+----------------+-------------------------------------+------+-----+---------+-------+
| Field          | Type                                | Null | Key | Default | Extra |
+----------------+-------------------------------------+------+-----+---------+-------+
| dbname         | varbinary(32)                       | NO   | MUL | NULL    |       |
| domain         | varbinary(48)                       | NO   | PRI | NULL    |       |
| ns_id          | int(8)                              | NO   |     | NULL    |       |
| ns_name        | varbinary(255)                      | NO   | PRI | NULL    |       |
| ns_type        | enum('primary','canonical','alias') | NO   |     | NULL    |       |
| ns_is_favorite | int(1)                              | YES  |     | 0       |       |
+----------------+-------------------------------------+------+-----+---------+-------+
6 rows in set (0.01 sec)

• ns_type:
  1. canonical: works on all wikis regardless of configuration
  2. primary: used in urls, omitted if identical to canonical
  3. alias: alternative name for namespace. Aliases and canonical names can be used in links, when performing a search.
• ns_is_favorite: true(1) for primary namespace name.

  If primary and canonical names are identical (e.g. on enwiki) this flag is enabled on the canonical type because in this case there is no row containing a primary type for this namespace (see above).

This table is useful for localizing the namespace name in query results. For example:

SELECT
  ns_name,
  page_title
FROM enwiki_p.page
 JOIN toolserver.namespacename 
  ON ns_id = page_namespace AND dbname = 'enwiki_p'
WHERE page_namespace = 6
 AND ns_is_favorite = true
LIMIT 1;

This query would produce:

+---------+-----------------+
| ns_name | page_title      |
+---------+-----------------+
| File    | !!!_(album).jpg | 
+---------+-----------------+
1 row in set (0.01 sec)

wiki

mysql> DESCRIBE wiki;
+---------------+----------------+------+-----+---------+-------+
| Field         | Type           | Null | Key | Default | Extra |
+---------------+----------------+------+-----+---------+-------+
| dbname        | varchar(32)    | NO   | PRI | NULL    |       | 
| lang          | varchar(16)    | NO   | MUL | NULL    |       | 
| family        | varchar(16)    | NO   | MUL | NULL    |       | 
| domain        | varchar(48)    | YES  | UNI | NULL    |       | 
| size          | int(11)        | NO   |     | NULL    |       | 
| is_meta       | tinyint(4)     | NO   | MUL | NULL    |       | 
| is_closed     | tinyint(4)     | NO   | MUL | NULL    |       | 
| is_multilang  | tinyint(4)     | NO   |     | NULL    |       | 
| is_sensitive  | tinyint(4)     | NO   |     | NULL    |       | 
| root_category | varbinary(255) | YES  |     | NULL    |       | 
| server        | int(4)         | YES  |     | NULL    |       | 
| script_path   | varchar(16)    | YES  |     | /w/     |       | 
+---------------+----------------+------+-----+---------+-------+
12 rows in set (0.00 sec)

This table is especially useful for looking up the host of a wiki database. For example:

SELECT
  server
FROM wiki
WHERE dbname = 'dewiki_p';

This query would produce:

+--------+
| server |
+--------+
|      5 | 
+--------+
1 row in set (0.00 sec)

This tells you that the dewiki_p database is located on the sql-s5 host.

The wiki table is also useful for things like link generation. You can select the domain name from the table for a given database to create proper working hyperlinks.

See also

• Admin:Toolserver database

Category:Database