filter constraint ids, drop undefs

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

diff --git a/lib/ID3FS/DB.pm b/lib/ID3FS/DB.pm
index 05b5f4f..8794ca1 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)=@_;
@@ -154,7 +160,7 @@ sub tags
        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 .
@@ -201,7 +207,7 @@ sub artists
        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);
@@ -234,7 +240,7 @@ sub albums
        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);
@@ -324,7 +330,7 @@ sub tracks
        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);
@@ -560,14 +566,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
@@ -583,6 +581,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
@@ -595,10 +604,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
 );