-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathinit-postgres.sql
More file actions
96 lines (93 loc) · 6.69 KB
/
Copy pathinit-postgres.sql
File metadata and controls
96 lines (93 loc) · 6.69 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
CREATE TABLE IF NOT EXISTS sources (
id SERIAL PRIMARY KEY,
name VARCHAR(255) NOT NULL,
type VARCHAR(50) NOT NULL,
weight FLOAT NOT NULL,
language VARCHAR(10) DEFAULT 'ru',
category VARCHAR(50),
url TEXT,
is_active BOOLEAN DEFAULT TRUE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS raw_messages (
id SERIAL PRIMARY KEY,
source_id INTEGER REFERENCES sources(id),
text TEXT NOT NULL,
original_text TEXT,
engagement_metric INTEGER DEFAULT 0,
published_at TIMESTAMP,
is_processed BOOLEAN DEFAULT FALSE,
sentiment_score FLOAT DEFAULT NULL,
probability FLOAT DEFAULT NULL,
language VARCHAR(10) DEFAULT 'ru',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_raw_messages_processed ON raw_messages(is_processed);
CREATE INDEX IF NOT EXISTS idx_raw_messages_published ON raw_messages(published_at DESC);
CREATE INDEX IF NOT EXISTS idx_raw_messages_source ON raw_messages(source_id);
CREATE INDEX IF NOT EXISTS idx_raw_messages_created ON raw_messages(created_at DESC);
INSERT INTO sources (id, name, type, weight, language, category, url) VALUES
(10, 'Lenta.ru', 'rss', 1.0, 'ru', 'federal', 'https://lenta.ru/rss/news'),
(11, 'RBC', 'rss', 1.0, 'ru', 'federal', 'https://rssexport.rbc.ru/rbcnews/news/30/full.rss'),
(12, 'RIA Novosti', 'rss', 1.0, 'ru', 'federal', 'https://ria.ru/export/rss2/archive/index.xml'),
(13, 'Vedomosti', 'rss', 1.0, 'ru', 'federal', 'https://www.vedomosti.ru/rss/news'),
(14, 'Kommersant', 'rss', 1.0, 'ru', 'federal', 'https://www.kommersant.ru/RSS/news.xml'),
(15, 'TASS', 'rss', 1.0, 'ru', 'federal', 'https://tass.ru/rss/v2.xml'),
(16, 'Rossiyskaya Gazeta', 'rss', 1.0, 'ru', 'federal', 'https://rg.ru/xml/index.xml'),
(17, 'Interfax', 'rss', 1.0, 'ru', 'federal', 'https://www.interfax.ru/rss.asp'),
(18, 'RT Russian', 'rss', 1.0, 'ru', 'federal', 'https://russian.rt.com/rss'),
(19, 'Izvestia', 'rss', 1.0, 'ru', 'federal', 'https://iz.ru/xml/rss/all.xml'),
(20, 'KP', 'rss', 1.0, 'ru', 'federal', 'https://www.kp.rf/rss/25000.xml'),
(21, 'MK', 'rss', 1.0, 'ru', 'federal', 'https://www.mk.ru/rss/news/'),
(22, 'Argumenty i Fakty', 'rss', 1.0, 'ru', 'federal', 'https://aif.ru/rss/all.xml'),
(23, 'Gazeta.ru', 'rss', 1.0, 'ru', 'federal', 'https://www.gazeta.ru/export/rss/lenta.xml'),
(24, 'NTV', 'rss', 1.0, 'ru', 'federal', 'https://www.ntv.ru/rss/news/'),
(25, 'Zvezda', 'rss', 1.0, 'ru', 'federal', 'https://tvzvezda.ru/rss/'),
(30, 'Meduza', 'rss', 1.2, 'ru', 'opposition', 'https://meduza.io/rss/all'),
(31, 'Dozhd', 'rss', 1.2, 'ru', 'opposition', 'https://tvrain.ru/rss/news.xml'),
(32, 'Radio Svoboda', 'rss', 1.2, 'ru', 'opposition', 'https://www.svoboda.org/api/zqovhtljaafg'),
(33, 'Novaya Gazeta', 'rss', 1.2, 'ru', 'opposition', 'https://novayagazeta.ru/rss'),
(34, 'The Insider', 'rss', 1.3, 'ru', 'opposition', 'https://theins.ru/rss'),
(35, 'Znak.com', 'rss', 1.1, 'ru', 'opposition', 'https://www.znak.com/rss.xml'),
(36, 'Ura.ru', 'rss', 1.1, 'ru', 'opposition', 'https://ura.news/rss/all'),
(37, 'EADaily', 'rss', 1.1, 'ru', 'opposition', 'https://eadaily.com/ru/rss'),
(38, 'Fontanka', 'rss', 1.1, 'ru', 'opposition', 'https://www.fontanka.ru/rss.xml'),
(39, 'Sobesednik', 'rss', 1.1, 'ru', 'opposition', 'https://sobesednik.ru/export/rss/news'),
(40, 'Republic', 'rss', 1.2, 'ru', 'opposition', 'https://republic.ru/rss/all/'),
(50, 'UNIAN', 'rss', 1.2, 'uk', 'ukraine', 'https://www.unian.net/rss'),
(51, 'Korrespondent', 'rss', 1.1, 'uk', 'ukraine', 'https://korrespondent.net/rss'),
(52, 'Segodnya.ua', 'rss', 1.1, 'uk', 'ukraine', 'https://www.segodnya.ua/rss.xml'),
(53, 'Gordon.ua', 'rss', 1.2, 'uk', 'ukraine', 'https://gordonua.com/rss'),
(54, 'Censor.NET', 'rss', 1.2, 'uk', 'ukraine', 'https://censor.net/ua/rss'),
(55, 'Strana.ua', 'rss', 1.1, 'uk', 'ukraine', 'https://strana.ua/rss'),
(56, 'Ukrainska Pravda', 'rss', 1.2, 'uk', 'ukraine', 'https://www.pravda.com.ua/rss/'),
(57, 'European Pravda', 'rss', 1.2, 'uk', 'ukraine', 'https://www.eurointegration.com.ua/rss/'),
(58, 'Zerkalo Nedeli', 'rss', 1.1, 'uk', 'ukraine', 'https://zn.ua/rss/all.xml'),
(59, 'Glavcom.ua', 'rss', 1.1, 'uk', 'ukraine', 'https://glavcom.ua/rss/all.xml'),
(60, 'NV.ua', 'rss', 1.1, 'uk', 'ukraine', 'https://nv.ua/rss'),
(70, 'BBC Russian', 'rss', 1.0, 'ru', 'international', 'https://www.bbc.com/russian/index.xml'),
(71, 'DW Russian', 'rss', 1.0, 'ru', 'international', 'https://rss.dw.com/rdf/rss-rus'),
(72, 'Reuters Russian', 'rss', 0.9, 'ru', 'international', 'https://www.reuters.com/rss'),
(73, 'AP Russian', 'rss', 0.9, 'ru', 'international', 'https://hosted2.ap.org/atom/APDefault/rss_RU.xml'),
(74, 'CNN', 'rss', 0.8, 'en', 'international', 'https://edition.cnn.com/services/rss/'),
(75, 'NYT', 'rss', 0.8, 'en', 'international', 'https://www.nytimes.com/services/xml/rss/nyt/HomePage.xml'),
(76, 'WSJ', 'rss', 0.8, 'en', 'international', 'https://feeds.a.dj.com/rss/RSSWorldNews.xml'),
(77, 'Financial Times', 'rss', 0.8, 'en', 'international', 'https://www.ft.com/rss/home/russia'),
(78, 'Le Monde', 'rss', 0.8, 'fr', 'international', 'https://www.lemonde.fr/rss/une.xml'),
(79, 'The Guardian', 'rss', 0.8, 'en', 'international', 'https://www.theguardian.com/world/rss'),
(80, 'Der Spiegel', 'rss', 0.8, 'de', 'international', 'https://www.spiegel.de/index.rss'),
(90, 'Politico', 'rss', 0.7, 'en', 'expert', 'https://www.politico.com/rss'),
(91, 'Foreign Policy', 'rss', 0.7, 'en', 'expert', 'https://foreignpolicy.com/feed/'),
(92, 'War on the Rocks', 'rss', 0.8, 'en', 'expert', 'https://warontherocks.com/feed/'),
(93, 'ISW', 'rss', 0.9, 'en', 'expert', 'https://www.understandingwar.org/rss'),
(94, 'Atlantic Council', 'rss', 0.7, 'en', 'expert', 'https://www.atlanticcouncil.org/feed/'),
(95, 'RAND Corporation', 'rss', 0.7, 'en', 'expert', 'https://www.rand.org/rss.xml'),
(100, 'TG Mash', 'rss', 1.3, 'ru', 'telegram', 'https://t.me/s/mash_rss'),
(101, 'TG Baza', 'rss', 1.3, 'ru', 'telegram', 'https://t.me/s/baza_rss'),
(102, 'TG Readovka', 'rss', 1.3, 'ru', 'telegram', 'https://t.me/s/readovka_news_rss'),
(103, 'TG Meduza Live', 'rss', 1.2, 'ru', 'telegram', 'https://t.me/s/meduzalive_rss'),
(104, 'TG War Gonzo', 'rss', 1.3, 'ru', 'telegram', 'https://t.me/s/wargonzo_rss'),
(110, 'Yuri Podolyaka', 'rss', 1.1, 'ru', 'blog', 'https://yuripodolyaka.com/feed/'),
(111, 'Anatoly Shariy', 'rss', 1.1, 'ru', 'blog', 'https://shariy.net/feed/'),
(112, 'Ukraina.ru', 'rss', 1.0, 'ru', 'blog', 'https://ukraina.ru/export/rss/news/'),
(5, 'YouTube', 'youtube', 1.5, 'mixed', 'video', NULL);