wehub/dolthub--doltgresql
1
0
Fork 0
Code Issues Pull Requests Actions Deployments Models Agent Notes Packages Projects Releases Wiki Activity
Files
main
dolthub--doltgresql/testing/dumps/sql/dennis-campos-11_xg90_app.sql
T
wehub-resource-sync e014feafe1 chore: import upstream snapshot with attribution
2026-07-13 12:32:25 +08:00

1270 lines
39 KiB
PL/PgSQL
Raw Permalink Blame History

-- Downloaded from: https://github.com/dennis-campos-11/xg90_app/blob/6987535ab3c9cc19cf58221c2f8a00b0f9e6973b/db/structure.sql
\restrict EdWRZenb7mFdNu2UgQVaersivDeqiv9OblwKlhJaxPhij60hrIzkbhoMS6kduPo
-- Dumped from database version 15.14 (Postgres.app)
-- Dumped by pg_dump version 15.14 (Postgres.app)
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;
--
-- Name: process_fixture_list_data(jsonb); Type: FUNCTION; Schema: public; Owner: -
--
CREATE FUNCTION public.process_fixture_list_data(params jsonb) RETURNS TABLE(team_id integer, fixture_id integer, team_location integer, processed_stats jsonb, processed_facts jsonb)
LANGUAGE sql STABLE
AS $$
WITH single_fixture_list AS MATERIALIZED (
SELECT
params->>'id' AS id,
params->>'name' AS name,
(params->>'total_matches')::int AS total_matches,
(params->>'season_index')::int AS season_index,
(params->>'home_location')::int AS home_location,
(params->>'away_location')::int AS away_location,
(params->>'settings')::jsonb AS settings,
(params->>'fixture_date')::int AS fixture_date,
COALESCE(params->'fixture_list_fields_attributes', params->'fixture_list_fields') AS fixture_list_fields,
COALESCE(params->'fixture_list_competitions_attributes', params->'fixture_list_competitions') AS fixture_list_competitions,
params->'sort' AS sort
),
sort_params AS MATERIALIZED (
SELECT
sort->>'field_code' AS field_code,
sort->>'field_type' AS field_type,
sort->>'metric' AS metric,
(sort->>'location')::int AS location,
COALESCE(sort->>'direction', 'asc') AS direction
FROM single_fixture_list
),
total_matches_value AS MATERIALIZED (
SELECT total_matches FROM single_fixture_list
),
fixture_date_value AS MATERIALIZED (
SELECT fixture_date FROM single_fixture_list
),
selected_competitions AS MATERIALIZED (
SELECT (comp->>'competition_id')::int AS competition_id
FROM single_fixture_list sfl,
LATERAL jsonb_array_elements(sfl.fixture_list_competitions) comp
),
upcoming_fixtures AS MATERIALIZED (
SELECT id, starting_at, home_id, away_id, competition_id
FROM fixtures
WHERE
starting_at >= CASE (SELECT fixture_date FROM fixture_date_value)
WHEN 0 THEN now()
WHEN 1 THEN date_trunc('day', now())
WHEN 2 THEN date_trunc('day', now() + interval '1 day')
WHEN 3 THEN date_trunc('day', now() + interval '2 day')
WHEN 4 THEN date_trunc('day', now() + interval '3 day')
ELSE now()
END
AND starting_at < CASE (SELECT fixture_date FROM fixture_date_value)
WHEN 0 THEN 'infinity'::timestamp
WHEN 1 THEN date_trunc('day', now()) + interval '1 day'
WHEN 2 THEN date_trunc('day', now() + interval '1 day') + interval '1 day'
WHEN 3 THEN date_trunc('day', now() + interval '2 day') + interval '1 day'
WHEN 4 THEN date_trunc('day', now() + interval '3 day') + interval '1 day'
ELSE 'infinity'::timestamp
END
AND (
EXISTS (SELECT 1 FROM selected_competitions)
AND competition_id IN (SELECT competition_id FROM selected_competitions)
OR
NOT EXISTS (SELECT 1 FROM selected_competitions)
)
),
input_teams AS MATERIALIZED (
SELECT DISTINCT
f.home_id AS team_id,
f.starting_at AS starting_at,
1 AS team_location,
sfl.home_location AS data_location,
f.id AS fixture_id,
f.competition_id
FROM upcoming_fixtures f
CROSS JOIN single_fixture_list sfl
UNION
SELECT DISTINCT
f.away_id AS team_id,
f.starting_at AS starting_at,
2 AS team_location,
sfl.away_location AS data_location,
f.id AS fixture_id,
f.competition_id
FROM upcoming_fixtures f
CROSS JOIN single_fixture_list sfl
),
fields AS MATERIALIZED (
SELECT
COALESCE(fl->>'field_code', df.code) AS field_code,
COALESCE((fl->>'field_type')::NUMERIC, df.field_type) AS field_type,
(fl->>'filters')::JSONB AS filters
FROM single_fixture_list sfl
CROSS JOIN LATERAL jsonb_array_elements(
sfl.fixture_list_fields ||
'[{"field_code":"minutes_on_field_ft","field_type":1,"filters":{}},
{"field_code":"minutes_on_field_1h","field_type":1,"filters":{}},
{"field_code":"minutes_on_field_2h","field_type":1,"filters":{}}]'::jsonb
) AS fl
LEFT JOIN data_fields df
ON df.id = (fl->>'data_field_id')::INT
OR df.code = fl->>'field_code'
),
recent_fixtures AS MATERIALIZED (
SELECT
t.team_id,
t.team_location,
t.fixture_id,
f.starting_at,
CASE WHEN t.team_id = f.home_id THEN f.home_stats ELSE f.away_stats END AS stats,
CASE WHEN t.team_id = f.home_id THEN f.home_facts ELSE f.away_facts END AS facts,
CASE WHEN t.team_id = f.home_id THEN f.away_stats ELSE f.home_stats END AS opponent_stats,
CASE WHEN t.team_id = f.home_id THEN f.away_facts ELSE f.home_facts END AS opponent_facts,
CASE WHEN t.team_id = f.home_id THEN 'home' ELSE 'away' END AS location,
CASE WHEN t.team_id = f.home_id THEN f.away_id ELSE f.home_id END AS opponent_id,
opponent.name AS opponent_name
FROM input_teams t
JOIN LATERAL (
SELECT fi.*
FROM (
SELECT fi.*
FROM fixtures fi
JOIN seasons s ON fi.season_id = s.id
CROSS JOIN single_fixture_list sfl
WHERE fi.status = 1
AND (sfl.season_index IS NULL OR s.index <= sfl.season_index)
AND fi.home_id = t.team_id
AND ((sfl.settings->'general'->>'only_current_competition')::boolean IS NOT TRUE OR fi.competition_id = t.competition_id)
UNION ALL
SELECT fi.*
FROM fixtures fi
JOIN seasons s ON fi.season_id = s.id
CROSS JOIN single_fixture_list sfl
WHERE fi.status = 1
AND (sfl.season_index IS NULL OR s.index <= sfl.season_index)
AND fi.away_id = t.team_id
AND ((sfl.settings->'general'->>'only_current_competition')::boolean IS NOT TRUE OR fi.competition_id = t.competition_id)
) fi
WHERE (t.data_location IS NULL OR t.data_location NOT IN (1,2))
OR (t.data_location = 1 AND fi.home_id = t.team_id)
OR (t.data_location = 2 AND fi.away_id = t.team_id)
AND (
NOT (SELECT (settings->'general'->>'only_current_competition')::boolean FROM single_fixture_list)
OR fi.competition_id = t.competition_id
)
ORDER BY fi.starting_at DESC
LIMIT (SELECT total_matches FROM total_matches_value)
) f ON true
JOIN teams opponent ON opponent.id = CASE WHEN t.team_id = f.home_id THEN f.away_id ELSE f.home_id END
),
stats_agg AS MATERIALIZED (
SELECT
rf.team_id,
rf.team_location,
rf.fixture_id,
f1.field_code,
COUNT(*) AS games_played,
ROUND(AVG((rf.stats ->> f1.field_code)::NUMERIC), 2) AS average,
ROUND(SUM((rf.stats ->> f1.field_code)::NUMERIC), 2) AS total,
ROUND((
SUM((rf.stats ->> f1.field_code)::NUMERIC) /
(
SUM((rf.stats ->> ('minutes_on_field_' || RIGHT(f1.field_code, 2)))::NUMERIC) /
CASE
WHEN RIGHT(f1.field_code, 2) = 'ft' THEN 90.0
WHEN RIGHT(f1.field_code, 2) IN ('1h', '2h') THEN 45.0
ELSE 1.0
END
)
), 2) AS average_by_period
FROM recent_fixtures rf
JOIN fields f1 ON f1.field_type = 1
GROUP BY rf.team_id, rf.team_location, rf.fixture_id, f1.field_code
),
stats_agg_extras AS MATERIALIZED (
SELECT
s.fixture_id,
s.field_code,
ROUND(SUM(average), 2) AS overall,
ROUND(SUM(average_by_period), 2) AS overall_by_period
FROM stats_agg s
WHERE s.fixture_id IS NOT NULL
GROUP BY s.fixture_id, s.field_code
),
filtered_stats AS (
SELECT s.*, se.overall, se.overall_by_period
FROM stats_agg s
JOIN stats_agg_extras se
ON se.fixture_id = s.fixture_id AND s.field_code = se.field_code
LEFT JOIN fields f ON s.field_code = f.field_code AND f.field_type = 1
WHERE
se.overall BETWEEN COALESCE(CASE s.team_location WHEN 1 THEN NULLIF(f.filters->'overall'->'home'->>'from', '')::NUMERIC WHEN 2 THEN NULLIF(f.filters->'overall'->'away'->>'from', '')::NUMERIC ELSE NULL END, 0)
AND COALESCE(CASE s.team_location WHEN 1 THEN NULLIF(f.filters->'overall'->'home'->>'to', '')::NUMERIC WHEN 2 THEN NULLIF(f.filters->'overall'->'away'->>'to', '')::NUMERIC ELSE NULL END, 'infinity')
AND se.overall_by_period BETWEEN COALESCE(CASE s.team_location WHEN 1 THEN NULLIF(f.filters->'overall_by_period'->'home'->>'from', '')::NUMERIC WHEN 2 THEN NULLIF(f.filters->'overall_by_period'->'away'->>'from', '')::NUMERIC ELSE NULL END, 0)
AND COALESCE(CASE s.team_location WHEN 1 THEN NULLIF(f.filters->'overall_by_period'->'home'->>'to', '')::NUMERIC WHEN 2 THEN NULLIF(f.filters->'overall_by_period'->'away'->>'to', '')::NUMERIC ELSE NULL END, 'infinity')
AND s.average BETWEEN COALESCE(CASE s.team_location WHEN 1 THEN NULLIF(f.filters->'average'->'home'->>'from', '')::NUMERIC WHEN 2 THEN NULLIF(f.filters->'average'->'away'->>'from', '')::NUMERIC ELSE NULL END, 0)
AND COALESCE(CASE s.team_location WHEN 1 THEN NULLIF(f.filters->'average'->'home'->>'to', '')::NUMERIC WHEN 2 THEN NULLIF(f.filters->'average'->'away'->>'to', '')::NUMERIC ELSE NULL END, 'infinity')
AND s.average_by_period BETWEEN COALESCE(CASE s.team_location WHEN 1 THEN NULLIF(f.filters->'average_by_period'->'home'->>'from', '')::NUMERIC WHEN 2 THEN NULLIF(f.filters->'average_by_period'->'away'->>'from', '')::NUMERIC ELSE NULL END, 0)
AND COALESCE(CASE s.team_location WHEN 1 THEN NULLIF(f.filters->'average_by_period'->'home'->>'to', '')::NUMERIC WHEN 2 THEN NULLIF(f.filters->'average_by_period'->'away'->>'to', '')::NUMERIC ELSE NULL END, 'infinity')
),
facts_agg AS MATERIALIZED (
SELECT
rf.team_id,
rf.team_location,
rf.fixture_id,
f2.field_code,
COUNT(*) AS games_played,
SUM((rf.facts ->> f2.field_code)::NUMERIC) AS total,
ROUND(AVG((rf.facts ->> f2.field_code)::NUMERIC * 100), 0) AS percentage,
COALESCE(
ARRAY_POSITION(
ARRAY_APPEND(ARRAY_AGG((rf.facts ->> f2.field_code)::int ORDER BY rf.starting_at ASC), 0),
0
) - 1, 0
) AS streak
FROM recent_fixtures rf
JOIN fields f2 ON f2.field_type = 2
GROUP BY rf.team_id, rf.team_location, rf.fixture_id, f2.field_code
),
facts_agg_extras AS MATERIALIZED (
SELECT
f.fixture_id,
f.field_code,
ROUND(AVG(percentage), 2) AS average
FROM facts_agg f
WHERE f.fixture_id IS NOT NULL
GROUP BY f.fixture_id, f.field_code
),
filtered_facts AS MATERIALIZED (
SELECT fa.*, fe.average
FROM facts_agg fa
JOIN facts_agg_extras fe
ON fe.fixture_id = fa.fixture_id AND fa.field_code = fe.field_code
LEFT JOIN fields f ON fa.field_code = f.field_code AND f.field_type = 2
WHERE
fa.percentage BETWEEN COALESCE(CASE fa.team_location WHEN 1 THEN NULLIF(f.filters->'percentage'->'home'->>'from', '')::NUMERIC WHEN 2 THEN NULLIF(f.filters->'percentage'->'away'->>'from', '')::NUMERIC ELSE NULL END, 0)
AND COALESCE(CASE fa.team_location WHEN 1 THEN NULLIF(f.filters->'percentage'->'home'->>'to', '')::NUMERIC WHEN 2 THEN NULLIF(f.filters->'percentage'->'away'->>'to', '')::NUMERIC ELSE NULL END, 'infinity')
AND fa.total BETWEEN COALESCE(CASE fa.team_location WHEN 1 THEN NULLIF(f.filters->'total'->'home'->>'from', '')::NUMERIC WHEN 2 THEN NULLIF(f.filters->'total'->'away'->>'from', '')::NUMERIC ELSE NULL END, 0)
AND COALESCE(CASE fa.team_location WHEN 1 THEN NULLIF(f.filters->'total'->'home'->>'to', '')::NUMERIC WHEN 2 THEN NULLIF(f.filters->'total'->'away'->>'to', '')::NUMERIC ELSE NULL END, 'infinity')
AND fa.streak BETWEEN COALESCE(CASE fa.team_location WHEN 1 THEN NULLIF(f.filters->'streak'->'home'->>'from', '')::NUMERIC WHEN 2 THEN NULLIF(f.filters->'streak'->'away'->>'from', '')::NUMERIC ELSE NULL END, 0)
AND COALESCE(CASE fa.team_location WHEN 1 THEN NULLIF(f.filters->'streak'->'home'->>'to', '')::NUMERIC WHEN 2 THEN NULLIF(f.filters->'streak'->'away'->>'to', '')::NUMERIC ELSE NULL END, 'infinity')
),
field_count AS MATERIALIZED (
SELECT COUNT(DISTINCT field_code) AS total_fields FROM fields
),
stats_max AS MATERIALIZED (
SELECT
s.field_code,
MAX(s.average) AS max_average,
MAX(s.overall) AS max_overall,
MAX(s.overall_by_period) AS max_overall_by_period,
MAX(s.average_by_period) AS max_average_by_period,
MAX(s.total) AS max_total
FROM filtered_stats s
GROUP BY s.field_code
),
facts_max AS MATERIALIZED (
SELECT
f.field_code,
MAX(f.average) AS max_average,
MAX(f.percentage) AS max_percentage,
MAX(f.total) AS max_total,
MAX(f.streak) AS max_streak
FROM filtered_facts f
GROUP BY f.field_code
),
final_data AS MATERIALIZED (
SELECT
t.starting_at,
t.team_id,
t.fixture_id,
COALESCE(s.team_location, f.team_location) AS team_location,
JSONB_AGG(DISTINCT JSONB_BUILD_OBJECT(
'field_code', s.field_code,
'team_location', s.team_location,
'games_played', s.games_played,
'overall', s.overall::FLOAT8,
'overall_by_period', s.overall_by_period::FLOAT8,
'total', s.total::FLOAT8,
'average', s.average::FLOAT8,
'average_by_period', s.average_by_period::FLOAT8,
'max', JSONB_BUILD_OBJECT(
'overall', sm.max_overall::FLOAT8,
'overall_by_period', sm.max_overall_by_period::FLOAT8,
'total', sm.max_total::FLOAT8,
'average', sm.max_average::FLOAT8,
'average_by_period', sm.max_average_by_period::FLOAT8
)
)) AS processed_stats,
JSONB_AGG(DISTINCT JSONB_BUILD_OBJECT(
'field_code', f.field_code,
'team_location', f.team_location,
'games_played', f.games_played,
'average', f.average::FLOAT8,
'total', f.total::FLOAT8,
'percentage', f.percentage::FLOAT8,
'streak', f.streak::FLOAT8,
'max', JSONB_BUILD_OBJECT(
'average', fm.max_average::FLOAT8,
'total', fm.max_total::FLOAT8,
'percentage', fm.max_percentage::FLOAT8,
'streak', fm.max_streak::FLOAT8
)
)) AS processed_facts
FROM input_teams t
LEFT JOIN filtered_stats s
ON s.team_id = t.team_id AND s.field_code IS NOT NULL
LEFT JOIN stats_max sm
ON sm.field_code = s.field_code
LEFT JOIN filtered_facts f
ON f.team_id = t.team_id AND f.field_code IS NOT NULL
LEFT JOIN facts_max fm
ON fm.field_code = f.field_code
GROUP BY t.starting_at, t.fixture_id, t.team_id, s.team_location, f.team_location
HAVING
(COUNT(DISTINCT s.field_code) + COUNT(DISTINCT f.field_code)) = (SELECT total_fields FROM field_count)
)
SELECT
team_id,
fixture_id,
team_location,
processed_stats,
processed_facts
FROM final_data
$$;
SET default_tablespace = '';
SET default_table_access_method = heap;
--
-- Name: ar_internal_metadata; Type: TABLE; Schema: public; Owner: -
--
CREATE TABLE public.ar_internal_metadata (
key character varying NOT NULL,
value character varying,
created_at timestamp(6) without time zone NOT NULL,
updated_at timestamp(6) without time zone NOT NULL
);
--
-- Name: competitions; Type: TABLE; Schema: public; Owner: -
--
CREATE TABLE public.competitions (
id bigint NOT NULL,
name character varying,
external_ws_id bigint,
country_id bigint NOT NULL,
created_at timestamp(6) without time zone NOT NULL,
updated_at timestamp(6) without time zone NOT NULL
);
--
-- Name: competitions_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--
CREATE SEQUENCE public.competitions_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
--
-- Name: competitions_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--
ALTER SEQUENCE public.competitions_id_seq OWNED BY public.competitions.id;
--
-- Name: countries; Type: TABLE; Schema: public; Owner: -
--
CREATE TABLE public.countries (
id bigint NOT NULL,
name character varying,
external_ws_id bigint,
created_at timestamp(6) without time zone NOT NULL,
updated_at timestamp(6) without time zone NOT NULL
);
--
-- Name: countries_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--
CREATE SEQUENCE public.countries_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
--
-- Name: countries_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--
ALTER SEQUENCE public.countries_id_seq OWNED BY public.countries.id;
--
-- Name: data_fields; Type: TABLE; Schema: public; Owner: -
--
CREATE TABLE public.data_fields (
id bigint NOT NULL,
code character varying,
field_type integer,
half_type integer,
settings jsonb DEFAULT '{}'::jsonb,
created_at timestamp(6) without time zone NOT NULL,
updated_at timestamp(6) without time zone NOT NULL
);
--
-- Name: data_fields_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--
CREATE SEQUENCE public.data_fields_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
--
-- Name: data_fields_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--
ALTER SEQUENCE public.data_fields_id_seq OWNED BY public.data_fields.id;
--
-- Name: fixture_list_competitions; Type: TABLE; Schema: public; Owner: -
--
CREATE TABLE public.fixture_list_competitions (
id bigint NOT NULL,
fixture_list_id bigint NOT NULL,
competition_id bigint NOT NULL,
created_at timestamp(6) without time zone NOT NULL,
updated_at timestamp(6) without time zone NOT NULL
);
--
-- Name: fixture_list_competitions_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--
CREATE SEQUENCE public.fixture_list_competitions_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
--
-- Name: fixture_list_competitions_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--
ALTER SEQUENCE public.fixture_list_competitions_id_seq OWNED BY public.fixture_list_competitions.id;
--
-- Name: fixture_list_fields; Type: TABLE; Schema: public; Owner: -
--
CREATE TABLE public.fixture_list_fields (
id bigint NOT NULL,
fixture_list_id bigint NOT NULL,
data_field_id bigint NOT NULL,
filters jsonb DEFAULT '{}'::jsonb,
index integer,
created_at timestamp(6) without time zone NOT NULL,
updated_at timestamp(6) without time zone NOT NULL
);
--
-- Name: fixture_list_fields_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--
CREATE SEQUENCE public.fixture_list_fields_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
--
-- Name: fixture_list_fields_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--
ALTER SEQUENCE public.fixture_list_fields_id_seq OWNED BY public.fixture_list_fields.id;
--
-- Name: fixture_lists; Type: TABLE; Schema: public; Owner: -
--
CREATE TABLE public.fixture_lists (
id bigint NOT NULL,
name character varying,
home_location integer,
away_location integer,
total_matches integer,
season_index integer,
sort jsonb DEFAULT '{}'::jsonb,
settings jsonb DEFAULT '{}'::jsonb,
created_at timestamp(6) without time zone NOT NULL,
updated_at timestamp(6) without time zone NOT NULL
);
--
-- Name: fixture_lists_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--
CREATE SEQUENCE public.fixture_lists_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
--
-- Name: fixture_lists_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--
ALTER SEQUENCE public.fixture_lists_id_seq OWNED BY public.fixture_lists.id;
--
-- Name: fixtures; Type: TABLE; Schema: public; Owner: -
--
CREATE TABLE public.fixtures (
id bigint NOT NULL,
competition_id bigint,
season_id bigint,
home_id bigint,
away_id bigint,
external_ws_id bigint,
starting_at timestamp(6) without time zone,
status integer,
home_stats jsonb DEFAULT '{}'::jsonb,
away_stats jsonb DEFAULT '{}'::jsonb,
home_facts jsonb DEFAULT '{}'::jsonb,
away_facts jsonb DEFAULT '{}'::jsonb,
created_at timestamp(6) without time zone NOT NULL,
updated_at timestamp(6) without time zone NOT NULL
);
--
-- Name: fixtures_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--
CREATE SEQUENCE public.fixtures_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
--
-- Name: fixtures_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--
ALTER SEQUENCE public.fixtures_id_seq OWNED BY public.fixtures.id;
--
-- Name: schema_migrations; Type: TABLE; Schema: public; Owner: -
--
CREATE TABLE public.schema_migrations (
version character varying NOT NULL
);
--
-- Name: season_teams; Type: TABLE; Schema: public; Owner: -
--
CREATE TABLE public.season_teams (
id bigint NOT NULL,
season_id bigint NOT NULL,
team_id bigint NOT NULL,
created_at timestamp(6) without time zone NOT NULL,
updated_at timestamp(6) without time zone NOT NULL
);
--
-- Name: season_teams_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--
CREATE SEQUENCE public.season_teams_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
--
-- Name: season_teams_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--
ALTER SEQUENCE public.season_teams_id_seq OWNED BY public.season_teams.id;
--
-- Name: seasons; Type: TABLE; Schema: public; Owner: -
--
CREATE TABLE public.seasons (
id bigint NOT NULL,
name character varying,
external_ws_id bigint,
competition_id bigint NOT NULL,
index integer,
created_at timestamp(6) without time zone NOT NULL,
updated_at timestamp(6) without time zone NOT NULL
);
--
-- Name: seasons_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--
CREATE SEQUENCE public.seasons_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
--
-- Name: seasons_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--
ALTER SEQUENCE public.seasons_id_seq OWNED BY public.seasons.id;
--
-- Name: teams; Type: TABLE; Schema: public; Owner: -
--
CREATE TABLE public.teams (
id bigint NOT NULL,
name character varying,
common_name character varying,
short_name character varying,
external_ws_id bigint,
primary_color character varying,
text_color character varying,
created_at timestamp(6) without time zone NOT NULL,
updated_at timestamp(6) without time zone NOT NULL
);
--
-- Name: teams_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--
CREATE SEQUENCE public.teams_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
--
-- Name: teams_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--
ALTER SEQUENCE public.teams_id_seq OWNED BY public.teams.id;
--
-- Name: users; Type: TABLE; Schema: public; Owner: -
--
CREATE TABLE public.users (
id bigint NOT NULL,
email character varying,
password_digest character varying,
first_name character varying,
last_name character varying,
language character varying DEFAULT 'en'::character varying,
appearance character varying DEFAULT 'light'::character varying,
created_at timestamp(6) without time zone NOT NULL,
updated_at timestamp(6) without time zone NOT NULL,
reset_password_token character varying,
reset_password_sent_at timestamp(6) without time zone
);
--
-- Name: users_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--
CREATE SEQUENCE public.users_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
--
-- Name: users_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--
ALTER SEQUENCE public.users_id_seq OWNED BY public.users.id;
--
-- Name: competitions id; Type: DEFAULT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.competitions ALTER COLUMN id SET DEFAULT nextval('public.competitions_id_seq'::regclass);
--
-- Name: countries id; Type: DEFAULT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.countries ALTER COLUMN id SET DEFAULT nextval('public.countries_id_seq'::regclass);
--
-- Name: data_fields id; Type: DEFAULT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.data_fields ALTER COLUMN id SET DEFAULT nextval('public.data_fields_id_seq'::regclass);
--
-- Name: fixture_list_competitions id; Type: DEFAULT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.fixture_list_competitions ALTER COLUMN id SET DEFAULT nextval('public.fixture_list_competitions_id_seq'::regclass);
--
-- Name: fixture_list_fields id; Type: DEFAULT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.fixture_list_fields ALTER COLUMN id SET DEFAULT nextval('public.fixture_list_fields_id_seq'::regclass);
--
-- Name: fixture_lists id; Type: DEFAULT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.fixture_lists ALTER COLUMN id SET DEFAULT nextval('public.fixture_lists_id_seq'::regclass);
--
-- Name: fixtures id; Type: DEFAULT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.fixtures ALTER COLUMN id SET DEFAULT nextval('public.fixtures_id_seq'::regclass);
--
-- Name: season_teams id; Type: DEFAULT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.season_teams ALTER COLUMN id SET DEFAULT nextval('public.season_teams_id_seq'::regclass);
--
-- Name: seasons id; Type: DEFAULT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.seasons ALTER COLUMN id SET DEFAULT nextval('public.seasons_id_seq'::regclass);
--
-- Name: teams id; Type: DEFAULT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.teams ALTER COLUMN id SET DEFAULT nextval('public.teams_id_seq'::regclass);
--
-- Name: users id; Type: DEFAULT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.users ALTER COLUMN id SET DEFAULT nextval('public.users_id_seq'::regclass);
--
-- Name: ar_internal_metadata ar_internal_metadata_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.ar_internal_metadata
ADD CONSTRAINT ar_internal_metadata_pkey PRIMARY KEY (key);
--
-- Name: competitions competitions_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.competitions
ADD CONSTRAINT competitions_pkey PRIMARY KEY (id);
--
-- Name: countries countries_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.countries
ADD CONSTRAINT countries_pkey PRIMARY KEY (id);
--
-- Name: data_fields data_fields_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.data_fields
ADD CONSTRAINT data_fields_pkey PRIMARY KEY (id);
--
-- Name: fixture_list_competitions fixture_list_competitions_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.fixture_list_competitions
ADD CONSTRAINT fixture_list_competitions_pkey PRIMARY KEY (id);
--
-- Name: fixture_list_fields fixture_list_fields_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.fixture_list_fields
ADD CONSTRAINT fixture_list_fields_pkey PRIMARY KEY (id);
--
-- Name: fixture_lists fixture_lists_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.fixture_lists
ADD CONSTRAINT fixture_lists_pkey PRIMARY KEY (id);
--
-- Name: fixtures fixtures_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.fixtures
ADD CONSTRAINT fixtures_pkey PRIMARY KEY (id);
--
-- Name: schema_migrations schema_migrations_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.schema_migrations
ADD CONSTRAINT schema_migrations_pkey PRIMARY KEY (version);
--
-- Name: season_teams season_teams_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.season_teams
ADD CONSTRAINT season_teams_pkey PRIMARY KEY (id);
--
-- Name: seasons seasons_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.seasons
ADD CONSTRAINT seasons_pkey PRIMARY KEY (id);
--
-- Name: teams teams_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.teams
ADD CONSTRAINT teams_pkey PRIMARY KEY (id);
--
-- Name: users users_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.users
ADD CONSTRAINT users_pkey PRIMARY KEY (id);
--
-- Name: idx_on_fixture_list_id_competition_id_7e158515e7; Type: INDEX; Schema: public; Owner: -
--
CREATE UNIQUE INDEX idx_on_fixture_list_id_competition_id_7e158515e7 ON public.fixture_list_competitions USING btree (fixture_list_id, competition_id);
--
-- Name: index_competitions_on_country_id; Type: INDEX; Schema: public; Owner: -
--
CREATE INDEX index_competitions_on_country_id ON public.competitions USING btree (country_id);
--
-- Name: index_competitions_on_external_ws_id; Type: INDEX; Schema: public; Owner: -
--
CREATE UNIQUE INDEX index_competitions_on_external_ws_id ON public.competitions USING btree (external_ws_id);
--
-- Name: index_countries_on_external_ws_id; Type: INDEX; Schema: public; Owner: -
--
CREATE UNIQUE INDEX index_countries_on_external_ws_id ON public.countries USING btree (external_ws_id);
--
-- Name: index_data_fields_on_code; Type: INDEX; Schema: public; Owner: -
--
CREATE INDEX index_data_fields_on_code ON public.data_fields USING btree (code);
--
-- Name: index_data_fields_on_code_and_field_type; Type: INDEX; Schema: public; Owner: -
--
CREATE INDEX index_data_fields_on_code_and_field_type ON public.data_fields USING btree (code, field_type);
--
-- Name: index_fixture_list_competitions_on_competition_id; Type: INDEX; Schema: public; Owner: -
--
CREATE INDEX index_fixture_list_competitions_on_competition_id ON public.fixture_list_competitions USING btree (competition_id);
--
-- Name: index_fixture_list_competitions_on_fixture_list_id; Type: INDEX; Schema: public; Owner: -
--
CREATE INDEX index_fixture_list_competitions_on_fixture_list_id ON public.fixture_list_competitions USING btree (fixture_list_id);
--
-- Name: index_fixture_list_fields_on_data_field_id; Type: INDEX; Schema: public; Owner: -
--
CREATE INDEX index_fixture_list_fields_on_data_field_id ON public.fixture_list_fields USING btree (data_field_id);
--
-- Name: index_fixture_list_fields_on_fixture_list_id; Type: INDEX; Schema: public; Owner: -
--
CREATE INDEX index_fixture_list_fields_on_fixture_list_id ON public.fixture_list_fields USING btree (fixture_list_id);
--
-- Name: index_fixture_list_fields_on_fixture_list_id_and_data_field_id; Type: INDEX; Schema: public; Owner: -
--
CREATE UNIQUE INDEX index_fixture_list_fields_on_fixture_list_id_and_data_field_id ON public.fixture_list_fields USING btree (fixture_list_id, data_field_id);
--
-- Name: index_fixtures_on_away_id; Type: INDEX; Schema: public; Owner: -
--
CREATE INDEX index_fixtures_on_away_id ON public.fixtures USING btree (away_id);
--
-- Name: index_fixtures_on_away_id_and_starting_at; Type: INDEX; Schema: public; Owner: -
--
CREATE INDEX index_fixtures_on_away_id_and_starting_at ON public.fixtures USING btree (away_id, starting_at);
--
-- Name: index_fixtures_on_competition_id; Type: INDEX; Schema: public; Owner: -
--
CREATE INDEX index_fixtures_on_competition_id ON public.fixtures USING btree (competition_id);
--
-- Name: index_fixtures_on_competition_id_and_starting_at; Type: INDEX; Schema: public; Owner: -
--
CREATE INDEX index_fixtures_on_competition_id_and_starting_at ON public.fixtures USING btree (competition_id, starting_at);
--
-- Name: index_fixtures_on_external_ws_id; Type: INDEX; Schema: public; Owner: -
--
CREATE UNIQUE INDEX index_fixtures_on_external_ws_id ON public.fixtures USING btree (external_ws_id);
--
-- Name: index_fixtures_on_home_id; Type: INDEX; Schema: public; Owner: -
--
CREATE INDEX index_fixtures_on_home_id ON public.fixtures USING btree (home_id);
--
-- Name: index_fixtures_on_home_id_and_starting_at; Type: INDEX; Schema: public; Owner: -
--
CREATE INDEX index_fixtures_on_home_id_and_starting_at ON public.fixtures USING btree (home_id, starting_at);
--
-- Name: index_fixtures_on_season_id; Type: INDEX; Schema: public; Owner: -
--
CREATE INDEX index_fixtures_on_season_id ON public.fixtures USING btree (season_id);
--
-- Name: index_fixtures_on_season_id_and_status; Type: INDEX; Schema: public; Owner: -
--
CREATE INDEX index_fixtures_on_season_id_and_status ON public.fixtures USING btree (season_id, status);
--
-- Name: index_fixtures_on_starting_at; Type: INDEX; Schema: public; Owner: -
--
CREATE INDEX index_fixtures_on_starting_at ON public.fixtures USING btree (starting_at);
--
-- Name: index_fixtures_on_status_and_away_id; Type: INDEX; Schema: public; Owner: -
--
CREATE INDEX index_fixtures_on_status_and_away_id ON public.fixtures USING btree (status, away_id);
--
-- Name: index_fixtures_on_status_and_home_id; Type: INDEX; Schema: public; Owner: -
--
CREATE INDEX index_fixtures_on_status_and_home_id ON public.fixtures USING btree (status, home_id);
--
-- Name: index_season_teams_on_season_id; Type: INDEX; Schema: public; Owner: -
--
CREATE INDEX index_season_teams_on_season_id ON public.season_teams USING btree (season_id);
--
-- Name: index_season_teams_on_season_id_and_team_id; Type: INDEX; Schema: public; Owner: -
--
CREATE UNIQUE INDEX index_season_teams_on_season_id_and_team_id ON public.season_teams USING btree (season_id, team_id);
--
-- Name: index_season_teams_on_team_id; Type: INDEX; Schema: public; Owner: -
--
CREATE INDEX index_season_teams_on_team_id ON public.season_teams USING btree (team_id);
--
-- Name: index_seasons_on_competition_id; Type: INDEX; Schema: public; Owner: -
--
CREATE INDEX index_seasons_on_competition_id ON public.seasons USING btree (competition_id);
--
-- Name: index_seasons_on_external_ws_id; Type: INDEX; Schema: public; Owner: -
--
CREATE UNIQUE INDEX index_seasons_on_external_ws_id ON public.seasons USING btree (external_ws_id);
--
-- Name: index_seasons_on_index; Type: INDEX; Schema: public; Owner: -
--
CREATE INDEX index_seasons_on_index ON public.seasons USING btree (index);
--
-- Name: index_teams_on_external_ws_id; Type: INDEX; Schema: public; Owner: -
--
CREATE UNIQUE INDEX index_teams_on_external_ws_id ON public.teams USING btree (external_ws_id);
--
-- Name: fixtures fk_rails_140b10c8ba; Type: FK CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.fixtures
ADD CONSTRAINT fk_rails_140b10c8ba FOREIGN KEY (season_id) REFERENCES public.seasons(id);
--
-- Name: fixtures fk_rails_1f42e7a792; Type: FK CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.fixtures
ADD CONSTRAINT fk_rails_1f42e7a792 FOREIGN KEY (competition_id) REFERENCES public.competitions(id);
--
-- Name: season_teams fk_rails_2155f0dc86; Type: FK CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.season_teams
ADD CONSTRAINT fk_rails_2155f0dc86 FOREIGN KEY (season_id) REFERENCES public.seasons(id);
--
-- Name: fixture_list_competitions fk_rails_285890b3a6; Type: FK CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.fixture_list_competitions
ADD CONSTRAINT fk_rails_285890b3a6 FOREIGN KEY (competition_id) REFERENCES public.competitions(id);
--
-- Name: fixtures fk_rails_306eb56476; Type: FK CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.fixtures
ADD CONSTRAINT fk_rails_306eb56476 FOREIGN KEY (home_id) REFERENCES public.teams(id);
--
-- Name: competitions fk_rails_3b0b6c07a2; Type: FK CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.competitions
ADD CONSTRAINT fk_rails_3b0b6c07a2 FOREIGN KEY (country_id) REFERENCES public.countries(id);
--
-- Name: fixture_list_fields fk_rails_4c4ea6f1fc; Type: FK CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.fixture_list_fields
ADD CONSTRAINT fk_rails_4c4ea6f1fc FOREIGN KEY (fixture_list_id) REFERENCES public.fixture_lists(id);
--
-- Name: seasons fk_rails_5426847078; Type: FK CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.seasons
ADD CONSTRAINT fk_rails_5426847078 FOREIGN KEY (competition_id) REFERENCES public.competitions(id);
--
-- Name: season_teams fk_rails_91293a5993; Type: FK CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.season_teams
ADD CONSTRAINT fk_rails_91293a5993 FOREIGN KEY (team_id) REFERENCES public.teams(id);
--
-- Name: fixture_list_fields fk_rails_9782f6ad4c; Type: FK CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.fixture_list_fields
ADD CONSTRAINT fk_rails_9782f6ad4c FOREIGN KEY (data_field_id) REFERENCES public.data_fields(id);
--
-- Name: fixtures fk_rails_a7807906ad; Type: FK CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.fixtures
ADD CONSTRAINT fk_rails_a7807906ad FOREIGN KEY (away_id) REFERENCES public.teams(id);
--
-- Name: fixture_list_competitions fk_rails_fcc1601ccf; Type: FK CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY public.fixture_list_competitions
ADD CONSTRAINT fk_rails_fcc1601ccf FOREIGN KEY (fixture_list_id) REFERENCES public.fixture_lists(id);
--
-- PostgreSQL database dump complete
--
\unrestrict EdWRZenb7mFdNu2UgQVaersivDeqiv9OblwKlhJaxPhij60hrIzkbhoMS6kduPo
SET search_path TO "$user", public;
INSERT INTO "schema_migrations" (version) VALUES
('20250811193804'),
('20250811161817'),
('20250714184422'),
('20250714184408'),
('20250714184356'),
('20250714184341'),
('20250714184331'),
('20250714184313'),
('20250714184306'),
('20250714184257'),
('20250714184246'),
('20250714184159'),
('20250713022556'),
('20250711194725');
Reference in New Issue View Git Blame Copy Permalink