Version 0.7-0

[vicar] / src / vicar-lib / cpp / databaseutility.cpp

diff --git a/src/vicar-lib/cpp/databaseutility.cpp b/src/vicar-lib/cpp/databaseutility.cpp
new file mode 100644 (file)
index 0000000..e2df72b
--- /dev/null
+++ b/src/vicar-lib/cpp/databaseutility.cpp
@@ -0,0 +1,203 @@
+#include "databaseutility.h"
+#include <QSqlDatabase>
+#include <QSqlError>
+#include <QSqlQuery>
+#include <QFile>
+#include <QDir>
+#include <QVariant>
+#include <QDebug>
+
+DatabaseUtility::DatabaseUtility(QObject *parent) :
+    QObject(parent)
+{
+}
+
+DatabaseUtility::~DatabaseUtility(){
+}
+
+bool DatabaseUtility::openDatabase(){
+    profilesDB = QSqlDatabase::addDatabase("QSQLITE");
+    //QString path(QDir::home().path());
+    //path.append(QDir::separator()).append("vicar.db.sqlite");
+    QString path("/home/user/vicar.db.sqlite");
+    path = QDir::toNativeSeparators(path);
+    qDebug()<< "Opening database at "<<path;
+    profilesDB.setDatabaseName(path);
+    return profilesDB.open();
+}
+
+bool DatabaseUtility::deleteDatabase(){
+    closeDatabase();
+    //QString path(QDir::home().path());
+    //path.append(QDir::separator()).append("vicar.db.sqlite");
+    QString path("/home/user/vicar.db.sqlite");
+    path = QDir::toNativeSeparators(path);
+    return QFile::remove(path);
+}
+
+void DatabaseUtility::closeDatabase(){
+    if (profilesDB.isOpen()){
+        profilesDB.close();
+    }
+}
+
+bool DatabaseUtility::tableExists(QString tableName){
+    bool exists = false;
+    if (profilesDB.isOpen()){
+        QStringList tablesList = profilesDB.tables();
+        exists = tablesList.contains(tableName,Qt::CaseInsensitive);
+    }
+    return exists;
+}
+
+bool DatabaseUtility::createProfilesTable(){
+    bool result = false;
+    if (profilesDB.isOpen()){
+        QSqlQuery query;
+        QString strQuery = "create table profiles"
+                           "(id integer primary key,"
+                           "numberpattern varchar(20),"
+                           "gatewaynumber varchar(30),"
+                           "dtmfformat varchar(100),"
+                           "dtmfprefix varchar(20),"
+                           "dtmfsuffix varchar(20)"
+                           ")";
+        result = query.exec(strQuery);
+    }
+    return result;
+}
+
+bool DatabaseUtility::selectProfile(int id,org::maemo::vicar::Profile* profile){
+    bool result = false;
+    if (profilesDB.isOpen()){
+        QString strQuery = QString("select * from profiles where id = %1").arg(id);
+        qDebug() << "SQL>"<<strQuery;
+        QSqlQuery query(strQuery);
+        if (query.next()){
+            profile->profileID = query.value(0).toInt();
+            profile->phoneNumberPattern = query.value(1).toString();
+            profile->gatewayNumber = query.value(2).toString();
+            profile->dtmfFormat = query.value(3).toString();
+            profile->dtmfPrefix = query.value(4).toString();
+            profile->dtmfSuffix = query.value(5).toString();
+            result = true;
+        }
+    }
+    return result;
+}
+
+bool DatabaseUtility::getAllProfiles(org::maemo::vicar::ProfileList* profileList){
+    bool result = false;
+    if (profilesDB.isOpen()){
+        QString strQuery = QString("select * from profiles order by id");
+        qDebug() << "SQL>"<<strQuery;
+        QSqlQuery query(strQuery);
+        while (query.next()){
+            org::maemo::vicar::Profile profile;
+            profile.profileID = query.value(0).toInt();
+            profile.phoneNumberPattern = query.value(1).toString();
+            profile.gatewayNumber = query.value(2).toString();
+            profile.dtmfFormat = query.value(3).toString();
+            profile.dtmfPrefix = query.value(4).toString();
+            profile.dtmfSuffix = query.value(5).toString();
+            profileList->append(profile);
+        }
+        result = true;
+    }
+    return result;
+}
+
+bool DatabaseUtility::findProfileByNumber(QString number,org::maemo::vicar::Profile* profile){
+    bool result = false;
+    if (profilesDB.isOpen()){
+        QString strQuery = QString("select * from profiles where '%1' like numberpattern||'%' order by length(numberpattern) desc")
+                           .arg(number);
+        qDebug() << "SQL>"<<strQuery;
+        QSqlQuery query(strQuery);
+        if (query.next()){
+            profile->profileID = query.value(0).toInt();
+            profile->phoneNumberPattern = query.value(1).toString();
+            profile->gatewayNumber = query.value(2).toString();
+            profile->dtmfFormat = query.value(3).toString();
+            profile->dtmfPrefix = query.value(4).toString();
+            profile->dtmfSuffix = query.value(5).toString();
+        }
+        result = true;
+    }
+    return result;
+}
+
+bool DatabaseUtility::getDefaultProfile(org::maemo::vicar::Profile *profile){
+    bool result = false;
+    if (profilesDB.isOpen()){
+        QString strQuery = QString("select * from profiles where numberpattern = '%' order by id");
+        qDebug() << "SQL>"<<strQuery;
+        QSqlQuery query(strQuery);
+        if (query.next()){
+            profile->profileID = query.value(0).toInt();
+            profile->phoneNumberPattern = query.value(1).toString();
+            profile->gatewayNumber = query.value(2).toString();
+            profile->dtmfFormat = query.value(3).toString();
+            profile->dtmfPrefix = query.value(4).toString();
+            profile->dtmfSuffix = query.value(5).toString();
+            result = true;
+        }
+    }
+    return result;
+}
+
+int DatabaseUtility::insertProfile(org::maemo::vicar::Profile profile){
+    int newId   = -1;
+    bool result = false;    
+    if (profilesDB.isOpen()){
+        bool continueInsert = true;
+        if (!this->tableExists("profiles")){
+            continueInsert = this->createProfilesTable();
+        }
+
+        if (continueInsert)
+        {
+            QSqlQuery query;
+            QString strQuery = QString("insert into profiles values(NULL,'%1','%2','%3','%4','%5')")
+                               .arg(profile.phoneNumberPattern).arg(profile.gatewayNumber)
+                               .arg(profile.dtmfFormat).arg(profile.dtmfPrefix).arg(profile.dtmfSuffix);
+            qDebug() << "SQL>"<<strQuery;
+            result = query.exec(strQuery);
+            if (result){
+                newId = query.lastInsertId().toInt();
+            }
+        }
+    }
+    return newId;
+}
+
+bool DatabaseUtility::updateProfile(org::maemo::vicar::Profile profile){
+    bool result = false;
+    if (profilesDB.isOpen()){
+        QSqlQuery query;
+        QString strQuery = QString("update profiles set numberpattern = '%1', "
+                                   "gatewaynumber = '%2', dtmfformat = '%3', "
+                                   "dtmfprefix = '%4', dtmfsuffix = '%5' where id = %6")
+                           .arg(profile.phoneNumberPattern).arg(profile.gatewayNumber)
+                           .arg(profile.dtmfFormat).arg(profile.dtmfPrefix)
+                           .arg(profile.dtmfSuffix).arg(profile.profileID);
+        qDebug() << "SQL>"<<strQuery;
+        result = query.exec(strQuery);
+    }
+    return result;
+}
+
+bool DatabaseUtility::deleteProfile(int id){
+    bool result = false;
+    if (profilesDB.isOpen()){
+        QSqlQuery query;
+        QString strQuery = QString("delete from profiles where id=%1").arg(id);
+        qDebug() << "SQL>"<<strQuery;
+        result = query.exec(strQuery);
+    }
+    return result;
+}
+
+QSqlError DatabaseUtility::lastError(){
+    return profilesDB.lastError();
+}