2. Demo Files#
This page shows the SQL files used for the demonstraion.
2.1. RV1#
2.1.1. Upgrade#
1-- create schema
2CREATE SCHEMA IF NOT EXISTS fundamentals;
3-- create table
4CREATE TABLE IF NOT EXISTS fundamentals.source
5 (
6 source_id INTEGER PRIMARY KEY,
7 source VARCHAR(50) NOT NULL UNIQUE,
8 created_at TIMESTAMP NOT NULL DEFAULT (now() at time zone 'utc'),
9 CONSTRAINT source_ux UNIQUE (source)
10 );
11-- Initialise with pre-set source
12INSERT INTO fundamentals.source (source_id, source)
13VALUES (1, 'yahoo'), (2, 'investing_dot_com') on conflict do nothing;
14-- create table
15CREATE TABLE IF NOT EXISTS fundamentals.price
16 (
17 ticker VARCHAR(10) NOT NULL,
18 date DATE NOT NULL,
19 open NUMERIC(50,10) NOT NULL CHECK (open >=0),
20 high NUMERIC(50,10) NOT NULL CHECK (high >=0),
21 low NUMERIC(50,10) NOT NULL CHECK (low >=0),
22 close NUMERIC(50,10) NOT NULL CHECK (close >=0),
23 adj_close NUMERIC(50,10) NOT NULL CHECK (adj_close >=0),
24 volume NUMERIC(50,10) NOT NULL CHECK (volume >=0),
25 source_id INTEGER,
26 created_at TIMESTAMP NOT NULL DEFAULT (now() at time zone 'utc'),
27 updated_at TIMESTAMP NOT NULL DEFAULT (now() at time zone 'utc'),
28 PRIMARY KEY (ticker, date),
29 CONSTRAINT fk_source FOREIGN KEY(source_id) REFERENCES fundamentals.source(source_id) ON DELETE CASCADE
30 );
2.1.2. Downgrade#
1drop schema fundamentals cascade;
2.2. RV2#
2.2.1. Upgrade#
1create schema if not exists datetime;
2create table if not exists datetime.special_date(
3 date date primary key,
4 is_hk_public_holiday boolean default False,
5 created_at TIMESTAMP NOT NULL DEFAULT (now() at time zone 'utc'),
6 updated_at TIMESTAMP NOT NULL DEFAULT (now() at time zone 'utc')
7);
2.2.2. Downgrade#
1drop schema datetime cascade;
2.3. RV3#
2.3.1. Upgrade#
1create or replace view datetime.vw_trading_days_since_2021 as with weekdays (weekday) as (
2select
3 dt
4-- Set timezone of now() to UTC
5from
6 generate_series('2021-01-01':: date, now() at time zone 'utc', '1 day':: interval) dt
7where
8 extract(dow
9from
10 dt) not in (6, 0)
11 ),
12 hk_holidays (hk_holiday) as (
13select
14 date
15from
16 datetime.special_date sd
17where
18 is_hk_public_holiday = true
19 ),
20trading_days (trading_day) as (
21select
22 weekday
23from
24 weekdays
25except
26select
27 hk_holiday
28from
29 hk_holidays)
30select
31 trading_day
32from
33 trading_days
34order by
35 trading_days ;
2.3.2. Downgrade#
1drop view if exists datetime.vw_trading_days_since_2021;