It's been over 2 years since I wrote my guide!
I got a new phone again and wanted to import my ratings from my old one to my new one. However, I seem to have a lot more dupes than the last time I did a transfer. I have 3x as many ratings as well. So, I decided to find another way of doing this, one that did not get dupes.
The old method I used merely used song name, which is no good because when you have several copies of the same song by the same artist (for example, a Single, and the same song in an Album, or Compilation) or even different songs by different artists that have the same name, you'll have no way of differentiating between them based on name alone.
Going off the 'guid' is not an option, as Neutron likes to generate new guids for each song when recreating the database on a new install on a new phone.
After a lot of thinking, I determined that if I use song name, album name and artist name, I will have unique songs and thus no dupes when importing ratings.
I got the assistance of a user on another site who goes by the name dogcow, and he wrote the code in step 3.c in under an hour for me. He also helped me with all the commands I use in Cygwin.
The steps are as follows.
1.a Install neutron on your new phone and add your music directories. Ideally you will have a 1:1 copy of your library from your old phone. I had copied the contents of my old microSD onto a new microSD. Once you do this, your new phone's neutronmp.db will be ready for the next step.
1.b Get your neutronmp.db from your OLD phone and your neutronmp.db from your NEW phone onto your computer.
2. Install DB Browser for SQLite (or download the portable version).
3. Install cygwin.
3.a After choosing Download Site (you can leave that and everything prior at their defaults), in the top left change the View dropdown from "Pending" to "Full" and type "perl-DBD-SQLite" in the text box. Install the first one which has "Perl" as the category, as seen in the screenshot below.
Let the installer finish by clicking Next a few more times.
3.b Open Cygwin Terminal
3.c Save the following code into a .pl file. I named mine "neutron_ratings_db_transfer.pl"
Code: Select all
#!/usr/bin/perl
# this code is public domain, with my apologies, dogcow 2019
use Carp;
use DBD::SQLite;
use strict;
my $f1 = $ARGV[0];
my $f2 = $ARGV[1];
$::dbh1 = DBI->connect("dbi:SQLite:dbname=${f1}", "", "")
or die "Couldn't open $!";
$::dbh2 = DBI->connect("dbi:SQLite:dbname=${f2}", "", "")
or die "Couldn't open $!";
my $ugh = $::dbh1->prepare(" SELECT guid, user FROM rating_music; ");
$ugh->execute();
my $hh;
my %ratty;
while ($hh = $ugh->fetchrow_hashref) {
$ratty{$hh->{guid}} = $hh->{user};
}
$ugh = $::dbh1->prepare("select guid, name FROM plist_artist");
$ugh->execute();
my (%artist);
while ($hh = $ugh->fetchrow_hashref) {
$artist{$hh->{guid}} = $hh->{name};
}
my %song;
$ugh = $::dbh1->prepare("select guid, artist, title FROM plist_music_details");
$ugh->execute();
while ($hh = $ugh->fetchrow_hashref) {
next if ! defined $ratty{$hh->{guid}};
# this is terrible, but there are only 1500-ish titles so we can do a
# bunch of linear searches and not care much.
$song{$hh->{guid}} = [$artist{$hh->{artist}}, $hh->{title},
$ratty{$hh->{guid}}]; # Creates a datastructure where it combines those pieces of information together: it creates (artistname, titleofsong, rating) (lines 37-38)
}
$DB::single=1;
# now, to do the same shit for the new db.
my (%nuart, %nusong);
$ugh = $::dbh2->prepare(" SELECT guid, name FROM plist_artist; ");
$ugh->execute();
while ($hh = $ugh->fetchrow_hashref) {
$nuart{$hh->{guid}} = $hh->{name};
}
$ugh = $::dbh2->prepare("select guid, artist, title FROM plist_music_details");
$ugh->execute();
while ($hh = $ugh->fetchrow_hashref) { # As long as there are results from the executed sql statement..... { do this stuff }
my $guid = $hh->{guid}; # $hh is the variable containg the results
my $aa = $nuart{$hh->{artist}}; # If you select guid, artist, title, youll get $hh->{guid}, $hh->{artist}, $hh->{title}
my $t = $hh->{title}; # If you added album to the list of select things, for instance, you'd get $hh->{album}
for my $i (sort keys %song) {
if ($song{$i}[0] eq $aa && $song{$i}[1] eq $t) { # Line 58 there is what checks to see if the artist/title match. It's pretty simpleminded
#print "yay, match for $i\n";
my $ts = time;
print "REPLACE INTO rating_music(guid, user, auto) VALUES ($guid,", $song{$i}[2], ", $ts);\n";
}
}
}
The above code takes into account song name and artist name.
I've found that the following code, which takes into account song name and album name, works better.
Code: Select all
#!/usr/bin/perl
# this code is public domain, with my apologies, dogcow 2019
use Carp;
use DBD::SQLite;
use strict;
my $f1 = $ARGV[0];
my $f2 = $ARGV[1];
$::dbh1 = DBI->connect("dbi:SQLite:dbname=${f1}", "", "")
or die "Couldn't open $!";
$::dbh2 = DBI->connect("dbi:SQLite:dbname=${f2}", "", "")
or die "Couldn't open $!";
my $ugh = $::dbh1->prepare(" SELECT guid, user FROM rating_music; ");
$ugh->execute();
my $hh;
my %ratty;
while ($hh = $ugh->fetchrow_hashref) {
$ratty{$hh->{guid}} = $hh->{user};
}
$ugh = $::dbh1->prepare("select guid, name FROM plist_album");
$ugh->execute();
my (%album);
while ($hh = $ugh->fetchrow_hashref) {
$album{$hh->{guid}} = $hh->{name};
}
my %song;
$ugh = $::dbh1->prepare("select guid, album, title FROM plist_music_details");
$ugh->execute();
while ($hh = $ugh->fetchrow_hashref) {
next if ! defined $ratty{$hh->{guid}};
# this is terrible, but there are only 1500-ish titles so we can do a
# bunch of linear searches and not care much.
$song{$hh->{guid}} = [$album{$hh->{album}}, $hh->{title},
$ratty{$hh->{guid}}]; # Creates a datastructure where it combines those pieces of information together: it creates (artistname, titleofsong, rating) (lines 37-38)
}
$DB::single=1;
# now, to do the same shit for the new db.
my (%nualb, %nusong);
$ugh = $::dbh2->prepare(" SELECT guid, name FROM plist_album; ");
$ugh->execute();
while ($hh = $ugh->fetchrow_hashref) {
$nualb{$hh->{guid}} = $hh->{name};
}
$ugh = $::dbh2->prepare("select guid, album, title FROM plist_music_details");
$ugh->execute();
while ($hh = $ugh->fetchrow_hashref) { # As long as there are results from the executed sql statement..... { do this stuff }
my $guid = $hh->{guid}; # $hh is the variable containg the results
my $aa = $nualb{$hh->{album}}; # If you select guid, artist, title, youll get $hh->{guid}, $hh->{artist}, $hh->{title}
my $t = $hh->{title}; # If you added album to the list of select things, for instance, you'd get $hh->{album}
for my $i (sort keys %song) {
if ($song{$i}[0] eq $aa && $song{$i}[1] eq $t) { # Line 58 there is what checks to see if the artist/title match. It's pretty simpleminded
#print "yay, match for $i\n";
my $ts = time;
print "REPLACE INTO rating_music(guid, user, auto) VALUES ($guid,", $song{$i}[2], ", $ts);\n";
}
}
}
4. In the Cygwin terminal, run the following command
Code: Select all
perl 'D:\Full\Path\Including\Drive\Letter\neutron_ratings_db_transfer.pl' 'D:\Full\Path\neutronmp OLD.db' 'D:\Full\Path\neutronmp NEW.db' > 'D:\Full\Path\transfer_output.sql'
It will look like the below screenshot (my folder names are very long).
Basically, perl 'path to your pl file' 'path to your old neutronmp.db' 'path to your new neutronmp.db' > 'path to the sql file you want the command to output to'
You can name the .sql file anything you want. It will newly create it for you.
The code uses the song name, album name and artist name from the ratings_music table in the OLD neutronmp.db to find a match in the NEW neutronmp.db, and outputs the guid of the song in a line of code that is ready to be run in the DB Browser for SQLite.
5. Open the generated transfer_output.sql file with notepad++ and copy its contents
6.a Open DB Browser for SQLite and Open your NEW neutronmp.db file. Go to the Execute SQL tab and paste the contents of the transfer_output.sql into the code window.
Hit f5 to run, or click the blue play button.
6.b Click Write Changes at the top, and you're done!
This will work every time as it uses the guid of the songs based on the NEW neutronmp.db file. There is some internal comments in the neutron_ratings_db_transfer.pl file that will explain what the corresponding lines do. With this guide, transferring ratings between devices is now a much easier task, especially for people who have a lot of ratings!