Kodi (XBMC) v17.6 Videos (MySQL) Database DDL Scripts (includes 30 tables and 6 views)

tables.sql

CREATE TABLE actor
(
  actor_id INT AUTO_INCREMENT
    PRIMARY KEY,
  name     TEXT NULL,
  art_urls TEXT NULL,
  CONSTRAINT ix_actor_1
  UNIQUE (name)
)
  ENGINE = InnoDB;

CREATE TRIGGER delete_person
  AFTER DELETE
  ON actor
  FOR EACH ROW
  BEGIN DELETE FROM art
  WHERE media_id = old.actor_id AND media_type IN ('actor', 'artist', 'writer', 'director');
  END;

CREATE TABLE actor_link
(
  actor_id   INT  NULL,
  media_id   INT  NULL,
  media_type TEXT NULL,
  role       TEXT NULL,
  cast_order INT  NULL,
  CONSTRAINT ix_actor_link_1
  UNIQUE (actor_id, media_type, media_id),
  CONSTRAINT ix_actor_link_2
  UNIQUE (media_id, media_type, actor_id)
)
  ENGINE = InnoDB;

CREATE INDEX ix_actor_link_3
  ON actor_link (media_type);

CREATE TABLE art
(
  art_id     INT AUTO_INCREMENT
    PRIMARY KEY,
  media_id   INT  NULL,
  media_type TEXT NULL,
  type       TEXT NULL,
  url        TEXT NULL
)
  ENGINE = InnoDB;

CREATE INDEX ix_art
  ON art (media_id, media_type, type);

CREATE TABLE bookmark
(
  idBookmark         INT AUTO_INCREMENT
    PRIMARY KEY,
  idFile             INT    NULL,
  timeInSeconds      DOUBLE NULL,
  totalTimeInSeconds DOUBLE NULL,
  thumbNailImage     TEXT   NULL,
  player             TEXT   NULL,
  playerState        TEXT   NULL,
  type               INT    NULL
)
  ENGINE = InnoDB;

CREATE INDEX ix_bookmark
  ON bookmark (idFile, type);

CREATE TABLE country
(
  country_id INT AUTO_INCREMENT
    PRIMARY KEY,
  name       TEXT NULL,
  CONSTRAINT ix_country_1
  UNIQUE (name)
)
  ENGINE = InnoDB;

CREATE TABLE country_link
(
  country_id INT  NULL,
  media_id   INT  NULL,
  media_type TEXT NULL,
  CONSTRAINT ix_country_link_1
  UNIQUE (country_id, media_type, media_id),
  CONSTRAINT ix_country_link_2
  UNIQUE (media_id, media_type, country_id)
)
  ENGINE = InnoDB;

CREATE INDEX ix_country_link_3
  ON country_link (media_type);

CREATE TABLE director_link
(
  actor_id   INT  NULL,
  media_id   INT  NULL,
  media_type TEXT NULL,
  CONSTRAINT ix_director_link_1
  UNIQUE (actor_id, media_type, media_id),
  CONSTRAINT ix_director_link_2
  UNIQUE (media_id, media_type, actor_id)
)
  ENGINE = InnoDB;

CREATE INDEX ix_director_link_3
  ON director_link (media_type);

CREATE TABLE episode
(
  idEpisode  INT AUTO_INCREMENT
    PRIMARY KEY,
  idFile     INT         NULL,
  c00        TEXT        NULL,
  c01        TEXT        NULL,
  c02        TEXT        NULL,
  c03        TEXT        NULL,
  c04        TEXT        NULL,
  c05        TEXT        NULL,
  c06        TEXT        NULL,
  c07        TEXT        NULL,
  c08        TEXT        NULL,
  c09        TEXT        NULL,
  c10        TEXT        NULL,
  c11        TEXT        NULL,
  c12        VARCHAR(24) NULL,
  c13        VARCHAR(24) NULL,
  c14        TEXT        NULL,
  c15        TEXT        NULL,
  c16        TEXT        NULL,
  c17        VARCHAR(24) NULL,
  c18        TEXT        NULL,
  c19        TEXT        NULL,
  c20        TEXT        NULL,
  c21        TEXT        NULL,
  c22        TEXT        NULL,
  c23        TEXT        NULL,
  idShow     INT         NULL,
  userrating INT         NULL,
  idSeason   INT         NULL,
  CONSTRAINT ix_episode_file_1
  UNIQUE (idEpisode, idFile),
  CONSTRAINT id_episode_file_2
  UNIQUE (idFile, idEpisode)
)
  ENGINE = InnoDB;

