Wednesday, July 29, 2009

select...group by artist

So I finally updated my Amarok mounting to work with 2.x. I'm still having a few issues with the 2.x of 'rok but that's another story.

One of the cool things about being able to mount things with libferris is that everything above ferris can also see them, XQuery, SQLite etc. For example, the amarok://playlist URL shows, as you would imagine, the playlist as it currently appears in the running amarok application.

The following little SQLite is fairly self explanatory:
.load libferrissqlitevtable.so
create virtual table fs using libferris(
'amarok://playlist',
'',
name text,
title text,
size int,
length int,
playtime text,
link-target text,
ferris-delegate-url text
);

select name, playtime, title from fs
order by name desc;

select avg(length) from fs;


And for a particular album, the playlist and average track time.

9|352|Absolutely Curtains
8|246|Stay
7|255|Free Four
6|274|Childhood's End
5|260|Mudmen
4|309|Wots ... Uh The Deal
3|187|The Gold It's In The ...
2|209|Burning Bridges
1|147|When You're In
0|185|Obscured By Clouds

242.4

Of course, a litttle group by artist, album... would make the SQL more generic:
select avg(length),max(artist),max(album)
from fs
group by artist, album;

285.777777777778|Pink Floyd|Dark Side of the Moon
242.4|Pink Floyd|Obscured By Clouds

Wednesday, July 22, 2009

Done bought me a new calculator...

So, the next release of libferris will include support for mounting google spreadsheets. This works right through to the file manager now. You can mount a spreadsheet as a filesystem and edit it with ego. Changes to cell a written to google, any formulas that are updated appear in the file manager right away, and of course if you are viewing the worksheet in a web browser its updated too.

I'm looking forward to compiling the latest ferris for maemo so that you can edit stuff on the tablet and have it change on your desktop automatically ;)

export SPREAD=google://docs/spreadsheets/smalltest1/Sheet1
$ echo 5 | ferris-redirect --ea b $SPREAD/9
$ echo 23 | ferris-redirect --ea c $SPREAD/9

$ echo -n "=sum(B9,C9)" | ferris-redirect --ea d $SPREAD/9
$ fcat -a d $SPREAD/9
28

And of course, if you use the first row for headings...

$ echo -n result | ferris-redirect --ea d $SPREAD/1
$ fcat -a result $SPREAD/9
28