14 my $class=ref($proto) || $proto;
18 my($dir, $init, $me)=@_;
19 $self->{dbpath}="$dir/$dbfile";
22 my $exists=-f $self->{dbpath};
26 unless($self->{postgres})
28 die("$me: $self->{dbpath}: not found. use --init to create.\n") if(!$exists && !$init);
29 die("$me: --init used but $self->{dbpath} exists.\n") if($exists && $init);
32 my $connectstr="dbi:SQLite:dbname=$self->{dbpath}";
33 my ($user, $pass)=("", "");
36 $connectstr="dbi:Pg:dbname=id3fs";
40 $self->{dbh}=DBI->connect($connectstr, $user, $pass,
42 unless(defined($self->{dbh}))
44 die("$me: DB Error: " . $DBI::errstr . "\n");
62 my @schema=split(/\n\n/,join("", <DATA>));
66 $self->{dbh}->do($cmd);
70 $self->cmd("CREATE SEQUENCE seq");
72 $self->cmd("INSERT INTO id3fs (schema_version) VALUES (?)", $SCHEMA_VERSION);
78 my ($version)=$self->cmd_onerow("SELECT schema_version from id3fs");
79 if(!defined($version) || $version != $SCHEMA_VERSION)
81 die("$self->{me}: id3fs database version " .
82 defined($version) ? $version : '""' .
83 "not known, current version is $SCHEMA_VERSION.\n");
89 my($self, $sql, @params)=@_;
90 my $sth=$self->{dbh}->prepare($sql);
92 for my $param (@params)
94 $param="" unless(defined($param));
95 $sth->bind_param($idx++, $param);
103 my($self, @constraints)=@_;
104 if(!@constraints) # /
106 my $sql="SELECT DISTINCT name FROM tags;";
107 my $tags=$self->cmd_rows($sql);
108 return(map { $_->[0]; } @$tags);
112 my $main_sql_start=("SELECT tags.name FROM files\n" .
113 " INNER JOIN files_x_tags fxt1 ON files.id=fxt1.files_id\n" .
114 " INNER JOIN files_x_tags fxt2 ON files.id=fxt2.files_id\n" .
115 " INNER JOIN tags ON tags.id=fxt2.tags_id\n" .
116 " WHERE fxt1.tags_id IN \n\t(");
117 my $main_sql_mid=")\n\tAND fxt2.tags_id NOT IN \n\t(";
118 my $main_sql_end=")\n GROUP BY tags.name;";
119 while(my $constraint=shift @constraints)
121 print "CONSTRAINT: $constraint->{name}\n";
122 my $cid=$constraint->{id};
123 push(@tag_ids, $cid);
125 @tag_ids = map( { "\"$_\""; } @tag_ids) unless($self->{postgres});
126 my $tagstr=join(", ", @tag_ids);
127 my $sql = ($main_sql_start . $tagstr .
128 $main_sql_mid . $tagstr .
131 my $result=$self->cmd_rows($sql);
132 my @tagnames=map { $_->[0]; } @$result;
133 print "SUBNAMES: ", join(', ', @tagnames), "\n";
140 my $sql=("SELECT DISTINCT tagvals.name FROM tags\n" .
141 "INNER JOIN tags_x_tagvals ON tags.id=tags_x_tagvals.tags_id\n" .
142 "INNER JOIN tagvals ON tagvals.id=tags_x_tagvals.tagvals_id\n" .
143 "WHERE tags.name=?");
144 my $tags=$self->cmd_rows($sql, $tag);
145 return(map { $_->[0]; } @$tags);
151 my $sql='SELECT id FROM tags WHERE name=?';
152 my ($id)=$self->cmd_onerow($sql, $tag);
159 my $file=ID3FS::File->new($path);
160 return unless(defined($file));
161 my $artist=$file->artist();
162 my $album=$file->album();
163 my $v1genre=$file->v1genre();
164 my $year=$file->year();
165 my $audiotype=$file->album();
166 my $tags=$file->tags();
167 my $haspic=$file->haspic();
169 my $file_id=$self->add_to_table("files", $path);
170 my $artists_id=$self->add_to_table("artists", $artist);
171 my $albums_id=$self->add_to_table("albums", $album);
172 for my $tag (keys %$tags)
174 $self->add_tag($file_id, $tag, $tags->{$tag});
179 $self->add_tag($file_id, "year", $year);
180 if($year=~/^(\d\d\d)\d$/)
182 $self->add_tag($file_id, "decade", "${1}0s");
185 if($self->ok($v1genre))
187 $self->add_tag($file_id, "v1genre", $v1genre);
192 $self->add_tag($file_id, "haspic", undef);
195 $self->add_relation("files_x_artists",
196 { "files_id" => $file_id,
197 "artists_id" => $artists_id });
199 $self->add_relation("artists_x_albums",
200 { "artists_id" => $artists_id,
201 "albums_id" => $albums_id});
206 my($self, $file_id, $tag, $val)=@_;
207 my $tag_id=$self->add_to_table("tags", $tag);
208 $self->add_relation("files_x_tags",
209 { "files_id" => $file_id,
210 "tags_id" => $tag_id });
213 my $val_id=$self->add_to_table("tagvals", $val);
214 $self->add_relation("tags_x_tagvals",
215 { "tags_id" => $tag_id,
216 "tagvals_id" => $val_id });
222 my($self, $table, $name, $extradata)=@_;
223 my $id=$self->lookup_id($table, $name);
226 my $sql="INSERT INTO $table (";
227 $sql .= "id, " if($self->{postgres});
229 if(defined($extradata))
231 push(@fields, sort keys(%$extradata));
233 $sql .= join(", ", @fields);
235 $sql .=") nextval('seq'), " if($self->{postgres});
236 $sql .= join(", ", map { "?"; } @fields);
238 $id=$self->cmd_id($sql, $name, map { $extradata->{$_} || ""; } sort keys %$extradata);
245 my ($self, $relname, $fields)=@_;
246 return if($self->relation_exists($relname, $fields));
247 my $sql="INSERT INTO $relname (";
248 $sql .= join(", ", sort keys(%$fields));
249 $sql .= ") VALUES (";
250 $sql .= join(", ", map { "?"; } sort keys(%$fields));
252 $self->cmd($sql, map { $fields->{$_}; } sort keys(%$fields));
257 my($self, $table, $name)=@_;
258 my($id)=$self->cmd_onerow("SELECT id FROM $table where name=?", $name);
264 my ($self, $relname, $fields)=@_;
265 my $sql="SELECT count(1) FROM $relname WHERE ";
268 for my $field (keys %$fields)
271 push(@vals,$fields->{$field});
273 $sql .= join(' AND ', map { "$_=?"; } @exprs);
274 my ($ret)=$self->cmd_onerow($sql, @vals);
280 my($self, $thing)=@_;
281 return(defined($thing) && length($thing));
286 my ($self, @args)=@_;
287 # don't care about retcode
288 $self->cmd_sth(@args);
293 my ($self, @args)=@_;
294 my $sth=$self->cmd_sth(@args);
295 return($sth->fetchrow_array());
300 my ($self, @args)=@_;
301 my $sth=$self->cmd_sth(@args);
302 return $sth->fetchall_arrayref();
307 my ($self, @args)=@_;
308 $self->cmd_sth(@args);
309 return($self->last_insert_id());
315 if($self->{postgres})
317 return $self->{dbh}->last_insert_id(undef, undef, undef, undef,
318 { sequence => "seq" });
322 return $self->{dbh}->last_insert_id("","","","");
329 schema_version INTEGER
333 id INTEGER PRIMARY KEY,
337 CREATE TABLE artists (
338 id INTEGER PRIMARY KEY,
342 CREATE TABLE albums (
343 id INTEGER PRIMARY KEY,
348 id INTEGER PRIMARY KEY,
352 CREATE TABLE tagvals (
353 id INTEGER PRIMARY KEY,
357 CREATE TABLE files_x_tags (
362 CREATE TABLE tags_x_tagvals (
367 CREATE TABLE files_x_artists (
372 CREATE TABLE artists_x_albums (