Additional ID3v2 Tag Fields?

[DavidEsp]

Hi, is there any likelihood of adding support for additional ID3 tag fields in the near future?

Meanwhile, are there any recommended workarounds?  For example stand-alone scripts or executables) to add such fields from ID3's to the database, e.g. to the songs table or to a separate ("songs_additional"?) table keyed by the same song IDs?.

Such fields would be useful e.g. as basis for more specialised music choice (via SQL-based rotations).

[DavidEsp]

Meantime...

I asked Bing's AI-LLM Copilot chatbot: "script to add id3 tag fields to a MySQL database table containing file paths of corresponding mp3 files".

It came up with the following, that doesn't do exactly what I asked, but demonstrates the general idea.

First, ensure the commandline program mp3info is installed.  Binary executables (binaries) for this, for various platforms - including Windows - are available at ibiblio, as recommended in a tech article at the Columbia University website.  I have (installed and) used it on my mac for a few years.

Then, create a table (e.g. called "mp3_tags") in a given database:

Code Select Expand

CREATE TABLE mp3_tags (
    id INT AUTO_INCREMENT PRIMARY KEY,
    file_path VARCHAR(255),
    artist VARCHAR(255),
    title VARCHAR(255),
    album VARCHAR(255),
    genre VARCHAR(255)
);
For the above table, the following (bash command) script then generates SQL to add an entry (row) for each MP3 file (in a given folder/directory) to it, having (data-populated) columns for in each named field in that MP3 file's ID3 tag.

Code Select Expand

#!/bin/bash
# "mp3 id3 tag to sql" importer
# Reads ID3 tags using "mp3info" and outputs a dumpfile that can be imported to MySQL

# Path to your MP3 files
MP3_DIR="/path/to/your/mp3/files"

