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 FROM tag WHERE name = '%1'")
  29                          .arg(tag));
  30
  31         //Tag already exists
  32         if (ret && tagQuery.next()) {
  33             tagId = tagQuery.value(0).toULongLong();
  34         }
  35         else {
  36             QSqlQuery tagInsertQuery;
  37             ret = tagInsertQuery.exec(QString("INSERT INTO tag VALUES(NULL, '%1')")
  38                              .arg(tag));
  39
  40             if (ret) {
  41                 tagId = tagInsertQuery.lastInsertId().toULongLong();
  42             }
  43         }
  44
  45         if (ret && (tagId != 0)) {
  46             QSqlQuery userTagQuery;
  47
  48             ret = userTagQuery.exec(QString("INSERT INTO usertag VALUES('%1', '%2')")
  49                                     .arg(userId).arg(tagId));
  50         }
  51     }
  52
  53     return ret;
  54 }
  55
  56 bool Database::createNotificationTable()
  57 {
  58     qDebug() << __PRETTY_FUNCTION__;
  59
  60     bool created = false;
  61
  62     if (m_database.isOpen()) {
  63
  64         QSqlQuery query;
  65         created = query.exec("CREATE TABLE IF NOT EXISTS notification ("
  66                              "id INTEGER PRIMARY KEY,"
  67                              "senderid INTEGER,"
  68                              "receiverid INTEGER,"
  69                              "type VARCHAR(10),"
  70                              "timestamp VARCHAR(20),"
  71                              "text TEXT,"
  72                              "FOREIGN KEY(senderid) REFERENCES user(id),"
  73                              "FOREIGN KEY(receiverid) REFERENCES user(id)"
  74                              ")");
  75     }
  76
  77     return created;
  78 }
  79
  80 bool Database::createTagTable()
  81 {
  82     qDebug() << __PRETTY_FUNCTION__;
  83
  84     bool created = false;
  85
  86     if (m_database.isOpen()) {
  87
  88         QSqlQuery query;
  89         created = query.exec("CREATE TABLE IF NOT EXISTS tag ("
  90                              "id INTEGER PRIMARY KEY,"
  91                              "name VARCHAR(30) UNIQUE"
  92                              ")");
  93     }
  94
  95     return created;
  96 }
  97
  98 bool Database::createUserTable()
  99 {
 100     qDebug() << __PRETTY_FUNCTION__;
 101
 102     bool created = false;
 103
 104     if (m_database.isOpen()) {
 105
 106         QSqlQuery query;
 107         created = query.exec("CREATE TABLE IF NOT EXISTS user ("
 108                              "id INTEGER PRIMARY KEY,"
 109                              "name VARCHAR(50),"
 110                              "latitude REAL,"
 111                              "longitude REAL,"
 112                              "image_url TEXT"
 113                              ")");
 114     }
 115
 116     return created;
 117 }
 118
 119 bool Database::createUserTagTable()
 120 {
 121     qDebug() << __PRETTY_FUNCTION__;
 122
 123     bool created = false;
 124
 125     if (m_database.isOpen()) {
 126
 127         QSqlQuery query;
 128         created = query.exec("CREATE TABLE IF NOT EXISTS usertag ("
 129                              "userid INTEGER,"
 130                              "tagid INTEGER,"
 131                              "PRIMARY KEY(userid, tagid),"
 132                              "FOREIGN KEY(userid) REFERENCES user(id),"
 133                              "FOREIGN KEY(tagid) REFERENCES tag(id)"
 134                              ")");
 135     }
 136
 137     return created;
 138 }
 139
 140 QByteArray Database::getInterestingPeople(qulonglong userId,
 141                                            const GeoCoordinate &southWestCoordinates,
 142                                            const GeoCoordinate &northEastCoordinates)
 143 {
 144     qDebug() << __PRETTY_FUNCTION__;
 145
 146     QSqlQuery query(QString("SELECT user.id, user.name, user.image_url FROM user WHERE "
 147                             "user.latitude >= '%1' AND user.latitude <= '%2' AND "
 148                             "user.longitude >= '%3' AND user.longitude <= '%4' AND user.id IN "
 149                             "(SELECT DISTINCT usertag.userid FROM usertag WHERE usertag.tagid IN "
 150                             "(SELECT usertag.tagid FROM usertag WHERE usertag.userid = '%5') AND "
 151                             "usertag.userid != '%6')")
 152                     .arg(southWestCoordinates.latitude()).arg(northEastCoordinates.latitude())
 153                     .arg(southWestCoordinates.longitude()).arg(northEastCoordinates.longitude())
 154                     .arg(userId).arg(userId));
 155
 156     QString result;
 157     result.append("{\"people\": [");
 158
 159     while (query.next()) {
 160         result.append("{");
 161         result.append("\"uid\": \"" + query.value(0).toString() + "\",");
 162         result.append("\"name\": \"" + query.value(1).toString() + "\",");
 163         result.append("\"image_url\": \"" + query.value(2).toString() + "\"");
 164         result.append("},");
 165     }
 166
 167     int lastComma = result.lastIndexOf(",");
 168     if (lastComma != -1)
 169         result.remove(result.lastIndexOf(","), 1);
 170
 171     result.append("]}");
 172
 173     return result.toUtf8();
 174 }
 175
 176 QList<Notification*> &Database::getNotifications(qulonglong userId)
 177 {
 178     qDebug() << __PRETTY_FUNCTION__;
 179
 180     m_notifications.clear();
 181
 182     QSqlQuery query(QString("SELECT message.id, message.senderid, user.name, message.text FROM "
 183                             "message, user WHERE message.receiverid = '%1' AND "
 184                             "message.senderid = user.id").arg(userId));
 185
 186     while (query.next()) {
 187         Notification *notification = new Notification();
 188         notification->setId(query.value(0).toString());
 189         notification->setSenderId(query.value(1).toString());
 190         notification->setTitle(query.value(2).toString());
 191         notification->setText(query.value(3).toString());
 192         m_notifications.append(notification);
 193     }
 194
 195     return m_notifications;
 196 }
 197
 198 QStringList Database::getTags(qulonglong userId)
 199 {
 200     qDebug() << __PRETTY_FUNCTION__;
 201
 202     QStringList tags;
 203
 204     QSqlQuery query(QString("SELECT tag.name FROM usertag, tag WHERE usertag.userid = '%1' "
 205                             "AND usertag.tagid = tag.id").arg(userId));
 206
 207     while (query.next())
 208         tags.append(query.value(0).toString());
 209
 210     return tags;
 211 }
 212
 213 bool Database::openDatabase()
 214 {
 215     qDebug() << __PRETTY_FUNCTION__;
 216
 217     m_database = QSqlDatabase::addDatabase("QSQLITE");
 218
 219     QString path(QDir::home().path());
 220     path.append(QDir::separator()).append("my.db.sqlite");
 221     path = QDir::toNativeSeparators(path);
 222     m_database.setDatabaseName(path);
 223
 224     return m_database.open();
 225 }