support postgres as well as sqlite (for testing)

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

diff --git a/lib/ID3FS/DB.pm b/lib/ID3FS/DB.pm
index af325ba..f9ae1dd 100644 (file)
--- a/lib/ID3FS/DB.pm
+++ b/lib/ID3FS/DB.pm
@@ -6,7 +6,6 @@ use DBI;
 use ID3FS::File;
 
 our $SCHEMA_VERSION=1;
-
 my $dbfile=".id3fs";
 
 sub new
@@ -21,10 +20,24 @@ sub new
     $self->{me}=$me;
 
     my $exists=-f $self->{dbpath};
-    die("$me: $self->{dbpath}: not found. use --init to create.\n") if(!$exists && !$init);
-    die("$me: --init used but $self->{dbpath} exists.\n")           if($exists && $init);
 
-    $self->{dbh}=DBI->connect("dbi:SQLite:dbname=$self->{dbpath}","","",
+    $self->{postgres}=0;
+
+    unless($self->{postgres})
+    {
+       die("$me: $self->{dbpath}: not found. use --init to create.\n") if(!$exists && !$init);
+       die("$me: --init used but $self->{dbpath} exists.\n")           if($exists && $init);
+    }
+
+    my $connectstr="dbi:SQLite:dbname=$self->{dbpath}";
+    my ($user, $pass)=("", "");
+    if($self->{postgres})
+    {
+       $connectstr="dbi:Pg:dbname=id3fs";
+       $user="ianb";
+       $pass="foo";
+    }
+    $self->{dbh}=DBI->connect($connectstr, $user, $pass,
                              { AutoCommit=>1 } );
     unless(defined($self->{dbh}))
     {
@@ -52,6 +65,10 @@ sub create
     {
        $self->{dbh}->do($cmd);
     }
+    if($self->{postgres})
+    {
+       $self->cmd("CREATE SEQUENCE seq");
+    }
     $self->cmd("INSERT INTO id3fs (schema_version) VALUES (?)", $SCHEMA_VERSION);
 }
 
@@ -81,6 +98,62 @@ sub cmd_sth
     return $sth;
 }
 
+sub tags
+{
+    my($self, @constraints)=@_;
+    if(!@constraints) # /
+    {
+       my $sql="SELECT DISTINCT name FROM tags;";
+       my $tags=$self->cmd_rows($sql);
+       return(map { $_->[0]; } @$tags);
+    }
+    my @file_ids=();
+    my @tag_ids=();
+    my $main_sql_start=("SELECT DISTINCT tags.name FROM files\n" .
+                       "INNER JOIN files_x_tags ON files.id=files_x_tags.files_id\n" .
+                       "INNER JOIN tags ON tags.id=files_x_tags.tags_id\n" .
+                       "WHERE files.id in (" .
+                       ("\tSELECT DISTINCT files.id FROM files\n" .
+                        "\tINNER JOIN files_x_tags ON files.id=files_x_tags.files_id\n" .
+                        "\tINNER JOIN tags ON tags.id=files_x_tags.tags_id\n" .
+                        "\tWHERE tags.id in ("));
+    my $main_sql_mid=")\n) AND tags.id NOT IN (";
+    my $main_sql_end=")\n";
+    while(my $constraint=shift @constraints)
+    {
+       print "CONSTRAINT: $constraint->{name}\n";
+       my $cid=$constraint->{id};
+       push(@tag_ids, $cid);
+    }
+    my $sql = ($main_sql_start . join(", ", @tag_ids) .
+              $main_sql_mid   . join(", ", @tag_ids) .
+              $main_sql_end);
+    print "SQL: $sql\n";
+    my $result=$self->cmd_rows($sql);
+    my @tagnames=map { $_->[0]; } @$result;
+    print "SUBNAMES: ", join(', ', @tagnames), "\n";
+    return(@tagnames);
+}
+
+sub tag_values
+{
+    my($self, $tag)=@_;
+    my $sql=("SELECT DISTINCT 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" .
+            "WHERE tags.name=?");
+    my $tags=$self->cmd_rows($sql, $tag);
+    return(map { $_->[0]; } @$tags);
+}
+
+sub tag_id
+{
+    my($self, $tag)=@_;
+    my $sql='SELECT id FROM tags WHERE name=?';
+    my ($id)=$self->cmd_onerow($sql, $tag);
+    return($id);
+}
+
 sub add
 {
     my($self,$path)=@_;
@@ -152,6 +225,7 @@ sub add_to_table
     unless(defined($id))
     {
        my $sql="INSERT INTO $table (";
+       $sql .= "id, " if($self->{postgres});
        my @fields=qw(name);
        if(defined($extradata))
        {
@@ -159,6 +233,7 @@ sub add_to_table
        }
        $sql .= join(", ", @fields);
        $sql .=") VALUES (";
+       $sql .=") nextval('seq'), " if($self->{postgres});
        $sql .= join(", ", map { "?"; } @fields);
        $sql .= ");";
        $id=$self->cmd_id($sql, $name, map { $extradata->{$_} || ""; } sort keys %$extradata);
@@ -221,6 +296,13 @@ sub cmd_onerow
     return($sth->fetchrow_array());
 }
 
+sub cmd_rows
+{
+    my ($self, @args)=@_;
+    my $sth=$self->cmd_sth(@args);
+    return $sth->fetchall_arrayref();
+}
+
 sub cmd_id
 {
     my ($self, @args)=@_;
@@ -231,56 +313,64 @@ sub cmd_id
 sub last_insert_id
 {
     my $self=shift;
-    return $self->{dbh}->last_insert_id("","","","");
+    if($self->{postgres})
+    {
+       return $self->{dbh}->last_insert_id(undef, undef, undef, undef,
+                                           { sequence => "seq" });
+    }
+    else
+    {
+       return $self->{dbh}->last_insert_id("","","","");
+    }
 }
 
 __DATA__
 
 CREATE TABLE id3fs (
-    schema_version
+    schema_version INTEGER
 );
 
 CREATE TABLE files (
     id INTEGER PRIMARY KEY,
-    name
+    name text
 );
 
 CREATE TABLE artists (
     id INTEGER PRIMARY KEY,
-    name
+    name text
 );
 
 CREATE TABLE albums (
     id INTEGER PRIMARY KEY,
-    name
+    name text
 );
 
 CREATE TABLE tags (
     id INTEGER PRIMARY KEY,
-    name
+    name text
 );
 
 CREATE TABLE tagvals (
     id INTEGER PRIMARY KEY,
-    name
+    name text
 );
 
 CREATE TABLE files_x_tags (
-    files_id,
-    tags_id
+    files_id INTEGER,
+    tags_id INTEGER
 );
 
 CREATE TABLE tags_x_tagvals (
-    tags_id,
-    tagvals_id
+    tags_id INTEGER,
+    tagvals_id INTEGER
 );
 
 CREATE TABLE files_x_artists (
-    files_id,
-    artists_id
+    files_id INTEGER,
+    artists_id INTEGER
 );
 
 CREATE TABLE artists_x_albums (
-    artists_id,
-    albums_id
+    artists_id INTEGER,
+    albums_id INTEGER
 );