From 573f37531cdfe9266457315980580a5720313acd Mon Sep 17 00:00:00 2001
From: Ian Beckwith <ianb@erislabs.net>
Date: Sun, 26 Sep 2010 09:45:11 +0100
Subject: [PATCH] add foreign key constraints

---
 lib/ID3FS/DB.pm | 35 ++++++++++++++++++++++++-----------
 1 file changed, 24 insertions(+), 11 deletions(-)

diff --git a/lib/ID3FS/DB.pm b/lib/ID3FS/DB.pm
index 05b5f4f..0c344d9 100644
--- 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)=@_;
@@ -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,
+    artists_id,
+    albums_id,
+    paths_id,
+    name text,
+    FOREIGN KEY(artists_id) REFERENCES artists(id),
+    FOREIGN KEY(albums_id)  REFERENCES albums(id),
+    FOREIGN KEY(paths_id)   REFERENCES paths(id)
+);
+
 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),
+    FOREIGN KEY(tags_id) REFERENCES tags(id)
 );
 
 CREATE TABLE tags_x_tagvals (
     tags_id INTEGER,
-    tagvals_id INTEGER
+    tagvals_id INTEGER,
+    FOREIGN KEY(tags_id) REFERENCES tags(id),
+    FOREIGN KEY(tagvals_id) REFERENCES tagvals(id)
 );
-- 
2.11.0