|· · ·|
|Resolved||LSobanski||T17441 Some tables lack unique or primary keys, may allow confusing duplicate data|
|Resolved||Reedy||T157227 MediaWiki DB tables with columns which references other columns but have different type (tracking)|
|Resolved||Reedy||T146570 Give user_properties a primary key|
|Resolved||Reedy||T172514 Add new PKs to tables.sql (mysql and sqlite)|
|· · ·|
From mediawiki/core.git:maintenance/tables.sql as of 090d0267daa4721ffb154e7e604804201365f9dd (October 1, 2016):
-- -- User preferences and perhaps other fun stuff. :) -- Replaces the old user.user_options blob, with a couple nice properties: -- -- 1) We only store non-default settings, so changes to the defauls -- are now reflected for everybody, not just new accounts. -- 2) We can more easily do bulk lookups, statistics, or modifications of -- saved options since it's a sane table structure. -- CREATE TABLE /*_*/user_properties ( -- Foreign key to user.user_id up_user int NOT NULL, -- Name of the option being saved. This is indexed for bulk lookup. up_property varbinary(255) NOT NULL, -- Property value as a string. up_value blob ) /*$wgDBTableOptions*/; CREATE UNIQUE INDEX /*i*/user_properties_user_property ON /*_*/user_properties (up_user,up_property); CREATE INDEX /*i*/user_properties_property ON /*_*/user_properties (up_property);
I can't guarantee that Wikimedia's database servers have this exact schema, but it's probably pretty close.
Largest issue is that a PK on user_properties should also be a foreign key to users (or at least virtually). The types are differents, being user UNSIGNED there and SIGNED here. That should also be fixed.
Reopening, this has been done on WMF, but not on tables.sql (+mediawiki patch): https://phabricator.wikimedia.org/source/mediawiki/browse/master/maintenance/tables.sql;b8a7d492ebf6dc199efedb110785cef3a89ef850$215 . Only the DBA part is done, Mediawiki patch is pending.