Tables
The following is a list of the tables in the Couch Potato database:
Legend:
PK- Primary Key
NN - Not Null
AI - Auto Increment

errorlog

Columns

Column
Datatype
PK
NN
AI
Default
id
INT(11)
timestamp
DATETIME
CURRENT_TIMESTAMP
status
VARCHAR(4)
subcode
VARCHAR(4)
title
VARCHAR(255)
message
VARCHAR(1000)
NULL
url
VARCHAR(255)

Indexes

Index
Type
Columns
Order
PRIMARY
PRIMARY
id
ASC

Foreign Keys

None.

Script

1
CREATE TABLE `errorlog` (
2
`id` int(11) NOT NULL AUTO_INCREMENT,
3
`timestamp` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
4
`status` varchar(4) NOT NULL,
5
`subcode` varchar(4) NOT NULL,
6
`title` varchar(45) NOT NULL,
7
`message` varchar(1000) DEFAULT NULL,
8
`url` varchar(255),
9
PRIMARY KEY (`id`)
10
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
Copied!

events

Columns

Column
Datatype
PK
NN
AI
Default
id
INT(11)
user
INT(11)
league
VARCHAR(45)
date
DATE

Indexes

Index
Type
Columns
Order
PRIMARY
PRIMARY
id
ASC
fk_user_idx
INDEX
user
ASC
fk_league_idx
INDEX
league
ASC

Foreign Keys

Foreign Key
Referenced Table
Column
Referenced Column
fk_user
'couch_potato'.'users'
user
id
fk_leagues
'couch_potato'.'leagues'
league
name

Script

1
CREATE TABLE `events` (
2
`id` int(11) NOT NULL AUTO_INCREMENT,
3
`user` int(11) NOT NULL,
4
`league` varchar(45) NOT NULL,
5
`date` date NOT NULL,
6
PRIMARY KEY (`id`),
7
KEY `fk_league_idx` (`league`),
8
KEY `fk_user_idx` (`user`),
9
CONSTRAINT `fk_leagues` FOREIGN KEY (`league`) REFERENCES `leagues` (`name`),
10
CONSTRAINT `fk_user` FOREIGN KEY (`user`) REFERENCES `users` (`id`)
11
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
Copied!

games

Columns

Column
Datatype
PK
NN
AI
Default
id
INT(11)
user
INT(11)
event
INT(11)
hometeam
VARCHAR(100)
awayteam
VARCHAR(100)
starttime
VARCHAR(12)
homescore
INT(11)
NULL
awayscore
INT(11)
NULL
whistle_start_time
VARCHAR(32)
NULL
whistle_end_time
VARCHAR(32)
NULL

Indexes

Index
Type
Columns
Order
PRIMARY
PRIMARY
id, user
ASC
user_idx
INDEX
user
ASC

Foreign Keys

None

Script

1
CREATE TABLE `games` (
2
`id` int(11) NOT NULL AUTO_INCREMENT,
3
`user` int(11) NOT NULL,
4
`event` int(11) NOT NULL,
5
`hometeam` varchar(100) NOT NULL,
6
`awayteam` varchar(100) NOT NULL,
7
`starttime` varchar(12) NOT NULL,
8
`homescore` int(11) DEFAULT NULL,
9
`awayscore` int(11) DEFAULT NULL,
10
`whistle_start_time` varchar(32) DEFAULT NULL,
11
`whistle_end_time` varchar(32) DEFAULT NULL,
12
PRIMARY KEY (`id`,`user`),
13
KEY `user_idx` (`user`),
14
KEY `event_idx` (`event`),
15
CONSTRAINT `fk_event` FOREIGN KEY (`event`) REFERENCES `events` (`id`)
16
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
Copied!

httplog

Columns

Column
Datatype
PK
NN
AI
Default
id
INT(11)
timestamp
DATETIME
CURRENT_TIMESTAMP
type
VARCHAR(12)
url
VARCHAR(255)
uniqueid
VARCHAR(255)
approveid
VARCHAR(255)
message
VARCHAR(1000)

Indexes

Index
Type
Columns
Order
PRIMARY
PRIMARY
id
ASC

Foreign Keys

None

Script

1
CREATE TABLE `httplog` (
2
`id` int(11) NOT NULL AUTO_INCREMENT,
3
`timestamp` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
4
`type` varchar(12) NOT NULL,
5
`url` varchar(255) NOT NULL,
6
`uniqueid` varchar(255) NOT NULL,
7
`approveid` varchar(255) NOT NULL,
8
`message` varchar(1000) NOT NULL,
9
PRIMARY KEY (`id`)
10
) ENGINE=InnoDB AUTO_INCREMENT=35 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
Copied!

incidents

Columns

Column
Datatype
PK
NN
AI
Default
id
INT(11)
timestamp
VARCHAR(60)
uniquename
VARCHAR(255)
call
VARCHAR(20)
message
JSON
url
VARCHAR(255)

Indexes

Index
Type
Columns
Order
PRIMARY
PRIMARY
id
ASC

Foreign Keys

None

Script

1
CREATE TABLE `incidents` (
2
`id` int(11) NOT NULL AUTO_INCREMENT,
3
`timestamp` varchar(60) NOT NULL,
4
`uniquename` varchar(255) NOT NULL,
5
`call` varchar(20) NOT NULL,
6
`message` json NOT NULL,
7
PRIMARY KEY (`id`)
8
) ENGINE=InnoDB AUTO_INCREMENT=35 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
Copied!

leagues

Columns

Column
Datatype
PK
NN
AI
Default
id
INT(11)
name
VARCHAR(45)
sport
INT(11)
icon
VARCHAR(64)

Indexes

Index
Type
Columns
Order
PRIMARY
PRIMARY
id, name
ASC
idx_name
INDEX
name
ASC
fk_sport_idx
INDEX
sport
ASC

Foreign Keys

Foreign Key
Referenced Table
Column
Referenced Column
fk_sport
'couch_potato'.'sports'
sport
id

Script

1
CREATE TABLE `leagues` (
2
`id` int(11) NOT NULL AUTO_INCREMENT,
3
`name` varchar(45) NOT NULL,
4
`sport` int(11) NOT NULL,
5
`icon` varchar(64) NOT NULL,
6
PRIMARY KEY (`id`,`name`),
7
KEY `idx_name` (`name`),
8
KEY `fk_sport_idx` (`sport`),
9
CONSTRAINT `fk_sport` FOREIGN KEY (`sport`) REFERENCES `sports` (`id`)
10
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
Copied!

progress

Columns

Column
Datatype
PK
NN
AI
Default
id
INT(11)
game
INT(11)
status
INT(11)

Indexes

Index
Type
Columns
Order
PRIMARY
PRIMARY
id
ASC
fk_status_idx
INDEX
status
ASC

Foreign Keys

Foreign Key
Referenced Table
Column
Referenced Column
fk_status
'couch_potato'.'status'
status
id

Script

1
CREATE TABLE `progress` (
2
`id` int(11) NOT NULL AUTO_INCREMENT,
3
`game` int(11) NOT NULL,
4
`status` int(11) NOT NULL,
5
PRIMARY KEY (`id`),
6
KEY `idx_progress_game` (`game`),
7
KEY `fk_status_idx` (`status`),
8
CONSTRAINT `fk_game` FOREIGN KEY (`game`) REFERENCES `games` (`id`),
9
CONSTRAINT `fk_status` FOREIGN KEY (`status`) REFERENCES `status` (`id`)
10
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
Copied!

sports

Columns

Column
Datatype
PK
NN
AI
Default
id
INT(11)
name
VARCHAR(45)
icon
VARCHAR(45)
duration
INT(11)
NULL

Indexes

Index
Type
Columns
Order
PRIMARY
PRIMARY
id
ASC
fk_leagues_idx
INDEX
name
ASC

Foreign Keys

None

Script

1
CREATE TABLE `sports` (
2
`id` int(11) NOT NULL AUTO_INCREMENT,
3
`name` varchar(45) NOT NULL,
4
`icon` varchar(45) NOT NULL,
5
`duration` int(11) DEFAULT NULL,
6
PRIMARY KEY (`id`,`name`),
7
KEY `fk_leagues_idx` (`name`)
8
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
Copied!

status

Columns

Column
Datatype
PK
NN
AI
Default
id
INT(11)
name
VARCHAR(20)

Indexes

Index
Type
Columns
Order
PRIMARY
PRIMARY
id
ASC

Foreign Keys

None

Script

1
CREATE TABLE `status` (
2
`id` int(11) NOT NULL,
3
`name` varchar(20) NOT NULL,
4
PRIMARY KEY (`id`)
5
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
Copied!

teams

Columns

Column
Datatype
PK
NN
AI
Default
id
INT(11)
name
VARCHAR(100)
icon
VARCHAR(45)
league
INT(11)

Indexes

Index
Type
Columns
Order
PRIMARY
PRIMARY
id
ASC
fk_teams_leagues_idx
INDEX
league
ASC

Foreign Keys

Foreign Key
Referenced Table
Column
Referenced Column
fk_teams_leagues
'couch_potato'.'leagues'
league
id

Script

1
CREATE TABLE `teams` (
2
`id` int(11) NOT NULL AUTO_INCREMENT,
3
`name` varchar(100) NOT NULL,
4
`icon` varchar(45) NOT NULL,
5
`league` int(11) NOT NULL,
6
PRIMARY KEY (`id`),
7
KEY `fk_teams_leagues_idx` (`league`),
8
CONSTRAINT `fk_teams_leagues` FOREIGN KEY (`league`) REFERENCES `leagues` (`id`)
9
) ENGINE=InnoDB AUTO_INCREMENT=721 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
Copied!

users

Columns

Column
Datatype
PK
NN
AI
Default
id
INT(11)
timestamp
DATETIME
CURRENT_TIMESTAMP
username
VARCHAR(45)
salt
VARCHAR(128)
password
CHAR(255)
email
VARCHAR(60)

Indexes

Index
Type
Columns
Order
PRIMARY
PRIMARY
id
ASC

Foreign Keys

None

Script

1
CREATE TABLE `users` (
2
`id` int(11) NOT NULL AUTO_INCREMENT,
3
`timestamp` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
4
`username` varchar(45) NOT NULL,
5
`salt` varchar(128) NOT NULL,
6
`password` char(255) NOT NULL,
7
`email` varchar(60) NOT NULL,
8
PRIMARY KEY (`id`)
9
) ENGINE=InnoDB AUTO_INCREMENT=38 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
Copied!
Last modified 2yr ago