CREATE INDEX ix_episode_show1
  ON episode (idEpisode, idShow);

CREATE INDEX ix_episode_season_episode
  ON episode (c12, c13);

CREATE INDEX ix_episode_bookmark
  ON episode (c17);

CREATE INDEX ixEpisodeBasePath
  ON episode (c19);

CREATE INDEX ix_episode_show2
  ON episode (idShow, idEpisode);

CREATE TRIGGER delete_episode
  AFTER DELETE
  ON episode
  FOR EACH ROW
  BEGIN DELETE FROM actor_link
  WHERE media_id = old.idEpisode AND media_type = 'episode';
    DELETE FROM director_link
    WHERE media_id = old.idEpisode AND media_type = 'episode';
    DELETE FROM writer_link
    WHERE media_id = old.idEpisode AND media_type = 'episode';
    DELETE FROM art
    WHERE media_id = old.idEpisode AND media_type = 'episode';
    DELETE FROM rating
    WHERE media_id = old.idEpisode AND media_type = 'episode';
    DELETE FROM uniqueid
    WHERE media_id = old.idEpisode AND media_type = 'episode';
  END;

CREATE TABLE files
(
  idFile      INT AUTO_INCREMENT
    PRIMARY KEY,
  idPath      INT  NULL,
  strFilename TEXT NULL,
  playCount   INT  NULL,
  lastPlayed  TEXT NULL,
  dateAdded   TEXT NULL
)
  ENGINE = InnoDB;

CREATE INDEX ix_files
  ON files (idPath, strFilename);

CREATE TRIGGER delete_file
  AFTER DELETE
  ON files
  FOR EACH ROW
  BEGIN DELETE FROM bookmark
  WHERE idFile = old.idFile;
    DELETE FROM settings
    WHERE idFile = old.idFile;
    DELETE FROM stacktimes
    WHERE idFile = old.idFile;
    DELETE FROM streamdetails
    WHERE idFile = old.idFile;
  END;

CREATE TABLE genre
(
  genre_id INT AUTO_INCREMENT
    PRIMARY KEY,
  name     TEXT NULL,
  CONSTRAINT ix_genre_1
  UNIQUE (name)
)
  ENGINE = InnoDB;

CREATE TABLE genre_link
(
  genre_id   INT  NULL,
  media_id   INT  NULL,
  media_type TEXT NULL,
  CONSTRAINT ix_genre_link_1
  UNIQUE (genre_id, media_type, media_id),
  CONSTRAINT ix_genre_link_2
  UNIQUE (media_id, media_type, genre_id)
)
  ENGINE = InnoDB;

CREATE INDEX ix_genre_link_3
  ON genre_link (media_type);

CREATE TABLE movie
(
  idMovie    INT AUTO_INCREMENT
    PRIMARY KEY,
  idFile     INT  NULL,
  c00        TEXT NULL,
  c01        TEXT NULL,
  c02        TEXT NULL,
  c03        TEXT NULL,
  c04        TEXT NULL,
  c05        TEXT NULL,
  c06        TEXT NULL,
  c07        TEXT NULL,
  c08        TEXT NULL,
  c09        TEXT NULL,
  c10        TEXT NULL,
  c11        TEXT NULL,
  c12        TEXT NULL,
  c13        TEXT NULL,
  c14        TEXT NULL,
  c15        TEXT NULL,
  c16        TEXT NULL,
  c17        TEXT NULL,
  c18        TEXT NULL,
  c19        TEXT NULL,
  c20        TEXT NULL,
  c21        TEXT NULL,
  c22        TEXT NULL,
  c23        TEXT NULL,
  idSet      INT  NULL,
  userrating INT  NULL,
  premiered  TEXT NULL,
  CONSTRAINT ix_movie_file_2
  UNIQUE (idMovie, idFile),
  CONSTRAINT ix_movie_file_1
  UNIQUE (idFile, idMovie)
)
  ENGINE = InnoDB;

CREATE INDEX ixMovieBasePath
  ON movie (c23);

