set ALL episodes in all series as unwatched (reset database for new users)

Discussion in 'My TVSeries' started by dir, June 23, 2013.

  1. dir
    • Premium Supporter

    dir MP Donator

    Joined:
    August 30, 2006
    Messages:
    408
    Likes Received:
    32
    Gender:
    Male
    Ratings:
    +40 / 0
    Home Country:
    Afghanistan Afghanistan
    Show System Specs
    Does anyone know of a quick way to reset the watched flag for all episodes in all series of a large collection? I have 350 series and 25000 episodes and have tried manually resetting series one at a time, either via the gui or via hte config util. Its going to take forever this way.

    I need to reset ALL episodes in ALL series at once - not one series at a time.



    I already know you can reset an entire series, or season, or episode at once - I'm asking to reset ALL series at once, and not one by one. (Please re-read this before you helpfully suggest what I've already said I've tried!)

    I've also tried exporting the watched flags to see if I could modify the file, but that doesn't work either.
     
  2. Google AdSense Guest Advertisement



    to hide all adverts.
  3. RoChess
    • Premium Supporter

    RoChess Extension Developer

    Joined:
    March 10, 2006
    Messages:
    4,192
    Likes Received:
    1,308
    Ratings:
    +1,689 / 2
    I've never looked into it, but Follw.it or Trakt might offer an easier "reset all" method on their website, which will then be synced back to your setup if you configured one of them in your MediaPortal setup.

    Otherwise there is always SQL.
     
  4. ltfearme
    • Premium Supporter

    ltfearme Community Plugin Dev

    Joined:
    June 10, 2007
    Messages:
    6,474
    Likes Received:
    4,286
    Gender:
    Male
    Occupation:
    Software Test Engineer
    Location:
    Sydney
    Ratings:
    +5,442 / 0
    Home Country:
    Australia Australia
    Quickest way will be a SQL set query on the database itself, there is no way to do this currently from with-in the Config/GUI. Let me know if you're not capable of doing this and I (or Rochess) will reply with detailed instructions.

    Note: If you're using trakt you may want to disable it or setup another account as it will re-sync your watched state to your local database.

    The trakt plugin can sync your unwatched states but only if you set them as unwatched in the MediaPortal GUI (not config or database), there is no quick way to un-watch them ALL from the website either.
     
  5. dir
    • Premium Supporter

    dir MP Donator

    Joined:
    August 30, 2006
    Messages:
    408
    Likes Received:
    32
    Gender:
    Male
    Ratings:
    +40 / 0
    Home Country:
    Afghanistan Afghanistan
    Show System Specs
    I take your SQL challenge and shall apply myself to working out the syntax.
    something like [quickly googles]

    UPDATE table1 SET column1='Value2' WHERE column1='Value1';

    Now I just gotta work out what the column names are, the table name, the utility to allow me to modify a database, and the initial syntax for opening the db!

    Progress:table is 'local_episodes' and column is 'DateWatched'
     
    Last edited: June 24, 2013
  6. dir
    • Premium Supporter

    dir MP Donator

    Joined:
    August 30, 2006
    Messages:
    408
    Likes Received:
    32
    Gender:
    Male
    Ratings:
    +40 / 0
    Home Country:
    Afghanistan Afghanistan
    Show System Specs
    Ok, I hit a brick wall. I worked out the following:

    Tool to use to open / modify database: SQLITE3.exe
    commands used:
    .mode column
    .headers on
    .output blah.txt
    select * from local_episodes;
    .quit

    I examine the output and it shows:
    Code (Text):
    1. EpisodeFilename                                                      OriginalComposite  CompositeID  SeriesID   SeasonIndex  EpisodeIndex  LocalEpisodeName  LocalImportProcessed  CompositeUpdated  AvailableSubtitles  OriginalComposite2  CompositeID2  EpisodeIndex2  videoWidth  videoHeight  FileDateAdded        FileDateCreated   IsAvailable  DateWatched  ext      VolumeLabel                  localPlaytime  VideoCodec   VideoFormat  VideoFormatProfile  VideoBitrate  VideoFrameRate  VideoAspectRatio  AudioCodec  AudioFormat  AudioFormatProfile  AudioBitrate  AudioChannels  AudioTracks  TextCount  StopTime
    2. ----------------------------------------------------------------------  -----------------  -----------  ----------  -----------  ------------  ----------------  --------------------  ----------------  ------------------  ------------------  ------------  -------------  ----------  -----------  -------------------  -------------------  -----------  -----------  ----------  ---------------------------  -------------  ---------------  -----------  ------------------  ------------  --------------  ----------------  ----------  -----------  ------------------  ------------  -------------  -----------  ----------  ----------
    3. \\mofo\Audio Video Media\TV\2 Broke Girls\Season 01\s01e01 - Pilot.mkv                   248741_1x1  248741   1         1            Pilot           2                   1               0                                                   0            1920      1080         2013-06-11 14:20:09  2012-12-18 23:17:50  2                         mkv         \\mofo\Audio Video Media\TV  1319319       V_MPEG4/ISO/AVC  AVC          High@L4.1       12675000    23.976          1.778          A_DTS    DTS         -1                  1509000     6           1         -1                 
    4. \\mofo\Audio Video Media\TV\2 Broke Girls\Season 01\s01e02 - And the B                   248741_1x2  248741   1         2            And the Breakup  2                  1               0                                                   0            1920      1080         2013-06-11 14:42:28  2012-12-18 23:44:39  2                         mkv         \\mofo\Audio Video Media\TV  1286286       V_MPEG4/ISO/AVC  AVC          High@L4.1       8164000     23.976          1.778          A_DTS    DTS         -1                  1509000     6           1         -1                 
    5. \\mofo\Audio Video Media\TV\2 Broke Girls\Season 01\s01e03 - And Strok                   248741_1x3  248741   1         3            And Strokes of G  2                     1               0                                                   0            1280      720       2013-06-11 15:02:31  2012-12-18 17:46:46  2                        mkv         \\mofo\Audio Video Media\TV  1299299       V_MPEG4/ISO/AVC  AVC          High@L4.1       5657000     23.976          1.778          A_DTS    DTS         -1                  1509000     6           1         -1                 
    6. \\mofo\Audio Video Media\TV\2 Broke Girls\Season 01\s01e04 - And the R                   248741_1x4  248741   1         4            And the Rich Peo  2                     1               0                                                   0            1280      720       2013-06-11 15:20:25  2012-12-18 18:08:58  2                        mkv         \\mofo\Audio Video Media\TV  1293293       V_MPEG4/ISO/AVC  AVC          High@L4.1       5690000     23.976          1.778          A_DTS    DTS         -1                  1509000     6           1         -1                 
    (I have no idea why the columns don't line up - they do in the output file and they do when I preview this forum posting. They just don't line up aftwards...) So the following may not seem clear from the table above, but its true:

    The 'DateWatched' column is blank for all 25000 episodes!

    What column is used in what table to indicate that an episode has been watched?
     
    Last edited: June 24, 2013
  7. ltfearme
    • Premium Supporter

    ltfearme Community Plugin Dev

    Joined:
    June 10, 2007
    Messages:
    6,474
    Likes Received:
    4,286
    Gender:
    Male
    Occupation:
    Software Test Engineer
    Location:
    Sydney
    Ratings:
    +5,442 / 0
    Home Country:
    Australia Australia
    The watched flag gets persisted to the online_episodes table. I believe the column name is 'watched' as well.

    I dont have the database in front of me until tonight so I may be a little off with my names.
     
    Last edited: June 24, 2013
  8. dir
    • Premium Supporter

    dir MP Donator

    Joined:
    August 30, 2006
    Messages:
    408
    Likes Received:
    32
    Gender:
    Male
    Ratings:
    +40 / 0
    Home Country:
    Afghanistan Afghanistan
    Show System Specs
    Ah, thanks. Unfortunately I ran into a show-stopper.
    The online_episodes table was fine - I found and reset all episode watched flags to 0.
    However, the 'season' table contains two columns that hold info about watched status - 'UnwatchedItems' and 'EpisodesUnwatched'. There's also 'EpisodeCount' which contains the # of episodes in the season.

    UnwatchedItems is a binary value, so I assume it indicates whether there are any unwatched episodes in that season. That's easy enough to reset.
    'EpisodesUnwatched' however is harder - it needs to be reset back to the same value as 'EpisodeCount'. In other words:

    Season 3 of Bewitched:
    UnwatchedItems = 1
    EpisodeCount = 23
    EpisodesUnwatched = 4

    Somehow I need to be able to set 'EpisodeUnwatched' to "23". Undoubtedly its possible using advanced SQL syntax but its beyond my simple grasp!

    I assume the same sort of columns exist at the Series level as well.
     
  9. RoChess
    • Premium Supporter

    RoChess Extension Developer

    Joined:
    March 10, 2006
    Messages:
    4,192
    Likes Received:
    1,308
    Ratings:
    +1,689 / 2
  10. ltfearme
    • Premium Supporter

    ltfearme Community Plugin Dev

    Joined:
    June 10, 2007
    Messages:
    6,474
    Likes Received:
    4,286
    Gender:
    Male
    Occupation:
    Software Test Engineer
    Location:
    Sydney
    Ratings:
    +5,442 / 0
    Home Country:
    Australia Australia
    Yeah the other fields you mention are tricker, these are updated at the very end of an import.

    So you can either 'Run Import', go do the shopping and hope it's finished when you get back OR wait until tonight and I will post the SQL command to run on the season and series tables.
     
  11. dir
    • Premium Supporter

    dir MP Donator

    Joined:
    August 30, 2006
    Messages:
    408
    Likes Received:
    32
    Gender:
    Male
    Ratings:
    +40 / 0
    Home Country:
    Afghanistan Afghanistan
    Show System Specs
    lol - no, I can't do a re-import as it takes hours and downloads several gigs of data. I'm doing this at a mate's house and he as a very very small monthly limit on his ISP plan.

    I found that I can resolve the previous issue via:
    sqlite> update season
    ....> set EpisodesUnwatched = EpisodeCount;

    And voila, that sets all EpisodesUnwatched to be the same as EpisodeCount.

    Now on to the series table.
     
Loading...

Users Viewing Thread (Users: 0, Guests: 0)

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice
  • About The Project

    The vision of the MediaPortal project is to create a free open source media centre application, which supports all advanced media centre functions, and is accessible to all Windows users.

    In reaching this goal we are working every day to make sure our software is one of the best.

             

  • Support MediaPortal!

    The team works very hard to make sure the community is running the best HTPC-software. We give away MediaPortal for free but hosting and software is not for us.

    Care to support our work with a few bucks? We'd really appreciate it!