# Loop through each MP3 file
for mp3_file in "$MP3_DIR"/*.mp3; do
    file_path="$mp3_file"
    artist=$(mp3info -p "%a" "$mp3_file")
    title=$(mp3info -p "%t" "$mp3_file")
    album=$(mp3info -p "%l" "$mp3_file")
    genre=$(mp3info -p "%g" "$mp3_file")

    # Output SQL INSERT statement
    echo "INSERT INTO mp3_tags (file_path, artist, title, album, genre) VALUES ('$file_path', '$artist', '$title', '$album', '$genre');"
done
The chatbot further reported: "Also, tools like MP3 Diags and MP3val can help you check files for errors before importing them into your database".  I had not heard of these before.

I am neither a fluent coder nor familiar with this kind of programming domain, merely a dabbler with long-ago coding experience leaning on a chatbot, but I will at least have a go with the above.

However the above code would need to be modified - e.g. to loop through the files in RadioDJ's songs table rather than (MP3) files in a folder.  I guess it would be safest to generate and insert-to a separate "sidecar" table (e.g. named "songs_custom"?), keyed/indexed by the same integers as in the songs table, enabling it to be linked in our RadioDJ SQL (e.g. Rotation) queries (via WHERE).

Requests to Action:

• I have no feel for what degree of efficiency penalty this would imply, as compared to instead appending additional columns to the songs table itself.  For example if there were 10K songs in the table.  Any idea, anyone?
  
• If anyone more fluent in and familiar with any of this could suitably modify the code, that would be fantastic.  Maybe for PowerShell (if that's what Windows "prefers")?  Any "takers" ?
  

[DavidEsp]

I re-asked Bing AI/LLM chatbot: "windows 10 script to add id3 tag fields to a MySQL database table that already contains the file paths of corresponding mp3 files, whose locations in a file system are not known in advance"

It gave the following Powershell code and advice:
(Again, not exactly what I wanted, but at least it is now Windows 10 oriented)

Code Select Expand

# PowerShell script to read ID3 tags and insert into MySQL
$mp3Files = Get-ChildItem -Path "C:\Path\To\Your\MP3\Folder" -Recurse -Include *.mp3

foreach ($file in $mp3Files) {
    $file_path = $file.FullName
    $artist = (Get-Mp3Tag -Path $file_path).Artist
    $title = (Get-Mp3Tag -Path $file_path).Title
    $album = (Get-Mp3Tag -Path $file_path).Album
    $genre = (Get-Mp3Tag -Path $file_path).Genre

    # Output SQL INSERT statement
    Write-Output "INSERT INTO mp3_tags (file_path, artist, title, album, genre) VALUES ('$file_path', '$artist', '$title', '$album', '$genre');"
}

Execute the generated SQL INSERT statements to populate your MySQL table with the ID3 tag information. You can use a MySQL client or command-line tool to import the data.

[DavidEsp]

Update:
I asked Bing-bot afresh for Powershell code to loop through a table (not a folder).  It gave code snippets (as opposed to the complete script) but recommended using PowerShell 7, not PowerShell 5 (e.g. that my current Windows 10 had), as that can pass on text output from commands.  Also Microsoft recommend it as it is the one they currently support into the future.

I found PowerShell (free) in the Microsoft Store, and it seems to have installed ok.

So on to the next step - get a complete PS7 script out of the Bingbot...
(for unknown reason my further Reply was forbidden, so I edited it into here instead)
((my bedtime now - so this will be all from me today))

The complete code (untested):

Code Select Expand

# Connect to your MySQL database (adjust connection details)
$server = "localhost"
$database = "your_database"
$username = "your_username"
$password = "your_password"

# Query existing entries (assuming you have an 'mp3_tags' table)
$entries = Invoke-SqlCmd -ServerInstance $server -Database $database -Username $username -Password $password -Query "SELECT id, file_path FROM mp3_tags"

# Loop through entries
foreach ($entry in $entries) {
    $entry_id = $entry.id
    $file_path = $entry.file_path

    # Read ID3 tags from the MP3 file
    $audiofile = Get-Mp3Tag -Path $file_path
    $artist = $audiofile.Artist
    $title = $audiofile.Title
    $album = $audiofile.Album
    $genre = $audiofile.Genre

    # Update the entry in the database
    $update_query = @"
        UPDATE mp3_tags
        SET artist = '$artist', title = '$title', album = '$album', genre = '$genre'
        WHERE id = $entry_id
"@
    Invoke-SqlCmd -ServerInstance $server -Database $database -Username $username -Password $password -Query $update_query
}

# Close the database connection
# (Note: In practice, use proper connection handling)


[DavidEsp]

FYPI - Update:
(So any interested folks can see my progress and possibly follow along in parallel etc.)

Into W10 I have (just now) installed the free MS IDE application VSCode and added its PowerShell extension.
I am (right now) learning how to use this (combination) and, within it, how to develop and debug PowerShell 7 scripts.

My initial websearches today had instead suggested the app ISE (Integrated Scripting Environment), but that appears to be obsolete, only compatible with PowerShell 5 (not 7).

Onwards!

My learning experiments will be based on a new "playground" database, running (under? in? on?) the same MariaDB database-server as my RadioDJ databases (I have more than one) before (backing up and) interacting with one of my RadioDJ databases that is already populated with songs/tracks.

[DavidEsp]

Update:
I made some good progress, but encountered a hurdle where Bing Copilot's PowerShell code makes use of the cmdlet  Invoke-Sqlcmd, but that cmdlet was not recognised (by PowerShell) when I ran the script.  This cmdlet had to be imported - in fact the module containing it - from an independent repository that (AFAICT) is tacitly (as opposed to officially) approved my Microsoft.  I stand ready to be corrected or confirmed in this view by anyone more familiar with it.

The PowerShell command to import it is:

Code Select Expand

Install-Module sqlserver
PowerShell warned that this module was coming from an "untrusted" server, but - in line with the above (and advice seen on expert forums) - I chose to "Accept All".  For anyone else: make your own judgement.

After that, I found it necessary to reboot Windows.  Then finally the cmdlet  Invoke-Sqlcmd was recognised.

[DavidEsp]

Update: An additional hurdle - which I am unsure how to approach or overcome...

When I run the script, I get the error:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

I don't understand why this is happening, nor how to diagnose it.  PowerShell and MariaDB are on the same Windows (10) machine.  Same as HeidiSQL and MariaDB are.  I am not that familiar with this kind of connecting to servers.

Is it necessary to configure something (e.g. MariaDB) in some further way?  Something to investigate further tomorrow...