package unify.fileio;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Logger;
import unify.ShowLibrary;
import unify.data.Episode;
import unify.data.Feed;
import unify.data.Search;
import unify.data.Season;
import unify.data.Settings;
import unify.data.Show;
import com.j256.ormlite.dao.Dao;
import com.j256.ormlite.dao.DaoManager;
import com.j256.ormlite.jdbc.JdbcConnectionSource;
import com.j256.ormlite.logger.LocalLog;
import com.j256.ormlite.stmt.PreparedQuery;
import com.j256.ormlite.stmt.QueryBuilder;
import com.j256.ormlite.support.ConnectionSource;
import com.j256.ormlite.table.TableUtils;
public class Database {
public static String dbFileName = "UnifyDatabase";
private final static Logger LOGGER = Logger.getLogger(Database.class.getName());
public static void initializeDB() {
LOGGER.info("Initialized database");
System.setProperty(LocalLog.LOCAL_LOG_LEVEL_PROPERTY, "error");
try {
ConnectionSource connectionSource = new JdbcConnectionSource("jdbc:h2:file:" + dbFileName);
TableUtils.createTableIfNotExists(connectionSource, Show.class);
TableUtils.createTableIfNotExists(connectionSource, Season.class);
TableUtils.createTableIfNotExists(connectionSource, Episode.class);
TableUtils.createTableIfNotExists(connectionSource, Feed.class);
TableUtils.createTableIfNotExists(connectionSource, Settings.class);
TableUtils.createTableIfNotExists(connectionSource, Search.class);
connectionSource.close();
} catch (SQLException e) {
LOGGER.severe("SQLException: " + e.toString());
System.exit(1);
}
}
public static void saveData() {
try {
ConnectionSource connectionSource = new JdbcConnectionSource("jdbc:h2:file:" + dbFileName);
Dao<Show, Integer> showDao = DaoManager.createDao(connectionSource, Show.class);
Dao<Season, Integer> seasonDao = DaoManager.createDao(connectionSource, Season.class);
Dao<Episode, Integer> episodeDao = DaoManager.createDao(connectionSource, Episode.class);
int numShows = 0;
int numSeasons = 0;
int numEpisodes = 0;
ArrayList<Show> shows = ShowLibrary.getInstance().shows;
for(Show show : shows) {
showDao.createOrUpdate(show);
numShows++;
for(Season season : show.seasons) {
seasonDao.createOrUpdate(season);
numSeasons++;
for(Episode episode : season.getEpisodes()) {
episodeDao.createOrUpdate(episode);
numEpisodes++;
}
}
}
for(Show dbShow : showDao.queryForAll()) {
Show myShow = ShowLibrary.getInstance().getShow(dbShow.getTitle());
if(myShow==null) {
for(Season season : dbShow.seasons) {
episodeDao.delete(season.getEpisodes());
}
seasonDao.delete(dbShow.seasons);
showDao.delete(dbShow);
}
}
Dao<Settings, Integer> settingsDao = DaoManager.createDao(connectionSource, Settings.class);
Dao<Feed, Integer> feedDao = DaoManager.createDao(connectionSource, Feed.class);
Dao<Search, Integer> searchDao = DaoManager.createDao(connectionSource, Search.class);
Settings settings = Settings.getInstance();
for(Feed feed : settings.getFeeds()) {
feedDao.createOrUpdate(feed);
}
for(Search search : settings.getSearches()) {
searchDao.createOrUpdate(search);
}
settingsDao.createOrUpdate(settings);
LOGGER.fine("Stored " + numShows + " shows, " + numSeasons + " seasons, and " + numEpisodes + " episodes in database.");
connectionSource.close();
} catch (SQLException e) {
LOGGER.severe("SQLException: " + e.toString());
System.exit(1);
}
}
public static void loadShows() {
try {
ConnectionSource connectionSource = new JdbcConnectionSource("jdbc:h2:file:" + dbFileName);
Dao<Show, Integer> showDao = DaoManager.createDao(connectionSource, Show.class);
QueryBuilder<Show, Integer> queryBuilder = showDao.queryBuilder().orderBy("title", true);
PreparedQuery<Show> preparedQuery = queryBuilder.prepare();
ShowLibrary.getInstance().shows.clear();
ShowLibrary.getInstance().shows.addAll(showDao.query(preparedQuery));
connectionSource.close();
} catch (SQLException e) {
LOGGER.severe("SQLException: " + e.toString());
System.exit(1);
}
int numShows = 0;
int numSeasons = 0;
int numEpisodes = 0;
for(int i=0;i<ShowLibrary.getInstance().shows.size();i++) {
numShows++;
for(Season season : ShowLibrary.getInstance().shows.get(i).seasons) {
numSeasons++;
numEpisodes = numEpisodes + season.getEpisodes().size();
}
}
LOGGER.fine("Loaded " + numShows + " shows, " + numSeasons + " seasons, and " + numEpisodes + " episodes from database to memory.");
}
public static Settings getSettings() {
Settings settings = null;
try {
ConnectionSource connectionSource = new JdbcConnectionSource("jdbc:h2:file:" + dbFileName);
Dao<Settings, Integer> settingsDao = DaoManager.createDao(connectionSource, Settings.class);
QueryBuilder<Settings, Integer> queryBuilder = settingsDao.queryBuilder();
queryBuilder.where().isNotNull("databaseId");
PreparedQuery<Settings> preparedQuery = queryBuilder.prepare();
settings = settingsDao.queryForFirst(preparedQuery);
settingsDao.update(settings);
connectionSource.close();
} catch (SQLException e) {
LOGGER.severe("SQLException: " + e.toString());
System.exit(1);
}
LOGGER.fine("Loaded settings from database");
return settings;
}
public static void clear() {
try {
ConnectionSource connectionSource = new JdbcConnectionSource("jdbc:h2:file:" + dbFileName);
TableUtils.dropTable(connectionSource, Show.class, true);
TableUtils.dropTable(connectionSource, Season.class, true);
TableUtils.dropTable(connectionSource, Episode.class, true);
TableUtils.dropTable(connectionSource, Settings.class, true);
TableUtils.dropTable(connectionSource, Feed.class, true);
TableUtils.dropTable(connectionSource, Search.class, true);
} catch (SQLException e) {
LOGGER.severe("SQLException: " + e.toString());
System.exit(1);
}
}
}