-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathinstall.sql
129 lines (105 loc) · 5.93 KB
/
install.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
SET NAMES utf8;
SET time_zone = '+00:00';
SET foreign_key_checks = 0;
SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
DROP TABLE IF EXISTS `message_log`;
CREATE TABLE `message_log` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
`message_sent` datetime DEFAULT NULL COMMENT 'Message sent',
`message_status` enum('ok','error') COLLATE utf8_czech_ci NOT NULL DEFAULT 'error' COMMENT 'Message status',
`message_body` text COLLATE utf8_czech_ci DEFAULT NULL COMMENT 'Message body',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci COMMENT='Messages';
DROP TABLE IF EXISTS `records`;
CREATE TABLE `records` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
`website_id` int(11) NOT NULL COMMENT 'Website',
`occurrence_first` datetime DEFAULT NULL COMMENT 'First occurence',
`occurrence_last` datetime DEFAULT NULL COMMENT 'Last occurence',
`item_id` text COLLATE utf8_czech_ci DEFAULT NULL COMMENT 'Item ID',
`item_1` text COLLATE utf8_czech_ci DEFAULT NULL COMMENT 'Item 1',
`item_2` text COLLATE utf8_czech_ci DEFAULT NULL COMMENT 'Item 2',
`item_3` text COLLATE utf8_czech_ci DEFAULT NULL COMMENT 'Item 3',
`item_4` text COLLATE utf8_czech_ci DEFAULT NULL COMMENT 'Item 4',
`item_5` text COLLATE utf8_czech_ci DEFAULT NULL COMMENT 'Item 5',
`message_sent` tinyint(4) NOT NULL DEFAULT 0 COMMENT 'Message sent status',
PRIMARY KEY (`id`),
UNIQUE KEY `item_id` (`item_id`(400)),
KEY `website_id` (`website_id`),
CONSTRAINT `records_ibfk_1` FOREIGN KEY (`website_id`) REFERENCES `website` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci COMMENT='Records';
DROP TABLE IF EXISTS `website`;
CREATE TABLE `website` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
`label` varchar(120) COLLATE utf8_czech_ci NOT NULL COMMENT 'Label',
`url` varchar(400) COLLATE utf8_czech_ci NOT NULL COMMENT 'Tracked URL',
`status` enum('active','inactive') COLLATE utf8_czech_ci NOT NULL DEFAULT 'active' COMMENT 'Status',
`tracking_last` datetime DEFAULT NULL COMMENT 'Last tracking datetime',
`tracking_interval` enum('10m','1h','1d') COLLATE utf8_czech_ci NOT NULL DEFAULT '1h' COMMENT 'Tracking interval',
`tracking_priority` enum('schedule','force_next') COLLATE utf8_czech_ci NOT NULL DEFAULT 'force_next' COMMENT 'Tracking priority',
`content_wrapper` varchar(400) COLLATE utf8_czech_ci NOT NULL COMMENT 'Block wrapper',
`content_id` varchar(400) COLLATE utf8_czech_ci NOT NULL COMMENT 'Content ID',
`content_item_1` varchar(400) COLLATE utf8_czech_ci NOT NULL COMMENT 'Content item 1',
`content_item_2` varchar(400) COLLATE utf8_czech_ci NOT NULL COMMENT 'Content item 2',
`content_item_3` varchar(400) COLLATE utf8_czech_ci NOT NULL COMMENT 'Content item 3',
`content_item_4` varchar(400) COLLATE utf8_czech_ci NOT NULL COMMENT 'Content item 4',
`content_item_5` varchar(400) COLLATE utf8_czech_ci NOT NULL COMMENT 'Content item 5',
`message` text COLLATE utf8_czech_ci NOT NULL COMMENT 'Message',
PRIMARY KEY (`id`),
KEY `status_id` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci COMMENT='Websites';
DROP TABLE IF EXISTS `tracking_log`;
CREATE TABLE `tracking_log` (
`id` int NOT NULL COMMENT 'ID' AUTO_INCREMENT PRIMARY KEY,
`tracking_timestamp` datetime NULL COMMENT 'Timestamp',
`tracking_log` text NULL COMMENT 'Log'
) COMMENT='Tracking log';
ALTER TABLE `website` ADD `default_content_item_1` varchar(400) COLLATE 'utf8_czech_ci' NULL COMMENT 'Content item 1 (default)';
ALTER TABLE `website` ADD `default_content_item_2` varchar(400) COLLATE 'utf8_czech_ci' NULL COMMENT 'Content item 2 (default)';
ALTER TABLE `website` ADD `default_content_item_3` varchar(400) COLLATE 'utf8_czech_ci' NULL COMMENT 'Content item 3 (default)';
ALTER TABLE `website` ADD `default_content_item_4` varchar(400) COLLATE 'utf8_czech_ci' NULL COMMENT 'Content item 4 (default)';
ALTER TABLE `website` ADD `default_content_item_5` varchar(400) COLLATE 'utf8_czech_ci' NULL COMMENT 'Content item 5 (default)';
ALTER TABLE `website`
ADD `content_id_context` enum('global','website') COLLATE 'utf8_czech_ci' NOT NULL DEFAULT 'website' COMMENT 'Content unique ID context' AFTER `content_id`;
ALTER TABLE `records`
DROP INDEX `item_id`;
ALTER TABLE `website`
ADD `tracking_type` enum('single','multiple') COLLATE 'utf8_czech_ci' NOT NULL DEFAULT 'multiple' COMMENT 'Tracking type' AFTER `tracking_priority`;
ALTER TABLE `records`
ADD `occurrence_count` int NULL COMMENT 'Occurence count' AFTER `occurrence_last`;
ALTER TABLE `records`
CHANGE `occurrence_count` `occurrence_count` int(11) NULL DEFAULT '1' COMMENT 'Occurence count' AFTER `occurrence_last`;
ALTER TABLE `website`
CHANGE `tracking_interval` `tracking_interval` enum('10m','1h','3h','1d') COLLATE 'utf8_czech_ci' NOT NULL DEFAULT '1h' COMMENT 'Tracking interval' AFTER `tracking_last`;
CREATE TABLE `folder` (
`id` int NOT NULL AUTO_INCREMENT PRIMARY KEY,
`name` varchar(120) NOT NULL
) COLLATE 'utf8_czech_ci';
ALTER TABLE `website`
ADD `folder_id` int(11) NULL AFTER `label`,
ADD FOREIGN KEY (`folder_id`) REFERENCES `folder` (`id`);
ALTER TABLE `website`
CHANGE `folder_id` `folder_id` int(11) NULL COMMENT 'Folder' AFTER `label`;
ALTER TABLE `folder`
COMMENT='Folder';
-- ===================================================
CREATE OR REPLACE VIEW cron_list AS
SELECT id,label,tracking_last, tracking_interval, tracking_priority, content_id_context, tracking_type FROM website
WHERE
status='active'
AND
(
tracking_priority = 'force_next'
OR
(
tracking_priority = 'schedule' AND
(
(tracking_interval='10m' AND tracking_last<(NOW()-INTERVAL 9 MINUTE)) OR
(tracking_interval='1h' AND tracking_last<(NOW()-INTERVAL 55 MINUTE)) OR
(tracking_interval='3h' AND tracking_last<(NOW()-INTERVAL 175 MINUTE)) OR
(tracking_interval='1d' AND tracking_last<(NOW()-INTERVAL 1435 MINUTE)) OR
tracking_last IS NULL
)
)
)
ORDER BY FIELD(tracking_priority, 'force_next','schedule'), tracking_last ASC;