CREATE TRIGGER delete_movie
  AFTER DELETE
  ON movie
  FOR EACH ROW
  BEGIN DELETE FROM genre_link
  WHERE media_id = old.idMovie AND media_type = 'movie';
    DELETE FROM actor_link
    WHERE media_id = old.idMovie AND media_type = 'movie';
    DELETE FROM director_link
    WHERE media_id = old.idMovie AND media_type = 'movie';
    DELETE FROM studio_link
    WHERE media_id = old.idMovie AND media_type = 'movie';
    DELETE FROM country_link
    WHERE media_id = old.idMovie AND media_type = 'movie';
    DELETE FROM writer_link
    WHERE media_id = old.idMovie AND media_type = 'movie';
    DELETE FROM movielinktvshow
    WHERE idMovie = old.idMovie;
    DELETE FROM art
    WHERE media_id = old.idMovie AND media_type = 'movie';
    DELETE FROM tag_link
    WHERE media_id = old.idMovie AND media_type = 'movie';
    DELETE FROM rating
    WHERE media_id = old.idMovie AND media_type = 'movie';
    DELETE FROM uniqueid
    WHERE media_id = old.idMovie AND media_type = 'movie';
  END;

CREATE TABLE movielinktvshow
(
  idMovie INT NULL,
  IdShow  INT NULL,
  CONSTRAINT ix_movielinktvshow_2
  UNIQUE (idMovie, IdShow),
  CONSTRAINT ix_movielinktvshow_1
  UNIQUE (IdShow, idMovie)
)
  ENGINE = InnoDB;

CREATE TABLE musicvideo
(
  idMVideo   INT AUTO_INCREMENT
    PRIMARY KEY,
  idFile     INT  NULL,
  c00        TEXT NULL,
  c01        TEXT NULL,
  c02        TEXT NULL,
  c03        TEXT NULL,
  c04        TEXT NULL,
  c05        TEXT NULL,
  c06        TEXT NULL,
  c07        TEXT NULL,
  c08        TEXT NULL,
  c09        TEXT NULL,
  c10        TEXT NULL,
  c11        TEXT NULL,
  c12        TEXT NULL,
  c13        TEXT NULL,
  c14        TEXT NULL,
  c15        TEXT NULL,
  c16        TEXT NULL,
  c17        TEXT NULL,
  c18        TEXT NULL,
  c19        TEXT NULL,
  c20        TEXT NULL,
  c21        TEXT NULL,
  c22        TEXT NULL,
  c23        TEXT NULL,
  userrating INT  NULL,
  premiered  TEXT NULL,
  CONSTRAINT ix_musicvideo_file_1
  UNIQUE (idMVideo, idFile),
  CONSTRAINT ix_musicvideo_file_2
  UNIQUE (idFile, idMVideo)
)
  ENGINE = InnoDB;

CREATE INDEX ixMusicVideoBasePath
  ON musicvideo (c14);

CREATE TRIGGER delete_musicvideo
  AFTER DELETE
  ON musicvideo
  FOR EACH ROW
  BEGIN DELETE FROM actor_link
  WHERE media_id = old.idMVideo AND media_type = 'musicvideo';
    DELETE FROM director_link
    WHERE media_id = old.idMVideo AND media_type = 'musicvideo';
    DELETE FROM genre_link
    WHERE media_id = old.idMVideo AND media_type = 'musicvideo';
    DELETE FROM studio_link
    WHERE media_id = old.idMVideo AND media_type = 'musicvideo';
    DELETE FROM art
    WHERE media_id = old.idMVideo AND media_type = 'musicvideo';
    DELETE FROM tag_link
    WHERE media_id = old.idMVideo AND media_type = 'musicvideo';
  END;

CREATE TABLE path
(
  idPath         INT AUTO_INCREMENT
    PRIMARY KEY,
  strPath        TEXT       NULL,
  strContent     TEXT       NULL,
  strScraper     TEXT       NULL,
  strHash        TEXT       NULL,
  scanRecursive  INT        NULL,
  useFolderNames TINYINT(1) NULL,
  strSettings    TEXT       NULL,
  noUpdate       TINYINT(1) NULL,
  exclude        TINYINT(1) NULL,
  dateAdded      TEXT       NULL,
  idParentPath   INT        NULL
)
  ENGINE = InnoDB;

CREATE INDEX ix_path
  ON path (strPath);

CREATE INDEX ix_path2
  ON path (idParentPath);

CREATE TABLE rating
(
  rating_id   INT AUTO_INCREMENT
    PRIMARY KEY,
  media_id    INT   NULL,
  media_type  TEXT  NULL,
  rating_type TEXT  NULL,
  rating      FLOAT NULL,
  votes       INT   NULL
)
  ENGINE = InnoDB;

