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