Saturday, April 8, 2023

Notes on Dedupping a VLC Playlist on Android

 I have an extensive library of licensed music tracks stored on my Android SD card. I connect my phone to my car by Bluetooth, and I use VLC to play custom playlists, added to from searched track options. I usually play my core playlist in random order. Somehow my core playlist had ballooned to over 500 tracks, many of which I suspected to be duplicates. I later confirmed that there were only 188 unique tracks. I was annoyed this had happened. (Oddly, when I recently added some Carpenters tracks onto the playlist, I did get prompted in trying to add a duplicate track from my card to the same playlist, which I would expect. However, the popup query seems to suggest duplicates are allowed. I don't recall seeing popup warnings adding tracks in the past.)

I was more familiar with specific playlist files on my Windows PC. For example, I have a backlog of over 3000 Cato Institute podcast episodes and I have a playlist arranged in a certain order, which I can update after episodes are played. On Android, however, VLC stores playlists in a sqlite database.

Now SQL (structured query language) is the lingua franca for relational databases (originally developed by IBM); I took a couple of graduate school database classes at UH, taught undergrad DBMS at UTEP, and wrote a minimal manual on SQL for a research project I did with Minnie Yen (Alaska-Anchorage). In addition, I've spent almost all my post-academic career as a DBA, predominantly Oracle but some exposure to SQL Server. I really haven't dealt with sqlite but I was confident I could figure out to get what I wanted.

The basic solution was:

  • dump the media database on Android and transfer it to my PC
  • download sqlite tools to my PC
  • load/mount the database, identify the target table and columns, and execute a relevant query to identify duplicated tracks.
Note this still left a manual task of doing identified duplicate track searches on the playlist in VLC Android and deleting the duplicate references. This is largely a workaround because more direct access would likely involve rooting the phone.

In VLC Android you can dump a copy of VLC database by going to app settings, advanced settings and select the dump media DB option. On my phone, the generated file was vlc_media.db. I transferred the file to my PC. in Windows.

If your PC doesn't have the sqlite3 command, you may need to download it, e.g., sqlite-tools-win32-x86-3410200.zip from https://www.sqlite.org/download.html (pan down to Precompiled Binaries for Windows) [note the link is valid at post publication].

I unzipped the file and navigated to the folder at the command prompt.

sqlite3 [path]\vlc_media.db

where [path] is the location for your dumped media database 

SQLite version 3.41.2 2023-03-22 11:56:21
Enter ".help" for usage hints.
sqlite>

To get a list of tables:

sqlite> .tables
Album                   FolderFts_segments      Movie
AlbumFts                Genre                   Playlist
AlbumFts_content        GenreFts                PlaylistFts
AlbumFts_segdir         GenreFts_content        PlaylistFts_content
AlbumFts_segments       GenreFts_segdir         PlaylistFts_segdir
Artist                  GenreFts_segments       PlaylistFts_segments
ArtistFts               Label                   PlaylistMediaRelation
ArtistFts_content       LabelFileRelation       Settings
ArtistFts_segdir        Media                   Show
ArtistFts_segments      MediaArtistRelation     ShowEpisode
AudioTrack              MediaFts                ShowFts
Bookmark                MediaFts_content        ShowFts_content
Chapter                 MediaFts_segdir         ShowFts_segdir
Device                  MediaFts_segments       ShowFts_segments
DeviceMountpoint        MediaGroup              SubtitleTrack
File                    MediaGroupFts           Task
Folder                  MediaGroupFts_content   Thumbnail
FolderFts               MediaGroupFts_segdir    ThumbnailCleanup
FolderFts_content       MediaGroupFts_segments  ThumbnailLinking
FolderFts_segdir        Metadata                VideoTrack

To describe a table, I use: pragma table_info(my table name);

There are 3 tables I'm particularly interested in: Playlist; Media; PlaylistMediaRelation.

sqlite> pragma table_info(playlist);
0|id_playlist|INTEGER|0||1
1|name|TEXT|0||0
2|creation_date|UNSIGNED INT|1||0
3|artwork_mrl|TEXT|0||0
4|nb_video|UNSIGNED INT|1|0|0
5|nb_audio|UNSIGNED INT|1|0|0
6|nb_unknown|UNSIGNED INT|1|0|0
7|nb_present_video|UNSIGNED INT|1|0|0
8|nb_present_audio|UNSIGNED INT|1|0|0
9|nb_present_unknown|UNSIGNED INT|1|0|0
10|duration|UNSIGNED INT|1|0|0
11|nb_duration_unknown|UNSIGNED INT|1|0|0

sqlite> pragma table_info(media);
0|id_media|INTEGER|0||1
1|type|INTEGER|0||0
2|subtype|INTEGER|1|0|0
3|duration|INTEGER|0|-1|0
4|last_position|REAL|0|-1|0
5|last_time|INTEGER|0|-1|0
6|play_count|UNSIGNED INTEGER|1|0|0
7|last_played_date|UNSIGNED INTEGER|0||0
8|insertion_date|UNSIGNED INTEGER|0||0
9|release_date|UNSIGNED INTEGER|0||0
10|title|TEXT|0||0
11|filename|TEXT|0||0
12|is_favorite|BOOLEAN|1|0|0
13|is_present|BOOLEAN|1|1|0
14|device_id|INTEGER|0||0
15|nb_playlists|UNSIGNED INTEGER|1|0|0
16|folder_id|UNSIGNED INTEGER|0||0
17|import_type|UNSIGNED INTEGER|1||0
18|group_id|UNSIGNED INTEGER|0||0
19|forced_title|BOOLEAN|1|0|0
20|artist_id|UNSIGNED INTEGER|0||0
21|genre_id|UNSIGNED INTEGER|0||0
22|track_number|UNSIGEND INTEGER|0||0
23|album_id|UNSIGNED INTEGER|0||0
24|disc_number|UNSIGNED INTEGER|0||0
25|lyrics|TEXT|0||0


sqlite> pragma table_info(PlaylistMediaRelation);
0|media_id|INTEGER|0||0
1|playlist_id|INTEGER|0||0
2|position|INTEGER|0||0

The last table is where I'll find the duplicates; I use the media table to identify the track, and I use the playlist table to get the id for what I call the playlist in VLC:

sqlite> select id_playlist, name from playlist;
1|rag001
2|rag003
3|rag002
4|xmas
 
RAG001 is what I call the long playlist in VLC, so 1 is the id I what I want to use in querying PlaylistMediaRelation.

So here is the query I'm using to identify the duplicate tracks in my playlist:

sqlite> select filename from media where id_media in (select media_id from playlistmediarelation
(x1...> where playlist_id=1 group by media_id having count(*) >1);
03 If I Thought You'd Ever Change Yo.m4a
001 Feels So Right.m4a
002 Love in the First Degree.m4a
001 Rock Me Gently.m4a
01 (Where Do I Begin) Love Story.mp3
06 Speak Softly Love (Love Theme fro.mp3
11 A Song For You.mp3
03 Don't Worry Baby.mp3
001 Woman in Love.m4a
002 Somewhere [From West Side Story].m4a
[and the list goes on...)