// Insert a new favorite
qint32 FavoritesTable::insert(const FavoritesRecord &record) {
NSqlQuery query(db);
NSqlQuery subQuery(db);
db->lockForWrite();
query.prepare("Select lid, data from datastore where key=:key and lid in (select lid from datastore where key=:parentkey and data=0) order by data");
query.bindValue(":key", FAVORITES_ORDER);
query.bindValue(":parentkey", FAVORITES_PARENT);
query.exec();
int newOrder = 0;
while (query.next()) {
qint32 lid = query.value(0).toInt();
int order = query.value(1).toInt();
if(order == record.order)
newOrder++;
subQuery.prepare("Update datastore set data=:neworder where lid=:lid and key=:key");
subQuery.bindValue(":neworder", newOrder);
subQuery.bindValue(":lid", lid);
subQuery.bindValue(":key", FAVORITES_ORDER);
subQuery.exec();
newOrder++;
}
query.finish();
db->unlock();
return add(record);
}
ContactList *RosterDBManager::getAllContacts()
{
ContactList *list = new ContactList();
QSqlQuery query(db);
qint64 startTime = QDateTime::currentDateTime().toMSecsSinceEpoch();
Utilities::logData("Reading roster DB...");
query.prepare("select * from roster");
query.exec();
while (query.next()) {
Contact *c;
int type = query.value(1).toInt();
c = ((type == Contact::TypeContact) ? new Contact() : new Group());
c->type = (Contact::ContactType) type;
c->jid = query.value(0).toString();
c->alias = query.value(2).toString();
c->fromAddressBook = query.value(3).toBool();
c->name = query.value(4).toString();
c->phone = query.value(5).toString();
c->status = query.value(6).toString();
c->statusTimestamp = query.value(7).toLongLong();
c->lastSeen = query.value(8).toLongLong();
QByteArray bytes = query.value(9).toByteArray();
c->photo = QImage::fromData(bytes);
if (c->type == Contact::TypeGroup)
{
Group *g = (Group *)c;
g->creationTimestamp = query.value(10).toLongLong();
g->author = query.value(11).toString();
g->subjectTimestamp = query.value(12).toLongLong();
g->subjectOwner = query.value(13).toString();
g->subjectOwnerName = query.value(14).toString();
QSqlQuery subQuery(db);
subQuery.prepare("select jid from participants where gjid = :gjid");
subQuery.bindValue(":jid",g->jid);
subQuery.exec();
while (subQuery.next()) {
g->addParticipant(subQuery.value(0).toString());
}
}
c->photoId = query.value(15).toString();
c->blocked = query.value(16).toBool();
list->insert(c->jid,c);
}
qint64 endTime = QDateTime::currentDateTime().toMSecsSinceEpoch() - startTime;
Utilities::logData("Roster retrieved in " + QString::number(endTime) +
" milliseconds.");
return list;
}
//-----------------------------------------------------------------------------
void
QGCCacheWorker::_exportSets(QGCMapTask* mtask)
{
if(!_testTask(mtask)) {
return;
}
QGCExportTileTask* task = static_cast<QGCExportTileTask*>(mtask);
//-- Delete target if it exists
QFile file(task->path());
file.remove();
//-- Create exported database
QSqlDatabase *dbExport = new QSqlDatabase(QSqlDatabase::addDatabase("QSQLITE", kExportSession));
dbExport->setDatabaseName(task->path());
dbExport->setConnectOptions("QSQLITE_ENABLE_SHARED_CACHE");
if (dbExport->open()) {
if(_createDB(dbExport, false)) {
//-- Prepare progress report
quint64 tileCount = 0;
quint64 currentCount = 0;
for(int i = 0; i < task->sets().count(); i++) {
QGCCachedTileSet* set = task->sets()[i];
//-- Default set has no unique tiles
if(set->defaultSet()) {
tileCount += set->totalTileCount();
} else {
tileCount += set->uniqueTileCount();
}
}
if(!tileCount) {
tileCount = 1;
}
//-- Iterate sets to save
for(int i = 0; i < task->sets().count(); i++) {
QGCCachedTileSet* set = task->sets()[i];
//-- Create Tile Exported Set
QSqlQuery exportQuery(*dbExport);
exportQuery.prepare("INSERT INTO TileSets("
"name, typeStr, topleftLat, topleftLon, bottomRightLat, bottomRightLon, minZoom, maxZoom, type, numTiles, defaultSet, date"
") VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
exportQuery.addBindValue(set->name());
exportQuery.addBindValue(set->mapTypeStr());
exportQuery.addBindValue(set->topleftLat());
exportQuery.addBindValue(set->topleftLon());
exportQuery.addBindValue(set->bottomRightLat());
exportQuery.addBindValue(set->bottomRightLon());
exportQuery.addBindValue(set->minZoom());
exportQuery.addBindValue(set->maxZoom());
exportQuery.addBindValue(set->type());
exportQuery.addBindValue(set->totalTileCount());
exportQuery.addBindValue(set->defaultSet());
exportQuery.addBindValue(QDateTime::currentDateTime().toTime_t());
if(!exportQuery.exec()) {
task->setError("Error adding tile set to exported database");
break;
} else {
//-- Get just created (auto-incremented) setID
quint64 exportSetID = exportQuery.lastInsertId().toULongLong();
//-- Find set tiles
QString s = QString("SELECT * FROM SetTiles WHERE setID = %1").arg(set->id());
QSqlQuery query(*_db);
if(query.exec(s)) {
dbExport->transaction();
while(query.next()) {
quint64 tileID = query.value("tileID").toULongLong();
//-- Get tile
QString s = QString("SELECT * FROM Tiles WHERE tileID = \"%1\"").arg(tileID);
QSqlQuery subQuery(*_db);
if(subQuery.exec(s)) {
if(subQuery.next()) {
QString hash = subQuery.value("hash").toString();
QString format = subQuery.value("format").toString();
QByteArray img = subQuery.value("tile").toByteArray();
int type = subQuery.value("type").toInt();
//-- Save tile
exportQuery.prepare("INSERT INTO Tiles(hash, format, tile, size, type, date) VALUES(?, ?, ?, ?, ?, ?)");
exportQuery.addBindValue(hash);
exportQuery.addBindValue(format);
exportQuery.addBindValue(img);
exportQuery.addBindValue(img.size());
exportQuery.addBindValue(type);
exportQuery.addBindValue(QDateTime::currentDateTime().toTime_t());
if(exportQuery.exec()) {
quint64 exportTileID = exportQuery.lastInsertId().toULongLong();
QString s = QString("INSERT INTO SetTiles(tileID, setID) VALUES(%1, %2)").arg(exportTileID).arg(exportSetID);
exportQuery.prepare(s);
exportQuery.exec();
currentCount++;
task->setProgress((int)((double)currentCount / (double)tileCount * 100.0));
}
}
}
}
}
dbExport->commit();
}
}
} else {
task->setError("Error creating export database");
}
} else {
qCritical() << "Map Cache SQL error (create export database):" << dbExport->lastError();
task->setError("Error opening export database");
}
delete dbExport;
QSqlDatabase::removeDatabase(kExportSession);
task->setExportCompleted();
}
//-----------------------------------------------------------------------------
void
QGCCacheWorker::_importSets(QGCMapTask* mtask)
{
if(!_testTask(mtask)) {
return;
}
QGCImportTileTask* task = static_cast<QGCImportTileTask*>(mtask);
//-- If replacing, simply copy over it
if(task->replace()) {
//-- Close and delete old database
if(_db) {
delete _db;
_db = NULL;
QSqlDatabase::removeDatabase(kSession);
}
QFile file(_databasePath);
file.remove();
//-- Copy given database
QFile::copy(task->path(), _databasePath);
task->setProgress(25);
_init();
if(_valid) {
task->setProgress(50);
_db = new QSqlDatabase(QSqlDatabase::addDatabase("QSQLITE", kSession));
_db->setDatabaseName(_databasePath);
_db->setConnectOptions("QSQLITE_ENABLE_SHARED_CACHE");
_valid = _db->open();
}
task->setProgress(100);
} else {
//-- Open imported set
QSqlDatabase* dbImport = new QSqlDatabase(QSqlDatabase::addDatabase("QSQLITE", kExportSession));
dbImport->setDatabaseName(task->path());
dbImport->setConnectOptions("QSQLITE_ENABLE_SHARED_CACHE");
if (dbImport->open()) {
QSqlQuery query(*dbImport);
//-- Prepare progress report
quint64 tileCount = 0;
quint64 currentCount = 0;
int lastProgress = -1;
QString s;
s = QString("SELECT COUNT(tileID) FROM Tiles");
if(query.exec(s)) {
if(query.next()) {
//-- Total number of tiles in imported database
tileCount = query.value(0).toULongLong();
}
}
if(tileCount) {
//-- Iterate Tile Sets
s = QString("SELECT * FROM TileSets ORDER BY defaultSet DESC, name ASC");
if(query.exec(s)) {
while(query.next()) {
QString name = query.value("name").toString();
quint64 setID = query.value("setID").toULongLong();
QString mapType = query.value("typeStr").toString();
double topleftLat = query.value("topleftLat").toDouble();
double topleftLon = query.value("topleftLon").toDouble();
double bottomRightLat = query.value("bottomRightLat").toDouble();
double bottomRightLon = query.value("bottomRightLon").toDouble();
int minZoom = query.value("minZoom").toInt();
int maxZoom = query.value("maxZoom").toInt();
int type = query.value("type").toInt();
quint32 numTiles = query.value("numTiles").toUInt();
int defaultSet = query.value("defaultSet").toInt();
quint64 insertSetID = _getDefaultTileSet();
//-- If not default set, create new one
if(!defaultSet) {
//-- Check if we have this tile set already
if(_findTileSetID(name, insertSetID)) {
int testCount = 0;
//-- Set with this name already exists. Make name unique.
while (true) {
QString testName;
testName.sprintf("%s %02d", name.toLatin1().data(), ++testCount);
if(!_findTileSetID(testName, insertSetID) || testCount > 99) {
name = testName;
break;
}
}
}
//-- Create new set
QSqlQuery cQuery(*_db);
cQuery.prepare("INSERT INTO TileSets("
"name, typeStr, topleftLat, topleftLon, bottomRightLat, bottomRightLon, minZoom, maxZoom, type, numTiles, defaultSet, date"
") VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
cQuery.addBindValue(name);
cQuery.addBindValue(mapType);
cQuery.addBindValue(topleftLat);
cQuery.addBindValue(topleftLon);
cQuery.addBindValue(bottomRightLat);
cQuery.addBindValue(bottomRightLon);
cQuery.addBindValue(minZoom);
cQuery.addBindValue(maxZoom);
cQuery.addBindValue(type);
cQuery.addBindValue(numTiles);
cQuery.addBindValue(defaultSet);
cQuery.addBindValue(QDateTime::currentDateTime().toTime_t());
if(!cQuery.exec()) {
task->setError("Error adding imported tile set to database");
break;
} else {
//-- Get just created (auto-incremented) setID
insertSetID = cQuery.lastInsertId().toULongLong();
}
}
//-- Find set tiles
QSqlQuery cQuery(*_db);
QSqlQuery subQuery(*dbImport);
QString sb = QString("SELECT * FROM Tiles WHERE tileID IN (SELECT A.tileID FROM SetTiles A JOIN SetTiles B ON A.tileID = B.tileID WHERE B.setID = %1 GROUP BY A.tileID HAVING COUNT(A.tileID) = 1)").arg(setID);
if(subQuery.exec(sb)) {
quint64 tilesFound = 0;
quint64 tilesSaved = 0;
_db->transaction();
while(subQuery.next()) {
tilesFound++;
QString hash = subQuery.value("hash").toString();
QString format = subQuery.value("format").toString();
QByteArray img = subQuery.value("tile").toByteArray();
int type = subQuery.value("type").toInt();
//-- Save tile
cQuery.prepare("INSERT INTO Tiles(hash, format, tile, size, type, date) VALUES(?, ?, ?, ?, ?, ?)");
cQuery.addBindValue(hash);
cQuery.addBindValue(format);
cQuery.addBindValue(img);
cQuery.addBindValue(img.size());
cQuery.addBindValue(type);
cQuery.addBindValue(QDateTime::currentDateTime().toTime_t());
if(cQuery.exec()) {
tilesSaved++;
quint64 importTileID = cQuery.lastInsertId().toULongLong();
QString s = QString("INSERT INTO SetTiles(tileID, setID) VALUES(%1, %2)").arg(importTileID).arg(insertSetID);
cQuery.prepare(s);
cQuery.exec();
currentCount++;
if(tileCount) {
int progress = (int)((double)currentCount / (double)tileCount * 100.0);
//-- Avoid calling this if (int) progress hasn't changed.
if(lastProgress != progress) {
lastProgress = progress;
task->setProgress(progress);
}
}
}
}
_db->commit();
if(tilesSaved) {
//-- Update tile count (if any added)
s = QString("SELECT COUNT(size) FROM Tiles A INNER JOIN SetTiles B on A.tileID = B.tileID WHERE B.setID = %1").arg(insertSetID);
if(cQuery.exec(s)) {
if(cQuery.next()) {
quint64 count = cQuery.value(0).toULongLong();
s = QString("UPDATE TileSets SET numTiles = %1 WHERE setID = %2").arg(count).arg(insertSetID);
cQuery.exec(s);
}
}
}
qint64 uniqueTiles = tilesFound - tilesSaved;
if((quint64)uniqueTiles < tileCount) {
tileCount -= uniqueTiles;
} else {
tileCount = 0;
}
//-- If there was nothing new in this set, remove it.
if(!tilesSaved && !defaultSet) {
qCDebug(QGCTileCacheLog) << "No unique tiles in" << name << "Removing it.";
_deleteTileSet(insertSetID);
}
}
}
} else {
task->setError("No tile set in database");
}
}
delete dbImport;
QSqlDatabase::removeDatabase(kExportSession);
if(!tileCount) {
task->setError("No unique tiles in imported database");
}
} else {
task->setError("Error opening import database");
}
}
task->setImportCompleted();
}
