Class: Kaede::Database

Inherits:
Object
  • Object
show all
Extended by:
Forwardable
Defined in:
lib/kaede/database.rb

Constant Summary collapse

DATETIME_FORMAT = 
'%Y-%m-%d %H:%M:%S'

Instance Method Summary collapse

Constructor Details

#initialize(path) ⇒ Database

Returns a new instance of Database.



11
12
13
14
15
 
# File 'lib/kaede/database.rb', line 11

def initialize(path)
  @db = SQLite3::Database.new(path.to_s)
  @db.send(:set_boolean_pragma, 'foreign_keys', true)
  prepare_tables
end

Instance Method Details

#add_channel(channel) ⇒ Object 



113
114
115
 
# File 'lib/kaede/database.rb', line 113

def add_channel(channel)
  @db.execute('INSERT INTO channels (name, for_recorder, for_syoboi) VALUES (?, ?, ?)', [channel.name, channel.for_recorder, channel.for_syoboi])
end

#add_tracking_title(tid) ⇒ Object 



136
137
138
 
# File 'lib/kaede/database.rb', line 136

def add_tracking_title(tid)
  @db.execute('INSERT INTO tracking_titles (tid, created_at) VALUES (?, ?)', [tid, current_timestamp])
end

#delete_job(pid) ⇒ Object 



78
79
80
 
# File 'lib/kaede/database.rb', line 78

def delete_job(pid)
  @db.execute('DELETE FROM jobs WHERE pid = ?', pid)
end

#from_db_datetime(str) ⇒ Object 



59
60
61
 
# File 'lib/kaede/database.rb', line 59

def from_db_datetime(str)
  Time.parse("#{str} UTC").localtime
end

#get_channels ⇒ Object 



107
108
109
110
111
 
# File 'lib/kaede/database.rb', line 107

def get_channels
  @db.execute('SELECT * FROM channels').map do |row|
    Channel.new(*row)
  end
end

#get_jobs ⇒ Object 



68
69
70
71
72
 
# File 'lib/kaede/database.rb', line 68

def get_jobs
  @db.execute('SELECT pid, enqueued_at FROM jobs WHERE finished_at IS NULL AND enqueued_at >= ? ORDER BY enqueued_at', [current_timestamp]).map do |pid, enqueued_at|
    { pid: pid, enqueued_at: from_db_datetime(enqueued_at) }
  end
end

#get_program(pid) ⇒ Object 



82
83
84
 
# File 'lib/kaede/database.rb', line 82

def get_program(pid)
  get_programs([pid])[pid]
end

#get_programs(pids) ⇒ Object 



86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
 
# File 'lib/kaede/database.rb', line 86

def get_programs(pids)
  rows = @db.execute(<<-SQL)
SELECT pid, tid, start_time, end_time, channels.name, for_syoboi, for_recorder, count, start_offset, subtitle, title, comment
FROM programs
INNER JOIN channels ON programs.channel_id = channels.id
WHERE programs.pid IN (#{pids.join(', ')})
  SQL
  programs = {}
  rows.each do |row|
    program = Program.new(*row)
    program.start_time = from_db_datetime(program.start_time)
    program.end_time = from_db_datetime(program.end_time)
    programs[program.pid] = program
  end
  programs
end

#get_tracking_titles ⇒ Object 



140
141
142
143
144
 
# File 'lib/kaede/database.rb', line 140

def get_tracking_titles
  @db.execute('SELECT tid FROM tracking_titles').map do |row|
    row[0]
  end
end

#mark_finished(pid) ⇒ Object 



103
104
105
 
# File 'lib/kaede/database.rb', line 103

def mark_finished(pid)
  @db.execute('UPDATE jobs SET finished_at = ? WHERE pid = ?', [current_timestamp, pid])
end

#to_db_datetime(time) ⇒ Object 



55
56
57
 
# File 'lib/kaede/database.rb', line 55

def to_db_datetime(time)
  time.utc.strftime(DATETIME_FORMAT)
end

#update_job(pid, enqueued_at) ⇒ Object 



74
75
76
 
# File 'lib/kaede/database.rb', line 74

def update_job(pid, enqueued_at)
  @db.execute('INSERT OR REPLACE INTO jobs (pid, enqueued_at, created_at) VALUES (?, ?, ?)', [pid, to_db_datetime(enqueued_at), current_timestamp])
end

#update_program(program, channel) ⇒ Object 



117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
 
# File 'lib/kaede/database.rb', line 117

def update_program(program, channel)
  row = [
    program.pid,
    program.tid,
    to_db_datetime(program.start_time),
    to_db_datetime(program.end_time),
    channel.id,
    program.count,
    program.start_offset,
    program.subtitle,
    program.title,
    program.comment,
  ]
  @db.execute(<<-SQL, row)
INSERT INTO programs (pid, tid, start_time, end_time, channel_id, count, start_offset, subtitle, title, comment)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
  SQL
end