You cannot select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
820 lines
43 KiB
PL/PgSQL
820 lines
43 KiB
PL/PgSQL
-- CREATE TABLES
|
|
create table articles (
|
|
article_id bigserial primary key,
|
|
title varchar(500) not null,
|
|
author varchar(200) not null,
|
|
date timestamptz not null,
|
|
content text not null,
|
|
description text not null,
|
|
rank integer not null default 5,
|
|
guid varchar(32) unique not null,
|
|
source_id integer not null,
|
|
uri varchar(500) not null unique);
|
|
create index articles_date_idx on articles (date desc);
|
|
create index articles_author_idx on articles (author);
|
|
create index articles_rank_idx on articles (rank);
|
|
create index articles_guid_idx on articles (guid);
|
|
create index articles_source_idx on articles (source_id);
|
|
|
|
create table authors (
|
|
id serial primary key,
|
|
author_id serial unique not null,
|
|
name varchar(50) unique not null);
|
|
create index authors_author_id_idx on authors (author_id);
|
|
create index authors_name_idx on authors (name);
|
|
|
|
create table article_author (
|
|
id bigserial primary key,
|
|
article_id integer not null references articles on delete cascade,
|
|
author_id integer not null references authors on delete cascade);
|
|
create index article_author_author_id_idx on article_author (author_id);
|
|
create index article_author_article_id_idx on article_author (article_id);
|
|
create unique index article_author_author_article_idx
|
|
on article_author(author_id, article_id);
|
|
|
|
create table article_source(
|
|
id serial primary key,
|
|
source_id serial unique not null,
|
|
name varchar(50) not null,
|
|
icon_path text not null);
|
|
create index source_name_idx on article_source (name);
|
|
create index source_source_id_idx on article_source (source_id);
|
|
|
|
create table article_images (
|
|
id bigserial primary key,
|
|
image_id bigserial not null unique,
|
|
article_id integer not null references articles on delete cascade,
|
|
original_path text not null,
|
|
original_width integer not null,
|
|
original_height integer not null,
|
|
cover boolean not null default 'FALSE');
|
|
create index article_images_article_id_idx on article_images (article_id);
|
|
|
|
create table article_stats (
|
|
id bigserial primary key,
|
|
article_id integer not null unique references articles on delete cascade,
|
|
points integer not null default 0,
|
|
view_count integer not null default 0);
|
|
create index article_stats_article_id_idx on article_stats (article_id);
|
|
create index article_stats_points_idx on article_stats (points);
|
|
create index article_stats_view_count_idx on article_stats (view_count);
|
|
|
|
create table tags(
|
|
id serial primary key,
|
|
tag_id serial unique not null,
|
|
name varchar(50) not null,
|
|
rank int not null);
|
|
create index tags_name_idx on tags (name);
|
|
create index tags_id_idx on tags(tag_id);
|
|
create index tags_rank_idx on tags(rank);
|
|
|
|
-- set default tag ranks
|
|
-- TODO change to insert
|
|
update tags set rank=10 where name='nation';
|
|
update tags set rank=12 where name='local';
|
|
update tags set rank=14 where name='politics';
|
|
update tags set rank=16 where name='world';
|
|
update tags set rank=18 where name='business';
|
|
update tags set rank=20 where name='opinion';
|
|
update tags set rank=22 where name='entertainment';
|
|
update tags set rank=24 where name='sports';
|
|
update tags set rank=26 where name='technology';
|
|
update tags set rank=28 where name='science';
|
|
update tags set rank=30 where name='health';
|
|
update tags set rank=32 where name='art';
|
|
update tags set rank=34 where name='style';
|
|
update tags set rank=36 where name='food';
|
|
update tags set rank=38 where name='travel';
|
|
update tags set rank=40 where name='music';
|
|
update tags set rank=42 where name='hacker-news';
|
|
update tags set rank=42 where name='eff';
|
|
update tags set rank=44 where name='religion';
|
|
update tags set rank=46 where name='president-2016';
|
|
update tags set rank=48 where name='bernie-sanders';
|
|
update tags set rank=48 where name='hillary-clinton';
|
|
update tags set rank=48 where name='jeb-bush';
|
|
update tags set rank=48 where name='ted-cruz';
|
|
update tags set rank=48 where name='rand-paul';
|
|
update tags set rank=48 where name='donald-trump';
|
|
update tags set rank=48 where name='ben-carson';
|
|
update tags set rank=48 where name='chris-christie';
|
|
update tags set rank=48 where name='carly-fiorina';
|
|
update tags set rank=48 where name='mike-huckabee';
|
|
update tags set rank=48 where name='marco-rubio';
|
|
|
|
update tags set rank=50 where name='terrorism';
|
|
update tags set rank=50 where name='isis';
|
|
update tags set rank=50 where name='climate-change';
|
|
|
|
update tags set rank=52 where name='comics';
|
|
update tags set rank=52 where name='christian';
|
|
|
|
update tags set name='carly-fiorina' where name='carly fiorina';
|
|
update tags set name='marco-rubio' where name='marco rubio';
|
|
update tags set name='chris-christie' where name='chris christie';
|
|
update tags set name='mike-hackabee' where name='mike huckabee';
|
|
|
|
|
|
create table tag_group_names(
|
|
id bigserial primary key,
|
|
group_id serial unique not null,
|
|
group_name varchar(255) unique not null,
|
|
group_rank int not null default 500);
|
|
create unique index tag_group_names_group_id_idx on tag_group_names(group_id);
|
|
create unique index tag_group_names_group_name_idx on tag_group_names(group_name);
|
|
|
|
create table tags_groups (
|
|
id bigserial primary key,
|
|
tag_id integer not null references tags on delete cascade,
|
|
group_id integer not null references tag_group_names on delete cascade);
|
|
create index tags_groups_tag_id_idx on tags_groups(tag_id);
|
|
create index tags_groups_group_id_idx on tags_groups(group_id);
|
|
create unique index tags_groups_tag_id_group_id_idx on tags_groups(tag_id, group_id);
|
|
|
|
insert into tag_group_names (group_name, group_rank) values('common', 10);
|
|
insert into tag_group_names (group_name, group_rank)
|
|
values('presidential-election-2016', 20);
|
|
insert into tag_group_names (group_name, group_rank) values('current-events', 30);
|
|
insert into tag_group_names (group_name, group_rank) values('programming', 40);
|
|
insert into tag_group_names (group_name, group_rank) values('comics', 50);
|
|
insert into tag_group_names (group_name, group_rank) values('eff', 51);
|
|
insert into tag_group_names (group_name, group_rank) values('default-common', 500);
|
|
insert into tag_group_names (group_name, group_rank) values('default-technology', 500);
|
|
insert into tag_group_names (group_name, group_rank) values('default-programming', 500);
|
|
insert into tag_group_names (group_name, group_rank) values('default-hacker-news', 500);
|
|
|
|
update tag_group_names set group_rank=10 where group_name='common';
|
|
update tag_group_names set group_rank=20
|
|
where group_name='presidential-election-2016';
|
|
update tag_group_names set group_rank=30 where group_name='current-events';
|
|
update tag_group_names set group_rank=40 where group_name='programming';
|
|
|
|
insert into tags_groups (tag_id, group_id)
|
|
values ((select tag_id from tags where tags.name='nation'),
|
|
(select group_id from tag_group_names where group_name='common'));
|
|
insert into tags_groups (tag_id, group_id)
|
|
values ((select tag_id from tags where tags.name='local'),
|
|
(select group_id from tag_group_names where group_name='common'));
|
|
insert into tags_groups (tag_id, group_id)
|
|
values ((select tag_id from tags where tags.name='business'),
|
|
(select group_id from tag_group_names where group_name='common'));
|
|
insert into tags_groups (tag_id, group_id)
|
|
values ((select tag_id from tags where tags.name='opinion'),
|
|
(select group_id from tag_group_names where group_name='common'));
|
|
insert into tags_groups (tag_id, group_id)
|
|
values ((select tag_id from tags where tags.name='entertainment'),
|
|
(select group_id from tag_group_names where group_name='common'));
|
|
insert into tags_groups (tag_id, group_id)
|
|
values ((select tag_id from tags where tags.name='sports'),
|
|
(select group_id from tag_group_names where group_name='common'));
|
|
insert into tags_groups (tag_id, group_id)
|
|
values ((select tag_id from tags where tags.name='technology'),
|
|
(select group_id from tag_group_names where group_name='common'));
|
|
insert into tags_groups (tag_id, group_id)
|
|
values ((select tag_id from tags where tags.name='style'),
|
|
(select group_id from tag_group_names where group_name='common'));
|
|
insert into tags_groups (tag_id, group_id)
|
|
values ((select tag_id from tags where tags.name='food'),
|
|
(select group_id from tag_group_names where group_name='common'));
|
|
insert into tags_groups (tag_id, group_id)
|
|
values ((select tag_id from tags where tags.name='travel'),
|
|
(select group_id from tag_group_names where group_name='common'));
|
|
insert into tags_groups (tag_id, group_id)
|
|
values ((select tag_id from tags where tags.name='religion'),
|
|
(select group_id from tag_group_names where group_name='common'));
|
|
insert into tags_groups (tag_id, group_id)
|
|
values ((select tag_id from tags where tags.name='politics'),
|
|
(select group_id from tag_group_names where group_name='common'));
|
|
insert into tags_groups (tag_id, group_id)
|
|
values ((select tag_id from tags where tags.name='world'),
|
|
(select group_id from tag_group_names where group_name='common'));
|
|
|
|
insert into tags_groups (tag_id, group_id)
|
|
values ((select tag_id from tags where tags.name='comics'),
|
|
(select group_id from tag_group_names where group_name='comics'));
|
|
|
|
insert into tags_groups (tag_id, group_id)
|
|
values ((select tag_id from tags where tags.name='eff'),
|
|
(select group_id from tag_group_names where group_name='eff'));
|
|
|
|
insert into tags_groups (tag_id, group_id)
|
|
values ((select tag_id from tags where tags.name='nation'),
|
|
(select group_id from tag_group_names where group_name='default-common'));
|
|
insert into tags_groups (tag_id, group_id)
|
|
values ((select tag_id from tags where tags.name='politics'),
|
|
(select group_id from tag_group_names where group_name='default-common'));
|
|
insert into tags_groups (tag_id, group_id)
|
|
values ((select tag_id from tags where tags.name='world'),
|
|
(select group_id from tag_group_names where group_name='default-common'));
|
|
insert into tags_groups (tag_id, group_id)
|
|
values ((select tag_id from tags where tags.name='opinion'),
|
|
(select group_id from tag_group_names where group_name='default-common'));
|
|
insert into tags_groups (tag_id, group_id)
|
|
values ((select tag_id from tags where tags.name='entertainment'),
|
|
(select group_id from tag_group_names where group_name='default-common'));
|
|
insert into tags_groups (tag_id, group_id)
|
|
values ((select tag_id from tags where tags.name='sports'),
|
|
(select group_id from tag_group_names where group_name='default-common'));
|
|
insert into tags_groups (tag_id, group_id)
|
|
values ((select tag_id from tags where tags.name='lafayette-shooting'),
|
|
(select group_id from tag_group_names where group_name='default-common'));
|
|
insert into tags_groups (tag_id, group_id)
|
|
values ((select tag_id from tags where tags.name='iran-accord'),
|
|
(select group_id from tag_group_names where group_name='default-common'));
|
|
|
|
insert into tags_groups (tag_id, group_id)
|
|
values ((select tag_id from tags where tags.name='technology'),
|
|
(select group_id from tag_group_names
|
|
where group_name='default-technology'));
|
|
insert into tags_groups (tag_id, group_id)
|
|
values ((select tag_id from tags where tags.name='hacker-news'),
|
|
(select group_id from tag_group_names
|
|
where group_name='default-technology'));
|
|
insert into tags_groups (tag_id, group_id)
|
|
values ((select tag_id from tags where tags.name='business'),
|
|
(select group_id from tag_group_names
|
|
where group_name='default-technology'));
|
|
insert into tags_groups (tag_id, group_id)
|
|
values ((select tag_id from tags where tags.name='opinion'),
|
|
(select group_id from tag_group_names
|
|
where group_name='default-technology'));
|
|
insert into tags_groups (tag_id, group_id)
|
|
values ((select tag_id from tags where tags.name='nation'),
|
|
(select group_id from tag_group_names
|
|
where group_name='default-technology'));
|
|
insert into tags_groups (tag_id, group_id)
|
|
values ((select tag_id from tags where tags.name='world'),
|
|
(select group_id from tag_group_names
|
|
where group_name='default-technology'));
|
|
|
|
insert into tags_groups (tag_id, group_id)
|
|
values ((select tag_id from tags where tags.name='technology'),
|
|
(select group_id from tag_group_names
|
|
where group_name='default-programming'));
|
|
insert into tags_groups (tag_id, group_id)
|
|
values ((select tag_id from tags where tags.name='hacker-news'),
|
|
(select group_id from tag_group_names
|
|
where group_name='default-programming'));
|
|
insert into tags_groups (tag_id, group_id)
|
|
values ((select tag_id from tags where tags.name='lisp'),
|
|
(select group_id from tag_group_names
|
|
where group_name='default-programming'));
|
|
insert into tags_groups (tag_id, group_id)
|
|
values ((select tag_id from tags where tags.name='haskell'),
|
|
(select group_id from tag_group_names
|
|
where group_name='default-programming'));
|
|
insert into tags_groups (tag_id, group_id)
|
|
values ((select tag_id from tags where tags.name='programming'),
|
|
(select group_id from tag_group_names
|
|
where group_name='default-programming'));
|
|
|
|
insert into tags_groups (tag_id, group_id)
|
|
values ((select tag_id from tags where tags.name='technology'),
|
|
(select group_id from tag_group_names
|
|
where group_name='default-hacker-news'));
|
|
insert into tags_groups (tag_id, group_id)
|
|
values ((select tag_id from tags where tags.name='hacker-news'),
|
|
(select group_id from tag_group_names
|
|
where group_name='default-hacker-news'));
|
|
insert into tags_groups (tag_id, group_id)
|
|
values ((select tag_id from tags where tags.name='opinion'),
|
|
(select group_id from tag_group_names
|
|
where group_name='default-hacker-news'));
|
|
insert into tags_groups (tag_id, group_id)
|
|
values ((select tag_id from tags where tags.name='world'),
|
|
(select group_id from tag_group_names
|
|
where group_name='default-hacker-news'));
|
|
insert into tags_groups (tag_id, group_id)
|
|
values ((select tag_id from tags where tags.name='nation'),
|
|
(select group_id from tag_group_names
|
|
where group_name='default-hacker-news'));
|
|
insert into tags_groups (tag_id, group_id)
|
|
values ((select tag_id from tags where tags.name='business'),
|
|
(select group_id from tag_group_names
|
|
where group_name='default-hacker-news'));
|
|
|
|
insert into tags_groups (tag_id, group_id)
|
|
values ((select tag_id from tags where tags.name='president-2016'),
|
|
(select group_id from tag_group_names
|
|
where group_name='presidential-election-2016'));
|
|
insert into tags_groups (tag_id, group_id)
|
|
values ((select tag_id from tags where tags.name='scott-walker'),
|
|
(select group_id from tag_group_names
|
|
where group_name='presidential-election-2016'));
|
|
insert into tags_groups (tag_id, group_id)
|
|
values ((select tag_id from tags where tags.name='mike-huckabee'),
|
|
(select group_id from tag_group_names
|
|
where group_name='presidential-election-2016'));
|
|
insert into tags_groups (tag_id, group_id)
|
|
values ((select tag_id from tags where tags.name='hillary-clinton'),
|
|
(select group_id from tag_group_names
|
|
where group_name='presidential-election-2016'));
|
|
insert into tags_groups (tag_id, group_id)
|
|
values ((select tag_id from tags where tags.name='ben-carson'),
|
|
(select group_id from tag_group_names
|
|
where group_name='presidential-election-2016'));
|
|
insert into tags_groups (tag_id, group_id)
|
|
values ((select tag_id from tags where tags.name='jim-webb'),
|
|
(select group_id from tag_group_names
|
|
where group_name='presidential-election-2016'));
|
|
insert into tags_groups (tag_id, group_id)
|
|
values ((select tag_id from tags where tags.name='marco-rubio'),
|
|
(select group_id from tag_group_names
|
|
where group_name='presidential-election-2016'));
|
|
insert into tags_groups (tag_id, group_id)
|
|
values ((select tag_id from tags where tags.name='rick-perry'),
|
|
(select group_id from tag_group_names
|
|
where group_name='presidential-election-2016'));
|
|
insert into tags_groups (tag_id, group_id)
|
|
values ((select tag_id from tags where tags.name='bernie-sanders'),
|
|
(select group_id from tag_group_names
|
|
where group_name='presidential-election-2016'));
|
|
insert into tags_groups (tag_id, group_id)
|
|
values ((select tag_id from tags where tags.name='ted-cruz'),
|
|
(select group_id from tag_group_names
|
|
where group_name='presidential-election-2016'));
|
|
insert into tags_groups (tag_id, group_id)
|
|
values ((select tag_id from tags where tags.name='carly-fiorina'),
|
|
(select group_id from tag_group_names
|
|
where group_name='presidential-election-2016'));
|
|
insert into tags_groups (tag_id, group_id)
|
|
values ((select tag_id from tags where tags.name='rand-paul'),
|
|
(select group_id from tag_group_names
|
|
where group_name='presidential-election-2016'));
|
|
insert into tags_groups (tag_id, group_id)
|
|
values ((select tag_id from tags where tags.name='donald-trump'),
|
|
(select group_id from tag_group_names
|
|
where group_name='presidential-election-2016'));
|
|
insert into tags_groups (tag_id, group_id)
|
|
values ((select tag_id from tags where tags.name='jeb-bush'),
|
|
(select group_id from tag_group_names
|
|
where group_name='presidential-election-2016'));
|
|
insert into tags_groups (tag_id, group_id)
|
|
values ((select tag_id from tags where tags.name='bobby-jindal'),
|
|
(select group_id from tag_group_names
|
|
where group_name='presidential-election-2016'));
|
|
|
|
insert into tags_groups (tag_id, group_id)
|
|
values ((select tag_id from tags where tags.name='marriage-equality'),
|
|
(select group_id from tag_group_names where group_name='current-events'));
|
|
insert into tags_groups (tag_id, group_id)
|
|
values ((select tag_id from tags where tags.name='greek-crisis'),
|
|
(select group_id from tag_group_names where group_name='current-events'));
|
|
insert into tags_groups (tag_id, group_id)
|
|
values ((select tag_id from tags where tags.name='isis'),
|
|
(select group_id from tag_group_names where group_name='current-events'));
|
|
insert into tags_groups (tag_id, group_id)
|
|
values ((select tag_id from tags where tags.name='climate-change'),
|
|
(select group_id from tag_group_names where group_name='current-events'));
|
|
insert into tags_groups (tag_id, group_id)
|
|
values ((select tag_id from tags where tags.name='tpp'),
|
|
(select group_id from tag_group_names where group_name='current-events'));
|
|
insert into tags_groups (tag_id, group_id)
|
|
values ((select tag_id from tags where tags.name='lafayette-shooting'),
|
|
(select group_id from tag_group_names where group_name='current-events'));
|
|
insert into tags_groups (tag_id, group_id)
|
|
values ((select tag_id from tags where tags.name='iran-accord'),
|
|
(select group_id from tag_group_names where group_name='current-events'));
|
|
|
|
insert into tags_groups (tag_id, group_id)
|
|
values ((select tag_id from tags where tags.name='programming'),
|
|
(select group_id from tag_group_names where group_name='programming'));
|
|
insert into tags_groups (tag_id, group_id)
|
|
values ((select tag_id from tags where tags.name='hacker-news'),
|
|
(select group_id from tag_group_names where group_name='programming'));
|
|
insert into tags_groups (tag_id, group_id)
|
|
values ((select tag_id from tags where tags.name='lisp-scheme'),
|
|
(select group_id from tag_group_names where group_name='programming'));
|
|
insert into tags_groups (tag_id, group_id)
|
|
values ((select tag_id from tags where tags.name='lisp'),
|
|
(select group_id from tag_group_names where group_name='programming'));
|
|
insert into tags_groups (tag_id, group_id)
|
|
values ((select tag_id from tags where tags.name='haskell'),
|
|
(select group_id from tag_group_names where group_name='programming'));
|
|
|
|
create table article_tags (
|
|
id bigserial primary key,
|
|
article_id integer not null references articles on delete cascade,
|
|
tag_id integer not null references tags on delete cascade);
|
|
create index article_tags_article_id_idx on article_tags (article_id);
|
|
create index article_tags_tag_id_idx on article_tags (tag_id);
|
|
create unique index article_tags_article_id_tag_id_idx
|
|
on article_tags(article_id, tag_id);
|
|
|
|
create table user_account(
|
|
user_id bigserial primary key,
|
|
password varchar(255) not null,
|
|
salt varchar(100) not null,
|
|
email varchar(255) not null unique);
|
|
|
|
create table user_settings(
|
|
id bigserial primary key,
|
|
user_id int unique not null references user_account on delete cascade,
|
|
unviewed_only boolean not null default FALSE,
|
|
tracking boolean not null default TRUE);
|
|
create unique index user_settings_user_id_idx on user_account (user_id);
|
|
|
|
create table breaking_news_email(
|
|
id bigserial primary key,
|
|
email varchar(255) unique not null);
|
|
create index breaking_news_email_email_idx on breaking_news_email (email);
|
|
|
|
create table get_invited(
|
|
id bigserial primary key,
|
|
email varchar(255) unique not null);
|
|
create unique index get_invited_email_idx on get_invited (email);
|
|
|
|
create table session(
|
|
id bigserial primary key,
|
|
session_id varchar(40) unique not null,
|
|
expiration integer not null,
|
|
user_id int not null references user_account on delete cascade);
|
|
create index session_session_id_idx on session (session_id);
|
|
create index session_user_id_idx on session (user_id);
|
|
|
|
create table article_viewed_by_user(
|
|
id bigserial primary key,
|
|
user_id integer not null references user_account on delete cascade,
|
|
article_id integer not null references articles on delete cascade,
|
|
viewed boolean not null default TRUE);
|
|
create index article_viewed_by_user_article_id_idx
|
|
on article_viewed_by_user (article_id);
|
|
create index article_viewed_by_user_user_id_idx
|
|
on article_viewed_by_user (user_id);
|
|
create unique index article_viewed_by_user_by_article_id_idx
|
|
on article_viewed_by_user (article_id, user_id);
|
|
|
|
create table upvoted_by_user(
|
|
id bigserial primary key,
|
|
user_id integer not null references user_account on delete cascade,
|
|
article_id integer not null references articles on delete cascade);
|
|
create index upvoted_by_user_article_id_idx on upvoted_by_user (article_id);
|
|
create index upvoted_by_user_user_id_idx on upvoted_by_user (user_id);
|
|
|
|
create table tag_subscriptions(
|
|
id bigserial primary key,
|
|
user_id integer not null references user_account on delete cascade,
|
|
tag_id integer not null references tags on delete cascade,
|
|
source_id integer not null references article_source on delete cascade);
|
|
create index tag_subscriptions_user_id_idx on tag_subscriptions (user_id);
|
|
create index tag_subscriptions_tag_id_idx on tag_subscriptions (tag_id);
|
|
create unique index tag_subscriptions_user_id_tag_id_source_id_idx
|
|
on tag_subscriptions(user_id, tag_id, source_id);
|
|
|
|
create table author_subscriptions(
|
|
id bigserial primary key,
|
|
user_id integer not null references user_account on delete cascade,
|
|
author_id integer not null references authors on delete cascade);
|
|
create index author_subscriptions_user_id_idx on author_subscriptions (user_id);
|
|
create index author_subscriptions_author_id_idx on author_subscriptions (author_id);
|
|
create unique index author_subscriptions_user_id_author_id_source_id_idx
|
|
on author_subscriptions(user_id, author_id, source_id);
|
|
|
|
create table user_sources(
|
|
id bigserial primary key,
|
|
user_id integer not null references user_account on delete cascade,
|
|
source_id integer not null);
|
|
create index user_sources_user_id_idx on user_sources (user_id);
|
|
create unique index user_sources_user_id_source_id_idx
|
|
on user_sources(user_id, source_id);
|
|
|
|
|
|
-- STORED PROCEDURES
|
|
create or replace function get_articles_by_popularity
|
|
(lim int, offs int, excludes int[], sources int[])
|
|
returns table(article_id bigint, title varchar, author varchar, date timestamptz,
|
|
description text, rank int, content text,
|
|
cover_image_path text, cover_image_height int,
|
|
cover_image_width int,
|
|
tags varchar[], points int, uri varchar,
|
|
source_name varchar, source_icon_path text, source_id int,
|
|
authors varchar[], author_ids int[])
|
|
as $$
|
|
begin
|
|
return query select article_view.article_id, article_view.title,
|
|
article_view.author, article_view.date, article_view.description,
|
|
article_view.rank, article_view.content, article_view.original_path,
|
|
article_view.original_height, article_view.original_width,
|
|
article_view.tags, article_view.points, article_view.uri,
|
|
article_view.source_name, article_view.source_icon_path,
|
|
article_view.source_id, article_view.authors, article_view.author_ids
|
|
from article_view
|
|
where article_view.article_id <> all(excludes) and
|
|
article_view.source_id = any(sources)
|
|
order by article_view.points desc, article_view.view_count desc
|
|
limit lim offset offs;
|
|
end;
|
|
$$ language plpgsql;
|
|
|
|
-- filtered by tags
|
|
create or replace function get_articles_by_popularity
|
|
(lim int, offs int, excludes int[], tag varchar[], sources int[])
|
|
returns table(article_id bigint, title varchar, author varchar, date timestamptz,
|
|
description text, rank int, content text,
|
|
cover_image_path text, cover_image_height int,
|
|
cover_image_width int,
|
|
tags varchar[], points int, uri varchar,
|
|
source_name varchar, source_icon_path text, source_id int,
|
|
authors varchar[], author_ids int[]) as $$
|
|
begin
|
|
return query select article_view.article_id, article_view.title,
|
|
article_view.author, article_view.date, article_view.description,
|
|
article_view.rank, article_view.content, article_view.original_path,
|
|
article_view.original_height, article_view.original_width,
|
|
article_view.tags, article_view.points, article_view.uri,
|
|
article_view.source_name, article_view.source_icon_path,
|
|
article_view.source_id, article_view.authors, article_view.author_ids
|
|
from article_view
|
|
where article_view.tags && tag::varchar[]
|
|
and article_view.article_id <> all(excludes)
|
|
and article_view.source_id = any(sources)
|
|
order by article_view.points desc, article_view.view_count desc
|
|
limit lim offset offs;
|
|
end;
|
|
$$ language plpgsql;
|
|
|
|
create or replace function get_tag_subscriptions_by_user_by_rank
|
|
(userid int, lim int)
|
|
returns table (article_id bigint, title varchar, author varchar,
|
|
date timestamptz,
|
|
description text, rank int, content text,
|
|
cover_image_path text, cover_image_height int,
|
|
cover_image_width int,
|
|
tags varchar[], points int, uri varchar,
|
|
source_name varchar, source_icon_path text, source_id int,
|
|
authors varchar[], author_ids int[]) as $$
|
|
begin
|
|
return query
|
|
select article_view.article_id, article_view.title, article_view.author,
|
|
article_view.date, article_view.description, article_view.rank,
|
|
article_view.content, article_view.original_path, article_view.original_height,
|
|
article_view.original_width, article_view.tags, article_view.points,
|
|
article_view.uri, article_view.source_name, article_view.source_icon_path,
|
|
article_view.source_id, article_view.authors, article_view.author_ids
|
|
from
|
|
(select t.article_id from
|
|
(select t.article_id, t.source_id, t.tags_ids, t.author_ids
|
|
from article_view as t
|
|
order by t.date desc limit 3000) as t
|
|
join tag_subscriptions on tag_subscriptions.source_id = t.source_id
|
|
and tag_subscriptions.tag_id = any(t.tags_ids)
|
|
where tag_subscriptions.user_id=userid
|
|
union
|
|
select t3.article_id from
|
|
(select t3.article_id, t3.author_ids
|
|
from article_view as t3
|
|
order by t3.date desc limit 3000) as t3
|
|
join author_subscriptions
|
|
on author_subscriptions.author_id = any(t3.author_ids)
|
|
where author_subscriptions.user_id=userid) as t4
|
|
join article_view on article_view.article_id = t4.article_id
|
|
order by article_view.rank, article_view.date desc limit lim;
|
|
end;
|
|
$$ language plpgsql;
|
|
|
|
create or replace function get_tag_subscriptions_by_user
|
|
(userid int, lim int)
|
|
returns table (article_id bigint, title varchar, author varchar,
|
|
date timestamptz,
|
|
description text, rank int, content text,
|
|
cover_image_path text, cover_image_height int,
|
|
cover_image_width int,
|
|
tags varchar[], points int, uri varchar,
|
|
source_name varchar, source_icon_path text, source_id int,
|
|
authors varchar[], author_ids int[]) as $$
|
|
begin
|
|
return query
|
|
select article_view.article_id, article_view.title, article_view.author,
|
|
article_view.date, article_view.description, article_view.rank,
|
|
article_view.content, article_view.original_path, article_view.original_height,
|
|
article_view.original_width, article_view.tags, article_view.points,
|
|
article_view.uri, article_view.source_name, article_view.source_icon_path,
|
|
article_view.source_id, article_view.authors, article_view.author_ids
|
|
from
|
|
(select t.article_id from
|
|
(select t.article_id, t.source_id, t.tags_ids, t.author_ids
|
|
from article_view as t
|
|
order by t.date desc limit 3000) as t
|
|
join tag_subscriptions on tag_subscriptions.source_id = t.source_id
|
|
and tag_subscriptions.tag_id = any(t.tags_ids)
|
|
where tag_subscriptions.user_id=userid
|
|
union
|
|
select t3.article_id from
|
|
(select t3.article_id, t3.author_ids
|
|
from article_view as t3
|
|
order by t3.date desc limit 3000) as t3
|
|
join author_subscriptions
|
|
on author_subscriptions.author_id = any(t3.author_ids)
|
|
where author_subscriptions.user_id=userid) as t4
|
|
join article_view on article_view.article_id = t4.article_id
|
|
order by article_view.date desc limit lim;
|
|
end;
|
|
$$ language plpgsql;
|
|
|
|
create or replace function get_tag_subscriptions_by_user_by_rank
|
|
(userid int, lim int, tag varchar)
|
|
returns table (article_id bigint, title varchar, author varchar,
|
|
date timestamptz,
|
|
description text, rank int, content text,
|
|
cover_image_path text, cover_image_height int,
|
|
cover_image_width int,
|
|
tags varchar[], points int, uri varchar,
|
|
source_name varchar, source_icon_path text, source_id int,
|
|
authors varchar[], author_ids int[]) as $$
|
|
begin
|
|
return query
|
|
select article_view.article_id, article_view.title, article_view.author,
|
|
article_view.date, article_view.description, article_view.rank,
|
|
article_view.content, article_view.original_path, article_view.original_height,
|
|
article_view.original_width, article_view.tags, article_view.points,
|
|
article_view.uri, article_view.source_name, article_view.source_icon_path,
|
|
article_view.source_id, article_view.authors, article_view.author_ids
|
|
from
|
|
(select distinct t.article_id from
|
|
(select t.article_id, t.source_id, t.tags from article_view as t
|
|
order by t.date desc limit 1000) as t
|
|
join (select tag_subscriptions.source_id from tag_subscriptions
|
|
join tags on tags.tag_id = tag_subscriptions.tag_id
|
|
where user_id=userid and tags.name = tag)
|
|
as tids
|
|
on tids.source_id = t.source_id
|
|
where tag = any(t.tags)) as t2
|
|
join article_view on article_view.article_id = t2.article_id
|
|
order by article_view.rank, article_view.date desc limit lim;
|
|
end;
|
|
$$ language plpgsql;
|
|
|
|
create or replace function get_tag_subscriptions_by_user
|
|
(userid int, lim int, tag varchar)
|
|
returns table (article_id bigint, title varchar, author varchar,
|
|
date timestamptz,
|
|
description text, rank int, content text,
|
|
cover_image_path text, cover_image_height int,
|
|
cover_image_width int,
|
|
tags varchar[], points int, uri varchar,
|
|
source_name varchar, source_icon_path text, source_id int,
|
|
authors varchar[], author_ids int[]) as $$
|
|
begin
|
|
return query
|
|
select article_view.article_id, article_view.title, article_view.author,
|
|
article_view.date, article_view.description, article_view.rank,
|
|
article_view.content, article_view.original_path, article_view.original_height,
|
|
article_view.original_width, article_view.tags, article_view.points,
|
|
article_view.uri, article_view.source_name, article_view.source_icon_path,
|
|
article_view.source_id, article_view.authors, article_view.author_ids
|
|
from
|
|
(select distinct t.article_id from
|
|
(select t.article_id, t.source_id, t.tags from article_view as t
|
|
order by t.date desc limit 1000) as t
|
|
join (select tag_subscriptions.source_id from tag_subscriptions
|
|
join tags on tags.tag_id = tag_subscriptions.tag_id
|
|
where user_id=userid and tags.name = tag)
|
|
as tids
|
|
on tids.source_id = t.source_id
|
|
where tag = any(t.tags)) as t2
|
|
join article_view on article_view.article_id = t2.article_id
|
|
order by article_view.date desc limit lim;
|
|
end;
|
|
$$ language plpgsql;
|
|
|
|
create or replace function get_articles_by_tags
|
|
(tag varchar[], lim int, excludes int[], sources int[])
|
|
returns table(article_id bigint, title varchar, author varchar, date timestamptz,
|
|
description text, rank int, content text,
|
|
cover_image_path text, cover_image_height int,
|
|
cover_image_width int,
|
|
tags varchar[], points int, uri varchar,
|
|
source_name varchar, source_icon_path text, source_id int,
|
|
authors varchar[], author_ids int[]) as $$
|
|
begin
|
|
return query select
|
|
article_view.article_id, article_view.title,
|
|
article_view.author, article_view.date, article_view.description,
|
|
article_view.rank, article_view.content, article_view.original_path,
|
|
article_view.original_height, article_view.original_width,
|
|
article_view.tags, article_view.points, article_view.uri,
|
|
article_view.source_name, article_view.source_icon_path,
|
|
article_view.source_id, article_view.authors, article_view.author_ids
|
|
from article_view
|
|
where article_view.tags && tag::varchar[]
|
|
and article_view.article_id <> all(excludes)
|
|
and article_view.source_id = any(sources)
|
|
order by article_view.date desc limit lim;
|
|
end;
|
|
$$ language plpgsql;
|
|
|
|
create or replace function get_articles_by_tags_and_rank
|
|
(tag varchar[], lim int, excludes int[], sources int[])
|
|
returns table(article_id bigint, title varchar, author varchar, date timestamptz,
|
|
description text, rank int, content text,
|
|
cover_image_path text, cover_image_height int,
|
|
cover_image_width int,
|
|
tags varchar[], points int, uri varchar,
|
|
source_name varchar, source_icon_path text, source_id int,
|
|
authors varchar[], author_ids int[]) as $$
|
|
begin
|
|
return query select
|
|
article_view.article_id, article_view.title,
|
|
article_view.author, article_view.date, article_view.description,
|
|
article_view.rank, article_view.content, article_view.original_path,
|
|
article_view.original_height, article_view.original_width,
|
|
article_view.tags, article_view.points, article_view.uri,
|
|
article_view.source_name, article_view.source_icon_path,
|
|
article_view.source_id, article_view.authors, article_view.author_ids
|
|
from article_view
|
|
where article_view.tags && tag::varchar[]
|
|
and article_view.source_id = any(sources)
|
|
and article_view.article_id <> all(excludes)
|
|
order by rank, article_view.date desc limit lim;
|
|
end;
|
|
$$ language plpgsql;
|
|
|
|
create or replace function get_articles_by_authors
|
|
(author_names varchar[], lim int, excludes int[])
|
|
returns table(article_id bigint, title varchar, author varchar, date timestamptz,
|
|
description text, rank int, content text,
|
|
cover_image_path text, cover_image_height int,
|
|
cover_image_width int,
|
|
tags varchar[], points int, uri varchar,
|
|
source_name varchar, source_icon_path text, source_id int,
|
|
authors varchar[], author_ids int[]) as $$
|
|
begin
|
|
return query select article_view.article_id, article_view.title,
|
|
article_view.author, article_view.date, article_view.description,
|
|
article_view.rank, article_view.content, article_view.original_path,
|
|
article_view.original_height, article_view.original_width,
|
|
article_view.tags, article_view.points, article_view.uri,
|
|
article_view.source_name, article_view.source_icon_path,
|
|
article_view.source_id, article_view.authors, article_view.author_ids
|
|
from article_view
|
|
where article_view.authors && author_names
|
|
and article_view.article_id <> all(excludes)
|
|
order by article_view.date desc limit lim;
|
|
end;
|
|
$$ language plpgsql;
|
|
|
|
-- VIEWS
|
|
create materialized view tags_and_sources as
|
|
select articles.source_id, article_tags.tag_id,
|
|
article_source.name as source_name, tags.name as tag_name,
|
|
tags.rank as tag_rank, article_source.icon_path as source_icon_path,
|
|
array_agg(distinct tags_groups.group_id) tag_groups_id,
|
|
array_agg(distinct tag_group_names.group_name) tag_groups_name
|
|
from article_tags
|
|
join articles on articles.article_id = article_tags.article_id
|
|
join article_source on article_source.source_id = articles.source_id
|
|
join tags on tags.tag_id = article_tags.tag_id
|
|
join tags_groups on tags_groups.tag_id = article_tags.tag_id
|
|
join tag_group_names on tag_group_names.group_id = tags_groups.group_id
|
|
group by articles.source_id, article_tags.tag_id, article_source.name,
|
|
tags.name, tags.rank, article_source.icon_path;
|
|
create index tags_and_sources_tag_id_idx on tags_and_sources(tag_id);
|
|
create index tags_and_sources_source_id_idx on tags_and_sources(source_id);
|
|
create unique index tags_and_sources_source_id_tag_id_idx
|
|
on tags_and_sources(source_id, tag_id);
|
|
|
|
create materialized view article_view as
|
|
select t.article_id, t.title, t.author, t.date, t.description,
|
|
t.rank, t.content, article_images.original_path,
|
|
article_images.original_width, article_images.original_height,
|
|
array_agg(distinct tags.name) tags,
|
|
array_agg(distinct tags.tag_id) tags_ids,
|
|
array_agg(distinct authors.name) authors,
|
|
array_agg(distinct article_author.author_id) author_ids,
|
|
t.points, t.uri, t.view_count, t.source_id, t.name as source_name,
|
|
t.icon_path as source_icon_path
|
|
from (select
|
|
articles.article_id, articles.title, articles.author,
|
|
articles.date, articles.description, articles.rank,
|
|
articles.content, articles.uri, articles.source_id,
|
|
article_source.name, article_source.icon_path,
|
|
article_stats.points, article_stats.view_count from articles
|
|
join article_tags on article_tags.article_id = articles.article_id
|
|
join tags on tags.tag_id = article_tags.tag_id
|
|
join article_source on article_source.source_id = articles.source_id
|
|
join article_stats on article_stats.article_id = articles.article_id
|
|
order by articles.date desc
|
|
limit 50000
|
|
) as t
|
|
left outer join article_images on t.article_id = article_images.article_id
|
|
and article_images.cover=true
|
|
join article_tags on t.article_id = article_tags.article_id
|
|
join tags on article_tags.tag_id = tags.tag_id
|
|
left outer join article_author on t.article_id = article_author.article_id
|
|
left outer join authors on authors.author_id = article_author.author_id
|
|
group by t.article_id, t.title, t.author, t.date, t.description, t.rank,
|
|
t.content, article_images.original_path, article_images.original_height,
|
|
article_images.original_width,
|
|
t.points, t.uri, t.view_count,
|
|
t.source_id, t.name, t.icon_path;
|
|
|
|
create unique index article_id_article_view_idx on article_view(article_id);
|
|
create index date_article_view_idx on article_view(date desc);
|
|
create index rank_article_view_idx on article_view(rank);
|
|
create index points_article_view_idx on article_view(points);
|
|
create index view_count_article_view_idx on article_view(view_count);
|
|
create index source_id_article_view_idx on article_view(source_id);
|
|
create index authors_article_view_idx on article_view using GIN(authors);
|
|
create index tags_article_view_idx on article_view using GIN(tags);
|
|
create index tags_ids_article_view_idx on article_view(tags_ids);
|