If you’ve ever tried to inspect your playlist structure outside of MediaMonkey, you might have run into issues like missing sub-playlist hierarchies, dropped parent folder containers, missing Auto-Playlists, or IUNICODE collation errors. This script tries to address those problems while adding useful metadata metrics.
!!! IT IS RECOMMENDED THIS IS DONE ON A RECENT BACKUP OF YOUR MM.DB (not the live / productions MM.DB) !!!
Key Features
===========
1). True Tree Hierarchy (HierarchyPath & HierarchyLevel)
Uses a Recursive Common Table Expression (CTE) to reconstruct full parent-child folder paths (e.g., Root Folder \ Genre \ 80s Rock). Sub-playlists are sorted and grouped directly beneath their parent containers.
2). Complete Coverage (No Missing Playlists)
Uses LEFT JOIN operations across Playlists, PlaylistSongs, and Songs. Pure structural parent folders, empty playlists, and un-cached Auto-Playlists are preserved and explicitly labeled rather than being dropped.
3). Auto-Playlist Filter Inspection
Classifies each playlist as AutoPlaylist or Standard and extracts the raw QueryData and QueryDataJSON filter criteria for easy inspection.
4). Playlist Track Sequence & Totals
PlaylistTrackIndex: A 1-based sequential track position index for each playlist.
MaxPlaylistTracks: The total track count for that playlist (making it easy to filter for tiny or oversized playlists).
5). Dual-Level "Potential" Duplicate Detection
DuplicateInCurrentPlaylist: Identifies if the exact same song appears multiple times within the same playlist (1 = Yes, 0 = No).
DuplicateAcrossPlaylistsCount: Shows how many distinct playlists contain that specific audio file across your entire library.
6). MediaMonkey Collation Compatibility
Overrides default text sorting with COLLATE NOCASE, preventing Error: no such collation sequence: IUNICODE when running queries outside the MediaMonkey application environment.
7). Temporary Table & Zero Database Writes
Creates a session-based temp_PlaylistTracksExport table. It never writes, alters, or corrupts your main MM.DB database file, and automatically disappears when you close your connection.
How to Use
==========
Backup your database (always good practice before opening MM.DB in third-party tools).
Open the Backup MM.DB in DB Browser for SQLite.
Go to the Execute SQL tab, paste the script, and press F5 (or click Execute ▶).
The output grid will automatically display the complete dataset.
To export to Excel / CSV: Go to File > Export > Table(s) as CSV file... (or right-click the results grid and copy/export).
Code: Select all
-- ============================================================================
-- SCRIPT TITLE : MediaMonkey - Hierarchical Playlist Export with Duplicate Analysis
-- AUTHOR : Ian
-- PURPOSE : Generates a fully recursive hierarchical tree of playlists,
-- populates metadata into a temporary table, and calculates
-- duplicate statuses within single vs across multiple playlists.
-- COMPATIBILITY: SQLite 3.x, DB Browser for SQLite, MediaMonkey DB (MM.DB)
-- ============================================================================
--
-- INSTRUCTIONS & USAGE:
-- ---------------------
-- 1. Open your BACKUP MediaMonkey database (MM.DB) in DB Browser for SQLite.
-- 2. Open the "Execute SQL" tab and paste this entire script.
-- 3. Click "Execute SQL" (F5).
-- 4. To export to CSV in DB Browser for SQLite:
-- a. Run: SELECT * FROM temp_PlaylistTracksExport;
-- b. Click "File" > "Export" > "Table(s) as CSV file...".
-- c. Set options: Column names in first line = Checked, Quote = ", Separator = ,
--
-- TECHNICAL REMARKS:
-- ------------------
-- * DUPLICATE IN CURRENT PLAYLIST: Flag (0 = No, 1 = Yes) indicating if the song
-- appears more than once in the *same* playlist.
-- * DUPLICATE ACROSS PLAYLISTS COUNT: Count of distinct playlists containing
-- this song (1 = Only in 1 playlist, 2+ = Appears in N different playlists).
-- * RECURSIVE CTE: Builds 'HierarchyPath' and 'HierarchyLevel' to preserve
-- parent-child tree order across arbitrary folder nesting levels.
-- * LEFT JOIN: Preserves parent folder containers and empty playlists.
-- * TEMPORARY TABLE: Automatically dropped when your connection closes.
-- It will NOT alter, mutate, or write permanent changes to your MM.DB file.
-- * COLLATION OVERRIDE: Uses 'COLLATE NOCASE' on text sorts to prevent
-- "Error: no such collation sequence: IUNICODE" when run outside MediaMonkey.
-- ============================================================================
-- Step 1: Clean up any existing temporary table instance from this session
DROP TABLE IF EXISTS temp_PlaylistTracksExport;
-- Step 2: Create temporary table populated via Recursive CTE and Window Functions
CREATE TEMP TABLE temp_PlaylistTracksExport AS
WITH RECURSIVE PlaylistTree AS (
-- ------------------------------------------------------------------------
-- RECURSIVE CTE - ANCHOR MEMBER:
-- Identifies top-level "Root" playlists (where ParentPlaylist IS NULL or <= 0).
-- Sets base HierarchyLevel to 0 and initializes HierarchyPath with Root Name.
-- ------------------------------------------------------------------------
SELECT
IDPlaylist,
PlaylistName,
ParentPlaylist,
0 AS HierarchyLevel,
PlaylistName AS HierarchyPath
FROM Playlists
WHERE ParentPlaylist IS NULL
OR ParentPlaylist <= 0
OR ParentPlaylist NOT IN (SELECT IDPlaylist FROM Playlists)
UNION ALL
-- ------------------------------------------------------------------------
-- RECURSIVE CTE - RECURSIVE MEMBER:
-- Iteratively joins child playlists to parent playlists.
-- Increments HierarchyLevel by 1 and appends child name using ' \ ' delimiter.
-- ------------------------------------------------------------------------
SELECT
child.IDPlaylist,
child.PlaylistName,
child.ParentPlaylist,
parent.HierarchyLevel + 1 AS HierarchyLevel,
parent.HierarchyPath || ' \ ' || child.PlaylistName AS HierarchyPath
FROM Playlists child
INNER JOIN PlaylistTree parent
ON child.ParentPlaylist = parent.IDPlaylist
),
-- ----------------------------------------------------------------------------
-- PRE-AGGREGATION CTE:
-- Pre-calculates the exact count of unique playlists each IDSong belongs to across
-- the entire database. Pre-aggregating here avoids costly subqueries in main SELECT.
-- ----------------------------------------------------------------------------
SongPlaylistCounts AS (
SELECT
IDSong,
COUNT(DISTINCT IDPlaylist) AS DistinctPlaylistCount
FROM PlaylistSongs
WHERE IDSong IS NOT NULL
GROUP BY IDSong
)
SELECT
-- ------------------------------------------------------------------------
-- Playlist Tree & Identifiers
-- ------------------------------------------------------------------------
tree.HierarchyPath,
tree.HierarchyLevel,
P.IDPlayList,
P.srcPath AS PlaylistSrcPath,
P.PlaylistName,
-- Dynamic Playlist Classification (Auto vs Standard)
-- Checks IsAutoPlaylist boolean or existence of serialized criteria strings
CASE
WHEN COALESCE(P.IsAutoPlaylist, 0) = 1
OR (P.QueryData IS NOT NULL AND P.QueryData <> '')
OR (P.QueryDataJSON IS NOT NULL AND P.QueryDataJSON <> '')
THEN 'AutoPlaylist'
ELSE 'Standard'
END AS PlaylistType,
-- Auto-Playlist Filter Criteria Definitions (Raw filter payloads)
P.QueryData,
P.QueryDataJSON,
-- ------------------------------------------------------------------------
-- Playlist Sequence & Track Counts
-- ------------------------------------------------------------------------
-- Sequential track position (1, 2, 3...) within this specific playlist
CASE
WHEN PlaylistSongs.IDSong IS NULL THEN 0
ELSE ROW_NUMBER() OVER (
PARTITION BY P.IDPlayList
ORDER BY PlaylistSongs.SongOrder ASC
)
END AS PlaylistTrackIndex,
-- Total number of tracks contained within this playlist (0 if empty/parent)
COUNT(PlaylistSongs.IDSong) OVER (
PARTITION BY P.IDPlayList
) AS MaxPlaylistTracks,
-- ------------------------------------------------------------------------
-- Duplicate Metrics Logic
-- ------------------------------------------------------------------------
-- Intra-Playlist Duplicate Flag:
-- Uses window PARTITION BY (IDPlayList + IDSong) to count how many times
-- the exact same IDSong appears inside THIS single playlist container.
-- Returns 1 if count > 1, else 0.
CASE
WHEN PlaylistSongs.IDSong IS NULL THEN 0
WHEN COUNT(*) OVER (
PARTITION BY P.IDPlayList, PlaylistSongs.IDSong
) > 1 THEN 1
ELSE 0
END AS DuplicateInCurrentPlaylist,
-- Inter-Playlist Duplicate Counter:
-- Fetches pre-computed distinct playlist count from SongPlaylistCounts.
-- 1 = Exists in only 1 playlist | 2+ = Duplicated across N different playlists
COALESCE(SongPlaylistCounts.DistinctPlaylistCount, 0) AS DuplicateAcrossPlaylistsCount,
-- ------------------------------------------------------------------------
-- Parent Playlist Hierarchy (Self-Join Lookup)
-- ------------------------------------------------------------------------
P.ParentPlaylist AS ParentPlaylistID,
Parent.PlaylistName AS ParentPlaylistName,
-- ------------------------------------------------------------------------
-- Track Metadata Fields
-- ------------------------------------------------------------------------
Songs.Artist,
Songs.AlbumArtist,
Songs.Album,
Songs.DiscNumber,
Songs.TrackNumber,
Songs.SongTitle,
Songs.SongPath,
-- Playlist Technical Sequencing Primary Keys
PlaylistSongs.SongOrder,
PlaylistSongs.IDPlaylistSong
FROM PlaylistTree tree
-- Join back to master Playlists table to extract playlist fields
INNER JOIN Playlists P
ON tree.IDPlaylist = P.IDPlaylist
-- Self-join Playlists table to resolve immediate parent playlist name
LEFT JOIN Playlists AS Parent
ON P.ParentPlaylist = Parent.IDPlaylist
-- LEFT JOIN track mapping table to preserve empty/parent container playlists
LEFT JOIN PlaylistSongs
ON P.IDPlaylist = PlaylistSongs.IDPlaylist
-- LEFT JOIN master song metadata table
LEFT JOIN Songs
ON PlaylistSongs.IDSong = Songs.ID
-- LEFT JOIN aggregated song count table for multi-playlist metrics
LEFT JOIN SongPlaylistCounts
ON PlaylistSongs.IDSong = SongPlaylistCounts.IDSong
-- Sort hierarchically by tree path, then by Track Order within each playlist
ORDER BY
tree.HierarchyPath COLLATE NOCASE ASC,
PlaylistSongs.SongOrder ASC;
-- ============================================================================
-- Step 3: Display output grid immediately in DB Browser for SQLite
-- ============================================================================
SELECT * FROM temp_PlaylistTracksExport;
-- ============================================================================
-- ADDENDUM: DATA EXPORT & INTEGRATION GUIDE
-- ============================================================================
/*
-----------------------------------------------------------------------------
1. EXPORT TO SPREADSHEETS (Excel, Google Sheets, LibreOffice Calc)
-----------------------------------------------------------------------------
A. DB Browser for SQLite GUI Export:
1. Run: SELECT * FROM temp_PlaylistTracksExport;
2. In the Results panel grid below, click 'Export to CSV' (or File > Export > Table(s) as CSV file).
3. Settings:
- Field separator: Comma (,)
- Quote character: Double quote (")
- Encoding: UTF-8 (Crucial for proper display of special characters in metadata)
- First line contains column names: Checked
4. Open the resulting .csv file directly in Excel / Google Sheets.
B. Copy / Paste Grid Directly into Excel:
1. Run: SELECT * FROM temp_PlaylistTracksExport;
2. Click inside the query output grid, press Ctrl+A (Select All), then Ctrl+C (Copy).
3. Open Excel and press Ctrl+V (Paste).
-----------------------------------------------------------------------------
2. EXPORT VIA COMMAND LINE / AUTOMATED SCRIPTS (SQLite CLI)
-----------------------------------------------------------------------------
To export directly to CSV using the SQLite Command Line Interface:
sqlite3 -header -csv MM.DB "< path/to/this_script.sql" > PlaylistExport.csv
-----------------------------------------------------------------------------
3. EXPORT TO OTHER DATABASE SYSTEMS (PostgreSQL, MySQL, SQL Server, SQLite)
-----------------------------------------------------------------------------
A. Dump as Permanent SQLite Table in a New Database File:
Run the following commands inside DB Browser for SQLite:
-- Attach a target destination database file
ATTACH DATABASE 'MediaMonkey_Export.db' AS TargetDB;
-- Create a permanent table in the target database
CREATE TABLE TargetDB.PlaylistTracksExport AS
SELECT * FROM temp_PlaylistTracksExport;
DETACH DATABASE TargetDB;
B. Transfer to PostgreSQL / MySQL via Python (pandas + sqlalchemy):
```python
import sqlite3
import pandas as pd
from sqlalchemy import create_engine
# 1. Connect to MM.DB and execute the temp table script
conn = sqlite3.connect('MM.DB')
with open('hierarchical_export_script.sql', 'r', encoding='utf-8') as f:
conn.executescript(f.read())
# 2. Read temp table into pandas DataFrame
df = pd.read_sql_query('SELECT * FROM temp_PlaylistTracksExport', conn)
# 3. Export to PostgreSQL / MySQL / SQL Server
pg_engine = create_engine('postgresql://user:pass@localhost:5432/music_db')
df.to_sql('playlist_tracks_export', pg_engine, if_exists='replace', index=False)
# 4. Or export directly to Excel with formatted sheets
df.to_excel('MediaMonkey_Playlists.xlsx', index=False, sheet_name='Playlists')
```
-----------------------------------------------------------------------------
4. DIRECT IMPORT INTO BI TOOLS (Power BI / Tableau)
-----------------------------------------------------------------------------
- Power BI: Use Get Data > SQLite database or ODBC / Python script import.
- Tableau: Connect via SQLite ODBC driver and point directly to a saved VIEW or
run this query via Custom SQL option.
*/
- Use F5 if DB Browser for SQLite is not showing anything.
- Duplication of Audio Files on your device is not a signicant overhead *IF* the same file exists (= SongPath) in 2 or more Playlists.
- Some apparent duplication of PlayLists might be for users convenience AND / OR organisational reasons or by virtue of Auto Playlists!