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             qDebug() << "asd" << oldDbLocation;
 134             fileExists = QFile::exists(oldDbLocation);
 135         }
 136 #endif
 137     }
 138     return fileExists;
 139 }
 140
 141 // static
 142 Database& Database::instance() {
 143     static QMutex mutex;
 144     QMutexLocker locker(&mutex);
 145     if (!databaseInstance) databaseInstance = new Database();
 146     return *databaseInstance;
 147 }
 148
 149 QSqlDatabase Database::getConnection() {
 150     QThread *currentThread = QThread::currentThread();
 151     if (!currentThread) {
 152         qDebug() << "current thread is null";
 153         return QSqlDatabase();
 154     }
 155
 156     const QString threadName = currentThread->objectName();
 157     // qDebug() << "threadName" << threadName << currentThread;
 158     if (connections.contains(currentThread)) {
 159         return connections.value(currentThread);
 160     } else {
 161         // qDebug() << "Creating db connection for" << threadName;
 162         QSqlDatabase connection = QSqlDatabase::addDatabase("QSQLITE", threadName);
 163         connection.setDatabaseName(dbLocation);
 164         if(!connection.open()) {
 165             qWarning() << QString("Cannot connect to database %1 in thread %2").arg(dbLocation, threadName);
 166         }
 167         connections.insert(currentThread, connection);
 168         return connection;
 169     }
 170 }
 171
 172 QVariant Database::getAttribute(const QString &name) {
 173     QSqlQuery query("select value from attributes where name=?", getConnection());
 174     query.bindValue(0, name);
 175
 176     bool success = query.exec();
 177     if (!success) qDebug() << query.lastQuery() << query.boundValues().values() << query.lastError().text();
 178     if (query.next())
 179         return query.value(0);
 180     return QVariant();
 181 }
 182
 183 void Database::setAttribute(const QString &name, const QVariant &value) {
 184     QSqlQuery query(getConnection());
 185     query.prepare("insert or replace into attributes (name, value) values (?,?)");
 186     query.bindValue(0, name);
 187     query.bindValue(1, value);
 188     bool success = query.exec();
 189     if (!success) qWarning() << query.lastError().text();
 190 }
 191
 192 void Database::fixChannelIds() {
 193     if (!getConnection().transaction())
 194         qWarning() << "Transaction failed" << __PRETTY_FUNCTION__;
 195
 196     qWarning() << "Fixing channel ids";
 197
 198     QSqlQuery query(getConnection());
 199     bool success = query.exec("update subscriptions set user_id='UC' || user_id where user_id not like 'UC%'");
 200     if (!success) qWarning() << query.lastError().text();
 201
 202     query = QSqlQuery(getConnection());
 203     success = query.exec("update subscriptions_videos set user_id='UC' || user_id where user_id not like 'UC%'");
 204     if (!success) qWarning() << query.lastError().text();
 205
 206     setAttribute("channelIdFix", 1);
 207
 208     if (!getConnection().commit())
 209         qWarning() << "Commit failed" << __PRETTY_FUNCTION__;
 210 }
 211
 212 /**
 213   * After calling this method you have to reacquire a valid instance using instance()
 214   */
 215 void Database::drop() {
 216     /// closeConnections();
 217     if (!QFile::remove(dbLocation)) {
 218         qWarning() << "Cannot delete database" << dbLocation;
 219
 220         // fallback to delete records in tables
 221         const QSqlDatabase db = getConnection();
 222         QSqlQuery query(db);
 223         if (!query.exec("select name from sqlite_master where type='table'")) {
 224             qWarning() << query.lastQuery() << query.lastError().text();
 225         }
 226
 227         while (query.next()) {
 228             QString tableName = query.value(0).toString();
 229             if (tableName.startsWith("sqlite_") || tableName == QLatin1String("attributes")) continue;
 230             QString dropSQL = "delete from " + tableName;
 231             QSqlQuery query2(db);
 232             if (!query2.exec(dropSQL))
 233                 qWarning() << query2.lastQuery() << query2.lastError().text();
 234         }
 235
 236         query.exec("delete from sqlite_sequence");
 237
 238     }
 239     if (databaseInstance) delete databaseInstance;
 240     databaseInstance = 0;
 241 }
 242
 243 void Database::closeConnections() {
 244     foreach(QSqlDatabase connection, connections) {
 245         // qDebug() << "Closing connection" << connection;
 246         connection.close();
 247     }
 248     connections.clear();
 249 }
 250
 251 void Database::closeConnection() {
 252     QThread *currentThread = QThread::currentThread();
 253     if (!connections.contains(currentThread)) return;
 254     QSqlDatabase connection = connections.take(currentThread);
 255     // qDebug() << "Closing connection" << connection;
 256     connection.close();
 257 }
 258
 259 void Database::shutdown() {
 260     if (!databaseInstance) return;
 261     QSqlQuery("vacuum", databaseInstance->getConnection());
 262     databaseInstance->closeConnections();
 263 }