/**
* @brief Creates all the tables
*
* @return bool
*/
bool DatabaseManager::createTables()
{
// Create table "movies"
bool l_ret = false;
if (m_db.isOpen())
{
QSqlQuery l_query(m_db);
l_ret = l_query.exec("CREATE TABLE IF NOT EXISTS movies("
"id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE, "
"title VARCHAR(50), "
"original_title VARCHAR(50), "
"director VARCHAR(30), "
"producer VARCHAR(30), "
"year INTEGER, "
"country VARCHAR(30), "
"duration INTEGER, "
"synopsis TEXT, "
"file_path VARCHAR(255), "
"colored BOOLEAN, "
"format VARCHAR(10) "
")");
l_ret = l_ret && l_query.exec("CREATE TABLE IF NOT EXISTS config("
"id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE, "
"movies_path VARCHAR(255) )");
//l_ret = l_ret && l_query.exec("INSERT INTO config (movies_path) VALUES ('path')");
}
return l_ret;
}
/**
* @brief Opens (or create the file of) the database
*
* @return bool db.open()
*/
bool DatabaseManager::openDB()
{
Macaw::DEBUG("[DatabaseManager] openDB");
if (QSqlDatabase::contains("Movies-database"))
{
m_db = QSqlDatabase::database("Movies-database");
}
else
{
m_db = QSqlDatabase::addDatabase("QSQLITE", "Movies-database");
}
QString l_dbPath = qApp->property("filesPath").toString() + "database.sqlite";
m_db.setDatabaseName(l_dbPath);
if (!m_db.open()) {
return false;
}
QSqlQuery l_query(m_db);
l_query.prepare("PRAGMA foreign_keys = ON");
if (!l_query.exec()) {
Macaw::DEBUG("In openDB:");
Macaw::DEBUG(l_query.lastError().text());
return false;
}
return true;
}
/**
* @brief Adds a path to the media player
*
* @param QString mediaPlayerPath: containing the path to the media player
* @return true if the path has been updated correctly
*/
bool DatabaseManager::addMediaPlayerPath(QString mediaPlayerPath)
{
QSqlQuery l_query(m_db);
l_query.prepare("DELETE FROM media_player");
if(!l_query.exec()) {
Macaw::DEBUG("[DatabaseManager] In addMediaPlayerPath():");
Macaw::DEBUG(l_query.lastError().text());
return false;
}
if (!mediaPlayerPath.trimmed().isEmpty()) {
l_query.prepare("INSERT INTO media_player (media_player_path) VALUES (:media_player_path)");
l_query.bindValue(":media_player_path", mediaPlayerPath);
if(!l_query.exec()) {
Macaw::DEBUG("[DatabaseManager] In addMediaPlayerPath():");
Macaw::DEBUG(l_query.lastError().text());
return false;
}
}
return true;
}
bool DatabaseManager::deleteMoviesPath(PathForMovies moviesPath)
{
QList<Movie> l_movieList = getMoviesByPath(moviesPath);
foreach (Movie l_movie, l_movieList) {
if (!deleteMovie(l_movie))
{
return false;
}
}
QSqlQuery l_query(m_db);
l_query.prepare("DELETE FROM path_list WHERE movies_path LIKE :movies_path||'%'");
l_query.bindValue(":movies_path", moviesPath.path());
if(!l_query.exec())
{
Macaw::DEBUG("In removeMoviesPath(), deleting path:");
Macaw::DEBUG(l_query.lastError().text());
return false;
}
return true;
}
/**
* @brief Get the movies directories
* @param imported boolean telling if the movies of this path have already been imported
* @return QStringList containing the paths of these directories
*/
QList<PathForMovies> DatabaseManager::getMoviesPaths(bool imported)
{
QList<PathForMovies> l_moviesPathList;
QSqlQuery l_query(m_db);
l_query.prepare("SELECT id, movies_path, type FROM path_list where imported=:imported");
l_query.bindValue(":imported", imported);
if(!l_query.exec())
{
Macaw::DEBUG("In getMoviesPaths():");
Macaw::DEBUG(l_query.lastError().text());
}
while(l_query.next())
{
PathForMovies l_moviesPath;
l_moviesPath.setId(l_query.value(0).toInt());
l_moviesPath.setPath(l_query.value(1).toString());
l_moviesPath.setType(l_query.value(2).toInt());
l_moviesPathList.append(l_moviesPath);
}
return l_moviesPathList;
}
QSqlQuery DatabaseManager::getAllTitles()
{
QSqlQuery l_query(m_db);
l_query.prepare("SELECT title || ifnull(' (' || year || ')','') as value FROM movies");
l_query.exec();
return l_query;
}
/**
* @brief Gets all the movies
*
* @return QSqlQuery
*/
QSqlQuery DatabaseManager::getAllMovies()
{
QSqlQuery l_query(m_db);
l_query.prepare("SELECT title, director, year, format, file_path FROM movies");
l_query.exec();
return l_query;
}
/**
* @brief Gets the movies with a defined parameter.
*
* @param QString parameter_name
* @param QVariant parameter_value
* @return QSqlQuery
*/
QSqlQuery DatabaseManager::getMovies(QString parameter_name, QVariant parameter_value)
{
QSqlQuery l_query(m_db);
l_query.prepare("SELECT * FROM movies WHERE " + parameter_name + "=:parameter_value");
l_query.bindValue(":parameter_value", parameter_value);
l_query.exec();
return l_query;
}
/**
* @brief Adds a movies directory
*
* @param PathForMovies moviesPath: containing the path to the movies directory
* @return true if the paths list have been updated correctly
*/
bool DatabaseManager::addMoviesPath(PathForMovies moviesPath)
{
QSqlQuery l_query(m_db);
l_query.prepare("INSERT INTO path_list (movies_path, type) VALUES (:movies_path, :type)");
l_query.bindValue(":movies_path", moviesPath.path());
l_query.bindValue(":type", moviesPath.type());
if(!l_query.exec())
{
Macaw::DEBUG("[DatabaseManager] In addMoviesPath():");
Macaw::DEBUG(l_query.lastError().text());
return false;
}
return true;
}
/**
* @brief Set the imported boolean of the movie path
*
* @param QString moviesPath: containing the path to the movies directory
* @param bool imported: true if the movies have been imported, false else
* @return true if the request succeed
*/
bool DatabaseManager::setMoviesPathImported(QString moviesPath, bool imported)
{
QSqlQuery l_query(m_db);
l_query.prepare("UPDATE path_list SET imported=:imported WHERE movies_path = :movies_path");
l_query.bindValue(":movies_path", moviesPath);
l_query.bindValue(":imported", imported);
if(!l_query.exec())
{
Macaw::DEBUG("In setMoviesPathImported():");
Macaw::DEBUG(l_query.lastError().text());
return false;
}
return true;
}
/**
* @brief Saves the movies directory
*
* @param QString moviePath: containing the path to the movies directory
*
* @return true if the paths list have been updated correctly
*/
bool DatabaseManager::saveMoviesPath(QString moviePath)
{
//TODO : is this test really usefull ? this value "sould" be validated before...
if(moviePath.isEmpty())
{
return false;
}
else if (!QDir(moviePath).exists())
{
return false;
}
QSqlQuery l_query(m_db);
l_query.prepare("SELECT movies_path FROM paths_list");
if(!l_query.exec())
{
debug("In saveMoviesPath(), getting existing path:");
debug(l_query.lastError().text());
}
while(l_query.next())
{
if(l_query.value(0).toString() == moviePath)
{
return false;
}
}
l_query.prepare("INSERT INTO paths_list (movies_path) VALUES (:path)");
l_query.bindValue(":path", moviePath);
//updates the movies path list in the settings window
m_moviesPathModel->insertRow(m_moviesPathModel->rowCount());
m_moviesPathModel->setData(m_moviesPathModel->index(m_moviesPathModel->rowCount()-1), moviePath);
if(!l_query.exec())
{
debug("In saveMoviesPath():");
debug(l_query.lastError().text());
return false;
}
return true;
}
/**
* @brief Adds a person to the database.
* Should not be called directly.
*
* @param People
* @return bool
*/
bool DatabaseManager::insertNewPeople(People &people)
{
QSqlQuery l_query(m_db);
// If a people with the same name exist, we update it
// else we insert
if(existPeople(people.name())) {
Macaw::DEBUG("[DatabaseManager.insertNewPeople] Name already known");
People l_peopleToUpdate = getOnePeopleByName(people.name());
people.setId(l_peopleToUpdate.id());
if(!updatePeople(people)) {
return false;
}
} else {
l_query.prepare("INSERT INTO people ("
"name, "
"birthday, "
"biography, "
"imported, "
"id_tmdb "
") VALUES ("
":name, "
":birthday, "
":biography, "
":imported, "
":id_tmdb "
")"
);
l_query.bindValue(":name", people.name());
l_query.bindValue(":birthday", people.birthday().toString(DATE_FORMAT));
l_query.bindValue(":biography", people.biography());
l_query.bindValue(":imported", people.isImported());
l_query.bindValue(":id_tmdb", people.tmdbId());
if (!l_query.exec()) {
Macaw::DEBUG("In insertNewPeople():");
Macaw::DEBUG(l_query.lastError().text());
return false;
}
people.setId(l_query.lastInsertId().toInt());
}
return true;
}
/**
* @brief Adds a tag to the database.
* Should not be called directly.
*
* @param Tag
* @return bool
*/
bool DatabaseManager::insertNewTag(Tag &tag)
{
QSqlQuery l_query(m_db);
l_query.prepare("INSERT INTO tag (name) "
"VALUES (:name)");
l_query.bindValue(":name", tag.name());
if (!l_query.exec())
{
Macaw::DEBUG("In insertNewTag():");
Macaw::DEBUG(l_query.lastError().text());
return false;
}
tag.setId(l_query.lastInsertId().toInt());
return true;
}
/**
* @brief Get the movies directory
*
* @return QStringList containing the paths of these directories
*/
QStringList DatabaseManager::getMoviesPath()
{
QSqlQuery l_query(m_db);
l_query.prepare("SELECT movies_path FROM config");
l_query.exec();
QStringList l_result;
while(l_query.next())
{
l_result.append(l_query.value(0).toString());
}
m_moviesPathModel->setStringList(l_result);
return l_result;
}
/**
* @brief Creates all the tables
*
* @return bool
*/
bool DatabaseManager::createTables()
{
Macaw::DEBUG("[DatabaseManager] createTables");
bool l_ret = false;
if (m_db.isOpen())
{
QSqlQuery l_query(m_db);
l_ret &= createTablePeople(l_query);
if(m_db.tables().contains("config"))
{
Macaw::DEBUG("[DatabaseManager.createTable] config table exists");
l_query.exec("SELECT db_version FROM config");
l_query.next();
if(l_query.value(0).toInt() != DB_VERSION)
{
l_ret = upgradeDB(l_query.value(0).toInt(), DB_VERSION);
}
}
else //if config table do not exists then the db is empty...
{
Macaw::DEBUG("[DatabaseManager.createTable] configTable does not exist");
l_ret = l_query.exec("PRAGMA foreign_keys = ON");
l_ret &= createTableMovies(l_query);
l_ret &= createTablePeople(l_query);
l_ret &= createTableMoviesPeople(l_query);
l_ret &= createTablePlaylists(l_query);
l_ret &= createTableMoviesPlaylists(l_query);
l_ret &= createTableTags(l_query);
l_ret &= createTableMoviesTags(l_query);
l_ret &= createTableShow(l_query);
l_ret &= createTableEpisodes(l_query);
l_ret &= createTablePathList(l_query);
if (l_ret) {
l_ret &= createTableConfig(l_query);
}
}
}
return l_ret;
}
/**
* @brief Saves the movies directory
*
* @param QString moviePath: containing the path to the movies directory
*
* @return true if the config hav been updated correctly
*/
bool DatabaseManager::saveMoviesPath(QString moviePath)
{
//TODO : is this test really usefull ? this value "sould" be validated before...
if(moviePath.isEmpty())
{
return false;
}
else if (!QDir(moviePath).exists())
{
return false;
}
QSqlQuery l_query(m_db);
l_query.prepare("INSERT INTO config (movies_path) VALUES (:path)");
l_query.bindValue(":path", moviePath);
return l_query.exec();
}
bool DatabaseManager::createTag(QString name)
{
QSqlQuery l_query(m_db);
l_query.prepare("INSERT INTO `tags` (name) VALUES (:name)");
l_query.bindValue(":name", name);
if (!l_query.exec())
{
debug("In createTag():");
debug(l_query.lastError().text());
return false;
}
m_tagListModel->insertRow(m_tagListModel->rowCount());
m_tagListModel->setData(m_tagListModel->index(m_tagListModel->rowCount()-1), name);
return true;
}
/**
* @brief Adds a playlist to the database.
*
* @param Playlist
* @return bool
*/
bool DatabaseManager::insertNewPlaylist(Playlist &playlist)
{
QSqlQuery l_query(m_db);
l_query.prepare("INSERT INTO playlists (name, creation_date, rate) "
"VALUES (:name, :creation_date, :rate)");
l_query.bindValue(":name", playlist.name());
l_query.bindValue(":creation_date", playlist.creationDate().toTime_t());
l_query.bindValue(":rate", playlist.rate());
if (!l_query.exec())
{
Macaw::DEBUG("In insertNewPlaylist():");
Macaw::DEBUG(l_query.lastError().text());
return false;
}
playlist.setId(l_query.lastInsertId().toInt());
return true;
}
/**
* @brief Get the movies directory having the id `id`
* @param id
* @return QString containing the path of this directory
*/
QString DatabaseManager::getMoviesPathById(int id)
{
QString l_moviesPath;
QSqlQuery l_query(m_db);
l_query.prepare("SELECT movies_path FROM path_list WHERE id=:id");
l_query.bindValue(":id", id);
if(!l_query.exec())
{
Macaw::DEBUG("In getMoviesPathById():");
Macaw::DEBUG(l_query.lastError().text());
}
if(l_query.next())
{
l_moviesPath = l_query.value(0).toString();
}
return l_moviesPath;
}
/**
* @brief Update a movies directory
*
* @param PathForMovies moviesPath: containing the path to the movies directory
* @return true if the paths list have been updated correctly
*/
bool DatabaseManager::updateMoviesPath(PathForMovies moviesPath)
{
QSqlQuery l_query(m_db);
l_query.prepare("UPDATE path_list "
"SET movies_path=:movies_path, type=:type "
"WHERE id=:id");
l_query.bindValue(":movies_path", moviesPath.path());
l_query.bindValue(":type", moviesPath.type());
l_query.bindValue(":id", moviesPath.id());
if(!l_query.exec())
{
Macaw::DEBUG("[DatabaseManager] In updateMoviesPath():");
Macaw::DEBUG(l_query.lastError().text());
return false;
}
return true;
}
/**
* @brief Get path to media player
* @return QStringList containing the path to the media player
*/
QString DatabaseManager::getMediaPlayerPath()
{
QSqlQuery l_query(m_db);
l_query.prepare("SELECT media_player FROM config");
if(!l_query.exec())
{
Macaw::DEBUG("In getMediaPlayerPath():");
Macaw::DEBUG(l_query.lastError().text());
}
QString l_mediaPlayerPath("");
while(l_query.next())
{
l_mediaPlayerPath.append(l_query.value(0).toString());
}
return l_mediaPlayerPath;
}
bool DatabaseManager::insertNewTitle(QStringList value)
{
if (value.size() % 2 == 1)
{
return false;
}
QString l_request = "INSERT into movies (";
// Even elements are names of fields
for (int i = 0 ; i < value.size()-1 ; i=i+2)
{
l_request += value.at(i);
if (i != value.size()-2)
{
l_request += ", ";
}
}
l_request += ") VALUES (";
// Odd elements are values of fields
for (int i = 1 ; i < value.size() ; i=i+2)
{
l_request += "'" + value.at(i) + "'";
if (i != value.size()-1)
{
l_request += ", ";
}
}
l_request += ")";
QSqlQuery l_query(m_db);
l_query.prepare(l_request);
l_query.exec();
qDebug()<< "******\n" + l_request;
return true;
}
/**
* @brief Get the tags in the database
*
* @return QStringList containing all the tags
*/
QStringList DatabaseManager::getTags()
{
QSqlQuery l_query(m_db);
l_query.prepare("SELECT name FROM tags");
if(!l_query.exec())
{
debug("In getTags():");
debug(l_query.lastError().text());
}
QStringList l_result;
while(l_query.next())
{
l_result.append(l_query.value(0).toString());
}
m_tagListModel->setStringList(l_result);
return l_result;
}
/**
* @brief Get the movies directories
*
* @return QStringList containing the paths of these directories
*/
QStringList DatabaseManager::getMoviesPath()
{
QSqlQuery l_query(m_db);
l_query.prepare("SELECT movies_path FROM paths_list");
if(!l_query.exec())
{
debug("In getMoviesPath():");
debug(l_query.lastError().text());
}
QStringList l_result;
while(l_query.next())
{
l_result.append(l_query.value(0).toString());
}
m_moviesPathModel->setStringList(l_result);
return l_result;
}
/**
* @brief add a new tag with specified name to the database.
* Returns the id of created tag, -1 if an error occurred.
* @param name new tag's name
* @returns the id of newly created tag in the db, -1 if an error occurs
*/
int DatabaseManager::createTag(QString name)
{
QSqlQuery l_query(m_db);
l_query.prepare("INSERT INTO `tags` (name) VALUES (:name)");
l_query.bindValue(":name", name);
if (!l_query.exec())
{
Macaw::DEBUG("In createTag():");
Macaw::DEBUG(l_query.lastError().text());
return -1;
}
if(l_query.exec("SELECT last_insert_rowid()"))
{
l_query.next();
return l_query.value(0).toInt();
}
return -1;
}
/**
* @brief Adds a person to the database and links it to a movie
*
* @param People
* @param Movie
* @return bool
*/
bool DatabaseManager::addPeopleToMovie(People &people, Movie &movie, const int type)
{
if (!insertNewPeople(people))
{
return false;
}
QSqlQuery l_query(m_db);
l_query.prepare("INSERT INTO movies_people (id_people, id_movie, type) "
"VALUES (:id_people, :id_movie, :type)");
l_query.bindValue(":id_people", people.id());
l_query.bindValue(":id_movie", movie.id());
l_query.bindValue(":type", type);
if (!l_query.exec())
{
Macaw::DEBUG("In addPeopleToMovie():");
Macaw::DEBUG(l_query.lastError().text());
return false;
}
return true;
}
/**
* @brief Adds a tag to the database and links it to a movie
*
* @param Tag
* @param Movie
* @return bool
*/
bool DatabaseManager::addTagToMovie(Tag &tag, Movie &movie)
{
if (!insertNewTag(tag))
{
return false;
}
QSqlQuery l_query(m_db);
l_query.prepare("INSERT INTO movies_tags (id_tag, id_movie) "
"VALUES (:id_tag, :id_movie)");
l_query.bindValue(":id_tag", tag.id());
l_query.bindValue(":id_movie", movie.id());
if (!l_query.exec())
{
Macaw::DEBUG("In addTagToMovie():");
Macaw::DEBUG(l_query.lastError().text());
return false;
}
movie = getOneMovieById(movie.id());
return true;
}
/**
* @brief Adds a movie to the database
*
* @param Movie
* @return bool
*/
bool DatabaseManager::insertNewMovie(Movie &movie, int moviesPathId)
{
QSqlQuery l_query(m_db);
l_query.prepare("INSERT INTO movies ("
"title, "
"original_title, "
"release_date, "
"country, "
"duration, "
"synopsis, "
"id_path, "
"file_path, "
"poster_path, "
"colored, "
"format, "
"suffix, "
"rank, "
"imported, "
"id_tmdb, "
"show"
") VALUES ("
":title, "
":original_title, "
":release_date, "
":country, "
":duration, "
":synopsis, "
":id_path, "
":file_path, "
":poster_path, "
":colored, "
":format, "
":suffix, "
":rank, "
":imported, "
":id_tmdb, "
":show"
")");
l_query.bindValue(":title", movie.title());
l_query.bindValue(":original_title", movie.originalTitle() );
l_query.bindValue(":release_date", movie.releaseDate().toString(DATE_FORMAT));
l_query.bindValue(":country", movie.country());
l_query.bindValue(":duration", movie.duration().msecsSinceStartOfDay());
l_query.bindValue(":synopsis", movie.synopsis());
l_query.bindValue(":id_path", moviesPathId);
l_query.bindValue(":file_path", movie.fileRelativePath());
l_query.bindValue(":poster_path", movie.posterPath());
l_query.bindValue(":colored", movie.isColored());
l_query.bindValue(":format", movie.format());
l_query.bindValue(":suffix", movie.suffix());
l_query.bindValue(":rank", movie.rank());
l_query.bindValue(":imported", movie.isImported());
l_query.bindValue(":id_tmdb", movie.tmdbId());
l_query.bindValue(":show", movie.isShow());
if (!l_query.exec())
{
Macaw::DEBUG("In insertNewMovie():");
Macaw::DEBUG(l_query.lastError().text());
return false;
}
Macaw::DEBUG("[DatabaseManager] Movie added");
movie.setId(l_query.lastInsertId().toInt());
for(int i = 0 ; i < movie.peopleList().size() ; i++)
{
People l_people = movie.peopleList().at(i);
if (!addPeopleToMovie(l_people, movie, l_people.type()))
{
return false;
}
}
return true;
}
/**
* @brief Upgrades DB between diffent DB versions
* This function needs to be updated each time a change to the DB structure is made
*
* @return bool
*/
bool DatabaseManager::upgradeDB(int fromVersion, int toVersion)
{
Macaw::DEBUG_IN("[DatabaseManager] upgradeDB");
bool l_ret = false;
int l_fromVersion(fromVersion);
if (m_db.isOpen())
{
// We need to have a clean version of the db instance.
//There might be a better way to do this.
m_db.close();
Macaw::DEBUG_IN("[DatabaseManager] backup database");
QFile::copy(m_db.databaseName(),
m_db.databaseName()+"_backup"
+ QDateTime::currentDateTime().toString("yyyyMMdd_HHmmss"));
Macaw::DEBUG_OUT("[DatabaseManager] database backup done");
m_db.open();
QSqlQuery l_query(m_db);
// PRAGMA is disabled so that renaming and deleting a database don't act on cascade
l_ret = l_query.exec("PRAGMA foreign_keys = OFF");
//switch to DB_VERSION 050
if (toVersion >= 50) {
Macaw::DEBUG_IN("[DatabaseManager] upgrade to v050");
l_query.finish();
l_query.clear();
if (!m_db.record("config").contains("media_player")) {
l_ret &= l_query.exec("ALTER TABLE config ADD media_player VARCHAR(255)");
if(!l_ret)
{
Macaw::DEBUG(l_query.lastError().text());
}
}
if (!m_db.record("movies").contains("id_tmdb")) {
l_ret &= l_query.exec("ALTER TABLE movies ADD id_tmdb INTEGER");
l_ret &= l_query.exec("UPDATE movies SET id_tmdb = 0");
if(!l_ret)
{
Macaw::DEBUG(l_query.lastError().text());
}
}
if (!m_db.record("movies").contains("show")) {
l_ret &= l_query.exec("ALTER TABLE movies ADD show BOOLEAN");
l_ret &= l_query.exec("UPDATE movies SET show = 0");
if(!l_ret)
{
Macaw::DEBUG(l_query.lastError().text());
}
}
if (!m_db.tables().contains("path_list")) {
Macaw::DEBUG_IN("[DatabaseManager] upgrade path_list table");
l_ret &= createTablePathList(l_query);
l_ret &= l_query.exec("INSERT INTO path_list(id, movies_path, imported) SELECT id, movies_path, imported FROM paths_list");
l_ret &= l_query.exec("DROP TABLE paths_list");
l_ret &= l_query.exec("UPDATE path_list SET type = 1");
if(!l_ret)
{
Macaw::DEBUG(l_query.lastError().text());
}
Macaw::DEBUG_OUT("[DatabaseManager] upgrade path_list table finished");
}
if (!m_db.record("people").contains("id_tmdb")) {
Macaw::DEBUG_IN("[DatabaseManager] upgrade people table");
l_ret &= l_query.exec("ALTER TABLE people ADD id_tmdb INTEGER");
l_ret &= l_query.exec("ALTER TABLE people ADD imported BOOLEAN");
l_ret &= l_query.exec("UPDATE people SET imported = 0, id_tmdb = 0");
if(!l_ret){
Macaw::DEBUG(l_query.lastError().text());
}
}
if (!m_db.record("movies").contains("id_path")) {
Macaw::DEBUG_IN("[DatabaseManager] upgrade movies table");
l_ret &= l_query.exec("ALTER TABLE movies ADD id_path INTEGER");
if(!l_ret){
Macaw::DEBUG(l_query.lastError().text());
}
l_ret &= l_query.exec("ALTER TABLE movies RENAME TO movies_old");
if(!l_ret){
Macaw::DEBUG(l_query.lastError().text());
}
l_ret &= createTableMovies(l_query);
if(!l_ret){
Macaw::DEBUG(l_query.lastError().text());
}
l_ret &= l_query.exec("UPDATE movies_old SET id_path=1");
if(!l_ret){
Macaw::DEBUG(l_query.lastError().text());
}
l_ret &= l_query.exec("INSERT INTO movies SELECT "+ m_movieFields +"FROM movies_old AS m");
if(!l_ret){
Macaw::DEBUG("Copying table movies failed");
Macaw::DEBUG(l_query.lastError().text());
}
l_ret &= l_query.exec("DROP TABLE movies_old");
if(!l_ret){
Macaw::DEBUG(l_query.lastError().text());
}
l_query.exec("SELECT id, movies_path FROM path_list");
QStringList l_pathList;
QList<int> l_idList;
while (l_query.next())
{
l_idList.append(l_query.value(0).toInt());
l_pathList.append(l_query.value(1).toString());
}
l_query.exec("SELECT file_path FROM movies");
while (l_query.next())
{
for (int i = 0 ; i < l_pathList.count() ; i++) {
if (l_query.value(0).toString().startsWith(l_pathList.at(i))) {
QString l_moviePath = l_query.value(0).toString();
QString l_moviePath_new = l_moviePath;
l_moviePath_new.remove(0, l_pathList.at(i).count()+1);
QSqlQuery l_query2(m_db);
l_ret &= l_query2.exec("UPDATE movies "
"SET file_path='"+l_moviePath_new+"', "+
"id_path="+QString::number(l_idList.at(i))+' '+
"WHERE file_path='"+l_moviePath+'\'');
if(!l_ret){
Macaw::DEBUG(l_query2.lastError().text());
}
}
}
}
Macaw::DEBUG_OUT("[DatabaseManager] upgrade movies table finished");
}
l_ret &= createTableShow(l_query);
l_ret &= createTableEpisodes(l_query);
if(l_ret) {
l_ret &= l_query.exec("UPDATE config "
"SET db_version = 050");
l_fromVersion = 050;
} else {
m_db.close();
Macaw::DEBUG_IN("[DatabaseManager] FAILED => Come back to backup");
QDir l_backups = m_db.databaseName();
l_backups.cdUp();
QStringList l_backupNameList = l_backups.entryList(QDir::Files|QDir::NoDotAndDotDot,
QDir::Name);
Macaw::DEBUG("Return to "+l_backupNameList.last());
this->deleteDB();
QFile::copy(l_backups.absolutePath()+QDir::separator()+l_backupNameList.last(),
m_db.databaseName());
Macaw::DEBUG_OUT("[DatabaseManager] Returned to backup");
this->openDB();
}
Macaw::DEBUG_OUT("[DatabaseManager] exits upgrade to v050");
}
}
Macaw::DEBUG_OUT("[DatabaseManager] exits upgradeDB");
return l_ret;
}
/**
* @brief Creates all the tables
*
* @return bool
*/
bool DatabaseManager::createTables()
{
debug("[DatabaseManager] createTables");
bool l_ret = false;
if (m_db.isOpen())
{
QSqlQuery l_query(m_db);
if(m_db.tables().contains("config"))
{
debug("[DatabaseManager.createTable] config table exists");
l_query.exec("SELECT db_version FROM config");
l_query.next();
if(l_query.value(0) != DB_VERSION) //TODO : make en intelligent upgrade of the database
{
QMessageBox msgBox;
msgBox.setText("Error your database version is "+ l_query.value(0).toString() +" which is too old.\n"+
"This program is not currently able to update the database and will close.\n"+
"Please delete your old database. ");
msgBox.setIcon(QMessageBox::Critical);
msgBox.exec();
exit(1);
}
}
else //if config table do not exists then the db is empty...
{
debug("[DatabaseManager.createTable] configTable does not exist");
// Movies
l_ret = l_query.exec("CREATE TABLE IF NOT EXISTS movies("
"id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE, "
"title VARCHAR(255) NOT NULL, "
"original_title VARCHAR(255), "
"release_date VARCHAR(10), "
"country VARCHAR(50), "
"duration INTEGER, "
"synopsis TEXT, "
"file_path VARCHAR(255) UNIQUE NOT NULL, "
"colored BOOLEAN, "
"format VARCHAR(10), "
"suffix VARCHAR(10), "
"rank INTEGER"
")");
// Peoples (directors, actor, music...)
l_ret = l_ret && l_query.exec("CREATE TABLE IF NOT EXISTS people("
"id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE, "
"lastname VARCHAR(100) NOT NULL, "
"firstname VARCHAR(100), "
"realname VARCHAR(255), "
"birthday VARCHAR(10), "
"biography TEXT"
")");
// Links between people and movies (a type of person is given here)
l_ret = l_ret && l_query.exec("CREATE TABLE IF NOT EXISTS movies_people("
"id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE, "
"id_movie INTEGER NOT NULL, "
"id_people INTEGER NOT NULL, "
"type INTEGER NOT NULL, "
"UNIQUE (id_people, id_movie, type) ON CONFLICT IGNORE "
")");
// Tags that can be attributed to the movies
l_ret = l_ret && l_query.exec("CREATE TABLE IF NOT EXISTS tags("
"id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE, "
"name VARCHAR(255) UNIQUE NOT NULL"
")");
// Links between tags and movies
l_ret = l_ret && l_query.exec("CREATE TABLE IF NOT EXISTS movies_tags("
"id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE, "
"id_movie INTEGER NOT NULL, "
"id_tag INTEGER NOT NULL, "
"UNIQUE (id_tag, id_movie) ON CONFLICT IGNORE "
")");
// Playlists
l_ret = l_ret && l_query.exec("CREATE TABLE IF NOT EXISTS playlists("
"id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE, "
"name VARCHAR(255) UNIQUE NOT NULL, "
"rate INTEGER, "
"creation_date INT"
")");
// Default Playlist: To Watch
l_ret = l_ret && l_query.exec ("INSERT INTO playlists "
"VALUES(1, 'To Watch', 0, 0)");
// Links between playlist and movies
l_ret = l_ret && l_query.exec("CREATE TABLE IF NOT EXISTS movies_playlists("
"id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE, "
"id_movie INTEGER NOT NULL, "
"id_playlist INTEGER NOT NULL, "
"UNIQUE (id_playlist, id_movie) ON CONFLICT IGNORE "
")");
// List of paths where the movies are stored
l_ret = l_ret && l_query.exec("CREATE TABLE IF NOT EXISTS paths_list("
"id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE, "
"movies_path VARCHAR(255) UNIQUE"
")");
// Config table (for update purposes)
l_ret = l_ret && l_query.exec("CREATE TABLE IF NOT EXISTS config("
"db_version INTERGER )");
// Set the database version
l_ret = l_ret && l_query.exec("INSERT INTO config (`db_version`) VALUES ('" + QString::number(DB_VERSION) + "')");
}
}
return l_ret;
}
