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

CREATE TABLE `errorlog` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `timestamp` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `status` varchar(4) NOT NULL,
  `subcode` varchar(4) NOT NULL,
  `title` varchar(45) NOT NULL,
  `message` varchar(1000) DEFAULT NULL,
  `url` varchar(255),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

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

CREATE TABLE `events` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user` int(11) NOT NULL,
  `league` varchar(45) NOT NULL,
  `date` date NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_league_idx` (`league`),
  KEY `fk_user_idx` (`user`),
  CONSTRAINT `fk_leagues` FOREIGN KEY (`league`) REFERENCES `leagues` (`name`),
  CONSTRAINT `fk_user` FOREIGN KEY (`user`) REFERENCES `users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

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

CREATE TABLE `games` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user` int(11) NOT NULL,
  `event` int(11) NOT NULL,
  `hometeam` varchar(100) NOT NULL,
  `awayteam` varchar(100) NOT NULL,
  `starttime` varchar(12) NOT NULL,
  `homescore` int(11) DEFAULT NULL,
  `awayscore` int(11) DEFAULT NULL,
  `whistle_start_time` varchar(32) DEFAULT NULL,
  `whistle_end_time` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`,`user`),
  KEY `user_idx` (`user`),
  KEY `event_idx` (`event`),
  CONSTRAINT `fk_event` FOREIGN KEY (`event`) REFERENCES `events` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

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

CREATE TABLE `httplog` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `timestamp` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `type` varchar(12) NOT NULL,
  `url` varchar(255) NOT NULL,
  `uniqueid` varchar(255) NOT NULL,
  `approveid` varchar(255) NOT NULL,
  `message` varchar(1000) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=35 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

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

CREATE TABLE `incidents` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `timestamp` varchar(60) NOT NULL,
  `uniquename` varchar(255) NOT NULL,
  `call` varchar(20) NOT NULL,
  `message` json NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=35 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

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

CREATE TABLE `leagues` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  `sport` int(11) NOT NULL,
  `icon` varchar(64) NOT NULL,
  PRIMARY KEY (`id`,`name`),
  KEY `idx_name` (`name`),
  KEY `fk_sport_idx` (`sport`),
  CONSTRAINT `fk_sport` FOREIGN KEY (`sport`) REFERENCES `sports` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

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

CREATE TABLE `progress` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `game` int(11) NOT NULL,
  `status` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_progress_game` (`game`),
  KEY `fk_status_idx` (`status`),
  CONSTRAINT `fk_game` FOREIGN KEY (`game`) REFERENCES `games` (`id`),
  CONSTRAINT `fk_status` FOREIGN KEY (`status`) REFERENCES `status` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

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

CREATE TABLE `sports` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  `icon` varchar(45) NOT NULL,
  `duration` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`,`name`),
  KEY `fk_leagues_idx` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

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

CREATE TABLE `status` (
  `id` int(11) NOT NULL,
  `name` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

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

CREATE TABLE `teams` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `icon` varchar(45) NOT NULL,
  `league` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_teams_leagues_idx` (`league`),
  CONSTRAINT `fk_teams_leagues` FOREIGN KEY (`league`) REFERENCES `leagues` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=721 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

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

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `timestamp` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `username` varchar(45) NOT NULL,
  `salt` varchar(128) NOT NULL,
  `password` char(255) NOT NULL,
  `email` varchar(60) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=38 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_c