CREATE INDEX ix_rating
  ON rating (media_id, media_type);

CREATE TABLE seasons
(
  idSeason   INT AUTO_INCREMENT
    PRIMARY KEY,
  idShow     INT  NULL,
  season     INT  NULL,
  name       TEXT NULL,
  userrating INT  NULL
)
  ENGINE = InnoDB;

CREATE INDEX ix_seasons
  ON seasons (idShow, season);

CREATE TRIGGER delete_season
  AFTER DELETE
  ON seasons
  FOR EACH ROW
  BEGIN DELETE FROM art
  WHERE media_id = old.idSeason AND media_type = 'season';
  END;

CREATE TABLE sets
(
  idSet       INT AUTO_INCREMENT
    PRIMARY KEY,
  strSet      TEXT NULL,
  strOverview TEXT NULL
)
  ENGINE = InnoDB;

CREATE TRIGGER delete_set
  AFTER DELETE
  ON sets
  FOR EACH ROW
  BEGIN DELETE FROM art
  WHERE media_id = old.idSet AND media_type = 'set';
  END;

CREATE TABLE settings
(
  idFile              INT        NULL,
  Deinterlace         TINYINT(1) NULL,
  ViewMode            INT        NULL,
  ZoomAmount          FLOAT      NULL,
  PixelRatio          FLOAT      NULL,
  VerticalShift       FLOAT      NULL,
  AudioStream         INT        NULL,
  SubtitleStream      INT        NULL,
  SubtitleDelay       FLOAT      NULL,
  SubtitlesOn         TINYINT(1) NULL,
  Brightness          FLOAT      NULL,
  Contrast            FLOAT      NULL,
  Gamma               FLOAT      NULL,
  VolumeAmplification FLOAT      NULL,
  AudioDelay          FLOAT      NULL,
  OutputToAllSpeakers TINYINT(1) NULL,
  ResumeTime          INT        NULL,
  Sharpness           FLOAT      NULL,
  NoiseReduction      FLOAT      NULL,
  NonLinStretch       TINYINT(1) NULL,
  PostProcess         TINYINT(1) NULL,
  ScalingMethod       INT        NULL,
  DeinterlaceMode     INT        NULL,
  StereoMode          INT        NULL,
  StereoInvert        TINYINT(1) NULL,
  VideoStream         INT        NULL,
  CONSTRAINT ix_settings
  UNIQUE (idFile)
)
  ENGINE = InnoDB;

CREATE TABLE stacktimes
(
  idFile INT  NULL,
  times  TEXT NULL,
  CONSTRAINT ix_stacktimes
  UNIQUE (idFile)
)
  ENGINE = InnoDB;

CREATE TABLE streamdetails
(
  idFile              INT   NULL,
  iStreamType         INT   NULL,
  strVideoCodec       TEXT  NULL,
  fVideoAspect        FLOAT NULL,
  iVideoWidth         INT   NULL,
  iVideoHeight        INT   NULL,
  strAudioCodec       TEXT  NULL,
  iAudioChannels      INT   NULL,
  strAudioLanguage    TEXT  NULL,
  strSubtitleLanguage TEXT  NULL,
  iVideoDuration      INT   NULL,
  strStereoMode       TEXT  NULL,
  strVideoLanguage    TEXT  NULL
)
  ENGINE = InnoDB;

CREATE INDEX ix_streamdetails
  ON streamdetails (idFile);

CREATE TABLE studio
(
  studio_id INT AUTO_INCREMENT
    PRIMARY KEY,
  name      TEXT NULL,
  CONSTRAINT ix_studio_1
  UNIQUE (name)
)
  ENGINE = InnoDB;

CREATE TABLE studio_link
(
  studio_id  INT  NULL,
  media_id   INT  NULL,
  media_type TEXT NULL,
  CONSTRAINT ix_studio_link_1
  UNIQUE (studio_id, media_type, media_id),
  CONSTRAINT ix_studio_link_2
  UNIQUE (media_id, media_type, studio_id)
)
  ENGINE = InnoDB;

CREATE INDEX ix_studio_link_3
  ON studio_link (media_type);

CREATE TABLE tag
(
  tag_id INT AUTO_INCREMENT
    PRIMARY KEY,
  name   TEXT NULL,
  CONSTRAINT ix_tag_1
  UNIQUE (name)
)
  ENGINE = InnoDB;

