From 3e118c57e9d2f85d35bac2c7ea3d3a4771619215 Mon Sep 17 00:00:00 2001 From: Ian Beckwith Date: Thu, 23 Sep 2010 19:18:03 +0100 Subject: [PATCH] start implementing albums --- lib/ID3FS/DB.pm | 175 ++++++++++++++++++++++++++++++++++++++-- lib/ID3FS/Path.pm | 97 ++++++++++++++++++---- lib/ID3FS/PathElement/Artist.pm | 4 + lib/ID3FS/PathElement/Tag.pm | 2 +- 4 files changed, 254 insertions(+), 24 deletions(-) diff --git a/lib/ID3FS/DB.pm b/lib/ID3FS/DB.pm index 01b41d8..b432cea 100644 --- a/lib/ID3FS/DB.pm +++ b/lib/ID3FS/DB.pm @@ -126,10 +126,10 @@ sub tags my $main_sql_start=("SELECT t2.name\n" . "\tFROM (SELECT files_id FROM tags t1\n" . "\t\tINNER JOIN files_x_tags ON t1.id=files_x_tags.tags_id\n" . - "\t\tWHERE t1.id in \n\t\t\t("); - my $main_sql_mid=(")\n\t\t) AS fxt1\n" . - "\tINNER JOIN files_x_tags fxt2 ON fxt1.files_id=fxt2.files_id\n" . - "\tINNER JOIN tags t2 ON fxt2.tags_id=t2.id\n" . + "\t\tWHERE t1.id in\n\t\t\t("); + my $main_sql_mid=(")\n\t\t) AS subselect\n" . + "\tINNER JOIN files_x_tags ON subselect.files_id=files_x_tags.files_id\n" . + "\tINNER JOIN tags t2 ON files_x_tags.tags_id=t2.id\n" . "\tWHERE t2.id NOT IN ("); my $main_sql_end=")\n\tGROUP BY t2.name;"; while(my $constraint=shift @constraints) @@ -161,11 +161,170 @@ sub tag_values return(map { $_->[0]; } @$tags); } -sub tag_id +sub artists { - my($self, $tag)=@_; - my $sql='SELECT id FROM tags WHERE name=?'; - my ($id)=$self->cmd_onerow($sql, $tag); + my($self, @constraints)=@_; + if(!@constraints) # /ALL + { + my $sql="SELECT DISTINCT name FROM artists;"; + my $tags=$self->cmd_rows($sql); + return(map { $_->[0]; } @$tags); + } + my @file_ids=(); + my @tag_ids=(); + my $main_sql_start=("SELECT artists.name\n" . + "\tFROM (SELECT files_id FROM tags\n" . + "\t\tINNER JOIN files_x_tags ON tags.id=files_x_tags.tags_id\n" . + "\t\tWHERE tags.id in\n\t\t\t("); + my $main_sql_end=(")\n\t\t) AS subselect\n" . + "\tINNER JOIN files_x_artists ON subselect.files_id=files_x_artists.files_id\n" . + "\tINNER JOIN artists ON artists.id=files_x_artists.artists_id\n" . + "\n\tGROUP BY artists.name;"); + while(my $constraint=shift @constraints) + { + print "CONSTRAINT: $constraint->{name}\n"; + my $cid=$constraint->{id}; + push(@tag_ids, $cid); + } + @tag_ids = map( { "\"$_\""; } @tag_ids) unless($self->{postgres}); + my $tagstr=join(", ", @tag_ids); + my $sql = ($main_sql_start . $tagstr . + $main_sql_end); + print "SQL: $sql\n"; + my $result=$self->cmd_rows($sql); + my @tagnames=map { $_->[0]; } @$result; + print "ARTISTS: ", join(', ', @tagnames), "\n"; + return(@tagnames); +} + +sub albums +{ + my($self, @constraints)=@_; + my @file_ids=(); # FIXME: needed? what about in artists() + my @tag_ids=(); + # FIXME: rework PathElements + if(ref($constraints[$#constraints]) eq "ID3FS::PathElement::Artist") + { + return $self->artist_albums($constraints[$#constraints]->{id}); + } + return(); # FIXME + my $main_sql_start=("SELECT artists.name\n" . + "\tFROM (SELECT files_id FROM tags\n" . + "\t\tINNER JOIN files_x_tags ON tags.id=files_x_tags.tags_id\n" . + "\t\tWHERE tags.id in\n\t\t\t("); + my $main_sql_end=(")\n\t\t) AS subselect\n" . + "\tINNER JOIN files_x_artists ON subselect.files_id=files_x_artists.files_id\n" . + "\tINNER JOIN artists ON artists.id=files_x_artists.artists_id\n" . + "\n\tGROUP BY artists.name;"); + while(my $constraint=shift @constraints) + { + print "CONSTRAINT: $constraint->{name}\n"; + my $cid=$constraint->{id}; + push(@tag_ids, $cid); + } + @tag_ids = map( { "\"$_\""; } @tag_ids) unless($self->{postgres}); + my $tagstr=join(", ", @tag_ids); + my $sql = ($main_sql_start . $tagstr . + $main_sql_end); + print "SQL: $sql\n"; + my $result=$self->cmd_rows($sql); + my @tagnames=map { $_->[0]; } @$result; + print "ARTISTS: ", join(', ', @tagnames), "\n"; + return(@tagnames); +} + +sub artist_albums +{ + my($self, $artist_id)=@_; + my $sql=("SELECT albums.name FROM artists\n\t" . + "INNER JOIN artists_x_albums ON artists.id=artists_x_albums.artists_id\n\t" . + "INNER JOIN albums ON albums.id=artists_x_albums.albums_id\n\t" . + "WHERE artists.id=?\n\t" . + "GROUP BY albums.name\n"); + print "ARTIST_ALBUMS SQL: $sql\n"; + my $result=$self->cmd_rows($sql, $artist_id); + my @albums=map { $_->[0]; } @$result; + print "ALBUMS: ", join(', ', @albums), "\n"; + return(@albums); +} + +sub artist_tracks +{ + my($self, $artist_id)=@_; + my $sql=("SELECT files.name FROM artists\n\t" . + "INNER JOIN artists_x_files ON artists.id=files_x_artists.artists_id\n\t" . + "INNER JOIN files ON files.id=files_x_artists.files_id\n\t" . + "WHERE artists.id=?\n\t" . + "GROUP BY files.name\n"); + print "ARTIST_TRACKS SQL: $sql\n"; + my $result=$self->cmd_rows($sql, $artist_id); + my @albums=map { $_->[0]; } @$result; + print "ALBUMS: ", join(', ', @albums), "\n"; + return(@albums); +} + +sub album_tracks +{ + # FIXME: need albums_x_files table + my($self, $album_id)=@_; + my $sql=("SELECT files.name FROM albums\n\t" . + "INNER JOIN files_x_albums ON albums.id=files_x_albums.albums_id\n\t" . + "INNER JOIN albums ON albums.id=files_x_albums.albums_id\n\t" . + "WHERE albums.id=?\n\t" . + "GROUP BY files.name\n"); + print "ALBUM_TRACKS SQL: $sql\n"; + my $result=$self->cmd_rows($sql, $album_id); + my @tracks=map { $_->[0]; } @$result; + print "TRACKS: ", join(', ', @tracks), "\n"; + return(@tracks); +} + +sub tracks +{ + my($self, @constraints)=@_; + # FIXME: rework PathElements + if(ref($constraints[$#constraints]) eq "ID3FS::PathElement::Artist") + { + return $self->artist_tracks($constraints[$#constraints]->{id}); + } + elsif(ref($constraints[$#constraints]) eq "ID3FS::PathElement::Album") + { + # FIXME + return(()); +# return $self->album_tracks($constraints[$#constraints]->{id}); + } + return(); # FIXME + my $main_sql_start=("SELECT artists.name\n" . + "\tFROM (SELECT files_id FROM tags\n" . + "\t\tINNER JOIN files_x_tags ON tags.id=files_x_tags.tags_id\n" . + "\t\tWHERE tags.id in\n\t\t\t("); + my $main_sql_end=(")\n\t\t) AS subselect\n" . + "\tINNER JOIN files_x_artists ON subselect.files_id=files_x_artists.files_id\n" . + "\tINNER JOIN artists ON artists.id=files_x_artists.artists_id\n" . + "\n\tGROUP BY artists.name;"); + my @tag_ids; + while(my $constraint=shift @constraints) + { + print "CONSTRAINT: $constraint->{name}\n"; + my $cid=$constraint->{id}; + push(@tag_ids, $cid); + } + @tag_ids = map( { "\"$_\""; } @tag_ids) unless($self->{postgres}); + my $tagstr=join(", ", @tag_ids); + my $sql = ($main_sql_start . $tagstr . + $main_sql_end); + print "SQL: $sql\n"; + my $result=$self->cmd_rows($sql); + my @tagnames=map { $_->[0]; } @$result; + print "ARTISTS: ", join(', ', @tagnames), "\n"; + return(@tagnames); +} + +sub id +{ + my($self, $type, $val)=@_; + my $sql="SELECT id FROM $type WHERE name=?"; + my ($id)=$self->cmd_onerow($sql, $val); return($id); } diff --git a/lib/ID3FS/Path.pm b/lib/ID3FS/Path.pm index c76c9e6..36eaba2 100644 --- a/lib/ID3FS/Path.pm +++ b/lib/ID3FS/Path.pm @@ -10,8 +10,9 @@ use ID3FS::PathElement::File; use ID3FS::PathElement::Tag; use ID3FS::PathElement::Tagval; -our ($STATE_INVALID, $STATE_ROOT, $STATE_WANTMORE, $STATE_TAG, $STATE_TAGVAL, - $STATE_BOOLEAN, $STATE_ARTIST, $STATE_ALBUM, $STATE_FILE)=(0..8); +our ($STATE_INVALID, $STATE_ROOT, $STATE_TAG, $STATE_TAGVAL, + $STATE_BOOLEAN, $STATE_ARTISTS, $STATE_ALBUMS, $STATE_TRACKLIST, + $STATE_FILE)=(0..9); sub new { @@ -47,32 +48,49 @@ sub isvalid sub dest { my($self)=@_; - return "FIXME"; + if($self->state() == $STATE_FILE) + { + return $self->{db}->filename(@{$self->{elements}}); + } + return "ERROR"; #should never happen? } sub dirents { my($self)=@_; + my @dents=(); given($self->state()) { when($STATE_TAG) { - return(qw(AND ARTISTS ALBUMS TRACKS)); + @dents=qw(AND ARTISTS ALBUMS TRACKS); } when($STATE_BOOLEAN) { - return $self->{db}->tags(@{$self->{elements}}); + @dents=$self->{db}->tags(@{$self->{elements}}); } when($STATE_ROOT) { - my @dents=("ALL", $self->{db}->tags(@{$self->{elements}})); - return @dents; + @dents=("ALL", $self->{db}->tags(@{$self->{elements}})); + } + when($STATE_ARTISTS) + { + @dents=$self->{db}->artists(@{$self->{elements}}); + } + when($STATE_ALBUMS) + { + @dents=$self->{db}->albums(@{$self->{elements}}); + } + when($STATE_TRACKLIST) + { + @dents=$self->{db}->tracks(@{$self->{elements}}); } default { print "DIRENTS: UNHANDLED STATE: $_\n"; } } + return(@dents); } sub parse @@ -81,7 +99,7 @@ sub parse @{$self->{components}}=split(/\//, $self->{path}); shift @{$self->{components}}; # drop empty field before leading / print "PATH: $self->{path}\n"; - print "COMPONENTS: ", join(' | ', @{$self->{components}}), "\n"; +# print "COMPONENTS: ", join(' | ', @{$self->{components}}), "\n"; $self->state($STATE_ROOT); return if($self->{path} eq "/"); my @parts=@{$self->{components}}; @@ -102,7 +120,7 @@ sub parse print "SM: ROOT: $name\n"; if($name eq "ALL") { - $self->state($STATE_ARTIST); + $self->state($STATE_ARTISTS); } else { @@ -128,9 +146,19 @@ sub parse $self->state($STATE_BOOLEAN); # push(@{$self->{elements}}, ID3FS::PathElement::Boolean->new($name)); } -# when("ARTISTS") { ; } -# when("ALBUMS") { ; } -# when("TRACKS") { ; } + when("ARTISTS") + { + $self->state($STATE_ARTISTS); + } + when("ALBUMS") + { + $self->state($STATE_ALBUMS); + } + when("TRACKS") + { + $self->state($STATE_TRACKLIST); + } + # when("OR") { ; } # when("NOT") { ; } default @@ -140,7 +168,7 @@ sub parse } } - when($STATE_WANTMORE) + when(255) #FIXME - dead code { print "SM: WANTMORE: $name\n"; $tag=ID3FS::PathElement::Tag->new($self->{db}, $name); @@ -192,17 +220,56 @@ sub parse $self->state($STATE_INVALID); } } - when($STATE_ARTIST) + when($STATE_ARTISTS) { print "SM: ARTIST: $name\n"; + my $artist=ID3FS::PathElement::Artist->new($self->{db}, $name); + push(@{$self->{elements}}, $tag); + if($artist) + { + push(@{$self->{elements}}, $artist); + $self->state($STATE_ALBUMS); + } + else + { + $self->state($STATE_INVALID); + } } - when($STATE_ALBUM) + when($STATE_ALBUMS) { print "SM: ALBUM: $name\n"; + my $album=ID3FS::PathElement::Album->new($self->{db}, $name); + push(@{$self->{elements}}, $album); + if($album) + { + push(@{$self->{elements}}, $album); + $self->state($STATE_TRACKLIST); + } + else + { + $self->state($STATE_INVALID); + } + } + when($STATE_TRACKLIST) + { + print "SM: TRACKS: $name\n"; + my $track=ID3FS::PathElement::File->new($self->{db}, $name); + push(@{$self->{elements}}, $track); + if($track) + { + push(@{$self->{elements}}, $track); + $self->state($STATE_FILE); + } + else + { + $self->state($STATE_INVALID); + } } when($STATE_FILE) { print "SM: FILE: $name\n"; + # Can't have anything after a filename + $self->state($STATE_INVALID); } default { diff --git a/lib/ID3FS/PathElement/Artist.pm b/lib/ID3FS/PathElement/Artist.pm index 80f5cec..9f5abfd 100644 --- a/lib/ID3FS/PathElement/Artist.pm +++ b/lib/ID3FS/PathElement/Artist.pm @@ -10,7 +10,11 @@ sub new my $self={}; bless($self,$class); + $self->{db}=shift; $self->{name}=shift; + $self->{id}=($self->{db}->id("artists", $self->{name})); + return(undef) unless(defined($self->{id})); + print "Artist ID: $self->{id}\n"; return $self; } diff --git a/lib/ID3FS/PathElement/Tag.pm b/lib/ID3FS/PathElement/Tag.pm index 5020908..6fc7978 100644 --- a/lib/ID3FS/PathElement/Tag.pm +++ b/lib/ID3FS/PathElement/Tag.pm @@ -12,7 +12,7 @@ sub new $self->{db}=shift; $self->{name}=shift; - $self->{id}=($self->{db}->tag_id($self->{name})); + $self->{id}=($self->{db}->id("tags", $self->{name})); return(undef) unless(defined($self->{id})); print "TAG ID: $self->{id}\n"; return($self); -- 2.11.0