Sorting Music

[atmurray]

I’ve been noticing that sometimes iTunes sorts by the far left hand column of numbers (which is not the track numbers I might add). When browsing a firefly share this corresponds to the order that the songs were returned to iTunes. This currently is set by the order of the songs in sqlite which in turn is set by the order that the files were added to the database which again is based on the order the file system returns the files.

Now, I’ve made a patch which returns all queries sorted by their path:
in src/db-sql.c:

1348a1349,1351

>       /* Apply ordering */

>       strcat(query," order by path");

>

For me this is ideal as I have my music stored as follows:

///– –

For some people ordering by album and then track may be more appropriate so if this was exposed via a config option I think it would make many people happy!

[CCRDude]

For your 1 or 2 GHz machine, this may be ideal… but please, open up a book on SQL and read how crazy the costs for sorting string fields are!

Many users here use small NAS devices, with only 200-300 Mhz 😉 And we don’t want Firefly to be as bloated as Slimserver. It would make many other people sad 😀

Anyway, it’s just for your method of file names, others don’t store track numbers in filenames, there sorting would be different…

If you really want to make many people happy, here’s another solution: write a small shell script that would

• create a temporary sorted table
• dump this to a .sql file
• delete temporary table
• rename original table to backup name
• re-import the .sql file as the new songs table

All that should be possible with a simple text file of sqlite commands which could get piped to sqlite/sqlite3. Wrap that into a bash script, and it would allow your sorting, while not producing heavy costs on every single query! And set up as a cronjob to run once every night or so, the small offsets of not being totally up-to-date in sorting on the day of adding can be neglected imho.

PS: just checked that SQLite3 really does not support ALTER TABLE `songs` ORDER BY `artist`, `album`, `track`;, which of course would have been much simpler than the above. And CREATE TABLE `tmpsongs` SELECT * FROM `songs` ORDER BY `artist`, `album`, `track`; doesn’t work either, damn.

[atmurray]

Yeah I know the sorts are kind of expensive but I forgot people were running this on low end machines. My idea was that it would be an option (off by default) perhaps even with a warning next to it about the computational cost.

I do still like your idea of a one-off sort, however short of dumping the table to a text file, manually sorting and then re-inserting it into the database I think we might be out of luck. It appears with sqlite SORT BY cannot be used with ALTER nor UPDATE unfortunately. 😥

[Author]

Posts