CREATE TABLE tag_link
(
  tag_id     INT  NULL,
  media_id   INT  NULL,
  media_type TEXT NULL,
  CONSTRAINT ix_tag_link_1
  UNIQUE (tag_id, media_type, media_id),
  CONSTRAINT ix_tag_link_2
  UNIQUE (media_id, media_type, tag_id)
)
  ENGINE = InnoDB;

CREATE INDEX ix_tag_link_3
  ON tag_link (media_type);

CREATE TRIGGER delete_tag
  AFTER DELETE
  ON tag_link
  FOR EACH ROW
  BEGIN DELETE FROM tag
  WHERE tag_id = old.tag_id AND tag_id NOT IN (SELECT DISTINCT tag_id
                                               FROM tag_link);
  END;

CREATE TABLE tvshow
(
  idShow     INT AUTO_INCREMENT
    PRIMARY KEY,
  c00        TEXT NULL,
  c01        TEXT NULL,
  c02        TEXT NULL,
  c03        TEXT NULL,
  c04        TEXT NULL,
  c05        TEXT NULL,
  c06        TEXT NULL,
  c07        TEXT NULL,
  c08        TEXT NULL,
  c09        TEXT NULL,
  c10        TEXT NULL,
  c11        TEXT NULL,
  c12        TEXT NULL,
  c13        TEXT NULL,
  c14        TEXT NULL,
  c15        TEXT NULL,
  c16        TEXT NULL,
  c17        TEXT NULL,
  c18        TEXT NULL,
  c19        TEXT NULL,
  c20        TEXT NULL,
  c21        TEXT NULL,
  c22        TEXT NULL,
  c23        TEXT NULL,
  userrating INT  NULL,
  duration   INT  NULL
)
  ENGINE = InnoDB;

CREATE TRIGGER delete_tvshow
  AFTER DELETE
  ON tvshow
  FOR EACH ROW
  BEGIN DELETE FROM actor_link
  WHERE media_id = old.idShow AND media_type = 'tvshow';
    DELETE FROM director_link
    WHERE media_id = old.idShow AND media_type = 'tvshow';
    DELETE FROM studio_link
    WHERE media_id = old.idShow AND media_type = 'tvshow';
    DELETE FROM tvshowlinkpath
    WHERE idShow = old.idShow;
    DELETE FROM genre_link
    WHERE media_id = old.idShow AND media_type = 'tvshow';
    DELETE FROM movielinktvshow
    WHERE idShow = old.idShow;
    DELETE FROM seasons
    WHERE idShow = old.idShow;
    DELETE FROM art
    WHERE media_id = old.idShow AND media_type = 'tvshow';
    DELETE FROM tag_link
    WHERE media_id = old.idShow AND media_type = 'tvshow';
    DELETE FROM rating
    WHERE media_id = old.idShow AND media_type = 'tvshow';
    DELETE FROM uniqueid
    WHERE media_id = old.idShow AND media_type = 'tvshow';
  END;

CREATE TABLE tvshowlinkpath
(
  idShow INT NULL,
  idPath INT NULL,
  CONSTRAINT ix_tvshowlinkpath_1
  UNIQUE (idShow, idPath),
  CONSTRAINT ix_tvshowlinkpath_2
  UNIQUE (idPath, idShow)
)
  ENGINE = InnoDB;

CREATE TABLE uniqueid
(
  uniqueid_id INT AUTO_INCREMENT
    PRIMARY KEY,
  media_id    INT  NULL,
  media_type  TEXT NULL,
  value       TEXT NULL,
  type        TEXT NULL
)
  ENGINE = InnoDB;

CREATE INDEX ix_uniqueid1
  ON uniqueid (media_id, media_type, type);

CREATE INDEX ix_uniqueid2
  ON uniqueid (media_type, value);

CREATE TABLE version
(
  idVersion      INT NULL,
  iCompressCount INT NULL
)
  ENGINE = InnoDB;

CREATE TABLE writer_link
(
  actor_id   INT  NULL,
  media_id   INT  NULL,
  media_type TEXT NULL,
  CONSTRAINT ix_writer_link_1
  UNIQUE (actor_id, media_type, media_id),
  CONSTRAINT ix_writer_link_2
  UNIQUE (media_id, media_type, actor_id)
)
  ENGINE = InnoDB;

CREATE INDEX ix_writer_link_3
  ON writer_link (media_type);

views.sql

