remove unnecessary slash stripping - sanitised already on add

[id3fs.git] / lib / ID3FS / DB.pm

diff --git a/lib/ID3FS/DB.pm b/lib/ID3FS/DB.pm
index 93f1de7..b4c95aa 100644 (file)
--- a/lib/ID3FS/DB.pm
+++ b/lib/ID3FS/DB.pm
@@ -54,7 +54,7 @@ sub new
     {
        $self->create();
     }
-
+    $self->enable_foreign_keys();
     return $self;
 }
 
@@ -100,6 +100,12 @@ sub checkschema
     }
 }
 
+sub enable_foreign_keys
+{
+    my $self=shift;
+    $self->cmd("PRAGMA foreign_keys = ON");
+}
+
 sub last_update
 {
     my($self, $newval)=@_;
@@ -151,11 +157,10 @@ sub tags
     my $main_sql_end=")\n\tGROUP BY t2.name;";
     while(my $constraint=shift @constraints)
     {
-       print "CONSTRAINT: $constraint->{name}\n";
        my $cid=$constraint->{id};
        push(@ids, $cid);
     }
-    @ids = map( { "\"$_\""; } @ids) unless($self->{postgres});
+    @ids = map( { "\"$_\""; } grep { defined; } @ids) unless($self->{postgres});
     my $tagstr=join(", ", @ids);
     my $sql = ($main_sql_start . $tagstr .
               $main_sql_mid   . $tagstr .
@@ -199,11 +204,10 @@ sub artists
                      "\n\tGROUP BY artists.name;");
     while(my $constraint=shift @constraints)
     {
-       print "CONSTRAINT: $constraint->{name}\n";
        my $cid=$constraint->{id};
        push(@ids, $cid);
     }
-    @ids = map( { "\"$_\""; } @ids) unless($self->{postgres});
+    @ids = map( { "\"$_\""; } grep { defined; } @ids) unless($self->{postgres});
     my $tagstr=join(", ", @ids);
     my $sql = ($main_sql_start . $tagstr .
               $main_sql_end);
@@ -233,15 +237,13 @@ sub albums
                      "\n\tGROUP BY albums.name;");
     while(my $constraint=shift @constraints)
     {
-       print "CONSTRAINT: $constraint->{name}\n";
        my $cid=$constraint->{id};
        push(@ids, $cid);
     }
-    @ids = map( { "\"$_\""; } @ids) unless($self->{postgres});
+    @ids = map( { "\"$_\""; } grep { defined; } @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 @names=map { $_->[0]; } @$result;
     print "ALBUMS: ", join(', ', @names), "\n";
@@ -274,7 +276,6 @@ sub artist_tracks
     print "ARTIST_TRACKS SQL: $sql\n";
     my $result=$self->cmd_rows($sql, $artist_id);
     my @names=map { $_->[0]; } @$result;
-    @names = map { s/.*\///; $_; } @names;
     print "ARTISTTRACKS: ", join(', ', @names), "\n";
     return(@names);
 }
@@ -290,7 +291,6 @@ sub album_tracks
     print "ALBUM_TRACKS SQL($artist_id, $album_id): $sql\n";
     my $result=$self->cmd_rows($sql, $artist_id, $album_id);
     my @names=map { $_->[0]; } @$result;
-    @names = map { s/.*\///; $_;} @names;
     print "TRACKS: ", join(', ', @names), "\n";
     return(@names);
 }
@@ -325,18 +325,16 @@ sub tracks
     my @ids;
     while(my $constraint=shift @constraints)
     {
-       print "CONSTRAINT: $constraint->{name}\n";
        my $cid=$constraint->{id};
        push(@ids, $cid);
     }
-    @ids = map( { "\"$_\""; } @ids) unless($self->{postgres});
+    @ids = map( { "\"$_\""; } grep { defined; } @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 @names=map { $_->[0]; } @$result;
-    @names = map { s/.*\///; $_; } @names;
     print "TRACKS: ", join(', ', @names), "\n";
     return(@names);
 }
@@ -358,10 +356,37 @@ sub filename
     die("DB::filename: unhandled case\n"); #FIXME
 }
 
+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" .
+            "GROUP BY tags.name\n");
+    my $result=$self->cmd_rows($sql);
+    my @names=map { $_->[0]; } @$result;
+    return (@names);
+}
+
+sub tags_with_values
+{
+    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 $result=$self->cmd_rows($sql);
+    my $tags={};
+    for my $pair (@$result)
+    {
+       push(@{$tags->{$pair->[0]}}, $pair->[1]);
+    }
+    return $tags;
+}
+
 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);
@@ -566,14 +591,6 @@ CREATE TABLE id3fs (
     last_update
 );
 
-CREATE TABLE files (
-    id INTEGER PRIMARY KEY,
-    artists_id,
-    albums_id,
-    paths_id,
-    name text
-);
-
 CREATE TABLE paths (
     id INTEGER PRIMARY KEY,
     name text
@@ -589,6 +606,17 @@ CREATE TABLE albums (
     name text
 );
 
+CREATE TABLE files (
+    id INTEGER PRIMARY KEY,
+    name text,
+    artists_id,
+    albums_id,
+    paths_id,
+    FOREIGN KEY(artists_id) REFERENCES artists(id) ON DELETE CASCADE ON UPDATE CASCADE,
+    FOREIGN KEY(albums_id)  REFERENCES albums(id)  ON DELETE CASCADE ON UPDATE CASCADE,
+    FOREIGN KEY(paths_id)   REFERENCES paths(id)   ON DELETE CASCADE ON UPDATE CASCADE
+);
+
 CREATE TABLE tags (
     id INTEGER PRIMARY KEY,
     name text
@@ -601,10 +629,14 @@ CREATE TABLE tagvals (
 
 CREATE TABLE files_x_tags (
     files_id INTEGER,
-    tags_id INTEGER
+    tags_id INTEGER,
+    FOREIGN KEY(files_id) REFERENCES files(id) ON DELETE CASCADE ON UPDATE CASCADE,
+    FOREIGN KEY(tags_id)  REFERENCES tags(id)  ON DELETE CASCADE ON UPDATE CASCADE
 );
 
 CREATE TABLE tags_x_tagvals (
     tags_id INTEGER,
-    tagvals_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
 );