[Blogging Intensifies]

Technology, Projects, Linux, Coding, Internet of Things, Music, Books, Life...

  • About
  • Code Projects Portfolio
  • Friends
  • Photo Gallery

Code Project: VLC Portable Playlist to Text Dump

February 4, 2023

It’s kind of funny how one post can lead to another sometimes.  This one is pretty basic but it also just shows a bit how useful I find knowing my way around computer systems to be.  Yesterday I posted about my little annual music playlists.  And as part of that, I wanted to actually post the playlist. I am pretty sure there is a fairly universal “playlist file type” out there and being open source, I had assumed that VLC on my phone stored the playlists somewhere in playlist files.

That assumption was wrong, it uses a .db file.  A little portable database.  There is an option to dump this file to the root of the phone, presumably for backup purposes, but it’s also useful to just browse it like I am doing here.  The file itself can be opened and browsed with SQL Lite’s DB manager.  It’s standard databases inside for tracks and artists and playlists.

Fortunately, I have had some experience dealing with database queries, so I set about building what was needed tog et the data I wanted.  Pull the Playlist I want, in this case “2023 Best” but I could change that to do any available Playlist.  This gives the tracks by id, but the tracks themselves are stored in a separate table for media.  So that needs joined in.  The media table stores track names, but not artist names, so an additional join is needed to get the artist names.  This complicated things a bit because both the playlist table and artist table have a column “name” so more clarity needed to be added.

The result was this little query that dumps out a basic table of Artist and Song title.

SELECT Artist.name, Media.title 
FROM Playlist
Inner Join playlistmediarelation ON playlist_id=id_playlist
Inner Join Media ON id_media=media_id
Inner Join Artist ON media.artist_id=Artist.id_artist
WHERE Playlist.name = '2023 Best'
ORDER BY Artist.name

Now, I could have done some cute clever trick now to merge the two into a new column and add in a ” – ” between but it was easier to drop it all into a notepad file and do a fine/replace on the weird space character that it stick in between the Artist and track title.

The added bonus here is I can easily use this query again anytime I want to dump a Playlist to text.

Related

Posted in: Programming Projects Tagged: Coding, Database, music, SQL, SQLLite