CREATE VIEW movie_view AS
  SELECT `movie`.`idMovie`               AS `idMovie`, `movie`.`idFile` AS `idFile`, `movie`.`c00` AS `c00`,
         `movie`.`c01`                   AS `c01`, `movie`.`c02` AS `c02`, `movie`.`c03` AS `c03`,
         `movie`.`c04`                   AS `c04`, `movie`.`c05` AS `c05`, `movie`.`c06` AS `c06`,
         `movie`.`c07`                   AS `c07`, `movie`.`c08` AS `c08`, `movie`.`c09` AS `c09`,
         `movie`.`c10`                   AS `c10`, `movie`.`c11` AS `c11`, `movie`.`c12` AS `c12`,
         `movie`.`c13`                   AS `c13`, `movie`.`c14` AS `c14`, `movie`.`c15` AS `c15`,
         `movie`.`c16`                   AS `c16`, `movie`.`c17` AS `c17`, `movie`.`c18` AS `c18`,
         `movie`.`c19`                   AS `c19`, `movie`.`c20` AS `c20`, `movie`.`c21` AS `c21`,
         `movie`.`c22`                   AS `c22`, `movie`.`c23` AS `c23`, `movie`.`idSet` AS `idSet`,
         `movie`.`userrating`            AS `userrating`, `movie`.`premiered` AS `premiered`,
         `sets`.`strSet`                 AS `strSet`, `sets`.`strOverview` AS `strSetOverview`,
         `files`.`strFilename`           AS `strFileName`, `path`.`strPath` AS `strPath`,
         `files`.`playCount`             AS `playCount`, `files`.`lastPlayed` AS `lastPlayed`,
         `files`.`dateAdded`             AS `dateAdded`, `bookmark`.`timeInSeconds` AS `resumeTimeInSeconds`,
         `bookmark`.`totalTimeInSeconds` AS `totalTimeInSeconds`, `rating`.`rating` AS `rating`,
         `rating`.`votes`                AS `votes`, `rating`.`rating_type` AS `rating_type`,
         `uniqueid`.`value`              AS `uniqueid_value`, `uniqueid`.`type` AS `uniqueid_type`
  FROM ((((((`movie`
    LEFT JOIN `sets` ON ((`sets`.`idSet` = `movie`.`idSet`))) JOIN
    `files` ON ((`files`.`idFile` = `movie`.`idFile`))) JOIN
    `path` ON ((`path`.`idPath` = `files`.`idPath`))) LEFT JOIN
    `bookmark` ON (((`bookmark`.`idFile` = `movie`.`idFile`) AND
                    (`bookmark`.`type` = 1)))) LEFT JOIN `rating`
      ON ((`rating`.`rating_id` = `movie`.`c05`))) LEFT JOIN `uniqueid`
      ON ((`uniqueid`.`uniqueid_id` = `movie`.`c09`)));

CREATE VIEW musicvideo_view AS
  SELECT `musicvideo`.`idMVideo`         AS `idMVideo`, `musicvideo`.`idFile` AS `idFile`, `musicvideo`.`c00` AS `c00`,
         `musicvideo`.`c01`              AS `c01`, `musicvideo`.`c02` AS `c02`, `musicvideo`.`c03` AS `c03`,
         `musicvideo`.`c04`              AS `c04`, `musicvideo`.`c05` AS `c05`, `musicvideo`.`c06` AS `c06`,
         `musicvideo`.`c07`              AS `c07`, `musicvideo`.`c08` AS `c08`, `musicvideo`.`c09` AS `c09`,
         `musicvideo`.`c10`              AS `c10`, `musicvideo`.`c11` AS `c11`, `musicvideo`.`c12` AS `c12`,
         `musicvideo`.`c13`              AS `c13`, `musicvideo`.`c14` AS `c14`, `musicvideo`.`c15` AS `c15`,
         `musicvideo`.`c16`              AS `c16`, `musicvideo`.`c17` AS `c17`, `musicvideo`.`c18` AS `c18`,
         `musicvideo`.`c19`              AS `c19`, `musicvideo`.`c20` AS `c20`, `musicvideo`.`c21` AS `c21`,
         `musicvideo`.`c22`              AS `c22`, `musicvideo`.`c23` AS `c23`,
         `musicvideo`.`userrating`       AS `userrating`, `musicvideo`.`premiered` AS `premiered`,
         `files`.`strFilename`           AS `strFileName`, `path`.`strPath` AS `strPath`,
         `files`.`playCount`             AS `playCount`, `files`.`lastPlayed` AS `lastPlayed`,
         `files`.`dateAdded`             AS `dateAdded`, `bookmark`.`timeInSeconds` AS `resumeTimeInSeconds`,
         `bookmark`.`totalTimeInSeconds` AS `totalTimeInSeconds`
  FROM (((`musicvideo`
    JOIN `files` ON ((`files`.`idFile` = `musicvideo`.`idFile`))) JOIN
    `path` ON ((`path`.`idPath` = `files`.`idPath`))) LEFT JOIN
    `bookmark` ON (((`bookmark`.`idFile` = `musicvideo`.`idFile`) AND
                    (`bookmark`.`type` = 1))));

