signon  8.58
credentialsdb.cpp
Go to the documentation of this file.
00001 /* -*- Mode: C++; indent-tabs-mode: nil; c-basic-offset: 4 -*- */
00002 /*
00003  * This file is part of signon
00004  *
00005  * Copyright (C) 2009-2010 Nokia Corporation.
00006  * Copyright (C) 2012 Canonical Ltd.
00007  *
00008  * Contact: Aurel Popirtac <ext-aurel.popirtac@nokia.com>
00009  * Contact: Alberto Mardegan <alberto.mardegan@canonical.com>
00010  *
00011  * This library is free software; you can redistribute it and/or
00012  * modify it under the terms of the GNU Lesser General Public License
00013  * version 2.1 as published by the Free Software Foundation.
00014  *
00015  * This library is distributed in the hope that it will be useful, but
00016  * WITHOUT ANY WARRANTY; without even the implied warranty of
00017  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
00018  * Lesser General Public License for more details.
00019  *
00020  * You should have received a copy of the GNU Lesser General Public
00021  * License along with this library; if not, write to the Free Software
00022  * Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA
00023  * 02110-1301 USA
00024  */
00025 
00026 #include "credentialsdb.h"
00027 #include "credentialsdb_p.h"
00028 #include "signond-common.h"
00029 #include "signonidentityinfo.h"
00030 #include "signonsessioncoretools.h"
00031 
00032 #define INIT_ERROR() ErrorMonitor errorMonitor(this)
00033 #define RETURN_IF_NO_SECRETS_DB(retval) \
00034     if (!isSecretsDBOpen()) { \
00035         TRACE() << "Secrets DB is not available"; \
00036         _lastError = noSecretsDB; return retval; \
00037     }
00038 
00039 #define S(s) QLatin1String(s)
00040 
00041 namespace SignonDaemonNS {
00042 
00043 static const QString driver = QLatin1String("QSQLITE");
00044 
00045 bool SecretsCache::lookupCredentials(quint32 id,
00046                                      QString &username,
00047                                      QString &password) const
00048 {
00049     QHash<quint32, AuthCache>::const_iterator i;
00050 
00051     i = m_cache.find(id);
00052     if (i == m_cache.end()) return false;
00053 
00054     username = i->m_username;
00055     password = i->m_password;
00056     return true;
00057 }
00058 
00059 QVariantMap SecretsCache::lookupData(quint32 id, quint32 method) const
00060 {
00061     return m_cache.value(id).m_blobData.value(method);
00062 }
00063 
00064 void SecretsCache::updateCredentials(quint32 id,
00065                                      const QString &username,
00066                                      const QString &password,
00067                                      bool storePassword)
00068 {
00069     if (id == 0) return;
00070 
00071     AuthCache &credentials = m_cache[id];
00072     credentials.m_username = username;
00073     credentials.m_password = password;
00074     credentials.m_storePassword = storePassword;
00075 }
00076 
00077 void SecretsCache::updateData(quint32 id, quint32 method,
00078                               const QVariantMap &data)
00079 {
00080     if (id == 0) return;
00081 
00082     AuthCache &credentials = m_cache[id];
00083     credentials.m_blobData[method] = data;
00084 }
00085 
00086 void
00087 SecretsCache::storeToDB(SignOn::AbstractSecretsStorage *secretsStorage) const
00088 {
00089     if (m_cache.isEmpty()) return;
00090 
00091     TRACE() << "Storing cached credentials into permanent storage";
00092 
00093     QHash<quint32, AuthCache>::const_iterator i;
00094     for (i = m_cache.constBegin();
00095          i != m_cache.constEnd();
00096          i++) {
00097         quint32 id = i.key();
00098         const AuthCache &cache = i.value();
00099 
00100         /* Store the credentials */
00101         QString password = cache.m_storePassword ?
00102             cache.m_password : QString();
00103         if (!cache.m_username.isEmpty() || !password.isEmpty()) {
00104             secretsStorage->updateCredentials(id,
00105                                               cache.m_username,
00106                                               password);
00107         }
00108 
00109         /* Store any binary blobs */
00110         QHash<quint32, QVariantMap>::const_iterator j;
00111         for (j = cache.m_blobData.constBegin();
00112              j != cache.m_blobData.constEnd();
00113              j++) {
00114             quint32 method = j.key();
00115             secretsStorage->storeData(id, method, j.value());
00116         }
00117     }
00118 }
00119 
00120 void SecretsCache::clear()
00121 {
00122     m_cache.clear();
00123 }
00124 
00125 SqlDatabase::SqlDatabase(const QString &databaseName,
00126                          const QString &connectionName,
00127                          int version):
00128     m_lastError(SignOn::CredentialsDBError()),
00129     m_version(version),
00130     m_database(QSqlDatabase::addDatabase(driver, connectionName))
00131 
00132 {
00133     TRACE() << "Supported Drivers:" << this->supportedDrivers();
00134     TRACE() << "DATABASE NAME [" << databaseName << "]";
00135 
00136     m_database.setDatabaseName(databaseName);
00137 }
00138 
00139 SqlDatabase::~SqlDatabase()
00140 {
00141     m_database.commit();
00142     m_database.close();
00143 }
00144 
00145 bool SqlDatabase::init()
00146 {
00147     if (!connect())
00148         return false;
00149 
00150     TRACE() <<  "Database connection succeeded.";
00151 
00152     if (!hasTables()) {
00153         TRACE() << "Creating SQL table structure...";
00154         if (!createTables())
00155             return false;
00156 
00157         if (!SqlDatabase::updateDB(m_version))
00158             BLAME() << "Failed to set database version to: " << m_version
00159                     << ".This could lead to data loss.";
00160     } else {
00161         TRACE() << "SQL table structure already created...";
00162         // check the DB version
00163         QSqlQuery q = exec(S("PRAGMA user_version"));
00164         int oldVersion = q.first() ? q.value(0).toInt() : 0;
00165         if (oldVersion < m_version)
00166             updateDB(oldVersion);
00167     }
00168 
00169     return true;
00170 }
00171 
00172 bool SqlDatabase::updateDB(int version)
00173 {
00174     TRACE() << "Update DB from version " << version << " to " << m_version;
00175     exec(QString::fromLatin1("PRAGMA user_version = %1").arg(m_version));
00176     return true;
00177 }
00178 
00179 bool SqlDatabase::connect()
00180 {
00181     if (!m_database.open()) {
00182         TRACE() << "Could not open database connection.\n";
00183         setLastError(m_database.lastError());
00184         return false;
00185     }
00186     return true;
00187 }
00188 
00189 void SqlDatabase::disconnect()
00190 {
00191     m_database.close();
00192 }
00193 
00194 bool SqlDatabase::startTransaction()
00195 {
00196     return m_database.transaction();
00197 }
00198 
00199 bool SqlDatabase::commit()
00200 {
00201     return m_database.commit();
00202 }
00203 
00204 void SqlDatabase::rollback()
00205 {
00206     if (!m_database.rollback())
00207         TRACE() << "Rollback failed, db data integrity could be compromised.";
00208 }
00209 
00210 QSqlQuery SqlDatabase::exec(const QString &queryStr)
00211 {
00212     QSqlQuery query(QString(), m_database);
00213 
00214     if (!query.prepare(queryStr))
00215         TRACE() << "Query prepare warning: " << query.lastQuery();
00216 
00217     if (!query.exec()) {
00218         TRACE() << "Query exec error: " << query.lastQuery();
00219         setLastError(query.lastError());
00220         TRACE() << errorInfo(query.lastError());
00221     } else
00222         m_lastError.clear();
00223 
00224     return query;
00225 }
00226 
00227 QSqlQuery SqlDatabase::exec(QSqlQuery &query)
00228 {
00229 
00230     if (!query.exec()) {
00231         TRACE() << "Query exec error: " << query.lastQuery();
00232         setLastError(query.lastError());
00233         TRACE() << errorInfo(query.lastError());
00234     } else
00235         m_lastError.clear();
00236 
00237     return query;
00238 }
00239 
00240 
00241 bool SqlDatabase::transactionalExec(const QStringList &queryList)
00242 {
00243     if (!startTransaction()) {
00244         setLastError(m_database.lastError());
00245         TRACE() << "Could not start transaction";
00246         return false;
00247     }
00248 
00249     bool allOk = true;
00250     foreach (QString queryStr, queryList) {
00251         TRACE() << QString::fromLatin1("TRANSACT Query [%1]").arg(queryStr);
00252         QSqlQuery query = exec(queryStr);
00253 
00254         if (errorOccurred()) {
00255             allOk = false;
00256             break;
00257         }
00258     }
00259 
00260     if (allOk && commit()) {
00261         TRACE() << "Commit SUCCEEDED.";
00262         return true;
00263     } else {
00264         rollback();
00265     }
00266 
00267     TRACE() << "Transactional exec FAILED!";
00268     return false;
00269 }
00270 
00271 SignOn::CredentialsDBError SqlDatabase::lastError() const
00272 {
00273     return m_lastError;
00274 }
00275 
00276 void SqlDatabase::setLastError(const QSqlError &sqlError)
00277 {
00278     if (sqlError.isValid()) {
00279         if (sqlError.type() == QSqlError::ConnectionError) {
00280             m_lastError.setType(SignOn::CredentialsDBError::ConnectionError);
00281         } else {
00282             m_lastError.setType(SignOn::CredentialsDBError::StatementError);
00283         }
00284         m_lastError.setText(sqlError.text());
00285     } else {
00286         m_lastError.clear();
00287     }
00288 }
00289 
00290 QString SqlDatabase::errorInfo(const QSqlError &error)
00291 {
00292     if (!error.isValid())
00293         return QLatin1String("SQL Error invalid.");
00294 
00295     QString text;
00296     QTextStream stream(&text);
00297     stream << "SQL error description:";
00298     stream << "\n\tType: ";
00299 
00300     const char *errType;
00301     switch (error.type()) {
00302         case QSqlError::NoError: errType = "NoError"; break;
00303         case QSqlError::ConnectionError: errType = "ConnectionError"; break;
00304         case QSqlError::StatementError: errType = "StatementError"; break;
00305         case QSqlError::TransactionError: errType = "TransactionError"; break;
00306         case QSqlError::UnknownError:
00307             /* fall trough */
00308         default: errType = "UnknownError";
00309     }
00310     stream << errType;
00311     stream << "\n\tDatabase text: " << error.databaseText();
00312     stream << "\n\tDriver text: " << error.driverText();
00313     stream << "\n\tNumber: " << error.number();
00314 
00315     return text;
00316 }
00317 
00318 QStringList SqlDatabase::queryList(const QString &query_str)
00319 {
00320     QSqlQuery query(QString(), m_database);
00321     if (!query.prepare(query_str))
00322         TRACE() << "Query prepare warning: " << query.lastQuery();
00323     return queryList(query);
00324 }
00325 
00326 QStringList SqlDatabase::queryList(QSqlQuery &q)
00327 {
00328     QStringList list;
00329     QSqlQuery query = exec(q);
00330     if (errorOccurred()) return list;
00331     while (query.next()) {
00332         list.append(query.value(0).toString());
00333     }
00334     query.clear();
00335     return list;
00336 }
00337 
00338 QStringList MetaDataDB::tableUpdates2()
00339 {
00340     QStringList tableUpdates = QStringList()
00341         <<  QString::fromLatin1(
00342             "CREATE TABLE OWNER"
00343             "(rowid INTEGER PRIMARY KEY AUTOINCREMENT,"
00344             "identity_id INTEGER CONSTRAINT fk_identity_id REFERENCES CREDENTIALS(id) ON DELETE CASCADE,"
00345             "token_id INTEGER CONSTRAINT fk_token_id REFERENCES TOKENS(id) ON DELETE CASCADE)")
00346         //added triggers for OWNER
00347         << QString::fromLatin1(
00348             // Foreign Key Preventing insert
00349             "CREATE TRIGGER fki_OWNER_token_id_TOKENS_id "
00350             "BEFORE INSERT ON [OWNER] "
00351             "FOR EACH ROW BEGIN "
00352             "  SELECT RAISE(ROLLBACK, 'insert on table OWNER violates foreign key constraint fki_OWNER_token_id_TOKENS_id') "
00353             "  WHERE NEW.token_id IS NOT NULL AND (SELECT id FROM TOKENS WHERE id = NEW.token_id) IS NULL; "
00354             "END; "
00355         )
00356         << QString::fromLatin1(
00357             // Foreign key preventing update
00358             "CREATE TRIGGER fku_OWNER_token_id_TOKENS_id "
00359             "BEFORE UPDATE ON [OWNER] "
00360             "FOR EACH ROW BEGIN "
00361             "    SELECT RAISE(ROLLBACK, 'update on table OWNER violates foreign key constraint fku_OWNER_token_id_TOKENS_id') "
00362             "      WHERE NEW.token_id IS NOT NULL AND (SELECT id FROM TOKENS WHERE id = NEW.token_id) IS NULL; "
00363             "END; "
00364         )
00365         << QString::fromLatin1(
00366             // Cascading Delete
00367             "CREATE TRIGGER fkdc_OWNER_token_id_TOKENS_id "
00368             "BEFORE DELETE ON TOKENS "
00369             "FOR EACH ROW BEGIN "
00370             "    DELETE FROM OWNER WHERE OWNER.token_id = OLD.id; "
00371             "END; "
00372         );
00373 
00374     return tableUpdates;
00375 }
00376 
00377 bool MetaDataDB::createTables()
00378 {
00379     /* !!! Foreign keys support seems to be disabled, for the moment... */
00380     QStringList createTableQuery = QStringList()
00381         <<  QString::fromLatin1(
00382             "CREATE TABLE CREDENTIALS"
00383             "(id INTEGER PRIMARY KEY AUTOINCREMENT,"
00384             "caption TEXT,"
00385             "username TEXT,"
00386             "flags INTEGER,"
00387             "type INTEGER)")
00388         <<  QString::fromLatin1(
00389             "CREATE TABLE METHODS"
00390             "(id INTEGER PRIMARY KEY AUTOINCREMENT,"
00391             "method TEXT UNIQUE)")
00392         <<  QString::fromLatin1(
00393             "CREATE TABLE MECHANISMS"
00394             "(id INTEGER PRIMARY KEY AUTOINCREMENT,"
00395             "mechanism TEXT UNIQUE)")
00396         <<  QString::fromLatin1(
00397             "CREATE TABLE TOKENS"
00398             "(id INTEGER PRIMARY KEY AUTOINCREMENT,"
00399             "token TEXT UNIQUE)")
00400         <<  QString::fromLatin1(
00401             "CREATE TABLE REALMS"
00402             "(identity_id INTEGER CONSTRAINT fk_identity_id REFERENCES CREDENTIALS(id) ON DELETE CASCADE,"
00403             "realm TEXT,"
00404             "hostname TEXT,"
00405             "PRIMARY KEY (identity_id, realm, hostname))")
00406         <<  QString::fromLatin1(
00407             "CREATE TABLE ACL"
00408             "(rowid INTEGER PRIMARY KEY AUTOINCREMENT,"
00409             "identity_id INTEGER CONSTRAINT fk_identity_id REFERENCES CREDENTIALS(id) ON DELETE CASCADE,"
00410             "method_id INTEGER CONSTRAINT fk_method_id REFERENCES METHODS(id) ON DELETE CASCADE,"
00411             "mechanism_id INTEGER CONSTRAINT fk_mechanism_id REFERENCES MECHANISMS(id) ON DELETE CASCADE,"
00412             "token_id INTEGER CONSTRAINT fk_token_id REFERENCES TOKENS(id) ON DELETE CASCADE)")
00413         <<  QString::fromLatin1(
00414             "CREATE TABLE REFS"
00415             "(identity_id INTEGER CONSTRAINT fk_identity_id REFERENCES CREDENTIALS(id) ON DELETE CASCADE,"
00416             "token_id INTEGER CONSTRAINT fk_token_id REFERENCES TOKENS(id) ON DELETE CASCADE,"
00417             "ref TEXT,"
00418             "PRIMARY KEY (identity_id, token_id, ref))")
00419 
00420 /*
00421 * triggers generated with
00422 * http://www.rcs-comp.com/site/index.php/view/Utilities-SQLite_foreign_key_trigger_generator
00423 */
00424         //insert triggers to force foreign keys support
00425         << QString::fromLatin1(
00426             // Foreign Key Preventing insert
00427             "CREATE TRIGGER fki_REALMS_identity_id_CREDENTIALS_id "
00428             "BEFORE INSERT ON [REALMS] "
00429             "FOR EACH ROW BEGIN "
00430             "  SELECT RAISE(ROLLBACK, 'insert on table REALMS violates foreign key constraint fki_REALMS_identity_id_CREDENTIALS_id') "
00431             "  WHERE NEW.identity_id IS NOT NULL AND (SELECT id FROM CREDENTIALS WHERE id = NEW.identity_id) IS NULL; "
00432             "END; "
00433         )
00434         << QString::fromLatin1(
00435             // Foreign key preventing update
00436             "CREATE TRIGGER fku_REALMS_identity_id_CREDENTIALS_id "
00437             "BEFORE UPDATE ON [REALMS] "
00438             "FOR EACH ROW BEGIN "
00439             "    SELECT RAISE(ROLLBACK, 'update on table REALMS violates foreign key constraint fku_REALMS_identity_id_CREDENTIALS_id') "
00440             "      WHERE NEW.identity_id IS NOT NULL AND (SELECT id FROM CREDENTIALS WHERE id = NEW.identity_id) IS NULL; "
00441             "END; "
00442         )
00443         << QString::fromLatin1(
00444             // Cascading Delete
00445             "CREATE TRIGGER fkdc_REALMS_identity_id_CREDENTIALS_id "
00446             "BEFORE DELETE ON CREDENTIALS "
00447             "FOR EACH ROW BEGIN "
00448             "    DELETE FROM REALMS WHERE REALMS.identity_id = OLD.id; "
00449             "END; "
00450         )
00451         << QString::fromLatin1(
00452             // Foreign Key Preventing insert
00453             "CREATE TRIGGER fki_ACL_identity_id_CREDENTIALS_id "
00454             "BEFORE INSERT ON [ACL] "
00455             "FOR EACH ROW BEGIN "
00456             "  SELECT RAISE(ROLLBACK, 'insert on table ACL violates foreign key constraint fki_ACL_identity_id_CREDENTIALS_id') "
00457             "  WHERE NEW.identity_id IS NOT NULL AND (SELECT id FROM CREDENTIALS WHERE id = NEW.identity_id) IS NULL; "
00458             "END;"
00459         )
00460         << QString::fromLatin1(
00461             // Foreign key preventing update
00462             "CREATE TRIGGER fku_ACL_identity_id_CREDENTIALS_id "
00463             "BEFORE UPDATE ON [ACL] "
00464             "FOR EACH ROW BEGIN "
00465             "    SELECT RAISE(ROLLBACK, 'update on table ACL violates foreign key constraint fku_ACL_identity_id_CREDENTIALS_id') "
00466             "      WHERE NEW.identity_id IS NOT NULL AND (SELECT id FROM CREDENTIALS WHERE id = NEW.identity_id) IS NULL; "
00467             "END; "
00468         )
00469         << QString::fromLatin1(
00470             // Cascading Delete
00471             "CREATE TRIGGER fkdc_ACL_identity_id_CREDENTIALS_id "
00472             "BEFORE DELETE ON CREDENTIALS "
00473             "FOR EACH ROW BEGIN "
00474              "   DELETE FROM ACL WHERE ACL.identity_id = OLD.id; "
00475             "END; "
00476         )
00477         << QString::fromLatin1(
00478             // Foreign Key Preventing insert
00479             "CREATE TRIGGER fki_ACL_method_id_METHODS_id "
00480             "BEFORE INSERT ON [ACL] "
00481             "FOR EACH ROW BEGIN "
00482             "  SELECT RAISE(ROLLBACK, 'insert on table ACL violates foreign key constraint fki_ACL_method_id_METHODS_id') "
00483             "  WHERE NEW.method_id IS NOT NULL AND (SELECT id FROM METHODS WHERE id = NEW.method_id) IS NULL; "
00484             "END; "
00485         )
00486         << QString::fromLatin1(
00487             // Foreign key preventing update
00488             "CREATE TRIGGER fku_ACL_method_id_METHODS_id "
00489             "BEFORE UPDATE ON [ACL] "
00490             "FOR EACH ROW BEGIN "
00491             "    SELECT RAISE(ROLLBACK, 'update on table ACL violates foreign key constraint fku_ACL_method_id_METHODS_id') "
00492             "      WHERE NEW.method_id IS NOT NULL AND (SELECT id FROM METHODS WHERE id = NEW.method_id) IS NULL; "
00493             "END; "
00494         )
00495         << QString::fromLatin1(
00496             // Cascading Delete
00497             "CREATE TRIGGER fkdc_ACL_method_id_METHODS_id "
00498             "BEFORE DELETE ON METHODS "
00499             "FOR EACH ROW BEGIN "
00500             "    DELETE FROM ACL WHERE ACL.method_id = OLD.id; "
00501             "END; "
00502         )
00503         << QString::fromLatin1(
00504             // Foreign Key Preventing insert
00505             "CREATE TRIGGER fki_ACL_mechanism_id_MECHANISMS_id "
00506             "BEFORE INSERT ON [ACL] "
00507             "FOR EACH ROW BEGIN "
00508             "  SELECT RAISE(ROLLBACK, 'insert on table ACL violates foreign key constraint fki_ACL_mechanism_id_MECHANISMS_id') "
00509             "  WHERE NEW.mechanism_id IS NOT NULL AND (SELECT id FROM MECHANISMS WHERE id = NEW.mechanism_id) IS NULL; "
00510             "END; "
00511         )
00512         << QString::fromLatin1(
00513             // Foreign key preventing update
00514             "CREATE TRIGGER fku_ACL_mechanism_id_MECHANISMS_id "
00515             "BEFORE UPDATE ON [ACL] "
00516             "FOR EACH ROW BEGIN "
00517             "    SELECT RAISE(ROLLBACK, 'update on table ACL violates foreign key constraint fku_ACL_mechanism_id_MECHANISMS_id') "
00518             "      WHERE NEW.mechanism_id IS NOT NULL AND (SELECT id FROM MECHANISMS WHERE id = NEW.mechanism_id) IS NULL; "
00519             "END; "
00520         )
00521         << QString::fromLatin1(
00522             // Cascading Delete
00523             "CREATE TRIGGER fkdc_ACL_mechanism_id_MECHANISMS_id "
00524             "BEFORE DELETE ON MECHANISMS "
00525             "FOR EACH ROW BEGIN "
00526             "    DELETE FROM ACL WHERE ACL.mechanism_id = OLD.id; "
00527             "END; "
00528         )
00529         << QString::fromLatin1(
00530             // Foreign Key Preventing insert
00531             "CREATE TRIGGER fki_ACL_token_id_TOKENS_id "
00532             "BEFORE INSERT ON [ACL] "
00533             "FOR EACH ROW BEGIN "
00534             "  SELECT RAISE(ROLLBACK, 'insert on table ACL violates foreign key constraint fki_ACL_token_id_TOKENS_id') "
00535             "  WHERE NEW.token_id IS NOT NULL AND (SELECT id FROM TOKENS WHERE id = NEW.token_id) IS NULL; "
00536             "END; "
00537         )
00538         << QString::fromLatin1(
00539             // Foreign key preventing update
00540             "CREATE TRIGGER fku_ACL_token_id_TOKENS_id "
00541             "BEFORE UPDATE ON [ACL] "
00542             "FOR EACH ROW BEGIN "
00543             "    SELECT RAISE(ROLLBACK, 'update on table ACL violates foreign key constraint fku_ACL_token_id_TOKENS_id') "
00544             "      WHERE NEW.token_id IS NOT NULL AND (SELECT id FROM TOKENS WHERE id = NEW.token_id) IS NULL; "
00545             "END; "
00546         )
00547         << QString::fromLatin1(
00548             // Cascading Delete
00549             "CREATE TRIGGER fkdc_ACL_token_id_TOKENS_id "
00550             "BEFORE DELETE ON TOKENS "
00551             "FOR EACH ROW BEGIN "
00552             "    DELETE FROM ACL WHERE ACL.token_id = OLD.id; "
00553             "END; "
00554         )
00555         << QString::fromLatin1(
00556             // Foreign Key Preventing insert
00557             "CREATE TRIGGER fki_REFS_identity_id_CREDENTIALS_id "
00558             "BEFORE INSERT ON [REFS] "
00559             "FOR EACH ROW BEGIN "
00560             "  SELECT RAISE(ROLLBACK, 'insert on table REFS violates foreign key constraint fki_REFS_identity_id_CREDENTIALS_id') "
00561             "  WHERE NEW.identity_id IS NOT NULL AND (SELECT id FROM CREDENTIALS WHERE id = NEW.identity_id) IS NULL; "
00562             "END; "
00563         )
00564         << QString::fromLatin1(
00565             // Foreign key preventing update
00566             "CREATE TRIGGER fku_REFS_identity_id_CREDENTIALS_id "
00567             "BEFORE UPDATE ON [REFS] "
00568             "FOR EACH ROW BEGIN "
00569             "    SELECT RAISE(ROLLBACK, 'update on table REFS violates foreign key constraint fku_REFS_identity_id_CREDENTIALS_id') "
00570             "      WHERE NEW.identity_id IS NOT NULL AND (SELECT id FROM CREDENTIALS WHERE id = NEW.identity_id) IS NULL; "
00571             "END; "
00572         )
00573         << QString::fromLatin1(
00574             // Cascading Delete
00575             "CREATE TRIGGER fkdc_REFS_identity_id_CREDENTIALS_id "
00576             "BEFORE DELETE ON CREDENTIALS "
00577             "FOR EACH ROW BEGIN "
00578             "    DELETE FROM REFS WHERE REFS.identity_id = OLD.id; "
00579             "END; "
00580         )
00581         << QString::fromLatin1(
00582             // Foreign Key Preventing insert
00583             "CREATE TRIGGER fki_REFS_token_id_TOKENS_id "
00584             "BEFORE INSERT ON [REFS] "
00585             "FOR EACH ROW BEGIN "
00586             "  SELECT RAISE(ROLLBACK, 'insert on table REFS violates foreign key constraint fki_REFS_token_id_TOKENS_id') "
00587             "  WHERE NEW.token_id IS NOT NULL AND (SELECT id FROM TOKENS WHERE id = NEW.token_id) IS NULL; "
00588             "END; "
00589         )
00590         << QString::fromLatin1(
00591             // Foreign key preventing update
00592             "CREATE TRIGGER fku_REFS_token_id_TOKENS_id "
00593             "BEFORE UPDATE ON [REFS] "
00594             "FOR EACH ROW BEGIN "
00595             "    SELECT RAISE(ROLLBACK, 'update on table REFS violates foreign key constraint fku_REFS_token_id_TOKENS_id') "
00596             "      WHERE NEW.token_id IS NOT NULL AND (SELECT id FROM TOKENS WHERE id = NEW.token_id) IS NULL; "
00597             "END; "
00598         )
00599         << QString::fromLatin1(
00600             // Cascading Delete
00601             "CREATE TRIGGER fkdc_REFS_token_id_TOKENS_id "
00602             "BEFORE DELETE ON TOKENS "
00603             "FOR EACH ROW BEGIN "
00604             "    DELETE FROM REFS WHERE REFS.token_id = OLD.id; "
00605             "END; "
00606         );
00607 /*
00608 end of generated code
00609 */
00610     //insert table updates
00611     createTableQuery << tableUpdates2();
00612 
00613     foreach (QString createTable, createTableQuery) {
00614         QSqlQuery query = exec(createTable);
00615         if (lastError().isValid()) {
00616             TRACE() << "Error occurred while creating the database.";
00617             return false;
00618         }
00619         query.clear();
00620         commit();
00621     }
00622     TRACE() << "Creation successful";
00623 
00624     return true;
00625 }
00626 
00627 bool MetaDataDB::updateDB(int version)
00628 {
00629     if (version == m_version)
00630         return true;
00631 
00632     if (version < 1) {
00633         TRACE() << "Upgrading from version < 1 not supported. Clearing DB";
00634         QString fileName = m_database.databaseName();
00635         QString connectionName = m_database.connectionName();
00636         m_database.close();
00637         QFile::remove(fileName);
00638         m_database = QSqlDatabase(QSqlDatabase::addDatabase(driver,
00639                                                             connectionName));
00640         m_database.setDatabaseName(fileName);
00641         if (!connect())
00642             return false;
00643 
00644         if (!createTables())
00645             return false;
00646     }
00647 
00648     //convert from 1 to 2
00649     if (version == 1) {
00650         QStringList createTableQuery = tableUpdates2();
00651         foreach (QString createTable, createTableQuery) {
00652             QSqlQuery query = exec(createTable);
00653             if (lastError().isValid()) {
00654                 TRACE() << "Error occurred while inseting new tables.";
00655                 return false;
00656             }
00657             query.clear();
00658             commit();
00659         }
00660         TRACE() << "Table insert successful";
00661 
00662         //populate owner table from acl
00663         QSqlQuery ownerInsert = exec(S("INSERT OR IGNORE INTO OWNER "
00664                             "(identity_id, token_id) "
00665                             " SELECT identity_id, token_id FROM ACL"));
00666         if (!commit()){
00667             BLAME() << "Table copy failed.";
00668             rollback();
00669         }
00670 
00671     } else {
00672         return false;
00673     }
00674 
00675     return SqlDatabase::updateDB(version);
00676 }
00677 
00678 QStringList MetaDataDB::methods(const quint32 id, const QString &securityToken)
00679 {
00680     QStringList list;
00681     if (securityToken.isEmpty()) {
00682         list = queryList(
00683                  QString::fromLatin1("SELECT DISTINCT METHODS.method FROM "
00684                         "( ACL JOIN METHODS ON ACL.method_id = METHODS.id ) "
00685                         "WHERE ACL.identity_id = '%1'").arg(id)
00686                  );
00687         return list;
00688     }
00689     QSqlQuery q = newQuery();
00690     q.prepare(S("SELECT DISTINCT METHODS.method FROM "
00691                 "( ACL JOIN METHODS ON ACL.method_id = METHODS.id) "
00692                 "WHERE ACL.identity_id = :id AND ACL.token_id = "
00693                 "(SELECT id FROM TOKENS where token = :token)"));
00694     q.bindValue(S(":id"), id);
00695     q.bindValue(S(":token"), securityToken);
00696     list = queryList(q);
00697 
00698     return list;
00699 }
00700 
00701 quint32 MetaDataDB::methodId(const QString &method)
00702 {
00703     TRACE() << "method:" << method;
00704 
00705     QSqlQuery q = newQuery();
00706     q.prepare(S("SELECT id FROM METHODS WHERE method = :method"));
00707     q.bindValue(S(":method"), method);
00708     exec(q);
00709     if (!q.first()) {
00710         TRACE() << "No result or invalid method query.";
00711         return 0;
00712     }
00713 
00714     return q.value(0).toUInt();
00715 }
00716 
00717 SignonIdentityInfo MetaDataDB::identity(const quint32 id)
00718 {
00719     QString query_str;
00720 
00721     query_str = QString::fromLatin1(
00722             "SELECT caption, username, flags, type "
00723             "FROM credentials WHERE id = %1").arg(id);
00724     QSqlQuery query = exec(query_str);
00725 
00726     if (!query.first()) {
00727         TRACE() << "No result or invalid credentials query.";
00728         return SignonIdentityInfo();
00729     }
00730 
00731     QString caption = query.value(0).toString();
00732     QString username = query.value(1).toString();
00733     int flags = query.value(2).toInt();
00734     bool savePassword = flags & RememberPassword;
00735     bool validated =  flags & Validated;
00736     bool isUserNameSecret = flags & UserNameIsSecret;
00737     if (isUserNameSecret) username = QString();
00738     int type = query.value(3).toInt();
00739 
00740     query.clear();
00741     QStringList realms = queryList(
00742             QString::fromLatin1("SELECT realm FROM REALMS "
00743                     "WHERE identity_id = %1").arg(id));
00744 
00745     QStringList ownerTokens = queryList(
00746             QString::fromLatin1("SELECT token FROM TOKENS "
00747                                 "WHERE id IN "
00748                                 "(SELECT token_id FROM OWNER WHERE identity_id = '%1' )")
00749                                 .arg(id));
00750 
00751     query_str = QString::fromLatin1("SELECT token FROM TOKENS "
00752             "WHERE id IN "
00753             "(SELECT token_id FROM ACL WHERE identity_id = '%1' )")
00754             .arg(id);
00755     query = exec(query_str);
00756     QStringList securityTokens;
00757     while (query.next()) {
00758         securityTokens.append(query.value(0).toString());
00759     }
00760     query.clear();
00761     MethodMap methods;
00762     query_str = QString::fromLatin1(
00763             "SELECT DISTINCT ACL.method_id, METHODS.method FROM "
00764             "( ACL JOIN METHODS ON ACL.method_id = METHODS.id ) "
00765             "WHERE ACL.identity_id = '%1'").arg(id);
00766     query = exec(query_str);
00767     while (query.next()) {
00768         QStringList mechanisms = queryList(
00769                 QString::fromLatin1("SELECT DISTINCT MECHANISMS.mechanism FROM "
00770                         "( MECHANISMS JOIN ACL "
00771                         "ON ACL.mechanism_id = MECHANISMS.id ) "
00772                         "WHERE ACL.method_id = '%1' AND ACL.identity_id = '%2' ")
00773                         .arg(query.value(0).toInt()).arg(id));
00774             methods.insert(query.value(1).toString(), mechanisms);
00775     }
00776     query.clear();
00777 
00778     int refCount = 0;
00779     //TODO query for refcount
00780 
00781     SignonIdentityInfo info;
00782     info.setId(id);
00783     if (!isUserNameSecret)
00784         info.setUserName(username);
00785     info.setStorePassword(savePassword);
00786     info.setCaption(caption);
00787     info.setMethods(methods);
00788     info.setRealms(realms);
00789     info.setAccessControlList(securityTokens);
00790     info.setOwnerList(ownerTokens);
00791     info.setType(type);
00792     info.setRefCount(refCount);
00793     info.setValidated(validated);
00794     info.setUserNameSecret(isUserNameSecret);
00795     return info;
00796 }
00797 
00798 QList<SignonIdentityInfo> MetaDataDB::identities(const QMap<QString,
00799                                                  QString> &filter)
00800 {
00801     TRACE();
00802     Q_UNUSED(filter)
00803     QList<SignonIdentityInfo> result;
00804 
00805     QString queryStr(QString::fromLatin1("SELECT id FROM credentials"));
00806 
00807     // TODO - process filtering step here !!!
00808 
00809     queryStr += QString::fromLatin1(" ORDER BY id");
00810 
00811     QSqlQuery query = exec(queryStr);
00812     if (errorOccurred()) {
00813         TRACE() << "Error occurred while fetching credentials from database.";
00814         return result;
00815     }
00816 
00817     while (query.next()) {
00818         SignonIdentityInfo info = identity(query.value(0).toUInt());
00819         if (errorOccurred())
00820             break;
00821         result << info;
00822     }
00823 
00824     query.clear();
00825     return result;
00826 }
00827 
00828 quint32 MetaDataDB::updateIdentity(const SignonIdentityInfo &info)
00829 {
00830     if (!startTransaction()) {
00831         TRACE() << "Could not start transaction. Error inserting credentials.";
00832         return 0;
00833     }
00834 
00835     quint32 id = updateCredentials(info);
00836     if (id == 0) {
00837         rollback();
00838         return 0;
00839     }
00840 
00841     /* Methods inserts */
00842     insertMethods(info.methods());
00843 
00844     if (!updateRealms(id, info.realms(), info.isNew())) {
00845         TRACE() << "Error in updating realms";
00846         rollback();
00847         return 0;
00848     }
00849 
00850     /* Security tokens insert */
00851     foreach (QString token, info.accessControlList()) {
00852         QSqlQuery tokenInsert = newQuery();
00853         tokenInsert.prepare(S("INSERT OR IGNORE INTO TOKENS (token) "
00854                               "VALUES ( :token )"));
00855         tokenInsert.bindValue(S(":token"), token);
00856         exec(tokenInsert);
00857     }
00858 
00859     foreach (QString token, info.ownerList()) {
00860         if (!token.isEmpty()) {
00861             QSqlQuery tokenInsert = newQuery();
00862             tokenInsert.prepare(S("INSERT OR IGNORE INTO TOKENS (token) "
00863                                   "VALUES ( :token )"));
00864             tokenInsert.bindValue(S(":token"), token);
00865             exec(tokenInsert);
00866         }
00867     }
00868 
00869     if (!info.isNew()) {
00870         //remove acl
00871         QString queryStr = QString::fromLatin1(
00872                     "DELETE FROM ACL WHERE "
00873                     "identity_id = '%1'")
00874                     .arg(info.id());
00875         QSqlQuery insertQuery = exec(queryStr);
00876         insertQuery.clear();
00877         //remove owner
00878         queryStr = QString::fromLatin1(
00879                     "DELETE FROM OWNER WHERE "
00880                     "identity_id = '%1'")
00881                     .arg(info.id());
00882         insertQuery = exec(queryStr);
00883         insertQuery.clear();
00884     }
00885 
00886     /* ACL insert, this will do basically identity level ACL */
00887     QMapIterator<QString, QStringList> it(info.methods());
00888     while (it.hasNext()) {
00889         it.next();
00890         if (!info.accessControlList().isEmpty()) {
00891             foreach (QString token, info.accessControlList()) {
00892                 foreach (QString mech, it.value()) {
00893                     QSqlQuery aclInsert = newQuery();
00894                     aclInsert.prepare(S("INSERT OR REPLACE INTO ACL "
00895                                         "(identity_id, method_id, mechanism_id, token_id) "
00896                                         "VALUES ( :id, "
00897                                         "( SELECT id FROM METHODS WHERE method = :method ),"
00898                                         "( SELECT id FROM MECHANISMS WHERE mechanism= :mech ), "
00899                                         "( SELECT id FROM TOKENS WHERE token = :token ))"));
00900                     aclInsert.bindValue(S(":id"), id);
00901                     aclInsert.bindValue(S(":method"), it.key());
00902                     aclInsert.bindValue(S(":mech"), mech);
00903                     aclInsert.bindValue(S(":token"), token);
00904                     exec(aclInsert);
00905                 }
00906                 //insert entires for empty mechs list
00907                 if (it.value().isEmpty()) {
00908                     QSqlQuery aclInsert = newQuery();
00909                     aclInsert.prepare(S("INSERT OR REPLACE INTO ACL (identity_id, method_id, token_id) "
00910                                         "VALUES ( :id, "
00911                                         "( SELECT id FROM METHODS WHERE method = :method ),"
00912                                         "( SELECT id FROM TOKENS WHERE token = :token ))"));
00913                     aclInsert.bindValue(S(":id"), id);
00914                     aclInsert.bindValue(S(":method"), it.key());
00915                     aclInsert.bindValue(S(":token"), token);
00916                     exec(aclInsert);
00917                 }
00918             }
00919         } else {
00920             foreach (QString mech, it.value()) {
00921                 QSqlQuery aclInsert = newQuery();
00922                 aclInsert.prepare(S("INSERT OR REPLACE INTO ACL "
00923                                     "(identity_id, method_id, mechanism_id) "
00924                                     "VALUES ( :id, "
00925                                     "( SELECT id FROM METHODS WHERE method = :method ),"
00926                                     "( SELECT id FROM MECHANISMS WHERE mechanism= :mech )"
00927                                     ")"));
00928                 aclInsert.bindValue(S(":id"), id);
00929                 aclInsert.bindValue(S(":method"), it.key());
00930                 aclInsert.bindValue(S(":mech"), mech);
00931                 exec(aclInsert);
00932             }
00933             //insert entires for empty mechs list
00934             if (it.value().isEmpty()) {
00935                 QSqlQuery aclInsert = newQuery();
00936                 aclInsert.prepare(S("INSERT OR REPLACE INTO ACL (identity_id, method_id) "
00937                                     "VALUES ( :id, "
00938                                     "( SELECT id FROM METHODS WHERE method = :method )"
00939                                     ")"));
00940                 aclInsert.bindValue(S(":id"), id);
00941                 aclInsert.bindValue(S(":method"), it.key());
00942                 exec(aclInsert);
00943             }
00944         }
00945     }
00946     //insert acl in case where methods are missing
00947     if (info.methods().isEmpty()) {
00948         foreach (QString token, info.accessControlList()) {
00949             QSqlQuery aclInsert = newQuery();
00950             aclInsert.prepare(S("INSERT OR REPLACE INTO ACL "
00951                                 "(identity_id, token_id) "
00952                                 "VALUES ( :id, "
00953                                 "( SELECT id FROM TOKENS WHERE token = :token ))"));
00954             aclInsert.bindValue(S(":id"), id);
00955             aclInsert.bindValue(S(":token"), token);
00956             exec(aclInsert);
00957         }
00958     }
00959 
00960     //insert owner list
00961     foreach (QString token, info.ownerList()) {
00962         if (!token.isEmpty()) {
00963             QSqlQuery ownerInsert = newQuery();
00964             ownerInsert.prepare(S("INSERT OR REPLACE INTO OWNER "
00965                             "(identity_id, token_id) "
00966                             "VALUES ( :id, "
00967                             "( SELECT id FROM TOKENS WHERE token = :token ))"));
00968             ownerInsert.bindValue(S(":id"), id);
00969             ownerInsert.bindValue(S(":token"), token);
00970             exec(ownerInsert);
00971         }
00972     }
00973 
00974     if (commit()) {
00975         return id;
00976     } else {
00977         rollback();
00978         TRACE() << "Credentials insertion failed.";
00979         return 0;
00980     }
00981 }
00982 
00983 bool MetaDataDB::removeIdentity(const quint32 id)
00984 {
00985     TRACE();
00986 
00987     QStringList queries = QStringList()
00988         << QString::fromLatin1(
00989             "DELETE FROM CREDENTIALS WHERE id = %1").arg(id)
00990         << QString::fromLatin1(
00991             "DELETE FROM ACL WHERE identity_id = %1").arg(id)
00992         << QString::fromLatin1(
00993             "DELETE FROM REALMS WHERE identity_id = %1").arg(id)
00994         << QString::fromLatin1(
00995             "DELETE FROM owner WHERE identity_id = %1").arg(id);
00996 
00997     return transactionalExec(queries);
00998 }
00999 
01000 bool MetaDataDB::clear()
01001 {
01002     TRACE();
01003 
01004     QStringList clearCommands = QStringList()
01005         << QLatin1String("DELETE FROM CREDENTIALS")
01006         << QLatin1String("DELETE FROM METHODS")
01007         << QLatin1String("DELETE FROM MECHANISMS")
01008         << QLatin1String("DELETE FROM ACL")
01009         << QLatin1String("DELETE FROM REALMS")
01010         << QLatin1String("DELETE FROM TOKENS")
01011         << QLatin1String("DELETE FROM OWNER");
01012 
01013     return transactionalExec(clearCommands);
01014 }
01015 
01016 QStringList MetaDataDB::accessControlList(const quint32 identityId)
01017 {
01018     return queryList(QString::fromLatin1("SELECT token FROM TOKENS "
01019             "WHERE id IN "
01020             "(SELECT token_id FROM ACL WHERE identity_id = '%1' )")
01021             .arg(identityId));
01022 }
01023 
01024 QStringList MetaDataDB::ownerList(const quint32 identityId)
01025 {
01026     return queryList(QString::fromLatin1("SELECT token FROM TOKENS "
01027             "WHERE id IN "
01028             "(SELECT token_id FROM OWNER WHERE identity_id = '%1' )")
01029             .arg(identityId));
01030 }
01031 
01032 bool MetaDataDB::addReference(const quint32 id,
01033                               const QString &token,
01034                               const QString &reference)
01035 {
01036     if (!startTransaction()) {
01037         TRACE() << "Could not start transaction. Error inserting data.";
01038         return false;
01039     }
01040 
01041     TRACE() << "Storing:" << id << ", " << token << ", " << reference;
01042     /* Data insert */
01043     bool allOk = true;
01044 
01045     /* Security token insert */
01046     QSqlQuery tokenInsert = newQuery();
01047     tokenInsert.prepare(S("INSERT OR IGNORE INTO TOKENS (token) "
01048                           "VALUES ( :token )"));
01049     tokenInsert.bindValue(S(":token"), token);
01050     exec(tokenInsert);
01051     if (errorOccurred()) {
01052                 allOk = false;
01053     }
01054 
01055     QSqlQuery refsInsert = newQuery();
01056     refsInsert.prepare(S("INSERT OR REPLACE INTO REFS "
01057                          "(identity_id, token_id, ref) "
01058                          "VALUES ( :id, "
01059                          "( SELECT id FROM TOKENS WHERE token = :token ),"
01060                          ":reference"
01061                          ")"));
01062     refsInsert.bindValue(S(":id"), id);
01063     refsInsert.bindValue(S(":token"), token);
01064     refsInsert.bindValue(S(":reference"), reference);
01065     exec(refsInsert);
01066     if (errorOccurred()) {
01067                 allOk = false;
01068     }
01069 
01070     if (allOk && commit()) {
01071         TRACE() << "Data insertion ok.";
01072         return true;
01073     }
01074     rollback();
01075     TRACE() << "Data insertion failed.";
01076     return false;
01077 }
01078 
01079 bool MetaDataDB::removeReference(const quint32 id,
01080                                  const QString &token,
01081                                  const QString &reference)
01082 {
01083     TRACE() << "Removing:" << id << ", " << token << ", " << reference;
01084     //check that there is references
01085     QStringList refs = references(id, token);
01086     if (refs.isEmpty())
01087         return false;
01088     if (!reference.isNull() && !refs.contains(reference))
01089         return false;
01090 
01091     if (!startTransaction()) {
01092         TRACE() << "Could not start transaction. Error removing data.";
01093         return false;
01094     }
01095 
01096     bool allOk = true;
01097     QSqlQuery refsDelete = newQuery();
01098 
01099     if (reference.isEmpty()) {
01100         refsDelete.prepare(S("DELETE FROM REFS "
01101                              "WHERE identity_id = :id AND "
01102                              "token_id = ( SELECT id FROM TOKENS WHERE token = :token )"));
01103         refsDelete.bindValue(S(":id"), id);
01104         refsDelete.bindValue(S(":token"), token);
01105     } else {
01106         refsDelete.prepare(S("DELETE FROM REFS "
01107                              "WHERE identity_id = :id AND "
01108                              "token_id = ( SELECT id FROM TOKENS WHERE token = :token ) "
01109                              "AND ref = :ref"));
01110         refsDelete.bindValue(S(":id"), id);
01111         refsDelete.bindValue(S(":token"), token);
01112         refsDelete.bindValue(S(":ref"), reference);
01113     }
01114 
01115     exec(refsDelete);
01116     if (errorOccurred()) {
01117                 allOk = false;
01118     }
01119 
01120     if (allOk && commit()) {
01121         TRACE() << "Data delete ok.";
01122         return true;
01123     }
01124     rollback();
01125     TRACE() << "Data delete failed.";
01126     return false;
01127 }
01128 
01129 QStringList MetaDataDB::references(const quint32 id, const QString &token)
01130 {
01131     if (token.isEmpty())
01132         return queryList(QString::fromLatin1("SELECT ref FROM REFS "
01133             "WHERE identity_id = '%1'")
01134             .arg(id));
01135     QSqlQuery q = newQuery();
01136     q.prepare(S("SELECT ref FROM REFS "
01137                 "WHERE identity_id = :id AND "
01138                 "token_id = (SELECT id FROM TOKENS WHERE token = :token )"));
01139     q.bindValue(S(":id"), id);
01140     q.bindValue(S(":token"), token);
01141     return queryList(q);
01142 }
01143 
01144 bool MetaDataDB::insertMethods(QMap<QString, QStringList> methods)
01145 {
01146     bool allOk = true;
01147 
01148     if (methods.isEmpty()) return false;
01149     //insert (unique) method names
01150     QMapIterator<QString, QStringList> it(methods);
01151     while (it.hasNext()) {
01152         it.next();
01153         QSqlQuery methodInsert = newQuery();
01154         methodInsert.prepare(S("INSERT OR IGNORE INTO METHODS (method) "
01155                                "VALUES( :method )"));
01156         methodInsert.bindValue(S(":method"), it.key());
01157         exec(methodInsert);
01158         if (errorOccurred()) allOk = false;
01159         //insert (unique) mechanism names
01160         foreach (QString mech, it.value()) {
01161             QSqlQuery mechInsert = newQuery();
01162             mechInsert.prepare(S("INSERT OR IGNORE INTO MECHANISMS (mechanism) "
01163                                  "VALUES( :mech )"));
01164             mechInsert.bindValue(S(":mech"), mech);
01165             exec(mechInsert);
01166             if (errorOccurred()) allOk = false;
01167         }
01168     }
01169     return allOk;
01170 }
01171 
01172 quint32 MetaDataDB::insertMethod(const QString &method, bool *ok)
01173 {
01174     QSqlQuery q = newQuery();
01175     q.prepare(S("INSERT INTO METHODS (method) VALUES(:method)"));
01176     q.bindValue(S(":method"), method);
01177     exec(q);
01178 
01179     if (errorOccurred()) {
01180         if (ok != 0) *ok = false;
01181         return 0;
01182     }
01183     return q.lastInsertId().toUInt(ok);
01184 }
01185 
01186 quint32 MetaDataDB::updateCredentials(const SignonIdentityInfo &info)
01187 {
01188     quint32 id;
01189     QSqlQuery q = newQuery();
01190 
01191     int flags = 0;
01192     if (info.validated()) flags |= Validated;
01193     if (info.storePassword()) flags |= RememberPassword;
01194     if (info.isUserNameSecret()) flags |= UserNameIsSecret;
01195 
01196     if (!info.isNew()) {
01197         TRACE() << "UPDATE:" << info.id() ;
01198         q.prepare(S("UPDATE CREDENTIALS SET caption = :caption, "
01199                     "username = :username, "
01200                     "flags = :flags, "
01201                     "type = :type WHERE id = :id"));
01202         q.bindValue(S(":id"), info.id());
01203     } else {
01204         TRACE() << "INSERT:" << info.id();
01205         q.prepare(S("INSERT INTO CREDENTIALS "
01206                     "(caption, username, flags, type) "
01207                     "VALUES(:caption, :username, :flags, :type)"));
01208     }
01209     q.bindValue(S(":username"),
01210                 info.isUserNameSecret() ? QString() : info.userName());
01211     q.bindValue(S(":caption"), info.caption());
01212     q.bindValue(S(":flags"), flags);
01213     q.bindValue(S(":type"), info.type());
01214     exec(q);
01215     if (errorOccurred()) {
01216         TRACE() << "Error occurred while updating crendentials";
01217         return 0;
01218     }
01219 
01220     if (info.isNew()) {
01221         /* Fetch id of the inserted credentials */
01222         QVariant idVariant = q.lastInsertId();
01223         if (!idVariant.isValid()) {
01224             TRACE() << "Error occurred while inserting crendentials";
01225             return 0;
01226         }
01227         id = idVariant.toUInt();
01228     } else {
01229         id = info.id() ;
01230     }
01231 
01232     return id;
01233 }
01234 
01235 bool MetaDataDB::updateRealms(quint32 id, const QStringList &realms, bool isNew)
01236 {
01237     QString queryStr;
01238 
01239     if (!isNew) {
01240         //remove realms list
01241         queryStr = QString::fromLatin1(
01242             "DELETE FROM REALMS WHERE identity_id = '%1'")
01243             .arg(id);
01244         exec(queryStr);
01245     }
01246 
01247     /* Realms insert */
01248     QSqlQuery q = newQuery();
01249     q.prepare(S("INSERT OR IGNORE INTO REALMS (identity_id, realm) "
01250                 "VALUES (:id, :realm)"));
01251     foreach (QString realm, realms) {
01252         q.bindValue(S(":id"), id);
01253         q.bindValue(S(":realm"), realm);
01254         exec(q);
01255         if (errorOccurred()) return false;
01256     }
01257     return true;
01258 }
01259 
01260 /* Error monitor class */
01261 
01262 CredentialsDB::ErrorMonitor::ErrorMonitor(CredentialsDB *db)
01263 {
01264     db->_lastError.setType(SignOn::CredentialsDBError::NoError);
01265     db->metaDataDB->clearError();
01266     if (db->secretsStorage != 0)
01267         db->secretsStorage->clearError();
01268     _db = db;
01269 }
01270 
01271 CredentialsDB::ErrorMonitor::~ErrorMonitor()
01272 {
01273     /* If there's an error set on the CredentialsDB, just let it be and return.
01274      * If not, take the error from the SqlDatabase objects, if any.
01275      */
01276     if (_db->_lastError.isValid())
01277         return;
01278 
01279     if (_db->secretsStorage != 0 &&
01280         _db->secretsStorage->lastError().isValid()) {
01281         _db->_lastError = _db->secretsStorage->lastError();
01282         return;
01283     }
01284 
01285     _db->_lastError = _db->metaDataDB->lastError();
01286 }
01287 
01288 /*    -------   CredentialsDB  implementation   -------    */
01289 
01290 CredentialsDB::CredentialsDB(const QString &metaDataDbName,
01291                              SignOn::AbstractSecretsStorage *secretsStorage):
01292     secretsStorage(secretsStorage),
01293     m_secretsCache(new SecretsCache),
01294     metaDataDB(new MetaDataDB(metaDataDbName))
01295 {
01296     noSecretsDB = SignOn::CredentialsDBError(
01297         QLatin1String("Secrets DB not opened"),
01298         SignOn::CredentialsDBError::ConnectionError);
01299 }
01300 
01301 CredentialsDB::~CredentialsDB()
01302 {
01303     TRACE();
01304 
01305     delete m_secretsCache;
01306 
01307     if (metaDataDB) {
01308         QString connectionName = metaDataDB->connectionName();
01309         delete metaDataDB;
01310         QSqlDatabase::removeDatabase(connectionName);
01311     }
01312 }
01313 
01314 bool CredentialsDB::init()
01315 {
01316     return metaDataDB->init();
01317 }
01318 
01319 bool CredentialsDB::openSecretsDB(const QString &secretsDbName)
01320 {
01321     QVariantMap configuration;
01322     configuration.insert(QLatin1String("name"), secretsDbName);
01323     if (!secretsStorage->initialize(configuration)) {
01324         TRACE() << "SecretsStorage initialization failed: " <<
01325             secretsStorage->lastError().text();
01326         return false;
01327     }
01328 
01329     m_secretsCache->storeToDB(secretsStorage);
01330     m_secretsCache->clear();
01331     return true;
01332 }
01333 
01334 bool CredentialsDB::isSecretsDBOpen()
01335 {
01336     return secretsStorage != 0 && secretsStorage->isOpen();
01337 }
01338 
01339 void CredentialsDB::closeSecretsDB()
01340 {
01341     if (secretsStorage != 0) secretsStorage->close();
01342 }
01343 
01344 SignOn::CredentialsDBError CredentialsDB::lastError() const
01345 {
01346     return _lastError;
01347 }
01348 
01349 QStringList CredentialsDB::methods(const quint32 id,
01350                                    const QString &securityToken)
01351 {
01352     INIT_ERROR();
01353     return metaDataDB->methods(id, securityToken);
01354 }
01355 
01356 bool CredentialsDB::checkPassword(const quint32 id,
01357                                   const QString &username,
01358                                   const QString &password)
01359 {
01360     INIT_ERROR();
01361     RETURN_IF_NO_SECRETS_DB(false);
01362     SignonIdentityInfo info = metaDataDB->identity(id);
01363     if (info.isUserNameSecret()) {
01364         return secretsStorage->checkPassword(id, username, password);
01365     } else {
01366         return username == info.userName() &&
01367             secretsStorage->checkPassword(id, QString(), password);
01368     }
01369 }
01370 
01371 SignonIdentityInfo CredentialsDB::credentials(const quint32 id,
01372                                               bool queryPassword)
01373 {
01374     TRACE() << "id:" << id << "queryPassword:" << queryPassword;
01375     INIT_ERROR();
01376     SignonIdentityInfo info = metaDataDB->identity(id);
01377     if (queryPassword && !info.isNew()) {
01378         QString username, password;
01379         if (info.storePassword() && isSecretsDBOpen()) {
01380             TRACE() << "Loading credentials from DB.";
01381             secretsStorage->loadCredentials(id, username, password);
01382         } else {
01383             TRACE() << "Looking up credentials from cache.";
01384             m_secretsCache->lookupCredentials(id, username, password);
01385         }
01386         if (info.isUserNameSecret())
01387             info.setUserName(username);
01388         info.setPassword(password);
01389 
01390 #ifdef DEBUG_ENABLED
01391         if (password.isEmpty()) {
01392             TRACE() << "Password is empty";
01393         }
01394 #endif
01395     }
01396     return info;
01397 }
01398 
01399 QList<SignonIdentityInfo>
01400 CredentialsDB::credentials(const QMap<QString, QString> &filter)
01401 {
01402     INIT_ERROR();
01403     return metaDataDB->identities(filter);
01404 }
01405 
01406 quint32 CredentialsDB::insertCredentials(const SignonIdentityInfo &info)
01407 {
01408     SignonIdentityInfo newInfo = info;
01409     if (!info.isNew())
01410         newInfo.setNew();
01411     return updateCredentials(newInfo);
01412 }
01413 
01414 quint32 CredentialsDB::updateCredentials(const SignonIdentityInfo &info)
01415 {
01416     INIT_ERROR();
01417     quint32 id = metaDataDB->updateIdentity(info);
01418     if (id == 0) return id;
01419 
01420     if (info.hasSecrets()) {
01421         QString password = info.password();
01422         QString userName;
01423         if (info.isUserNameSecret())
01424             userName = info.userName();
01425 
01426         if (info.storePassword() && isSecretsDBOpen()) {
01427             secretsStorage->updateCredentials(id, userName, password);
01428         } else {
01429             /* Cache username and password in memory */
01430             m_secretsCache->updateCredentials(id, userName, password,
01431                                               info.storePassword());
01432         }
01433     }
01434 
01435     Q_EMIT credentialsUpdated(id);
01436 
01437     return id;
01438 }
01439 
01440 bool CredentialsDB::removeCredentials(const quint32 id)
01441 {
01442     INIT_ERROR();
01443 
01444     /* We don't allow removing the credentials if the secrets DB is not
01445      * available */
01446     RETURN_IF_NO_SECRETS_DB(false);
01447 
01448     return secretsStorage->removeCredentials(id) &&
01449         metaDataDB->removeIdentity(id);
01450 }
01451 
01452 bool CredentialsDB::clear()
01453 {
01454     TRACE();
01455 
01456     INIT_ERROR();
01457 
01458     /* We don't allow clearing the DB if the secrets DB is not available */
01459     RETURN_IF_NO_SECRETS_DB(false);
01460 
01461     return secretsStorage->clear() && metaDataDB->clear();
01462 }
01463 
01464 QVariantMap CredentialsDB::loadData(const quint32 id, const QString &method)
01465 {
01466     TRACE() << "Loading:" << id << "," << method;
01467 
01468     INIT_ERROR();
01469     if (id == 0) return QVariantMap();
01470 
01471     quint32 methodId = metaDataDB->methodId(method);
01472     if (methodId == 0) return QVariantMap();
01473 
01474     if (isSecretsDBOpen()) {
01475         return secretsStorage->loadData(id, methodId);
01476     } else {
01477         TRACE() << "Looking up data from cache";
01478         return m_secretsCache->lookupData(id, methodId);
01479     }
01480 }
01481 
01482 bool CredentialsDB::storeData(const quint32 id, const QString &method,
01483                               const QVariantMap &data)
01484 {
01485     TRACE() << "Storing:" << id << "," << method;
01486 
01487     INIT_ERROR();
01488     if (id == 0) return false;
01489 
01490     quint32 methodId = metaDataDB->methodId(method);
01491     if (methodId == 0) {
01492         bool ok = false;
01493         methodId = metaDataDB->insertMethod(method, &ok);
01494         if (!ok)
01495             return false;
01496     }
01497 
01498     if (isSecretsDBOpen()) {
01499         return secretsStorage->storeData(id, methodId, data);
01500     } else {
01501         TRACE() << "Storing data into cache";
01502         m_secretsCache->updateData(id, methodId, data);
01503         return true;
01504     }
01505 }
01506 
01507 bool CredentialsDB::removeData(const quint32 id, const QString &method)
01508 {
01509     TRACE() << "Removing:" << id << "," << method;
01510 
01511     INIT_ERROR();
01512     RETURN_IF_NO_SECRETS_DB(false);
01513     if (id == 0) return false;
01514 
01515     quint32 methodId;
01516     if (!method.isEmpty()) {
01517         methodId = metaDataDB->methodId(method);
01518         if (methodId == 0) return false;
01519     } else {
01520         methodId = 0;
01521     }
01522 
01523     return secretsStorage->removeData(id, methodId);
01524 }
01525 
01526 QStringList CredentialsDB::accessControlList(const quint32 identityId)
01527 {
01528     INIT_ERROR();
01529     return metaDataDB->accessControlList(identityId);
01530 }
01531 
01532 QStringList CredentialsDB::ownerList(const quint32 identityId)
01533 {
01534     INIT_ERROR();
01535     return metaDataDB->ownerList(identityId);
01536 }
01537 
01538 QString CredentialsDB::credentialsOwnerSecurityToken(const quint32 identityId)
01539 {
01540     //return first owner token
01541     QStringList owners = ownerList(identityId);
01542     return owners.count() ? owners.at(0) : QString();
01543 }
01544 
01545 bool CredentialsDB::addReference(const quint32 id,
01546                                  const QString &token,
01547                                  const QString &reference)
01548 {
01549     INIT_ERROR();
01550     return metaDataDB->addReference(id, token, reference);
01551 }
01552 
01553 bool CredentialsDB::removeReference(const quint32 id,
01554                                     const QString &token,
01555                                     const QString &reference)
01556 {
01557     INIT_ERROR();
01558     return metaDataDB->removeReference(id, token, reference);
01559 }
01560 
01561 QStringList CredentialsDB::references(const quint32 id, const QString &token)
01562 {
01563     INIT_ERROR();
01564     return metaDataDB->references(id, token);
01565 }
01566 
01567 } //namespace SignonDaemonNS