Categories

  • collapsCat options: Array ( [title] => Categories [showPostCount] => 1 [inExclude] => exclude [inExcludeCats] => Photos, Uncategorized, mastodon-feed, goodreads [showPosts] => 0 [showPages] => 0 [linkToCat] => 1 [olderThan] => 0 [excludeAll] => 0 [catSortOrder] => ASC [catSort] => catName [postSortOrder] => ASC [postSort] => postTitle [expand] => 0 [defaultExpand] => Technology, Maker, Coding, Hobbies [debug] => 1 [postTitleLength] => 0 [catfeed] => none [taxonomy] => category [post_type] => post [postDateAppend] => after [postDateFormat] => m/d [showPostDate] => 1 [useCookies] => 1 [postsBeforeCats] => 1 [expandCatPost] => 1 [showEmptyCat] => 1 [showTopLevel] => 1 [useAjax] => 0 [customExpand] => [customCollapse] => [style] => kubrick [accordion] => 1 [title_link] => [addMisc] => 1 [addMiscTitle] => [number] => 2 [includeCatArray] => Array ( ) [expandSym] => ► [collapseSym] => ▼ ) postsToExclude: Array ( ) CATEGORY QUERY RESULTS Array ( [0] => WP_Term Object ( [term_id] => 641 [name] => 100DaysOfCode [slug] => 100daysofcode [term_group] => 0 [term_taxonomy_id] => 641 [taxonomy] => category [description] => [parent] => 172 [count] => 14 [filter] => raw ) [1] => WP_Term Object ( [term_id] => 486 [name] => Advent of Code [slug] => advent-of-code [term_group] => 0 [term_taxonomy_id] => 486 [taxonomy] => category [description] => [parent] => 172 [count] => 27 [filter] => raw ) [2] => WP_Term Object ( [term_id] => 666 [name] => AI Art [slug] => ai-art [term_group] => 0 [term_taxonomy_id] => 666 [taxonomy] => category [description] => [parent] => 153 [count] => 5 [filter] => raw ) [3] => WP_Term Object ( [term_id] => 438 [name] => Books [slug] => books [term_group] => 0 [term_taxonomy_id] => 438 [taxonomy] => category [description] => [parent] => 436 [count] => 4 [filter] => raw ) [4] => WP_Term Object ( [term_id] => 172 [name] => Coding [slug] => programming [term_group] => 0 [term_taxonomy_id] => 172 [taxonomy] => category [description] => [parent] => 153 [count] => 12 [filter] => raw ) [5] => WP_Term Object ( [term_id] => 541 [name] => Concerts [slug] => concertphotos [term_group] => 0 [term_taxonomy_id] => 541 [taxonomy] => category [description] => [parent] => 527 [count] => 7 [filter] => raw ) [6] => WP_Term Object ( [term_id] => 155 [name] => Devices (Phones and Tablets) [slug] => devices [term_group] => 0 [term_taxonomy_id] => 155 [taxonomy] => category [description] => [parent] => 166 [count] => 9 [filter] => raw ) [7] => WP_Term Object ( [term_id] => 606 [name] => Fairs [slug] => fairs [term_group] => 0 [term_taxonomy_id] => 606 [taxonomy] => category [description] => [parent] => 527 [count] => 8 [filter] => raw ) [8] => WP_Term Object ( [term_id] => 523 [name] => Feeds [slug] => feeds [term_group] => 0 [term_taxonomy_id] => 523 [taxonomy] => category [description] => [parent] => 0 [count] => 0 [filter] => raw ) [10] => WP_Term Object ( [term_id] => 436 [name] => Hobbies [slug] => hobbies [term_group] => 0 [term_taxonomy_id] => 436 [taxonomy] => category [description] => [parent] => 0 [count] => 0 [filter] => raw ) [11] => WP_Term Object ( [term_id] => 656 [name] => IOT Projects [slug] => iot [term_group] => 0 [term_taxonomy_id] => 656 [taxonomy] => category [description] => [parent] => 153 [count] => 19 [filter] => raw ) [12] => WP_Term Object ( [term_id] => 446 [name] => Language [slug] => language [term_group] => 0 [term_taxonomy_id] => 446 [taxonomy] => category [description] => [parent] => 436 [count] => 1 [filter] => raw ) [13] => WP_Term Object ( [term_id] => 524 [name] => Letterboxed [slug] => letterboxed [term_group] => 0 [term_taxonomy_id] => 524 [taxonomy] => category [description] => [parent] => 523 [count] => 284 [filter] => raw ) [14] => WP_Term Object ( [term_id] => 653 [name] => Link List [slug] => link-list [term_group] => 0 [term_taxonomy_id] => 653 [taxonomy] => category [description] => [parent] => 523 [count] => 63 [filter] => raw ) [15] => WP_Term Object ( [term_id] => 224 [name] => Linux & Open Source [slug] => linux [term_group] => 0 [term_taxonomy_id] => 224 [taxonomy] => category [description] => [parent] => 166 [count] => 6 [filter] => raw ) [16] => WP_Term Object ( [term_id] => 153 [name] => Maker [slug] => maker [term_group] => 0 [term_taxonomy_id] => 153 [taxonomy] => category [description] => [parent] => 0 [count] => 2 [filter] => raw ) [18] => WP_Term Object ( [term_id] => 530 [name] => Micro Blog [slug] => microblog [term_group] => 0 [term_taxonomy_id] => 530 [taxonomy] => category [description] => [parent] => 0 [count] => 55 [filter] => raw ) [19] => WP_Term Object ( [term_id] => 437 [name] => Music [slug] => music [term_group] => 0 [term_taxonomy_id] => 437 [taxonomy] => category [description] => [parent] => 436 [count] => 18 [filter] => raw ) [20] => WP_Term Object ( [term_id] => 395 [name] => My DIY Projects [slug] => my-diy-projects [term_group] => 0 [term_taxonomy_id] => 395 [taxonomy] => category [description] => [parent] => 153 [count] => 7 [filter] => raw ) [21] => WP_Term Object ( [term_id] => 154 [name] => Opinion/Editorial/Life [slug] => articles [term_group] => 0 [term_taxonomy_id] => 154 [taxonomy] => category [description] => [parent] => 0 [count] => 18 [filter] => raw ) [22] => WP_Term Object ( [term_id] => 491 [name] => Organizing [slug] => organizing [term_group] => 0 [term_taxonomy_id] => 491 [taxonomy] => category [description] => [parent] => 436 [count] => 7 [filter] => raw ) [23] => WP_Term Object ( [term_id] => 534 [name] => Other Photos [slug] => otherphotos [term_group] => 0 [term_taxonomy_id] => 534 [taxonomy] => category [description] => [parent] => 527 [count] => 12 [filter] => raw ) [24] => WP_Term Object ( [term_id] => 617 [name] => Outdoor and Nature [slug] => outdoor [term_group] => 0 [term_taxonomy_id] => 617 [taxonomy] => category [description] => [parent] => 527 [count] => 4 [filter] => raw ) [25] => WP_Term Object ( [term_id] => 242 [name] => PC Hardware [slug] => pcs [term_group] => 0 [term_taxonomy_id] => 242 [taxonomy] => category [description] => [parent] => 166 [count] => 6 [filter] => raw ) [27] => WP_Term Object ( [term_id] => 712 [name] => Programming Projects [slug] => projects [term_group] => 0 [term_taxonomy_id] => 712 [taxonomy] => category [description] => [parent] => 172 [count] => 11 [filter] => raw ) [28] => WP_Term Object ( [term_id] => 241 [name] => Synology NAS [slug] => synology-nas [term_group] => 0 [term_taxonomy_id] => 241 [taxonomy] => category [description] => [parent] => 166 [count] => 5 [filter] => raw ) [29] => WP_Term Object ( [term_id] => 166 [name] => Technology [slug] => technology [term_group] => 0 [term_taxonomy_id] => 166 [taxonomy] => category [description] => [parent] => 0 [count] => 10 [filter] => raw ) [30] => WP_Term Object ( [term_id] => 424 [name] => The Basement [slug] => the-basement [term_group] => 0 [term_taxonomy_id] => 424 [taxonomy] => category [description] => [parent] => 153 [count] => 6 [filter] => raw ) [31] => WP_Term Object ( [term_id] => 557 [name] => Toy Photos [slug] => toyphotos [term_group] => 0 [term_taxonomy_id] => 557 [taxonomy] => category [description] => [parent] => 527 [count] => 0 [filter] => raw ) [32] => WP_Term Object ( [term_id] => 1 [name] => Uncategorized [slug] => uncategorized [term_group] => 0 [term_taxonomy_id] => 1 [taxonomy] => category [description] => [parent] => 0 [count] => 0 [filter] => raw ) [33] => WP_Term Object ( [term_id] => 280 [name] => Windows [slug] => windows [term_group] => 0 [term_taxonomy_id] => 280 [taxonomy] => category [description] => [parent] => 166 [count] => 2 [filter] => raw ) [34] => WP_Term Object ( [term_id] => 538 [name] => Zoos [slug] => zoophotos [term_group] => 0 [term_taxonomy_id] => 538 [taxonomy] => category [description] => [parent] => 527 [count] => 12 [filter] => raw ) ) POST QUERY: POST QUERY RESULTS
  • ►Feeds (347)
    • Letterboxed (284)
    • Link List (63)
  • ▼Hobbies (30)
    • Books (4)
    • Language (1)
    • Music (18)
    • Organizing (7)
  • ▼Maker (103)
    • AI Art (5)
    • ▼Coding (64)
      • 100DaysOfCode (14)
      • Advent of Code (27)
      • Programming Projects (11)
    • IOT Projects (19)
    • My DIY Projects (7)
    • The Basement (6)
  • ►Micro Blog (55)
  • ►Opinion/Editorial/Life (18)
  • ▼Technology (38)
    • Devices (Phones and Tablets) (9)
    • Linux & Open Source (6)
    • PC Hardware (6)
    • Synology NAS (5)
    • Windows (2)
  • ►Uncategorized (0)

MastodonLinkedIn

emailInstagramInstagram

GitHubLetterboxdDuolongo
GoodreadsLast.fmElite Dangerous INARA
Lameazoid Logo


Copyright © 2023 [Blogging Intensifies].

Me WordPress Theme by themehall.com