8 Database::Database(QObject *parent) :
11 qDebug() << __PRETTY_FUNCTION__;
16 qDebug() << __PRETTY_FUNCTION__;
19 bool Database::addTag(int userId, const QString &tag)
21 qDebug() << __PRETTY_FUNCTION__;
25 if (m_database.isOpen()) {
28 ret = tagQuery.exec(QString("INSERT INTO tag VALUES(NULL, '%1')")
31 qWarning() << tagQuery.lastError().number();
32 qWarning() << tagQuery.lastError().driverText();
33 qWarning() << tagQuery.lastError().databaseText();
35 if (ret || (tagQuery.lastError().number() == 19)) {
36 int tagId = tagQuery.lastInsertId().toInt();
38 QSqlQuery userTagQuery;
40 ret = userTagQuery.exec(QString("INSERT INTO usertag VALUES(NULL, '%1', '%2')")
41 .arg(userId).arg(tagId));
48 bool Database::createTagTable()
50 qDebug() << __PRETTY_FUNCTION__;
54 if (m_database.isOpen()) {
57 created = query.exec("CREATE TABLE IF NOT EXISTS tag ("
58 "id INTEGER PRIMARY KEY,"
59 "name VARCHAR(30) UNIQUE"
66 bool Database::createUserTagTable()
68 qDebug() << __PRETTY_FUNCTION__;
72 if (m_database.isOpen()) {
75 created = query.exec("CREATE TABLE IF NOT EXISTS usertag ("
76 "id INTEGER PRIMARY KEY,"
79 "FOREIGN KEY(tagid) REFERENCES tag(id)"
86 QStringList Database::getInterestingPeople(int userId)
88 qDebug() << __PRETTY_FUNCTION__;
90 QStringList interestingPeopleId;
92 QSqlQuery query(QString("SELECT DISTINCT ut.userid FROM usertag ut WHERE ut.id IN "
93 "(SELECT ut.id FROM usertag ut WHERE userid = '%1')").arg(userId));
96 interestingPeopleId.append(query.value(0).toString());
98 return interestingPeopleId;
101 QStringList Database::getTags(int userId)
103 qDebug() << __PRETTY_FUNCTION__;
107 QSqlQuery query(QString("SELECT t.name from usertag ut, tag t WHERE userid = '%1' AND "
108 "ut.tagid = t.id").arg(userId));
111 tags.append(query.value(0).toString());
116 bool Database::openDatabase()
118 qDebug() << __PRETTY_FUNCTION__;
120 m_database = QSqlDatabase::addDatabase("QSQLITE");
122 QString path(QDir::home().path());
123 path.append(QDir::separator()).append("my.db.sqlite");
124 path = QDir::toNativeSeparators(path);
125 m_database.setDatabaseName(path);
127 return m_database.open();
130 void Database::test()
132 qDebug() << __PRETTY_FUNCTION__;
136 QSqlQuery tagQuery(QString("SELECT * from tag"));
137 qWarning() << tagQuery.lastError().text();
138 qWarning() << "id" << "name";
139 while (tagQuery.next()) {
140 qWarning() << tagQuery.value(0).toString() << tagQuery.value(1).toString();
143 QSqlQuery userTagQuery(QString("SELECT * from usertag"));
144 qWarning() << userTagQuery.lastError().text();
145 qWarning() << "id" << "userid" << "tagid";
146 while (userTagQuery.next()) {
147 qWarning() << userTagQuery.value(0).toString() <<
148 userTagQuery.value(1).toString() << userTagQuery.value(2).toString();