X-Git-Url: http://erislabs.net/gitweb/?a=blobdiff_plain;f=lib%2FID3FS%2FDB.pm;h=96b88f94cd3aacb293e1e410cccf859c30f0ec3f;hb=23de00b9fa92c111bce2e40f5e778566d9d90949;hp=d149b6addf31a4b87a8045e6bddfa47d600295fb;hpb=718f65e679aed209f98961cbdf655bfed44ca1a4;p=id3fs.git

diff --git a/lib/ID3FS/DB.pm b/lib/ID3FS/DB.pm
index d149b6a..96b88f9 100644
--- a/lib/ID3FS/DB.pm
+++ b/lib/ID3FS/DB.pm
@@ -94,6 +94,8 @@ sub find_db
     return $file;
 }
 
+sub base_dir { return shift->{base}; }
+
 sub create
 {
     my($self,$name)=@_;
@@ -136,6 +138,12 @@ sub checkschema
     }
 }
 
+sub analyze
+{
+    my $self=shift;
+    $self->cmd("ANALYZE");
+}
+
 sub enable_foreign_keys
 {
     my $self=shift;
@@ -176,31 +184,47 @@ sub tags
     if(!@constraints) # /
     {
 	# FIXME: add ALL?
-	my $sql="SELECT DISTINCT name FROM tags;";
+	my $sql="SELECT DISTINCT name FROM tags WHERE parents_id='';";
 	my $tags=$self->cmd_rows($sql);
 	return(map { $_->[0]; } @$tags);
     }
     my @ids=();
 
-    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 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)
-    {
-	my $cid=$constraint->{id};
-	push(@ids, $cid);
-    }
-    @ids = map( { "\"$_\""; } grep { defined; } @ids) unless($self->{postgres});
-    my $tagstr=join(", ", @ids);
-    my $sql = ($main_sql_start . $tagstr .
-	       $main_sql_mid   . $tagstr .
-	       $main_sql_end);
+    my $sql=("SELECT t2.name FROM (\n" .
+	     $self->tags_subselect(@constraints) .
+	     ") AS subselect\n" .
+	     "INNER JOIN files_x_tags ON subselect.files_id=files_x_tags.files_id\n" .
+	     "INNER JOIN tags t2 ON files_x_tags.tags_id=t2.id\n");
+    my ($tags, $tags_vals, $parent)=$self->constraints_tag_list(@constraints);
+    my @tags=@$tags;
+    my @tags_vals=@$tags_vals;;
+    my @orclauses=();
+    my @andclauses=();
+    use Data::Dumper;
+    print "TAGS: ", Dumper \@tags;
+    print "VALS: ", Dumper \@tags_vals;
+
+    push(@andclauses, "( t2.parents_id=" . (defined($parent) ? $parent : "''") . " )");
+    if(@tags)
+    {
+	push(@orclauses, "( t2.id NOT IN ( " . join(', ', @tags) ." ) )");
+    }
+    for my $pair (@tags_vals)
+    {
+	my($tag, $val)=@$pair;
+#	push(@orclauses, "( NOT ( t2.parents_id=$tag AND t2.id=$val ) )");
+	push(@andclauses, "( NOT ( t2.id=$tag ) )");
+    }
+    if(@orclauses)
+    {
+	push(@andclauses, join("\n\tOR ", @orclauses));
+    }
+    if(@andclauses)
+    {
+	$sql .= "\tWHERE\n\t\t";
+	$sql .= join("\n\tAND ", @andclauses) . "\n";
+    }
+    $sql .= "GROUP BY t2.name;";
     print "SQL: $sql\n";
     my $result=$self->cmd_rows($sql);
     my @tagnames=map { $_->[0]; } @$result;
@@ -211,9 +235,8 @@ sub tags
 sub tag_values
 {
     my($self, $tagid)=@_;
-    my $sql=("SELECT DISTINCT tagvals.name FROM tagvals\n" .
-	     "INNER JOIN tags_x_tagvals ON tagvals.id=tags_x_tagvals.tagvals_id\n" .
-	     "WHERE tags_x_tagvals.tags_id=?");
+    my $sql=("SELECT DISTINCT name FROM tags\n" .
+	     "WHERE parents_id=?");
     my $tags=$self->cmd_rows($sql, $tagid);
     my @tags=map { $_->[0]; } @$tags;
     @tags=map { length($_) ? $_ : "NOVALUE"; } @tags;
@@ -230,23 +253,12 @@ sub artists
 	return(map { $_->[0]; } @$tags);
     }
     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" .