CREATE VIEW tvshowcounts AS
  SELECT `tvshow`.`idShow`                          AS `idShow`, max(`files`.`lastPlayed`) AS `lastPlayed`,
         nullif(count(`episode`.`c12`), 0)          AS `totalCount`, count(`files`.`playCount`) AS `watchedcount`,
         nullif(count(DISTINCT `episode`.`c12`), 0) AS `totalSeasons`, max(`files`.`dateAdded`) AS `dateAdded`
  FROM ((`tvshow`
    LEFT JOIN `episode`
      ON ((`episode`.`idShow` = `tvshow`.`idShow`))) LEFT JOIN `files`
      ON ((`files`.`idFile` = `episode`.`idFile`)))
  GROUP BY `tvshow`.`idShow`;

CREATE VIEW tvshow_view AS
  SELECT `tvshow`.`idShow`             AS `idShow`, `tvshow`.`c00` AS `c00`, `tvshow`.`c01` AS `c01`,
         `tvshow`.`c02`                AS `c02`, `tvshow`.`c03` AS `c03`, `tvshow`.`c04` AS `c04`,
         `tvshow`.`c05`                AS `c05`, `tvshow`.`c06` AS `c06`, `tvshow`.`c07` AS `c07`,
         `tvshow`.`c08`                AS `c08`, `tvshow`.`c09` AS `c09`, `tvshow`.`c10` AS `c10`,
         `tvshow`.`c11`                AS `c11`, `tvshow`.`c12` AS `c12`, `tvshow`.`c13` AS `c13`,
         `tvshow`.`c14`                AS `c14`, `tvshow`.`c15` AS `c15`, `tvshow`.`c16` AS `c16`,
         `tvshow`.`c17`                AS `c17`, `tvshow`.`c18` AS `c18`, `tvshow`.`c19` AS `c19`,
         `tvshow`.`c20`                AS `c20`, `tvshow`.`c21` AS `c21`, `tvshow`.`c22` AS `c22`,
         `tvshow`.`c23`                AS `c23`, `tvshow`.`userrating` AS `userrating`,
         `tvshow`.`duration`           AS `duration`, `path`.`idParentPath` AS `idParentPath`,
         `path`.`strPath`              AS `strPath`, `tvshowcounts`.`dateAdded` AS `dateAdded`,
         `tvshowcounts`.`lastPlayed`   AS `lastPlayed`, `tvshowcounts`.`totalCount` AS `totalCount`,
         `tvshowcounts`.`watchedcount` AS `watchedcount`, `tvshowcounts`.`totalSeasons` AS `totalSeasons`,
         `rating`.`rating`             AS `rating`, `rating`.`votes` AS `votes`,
         `rating`.`rating_type`        AS `rating_type`, `uniqueid`.`value` AS `uniqueid_value`,
         `uniqueid`.`type`             AS `uniqueid_type`
  FROM (((((`tvshow`
    LEFT JOIN `tvshowlinkpath`
      ON ((`tvshowlinkpath`.`idShow` = `tvshow`.`idShow`))) LEFT JOIN `path`
      ON ((`path`.`idPath` = `tvshowlinkpath`.`idPath`))) JOIN `tvshowcounts`
      ON ((`tvshow`.`idShow` = `tvshowcounts`.`idShow`))) LEFT JOIN `rating`
      ON ((`rating`.`rating_id` = `tvshow`.`c04`))) LEFT JOIN `uniqueid`
      ON ((`uniqueid`.`uniqueid_id` = `tvshow`.`c12`)))
  GROUP BY `tvshow`.`idShow`;

