Class: Ektoplayer::Database

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

Constant Summary collapse

SELECT_DESCRIPTION = 
%q[
   SELECT description
   FROM   albums
   WHERE  url = ?
].squeeze(' ').freeze
SELECT_ARCHIVES = 
%q[
   SELECT archive_url, archive_type
   FROM   archive_urls
   JOIN   tracks AS t ON t.album_url = archive_urls.album_url
   WHERE  t.url = ?
].squeeze(' ').freeze
SELECT = 
%q[
SELECT
   %{SELECT_COLUMNS}
FROM (
   SELECT DISTINCT
      t.url             AS url,
      t.album_url       AS album_url,
      t.title           AS title,
      t.artist          AS artist,
      t.remix           AS remix,
      t.number          AS number,
      t.bpm             AS bpm,

      a.artist          AS album_artist,
      a.title           AS album,
      a.cover_url       AS cover_url,
      a.description     AS description,
      a.date            AS date,
      a.rating          AS rating,
      a.votes           AS votes,
      a.download_count  AS download_count,

      strftime('%%Y', date)  AS year,
      strftime('%%m', date)  AS month,
      strftime('%%d', date)  AS day,

      a_s.style         AS style,

      (
         SELECT GROUP_CONCAT(style)
         FROM  albums_styles
         WHERE albums_styles.album_url = t.album_url
      ) AS styles
   FROM
      tracks AS t

   JOIN albums        AS a   ON a.url = t.album_url
   JOIN albums_styles AS a_s ON a.url = a_s.album_url
)

WHERE 1  %{WHERE}
GROUP BY %{GROUP_BY}
ORDER BY %{ORDER_BY}
%{LIMIT} ].squeeze(' ').freeze

Instance Attribute Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(db_file) ⇒ Database

Returns a new instance of Database.



66
67
68
69
70
71
 
# File 'lib/ektoplayer/database.rb', line 66

def initialize(db_file)
   @db = SQLite3::Database.new db_file
   @db.results_as_hash = true
   @events = Events.new(:changed)
   create_tables
end

Instance Attribute Details

#db ⇒ Object (readonly)

Returns the value of attribute db.



64
65
66
 
# File 'lib/ektoplayer/database.rb', line 64

def db
  @db
end

#events ⇒ Object (readonly)

Returns the value of attribute events.



64
65
66
 
# File 'lib/ektoplayer/database.rb', line 64

def events
  @events
end

Instance Method Details

#album_count ⇒ Object 



204
205
206
 
# File 'lib/ektoplayer/database.rb', line 204

def album_count
   @db.get_first_value('SELECT COUNT(*) FROM albums')
end

#commit ⇒ Object 



124
125
126
 
# File 'lib/ektoplayer/database.rb', line 124

def commit
   @db.commit rescue Application.log(self, $!)
end

#execute(query, params = []) ⇒ Object 



143
144
145
146
147
 
# File 'lib/ektoplayer/database.rb', line 143

def execute(query, params=[])
   @db.execute(query, *params)
rescue
   Application.log(self, $!)
end

#get_archives(url) ⇒ Object 



192
193
194
 
# File 'lib/ektoplayer/database.rb', line 192

def get_archives(url)
   execute(SELECT_ARCHIVES, [url])
end

#get_description(album_url) ⇒ Object 



196
197
198
 
# File 'lib/ektoplayer/database.rb', line 196

def get_description(album_url)
   @db.get_first_value(SELECT_DESCRIPTION, [album_url])
end

#insert_into(table, hash, mode: :insert) ⇒ Object 



128
129
130
131
132
133
134
135
136
137
 
# File 'lib/ektoplayer/database.rb', line 128

def insert_into(table, hash, mode: :insert)
   cols   = ?( + (hash.keys * ?,) + ?)
   values = ?( + (([??] * hash.size) * ?,) + ?)
   q = @db.prepare "#{mode} INTO #{table} #{cols} VALUES #{values}"
   q.bind_params(hash.values)
   q.execute
   @events.trigger(:changed)
rescue
   Application.log(self, hash, $!)
end

#replace_into(table, hash) ⇒ Object 



139
140
141
 
# File 'lib/ektoplayer/database.rb', line 139

def replace_into(table, hash)
   insert_into(table, hash, mode: :replace)
end

#select(columns: 'number,artist,album,title,styles,date,year,rating,votes,download_count,bpm,album_url,url', filters: [], group_by: 'url', order_by: 'album,number', limit: nil) ⇒ Object 



149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
 
# File 'lib/ektoplayer/database.rb', line 149

def select(
   columns: 'number,artist,album,title,styles,date,year,rating,votes,download_count,bpm,album_url,url',
   filters: [],
   group_by: 'url',
   order_by: 'album,number',
   limit: nil
)
   where_clauses, where_params = [], []
        
   filters.each do |filter|
      where_clauses << "AND #{filter[:tag]} #{filter[:operator]} ?"
      where_params  << filter[:value]
   end

   if order_by.is_a?Array
      fail ArgumentError, 'order_by is empty' if order_by.empty?
      order_by = order_by.join(?,)
   else
      fail ArgumentError, 'order_by is empty' if order_by.empty?
   end

   if group_by.is_a?Array
      fail ArgumentError, 'group_by is empty' if group_by.empty?
      group_by = group_by.join(?,)
   else
      fail ArgumentError, 'group_by is empty' if group_by.empty?
   end

   limit = "LIMIT #{limit}" if limit

   query = SELECT % {
      SELECT_COLUMNS: columns,
      WHERE:          where_clauses.join(' '),
      GROUP_BY:       group_by,
      ORDER_BY:       order_by,
      LIMIT:          limit
   }

   @db.execute(query, *where_params)
rescue
   Application.log(self, $!)
end

#track_count ⇒ Object 



200
201
202
 
# File 'lib/ektoplayer/database.rb', line 200

def track_count
   @db.get_first_value('SELECT COUNT(*) FROM tracks')
end

#transaction ⇒ Object 



120
121
122
 
# File 'lib/ektoplayer/database.rb', line 120

def transaction
   @db.transaction rescue Application.log(self, $!)
end