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"
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 QString dataLocation = QStandardPaths::writableLocation(QStandardPaths::DataLocation);
32 if (!QDir().mkpath(dataLocation)) {
33 qCritical() << "Failed to create directory " << dataLocation;
35 dbLocation = dataLocation + "/" + dbName;
36 qDebug() << "dbLocation" << dbLocation;
38 QMutexLocker locker(&lock);
40 if (QFile::exists(dbLocation)) {
42 int databaseVersion = getAttribute("version").toInt();
43 if (databaseVersion > DATABASE_VERSION)
44 qWarning("Wrong database version: %d", databaseVersion);
46 if (!getAttribute("channelIdFix").toBool())
49 } else createDatabase();
52 Database::~Database() {
56 void Database::createDatabase() {
57 qDebug() << __PRETTY_FUNCTION__;
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;
73 qWarning() << "Creating the database";
75 const QSqlDatabase db = getConnection();
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,"
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
93 QSqlQuery("create unique index idx_user_id on subscriptions(user_id)", db);
95 QSqlQuery("create table subscriptions_videos ("
96 "id integer primary key autoincrement,"
98 "channel_id integer," // this is really subscription_id
103 "author varchar," // this is really channel_title
104 "user_id varchar," // this is really channel_id
105 "description varchar,"
111 QSqlQuery("create unique index idx_video_id on subscriptions_videos(video_id)", db);
113 QSqlQuery("create table attributes (name varchar, value)", db);
114 QSqlQuery("insert into attributes (name, value) values ('version', "
115 + QString::number(DATABASE_VERSION) + ")", db);
119 QString Database::getDbLocation() {
120 return QStandardPaths::writableLocation(QStandardPaths::DataLocation) + "/" + dbName;
124 bool Database::exists() {
125 static bool fileExists = false;
127 fileExists = QFile::exists(getDbLocation());
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);
142 Database& Database::instance() {
144 QMutexLocker locker(&mutex);
145 if (!databaseInstance) databaseInstance = new Database();
146 return *databaseInstance;
149 QSqlDatabase Database::getConnection() {
150 QThread *currentThread = QThread::currentThread();
151 if (!currentThread) {
152 qDebug() << "current thread is null";
153 return QSqlDatabase();
156 const QString threadName = currentThread->objectName();
157 // qDebug() << "threadName" << threadName << currentThread;
158 if (connections.contains(currentThread)) {
159 return connections.value(currentThread);
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);
167 connections.insert(currentThread, connection);
172 QVariant Database::getAttribute(const QString &name) {
173 QSqlQuery query("select value from attributes where name=?", getConnection());
174 query.bindValue(0, name);
176 bool success = query.exec();
177 if (!success) qDebug() << query.lastQuery() << query.boundValues().values() << query.lastError().text();
179 return query.value(0);
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();
192 void Database::fixChannelIds() {
193 if (!getConnection().transaction())
194 qWarning() << "Transaction failed" << __PRETTY_FUNCTION__;
196 qWarning() << "Fixing channel ids";
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();
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();
206 setAttribute("channelIdFix", 1);
208 if (!getConnection().commit())
209 qWarning() << "Commit failed" << __PRETTY_FUNCTION__;
213 * After calling this method you have to reacquire a valid instance using instance()
215 void Database::drop() {
216 /// closeConnections();
217 if (!QFile::remove(dbLocation)) {
218 qWarning() << "Cannot delete database" << dbLocation;
220 // fallback to delete records in tables
221 const QSqlDatabase db = getConnection();
223 if (!query.exec("select name from sqlite_master where type='table'")) {
224 qWarning() << query.lastQuery() << query.lastError().text();
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();
236 query.exec("delete from sqlite_sequence");
239 if (databaseInstance) delete databaseInstance;
240 databaseInstance = 0;
243 void Database::closeConnections() {
244 foreach(QSqlDatabase connection, connections) {
245 // qDebug() << "Closing connection" << connection;
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;
259 void Database::shutdown() {
260 if (!databaseInstance) return;
261 QSqlQuery("vacuum", databaseInstance->getConnection());
262 databaseInstance->closeConnections();