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

   1 #include "databaseutility.h"
   2 #include <QCryptographicHash>
   3 #include <QSqlDatabase>
   4 #include <QSqlError>
   5 #include <QSqlQuery>
   6 #include <QFile>
   7 #include <QDir>
   8 #include <QVariant>
   9 #include <QDebug>
  10
  11 DatabaseUtility::DatabaseUtility(QObject *parent) :
  12     QObject(parent)
  13 {
  14 }
  15
  16 DatabaseUtility::~DatabaseUtility(){
  17 }
  18
  19 bool DatabaseUtility::openDatabase(){
  20     vicarDB = QSqlDatabase::addDatabase("QSQLITE");
  21 #if defined(Q_WS_MAEMO_5)
  22     QString path("/home/user/vicar.db.sqlite");
  23 #elif defined(Q_WS_MAEMO_6)
  24     QString path("/home/user/.local/share/data/QML/OfflineStorage/Databases/");
  25     path.append(QCryptographicHash::hash("VICaR",QCryptographicHash::Md5).toHex()).append(".sqlite");
  26 #endif
  27     path = QDir::toNativeSeparators(path);
  28     qDebug()<< "Opening database at "<<path;
  29     vicarDB.setDatabaseName(path);
  30     return vicarDB.open();
  31 }
  32
  33 bool DatabaseUtility::deleteDatabase(){
  34     closeDatabase();
  35 #if defined(Q_WS_MAEMO_5)
  36     QString path("/home/user/vicar.db.sqlite");
  37 #elif defined(Q_WS_MAEMO_6)
  38     QString path("/home/user/.local/share/data/QML/OfflineStorage/Databases/");
  39     path.append(QCryptographicHash::hash("VICaR",QCryptographicHash::Md5).toHex()).append(".sqlite");
  40 #endif
  41     path = QDir::toNativeSeparators(path);
  42     return QFile::remove(path);
  43 }
  44
  45 void DatabaseUtility::closeDatabase(){
  46     if (vicarDB.isOpen()){
  47         vicarDB.close();
  48     }
  49 }
  50
  51 bool DatabaseUtility::tableExists(QString tableName){
  52     bool exists = false;
  53     if (vicarDB.isOpen()){
  54         QStringList tablesList = vicarDB.tables();
  55         exists = tablesList.contains(tableName,Qt::CaseInsensitive);
  56     }
  57     return exists;
  58 }
  59
  60 bool DatabaseUtility::prepareTables(){
  61     bool result = true;
  62     if (vicarDB.isOpen()){
  63         QSqlQuery query;
  64         QString strQuery = "create table if not exists profiles"
  65                            "(id integer primary key,"
  66                            "numberpattern varchar(20),"
  67                            "gatewaynumber varchar(30),"
  68                            "dtmfformat varchar(100),"
  69                            "dtmfprefix varchar(20),"
  70                            "dtmfsuffix varchar(20)"
  71                            ")";
  72         if (!query.exec(strQuery)){
  73             qDebug() << "Error creating profiles table";
  74             result = false;
  75         }
  76
  77         strQuery = "CREATE TABLE IF NOT EXISTS settings(setting TEXT UNIQUE, value TEXT)";
  78         if (!query.exec(strQuery)){
  79             qDebug() << "Error creating settings table";
  80             result = false;
  81         }
  82     }
  83     return result;
  84 }
  85
  86 QString DatabaseUtility::getSetting(QString setting){
  87     QString value = "Unknown";
  88     if (vicarDB.isOpen()){
  89         QString strQuery = QString("'SELECT value FROM settings WHERE setting='%1'").arg(setting);
  90         qDebug() << "SQL>"<<strQuery;
  91         QSqlQuery query(strQuery);
  92         if (query.next()){
  93             value = query.value(0).toString();
  94         }
  95     }
  96     return value;
  97 }
  98
  99 bool DatabaseUtility::setSetting(QString setting, QString value){
 100     bool result = false;
 101     if (vicarDB.isOpen()){
 102         QSqlQuery query;
 103         QString strQuery = QString("INSERT OR REPLACE INTO settings VALUES ('%1','%2')")
 104                            .arg(setting).arg(value);
 105         qDebug() << "SQL>"<<strQuery;
 106         result = query.exec(strQuery);
 107     }
 108     return result;
 109 }
 110
 111 bool DatabaseUtility::selectProfile(int id,org::maemo::vicar::Profile* profile){
 112     bool result = false;
 113     if (vicarDB.isOpen()){
 114         QString strQuery = QString("select * from profiles where id = %1").arg(id);
 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             result = true;
 125         }
 126     }
 127     return result;
 128 }
 129
 130 bool DatabaseUtility::getAllProfiles(org::maemo::vicar::ProfileList* profileList){
 131     bool result = false;
 132     if (vicarDB.isOpen()){
 133         QString strQuery = QString("select * from profiles order by id");
 134         qDebug() << "SQL>"<<strQuery;
 135         QSqlQuery query(strQuery);
 136         while (query.next()){
 137             org::maemo::vicar::Profile profile;
 138             profile.profileID = query.value(0).toInt();
 139             profile.phoneNumberPattern = query.value(1).toString();
 140             profile.gatewayNumber = query.value(2).toString();
 141             profile.dtmfFormat = query.value(3).toString();
 142             profile.dtmfPrefix = query.value(4).toString();
 143             profile.dtmfSuffix = query.value(5).toString();
 144             profileList->append(profile);
 145         }
 146         result = true;
 147     }
 148     return result;
 149 }
 150
 151 bool DatabaseUtility::findProfileByNumber(QString number,org::maemo::vicar::Profile* profile){
 152     bool result = false;
 153     if (vicarDB.isOpen()){
 154         QString strQuery = QString("select * from profiles where '%1' like numberpattern||'%' order by length(numberpattern) desc")
 155                            .arg(number);
 156         qDebug() << "SQL>"<<strQuery;
 157         QSqlQuery query(strQuery);
 158         if (query.next()){
 159             profile->profileID = query.value(0).toInt();
 160             profile->phoneNumberPattern = query.value(1).toString();
 161             profile->gatewayNumber = query.value(2).toString();
 162             profile->dtmfFormat = query.value(3).toString();
 163             profile->dtmfPrefix = query.value(4).toString();
 164             profile->dtmfSuffix = query.value(5).toString();
 165         }
 166         result = true;
 167     }
 168     return result;
 169 }
 170
 171 bool DatabaseUtility::getDefaultProfile(org::maemo::vicar::Profile *profile){
 172     bool result = false;
 173     if (vicarDB.isOpen()){
 174         QString strQuery = QString("select * from profiles where numberpattern = '%' order by id");
 175         qDebug() << "SQL>"<<strQuery;
 176         QSqlQuery query(strQuery);
 177         if (query.next()){
 178             profile->profileID = query.value(0).toInt();
 179             profile->phoneNumberPattern = query.value(1).toString();
 180             profile->gatewayNumber = query.value(2).toString();
 181             profile->dtmfFormat = query.value(3).toString();
 182             profile->dtmfPrefix = query.value(4).toString();
 183             profile->dtmfSuffix = query.value(5).toString();
 184             result = true;
 185         }
 186     }
 187     return result;
 188 }
 189
 190 int DatabaseUtility::insertProfile(org::maemo::vicar::Profile profile){
 191     int newId   = -1;
 192     bool result = false;
 193     if (vicarDB.isOpen()){
 194         bool continueInsert = true;
 195         if (!this->tableExists("profiles")){
 196             continueInsert = this->prepareTables();
 197         }
 198
 199         if (continueInsert)
 200         {
 201             QSqlQuery query;
 202             QString strQuery = QString("insert into profiles values(NULL,'%1','%2','%3','%4','%5')")
 203                                .arg(profile.phoneNumberPattern).arg(profile.gatewayNumber)
 204                                .arg(profile.dtmfFormat).arg(profile.dtmfPrefix).arg(profile.dtmfSuffix);
 205             qDebug() << "SQL>"<<strQuery;
 206             result = query.exec(strQuery);
 207             if (result){
 208                 newId = query.lastInsertId().toInt();
 209             }
 210         }
 211     }
 212     return newId;
 213 }
 214
 215 bool DatabaseUtility::updateProfile(org::maemo::vicar::Profile profile){
 216     bool result = false;
 217     if (vicarDB.isOpen()){
 218         QSqlQuery query;
 219         QString strQuery = QString("update profiles set numberpattern = '%1', "
 220                                    "gatewaynumber = '%2', dtmfformat = '%3', "
 221                                    "dtmfprefix = '%4', dtmfsuffix = '%5' where id = %6")
 222                            .arg(profile.phoneNumberPattern).arg(profile.gatewayNumber)
 223                            .arg(profile.dtmfFormat).arg(profile.dtmfPrefix)
 224                            .arg(profile.dtmfSuffix).arg(profile.profileID);
 225         qDebug() << "SQL>"<<strQuery;
 226         result = query.exec(strQuery);
 227     }
 228     return result;
 229 }
 230
 231 bool DatabaseUtility::deleteProfile(int id){
 232     bool result = false;
 233     if (vicarDB.isOpen()){
 234         QSqlQuery query;
 235         QString strQuery = QString("delete from profiles where id=%1").arg(id);
 236         qDebug() << "SQL>"<<strQuery;
 237         result = query.exec(strQuery);
 238     }
 239     return result;
 240 }
 241
 242 QSqlError DatabaseUtility::lastError(){
 243     return vicarDB.lastError();
 244 }