337 lines
11 KiB
Plaintext
337 lines
11 KiB
Plaintext
|
/* =============================================================================
|
||
|
|
* PostgreSQL Schema - designs for a new HPR database
|
||
|
|
*
|
||
|
|
* File: hpr_schema.pgsql
|
||
|
|
* Created: 2017-03-15
|
||
|
|
* Updated: 2017-10-16
|
||
|
|
* =============================================================================
|
||
|
|
*/
|
||
|
|
|
||
|
|
/* ------------------------------------------------------------------------------
|
||
|
|
* Drop everything to start with. The order is important because of the
|
||
|
|
* relations between tables. Also, some items are dependent and go with the
|
||
|
|
* tables.
|
||
|
|
* ------------------------------------------------------------------------------
|
||
|
|
*/
|
||
|
|
DROP TABLE IF EXISTS comments CASCADE;
|
||
|
|
DROP TABLE IF EXISTS episodes CASCADE;
|
||
|
|
DROP TABLE IF EXISTS episodes_hosts_xref CASCADE;
|
||
|
|
DROP TABLE IF EXISTS episodes_series_xref CASCADE;
|
||
|
|
DROP TABLE IF EXISTS episodes_tags_xref CASCADE;
|
||
|
|
DROP TABLE IF EXISTS hosts CASCADE;
|
||
|
|
DROP TABLE IF EXISTS licenses CASCADE;
|
||
|
|
DROP TABLE IF EXISTS series CASCADE;
|
||
|
|
DROP TABLE IF EXISTS tags CASCADE;
|
||
|
|
|
||
|
|
-- DROP INDEX IF EXISTS episode_release_date_key;
|
||
|
|
|
||
|
|
DROP SEQUENCE IF EXISTS comment_seq;
|
||
|
|
DROP SEQUENCE IF EXISTS episode_seq;
|
||
|
|
DROP SEQUENCE IF EXISTS host_seq;
|
||
|
|
DROP SEQUENCE IF EXISTS license_seq;
|
||
|
|
DROP SEQUENCE IF EXISTS series_seq;
|
||
|
|
DROP SEQUENCE IF EXISTS tag_seq;
|
||
|
|
|
||
|
|
-- DROP VIEW IF EXISTS eht_view;
|
||
|
|
|
||
|
|
/* ------------------------------------------------------------------------------
|
||
|
|
* Table 'licenses' - licenses relating to episodes (needed because 'hosts'
|
||
|
|
* references it)
|
||
|
|
* ------------------------------------------------------------------------------
|
||
|
|
*/
|
||
|
|
CREATE SEQUENCE license_seq;
|
||
|
|
|
||
|
|
ALTER TABLE license_seq
|
||
|
|
OWNER TO hpradmin;
|
||
|
|
|
||
|
|
CREATE TABLE licenses (
|
||
|
|
id integer default nextval('license_seq') PRIMARY KEY,
|
||
|
|
short_name varchar(11) NOT NULL UNIQUE,
|
||
|
|
long_name varchar(40) NOT NULL,
|
||
|
|
url varchar(80) NOT NULL
|
||
|
|
);
|
||
|
|
|
||
|
|
ALTER TABLE licenses
|
||
|
|
OWNER TO hpradmin;
|
||
|
|
|
||
|
|
/*
|
||
|
|
* Load the table since it's quite short. Don't set the id to ensure the
|
||
|
|
* sequence is updated properly.
|
||
|
|
*/
|
||
|
|
INSERT INTO licenses (short_name, long_name, url) VALUES
|
||
|
|
('CC-0', 'Public Domain Dedication', 'http://creativecommons.org/publicdomain/zero/1.0/'),
|
||
|
|
('CC-BY', 'Attribution', 'http://creativecommons.org/licenses/by/4.0'),
|
||
|
|
('CC-BY-SA', 'Attribution-ShareAlike', 'http://creativecommons.org/licenses/by-sa/3.0'),
|
||
|
|
('CC-BY-ND', 'Attribution-NoDerivs', 'http://creativecommons.org/licenses/by-nd/4.0'),
|
||
|
|
('CC-BY-NC', 'Attribution-NonCommercial', 'http://creativecommons.org/licenses/by-nc/4.0'),
|
||
|
|
('CC-BY-NC-SA', 'Attribution-NonCommercial-ShareAlike', 'http://creativecommons.org/licenses/by-nc-sa/4.0'),
|
||
|
|
('CC-BY-NC-ND', 'Attribution-NonCommercial-NoDerivs', 'http://creativecommons.org/licenses/by-nc-nd/4.0');
|
||
|
|
|
||
|
|
/* ------------------------------------------------------------------------------
|
||
|
|
* Table 'episodes' - HPR shows
|
||
|
|
* ------------------------------------------------------------------------------ */
|
||
|
|
CREATE SEQUENCE episode_seq;
|
||
|
|
|
||
|
|
ALTER TABLE episode_seq
|
||
|
|
OWNER TO hpradmin;
|
||
|
|
|
||
|
|
CREATE TABLE episodes (
|
||
|
|
id integer default nextval('episode_seq') PRIMARY KEY,
|
||
|
|
release_date date NOT NULL,
|
||
|
|
title varchar(100) NOT NULL,
|
||
|
|
summary varchar(100),
|
||
|
|
notes text NOT NULL,
|
||
|
|
explicit smallint NOT NULL DEFAULT '1',
|
||
|
|
license varchar(11) NOT NULL DEFAULT 'CC-BY-SA'
|
||
|
|
REFERENCES licenses (short_name),
|
||
|
|
duration integer NOT NULL DEFAULT 0,
|
||
|
|
downloads integer NOT NULL DEFAULT 0
|
||
|
|
);
|
||
|
|
|
||
|
|
ALTER TABLE episodes
|
||
|
|
OWNER TO hpradmin;
|
||
|
|
|
||
|
|
CREATE INDEX episode_release_date_key
|
||
|
|
ON episodes
|
||
|
|
USING btree
|
||
|
|
(release_date);
|
||
|
|
|
||
|
|
/* ------------------------------------------------------------------------------
|
||
|
|
* Table 'hosts' - hosts contributing shows
|
||
|
|
* ------------------------------------------------------------------------------ */
|
||
|
|
CREATE SEQUENCE host_seq;
|
||
|
|
|
||
|
|
ALTER TABLE host_seq
|
||
|
|
OWNER TO hpradmin;
|
||
|
|
|
||
|
|
CREATE TABLE hosts (
|
||
|
|
id integer default nextval('host_seq') PRIMARY KEY,
|
||
|
|
host varchar(1024) UNIQUE NOT NULL,
|
||
|
|
-- email varchar(1024) CHECK (email <> ''),
|
||
|
|
email varchar(1024) UNIQUE NOT NULL,
|
||
|
|
profile text,
|
||
|
|
license varchar(11) NOT NULL DEFAULT 'CC-BY-SA'
|
||
|
|
REFERENCES licenses (short_name),
|
||
|
|
local_image smallint NOT NULL DEFAULT '0',
|
||
|
|
gpg text,
|
||
|
|
valid smallint NOT NULL DEFAULT '1',
|
||
|
|
date_added date
|
||
|
|
);
|
||
|
|
|
||
|
|
ALTER TABLE hosts
|
||
|
|
OWNER TO hpradmin;
|
||
|
|
|
||
|
|
/* ------------------------------------------------------------------------------
|
||
|
|
* Table 'episodes_hosts_xref' - joining table between 'episodes' and 'hosts'
|
||
|
|
* ------------------------------------------------------------------------------ */
|
||
|
|
CREATE TABLE episodes_hosts_xref (
|
||
|
|
episodes_id integer REFERENCES episodes(id)
|
||
|
|
ON DELETE RESTRICT,
|
||
|
|
hosts_id integer REFERENCES hosts(id)
|
||
|
|
ON DELETE CASCADE,
|
||
|
|
PRIMARY KEY (episodes_id, hosts_id)
|
||
|
|
);
|
||
|
|
|
||
|
|
ALTER TABLE episodes_hosts_xref
|
||
|
|
OWNER TO hpradmin;
|
||
|
|
|
||
|
|
/* ------------------------------------------------------------------------------
|
||
|
|
* Table 'tags' - tags relating to episodes
|
||
|
|
* ------------------------------------------------------------------------------ */
|
||
|
|
CREATE SEQUENCE tag_seq;
|
||
|
|
|
||
|
|
ALTER TABLE tag_seq
|
||
|
|
OWNER TO hpradmin;
|
||
|
|
|
||
|
|
CREATE TABLE tags (
|
||
|
|
id integer default nextval('tag_seq') PRIMARY KEY,
|
||
|
|
tag varchar(1024) NOT NULL
|
||
|
|
);
|
||
|
|
|
||
|
|
ALTER TABLE tags
|
||
|
|
OWNER TO hpradmin;
|
||
|
|
|
||
|
|
/* ------------------------------------------------------------------------------
|
||
|
|
* Table 'episodes_tags_xref' - joining table between 'episodes' and 'tags'
|
||
|
|
* ------------------------------------------------------------------------------ */
|
||
|
|
CREATE TABLE episodes_tags_xref (
|
||
|
|
episodes_id integer REFERENCES episodes(id)
|
||
|
|
ON DELETE RESTRICT,
|
||
|
|
tags_id integer REFERENCES tags(id)
|
||
|
|
ON DELETE CASCADE,
|
||
|
|
PRIMARY KEY (episodes_id, tags_id)
|
||
|
|
);
|
||
|
|
|
||
|
|
ALTER TABLE episodes_tags_xref
|
||
|
|
OWNER TO hpradmin;
|
||
|
|
|
||
|
|
/* ------------------------------------------------------------------------------
|
||
|
|
* Table 'series' - series grouping for episodes
|
||
|
|
* ------------------------------------------------------------------------------ */
|
||
|
|
CREATE SEQUENCE series_seq;
|
||
|
|
|
||
|
|
ALTER TABLE series_seq
|
||
|
|
OWNER TO hpradmin;
|
||
|
|
|
||
|
|
CREATE TABLE series (
|
||
|
|
id integer default nextval('series_seq') PRIMARY KEY,
|
||
|
|
name varchar(100) NOT NULL,
|
||
|
|
description text NOT NULL,
|
||
|
|
private smallint NOT NULL DEFAULT '0',
|
||
|
|
image text,
|
||
|
|
valid smallint NOT NULL DEFAULT '1'
|
||
|
|
);
|
||
|
|
|
||
|
|
ALTER TABLE series
|
||
|
|
OWNER TO hpradmin;
|
||
|
|
|
||
|
|
/* ------------------------------------------------------------------------------
|
||
|
|
* Table 'episodes_series_xref' - joining table between 'episodes' and 'series'
|
||
|
|
* ------------------------------------------------------------------------------ */
|
||
|
|
CREATE TABLE episodes_series_xref (
|
||
|
|
episodes_id integer REFERENCES episodes(id)
|
||
|
|
ON DELETE RESTRICT,
|
||
|
|
series_id integer REFERENCES series(id)
|
||
|
|
ON DELETE CASCADE,
|
||
|
|
PRIMARY KEY (episodes_id, series_id)
|
||
|
|
);
|
||
|
|
|
||
|
|
ALTER TABLE episodes_series_xref
|
||
|
|
OWNER TO hpradmin;
|
||
|
|
|
||
|
|
/* ------------------------------------------------------------------------------
|
||
|
|
* Table 'comments' - comments relating to episodes
|
||
|
|
* ------------------------------------------------------------------------------ */
|
||
|
|
CREATE SEQUENCE comment_seq;
|
||
|
|
|
||
|
|
ALTER TABLE comment_seq
|
||
|
|
OWNER TO hpradmin;
|
||
|
|
|
||
|
|
CREATE TABLE comments (
|
||
|
|
id integer default nextval('comment_seq') PRIMARY KEY,
|
||
|
|
eps_id integer REFERENCES episodes(id)
|
||
|
|
ON DELETE RESTRICT,
|
||
|
|
comment_timestamp timestamp without time zone NOT NULL,
|
||
|
|
comment_author_name varchar(1024),
|
||
|
|
comment_title varchar(1024),
|
||
|
|
comment_text text,
|
||
|
|
last_changed timestamp without time zone NOT NULL
|
||
|
|
DEFAULT timezone('UTC'::text, now())
|
||
|
|
);
|
||
|
|
|
||
|
|
ALTER TABLE comments
|
||
|
|
OWNER TO hpradmin;
|
||
|
|
|
||
|
|
/* ------------------------------------------------------------------------------
|
||
|
|
* Tables from "Today with a Techie" for further processing
|
||
|
|
* ------------------------------------------------------------------------------ */
|
||
|
|
CREATE SEQUENCE twat_hosts_seq;
|
||
|
|
|
||
|
|
ALTER TABLE twat_hosts_seq
|
||
|
|
OWNER TO hpradmin;
|
||
|
|
|
||
|
|
CREATE TABLE twat_hosts (
|
||
|
|
id integer default nextval('twat_hosts_seq') PRIMARY KEY,
|
||
|
|
host varchar(1024) NOT NULL,
|
||
|
|
email varchar(1024),
|
||
|
|
website varchar(1024),
|
||
|
|
repeat integer NOT NULL
|
||
|
|
);
|
||
|
|
|
||
|
|
ALTER TABLE twat_hosts
|
||
|
|
OWNER TO hpradmin;
|
||
|
|
|
||
|
|
|
||
|
|
CREATE SEQUENCE twat_episodes_seq;
|
||
|
|
|
||
|
|
ALTER TABLE twat_episodes_seq
|
||
|
|
OWNER TO hpradmin;
|
||
|
|
|
||
|
|
CREATE TABLE twat_episodes (
|
||
|
|
ep_num integer default nextval('twat_episodes_seq') PRIMARY KEY,
|
||
|
|
date integer NOT NULL,
|
||
|
|
host varchar(1024) NOT NULL,
|
||
|
|
topic varchar(1024) NOT NULL,
|
||
|
|
writeup text,
|
||
|
|
url varchar(1024) NOT NULL
|
||
|
|
);
|
||
|
|
|
||
|
|
ALTER TABLE twat_episodes
|
||
|
|
OWNER TO hpradmin;
|
||
|
|
|
||
|
|
/* ------------------------------------------------------------------------------
|
||
|
|
* Experimental views
|
||
|
|
* ------------------------------------------------------------------------------ */
|
||
|
|
--
|
||
|
|
-- eh_view
|
||
|
|
--
|
||
|
|
CREATE OR REPLACE VIEW eh_view AS
|
||
|
|
SELECT
|
||
|
|
ep.id,
|
||
|
|
ep.release_date,
|
||
|
|
ep.title,
|
||
|
|
(SELECT string_agg(host, ', ' ORDER BY host)
|
||
|
|
FROM hosts h2,
|
||
|
|
episodes_hosts_xref eh2
|
||
|
|
WHERE eh2.hosts_id = h2.id
|
||
|
|
GROUP BY eh2.episodes_id
|
||
|
|
HAVING eh2.episodes_id = ep.id) AS hosts
|
||
|
|
FROM episodes ep
|
||
|
|
GROUP BY ep.id
|
||
|
|
ORDER BY ep.id;
|
||
|
|
|
||
|
|
ALTER TABLE eh_view
|
||
|
|
OWNER TO hpradmin;
|
||
|
|
|
||
|
|
--
|
||
|
|
-- eht_view
|
||
|
|
--
|
||
|
|
CREATE OR REPLACE VIEW eht_view AS
|
||
|
|
SELECT e.*,
|
||
|
|
h.host,
|
||
|
|
t.tag,
|
||
|
|
(SELECT string_agg(tag, ', ')
|
||
|
|
FROM tags t2,
|
||
|
|
episodes_tags_xref et2
|
||
|
|
WHERE et2.tags_id = t2.id
|
||
|
|
GROUP BY et2.episodes_id
|
||
|
|
HAVING et2.episodes_id = e.id) AS tags
|
||
|
|
FROM episodes e,
|
||
|
|
hosts h,
|
||
|
|
episodes_hosts_xref eh,
|
||
|
|
episodes_tags_xref et,
|
||
|
|
tags t
|
||
|
|
WHERE e.id = eh.episodes_id
|
||
|
|
AND h.id = eh.hosts_id
|
||
|
|
AND e.id = et.episodes_id
|
||
|
|
AND et.tags_id = t.id
|
||
|
|
GROUP BY e.id,
|
||
|
|
h.host,
|
||
|
|
t.tag
|
||
|
|
ORDER BY e.id;
|
||
|
|
|
||
|
|
-- CREATE OR REPLACE VIEW eht_view AS
|
||
|
|
-- SELECT
|
||
|
|
-- e.*,
|
||
|
|
-- h.host,
|
||
|
|
-- t.tag,
|
||
|
|
-- (SELECT string_agg(tag, ', ')
|
||
|
|
-- FROM tags t2, episodes_tags_xref et2
|
||
|
|
-- WHERE et2.tags_id = t2.id
|
||
|
|
-- GROUP BY et2.episodes_id
|
||
|
|
-- HAVING et2.episodes_id = e.id) AS tags
|
||
|
|
-- FROM episodes e, hosts h, episodes_hosts_xref eh, episodes_tags_xref et, tags t
|
||
|
|
-- WHERE e.id = eh.episodes_id
|
||
|
|
-- AND h.id = eh.hosts_id
|
||
|
|
-- AND e.id = et.episodes_id
|
||
|
|
-- AND et.tags_id = t.id
|
||
|
|
-- GROUP BY e.id,h.host,t.tag
|
||
|
|
-- ORDER BY e.id;
|
||
|
|
|
||
|
|
ALTER TABLE eht_view
|
||
|
|
OWNER TO hpradmin;
|
||
|
|
|
||
|
|
-- Footer ---------------------------------------------------------------------
|
||
|
|
|
||
|
|
-- vim: syntax=pgsql:ts=8:sw=4:ai:tw=78:et:fo=tcrqn21:nu:rnu
|