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.