Subversion Repositories VORC

## View: v_games
#
# +--------------+--------------+------+-----+---------+-------+
# | Field        | Type         | Null | Key | Default | Extra |
# +--------------+--------------+------+-----+---------+-------+
# | id           | int(11)      | NO   |     | 0       |       |
# | teams        | varchar(262) | YES  |     | NULL    |       |
# | date         | date         | NO   |     | NULL    |       |
# | dayofweek    | varchar(9)   | YES  |     | NULL    |       |
# | time         | varchar(23)  | YES  |     | NULL    |       |
# | start_time   | time         | NO   |     | NULL    |       |
# | end_time     | time         | NO   |     | NULL    |       |
# | volhours     | double       | YES  |     | NULL    |       |
# | track        | varchar(45)  | NO   |     | NULL    |       |
# | level        | varchar(45)  | NO   |     | NULL    |       |
# | restrictions | varchar(45)  | YES  |     | NULL    |       |
# | gtype        | varchar(45)  | NO   |     | NULL    |       |
# | notes        | mediumtext   | YES  |     | NULL    |       |
# +--------------+--------------+------+-----+---------+-------+

CREATE OR REPLACE VIEW `v_games` AS select `game`.`id` AS `id`,concat_ws(' ',`game`.`title`,`game`.`team1`,'vs.',`game`.`team2`) AS `teams`,`game`.`date` AS `date`,dayname(`game`.`date`) AS `dayofweek`,concat(convert(time_format(`game`.`time`,'%H:%i') using utf8mb4),' - ',convert(time_format(`game`.`end_time`,'%H:%i') using utf8mb4)) AS `time`,`game`.`time` AS `start_time`,`game`.`end_time` AS `end_time`,(trim((time_to_sec(timediff(`game`.`end_time`,`game`.`time`)) / 3600)) + 0) AS `volhours`,`game`.`track` AS `track`,`game`.`level` AS `level`,`game`.`restrictions` AS `restrictions`,`game`.`type` AS `gtype`,`game`.`notes` AS `notes` from `game` order by `game`.`date`,`game`.`track`,concat(convert(time_format(`game`.`time`,'%H:%i') using utf8mb4),' - ',convert(time_format(`game`.`end_time`,'%H:%i') using utf8mb4)),`game`.`type`