Class: Database

Inherits:
Object
  • Object
show all
Defined in:
lib/itc/database.rb

Instance Method Summary collapse

Constructor Details

#initialize ⇒ Database

Returns a new instance of Database.



6
7
8
 
# File 'lib/itc/database.rb', line 6

def initialize
    @db = SQLite3::Database.new(ENV["HOME"] + "/.itc.db")
end

Instance Method Details

#add_tracks(tracks) ⇒ Object 



38
39
40
41
42
43
44
45
46
47
 
# File 'lib/itc/database.rb', line 38

def add_tracks(tracks)
    @db.transaction do |db|
        db.prepare("insert into tracks values (?, ?, ?, ?, ?, ?, ?, ?)") do |stmt|
            tracks.each do |t|
                stmt.execute t[:track_id].to_i, t[:artist], t[:album], t[:name],
                t[:genre], t[:year], t[:disc_number].to_i, t[:track_number].to_i
            end
        end
    end
end

#list(filters) ⇒ Object 



54
55
56
57
58
59
60
61
 
# File 'lib/itc/database.rb', line 54

def list(filters)
    c = filters.keys.collect { |k| "#{k} like \"%#{filters[k]}%\"" }
    condition = c.empty? ? "" : "where #{c.join(' and ')}"
    fields = filters.keys.join(",")
    @db.execute <<-SQL
        select distinct #{fields} from tracks #{condition} order by artist, album, disc_number, track_number
    SQL
end

#list_by_ids(ids) ⇒ Object 



71
72
73
74
75
76
77
78
79
80
81
82
 
# File 'lib/itc/database.rb', line 71

def list_by_ids(ids)
    c = ids.collect { |i| "track_id = #{i}" }
    condition = c.empty? ? "" : "where #{c.join(' or ')}"
    @db.execute <<-SQL
        select
            track_id, artist, album, song
        from
            tracks #{condition}
        order by
            artist, album, disc_number, track_number
    SQL
end

#list_ids(filters) ⇒ Object 



63
64
65
66
67
68
69
 
# File 'lib/itc/database.rb', line 63

def list_ids(filters)
    c = filters.keys.collect { |k| "#{k} like \"%#{filters[k]}%\"" }
    condition = c.empty? ? "" : "where #{c.join(' and ')}"
    @db.execute <<-SQL
        select track_id from tracks #{condition} order by artist, album, disc_number, track_number
    SQL
end

#reindex(music) ⇒ Object 



49
50
51
52
 
# File 'lib/itc/database.rb', line 49

def reindex(music)
    reset
    add_tracks music.tracks
end

#reset ⇒ Object 



10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
 
# File 'lib/itc/database.rb', line 10

def reset
    @db.execute_batch <<-SQL
        drop index if exists tracks_artist_index;
        drop index if exists tracks_album_index;
        drop index if exists tracks_song_index;
        drop index if exists tracks_genre_index;
        drop index if exists tracks_year_index;
        drop table if exists tracks;

        create table tracks (
            track_id        INTEGER PRIMARY KEY,
            artist          VARCHAR DEFAULT NULL,
            album           VARCHAR DEFAULT NULL,
            song            VARCHAR DEFAULT NULL,
            genre           VARCHAR DEFAULT NULL,
            year            VARCHAR DEFAULT NULL,
            disc_number     INTEGER DEFAULT NULL,
            track_number    INTEGER DEFAULT NULL
        );

        create index tracks_artist_index on tracks (artist);
        create index tracks_album_index on tracks (album);
        create index tracks_song_index on tracks (song);
        create index tracks_genre_index on tracks (genre);
        create index tracks_year_index on tracks (year);
    SQL
end