MDSN8KmV

1-- data model search
2-- Author: Julio María Meca Hansen <julio@meca-innotech.com>
3
4-- bootstrapping
5SET FOREIGN_KEY_CHECKS = 0;
6DROP TABLE IF EXISTS c_search_history;
7DROP TABLE IF EXISTS c_search_weight_entries;
8DROP TABLE IF EXISTS c_search_weight_history;
9SET FOREIGN_KEY_CHECKS = 1;
10
11-- search history
12CREATE TABLE IF NOT EXISTS c_search_history (
13    entry_id BIGINT(64) UNSIGNED NOT NULL AUTO_INCREMENT,
14    entry_date_added DATETIME NOT NULL,
15    entry_language CHAR(2) NOT NULL DEFAULT 'es',
16    entry_query TEXT NOT NULL,
17    PRIMARY KEY (entry_id),
18    INDEX (entry_date_added),
19    INDEX (entry_language)
20) ENGINE=InnoDB DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_unicode_520_ci;
21
22-- search weight entries
23CREATE TABLE IF NOT EXISTS c_search_weight_entries (
24    entry_id BIGINT(64) UNSIGNED NOT NULL AUTO_INCREMENT,
25    entry_date DATETIME NOT NULL,
26    entry_model VARCHAR(175) NOT NULL,
27    entry_model_id BIGINT(64) UNSIGNED NOT NULL,
28    entry_value DOUBLE NOT NULL,
29    PRIMARY KEY (entry_id),
30    INDEX (entry_date),
31    INDEX (entry_model),
32    INDEX (entry_model_id),
33    INDEX (entry_value)
34) ENGINE=InnoDB DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_unicode_520_ci;
35
36-- search weight entry history
37CREATE TABLE IF NOT EXISTS c_search_weight_entries_history (
38    entry_id BIGINT(64) UNSIGNED NOT NULL AUTO_INCREMENT,
39    entry_date DATETIME NOT NULL,
40    entry_model VARCHAR(175) NOT NULL,
41    entry_model_id BIGINT(64) UNSIGNED,
42    entry_value DOUBLE NOT NULL,
43    PRIMARY KEY (entry_id),
44    INDEX (entry_date),
45    INDEX (entry_model),
46    INDEX (entry_model_id),
47    INDEX (entry_value)
48) ENGINE=InnoDB DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_unicode_520_ci;
49
50CREATE TRIGGER t_search_weight_entries_insert AFTER INSERT ON c_search_weight_entries FOR EACH ROW
51BEGIN
52    INSERT INTO c_search_weight_entries_history (entry_date, entry_model, entry_model_id, entry_value) VALUES (NOW(), NEW.entry_model, NEW.entry_model_id, NEW.entry_value);
53END;