restore only adds shows but not watch status #2

jmichael2497 · 2023-05-15T19:48:26Z

tested on LineageOS (A13) Moto x4 migrating from DroidShows (F-Droid) 7.11.2

not sure if restore is supposed to be working again yet, lacking changelog or mention of any other forum to discuss (this github About links to 404 http://ltguillaume.github.io/DroidShows which seems meant to point upstream github, and in app still links to XDA for DroidShows)

i did test out multiple versions starting from v4 where it switched to its own unique name not trying to install over DroidShows and few around there, with weird always backup before restoring bug that ended up restoring empty new backup, or not waiting for reply from TMDB so everything fast failed...

then v18 and latest v22 from a few months ago, which at least downloads all the shows (except a foreign miniseries which i deleted to make sure that didn't cause issues), but no watch status update (or pin status, but does have active vs archive correct) after taking 15min to download 90shows from my almost 5MB db (only able to complete with screen kept on).

hopefully the restore function gets... restored before i run out of show updates, since any further attempts in older apps is getting partial data and deleting existing older seasons (but i could just manually go through marking the status in the 90 shows).

thanks for keeping this alive, cheers.

warren-bank · 2023-05-15T22:10:14Z

just to be clear, can you confirm that this is what you've already tried?

in DroidShows v7.11.2
- export database
in TV Tracker v8.0.22
- import database (from DroidShows)
- keep screen on (ie: phone fully awake since the migration process doesn't bother to set a wakelock) while database migration occurs

when migration finally completes..

is there a toast that displays any error messages?

could it be possible to get a copy?..
of both of your:

DroidShows database (before migration)
TV Tracker database (after migration)
- obtained by exporting the database from TV Tracker after migration has finished

warren-bank · 2023-05-15T22:22:22Z

correction.. the migration process does set wakelocks

leaving the screen on shouldn't be necessary..
though it's still always a good idea

jmichael2497 · 2023-05-16T05:16:22Z

yes, you understood correctly on what i did.

i did keep the screen on as it seemed otherwise power saving kicked in and stopped the app with an error message, despite setting app to be unrestricted in battery (maybe because i always keep battery saver mode active)...

although now that i think of it, i may have misattributed the error about the single foreign show it couldn't find, and the lack of watch status as being a migration failure from power management closing it.

i will test again now, to verify as it should only take 15min with screen on and that one show already removed from the source backup.

(also if you have a way to share the source db through private message?)

jmichael2497 · 2023-05-16T06:35:56Z

i plugged in phone to power in addition to turning off battery optimization for the app, just in case, and it completed... eventually (distracted watching a show, still not 1/3 of way after 30min, but done within 50min.

no toast or log just opened up to blank screen, which i'd encountered in an earlier test, so exited and reopened, just has all shows without watched status transferred (nor pin status, but does have active vs archive correct), exited and reopened, no change.

original db source is 4.8MB, after import and re-export now 4.9MB.

i will go ahead and setup the temporary share in a few minutes.

warren-bank · 2023-05-16T07:07:14Z

OK.. I've got the db files.
I'll take a peek and probably re-run the migration.

I'll be honest..
I'm very tempted to carve out the code for the database migration from the app..
and make a tiny standalone command-line tool..
as a jar file that can run with JRE on a desktop/laptop..
so it can run faster and without any Android battery optimization shenanigans.

warren-bank · 2023-05-16T07:26:33Z

just quickly inspecting the contents of the 2x SQLite db files (using: DB Browser for SQLite)..
I can see that migration didn't complete by the fact that the following 2x tables still exist:

tmdb_migration_series
tmdb_migration_episodes

These are temporary tables that contain data from the old db that will need to be merged back into the new db.. such as, for example, the timestamps for when episodes had been seen. This merging process is performed after all of the series/episode data has been downloaded from the API and used to populate tables in the new db.

jmichael2497 · 2023-05-16T07:33:37Z

i forgot i had the portable version of that SQL browser on here from tinkering with a podcast db... but i noticed google drive offered to open the files directly in browser, and was wondering about those 2 migration tables as well.

after restore of the source version with the one ambiguous to match show removed ahead of time, i just ended up with no floating window indication that things were still running after the x/90 window completed.

while a standalone migration tool might be nice, definitely would make it an advanced user solution, which would be fine for me, but... then it loses accessibility for a lot of folks.

warren-bank · 2023-05-16T07:39:41Z

I could be wrong about this, as I haven't looked at the code or used it in quite a while..
but I'm pretty sure that (from a UI standpoint) the migration goes:

alert dialog that shows the API download process with series-level granularity (ie: downloading 1 of 100)
alert dialog closes.. which is when the merging of temporary tables begins.. which is a fairly quick process as all data is now local
the list of series in the app updates with the fresh data
done
???
profit

jmichael2497 · 2023-05-16T07:44:06Z

well i've got a few shows in the source db to keep me entertained for at least a month before i run out of trackable data and can't refresh from TVDB, i should be off to sleep myself, so no hurry, cheers for keeping this app going 👍

warren-bank · 2023-05-16T07:46:53Z

I'll run the migration here.. and let you know when I have a db file that you can use. When ready.. if you want to grant me write permission to your Google Drive directory.. I can upload it privately.

jmichael2497 · 2023-05-16T18:32:28Z

i have at least one friend i got started using DroidShows (and also happens to be an advanced user that wouldn't mind running a separate conversion tool)...

and you probably don't want to be doing everyone's conversion manually, so thank you for the offer, though it would be more beneficial to have a reproducible working method.

if you can confirm the f-droid 7.11.2 db backup can restore into the v22 build with watch status, and approx how long it takes... then i'll give it another try myself or try to assist with more data and testing newer builds.

jmichael2497 · 2023-05-16T18:49:30Z

also potential room for migration optimization might involve the media cover art pulled is my guess, because i'm noticing my typical monthly wifi usage with DroidShows is around 15MB...

but currently with a few import attempts since reinstall of A4T v22 shows 516MB download (split almost half foreground vs background), and 23MB upload.

once you can confirm the import with my db worked correctly for you, i'll uninstall/reinstall, and reproduce to get precise wifi usage stats for you.

warren-bank · 2023-05-16T19:41:25Z

I did a fresh install of the app on my tv box.. started a migration.. and went to bed;
the tv box never goes to sleep because I tell it to ignore HDMI CEC.. and I never turn it off.

When I woke up I noticed 2 things:

the migration appears to have finished successfully
- I can upload the db file to you, if you want
my Verizon 5G modem updated its firmware overnight.. and wiped out all of my static IP assignments
- I have every device in the house statically assigned.. and they all talk to each other
- the new firmware reserves a huge block of IPs for its own purposes, which completely breaks my LAN
- so.. I'm trying to deal with that at the moment

jmichael2497 · 2023-05-16T22:27:19Z

curious what A# version your tv box is, but if the import of my DroidShows source file worked for you, showing viewed status and all, then i will try one more reinstall and import on my A13 based mobile tonight (and maybe A9 virtualbox if i still have that quirky image saved).

warren-bank · 2023-05-16T22:54:52Z

the tv box is Android 7.1.2..
just a super cheap Chinese box with a full version of AOSP..
which works great and does everything I ask it to do :)

truthfully, I'd probably still be using my old tv box with Android 4.4 if I could update its System WebView..
that was the only thing about it that made it obsolete.

jmichael2497 · 2023-05-17T23:59:19Z

using conditions of plugged into power and force screen staying on, after half hour and under 150MB for my 90 shows:

immediately visible and with watched status but not pins using my LOS A11 mobile.

not immediately presenting list of shows until at least switching screens in app from active to archive or log, still no status or pin, using my LOS A13 mobile.

my theory was maybe SQLite and/or OS version related, worked with A11 (SQLite 3.28.0) but not A13 (SQLite 3.32.2), see chart and changelogs:

https://stackoverflow.com/questions/2421189/version-of-sqlite-used-in-android/4377116#4377116

after creating the updated db in older device copied and does instant import in new device, only needing to download covers.

noted that few shows with differing opinions on season count or which season an episode goes in will match to season and episode number instead of name, so... oh well, close enough for me.

warren-bank · 2023-05-18T10:01:07Z

Today, I ended up writing a migration tool for the command-line.
It uses:

JRE
- I tested with Java 11
SQLite native library
- I tested with 3.42.0 for Win64,
  which is included in both the repo and release

The README gives some advanced usage instructions,
but the simplest way to use the tools is to:

download the release zip bundle
unzip
copy DroidShows.db from phone to the directory: tests/01/data
run: tests/01/run-test-migration.bat
wait for the terminal to close when complete
copy TV-Tracker.db to phone from the directory: tests/01/data
import TV-Tracker.db to TV-Tracker app
wait for thumbnails to download and resize

jmichael2497 · 2023-05-18T17:19:25Z

(edit:nvm, TVDB v1 API still broken, after full refresh many other shows broken missing earlier or random seasons! though my most messed up show even restored earlier watched dates after the earlier seasons came back with latest season and extras quadrupled again!)

so i guess that doesn't give more time to smooth out the TMDB fork import issues by maybe including specific SQLite library for consistency in app?

(though my friends keep mentioning should try something called ai copilot to help modernize code for best practices and catching common mistakes, and something about coworkers using that to program via tabbing auto-complete).

warren-bank · 2023-05-18T18:48:36Z

Just a quick heads up..
v1.0.0 of the JRE migration tool (released last night) is localized for english only.

Adding support for all languages shouldn't take very long..
so today I plan to release a v1.1.0 that takes an optional 3rd parameter to specify locale..
which would default to english if not specified.

The reason that migration uses a locale is that the TMDB v3 API takes a locale parameter..
to customize its response for things like series and episode title and description.

ltguillaume · 2023-05-18T19:05:47Z

No sure if you mean the locale for synopses but this, but TheTVDB takes a locale parameter, too, and DroidShows allows for per-show setting of the locale. Are you taking this into account in the migration?

warren-bank · 2023-05-18T19:17:54Z

I don't remember.. probably not, since the set of values accepted by one service would need to be mapped onto the set of values accepted by the other.

Pretty sure migration in Android just uses the current system locale when it redownloads data for every series in the old db. It uses a localized string resource to map from the Android system locale to a value accepted by the service API.

On the command-line, this proposed 3rd parameter would directly specify a value accepted by the service API.. without any mapping.

ltguillaume · 2023-05-18T19:25:02Z

That may introduce nasty issues as well, as some shows may have (virtually) no episode listings for one language, but are complete for another. This is why I had introduced the option to set the synopsis language for each show separately.

warren-bank · 2023-05-18T19:36:43Z

tracing through the code..

code

  String query = "SELECT tmdbid, name, language, archived FROM tmdb_migration_series";

code
```
  language = c.getString(2);
```

code

  oneResult = api.addSeries(serieId, language, (archived == 1));

code

  public boolean addSeries(int serieId, String language, boolean archived, boolean ignoreMissingEpisodes) {
    TVInfo apiSeries = getApiSeries(serieId, language);
  }

code

  this.api = new TheMovieDbApi( context.getString(R.string.api_key) );

code

  private TVInfo getApiSeries(int serieId, String language) {
    try {
      String[] appendToResponse = new String[]{"external_ids", "credits", "content_ratings"};
      TVInfo apiSeries          = api.getTVInfo(serieId, language, appendToResponse);
      return apiSeries;
    }
  }

you're right..
it seems that I'm using the per-series locale string from the old db to query fresh data from the new service API..
I wouldn't be doing that had I not cross-referenced the 2 sets of values and determined that they were compatible..
so, maybe specifying a (default) locale isn't even necessary..
I'll need to trace a bit farther and see where (if anywhere) a default locale value is used during migration.

warren-bank · 2023-05-18T19:39:12Z

sufficed to say.. none of this is fresh in my mind 🤷

ltguillaume · 2023-05-18T19:54:09Z

Ah great! Then there's no trouble there, or at least it's what users would expect. There's a language set for every show, so I don't think a default language would be necessary.

warren-bank · 2023-05-18T19:58:08Z

oh, ok.. I see what the default locale value is used for..

code

  String defaultLangCode = context.getString(R.string.lang_code);

code

  queries.add("INSERT into tmdb_migration_series SELECT NULL as tmdbid, cast(id as INTEGER) as thetvdbid, serieName as name, language, passiveStatus as archived, 0 as pinned, extResources FROM series");

code

  queries.add("UPDATE tmdb_migration_series SET language = '" + defaultLangCode + "' WHERE language=NULL");

so..

first, the temporary table is populated by an INSERT-SELECT statement
then, the temporary table is sanitized
- any series that doesn't specify a locale is updated to set its value to that of the default locale

so..

this should be an unlikely occurance
always using english as a fallback default is a sane choice
I don't really see a need to make any changes..
v1.0.0 seems to be sufficient

jmichael2497 · 2023-05-20T06:44:36Z

after a day's struggle with failed LOS A11 to A12 upgrade, i gave in and formatted data, and have A4T migration test results:

confirming same behavior in now LOS A12 mobile as with different LOS A13 mobile device, neither get past adding shows, and since they include the same SQLite version...

including the migration working in your A7 (SQLite 3.9.2) device, that is more evidence pointing towards migration should work okay in A4T app up to A11 (SQLite 3.28.0)...

but A12+ (SQLite 3.32.2) must have had some breaking https://www.sqlite.org/changes.html since prior versions, and those users will need to use your separate tool for now.

warren-bank · 2023-05-20T07:59:47Z

I'll run a test migration tomorrow on a device with LineageOS 20 (Android 13)..
to see if I can reproduce the issue that you're describing.
If so, then I'll step through it with a debugger attached to determine what's causing the problem.

idk if this is in any way related..
but the Java library that I used to polyfill the native Android SQLite wrapper didn't work out of the box;
I needed to use jdb (the Java debugger) to figure out that AbstractCursor.moveToFirst() didn't work properly..
and I needed to rewrite this method to behave as expected.

warren-bank · 2023-05-20T08:37:17Z

the most likely culprit is this block of code..
which only runs on Android 12+

to be perfectly honest, this block of code is entirely unnecessary..
when I originally wrote the code for this method:

the SQL statement in this block was the original implementation
it didn't work in testing.. which is when I realized that its syntax wasn't available in versions of SQLite that were embedded in Android prior to 12
I rewrote the SQL statement to use an older (less elegant) syntax
though this older syntax would also work in Android 12+, I couldn't bring myself to deleting the newer syntax.. so I included both and allowed the version of Android to determine which is executed
at the time, I didn't have an Android 12+ device to test the SQL statement with the newer syntax
- since it's essentially untested, it's very possible that this is the code that's breaking

I apologize for this snafu..
if it turns out that the issue is this block of code, then it's entirely my fault..
I completely forgot about this.

warren-bank · 2023-05-20T09:01:39Z

oh shoot..
though I still need to test this block of code..
I see another problem, which is probably the real issue..

the SQLite changelog for v3.33.0 confirms that it adds support for UPDATE-FROM syntax
the table that shows the version of SQLite embedded into Android by API version says that the most recent update was in API 31 (Android 12) to SQLite v3.32.0

so.. I messed up;
no version of Android currently supports this syntax

ltguillaume · 2023-05-20T15:11:01Z

Could you perhaps make a quick release without the UPDATE-FROM code? I know of some people who'd like to migrate, but are on Android 13, so this code is triggered during the migration process.

warren-bank · 2023-05-20T18:28:37Z

will do.. will be done soon

ltguillaume · 2023-05-20T23:19:48Z

That's great, thank you! 🙂

warren-bank · 2023-05-21T00:59:54Z

all set.. v8.0.23 disables that block of code so all versions of Android will run the same migration SQL.

I'll revisit whether or not to re-enable this code when a newer version of Android is released that can run it.

ltguillaume · 2023-05-21T18:18:27Z

I just tried to migrate my big test database with 320 shows. I'm having a hard time believing it, but during the process, the database operations caused 5.5 GIGAbytes of disk writes and 1.2 GIGAbytes of disk reads.

How is this even possible for a database of only 9.5MB?

Also, there is a steep decrease in time necessary for show updates: the first shows migrated cost minutes, then it gradually decreases to a second per show.

warren-bank · 2023-05-21T19:56:37Z

I really don't know..
all I can tell you is that there are no unnecessary API calls being made..
and the only writing to disk involves:

the database
- which (as you say) is fairly small
images
- which download at the full poster size (whatever that is)
- is resized to 2x smaller sizes.. which are saved to disk

Off-hand, I'd be curious to know..

what proportion of the downloaded data occurs during the migration
- ex: using the command-line tool
what proportion of the downloaded data occurs during the initial update
- ie: when there are no images on disk and they all need to be downloaded and resized

Maybe it would be more efficient to produce the 2x thumbnails from an image smaller than the full size poster,
but I can't imagine the difference would be very dramatic.

Since this can now be run on the command-line (and compiles in a few seconds),
maybe I'll mess around with adding lots of additional logging..
to try to figure out if there's any inefficiency in the migration process.

update: as an added benefit to using the cli.. it's super simple to configure the JRE to tunnel all network requests through an HTTP proxy; I'm using Fiddler (Classic):

  -Dhttp.proxyHost=localhost -Dhttp.proxyPort=8888

warren-bank · 2023-05-21T21:50:36Z

here are some quick stats..
and a copy of the files used to test that produced them:

data.zip

the attached DroidShows.db was an old db file that I had originally used to test the migration process..
it only has 4x series:

all of which only have a few episodes..
so it's minimal and good to test with.

I ran its migration using the cli tool..
using Fiddler to capture the API traffic..
when complete, I exported the entire migration session from Fiddler to an HTTP archive (v1.1)..
and used my old moz-harviewer legacy Firefox addon..
to view it in Palemoon v29.2.0..
which just happens to be the version I already had handy, though any version should work.. as Palemoon is a fork of legacy Firefox

so.. to summarize:

4 series
42 episodes in total
59 network requests
285.4 KB downloaded in total from all responses
size of input db: 88 KB
size of output db: 112 KB

casperklein · 2023-05-21T22:13:51Z

I've tried the migration tool. My DroidShows.db is 8,5 MB and contains 350 tv shows. It took about 45 minutes.
PS: Some kind of status indicator while migrating would be nice (e.g. "5 of 350 tv shows migrated").

warren-bank · 2023-05-21T22:17:32Z

it writes status updates to stdout..
if you're using the windows bat script: tests/01/run-test-migration.bat
then you'll notice that it's piping its stdout (and stderr) to the log file: tests/01/run-test-migration.log

casperklein · 2023-05-21T22:22:04Z

I just noticed that myself 😆

In run-test-migration.log I see a lot exceptions similar to those two:

ExceptionType=MAPPING_FAILED, ResponseCode=0, URL=http://api.themoviedb.org/3/find/306859?api_key=xxx&external_source=tvdb_id
ExceptionType=MAPPING_FAILED, ResponseCode=0, URL=http://api.themoviedb.org/3/find/95011?api_key=xxx&external_source=tvdb_id

Should I be worried about these?

In TV-Tracker.db.log one tv show is mentioned that could not be added, which is okay for me (I'll try to add it manually).

warren-bank · 2023-05-21T22:26:40Z

I noticed those exceptions (MAPPING_FAILED) as well..
I'm about 99% sure that's because:

those requests are each asking TMDB to look up a series by its TVDB identifier
the TMDB responses are incomplete.. meaning that it doesn't contain any such mapping

then.. the migration:

uses a fallback.. and asks TMDB to look up the series by its IMDB identifier (if present in the old db)
in most cases, this works.. which allows the migration of the series to proceed

casperklein · 2023-05-21T22:43:15Z

Importing of the new TV-Tracker.db was successful 👍 All shows are there, with the exception of the one mentioned in Tracker.db.log, which was expected.

I noticed one minor issue. When viewing a season's list of episodes, german umlauts are not displayed correctly in the episode's name. For example, instead of "ö", I see two chinese? characters. Might be an unicode issue?

warren-bank · 2023-05-21T22:43:45Z

for example, in my test migration (above)..
the log file includes:

ExceptionType=MAPPING_FAILED, ResponseCode=0, URL=http://api.themoviedb.org/3/find/81560?api_key=c9eb196aaf70baf91d4ce4f0fea6a360&external_source=tvdb_id
ExceptionType=MAPPING_FAILED, ResponseCode=0, URL=http://api.themoviedb.org/3/find/82507?api_key=c9eb196aaf70baf91d4ce4f0fea6a360&external_source=tvdb_id

the TVDB identifier for each series is:

307474 = Roots
81560 = Holocauste
78874 = Firefly
82507 = Dr. Horrible's Sing-Along Blog

so..

the lookup for Roots and Firefly succeed
the lookup for Holocauste and Dr. Horrible's Sing-Along Blog fail

but..

when I make the failed requests in a web browser.. the responses do contain corresponding TMDB id values
the API client library may consider certain fields as required.. and throws an exception when missing,
but for our purposes.. all that matters is the TMDB id value

casperklein · 2023-05-21T22:45:56Z

In case, that was not your intention: you did not redact your API key in the log above.

warren-bank · 2023-05-21T22:48:48Z

I noticed one minor issue. When viewing a season's list of episodes, german umlauts are not displayed correctly in the episode's name. For example, instead of "ö", I see two chinese? characters. Might be an unicode issue?

that's interesting..
I haven't noticed this..
I'm not sure if that's because either:

none of my shows have used non-ascii characters
ths unicode issue is the result of the JRE implementation.. and doesn't happen on Android

Can you give me the title to one show that is definitely experiencing this issue?
I'll try to reproduce.. both in Android (ie: adding the show directly) and in JRE (through migration).

casperklein · 2023-05-21T22:52:25Z

Rabbit Hole. S01E03 should be "Die grosse Verschwörung", but shown as "Die grosse Verschw??g"
Yellowstone. S05E05 should be "Das Frühjahrstreffen", but shown as "Das Fr??hrstreffen"

warren-bank · 2023-05-21T22:58:23Z

you did not redact your API key in the log above

it's in the source code.. not a huge secret 🤷

warren-bank · 2023-05-21T23:17:03Z

the unicode issue must be the result of using the JRE for migration..
in the Android app, I just:

added: Yellowstone
noticing that the episode title was in English.. and that your system locale must be German:
- open the 3-dot menu next to the Yellowstone series in the list
- click: Synopsis language (en)
- select: German
- wait for update
now, S05E05 title is in German.. and its unicode is correct
- no issue with mangled ascii

so.. since Java isn't my "first language"..
I'll need to Google what the deal is with Java and unicode.

warren-bank · 2023-05-21T23:26:12Z

this SO question (1st one I looked at) has an interesting comment:

Since you are using Java (which uses UTF-16 encoding for strings), you should make sure, that your SQLite DB is set to use UTF-16 as well. This can be done when opening a new database, or set by using PRAGMA encoding. This is based on the assumption, that your Java library doesn't perform any encoding itself.

warren-bank · 2023-05-22T00:23:09Z

https://github.com/warren-bank/Android-Tiny-Television-Time-Tracker/blob/tmdb/008.00.23-09API/android-studio-project/TV-Tracker/src/main/java/com/github/warren_bank/tiny_television_time_tracker/database/SQLiteStore.java#L86

  db = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READWRITE);

https://developer.android.com/reference/android/database/sqlite/SQLiteDatabase#openDatabase(java.lang.String,%20android.database.sqlite.SQLiteDatabase.CursorFactory,%20int)

https://github.com/warren-bank/Android-Tiny-Television-Time-Tracker/blob/db-migration-tool/001.00.00-11JRE/libs/sources/android/database/sqlite/SQLiteDatabase.java#L38

  public static SQLiteDatabase openDatabase(String dbQname, Object object, int i) throws SQLiteException {
    final int res = SQLite.sqlite3_open_v2(dbQname, ppDb, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, null);
  }

https://www.sqlite.org/c3ref/open.html
https://www.sqlite.org/c3ref/prepare.html

https://github.com/warren-bank/Android-Tiny-Television-Time-Tracker/blob/db-migration-tool/001.00.00-11JRE/libs/sources/android/database/sqlite/SQLiteDatabase.java#L78

  public Pointer prepare(String sql, Object[] makeArgListQueryString) throws SQLiteException {
    Pointer pSql = SQLite.nativeString(sql);
  }

https://github.com/warren-bank/Android-Tiny-Television-Time-Tracker/blob/db-migration-tool/001.00.00-11JRE/libs/sources/android/database/SQLite.java#L107-L113

  public static Pointer nativeString(String sql) { // TODO Check encoding?
    byte[] data = sql.getBytes();
    final Pointer pointer = new Memory(data.length + 1);
    pointer.write(0, data, 0, data.length);
    pointer.setByte(data.length, (byte) 0);
    return pointer;
  }

this looks like the culprit..
unicode won't work off-the-shelf..
the database is UTF-8..
the sql String is UTF-16..
sql.getBytes() is a bug..
sql.getBytes(StandardCharsets.UTF_8) should fix it..
TBD..

warren-bank · 2023-05-22T00:27:52Z

just a quick check whether the polyfill library where this bug occurs has been fixed after the tagged version being used..

..no change;
hasn't been fixed upstream

warren-bank · 2023-05-22T01:47:33Z

will be pushing a new release in a few minutes..
that line of code was the culprit..
new version will fix unicode characters.

huge thanks for reporting that issue!

update:

v1.0.1 is available
if you compare the TV-Tracker.db in data.zip that was produced in the test migration using v1.0.0 to
TV-Tracker.zip that was produced with the same input db using v1.0.1.. you'll see that much of the text in the former was mangled (because 3 of the 4 series specified a non-english language).. whereas all of the unicode text in the latter is fixed

casperklein · 2023-05-22T10:13:33Z

I can confirm, that now the unicode problem is fixed 🎉

While migrating, there might be a rate-limit or something else, which causes some requests (16 of 350 in my case) to fail:

Downloading series: Severance
no results
Downloading series: Squid Game
no results

After importing TV-Tracker.db, those shows are missing the covers. However, updating one affected show or all together fixes the problem 👍

warren-bank · 2023-05-22T13:47:51Z

I just released another update.. v1.0.2

It fixes the problem with the TMDB API client..
that could cause it to ignore the results when attempting to lookup the ID of a series by its corresponding TVDB ID..
and resulting in the MAPPING_FAILED log messages.

"Fix" is a bit strong..
it just removes the parts that weren't working, because they were unused anyway.

The following notes are included mainly for my own future reference…

  --------------------------------------------------------------------------------

  succeed:
  =====
  http://api.themoviedb.org/3/find/307474?api_key=c9eb196aaf70baf91d4ce4f0fea6a360&external_source=tvdb_id
  =====
  {
      "movie_results": [],
      "person_results": [],
      "tv_results": [{
          "adult": false,
          "backdrop_path": "/zIsEKq9n0DEk3gUJGmt3cWCEiKl.jpg",
          "id": 66606,
          "name": "Roots",
          "original_language": "en",
          "original_name": "Roots",
          "overview": "An adaptation of Alex Haley's \"Roots\", chronicling the history of an African slave, Kunta Kinte sold to America and his descendants.",
          "poster_path": "/nBYRp5pL1FPEfMpfDgfM1Fo4oCS.jpg",
          "media_type": "tv",
          "genre_ids": [18, 10759],
          "popularity": 13.774,
          "first_air_date": "2016-05-30",
          "vote_average": 6.886,
          "vote_count": 149,
          "origin_country": ["US"]
      }],
      "tv_episode_results": [],
      "tv_season_results": []
  }

  --------------------------------------------------------------------------------

  succeed:
  =====
  http://api.themoviedb.org/3/find/78874?api_key=c9eb196aaf70baf91d4ce4f0fea6a360&external_source=tvdb_id
  =====
  {
      "movie_results": [],
      "person_results": [],
      "tv_results": [{
          "adult": false,
          "backdrop_path": "/iP9Xjlc8KRmXE0kVMTaJRaN0umh.jpg",
          "id": 1437,
          "name": "Firefly",
          "original_language": "en",
          "original_name": "Firefly",
          "overview": "In the year 2517, after the arrival of humans in a new star system, follow the adventures of the renegade crew of Serenity, a \"Firefly-class\" spaceship. The ensemble cast portrays the nine characters who live on Serenity.",
          "poster_path": "/vZcKsy4sGAvWMVqLluwYuoi11Kj.jpg",
          "media_type": "tv",
          "genre_ids": [18, 10759, 10765],
          "popularity": 35.575,
          "first_air_date": "2002-09-20",
          "vote_average": 8.323,
          "vote_count": 1756,
          "origin_country": ["US"]
      }],
      "tv_episode_results": [],
      "tv_season_results": []
  }

  --------------------------------------------------------------------------------

  fail:
  =====
  http://api.themoviedb.org/3/find/81560?api_key=c9eb196aaf70baf91d4ce4f0fea6a360&external_source=tvdb_id
  =====
  {
      "movie_results": [],
      "person_results": [],
      "tv_results": [{
          "adult": false,
          "backdrop_path": "/1OLWHNAfJF48d08JJI4qUZH3bSJ.jpg",
          "id": 15571,
          "name": "Holocaust",
          "original_language": "en",
          "original_name": "Holocaust",
          "overview": "Berlin, Germany, 1935. The day Karl Weiss, a Jewish painter, and Inga Helms, a Christian woman, marry, is the one in which both of them and the entire Weiss family are caught up in the maelstrom of the Nazi regime, the storms of World War II and the horrors of the criminal Final Solution, the Holocaust, the Shoah; while Erik Dorf, an ambitious lawyer, undertakes his fall into hell at the hands of the sinister Reinhard Heydrich.",
          "poster_path": "/gK1gnzIOwZWuTqp4HGEPmjTMijg.jpg",
          "media_type": "tv",
          "genre_ids": [18, 10768],
          "popularity": 6.732,
          "first_air_date": "1978-04-16",
          "vote_average": 7.5,
          "vote_count": 55,
          "origin_country": ["US"]
      }],
      "tv_episode_results": [{
          "id": 445875,
          "name": "Host: Jimmy Durante",
          "overview": "Host: Jimmy Durante   --Ethel Merman    --Sugar Ray Robinson    --Vikki Carr    --Bill Dana    --Leland Palmer    --Hendra \u0026 Ullett",
          "media_type": "tv_episode",
          "vote_average": 0.0,
          "vote_count": 0,
          "air_date": "1968-12-14",
          "episode_number": 11,
          "production_code": "",
          "runtime": 60,
          "season_number": 6,
          "show_id": 11142,
          "still_path": null
      }],
      "tv_season_results": []
  }

  --------------------------------------------------------------------------------

  fail:
  =====
  http://api.themoviedb.org/3/find/82507?api_key=c9eb196aaf70baf91d4ce4f0fea6a360&external_source=tvdb_id
  =====
  {
      "movie_results": [],
      "person_results": [],
      "tv_results": [{
          "adult": false,
          "backdrop_path": "/fJa29nluTKy4qOc5kyyGZfCuDDc.jpg",
          "id": 5739,
          "name": "Dr. Horrible's Sing-Along Blog",
          "original_language": "en",
          "original_name": "Dr. Horrible's Sing-Along Blog",
          "overview": "Aspiring super-villain Dr. Horrible wants to join the Evil League of Evil and win the girl of his dreams, but his nemesis, Captain Hammer, stops him at every turn.",
          "poster_path": "/wPXpC4AJHlwnVk5AtlBgsd0497c.jpg",
          "media_type": "tv",
          "genre_ids": [35, 18, 10765],
          "popularity": 9.594,
          "first_air_date": "2008-07-15",
          "vote_average": 7.549,
          "vote_count": 460,
          "origin_country": ["US"]
      }],
      "tv_episode_results": [{
          "id": 143384,
          "name": "Lost and Floundering",
          "overview": "Paul and Barry are put in charge of a lost and found office.",
          "media_type": "tv_episode",
          "vote_average": 0.0,
          "vote_count": 0,
          "air_date": "2001-03-07",
          "episode_number": 12,
          "production_code": "",
          "runtime": 20,
          "season_number": 13,
          "show_id": 2082,
          "still_path": null
      }],
      "tv_season_results": []
  }

  --------------------------------------------------------------------------------

  https://github.com/warren-bank/Android-Tiny-Television-Time-Tracker/blob/tmdb/008.00.23-09API/android-studio-project/TV-Tracker/src/main/java/com/github/warren_bank/tiny_television_time_tracker/database/Update.java#L643-L651
    String thetvdbid = thetvdbids.get(i);
    String imdbid    = imdbids.get(i);
    int tmdbid;

    Map<String,String> origins = new HashMap<String, String>();
    origins.put("thetvdb", thetvdbid);
    origins.put("imdb",    imdbid);

    tmdbid = api.findSeriesByExternalId(origins);

  https://github.com/warren-bank/Android-Tiny-Television-Time-Tracker/blob/tmdb/008.00.23-09API/android-studio-project/TV-Tracker/src/main/java/com/github/warren_bank/tiny_television_time_tracker/api/ApiGateway.java#L69
    this.api = new TheMovieDbApi( context.getString(R.string.api_key) );
  https://github.com/warren-bank/Android-Tiny-Television-Time-Tracker/blob/tmdb/008.00.23-09API/android-studio-project/TV-Tracker/src/main/java/com/github/warren_bank/tiny_television_time_tracker/api/ApiGateway.java#L80
    public int findSeriesByExternalId(Map<String,String> origins, String language)
  https://github.com/warren-bank/Android-Tiny-Television-Time-Tracker/blob/tmdb/008.00.23-09API/android-studio-project/TV-Tracker/src/main/java/com/github/warren_bank/tiny_television_time_tracker/api/ApiGateway.java#L98
    public int findSeriesByExternalId(String origin, String externalId, String language)
  https://github.com/warren-bank/Android-Tiny-Television-Time-Tracker/blob/tmdb/008.00.23-09API/android-studio-project/TV-Tracker/src/main/java/com/github/warren_bank/tiny_television_time_tracker/api/ApiGateway.java#L111
    public int findSeriesByExternalId(ExternalSource origin, String externalId, String language) {
      FindResults response = api.find(externalId, origin, language);
    }

  https://github.com/warren-bank/Android-Tiny-Television-Time-Tracker/blob/tmdb/008.00.23-09API/android-studio-project/libs/api-themoviedb/src/main/java/com/omertron/themoviedbapi/TheMovieDbApi.java#L36
    import com.omertron.themoviedbapi.methods.TmdbFind;
  https://github.com/warren-bank/Android-Tiny-Television-Time-Tracker/blob/tmdb/008.00.23-09API/android-studio-project/libs/api-themoviedb/src/main/java/com/omertron/themoviedbapi/TheMovieDbApi.java#L48
    import com.omertron.themoviedbapi.model.FindResults;
  https://github.com/warren-bank/Android-Tiny-Television-Time-Tracker/blob/tmdb/008.00.23-09API/android-studio-project/libs/api-themoviedb/src/main/java/com/omertron/themoviedbapi/TheMovieDbApi.java#L665
    public FindResults find(String id, ExternalSource externalSource, String language) throws MovieDbException {
      return tmdbFind.find(id, externalSource, language);
    }

  https://github.com/warren-bank/Android-Tiny-Television-Time-Tracker/blob/tmdb/008.00.23-09API/android-studio-project/libs/api-themoviedb/src/main/java/com/omertron/themoviedbapi/methods/TmdbFind.java#L39
    public class TmdbFind extends AbstractMethod
  https://github.com/warren-bank/Android-Tiny-Television-Time-Tracker/blob/tmdb/008.00.23-09API/android-studio-project/libs/api-themoviedb/src/main/java/com/omertron/themoviedbapi/methods/TmdbFind.java#L72-L86
    public FindResults find(String id, ExternalSource externalSource, String language) throws MovieDbException {
      TmdbParameters parameters = new TmdbParameters();
      parameters.add(Param.ID, id);
      parameters.add(Param.LANGUAGE, language);
      parameters.add(Param.EXTERNAL_SOURCE, externalSource.getPropertyString());

      URL url = new ApiUrl(apiKey, MethodBase.FIND).buildUrl(parameters);
      String webpage = httpTools.getRequest(url);

      try {
        return MAPPER.readValue(webpage, FindResults.class);
      } catch (IOException ex) {
        throw new MovieDbException(ApiExceptionType.MAPPING_FAILED, "Failed to get find results", url, ex);
      }
    }

  https://github.com/warren-bank/Android-Tiny-Television-Time-Tracker/blob/tmdb/008.00.23-09API/android-studio-project/libs/api-themoviedb/src/main/java/com/omertron/themoviedbapi/methods/AbstractMethod.java#L23
    import com.fasterxml.jackson.databind.ObjectMapper;
  https://github.com/warren-bank/Android-Tiny-Television-Time-Tracker/blob/tmdb/008.00.23-09API/android-studio-project/libs/api-themoviedb/src/main/java/com/omertron/themoviedbapi/methods/AbstractMethod.java#L71
    protected static final ObjectMapper MAPPER = new ObjectMapper();

  https://github.com/warren-bank/Android-Tiny-Television-Time-Tracker/blob/tmdb/008.00.23-09API/android-studio-project/libs/api-themoviedb/src/main/java/com/omertron/themoviedbapi/model/AbstractJsonMapping.java#L35
    public abstract class AbstractJsonMapping
  https://github.com/warren-bank/Android-Tiny-Television-Time-Tracker/blob/tmdb/008.00.23-09API/android-studio-project/libs/api-themoviedb/src/main/java/com/omertron/themoviedbapi/model/FindResults.java#L34
    public class FindResults extends AbstractJsonMapping
  https://github.com/warren-bank/Android-Tiny-Television-Time-Tracker/blob/tmdb/008.00.23-09API/android-studio-project/libs/api-themoviedb/src/main/java/com/omertron/themoviedbapi/model/FindResults.java#L26
    import com.omertron.themoviedbapi.model.tv.TVEpisodeBasic;
  https://github.com/warren-bank/Android-Tiny-Television-Time-Tracker/blob/tmdb/008.00.23-09API/android-studio-project/libs/api-themoviedb/src/main/java/com/omertron/themoviedbapi/model/FindResults.java#L46-L47
    @JsonProperty("tv_episode_results")
    private List<TVEpisodeBasic> tvEpisodeResults;

  https://github.com/warren-bank/Android-Tiny-Television-Time-Tracker/blob/tmdb/008.00.23-09API/android-studio-project/libs/api-themoviedb/src/main/java/com/omertron/themoviedbapi/model/tv/TVEpisodeBasic.java#L32
    public class TVEpisodeBasic extends MediaBasic
  https://github.com/warren-bank/Android-Tiny-Television-Time-Tracker/blob/tmdb/008.00.23-09API/android-studio-project/libs/api-themoviedb/src/main/java/com/omertron/themoviedbapi/model/tv/TVEpisodeBasic.java#L24
    import com.omertron.themoviedbapi.model.media.MediaBasic;
  https://github.com/warren-bank/Android-Tiny-Television-Time-Tracker/blob/tmdb/008.00.23-09API/android-studio-project/libs/api-themoviedb/src/main/java/com/omertron/themoviedbapi/model/tv/TVEpisodeBasic.java#L36-L49
    @JsonProperty("air_date")
    @JsonProperty("season_number")
    @JsonProperty("episode_number")
    @JsonProperty("name")
    @JsonProperty("overview")
    @JsonProperty("still_path")
    @JsonProperty("show_id")

  https://github.com/warren-bank/Android-Tiny-Television-Time-Tracker/blob/tmdb/008.00.23-09API/android-studio-project/libs/api-themoviedb/src/main/java/com/omertron/themoviedbapi/model/media/MediaBasic.java#L34
    public class MediaBasic extends AbstractJsonMapping
  https://github.com/warren-bank/Android-Tiny-Television-Time-Tracker/blob/tmdb/008.00.23-09API/android-studio-project/libs/api-themoviedb/src/main/java/com/omertron/themoviedbapi/model/media/MediaBasic.java#L38-L50
    @JsonProperty("id")
    @JsonProperty("backdrop_path")
    @JsonProperty("poster_path")
    @JsonProperty("popularity")
    @JsonProperty("vote_average")
    @JsonProperty("vote_count")

  --------------------------------------------------------------------------------

  "tv_episode_results" fields in JSON response:
    "id"
    "name"
    "overview"
    "media_type"
    "vote_average"
    "vote_count"
    "air_date"
    "episode_number"
    "production_code"
    "runtime"
    "season_number"
    "show_id"
    "still_path"

  missing fields:
    "backdrop_path"
    "poster_path"
    "popularity"

  --------------------------------------------------------------------------------

  https://fasterxml.github.io/jackson-databind/javadoc/2.13/com/fasterxml/jackson/databind/ObjectMapper.html
  http://fasterxml.github.io/jackson-annotations/javadoc/2.13/com/fasterxml/jackson/annotation/JsonIgnoreProperties.html#ignoreUnknown--

  https://github.com/FasterXML/jackson-annotations/wiki/Jackson-Annotations
  https://github.com/FasterXML/jackson-databind/wiki/Databind-annotations
  https://github.com/FasterXML/jackson-databind/wiki/Deserialization-Features

  https://www.baeldung.com/jackson-annotations#jackson-deserialization-annotations
  https://www.baeldung.com/jackson-deserialization
  https://www.baeldung.com/jackson-deserialize-json-unknown-properties
  https://www.baeldung.com/jackson-ignore-null-fields

  --------------------------------------------------------------------------------

  https://github.com/Omertron/api-themoviedb/issues

  --------------------------------------------------------------------------------

jmichael2497 mentioned this issue May 15, 2023

[FR] Wishlist, New Fork ltguillaume/droidshows#108

Open

ltguillaume mentioned this issue May 16, 2023

Duplicate entries ltguillaume/droidshows#79

Closed

restore only adds shows but not watch status #2

restore only adds shows but not watch status #2

Comments

jmichael2497 commented May 15, 2023 • edited Loading

warren-bank commented May 15, 2023

warren-bank commented May 15, 2023

jmichael2497 commented May 16, 2023 • edited Loading

jmichael2497 commented May 16, 2023 • edited Loading

warren-bank commented May 16, 2023

warren-bank commented May 16, 2023 • edited Loading

jmichael2497 commented May 16, 2023 • edited Loading

warren-bank commented May 16, 2023

jmichael2497 commented May 16, 2023

warren-bank commented May 16, 2023

jmichael2497 commented May 16, 2023 • edited Loading

jmichael2497 commented May 16, 2023 • edited Loading

warren-bank commented May 16, 2023

jmichael2497 commented May 16, 2023

warren-bank commented May 16, 2023 • edited Loading

jmichael2497 commented May 17, 2023

warren-bank commented May 18, 2023

jmichael2497 commented May 18, 2023 • edited Loading

warren-bank commented May 18, 2023 • edited Loading

ltguillaume commented May 18, 2023

warren-bank commented May 18, 2023 • edited Loading

ltguillaume commented May 18, 2023

warren-bank commented May 18, 2023 • edited Loading

warren-bank commented May 18, 2023

ltguillaume commented May 18, 2023

warren-bank commented May 18, 2023

jmichael2497 commented May 20, 2023 • edited Loading

warren-bank commented May 20, 2023

warren-bank commented May 20, 2023

warren-bank commented May 20, 2023

ltguillaume commented May 20, 2023 • edited Loading

warren-bank commented May 20, 2023

ltguillaume commented May 20, 2023

warren-bank commented May 21, 2023

ltguillaume commented May 21, 2023 • edited Loading

warren-bank commented May 21, 2023 • edited Loading

warren-bank commented May 21, 2023 • edited Loading

casperklein commented May 21, 2023

warren-bank commented May 21, 2023

casperklein commented May 21, 2023 • edited Loading

warren-bank commented May 21, 2023 • edited Loading

casperklein commented May 21, 2023 • edited Loading

warren-bank commented May 21, 2023

casperklein commented May 21, 2023

warren-bank commented May 21, 2023

casperklein commented May 21, 2023

warren-bank commented May 21, 2023

warren-bank commented May 21, 2023 • edited Loading

warren-bank commented May 21, 2023

warren-bank commented May 22, 2023

warren-bank commented May 22, 2023

warren-bank commented May 22, 2023 • edited Loading

casperklein commented May 22, 2023

warren-bank commented May 22, 2023 • edited Loading

jmichael2497 commented May 15, 2023 •

edited

Loading

jmichael2497 commented May 16, 2023 •

edited

Loading

jmichael2497 commented May 16, 2023 •

edited

Loading

warren-bank commented May 16, 2023 •

edited

Loading

jmichael2497 commented May 16, 2023 •

edited

Loading

jmichael2497 commented May 16, 2023 •

edited

Loading

jmichael2497 commented May 16, 2023 •

edited

Loading

warren-bank commented May 16, 2023 •

edited

Loading

jmichael2497 commented May 18, 2023 •

edited

Loading

warren-bank commented May 18, 2023 •

edited

Loading

warren-bank commented May 18, 2023 •

edited

Loading

warren-bank commented May 18, 2023 •

edited

Loading

jmichael2497 commented May 20, 2023 •

edited

Loading

ltguillaume commented May 20, 2023 •

edited

Loading

ltguillaume commented May 21, 2023 •

edited

Loading

warren-bank commented May 21, 2023 •

edited

Loading

warren-bank commented May 21, 2023 •

edited

Loading

casperklein commented May 21, 2023 •

edited

Loading

warren-bank commented May 21, 2023 •

edited

Loading

casperklein commented May 21, 2023 •

edited

Loading

warren-bank commented May 21, 2023 •

edited

Loading

warren-bank commented May 22, 2023 •

edited

Loading

warren-bank commented May 22, 2023 •

edited

Loading