4 path text not null primary key,
7 last_queued timestamp with time zone,
8 generation bigint not null);
10 create index songs_artist_idx on songs(artist);
11 create index songs_album_idx on songs(album);
16 last_queued timestamp with time zone,
17 generation bigint not null,
18 primary key(album,artist));
21 artist text not null primary key,
22 last_queued timestamp with time zone,
23 generation bigint not null);
25 create table unwanted_albums(
28 generation bigint not null,
29 primary key(album,artist));
31 create table unwanted_artists(
32 artist text not null primary key,
33 generation bigint not null
37 generation bigint not null
40 insert into options(generation) values(0);
42 CREATE FUNCTION update_song_data(
44 , OUT total_songs bigint
45 , OUT total_artists bigint
46 , OUT total_albums bigint
47 , OUT new_songs bigint
48 , OUT new_artists bigint
49 , OUT new_albums bigint)
61 SELECT path, artist, album
64 total_songs = total_songs + 1;
67 SET generation = new_generation
68 WHERE path = song_data.path;
71 new_songs = new_songs + 1;
72 INSERT INTO songs(path, artist, album, generation)
73 values(song_data.path, song_data.artist,
74 song_data.album, new_generation);
76 -----------------------------
78 SET generation = new_generation
79 WHERE artist = song_data.artist;
82 new_artists = new_artists + 1;
83 INSERT INTO artists(artist, generation)
84 VALUES(song_data.artist, new_generation);
86 -----------------------------
88 SET generation = new_generation
89 WHERE album = song_data.album
90 AND artist = song_data.artist;
93 new_albums = new_albums + 1;
94 INSERT INTO albums(album, artist, generation)
95 VALUES(song_data.album, song_data.artist, new_generation);
99 SELECT COUNT(*) INTO total_artists FROM artists WHERE generation = new_generation;
100 SELECT COUNT(*) INTO total_albums FROM albums WHERE generation = new_generation;