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 <QDesktopServices>
  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 #if QT_VERSION >= 0x050000
  31     QString dataLocation = QStandardPaths::writableLocation(QStandardPaths::DataLocation);
  32 #else
  33     QString dataLocation = QDesktopServices::storageLocation(QDesktopServices::DataLocation);
  34 #endif
  35
  36     QDir().mkpath(dataLocation);
  37     dbLocation = dataLocation + "/" + dbName;
  38
  39     QMutexLocker locker(&lock);
  40
  41     if (QFile::exists(dbLocation)) {
  42         // check db version
  43         int databaseVersion = getAttribute("version").toInt();
  44         if (databaseVersion > DATABASE_VERSION)
  45             qWarning("Wrong database version: %d", databaseVersion);
  46
  47         if (!getAttribute("channelIdFix").toBool())
  48             fixChannelIds();
  49
  50     } else createDatabase();
  51 }
  52
  53 Database::~Database() {
  54     closeConnections();
  55 }
  56
  57 void Database::createDatabase() {
  58
  59     qWarning() << "Creating the database";
  60
  61     const QSqlDatabase db = getConnection();
  62
  63     QSqlQuery("create table subscriptions ("
  64               "id integer primary key autoincrement,"
  65               "user_id varchar," // this is really channel_id
  66               "user_name varchar," // obsolete yt2 username
  67               "name varchar," // this is really channel_title
  68               "description varchar,"
  69               "thumb_url varchar,"
  70               "country varchar,"
  71               "added integer,"
  72               "checked integer," // last check for videos on YT APIs
  73               "updated integer," // most recent video added
  74               "watched integer," // last time the user watched this channel
  75               "loaded integer," // last time channel metadata was loaded from YT APIs
  76               "notify_count integer," // new videos since "watched"
  77               "views integer)" // number of times the user watched this channel
  78               , db);
  79     QSqlQuery("create unique index idx_user_id on subscriptions(user_id)", db);
  80
  81     QSqlQuery("create table subscriptions_videos ("
  82               "id integer primary key autoincrement,"
  83               "video_id varchar,"
  84               "channel_id integer," // this is really subscription_id
  85               "published integer,"
  86               "added integer,"
  87               "watched integer,"
  88               "title varchar,"
  89               "author varchar," // this is really channel_title
  90               "user_id varchar," // this is really channel_id
  91               "description varchar,"
  92               "url varchar,"
  93               "thumb_url varchar,"
  94               "views integer,"
  95               "duration integer)"
  96               , db);
  97     QSqlQuery("create unique index idx_video_id on subscriptions_videos(video_id)", db);
  98
  99     QSqlQuery("create table attributes (name varchar, value)", db);
 100     QSqlQuery("insert into attributes (name, value) values ('version', "
 101               + QString::number(DATABASE_VERSION) + ")", db);
 102 }
 103
 104 QString Database::getDbLocation() {
 105 #if QT_VERSION >= 0x050000
 106     static const QString dataLocation = QStandardPaths::writableLocation(QStandardPaths::DataLocation);
 107 #else
 108     static const QString dataLocation = QDesktopServices::storageLocation(QDesktopServices::DataLocation);
 109 #endif
 110     return dataLocation + "/" + dbName;
 111 }
 112
 113 bool Database::exists() {
 114     static bool fileExists = false;
 115     if (!fileExists)
 116         fileExists = QFile::exists(getDbLocation());
 117     return fileExists;
 118 }
 119
 120 Database& Database::instance() {
 121     static QMutex mutex;
 122     QMutexLocker locker(&mutex);
 123     if (!databaseInstance) databaseInstance = new Database();
 124     return *databaseInstance;
 125 }
 126
 127 QSqlDatabase Database::getConnection() {
 128     QThread *currentThread = QThread::currentThread();
 129     if (!currentThread) {
 130         qDebug() << "current thread is null";
 131         return QSqlDatabase();
 132     }
 133
 134     const QString threadName = currentThread->objectName();
 135     // qDebug() << "threadName" << threadName << currentThread;
 136     if (connections.contains(currentThread)) {
 137         return connections.value(currentThread);
 138     } else {
 139         // qDebug() << "Creating db connection for" << threadName;
 140         QSqlDatabase connection = QSqlDatabase::addDatabase("QSQLITE", threadName);
 141         connection.setDatabaseName(dbLocation);
 142         if(!connection.open()) {
 143             qWarning() << QString("Cannot connect to database %1 in thread %2").arg(dbLocation, threadName);
 144         }
 145         connections.insert(currentThread, connection);
 146         return connection;
 147     }
 148 }
 149
 150 QVariant Database::getAttribute(QString name) {
 151     QSqlQuery query("select value from attributes where name=?", getConnection());
 152     query.bindValue(0, name);
 153
 154     bool success = query.exec();
 155     if (!success) qDebug() << query.lastQuery() << query.boundValues().values() << query.lastError().text();
 156     if (query.next())
 157         return query.value(0);
 158     return QVariant();
 159 }
 160
 161 void Database::setAttribute(QString name, QVariant value) {
 162     QSqlQuery query(getConnection());
 163     query.prepare("insert or replace into attributes (name, value) values (?,?)");
 164     query.bindValue(0, name);
 165     query.bindValue(1, value);
 166     bool success = query.exec();
 167     if (!success) qWarning() << query.lastError().text();
 168 }
 169
 170 void Database::fixChannelIds() {
 171     if (!getConnection().transaction())
 172         qWarning() << "Transaction failed" << __PRETTY_FUNCTION__;
 173
 174     qWarning() << "Fixing channel ids";
 175
 176     QSqlQuery query(getConnection());
 177     bool success = query.exec("update subscriptions set user_id='UC' || user_id where user_id not like 'UC%'");
 178     if (!success) qWarning() << query.lastError().text();
 179
 180     query = QSqlQuery(getConnection());
 181     success = query.exec("update subscriptions_videos set user_id='UC' || user_id where user_id not like 'UC%'");
 182     if (!success) qWarning() << query.lastError().text();
 183
 184     setAttribute("channelIdFix", 1);
 185
 186     if (!getConnection().commit())
 187         qWarning() << "Commit failed" << __PRETTY_FUNCTION__;
 188 }
 189
 190 /**
 191   * After calling this method you have to reacquire a valid instance using instance()
 192   */
 193 void Database::drop() {
 194     /// closeConnections();
 195     if (!QFile::remove(dbLocation)) {
 196         qWarning() << "Cannot delete database" << dbLocation;
 197
 198         // fallback to delete records in tables
 199         const QSqlDatabase db = getConnection();
 200         QSqlQuery query(db);
 201         if (!query.exec("select name from sqlite_master where type='table'")) {
 202             qWarning() << query.lastQuery() << query.lastError().text();
 203         }
 204
 205         while (query.next()) {
 206             QString tableName = query.value(0).toString();
 207             if (tableName.startsWith("sqlite_") || tableName == QLatin1String("attributes")) continue;
 208             QString dropSQL = "delete from " + tableName;
 209             QSqlQuery query2(db);
 210             if (!query2.exec(dropSQL))
 211                 qWarning() << query2.lastQuery() << query2.lastError().text();
 212         }
 213
 214         query.exec("delete from sqlite_sequence");
 215
 216     }
 217     if (databaseInstance) delete databaseInstance;
 218     databaseInstance = 0;
 219 }
 220
 221 void Database::closeConnections() {
 222     foreach(QSqlDatabase connection, connections.values()) {
 223         // qDebug() << "Closing connection" << connection;
 224         connection.close();
 225     }
 226     connections.clear();
 227 }
 228
 229 void Database::closeConnection() {
 230     QThread *currentThread = QThread::currentThread();
 231     if (!connections.contains(currentThread)) return;
 232     QSqlDatabase connection = connections.take(currentThread);
 233     // qDebug() << "Closing connection" << connection;
 234     connection.close();
 235 }
 236
 237 void Database::shutdown() {
 238     if (!databaseInstance) return;
 239     QSqlQuery("vacuum", databaseInstance->getConnection());
 240     databaseInstance->closeConnections();
 241 }