package com.battlelancer.seriesguide;
import com.battlelancer.seriesguide.SeriesGuideData.ShowSorting;
import com.battlelancer.thetvdbapi.Series;
import android.content.ContentValues;
import android.content.Context;
import android.content.SharedPreferences;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.Bundle;
import android.os.Environment;
import android.preference.PreferenceManager;
import android.util.Log;
import java.io.File;
import java.io.IOException;
import java.util.Date;
import java.util.HashSet;
public class SeriesDatabase {
public static final String SERIESDATABASE_PATH = "/data/com.battlelancer.seriesguide/databases/seriesdatabase";
private static final String TAG = "SeriesDatabase";
private static final String DATABASE_NAME = "seriesdatabase";
public static final int DATABASE_VERSION = 16;
private static final String SERIES_TABLE = "series";
private static final String SEASONS_TABLE = "seasons";
private static final String EPISODE_TABLE = "episodes";
private static final String FTS_SEARCH_TABLE = "searchtable";
public static final String SEASON_POSTER = "seasonposter";
public static final String SEASON_UNAIREDCOUNT = "willaircount";
public static final String SEASON_WATCHCOUNT = "watchcount";
public static final String SEASON_NOAIRDATECOUNT = "noairdatecount";
public static final String SEASON_SERIES_ID = "series_id";
public static final String SEASON_COMBINED = "combinednr";
public static final String SEASON_ID = "_id";
public static final String EPISODE_SEASON = "season";
public static final String EPISODE_NUMBER = "episodenumber";
public static final String EPISODE_DVDNUMBER = "dvdnumber";
public static final String EPISODE_IMAGE = "episodeimage";
public static final String EPISODE_WRITERS = "writers";
public static final String EPISODE_GUESTSTARS = "gueststars";
public static final String EPISODE_DIRECTORS = "directors";
public static final String EPISODE_RATING = "rating";
public static final String EPISODE_FIRSTAIRED = "epfirstaired";
public static final String EPISODE_WATCHED = "watched";
public static final String EPISODE_SERIES_ID = "series_id";
public static final String EPISODE_SEASON_ID = "season_id";
public static final String EPISODE_OVERVIEW = "episodedescription";
public static final String EPISODE_TITLE = "episodetitle";
public static final String EPISODE_ID = "_id";
public static final String SERIES_NEXTEPISODE = "next";
public static final String SERIES_NEXTAIRDATE = "nextairdate";
public static final String SERIES_NEXTTEXT = "nexttext";
public static final String SERIES_POSTER = "poster";
public static final String SERIES_CONTENTRATING = "contentrating";
public static final String SERIES_STATUS = "status";
public static final String SERIES_RUNTIME = "runtime";
public static final String SERIES_RATING = "rating";
public static final String SERIES_NETWORK = "network";
public static final String SERIES_GENRES = "genres";
public static final String SERIES_FIRSTAIRED = "firstaired";
public static final String SERIES_AIRSTIME = "airstime";
public static final String SERIES_AIRSDAYOFWEEK = "airsdayofweek";
public static final String SERIES_ACTORS = "actors";
public static final String SERIES_OVERVIEW = "overview";
public static final String SERIES_NAME = "seriestitle";
public static final String SERIES_ID = "_id";
public static final String SERIES_IMDBID = "imdbid";
private DatabaseHelper mDbHelper;
private SQLiteDatabase mDb;
private final Context mApplicationCtx;
private static class DatabaseHelper extends SQLiteOpenHelper {
private static final String CREATE_SERIES_TABLE = "create table " + SERIES_TABLE + " ("
+ SERIES_ID + " integer PRIMARY KEY,"
+ SERIES_NAME + " TEXT,"
+ SERIES_OVERVIEW + " TEXT DEFAULT '',"
+ SERIES_ACTORS + " TEXT DEFAULT '',"
+ SERIES_AIRSDAYOFWEEK + " TEXT DEFAULT '',"
+ SERIES_AIRSTIME + " INTEGER DEFAULT '',"
+ SERIES_FIRSTAIRED + " TEXT DEFAULT '',"
+ SERIES_GENRES + " TEXT DEFAULT '',"
+ SERIES_NETWORK + " TEXT DEFAULT '',"
+ SERIES_RATING + " TEXT DEFAULT '',"
+ SERIES_RUNTIME + " TEXT DEFAULT '',"
+ SERIES_STATUS + " TEXT DEFAULT '',"
+ SERIES_CONTENTRATING + " TEXT DEFAULT '',"
+ SERIES_NEXTEPISODE + " TEXT DEFAULT '',"
+ SERIES_POSTER + " TEXT DEFAULT '',"
+ SERIES_NEXTAIRDATE + " text default '0',"
+ SERIES_NEXTTEXT + " text default '',"
+ SERIES_IMDBID + " text default ''"
+ ");";
DatabaseHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
@Override
public void onCreate(SQLiteDatabase db) {
/*
* Be aware that sqlite comes in different versions on shipping
* Android devices, so some do NOT support foreign keys or constraints.
* It is not that big of a downside, as it is more a convenience to
* have those (e.g. cascade on delete).
*/
db.execSQL(CREATE_SERIES_TABLE);
db.execSQL("create table " + SEASONS_TABLE + " ("
+ SeriesDatabase.SEASON_ID + " integer PRIMARY KEY,"
+ SeriesDatabase.SEASON_COMBINED + " integer,"
+ SeriesDatabase.SEASON_SERIES_ID + " integer REFERENCES " + SERIES_TABLE + "(" + SeriesDatabase.SERIES_ID
+ ") ON UPDATE CASCADE ON DELETE CASCADE,"
+ SeriesDatabase.SEASON_WATCHCOUNT + " integer default 0,"
+ SeriesDatabase.SEASON_UNAIREDCOUNT + " integer default 0,"
+ SeriesDatabase.SEASON_NOAIRDATECOUNT + " integer default 0,"
+ SeriesDatabase.SEASON_POSTER + " TEXT DEFAULT ''" + ");");
db.execSQL("create table " + EPISODE_TABLE + " ("
+ SeriesDatabase.EPISODE_ID + " integer PRIMARY KEY,"
+ SeriesDatabase.EPISODE_TITLE + " text NOT NULL,"
+ SeriesDatabase.EPISODE_OVERVIEW + " text,"
+ SeriesDatabase.EPISODE_NUMBER + " integer default 0,"
+ SeriesDatabase.EPISODE_SEASON + " integer default 0,"
+ SeriesDatabase.EPISODE_DVDNUMBER + " real,"
+ SeriesDatabase.EPISODE_FIRSTAIRED + " text,"
+ SeriesDatabase.EPISODE_SEASON_ID + " integer REFERENCES " + SEASONS_TABLE + "(" + SeriesDatabase.SEASON_ID
+ ") ON UPDATE CASCADE ON DELETE CASCADE,"
+ SeriesDatabase.EPISODE_SERIES_ID + " integer,"
+ SeriesDatabase.EPISODE_WATCHED + " integer DEFAULT 0,"
+ SeriesDatabase.EPISODE_DIRECTORS + " text DEFAULT '',"
+ SeriesDatabase.EPISODE_GUESTSTARS + " text DEFAULT '',"
+ SeriesDatabase.EPISODE_WRITERS + " text DEFAULT '',"
+ SeriesDatabase.EPISODE_IMAGE + " text DEFAULT '',"
+ SeriesDatabase.EPISODE_RATING + " text DEFAULT ''" + ");");
db.execSQL("create virtual table " + FTS_SEARCH_TABLE + " using FTS3("
+ SeriesDatabase.EPISODE_TITLE + " text,"
+ SeriesDatabase.EPISODE_OVERVIEW + " text"
+ ");");
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
Log.w(TAG, "Upgrading database from version " + oldVersion + " to " + newVersion);
if (oldVersion < 4 || oldVersion > SeriesDatabase.DATABASE_VERSION) {
Log.w(TAG, "Database is newer version (" + oldVersion + ") than this app is using (" + newVersion + "), starting from scratch");
db.execSQL("DROP TABLE IF EXISTS " + SERIES_TABLE);
db.execSQL("DROP TABLE IF EXISTS " + SEASONS_TABLE);
db.execSQL("DROP TABLE IF EXISTS " + EPISODE_TABLE);
db.execSQL("drop table if exists " + FTS_SEARCH_TABLE);
db.setVersion(SeriesDatabase.DATABASE_VERSION);
onCreate(db);
return;
}
File dbFile = new File(Environment.getDataDirectory()
+ SeriesDatabase.SERIESDATABASE_PATH);
File exportDir = new File(Environment.getExternalStorageDirectory(),
"seriesguidebackup");
if (!exportDir.exists()) {
exportDir.mkdirs();
}
File file = new File(exportDir, dbFile.getName() + "_b4upgr");
try {
file.createNewFile();
FileUtil.copyFile(dbFile, file);
} catch (IOException e) {
Log.e(TAG, e.getMessage(), e);
}
int currentVersion = oldVersion;
while (currentVersion < newVersion) {
switch (currentVersion) {
case 4:
upgradeToFive(db);
break;
case 5:
upgradeToSix(db);
break;
case 6:
upgradeToSeven(db);
break;
case 7:
upgradeToEight(db);
break;
case 8:
upgradeToNine(db);
break;
case 9:
upgradeToTen(db);
break;
case 10:
upgradeToEleven(db);
break;
case 11:
upgradeToTwelve(db);
break;
case 12:
upgradeToThirteen(db);
break;
case 13:
upgradeToFourteen(db);
break;
case 14:
upgradeToFifteen(db);
break;
case 15:
upgradeToSixteen(db);
break;
}
currentVersion++;
}
db.setVersion(newVersion);
}
/**
* In version 5 more episode information was added.
*
* @param db
*/
private void upgradeToFive(SQLiteDatabase db) {
db.execSQL("ALTER TABLE " + EPISODE_TABLE + " ADD COLUMN " + SeriesDatabase.EPISODE_DIRECTORS
+ " TEXT DEFAULT '';");
db.execSQL("ALTER TABLE " + EPISODE_TABLE + " ADD COLUMN " + SeriesDatabase.EPISODE_GUESTSTARS
+ " TEXT DEFAULT '';");
db.execSQL("ALTER TABLE " + EPISODE_TABLE + " ADD COLUMN " + SeriesDatabase.EPISODE_WRITERS
+ " TEXT DEFAULT '';");
db.execSQL("ALTER TABLE " + EPISODE_TABLE + " ADD COLUMN " + SeriesDatabase.EPISODE_RATING
+ " TEXT DEFAULT '';");
}
/**
* In version 6 a watchcount column was added to the season list.
*
* @param db
*/
private void upgradeToSix(SQLiteDatabase db) {
db.execSQL("ALTER TABLE " + SEASONS_TABLE + " ADD COLUMN " + SeriesDatabase.SEASON_WATCHCOUNT
+ " INTEGER;");
}
/**
* In version 7 more information about a show was added. Since 1.2.0.
*
* @param db
*/
private void upgradeToSeven(SQLiteDatabase db) {
db.execSQL("ALTER TABLE " + SERIES_TABLE + " ADD COLUMN " + SeriesDatabase.SERIES_OVERVIEW
+ " TEXT DEFAULT '';");
db.execSQL("ALTER TABLE " + SERIES_TABLE + " ADD COLUMN " + SeriesDatabase.SERIES_ACTORS
+ " TEXT DEFAULT '';");
db.execSQL("ALTER TABLE " + SERIES_TABLE + " ADD COLUMN " + SeriesDatabase.SERIES_AIRSDAYOFWEEK
+ " TEXT DEFAULT '';");
db.execSQL("ALTER TABLE " + SERIES_TABLE + " ADD COLUMN " + SeriesDatabase.SERIES_AIRSTIME
+ " TEXT DEFAULT '';");
db.execSQL("ALTER TABLE " + SERIES_TABLE + " ADD COLUMN " + SeriesDatabase.SERIES_FIRSTAIRED
+ " TEXT DEFAULT '';");
db.execSQL("ALTER TABLE " + SERIES_TABLE + " ADD COLUMN " + SeriesDatabase.SERIES_GENRES
+ " TEXT DEFAULT '';");
db.execSQL("ALTER TABLE " + SERIES_TABLE + " ADD COLUMN " + SeriesDatabase.SERIES_NETWORK
+ " TEXT DEFAULT '';");
db.execSQL("ALTER TABLE " + SERIES_TABLE + " ADD COLUMN " + SeriesDatabase.SERIES_RATING
+ " TEXT DEFAULT '';");
db.execSQL("ALTER TABLE " + SERIES_TABLE + " ADD COLUMN " + SeriesDatabase.SERIES_RUNTIME
+ " TEXT DEFAULT '';");
db.execSQL("ALTER TABLE " + SERIES_TABLE + " ADD COLUMN " + SeriesDatabase.SERIES_STATUS
+ " TEXT DEFAULT '';");
db.execSQL("ALTER TABLE " + SERIES_TABLE + " ADD COLUMN " + SeriesDatabase.SERIES_CONTENTRATING
+ " TEXT DEFAULT '';");
db.execSQL("ALTER TABLE " + SERIES_TABLE + " ADD COLUMN " + SeriesDatabase.SERIES_POSTER
+ " TEXT DEFAULT '';");
}
/**
* In version 8 a poster column was added to the season list. Since
* 1.3.0.
*
* @param db
*/
private void upgradeToEight(SQLiteDatabase db) {
db.execSQL("ALTER TABLE " + SEASONS_TABLE + " ADD COLUMN " + SeriesDatabase.SEASON_POSTER
+ " TEXT DEFAULT '';");
db.execSQL("ALTER TABLE " + EPISODE_TABLE + " ADD COLUMN " + SeriesDatabase.EPISODE_IMAGE
+ " TEXT DEFAULT '';");
}
/**
* In version 9 a next episode column was added to the show list. Since
* 1.3.2.
*
* @param db
*/
private void upgradeToNine(SQLiteDatabase db) {
db.execSQL("ALTER TABLE " + SERIES_TABLE + " ADD COLUMN " + SeriesDatabase.SERIES_NEXTEPISODE
+ " TEXT DEFAULT '';");
}
/**
* In version 10 a will air count column was added to the season list.
*
* @param db
*/
private void upgradeToTen(SQLiteDatabase db) {
db.execSQL("ALTER TABLE " + SEASONS_TABLE + " ADD COLUMN " + SeriesDatabase.SEASON_UNAIREDCOUNT
+ " INTEGER;");
}
/**
* In version 11 the combined season and number values in episodes
* have been replaced with number and season. In the season table
* the name column was removed.
* @param db
*/
private void upgradeToEleven(SQLiteDatabase db){
// db.execSQL("ALTER TABLE " + EPISODE_TABLE + " ADD COLUMN " + SeriesGuideData.EPISODE_NUMBER
// + " INTEGER DEFAULT 0;");
// db.execSQL("ALTER TABLE " + EPISODE_TABLE + " ADD COLUMN " + SeriesGuideData.EPISODE_SEASON
// + " INTEGER DEFAULT 0;");
db.execSQL("ALTER TABLE " + EPISODE_TABLE + " RENAME TO episode_old;");
db.execSQL("create table " + EPISODE_TABLE + " ("
+ SeriesDatabase.EPISODE_ID + " int PRIMARY KEY,"
+ SeriesDatabase.EPISODE_TITLE + " text NOT NULL,"
+ SeriesDatabase.EPISODE_OVERVIEW + " text,"
// + SeriesGuideData.EPISODE_COMBINEDNUMBER + " INTEGER,"
// + SeriesGuideData.EPISODE_COMBINEDSEASON + " INTEGER,"
+ SeriesDatabase.EPISODE_NUMBER + " int DEFAULT 0,"
+ SeriesDatabase.EPISODE_SEASON + " int DEFAULT 0,"
+ SeriesDatabase.EPISODE_FIRSTAIRED + " text,"
+ SeriesDatabase.EPISODE_SEASON_ID + " int REFERENCES " + SEASONS_TABLE + "(" + SeriesDatabase.SEASON_ID
+ ") ON UPDATE CASCADE ON DELETE CASCADE,"
+ SeriesDatabase.EPISODE_SERIES_ID + " int,"
+ SeriesDatabase.EPISODE_WATCHED + " int DEFAULT 0,"
+ SeriesDatabase.EPISODE_DIRECTORS + " text DEFAULT '',"
+ SeriesDatabase.EPISODE_GUESTSTARS + " text DEFAULT '',"
+ SeriesDatabase.EPISODE_WRITERS + " text DEFAULT '',"
+ SeriesDatabase.EPISODE_IMAGE + " text DEFAULT '',"
+ SeriesDatabase.EPISODE_RATING + " text DEFAULT ''" + ");");
db.execSQL("INSERT INTO " + EPISODE_TABLE + "("
+ EPISODE_ID + ","
+ EPISODE_TITLE + ","
+ EPISODE_OVERVIEW + ","
// + "combinednr" + ","
// + "combinedseason" + ","
+ EPISODE_NUMBER + ","
+ EPISODE_SEASON + ","
+ EPISODE_FIRSTAIRED + ","
+ EPISODE_SEASON_ID + ","
+ EPISODE_SERIES_ID + ","
+ EPISODE_WATCHED + ","
+ EPISODE_DIRECTORS + ","
+ EPISODE_GUESTSTARS + ","
+ EPISODE_WRITERS + ","
+ EPISODE_IMAGE + ","
+ EPISODE_RATING
+ ")"
+ " SELECT "
+ EPISODE_ID + ","
+ EPISODE_TITLE + ","
+ EPISODE_OVERVIEW + ","
+ "combinednr" + ","
+ "combinedseason" + ","
// + EPISODE_NUMBER + ","
// + EPISODE_SEASON + ","
+ EPISODE_FIRSTAIRED + ","
+ EPISODE_SEASON_ID + ","
+ EPISODE_SERIES_ID + ","
+ EPISODE_WATCHED + ","
+ EPISODE_DIRECTORS + ","
+ EPISODE_GUESTSTARS + ","
+ EPISODE_WRITERS + ","
+ EPISODE_IMAGE + ","
+ EPISODE_RATING
+ " FROM episode_old;");
db.execSQL("DROP TABLE episode_old;");
db.execSQL("ALTER TABLE " + SEASONS_TABLE + " RENAME TO seasons_old;");
db.execSQL("create table " + SEASONS_TABLE + " ("
+ SeriesDatabase.SEASON_ID + " int PRIMARY KEY,"
+ SeriesDatabase.SEASON_COMBINED + " int,"
// + SeriesGuideData.SEASON_NAME + " TEXT,"
+ SeriesDatabase.SEASON_SERIES_ID + " int REFERENCES " + SERIES_TABLE + "(" + SeriesDatabase.SERIES_ID
+ ") ON UPDATE CASCADE ON DELETE CASCADE,"
+ SeriesDatabase.SEASON_WATCHCOUNT + " int,"
+ SeriesDatabase.SEASON_UNAIREDCOUNT + " int,"
+ SeriesDatabase.SEASON_POSTER + " TEXT DEFAULT ''" + ");");
db.execSQL("INSERT INTO " + SEASONS_TABLE
+ " SELECT "
+ SeriesDatabase.SEASON_ID + ","
+ SeriesDatabase.SEASON_COMBINED + ","
+ SeriesDatabase.SEASON_SERIES_ID + ","
+ SeriesDatabase.SEASON_WATCHCOUNT + ","
+ SeriesDatabase.SEASON_UNAIREDCOUNT + ","
+ SeriesDatabase.SEASON_POSTER
+ " FROM seasons_old;");
db.execSQL("DROP TABLE seasons_old;");
}
/**
* In version 12 a virtual FTS3 table was created to allow
* faster text search within episodes.
* @param db
*/
private void upgradeToTwelve(SQLiteDatabase db){
db.execSQL("create virtual table " + FTS_SEARCH_TABLE + " using FTS3("
+ SeriesDatabase.EPISODE_TITLE + " text,"
+ SeriesDatabase.EPISODE_OVERVIEW + " text"
+ ");");
db.execSQL("INSERT INTO " + FTS_SEARCH_TABLE
+ "(docid,"
+ SeriesDatabase.EPISODE_TITLE + ","
+ SeriesDatabase.EPISODE_OVERVIEW + ")"
+ " select "
+ SeriesDatabase.EPISODE_ID + ","
+ SeriesDatabase.EPISODE_TITLE + ","
+ SeriesDatabase.EPISODE_OVERVIEW
+ " from " + EPISODE_TABLE + ";");
}
/**
* In version 13 the next episode field is just storing the
* id of the next episode. Clears out previous values to "".
* @param db
*/
private void upgradeToThirteen(SQLiteDatabase db){
ContentValues values = new ContentValues();
values.put(SERIES_NEXTEPISODE, "");
db.update(SERIES_TABLE, values, null, null);
db.execSQL("ALTER TABLE " + SERIES_TABLE + " ADD COLUMN " + SERIES_NEXTAIRDATE
+ " TEXT DEFAULT '0';");
db.execSQL("ALTER TABLE " + SERIES_TABLE + " ADD COLUMN " + SERIES_NEXTTEXT
+ " TEXT DEFAULT '';");
}
/**
* In version 14 the imdb id for shows gets now parsed, too. A season
* now knows the number of all its episodes.
*
* @param db
*/
private void upgradeToFourteen(SQLiteDatabase db) {
db.execSQL("ALTER TABLE " + SERIES_TABLE + " ADD COLUMN " + SERIES_IMDBID
+ " text default '';");
db.execSQL("ALTER TABLE " + SEASONS_TABLE + " ADD COLUMN " + SEASON_NOAIRDATECOUNT
+ " integer default 0;");
}
/**
* In version 15 the DVD episode number is stored in the episodes table.
*
* @param db
*/
private void upgradeToFifteen(SQLiteDatabase db) {
db.execSQL("ALTER TABLE " + EPISODE_TABLE + " ADD COLUMN " + EPISODE_DVDNUMBER
+ " real;");
}
/**
* In version 16 the shows airtime was converted to integer values.
*
* @param db
*/
private void upgradeToSixteen(SQLiteDatabase db) {
// convert airtime strings to ms-integers
Cursor shows = db.query(SERIES_TABLE, new String[] {
SERIES_ID, SERIES_AIRSTIME
}, null, null, null, null, null);
String id;
long airtime;
String airtimeText;
ContentValues values = new ContentValues();
db.beginTransaction();
try {
while (shows.moveToNext()) {
id = shows.getString(shows.getColumnIndexOrThrow(SERIES_ID));
airtimeText = shows.getString(shows.getColumnIndexOrThrow(SERIES_AIRSTIME));
airtime = SeriesGuideData.parseTimeToMilliseconds(airtimeText);
values.put(SERIES_AIRSTIME, airtime);
db.update(SERIES_TABLE, values, SERIES_ID + "=?", new String[] { id });
values.clear();
}
db.setTransactionSuccessful();
} finally {
db.endTransaction();
}
shows.close();
// rename old table, create new one with different schema, copy data, delete old one
db.execSQL("ALTER TABLE " + SERIES_TABLE + " RENAME TO series_old;");
db.execSQL(CREATE_SERIES_TABLE);
db.execSQL("INSERT INTO " + SERIES_TABLE
+ "("
+ SERIES_ID + ","
+ SERIES_NAME + ","
+ SERIES_OVERVIEW + ","
+ SERIES_ACTORS + ","
+ SERIES_AIRSDAYOFWEEK + ","
+ SERIES_AIRSTIME + ","
+ SERIES_FIRSTAIRED + ","
+ SERIES_GENRES + ","
+ SERIES_NETWORK + ","
+ SERIES_RATING + ","
+ SERIES_RUNTIME + ","
+ SERIES_STATUS + ","
+ SERIES_CONTENTRATING + ","
+ SERIES_NEXTEPISODE + ","
+ SERIES_POSTER + ","
+ SERIES_NEXTAIRDATE + ","
+ SERIES_NEXTTEXT + ","
+ SERIES_IMDBID
+ ")"
+ " SELECT "
+ SERIES_ID + ","
+ SERIES_NAME + ","
+ SERIES_OVERVIEW + ","
+ SERIES_ACTORS + ","
+ SERIES_AIRSDAYOFWEEK + ","
+ SERIES_AIRSTIME + ","
+ SERIES_FIRSTAIRED + ","
+ SERIES_GENRES + ","
+ SERIES_NETWORK + ","
+ SERIES_RATING + ","
+ SERIES_RUNTIME + ","
+ SERIES_STATUS + ","
+ SERIES_CONTENTRATING + ","
+ SERIES_NEXTEPISODE + ","
+ SERIES_POSTER + ","
+ SERIES_NEXTAIRDATE + ","
+ SERIES_NEXTTEXT + ","
+ SERIES_IMDBID
+ " FROM series_old;");
db.execSQL("DROP TABLE series_old;");
}
}
/**
* Constructor - takes the context to allow the database to be
* opened/created
*
* @param ctx
* the Context within which to work
*/
public SeriesDatabase(Context ctx) {
this.mApplicationCtx = ctx;
}
/**
* Create new DatabaseHelper and then try to get a writable database from
* it.
*
* @return SeriesDatabase
* @throws SQLException
*/
public SeriesDatabase open() {
mDbHelper = new DatabaseHelper(mApplicationCtx);
mDb = mDbHelper.getWritableDatabase();
return this;
}
/**
* Closes the DatabaseHelper.
*/
public void close() {
mDbHelper.close();
}
/**
* Closes the database of the DatabaseHelper and reopens it from the default
* SeriesGuide database file.
*/
public void resetDbConnection() {
this.close();
this.open();
}
/**
* Fetches all shows in the series table, rows sorted by
* given sorting. Does not include all columns.
* To be used to list shows.
*
* @return Cursor including the rows for all series
*/
public Cursor fetchAllSeries(ShowSorting sorting) {
return mDb.query(SERIES_TABLE, new String[] { SERIES_ID, SERIES_NAME, SERIES_AIRSDAYOFWEEK,
SERIES_AIRSTIME, SERIES_NETWORK, SERIES_NEXTTEXT, SERIES_POSTER, SERIES_STATUS }, null, null, null, null,
sorting.query());
}
/**
* Fetches all seasons in a cursor, only returning the id and poster column.
* @return Cursor including id and poster path for all shows
*/
public Cursor fetchAllSeriesPosterPaths(){
return mDb.query(SERIES_TABLE, new String[] { SeriesDatabase.SERIES_ID, SeriesDatabase.SERIES_POSTER }, null, null, null, null, null);
}
/**
* Fetches a series seasons sorted by combined season number. Returned
* sorting order is determined by given sorting parameter (asc or desc).
*
* @param sorting order to sort the seasons, uses enum
* SeriesDatabase.Sorting
* @return Cursor containing a series seasons sorted by given order
*/
public Cursor fetchSeriesSeasons(Long seriesid, SeriesGuideData.SeasonSorting sorting) {
return mDb.query(SEASONS_TABLE, new String[] {
SEASON_ID, SEASON_COMBINED, SEASON_WATCHCOUNT, SEASON_UNAIREDCOUNT,
SEASON_NOAIRDATECOUNT
}, SEASON_SERIES_ID + "=" + seriesid.toString(), null, null, null, sorting.query());
}
/**
* Looks up the episodes of a given season and stores the count of already aired,
* but not watched ones in the seasons watchcount.
*
* @param seasonid
*/
public void updateUnwatchedCount(long seasonid) {
Date date = new Date();
String today = SeriesGuideData.theTVDBDateFormat.format(date);
// unwatched, aired episodes
Cursor unwatched = mDb.query(EPISODE_TABLE, new String[] { SeriesDatabase.EPISODE_ID }, SeriesDatabase.EPISODE_SEASON_ID
+ "=" + seasonid + " AND " + SeriesDatabase.EPISODE_WATCHED + "=0 AND " + SeriesDatabase.EPISODE_FIRSTAIRED + " like '%-%'" + " AND " + SeriesDatabase.EPISODE_FIRSTAIRED + "<='" + today + "'", null, null, null, null);
int count = unwatched.getCount();
unwatched.close();
// unwatched, aired in the future episodes
Cursor unaired = mDb.query(EPISODE_TABLE, new String[] {
SeriesDatabase.EPISODE_ID
}, SeriesDatabase.EPISODE_SEASON_ID + "=" + seasonid + " AND "
+ SeriesDatabase.EPISODE_WATCHED + "=0 AND " + SeriesDatabase.EPISODE_FIRSTAIRED
+ ">'" + today + "'", null, null, null, null);
int unaired_count = unaired.getCount();
unaired.close();
// unwatched, no airdate
Cursor noairdate = mDb.query(EPISODE_TABLE, new String[] { SeriesDatabase.EPISODE_ID },
SeriesDatabase.EPISODE_SEASON_ID + "=" + seasonid + " AND "
+ SeriesDatabase.EPISODE_WATCHED + "=0 AND " + SeriesDatabase.EPISODE_FIRSTAIRED
+ "=''", null, null, null, null);
int noairdate_count = noairdate.getCount();
noairdate.close();
ContentValues update = new ContentValues();
update.put(SEASON_WATCHCOUNT, count);
update.put(SEASON_UNAIREDCOUNT, unaired_count);
update.put(SEASON_NOAIRDATECOUNT, noairdate_count);
mDb.update(SEASONS_TABLE, update, SeriesDatabase.SEASON_ID + "=" + seasonid, null);
}
/**
* Fetches a seasons episodes sorted by combined episode number. Returned
* sorting order is determined by given sorting parameter (asc or desc).
* Just returns columsn needed for episode listing. (no description, etc.)
*
* @param sorting
* order to sort the episodes, uses enum SeriesDatabase.Sorting
* @return Cursor containing a seasons episodes sorted by given order
*/
public Cursor fetchSeasonEpisodes(Long seasonid, SeriesGuideData.EpisodeSorting sorting) {
return mDb.query(EPISODE_TABLE, new String[] { EPISODE_ID, EPISODE_WATCHED, EPISODE_TITLE,
EPISODE_NUMBER, EPISODE_DVDNUMBER, EPISODE_FIRSTAIRED }, EPISODE_SEASON_ID + "="
+ seasonid.toString(), null, null, null, sorting.query());
}
/**
* Returns the first 20 upcoming episodes together with their shows title, network and airtime.
* @param limit
* @return Cursor including 20 upcoming episodes with show title, network and airtime.
*/
public Cursor getUpcomingEpisodes(String limit) {
Date date = new Date();
String today = SeriesGuideData.theTVDBDateFormat.format(date);
String query = "select " + EPISODE_ID + "," + EPISODE_TITLE + "," + EPISODE_WATCHED + ","
+ EPISODE_NUMBER + "," + EPISODE_SEASON + "," + EPISODE_FIRSTAIRED + "," + SERIES_NAME
+ "," + SERIES_AIRSTIME + "," + SERIES_NETWORK
+ " from "
+ "("
+ "select " + EPISODE_ID + "," + EPISODE_TITLE + "," + EPISODE_WATCHED + ","
+ EPISODE_NUMBER + "," + EPISODE_SEASON + "," + EPISODE_FIRSTAIRED + "," + EPISODE_SERIES_ID
+ " from " + EPISODE_TABLE
+ " where " + EPISODE_FIRSTAIRED + ">='" + today + "'"
+ " order by " + EPISODE_FIRSTAIRED + " asc"
+ ")"
+ " join "
+ "("
+ "select " + SERIES_ID + " as sid," + SERIES_NAME + "," + SERIES_AIRSTIME + "," + SERIES_NETWORK
+ " from " + SERIES_TABLE
+ ")"
+ " on " + EPISODE_SERIES_ID + "=" + "sid"
+ " order by " + EPISODE_FIRSTAIRED + " asc," + SERIES_AIRSTIME + " asc," + SERIES_NAME + " asc"
+ " limit " + limit
;
return mDb.rawQuery(query, null);
}
public Cursor getRecentEpisodes(String limit) {
Date date = new Date();
String today = SeriesGuideData.theTVDBDateFormat.format(date);
String query = "select " + EPISODE_ID + "," + EPISODE_TITLE + "," + EPISODE_WATCHED + ","
+ EPISODE_NUMBER + "," + EPISODE_SEASON + "," + EPISODE_FIRSTAIRED + "," + SERIES_NAME
+ "," + SERIES_AIRSTIME + "," + SERIES_NETWORK
+ " from "
+ "("
+ "select " + EPISODE_ID + "," + EPISODE_TITLE + "," + EPISODE_WATCHED + ","
+ EPISODE_NUMBER + "," + EPISODE_SEASON + "," + EPISODE_FIRSTAIRED + "," + EPISODE_SERIES_ID
+ " from " + EPISODE_TABLE
+ " where " + EPISODE_FIRSTAIRED + "<'" + today + "'"
+ " order by " + EPISODE_FIRSTAIRED + " desc"
+ " limit " + limit
+ ")"
+ " join "
+ "("
+ "select " + SERIES_ID + " as sid," + SERIES_NAME + "," + SERIES_AIRSTIME + "," + SERIES_NETWORK
+ " from " + SERIES_TABLE
+ ")"
+ " on " + EPISODE_SERIES_ID + "=" + "sid"
+ " order by " + EPISODE_FIRSTAIRED + " desc," + SERIES_AIRSTIME + " asc," + SERIES_NAME + " asc"
;
return mDb.rawQuery(query, null);
}
/**
* Drop all tables in the database and recreate an empty database.
*/
public void clear() {
mDbHelper.onUpgrade(mDb, 0, SeriesDatabase.DATABASE_VERSION);
}
/**
* Return the version of the database.
*
* @return the database version as int
*/
public int getVersion() {
return mDb.getVersion();
}
/**
* Updates the given boolean to the EPISODE_WATCHED column of the given
* episode row. Be aware that the database stores the value as an integer.
*
* @param rowid
* @param state
*/
public void markEpisode(long rowid, boolean state) {
ContentValues values = new ContentValues();
values.put(SeriesDatabase.EPISODE_WATCHED, state);
mDb.update(EPISODE_TABLE, values, SeriesDatabase.EPISODE_ID + "=" + rowid, null);
}
/**
* Marks the next episode (if there is one) of this show as watched.
*
* @param seriesid
*/
public void markNextEpisode(long seriesid) {
Cursor show = mDb.query(SERIES_TABLE, new String[] {SERIES_NEXTEPISODE}, SERIES_ID + "=" + String.valueOf(seriesid), null, null, null, null);
show.moveToFirst();
ContentValues values = new ContentValues();
values.put(SeriesDatabase.EPISODE_WATCHED, true);
String episodeid = show.getString(show.getColumnIndexOrThrow(SERIES_NEXTEPISODE));
if (episodeid.length() != 0) {
mDb.update(EPISODE_TABLE, values, SeriesDatabase.EPISODE_ID + "=" + episodeid, null);
}
show.close();
}
/**
* Updates all EPISODE_WATCHED columns of the episodes of the given season
* with the given boolean. Be aware that the database stores the value as an
* integer.
*
* @param seasonid
* @param state
*/
public void markSeasonEpisodes(long seasonid, boolean state) {
ContentValues values = new ContentValues();
values.put(SeriesDatabase.EPISODE_WATCHED, state);
mDb.update(EPISODE_TABLE, values, SeriesDatabase.EPISODE_SEASON_ID + "=" + seasonid, null);
}
/**
* Queries the database for the given episode. Returns some of the column
* values as a bundle as required by the EpisodeDetails activity.
*
* @param episodeid
* @return a Bundle containing some information about an episode
*/
public Bundle getEpisodeDetails(Long episodeid) {
Cursor details = mDb.query(EPISODE_TABLE, null, EPISODE_ID + "=" + episodeid, null, null,
null, null);
details.moveToFirst();
// bundle up episode metadata
Bundle extras = new Bundle();
extras.putString(EPISODE_TITLE, details.getString(details
.getColumnIndexOrThrow(EPISODE_TITLE)));
extras.putString(EPISODE_OVERVIEW, details.getString(details
.getColumnIndexOrThrow(EPISODE_OVERVIEW)));
extras.putString(EPISODE_NUMBER, details.getString(details
.getColumnIndexOrThrow(EPISODE_NUMBER)));
extras.putString(EPISODE_SEASON, details.getString(details
.getColumnIndexOrThrow(EPISODE_SEASON)));
extras.putBoolean(EPISODE_WATCHED, 1 == details.getInt((details
.getColumnIndexOrThrow(EPISODE_WATCHED))));
extras.putString(EPISODE_FIRSTAIRED, details.getString(details
.getColumnIndexOrThrow(EPISODE_FIRSTAIRED)));
extras.putString(EPISODE_DIRECTORS, details.getString(details
.getColumnIndexOrThrow(EPISODE_DIRECTORS)));
extras.putString(EPISODE_GUESTSTARS, details.getString(details
.getColumnIndexOrThrow(EPISODE_GUESTSTARS)));
extras.putString(EPISODE_WRITERS, details.getString(details
.getColumnIndexOrThrow(EPISODE_WRITERS)));
extras.putString(EPISODE_RATING, details.getString(details
.getColumnIndexOrThrow(EPISODE_RATING)));
extras.putString(EPISODE_IMAGE, details.getString(details
.getColumnIndexOrThrow(EPISODE_IMAGE)));
float dvdnumber = details.getFloat(details
.getColumnIndexOrThrow(EPISODE_DVDNUMBER));
if (dvdnumber == 0.0) {
extras.putString(EPISODE_DVDNUMBER, "");
} else {
extras.putString(EPISODE_DVDNUMBER, String.valueOf(dvdnumber));
}
// look for and bundle show name
Cursor show = mDb.query(SERIES_TABLE, new String[] {SERIES_NAME}, SERIES_ID + "=" + details.getString(details
.getColumnIndexOrThrow(EPISODE_SERIES_ID)), null, null, null, null);
show.moveToFirst();
extras.putString(SERIES_NAME, show.getString(show.getColumnIndexOrThrow(SERIES_NAME)));
show.close();
details.close();
return extras;
}
/**
* Fetches the row to a given show id and returns the results an Series
* object.
*
* @param seriesid
* @return
*/
public Series getShow(Long seriesid) {
Series show = new Series();
Cursor details = mDb.query(SERIES_TABLE, null, SeriesDatabase.SERIES_ID + "=" + seriesid, null, null,
null, null);
details.moveToFirst();
show.setActors(details.getString(details.getColumnIndexOrThrow(SERIES_ACTORS)));
show.setAirsDayOfWeek(details
.getString(details.getColumnIndexOrThrow(SERIES_AIRSDAYOFWEEK)));
show.setAirsTime(details.getLong(details.getColumnIndexOrThrow(SERIES_AIRSTIME)));
show.setContentRating(details
.getString(details.getColumnIndexOrThrow(SERIES_CONTENTRATING)));
show.setFirstAired(details.getString(details.getColumnIndexOrThrow(SERIES_FIRSTAIRED)));
show.setGenres(details.getString(details.getColumnIndexOrThrow(SERIES_GENRES)));
show.setId(details.getString(details.getColumnIndexOrThrow(SERIES_ID)));
show.setNetwork(details.getString(details.getColumnIndexOrThrow(SERIES_NETWORK)));
show.setOverview(details.getString(details.getColumnIndexOrThrow(SERIES_OVERVIEW)));
show.setPoster(details.getString(details.getColumnIndexOrThrow(SERIES_POSTER)));
show.setRating(details.getString(details.getColumnIndexOrThrow(SERIES_RATING)));
show.setRuntime(details.getString(details.getColumnIndexOrThrow(SERIES_RUNTIME)));
show.setSeriesId(details.getString(details.getColumnIndexOrThrow(SERIES_ID)));
show.setSeriesName(details.getString(details.getColumnIndexOrThrow(SERIES_NAME)));
show.setStatus(details.getString(details.getColumnIndexOrThrow(SERIES_STATUS)));
show.setImdbId(details.getString(details.getColumnIndexOrThrow(SERIES_IMDBID)));
details.close();
return show;
}
/**
* Adds a show to the series table if it not exists already. Use updateShow
* if you want to change a shows details.
*
* @param show
* @return true if the show was created, false if there already is a show
* with this id
*/
public boolean addShow(Series show) {
Cursor testsearch = mDb.query(SERIES_TABLE, new String[] { SeriesDatabase.SERIES_ID }, SeriesDatabase.SERIES_ID + "="
+ show.getId(), null, null, null, null);
if (testsearch.getCount() != 0) {
testsearch.close();
return false;
}
testsearch.close();
ContentValues values = new ContentValues();
values.put(SeriesDatabase.SERIES_ID, show.getId());
values = putCommonShowValues(show, values);
mDb.insert(SERIES_TABLE, null, values);
return true;
}
/**
* Updates show information. Currently only SERIES_TITLE.
*
* @param show
*/
public void updateShow(Series show) {
ContentValues values = new ContentValues();
values = putCommonShowValues(show, values);
mDb.update(SERIES_TABLE, values, SeriesDatabase.SERIES_ID + "=" + show.getId(), null);
}
/**
* Delete a show and manually delete its seasons and episodes as the Android
* Sqlite version does not enforce constraints :( Using a transaction.
*
* @param id
* Seriesid
*/
public void deleteShow(long id) {
mDb.beginTransaction();
try {
HashSet<Long> episodeIDs = getEpisodeIDsForShow(id);
for (Long episodeID : episodeIDs) {
mDb.delete(FTS_SEARCH_TABLE, "docid=" + episodeID, null);
}
mDb.delete(SERIES_TABLE, SeriesDatabase.SERIES_ID + "=" + id, null);
mDb.delete(SEASONS_TABLE, SeriesDatabase.SEASON_SERIES_ID + "=" + id, null);
mDb.delete(EPISODE_TABLE, SeriesDatabase.EPISODE_SERIES_ID + "=" + id, null);
mDb.setTransactionSuccessful();
} finally {
mDb.endTransaction();
}
}
/**
* Adds default show information from given Series object to given
* ContentValues.
*
* @param show
* @param values
* @return
*/
private static ContentValues putCommonShowValues(Series show, ContentValues values) {
values.put(SERIES_NAME, show.getSeriesName());
values.put(SERIES_OVERVIEW, show.getOverview());
values.put(SERIES_ACTORS, show.getActors());
values.put(SERIES_AIRSDAYOFWEEK, show.getAirsDayOfWeek());
values.put(SERIES_AIRSTIME, show.getAirsTime());
values.put(SERIES_FIRSTAIRED, show.getFirstAired());
values.put(SERIES_GENRES, show.getGenres());
values.put(SERIES_NETWORK, show.getNetwork());
values.put(SERIES_RATING, show.getRating());
values.put(SERIES_RUNTIME, show.getRuntime());
values.put(SERIES_STATUS, show.getStatus());
values.put(SERIES_CONTENTRATING, show.getContentRating());
values.put(SERIES_POSTER, show.getPoster());
values.put(SERIES_IMDBID, show.getImdbId());
return values;
}
/**
* Returns the episode IDs for a given show as a efficiently searchable
* HashMap.
*
* @param seriesid
* @return HashMap containing the shows existing episodes
*/
public HashSet<Long> getEpisodeIDsForShow(long seriesid) {
Cursor eptest = mDb.query(EPISODE_TABLE, new String[] { SeriesDatabase.EPISODE_ID }, SeriesDatabase.EPISODE_SERIES_ID
+ "=" + String.valueOf(seriesid), null, null, null, null);
HashSet<Long> episodeIDs = new HashSet<Long>();
eptest.moveToFirst();
while (!eptest.isAfterLast()) {
episodeIDs.add(eptest.getLong(eptest.getColumnIndexOrThrow(SeriesDatabase.EPISODE_ID)));
eptest.moveToNext();
}
eptest.close();
return episodeIDs;
}
public HashSet<Long> getAllEpisodeIDs() {
Cursor eptest = mDb.query(EPISODE_TABLE, new String[] { SeriesDatabase.EPISODE_ID }, null, null, null,
null, null);
HashSet<Long> episodeIDs = new HashSet<Long>();
eptest.moveToFirst();
while (!eptest.isAfterLast()) {
episodeIDs.add(eptest.getLong(eptest.getColumnIndexOrThrow(SeriesDatabase.EPISODE_ID)));
eptest.moveToNext();
}
eptest.close();
return episodeIDs;
}
/**
* Returns the season IDs for a given show as a efficiently searchable
* HashMap.
*
* @param seriesid
* @return HashMap containing the shows existing seasons
*/
public HashSet<Long> getSeasonIDsForShow(long seriesid) {
Cursor setest = mDb.query(SEASONS_TABLE, new String[] { SeriesDatabase.SEASON_ID }, SeriesDatabase.SEASON_SERIES_ID + "="
+ String.valueOf(seriesid), null, null, null, null);
HashSet<Long> seasonIDs = new HashSet<Long>();
setest.moveToFirst();
while (!setest.isAfterLast()) {
seasonIDs.add(setest.getLong(setest.getColumnIndexOrThrow(SeriesDatabase.SEASON_ID)));
setest.moveToNext();
}
setest.close();
return seasonIDs;
}
// /**
// * Returns all show IDs as a efficiently searchable HashMap.
// * @return HashMap containing all show ids
// */
// public HashSet<Long> getAllShowIDs() {
// Cursor shows = mDb.query(SERIES_TABLE, new String[]{ SERIES_ID }, null,
// null, null, null, null);
// HashSet<Long> showIDs = new HashSet<Long>();
// shows.moveToFirst();
// while (!shows.isAfterLast()) {
// showIDs.add(shows.getLong(shows.getColumnIndexOrThrow(SeriesDatabase.SERIES_ID)));
// shows.moveToNext();
// }
// shows.close();
// return showIDs;
// }
/**
* Adds an episode to the database. Does an insert if isNew, if false an
* update instead.
*
* @param values
* @param isNew
*/
public void addEpisode(ContentValues values, boolean isNew) {
ContentValues ftsValues = new ContentValues();
ftsValues.put(SeriesDatabase.EPISODE_TITLE, values.getAsString(SeriesDatabase.EPISODE_TITLE));
ftsValues.put(SeriesDatabase.EPISODE_OVERVIEW, values.getAsString(SeriesDatabase.EPISODE_OVERVIEW));
if (!isNew) {
mDb.update(EPISODE_TABLE, values, SeriesDatabase.EPISODE_ID + "=" + values.getAsString(SeriesDatabase.EPISODE_ID),
null);
} else {
mDb.insert(EPISODE_TABLE, null, values);
}
}
/**
* Inserts a non-existing season into the database.
*
* @param values
* of a parsed episode of this season
*/
public void addSeason(ContentValues values) {
ContentValues seasonValues = new ContentValues();
seasonValues.put(SeriesDatabase.SEASON_ID, values.getAsString(SeriesDatabase.EPISODE_SEASON_ID));
seasonValues.put(SeriesDatabase.SEASON_SERIES_ID, values.getAsString(SeriesDatabase.EPISODE_SERIES_ID));
String combseason = values.getAsString(SeriesDatabase.EPISODE_SEASON);
seasonValues.put(SeriesDatabase.SEASON_COMBINED, combseason);
mDb.insert(SEASONS_TABLE, null, seasonValues);
}
public void updateSeason(ContentValues values) {
ContentValues seasonValues = new ContentValues();
String seasonNumber = values.getAsString(SeriesDatabase.EPISODE_SEASON);
seasonValues.put(SeriesDatabase.SEASON_COMBINED, seasonNumber);
mDb.update(SEASONS_TABLE, seasonValues, SeriesDatabase.SEASON_ID + "=" + values.getAsString(SeriesDatabase.EPISODE_SEASON_ID), null);
}
/**
* Update the field with the latest episode text
* @param id
*/
public void updateLatestEpisode(long id) {
SharedPreferences prefs = PreferenceManager
.getDefaultSharedPreferences(mApplicationCtx);
boolean onlyFutureEpisodes = prefs.getBoolean("onlyFutureEpisodes", true);
Cursor unwatched;
if (onlyFutureEpisodes) {
Date date = new Date();
String today = SeriesGuideData.theTVDBDateFormat.format(date);
unwatched = mDb.query(EPISODE_TABLE, new String[] { EPISODE_ID, EPISODE_FIRSTAIRED, EPISODE_SEASON, EPISODE_NUMBER, EPISODE_TITLE },
EPISODE_SERIES_ID + "=" + id + " AND " + EPISODE_WATCHED + "=0 AND " + EPISODE_FIRSTAIRED + ">='" + today + "'",
null, null, null, EPISODE_FIRSTAIRED + " asc");
} else {
unwatched = mDb.query(EPISODE_TABLE, new String[] { EPISODE_ID, EPISODE_FIRSTAIRED, EPISODE_SEASON, EPISODE_NUMBER, EPISODE_TITLE }, EPISODE_SERIES_ID
+ "=" + id + " AND " + EPISODE_WATCHED + "=0 AND " + EPISODE_FIRSTAIRED + " like '%-%'", null, null, null, EPISODE_FIRSTAIRED + " asc");
}
// maybe there are no episodes due to errors, or airdates are just unknown ("")
ContentValues update = new ContentValues();
if (unwatched.getCount() != 0) {
unwatched.moveToFirst();
String nextEpisodeString = SeriesGuideData.getNextEpisodeString(unwatched);
nextEpisodeString += ",";
String firstAired = unwatched.getString(unwatched.getColumnIndexOrThrow(SeriesDatabase.EPISODE_FIRSTAIRED));
if (firstAired.length() != 0) {
nextEpisodeString += " " + SeriesGuideData.parseDateToLocalRelative(firstAired, mApplicationCtx);
}
update.put(SERIES_NEXTEPISODE, unwatched.getLong(unwatched.getColumnIndexOrThrow(EPISODE_ID)));
update.put(SERIES_NEXTAIRDATE, unwatched.getString(unwatched.getColumnIndexOrThrow(EPISODE_FIRSTAIRED)));
update.put(SERIES_NEXTTEXT, nextEpisodeString);
}else {
update.put(SERIES_NEXTEPISODE, "");
// Write 9999 for unkown airdates/no next episodes, sorting then assumes year 9999 and sorts these last
update.put(SERIES_NEXTAIRDATE, "9999");
update.put(SERIES_NEXTTEXT, "");
}
mDb.update(SERIES_TABLE, update, SeriesDatabase.SERIES_ID + "=" + id, null);
unwatched.close();
}
public void beginTransaction(){
mDb.beginTransaction();
}
public void setTransactionSuccessful(){
mDb.setTransactionSuccessful();
}
public void endTransaction(){
mDb.endTransaction();
}
public Cursor search(String query) {
/* select _id,episodetitle,episodedescription,number,season,watched,seriestitle
* from ((select _id as sid,seriestitle from series) join
* (select _id,episodedescription,series_id,episodetitle,number,season,watched
* from (select rowid,snippet(searchtable) as episodedescription
* from searchtable where searchtable match 'pilot') join (select _id,series_id,episodetitle,number,season,watched from episodes) on _id=rowid)
* on sid=series_id)
*/
return mDb.rawQuery(
"select _id,"
+ EPISODE_TITLE + ","
+ EPISODE_OVERVIEW + ","
+ EPISODE_NUMBER + ","
+ EPISODE_SEASON + ","
+ EPISODE_WATCHED + ","
+ SERIES_NAME
+ " from ((select _id as sid," + SERIES_NAME + " from " + SERIES_TABLE + ")"
+ " join "
+ "(select _id,"
+ EPISODE_TITLE + ","
+ EPISODE_OVERVIEW + ","
+ EPISODE_NUMBER + ","
+ EPISODE_SEASON + ","
+ EPISODE_WATCHED + ","
+ EPISODE_SERIES_ID
+ " from "
+ "(select docid,snippet(" + FTS_SEARCH_TABLE + ",'','','...') as " + EPISODE_OVERVIEW
+ " from " + FTS_SEARCH_TABLE + " where " + FTS_SEARCH_TABLE + " match " + "?)"
+ " join "
+ "(select _id,"
+ EPISODE_TITLE + ","
+ EPISODE_NUMBER + ","
+ EPISODE_SEASON+ ","
+ EPISODE_WATCHED + ","
+ EPISODE_SERIES_ID
+ " from episodes)"
+ "on _id=docid)"
+"on sid=" + EPISODE_SERIES_ID + ")", new String[] { query } );
}
public void onRenewFTSTable() {
mDb.beginTransaction();
try {
mDb.execSQL("drop table if exists " + FTS_SEARCH_TABLE);
mDbHelper.upgradeToTwelve(mDb);
mDb.setTransactionSuccessful();
}
finally {
mDb.endTransaction();
}
}
}
|