1 #include "databaseutility.h"
2 #include <QSqlDatabase>
10 DatabaseUtility::DatabaseUtility(QObject *parent) :
15 DatabaseUtility::~DatabaseUtility(){
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();
29 bool DatabaseUtility::deleteDatabase(){
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);
38 void DatabaseUtility::closeDatabase(){
39 if (profilesDB.isOpen()){
44 bool DatabaseUtility::tableExists(QString tableName){
46 if (profilesDB.isOpen()){
47 QStringList tablesList = profilesDB.tables();
48 exists = tablesList.contains(tableName,Qt::CaseInsensitive);
53 bool DatabaseUtility::createProfilesTable(){
55 if (profilesDB.isOpen()){
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)"
65 result = query.exec(strQuery);
70 bool DatabaseUtility::selectProfile(int id,org::maemo::vicar::Profile* profile){
72 if (profilesDB.isOpen()){
73 QString strQuery = QString("select * from profiles where id = %1").arg(id);
74 qDebug() << "SQL>"<<strQuery;
75 QSqlQuery query(strQuery);
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();
89 bool DatabaseUtility::getAllProfiles(org::maemo::vicar::ProfileList* profileList){
91 if (profilesDB.isOpen()){
92 QString strQuery = QString("select * from profiles order by id");
93 qDebug() << "SQL>"<<strQuery;
94 QSqlQuery query(strQuery);
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);
110 bool DatabaseUtility::findProfileByNumber(QString number,org::maemo::vicar::Profile* profile){
112 if (profilesDB.isOpen()){
113 QString strQuery = QString("select * from profiles where '%1' like numberpattern||'%' order by length(numberpattern) desc")
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::getDefaultProfile(org::maemo::vicar::Profile *profile){
132 if (profilesDB.isOpen()){
133 QString strQuery = QString("select * from profiles where numberpattern = '%' order by id");
134 qDebug() << "SQL>"<<strQuery;
135 QSqlQuery query(strQuery);
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();
149 int DatabaseUtility::insertProfile(org::maemo::vicar::Profile profile){
152 if (profilesDB.isOpen()){
153 bool continueInsert = true;
154 if (!this->tableExists("profiles")){
155 continueInsert = this->createProfilesTable();
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);
167 newId = query.lastInsertId().toInt();
174 bool DatabaseUtility::updateProfile(org::maemo::vicar::Profile profile){
176 if (profilesDB.isOpen()){
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);
190 bool DatabaseUtility::deleteProfile(int id){
192 if (profilesDB.isOpen()){
194 QString strQuery = QString("delete from profiles where id=%1").arg(id);
195 qDebug() << "SQL>"<<strQuery;
196 result = query.exec(strQuery);
201 QSqlError DatabaseUtility::lastError(){
202 return profilesDB.lastError();