restore only adds shows but not watch status

[jmichael2497]

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]

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]

correction.. the migration process does set wakelocks

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

[jmichael2497]

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]

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]

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]

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]

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]

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:

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

[jmichael2497]

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]

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]

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]

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]

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:

1. the migration appears to have finished successfully
   • I can upload the db file to you, if you want
2. 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]

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]

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]

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]

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]

(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]

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]

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]

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]

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]

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]

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

[ltguillaume]

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]

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]

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]

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]

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]

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]

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]

will do.. will be done soon

[ltguillaume]

That's great, thank you! 🙂

[warren-bank]

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]

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]

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]

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:

• Roots
• Holocauste
• Firefly
• Dr. Horrible's Sing-Along Blog

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]

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]

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]

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]

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]

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]

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]

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

[warren-bank]

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:

1. none of my shows have used non-ascii characters
2. 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]

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]

you did not redact your API key in the log above

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

[warren-bank]

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]

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]

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..