258 lines
7.4 KiB
SQL
258 lines
7.4 KiB
SQL
CREATE DATABASE IF NOT EXISTS `mobilemodels`
|
|
DEFAULT CHARACTER SET utf8mb4
|
|
DEFAULT COLLATE utf8mb4_0900_ai_ci;
|
|
|
|
CREATE DATABASE IF NOT EXISTS `python_services_test`
|
|
DEFAULT CHARACTER SET utf8mb4
|
|
DEFAULT COLLATE utf8mb4_0900_ai_ci;
|
|
|
|
SET @drop_stmt = (
|
|
SELECT CASE `TABLE_TYPE`
|
|
WHEN 'BASE TABLE' THEN 'DROP TABLE `python_services_test`.`models`'
|
|
WHEN 'VIEW' THEN 'DROP VIEW `python_services_test`.`models`'
|
|
ELSE 'DO 0'
|
|
END
|
|
FROM `information_schema`.`TABLES`
|
|
WHERE `TABLE_SCHEMA` = 'python_services_test' AND `TABLE_NAME` = 'models'
|
|
LIMIT 1
|
|
);
|
|
SET @drop_stmt = COALESCE(@drop_stmt, 'DO 0');
|
|
PREPARE stmt FROM @drop_stmt;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
USE `mobilemodels`;
|
|
|
|
SET @drop_stmt = (
|
|
SELECT CASE `TABLE_TYPE`
|
|
WHEN 'BASE TABLE' THEN 'DROP TABLE `mm_device_record`'
|
|
WHEN 'VIEW' THEN 'DROP VIEW `mm_device_record`'
|
|
ELSE 'DO 0'
|
|
END
|
|
FROM `information_schema`.`TABLES`
|
|
WHERE `TABLE_SCHEMA` = 'mobilemodels' AND `TABLE_NAME` = 'mm_device_record'
|
|
LIMIT 1
|
|
);
|
|
SET @drop_stmt = COALESCE(@drop_stmt, 'DO 0');
|
|
PREPARE stmt FROM @drop_stmt;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
SET @drop_stmt = (
|
|
SELECT CASE `TABLE_TYPE`
|
|
WHEN 'BASE TABLE' THEN 'DROP TABLE `mm_device_lookup`'
|
|
WHEN 'VIEW' THEN 'DROP VIEW `mm_device_lookup`'
|
|
ELSE 'DO 0'
|
|
END
|
|
FROM `information_schema`.`TABLES`
|
|
WHERE `TABLE_SCHEMA` = 'mobilemodels' AND `TABLE_NAME` = 'mm_device_lookup'
|
|
LIMIT 1
|
|
);
|
|
SET @drop_stmt = COALESCE(@drop_stmt, 'DO 0');
|
|
PREPARE stmt FROM @drop_stmt;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
SET @drop_stmt = (
|
|
SELECT CASE `TABLE_TYPE`
|
|
WHEN 'BASE TABLE' THEN 'DROP TABLE `models`'
|
|
WHEN 'VIEW' THEN 'DROP VIEW `models`'
|
|
ELSE 'DO 0'
|
|
END
|
|
FROM `information_schema`.`TABLES`
|
|
WHERE `TABLE_SCHEMA` = 'mobilemodels' AND `TABLE_NAME` = 'models'
|
|
LIMIT 1
|
|
);
|
|
SET @drop_stmt = COALESCE(@drop_stmt, 'DO 0');
|
|
PREPARE stmt FROM @drop_stmt;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
SET @drop_stmt = (
|
|
SELECT CASE `TABLE_TYPE`
|
|
WHEN 'BASE TABLE' THEN 'DROP TABLE `vw_mm_device_lookup`'
|
|
WHEN 'VIEW' THEN 'DROP VIEW `vw_mm_device_lookup`'
|
|
ELSE 'DO 0'
|
|
END
|
|
FROM `information_schema`.`TABLES`
|
|
WHERE `TABLE_SCHEMA` = 'mobilemodels' AND `TABLE_NAME` = 'vw_mm_device_lookup'
|
|
LIMIT 1
|
|
);
|
|
SET @drop_stmt = COALESCE(@drop_stmt, 'DO 0');
|
|
PREPARE stmt FROM @drop_stmt;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
SET @drop_stmt = (
|
|
SELECT CASE `TABLE_TYPE`
|
|
WHEN 'BASE TABLE' THEN 'DROP TABLE `vw_models`'
|
|
WHEN 'VIEW' THEN 'DROP VIEW `vw_models`'
|
|
ELSE 'DO 0'
|
|
END
|
|
FROM `information_schema`.`TABLES`
|
|
WHERE `TABLE_SCHEMA` = 'mobilemodels' AND `TABLE_NAME` = 'vw_models'
|
|
LIMIT 1
|
|
);
|
|
SET @drop_stmt = COALESCE(@drop_stmt, 'DO 0');
|
|
PREPARE stmt FROM @drop_stmt;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
CREATE TABLE IF NOT EXISTS `mm_device_catalog` (
|
|
`record_id` varchar(64) NOT NULL,
|
|
`model` varchar(191) NOT NULL,
|
|
`alias_norm` varchar(191) NOT NULL,
|
|
`device_name` varchar(255) NOT NULL,
|
|
`brand` varchar(64) NOT NULL,
|
|
`manufacturer_brand` varchar(64) NOT NULL,
|
|
`parent_brand` varchar(64) NOT NULL,
|
|
`market_brand` varchar(64) NOT NULL,
|
|
`device_type` enum('phone','tablet','wear','tv','other') NOT NULL,
|
|
`code` varchar(64) DEFAULT NULL,
|
|
`code_alias` varchar(255) DEFAULT NULL,
|
|
`ver_name` text DEFAULT NULL,
|
|
`source_file` varchar(255) NOT NULL,
|
|
`section` varchar(255) NOT NULL,
|
|
`source_rank` int NOT NULL,
|
|
`source_weight` decimal(6,3) NOT NULL,
|
|
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
`hash_md5` char(32) GENERATED ALWAYS AS (
|
|
md5(concat_ws(_utf8mb4'|', `model`, `device_type`, `market_brand`, `manufacturer_brand`, `code`, `code_alias`, `device_name`, `ver_name`))
|
|
) STORED,
|
|
`hash_crc` int unsigned GENERATED ALWAYS AS (
|
|
crc32(concat_ws(_utf8mb4'|', `model`, `device_type`, `market_brand`, `manufacturer_brand`, `code`, `code_alias`, `device_name`, `ver_name`))
|
|
) STORED,
|
|
PRIMARY KEY (`record_id`, `model`),
|
|
KEY `idx_mm_device_catalog_alias_norm` (`alias_norm`, `source_rank`, `record_id`),
|
|
KEY `idx_mm_device_catalog_model` (`model`),
|
|
KEY `idx_mm_device_catalog_market_brand` (`market_brand`),
|
|
KEY `idx_mm_device_catalog_parent_brand` (`parent_brand`),
|
|
KEY `idx_mm_device_catalog_manufacturer_brand` (`manufacturer_brand`),
|
|
KEY `idx_mm_device_catalog_device_type` (`device_type`),
|
|
KEY `idx_mm_device_catalog_code` (`code`),
|
|
KEY `idx_mm_device_catalog_hash` (`hash_md5`, `hash_crc`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS `mm_brand_lookup` (
|
|
`alias_norm` varchar(191) NOT NULL,
|
|
`alias_type` enum('manufacturer','parent','market') NOT NULL,
|
|
`canonical_brand` varchar(64) NOT NULL,
|
|
`manufacturer_brand` varchar(64) DEFAULT NULL,
|
|
`parent_brand` varchar(64) DEFAULT NULL,
|
|
`market_brand` varchar(64) DEFAULT NULL,
|
|
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`alias_norm`, `alias_type`),
|
|
KEY `idx_mm_brand_lookup_canonical_brand` (`canonical_brand`),
|
|
KEY `idx_mm_brand_lookup_manufacturer_brand` (`manufacturer_brand`),
|
|
KEY `idx_mm_brand_lookup_parent_brand` (`parent_brand`),
|
|
KEY `idx_mm_brand_lookup_market_brand` (`market_brand`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
|
|
|
|
CREATE OR REPLACE VIEW `mm_device_lookup` AS
|
|
SELECT
|
|
c.`alias_norm`,
|
|
c.`record_id`,
|
|
c.`device_name`,
|
|
c.`brand`,
|
|
c.`manufacturer_brand`,
|
|
c.`parent_brand`,
|
|
c.`market_brand`,
|
|
c.`device_type`,
|
|
c.`source_file`,
|
|
c.`section`,
|
|
c.`source_rank`,
|
|
c.`source_weight`,
|
|
c.`updated_at`
|
|
FROM `mm_device_catalog` AS c;
|
|
|
|
CREATE OR REPLACE VIEW `mm_device_record` AS
|
|
SELECT
|
|
c.`record_id`,
|
|
c.`device_name`,
|
|
c.`brand`,
|
|
c.`manufacturer_brand`,
|
|
c.`parent_brand`,
|
|
c.`market_brand`,
|
|
c.`device_type`,
|
|
c.`source_file`,
|
|
c.`section`,
|
|
c.`source_rank`,
|
|
c.`source_weight`,
|
|
CAST(CONCAT('[', GROUP_CONCAT(JSON_QUOTE(c.`model`) ORDER BY c.`model` SEPARATOR ','), ']') AS JSON) AS `aliases_json`,
|
|
MAX(c.`updated_at`) AS `updated_at`
|
|
FROM `mm_device_catalog` AS c
|
|
GROUP BY
|
|
c.`record_id`,
|
|
c.`device_name`,
|
|
c.`brand`,
|
|
c.`manufacturer_brand`,
|
|
c.`parent_brand`,
|
|
c.`market_brand`,
|
|
c.`device_type`,
|
|
c.`source_file`,
|
|
c.`section`,
|
|
c.`source_rank`,
|
|
c.`source_weight`;
|
|
|
|
CREATE OR REPLACE VIEW `vw_mm_device_lookup` AS
|
|
SELECT
|
|
c.`alias_norm`,
|
|
c.`record_id`,
|
|
c.`device_name`,
|
|
c.`brand`,
|
|
c.`manufacturer_brand`,
|
|
c.`parent_brand`,
|
|
c.`market_brand`,
|
|
c.`device_type`,
|
|
c.`source_file`,
|
|
c.`section`,
|
|
c.`source_rank`,
|
|
c.`source_weight`,
|
|
c.`updated_at`
|
|
FROM `mm_device_catalog` AS c;
|
|
|
|
CREATE OR REPLACE VIEW `models` AS
|
|
SELECT
|
|
c.`model`,
|
|
c.`device_type` AS `dtype`,
|
|
c.`market_brand` AS `brand`,
|
|
c.`manufacturer_brand` AS `brand_title`,
|
|
c.`code`,
|
|
c.`code_alias`,
|
|
c.`device_name` AS `model_name`,
|
|
c.`ver_name`,
|
|
c.`updated_at` AS `update_at`,
|
|
c.`hash_md5`,
|
|
c.`hash_crc`
|
|
FROM `mm_device_catalog` AS c;
|
|
|
|
CREATE OR REPLACE VIEW `vw_models` AS
|
|
SELECT
|
|
c.`model`,
|
|
c.`device_type` AS `dtype`,
|
|
c.`market_brand` AS `brand`,
|
|
c.`manufacturer_brand` AS `brand_title`,
|
|
c.`code`,
|
|
c.`code_alias`,
|
|
c.`device_name` AS `model_name`,
|
|
c.`ver_name`,
|
|
c.`updated_at` AS `update_at`,
|
|
c.`hash_md5`,
|
|
c.`hash_crc`
|
|
FROM `mm_device_catalog` AS c;
|
|
|
|
CREATE OR REPLACE VIEW `python_services_test`.`models` AS
|
|
SELECT
|
|
`model`,
|
|
`dtype`,
|
|
`brand`,
|
|
`brand_title`,
|
|
`code`,
|
|
`code_alias`,
|
|
`model_name`,
|
|
`ver_name`,
|
|
`update_at`,
|
|
`hash_md5`,
|
|
`hash_crc`
|
|
FROM `mobilemodels`.`models`;
|