From fa93319dda0cc1919e135d6c93e93f8a11c8d1c6 Mon Sep 17 00:00:00 2001 From: Ian Beckwith Date: Thu, 23 Sep 2010 21:37:45 +0100 Subject: [PATCH] First music successfully played via id3fs split file/path in schema implement tracks --- lib/ID3FS/DB.pm | 183 ++++++++++++++++++++++++++++-------------- lib/ID3FS/Fuse.pm | 2 +- lib/ID3FS/Path.pm | 2 +- lib/ID3FS/PathElement/File.pm | 4 + 4 files changed, 130 insertions(+), 61 deletions(-) diff --git a/lib/ID3FS/DB.pm b/lib/ID3FS/DB.pm index b432cea..784abb9 100644 --- a/lib/ID3FS/DB.pm +++ b/lib/ID3FS/DB.pm @@ -4,6 +4,7 @@ use strict; use warnings; use DBI; use ID3FS::File; +use Cwd; our $SCHEMA_VERSION=1; my $dbfile=".id3fs"; @@ -16,6 +17,8 @@ sub new bless($self,$class); my($dir, $init, $me)=@_; + $self->{base}=$dir; + $self->{absbase}=Cwd::abs_path($dir); $self->{dbpath}="$dir/$dbfile"; $self->{me}=$me; @@ -71,12 +74,12 @@ sub create } else { - my %indexes=( "idx_files_id" => "files(id)", - "idx_fxt_both" => "files_x_tags(files_id, tags_id)", + my %indexes=( "idx_files_id" => "files (id)", + "idx_fxt_both" => "files_x_tags (files_id, tags_id)", "idx_fxt_files" => "files_x_tags (files_id)", "idx_fxt_tags" => "files_x_tags (tags_id)", "idx_tags_id" => "tags (id)", - "idx_tags_name" => "tags(name)" ); + "idx_tags_name" => "tags (name)"); for my $index (keys %indexes) { $self->{dbh}->do("CREATE INDEX $index ON " . $indexes{$index}); @@ -120,8 +123,7 @@ sub tags my $tags=$self->cmd_rows($sql); return(map { $_->[0]; } @$tags); } - my @file_ids=(); - my @tag_ids=(); + my @ids=(); my $main_sql_start=("SELECT t2.name\n" . "\tFROM (SELECT files_id FROM tags t1\n" . @@ -136,10 +138,10 @@ sub tags { print "CONSTRAINT: $constraint->{name}\n"; my $cid=$constraint->{id}; - push(@tag_ids, $cid); + push(@ids, $cid); } - @tag_ids = map( { "\"$_\""; } @tag_ids) unless($self->{postgres}); - my $tagstr=join(", ", @tag_ids); + @ids = map( { "\"$_\""; } @ids) unless($self->{postgres}); + my $tagstr=join(", ", @ids); my $sql = ($main_sql_start . $tagstr . $main_sql_mid . $tagstr . $main_sql_end); @@ -170,8 +172,7 @@ sub artists my $tags=$self->cmd_rows($sql); return(map { $_->[0]; } @$tags); } - my @file_ids=(); - my @tag_ids=(); + my @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" . @@ -184,10 +185,10 @@ sub artists { print "CONSTRAINT: $constraint->{name}\n"; my $cid=$constraint->{id}; - push(@tag_ids, $cid); + push(@ids, $cid); } - @tag_ids = map( { "\"$_\""; } @tag_ids) unless($self->{postgres}); - my $tagstr=join(", ", @tag_ids); + @ids = map( { "\"$_\""; } @ids) unless($self->{postgres}); + my $tagstr=join(", ", @ids); my $sql = ($main_sql_start . $tagstr . $main_sql_end); print "SQL: $sql\n"; @@ -200,37 +201,35 @@ sub artists sub albums { my($self, @constraints)=@_; - my @file_ids=(); # FIXME: needed? what about in artists() - my @tag_ids=(); + my @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" . + my $main_sql_start=("SELECT albums.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;"); + "\tINNER JOIN files_x_albums ON subselect.files_id=files_x_albums.files_id\n" . + "\tINNER JOIN albums ON albums.id=files_x_albums.albums_id\n" . + "\n\tGROUP BY albums.name;"); while(my $constraint=shift @constraints) { print "CONSTRAINT: $constraint->{name}\n"; my $cid=$constraint->{id}; - push(@tag_ids, $cid); + push(@ids, $cid); } - @tag_ids = map( { "\"$_\""; } @tag_ids) unless($self->{postgres}); - my $tagstr=join(", ", @tag_ids); - my $sql = ($main_sql_start . $tagstr . + @ids = map( { "\"$_\""; } @ids) unless($self->{postgres}); + my $str=join(", ", @ids); + my $sql = ($main_sql_start . $str . $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); + my @names=map { $_->[0]; } @$result; + print "ALBUMS: ", join(', ', @names), "\n"; + return(@names); } sub artist_albums @@ -258,14 +257,14 @@ sub artist_tracks "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); + my @names=map { $_->[0]; } @$result; + @names = map { s/.*\///; } @names; + print "ARTISTTRACKS: ", join(', ', @names), "\n"; + return(@names); } 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" . @@ -274,9 +273,10 @@ sub album_tracks "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); + my @names=map { $_->[0]; } @$result; + @names = map { s/.*\///; } @names; + print "TRACKS: ", join(', ', @names), "\n"; + return(@names); } sub tracks @@ -293,36 +293,55 @@ sub tracks return(()); # return $self->album_tracks($constraints[$#constraints]->{id}); } - return(); # FIXME - my $main_sql_start=("SELECT artists.name\n" . + + my $main_sql_start=("SELECT files.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; + "\tINNER JOIN files ON files.id=subselect.files_id" . + "\tGROUP BY files.name;"); + my @ids; while(my $constraint=shift @constraints) { print "CONSTRAINT: $constraint->{name}\n"; my $cid=$constraint->{id}; - push(@tag_ids, $cid); + push(@ids, $cid); } - @tag_ids = map( { "\"$_\""; } @tag_ids) unless($self->{postgres}); - my $tagstr=join(", ", @tag_ids); - my $sql = ($main_sql_start . $tagstr . + @ids = map( { "\"$_\""; } @ids) unless($self->{postgres}); + my $str=join(", ", @ids); + my $sql = ($main_sql_start . $str . $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); + my @names=map { $_->[0]; } @$result; + @names = map { s/.*\///; $_; } @names; + print "TRACKS: ", join(', ', @names), "\n"; + return(@names); +} + +sub filename +{ + my($self, @constraints)=@_; + if(ref($constraints[$#constraints]) eq "ID3FS::PathElement::File") + { + my $id=$constraints[$#constraints]->{id}; + my $sql=("SELECT paths.name, files.name FROM files\n" . + "INNER JOIN paths_x_files ON files.id=paths_x_files.files_id\n" . + "INNER JOIN paths ON paths_x_files.paths_id=paths.id\n" . + "WHERE files.id=?\n" . + "GROUP BY paths.name, files.name"); + print "FILENAME SQL: $sql\n"; + my ($path, $name)=$self->cmd_onerow($sql, $id); + return($self->{absbase} . "/$path/$name"); + } + die("DB::filename: unhandled case\n"); #FIXME } sub id { my($self, $type, $val)=@_; + print "ID: $type $val\n"; my $sql="SELECT id FROM $type WHERE name=?"; my ($id)=$self->cmd_onerow($sql, $val); return($id); @@ -331,6 +350,18 @@ sub id sub add { my($self,$path)=@_; + my $relpath=$path; + $relpath =~ s/$self->{base}\/?//; + my($filepart,$pathpart); + if($path !~ /\//) + { + $pathpart=''; + $filepart=$relpath; + } + else + { + ($pathpart, $filepart) = ($relpath =~ /(.*)\/(.*)/); + } my $file=ID3FS::File->new($path); return unless(defined($file)); my $artist=$file->artist(); @@ -341,14 +372,40 @@ sub add my $tags=$file->tags(); my $haspic=$file->haspic(); - my $file_id=$self->add_to_table("files", $path); - my $artists_id=$self->add_to_table("artists", $artist); - my $albums_id=$self->add_to_table("albums", $album); + my $file_id=$self->add_to_table("files", $filepart); + my $path_id=$self->add_to_table("paths", $pathpart); + $self->add_relation("paths_x_files", + { "paths_id" => $path_id, + "files_id" => $file_id}); + for my $tag (keys %$tags) { $self->add_tag($file_id, $tag, $tags->{$tag}); } + my $artist_id; + if($self->ok($artist)) + { + $artist_id=$self->add_to_table("artists", $artist); + $self->add_relation("files_x_artists", + { "files_id" => $file_id, + "artists_id" => $artist_id }); + } + + if($self->ok($album)) + { + my $albums_id=$self->add_to_table("albums", $album); + $self->add_relation("files_x_albums", + { "files_id" => $file_id, + "albums_id" => $albums_id}); + if($self->ok($artist)) + { + $self->add_relation("artists_x_albums", + { "artists_id" => $artist_id, + "albums_id" => $albums_id}); + } + } + if($self->ok($year)) { $self->add_tag($file_id, "year", $year); @@ -357,6 +414,7 @@ sub add $self->add_tag($file_id, "decade", "${1}0s"); } } + if($self->ok($v1genre)) { $self->add_tag($file_id, "v1genre", $v1genre); @@ -366,14 +424,6 @@ sub add { $self->add_tag($file_id, "haspic", undef); } - - $self->add_relation("files_x_artists", - { "files_id" => $file_id, - "artists_id" => $artists_id }); - - $self->add_relation("artists_x_albums", - { "artists_id" => $artists_id, - "albums_id" => $albums_id}); } sub add_tag @@ -453,7 +503,7 @@ sub relation_exists sub ok { my($self, $thing)=@_; - return(defined($thing) && length($thing)); + return(defined($thing) && length($thing) && $thing =~ /\S+/); } sub cmd @@ -509,6 +559,11 @@ CREATE TABLE files ( name text ); +CREATE TABLE paths ( + id INTEGER PRIMARY KEY, + name text +); + CREATE TABLE artists ( id INTEGER PRIMARY KEY, name text @@ -529,6 +584,11 @@ CREATE TABLE tagvals ( name text ); +CREATE TABLE paths_x_files ( + paths_id INTEGER, + files_id INTEGER +); + CREATE TABLE files_x_tags ( files_id INTEGER, tags_id INTEGER @@ -544,6 +604,11 @@ CREATE TABLE files_x_artists ( artists_id INTEGER ); +CREATE TABLE files_x_albums ( + files_id INTEGER, + albums_id INTEGER +); + CREATE TABLE artists_x_albums ( artists_id INTEGER, albums_id INTEGER diff --git a/lib/ID3FS/Fuse.pm b/lib/ID3FS/Fuse.pm index de81df9..166741b 100644 --- a/lib/ID3FS/Fuse.pm +++ b/lib/ID3FS/Fuse.pm @@ -82,7 +82,7 @@ sub readlink my($self,$filename)=@_; print "**READLINK: $filename\n"; my $path=ID3FS::Path->new($self->{db}, $filename); - return(-EINVAL()) if($path->isdir()); + return(-EINVAL()) if($path->isdir()); # FIXME: isfile() return $path->dest(); } diff --git a/lib/ID3FS/Path.pm b/lib/ID3FS/Path.pm index 36eaba2..1babe7d 100644 --- a/lib/ID3FS/Path.pm +++ b/lib/ID3FS/Path.pm @@ -12,7 +12,7 @@ use ID3FS::PathElement::Tagval; our ($STATE_INVALID, $STATE_ROOT, $STATE_TAG, $STATE_TAGVAL, $STATE_BOOLEAN, $STATE_ARTISTS, $STATE_ALBUMS, $STATE_TRACKLIST, - $STATE_FILE)=(0..9); + $STATE_FILE)=(0..8); sub new { diff --git a/lib/ID3FS/PathElement/File.pm b/lib/ID3FS/PathElement/File.pm index 3071cc3..71f1a10 100644 --- a/lib/ID3FS/PathElement/File.pm +++ b/lib/ID3FS/PathElement/File.pm @@ -10,7 +10,11 @@ sub new my $self={}; bless($self,$class); + $self->{db}=shift; $self->{name}=shift; + $self->{id}=$self->{db}->id("files", $self->{name}); + return(undef) unless(defined($self->{id})); + print "File ID: $self->{id}\n"; return $self; } -- 2.11.0