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 QByteArray Database::getNotifications(qulonglong userId)
 177 {
 178     qDebug() << __PRETTY_FUNCTION__;
 179
 180     QSqlQuery query(QString("SELECT notification.id, notification.senderid, user.name, "
 181                             "user.image_url, notification.timestamp, notification.text "
 182                             "FROM notification, user WHERE notification.receiverid = '%1' "
 183                             "AND notification.senderid = user.id").arg(userId));
 184
 185     QString result;
 186     result.append("{\"messages\": [");
 187
 188     while (query.next()) {
 189         result.append("{");
 190         result.append("\"id\": \"" + query.value(0).toString() + "\",");
 191         result.append("\"sender_id\": \"" + query.value(1).toString() + "\",");
 192         result.append("\"sender_name\": \"" + query.value(2).toString() + "\",");
 193         result.append("\"image_url\": \"" + query.value(3).toString() + "\",");
 194         result.append("\"timestamp\": \"" + query.value(4).toString() + "\",");
 195         result.append("\"text\": \"" + query.value(5).toString() + "\"");
 196         result.append("},");
 197     }
 198
 199     int lastComma = result.lastIndexOf(",");
 200     if (lastComma != -1)
 201         result.remove(result.lastIndexOf(","), 1);
 202
 203     result.append("]}");
 204
 205     return result.toUtf8();
 206 }
 207
 208 QStringList Database::getTags(qulonglong userId)
 209 {
 210     qDebug() << __PRETTY_FUNCTION__;
 211
 212     QStringList tags;
 213
 214     QSqlQuery query(QString("SELECT tag.name FROM usertag, tag WHERE usertag.userid = '%1' "
 215                             "AND usertag.tagid = tag.id").arg(userId));
 216
 217     while (query.next())
 218         tags.append(query.value(0).toString());
 219
 220     return tags;
 221 }
 222
 223 bool Database::openDatabase()
 224 {
 225     qDebug() << __PRETTY_FUNCTION__;
 226
 227     m_database = QSqlDatabase::addDatabase("QSQLITE");
 228
 229     QString path(QDir::home().path());
 230     path.append(QDir::separator()).append("my.db.sqlite");
 231     path = QDir::toNativeSeparators(path);
 232     m_database.setDatabaseName(path);
 233
 234     return m_database.open();
 235 }
 236
 237 bool Database::sendMessage(qulonglong senderId, qulonglong receiverId, const QString &message)
 238 {
 239     qDebug() << __PRETTY_FUNCTION__;
 240
 241     QSqlQuery sendMessageQuery;
 242
 243     return sendMessageQuery.exec(QString("INSERT INTO notification VALUES("
 244             "NULL, '%1', '%2', \"message\", strftime('%s','now'), '%3')")
 245                             .arg(senderId).arg(receiverId).arg(message));
 246 }