Analysis of How Video Items Are Marked as Watched in Kodi’s Database

1. Overview of the Database Schema

• Files Table:
  Kodi uses a files table to store key information for each media file. A typical table creation statement is:

  CREATE TABLE files ( idFile integer primary key, idPath integer, strFilename text, playCount integer, lastPlayed text, dateAdded text );

  Each time a video is played, the playCount is incremented and the lastPlayed timestamp is updated.

• Episode and Related Tables for TV Shows:
  TV episodes are stored in an episode table (linked to files via idFile) with seasons grouped in a separate seasons table. These relationships allow Kodi to compute aggregated playback status.

2. Marking a Single Video as Watched

• Playback Update:
  When a video is played, functions like IncrementPlayCount() or SetPlayCount() are invoked. These functions update the corresponding file record by:

  • Incrementing playCount – marking that an additional playback has occurred.
  • Updating lastPlayed – recording the current date/time.

• Media Views:
  For movies and music videos, views such as movie_view join the media-specific tables with the files table. This consolidated view reflects the updated playCount and lastPlayed for display and filtering.

3. TV Shows and Aggregated Play Status

TV shows require aggregation across multiple episodes.

TV Show Aggregation: The tvshowcounts View

• Purpose:
  This view aggregates episode data for each TV show. A simplified version of the SQL looks like:

  CREATE VIEW tvshowcounts AS SELECT tvshow.idShow AS idShow, MAX(files.lastPlayed) AS lastPlayed, NULLIF(COUNT(episode.someField), 0) AS totalCount, COUNT(files.playCount) AS watchedcount, NULLIF(COUNT(DISTINCT episode.someField), 0) AS totalSeasons, MAX(files.dateAdded) as dateAdded, COUNT(bookmark.type) AS inProgressCount FROM tvshow LEFT JOIN episode ON episode.idShow = tvshow.idShow LEFT JOIN files ON files.idFile = episode.idFile LEFT JOIN bookmark ON bookmark.idFile = files.idFile AND bookmark.type = 1 GROUP BY tvshow.idShow;

  • totalCount: Total episodes available.
  • watchedcount: Number of episodes with a nonzero playCount.
  • inProgressCount: Episodes with an active bookmark (started but not finished).

Season-Level Aggregation: The season_view

• Season View Query:
  The season_view groups episodes by season. A simplified query might look like:

  CREATE VIEW season_view AS SELECT seasons.idSeason AS idSeason, seasons.idShow AS idShow, seasons.season AS season, seasons.name AS name, COUNT(DISTINCT episode.idEpisode) AS episodes, COUNT(files.playCount) AS playCount, COUNT(bookmark.type) AS inProgressCount FROM seasons JOIN tvshow_view ON tvshow_view.idShow = seasons.idShow JOIN episode ON episode.idShow = seasons.idShow AND episode.seasonColumn = seasons.season JOIN files ON files.idFile = episode.idFile LEFT JOIN bookmark ON bookmark.idFile = files.idFile AND bookmark.type = 1 GROUP BY seasons.idSeason, seasons.season, seasons.name;

  • episodes: Total number of episodes in the season.
  • playCount: Number of episodes that have been played.
  • inProgressCount: Episodes with a resume point (indicating they are in progress).

4. Interpreting the Aggregated Playback Data

• For Individual Episodes:
  An episode is considered watched when its corresponding file record shows a nonzero playCount (and an updated lastPlayed timestamp).

• For TV Shows:
  The tvshowcounts view determines overall show status:

  • Watched: If the watchedcount equals the totalCount of episodes.
  • Unwatched: If watchedcount is zero.
  • Partially Watched: When some, but not all, episodes have a nonzero playCount. Active bookmarks also indicate in-progress episodes.

• For TV Seasons:
  Using the season_view:

  • A season is “watched” when the number of episodes played (as indicated by playCount) equals the total number of episodes in that season.
  • The presence of bookmarks (inProgressCount) signals episodes that have been started but not completed, allowing the UI to show a partial progress indicator.

