bool SyncJournalDb::checkConnect()
{
if( _db.isOpen() ) {
return true;
}
if( _dbFile.isEmpty()) {
qDebug() << "Database filename" + _dbFile + " is empty";
return false;
}
bool isNewDb = !QFile::exists(_dbFile);
// The database file is created by this call (SQLITE_OPEN_CREATE)
if( !_db.openOrCreateReadWrite(_dbFile) ) {
QString error = _db.error();
qDebug() << "Error opening the db: " << error;
return false;
}
if( !QFile::exists(_dbFile) ) {
qDebug() << "Database file" + _dbFile + " does not exist";
return false;
}
SqlQuery pragma1(_db);
pragma1.prepare("SELECT sqlite_version();");
if (!pragma1.exec()) {
return sqlFail("SELECT sqlite_version()", pragma1);
} else {
pragma1.next();
qDebug() << "sqlite3 version" << pragma1.stringValue(0);
}
// Allow forcing the journal mode for debugging
static QString env_journal_mode = QString::fromLocal8Bit(qgetenv("OWNCLOUD_SQLITE_JOURNAL_MODE"));
QString journal_mode = env_journal_mode;
if (journal_mode.isEmpty()) {
journal_mode = defaultJournalMode(_dbFile);
}
pragma1.prepare(QString("PRAGMA journal_mode=%1;").arg(journal_mode));
if (!pragma1.exec()) {
return sqlFail("Set PRAGMA journal_mode", pragma1);
} else {
pragma1.next();
qDebug() << "sqlite3 journal_mode=" << pragma1.stringValue(0);
}
// For debugging purposes, allow temp_store to be set
static QString env_temp_store = QString::fromLocal8Bit(qgetenv("OWNCLOUD_SQLITE_TEMP_STORE"));
if (!env_temp_store.isEmpty()) {
pragma1.prepare(QString("PRAGMA temp_store = %1;").arg(env_temp_store));
if (!pragma1.exec()) {
return sqlFail("Set PRAGMA temp_store", pragma1);
}
qDebug() << "sqlite3 with temp_store =" << env_temp_store;
}
pragma1.prepare("PRAGMA synchronous = 1;");
if (!pragma1.exec()) {
return sqlFail("Set PRAGMA synchronous", pragma1);
}
pragma1.prepare("PRAGMA case_sensitive_like = ON;");
if (!pragma1.exec()) {
return sqlFail("Set PRAGMA case_sensitivity", pragma1);
}
/* Because insert is so slow, we do everything in a transaction, and only need one call to commit */
startTransaction();
SqlQuery createQuery(_db);
createQuery.prepare("CREATE TABLE IF NOT EXISTS metadata("
"phash INTEGER(8),"
"pathlen INTEGER,"
"path VARCHAR(4096),"
"inode INTEGER,"
"uid INTEGER,"
"gid INTEGER,"
"mode INTEGER,"
"modtime INTEGER(8),"
"type INTEGER,"
"md5 VARCHAR(32)," /* This is the etag. Called md5 for compatibility */
// updateDatabaseStructure() will add a fileid column
// updateDatabaseStructure() will add a remotePerm column
"PRIMARY KEY(phash)"
");");
if (!createQuery.exec()) {
return sqlFail("Create table metadata", createQuery);
}
createQuery.prepare("CREATE TABLE IF NOT EXISTS downloadinfo("
"path VARCHAR(4096),"
"tmpfile VARCHAR(4096),"
"etag VARCHAR(32),"
"errorcount INTEGER,"
"PRIMARY KEY(path)"
");");
if (!createQuery.exec()) {
return sqlFail("Create table downloadinfo", createQuery);
}
createQuery.prepare("CREATE TABLE IF NOT EXISTS uploadinfo("
"path VARCHAR(4096),"
"chunk INTEGER,"
"transferid INTEGER,"
"errorcount INTEGER,"
"size INTEGER(8),"
"modtime INTEGER(8),"
"PRIMARY KEY(path)"
");");
if (!createQuery.exec()) {
return sqlFail("Create table uploadinfo", createQuery);
}
// create the blacklist table.
createQuery.prepare("CREATE TABLE IF NOT EXISTS blacklist ("
"path VARCHAR(4096),"
"lastTryEtag VARCHAR[32],"
"lastTryModtime INTEGER[8],"
"retrycount INTEGER,"
"errorstring VARCHAR[4096],"
"PRIMARY KEY(path)"
");");
if (!createQuery.exec()) {
return sqlFail("Create table blacklist", createQuery);
}
createQuery.prepare("CREATE TABLE IF NOT EXISTS poll("
"path VARCHAR(4096),"
"modtime INTEGER(8),"
"pollpath VARCHAR(4096));");
if (!createQuery.exec()) {
return sqlFail("Create table poll", createQuery);
}
// create the selectivesync table.
createQuery.prepare("CREATE TABLE IF NOT EXISTS selectivesync ("
"path VARCHAR(4096),"
"type INTEGER"
");");
if (!createQuery.exec()) {
return sqlFail("Create table selectivesync", createQuery);
}
createQuery.prepare("CREATE TABLE IF NOT EXISTS version("
"major INTEGER(8),"
"minor INTEGER(8),"
"patch INTEGER(8),"
"custom VARCHAR(256)"
");");
if (!createQuery.exec()) {
return sqlFail("Create table version", createQuery);
}
bool forceRemoteDiscovery = false;
SqlQuery versionQuery("SELECT major, minor, patch FROM version;", _db);
if (!versionQuery.next()) {
// If there was no entry in the table, it means we are likely upgrading from 1.5
if (!isNewDb) {
qDebug() << Q_FUNC_INFO << "possibleUpgradeFromMirall_1_5 detected!";
forceRemoteDiscovery = true;
}
createQuery.prepare("INSERT INTO version VALUES (?1, ?2, ?3, ?4);");
createQuery.bindValue(1, MIRALL_VERSION_MAJOR);
createQuery.bindValue(2, MIRALL_VERSION_MINOR);
createQuery.bindValue(3, MIRALL_VERSION_PATCH);
createQuery.bindValue(4, MIRALL_VERSION_BUILD);
createQuery.exec();
} else {
int major = versionQuery.intValue(0);
int minor = versionQuery.intValue(1);
int patch = versionQuery.intValue(2);
if( major == 1 && minor == 8 && (patch == 0 || patch == 1) ) {
qDebug() << Q_FUNC_INFO << "possibleUpgradeFromMirall_1_8_0_or_1 detected!";
forceRemoteDiscovery = true;
}
// Not comparing the BUILD id here, correct?
if( !(major == MIRALL_VERSION_MAJOR && minor == MIRALL_VERSION_MINOR && patch == MIRALL_VERSION_PATCH) ) {
createQuery.prepare("UPDATE version SET major=?1, minor=?2, patch =?3, custom=?4 "
"WHERE major=?5 AND minor=?6 AND patch=?7;");
createQuery.bindValue(1, MIRALL_VERSION_MAJOR);
createQuery.bindValue(2, MIRALL_VERSION_MINOR);
createQuery.bindValue(3, MIRALL_VERSION_PATCH);
createQuery.bindValue(4, MIRALL_VERSION_BUILD);
createQuery.bindValue(5, major);
createQuery.bindValue(6, minor);
createQuery.bindValue(7, patch);
if (!createQuery.exec()) {
return sqlFail("Update version", createQuery);
}
}
}
commitInternal("checkConnect");
bool rc = updateDatabaseStructure();
if( !rc ) {
qDebug() << "WARN: Failed to update the database structure!";
}
/*
* If we are upgrading from a client version older than 1.5,
* we cannot read from the database because we need to fetch the files id and etags.
*
* If 1.8.0 caused missing data in the local tree, so we also don't read from DB
* to get back the files that were gone.
* In 1.8.1 we had a fix to re-get the data, but this one here is better
*/
if (forceRemoteDiscovery) {
forceRemoteDiscoveryNextSyncLocked();
}
_getFileRecordQuery.reset(new SqlQuery(_db));
_getFileRecordQuery->prepare("SELECT path, inode, uid, gid, mode, modtime, type, md5, fileid, remotePerm, filesize, ignoredChildrenRemote FROM "
"metadata WHERE phash=?1" );
_setFileRecordQuery.reset(new SqlQuery(_db) );
_setFileRecordQuery->prepare("INSERT OR REPLACE INTO metadata "
"(phash, pathlen, path, inode, uid, gid, mode, modtime, type, md5, fileid, remotePerm, filesize, ignoredChildrenRemote) "
"VALUES (?1 , ?2, ?3 , ?4 , ?5 , ?6 , ?7, ?8 , ?9 , ?10, ?11, ?12, ?13, ?14);" );
_getDownloadInfoQuery.reset(new SqlQuery(_db) );
_getDownloadInfoQuery->prepare( "SELECT tmpfile, etag, errorcount FROM "
"downloadinfo WHERE path=?1" );
_setDownloadInfoQuery.reset(new SqlQuery(_db) );
_setDownloadInfoQuery->prepare( "INSERT OR REPLACE INTO downloadinfo "
"(path, tmpfile, etag, errorcount) "
"VALUES ( ?1 , ?2, ?3, ?4 )" );
_deleteDownloadInfoQuery.reset(new SqlQuery(_db) );
_deleteDownloadInfoQuery->prepare( "DELETE FROM downloadinfo WHERE path=?1" );
_getUploadInfoQuery.reset(new SqlQuery(_db));
_getUploadInfoQuery->prepare( "SELECT chunk, transferid, errorcount, size, modtime FROM "
"uploadinfo WHERE path=?1" );
_setUploadInfoQuery.reset(new SqlQuery(_db));
_setUploadInfoQuery->prepare( "INSERT OR REPLACE INTO uploadinfo "
"(path, chunk, transferid, errorcount, size, modtime) "
"VALUES ( ?1 , ?2, ?3 , ?4 , ?5, ?6 )");
_deleteUploadInfoQuery.reset(new SqlQuery(_db));
_deleteUploadInfoQuery->prepare("DELETE FROM uploadinfo WHERE path=?1" );
_deleteFileRecordPhash.reset(new SqlQuery(_db));
_deleteFileRecordPhash->prepare("DELETE FROM metadata WHERE phash=?1");
_deleteFileRecordRecursively.reset(new SqlQuery(_db));
_deleteFileRecordRecursively->prepare("DELETE FROM metadata WHERE path LIKE(?||'/%')");
QString sql( "SELECT lastTryEtag, lastTryModtime, retrycount, errorstring, lastTryTime, ignoreDuration "
"FROM blacklist WHERE path=?1");
if( Utility::fsCasePreserving() ) {
// if the file system is case preserving we have to check the blacklist
// case insensitively
sql += QLatin1String(" COLLATE NOCASE");
}
_getErrorBlacklistQuery.reset(new SqlQuery(_db));
_getErrorBlacklistQuery->prepare(sql);
_setErrorBlacklistQuery.reset(new SqlQuery(_db));
_setErrorBlacklistQuery->prepare("INSERT OR REPLACE INTO blacklist "
"(path, lastTryEtag, lastTryModtime, retrycount, errorstring, lastTryTime, ignoreDuration) "
"VALUES ( ?1, ?2, ?3, ?4, ?5, ?6, ?7)");
_getSelectiveSyncListQuery.reset(new SqlQuery(_db));
_getSelectiveSyncListQuery->prepare("SELECT path FROM selectivesync WHERE type=?1");
// don't start a new transaction now
commitInternal(QString("checkConnect End"), false);
// Hide 'em all!
FileSystem::setFileHidden(databaseFilePath(), true);
FileSystem::setFileHidden(databaseFilePath() + "-wal", true);
FileSystem::setFileHidden(databaseFilePath() + "-shm", true);
FileSystem::setFileHidden(databaseFilePath() + "-journal", true);
return rc;
}
bool SyncJournalDb::checkConnect()
{
if( _db.isOpen() ) {
return true;
}
if( _dbFile.isEmpty() || !QFile::exists(_dbFile) ) {
qDebug() << "Database " + _dbFile + " is empty or does not exist";
return false;
}
QStringList list = QSqlDatabase::drivers();
if( list.size() == 0 ) {
qDebug() << "Database Drivers could not be loaded.";
return false ;
} else {
if( list.indexOf( QSQLITE ) == -1 ) {
qDebug() << "Database Driver QSQLITE could not be loaded!";
return false;
}
}
// Add the connection
_db = QSqlDatabase::addDatabase( QSQLITE, _dbFile);
// Open the file
_db.setDatabaseName(_dbFile);
if (!_db.isOpen()) {
if( !_db.open() ) {
QSqlError error = _db.lastError();
qDebug() << "Error opening the db: " << error.text();
return false;
}
}
QSqlQuery pragma1(_db);
pragma1.prepare("PRAGMA synchronous = 1;");
if (!pragma1.exec()) {
return sqlFail("Set PRAGMA synchronous", pragma1);
}
pragma1.prepare("PRAGMA case_sensitive_like = ON;");
if (!pragma1.exec()) {
return sqlFail("Set PRAGMA case_sensitivity", pragma1);
}
/* Because insert are so slow, e do everything in a transaction, and one need to call commit */
startTransaction();
QSqlQuery createQuery(_db);
createQuery.prepare("CREATE TABLE IF NOT EXISTS metadata("
"phash INTEGER(8),"
"pathlen INTEGER,"
"path VARCHAR(4096),"
"inode INTEGER,"
"uid INTEGER,"
"gid INTEGER,"
"mode INTEGER,"
"modtime INTEGER(8),"
"type INTEGER,"
"md5 VARCHAR(32)," /* This is the etag. Called md5 for compatibility */
// updateDatabaseStructure() will add a fileid column
// updateDatabaseStructure() will add a remotePerm column
"PRIMARY KEY(phash)"
");");
if (!createQuery.exec()) {
return sqlFail("Create table metadata", createQuery);
}
createQuery.prepare("CREATE TABLE IF NOT EXISTS downloadinfo("
"path VARCHAR(4096),"
"tmpfile VARCHAR(4096),"
"etag VARCHAR(32),"
"errorcount INTEGER,"
"PRIMARY KEY(path)"
");");
if (!createQuery.exec()) {
return sqlFail("Create table downloadinfo", createQuery);
}
createQuery.prepare("CREATE TABLE IF NOT EXISTS uploadinfo("
"path VARCHAR(4096),"
"chunk INTEGER,"
"transferid INTEGER,"
"errorcount INTEGER,"
"size INTEGER(8),"
"modtime INTEGER(8),"
"PRIMARY KEY(path)"
");");
if (!createQuery.exec()) {
return sqlFail("Create table uploadinfo", createQuery);
}
// create the blacklist table.
createQuery.prepare("CREATE TABLE IF NOT EXISTS blacklist ("
"path VARCHAR(4096),"
"lastTryEtag VARCHAR[32],"
"lastTryModtime INTEGER[8],"
"retrycount INTEGER,"
"errorstring VARCHAR[4096],"
"PRIMARY KEY(path)"
");");
if (!createQuery.exec()) {
return sqlFail("Create table blacklist", createQuery);
}
createQuery.prepare("CREATE TABLE IF NOT EXISTS version("
"major INTEGER(8),"
"minor INTEGER(8),"
"patch INTEGER(8),"
"custom VARCHAR(256)"
");");
if (!createQuery.exec()) {
return sqlFail("Create table blacklist", createQuery);
}
QSqlQuery versionQuery("SELECT major, minor FROM version;", _db);
if (!versionQuery.next()) {
// If there was no entry in the table, it means we are likely upgrading from 1.5
_possibleUpgradeFromMirall_1_5 = true;
} else {
// Delete the existing entry so we can replace it by the new one
createQuery.prepare("DELETE FROM version;");
if (!createQuery.exec()) {
return sqlFail("Remove version", createQuery);
}
}
createQuery.prepare("INSERT INTO version (major, minor, patch) VALUES ( ? , ? , ? );");
createQuery.bindValue(0, MIRALL_VERSION_MAJOR);
createQuery.bindValue(1, MIRALL_VERSION_MINOR);
createQuery.bindValue(2, MIRALL_VERSION_PATCH);
if (!createQuery.exec()) {
return sqlFail("Insert Version", createQuery);
}
commitInternal("checkConnect");
bool rc = updateDatabaseStructure();
if( !rc ) {
qDebug() << "WARN: Failed to update the database structure!";
}
_getFileRecordQuery.reset(new QSqlQuery(_db));
_getFileRecordQuery->prepare("SELECT path, inode, uid, gid, mode, modtime, type, md5, fileid, remotePerm FROM "
"metadata WHERE phash=:ph" );
_setFileRecordQuery.reset(new QSqlQuery(_db) );
_setFileRecordQuery->prepare("INSERT OR REPLACE INTO metadata "
"(phash, pathlen, path, inode, uid, gid, mode, modtime, type, md5, fileid, remotePerm) "
"VALUES ( ? , ?, ? , ? , ? , ? , ?, ? , ? , ?, ?, ? )" );
_getDownloadInfoQuery.reset(new QSqlQuery(_db) );
_getDownloadInfoQuery->prepare( "SELECT tmpfile, etag, errorcount FROM "
"downloadinfo WHERE path=:pa" );
_setDownloadInfoQuery.reset(new QSqlQuery(_db) );
_setDownloadInfoQuery->prepare( "INSERT OR REPLACE INTO downloadinfo "
"(path, tmpfile, etag, errorcount) "
"VALUES ( ? , ?, ? , ? )" );
_deleteDownloadInfoQuery.reset(new QSqlQuery(_db) );
_deleteDownloadInfoQuery->prepare( "DELETE FROM downloadinfo WHERE path=?" );
_getUploadInfoQuery.reset(new QSqlQuery(_db));
_getUploadInfoQuery->prepare( "SELECT chunk, transferid, errorcount, size, modtime FROM "
"uploadinfo WHERE path=:pa" );
_setUploadInfoQuery.reset(new QSqlQuery(_db));
_setUploadInfoQuery->prepare( "INSERT OR REPLACE INTO uploadinfo "
"(path, chunk, transferid, errorcount, size, modtime) "
"VALUES ( ? , ?, ? , ? , ? , ? )");
_deleteUploadInfoQuery.reset(new QSqlQuery(_db));
_deleteUploadInfoQuery->prepare("DELETE FROM uploadinfo WHERE path=?" );
_deleteFileRecordPhash.reset(new QSqlQuery(_db));
_deleteFileRecordPhash->prepare("DELETE FROM metadata WHERE phash=?");
_deleteFileRecordRecursively.reset(new QSqlQuery(_db));
_deleteFileRecordRecursively->prepare("DELETE FROM metadata WHERE path LIKE(?||'/%')");
_blacklistQuery.reset(new QSqlQuery(_db));
_blacklistQuery->prepare("SELECT lastTryEtag, lastTryModtime, retrycount, errorstring "
"FROM blacklist WHERE path=:path");
return rc;
}
bool SyncJournalDb::checkConnect()
{
if( _db.isOpen() ) {
return true;
}
if( _dbFile.isEmpty() || !QFile::exists(_dbFile) ) {
return false;
}
QStringList list = QSqlDatabase::drivers();
if( list.size() == 0 ) {
qDebug() << "Database Drivers could not be loaded.";
return false ;
} else {
if( list.indexOf( QSQLITE ) == -1 ) {
qDebug() << "Database Driver QSQLITE could not be loaded!";
return false;
}
}
// Add the connection
_db = QSqlDatabase::addDatabase( QSQLITE, _dbFile);
// Open the file
_db.setDatabaseName(_dbFile);
if (!_db.isOpen()) {
if( !_db.open() ) {
QSqlError error = _db.lastError();
qDebug() << "Error opening the db: " << error.text();
return false;
}
}
QSqlQuery pragma1(_db);
pragma1.prepare("PRAGMA synchronous = 1;");
if (!pragma1.exec()) {
return sqlFail("Set PRAGMA synchronous", pragma1);
}
pragma1.prepare("PRAGMA case_sensitive_like = ON;");
if (!pragma1.exec()) {
return sqlFail("Set PRAGMA case_sensitivity", pragma1);
}
/* Because insert are so slow, e do everything in a transaction, and one need to call commit */
startTransaction();
QSqlQuery createQuery(_db);
createQuery.prepare("CREATE TABLE IF NOT EXISTS metadata("
"phash INTEGER(8),"
"pathlen INTEGER,"
"path VARCHAR(4096),"
"inode INTEGER,"
"uid INTEGER,"
"gid INTEGER,"
"mode INTEGER,"
"modtime INTEGER(8),"
"type INTEGER,"
"md5 VARCHAR(32)," /* This is the etag. Called md5 for compatibility */
"PRIMARY KEY(phash)"
");");
if (!createQuery.exec()) {
return sqlFail("Create table metadata", createQuery);
}
createQuery.prepare("CREATE TABLE IF NOT EXISTS downloadinfo("
"path VARCHAR(4096),"
"tmpfile VARCHAR(4096),"
"etag VARCHAR(32),"
"errorcount INTEGER,"
"PRIMARY KEY(path)"
");");
if (!createQuery.exec()) {
return sqlFail("Create table downloadinfo", createQuery);
}
createQuery.prepare("CREATE TABLE IF NOT EXISTS uploadinfo("
"path VARCHAR(4096),"
"chunk INTEGER,"
"transferid INTEGER,"
"errorcount INTEGER,"
"size INTEGER(8),"
"modtime INTEGER(8),"
"PRIMARY KEY(path)"
");");
if (!createQuery.exec()) {
return sqlFail("Create table uploadinfo", createQuery);
}
// create the blacklist table.
createQuery.prepare("CREATE TABLE IF NOT EXISTS blacklist ("
"path VARCHAR(4096),"
"lastTryEtag VARCHAR[32],"
"lastTryModtime INTEGER[8],"
"retrycount INTEGER,"
"errorstring VARCHAR[4096],"
"PRIMARY KEY(path)"
");");
if (!createQuery.exec()) {
return sqlFail("Create table blacklist", createQuery);
}
commitInternal("checkConnect");
bool rc = updateDatabaseStructure();
if( rc ) {
_getFileRecordQuery.reset(new QSqlQuery(_db));
_getFileRecordQuery->prepare("SELECT path, inode, uid, gid, mode, modtime, type, md5, fileid FROM "
"metadata WHERE phash=:ph" );
_setFileRecordQuery.reset(new QSqlQuery(_db) );
_setFileRecordQuery->prepare("INSERT OR REPLACE INTO metadata "
"(phash, pathlen, path, inode, uid, gid, mode, modtime, type, md5, fileid) "
"VALUES ( ? , ?, ? , ? , ? , ? , ?, ? , ? , ?, ? )" );
_getDownloadInfoQuery.reset(new QSqlQuery(_db) );
_getDownloadInfoQuery->prepare( "SELECT tmpfile, etag, errorcount FROM "
"downloadinfo WHERE path=:pa" );
_setDownloadInfoQuery.reset(new QSqlQuery(_db) );
_setDownloadInfoQuery->prepare( "INSERT OR REPLACE INTO downloadinfo "
"(path, tmpfile, etag, errorcount) "
"VALUES ( ? , ?, ? , ? )" );
_deleteDownloadInfoQuery.reset(new QSqlQuery(_db) );
_deleteDownloadInfoQuery->prepare( "DELETE FROM downloadinfo WHERE path=?" );
_getUploadInfoQuery.reset(new QSqlQuery(_db));
_getUploadInfoQuery->prepare( "SELECT chunk, transferid, errorcount, size, modtime FROM "
"uploadinfo WHERE path=:pa" );
_setUploadInfoQuery.reset(new QSqlQuery(_db));
_setUploadInfoQuery->prepare( "INSERT OR REPLACE INTO uploadinfo "
"(path, chunk, transferid, errorcount, size, modtime) "
"VALUES ( ? , ?, ? , ? , ? , ? )");
_deleteUploadInfoQuery.reset(new QSqlQuery(_db));
_deleteUploadInfoQuery->prepare("DELETE FROM uploadinfo WHERE path=?" );
_deleteFileRecordPhash.reset(new QSqlQuery(_db));
_deleteFileRecordPhash->prepare("DELETE FROM metadata WHERE phash=?");
_deleteFileRecordRecursively.reset(new QSqlQuery(_db));
_deleteFileRecordRecursively->prepare("DELETE FROM metadata WHERE path LIKE(?||'/%')");
_blacklistQuery.reset(new QSqlQuery(_db));
_blacklistQuery->prepare("SELECT lastTryEtag, lastTryModtime, retrycount, errorstring "
"FROM blacklist WHERE path=:path");
}
return rc;
}
