KnYnJCLE

1DROP DATABASE IF EXISTS knex;
2DROP USER IF EXISTS knex;
3
4CREATE DATABASE knex
5       ENCODING = 'UTF8'
6       TABLESPACE = pg_default
7       LC_COLLATE = 'en_GB.UTF-8'
8       LC_CTYPE = 'en_GB.UTF-8'
9       CONNECTION LIMIT = -1;
10
11CREATE USER knex WITH PASSWORD 'knex';
12GRANT ALL PRIVILEGES ON DATABASE knex to knex;
13
14DROP TABLE IF EXISTS results;
15DROP TABLE IF EXISTS teams;
16DROP TABLE IF EXISTS managers;
17
18CREATE TABLE managers
19(
20  id serial NOT NULL,
21  name character varying(255) NOT NULL,
22  start_date timestamp with time zone,
23  end_date timestamp with time zone,
24  CONSTRAINT managers_pkey PRIMARY KEY (id)
25)
26WITH (
27  OIDS=FALSE
28);
29
30CREATE TABLE teams
31(
32  id serial NOT NULL,
33  name character varying(255),
34  CONSTRAINT teams_pkey PRIMARY KEY (id)
35)
36WITH (
37  OIDS=FALSE
38);
39
40CREATE TABLE results
41(
42  id serial NOT NULL,
43  "time" timestamp with time zone NOT NULL,
44  scored integer NOT NULL,
45  conceded integer NOT NULL,
46  result character varying(1) NOT NULL,
47  "position" integer NOT NULL,
48  team_id integer,
49  location character varying(1) NOT NULL,
50  manager_id integer,
51  CONSTRAINT results_pkey PRIMARY KEY (id),
52  CONSTRAINT results_manager_id_foreign FOREIGN KEY (manager_id)
53      REFERENCES managers (id) MATCH SIMPLE
54      ON UPDATE NO ACTION ON DELETE NO ACTION,
55  CONSTRAINT results_team_id_foreign FOREIGN KEY (team_id)
56      REFERENCES teams (id) MATCH SIMPLE
57      ON UPDATE NO ACTION ON DELETE NO ACTION
58)
59WITH (
60  OIDS=FALSE
61);