-			"\t\tWHERE tags.id in\n\t\t\t(");
-    my $main_sql_end=(")\n\t\t) AS subselect\n" .
-		      "\tINNER JOIN files ON subselect.files_id=files.id\n" .
-		      "\tINNER JOIN artists ON files.artists_id=artists.id\n" .
-		      "\n\tGROUP BY artists.name;");
-    while(my $constraint=shift @constraints)
-    {
-	my $cid=$constraint->{id};
-	push(@ids, $cid);
-    }
-    @ids = map( { "\"$_\""; } grep { defined; } @ids) unless($self->{postgres});
-    my $tagstr=join(", ", @ids);
-    my $sql = ($main_sql_start . $tagstr .
-	       $main_sql_end);
+    my $sql=("SELECT artists.name FROM (\n" .
+	     $self->tags_subselect(@constraints) .
+	     ") AS subselect\n" .
+	     "INNER JOIN files ON subselect.files_id=files.id\n" .
+	     "INNER JOIN artists ON files.artists_id=artists.id\n" .
+	     "GROUP BY artists.name;");
     print "SQL: $sql\n";
     my $result=$self->cmd_rows($sql);
     my @tagnames=map { $_->[0]; } @$result;
@@ -261,25 +273,15 @@ sub albums
     # FIXME: rework PathElements
     if(ref($constraints[$#constraints]) eq "ID3FS::PathElement::Artist")
     {
-	return $self->artist_albums($constraints[$#constraints]->{id});
-    }
-    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 ON subselect.files_id=files.id\n" .
-		      "\tINNER JOIN albums ON files.albums_id=albums.id\n" .
-		      "\n\tGROUP BY albums.name;");
-    while(my $constraint=shift @constraints)
-    {
-	my $cid=$constraint->{id};
-	push(@ids, $cid);
-    }
-    @ids = map( { "\"$_\""; } grep { defined; } @ids) unless($self->{postgres});
-    my $str=join(", ", @ids);
-    my $sql = ($main_sql_start . $str .
-	       $main_sql_end);
+	return $self->artist_albums($constraints[$#constraints]->{id}, @constraints);
+    }
+    my $sql=("SELECT albums.name\n" .
+	     "\tFROM (\n" .
+	     $self->tags_subselect(@constraints) .
+	     "\t) AS subselect\n" .
+	     "INNER JOIN files ON subselect.files_id=files.id\n" .
+	     "INNER JOIN albums ON files.albums_id=albums.id\n" .
+	     "GROUP BY albums.name;");
     my $result=$self->cmd_rows($sql);
     my @names=map { $_->[0]; } @$result;
     print "ALBUMS: ", join(', ', @names), "\n";
@@ -288,8 +290,11 @@ sub albums
 
 sub artist_albums
 {
-    my($self, $artist_id)=@_;
-    my $sql=("SELECT albums.name FROM files\n\t" .
+    my($self, $artist_id, @constraints)=@_;
+    my $sql=("SELECT albums.name FROM (\n" .
+	     $self->tags_subselect(@constraints) .
+	     "\t) AS subselect\n" .
+	     "INNER JOIN files ON subselect.files_id=files.id\n" .
 	     "INNER JOIN albums ON albums.id=files.albums_id\n\t" .
 	     "INNER JOIN artists ON artists.id=files.artists_id\n\t" .
 	     "WHERE artists.id=? and albums.name <> ''\n\t" .
@@ -303,8 +308,11 @@ sub artist_albums
 
 sub artist_tracks
 {
-    my($self, $artist_id)=@_;
-    my $sql=("SELECT files.name FROM files\n\t" .
+    my($self, $artist_id, @constraints)=@_;
+    my $sql=("SELECT files.name FROM (\n" .
+	     $self->tags_subselect(@constraints) .
+	     "\t) AS subselect\n" .
+	     "INNER JOIN files ON subselect.files_id=files.id\n" .
 	     "INNER JOIN artists ON artists.id=files.artists_id\n\t" .
 	     "INNER JOIN albums  ON albums.id=files.albums_id\n\t" .
 	     "WHERE artists.id=? AND albums.name=''\n\t" .
@@ -337,7 +345,7 @@ sub tracks
     # FIXME: rework PathElements
     if(ref($constraints[$#constraints]) eq "ID3FS::PathElement::Artist")
     {
-	return $self->artist_tracks($constraints[$#constraints]->{id});
+	return $self->artist_tracks($constraints[$#constraints]->{id}, @constraints);
     }
     elsif(ref($constraints[$#constraints]) eq "ID3FS::PathElement::Album")
     {
@@ -351,23 +359,12 @@ sub tracks
 	return $self->album_tracks($artist_id, $constraints[$#constraints]->{id});
     }
 
-    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 ON files.id=subselect.files_id" .
-		      "\tGROUP BY files.name;");
-    my @ids;
-    while(my $constraint=shift @constraints)
-    {
-	my $cid=$constraint->{id};
-	push(@ids, $cid);
-    }
-    @ids = map( { "\"$_\""; } grep { defined; } @ids) unless($self->{postgres});
-    my $str=join(", ", @ids);
-    my $sql = ($main_sql_start . $str .
-	       $main_sql_end);
+    my $sql=("SELECT files.name\n" .
+	     "\tFROM (\n" .
+	     $self->tags_subselect(@constraints) .
+	     "\t) AS subselect\n" .
+	     "INNER JOIN files ON files.id=subselect.files_id\n" .
+	     "GROUP BY files.name;");
     print "SQL: $sql\n";
     my $result=$self->cmd_rows($sql);
     my @names=map { $_->[0]; } @$result;
@@ -377,7 +374,7 @@ sub tracks
 
 sub filename
 {
-    my($self, @constraints)=@_;
+    my($self, $mountpoint, @constraints)=@_;
     if(ref($constraints[$#constraints]) eq "ID3FS::PathElement::File")
     {
 	my $id=$constraints[$#constraints]->{id};
@@ -387,17 +384,124 @@ sub filename
 		 "GROUP BY paths.name, files.name");
 	print "FILENAME SQL: $sql\n";
 	my ($path, $name)=$self->cmd_onerow($sql, $id);
-	return($self->{absbase} . "/$path/$name");
+	my $id3fs_path=join('/', map { $_->{name}; }  @constraints);
+	return($self->relativise($path, $name, $mountpoint, $id3fs_path));
     }
     die("DB::filename: unhandled case\n"); #FIXME
 }
 
+sub tags_subselect
+{
+    my($self,@constraints)=@_;
+    my ($tags, $tags_vals, $parent)=$self->constraints_tag_list(@constraints);
+    my @tags=@$tags;
+    my @tags_vals=@$tags_vals;;
+
+    my $sql=("\tSELECT files_x_tags.files_id FROM tags t1\n" .
+	     "\tINNER JOIN files_x_tags ON t1.id=files_x_tags.tags_id\n");
+    my @orclauses=();
+    my @andclauses=();
+    if(@tags)
+    {
+	push(@andclauses, "( t1.parents_id=" . (defined($parent) ? $parent : "''") . " )");
+	push(@andclauses, "( t1.id IN ( " . join(', ', @tags) ." ) )");
+    }
+    for my $pair (@tags_vals)
+    {
+	my($tag, $val)=@$pair;
+	push(@orclauses, "( t1.parents_id=$tag AND t1.id=$val )");
+    }
+#    push(@andclauses, "( t1.parents_id=" . (defined($parent) ? $parent : "''") . " )");
+    if(@orclauses)
+    {
+	push(@andclauses, join("\n\t\tOR ", @orclauses));
+    }
+    if(@andclauses)
+    {
+	$sql .= "\tWHERE\n\t\t";
+	$sql .= join("\n\t\tAND ", @andclauses) . "\n";
+    }
+    $sql .= "\tGROUP BY files_x_tags.files_id\n";
+    return $sql;
+}
+
+sub constraints_tag_list
+{
+    my($self, @constraints)=@_;
+    my $lasttag=undef;
+    my @tags=();
+    my @tags_vals=();
+    for my $constraint (@constraints)
+    {
+	print ref($constraint), ": ", $constraint->{name}, "\n";
+	if(ref($constraint) eq "ID3FS::PathElement::Tag")
+	{
+	    if(defined($lasttag))
+	    {
+		print "TAGVAL\n";
+		push(@tags_vals, [$lasttag, $constraint->{id}]) if defined($constraint->{id});
+		$lasttag=undef;
+	    }
+	    elsif($self->tag_has_values($constraint->{id}))
+	    {
+		print "HASVALUES\n";
+		$lasttag=$constraint->{id} if defined($constraint->{id});
+	    }
+	    else
+	    {
+		print "NOVALUES\n";
+		push(@tags, $constraint->{id}) if(defined($constraint->{id}));
+	    }
+	}
+    }
+    unless($self->{postgres})
+    {
+	@tags=map{ "\"$_\""; } @tags;
+	@tags_vals=map( { [ map({ "\"$_\""; } @$_ ) ] } @tags_vals);
+	$lasttag="\"$lasttag\"" if defined($lasttag);
+    }
+    return(\@tags, \@tags_vals, $lasttag);
+}
+
+
+sub relativise
+{
+    my($self, $path, $name, $mountpoint, $id3fs_path)=@_;
+    $id3fs_path=~s/(.*)\/.*/$1/;
+    my $rpath="$self->{absbase}/$path";
+    my $vpath="$mountpoint/$id3fs_path";
+    my @path=split(/\//,$rpath);
+    my @rel=split(/\//,$vpath);
+    #absolute paths have empty first element due to leading /
+    shift(@path) if($path[0] eq "");
+    shift(@rel)  if($rel[0]  eq "");
+    if($path[0] ne $rel[0])
+    {
+	#no path in common, return absolute
+	print "FAIL: NO PATHS IN COMMON\n";
+	return $name;
+    }
+    # f: /home/foo/bar/baz.mp3
+    # r: /home/ianb/music/albums
+    while(@path && @rel && ($path[0] eq $rel[0]))
+    {
+	shift(@path);
+	shift(@rel);
+	print "POP ";
+    }
+    print "\n";
+    my $upcount=scalar(@rel);
+    my $result="../" x $upcount;
+    $result .= join("/",@path);
+    $result .= "/$name";
+    return $result;
+}
+
 sub bare_tags
 {
     my($self)=@_;
     my $sql=("SELECT tags.name FROM tags\n" .
-	     "LEFT JOIN tags_x_tagvals ON tags.id=tags_x_tagvals.tags_id\n" .
-	     "WHERE tags_x_tagvals.tags_id IS NULL\n" .
+	     "WHERE tags.parents_id=''\n" .
 	     "GROUP BY tags.name\n");
     my $result=$self->cmd_rows($sql);
     my @names=map { $_->[0]; } @$result;
@@ -406,11 +510,12 @@ sub bare_tags
 
 sub tags_with_values
 {
+    # FIXME: only shows one level of tag depth
     my($self)=@_;
-    my $sql=("SELECT tags.name, tagvals.name FROM tags\n" .
-	     "INNER JOIN tags_x_tagvals ON tags.id=tags_x_tagvals.tags_id\n" .
-	     "INNER JOIN tagvals ON tagvals.id=tags_x_tagvals.tagvals_id\n" .
-	     "GROUP BY tags.name, tagvals.name\n");
+    my $sql=("SELECT p.name, t.name  FROM tags t\n" .
+	     "INNER JOIN tags p ON t.parents_id=p.id\n" .
+	     "GROUP BY p.name, t.name\n");
+#    print "SQL: $sql\n";
     my $result=$self->cmd_rows($sql);
     my $tags={};
     for my $pair (@$result)
@@ -443,14 +548,14 @@ sub add
     {
 	($pathpart, $filepart) = ($relpath =~ /(.*)\/(.*)/);
     }
-    my $file=ID3FS::AudioFile->new($path);
+    my $file=ID3FS::AudioFile->new($path, $self->{me});
     return unless(defined($file));
     my $artist=$file->artist();
     my $album=$file->album();
     my $v1genre=$file->v1genre();
     my $year=$file->year();
     my $audiotype=$file->audiotype();
-    my $tags=$file->tags();
+    my @tags=$file->tags();
     my $haspic=$file->haspic();
 
     $artist=undef unless($self->ok($artist));
@@ -468,9 +573,9 @@ sub add
 				    { "artists_id" => $artist_id,
 				      "albums_id"  => $albums_id,
 				      "paths_id"   => $path_id });
-    for my $tag (keys %$tags)
+    for my $tag (@tags)
     {
-	$self->add_tag($file_id, $tag, $tags->{$tag});
+	$self->add_tag($file_id, @$tag);
     }
 
     if($self->ok($year))
@@ -495,17 +600,19 @@ sub add
 
 sub add_tag
 {
-    my($self, $file_id, $tag, $val)=@_;
-    my $tag_id=$self->add_to_table("tags",  $tag);
+    my($self, $file_id, $tag, $value)=@_;
+    my $tag_id=$self->add_to_table("tags",  $tag,
+				   { "parents_id" => undef });
     $self->add_relation("files_x_tags",
 			{ "files_id" => $file_id,
 			  "tags_id"  => $tag_id });
-    if(defined($val))
+    if(defined($value) && length($value))
     {
-	my $val_id=$self->add_to_table("tagvals", $val);
-	$self->add_relation("tags_x_tagvals",
-			    { "tags_id"     => $tag_id,
-			      "tagvals_id"  => $val_id });
+	my $val_id=$self->add_to_table("tags",  $value,
+				       { "parents_id" => $tag_id });
+	$self->add_relation("files_x_tags",
+			    { "files_id" => $file_id,
+			      "tags_id"  => $val_id });
     }
 }
 
@@ -555,9 +662,7 @@ sub tag_has_values
 {
     my($self, $id)=@_;
     my $sql=("SELECT COUNT(*) FROM tags\n\t" .
-	     "INNER JOIN tags_x_tagvals ON tags.id=tags_x_tagvals.tags_id\n\t" .
-	     "INNER JOIN tagvals ON tagvals.id=tags_x_tagvals.tagvals_id\n\t" .
-	     "WHERE tags.id=?\n");
+	     "WHERE tags.parents_id=?\n");
     my ($rows)=$self->cmd_onerow($sql, $id);
     return $rows;
 }
@@ -566,7 +671,6 @@ sub files_in
 {
     my ($self, $dir)=@_;
     $dir=~s/^$self->{base}\/?//;
-#    print "Munged dir: $dir\n";
     my $sql=("SELECT files.name FROM files\n" .
 	     "INNER JOIN paths ON files.paths_id=paths.id\n" .
 	     "WHERE paths.name=?\n");
@@ -584,7 +688,6 @@ sub prune_directories
     {
 	my($path, $id)=@$pathpair;
 	my $fullpath="$self->{absbase}/$path";
-#	print "PRUNING PATH $fullpath: ";
 	unless(-d $fullpath)
 	{
 	    push(@ids, $id)
@@ -633,17 +736,8 @@ sub remove_unused
        LEFT JOIN files_x_tags ON files_x_tags.tags_id=tags.id
        WHERE files_x_tags.files_id IS NULL);
 
-   DELETE FROM tags_x_tagvals WHERE tags_id IN (
-       SELECT tags_x_tagvals.tags_id FROM tags_x_tagvals
-       LEFT JOIN tags ON tags.id=tags_x_tagvals.tags_id
-       WHERE tags.id IS NULL);
-
-   DELETE FROM tagvals WHERE id IN (
-       SELECT tagvals.id FROM tagvals
-       LEFT JOIN tags_x_tagvals ON tags_x_tagvals.tagvals_id=tagvals.id
-       WHERE tags_x_tagvals.tagvals_id IS NULL);
 EOT
-    print "SQL: $sql\n";
+#    print "SQL: $sql\n";
     my @sql=split(/\n\n/, $sql);
     $self->cmd($_) for (@sql);
 }
@@ -747,11 +841,7 @@ CREATE TABLE files (
 
 CREATE TABLE tags (
     id INTEGER PRIMARY KEY,
-    name text
-);
-
-CREATE TABLE tagvals (
-    id INTEGER PRIMARY KEY,
+    parents_id INTEGER,
     name text
 );
 
@@ -762,10 +852,3 @@ CREATE TABLE files_x_tags (
     FOREIGN KEY(tags_id)  REFERENCES tags(id)  ON DELETE CASCADE ON UPDATE CASCADE
 );
 
-CREATE TABLE tags_x_tagvals (
-    tags_id INTEGER,
-    tagvals_id INTEGER,
-    FOREIGN KEY(tags_id) REFERENCES tags(id) ON DELETE CASCADE ON UPDATE CASCADE,
-    FOREIGN KEY(tagvals_id) REFERENCES tagvals(id) ON DELETE CASCADE ON UPDATE CASCADE
-);
-