nsFbcxYH

1
2drop schema if exists public cascade;
3create schema public;
4
5create table system_settings (
6  setting_id    serial,
7
8  setting_name  text not null unique,
9  setting_value text not null,
10
11  primary key (setting_id)
12);
13
14create table feeds (
15  feed_id serial,
16
17  feed_name text not NULL,
18  feed_url text NOT NULL,
19
20  feed_hash bigint,
21  
22  primary key(feed_id)
23);
24
25CREATE TABLE feed_items (
26  feed_item_id serial,
27
28  feed_item_title text NOT NULL,
29  feed_item_url text,
30
31  feed_id INTEGER NOT NULL REFERENCES feeds,
32  feed_item_added_time timestamp(0) NOT NULL,
33  feed_item_description text,
34
35  feed_item_hash bigint not null,
36
37  PRIMARY KEY (feed_item_id)
38);
39
40create index idx__feed_items__feed_id on feed_items using btree(feed_id);
41create unique index idx__feed_items__feed_item_hash on feed_items using btree(feed_item_hash);
42
43CREATE FUNCTION get_missing_feed_items(p_feed_id int, p_new_item_hashes bigint[]) RETURNS bigint[] AS $$
44  SELECT array(
45    SELECT unnest(p_new_item_hashes)
46    EXCEPT
47    SELECT fi.feed_item_hash from feed_items fi where fi.feed_id = p_feed_id
48  )
49$$
50language SQL stable;
51
52
53
54INSERT into public.system_settings(setting_name, setting_value) VALUES
55  ('HttpClientUserAgent', 'Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/72.0.3626.81 Safari/537.36'),
56  ('FetcherCyclePause', '60'),
57  ('HttpClientRequestTimeout', '120'),
58  ('ParallelFeedFetching', 'true')
59  ;