Java tutorial
/* This file is part of Subsonic. Subsonic is free software: you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation, either version 3 of the License, or (at your option) any later version. Subsonic is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details. You should have received a copy of the GNU General Public License along with Subsonic. If not, see <http://www.gnu.org/licenses/>. Copyright 2015 (C) Scott Jackson */ package github.popeen.dsub.util; import android.content.ContentValues; import android.content.Context; import android.content.SharedPreferences; import android.database.Cursor; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteOpenHelper; import org.json.JSONArray; import org.json.JSONObject; import java.util.ArrayList; import java.util.List; import github.popeen.dsub.domain.MusicDirectory; import github.popeen.dsub.service.DownloadFile; public class SongDBHandler extends SQLiteOpenHelper { private static final String TAG = SongDBHandler.class.getSimpleName(); private static SongDBHandler dbHandler; private static final int DATABASE_VERSION = 2; public static final String DATABASE_NAME = "SongsDB"; public static final String TABLE_SONGS = "RegisteredSongs"; public static final String SONGS_ID = "id"; public static final String SONGS_SERVER_KEY = "serverKey"; public static final String SONGS_SERVER_ID = "serverId"; public static final String SONGS_COMPLETE_PATH = "completePath"; public static final String SONGS_LAST_PLAYED = "lastPlayed"; public static final String SONGS_LAST_COMPLETED = "lastCompleted"; private Context context; private SongDBHandler(Context context) { super(context, DATABASE_NAME, null, DATABASE_VERSION); this.context = context; } @Override public void onCreate(SQLiteDatabase db) { db.execSQL("CREATE TABLE " + TABLE_SONGS + " ( " + SONGS_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " + SONGS_SERVER_KEY + " INTEGER NOT NULL, " + SONGS_SERVER_ID + " TEXT NOT NULL, " + SONGS_COMPLETE_PATH + " TEXT NOT NULL, " + SONGS_LAST_PLAYED + " INTEGER, " + SONGS_LAST_COMPLETED + " INTEGER, " + "UNIQUE(" + SONGS_SERVER_KEY + ", " + SONGS_SERVER_ID + "))"); } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { db.execSQL("DROP TABLE IF EXISTS " + TABLE_SONGS); this.onCreate(db); } public JSONArray exportData() { SQLiteDatabase db = this.getReadableDatabase(); String[] columns = { SONGS_ID, SONGS_SERVER_KEY, SONGS_SERVER_ID, SONGS_COMPLETE_PATH, SONGS_LAST_PLAYED, SONGS_LAST_COMPLETED }; Cursor cursor = db.query(TABLE_SONGS, columns, SONGS_LAST_PLAYED + " != ''", null, null, null, null, null); try { JSONArray jsonSongDb = new JSONArray(); while (cursor.moveToNext()) { JSONObject tempJson = new JSONObject(); tempJson.put("SONGS_ID", cursor.getInt(0)); tempJson.put("SONGS_SERVER_KEY", cursor.getInt(1)); tempJson.put("SONGS_SERVER_ID", cursor.getString(2)); tempJson.put("SONGS_COMPLETE_PATH", cursor.getString(3)); tempJson.put("SONGS_LAST_PLAYED", cursor.getInt(4)); tempJson.put("SONGS_LAST_COMPLETED", cursor.getInt(5)); jsonSongDb.put(tempJson); } cursor.close(); return jsonSongDb; } catch (Exception e) { } return new JSONArray(); } public void importData(JSONArray array) { SQLiteDatabase db = this.getReadableDatabase(); try { for (int i = 0; i < array.length(); i++) { JSONObject row = array.getJSONObject(i); ContentValues values = new ContentValues(); values.put(SONGS_ID, row.getInt("SONGS_ID")); values.put(SONGS_SERVER_KEY, row.getInt("SONGS_SERVER_KEY")); values.put(SONGS_SERVER_ID, row.getString("SONGS_SERVER_ID")); values.put(SONGS_COMPLETE_PATH, row.getString("SONGS_COMPLETE_PATH")); values.put(SONGS_LAST_PLAYED, row.getInt("SONGS_LAST_PLAYED")); values.put(SONGS_LAST_COMPLETED, row.getInt("SONGS_LAST_COMPLETED")); db.insertWithOnConflict(TABLE_SONGS, null, values, SQLiteDatabase.CONFLICT_REPLACE); } } catch (Exception e) { } } public synchronized void addSong(DownloadFile downloadFile) { addSong(Util.getMostRecentActiveServer(context), downloadFile); } public synchronized void addSong(int instance, DownloadFile downloadFile) { SQLiteDatabase db = this.getWritableDatabase(); addSong(db, instance, downloadFile); db.close(); } protected synchronized void addSong(SQLiteDatabase db, DownloadFile downloadFile) { addSong(db, Util.getMostRecentActiveServer(context), downloadFile); } protected synchronized void addSong(SQLiteDatabase db, int instance, DownloadFile downloadFile) { addSong(db, instance, downloadFile.getSong().getId(), downloadFile.getSaveFile().getAbsolutePath()); } protected synchronized void addSong(SQLiteDatabase db, String id, String absolutePath) { addSong(db, Util.getMostRecentActiveServer(context), id, absolutePath); } protected synchronized void addSong(SQLiteDatabase db, int instance, String id, String absolutePath) { addSongImpl(db, Util.getRestUrlHash(context, instance), id, absolutePath); } protected synchronized void addSongImpl(SQLiteDatabase db, int serverKey, String id, String absolutePath) { ContentValues values = new ContentValues(); values.put(SONGS_SERVER_KEY, serverKey); values.put(SONGS_SERVER_ID, id); values.put(SONGS_COMPLETE_PATH, absolutePath); db.insertWithOnConflict(TABLE_SONGS, null, values, SQLiteDatabase.CONFLICT_IGNORE); } public synchronized void addSongs(int instance, List<MusicDirectory.Entry> entries) { SQLiteDatabase db = this.getWritableDatabase(); List<Pair<String, String>> pairs = new ArrayList<>(); for (MusicDirectory.Entry entry : entries) { pairs.add(new Pair<>(entry.getId(), FileUtil.getSongFile(context, entry).getAbsolutePath())); } addSongs(db, instance, pairs); db.close(); } public synchronized void addSongs(SQLiteDatabase db, int instance, List<Pair<String, String>> entries) { addSongsImpl(db, Util.getRestUrlHash(context, instance), entries); } protected synchronized void addSongsImpl(SQLiteDatabase db, int serverKey, List<Pair<String, String>> entries) { db.beginTransaction(); try { for (Pair<String, String> entry : entries) { ContentValues values = new ContentValues(); values.put(SONGS_SERVER_KEY, serverKey); values.put(SONGS_SERVER_ID, entry.getFirst()); values.put(SONGS_COMPLETE_PATH, entry.getSecond()); db.insertWithOnConflict(TABLE_SONGS, null, values, SQLiteDatabase.CONFLICT_IGNORE); } db.setTransactionSuccessful(); } catch (Exception e) { } db.endTransaction(); } public synchronized void setSongPlayed(DownloadFile downloadFile, boolean submission) { // TODO: In case of offline want to update all matches Pair<Integer, String> pair = getOnlineSongId(downloadFile); if (pair == null) { return; } int serverKey = pair.getFirst(); String id = pair.getSecond(); // Open and make sure song is in db SQLiteDatabase db = this.getWritableDatabase(); addSongImpl(db, serverKey, id, downloadFile.getSaveFile().getAbsolutePath()); // Update song's last played ContentValues values = new ContentValues(); values.put(submission ? SONGS_LAST_COMPLETED : SONGS_LAST_PLAYED, System.currentTimeMillis()); db.update(TABLE_SONGS, values, SONGS_SERVER_KEY + " = ? AND " + SONGS_SERVER_ID + " = ?", new String[] { Integer.toString(serverKey), id }); db.close(); } public boolean hasBeenPlayed(MusicDirectory.Entry entry) { Long[] lastPlayed = getLastPlayed(entry); return lastPlayed != null && lastPlayed[0] != null && lastPlayed[0] > 0; } public boolean hasBeenCompleted(MusicDirectory.Entry entry) { Long[] lastPlayed = getLastPlayed(entry); return lastPlayed != null && lastPlayed[1] != null && lastPlayed[1] > 0; } public synchronized Long[] getLastPlayed(MusicDirectory.Entry entry) { return getLastPlayed(getOnlineSongId(entry)); } protected synchronized Long[] getLastPlayed(Pair<Integer, String> pair) { if (pair == null) { return null; } else { return getLastPlayed(pair.getFirst(), pair.getSecond()); } } public synchronized Long[] getLastPlayed(int serverKey, String id) { SQLiteDatabase db = this.getReadableDatabase(); String[] columns = { SONGS_LAST_PLAYED, SONGS_LAST_COMPLETED }; Cursor cursor = db.query(TABLE_SONGS, columns, SONGS_SERVER_KEY + " = ? AND " + SONGS_SERVER_ID + " = ?", new String[] { Integer.toString(serverKey), id }, null, null, null, null); try { cursor.moveToFirst(); Long[] dates = new Long[2]; dates[0] = cursor.getLong(0); dates[1] = cursor.getLong(1); return dates; } catch (Exception e) { return null; } finally { db.close(); } } public synchronized Pair<Integer, String> getOnlineSongId(MusicDirectory.Entry entry) { return getOnlineSongId(Util.getRestUrlHash(context), entry.getId(), FileUtil.getSongFile(context, entry).getAbsolutePath(), Util.isOffline(context) ? false : true); } public synchronized Pair<Integer, String> getOnlineSongId(DownloadFile downloadFile) { return getOnlineSongId(Util.getRestUrlHash(context), downloadFile.getSong().getId(), downloadFile.getSaveFile().getAbsolutePath(), Util.isOffline(context) ? false : true); } public synchronized Pair<Integer, String> getOnlineSongId(int serverKey, MusicDirectory.Entry entry) { return getOnlineSongId(serverKey, new DownloadFile(context, entry, true)); } public synchronized Pair<Integer, String> getOnlineSongId(int serverKey, DownloadFile downloadFile) { return getOnlineSongId(serverKey, downloadFile.getSong().getId(), downloadFile.getSaveFile().getAbsolutePath(), true); } public synchronized Pair<Integer, String> getOnlineSongId(int serverKey, String id, String savePath, boolean requireServerKey) { SharedPreferences prefs = Util.getPreferences(context); String cacheLocn = prefs.getString(Constants.PREFERENCES_KEY_CACHE_LOCATION, null); if (cacheLocn != null && id.indexOf(cacheLocn) != -1) { if (requireServerKey) { return getIdFromPath(serverKey, savePath); } else { return getIdFromPath(savePath); } } else { return new Pair<>(serverKey, id); } } public synchronized Pair<Integer, String> getIdFromPath(String path) { SQLiteDatabase db = this.getReadableDatabase(); String[] columns = { SONGS_SERVER_KEY, SONGS_SERVER_ID }; Cursor cursor = db.query(TABLE_SONGS, columns, SONGS_COMPLETE_PATH + " = ?", new String[] { path }, null, null, SONGS_LAST_PLAYED + " DESC", null); try { cursor.moveToFirst(); return new Pair(cursor.getInt(0), cursor.getString(1)); } catch (Exception e) { return null; } finally { db.close(); } } public synchronized Pair<Integer, String> getIdFromPath(int serverKey, String path) { SQLiteDatabase db = this.getReadableDatabase(); String[] columns = { SONGS_SERVER_KEY, SONGS_SERVER_ID }; Cursor cursor = db.query(TABLE_SONGS, columns, SONGS_SERVER_KEY + " = ? AND " + SONGS_COMPLETE_PATH + " = ?", new String[] { Integer.toString(serverKey), path }, null, null, null, null); try { cursor.moveToFirst(); return new Pair(cursor.getInt(0), cursor.getString(1)); } catch (Exception e) { return null; } finally { db.close(); } } public static SongDBHandler getHandler(Context context) { if (dbHandler == null) { dbHandler = new SongDBHandler(context); } return dbHandler; } }