git.maemo.org Git - vicar/blob - src/vicar-lib/cpp/databaseutility.cpp

   1 #include "databaseutility.h"
   2 #include <QSqlDatabase>
   3 #include <QSqlError>
   4 #include <QSqlQuery>
   5 #include <QFile>
   6 #include <QDir>
   7 #include <QVariant>
   8 #include <QDebug>
   9
  10 DatabaseUtility::DatabaseUtility(QObject *parent) :
  11     QObject(parent)
  12 {
  13 }
  14
  15 DatabaseUtility::~DatabaseUtility(){
  16 }
  17
  18 bool DatabaseUtility::openDatabase(){
  19     profilesDB = QSqlDatabase::addDatabase("QSQLITE");
  20     //QString path(QDir::home().path());
  21     //path.append(QDir::separator()).append("vicar.db.sqlite");
  22     QString path("/home/user/vicar.db.sqlite");
  23     path = QDir::toNativeSeparators(path);
  24     qDebug()<< "Opening database at "<<path;
  25     profilesDB.setDatabaseName(path);
  26     return profilesDB.open();
  27 }
  28
  29 bool DatabaseUtility::deleteDatabase(){
  30     closeDatabase();
  31     //QString path(QDir::home().path());
  32     //path.append(QDir::separator()).append("vicar.db.sqlite");
  33     QString path("/home/user/vicar.db.sqlite");
  34     path = QDir::toNativeSeparators(path);
  35     return QFile::remove(path);
  36 }
  37
  38 void DatabaseUtility::closeDatabase(){
  39     if (profilesDB.isOpen()){
  40         profilesDB.close();
  41     }
  42 }
  43
  44 bool DatabaseUtility::tableExists(QString tableName){
  45     bool exists = false;
  46     if (profilesDB.isOpen()){
  47         QStringList tablesList = profilesDB.tables();
  48         exists = tablesList.contains(tableName,Qt::CaseInsensitive);
  49     }
  50     return exists;
  51 }
  52
  53 bool DatabaseUtility::createProfilesTable(){
  54     bool result = false;
  55     if (profilesDB.isOpen()){
  56         QSqlQuery query;
  57         QString strQuery = "create table profiles"
  58                            "(id integer primary key,"
  59                            "numberpattern varchar(20),"
  60                            "gatewaynumber varchar(30),"
  61                            "dtmfformat varchar(100),"
  62                            "dtmfprefix varchar(20),"
  63                            "dtmfsuffix varchar(20)"
  64                            ")";
  65         result = query.exec(strQuery);
  66     }
  67     return result;
  68 }
  69
  70 bool DatabaseUtility::selectProfile(int id,org::maemo::vicar::Profile* profile){
  71     bool result = false;
  72     if (profilesDB.isOpen()){
  73         QString strQuery = QString("select * from profiles where id = %1").arg(id);
  74         qDebug() << "SQL>"<<strQuery;
  75         QSqlQuery query(strQuery);
  76         if (query.next()){
  77             profile->profileID = query.value(0).toInt();
  78             profile->phoneNumberPattern = query.value(1).toString();
  79             profile->gatewayNumber = query.value(2).toString();
  80             profile->dtmfFormat = query.value(3).toString();
  81             profile->dtmfPrefix = query.value(4).toString();
  82             profile->dtmfSuffix = query.value(5).toString();
  83             result = true;
  84         }
  85     }
  86     return result;
  87 }
  88
  89 bool DatabaseUtility::getAllProfiles(org::maemo::vicar::ProfileList* profileList){
  90     bool result = false;
  91     if (profilesDB.isOpen()){
  92         QString strQuery = QString("select * from profiles order by id");
  93         qDebug() << "SQL>"<<strQuery;
  94         QSqlQuery query(strQuery);
  95         while (query.next()){
  96             org::maemo::vicar::Profile profile;
  97             profile.profileID = query.value(0).toInt();
  98             profile.phoneNumberPattern = query.value(1).toString();
  99             profile.gatewayNumber = query.value(2).toString();
 100             profile.dtmfFormat = query.value(3).toString();
 101             profile.dtmfPrefix = query.value(4).toString();
 102             profile.dtmfSuffix = query.value(5).toString();
 103             profileList->append(profile);
 104         }
 105         result = true;
 106     }
 107     return result;
 108 }
 109
 110 bool DatabaseUtility::findProfileByNumber(QString number,org::maemo::vicar::Profile* profile){
 111     bool result = false;
 112     if (profilesDB.isOpen()){
 113         QString strQuery = QString("select * from profiles where '%1' like numberpattern||'%' order by length(numberpattern) desc")
 114                            .arg(number);
 115         qDebug() << "SQL>"<<strQuery;
 116         QSqlQuery query(strQuery);
 117         if (query.next()){
 118             profile->profileID = query.value(0).toInt();
 119             profile->phoneNumberPattern = query.value(1).toString();
 120             profile->gatewayNumber = query.value(2).toString();
 121             profile->dtmfFormat = query.value(3).toString();
 122             profile->dtmfPrefix = query.value(4).toString();
 123             profile->dtmfSuffix = query.value(5).toString();
 124         }
 125         result = true;
 126     }
 127     return result;
 128 }
 129
 130 bool DatabaseUtility::getDefaultProfile(org::maemo::vicar::Profile *profile){
 131     bool result = false;
 132     if (profilesDB.isOpen()){
 133         QString strQuery = QString("select * from profiles where numberpattern = '%' order by id");
 134         qDebug() << "SQL>"<<strQuery;
 135         QSqlQuery query(strQuery);
 136         if (query.next()){
 137             profile->profileID = query.value(0).toInt();
 138             profile->phoneNumberPattern = query.value(1).toString();
 139             profile->gatewayNumber = query.value(2).toString();
 140             profile->dtmfFormat = query.value(3).toString();
 141             profile->dtmfPrefix = query.value(4).toString();
 142             profile->dtmfSuffix = query.value(5).toString();
 143             result = true;
 144         }
 145     }
 146     return result;
 147 }
 148
 149 int DatabaseUtility::insertProfile(org::maemo::vicar::Profile profile){
 150     int newId   = -1;
 151     bool result = false;
 152     if (profilesDB.isOpen()){
 153         bool continueInsert = true;
 154         if (!this->tableExists("profiles")){
 155             continueInsert = this->createProfilesTable();
 156         }
 157
 158         if (continueInsert)
 159         {
 160             QSqlQuery query;
 161             QString strQuery = QString("insert into profiles values(NULL,'%1','%2','%3','%4','%5')")
 162                                .arg(profile.phoneNumberPattern).arg(profile.gatewayNumber)
 163                                .arg(profile.dtmfFormat).arg(profile.dtmfPrefix).arg(profile.dtmfSuffix);
 164             qDebug() << "SQL>"<<strQuery;
 165             result = query.exec(strQuery);
 166             if (result){
 167                 newId = query.lastInsertId().toInt();
 168             }
 169         }
 170     }
 171     return newId;
 172 }
 173
 174 bool DatabaseUtility::updateProfile(org::maemo::vicar::Profile profile){
 175     bool result = false;
 176     if (profilesDB.isOpen()){
 177         QSqlQuery query;
 178         QString strQuery = QString("update profiles set numberpattern = '%1', "
 179                                    "gatewaynumber = '%2', dtmfformat = '%3', "
 180                                    "dtmfprefix = '%4', dtmfsuffix = '%5' where id = %6")
 181                            .arg(profile.phoneNumberPattern).arg(profile.gatewayNumber)
 182                            .arg(profile.dtmfFormat).arg(profile.dtmfPrefix)
 183                            .arg(profile.dtmfSuffix).arg(profile.profileID);
 184         qDebug() << "SQL>"<<strQuery;
 185         result = query.exec(strQuery);
 186     }
 187     return result;
 188 }
 189
 190 bool DatabaseUtility::deleteProfile(int id){
 191     bool result = false;
 192     if (profilesDB.isOpen()){
 193         QSqlQuery query;
 194         QString strQuery = QString("delete from profiles where id=%1").arg(id);
 195         qDebug() << "SQL>"<<strQuery;
 196         result = query.exec(strQuery);
 197     }
 198     return result;
 199 }
 200
 201 QSqlError DatabaseUtility::lastError(){
 202     return profilesDB.lastError();
 203 }