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::createMessageTable()
  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 message ("
  66                              "id INTEGER PRIMARY KEY,"
  67                              "senderid INTEGER,"
  68                              "receiverid INTEGER,"
  69                              "text TEXT,"
  70                              "FOREIGN KEY(senderid) REFERENCES user(id),"
  71                              "FOREIGN KEY(receiverid) REFERENCES user(id)"
  72                              ")");
  73     }
  74
  75     return created;
  76 }
  77
  78 bool Database::createTagTable()
  79 {
  80     qDebug() << __PRETTY_FUNCTION__;
  81
  82     bool created = false;
  83
  84     if (m_database.isOpen()) {
  85
  86         QSqlQuery query;
  87         created = query.exec("CREATE TABLE IF NOT EXISTS tag ("
  88                              "id INTEGER PRIMARY KEY,"
  89                              "name VARCHAR(30) UNIQUE"
  90                              ")");
  91     }
  92
  93     return created;
  94 }
  95
  96 bool Database::createUserTable()
  97 {
  98     qDebug() << __PRETTY_FUNCTION__;
  99
 100     bool created = false;
 101
 102     if (m_database.isOpen()) {
 103
 104         QSqlQuery query;
 105         created = query.exec("CREATE TABLE IF NOT EXISTS user ("
 106                              "id INTEGER PRIMARY KEY,"
 107                              "name VARCHAR(50),"
 108                              "latitude REAL,"
 109                              "longitude REAL"
 110                              ")");
 111
 112         qWarning() << query.lastError().text();
 113     }
 114
 115     return created;
 116 }
 117
 118 bool Database::createUserTagTable()
 119 {
 120     qDebug() << __PRETTY_FUNCTION__;
 121
 122     bool created = false;
 123
 124     if (m_database.isOpen()) {
 125
 126         QSqlQuery query;
 127         created = query.exec("CREATE TABLE IF NOT EXISTS usertag ("
 128                              "userid INTEGER,"
 129                              "tagid INTEGER,"
 130                              "PRIMARY KEY(userid, tagid),"
 131                              "FOREIGN KEY(userid) REFERENCES user(id),"
 132                              "FOREIGN KEY(tagid) REFERENCES tag(id)"
 133                              ")");
 134     }
 135
 136     return created;
 137 }
 138
 139 QStringList Database::getInterestingPeople(qulonglong userId,
 140                                            const GeoCoordinate &southWestCoordinates,
 141                                            const GeoCoordinate &northEastCoordinates)
 142 {
 143     qDebug() << __PRETTY_FUNCTION__;
 144
 145     QStringList interestingPeopleId;
 146
 147     QSqlQuery query(QString("SELECT user.id, user.name FROM user WHERE "
 148                             "user.latitude >= '%1' AND user.latitude <= '%2' AND "
 149                             "user.longitude >= '%3' AND user.longitude <= '%4' AND user.id IN "
 150                             "(SELECT DISTINCT usertag.userid FROM usertag WHERE usertag.tagid IN "
 151                             "(SELECT usertag.tagid FROM usertag WHERE usertag.userid = '%5') AND "
 152                             "usertag.userid != '%6')")
 153                     .arg(southWestCoordinates.latitude()).arg(northEastCoordinates.latitude())
 154                     .arg(southWestCoordinates.longitude()).arg(northEastCoordinates.longitude())
 155                     .arg(userId).arg(userId));
 156
 157     qWarning() << query.lastQuery();
 158
 159     while (query.next())
 160         interestingPeopleId.append(query.value(0).toString());
 161
 162     return interestingPeopleId;
 163 }
 164
 165 QList<Notification> &Database::getNotifications(qulonglong userId)
 166 {
 167     qDebug() << __PRETTY_FUNCTION__;
 168
 169     m_notifications.clear();
 170
 171     QSqlQuery query(QString("SELECT message.id, message.senderid, user.name, message.text FROM "
 172                             "message, user WHERE message.receiverid = '%1' AND "
 173                             "message.senderid = user.id").arg(userId));
 174
 175     qWarning() << query.lastQuery();
 176     qWarning() << query.lastError().text();
 177
 178     while (query.next()) {
 179         qWarning() << query.value(1).toString();
 180         Notification notification;
 181         notification.setId(query.value(0).toString());
 182         notification.setSenderId(query.value(1).toString());
 183         notification.setTitle(query.value(2).toString());
 184         notification.setText(query.value(3).toString());
 185         m_notifications.append(notification);
 186     }
 187
 188     return m_notifications;
 189 }
 190
 191 QStringList Database::getTags(qulonglong userId)
 192 {
 193     qDebug() << __PRETTY_FUNCTION__;
 194
 195     QStringList tags;
 196
 197     QSqlQuery query(QString("SELECT tag.name FROM usertag, tag WHERE usertag.userid = '%1' "
 198                             "AND usertag.tagid = tag.id").arg(userId));
 199
 200     while (query.next())
 201         tags.append(query.value(0).toString());
 202
 203     return tags;
 204 }
 205
 206 bool Database::openDatabase()
 207 {
 208     qDebug() << __PRETTY_FUNCTION__;
 209
 210     m_database = QSqlDatabase::addDatabase("QSQLITE");
 211
 212     QString path(QDir::home().path());
 213     path.append(QDir::separator()).append("my.db.sqlite");
 214     path = QDir::toNativeSeparators(path);
 215     m_database.setDatabaseName(path);
 216
 217     return m_database.open();
 218 }