- This topic has 31 replies, 5 voices, and was last updated 11 years, 4 months ago by Anonymous.
- 20th June 2008 at 4:14 pm #2520AnonymousInactive
I am writing a small perl script to enable song rating with the remote of the Roku Soundbridge. It will become part of the Tools4Roku (Google for Tools4Roku 😉 ) when working.
So far its working, but how do I get the information into fireflys database?
I thought about manipulating the SQLite database (songs.db) directly, but that may not be future save as Ron (?) mentioned in another thread. Best would be comunicate with firefly directly. Is the RSP the right way? Where can I find information or documentation to the roku server protocol?
Does anyone have an example (best in perl 😀 ) how to read/send information from/to the database?
Looking forward to a happy rating soon!
Goemon20th June 2008 at 7:18 pm #17264
There are no such features in firefly currently.
So you have to talk to the database directly for now.
Seeing as its perl only, this limits it to *IX clients anyway.20th June 2008 at 7:44 pm #17265AnonymousInactive
Thanks for the answer. I started to write something in perl to manipulate the database directly – still sitting over it, 🙄 have not done anything with sql or sqlite so far, but it seems possible to me.
I have installed “perl-dbd-sqlite” and “perl-dbi” via ipkg.
The database seems to be sqite2 and not 3, I thought Ron made it sqlite3 some time ago. Btw I use svn-1696. ❓
Juergen20th June 2008 at 9:50 pm #17266AnonymousInactive
So I found a solution, the DBI and SQLite modules of perl seem not to support SQLite2 … but the software ic on my NSLU2 so I did it the dirty way (but its short and easy to understand 😉 )
$data = `sqlite /opt/var/mt-daapd/songs.db "SELECT rating FROM songs WHERE id = $m";`;
$data = int(($data+$likeit)/2+.5);
$display->msg(text => "I Like it! (".$data."%)", clear => 1, duration=>1, font => 10, x=>110, y=>0, keygrab=>2);
$data = `sqlite /opt/var/mt-daapd/songs.db "UPDATE songs SET rating = $data WHERE id = $m" `;
The first line reads the actual rating the next line calculates the new value for the rating (between 0 and 100). The $likeit can be 100 for a loved song and 0 for disliked songs. Then I give a visual feedback on the Roku Soundbridge display and write the new value to the database.
It seems to work – the smart playlist with “rating > 80” shows the songs I rated for testing. 😛
I will post again when I put an update of my Tools4Roku online.
Juergen21st June 2008 at 11:11 am #17267
Nice, and that seems to work for sqlite3 as well 😉
I’m a total perl n00b, but I do know my way around, so if you need someone to test it, here I am. 🙂
Note that my soundbridge still has the old display, with a low resolution.
Rating & Smart Playlists – now thats a killer combo. 🙂23rd June 2008 at 11:48 pm #17268AnonymousInactive
As promised, I post when the update is ready: I just put the update online at http://billiatjapan.netfirms.com/Tools4Roku/.
The new version makes song rating possible with the remote from your couch! Just press the “right” or “left” key to rate the currently playing song as loved or not loved respectively. Multiple rating is possible and brings the value closer to 5 stars (or 100% love!). The rating information is directly written to the firefly database via SQLite, which is needed to run it. ❗ Caution, this may not be safe or very smart. I am not responsible for any loss of your data. Backup first. ❗
Happy Song Rating!! I have fun with my smart playlist “rating>80” now! 😆
Goemon24th June 2008 at 7:18 am #17269
Gee, nice 🙂
Gotta try, but I had to read your rating function three times in order to understand it (ok, didn’t have my cup of coffee yet :D)
Now, I really appreciate the effort, so don’t take this the wrong way:
Why not increase/decrease the rating by 20 upon the press of left/right?
Seems way more easier to me and that would go together nice with the 5 star schema.
You don’t happen to have an id3-tool handy that runs on the slug now, do you?27th June 2008 at 8:44 pm #17270AnonymousInactive
I agree with you, that rating according to the “5 star” system would be easier to understand. However, I do not like was and thats the only reason to create such an evil formula. 😈
The thing I like on my way of calculating the rating is the fact, that the more I rate a song as loved , the closer it comes to 100% pure love. From the mathematical point of view its infinite. But as I have to round, the song will hit 100% – The other way round, If I do not like a song anymore it drops faster at begin and only a multiple bad rating will bring it really down …
If there would be more responce for the rating feature and the request to implement the “5 star rating” – I would do it. Maybe even switchable in the localisation file.
CU – Juergen2nd July 2008 at 5:10 pm #17271
Ok, obviously its not great that the DB stuff is hardcoded in t4roku.pl 😉
Im using sqlite3 on svn-1696, so I obviously needed to adjust that settings.
Good news first: It’s also working with sqlite3 on my slug.
Bad news: the menu that shows on triple-press of the home key is very slow. The cursor is buggy, as it doesn’t get redrawn when you move the selection. So you actually cannot tell what you have selected once you move it once. I’m using a soundbridgeM1001 with a 16-row display, FYI.
And the rating of a song itself takes about 2-3 seconds, once.
My database has about 9000 songs, and sqlite3 is said to be faster than sqlite2. *shrug*
The final test is to see how the slug responds when I turn off (totally) my soundbridge. 😉
Ok, when I turn the SoundBridge completely off (cut the power), the perl process is left hogging the poor slug’s CPU. 🙁
Would it be possible to include some kind of telnet, TCP timeout, hm? 🙂3rd August 2008 at 5:17 pm #17272milesianGuest
Mr. Goemon and fizze, you guys have no idea how big a hole in my music life you’re filling with your work here on Song Rating. I bend a knee in acknowledgment of that!
Permit me to beg your assistance with my installation of the latest Tool4Roku package on my DNS 323 NAS box. Somehow, I managed to install ipkg, Perl, sqlite, and the Net-Telnet module successfully (I think..). I’m able to ‘perl t4roku.pl’ and get your toolset running (weather!) on my Roku. Supreme satisfaction!
But sqlite is a different story. When I hit the left-right buttons on the remote, my t4roku.pl telnet perl session tells me sqlite cannot be found. Using ipkg, I installed the “perl-dbd-sqlite” and “sqlite” packages and everything apparently installed to root.
So it seems I’ve got the program installed but your toolset cannot find and run the sqlite command. This smells like a simple PATHing issue? Honestly I have no idea where things get installed in the Linux environment, and I was pleasantly surprised when perl just ran anywhere after installation. Maybe ipkg configured it automatically?
With enough work, I’m a Linux noob who has a decent sense of what goes on. Please set me straight! ^_^4th August 2008 at 6:51 am #17273
Are you using sqlite2 or sqlite3 with your firefly?
Also, you might need to adjust the database path in the t4roku.pl according to your firefly config.
Currently it’s all hardcoded in that t4roku.pl script. You have to change it in 4 locations, I think.
any perl-db related packages aren’t needed. Thats the “trick” here. Goemon uses perl to simply call the sqlite binary, and doesn’t use perl bindings.11th August 2008 at 10:05 pm #17274AnonymousInactive
I was trying this at the weekend. Very promising but a bit buggy at the moment. Sam observations as fizze – I’m on sqlite3 so the hard coded DB path doesn’t work and the menu redraw is broken. Most of the screens are drawn a few pixels too low and the graphics go off the bottom (tried all screen settings BTW, using an M1001).
I like the “hidden” alarm in the SB but I was actually starting to write a control library for PHP so that I could let my server act as an alarm clock by setting off a cron job to wake up and queue tracks etc. This way I can just have alarm scheduling on a Google calendar or something 😉13th August 2008 at 11:32 am #17275
Such an alarm would be sweet indeed. 🙂
I’m definitely interested, as soon as you have something in a release-friendly shape 🙂14th August 2008 at 5:44 pm #17276AnonymousInactive
If there was a GetSongByID method in RCP then I’d be done by now. The only way I can recreate a playlist is by searching on track names which obviously isn’t very accurate.
Another way is injecting a playlist into Firefly and then selecting it from the SB. The playlist creation and editing isn’t great between SB and Firefly though.
If I create a row in the playlist table and a bunch of rows in the playlistitems table, is that going to be presented as a playlist in the SB? My playlistitems table is currently empty and all my playlists are smart playlists.14th August 2008 at 7:05 pm #17277
Presets on the SB can help you out there.
You could just create a preset for one particuliar playlist, and then fill that playlist according to your wishes.
- The forum ‘General Discussion’ is closed to new topics and replies.