-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathdb_structure.sql
More file actions
246 lines (210 loc) · 10.9 KB
/
db_structure.sql
File metadata and controls
246 lines (210 loc) · 10.9 KB
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
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
-- MySQL Workbench Forward Engineering
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
-- -----------------------------------------------------
-- Schema tweet_wishes
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `tweet_wishes` DEFAULT CHARACTER SET utf8mb4 ;
USE `tweet_wishes` ;
-- -----------------------------------------------------
-- Table `tweet_wishes`.`users`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `tweet_wishes`.`users` ;
CREATE TABLE IF NOT EXISTS `tweet_wishes`.`users` (
`id` BIGINT NOT NULL,
`username` VARCHAR(140) NOT NULL,
`profile_picture_url` TEXT NULL DEFAULT NULL,
PRIMARY KEY (`id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4;
-- -----------------------------------------------------
-- Table `tweet_wishes`.`tweet_wishes`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `tweet_wishes`.`tweet_wishes` ;
CREATE TABLE IF NOT EXISTS `tweet_wishes`.`tweet_wishes` (
`id` BIGINT NOT NULL,
`author` BIGINT NOT NULL,
`tweet_text` TEXT NULL DEFAULT NULL,
`created_at` DATETIME NULL,
`is_retweet` TINYINT(1) NULL DEFAULT NULL,
`retweet_tweet_id` BIGINT NULL,
`sentiment` DOUBLE NULL,
INDEX `fk_tweet_wishes_users_idx` (`author` ASC),
PRIMARY KEY (`id`),
CONSTRAINT `fk_author`
FOREIGN KEY (`author`)
REFERENCES `tweet_wishes`.`users` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4;
-- -----------------------------------------------------
-- Table `tweet_wishes`.`tweet_mentions_user`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `tweet_wishes`.`tweet_mentions_user` ;
CREATE TABLE IF NOT EXISTS `tweet_wishes`.`tweet_mentions_user` (
`tweet_id` BIGINT NOT NULL,
`user_id` BIGINT NOT NULL,
INDEX `fk_tweet_mentions_user_users1_idx` (`user_id` ASC),
CONSTRAINT `fk_tweet_mentioning_user`
FOREIGN KEY (`tweet_id`)
REFERENCES `tweet_wishes`.`tweet_wishes` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_user_mentioned`
FOREIGN KEY (`user_id`)
REFERENCES `tweet_wishes`.`users` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4;
-- -----------------------------------------------------
-- Table `tweet_wishes`.`hashtags`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `tweet_wishes`.`hashtags` ;
CREATE TABLE IF NOT EXISTS `tweet_wishes`.`hashtags` (
`hashtag` VARCHAR(140) NOT NULL,
PRIMARY KEY (`hashtag`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4;
-- -----------------------------------------------------
-- Table `tweet_wishes`.`tweet_contains_hashtag`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `tweet_wishes`.`tweet_contains_hashtag` ;
CREATE TABLE IF NOT EXISTS `tweet_wishes`.`tweet_contains_hashtag` (
`tweet_id` BIGINT NOT NULL,
`hashtag` VARCHAR(140) NOT NULL,
INDEX `fk_hashtag_idx` (`hashtag` ASC),
CONSTRAINT `fk_tweet_containining_hash`
FOREIGN KEY (`tweet_id`)
REFERENCES `tweet_wishes`.`tweet_wishes` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_hashtag_contained`
FOREIGN KEY (`hashtag`)
REFERENCES `tweet_wishes`.`hashtags` (`hashtag`)
ON DELETE NO ACTION
ON UPDATE NO ACTION
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4;
-- -----------------------------------------------------
-- Table `tweet_wishes`.`stats_general_40s`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `tweet_wishes`.`stats_general_40s` ;
CREATE TABLE IF NOT EXISTS `tweet_wishes`.`stats_general_40s` (
`datetime` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`tweets_total` INT NULL DEFAULT NULL,
`tweets_english` INT NULL DEFAULT NULL,
`wishes_total` INT NULL DEFAULT NULL,
`sentiment_average` DOUBLE NULL)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4;
-- -----------------------------------------------------
-- Table `tweet_wishes`.`stats_general_20m`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `tweet_wishes`.`stats_general_20m` ;
CREATE TABLE IF NOT EXISTS `tweet_wishes`.`stats_general_20m` (
`datetime` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`tweets_total` INT NULL DEFAULT NULL,
`tweets_english` INT NULL DEFAULT NULL,
`wishes_total` INT NULL DEFAULT NULL,
`sentiment_average` DOUBLE NULL)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4;
-- -----------------------------------------------------
-- Table `tweet_wishes`.`stats_general_1d`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `tweet_wishes`.`stats_general_1d` ;
CREATE TABLE IF NOT EXISTS `tweet_wishes`.`stats_general_1d` (
`datetime` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`tweets_total` INT NULL DEFAULT NULL,
`tweets_english` INT NULL DEFAULT NULL,
`wishes_total` INT NULL DEFAULT NULL,
`sentiment_average` DOUBLE NULL)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4;
-- -----------------------------------------------------
-- Events for database 'tweet_wishes'
-- -----------------------------------------------------
/*!50106 SET @save_time_zone= @@TIME_ZONE */ ;
/*!50106 DROP EVENT IF EXISTS `stats_general_20m_clean` */;
DELIMITER ;;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;;
/*!50003 SET character_set_client = utf8mb4 */ ;;
/*!50003 SET character_set_results = utf8mb4 */ ;;
/*!50003 SET collation_connection = utf8mb4_unicode_ci */ ;;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;;
/*!50003 SET sql_mode = 'NO_ENGINE_SUBSTITUTION' */ ;;
/*!50003 SET @saved_time_zone = @@time_zone */ ;;
/*!50003 SET time_zone = 'UTC' */ ;;
/*!50106 CREATE*/ /*!50117 DEFINER=`user_rw`@`%`*/ /*!50106 EVENT `stats_general_20m_clean` ON SCHEDULE EVERY 1 DAY STARTS '2016-01-01 00:00:00' ON COMPLETION NOT PRESERVE ENABLE COMMENT 'Clean old data from stats_general_20m' DO delete from stats_general_20m where datetime < timestamp(utc_timestamp() - interval 3 day) */ ;;
/*!50003 SET time_zone = @saved_time_zone */ ;;
/*!50003 SET sql_mode = @saved_sql_mode */ ;;
/*!50003 SET character_set_client = @saved_cs_client */ ;;
/*!50003 SET character_set_results = @saved_cs_results */ ;;
/*!50003 SET collation_connection = @saved_col_connection */ ;;
/*!50106 DROP EVENT IF EXISTS `stats_general_20m_to_1d` */;;
DELIMITER ;;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;;
/*!50003 SET character_set_client = utf8mb4 */ ;;
/*!50003 SET character_set_results = utf8mb4 */ ;;
/*!50003 SET collation_connection = utf8mb4_unicode_ci */ ;;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;;
/*!50003 SET sql_mode = 'NO_ENGINE_SUBSTITUTION' */ ;;
/*!50003 SET @saved_time_zone = @@time_zone */ ;;
/*!50003 SET time_zone = 'UTC' */ ;;
/*!50106 CREATE*/ /*!50117 DEFINER=`user_rw`@`%`*/ /*!50106 EVENT `stats_general_20m_to_1d` ON SCHEDULE EVERY 1 DAY STARTS '2016-01-01 00:02:00' ON COMPLETION NOT PRESERVE ENABLE COMMENT 'Aggregate general stats 20m -> 1d' DO begin insert into stats_general_1d (tweets_total, tweets_english, wishes_total, sentiment_average) select SUM(tweets_total), SUM(tweets_english), SUM(wishes_total), AVG(sentiment_average) from stats_general_20m where datetime > timestamp(utc_timestamp() - interval 1 day); end */ ;;
/*!50003 SET time_zone = @saved_time_zone */ ;;
/*!50003 SET sql_mode = @saved_sql_mode */ ;;
/*!50003 SET character_set_client = @saved_cs_client */ ;;
/*!50003 SET character_set_results = @saved_cs_results */ ;;
/*!50003 SET collation_connection = @saved_col_connection */ ;;
/*!50106 DROP EVENT IF EXISTS `stats_general_40s_clean` */;;
DELIMITER ;;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;;
/*!50003 SET character_set_client = utf8mb4 */ ;;
/*!50003 SET character_set_results = utf8mb4 */ ;;
/*!50003 SET collation_connection = utf8mb4_unicode_ci */ ;;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;;
/*!50003 SET sql_mode = 'NO_ENGINE_SUBSTITUTION' */ ;;
/*!50003 SET @saved_time_zone = @@time_zone */ ;;
/*!50003 SET time_zone = 'UTC' */ ;;
/*!50106 CREATE*/ /*!50117 DEFINER=`user_rw`@`%`*/ /*!50106 EVENT `stats_general_40s_clean` ON SCHEDULE EVERY 20 MINUTE STARTS '2016-01-01 00:00:00' ON COMPLETION NOT PRESERVE ENABLE COMMENT 'Clean old data from stats_general_40s' DO delete from stats_general_40s where datetime < timestamp(utc_timestamp() - interval 1 hour) */ ;;
/*!50003 SET time_zone = @saved_time_zone */ ;;
/*!50003 SET sql_mode = @saved_sql_mode */ ;;
/*!50003 SET character_set_client = @saved_cs_client */ ;;
/*!50003 SET character_set_results = @saved_cs_results */ ;;
/*!50003 SET collation_connection = @saved_col_connection */ ;;
/*!50106 DROP EVENT IF EXISTS `stats_general_40s_to_20m` */;;
DELIMITER ;;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;;
/*!50003 SET character_set_client = utf8mb4 */ ;;
/*!50003 SET character_set_results = utf8mb4 */ ;;
/*!50003 SET collation_connection = utf8mb4_unicode_ci */ ;;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;;
/*!50003 SET sql_mode = 'NO_ENGINE_SUBSTITUTION' */ ;;
/*!50003 SET @saved_time_zone = @@time_zone */ ;;
/*!50003 SET time_zone = 'UTC' */ ;;
/*!50106 CREATE*/ /*!50117 DEFINER=`user_rw`@`%`*/ /*!50106 EVENT `stats_general_40s_to_20m` ON SCHEDULE EVERY 20 MINUTE STARTS '2016-01-01 00:01:00' ON COMPLETION NOT PRESERVE ENABLE COMMENT 'Aggregate general stats 40s -> 20m' DO begin insert into stats_general_20m (tweets_total, tweets_english, wishes_total, sentiment_average) select SUM(tweets_total), SUM(tweets_english), SUM(wishes_total), AVG(sentiment_average) from stats_general_40s where datetime > timestamp(utc_timestamp() - interval 20 minute); end */ ;;
/*!50003 SET time_zone = @saved_time_zone */ ;;
/*!50003 SET sql_mode = @saved_sql_mode */ ;;
/*!50003 SET character_set_client = @saved_cs_client */ ;;
/*!50003 SET character_set_results = @saved_cs_results */ ;;
/*!50003 SET collation_connection = @saved_col_connection */ ;;
DELIMITER ;
/*!50106 SET TIME_ZONE= @save_time_zone */ ;
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;