vcs.maemo.org Git - situare/blob - src/situareservice/database.cpp

   1 #include <QDebug>
   2 #include <QDir>
   3 #include <QSqlQuery>
   4 #include <QVariant>
   5
   6 #include "database.h"
   7
   8 Database::Database(QObject *parent) :
   9     QObject(parent)
  10 {
  11     qDebug() << __PRETTY_FUNCTION__;
  12 }
  13
  14 Database::~Database()
  15 {
  16     qDebug() << __PRETTY_FUNCTION__;
  17 }
  18
  19 bool Database::addTag(qulonglong userId, const QString &tag)
  20 {
  21     qDebug() << __PRETTY_FUNCTION__;
  22
  23     bool ret = false;
  24     qulonglong tagId = 0;
  25
  26     if (m_database.isOpen()) {
  27         QSqlQuery tagQuery;
  28         ret = tagQuery.exec(QString("SELECT id, count FROM tag WHERE name = '%1'")
  29                          .arg(tag));
  30
  31         //Tag already exists
  32         if (ret && tagQuery.next()) {
  33             qWarning() << tagQuery.value(1).toInt();
  34             int count = tagQuery.value(1).toInt() + 1;
  35             qWarning() << count;
  36             QSqlQuery tagUpdateQuery;
  37             tagUpdateQuery.exec(QString("UPDATE tag SET count = '%1' WHERE id = '%2'")
  38                              .arg(count).arg(tagQuery.value(0).toString()));
  39             tagId = tagQuery.value(0).toULongLong();
  40             qWarning() << tagUpdateQuery.lastQuery();
  41         }
  42         else {
  43             QSqlQuery tagInsertQuery;
  44             ret = tagInsertQuery.exec(QString("INSERT INTO tag VALUES(NULL, '%1')")
  45                              .arg(tag));
  46
  47             if (ret) {
  48                 tagId = tagInsertQuery.lastInsertId().toULongLong();
  49             }
  50         }
  51
  52         if (ret && (tagId != 0)) {
  53             QSqlQuery userTagQuery;
  54
  55             ret = userTagQuery.exec(QString("INSERT INTO usertag VALUES('%1', '%2')")
  56                                     .arg(userId).arg(tagId));
  57         }
  58     }
  59
  60     return ret;
  61 }
  62
  63 bool Database::createNotificationTable()
  64 {
  65     qDebug() << __PRETTY_FUNCTION__;
  66
  67     bool created = false;
  68
  69     if (m_database.isOpen()) {
  70
  71         QSqlQuery query;
  72         created = query.exec("CREATE TABLE IF NOT EXISTS notification ("
  73                              "id INTEGER PRIMARY KEY,"
  74                              "senderid INTEGER,"
  75                              "receiverid INTEGER,"
  76                              "timestamp VARCHAR(20),"
  77                              "text TEXT,"
  78                              "latitude REAL,"
  79                              "longitude REAL,"
  80                              "FOREIGN KEY(senderid) REFERENCES user(id),"
  81                              "FOREIGN KEY(receiverid) REFERENCES user(id)"
  82                              ")");
  83     }
  84
  85     return created;
  86 }
  87
  88 bool Database::createTagTable()
  89 {
  90     qDebug() << __PRETTY_FUNCTION__;
  91
  92     bool created = false;
  93
  94     if (m_database.isOpen()) {
  95
  96         QSqlQuery query;
  97         created = query.exec("CREATE TABLE IF NOT EXISTS tag ("
  98                              "id INTEGER PRIMARY KEY,"
  99                              "name VARCHAR(30) UNIQUE"
 100                              ")");
 101     }
 102
 103     return created;
 104 }
 105
 106 bool Database::createUserTable()
 107 {
 108     qDebug() << __PRETTY_FUNCTION__;
 109
 110     bool created = false;
 111
 112     if (m_database.isOpen()) {
 113
 114         QSqlQuery query;
 115         created = query.exec("CREATE TABLE IF NOT EXISTS user ("
 116                              "id INTEGER PRIMARY KEY,"
 117                              "name VARCHAR(50),"
 118                              "latitude REAL,"
 119                              "longitude REAL,"
 120                              "image_url TEXT"
 121                              ")");
 122     }
 123
 124     return created;
 125 }
 126
 127 bool Database::createUserTagTable()
 128 {
 129     qDebug() << __PRETTY_FUNCTION__;
 130
 131     bool created = false;
 132
 133     if (m_database.isOpen()) {
 134
 135         QSqlQuery query;
 136         created = query.exec("CREATE TABLE IF NOT EXISTS usertag ("
 137                              "userid INTEGER,"
 138                              "tagid INTEGER,"
 139                              "PRIMARY KEY(userid, tagid),"
 140                              "FOREIGN KEY(userid) REFERENCES user(id),"
 141                              "FOREIGN KEY(tagid) REFERENCES tag(id)"
 142                              ")");
 143     }
 144
 145     return created;
 146 }
 147
 148 QByteArray Database::getInterestingPeopleByTag(qulonglong userId, const QString &tag)
 149 {
 150     qDebug() << __PRETTY_FUNCTION__;
 151
 152     //People start
 153     QString result;
 154     result.append("{\"people\": {");
 155
 156     QHash<QString, QString> tags;
 157
 158     //Get tags
 159     QSqlQuery tagQuery(QString("SELECT DISTINCT usertag.userid, tag.name FROM usertag, tag WHERE "
 160                                    "usertag.tagid = tag.id AND usertag.userid != '%1' AND "
 161                                    "tag.name LIKE '\%%2\%'").arg(userId).arg(tag));
 162
 163     while (tagQuery.next()) {
 164         QString userId = tagQuery.value(0).toString();
 165         QString tagName = tagQuery.value(1).toString();
 166
 167         if (tags.contains(userId)) {
 168             QString value = tags.take(userId);
 169             value.append(", \"" + tagName + "\"");
 170             tags.insert(userId, value);
 171         }
 172         else {
 173             tags.insert(userId, QString("\"" + tagName + "\""));
 174         }
 175     }
 176
 177     QStringList userIds;
 178     QHashIterator<QString, QString> i(tags);
 179     while (i.hasNext())
 180         userIds.append(i.next().key());
 181
 182     //Get friends
 183     result.append("\"friends\": [");
 184
 185     QSqlQuery friendQuery(QString("SELECT user.id, user.name, user.image_url, user.latitude, "
 186                                   "user.longitude FROM user WHERE "
 187                                   "user.id IN (") + userIds.join(", ") + QString(") AND "
 188                                   "user.id IN "
 189                                   "(SELECT friend.uid2 FROM friend WHERE friend.uid1 = '%1' "
 190                                   "UNION "
 191                                   "SELECT friend.uid1 FROM friend WHERE friend.uid2 = '%1')")
 192                 .arg(userId));
 193
 194     bool friendRemoveLast = false;
 195
 196     while (friendQuery.next()) {
 197         friendRemoveLast = true;
 198         result.append("{");
 199         result.append("\"uid\": \"" + friendQuery.value(0).toString() + "\",");
 200         result.append("\"name\": \"" + friendQuery.value(1).toString() + "\",");
 201         result.append("\"image_url\": \"" + friendQuery.value(2).toString() + "\",");
 202         result.append("\"latitude\": \"" + friendQuery.value(3).toString() + "\",");
 203         result.append("\"longitude\": \"" + friendQuery.value(4).toString() + "\",");
 204         result.append("\"tags\": [" + tags.value(friendQuery.value(0).toString()) + "]");
 205         result.append("},");
 206     }
 207
 208     if (friendRemoveLast) {
 209         int friendLastComma = result.lastIndexOf(",");
 210         if (friendLastComma != -1)
 211             result.remove(result.lastIndexOf(","), 1);
 212     }
 213
 214     result.append("],");
 215
 216     //Get others
 217     result.append("\"others\": [");
 218
 219     QSqlQuery otherQuery(QString("SELECT user.id, user.name, user.image_url FROM user WHERE "
 220                                   "user.id IN (") + userIds.join(", ") + QString(") AND "
 221                                   "user.id NOT IN "
 222                                   "(SELECT friend.uid2 FROM friend WHERE friend.uid1 = '%1' "
 223                                   "UNION "
 224                                   "SELECT friend.uid1 FROM friend WHERE friend.uid2 = '%1')")
 225                 .arg(userId));
 226
 227     bool otherRemoveLast = false;
 228
 229     while (otherQuery.next()) {
 230         otherRemoveLast = true;
 231         result.append("{");
 232         result.append("\"uid\": \"" + otherQuery.value(0).toString() + "\",");
 233         result.append("\"name\": \"" + otherQuery.value(1).toString() + "\",");
 234         result.append("\"image_url\": \"" + otherQuery.value(2).toString() + "\",");;
 235         result.append("\"tags\": [" + tags.value(otherQuery.value(0).toString()) + "]");
 236         result.append("},");
 237     }
 238
 239     if (otherRemoveLast) {
 240         int otherLastComma = result.lastIndexOf(",");
 241         if (otherLastComma != -1)
 242             result.remove(result.lastIndexOf(","), 1);
 243     }
 244
 245     result.append("]");
 246
 247     result.append("}}");
 248
 249     return result.toUtf8();
 250 }
 251
 252
 253 QByteArray Database::getInterestingPeople(qulonglong userId,
 254                                            const GeoCoordinate &southWestCoordinates,
 255                                            const GeoCoordinate &northEastCoordinates)
 256 {
 257     qDebug() << __PRETTY_FUNCTION__;
 258
 259     //People start
 260     QString result;
 261     result.append("{\"people\": {");
 262
 263     QHash<QString, QString> tags;
 264
 265     //Get tags
 266     QSqlQuery tagQuery(QString("SELECT usertag.userid, tag.name FROM usertag, tag WHERE "
 267                                "usertag.tagid IN (SELECT usertag.tagid FROM usertag WHERE "
 268                                "usertag.userid = '%1') AND usertag.userid != '%2' "
 269                                "AND usertag.tagid = tag.id").arg(userId).arg(userId));
 270
 271     while (tagQuery.next()) {
 272         QString userId = tagQuery.value(0).toString();
 273         QString tagName = tagQuery.value(1).toString();
 274
 275         if (tags.contains(userId)) {
 276             QString value = tags.take(userId);
 277             value.append(", \"" + tagName + "\"");
 278             tags.insert(userId, value);
 279         }
 280         else {
 281             tags.insert(userId, QString("\"" + tagName + "\""));
 282         }
 283     }
 284
 285     QStringList userIds;
 286     QHashIterator<QString, QString> i(tags);
 287     while (i.hasNext())
 288         userIds.append(i.next().key());
 289
 290     //Get friends
 291     result.append("\"friends\": [");
 292
 293     QSqlQuery friendQuery(QString("SELECT user.id, user.name, user.image_url, user.latitude, "
 294                                   "user.longitude FROM user WHERE "
 295                                   "user.id IN (") + userIds.join(", ") + QString(") AND "
 296                                   "user.latitude >= '%1' AND user.latitude < '%2' AND "
 297                                   "user.longitude >= '%3' AND user.longitude < '%4' AND user.id IN "
 298                                   "(SELECT friend.uid2 FROM friend WHERE friend.uid1 = '%5' "
 299                                   "UNION "
 300                                   "SELECT friend.uid1 FROM friend WHERE friend.uid2 = '%5')")
 301                 .arg(southWestCoordinates.latitude()).arg(northEastCoordinates.latitude())
 302                 .arg(southWestCoordinates.longitude()).arg(northEastCoordinates.longitude())
 303                 .arg(userId));
 304
 305     bool friendRemoveLast = false;
 306
 307     while (friendQuery.next()) {
 308         friendRemoveLast = true;
 309         result.append("{");
 310         result.append("\"uid\": \"" + friendQuery.value(0).toString() + "\",");
 311         result.append("\"name\": \"" + friendQuery.value(1).toString() + "\",");
 312         result.append("\"image_url\": \"" + friendQuery.value(2).toString() + "\",");
 313         result.append("\"latitude\": \"" + friendQuery.value(3).toString() + "\",");
 314         result.append("\"longitude\": \"" + friendQuery.value(4).toString() + "\",");
 315         result.append("\"tags\": [" + tags.value(friendQuery.value(0).toString()) + "]");
 316         result.append("},");
 317     }
 318
 319     if (friendRemoveLast) {
 320         int friendLastComma = result.lastIndexOf(",");
 321         if (friendLastComma != -1)
 322             result.remove(result.lastIndexOf(","), 1);
 323     }
 324
 325     result.append("],");
 326
 327     //Get others
 328     result.append("\"others\": [");
 329
 330     QSqlQuery otherQuery(QString("SELECT user.id, user.name, user.image_url FROM user WHERE "
 331                                   "user.id IN (") + userIds.join(", ") + QString(") AND "
 332                                   "user.latitude >= '%1' AND user.latitude < '%2' AND "
 333                                   "user.longitude >= '%3' AND user.longitude < '%4' AND user.id "
 334                                   "NOT IN "
 335                                   "(SELECT friend.uid2 FROM friend WHERE friend.uid1 = '%5' "
 336                                   "UNION "
 337                                   "SELECT friend.uid1 FROM friend WHERE friend.uid2 = '%5')")
 338                 .arg(southWestCoordinates.latitude()).arg(northEastCoordinates.latitude())
 339                 .arg(southWestCoordinates.longitude()).arg(northEastCoordinates.longitude())
 340                 .arg(userId));
 341
 342     bool otherRemoveLast = false;
 343
 344     while (otherQuery.next()) {
 345         otherRemoveLast = true;
 346         result.append("{");
 347         result.append("\"uid\": \"" + otherQuery.value(0).toString() + "\",");
 348         result.append("\"name\": \"" + otherQuery.value(1).toString() + "\",");
 349         result.append("\"image_url\": \"" + otherQuery.value(2).toString() + "\",");;
 350         result.append("\"tags\": [" + tags.value(otherQuery.value(0).toString()) + "]");
 351         result.append("},");
 352     }
 353
 354     if (otherRemoveLast) {
 355         int otherLastComma = result.lastIndexOf(",");
 356         if (otherLastComma != -1)
 357             result.remove(result.lastIndexOf(","), 1);
 358     }
 359
 360     result.append("]");
 361
 362     result.append("}}");
 363
 364     return result.toUtf8();
 365 }
 366
 367 QByteArray Database::getNotifications(qulonglong userId)
 368 {
 369     qDebug() << __PRETTY_FUNCTION__;
 370
 371     QString result;
 372     result.append("{\"messages\": {");
 373
 374     //Received
 375     QSqlQuery receivedQuery(QString("SELECT notification.id, notification.senderid, "
 376                             "notification.receiverid, user.name, user.image_url, "
 377                             "notification.timestamp, notification.text, "
 378                             "notification.latitude, notification.longitude "
 379                             "FROM notification, user WHERE notification.receiverid = '%1' AND "
 380                             "notification.senderid = user.id "
 381                             "ORDER BY notification.timestamp DESC")
 382                     .arg(userId));
 383
 384     result.append("\"received\": [");
 385
 386     bool receivedRemoveLast = false;
 387
 388     while (receivedQuery.next()) {
 389         receivedRemoveLast = true;
 390         result.append("{");
 391         result.append("\"id\": \"" + receivedQuery.value(0).toString() + "\",");
 392         result.append("\"sender_id\": \"" + receivedQuery.value(1).toString() + "\",");
 393         result.append("\"receiver_id\": \"" + receivedQuery.value(2).toString() + "\",");
 394         result.append("\"sender_name\": \"" + receivedQuery.value(3).toString() + "\",");
 395         result.append("\"image_url\": \"" + receivedQuery.value(4).toString() + "\",");
 396         result.append("\"timestamp\": \"" + receivedQuery.value(5).toString() + "\",");
 397         result.append("\"text\": \"" + receivedQuery.value(6).toString() + "\",");
 398         result.append("\"address\": \"" + QString("Address 13, Oulu") + "\",");
 399         result.append("\"latitude\": \"" + receivedQuery.value(7).toString() + "\",");
 400         result.append("\"longitude\": \"" + receivedQuery.value(8).toString() + "\"");
 401         result.append("},");
 402     }
 403
 404     if (receivedRemoveLast) {
 405         int receivedLastComma = result.lastIndexOf(",");
 406         if (receivedLastComma != -1)
 407             result.remove(result.lastIndexOf(","), 1);
 408     }
 409
 410     result.append("],");
 411
 412     //Sent
 413     QSqlQuery sentQuery(QString("SELECT notification.id, notification.senderid, "
 414                                 "notification.receiverid, user.name, user.image_url, "
 415                                 "notification.timestamp, notification.text, "
 416                                 "notification.latitude, notification.longitude "
 417                                 "FROM notification, user WHERE notification.senderid = '%1' AND "
 418                                 "notification.receiverid = user.id "
 419                                 "ORDER BY notification.timestamp DESC")
 420                     .arg(userId));
 421
 422     result.append("\"sent\": [");
 423
 424     bool sentRemoveLast = false;
 425
 426     while (sentQuery.next()) {
 427         sentRemoveLast = true;
 428         result.append("{");
 429         result.append("\"id\": \"" + sentQuery.value(0).toString() + "\",");
 430         result.append("\"sender_id\": \"" + sentQuery.value(1).toString() + "\",");
 431         result.append("\"receiver_id\": \"" + sentQuery.value(2).toString() + "\",");
 432         result.append("\"sender_name\": \"" + sentQuery.value(3).toString() + "\",");
 433         result.append("\"image_url\": \"" + sentQuery.value(4).toString() + "\",");
 434         result.append("\"timestamp\": \"" + sentQuery.value(5).toString() + "\",");
 435         result.append("\"text\": \"" + sentQuery.value(6).toString() + "\",");
 436         result.append("\"address\": \"" + QString("Address 13, Oulu") + "\",");
 437         result.append("\"latitude\": \"" + sentQuery.value(7).toString() + "\",");
 438         result.append("\"longitude\": \"" + sentQuery.value(8).toString() + "\"");
 439         result.append("},");
 440     }
 441
 442     if (sentRemoveLast) {
 443         int lastComma = result.lastIndexOf(",");
 444         if (lastComma != -1)
 445             result.remove(result.lastIndexOf(","), 1);
 446     }
 447
 448     result.append("]");
 449
 450     result.append("}}");
 451
 452     return result.toUtf8();
 453 }
 454
 455 QByteArray Database::getPopularTags()
 456 {
 457     qDebug() << __PRETTY_FUNCTION__;
 458
 459     QSqlQuery query(QString("SELECT tag.id, tag.name FROM tag ORDER BY count DESC LIMIT 10"));
 460
 461     QString result;
 462     result.append("{\"popular_tags\": [");
 463
 464     while (query.next()) {
 465         result.append("{");
 466         result.append("\"id\": \"" + query.value(0).toString() + "\",");
 467         result.append("\"name\": \"" + query.value(1).toString() + "\"");
 468         result.append("},");
 469     }
 470
 471     int lastComma = result.lastIndexOf(",");
 472     if (lastComma != -1)
 473         result.remove(result.lastIndexOf(","), 1);
 474
 475     result.append("]");
 476
 477     return result.toUtf8();
 478 }
 479
 480 QHash<QString, QString> Database::getTags(qulonglong userId)
 481 {
 482     qDebug() << __PRETTY_FUNCTION__;
 483
 484     QHash<QString, QString> tags;
 485
 486     QSqlQuery query(QString("SELECT tag.id, tag.name FROM usertag, tag WHERE usertag.userid = '%1' "
 487                             "AND usertag.tagid = tag.id").arg(userId));
 488
 489     while (query.next())
 490         tags.insert(query.value(0).toString(), query.value(1).toString());
 491
 492     return tags;
 493 }
 494
 495 bool Database::openDatabase()
 496 {
 497     qDebug() << __PRETTY_FUNCTION__;
 498
 499     m_database = QSqlDatabase::addDatabase("QSQLITE");
 500
 501     QString path(QDir::home().path());
 502     path.append(QDir::separator()).append("my.db.sqlite");
 503     path = QDir::toNativeSeparators(path);
 504     m_database.setDatabaseName(path);
 505
 506     return m_database.open();
 507 }
 508
 509 bool Database::removeMessage(qulonglong userId, const QString &id)
 510 {
 511     qDebug() << __PRETTY_FUNCTION__;
 512
 513     QSqlQuery removeMessageQuery;
 514
 515     return removeMessageQuery.exec(QString("DELETE FROM notification WHERE "
 516                                            "(notification.receiverid = '%1' OR "
 517                                            "notification.senderid = '%1') AND "
 518                                            "notification.id = '%2'").arg(userId).arg(id));
 519 }
 520
 521 bool Database::removeTags(qulonglong userId, const QStringList &tags)
 522 {
 523     qDebug() << __PRETTY_FUNCTION__;
 524
 525     QSqlQuery removeTagsQuery;
 526
 527     return removeTagsQuery.exec(QString("DELETE FROM usertag WHERE usertag.userid = "
 528                                          "'%1' AND usertag.tagid IN (").arg(userId) +
 529                                  tags.join(", ") + ")");
 530 }
 531
 532 bool Database::sendMessage(qulonglong senderId, qulonglong receiverId, const QString &message,
 533                            const GeoCoordinate &coordinates)
 534 {
 535     qDebug() << __PRETTY_FUNCTION__;
 536
 537     QSqlQuery sendMessageQuery;
 538
 539     bool returnValue;
 540
 541     if (coordinates.isValid()) {
 542         returnValue = sendMessageQuery.exec(QString("INSERT INTO notification VALUES("
 543                                     "NULL, '%1', '%2', strftime('%s','now'), '%3', '%4', '%5')")
 544                             .arg(senderId).arg(receiverId).arg(message).arg(coordinates.latitude()).
 545                             arg(coordinates.longitude()));
 546     } else {
 547         returnValue = sendMessageQuery.exec(QString("INSERT INTO notification VALUES("
 548                                     "NULL, '%1', '%2', strftime('%s','now'), '%3', NULL, NULL)")
 549                             .arg(senderId).arg(receiverId).arg(message));
 550     }
 551
 552     return returnValue;
 553 }