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