Follow-on for the non-UBN part of T370219: DBQueryError marking page for translation: Table 'mediawikiwiki.translate_cache' doesn't exist.
Table information
Added in 628631: Add persistent translate cache | https://gerrit.wikimedia.org/r/c/mediawiki/extensions/Translate/+/628631 to meet the needs of T182433: Implement a stronger synchronization in RepoNG and Translate
Why? To keep track of the message groups and messages currently being processed, we needed a persistent cache. For this we looked at existing caching solutions provided by MediaWiki (specifically ObjectCache::getInstance(CACHE_DB)), but these could not be used as they were not persistent.
This was something that is needed only on translatewiki.net, and hence this table wasn't created on Wikimedia wikis.
We're now also storing translation state for pages that translators wish to mark for translation. See: T360409: Page status tri-state for pages not marked for translation
This is a fairly generic table, and in the future, we might store more information in this table as needed.
Table structure
CREATE TABLE /*_*/translate_cache ( tc_key VARBINARY(255) NOT NULL, tc_value MEDIUMBLOB DEFAULT NULL, tc_exptime VARBINARY(14) DEFAULT NULL, tc_tag VARBINARY(255) DEFAULT NULL, INDEX tc_tag (tc_tag), PRIMARY KEY(tc_key) ) /*$wgDBTableOptions*/;
More information
Should this table be replicated to wiki replicas (does it not contain private data)?
Yes, we need to create the table on all the wikis where the Translate extension is enabled. It contains information about the translation state of pages: whether they should be marked for translation or not.
It does not contain private data currently.
Will you be doing cross-joins with the wiki metadata?
Currently we're not doing any joins with wiki metadata, and I don't foresee doing so in the future either.
Size of the table (number of rows expected).
With the current usage of the table, there will be at most:
1 record per pages with content model wikitext - (Number of pages marked for translation)
The actual number will be much smaller. This table only contains records for pages that have a translation state set.
Expected growth per year (number of rows).
The worst case scenario for growth will be equal to number of pages created with wikitext content model
Expected amount of queries, both writes and reads (per minute, per hour...per day, any of those are ok)
Rather difficult to predict. On wikis running the translate extension 1 query will be run every time a user views a non translatable wikitext page.
-- Fetch row by key SELECT tc_key, tc_value, tc_exptime, tc_tag FROM translate_cache WHERE tc_key = "xyz";
Examples of queries that will be using the table
All database operations are carried out in the class: https://github.com/wikimedia/mediawiki-extensions-Translate/blob/master/src/Cache/PersistentDatabaseCache.php
Table is not specific for this use case, so in the future there is a small possibility that the queries might change.
-- Fetch row by key SELECT tc_key, tc_value, tc_exptime, tc_tag FROM translate_cache WHERE tc_key = "xyz"; -- Fetch rows by tag SELECT tc_key, tc_value, tc_exptime, tc_tag FROM translate_cache WHERE tc_tag = "tag"; -- Insert values into the table INSERT INTO users (tc_key, tc_value, tc_exptime, tc_tag) VALUES ('key', 'value', null, 'tag') ON DUPLICATE KEY UPDATE tc_key = 'key'; -- Update expiry time based on key UPDATE translate_cache SET tc_exptime = 'exp' WHERE tc_key = "key"; -- Delete entry with key DELETE FROM translate_cache WHERE tc_key = "key"; -- Delete entries with tag DELETE FROM translate_cache WHERE tc_tag= "tag";
See https://wikitech.wikimedia.org/wiki/Creating_new_tables for more.