"Most Played" list

[AndyIrwin2008]

I have two speeds of drives that I keep my music collection on. I would like to keep the "Most Played" albums on the faster drives. All I need is text file with the list of directories that are shown by the "Most Played" screen. How can I get that information from the subsonic database?

[supra92]

Hey Andy -- two steps are all it takes

1. Pull up your Subsonic in a browser, and change the "index.view" part to "db.view", such as: http://www.yourdomain.com/subsonic/db.view

2. In the white box, type the following SQL code:

Code: Select all

select * from music_file_info order by play_count desc

and click the OK button.


You will see every album in your database, sorted by the PLAY_COUNT column with the most-played ones at the top.

Hope this helps, and cheers,
Supra92

[AndyIrwin2008]

Awesome! Thanks...any idea how to script that query and output to a text file in ubuntu server? I would like to copy the most played albums between drives automagically without having to output manually from a browser.

[supra92]

It's definitely doable, but you'll need to install and configure HSQLDB, which is the relational database that Subsonic uses on the backend.

Installing HSQLDB is probably a bit beyond the scope of this forum, but here are some links to get you going.

Main HSQLDB website: http://hsqldb.org/

Latest download: http://sourceforge.net/projects/hsqldb/ ... p/download


You'll want to get that installed, and a connection pointed at the Subsonic database, which is located here: /var/subsonic/db/subsonic.data


Then create a textfile containing your SQL code in, say, /home/andyirwin/mostplayed.sql

You can extract the data to a textfile like so, automating this as you see fit with a cron job:

Code: Select all

java -jar $HSQLDB_HOME/lib/hsqldb.jar urlid /home/andyirwin/mostplayed.sql > /home/andyirwin/mostplayedresults.txt 2>&1

Let us know how this goes if you wind up pursuing it.

Regards,
Supra92