1 /***
2 * Ambient - A music player for the Android platform
3 Copyright (C) 2007 Martin Vysny
4
5 This program is free software: you can redistribute it and/or modify
6 it under the terms of the GNU General Public License as published by
7 the Free Software Foundation, either version 3 of the License, or
8 (at your option) any later version.
9
10 This program is distributed in the hope that it will be useful,
11 but WITHOUT ANY WARRANTY; without even the implied warranty of
12 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
13 GNU General Public License for more details.
14
15 You should have received a copy of the GNU General Public License
16 along with this program. If not, see <http://www.gnu.org/licenses/>.
17 */
18 package sk.baka.ambient.library;
19
20 import java.io.FileNotFoundException;
21 import java.util.ArrayList;
22 import java.util.Collections;
23 import java.util.EnumMap;
24 import java.util.HashMap;
25 import java.util.Iterator;
26 import java.util.List;
27 import java.util.Map;
28 import java.util.Set;
29 import java.util.Map.Entry;
30
31 import sk.baka.ambient.collection.CategoryEnum;
32 import sk.baka.ambient.collection.TrackMetadataBean;
33 import sk.baka.ambient.collection.TrackOriginEnum;
34 import android.content.ContentValues;
35 import android.content.Context;
36 import android.database.Cursor;
37 import android.database.sqlite.SQLiteDatabase;
38
39 /***
40 * The database backend for the library. Handles database operations.
41 *
42 * @author Martin Vysny
43 */
44 public final class DBStrategy {
45 /***
46 * The database handle.
47 */
48 private SQLiteDatabase db;
49
50 /***
51 * The DB name.
52 */
53 private static final String DATABASE_NAME = "Ambient";
54
55 /***
56 * The database version.
57 */
58 public static final int DATABASE_VERSION = 4;
59
60 /***
61 * Creates the backend instance and opens/creates the database.
62 *
63 * @param ctx
64 * the context.
65 * @throws FileNotFoundException
66 * if the database fails to both open and create.
67 */
68 DBStrategy(final Context ctx) throws FileNotFoundException {
69 final boolean dbExists = dbExists(ctx);
70 db = ctx
71 .openOrCreateDatabase(DATABASE_NAME, Context.MODE_PRIVATE, null);
72 final int version = db.getVersion();
73 if (version < 0)
74 throw new FileNotFoundException("Error getting db version: "
75 + version);
76 if ((version != DATABASE_VERSION) || (!dbExists)) {
77 initializeDB();
78 db.setVersion(DATABASE_VERSION);
79 }
80 }
81
82 private static boolean dbExists(final Context ctx) {
83 final String[] dblist = ctx.databaseList();
84 if (dblist == null)
85 return false;
86 for (final String dbname : dblist) {
87 if (DATABASE_NAME.equals(dbname))
88 return true;
89 }
90 return false;
91 }
92
93 /***
94 * Creates all tables.
95 */
96 private void initializeDB() {
97 getDb().execSQL("drop table if exists tracks_genres;");
98 getDb().execSQL("drop table if exists tracks;");
99 getDb().execSQL("drop table if exists genres;");
100 getDb().execSQL(
101 "create table tracks "
102 + " (id integer primary key autoincrement, "
103 + "origin integer not null, "
104 + "title text, artist text, composer text, "
105 + "album text, genre text, trackNumber text, "
106 + "location text not null, length integer not null, "
107 + "bitrate integer not null, fileSize long not null, "
108 + "yearReleased text, frequency integer not null, "
109 + "buy_url text, license text, "
110 + "artist_url text, artist_desc text);");
111 getDb().execSQL("create index idx_lib_title on tracks (title);");
112 getDb().execSQL("create index idx_lib_artist on tracks (artist);");
113 getDb().execSQL("create index idx_lib_album on tracks (album);");
114 getDb().execSQL("create index idx_lib_year on tracks (yearReleased);");
115 getDb().execSQL(
116 "create index idx_lib_tracknum on tracks (trackNumber);");
117 getDb().execSQL("create index idx_lib_origin on tracks (origin);");
118 getDb().execSQL(
119 "create table genres "
120 + "(id integer primary key autoincrement,"
121 + "name text unique not null);");
122 getDb().execSQL("create index idx_gen_genre on genres (name);");
123 getDb()
124 .execSQL(
125 "create table tracks_genres "
126 + "(trackid integer not null, genreid integer not null);");
127 getDb().execSQL(
128 "create index idx_tg_trackid on tracks_genres (trackid);");
129 getDb().execSQL(
130 "create index idx_tg_genreid on tracks_genres (genreid);");
131 }
132
133 /***
134 * Closes the database and releases all resources.
135 */
136 void close() {
137 db.close();
138 db = null;
139 }
140
141 /***
142 * Returns the initialized database instance.
143 *
144 * @return database instance, never <code>null</code>.
145 * @throws IllegalStateException
146 * if the database was closed.
147 */
148 SQLiteDatabase getDb() {
149 if (db == null)
150 throw new IllegalStateException("DB is closed");
151 return db;
152 }
153
154 /***
155 * Removes all data from all tables.
156 *
157 * @param storage
158 * the storage to clean. If <code>null</code> then all tracks and
159 * genres are removed.
160 */
161 void clean(final TrackOriginEnum storage) {
162 if (storage == null) {
163 getDb().delete("tracks_genres", null, null);
164 getDb().delete("tracks", null, null);
165 getDb().delete("genres", null, null);
166 } else {
167 getDb().execSQL(
168 "delete from tracks_genres where trackid in "
169 + "(select id from tracks where origin="
170 + storage.ordinal() + ");");
171 getDb().execSQL(
172 "delete from tracks where origin=" + storage.ordinal()
173 + ";");
174 }
175 }
176
177 /***
178 * Searches for given genre and returns its id.
179 *
180 * @param genre
181 * the genre to search for, must not be <code>null</code>.
182 * @return id of the genre or <code>-1</code> if given genre does not yet
183 * exist.
184 */
185 public int getGenreId(final String genre) {
186 final Cursor c = getDb().rawQuery("select id from genres where name=?",
187 new String[] { genre });
188 int result = -1;
189 if (c.moveToFirst()) {
190 result = c.getInt(0);
191 }
192 c.close();
193 return result;
194 }
195
196 /***
197 * Registers new genre and returns its id. The genre must be unique!
198 *
199 * @param name
200 * the genre name, must not be <code>null</code>.
201 * @return id.
202 */
203 long registerNewGenre(final String name) {
204 final ContentValues values = new ContentValues();
205 values.put("name", name);
206 return getDb().insert("genres", null, values);
207 }
208
209 /***
210 * Registers a new track with given genres.
211 *
212 * @param track
213 * the track to register.
214 * @param genreIds
215 * list of genres for this track.
216 * @return track's new ID.
217 */
218 long registerNewTrack(final TrackMetadataBean track,
219 final Set<Long> genreIds) {
220 final ContentValues values = LibraryUtils.trackBeanToValues(track);
221 final long result = getDb().insert("tracks", null, values);
222 final ContentValues mapping = new ContentValues();
223 mapping.put("trackid", result);
224 for (final Long genreId : genreIds) {
225 mapping.put("genreid", genreId);
226 getDb().insert("tracks_genres", null, mapping);
227 }
228 return result;
229 }
230
231 /***
232 * Returns matchable strings from existing tracks for given criteria values.
233 * For example, {@link CategoryEnum#Genre} returns all genres. Returns
234 * ordered list.
235 *
236 * @param crit
237 * the criteria to search for
238 * @param criteria
239 * the criteria map. All criteria combines using the AND
240 * operator. Must not contain the <code>crit</code> parameter.
241 * May be <code>null</code> - this is equal to an empty map.
242 * @return list of all criteria, retrievable via
243 * <code>Cursor.getString(0)</code>.
244 */
245 public Cursor getCriteriaList(final CategoryEnum[] crit,
246 final EnumMap<CategoryEnum, String> criteria) {
247 final StringBuilder b = new StringBuilder();
248 for (int i = 0; i < crit.length; i++) {
249 if (i != 0) {
250 b.append(",");
251 }
252 b.append(COLUMN.get(crit[i]));
253 }
254 return getCriteriaCursor(b.toString(), convertCriteria(criteria),
255 false, true, b.toString());
256 }
257
258 /***
259 * Searches tracks.
260 *
261 * @param criteria
262 * the criteria map. All criteria combines using the AND
263 * operator. May be <code>null</code> - this is equal to an empty
264 * map. When using the {@link CategoryEnum#Origin} criteria, use
265 * {@link TrackOriginEnum#toDBString()} as value.
266 * @param orderBy
267 * the order by clause, if <code>null</code> then ordering is
268 * undefined.
269 * @param like
270 * if <code>true</code> then given strings will be compared using
271 * the LIKE operator. Note that the values are automatically
272 * enclosed in %%.
273 * @return cursor with items convertible to {@link TrackMetadataBean} using
274 * {@link LibraryUtils#cursorToTrackBean(Cursor)}. Use
275 * {@link LibraryUtils#pollTracks(Cursor)} to get all results.
276 */
277 public Cursor findByCriteria(final Map<CategoryEnum, String> criteria,
278 final boolean like, final String orderBy) {
279 return getCriteriaCursor(LibraryUtils.COLUMNS,
280 convertCriteria(criteria), like, true, orderBy);
281 }
282
283 /***
284 * Searches tracks.
285 *
286 * @param criteria
287 * the criteria map. All criteria are combined using the required
288 * operator (depending on the value of the <code>and</code>
289 * argument). May be <code>null</code> - this is equal to an
290 * empty map. Several values for a single criteria are combined
291 * depending on the value of the <code>and</code> parameter. When
292 * using the {@link CategoryEnum#Origin} criteria, use
293 * {@link TrackOriginEnum#toDBString()} as value.
294 * @param like
295 * if <code>true</code> then given strings will be compared using
296 * the LIKE operator. Note that the values are automatically
297 * enclosed in %%.
298 * @param and
299 * If <code>true</code> then the criteria is combined using the
300 * AND operator. If <code>false</code> then the criteria is
301 * combined using the OR operator.
302 * @param orderBy
303 * the order by clause, if <code>null</code> then ordering is
304 * undefined.
305 * @return cursor with items convertible to {@link TrackMetadataBean} using
306 * {@link LibraryUtils#cursorToTrackBean(Cursor)}. Use
307 * {@link LibraryUtils#pollTracks(Cursor)} to get all results.
308 */
309 public Cursor findByCriteria(
310 final EnumMap<CategoryEnum, List<String>> criteria,
311 final boolean like, final boolean and, final String orderBy) {
312 return getCriteriaCursor(LibraryUtils.COLUMNS, criteria, like, and,
313 orderBy);
314 }
315
316 /***
317 * Converts a simple criteria to a complex criteria type.
318 *
319 * @param map
320 * the simple criteria.
321 * @return the complex criteria type.
322 */
323 public static EnumMap<CategoryEnum, List<String>> convertCriteria(
324 final Map<? extends CategoryEnum, ? extends String> map) {
325 final EnumMap<CategoryEnum, List<String>> result = new EnumMap<CategoryEnum, List<String>>(
326 CategoryEnum.class);
327 for (final Entry<? extends CategoryEnum, ? extends String> entry : map
328 .entrySet()) {
329 final CategoryEnum key = entry.getKey();
330 final String value = entry.getValue();
331 result.put(key, Collections.singletonList(value));
332 }
333 return result;
334 }
335
336 /***
337 * Finds all tracks.
338 *
339 * @return cursor with items convertible to {@link TrackMetadataBean} using
340 * {@link LibraryUtils#cursorToTrackBean(Cursor)}. Use
341 * {@link LibraryUtils#pollTracks(Cursor)} to get all results.
342 */
343 @SuppressWarnings("unchecked")
344 public Cursor findAll() {
345 return getCriteriaCursor(LibraryUtils.COLUMNS, Collections.EMPTY_MAP,
346 false, true, null);
347 }
348
349 /***
350 * Performs a raw query on the track table.
351 *
352 * @param where
353 * the where clausule
354 * @param selectionArgs
355 * optional selection argument values, may be <code>null</code>.
356 * @return track query cursor with items convertible to
357 * {@link TrackMetadataBean} using
358 * {@link LibraryUtils#cursorToTrackBean(Cursor)}. Use
359 * {@link LibraryUtils#pollTracks(Cursor)} to get all results.
360 */
361 public Cursor rawTrackQuery(final String where, final String[] selectionArgs) {
362 final StringBuilder select = new StringBuilder();
363 select.append("select distinct ");
364 select.append(LibraryUtils.COLUMNS);
365 select.append(" from tracks");
366 if (where != null) {
367 select.append(" where ");
368 select.append(where);
369 }
370 final Cursor result = getDb()
371 .rawQuery(select.toString(), selectionArgs);
372 return result;
373 }
374
375 private static final Map<CategoryEnum, String> COLUMN = new EnumMap<CategoryEnum, String>(
376 CategoryEnum.class);
377 static {
378 COLUMN.put(CategoryEnum.Album, "album");
379 COLUMN.put(CategoryEnum.Artist, "artist");
380 COLUMN.put(CategoryEnum.Genre, "name");
381 COLUMN.put(CategoryEnum.Origin, "origin");
382 COLUMN.put(CategoryEnum.Title, "title");
383 COLUMN.put(CategoryEnum.YearReleased, "yearReleased");
384 }
385
386 private static final String getTableName(final CategoryEnum cat) {
387 return cat == CategoryEnum.Genre ? "genres" : "tracks";
388 }
389
390 /***
391 * Selects given columns for given criteria.
392 *
393 * @param columns
394 * columns to select.
395 * @param criteria
396 * the criteria map. All criteria are combined using the required
397 * operator (depending on the value of the <code>and</code>
398 * argument). May be <code>null</code> - this is equal to an
399 * empty map. Several values for a single criteria are combined
400 * depending on the value of the <code>and</code> parameter. When
401 * using the {@link CategoryEnum#Origin} criteria, use
402 * {@link TrackOriginEnum#toDBString()} as value.
403 * @param orderBy
404 * the order by clause, if <code>null</code> then ordering is
405 * undefined.
406 * @param like
407 * if <code>true</code> then given strings will be compared using
408 * the LIKE operator. Note that the values are automatically
409 * enclosed in %%.
410 * @param and
411 * If <code>true</code> then the criteria is combined using the
412 * AND operator. If <code>false</code> then the criteria is
413 * combined using the OR operator.
414 * @return cursor with items convertible to {@link TrackMetadataBean} using
415 * {@link LibraryUtils#cursorToTrackBean(Cursor)}. Use
416 * {@link LibraryUtils#pollTracks(Cursor)} to get all results.
417 */
418 @SuppressWarnings("unchecked")
419 private Cursor getCriteriaCursor(
420 final String columns,
421 final Map<? extends CategoryEnum, ? extends List<? extends String>> criteria,
422 final boolean like, final boolean and, final String orderBy) {
423 final Map<? extends CategoryEnum, ? extends List<? extends String>> crit = criteria != null ? criteria
424 : Collections.EMPTY_MAP;
425 final StringBuilder select = new StringBuilder();
426 select.append("select distinct ");
427 select.append(columns);
428 select.append(" from tracks");
429 final boolean isGenre = crit.containsKey(CategoryEnum.Genre)
430 || columns.equals(COLUMN.get(CategoryEnum.Genre));
431 if (isGenre) {
432 select.append(",tracks_genres,genres");
433 }
434 if (!crit.isEmpty() || isGenre) {
435 select.append(" where ");
436 }
437 if (isGenre) {
438 select.append("tracks.id=tracks_genres.trackid and "
439 + "genres.id=tracks_genres.genreid ");
440 if (!crit.isEmpty())
441 select.append("and ");
442 }
443 final List<String> selectionArgs = new ArrayList<String>();
444 for (final Iterator<? extends CategoryEnum> i = crit.keySet()
445 .iterator(); i.hasNext();) {
446 final CategoryEnum critField = i.next();
447 final List<? extends String> critValues = crit.get(critField);
448 select.append('(');
449 for (final Iterator<? extends String> j = critValues.iterator(); j
450 .hasNext();) {
451 String critValue = j.next();
452 select.append(COLUMN.get(critField));
453 if (critValue == null) {
454 select.append(" isnull");
455 } else {
456 if (like) {
457 critValue = "%" + critValue + "%";
458 }
459 if (like) {
460 select.append(" like ?");
461 } else {
462 select.append("=?");
463 }
464 selectionArgs.add(critValue);
465 }
466 if (j.hasNext()) {
467 select.append(" or ");
468 }
469 }
470 select.append(')');
471 if (i.hasNext()) {
472 if (and) {
473 select.append(" and ");
474 } else {
475 select.append(" or ");
476 }
477 }
478 }
479 if (orderBy != null) {
480 select.append(" order by ");
481 select.append(orderBy);
482 }
483 final Cursor result = getDb().rawQuery(select.toString(),
484 selectionArgs.toArray(new String[0]));
485 return result;
486 }
487
488 /***
489 * Invokes the {@link SQLiteDatabase#rawQuery(String, String[])} method.
490 *
491 * @param select
492 * the SQL SELECT statement.
493 * @param selectionArgs
494 * optional arguments, may be <code>null</code>.
495 * @return opened cursor.
496 */
497 public Cursor rawQuery(final String select, final String[] selectionArgs) {
498 return getDb().rawQuery(select, selectionArgs);
499 }
500
501 /***
502 * Loads all genres as a map.
503 *
504 * @return all genres.
505 */
506 public Map<String, Long> getAllGenres() {
507 final Map<String, Long> result = new HashMap<String, Long>();
508 final Cursor cursor = getDb().rawQuery("select name,id from genres",
509 null);
510 while (cursor.moveToNext()) {
511 final String genre = cursor.getString(0);
512 final Long id = cursor.getLong(1);
513 result.put(genre, id);
514 }
515 cursor.close();
516 return result;
517 }
518 }