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

[dir]

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.

 

[RoChess]

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.

 

[ltfearme]

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.

 

[dir]

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'

 

[dir]

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:

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 
----------------------------------------------------------------------  -----------------  -----------  ----------  -----------  ------------  ----------------  --------------------  ----------------  ------------------  ------------------  ------------  -------------  ----------  -----------  -------------------  -------------------  -----------  -----------  ----------  ---------------------------  -------------  ---------------  -----------  ------------------  ------------  --------------  ----------------  ----------  -----------  ------------------  ------------  -------------  -----------  ----------  ----------
\\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					
\\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					
\\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					
\\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?

 

[ltfearme]

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.

 

[dir]

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.

 

[RoChess]

And unless you are a slave to the command-line, use a nice SQLite GUI editor, such as: http://www.sqliteexpert.com/download.html ("SQLite Expert Personal" Freeware version is what I use myself)

 

[ltfearme]

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.

 

[dir]

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.