/*****************************************************************************
* MediaDatabase.java
*****************************************************************************
* Copyright © 2011-2014 VLC authors and VideoLAN
*
* This program 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 2 of the License, or
* (at your option) any later version.
*
* This program 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 this program; if not, write to the Free Software
* Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston MA 02110-1301, USA.
*****************************************************************************/
package org.videolan.vlc;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Locale;
import java.util.Set;
import org.videolan.libvlc.Media;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.graphics.Bitmap;
import android.graphics.BitmapFactory;
import android.util.Log;
public class MediaDatabase {
public final static String TAG = "VLC/MediaDatabase";
private static MediaDatabase instance;
private SQLiteDatabase mDb;
private final String DB_NAME = "vlc_database";
private final int DB_VERSION = 8;
private final int CHUNK_SIZE = 50;
private final String DIR_TABLE_NAME = "directories_table";
private final String DIR_ROW_PATH = "path";
private final String MEDIA_TABLE_NAME = "media_table";
private final String MEDIA_LOCATION = "location";
private final String MEDIA_TIME = "time";
private final String MEDIA_LENGTH = "length";
private final String MEDIA_TYPE = "type";
private final String MEDIA_PICTURE = "picture";
private final String MEDIA_TITLE = "title";
private final String MEDIA_ARTIST = "artist";
private final String MEDIA_GENRE = "genre";
private final String MEDIA_ALBUM = "album";
private final String MEDIA_WIDTH = "width";
private final String MEDIA_HEIGHT = "height";
private final String MEDIA_ARTWORKURL = "artwork_url";
private final String MEDIA_AUDIOTRACK = "audio_track";
private final String MEDIA_SPUTRACK = "spu_track";
private final String PLAYLIST_TABLE_NAME = "playlist_table";
private final String PLAYLIST_NAME = "name";
private final String PLAYLIST_MEDIA_TABLE_NAME = "playlist_media_table";
private final String PLAYLIST_MEDIA_ID = "id";
private final String PLAYLIST_MEDIA_PLAYLISTNAME = "playlist_name";
private final String PLAYLIST_MEDIA_MEDIAPATH = "media_path";
private final String SEARCHHISTORY_TABLE_NAME = "searchhistory_table";
private final String SEARCHHISTORY_DATE = "date";
private final String SEARCHHISTORY_KEY = "key";
public enum mediaColumn {
MEDIA_TABLE_NAME, MEDIA_PATH, MEDIA_TIME, MEDIA_LENGTH,
MEDIA_TYPE, MEDIA_PICTURE, MEDIA_TITLE, MEDIA_ARTIST, MEDIA_GENRE, MEDIA_ALBUM,
MEDIA_WIDTH, MEDIA_HEIGHT, MEDIA_ARTWORKURL, MEDIA_AUDIOTRACK, MEDIA_SPUTRACK
}
/**
* Constructor
*
* @param context
*/
private MediaDatabase(Context context) {
// create or open database
DatabaseHelper helper = new DatabaseHelper(context);
this.mDb = helper.getWritableDatabase();
}
public synchronized static MediaDatabase getInstance(Context context) {
if (instance == null) {
instance = new MediaDatabase(context.getApplicationContext());
}
return instance;
}
private class DatabaseHelper extends SQLiteOpenHelper {
public DatabaseHelper(Context context) {
super(context, DB_NAME, null, DB_VERSION);
}
@Override
public SQLiteDatabase getWritableDatabase() {
SQLiteDatabase db;
try {
return super.getWritableDatabase();
} catch(SQLiteException e) {
try {
db = SQLiteDatabase.openOrCreateDatabase(VLCApplication.getAppContext().getDatabasePath(DB_NAME), null);
} catch(SQLiteException e2) {
Log.w(TAG, "SQLite database could not be created! Media library cannot be saved.");
db = SQLiteDatabase.create(null);
}
}
int version = db.getVersion();
if (version != DB_VERSION) {
db.beginTransaction();
try {
if (version == 0) {
onCreate(db);
} else {
onUpgrade(db, version, DB_VERSION);
}
db.setVersion(DB_VERSION);
db.setTransactionSuccessful();
} finally {
db.endTransaction();
}
}
return db;
}
public void dropMediaTableQuery(SQLiteDatabase db) {
String query = "DROP TABLE " + MEDIA_TABLE_NAME + ";";
db.execSQL(query);
}
public void createMediaTableQuery(SQLiteDatabase db) {
String query = "CREATE TABLE IF NOT EXISTS "
+ MEDIA_TABLE_NAME + " ("
+ MEDIA_LOCATION + " TEXT PRIMARY KEY NOT NULL, "
+ MEDIA_TIME + " INTEGER, "
+ MEDIA_LENGTH + " INTEGER, "
+ MEDIA_TYPE + " INTEGER, "
+ MEDIA_PICTURE + " BLOB, "
+ MEDIA_TITLE + " VARCHAR(200), "
+ MEDIA_ARTIST + " VARCHAR(200), "
+ MEDIA_GENRE + " VARCHAR(200), "
+ MEDIA_ALBUM + " VARCHAR(200), "
+ MEDIA_WIDTH + " INTEGER, "
+ MEDIA_HEIGHT + " INTEGER, "
+ MEDIA_ARTWORKURL + " VARCHAR(256), "
+ MEDIA_AUDIOTRACK + " INTEGER, "
+ MEDIA_SPUTRACK + " INTEGER"
+ ");";
db.execSQL(query);
}
@Override
public void onCreate(SQLiteDatabase db) {
String createDirTabelQuery = "CREATE TABLE IF NOT EXISTS "
+ DIR_TABLE_NAME + " ("
+ DIR_ROW_PATH + " TEXT PRIMARY KEY NOT NULL"
+ ");";
// Create the directories table
db.execSQL(createDirTabelQuery);
// Create the media table
createMediaTableQuery(db);
String createPlaylistTableQuery = "CREATE TABLE IF NOT EXISTS " +
PLAYLIST_TABLE_NAME + " (" +
PLAYLIST_NAME + " VARCHAR(200) PRIMARY KEY NOT NULL);";
db.execSQL(createPlaylistTableQuery);
String createPlaylistMediaTableQuery = "CREATE TABLE IF NOT EXISTS " +
PLAYLIST_MEDIA_TABLE_NAME + " (" +
PLAYLIST_MEDIA_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
PLAYLIST_MEDIA_PLAYLISTNAME + " VARCHAR(200) NOT NULL," +
PLAYLIST_MEDIA_MEDIAPATH + " TEXT NOT NULL);";
db.execSQL(createPlaylistMediaTableQuery);
String createSearchhistoryTabelQuery = "CREATE TABLE IF NOT EXISTS "
+ SEARCHHISTORY_TABLE_NAME + " ("
+ SEARCHHISTORY_KEY + " VARCHAR(200) PRIMARY KEY NOT NULL, "
+ SEARCHHISTORY_DATE + " DATETIME NOT NULL"
+ ");";
// Create the searchhistory table
db.execSQL(createSearchhistoryTabelQuery);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
if (oldVersion < DB_VERSION && newVersion == DB_VERSION) {
dropMediaTableQuery(db);
createMediaTableQuery(db);
}
}
}
/**
* Get all playlists in the database
* @return
*/
public String[] getPlaylists() {
ArrayList<String> playlists = new ArrayList<String>();
Cursor cursor;
cursor = mDb.query(
PLAYLIST_TABLE_NAME,
new String[] { PLAYLIST_NAME },
null, null, null, null, null);
cursor.moveToFirst();
if (!cursor.isAfterLast()) {
do {
playlists.add(cursor.getString(10));
} while (cursor.moveToNext());
}
cursor.close();
return (String[]) playlists.toArray();
}
/**
* Add new playlist
* @param name
* @return id of the new playlist
*/
public void addPlaylist(String name) {
ContentValues values = new ContentValues();
values.put(PLAYLIST_NAME, name);
mDb.insert(PLAYLIST_TABLE_NAME, "NULL", values);
}
public void deletePlaylist(String name) {
mDb.delete(PLAYLIST_TABLE_NAME, PLAYLIST_NAME + "=?",
new String[] { name });
}
public void addMediaToPlaylist(String playlistName, String mediaPath) {
ContentValues values = new ContentValues();
values.put(PLAYLIST_MEDIA_PLAYLISTNAME, playlistName);
values.put(PLAYLIST_MEDIA_MEDIAPATH, mediaPath);
}
public void removeMediaFromPlaylist(String playlistName, String mediaPath) {
mDb.delete(PLAYLIST_MEDIA_TABLE_NAME,
PLAYLIST_MEDIA_PLAYLISTNAME + "=? "
+ PLAYLIST_MEDIA_MEDIAPATH + "=?",
new String[] { playlistName, mediaPath });
}
/**
* Add a new media to the database. The picture can only added by update.
* @param media which you like to add to the database
*/
public synchronized void addMedia(Media media) {
ContentValues values = new ContentValues();
values.put(MEDIA_LOCATION, media.getLocation());
values.put(MEDIA_TIME, media.getTime());
values.put(MEDIA_LENGTH, media.getLength());
values.put(MEDIA_TYPE, media.getType());
values.put(MEDIA_TITLE, media.getTitle());
values.put(MEDIA_ARTIST, media.getArtist());
values.put(MEDIA_GENRE, media.getGenre());
values.put(MEDIA_ALBUM, media.getAlbum());
values.put(MEDIA_WIDTH, media.getWidth());
values.put(MEDIA_HEIGHT, media.getHeight());
values.put(MEDIA_ARTWORKURL, media.getArtworkURL());
values.put(MEDIA_AUDIOTRACK, media.getAudioTrack());
values.put(MEDIA_SPUTRACK, media.getSpuTrack());
mDb.replace(MEDIA_TABLE_NAME, "NULL", values);
}
/**
* Check if the item is already in the database
* @param location of the item (primary key)
* @return True if the item exists, false if it does not
*/
public synchronized boolean mediaItemExists(String location) {
try {
Cursor cursor = mDb.query(MEDIA_TABLE_NAME,
new String[] { MEDIA_LOCATION },
MEDIA_LOCATION + "=?",
new String[] { location },
null, null, null);
boolean exists = cursor.moveToFirst();
cursor.close();
return exists;
} catch (Exception e) {
Log.e(TAG, "Query failed");
return false;
}
}
/**
* Get all paths from the items in the database
* @return list of File
*/
@SuppressWarnings("unused")
private synchronized HashSet<File> getMediaFiles() {
HashSet<File> files = new HashSet<File>();
Cursor cursor;
cursor = mDb.query(
MEDIA_TABLE_NAME,
new String[] { MEDIA_LOCATION },
null, null, null, null, null);
cursor.moveToFirst();
if (!cursor.isAfterLast()) {
do {
File file = new File(cursor.getString(0));
files.add(file);
} while (cursor.moveToNext());
}
cursor.close();
return files;
}
public synchronized HashMap<String, Media> getMedias() {
Cursor cursor;
HashMap<String, Media> medias = new HashMap<String, Media>();
int chunk_count = 0;
int count = 0;
do {
count = 0;
cursor = mDb.rawQuery(String.format(Locale.US,
"SELECT %s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s FROM %s LIMIT %d OFFSET %d",
MEDIA_TIME, //0 long
MEDIA_LENGTH, //1 long
MEDIA_TYPE, //2 int
MEDIA_TITLE, //3 string
MEDIA_ARTIST, //4 string
MEDIA_GENRE, //5 string
MEDIA_ALBUM, //6 string
MEDIA_WIDTH, //7 int
MEDIA_HEIGHT, //8 int
MEDIA_ARTWORKURL, //9 string
MEDIA_AUDIOTRACK, //10 string
MEDIA_SPUTRACK, //11 string
MEDIA_LOCATION, //12 string
MEDIA_TABLE_NAME,
CHUNK_SIZE,
chunk_count * CHUNK_SIZE), null);
if (cursor.moveToFirst()) {
do {
String location = cursor.getString(12);
Media media = new Media(location,
cursor.getLong(0), // MEDIA_TIME
cursor.getLong(1), // MEDIA_LENGTH
cursor.getInt(2), // MEDIA_TYPE
null, // MEDIA_PICTURE
cursor.getString(3), // MEDIA_TITLE
cursor.getString(4), // MEDIA_ARTIST
cursor.getString(5), // MEDIA_GENRE
cursor.getString(6), // MEDIA_ALBUM
cursor.getInt(7), // MEDIA_WIDTH
cursor.getInt(8), // MEDIA_HEIGHT
cursor.getString(9), // MEDIA_ARTWORKURL
cursor.getInt(10), // MEDIA_AUDIOTRACK
cursor.getInt(11)); // MEDIA_SPUTRACK
medias.put(media.getLocation(), media);
count++;
} while (cursor.moveToNext());
}
cursor.close();
chunk_count++;
} while (count == CHUNK_SIZE);
return medias;
}
public synchronized HashMap<String, Long> getVideoTimes(Context context) {
Cursor cursor;
HashMap<String, Long> times = new HashMap<String, Long>();
int chunk_count = 0;
int count = 0;
do {
count = 0;
cursor = mDb.rawQuery(String.format(Locale.US,
"SELECT %s,%s FROM %s WHERE %s=%d LIMIT %d OFFSET %d",
MEDIA_LOCATION, //0 string
MEDIA_TIME, //1 long
MEDIA_TABLE_NAME,
MEDIA_TYPE,
Media.TYPE_VIDEO,
CHUNK_SIZE,
chunk_count * CHUNK_SIZE), null);
if (cursor.moveToFirst()) {
do {
String location = cursor.getString(0);
long time = cursor.getLong(1);
times.put(location, time);
count++;
} while (cursor.moveToNext());
}
cursor.close();
chunk_count++;
} while (count == CHUNK_SIZE);
return times;
}
public synchronized Media getMedia(String location) {
Cursor cursor;
Media media = null;
try {
cursor = mDb.query(
MEDIA_TABLE_NAME,
new String[] {
MEDIA_TIME, //0 long
MEDIA_LENGTH, //1 long
MEDIA_TYPE, //2 int
MEDIA_TITLE, //3 string
MEDIA_ARTIST, //4 string
MEDIA_GENRE, //5 string
MEDIA_ALBUM, //6 string
MEDIA_WIDTH, //7 int
MEDIA_HEIGHT, //8 int
MEDIA_ARTWORKURL, //9 string
MEDIA_AUDIOTRACK, //10 string
MEDIA_SPUTRACK //11 string
},
MEDIA_LOCATION + "=?",
new String[] { location },
null, null, null);
} catch(IllegalArgumentException e) {
// java.lang.IllegalArgumentException: the bind value at index 1 is null
return null;
}
if (cursor.moveToFirst()) {
media = new Media(location,
cursor.getLong(0),
cursor.getLong(1),
cursor.getInt(2),
null, // lazy loading, see getPicture()
cursor.getString(3),
cursor.getString(4),
cursor.getString(5),
cursor.getString(6),
cursor.getInt(7),
cursor.getInt(8),
cursor.getString(9),
cursor.getInt(10),
cursor.getInt(11));
}
cursor.close();
return media;
}
public synchronized Bitmap getPicture(Context context, String location) {
/* Used for the lazy loading */
Cursor cursor;
Bitmap picture = null;
byte[] blob;
cursor = mDb.query(
MEDIA_TABLE_NAME,
new String[] { MEDIA_PICTURE },
MEDIA_LOCATION + "=?",
new String[] { location },
null, null, null);
if (cursor.moveToFirst()) {
blob = cursor.getBlob(0);
if (blob != null && blob.length > 1 && blob.length < 500000) {
picture = BitmapFactory.decodeByteArray(blob, 0, blob.length);
blob = null;
}
}
cursor.close();
return picture;
}
public synchronized void removeMedia(String location) {
mDb.delete(MEDIA_TABLE_NAME, MEDIA_LOCATION + "=?", new String[] { location });
}
public void removeMedias(Set<String> locations) {
mDb.beginTransaction();
try {
for (String location : locations)
mDb.delete(MEDIA_TABLE_NAME, MEDIA_LOCATION + "=?", new String[] { location });
mDb.setTransactionSuccessful();
} finally {
mDb.endTransaction();
}
}
public synchronized void updateMedia(String location, mediaColumn col,
Object object) {
if (location == null)
return;
ContentValues values = new ContentValues();
switch (col) {
case MEDIA_PICTURE:
if (object != null) {
Bitmap picture = (Bitmap) object;
ByteArrayOutputStream out = new ByteArrayOutputStream();
picture.compress(Bitmap.CompressFormat.JPEG, 90, out);
values.put(MEDIA_PICTURE, out.toByteArray());
}
else {
values.put(MEDIA_PICTURE, new byte[1]);
}
break;
case MEDIA_TIME:
if (object != null)
values.put(MEDIA_TIME, (Long)object);
break;
case MEDIA_AUDIOTRACK:
if (object != null)
values.put(MEDIA_AUDIOTRACK, (Integer)object);
break;
case MEDIA_SPUTRACK:
if (object != null)
values.put(MEDIA_SPUTRACK, (Integer)object);
break;
case MEDIA_LENGTH:
if (object != null)
values.put(MEDIA_LENGTH, (Long)object);
break;
default:
return;
}
mDb.update(MEDIA_TABLE_NAME, values, MEDIA_LOCATION + "=?", new String[] { location });
}
/**
* Add directory to the directories table
*
* @param path
*/
public synchronized void addDir(String path) {
if (!mediaDirExists(path)) {
ContentValues values = new ContentValues();
values.put(DIR_ROW_PATH, path);
mDb.insert(DIR_TABLE_NAME, null, values);
}
}
/**
* Delete directory from directories table
*
* @param path
*/
public synchronized void removeDir(String path) {
mDb.delete(DIR_TABLE_NAME, DIR_ROW_PATH + "=?", new String[] { path });
}
/**
*
* @return
*/
public synchronized List<File> getMediaDirs() {
List<File> paths = new ArrayList<File>();
Cursor cursor;
cursor = mDb.query(
DIR_TABLE_NAME,
new String[] { DIR_ROW_PATH },
null, null, null, null, null);
cursor.moveToFirst();
if (!cursor.isAfterLast()) {
do {
File dir = new File(cursor.getString(0));
paths.add(dir);
} while (cursor.moveToNext());
}
cursor.close();
return paths;
}
private synchronized boolean mediaDirExists(String path) {
Cursor cursor = mDb.query(DIR_TABLE_NAME,
new String[] { DIR_ROW_PATH },
DIR_ROW_PATH + "=?",
new String[] { path },
null, null, null);
boolean exists = cursor.moveToFirst();
cursor.close();
return exists;
}
/**
*
* @param key
*/
public synchronized void addSearchhistoryItem(String key) {
// set the format to sql date time
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss", Locale.US);
Date date = new Date();
ContentValues values = new ContentValues();
values.put(SEARCHHISTORY_KEY, key);
values.put(SEARCHHISTORY_DATE, dateFormat.format(date));
mDb.replace(SEARCHHISTORY_TABLE_NAME, null, values);
}
public synchronized ArrayList<String> getSearchhistory(int size) {
ArrayList<String> history = new ArrayList<String>();
Cursor cursor = mDb.query(SEARCHHISTORY_TABLE_NAME,
new String[] { SEARCHHISTORY_KEY },
null, null, null, null,
SEARCHHISTORY_DATE + " DESC",
Integer.toString(size));
while (cursor.moveToNext()) {
history.add(cursor.getString(0));
}
cursor.close();
return history;
}
public synchronized void clearSearchhistory() {
mDb.delete(SEARCHHISTORY_TABLE_NAME, null, null);
}
/**
* Empty the database for debugging purposes
*/
public synchronized void emptyDatabase() {
mDb.delete(MEDIA_TABLE_NAME, null, null);
}
}