F0T6dTtx

1create schema if not exists newsdata collate utf8mb4_unicode_ci;
2
3create table if not exists category
4(
5	id int auto_increment
6		primary key,
7	category varchar(32) not null,
8	constraint category
9		unique (category)
10);
11
12create table if not exists country
13(
14	id int auto_increment
15		primary key,
16	short_code varchar(32) not null,
17	country varchar(32) not null,
18	constraint short_code
19		unique (short_code)
20);
21
22create table if not exists language
23(
24	id int auto_increment
25		primary key,
26	short_code varchar(2) not null,
27	language varchar(32) not null,
28	constraint short_code
29		unique (short_code)
30);
31
32create table if not exists source
33(
34	id int auto_increment
35		primary key,
36	source_id varchar(32) not null,
37	name varchar(255) not null,
38	url varchar(255) not null,
39	title text not null,
40	category int not null,
41	language int not null,
42	country int not null,
43	constraint source_id
44		unique (source_id),
45	constraint source_ibfk_1
46		foreign key (category) references category (id),
47	constraint source_ibfk_2
48		foreign key (country) references country (id),
49	constraint source_ibfk_3
50		foreign key (language) references language (id)
51);
52
53create table if not exists news
54(
55	id int auto_increment
56		primary key,
57	url varchar(512) not null,
58	url_sha1_hash varchar(64) not null,
59	title varchar(512) not null,
60	author varchar(128) null,
61	source int not null,
62	description varchar(512) null,
63	published_date datetime null,
64	main_image varchar(1024) null,
65	constraint url_sha1_hash
66		unique (url_sha1_hash),
67	constraint news_ibfk_1
68		foreign key (source) references source (id)
69);
70
71create index published_date_idx
72	on news (published_date);
73
74create index source
75	on news (source);
76
77create index title_idx
78	on news (title);
79
80create index source_id
81	on news_all (source_id);
82
83create index category
84	on source (category);
85
86create index country
87	on source (country);
88
89create index language
90	on source (language);