1 #include "databaseutility.h"
2 #include <QCryptographicHash>
3 #include <QSqlDatabase>
11 DatabaseUtility::DatabaseUtility(QObject *parent) :
16 DatabaseUtility::~DatabaseUtility(){
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");
27 path = QDir::toNativeSeparators(path);
28 qDebug()<< "Opening database at "<<path;
29 vicarDB.setDatabaseName(path);
30 return vicarDB.open();
33 bool DatabaseUtility::deleteDatabase(){
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");
41 path = QDir::toNativeSeparators(path);
42 return QFile::remove(path);
45 void DatabaseUtility::closeDatabase(){
46 if (vicarDB.isOpen()){
51 bool DatabaseUtility::tableExists(QString tableName){
53 if (vicarDB.isOpen()){
54 QStringList tablesList = vicarDB.tables();
55 exists = tablesList.contains(tableName,Qt::CaseInsensitive);
60 bool DatabaseUtility::prepareTables(){
62 if (vicarDB.isOpen()){
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)"
72 if (!query.exec(strQuery)){
73 qDebug() << "Error creating profiles table";
77 strQuery = "CREATE TABLE IF NOT EXISTS settings(setting TEXT UNIQUE, value TEXT)";
78 if (!query.exec(strQuery)){
79 qDebug() << "Error creating settings table";
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);
93 value = query.value(0).toString();
99 bool DatabaseUtility::setSetting(QString setting, QString value){
101 if (vicarDB.isOpen()){
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);
111 bool DatabaseUtility::selectProfile(int id,org::maemo::vicar::Profile* profile){
113 if (vicarDB.isOpen()){
114 QString strQuery = QString("select * from profiles where id = %1").arg(id);
115 qDebug() << "SQL>"<<strQuery;
116 QSqlQuery query(strQuery);
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();
130 bool DatabaseUtility::getAllProfiles(org::maemo::vicar::ProfileList* profileList){
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);
151 bool DatabaseUtility::findProfileByNumber(QString number,org::maemo::vicar::Profile* profile){
153 if (vicarDB.isOpen()){
154 QString strQuery = QString("select * from profiles where '%1' like numberpattern||'%' order by length(numberpattern) desc")
156 qDebug() << "SQL>"<<strQuery;
157 QSqlQuery query(strQuery);
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();
171 bool DatabaseUtility::getDefaultProfile(org::maemo::vicar::Profile *profile){
173 if (vicarDB.isOpen()){
174 QString strQuery = QString("select * from profiles where numberpattern = '%' order by id");
175 qDebug() << "SQL>"<<strQuery;
176 QSqlQuery query(strQuery);
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();
190 int DatabaseUtility::insertProfile(org::maemo::vicar::Profile profile){
193 if (vicarDB.isOpen()){
194 bool continueInsert = true;
195 if (!this->tableExists("profiles")){
196 continueInsert = this->prepareTables();
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);
208 newId = query.lastInsertId().toInt();
215 bool DatabaseUtility::updateProfile(org::maemo::vicar::Profile profile){
217 if (vicarDB.isOpen()){
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);
231 bool DatabaseUtility::deleteProfile(int id){
233 if (vicarDB.isOpen()){
235 QString strQuery = QString("delete from profiles where id=%1").arg(id);
236 qDebug() << "SQL>"<<strQuery;
237 result = query.exec(strQuery);
242 QSqlError DatabaseUtility::lastError(){
243 return vicarDB.lastError();