[itsme]

I have a number of questions around transfering mobilesheets.db between Android and Win10 devices.

1.) I understood that file storage in MSP's database has been changed from absolute paths to paths relative to the MSP folder so that copying mobilesheets.db from an Android device to a Win10 device should work (assuming all files are stored in the same subfolders of the MSP directory). In the latest versions I still see absolute paths. Is any action required to switch my library to use relative paths? Or are relative paths still an open topic?

2.) I experimented with Sciurius' MSProTools - db_reloc to convert the paths in my mobilesheets.db from Android paths to Windows paths, so that a converted Android database should work on Win10. When I run my script, db_reloc reports
"Database version 47 does not match API version 40, proceeding anyway at d:\CmdTools\MSProTools\script\get_meta.pl line 63."
db_reloc runs successfully, the results are promising. Both MSP and db_reloc are the latest versions. Any need to change something? "database version 47" and "API version 40" refer to MSP, correct?

3.) Not only db_reloc, also MSPs synchronization and backup/restore seem to require compatible mobilesheets.db versions. Where can I find the database version in mobilesheets.db? Wouldn't it be useful to display the version of mobilesheets.db in MSP, e.g. in Settings - About

[sciurius]

1. As I understand it, the relocation is no longer necessary (even unwanted) since path names are stored relative to the library provided the files are, indeed, located in the MSPro library folder.

2. The mobilesheets database has a version number that reflects the internal structure of the database. Every time the structure is changed (tables are added, columns change, and so on) this number is incremented. The MSProTools were developed when the database version was 40, and the current database is version 47. The warning is given to indicate that there may be problems, for example when the tools would refer to a table that no longer exists. Fortunately, except for the annotations, most tables and data that the MSProTools use are still where they used to be so the tools still mostly work.

3. To inspect the database version, use

     sqlite3 mobilesheets.db "PRAGMA user_version"

Whether to display this number in 'Settings - About' is up to Mike.


If you encounter strange things with the MSProTools, please let me know or file an issue at GitHub.

[itsme]

Thanks Johan for your answers.

1.) I tried copying an unchanged mobilesheets.db from my Android tablet to my Win10 convertible.
"Find missing files" reports all songs as missing with paths like /storage/emulated/legacy/...
This corresponds to the result of get_meta.pl that shows absolute Android paths for an Android mobilesheets.db and absolute Windows paths for a mobilesheets.db created on Win10.
Note: most of my songs are in subdirectories of the MSP folder in internal memory. There are still some exceptions that are stored on the external SD card.

2.) / 3.) 
sqlite3 mobilesheets.db "PRAGMA user_version" returns "47"
so the output of db_reloc.pl is correct

2.) My batch file with db_reloc.pl still works fine. If I use it to modify mobilesheets.db before copying, MSP reports no missing files.

So my requests to Mike:
- please let me know what the status is regarding "relative paths"
- if the database version is relevant, please display it somewhere within MSP

[Zubersoft]

Relative paths are currently being used. If your library is not using relative paths, it means that something prevented a relative path from being used when the paths were converted in one of the previous updates, such as them not being underneath the storage location. If you believe all of your files are under the storage location, then you can run a query on the database to strip the storage location from all the paths in the Files table. I can help you do this if you send me the database.

I really don't want to display the database number as it doesn't mean anything useful to 99% of users. Yes it is displayed when performing a library synchronization to show that there is a version mismatch, but I could just as well put something like, "You are running version 2.3.7 and the database was created with version 2.2.8 which is not compatible". That just requires more tracking on my part in the code (i.e. associating database versions with application versions) and isn't useful most of the time.  I could even opt not to show the database version and just indicate that the user must update both devices to the latest version available.

Mike

[itsme]

Thank you for the quick response.
Probably it is caused by files that are not in the MSP folder. Or I might have missed an update step because I skipped an important MSP version.

I will have to move all files into the MSP folder and try again. But that will take some time because of upcoming gigs and rehearsals.
Could you please post the sql query that I have to run to modify mobilesheets.db? I suppose that I'm able to do that on my own.

If I have further questions I'll caome back to you.

[Zubersoft]

Code:

UPDATE FILES SET Path = replace( Path, '/storage/emulated/legacy/', '' ) WHERE Path LIKE '/storage/emulated/legacy/%';

Replace "/storage/emulated/legacy/" with whatever your storage location is that you need to strip from the paths. You will also want to do this for audio files if you have those stored under the storage location. For that query, it would be

UPDATE AudioFiles SET File = replace( File, '<storage location>', '' ) WHERE File LIKE '<storage location>/%';


Let me know if you run into any problems using those queries. You probably already planned on doing this, but always make a copy of mobilesheets.db before running queries like that on it in case something goes wrong and you want to revert it.

Mike

[itsme]

After stripping the file paths I can copy mobilesheets.db from Android to Win10 and it works fine. Thank you guys for your support.

@Sciurius:

I used db_reloc to modify the file paths in the database.

db_reloc.pl ...--src="/storage/emulated/legacy/_MobileSheets/" --dst=""
was not possible as --dst="" is considered as missing and causes the error message "Option dst requires an argument"

As a workaround I had to duplicate all my replace commands and keep the last subfolder, like:
db_reloc.pl ... --src="/storage/emulated/legacy/_MobileSheets/Songs" --dst="Songs"
db_reloc.pl ... --src="/storage/emulated/legacy/_MobileSheets/Fkbk0" --dst="Fkbk0"
and so on.
That works for me as I have to handle only three subfolders and it is done now.

But maybe you can allow --dst="" so that simply removing a part of a path is possible. It might be helpful for others and in other cases.