3 This file is part of Minitube.
4 Copyright 2009, Flavio Tordini <flavio.tordini@gmail.com>
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.
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.
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/>.
22 #include "constants.h"
23 #include <QDesktopServices>
25 static const int DATABASE_VERSION = 1;
26 static const QString dbName = QLatin1String(Constants::UNIX_NAME) + ".db";
27 static Database *databaseInstance = 0;
29 Database::Database() {
30 #if QT_VERSION >= 0x050000
31 QString dataLocation = QStandardPaths::writableLocation(QStandardPaths::DataLocation);
33 QString dataLocation = QDesktopServices::storageLocation(QDesktopServices::DataLocation);
36 QDir().mkpath(dataLocation);
37 dbLocation = dataLocation + "/" + dbName;
39 QMutexLocker locker(&lock);
41 if (QFile::exists(dbLocation)) {
43 int databaseVersion = getAttribute("version").toInt();
44 if (databaseVersion > DATABASE_VERSION)
45 qWarning("Wrong database version: %d", databaseVersion);
47 if (!getAttribute("channelIdFix").toBool())
50 } else createDatabase();
53 Database::~Database() {
57 void Database::createDatabase() {
59 qWarning() << "Creating the database";
61 const QSqlDatabase db = getConnection();
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,"
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
79 QSqlQuery("create unique index idx_user_id on subscriptions(user_id)", db);
81 QSqlQuery("create table subscriptions_videos ("
82 "id integer primary key autoincrement,"
84 "channel_id integer," // this is really subscription_id
89 "author varchar," // this is really channel_title
90 "user_id varchar," // this is really channel_id
91 "description varchar,"
97 QSqlQuery("create unique index idx_video_id on subscriptions_videos(video_id)", db);
99 QSqlQuery("create table attributes (name varchar, value)", db);
100 QSqlQuery("insert into attributes (name, value) values ('version', "
101 + QString::number(DATABASE_VERSION) + ")", db);
104 QString Database::getDbLocation() {
105 #if QT_VERSION >= 0x050000
106 static const QString dataLocation = QStandardPaths::writableLocation(QStandardPaths::DataLocation);
108 static const QString dataLocation = QDesktopServices::storageLocation(QDesktopServices::DataLocation);
110 return dataLocation + "/" + dbName;
113 bool Database::exists() {
114 static bool fileExists = false;
116 fileExists = QFile::exists(getDbLocation());
120 Database& Database::instance() {
122 QMutexLocker locker(&mutex);
123 if (!databaseInstance) databaseInstance = new Database();
124 return *databaseInstance;
127 QSqlDatabase Database::getConnection() {
128 QThread *currentThread = QThread::currentThread();
129 if (!currentThread) {
130 qDebug() << "current thread is null";
131 return QSqlDatabase();
134 const QString threadName = currentThread->objectName();
135 // qDebug() << "threadName" << threadName << currentThread;
136 if (connections.contains(currentThread)) {
137 return connections.value(currentThread);
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);
145 connections.insert(currentThread, connection);
150 QVariant Database::getAttribute(QString name) {
151 QSqlQuery query("select value from attributes where name=?", getConnection());
152 query.bindValue(0, name);
154 bool success = query.exec();
155 if (!success) qDebug() << query.lastQuery() << query.boundValues().values() << query.lastError().text();
157 return query.value(0);
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();
170 void Database::fixChannelIds() {
171 if (!getConnection().transaction())
172 qWarning() << "Transaction failed" << __PRETTY_FUNCTION__;
174 qWarning() << "Fixing channel ids";
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();
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();
184 setAttribute("channelIdFix", 1);
186 if (!getConnection().commit())
187 qWarning() << "Commit failed" << __PRETTY_FUNCTION__;
191 * After calling this method you have to reacquire a valid instance using instance()
193 void Database::drop() {
194 /// closeConnections();
195 if (!QFile::remove(dbLocation)) {
196 qWarning() << "Cannot delete database" << dbLocation;
198 // fallback to delete records in tables
199 const QSqlDatabase db = getConnection();
201 if (!query.exec("select name from sqlite_master where type='table'")) {
202 qWarning() << query.lastQuery() << query.lastError().text();
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();
214 query.exec("delete from sqlite_sequence");
217 if (databaseInstance) delete databaseInstance;
218 databaseInstance = 0;
221 void Database::closeConnections() {
222 foreach(QSqlDatabase connection, connections.values()) {
223 // qDebug() << "Closing connection" << connection;
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;
237 void Database::shutdown() {
238 if (!databaseInstance) return;
239 QSqlQuery("vacuum", databaseInstance->getConnection());
240 databaseInstance->closeConnections();