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;