| Addshore added a comment. |
So I have a second proposal now which is slightly more complex than the initial single table design.
The below SQL includes querys that would be run against the scheme, including on page create, move, delete & load!
use cognate;
DROP TABLE cognate_sites;
DROP TABLE cognate_titles;
DROP TABLE cognate_normalizations;
/*
*Create the tables
*/
CREATE TABLE IF NOT EXISTS cognate_sites (
cs_dbname VARBINARY(32) PRIMARY KEY NOT NULL,
cs_group VARBINARY(32) NOT NULL,
cs_interwiki VARBINARY(32) NOT NULL
);
CREATE TABLE IF NOT EXISTS cognate_titles (
ct_site VARBINARY(32) NOT NULL,
ct_namespace INT NOT NULL,
ct_title VARBINARY(255),
PRIMARY KEY (ct_site, ct_namespace, ct_title)
);
CREATE TABLE IF NOT EXISTS cognate_normalizations (
cn_raw VARBINARY(255) PRIMARY KEY,
cn_normalized VARBINARY(255)
);
CREATE INDEX cn_normalized ON cognate_normalizations (cn_normalized);
/*
*Insert some sample data / example production queries
*/
/*Sites will be added by a maint script*/
INSERT IGNORE INTO cognate_sites (cs_dbname, cs_group, cs_interwiki)
VALUES
('enwiktionary', 'wiktionary', 'en'),
('dewiktionary', 'wiktionary', 'de'),
('ptwiktionary', 'wiktionary', 'pt'),
('zh_min_nanwiktionary', 'wiktionary', 'zh-min-nan');
/*On page create (mediawiki hook)*/
/*If the normalized value differs from the raw value add an entry*/
INSERT IGNORE INTO cognate_titles (ct_site, ct_namespace, ct_title)
VALUES('enwiktionary', 0, 'Apple');
INSERT IGNORE INTO cognate_titles (ct_site, ct_namespace, ct_title)
VALUES('zh_min_nanwiktionary', 0, 'Apostrophe’');
INSERT IGNORE INTO cognate_normalizations (cn_raw, cn_normalized)
VALUES('Apostrophe’', 'Apostrophe''');
INSERT IGNORE INTO cognate_titles (ct_site, ct_namespace, ct_title)
VALUES('enwiktionary', 0, 'Apostrophe’');
INSERT IGNORE INTO cognate_normalizations (cn_raw, cn_normalized)
VALUES('Apostrophe’', 'Apostrophe''');
INSERT IGNORE INTO cognate_titles (ct_site, ct_namespace, ct_title)
VALUES('dewiktionary', 0, 'Apostrophe''');
INSERT IGNORE INTO cognate_titles (ct_site, ct_namespace, ct_title)
VALUES('ptwiktionary', 0, 'Apostrophe''');
/*On page delete*/
DELETE FROM cognate_titles
WHERE ct_site = 'enwiktionary' AND ct_namespace = 0 AND ct_title = 'Apostrophe222’';
/*Deferred, delete normalizations that may have existed but are no longer needed?*/
SELECT COUNT(*) FROM cognate_titles WHERE ct_title = 'Apostrophe222’';
/*If no rows are returned*/
DELETE IGNORE FROM cognate_normalizations WHERE cn_raw = 'Apostrophe222’';
/*On page load*/
SET @wgCognateGroup = 'wiktionary';
SET @cognateNormalizedTitle = 'Apostrophe''';
SELECT cn_raw AS title, ct_site AS site, cs_interwiki AS interwiki
FROM cognate_normalizations
LEFT JOIN cognate_titles ON cn_raw = ct_title
LEFT JOIN cognate_sites ON ct_site = cs_dbname
WHERE cn_normalized = @cognateNormalizedTitle
AND cs_group = @wgCognateGroup
UNION
SELECT ct_title AS title, ct_site AS site, cs_interwiki AS interwiki
FROM cognate_titles
LEFT JOIN cognate_sites ON ct_site = cs_dbname
WHERE ct_title = @cognateNormalizedTitle
AND cs_group = @wgCognateGroupTASK DETAIL
EMAIL PREFERENCES
To: jcrespo, Addshore
Cc: hoo, Aklapper, jcrespo, Addshore, Marostegui, Minhnv-2809, D3r1ck01, Izno, Luke081515, Wikidata-bugs, aude, Darkdadaah, Mbch331, Jay8g, Krenair
Cc: hoo, Aklapper, jcrespo, Addshore, Marostegui, Minhnv-2809, D3r1ck01, Izno, Luke081515, Wikidata-bugs, aude, Darkdadaah, Mbch331, Jay8g, Krenair
_______________________________________________ Wikidata-bugs mailing list Wikidata-bugs@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/wikidata-bugs
