fHNYtwB3

1CREATE TYPE RUNNER_STATUS -- статус опции рынка
2  AS ENUM ('ACTIVE', 'REMOVED', 'WINNER', 'LOSER', 'HIDDEN', 'UNKNOWN');
3
4CREATE TYPE SIDE  -- тип ставки
5  AS ENUM (
6    'B',  -- ставка ЗА
7    'L'   -- ставка ПРОТИВ
8);
9
10CREATE TABLE IF NOT EXISTS teams ( -- команды
11  team_id SERIAL PRIMARY KEY,     -- идентификатор команды
12  team_name TEXT UNIQUE NOT NULL  -- имя команды
13);
14
15CREATE TABLE IF NOT EXISTS competitions ( -- чемпионаты
16  competition_id INT UNIQUE NOT NULL,   -- идентификатор чемпионата
17  competition_name TEXT NOT NULL,       -- имя чемпионата
18  CONSTRAINT positive_competition_id CHECK (competition_id > 0),
19  PRIMARY KEY (competition_id)
20);
21
22CREATE TABLE IF NOT EXISTS events ( -- неизменяемые характеристики футбольного матча
23  event_id INT NOT NULL CONSTRAINT positive_event_id CHECK (event_id > 0),     -- идентификатор матча
24  open_date DATE NOT NULL,  -- дата матча
25  competition_id INT NOT NULL,  -- чемпионат
26  home_id INT NOT NULL,     -- команда дома
27  away_id INT NOT NULL,     -- команда в гостях
28  country_code VARCHAR(2),  -- код страны
29
30  FOREIGN KEY (competition_id)
31    REFERENCES competitions (competition_id),
32  FOREIGN KEY (home_id)
33    REFERENCES teams (team_id),
34  FOREIGN KEY (away_id)
35    REFERENCES teams (team_id),
36  PRIMARY KEY (event_id, open_date)
37);
38
39
40CREATE TABLE IF NOT EXISTS markets ( -- рынки
41  event_id INT NOT NULL CONSTRAINT positive_event_id CHECK (event_id > 0),     -- идентификатор матча
42  open_date DATE NOT NULL,  -- дата матча
43  market_id INT NOT NULL CONSTRAINT positive_market_id CHECK (market_id > 0),   -- идентификатор рынка
44  market_name TEXT NOT NULL,-- имя рынка
45  FOREIGN KEY (event_id,open_date)
46    REFERENCES events (event_id,open_date) ON DELETE CASCADE,
47  PRIMARY KEY (event_id, open_date, market_id)
48);
49
50
51CREATE TABLE IF NOT EXISTS runner_names (  -- наименование опции
52  selection_id INT NOT NULL CONSTRAINT not_negative_selection_id CHECK (selection_id > -1),-- идентификатор опции рынка
53  runner_name TEXT NOT NULL,-- имя опции
54  PRIMARY KEY (selection_id)
55);
56
57CREATE TABLE IF NOT EXISTS runners (  -- опции на рынках
58  event_id INT NOT NULL CONSTRAINT positive_event_id CHECK (event_id > 0),    -- идентификатор матча
59  open_date DATE NOT NULL,  -- дата матча
60  market_id INT NOT NULL CONSTRAINT positive_market_id CHECK (market_id > 0),   -- идентификатор рынка
61  selection_id INT NOT NULL CONSTRAINT not_negative_selection_id CHECK (selection_id > -1),-- идентификатор опции рынка
62  status RUNNER_STATUS NOT NULL,  -- статус опции
63  FOREIGN KEY (event_id, open_date, market_id)
64    REFERENCES markets (event_id, open_date, market_id ) ON DELETE CASCADE,
65  PRIMARY KEY (event_id, open_date, market_id, selection_id)
66);
67
68CREATE TABLE IF NOT EXISTS prices(  --
69  created_at DATE,          -- временная метка, фомируемая в момент добавления записи
70  event_id INT NOT NULL CONSTRAINT positive_event_id CHECK (event_id > 0),    -- идентификатор матча
71  open_date DATE NOT NULL,  -- дата матча
72  market_id INT NOT NULL CONSTRAINT positive_market_id CHECK (market_id > 0),   -- идентификатор рынка
73  selection_id INT NOT NULL CONSTRAINT not_negative_selection_id CHECK (selection_id > -1),-- идентификатор опции рынка
74  side SIDE NOT NULL,       -- вид ставки: ЗА или ПРОТИВ
75  price_index SMALLINT NOT NULL CONSTRAINT price_index_form_0_to_2 CHECK (price_index > -1 AND price_index < 3), -- позиция котировки в списке котировок
76  game_minute SMALLINT NOT NULL CONSTRAINT positive_game_minute CHECK (game_minute > -1), -- минута матча
77  score_home SMALLINT NOT NULL CONSTRAINT positive_score_home CHECK (score_home > -1), -- счёт матча: домашняя команда
78  score_away SMALLINT NOT NULL CONSTRAINT positive_score_away CHECK (score_away > -1), -- счёт матча: команда в гостях
79  market_total_matched NUMERIC NOT NULL, -- совокупный объём совпавших пари на рынке опции, к которой относится котировка
80  market_total_available NUMERIC NOT NULL, -- совокупный объём доступных пари на рынке опции, к которой относится котировка
81  runner_last_price_traded NUMERIC NOT NULL, -- последний сыгрынный коэффициент для опции, к которой относится котировка
82  price NUMERIC NOT NULL, -- значение доступного коэффициента
83  size NUMERIC NOT NULL,  -- значение доступной ставки в долларах
84  FOREIGN KEY (event_id, open_date, market_id, selection_id)
85  REFERENCES runners (event_id, open_date, market_id, selection_id) ON DELETE CASCADE
86);
87
88
89CREATE OR REPLACE FUNCTION team( the_team_name text )
90  RETURNS INT AS $$
91  -- создать запись в таблице teams если запись со значением поля team_name, равным the_team_name, не существует
92  -- вернуть team_id записи, соответствующий значению the_team_name
93DECLARE
94  the_team_id INT;
95BEGIN
96  SELECT team_id INTO the_team_id
97  FROM teams
98  WHERE team_name= the_team_name
99  LIMIT 1;
100  IF the_team_id IS NULL THEN
101    INSERT INTO teams (team_name)
102    VALUES (the_team_name)
103    RETURNING team_id INTO the_team_id;
104  END IF;
105  RETURN the_team_id;
106END
107$$ LANGUAGE plpgsql;
108SELECT team('Спартак');
109
110
111
112CREATE OR REPLACE FUNCTION competition(the_competition_id INT, the_competition_name text )
113  -- создать запись в таблице competitions если ключ (the_competition_id) не существует
114  RETURNS VOID AS $$
115BEGIN
116  INSERT INTO competitions (competition_id, competition_name)
117  VALUES (the_competition_id, the_competition_name) ON CONFLICT (competition_id) DO NOTHING ;
118END
119$$ LANGUAGE plpgsql;
120SELECT competition(8596554, 'Israeli Liga Bet - North B');
121SELECT competition(821269, 'Spanish Tercera Division');
122
123
124CREATE OR REPLACE FUNCTION event(
125  the_event_id INT,
126  the_open_date DATE,
127  the_competition_id INT,
128  the_competition_name text,
129  the_home text,
130  the_away text,
131  the_country_code VARCHAR(2)
132)
133  -- создать запись в таблице events если ключ (the_event_id,the_open_date) не существует
134  RETURNS VOID AS $$
135BEGIN
136  PERFORM competition(the_competition_id, the_competition_name);
137  INSERT INTO events(event_id, open_date, competition_id,  home_id, away_id, country_code)
138  VALUES (
139    the_event_id,
140    the_open_date,
141    the_competition_id,
142    team(the_home),
143    team(the_away),
144    the_country_code)
145  ON CONFLICT (event_id, open_date) DO NOTHING;
146END
147$$ LANGUAGE plpgsql;
148
149
150SELECT event(
151    8596554, '2018-01-20T10:00:00Z',
152    821269, 'Spanish Tercera Division',
153    'Budaorsi', 'Пакш',
154    'RU'
155);
156
157SELECT event(
158    8591154, '2018-02-20T10:00:00Z',
159    821229, 'Лига Европы',
160    'Милан', 'Реал',
161    'EN'
162);
163
164
165CREATE OR REPLACE FUNCTION runner(
166  the_event_id INT,
167  the_open_date DATE,
168  the_market_id INT,
169  the_selection_id INT,
170  the_runner_name TEXT,
171  the_status RUNNER_STATUS
172)
173  RETURNS VOID AS $$
174  -- создать запись в таблице runners если данный ключ не существует в ней
175  -- иначе устанвить соответствующее ключу значение status
176BEGIN
177  INSERT INTO runners(event_id, open_date, market_id, selection_id, status)
178  VALUES (
179    the_event_id,
180    the_open_date,
181    the_market_id,
182    the_selection_id,
183    the_status
184  )
185  ON CONFLICT (event_id, open_date, market_id, selection_id)
186    DO UPDATE
187      SET status = the_status
188      WHERE
189        event_id = the_event_id AND
190        open_date = the_open_date AND
191        market_id = the_market_id AND
192        selection_id = the_selection_id;
193  INSERT INTO runner_names(selection_id, runner_name)
194  VALUES ( the_selection_id, the_runner_name )
195  ON CONFLICT (selection_id) DO NOTHING;
196END
197$$ LANGUAGE plpgsql;
198
199CREATE OR REPLACE FUNCTION market(
200  the_event_id INT,
201  the_open_date DATE,
202  the_market_id INT,
203  the_market_name text
204)
205  RETURNS VOID AS $$
206  -- создать запись в таблице markets если ключ (the_event_id,the_open_date,the_market_id) не существует
207BEGIN
208  INSERT INTO markets(event_id, open_date, market_id, market_name)
209  VALUES ( the_event_id, the_open_date, the_market_id, the_market_name)
210  ON CONFLICT (event_id, open_date, market_id) DO NOTHING;
211END
212$$ LANGUAGE plpgsql;