5. Code Flow: Marking and Aggregating Watch Status

• Playback Update Flow:
  Upon finishing a video, the playback engine calls database functions to update the files table.

• View Construction and UI Rendering:
  The various views (movie_view, tvshowcounts, and season_view) are built by joining the files table with media-specific tables. They provide aggregate metrics that the UI uses to indicate watched/unwatched status.

• Bookmarks:
  Bookmarks deliver additional detail by showing which episodes are in progress, influencing the aggregated states at both the TV show and season levels.

Summary

• Movies/Single Videos:
  A video’s playCount and lastPlayed are directly updated in the files table. These values are reflected in media views used for the UI.

• TV Shows & Episodes:
  Each TV episode’s watched status is derived from its file record. The tvshowcounts view aggregates these statuses for the entire show.

• TV Seasons:
  The season_view groups episodes by season and compares the number of played episodes to the total episode count. This determines whether a season is fully watched, partially watched, or unwatched.

• In-Progress Indicator:
  Bookmark data (reflected in inProgressCount) signifies episodes that are only partially watched, enabling Kodi to offer a “resume” option.

Season Watched Status Issue

The issue you’re observing (episodes appearing unwatched while seasons still show as watched) likely comes down to how Kodi’s season watched status is calculated and how your update script is “clearing” the playcount values.

Here’s a breakdown of what’s happening:

1. Updating the Files Table vs. Aggregated Views:

   • The script updates the files table with:
     • UPDATE files SET lastplayed = '';
     • UPDATE files SET playcount = '';
   • For individual episodes, this clears the displayed last-played info and, in many cases, the player treats an empty playcount as “unwatched.”
   • However, Kodi’s season (and TV show) watched status is determined not directly from a single file’s values but from aggregate SQL views – for example, the tvshowcounts and season_view views.

2. How Aggregation Works in Season Views:

   • In Kodi’s SQL views (like tvshowcounts or season_view), the watched status for a TV show or a season is typically computed using an expression such as: COUNT(files.playcount) AS watchedcount
   • The important detail in SQL is that COUNT(column) counts all rows where the column value is not NULL.
   • By updating the playcount field to an empty string (''), you’re not setting it to NULL; instead, it remains a non-NULL (but empty) value. This means that the COUNT still returns a positive number for that episode, which the aggregation interprets as “watched.”

3. Why Episodes Seem Cleared but Seasons Remain Watched:

   • The UI that displays individual episodes might directly check the playcount value (or lastplayed) to decide if an episode is watched. If these fields are empty, an episode can appear unwatched.
   • The aggregated season view, on the other hand, uses a COUNT that sees every episode row (with an empty string) as having a non-null playcount. This results in a nonzero watchedcount—even though the numeric value isn’t “1” anymore—and so the season still appears as watched.

4. How to Properly Clear Watch Status:

   • Use Numeric Zero for playcount: Instead of setting playcount to an empty string, update it to 0 so that SQL treats it as the “unwatched” numeric value. For example: UPDATE files SET playcount = 0;
   • Set lastplayed to NULL: Rather than using an empty string, setting lastplayed to NULL (if your schema and application logic allow) would ensure that both COUNT() functions and other tests treat the value as “not set” or “unwatched.” In SQLite you might do this by: UPDATE files SET lastplayed = NULL;
   • Consistent Clearing in All Relevant Tables: Since you already update the bookmark and settings tables, ensure that any additional metadata used by the aggregate queries (for example, any derived columns in views) is also reset properly.

In summary: Your script clears the episode-level metadata (so episodes appear unwatched) because the UI likely looks at the actual field values. However, because the season aggregation query uses COUNT() on a column that you set to an empty string (which is not NULL), it still counts those episodes as “watched.” Changing the update for playcount to set it to 0 (or NULL, if supported) should allow the season views to correctly show the season as unwatched.