· 6 years ago · Jan 03, 2020, 03:48 PM
1create schema if not exists newsdata collate utf8mb4_unicode_ci;
2
3create table if not exists alembic_version
4(
5 version_num varchar(32) not null
6 primary key
7)
8charset=latin1;
9
10create table if not exists category
11(
12 id int auto_increment
13 primary key,
14 category varchar(32) not null,
15 constraint category
16 unique (category)
17);
18
19create table if not exists contact
20(
21 id int auto_increment
22 primary key,
23 name varchar(256) not null,
24 email varchar(256) not null,
25 message text not null
26)
27charset=latin1;
28
29create table if not exists country
30(
31 id int auto_increment
32 primary key,
33 short_code varchar(32) not null,
34 country varchar(32) not null,
35 constraint short_code
36 unique (short_code)
37);
38
39create table if not exists language
40(
41 id int auto_increment
42 primary key,
43 short_code varchar(2) not null,
44 language varchar(32) not null,
45 constraint short_code
46 unique (short_code)
47);
48
49create table if not exists source
50(
51 id int auto_increment
52 primary key,
53 source_id varchar(32) not null,
54 name varchar(255) not null,
55 url varchar(255) not null,
56 title text not null,
57 category int not null,
58 language int not null,
59 country int not null,
60 constraint source_id
61 unique (source_id),
62 constraint source_ibfk_1
63 foreign key (category) references category (id),
64 constraint source_ibfk_2
65 foreign key (country) references country (id),
66 constraint source_ibfk_3
67 foreign key (language) references language (id)
68);
69
70create table if not exists news
71(
72 id int auto_increment
73 primary key,
74 url varchar(512) not null,
75 url_sha1_hash varchar(64) not null,
76 title varchar(512) not null,
77 author varchar(128) null,
78 source int not null,
79 description varchar(512) null,
80 published_date datetime null,
81 main_image varchar(1024) null,
82 constraint url_sha1_hash
83 unique (url_sha1_hash),
84 constraint news_ibfk_1
85 foreign key (source) references source (id)
86);
87
88create index published_date_idx
89 on news (published_date);
90
91create index source
92 on news (source);
93
94create index title_idx
95 on news (title);
96
97create table if not exists news_all
98(
99 id int auto_increment
100 primary key,
101 url varchar(512) not null,
102 url_sha1_hash varchar(64) not null,
103 title varchar(512) not null,
104 author varchar(128) null,
105 source_id int not null,
106 subtitle text null,
107 description varchar(512) null,
108 published_date datetime null,
109 main_image varchar(1024) null,
110 content text null,
111 category text null,
112 tags text null,
113 constraint url_sha1_hash
114 unique (url_sha1_hash),
115 constraint news_all_ibfk_1
116 foreign key (source_id) references source (id)
117);
118
119create index source_id
120 on news_all (source_id);
121
122create index category
123 on source (category);
124
125create index country
126 on source (country);
127
128create index language
129 on source (language);
130
131create table if not exists subscriber
132(
133 id int auto_increment
134 primary key,
135 name varchar(256) null,
136 email varchar(256) not null
137)
138charset=latin1;
139
140create table if not exists user
141(
142 id int auto_increment
143 primary key,
144 name varchar(256) not null,
145 email varchar(256) not null,
146 password varchar(256) not null,
147 api_key varchar(256) not null,
148 is_active tinyint(1) not null,
149 plan varchar(256) not null,
150 total_requests int not null,
151 remaining_requests int not null,
152 created_on datetime not null,
153 quota_reset_date date not null,
154 constraint email
155 unique (email)
156)
157charset=latin1;