git.sur5r.net Git - minitube/blob - src/database.cpp

   1 /* $BEGIN_LICENSE
   2
   3 This file is part of Minitube.
   4 Copyright 2009, Flavio Tordini <flavio.tordini@gmail.com>
   5
   6 Minitube is free software: you can redistribute it and/or modify
   7 it under the terms of the GNU General Public License as published by
   8 the Free Software Foundation, either version 3 of the License, or
   9 (at your option) any later version.
  10
  11 Minitube is distributed in the hope that it will be useful,
  12 but WITHOUT ANY WARRANTY; without even the implied warranty of
  13 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  14 GNU General Public License for more details.
  15
  16 You should have received a copy of the GNU General Public License
  17 along with Minitube.  If not, see <http://www.gnu.org/licenses/>.
  18
  19 $END_LICENSE */
  20
  21 #include "database.h"
  22 #include "constants.h"
  23 #include <QtDebug>
  24
  25 static const int DATABASE_VERSION = 1;
  26 static const QString dbName = QLatin1String(Constants::UNIX_NAME) + ".db";
  27 static Database *databaseInstance = 0;
  28
  29 Database::Database() {
  30     QString dataLocation = QStandardPaths::writableLocation(QStandardPaths::DataLocation);
  31
  32     if (!QDir().mkpath(dataLocation)) {
  33         qCritical() << "Failed to create directory " << dataLocation;
  34     }
  35     dbLocation = dataLocation + "/" + dbName;
  36     qDebug() << "dbLocation" << dbLocation;
  37
  38     QMutexLocker locker(&lock);
  39
  40     if (QFile::exists(dbLocation)) {
  41         // check db version
  42         int databaseVersion = getAttribute("version").toInt();
  43         if (databaseVersion > DATABASE_VERSION)
  44             qWarning("Wrong database version: %d", databaseVersion);
  45
  46         if (!getAttribute("channelIdFix").toBool())
  47             fixChannelIds();
  48
  49     } else createDatabase();
  50 }
  51
  52 Database::~Database() {
  53     closeConnections();
  54 }
  55
  56 void Database::createDatabase() {
  57     qDebug() << __PRETTY_FUNCTION__;
  58
  59 #ifdef APP_LINUX
  60     // Qt5 changed its "data" path. Try to move the old db to the new path
  61     QString homeLocation = QStandardPaths::writableLocation(QStandardPaths::HomeLocation);
  62     QString qt4DataLocation = homeLocation + "/.local/share/data/" + Constants::ORG_NAME + "/" + Constants::NAME;
  63     QString oldDbLocation = qt4DataLocation + "/" + dbName;
  64     qDebug() << oldDbLocation;
  65     if (QFile::exists(oldDbLocation)) {
  66         if (QFile::copy(oldDbLocation, dbLocation)) {
  67             qDebug() << "Moved db from" << oldDbLocation << "to" << dbLocation;
  68             return;
  69         }
  70     }
  71 #endif
  72
  73     qWarning() << "Creating the database";
  74
  75     const QSqlDatabase db = getConnection();
  76
  77     QSqlQuery("create table subscriptions ("
  78               "id integer primary key autoincrement,"
  79               "user_id varchar," // this is really channel_id
  80               "user_name varchar," // obsolete yt2 username
  81               "name varchar," // this is really channel_title
  82               "description varchar,"
  83               "thumb_url varchar,"
  84               "country varchar,"
  85               "added integer,"
  86               "checked integer," // last check for videos on YT APIs
  87               "updated integer," // most recent video added
  88               "watched integer," // last time the user watched this channel
  89               "loaded integer," // last time channel metadata was loaded from YT APIs
  90               "notify_count integer," // new videos since "watched"
  91               "views integer)" // number of times the user watched this channel
  92               , db);
  93     QSqlQuery("create unique index idx_user_id on subscriptions(user_id)", db);
  94
  95     QSqlQuery("create table subscriptions_videos ("
  96               "id integer primary key autoincrement,"
  97               "video_id varchar,"
  98               "channel_id integer," // this is really subscription_id
  99               "published integer,"
 100               "added integer,"
 101               "watched integer,"
 102               "title varchar,"
 103               "author varchar," // this is really channel_title
 104               "user_id varchar," // this is really channel_id
 105               "description varchar,"
 106               "url varchar,"
 107               "thumb_url varchar,"
 108               "views integer,"
 109               "duration integer)"
 110               , db);
 111     QSqlQuery("create unique index idx_video_id on subscriptions_videos(video_id)", db);
 112
 113     QSqlQuery("create table attributes (name varchar, value)", db);
 114     QSqlQuery("insert into attributes (name, value) values ('version', "
 115               + QString::number(DATABASE_VERSION) + ")", db);
 116 }
 117
 118 // static
 119 QString Database::getDbLocation() {
 120     return QStandardPaths::writableLocation(QStandardPaths::DataLocation) + "/" + dbName;
 121 }
 122
 123 // static
 124 bool Database::exists() {
 125     static bool fileExists = false;
 126     if (!fileExists) {
 127         fileExists = QFile::exists(getDbLocation());
 128 #ifdef APP_LINUX
 129         if (!fileExists) {
 130             QString homeLocation = QStandardPaths::writableLocation(QStandardPaths::HomeLocation);
 131             QString qt4DataLocation = homeLocation + "/.local/share/data/" + Constants::ORG_NAME + "/" + Constants::NAME;
 132             QString oldDbLocation = qt4DataLocation + "/" + dbName;
 133             fileExists = QFile::exists(oldDbLocation);
 134         }
 135 #endif
 136     }
 137     return fileExists;
 138 }
 139
 140 // static
 141 Database& Database::instance() {
 142     static QMutex mutex;
 143     QMutexLocker locker(&mutex);
 144     if (!databaseInstance) databaseInstance = new Database();
 145     return *databaseInstance;
 146 }
 147
 148 QSqlDatabase Database::getConnection() {
 149     QThread *currentThread = QThread::currentThread();
 150     if (!currentThread) {
 151         qDebug() << "current thread is null";
 152         return QSqlDatabase();
 153     }
 154
 155     const QString threadName = currentThread->objectName();
 156     // qDebug() << "threadName" << threadName << currentThread;
 157     if (connections.contains(currentThread)) {
 158         return connections.value(currentThread);
 159     } else {
 160         // qDebug() << "Creating db connection for" << threadName;
 161         QSqlDatabase connection = QSqlDatabase::addDatabase("QSQLITE", threadName);
 162         connection.setDatabaseName(dbLocation);
 163         if(!connection.open()) {
 164             qWarning() << QString("Cannot connect to database %1 in thread %2").arg(dbLocation, threadName);
 165         }
 166         connections.insert(currentThread, connection);
 167         return connection;
 168     }
 169 }
 170
 171 QVariant Database::getAttribute(const QString &name) {
 172     QSqlQuery query("select value from attributes where name=?", getConnection());
 173     query.bindValue(0, name);
 174
 175     bool success = query.exec();
 176     if (!success) qDebug() << query.lastQuery() << query.boundValues().values() << query.lastError().text();
 177     if (query.next())
 178         return query.value(0);
 179     return QVariant();
 180 }
 181
 182 void Database::setAttribute(const QString &name, const QVariant &value) {
 183     QSqlQuery query(getConnection());
 184     query.prepare("insert or replace into attributes (name, value) values (?,?)");
 185     query.bindValue(0, name);
 186     query.bindValue(1, value);
 187     bool success = query.exec();
 188     if (!success) qWarning() << query.lastError().text();
 189 }
 190
 191 void Database::fixChannelIds() {
 192     if (!getConnection().transaction())
 193         qWarning() << "Transaction failed" << __PRETTY_FUNCTION__;
 194
 195     qWarning() << "Fixing channel ids";
 196
 197     QSqlQuery query(getConnection());
 198     bool success = query.exec("update subscriptions set user_id='UC' || user_id where user_id not like 'UC%'");
 199     if (!success) qWarning() << query.lastError().text();
 200
 201     query = QSqlQuery(getConnection());
 202     success = query.exec("update subscriptions_videos set user_id='UC' || user_id where user_id not like 'UC%'");
 203     if (!success) qWarning() << query.lastError().text();
 204
 205     setAttribute("channelIdFix", 1);
 206
 207     if (!getConnection().commit())
 208         qWarning() << "Commit failed" << __PRETTY_FUNCTION__;
 209 }
 210
 211 /**
 212   * After calling this method you have to reacquire a valid instance using instance()
 213   */
 214 void Database::drop() {
 215     /// closeConnections();
 216     if (!QFile::remove(dbLocation)) {
 217         qWarning() << "Cannot delete database" << dbLocation;
 218
 219         // fallback to delete records in tables
 220         const QSqlDatabase db = getConnection();
 221         QSqlQuery query(db);
 222         if (!query.exec("select name from sqlite_master where type='table'")) {
 223             qWarning() << query.lastQuery() << query.lastError().text();
 224         }
 225
 226         while (query.next()) {
 227             QString tableName = query.value(0).toString();
 228             if (tableName.startsWith("sqlite_") || tableName == QLatin1String("attributes")) continue;
 229             QString dropSQL = "delete from " + tableName;
 230             QSqlQuery query2(db);
 231             if (!query2.exec(dropSQL))
 232                 qWarning() << query2.lastQuery() << query2.lastError().text();
 233         }
 234
 235         query.exec("delete from sqlite_sequence");
 236
 237     }
 238     if (databaseInstance) delete databaseInstance;
 239     databaseInstance = 0;
 240 }
 241
 242 void Database::closeConnections() {
 243     foreach(QSqlDatabase connection, connections) {
 244         // qDebug() << "Closing connection" << connection;
 245         connection.close();
 246     }
 247     connections.clear();
 248 }
 249
 250 void Database::closeConnection() {
 251     QThread *currentThread = QThread::currentThread();
 252     if (!connections.contains(currentThread)) return;
 253     QSqlDatabase connection = connections.take(currentThread);
 254     // qDebug() << "Closing connection" << connection;
 255     connection.close();
 256 }
 257
 258 void Database::shutdown() {
 259     if (!databaseInstance) return;
 260     QSqlQuery("vacuum", databaseInstance->getConnection());
 261     databaseInstance->closeConnections();
 262 }