CREATE VIEW season_view AS
  SELECT `seasons`.`idSeason`                  AS `idSeason`, `seasons`.`idShow` AS `idShow`,
         `seasons`.`season`                    AS `season`, `seasons`.`name` AS `name`,
         `seasons`.`userrating`                AS `userrating`, `tvshow_view`.`strPath` AS `strPath`,
         `tvshow_view`.`c00`                   AS `showTitle`, `tvshow_view`.`c01` AS `plot`,
         `tvshow_view`.`c05`                   AS `premiered`, `tvshow_view`.`c08` AS `genre`,
         `tvshow_view`.`c14`                   AS `studio`, `tvshow_view`.`c13` AS `mpaa`,
         count(DISTINCT `episode`.`idEpisode`) AS `episodes`, count(`files`.`playCount`) AS `playCount`,
         min(`episode`.`c05`)                  AS `aired`
  FROM (((`seasons`
    JOIN `tvshow_view` ON ((`tvshow_view`.`idShow` = `seasons`.`idShow`))) JOIN
    `episode` ON (((`episode`.`idShow` = `seasons`.`idShow`) AND
                   (`episode`.`c12` = `seasons`.`season`)))) JOIN
    `files` ON ((`files`.`idFile` = `episode`.`idFile`)))
  GROUP BY `seasons`.`idSeason`;

CREATE VIEW episode_view AS
  SELECT `episode`.`idEpisode`           AS `idEpisode`, `episode`.`idFile` AS `idFile`, `episode`.`c00` AS `c00`,
         `episode`.`c01`                 AS `c01`, `episode`.`c02` AS `c02`, `episode`.`c03` AS `c03`,
         `episode`.`c04`                 AS `c04`, `episode`.`c05` AS `c05`, `episode`.`c06` AS `c06`,
         `episode`.`c07`                 AS `c07`, `episode`.`c08` AS `c08`, `episode`.`c09` AS `c09`,
         `episode`.`c10`                 AS `c10`, `episode`.`c11` AS `c11`, `episode`.`c12` AS `c12`,
         `episode`.`c13`                 AS `c13`, `episode`.`c14` AS `c14`, `episode`.`c15` AS `c15`,
         `episode`.`c16`                 AS `c16`, `episode`.`c17` AS `c17`, `episode`.`c18` AS `c18`,
         `episode`.`c19`                 AS `c19`, `episode`.`c20` AS `c20`, `episode`.`c21` AS `c21`,
         `episode`.`c22`                 AS `c22`, `episode`.`c23` AS `c23`, `episode`.`idShow` AS `idShow`,
         `episode`.`userrating`          AS `userrating`, `episode`.`idSeason` AS `idSeason`,
         `files`.`strFilename`           AS `strFileName`, `path`.`strPath` AS `strPath`,
         `files`.`playCount`             AS `playCount`, `files`.`lastPlayed` AS `lastPlayed`,
         `files`.`dateAdded`             AS `dateAdded`, `tvshow`.`c00` AS `strTitle`, `tvshow`.`c08` AS `genre`,
         `tvshow`.`c14`                  AS `studio`, `tvshow`.`c05` AS `premiered`, `tvshow`.`c13` AS `mpaa`,
         `bookmark`.`timeInSeconds`      AS `resumeTimeInSeconds`,
         `bookmark`.`totalTimeInSeconds` AS `totalTimeInSeconds`, `rating`.`rating` AS `rating`,
         `rating`.`votes`                AS `votes`, `rating`.`rating_type` AS `rating_type`,
         `uniqueid`.`value`              AS `uniqueid_value`, `uniqueid`.`type` AS `uniqueid_type`
  FROM (((((((`episode`
    JOIN `files` ON ((`files`.`idFile` = `episode`.`idFile`))) JOIN
    `tvshow` ON ((`tvshow`.`idShow` = `episode`.`idShow`))) JOIN
    `seasons` ON ((`seasons`.`idSeason` = `episode`.`idSeason`))) JOIN
    `path` ON ((`files`.`idPath` = `path`.`idPath`))) LEFT JOIN
    `bookmark` ON (((`bookmark`.`idFile` = `episode`.`idFile`) AND
                    (`bookmark`.`type` = 1)))) LEFT JOIN `rating`
      ON ((`rating`.`rating_id` = `episode`.`c03`))) LEFT JOIN `uniqueid`
      ON ((`uniqueid`.`uniqueid_id` = `episode`.`c20`)));

It seems that Kodi (v17.6 at least) has some problems with MariaDB (v10.x) that it cannot generate views correctly.

Here is the dump of another database my Kodi generated on MySQL. I have successfully fixed my MariaDB instance by creating views manually. (running views.sql scripts)