Page MenuHomePhabricator
Paste P8075

wb_terms normalization queries #1
ActivePublic

Authored by Addshore on Feb 13 2019, 8:25 AM.
DROP TABLE wb_entity_term_type;
DROP TABLE wb_entity_terms;
DROP TABLE wb_page_to_entity;
DROP TABLE wb_term_search_langs;
DROP TABLE wb_term_search_langstring;
DROP TABLE wb_term_search_langstringtype;
DROP TABLE wb_term_search_strings;
CREATE TABLE `wb_entity_term_type`
(
`id` int NOT NULL ,
`type` varchar(45) NOT NULL ,
PRIMARY KEY (`id`)
);
CREATE TABLE `wb_entity_terms`
(
`id` int NOT NULL ,
`page_id` int NOT NULL ,
`langstringtype` int NOT NULL ,
PRIMARY KEY (`id`)
);
CREATE TABLE `wb_page_to_entity`
(
`entity_id` varchar(45) NOT NULL ,
`page_id` int NOT NULL ,
PRIMARY KEY (`page_id`)
);
CREATE TABLE `wb_term_search_langs`
(
`id` int NOT NULL ,
`language` varchar(20) NOT NULL ,
PRIMARY KEY (`id`)
);
CREATE TABLE `wb_term_search_langstring`
(
`id` int NOT NULL ,
`term_text` int NOT NULL ,
`term_language` int NOT NULL ,
PRIMARY KEY (`id`)
);
CREATE TABLE `wb_term_search_langstringtype`
(
`id` int NOT NULL ,
`langstring` int NOT NULL ,
`type` int NOT NULL ,
PRIMARY KEY (`id`)
);
CREATE TABLE `wb_term_search_strings`
(
`id` int NOT NULL ,
`term_text` varchar(255) NOT NULL ,
PRIMARY KEY (`id`)
);
ALTER TABLE wb_entity_term_type MODIFY COLUMN id INT auto_increment;
ALTER TABLE wb_term_search_langs MODIFY COLUMN id INT auto_increment;
ALTER TABLE wb_term_search_strings MODIFY COLUMN id INT auto_increment;
ALTER TABLE wb_term_search_langstring MODIFY COLUMN id INT auto_increment;
ALTER TABLE wb_term_search_langstringtype MODIFY COLUMN id INT auto_increment;
ALTER TABLE wb_page_to_entity MODIFY COLUMN page_id INT auto_increment;
ALTER TABLE wb_entity_terms MODIFY COLUMN id INT auto_increment;
CREATE UNIQUE INDEX unique_content ON wb_entity_term_type ( type );
CREATE UNIQUE INDEX unique_content ON wb_term_search_langs ( language );
CREATE UNIQUE INDEX unique_content ON wb_term_search_strings ( term_text );
CREATE UNIQUE INDEX unique_content ON wb_term_search_langstring ( term_text, term_language );
CREATE UNIQUE INDEX unique_content ON wb_term_search_langstringtype ( langstring, type );
CREATE UNIQUE INDEX unique_content ON wb_page_to_entity ( entity_id );
CREATE UNIQUE INDEX unique_content ON wb_entity_terms ( page_id, langstringtype );
/*
INSERT INTO wb_entity_term_type
( type )
SELECT DISTINCT term_type from wb_terms
;
*/
INSERT INTO wb_entity_term_type
( type )
SELECT DISTINCT term_type from wb_terms_reduced
;
INSERT INTO wb_term_search_langs
( language )
SELECT DISTINCT term_language from wb_terms
;
INSERT INTO wb_term_search_strings
( term_text )
SELECT DISTINCT term_text from wb_terms
;
INSERT INTO wb_term_search_langstring
( term_text, term_language )
SELECT DISTINCT wb_term_search_strings.id, wb_term_search_langs.id from wb_terms
INNER JOIN wb_term_search_langs ON wb_term_search_langs.language = wb_terms.term_language
INNER JOIN wb_term_search_strings ON wb_term_search_strings.term_text = wb_terms.term_text
;
INSERT INTO wb_term_search_langstringtype
( langstring, type )
SELECT DISTINCT wb_term_search_langstring.id, wb_entity_term_type.id from wb_terms
INNER JOIN wb_entity_term_type ON wb_entity_term_type.type = wb_terms.term_type
INNER JOIN wb_term_search_langs ON wb_term_search_langs.language = wb_terms.term_language
INNER JOIN wb_term_search_strings ON wb_term_search_strings.term_text = wb_terms.term_text
INNER JOIN wb_term_search_langstring ON ( wb_term_search_langstring.term_text = wb_term_search_strings.id AND wb_term_search_langstring.term_language = wb_term_search_langs.id )
;
INSERT INTO wb_page_to_entity
( entity_id )'\
SELECT DISTINCT wb_terms.term_full_entity_id from wb_terms
;
INSERT INTO wb_entity_terms
( page_id, langstringtype )
SELECT DISTINCT wb_page_to_entity.page_id, wb_term_search_langstringtype.id from wb_terms
INNER JOIN wb_page_to_entity ON wb_page_to_entity.entity_id = wb_terms.term_full_entity_id
INNER JOIN wb_entity_term_type ON wb_entity_term_type.type = wb_terms.term_type
INNER JOIN wb_term_search_langs ON wb_term_search_langs.language = wb_terms.term_language
INNER JOIN wb_term_search_strings ON wb_term_search_strings.term_text = wb_terms.term_text
INNER JOIN wb_term_search_langstring ON ( wb_term_search_langstring.term_text = wb_term_search_strings.id AND wb_term_search_langstring.term_language = wb_term_search_langs.id )
INNER JOIN wb_term_search_langstringtype ON ( wb_term_search_langstringtype.langstring = wb_term_search_langstring.id AND wb_term_search_langstringtype.type = wb_entity_term_type.id )
;
OPTIMIZE TABLE wb_entity_term_type;
OPTIMIZE TABLE wb_entity_terms;
OPTIMIZE TABLE wb_page_to_entity;
OPTIMIZE TABLE wb_term_search_langs;
OPTIMIZE TABLE wb_term_search_langstring;
OPTIMIZE TABLE wb_term_search_langstringtype;
OPTIMIZE TABLE wb_term_search_strings;
ALTER TABLE wb_entity_term_type row_format=compressed key_block_size=8;
ALTER TABLE wb_entity_terms row_format=compressed key_block_size=8;
ALTER TABLE wb_page_to_entity row_format=compressed key_block_size=8;
ALTER TABLE wb_term_search_langs row_format=compressed key_block_size=8;
ALTER TABLE wb_term_search_langstring row_format=compressed key_block_size=8;
ALTER TABLE wb_term_search_langstringtype row_format=compressed key_block_size=8;
ALTER TABLE wb_term_search_strings row_format=compressed key_block_size=8;